USE [METX]
GO
/****** Object:  Trigger [mt].[cal_i_instead]    Script Date: 9/24/2024 11:40:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[cal_i_instead]
   ON  [mt].[Calibration]
   INSTEAD OF INSERT
/******************************************************************************************************************************************************/
/***	TSB	- 05/26/2016	No original conversion date or author, but modifications start here.													***/   
/***	Added transactions, uncommented the THROW statement in the catch block																		***/
/***	JCF - 06/09/2016	Rewrote triggers as data set processes																					***/
/***	JCF - 08/11/2016	Added missing insert statement																							***/
/***	JCF - 01/27/2017	Added check and correction for duplicated CTAGs																			***/
/***	JCF	- 03/03/2017	Set created/modified by if empty and over write created/modified when with server time									***/
/***	AGH	- 06/07/2017	Updated 'created by' (C2336) to accept value being passed from METx2 or use system user if no value is provided			***/
/***	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;

		SELECT * INTO #tmp_ins FROM inserted;
/* Start of cal_tag_trig
			If CTAG is not provided generate a unique CTAG.
			CONCAT
				Split MTAG at ':' if present
				colon (:) 
				the current time in seconds */
		UPDATE #tmp_ins 
			SET CTAG=(SELECT CONCAT(LEFT(MTAG, ISNULL(NULLIF(CHARINDEX(':', MTAG) - 1, -1), LEN(MTAG))),':',CONVERT(INT,DATEDIFF(SECOND,'19700101',GETDATE()))))
		WHERE LEN(LTRIM(CTAG)) = 0 OR CTAG IS NULL;

		--Correct duplicate CTAGs
		IF EXISTS (SELECT 1 FROM #tmp_ins GROUP BY CTAG HAVING COUNT(CTAG) > 1)
		BEGIN-- common table expression to number duplicates
			;WITH CTE AS(
				SELECT MTAG, C2301, C2333,
					ROW_NUMBER() OVER (ORDER BY mt.conv_date_timestr_to_datetime(C2301,C2333) DESC) AS rn
					FROM #tmp_ins
					WHERE CTAG IN (SELECT CTAG FROM #tmp_ins GROUP BY CTAG HAVING COUNT(CTAG) > 1)
				)
			UPDATE ti	--join cte to tmp_ins and convert the number to letter(s) [A..ZZZ] good for up to 18278 duplicates (26 + 26^2 + 26^3) 
				SET ti.CTAG = ti.CTAG + 
						(SELECT COALESCE((SELECT CHAR(65 + (rn - 703) / 676 % 26) WHERE rn > = 703), '') +
						COALESCE((SELECT CHAR(65 + (rn - 27) / 26 % 26) WHERE rn > = 27), '') +
						(SELECT CHAR(65 + (rn - 1) % 26))) 
			FROM #tmp_ins ti JOIN CTE ON (CTE.MTAG = ti.MTAG AND CTE.C2301 = ti.C2301 AND CTE.C2333=ti.C2333);	
		END

		--Set tech1 time if null
		UPDATE #tmp_ins SET C2333 = CONVERT(NVARCHAR(8),GETDATE(),108) WHERE C2333 IS NULL;

		--Set tech2 time if null and if tech2 date is not null 
		UPDATE #tmp_ins SET C2334 = CONVERT(NVARCHAR(8),GETDATE(),108) WHERE C2303 IS NOT NULL AND C2334 IS NULL;

/* End of cal_tag_trig   */

/* Begin rel_field_trig */
		IF EXISTS (SELECT 1 FROM #tmp_ins WHERE COALESCE(combine,N'') <> N'CMB')
			UPDATE i SET i.C2362 = 
				CASE i.C2308
					WHEN 'AS-FOUND' THEN 
						CASE
							WHEN i.C2323 = 'Y' THEN 
								CASE 
									WHEN COALESCE(c.C2362,0) < 1 THEN 1
									ELSE COALESCE(c.C2362,1) + 1
								END
							ELSE 
								CASE
									WHEN COALESCE(c.C2362,0) > -1 THEN -1
									ELSE COALESCE(c.C2362,0) - 1
								END
						END
					WHEN 'FOUND-LEFT' THEN 
						CASE
							WHEN i.C2323 = 'Y' THEN 
								CASE 
									WHEN COALESCE(c.C2362,0) < 1 THEN 1
									ELSE COALESCE(c.C2362,0) + 1
								END
							ELSE 
								CASE
									WHEN COALESCE(c.C2362,0) > -1 THEN -1
									ELSE COALESCE(c.C2362,0) - 1
								END
						END
					ELSE COALESCE(c.C2362,1)	
				END	
			FROM #tmp_ins i JOIN mt.Calibration c ON (i.MTAG=c.MTAG)
			WHERE c.C2339 = 1 AND COALESCE(i.combine,N'') <> N'CMB'
/* End rel_field_trig */

/* Begin cal_i_2309 */
		UPDATE #tmp_ins SET C2310 = COALESCE(C2324, 0) + COALESCE(C2328, 0);
/* End cal_i_2309 */

	--Overwrite timestamps with server time
		UPDATE #tmp_ins SET 
			C2335 = CURRENT_TIMESTAMP,
			C2337 = CURRENT_TIMESTAMP

	--Set created by if empty
		UPDATE #tmp_ins SET 
			C2336 = COALESCE(C2336, SUBSTRING(SYSTEM_USER, CHARINDEX('\', SYSTEM_USER)+1, LEN(SYSTEM_USER)))
			WHERE C2336 IS NULL OR LEN(C2336) = 0;

	-- set modified by with created by if empty
		UPDATE #tmp_ins SET 
			C2338 = C2336
			WHERE C2338 IS NULL OR LEN(C2338) = 0;

		INSERT INTO mt.Calibration
			SELECT * FROM #tmp_ins

		COMMIT TRANSACTION;
		SET NOCOUNT OFF;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

