USE [METX]
GO
/****** Object:  Trigger [mt].[inv_u_instead]    Script Date: 9/24/2024 11:20:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[inv_u_instead]
	ON [mt].[Inventory]
	INSTEAD OF UPDATE
/******************************************************************************************************************************************************/
/***	TSB	- 05/27/2016	No original conversion date, but modifications start here.																***/   
/***	Added transactions, uncommented the THROW statement in the catch block, changed all varchar to nvarchar, use integers for int compar		***/
/***	JCF - 06/12/2016	Rewrote triggers as data set processes.  Moved inv_u_abeyance to the end so the inventory table is modified before   	***/
/***						any other tables are changed because of triggers.																		***/
/***	JCF - 10/20/2016	Fixed @i <= @cnt to @i < @cnt																							***/
/***	JCF - 02/08/2017	Corrected the removal of domain from SYSTEM_USER for modified by field													***/
/***	JCF	- 03/03/2017	Modified by is now overwritten by server time																			***/
/***	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;
		
		DECLARE @msg NVARCHAR(50);
		DECLARE @duedate DATE;
		DECLARE @duetime NVARCHAR(8);
		DECLARE @rtn INT;
		DECLARE @cal_date DATE;
		DECLARE @cal_time NVARCHAR(8);
		DECLARE @int INT;
		DECLARE @type NVARCHAR(1);
		DECLARE @pass_fail NVARCHAR(1);
		DECLARE @i INT = 0;
		DECLARE @cnt INT;
		
		SELECT * INTO #tmp FROM inserted;
/* Start of inv_row_update */
		--JCF: If one of these columns was updated I4238 (when modified) & I4239 (who modified) need to be updated
		SELECT i.MTAG as ins_mtag INTO #wat
			FROM inserted i join deleted d on (i.MTAG = d.MTAG)
			WHERE i.I4201 <> d.I4201 OR i.I4202 <> d.I4202 OR i.I4203 <> d.I4203 OR i.I4204 <> d.I4204 OR i.I4205 <> d.I4205 
				OR i.I4206 <> d.I4206 OR i.I4207 <> d.I4207 OR i.I4208 <> d.I4208 OR i.I4209 <> d.I4209 OR i.I4210 <> d.I4210 
				OR i.I4211 <> d.I4211 OR i.I4212 <> d.I4212 OR i.I4213 <> d.I4213 OR i.I4214 <> d.I4214 OR i.I4215 <> d.I4215 
				OR i.I4216 <> d.I4216 OR i.I4217 <> d.I4217 OR i.I4218 <> d.I4218 OR i.I4219 <> d.I4219 OR i.I4220 <> d.I4220 
				OR i.I4221 <> d.I4221 OR i.I4222 <> d.I4222 OR i.I4223 <> d.I4223 OR i.I4224 <> d.I4224 OR i.I4225 <> d.I4225 
				OR i.I4226 <> d.I4226 OR i.I4228 <> d.I4228 OR i.I4229 <> d.I4229 OR i.I4230 <> d.I4230 OR i.I4231 <> d.I4231 
				OR i.I4232 <> d.I4232 OR i.I4233 <> d.I4233 OR i.I4234 <> d.I4234 OR i.I4235 <> d.I4235 OR i.I4240 <> d.I4240 
				OR i.I4299 <> d.I4299 OR i.I4241 <> d.I4241 OR i.I4242 <> d.I4242 OR i.I4243 <> d.I4243 OR i.I4258 <> d.I4258 
				OR i.I4244 <> d.I4244 OR i.I4245 <> d.I4245 OR i.I4246 <> d.I4246 OR i.I4247 <> d.I4247 OR i.I4248 <> d.I4248 
				OR i.I4249 <> d.I4249 OR i.I4250 <> d.I4250 OR i.I4251 <> d.I4251 OR i.I4252 <> d.I4252 OR i.I4253 <> d.I4253 
				OR i.I4254 <> d.I4254 OR i.I4255 <> d.I4255 OR i.I4256 <> d.I4256 OR i.I4257 <> d.I4257 OR i.I4259 <> d.I4259 
				OR i.I4260 <> d.I4260 OR i.I4261 <> d.I4261 OR i.I4262 <> d.I4262 OR i.I4263 <> d.I4263 OR i.idsrc <> d.idsrc OR i.ktag <> d.ktag
				OR d.I42DT2_1 <> i.I42DT2_1 OR d.I42DT2_2 <> i.I42DT2_2 OR d.I42DATE_1 <> i.I42DATE_1 OR d.I42DATE_2 <> i.I42DATE_2
				OR d.I42TIME_1 <> i.I42TIME_1 OR d.I42TIME_2 <> i.I42TIME_2 OR d.I42FLT_1 <> i.I42FLT_1 OR d.I42FLT_2 <> i.I42FLT_2
				OR d.I42INT_1 <> i.I42INT_1 OR d.I42INT_2 <> i.I42INT_2 OR d.I42NVAR_1 <> i.I42NVAR_1 OR d.I42NVAR_2 <> i.I42NVAR_2
				OR d.I42NVAR_3 <> i.I42NVAR_3 OR d.I42NVAR_4 <> i.I42NVAR_4 OR d.I42TEXT_1 <> i.I42TEXT_1;


		--JCF: Set I4238 & I4239 if null or default
		UPDATE #tmp SET I4238 = CURRENT_TIMESTAMP 
			WHERE MTAG IN (SELECT ins_mtag FROM #wat);

			--SET @msg = (SELECT I4238 from #tmp);
							--THROW 500001, @msg, 1; 

		UPDATE #tmp SET I4239 = SUBSTRING(SYSTEM_USER, CHARINDEX('\', SYSTEM_USER)+1, LEN(SYSTEM_USER)) 
			WHERE MTAG IN (SELECT ins_mtag FROM #wat) AND (I4239 IS NULL OR LEN(LTRIM(I4239)) = 0);
/* End of inv_row_update */

/* Use an update statement to move the changed data to the target table for all rows updated.
** There is an after update trigger, so bring along mtag and I4201. 
*/
			UPDATE I
			   SET I.I4201=t.I4201, 
				I.I4202=t.I4202, 
				I.I4203=t.I4203, 
				I.I4204=t.I4204, 
				I.I4205=t.I4205, 
				I.I4206=t.I4206, 
				I.I4207=t.I4207, 
				I.I4208=t.I4208, 
				I.I4209=t.I4209, 
				I.I4210=t.I4210, 
				I.I4211=t.I4211, 
				I.I4212=t.I4212, 
				I.I4213=t.I4213, 
				I.I4214=t.I4214, 
				I.I4215=t.I4215, 
				I.I4216=t.I4216, 
				I.I4217=t.I4217, 
				I.I4218=t.I4218, 
				I.I4219=t.I4219, 
				I.I4220=t.I4220, 
				I.I4221=t.I4221, 
				I.I4222=t.I4222, 
				I.I4223=t.I4223, 
				I.I4224=t.I4224, 
				I.I4225=t.I4225, 
				I.I4226=t.I4226, 
				I.I4228=t.I4228, 
				I.I4229=t.I4229, 
				I.I4230=t.I4230, 
				I.I4231=t.I4231, 
				I.I4232=t.I4232, 
				I.I4233=t.I4233, 
				I.I4234=t.I4234, 
				I.I4235=t.I4235, 
				I.I4236=t.I4236, 
				I.I4237=t.I4237, 
				I.I4238=t.I4238, 
				I.I4239=t.I4239, 
				I.I4240=t.I4240, 
				I.I4299=t.I4299, 
				I.MTAG=t.MTAG, 
				I.I4241=t.I4241, 
				I.I4242=t.I4242, 
				I.I4243=t.I4243, 
				I.idsrc=t.idsrc, 
				I.IPUBLISH=t.IPUBLISH, 
				I.ktag=t.ktag, 
				I.replic=t.replic, 
				I.I4244=t.I4244, 
				I.I4245=t.I4245, 
				I.I4246=t.I4246, 
				I.I4247=t.I4247, 
				I.I4248=t.I4248, 
				I.I4249=t.I4249, 
				I.I4250=t.I4250, 
				I.I4251=t.I4251, 
				I.I4252=t.I4252, 
				I.I4253=t.I4253, 
				I.I4254=t.I4254, 
				I.I4255=t.I4255, 
				I.I4256=t.I4256, 
				I.I4257=t.I4257, 
				I.I4258=t.I4258, 
				I.I4259=t.I4259, 
				I.I4260=t.I4260, 
				I.I4261=t.I4261, 
				I.I4262=t.I4262,
				I.I4263=t.I4263,
				I.I42DT2_1=t.I42DT2_1,
				I.I42DT2_2=t.I42DT2_2,
				I.I42DATE_1=t.I42DATE_1,
				I.I42DATE_2=t.I42DATE_2,
				I.I42TIME_1=t.I42TIME_1,
				I.I42TIME_2=t.I42TIME_2,
				I.I42FLT_1=t.I42FLT_1,
				I.I42FLT_2=t.I42FLT_2,
				I.I42INT_1=t.I42INT_1,
				I.I42INT_2=t.I42INT_2,
				I.I42NVAR_1=t.I42NVAR_1,
				I.I42NVAR_2=t.I42NVAR_2,
				I.I42NVAR_3=t.I42NVAR_3,
				I.I42NVAR_4=t.I42NVAR_4,
				I.I42TEXT_1=t.I42TEXT_1
			   FROM [mt].[Inventory] AS I
			   JOIN  #tmp AS t 
			   ON I.MTAG=t.MTAG;

/* Start of inv_u_abeyance 
** update cal record due date if abeyence was turned off 
*/
		IF EXISTS (SELECT 1 FROM inserted i JOIN deleted d ON (i.MTAG = d.MTAG) WHERE i.I4258 = 'N' AND d.I4258 = 'Y')
		BEGIN
			SELECT ID = IDENTITY(INT, 0, 1), i.I4201 as geetsid, c.CTAG, c.C2302 as interval, c.C2303 as duedate, c.c2334 as duetime, c.C2323 as passfail, c.C2331 as inttype, c.C2339 as isactive into #abey
				FROM inserted i JOIN deleted d ON (i.MTAG = d.MTAG)
				JOIN (SELECT cal.* 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 i.MTAG FROM inserted i JOIN deleted d ON (i.MTAG = d.MTAG) WHERE i.I4258 = 'N' AND d.I4258 = 'Y') group by MTAG) tmp 
					ON (cal.MTAG=tmp.MTAG and cal.C2301 = tmp.C2301 and cal.C2333= CONVERT(NVARCHAR(8),tmp.C2333,108))) c ON (i.MTAG = c.MTAG)
				WHERE c.C2380 IS NULL			
				SELECT * FROM #abey;
			--Raise error if any equipment doesn't have an active cal record
			IF EXISTS (SELECT 1 FROM #abey a WHERE a.isactive <> 1)
			BEGIN
				SET @msg = CHAR(10)+CHAR(10) 
				+ 'No active cal record found for ' 
				+ (SELECT (STUFF((SELECT CAST(', ' + a.geetsid  AS VARCHAR(MAX)) 
					 FROM #abey a WHERE a.isactive <> 1 
					 FOR XML PATH ('')), 1, 2, '')) AS GEETSID)
				+ CHAR(10)+CHAR(10) ;
				RAISERROR (@msg, 11,1); 
			END

			
			SELECT @cnt = COUNT(*), @cal_date = GETDATE(), @cal_time = CONVERT(NVARCHAR(8),GETDATE(),108) FROM #abey;
			WHILE (@i < @cnt)
			BEGIN
				SELECT @pass_fail = passfail, @type = inttype, @int = interval FROM #abey WHERE ID = @i;
				
				EXECUTE [mt].[mt_calc_due_date] 
				   @cal_date
				  ,@cal_time
				  ,@pass_fail
				  ,@type
				  ,@int
				  ,@duedate OUTPUT
				  ,@duetime OUTPUT
				  ,@rtn OUTPUT

				UPDATE #abey SET duedate = @duedate, duetime = @duetime WHERE ID = @i;
				SET @i = @i + 1;
			END

			UPDATE c SET C2380 = @cal_date, C2381 = @cal_time, C2382 = N'N', C2303 = duedate, C2334 = duetime 
				FROM mt.Calibration c JOIN #abey a ON (c.CTAG = a.CTAG) 

		END
		/*This is set based handling of abeyance without calling mt_calc_due_date.
			--get latest cal records where abeyance was turned on
			SELECT ID = IDENTITY(INT, 0, 1), i.I4201 as geetsid, c.CTAG, c.C2302 as interval, c.C2303 as duedate, c.C2323 as passfail, c.C2331 as inttype, c.C2339 as isactive, c.C2380 as abeydate into #abey
				FROM inserted i JOIN deleted d ON (i.MTAG = d.MTAG)
				JOIN (SELECT cal.* 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 i.MTAG FROM inserted i JOIN deleted d ON (i.MTAG = d.MTAG) WHERE i.I4258 = 'Y' AND d.I4258 = 'N') group by MTAG) tmp 
					ON (cal.MTAG=tmp.MTAG and cal.C2301 = tmp.C2301 and cal.C2333= CONVERT(NVARCHAR(8),tmp.C2333,108))) c ON (i.MTAG = c.MTAG)
				WHERE c.C2380 IS NULL;
			

			--Raise error if any equipment doesn't have an active cal record
			IF EXISTS (SELECT 1 FROM #abey a WHERE a.isactive <> 1)
			BEGIN
				SET @msg = CHAR(10)+CHAR(10) 
				+ 'Inactive cal record found for ' 
				+ (SELECT (STUFF((SELECT CAST(', ' + a.geetsid  AS VARCHAR(MAX)) 
					 FROM #abey a WHERE a.isactive <> 1 
					 FOR XML PATH ('')), 1, 2, '')) AS GEETSID)
				+ CHAR(10)+CHAR(10) ;
				RAISERROR (@msg, 11,1); 
			END

			--Raise error if interval type is N but interval is greater than 0
			IF EXISTS (SELECT 1 FROM #abey a WHERE a.inttype = 'N' AND a.interval > 0)
			BEGIN
				SET @msg = CHAR(10)+CHAR(10) 
					+ 'Cal interval greater than zero while type is N on ' 
					+ (SELECT (STUFF((SELECT CAST(', ' + a.geetsid AS VARCHAR(MAX)) 
						FROM #abey a WHERE a.inttype = 'N' AND a.interval > 0 
						FOR XML PATH ('')), 1, 2, '')) AS GEETSID)
					+ CHAR(10)+CHAR(10);
				RAISERROR (@msg, 11,1);
			END	

			--Raise error if interval type is invalid
			IF EXISTS (SELECT 1 FROM #abey a WHERE a.inttype NOT IN ('d','w','m','y','u'))
			BEGIN
				SET @msg = CHAR(10)+CHAR(10) 
					+ 'Invalid Cal interval type for ' 
					+ (SELECT (STUFF((SELECT CAST(', ' + a.geetsid AS VARCHAR(MAX)) 
						FROM #abey a WHERE a.inttype NOT IN ('d','w','m','y','u') 
						FOR XML PATH ('')), 1, 2, '')) AS GEETSID)
					+ CHAR(10)+CHAR(10);
				RAISERROR (@msg, 11,1);
			END	
			
			--SET due date to today if interval is 0 or passfail not Y
			UPDATE #abey SET duedate = GETDATE() WHERE interval = 0 OR passfail <> 'Y';

			--SET due date per type and interval
			UPDATE #abey SET duedate = CASE inttype
											WHEN 'Y' THEN DATEADD(YEAR, interval,GETDATE())
											WHEN 'M' THEN DATEADD(MONTH, interval, GETDATE())
											WHEN 'W' THEN DATEADD(WEEK, interval, GETDATE())
											WHEN 'D' THEN DATEADD(DAY, interval, GETDATE())
											ELSE GETDATE()
										END
									WHERE interval > 0 AND passfail = 'Y';
			
			--Update valid due dates for non-workdays & holidays
			SELECT @cnt = COUNT(*) FROM #abey;
			WHILE (@i <= @cnt)
			BEGIN
				WHILE EXISTS (SELECT 1 FROM mt.nonworkdays WHERE [dayofweek] = DATENAME(weekday,(SELECT duedate FROM #abey WHERE interval > 0 AND passfail = 'Y' AND ID=@i)))
				BEGIN
					UPDATE #abey SET duedate = DATEADD(DAY, -1, duedate) WHERE interval > 0 AND passfail = 'Y' AND ID=@i; 
				END 

				WHILE EXISTS (SELECT 1 FROM mt.DTI_holidays WHERE holiday = (SELECT duedate FROM #abey WHERE interval > 0 AND passfail = 'Y' AND ID=@i))
				BEGIN
					UPDATE #abey SET duedate = DATEADD(DAY, -1, duedate) WHERE interval > 0 AND passfail = 'Y' AND ID=@i; 
				END 
				SET @i = @i + 1;
			END
			SELECT * FROM #abey;
			--Update calibration table with the new due dates, due times, and abeyance date & time


			UPDATE c SET C2303 = duedate, C2334 = CONVERT(NVARCHAR(8),GETDATE(),108), C2380 = GETDATE(),  C2381 = CONVERT(NVARCHAR(8),GETDATE(),108)
				FROM mt.Calibration c JOIN #abey a ON (c.CTAG = a.CTAG); 
		END
		*/
/* End of inv_u_abeyance */

		COMMIT TRANSACTION;
		SET NOCOUNT OFF;
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;	

		THROW;
	END CATCH
END

