

-- Location

	UPDATE RDMDB..facility_master SET Demarcation = NULL WHERE Demarcation = '';	
	
	DECLARE	@MergeFacilityID INT
	DECLARE	@MergeFacilityName VARCHAR(100)	

	SELECT	@MergeFacilityID = MIN(ID), @MergeFacilityName = MAX(MergeFacilityName)
	FROM	RDMDB..facility_master;

	UPDATE RDMDB..RDM_Facility SET FacilityName = @MergeFacilityName WHERE FacilityID = @MergeFacilityID;


/* --------------------------  MIGRATE Maintain List Values	--------------------------------------------------- Start */
	
	-- FacilityZone
		
		UPDATE RDMDB..RDM_FacilityZone SET ZoneName = LTRIM(RTRIM(ZoneName));

		DECLARE @FacilityZone TABLE ( FacilityZoneID INT, New_FacilityZoneID INT )

		INSERT INTO @FacilityZone
		SELECT	KS.FacilityZoneID, DP.FacilityZoneID AS New_FacilityZoneID
		FROM	RDMDB..RDM_FacilityZone	KS
		JOIN	( SELECT MIN(FacilityZoneID) FacilityZoneID, ZoneName FROM RDMDB..RDM_FacilityZone GROUP BY ZoneName HAVING COUNT(*) > 1 )	DP ON KS.ZoneName = DP.ZoneName
		
		UPDATE	KM
		SET		KM.FacilityZoneID = KS.New_FacilityZoneID
		FROM	RDMDB..RDM_Building	KM
		JOIN	@FacilityZone	KS	ON KM.FacilityZoneID = KS.FacilityZoneID

		DELETE	KS
		FROM	RDMDB..RDM_FacilityZone	KS
		JOIN	@FacilityZone			TS	ON KS.FacilityZoneID = TS.FacilityZoneID
		WHERE	TS.FacilityZoneID <> TS.New_FacilityZoneID;
		
		UPDATE RDMDB..RDM_FacilityZone SET FacilityID = @MergeFacilityID;

	-- DoorType
		
		UPDATE RDMDB..[RDM_DoorType] SET [Type] = LTRIM(RTRIM([Type]));

		DECLARE @DoorType TABLE ( DoorTypeID INT, New_DoorTypeID INT )

		INSERT INTO @DoorType
		SELECT	KS.DoorTypeID, DP.DoorTypeID AS New_DoorTypeID
		FROM	RDMDB..[RDM_DoorType]	KS
		JOIN	( SELECT MIN(DoorTypeID) DoorTypeID, [Type] FROM RDMDB..[RDM_DoorType] GROUP BY [Type] HAVING COUNT(*) > 1 )	DP ON KS.[Type] = DP.[Type]
		
		UPDATE	KM
		SET		KM.DoorTypeID = KS.New_DoorTypeID
		FROM	RDMDB..RDM_Door	KM
		JOIN	@DoorType		KS	ON KM.DoorTypeID = KS.DoorTypeID

		DELETE	KS
		FROM	RDMDB..[RDM_DoorType]	KS
		JOIN	@DoorType				TS	ON KS.DoorTypeID = TS.DoorTypeID
		WHERE	TS.DoorTypeID <> TS.New_DoorTypeID;
		
		UPDATE RDMDB..[RDM_DoorType] SET FacilityID = @MergeFacilityID;		

	-- EmployeeType
		
		UPDATE RDMDB..[RDM_EmpType] SET [Type] = LTRIM(RTRIM([Type]));

		DECLARE @EmpType TABLE ( EmpTypeID INT, New_EmpTypeID INT )

		INSERT INTO @EmpType
		SELECT	KS.EmpTypeID, DP.EmpTypeID AS New_EmpTypeID
		FROM	RDMDB..[RDM_EmpType]	KS
		JOIN	( SELECT MIN(EmpTypeID) EmpTypeID, [Type] FROM RDMDB..[RDM_EmpType] GROUP BY [Type] HAVING COUNT(*) > 1 )	DP ON KS.[Type] = DP.[Type]
		
		UPDATE	KM
		SET		KM.EmpTypeID = KS.New_EmpTypeID
		FROM	RDMDB..RDM_Employee	KM
		JOIN	@EmpType			KS	ON KM.EmpTypeID = KS.EmpTypeID

		DELETE	KS
		FROM	RDMDB..[RDM_EmpType]	KS
		JOIN	@EmpType				TS	ON KS.EmpTypeID = TS.EmpTypeID
		WHERE	TS.EmpTypeID <> TS.New_EmpTypeID;
		
		UPDATE RDMDB..[RDM_EmpType] SET FacilityID = @MergeFacilityID;

	-- EmployeeTitle
		
		UPDATE RDMDB..[RDM_EmpTitle] SET Title = LTRIM(RTRIM(Title));

		DECLARE @EmpTitle TABLE ( TitleID INT, New_TitleID INT )

		INSERT INTO @EmpTitle
		SELECT	KS.EmpTitleID, DP.TitleID AS New_TitleID
		FROM	RDMDB..[RDM_EmpTitle]	KS
		JOIN	( SELECT MIN(EmpTitleID) TitleID, Title FROM RDMDB..[RDM_EmpTitle] GROUP BY Title HAVING COUNT(*) > 1 )	DP ON KS.Title = DP.Title
		
		UPDATE	KM
		SET		KM.EmpTitleID = KS.New_TitleID
		FROM	RDMDB..RDM_Employee	KM
		JOIN	@EmpTitle			KS	ON KM.EmpTitleID = KS.TitleID

		DELETE	KS
		FROM	RDMDB..[RDM_EmpTitle]	KS
		JOIN	@EmpTitle				TS	ON KS.EmpTitleID = TS.TitleID
		WHERE	TS.TitleID <> TS.New_TitleID;
		
		UPDATE RDMDB..[RDM_EmpTitle] SET FacilityID = @MergeFacilityID;

	-- Keyways
		
		UPDATE RDMDB..[RDM_Keyway] SET KeywayName = LTRIM(RTRIM(KeywayName));

		DECLARE @Keyway TABLE ( KeywayID INT, New_KeywayID INT )

		INSERT INTO @Keyway
		SELECT	KS.KeywayID, DP.KeywayID AS New_KeywayID
		FROM	RDMDB..[RDM_Keyway]	KS
		JOIN	( SELECT MIN(KeywayID) KeywayID, KeywayName FROM RDMDB..[RDM_Keyway] GROUP BY KeywayName HAVING COUNT(*) > 1 )	DP ON KS.KeywayName = DP.KeywayName
		
		UPDATE	KM
		SET		KM.KeywayID = KS.New_KeywayID
		FROM	RDMDB..RDM_MasterKeySystem	KM
		JOIN	@Keyway						KS	ON KM.KeywayID = KS.KeywayID

		DELETE	KS
		FROM	RDMDB..[RDM_Keyway]	KS
		JOIN	@Keyway				 TS	ON KS.KeywayID = TS.KeywayID
		WHERE	TS.KeywayID <> TS.New_KeywayID;
		
		UPDATE RDMDB..[RDM_Keyway] SET FacilityID = @MergeFacilityID;

	-- KeymarkStatus
		
		UPDATE RDMDB..[RDM_KeyMarkStatus] SET [Status] = LTRIM(RTRIM([Status]));

		DECLARE @KMS TABLE ( KeymarkStatusID INT, New_KeymarkStatusID INT )

		INSERT INTO @KMS
		SELECT	KS.KeymarkStatusID, DP.KeymarkStatusID AS New_KeymarkStatusID
		FROM	RDMDB..[RDM_KeyMarkStatus]	KS
		JOIN	( SELECT MIN(KeymarkStatusID) KeymarkStatusID, [Status] FROM RDMDB..[RDM_KeyMarkStatus] GROUP BY [Status] HAVING COUNT(*) > 1 )	DP ON KS.[Status] = DP.[Status]
		
		UPDATE	KM
		SET		KM.KeymarkStatusID = KS.New_KeymarkStatusID
		FROM	RDMDB..RDM_KeyMark	KM
		JOIN	@KMS				KS	ON KM.KeyMarkStatusID = KS.KeymarkStatusID

		DELETE	KS
		FROM	RDMDB..[RDM_KeyMarkStatus]	KS
		JOIN	@KMS						TS	ON KS.KeyMarkStatusID = TS.KeymarkStatusID
		WHERE	TS.KeymarkStatusID <> TS.New_KeymarkStatusID;
		
		UPDATE RDMDB..[RDM_KeyMarkStatus] SET FacilityID = @MergeFacilityID;

	-- ProductMfg
