USE [METX]
GO
/****** Object:  Trigger [mt].[cal_i_after]    Script Date: 9/24/2024 11:38:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[cal_i_after]
	ON [mt].[Calibration]
	AFTER INSERT
/******************************************************************************************************************************************************/
/***	TSB	- 05/26/2016	No original conversion date or author, but modifications start here. 													***/   
/***	THIS TRIGGER MAY NEED WORK AS IT WAS PROCESSING ALL ROWS IN THE CUSTOMIZE TABLE WITH A CURSOR NOT JUST THOSE THAT START WITH I IN DCOL. 	***/   
/***	I changed it to match intent.	Added drop trigger, changed all NVARCHAR declarations to NVARCHAR, added semicolons, added  				***/
/**		LOCAL FORWARD_ONLY	READ_ONLY to c_cai_customize and c_cai_dbconfig cursors, adding unicode definitions to strings, increased the size		***/
/***	of the variable @cmd from 200 to 1000 since ctag(80) + mtag (80) =160 only leave 40 for the rest of the commnad to prevent truncation, 		***/
/***	added error message and uncommented the THROW statement in the catch block																	***/
/***	JCF - 06/09/2016	Rewrote triggers as data set processes																					***/
/***	JCF - 10/29/2017	added return when @@ROWCOUNT = 0 to stop trigger as soon as possible													***/
/******************************************************************************************************************************************************/
AS 
BEGIN
IF @@ROWCOUNT = 0 RETURN;
	BEGIN TRY
		SET NOCOUNT ON;
		BEGIN TRANSACTION;
/*	Start of mt.cal_archive_insert  
		Marks the current calibration record (C2339=1)
		Updates Inventory record per mt.customiz 
*/

		DECLARE @i_loc INT = 1;
		DECLARE @row_cal INT = 1;
		DECLARE @i_dcopy INT;
		DECLARE @row_dcopy INT;
		DECLARE @stmt NVARCHAR(512);
		DECLARE @tmp_cal TABLE
			(
			ColumnID INT IDENTITY(1,1), 
			mtag NVARCHAR(80),
			ctag NVARCHAR(80)
			);
		DECLARE @tmp_dcopy TABLE 
			(
				ColumnID INT IDENTITY(1,1), 
				dcopy	 NVARCHAR(20), 
				datacol  NVARCHAR(20)
			); 
		
				
		--Find the latest calibration record for each piece of equipment.
		INSERT INTO @tmp_cal (ctag, mtag)  
				SELECT CTAG, cal.MTAG FROM mt.Calibration cal 
					JOIN (SELECT MTAG, 
						CAST(MAX(DATEADD(DAY, DATEDIFF(DAY, CAST('1900-1-1' AS DATE), C2301), CAST(convert(TIME, C2333) AS DATETIME2))) AS DATE) as C2301,  
						CAST(MAX(DATEADD(DAY, DATEDIFF(DAY, CAST('1900-1-1' AS DATE), C2301), CAST(convert(TIME, C2333) AS DATETIME2))) AS TIME) as C2333 
						from mt.Calibration where MTAG in (SELECT DISTINCT MTAG FROM inserted) group by MTAG) tmp 
					on (cal.MTAG=tmp.MTAG and cal.C2301 = tmp.C2301 and cal.C2333= CONVERT(NVARCHAR(8),tmp.C2333,108))

		UPDATE mt.Calibration SET C2339 = 0 WHERE MTAG = ANY(SELECT MTAG FROM @tmp_cal);
		UPDATE mt.Calibration SET C2339 = 1 WHERE CTAG = ANY(SELECT CTAG FROM @tmp_cal);

		SELECT @row_dcopy = COUNT(*) FROM mt.customiz WHERE fform=4 AND dcopy <> 'OFF';
		IF @row_dcopy > 0 
			INSERT INTO @tmp_dcopy (datacol,dcopy) SELECT datacol, dcopy FROM mt.customiz WHERE fform=4 AND dcopy <> 'OFF';

			SELECT @row_cal = COUNT(*) FROM @tmp_cal;
			
			WHILE @i_loc <= @row_cal
			BEGIN
				WHILE @i_dcopy <= @row_dcopy
				BEGIN
					SET @stmt =  N'UPDATE mt.Inventory SET I' 
						+ (SELECT dcopy FROM @tmp_dcopy WHERE ColumnID = @i_dcopy)
						+ ' = (SELECT '
						+ (SELECT datacol FROM @tmp_dcopy WHERE ColumnID = @i_dcopy) 
						+ 'FROM mt.Calibration WHERE CTAG = '
						+ (SELECT ctag FROM @tmp_cal WHERE ColumnID = @i_loc)
						+ ') WHERE MTAG = '
						+ (SELECT mtag FROM @tmp_cal WHERE ColumnID = @i_loc)
						+ ')';
					EXECUTE sp_executesql @stmt
				SET	@i_dcopy = @i_dcopy + 1; 
				END
				SET @i_loc = @i_loc + 1;
			END
		--Reset status flag 4225 & 4224
		UPDATE mt.Inventory SET I4225 = NULL
			WHERE MTAG IN (SELECT DISTINCT MTAG FROM inserted, mt.dbconfig WHERE C2323 = 'Y' AND b_cal_4225 = 'Y' AND b_cal_p = 'Y');
		UPDATE mt.Inventory SET I4224 = NULL
			WHERE MTAG IN (SELECT DISTINCT MTAG FROM inserted, mt.dbconfig WHERE C2323 = 'Y' AND b_cal_4224 = 'Y' AND b_cal_p = 'Y');

/*	end of mt.cal_archive_insert  */

 		COMMIT TRANSACTION;
		SET NOCOUNT OFF;
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

