// ***************************************
// Title: Procedures - Set Duplicate Templates to Inactive
// Author: Michael Johnston and Ed Wadagnolo, Fluke Calibration
// Date: 2019-01-02
// 
// When saving a procedure record with a manual template,
// this data check sets all but the newest template of the  
// same name to inactive. This is related to the change inactive
// Manual Template editing in MET/TEAM 2.2.1.
// ***************************************

/*
SQL Function 1: UpdateOldTemplates

DECLARE @cFileName AS NVARCHAR(150)
DECLARE @Desired_nFileUID AS UNIQUEIDENTIFIER

-- Get our File (& ManualTemplate) name....
SELECT @cFileName = f.cFileName FROM dbo.Procedures p
INNER JOIN dbo.Files f ON p.nAttTemplateUID = f.nFileUID
WHERE p.nProcedureUID = strUID

-- Get nFileUID of the newest active, not delted File record with our name.
SET @Desired_nFileUID = (	SELECT TOP 1 nFileUID 
							FROM Files 
							WHERE cFileName = @cFileName 
									AND lDeleted = 0 
									AND lActive <> 0 
									AND nManualTemplateUID IS NOT NULL 
							ORDER BY tAddTime DESC )

-- Inactivate all but the newest ManualTemplate with matching @cFileName
UPDATE dbo.ManualTemplates 
SET lActive = 0
	, nUpdateUserUID = strUserUID
	, nUpdateFacilityUID = strActiveFacilityUID 
	, tUpdateTime = GETUTCDATE()
	, tMobileTime = GETUTCDATE()
WHERE	lActive <> 0 
		AND lDeleted = 0
		AND nManualTemplateUID IN (SELECT DISTINCT nManualTemplateUID 
								FROM Files 
								WHERE cFileName = @cFileName 
									AND lDeleted = 0 
									AND lActive <> 0 
									AND nManualTemplateUID IS NOT NULL 
									AND nFileUID <> @Desired_nFileUID )

-- Inactivate all but the newest Files for manual templates with matching @cFileName
UPDATE dbo.Files 
SET lActive = 0
	, nUpdateUserUID = strUserUID
	, nUpdateFacilityUID = strActiveFacilityUID 
	, tUpdateTime = GETUTCDATE()
	, tMobileTime = GETUTCDATE()
WHERE cFileName = @cFileName 
	AND lDeleted = 0 
	AND lActive <> 0 
	AND nManualTemplateUID IS NOT NULL 
	AND nFileUID <> @Desired_nFileUID
*/


	
DataCheck.ExecuteSQL('UpdateOldTemplates', function() {});
pass();