/* --------------------------  MIGRATE Maintain List Values	--------------------------------------------------- End */

	-- Building

		UPDATE RDMDB..[RDM_Building] SET FacilityID = @MergeFacilityID;

	--Department
		
		UPDATE RDMDB..[RDM_Department] SET FacilityID = @MergeFacilityID;

	--Cabinet

		UPDATE RDMDB..[RDM_Cabinet] SET FacilityID = @MergeFacilityID;
	
	--Hook		

	--Door

		UPDATE RDMDB..[RDM_Door] SET FacilityID = @MergeFacilityID;

	--Employee

		UPDATE RDMDB..[RDM_Employee] SET FacilityID = @MergeFacilityID;

	-- Product		

	-- Parts
	print 'parts..';

		UPDATE KE
		SET	KE.SerialNo = STUFF(KE.SerialNo,FM.separator,LEN(ME.ProductName),'')
		FROM	RDMDB..RDM_Part	KE
		JOIN	RDMDB..facility_master	FM ON KE.FacilityID = FM.ID
		JOIN	(
					SELECT	KY.PartID,KM.ProductName
					FROM	RDMDB..RDM_Part			KY
					JOIN	RDMDB..RDM_Product		KM ON KY.ProductID = KM.ProductID
					JOIN	RDMDB..facility_master	FI ON KY.FacilityID = FI.ID
					WHERE	FI.Demarcation IS NOT NULL
				) ME ON KE.PartID = ME.PartID
		WHERE SUBSTRING(KE.SerialNo,FM.separator,LEN(ME.ProductName)) = ME.ProductName
		AND		FM.Demarcation IS NOT NULL;

		UPDATE KE
		SET KE.SerialNo = STUFF(LTRIM(KE.SerialNo),FM.separator,1,'')
		FROM	RDMDB..RDM_Part			KE
		JOIN	RDMDB..facility_master	FM ON KE.FacilityID = FM.ID
		WHERE SUBSTRING(KE.SerialNo,FM.separator,1) = '-'
		AND		FM.Demarcation IS NOT NULL;
		
		UPDATE KY
		SET		KY.SerialNo = CASE KY.SerialNo WHEN FM.Demarcation THEN '' ELSE KY.SerialNo END
		FROM	RDMDB..RDM_Part			KY
		JOIN	RDMDB..facility_master	FM ON KY.FacilityID = FM.ID;

		UPDATE RDMDB..RDM_Part SET FacilityID = @MergeFacilityID;

	-- MasterKeySystem
