---------ENUM Values
--  KeyMark Level

--SET IDENTITY_INSERT [dbo].RDM_KeymarkLevel ON;
--INSERT INTO [dbo].RDM_KeymarkLevel (KeyMarkLevelID, KeyMarkLevel)
--SELECT L.KeyMarkLevelID,REPLACE(L.KeyMarkLevel,CHAR(9),'') FROM
--(
--SELECT RTRIM(LTRIM('CONTROL')) KeyMarkLevel,	0 KeyMarkLevelID
--UNION SELECT RTRIM(RTRIM('GGGGGM	')),1
--UNION SELECT RTRIM(RTRIM('GGGGM	')),2
--UNION SELECT RTRIM(RTRIM('GGGM	')),3
--UNION SELECT RTRIM(RTRIM('GGM	')),	4
--UNION SELECT RTRIM(RTRIM('GM		')),5
--UNION SELECT RTRIM(RTRIM('M		')),6
--UNION SELECT RTRIM(RTRIM('SM		')),7
--UNION SELECT RTRIM(RTRIM('SSM	')),	8
--UNION SELECT RTRIM(RTRIM('SSSM	')),9
--UNION SELECT RTRIM(RTRIM('SSSSM	')),10
--UNION SELECT RTRIM(RTRIM('SSSSSM	')),11
--UNION SELECT RTRIM(RTRIM('OP		')),12
--UNION SELECT RTRIM(RTRIM('SEL	')),13
--)L
--SET IDENTITY_INSERT [dbo].RDM_KeymarkLevel OFF;

----  Mark

--SET IDENTITY_INSERT [dbo].RDM_Mark ON;
--INSERT INTO [dbo].RDM_Mark (MarkID, Mark)
--VALUES (0,''),(1,'S'),(2,'F')
--SET IDENTITY_INSERT [dbo].RDM_Mark OFF;

----  PinSize

--SET IDENTITY_INSERT [dbo].RDM_PinSize ON;
--INSERT INTO [dbo].RDM_PinSize (PinSizeID, PinSize)
--VALUES (0,''),(1,'0'),(2,'5'),(3,'6'),(4,'7')
--SET IDENTITY_INSERT [dbo].RDM_PinSize OFF;

----  System Type

--SET IDENTITY_INSERT [dbo].RDM_SystemType ON;
--INSERT INTO [dbo].RDM_SystemType (SystemTypeID, SystemType)
--VALUES (0,''),(1,'A2'),(2,'A3'),(3,'A4'),(4,'OTHER')
--SET IDENTITY_INSERT [dbo].RDM_SystemType OFF;

----  Product Type

--SET IDENTITY_INSERT [dbo].RDM_ProductType ON;
--INSERT INTO [dbo].RDM_ProductType (ProductTypeID, ProductType)
--VALUES (0,''),(1,'CORE'),(2,'KEY'),(3,'PART'),(4,'OTHER')
--SET IDENTITY_INSERT [dbo].RDM_ProductType OFF;

------------------

--INSERT INTO dbo.Facility
--        ( FacilityName ,
--          Description
--        )
--VALUES  ( 'Demo Location',
--          'Demo Location created for Customer'         
--        )

--DECLARE @FacilityID INT
--SELECT @FacilityID= FacilityID FROM dbo.Facility WHERE  FacilityName = 'Demo Location'

---- Insert Default data into DoorTypes
--	INSERT  INTO [dbo].DoorType
--			( Type, FacilityID )
--	VALUES
--			 ('Wood',@FacilityID)
--			,('Glass',@FacilityID)
--			,('Metal',@FacilityID)
		 
	
--	-- Employee Types

--	INSERT  INTO [dbo].EmpType
--			( Type, FacilityID )
--	VALUES
--			 ('Part Time',@FacilityID)
--			,('Full Time',@FacilityID)
--			,('Contract',@FacilityID)
 
	
--	--  KeyMark Status
	
--		INSERT  INTO [dbo].KeymarkStatus
--				( Status, FacilityID )
--		VALUES
--			 ('In use',@FacilityID)
--			,('Do not use',@FacilityID)
--			,('Not used',@FacilityID)
--			,('Other',@FacilityID)