print 'mks..';

		UPDATE RDMDB..RDM_MasterKeySystem SET FacilityID = @MergeFacilityID;

	-- Keymarks
print 'keymark..';

		UPDATE RDMDB..RDM_KeyMark SET FacilityID = @MergeFacilityID;

	-- Set GM key, Control key for MasterKey system
		
	-- Coremarks
	print 'coremark..';

		UPDATE RDMDB..RDM_CoreMark SET FacilityID = @MergeFacilityID;

	-- Control_Sel		

	-- Keyrings
	print 'keyring..';

		UPDATE RDMDB..RDM_KeyRings SET FacilityID = @MergeFacilityID;

	-- Key
	print 'key..';

		UPDATE KE
		SET	KE.KeySerialNo = STUFF(KE.KeySerialNo,FM.separator,LEN(ME.KeyMarkName),'')
		FROM	RDMDB..RDM_Key			KE
		JOIN	RDMDB..facility_master	FM ON KE.FacilityID = FM.ID
		JOIN	(
					SELECT	KY.KeyID,CASE WHEN FI.Prefix = 1 THEN STUFF(KM.KeyMarkName,1,2,'') ELSE SUBSTRING(KM.KeyMarkName,1,LEN(KM.KeyMarkName)-2) END KeyMarkName
					FROM	RDMDB..RDM_Key			KY
					JOIN	RDMDB..RDM_KeyMark		KM ON KY.KeyMarkID = KM.KeyMarkID
					JOIN	RDMDB..facility_master	FI ON KY.FacilityID = FI.ID
					WHERE	FI.Demarcation IS NOT NULL
				) ME ON KE.KeyID = ME.KeyID		
		WHERE SUBSTRING(KE.KeySerialNo,FM.separator,LEN(ME.KeyMarkName)) = ME.KeyMarkName
		AND		FM.Demarcation IS NOT NULL;
		
		UPDATE	KE
		SET		KE.KeySerialNo = STUFF(KE.KeySerialNo,FM.separator,1,'')
		FROM	RDMDB..RDM_Key			KE
		JOIN	RDMDB..facility_master	FM ON KE.FacilityID = FM.ID
		WHERE	SUBSTRING(KE.KeySerialNo,FM.separator,1) = '-'
		AND		FM.Demarcation IS NOT NULL;

		UPDATE KY
		SET		KY.KeySerialNo = CASE KY.KeySerialNo WHEN FM.Demarcation THEN '' ELSE KY.KeySerialNo END
		FROM	RDMDB..RDM_Key			KY
		JOIN	RDMDB..facility_master	FM ON KY.FacilityID = FM.ID;

		UPDATE RDMDB..[RDM_Key] SET FacilityID = @MergeFacilityID;

	-- Core 
print 'core..';

		UPDATE KE
		SET	KE.CoreSerialNo = STUFF(KE.CoreSerialNo,FM.separator,LEN(ME.CoreMarkName),'')
		FROM	RDMDB..RDM_Core			KE
		JOIN	RDMDB..facility_master	FM ON KE.RDM_FacilityID = FM.ID
		JOIN	(
					SELECT	KY.CoreID,CASE WHEN FI.Prefix = 1 THEN STUFF(KM.CoreMarkName,1,2,'') ELSE SUBSTRING(KM.CoreMarkName,1,LEN(KM.CoreMarkName)-2) END CoreMarkName
					FROM	RDMDB..RDM_Core			KY
					JOIN	RDMDB..RDM_CoreMark		KM ON KY.CoreMarkID = KM.CoreMarkID
					JOIN	RDMDB..facility_master	FI ON KY.RDM_FacilityID = FI.ID
					WHERE	FI.Demarcation IS NOT NULL
				) ME ON KE.CoreID = ME.CoreID
		WHERE SUBSTRING(KE.CoreSerialNo,FM.separator,LEN(ME.CoreMarkName)) = ME.CoreMarkName
		AND		FM.Demarcation IS NOT NULL;

		UPDATE KE
		SET		KE.CoreSerialNo = STUFF(KE.CoreSerialNo,FM.separator,1,'')
		FROM	RDMDB..RDM_Core			KE
		JOIN	RDMDB..facility_master	FM ON KE.RDM_FacilityID = FM.ID
		WHERE	SUBSTRING(KE.CoreSerialNo,FM.separator,1) = '-'
		AND		FM.Demarcation IS NOT NULL;

		UPDATE KY
		SET		KY.CoreSerialNo = CASE KY.CoreSerialNo WHEN FM.Demarcation THEN '' ELSE KY.CoreSerialNo END
		FROM	RDMDB..RDM_Core			KY
		JOIN	RDMDB..facility_master	FM ON KY.RDM_FacilityID = FM.ID;

		UPDATE RDMDB..RDM_Core SET FacilityID = @MergeFacilityID, RDM_FacilityID = @MergeFacilityID;

	-- Users	

	-- User Permissions	

	-- User Facilities
print 'facility..';

		DELETE FROM RDMDB..RDM_UserFacilities WHERE FacilityID <> @MergeFacilityID;

	-- Dashboard Settings	

	-- ItemHistory ( change Identity value)
print 'history..';

		UPDATE RDMDB..RDM_ItemHistory SET FacilityID = @MergeFacilityID;
		
	-- For calculating the stacking for all migrated selective coremarks.
			
	-- For Create Coremarks for Migrated Data - Issue#140

	-- Separating the actual serial number from the key	
	
	DELETE FROM RDMDB..RDM_Facility WHERE FacilityID <> @MergeFacilityID;