-- Location
SET IDENTITY_INSERT [dbo].RDM_Facility ON; 
INSERT INTO RDM_Facility
	(FacilityID, FacilityName, Path, Description, LastBackup, IsDeleted, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	fac.ID,fac.FacilityName,NULL,fdesc.Description,NULL, 0,GETDATE(),fac.ID,NULL,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+fnote.Note FROM facility_Notes fnote WHERE fac.ID = fnote.FacilityID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	facility_master	fac
LEFT JOIN	facility fconf ON fac.ID = fconf.FacilityID
LEFT JOIN	facility_description fdesc ON fac.ID = fdesc.FacilityID AND fconf.DescriptionID = fdesc.ID
SET IDENTITY_INSERT [dbo].RDM_Facility OFF;



/* --------------------------  MIGRATE Maintain List Values	--------------------------------------------------- Start */
	-- FacilityZone

	INSERT INTO RDM_FacilityZone ( ZoneName, FacilityID, RDM_FacilityID, RDM_ID)
	SELECT fzone.FacilityZoneName,[FacilityID],[FacilityID],[ID]
	FROM	facility_zones fzone;

	-- DoorType

	INSERT INTO [RDM_DoorType] ( Type, FacilityID, RDM_FacilityID, RDM_ID)
	SELECT	dtypes.DoorTypeName,dtypes.FacilityID,dtypes.FacilityID,dtypes.ID
	FROM	door_types dtypes;

	-- EmployeeType

	INSERT INTO [RDM_EmpType] ( Type, FacilityID, RDM_FacilityID, RDM_ID )
	SELECT	etypes.EmployeeTypeName,etypes.FacilityID,etypes.FacilityID,etypes.ID
	FROM	employee_types etypes;

	-- EmployeeTitle

	INSERT INTO [RDM_EmpTitle] ( Title, FacilityID, RDM_FacilityID, RDM_ID )
	SELECT	eti.EmployeeTitleName,eti.FacilityID,eti.FacilityID,eti.ID
	FROM	employee_titles eti;

	-- Keyways

	INSERT INTO [RDM_Keyway] ( KeywayName, FacilityID, RDM_FacilityID, RDM_ID )
	SELECT	kwy.KeywayName,kwy.FacilityID,kwy.FacilityID,kwy.ID
	FROM	keyways kwy;

	-- KeymarkStatus

	INSERT INTO [RDM_KeyMarkStatus] ( Status, FacilityID, RDM_FacilityID, RDM_ID )
	SELECT	kms.KeymarkStatusName,kms.FacilityID,kms.FacilityID,kms.ID
	FROM	keymark_statuses kms;

	-- ProductMfg
	
	INSERT INTO RDM_ProductMfg (  MfgName, RDM_ID )
	SELECT	mf.ManufacturerName, mf.ID
	FROM	manufacturers mf
	

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

-- Building

INSERT INTO [dbo].[RDM_Building]
	( BuildingName, FacilityZoneID, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Description, Notes)
SELECT	bldg.BuildingName,fzone.FacilityZoneID,bldg.FacilityID,GETDATE(),bldg.FacilityID,bldg.ID,fdesc.Description,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+bnote.Note FROM building_notes bnote WHERE bnote.FacilityID = bldg.FacilityID AND bnote.ID = bldg.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	buildings bldg
LEFT JOIN RDM_FacilityZone fzone ON bldg.FacilityID = fzone.RDM_FacilityID AND bldg.FacilityZoneID = fzone.RDM_ID
LEFT JOIN	facility_description fdesc ON bldg.FacilityID = fdesc.FacilityID AND bldg.DescriptionID = fdesc.ID;


--Department

INSERT INTO [dbo].[RDM_department]
	( DeptName, Description, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	dept.DepartmentName,fdesc.Description,dept.FacilityID,GETDATE(),dept.FacilityID,dept.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+dnote.Note FROM department_notes dnote WHERE dnote.FacilityID = dept.FacilityID AND dnote.ID = dept.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[departments] dept
LEFT JOIN	facility_description fdesc ON dept.FacilityID = fdesc.FacilityID AND dept.DescriptionID = fdesc.ID;

--Cabinet

INSERT INTO [dbo].[RDM_Cabinet]
	( CabinetName, CabinetDesription, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	cabn.CabinetName,fdesc.Description,cabn.FacilityID,GETDATE(),cabn.FacilityID,cabn.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+cnote.Note FROM cabinets_notes cnote WHERE cnote.FacilityID = cabn.FacilityID AND cnote.ID = cabn.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[cabinets] cabn
LEFT JOIN	facility_description fdesc ON cabn.FacilityID = fdesc.FacilityID AND cabn.DescriptionID = fdesc.ID;

--Hook

INSERT INTO [dbo].[RDM_Hook]
	( HookUID, HookName, HookDescription, CabinetID, IsDeleted, DeletedDate, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	hks.HookUID,hks.HookName,hks.HookDescription,cabn.CabinetID,0,NULL,GETDATE(),hks.FacilityID,hks.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+hnote.Note FROM hooks_notes hnote WHERE hnote.FacilityID = hks.FacilityID AND hnote.ID = hks.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[hooks] hks
LEFT JOIN RDM_Cabinet cabn ON hks.FacilityID = cabn.RDM_FacilityID AND hks.CabinetID = cabn.RDM_ID

UNION ALL

SELECT	hks.HookUIDDel,hks.HookNameDel,hks.HookDescriptionDel,cabn.CabinetID,1,hks.HookDeletedDate,GETDATE(),hks.FacilityID,hks.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+hnote.Note FROM hooks_deleted_notes hnote WHERE hnote.FacilityID = hks.FacilityID AND hnote.ID = hks.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[hooks_deleted] hks
LEFT JOIN RDM_Cabinet cabn ON hks.FacilityID = cabn.RDM_FacilityID AND hks.CabinetID = cabn.RDM_ID;

--Door

INSERT INTO [dbo].[RDM_Door]
	( DoorUID, DoorTypeID, DoorNumber, Area, Description, BuildingID, IsDeleted, DeletedDate, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	drs.DoorUID,dtyp.DoorTypeID,drs.DoorNumber,drs.Area,fdesc.Description,bldg.BuildingID,0,NULL,drs.FacilityID,GETDATE(),drs.FacilityID,drs.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+dnote.Note FROM door_notes dnote WHERE dnote.FacilityID = drs.FacilityID AND dnote.ID = drs.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[doors] drs
LEFT JOIN RDM_DoorType dtyp ON drs.FacilityID = dtyp.RDM_FacilityID AND drs.DoorTypeID = dtyp.RDM_ID
LEFT JOIN RDM_Building bldg ON drs.FacilityID = bldg.RDM_FacilityID AND drs.BuildingID = bldg.RDM_ID
LEFT JOIN	facility_description fdesc ON drs.FacilityID = fdesc.FacilityID AND drs.DescriptionID = fdesc.ID

UNION ALL

SELECT	drs.DoorUIDDel,dtyp.DoorTypeID,drs.DoorNumberDel,drs.AreaDel,fdesc.Description,bldg.BuildingID,1,drs.DoorDeletedDate,drs.FacilityID,GETDATE(),drs.FacilityID,drs.ID,
NULL Notes
FROM	[doors_deleted] drs
LEFT JOIN RDM_DoorType dtyp ON drs.FacilityID = dtyp.RDM_FacilityID AND drs.DoorTypeIDDel = dtyp.RDM_ID
LEFT JOIN RDM_Building bldg ON drs.FacilityID = bldg.RDM_FacilityID AND drs.BuildingID = bldg.RDM_ID
LEFT JOIN	facility_description fdesc ON drs.FacilityID = fdesc.FacilityID AND drs.DescriptionIDDel = fdesc.ID;

--Employee  (EmployeeUID,Notes)

INSERT INTO [dbo].[RDM_Employee]
	( EmployeeUID, ID, LastName, FirstName, MiddleInitial, Email, Phone, Misc, Description, EmpTypeID, EmpTitleID, DeptID, IsDeleted, DeletedDate, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	EmployeeUID,emp.EmployeeID,emp.LastName,emp.FirstName,emp.MiddleInit,NULL,NULL,NULL,fdesc.Description,etyp.EmpTypeID,etl.EmpTitleID,dep.DeptID,0,NULL,emp.FacilityID,GETDATE(),emp.FacilityID,emp.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM employee_notes enote WHERE enote.FacilityID = emp.FacilityID AND 
enote.ID = emp.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	[employees] emp
LEFT JOIN RDM_EmpType etyp ON emp.FacilityID = etyp.RDM_FacilityID AND emp.EmployeeTypeID = etyp.RDM_ID
LEFT JOIN RDM_EmpTitle etl ON emp.FacilityID = etl.RDM_FacilityID AND emp.EmployeeTitleID = etl.RDM_ID
LEFT JOIN RDM_Department dep ON emp.FacilityID = dep.RDM_FacilityID AND emp.DepartmentID = dep.RDM_ID
LEFT JOIN	facility_description fdesc ON emp.FacilityID = fdesc.FacilityID AND emp.DescriptionID = fdesc.ID

UNION ALL

SELECT	EmployeeUID,emp.EmployeeIDDel,emp.LastNameDel,emp.FirstNameDel,emp.MiddleInitDel,NULL,NULL,NULL,fdesc.Description,etyp.EmpTypeID,etl.EmpTitleID,dep.DeptID,1,emp.EmployeeDeletedDate,emp.FacilityID,GETDATE(),emp.FacilityID,emp.ID,
NULL Notes
FROM	[employees_deleted] emp
LEFT JOIN RDM_EmpType etyp ON emp.FacilityID = etyp.RDM_FacilityID AND emp.EmployeeTypeIDDel = etyp.RDM_ID
LEFT JOIN RDM_EmpTitle etl ON emp.FacilityID = etl.RDM_FacilityID AND emp.EmployeeTitleIDDel = etl.RDM_ID
LEFT JOIN RDM_Department dep ON emp.FacilityID = dep.RDM_FacilityID AND emp.DepartmentIDDel = dep.RDM_ID
LEFT JOIN	facility_description fdesc ON emp.FacilityID = fdesc.FacilityID AND emp.DescriptionIDDel = fdesc.ID;

-- Product (Refer this Notes for all other Notes)

INSERT INTO RDM_Product
	( ProductName, ProductDescription, MfgID, ProductTypeID, NumCores, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	prd.ProductName, fdesc.Description,mfg.MfgID,typ.ProductTypeID,prd.NumCores,GETDATE(),NULL,prd.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM products_notes enote WHERE enote.ID = prd.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		products	prd
LEFT JOIN	RDM_ProductMfg mfg ON prd.ManufacturerID = mfg.RDM_ID--MfgID
LEFT JOIN	RDM_ProductType typ ON prd.ProductTypeIX = typ.ProductTypeID
LEFT JOIN	product_descriptions fdesc ON prd.DescriptionID = fdesc.ID;

-- Parts (Without Dispositions, Notes has to be imported)

INSERT INTO RDM_Part
	( PartUID,SerialNo, PartDescription, ProductID, CreatedDate, Serialize, DispositionID, DispositionUID, DispositionType, OosDate, FacilityID, RDM_FacilityID, RDM_ID, Notes)
SELECT	pt.lock_uid,pt.LockSerialNo,fdesc.Description,prd.ProductID,GETDATE(),pt.LockSerialize,pt.DispositionID,pt.DispositionUID,pt.DispositionTypeID,pt.OosDate,pt.FacilityID,pt.FacilityID,pt.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM best_locks_notes enote WHERE enote.ID = pt.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		best_locks	pt
LEFT JOIN	facility_products fprd ON pt.FacilityID = fprd.FacilityID AND pt.ProductID = fprd.ID
LEFT JOIN	RDM_Product prd ON QUOTENAME(fprd.ProductName) = QUOTENAME(prd.ProductName) AND pt.ProductID = fprd.ID
LEFT JOIN	facility_description fdesc ON pt.DescriptionID = fdesc.ID AND pt.FacilityID = fdesc.FacilityID;

-- MasterKeySystem (Without GM, Control Keys, Notes has to be imported)

INSERT INTO RDM_MasterKeySystem
	( SystemName, KeywayID, SystemTypeID, PinsizeID, MarkID, Keystamp, GM_KeyMarkID, CT_KeyMarkID, FacilityID, RDM_FacilityID, RDM_ID, Notes)
SELECT	ss.SystemName,typ.KeywayID,ss.SystemTypeIx,ss.PinSizeIx,ss.MarkIx,ss.KeyStamp,NULL,NULL,ss.FacilityID,ss.FacilityID,ss.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM masterkeys_notes enote WHERE enote.ID = ss.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM	best_system	ss
LEFT JOIN	RDM_Keyway typ ON ss.KeywayID = typ.RDM_ID AND ss.FacilityID = typ.FacilityID;


-- Keymarks (Need to confirm regarding ParentID, Notes has to be imported)

INSERT INTO RDM_KeyMark
	( KeyMarkName, KeyMarkStatusID, SystemID, KeyCut, KeyMarkLevelID, ParentID, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	km.Keymark,kms.KeymarkStatusID,ms.SystemID,km.Keycut,km.LevelIx,NULL,km.FacilityID,GETDATE(),km.FacilityID,km.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM keymark_notes enote WHERE enote.ID = km.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		keymarks	km
LEFT JOIN	RDM_KeyMarkStatus kms ON km.KeymarkStatusID = kms.RDM_ID AND km.FacilityID = kms.FacilityID
LEFT JOIN	RDM_MasterKeySystem ms ON km.KeymarkSystemID = ms.RDM_ID AND km.FacilityID = ms.FacilityID;


UPDATE km		-- ParentID for keymarks
SET ParentID = pkm.KeyMarkID
FROM RDM_Keymark km
JOIN	keymarks	kms ON km.FacilityID = kms.FacilityID AND km.RDM_ID = kms.ID
JOIN	masterkeys	mk ON kms.FacilityID = mk.FacilityID AND kms.MasterkeyID = mk.ID
JOIN	RDM_KeyMark pkm ON mk.IsMasterID = pkm.RDM_ID AND mk.FacilityID = pkm.FacilityID;

UPDATE km		-- ParentID for Control Keys (Added on 18Jun 2018 )
SET ParentID = pkm.KeyMarkID
FROM	RDM_Keymark km
JOIN	control_Key ck ON km.FacilityID = ck.FacilityID AND km.RDM_ID = ck.ControlKeymarkID
JOIN	RDM_Keymark pkm ON pkm.FacilityID = ck.FacilityID AND pkm.RDM_ID = ck.SeriesKeymarkID;

UPDATE mk		-- Set GM key, Control key for MasterKey system
SET GM_KeyMarkID = ck.SeriesKeymarkID,CT_KeyMarkID = ck.ControlKeymarkID
FROM	RDM_Keymark km
JOIN	RDM_MasterKeySystem mk ON km.SystemID = mk.SystemID
JOIN	control_Key ck ON km.FacilityID = ck.FacilityID AND km.RDM_ID = ck.SeriesKeymarkID AND km.KeyMarkLevelID <= 5;

UPDATE mk		-- Set GM key, Control key for MasterKey system
SET GM_KeyMarkID = km.KeyMarkID,CT_KeyMarkID = ckm.KeyMarkID
FROM		RDM_MasterKeySystem mk
LEFT JOIN	RDM_Keymark km ON km.facilityID = mk.facilityID AND km.rdm_ID = mk.GM_KeyMarkID
LEFT JOIN	RDM_Keymark ckm ON ckm.facilityID = mk.facilityID AND ckm.rdm_ID = mk.CT_KeyMarkID


-- Coremarks(Notes has to be imported)

INSERT INTO RDM_CoreMark
	( CoreMarkName, KeyMarkID, FacilityID, OP_By, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	cm.Coremark,km.keymarkid, cm.FacilityID, NULL, GETDATE(), cm.FacilityID, cm.ID,
REPLACE(
REPLACE(
	REPLACE(
		STUFF((SELECT ''+enote.Note FROM coremarks_notes enote WHERE enote.ID = cm.ID FOR XML PATH('')),1,0,'')
	,'&#x0D;','')
,'&#x20;',' ') 
,'newline','<BR>') Notes
FROM		coremarks cm
LEFT JOIN	RDM_KeyMark km ON cm.FacilityID = km.FacilityID AND cm.KeymarkID = km.RDM_ID;

-- Control_Sel

INSERT INTO RDM_CoreMark_Sel
	( CoreMarkID,KeyMarkID, RDM_FacilityID )
SELECT	cm.CoreMarkID, km.KeyMarkID, km.FacilityID
FROM		control_sel	cs
JOIN	RDM_KeyMark km ON cs.FacilityID = km.FacilityID AND cs.SelMasterCoreID = km.RDM_ID
JOIN	RDM_CoreMark cm ON cs.FacilityID = cm.FacilityID AND cs.SelOperCoreID = cm.RDM_ID



-- Keyrings

INSERT INTO RDM_KeyRings
	( KeyRingUID, KeyRing, Description, DispositionID, DispositionUID, DispositionType, KeyringOosDate, FacilityID, RDM_FacilityID, RDM_ID, Notes)
SELECT	kr.KeyringUID, kr.KeyringID, fdesc.Description, kr.DispositionID,kr.DispostionUID,kr.DispositionType,kr.KeyringOosDate,kr.FacilityID,kr.FacilityID,kr.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM keyring_notes enote WHERE enote.ID = kr.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		keyrings kr
LEFT JOIN	facility_description fdesc ON kr.DescriptionID = fdesc.ID AND kr.FacilityID = fdesc.FacilityID;

-- Key (Notes has to be imported)

INSERT INTO [RDM_Key]
	( KeyUID, KeySerialNo, KeyDescription, KeyMarkID, ProductID, KeySerialize, KeyDispositonID, KeyDispositionUID, KeyDispositionType, KeyOosDate, KeyFinalDispID, KeyFinalDispType, FacilityID, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	ky.KeyUID, ky.KeySerialNo, fdesc.Description, km.KeyMarkID, prd.ProductID, ky.KeySerialize, ky.KeyDispositonID, ky.KeyDispositionUID, ky.KeyDispositionTypeID, ky.KeyOosDate, ky.KeyFinalDispUID, ky.KeyFinalDispTypeID, ky.FacilityID, GETDATE(), ky.FacilityID, ky.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM best_key_notes enote WHERE enote.ID = ky.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		best_key ky
LEFT JOIN	RDM_KeyMark km ON ky.FacilityID = km.FacilityID AND ky.KeymarkID = km.RDM_ID
LEFT JOIN	facility_products fprd ON ky.FacilityID = fprd.FacilityID AND ky.KeyProductID = fprd.ID
LEFT JOIN	RDM_Product prd ON QUOTENAME(fprd.ProductName) = QUOTENAME(prd.ProductName) AND ky.KeyProductID = fprd.ID
LEFT JOIN	facility_description fdesc ON ky.DescriptionID = fdesc.ID AND ky.FacilityID = fdesc.FacilityID;


-- Core 

INSERT INTO RDM_Core
	( CoreUID, CoreSerialNo, CoreMarkID, ProductID, CoreSerialize, CoreDispositionID, CoreDispositionUID, CoreDispositionType, CoreOosDate, CoreFinalDispID, CoreFinalDispType, ModifiedDate, RDM_FacilityID, RDM_ID, Notes)
SELECT	cr.CoreUID, cr.CoreSerialNo, cm.CoreMarkID, prd.ProductID, cr.CoreSerialize, cr.DispositionID, cr.CoreDispositionUID, cr.CoreDispositionTypeID,cr.CoreOosDate, cr.CoreFinalDispUID, cr.CoreFinalDispTypeID, GETDATE(), cr.FacilityID, cr.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM core_notes enote WHERE enote.ID = cr.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		cores cr
LEFT JOIN	RDM_CoreMark cm ON cr.FacilityID = cm.FacilityID AND cr.CoremarkID = cm.RDM_ID
LEFT JOIN	facility_products fprd ON cr.FacilityID = fprd.FacilityID AND cr.ProductID = fprd.ID
LEFT JOIN	RDM_Product prd ON QUOTENAME(fprd.ProductName) = QUOTENAME(prd.ProductName) AND cr.ProductID = fprd.ID;

-- Users (include logic for Notes)

INSERT INTO [RDM_User]
	( UserLogin, Email, HashedPassword, Description, IsLocked, RoleID, ModifiedDate, PwdChangedDate, RDM_ID, Notes)
SELECT	DISTINCT usr.Login,NULL,usr.Password,NULL,0,CASE WHEN usr.PERMIT_SUPER_USER = 1 THEN 1 ELSE 2 END,GETDATE(),GETDATE(),usr.ID,
REPLACE(REPLACE(REPLACE(STUFF((SELECT ''+enote.Note FROM facility_user_notes enote WHERE enote.ID = usr.ID FOR XML PATH('')),1,0,''),'&#x0D;',''),'&#x20;',' '),'newline','<BR>') Notes
FROM		users	usr
WHERE usr.[Login] NOT IN (SELECT UserLogin FROM [RDM_User] WHERE IsMigrationRequired = 0 ) 
--LEFT JOIN	facility_users fusr ON usr.Login = fusr.UserLogin
;

--UPDATE [RDM_User] SET HashedPassword = 'srbQ8MmZDspCOUVpBpWgmA==' WHERE IsMigrationRequired = 1;

INSERT INTO RDM_UserFacilities
	(USERID,FacilityID,IsDefault)
SELECT	usr.UserID,fac.FacilityID,0
FROM	RDM_User	usr,RDM_Facility fac
WHERE usr.IsMigrationRequired = 0;

DELETE FROM [RDM_User] WHERE IsMigrationRequired = 0;

-- User Permissions

INSERT INTO RDM_UserPermissions
	(USERID,CLONED_FROM)
SELECT	usr.UserID,CASE WHEN usr.RoleID = 1 THEN 'ADMIN' ELSE 'DEFAULT USER' END
FROM	RDM_User	usr;

UPDATE	per
SET
	 per.PERMIT_VIEW_CODES			= osr.PERMIT_VIEW_CODES
	,per.PERMIT_VIEW_BUILDING		= osr.PERMIT_BUILDING 
	,per.PERMIT_UPDATE_BUILDING		= osr.PERMIT_BUILDING & osr.PERMIT_UPDATE_BUILDING 
	,per.PERMIT_DELETE_BUILDING		= osr.PERMIT_BUILDING & osr.PERMIT_UPDATE_BUILDING

	,per.PERMIT_VIEW_CABINET		= osr.PERMIT_CABINET
	,per.PERMIT_UPDATE_CABINET		= osr.PERMIT_CABINET & osr.PERMIT_UPDATE_CABINET
	,per.PERMIT_DELETE_CABINET		= osr.PERMIT_CABINET & osr.PERMIT_UPDATE_CABINET

	,per.PERMIT_VIEW_CORE			= osr.PERMIT_CORE
	,per.PERMIT_UPDATE_CORE			= osr.PERMIT_CORE & osr.PERMIT_UPDATE_CORE
	,per.PERMIT_DELETE_CORE			= osr.PERMIT_CORE & osr.PERMIT_UPDATE_CORE

	,per.PERMIT_VIEW_DEPARTMENT		= osr.PERMIT_DEPT
	,per.PERMIT_UPDATE_DEPARTMENT	= osr.PERMIT_DEPT & osr.PERMIT_UPDATE_DEPT
	,per.PERMIT_DELETE_DEPARTMENT	= osr.PERMIT_DEPT & osr.PERMIT_UPDATE_DEPT

	,per.PERMIT_VIEW_DOOR			= osr.PERMIT_DOOR
	,per.PERMIT_UPDATE_DOOR			= osr.PERMIT_DOOR & osr.PERMIT_UPDATE_DOOR
	,per.PERMIT_DELETE_DOOR			= osr.PERMIT_DOOR & osr.PERMIT_UPDATE_DOOR

	,per.PERMIT_VIEW_EMPLOYEE		= osr.PERMIT_EMPLOYEE
	,per.PERMIT_UPDATE_EMPLOYEE		= osr.PERMIT_EMPLOYEE & osr.PERMIT_UPDATE_EMPLOYEE
	,per.PERMIT_DELETE_EMPLOYEE		= osr.PERMIT_EMPLOYEE & osr.PERMIT_UPDATE_EMPLOYEE

	,per.PERMIT_VIEW_FACILITY		= osr.PERMIT_FACILITY

	,per.PERMIT_VIEW_HOOK			= osr.PERMIT_HOOK
	,per.PERMIT_UPDATE_HOOK			= osr.PERMIT_HOOK & osr.PERMIT_UPDATE_HOOK
	,per.PERMIT_DELETE_HOOK			= osr.PERMIT_HOOK & osr.PERMIT_UPDATE_HOOK

	,per.PERMIT_VIEW_KEY			= osr.PERMIT_KEY_CARD
	,per.PERMIT_UPDATE_KEY			= osr.PERMIT_KEY_CARD & osr.PERMIT_UPDATE_KEY
	,per.PERMIT_DELETE_KEY			= osr.PERMIT_KEY_CARD & osr.PERMIT_UPDATE_KEY

	,per.PERMIT_VIEW_KEYRING		= osr.PERMIT_KEYRING
	,per.PERMIT_UPDATE_KEYRING		= osr.PERMIT_KEYRING & osr.PERMIT_UPDATE_KEYRING
	,per.PERMIT_DELETE_KEYRING		= osr.PERMIT_KEYRING & osr.PERMIT_UPDATE_KEYRING

	,per.PERMIT_VIEW_PART			= osr.PERMIT_LOCK
	,per.PERMIT_UPDATE_PART			= osr.PERMIT_LOCK & osr.PERMIT_UPDATE_LOCK
	,per.PERMIT_DELETE_PART			= osr.PERMIT_LOCK & osr.PERMIT_UPDATE_LOCK

	,per.PERMIT_VIEW_MASTERKEY		= osr.PERMIT_MASTERKEY
	,per.PERMIT_UPDATE_MASTERKEY	= osr.PERMIT_MASTERKEY & osr.PERMIT_UPDATE_MASTERKEY
	,per.PERMIT_DELETE_MASTERKEY	= osr.PERMIT_MASTERKEY & osr.PERMIT_UPDATE_MASTERKEY

	,per.PERMIT_VIEW_PRODUCT		= osr.PERMIT_PRODUCT
	,per.PERMIT_UPDATE_PRODUCT		= osr.PERMIT_PRODUCT & osr.PERMIT_UPDATE_PRODUCT
	,per.PERMIT_DELETE_PRODUCT		= osr.PERMIT_PRODUCT & osr.PERMIT_UPDATE_PRODUCT

	,per.PERMIT_VIEW_REPORTS		= osr.PERMIT_REPORTS
	,per.PERMIT_UPDATE_REPORTS		= osr.PERMIT_REPORTS & osr.PERMIT_UPDATE_REPORTS
	,per.PERMIT_DELETE_REPORTS		= osr.PERMIT_REPORTS & osr.PERMIT_UPDATE_REPORTS

	,per.PERMIT_VIEW_UNASSIGNED		= osr.PERMIT_UNASSIGNED
	,per.PERMIT_UPDATE_UNASSIGNED	= osr.PERMIT_UNASSIGNED & osr.PERMIT_UPDATE_UNASSIGNED
	,per.PERMIT_DELETE_UNASSIGNED	= osr.PERMIT_UNASSIGNED & osr.PERMIT_UPDATE_UNASSIGNED

	,per.PERMIT_VIEW_OOS			= osr.PERMIT_DISPLACED
	,per.PERMIT_UPDATE_OOS			= osr.PERMIT_DISPLACED & osr.PERMIT_UPDATE_DISPLACED
	,per.PERMIT_DELETE_OOS			= osr.PERMIT_DISPLACED & osr.PERMIT_UPDATE_DISPLACED

	,per.PERMIT_TRANS_OOS_TRANSFERCORE		= osr.PERMIT_TRANS_DISPLACED
	,per.PERMIT_TRANS_OOS_TRANSFERKEY		= osr.PERMIT_TRANS_DISPLACED
	,per.PERMIT_TRANS_OOS_TRANSFERKEYRING	= osr.PERMIT_TRANS_DISPLACED
	,per.PERMIT_TRANS_OOS_TRANSFERPART		= osr.PERMIT_TRANS_DISPLACED

	,per.PERMIT_TRANS_DOOR_INSTALLNEWCORE		= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_REMOVECORE			= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_INSTALLEXISTINGCORE	= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_TRANSFERCORE			= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_INSTALLNEWPART		= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_REMOVEPART			= osr.PERMIT_TRANS_DOOR
	,per.PERMIT_TRANS_DOOR_INSTALLEXISTINGPART	= osr.PERMIT_TRANS_DOOR

	,per.PERMIT_TRANS_EMP_ISSUENEWKEY			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_RETURNKEY				= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUEEXISTINGKEY		= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_TRANSFERKEY			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_TRANSFERCORE			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUENEWCORE			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_RETURNCORE			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUEEXISTINGCORE		= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUENEWKEYRING		= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_RETURNKEYRING			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUEEXISTINGKEYRING	= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_TRANSFERKEYRING		= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_TRANSFERPART			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUENEWPART			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_RETURNPART			= osr.PERMIT_TRANS_EMPLOYEE
	,per.PERMIT_TRANS_EMP_ISSUEEXISTINGPART		= osr.PERMIT_TRANS_EMPLOYEE

	,per.PERMIT_TRANS_HOOK_ISSUENEWCORE			= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_ISSUENEWKEY			= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_ISSUENEWKEYRING		= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_ISSUENEWPART			= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_TRANSFERCORE			= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_TRANSFERKEY			= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_TRANSFERKEYRING		= osr.PERMIT_TRANS_HOOK
	,per.PERMIT_TRANS_HOOK_TRANSFERPART			= osr.PERMIT_TRANS_HOOK

	,per.PERMIT_TRANS_KEYRING_ATTACHNEWKEY		= osr.PERMIT_TRANS_KEYRING
	,per.PERMIT_TRANS_KEYRING_DETACHKEY			= osr.PERMIT_TRANS_KEYRING
	,per.PERMIT_TRANS_KEYRING_ATTACHEXISTINGKEY	= osr.PERMIT_TRANS_KEYRING

	,per.PERMIT_TRANS_PART_INSTALLEXISTINGCORE	= osr.PERMIT_TRANS_LOCK
	,per.PERMIT_TRANS_PART_INSTALLNEWCORE		= osr.PERMIT_TRANS_LOCK
	,per.PERMIT_TRANS_PART_REMOVECORE			= osr.PERMIT_TRANS_LOCK

	,per.PERMIT_TRANS_UNA_TRANSFERKEY			= osr.PERMIT_TRANS_UNASSIGNED
	,per.PERMIT_TRANS_UNA_TRANSFERCORE			= osr.PERMIT_TRANS_UNASSIGNED
	,per.PERMIT_TRANS_UNA_TRANSFERKEYRING		= osr.PERMIT_TRANS_UNASSIGNED
	,per.PERMIT_TRANS_UNA_TRANSFERPART			= osr.PERMIT_TRANS_UNASSIGNED

FROM	RDM_User			usr
JOIN	RDM_UserPermissions	per	ON usr.UserID = per.USERID
JOIN	users				osr ON usr.UserLogin = osr.Login

-- User Facilities (Detail defaults has to be included)

INSERT INTO RDM_UserFacilities
	(USERID,FacilityID,IsDefault)
SELECT	UserID,FacilityID,CASE WHEN IsDefault = 1 THEN 1 ELSE 0 END FROM
(
SELECT	usr.UserID,fac.FacilityID, ROW_NUMBER()OVER(PARTITION BY usr.UserID ORDER BY fac.FacilityID) IsDefault
FROM	RDM_User	usr,RDM_Facility fac
)A;

-- Dashboard Settings

INSERT INTO RDM_DashboardSettings
	( TileName, Position, Visibility, UserID)
SELECT	ds.Setting,1,1,usr.UserID
FROM RDM_User usr,
(	SELECT 'Masterkey' AS Setting
	UNION ALL SELECT 'Departments'
	UNION ALL SELECT 'People'
	UNION ALL SELECT 'Keys'
	UNION ALL SELECT 'Buildings'
	UNION ALL SELECT 'Doors'
	UNION ALL SELECT 'Cores'
	UNION ALL SELECT 'Cabinets'
	UNION ALL SELECT 'Hooks'
	UNION ALL SELECT 'Keyrings'
	UNION ALL SELECT 'Products'
	UNION ALL SELECT 'Parts'
	UNION ALL SELECT 'Out of Service'
	UNION ALL SELECT 'Unassigned'
	UNION ALL SELECT 'Reports'
	UNION ALL SELECT 'Location'
) ds


-- ItemHistory ( change Identity value)
	
	SELECT * INTO histories_Del FROM histories

	-- Handle deleted employees in History table

	UPDATE H
	SET H.ToParentType = 30
	FROM histories_Del H
	WHERE ToParentType = 6 
	AND NOT EXISTS (SELECT 1 FROM employees E WHERE E.EmployeeUID = H.ToParentID AND E.ID = H.ToID )
	AND NOT EXISTS (SELECT 1 FROM employees E WHERE H.ToID = H.ToParentID AND E.ID = H.ToID )
	AND EXISTS ( SELECT 1 FROM employees_deleted ED WHERE ED.ID = H.ToID );

	UPDATE H
	SET H.ToParentType = 31
	FROM histories_Del H
	WHERE ToParentType = 18 
	AND NOT EXISTS (SELECT 1 FROM doors E WHERE E.DoorUID = H.ToParentID AND E.ID = H.ToID )
	AND NOT EXISTS (SELECT 1 FROM doors E WHERE H.ToID = H.ToParentID AND E.ID = H.ToID )
	AND EXISTS ( SELECT 1 FROM doors_deleted ED WHERE ED.ID = H.ToID );

	UPDATE histories_Del SET DateReturn = NULL WHERE YEAR(DateReturn) = 2038;

INSERT INTO RDM_ItemHistory
	(  ToID, UserID, DatetimeWhen, DateReturn, DateDue, Comment, ItemParentID, ItemParentType, ToParentID, ToParentType, FacilityID, RDM_FacilityID, RDM_ID)
SELECT	hst.ToID, UserID, hst.DatetimeWhen, hst.DateReturn, hst.DateDue, hst.Comment, hst.ItemParentID, hst.ItemParentType, hst.ToParentID, hst.ToParentType, hst.FacilityID, hst.FacilityID, hst.ID
FROM		histories_Del hst
ORDER BY hst.ItemParentType,hst.ItemParentID, hst.DatetimeWhen,hst.ID;
--ORDER BY hst.DatetimeWhen,hst.ID;


-- ItemHistory Dispositions

UPDATE RDM_ItemHistory
SET ToParentType = CASE ToParentType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 30 THEN 'EMPLOYEE_DEL'
						WHEN 16	THEN 'KEYRING'
						WHEN 18	THEN 'DOOR'
						WHEN 31	THEN 'DOOR_DEL'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END;

UPDATE IH
SET IH.ToParentID = Em.EmployeeId,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Employee Em ON IH.FacilityID = Em.FacilityID AND IH.ToID = Em.RDM_ID --AND IH.ToParentID = Em.EmployeeUID
WHERE IH.ToParentType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.ToParentID = Em.EmployeeId,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Employee Em ON IH.FacilityID = Em.FacilityID AND IH.ToID = Em.RDM_ID --AND IH.ToParentID = Em.EmployeeUID
WHERE IH.ToParentType = 'EMPLOYEE_DEL'
AND Em.IsDeleted = 1;

UPDATE IH
SET IH.ToParentID = Em.DoorID,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Door Em ON IH.FacilityID = Em.FacilityID AND IH.ToID = Em.RDM_ID --IH.ToParentID = Em.DoorUID
WHERE IH.ToParentType = 'DOOR'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.ToParentID = Em.DoorID,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Door Em ON IH.FacilityID = Em.FacilityID AND IH.ToID = Em.RDM_ID --IH.ToParentID = Em.DoorUID 
WHERE IH.ToParentType = 'DOOR_DEL'
AND Em.IsDeleted = 1;

UPDATE IH
SET IH.ToParentID = Em.KeyRingID,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_KeyRings Em ON IH.FacilityID = Em.FacilityID AND IH.ToParentID = Em.KeyRingUID --IH.ToParentID = Em.RDM_ID
WHERE IH.ToParentType = 'KEYRING';

UPDATE IH
SET IH.ToParentID = Em.HookID,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Hook Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.ToParentID = Em.HookUID --IH.ToParentID = Em.RDM_ID
WHERE IH.ToParentType = 'HOOK';

UPDATE IH
SET IH.ToParentID = NULL,IH.UserID = 1
--SELECT IH.*
FROM	RDM_ItemHistory	IH
WHERE IH.ToParentType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');

UPDATE RDM_ItemHistory
SET ItemParentType = CASE ItemParentType 
						WHEN 11	THEN 'KEY'
						WHEN 15 THEN 'CORE'
						WHEN 16	THEN 'KEYRING'
						WHEN 17	THEN 'PART' END;


UPDATE IH
SET IH.ItemParentID = Em.keyID,IH.itemID = Em.KeyID
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	[RDM_Key] Em ON IH.FacilityID = Em.FacilityID AND IH.ItemParentID = Em.KeyUID --IH.ItemParentID = Em.RDM_ID
WHERE IH.ItemParentType = 'KEY';

UPDATE IH
SET IH.ItemParentID = Em.CoreID,IH.itemID = Em.CoreID
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_Core Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.ItemParentID = Em.CoreUID --IH.ItemParentID = Em.RDM_ID
WHERE IH.ItemParentType = 'CORE';

UPDATE IH
SET IH.ItemParentID = Em.KeyRingID,IH.itemID = Em.KeyRingID
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_KeyRings Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.ItemParentID = Em.KeyRingUID --IH.ItemParentID = Em.RDM_ID
WHERE IH.ItemParentType = 'KEYRING';

UPDATE IH
SET IH.ItemParentID = Em.PartID,IH.itemID = Em.PartID
--SELECT IH.*
FROM	RDM_ItemHistory	IH
JOIN	RDM_part Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.ItemParentID = Em.PartUID --IH.ItemParentID = Em.RDM_ID
WHERE IH.ItemParentType = 'PART';


UPDATE RDM_ItemHistory
SET ToParentType = 'EMPLOYEE' WHERE ToParentType = 'EMPLOYEE_DEL';

UPDATE RDM_ItemHistory
SET ToParentType = 'DOOR' WHERE ToParentType = 'DOOR_DEL';

-- Part Dispositions

UPDATE RDM_Part
SET DispositionType = CASE DispositionType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END;

UPDATE IH
SET IH.DispositionID = Em.EmployeeId
--SELECT IH.*
FROM	RDM_Part	IH
JOIN	RDM_Employee Em ON IH.RDM_FacilityID = Em.FacilityID AND IH.DispositionID = Em.RDM_ID AND IH.DispositionUID = Em.EmployeeUID
WHERE IH.DispositionType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.DispositionID = Em.DoorID
--SELECT IH.*
FROM	RDM_Part	IH
JOIN	RDM_Door Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.DispositionID = Em.RDM_ID AND IH.DispositionUID = Em.DoorUID
WHERE IH.DispositionType = 'DOOR'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.DispositionID = Em.HookID
--SELECT IH.*
FROM	RDM_Part	IH
JOIN	RDM_Hook Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.DispositionUID = Em.HookUID AND IH.DispositionID = Em.RDM_ID
WHERE IH.DispositionType = 'HOOK';


UPDATE IH
SET IH.DispositionID = NULL
--SELECT IH.*
FROM	RDM_Part	IH
WHERE IH.DispositionType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');

-- Keyring Dispositions

UPDATE RDM_KeyRings
SET DispositionType = CASE DispositionType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END;

UPDATE IH
SET IH.DispositionID = Em.EmployeeId
--SELECT IH.*
FROM	RDM_KeyRings	IH
JOIN	RDM_Employee Em ON IH.FacilityID = Em.FacilityID AND IH.DispositionID = Em.RDM_ID AND IH.DispositionUID = Em.EmployeeUID
WHERE IH.DispositionType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.DispositionID = Em.HookID
--SELECT IH.*
FROM	RDM_KeyRings	IH
JOIN	RDM_Hook Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.DispositionUID = Em.HookUID AND IH.DispositionID = Em.RDM_ID
WHERE IH.DispositionType = 'HOOK';

UPDATE IH
SET IH.DispositionID = NULL
--SELECT IH.*
FROM	RDM_KeyRings	IH
WHERE IH.DispositionType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');


-- Key Dispositions

UPDATE [RDM_Key]
SET KeyDispositionType = CASE KeyDispositionType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END
	,KeyFinalDispType = CASE KeyFinalDispType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END;


UPDATE IH
SET IH.KeyDispositonID = Em.EmployeeId
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_Employee Em ON IH.FacilityID = Em.FacilityID AND IH.KeyDispositonID = Em.RDM_ID
WHERE IH.KeyDispositionType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.KeyFinalDispID = Em.EmployeeId
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_Employee Em ON IH.FacilityID = Em.FacilityID AND IH.KeyFinalDispID = Em.EmployeeUID --IH.KeyFinalDispID = Em.RDM_ID
WHERE IH.KeyFinalDispType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.KeyDispositonID = Em.HookID
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_hook Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.KeyDispositonID = Em.RDM_ID
WHERE IH.KeyDispositionType = 'HOOK';

UPDATE IH
SET IH.KeyFinalDispID = Em.HookID
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_hook Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.KeyFinalDispID = Em.HookUID --IH.KeyFinalDispID = Em.RDM_ID
WHERE IH.KeyFinalDispType = 'HOOK';

UPDATE IH
SET IH.KeyDispositonID = Em.KeyRingID
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_keyrings Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.KeyDispositonID = Em.RDM_ID
WHERE IH.KeyDispositionType = 'KEYRING';

UPDATE IH
SET IH.KeyFinalDispID = Em.KeyRingID
--SELECT IH.*
FROM	[RDM_Key]	IH
JOIN	RDM_keyrings Em ON IH.FacilityID = Em.RDM_FacilityID AND IH.KeyFinalDispID = Em.KeyRingUID --IH.KeyFinalDispID = Em.RDM_ID
WHERE IH.KeyFinalDispType = 'KEYRING';


UPDATE IH
SET IH.KeyDispositonID = NULL
--SELECT IH.*
FROM	[RDM_Key]	IH
WHERE IH.KeyDispositionType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');

UPDATE IH
SET IH.KeyFinalDispID = NULL
--SELECT IH.*
FROM	[RDM_Key]	IH
WHERE IH.KeyFinalDispType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');


-- Core Dispositions



UPDATE RDM_Core
SET CoreDispositionType = CASE CoreDispositionType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 17	THEN 'PART'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END
	,CoreFinalDispType = CASE CoreFinalDispType 
						WHEN 6	THEN 'EMPLOYEE'
						WHEN 16	THEN 'KEYRING'
						WHEN 17	THEN 'PART'
						WHEN 18	THEN 'DOOR'
						WHEN 36	THEN 'HOOK'
						WHEN 22	THEN 'LOST'
						WHEN 23	THEN 'STOLEN'
						WHEN 24	THEN 'DESTROYED'
						WHEN 25	THEN 'UNASSIGNED' END;


UPDATE IH
SET IH.CoreDispositionID = Em.EmployeeId
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_Employee Em ON IH.RDM_FacilityID = Em.FacilityID AND IH.CoreDispositionID = Em.RDM_ID
WHERE IH.CoreDispositionType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.CoreFinalDispID = Em.EmployeeId
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_Employee Em ON IH.RDM_FacilityID = Em.FacilityID AND IH.CoreFinalDispID = Em.EmployeeUID --IH.CoreFinalDispID = Em.RDM_ID
WHERE IH.CoreFinalDispType = 'EMPLOYEE'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.CoreDispositionID = Em.DoorID
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_Door Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreDispositionID = Em.RDM_ID
WHERE IH.CoreDispositionType = 'DOOR'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.CoreFinalDispID = Em.DoorID
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_Door Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreFinalDispID = Em.DoorUID --IH.CoreFinalDispID = Em.RDM_ID
WHERE IH.CoreFinalDispType = 'DOOR'
AND Em.IsDeleted = 0;

UPDATE IH
SET IH.CoreDispositionID = Em.PartID
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_part Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreDispositionID = Em.RDM_ID
WHERE IH.CoreDispositionType = 'PART';

UPDATE IH
SET IH.CoreFinalDispID = Em.PartID
--SELECT IH.*
FROM	RDM_Core	IH
JOIN	RDM_part Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreFinalDispID = Em.PartUID --IH.CoreFinalDispID = Em.RDM_ID
WHERE IH.CoreFinalDispType = 'PART';

UPDATE IH
SET IH.CoreDispositionID = Em.HookID
--SELECT IH.*
FROM	[RDM_Core]	IH
JOIN	RDM_hook Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreDispositionID = Em.RDM_ID
WHERE IH.CoreDispositionType = 'HOOK';

UPDATE IH
SET IH.CoreFinalDispID = Em.HookID
--SELECT IH.*
FROM	[RDM_Core]	IH
JOIN	RDM_hook Em ON IH.RDM_FacilityID = Em.RDM_FacilityID AND IH.CoreFinalDispID = Em.HookUID --IH.CoreFinalDispID = Em.RDM_ID
WHERE IH.CoreFinalDispType = 'HOOK';

UPDATE IH
SET IH.CoreDispositionID = NULL
--SELECT IH.*
FROM	RDM_Core	IH
WHERE IH.CoreDispositionType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');

UPDATE IH
SET IH.CoreFinalDispID = NULL
--SELECT IH.*
FROM	RDM_Core	IH
WHERE IH.CoreFinalDispType IN ('LOST','STOLEN','DESTROYED','UNASSIGNED');

INSERT INTO RDMDB..RDM_MasterKeySystem ( SystemName,FacilityID,SystemTypeID, PinsizeID )
SELECT DISTINCT 'ORPHANED',FacilityID,0,0 FROM RDMDB..RDM_KeyMark WHERE SystemID IS NULL;

UPDATE KM
SET SystemID = MK.SystemID,KeyMarkLevelID=5
FROM RDMDB..RDM_KeyMark			KM
JOIN RDMDB..RDM_MasterKeySystem	MK ON KM.FacilityID = MK.FacilityID AND KM.SystemID IS NULL AND MK.SystemName = 'ORPHANED';


