﻿

DECLARE @DisableTriggers VARCHAR(MAX)

SELECT @DisableTriggers = (SELECT STUFF((SELECT ';'+'ALTER TABLE ['+OBJECT_NAME(parent_id)+'] DISABLE TRIGGER ['+name+']' FROM sys.triggers FOR XML PATH('')),1,0,''))

EXEC (@DisableTriggers);

---------ENUM Values
---- Role

--SET IDENTITY_INSERT [dbo].[Role] ON;
--INSERT INTO [Role]
--	(RoleID,Name,PwdExpiryInDays,ModifiedBy)
--SELECT 1 AS ID,'ADMIN' Name,90 PwdExpiryInDays,NULL AS ModifiedBy UNION ALL
--SELECT 2 AS ID,'DEFAULT USER' Name,90 PwdExpiryInDays,NULL AS ModifiedBy

--SET IDENTITY_INSERT [dbo].[Role] OFF;


----  KeyMark Level

--SET IDENTITY_INSERT [dbo].KeymarkLevel ON;
--INSERT INTO [dbo].KeymarkLevel (KeyMarkLevelID, KeyMarkLevel)
--SELECT KeyMarkLevelID,REPLACE(KeyMarkLevel,CHAR(9),'')
--FROM RDMDB..RDM_KeyMarkLevel

--SET IDENTITY_INSERT [dbo].KeymarkLevel OFF;

----  Mark

--SET IDENTITY_INSERT [dbo].Mark ON;
--INSERT INTO [dbo].Mark (MarkID, Mark)
--SELECT	MarkID,Mark
--FROM	RDMDB..RDM_Mark
--SET IDENTITY_INSERT [dbo].Mark OFF;

----  PinSize

--SET IDENTITY_INSERT [dbo].PinSize ON;
--INSERT INTO [dbo].PinSize (PinSizeID, PinSize)
--SELECT	PinSizeID,PinSize
--FROM RDMDB..RDM_PinSize
--SET IDENTITY_INSERT [dbo].PinSize OFF;

----  System Type

--SET IDENTITY_INSERT [dbo].SystemType ON;
--INSERT INTO [dbo].SystemType (SystemTypeID, SystemType)
--SELECT	SystemTypeID,SystemType
--FROM RDMDB..RDM_SystemType
--SET IDENTITY_INSERT [dbo].SystemType OFF;

----  Product Type

--SET IDENTITY_INSERT [dbo].ProductType ON;
--INSERT INTO [dbo].ProductType (ProductTypeID, ProductType)
--SELECT	ProductTypeID,ProductType
--FROM RDMDB..RDM_ProductType
--SET IDENTITY_INSERT [dbo].ProductType OFF;

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

-- Disable existing same locations

--UPDATE web
--SET web.IsDeleted = 1
--SELECT	web.*
--FROM	RDMDB..RDM_Facility	fac
--JOIN	Facility			web ON REPLACE(SUBSTRING(fac.FacilityName,PATINDEX('%[_]%',fac.FacilityName)+1,LEN(fac.FacilityName)),'_',' ') = web.FacilityName;


-- Location
SET IDENTITY_INSERT [dbo].Facility ON; 
INSERT INTO Facility
	(FacilityID, FacilityName, Path, Description, LastBackup, IsDeleted, ModifiedDate, Notes)	
SELECT	FacilityID, 
REPLACE(SUBSTRING(FACILITYNAME,PATINDEX('%[_]%',FACILITYNAME)+1,LEN(FACILITYNAME)),'_',' ') as FacilityName, 
Path, Description, LastBackup, IsDeleted, ModifiedDate, Notes
FROM	RDMDB..RDM_Facility	fac
SET IDENTITY_INSERT [dbo].Facility OFF;

/* --------------------------  MIGRATE Maintain List Values	--------------------------------------------------- Start */
	
	-- FacilityZone
		SET IDENTITY_INSERT [dbo].FacilityZone ON;
		INSERT INTO FacilityZone
			( FacilityZoneID, ZoneName, FacilityID )
		SELECT FacilityZoneID, ZoneName, FacilityID
		FROM	RDMDB..RDM_FacilityZone fzone
		SET IDENTITY_INSERT [dbo].FacilityZone OFF;

	-- DoorType
		SET IDENTITY_INSERT [dbo].[DoorType] ON;
		INSERT INTO [DoorType]
			( DoorTypeID, Type, FacilityID )
		SELECT	DoorTypeID, Type, FacilityID
		FROM	RDMDB..[RDM_DoorType] dtypes
		SET IDENTITY_INSERT [dbo].[DoorType] OFF;

	-- EmployeeType
		SET IDENTITY_INSERT [dbo].[EmpType] ON;
		INSERT INTO [EmpType]
			( EmpTypeID, Type, FacilityID )
		SELECT	EmpTypeID, Type, FacilityID
		FROM	RDMDB..[RDM_EmpType] etypes
		SET IDENTITY_INSERT [dbo].[EmpType] OFF;

	-- EmployeeTitle
		SET IDENTITY_INSERT [dbo].[EmpTitle] ON;
		INSERT INTO [EmpTitle]
			( EmpTitleID, Title, FacilityID )
		SELECT	EmpTitleID, Title, FacilityID
		FROM	RDMDB..[RDM_EmpTitle] eti
		SET IDENTITY_INSERT [dbo].[EmpTitle] OFF;

	-- Keyways
		SET IDENTITY_INSERT [dbo].[Keyway] ON;
		INSERT INTO [Keyway] 
			( KeywayID, KeywayName, FacilityID )
		SELECT	KeywayID, KeywayName, FacilityID
		FROM	RDMDB..[RDM_Keyway] kwy
		SET IDENTITY_INSERT [dbo].[Keyway] OFF;

	-- KeymarkStatus
		SET IDENTITY_INSERT [dbo].[KeyMarkStatus] ON;
		INSERT INTO [KeyMarkStatus]
			( KeymarkStatusID, Status, FacilityID )
		SELECT	KeymarkStatusID, Status, FacilityID
		FROM	RDMDB..[RDM_KeyMarkStatus] kms
		SET IDENTITY_INSERT [dbo].[KeyMarkStatus] OFF;

	-- ProductMfg

		SET IDENTITY_INSERT [dbo].ProductMfg ON;
		INSERT INTO ProductMfg
			( MfgID, MfgName )
		SELECT	MfgID, MfgName
		FROM	RDMDB..RDM_ProductMfg mf
		SET IDENTITY_INSERT [dbo].ProductMfg OFF;

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

	-- Building

		SET IDENTITY_INSERT [dbo].[Building] ON;
		INSERT INTO [dbo].[Building]
			( BuildingID, BuildingName, Description, FacilityZoneID, Notes, FacilityID, ModifiedDate)
		SELECT	BuildingID, BuildingName, Description, FacilityZoneID, Notes, FacilityID, ModifiedDate
		FROM	RDMDB..[RDM_Building] bldg
		SET IDENTITY_INSERT [dbo].[Building] OFF;

	--Department
		
		SET IDENTITY_INSERT [dbo].[department] ON;
		INSERT INTO [dbo].[department]
			( DeptID, DeptName, Description, Notes, FacilityID, ModifiedDate)
		SELECT	DeptID, DeptName, Description, Notes, FacilityID, ModifiedDate
		FROM	RDMDB..[RDM_Department] dept
		SET IDENTITY_INSERT [dbo].[department] OFF;

	--Cabinet

		SET IDENTITY_INSERT [dbo].[Cabinet] ON;
		INSERT INTO [dbo].[Cabinet]
			( CabinetID, CabinetName, CabinetDesription, FacilityID, Notes, ModifiedDate,IsDeleted)
		SELECT	CabinetID, CabinetName, CabinetDesription, FacilityID, Notes, ModifiedDate,0
		FROM	RDMDB..[RDM_Cabinet] cabn
		SET IDENTITY_INSERT [dbo].[Cabinet] OFF;
	
	--Hook
		
		SET IDENTITY_INSERT [dbo].[Hook] ON;
		INSERT INTO [dbo].[Hook]
			( HookID, HookUID, HookName, HookDescription, CabinetID, Notes, IsDeleted, DeletedDate, ModifiedDate)
		SELECT	HookID, HookUID, HookName, HookDescription, CabinetID, Notes, IsDeleted, DeletedDate, ModifiedDate
		FROM	RDMDB..[RDM_Hook] hks
		SET IDENTITY_INSERT [dbo].[Hook] OFF;

	--Door

		SET IDENTITY_INSERT [dbo].[Door] ON;
		INSERT INTO [dbo].[Door]
			( DoorID, DoorUID, DoorTypeID, DoorNumber, Area, Description, BuildingID, IsDeleted, DeletedDate, Notes, FacilityID, ModifiedDate)
		SELECT	DoorID, DoorUID, DoorTypeID, DoorNumber, Area, Description, BuildingID, IsDeleted, DeletedDate, Notes, FacilityID, ModifiedDate
		FROM	RDMDB..[RDM_Door] drs
		SET IDENTITY_INSERT [dbo].[Door] OFF;

	--Employee  (EmployeeUID,Notes)
	
	print 'inserting employees...';
	select count(*) from RDMDB..[RDM_Employee] emp;



		EXEC usp_Connection 1;

		SET IDENTITY_INSERT [dbo].[Employee] ON;
		INSERT INTO [dbo].[Employee]
			( EmployeeId, EmployeeUID, ID, LastName, FirstName, MiddleInitial, Email, Phone, FullName, Misc, Description, EmpTypeID, EmpTitleID, DeptID, IsDeleted, DeletedDate, Notes, FacilityID, ModifiedDate)
		SELECT	EmployeeId, EmployeeUID,
		 [ID]				= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),ID)
		,[LastName]			= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),LastName)
		,[FirstName]		= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),FirstName)
		,[MiddleInitial]	= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),MiddleInitial)
		,[Email]			= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),Email)
		,[Phone]			= ENCRYPTBYKEY(KEY_GUID('Aes128Key'),Phone)
		,[FullName]			= ENCRYPTBYKEY(KEY_GUID('Aes128Key'), [dbo].[udf_scalar_GetFullName](FirstName, MiddleInitial, LastName))
		, Misc, Description, EmpTypeID, EmpTitleID, DeptID, IsDeleted, DeletedDate, Notes, FacilityID, ModifiedDate
		FROM	RDMDB..[RDM_Employee] emp
		SET IDENTITY_INSERT [dbo].[Employee] OFF;

		EXEC usp_Connection 0;

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

		SET IDENTITY_INSERT [dbo].Product ON;
		INSERT INTO Product
			( ProductID, ProductName, ProductDescription, MfgID, ProductTypeID, NumCores, Notes, ModifiedDate)
		SELECT	ProductID, ProductName, ProductDescription, MfgID, ProductTypeID, NumCores, Notes, ModifiedDate
		FROM		RDMDB..RDM_Product	prd
		SET IDENTITY_INSERT [dbo].Product OFF;

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

		SET IDENTITY_INSERT [dbo].Part ON;
		INSERT INTO Part
			( PartID, SerialNo, PartDescription, ProductID, Notes, CreatedDate, Serialize, DispositionID, DispositionUID, DispositionType, OosDate, FacilityID)
		SELECT	PartID, SerialNo, PartDescription, ProductID, Notes, CreatedDate, Serialize, DispositionID, DispositionUID, DispositionType, OosDate, FacilityID
		FROM		RDMDB..RDM_Part	pt
		SET IDENTITY_INSERT [dbo].Part OFF;

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

		SET IDENTITY_INSERT [dbo].MasterKeySystem ON;
		INSERT INTO MasterKeySystem
			( SystemID, SystemName, KeywayID, SystemTypeID, PinsizeID, MarkID, Keystamp, Notes, GM_KeyMarkID, CT_KeyMarkID, FacilityID)
		SELECT	SystemID, SystemName, KeywayID, SystemTypeID, PinsizeID, MarkID, Keystamp, Notes, NULL, NULL, FacilityID
		FROM		RDMDB..RDM_MasterKeySystem	ss
		SET IDENTITY_INSERT [dbo].MasterKeySystem OFF;


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

		EXEC usp_Connection 1;

		SET IDENTITY_INSERT [dbo].KeyMark ON;
		INSERT INTO KeyMark
			( KeyMarkID, KeyMarkName, KeyMarkStatusID, SystemID, KeyCut, KeyMarkLevelID, ParentID, Notes, FacilityID, ModifiedDate)
		SELECT	KeyMarkID, KeyMarkName, KeyMarkStatusID, SystemID, ENCRYPTBYKEY(KEY_GUID('Aes128Key'),KeyCut), KeyMarkLevelID, ParentID, Notes, FacilityID, ModifiedDate
		FROM		RDMDB..RDM_KeyMark	km
		SET IDENTITY_INSERT [dbo].KeyMark OFF;

		EXEC usp_Connection 0;

	-- Set GM key, Control key for MasterKey system

		UPDATE mk		
		SET		 mk.GM_KeyMarkID = rmk.GM_KeyMarkID
				,mk.CT_KeyMarkID = rmk.CT_KeyMarkID
		FROM		MasterKeySystem mk
		JOIN	RDMDB..RDM_MasterKeySystem rmk ON mk.SystemID = rmk.SystemID

	-- Set GM as parentID to Control keymark (Commented on 18Jun 2018 for fixing the migration issue when having multiple CONTROL keymarks for GM)

	-- 	UPDATE mk		
	-- 	SET		 mk.ParentID = rmk.GM_KeyMarkID
	-- 	FROM		KeyMark mk
	-- 	JOIN	(SELECT CT_KeyMarkID,GM_KeyMarkID FROM RDMDB..RDM_MasterKeySystem ) rmk ON mk.KeyMarkID = rmk.CT_KeyMarkID

	-- Coremarks(Notes has to be imported)

		SET IDENTITY_INSERT [dbo].CoreMark ON;
		INSERT INTO CoreMark
			( CoreMarkID, CoreMarkName, KeyMarkID, Notes, FacilityID, OP_By, ModifiedDate)
		SELECT	CoreMarkID, CoreMarkName, KeyMarkID, Notes, FacilityID, OP_By, ModifiedDate
		FROM		RDMDB..RDM_CoreMark cm
		SET IDENTITY_INSERT [dbo].CoreMark OFF;

	-- Control_Sel

		SET IDENTITY_INSERT [dbo].CoreMark_sel ON;
		INSERT INTO CoreMark_sel
			( SelectivityID,CoreMarkID, KeyMarkID, IsStandardSelectivity )
		SELECT	SelectivityID,CoreMarkID, KeyMarkID, 1
		FROM		RDMDB..RDM_CoreMark_sel cm
		SET IDENTITY_INSERT [dbo].CoreMark_sel OFF;


	-- Keyrings

		SET IDENTITY_INSERT [dbo].KeyRings ON;
		INSERT INTO KeyRings
			( KeyRingID, KeyRingUID, KeyRing, Description, DispositionID, DispositionUID, DispositionType, Notes, KeyringOosDate, FacilityID)
		SELECT	KeyRingID, KeyRingUID, KeyRing, Description, DispositionID, DispositionUID, DispositionType, Notes, KeyringOosDate, FacilityID
		FROM	RDMDB..RDM_KeyRings kr
		SET IDENTITY_INSERT [dbo].KeyRings OFF;

	-- Key (Notes has to be imported)

		SET IDENTITY_INSERT [dbo].[Key] ON;
		INSERT INTO [Key]
			( KeyID, KeyUID, KeySerialNo, KeyDescription, KeyMarkID, ProductID, Notes, KeySerialize, KeyDispositonID, KeyDispositionUID, KeyDispositionType, KeyOosDate, KeyFinalDispID, KeyFinalDispType, FacilityID, ModifiedDate)
		SELECT	KeyID, KeyUID, KeySerialNo, KeyDescription, KeyMarkID, ProductID, Notes, KeySerialize, KeyDispositonID, KeyDispositionUID, KeyDispositionType, KeyOosDate, KeyFinalDispID, KeyFinalDispType, FacilityID, ModifiedDate
		FROM	RDMDB..[RDM_Key] ky
		SET IDENTITY_INSERT [dbo].[Key] OFF;


	-- Core 

		SET IDENTITY_INSERT [dbo].Core ON;
		INSERT INTO Core
			( CoreID, CoreUID, CoreSerialNo, CoreMarkID, ProductID, Notes, CoreSerialize, CoreDispositionID, CoreDispositionUID, CoreDispositionType, CoreOosDate, CoreFinalDispID, CoreFinalDispType, ModifiedDate, FacilityID)
		SELECT	CoreID, CoreUID, CoreSerialNo, CoreMarkID, ProductID, Notes, CoreSerialize, CoreDispositionID, CoreDispositionUID, CoreDispositionType, CoreOosDate, CoreFinalDispID, CoreFinalDispType, ModifiedDate, RDM_FacilityID
		FROM	RDMDB..RDM_Core cr
		SET IDENTITY_INSERT [dbo].Core OFF;

	-- Users (Password to be encrypted from front end)

	DECLARE @PwdExpire DATE
	SELECT  @PwdExpire = DATEADD(DD,-(PwdExpiryInDays+1),GETDATE()) FROM [Role] WHERE RoleID = 1;

	SET IDENTITY_INSERT [dbo].[User] ON;
	INSERT INTO [User]
		( IsMigratedUser,UserID,UserLogin, Email, HashedPassword, Description, Notes, Salt, IsLocked, RoleID, GeneralPermissionId, UpdatePermissionId, TransactionPermissionId, ModifiedDate, EmployeeID, PwdChangedDate)
	SELECT	1,UserID,REPLACE(LTRIM(RTRIM(UserLogin)),' ',''), Email, EncryptedPassword, Description, Notes, Salt, IsLocked, RoleID, GeneralPermissionId, UpdatePermissionId, TransactionPermissionId, ModifiedDate, EmployeeID, @PwdExpire
	FROM RDMDB..RDM_User
	SET IDENTITY_INSERT [dbo].[User] OFF;

	-- User Permissions

	SET IDENTITY_INSERT [dbo].[UserPermissions] ON;
	INSERT INTO [UserPermissions]
		( PERMISSIONID, USERID, CLONED_FROM,  PERMIT_VIEW_MASTERKEY, PERMIT_VIEW_DEPARTMENT, PERMIT_VIEW_EMPLOYEE, PERMIT_VIEW_KEY, PERMIT_VIEW_BUILDING, PERMIT_VIEW_DOOR, PERMIT_VIEW_CORE, PERMIT_VIEW_CABINET, PERMIT_VIEW_HOOK, PERMIT_VIEW_KEYRING, PERMIT_VIEW_PRODUCT, PERMIT_VIEW_PART, PERMIT_VIEW_OOS, PERMIT_VIEW_UNASSIGNED, PERMIT_VIEW_REPORTS,  PERMIT_VIEW_FACILITY,  PERMIT_UPDATE_MASTERKEY, PERMIT_UPDATE_DEPARTMENT, PERMIT_UPDATE_EMPLOYEE, PERMIT_UPDATE_KEY, PERMIT_UPDATE_BUILDING, PERMIT_UPDATE_DOOR, PERMIT_UPDATE_CORE, PERMIT_UPDATE_CABINET, PERMIT_UPDATE_HOOK, PERMIT_UPDATE_KEYRING, PERMIT_UPDATE_PRODUCT, PERMIT_UPDATE_PART, PERMIT_UPDATE_OOS, PERMIT_UPDATE_UNASSIGNED, PERMIT_UPDATE_REPORTS, PERMIT_DELETE_MASTERKEY, PERMIT_DELETE_DEPARTMENT, PERMIT_DELETE_EMPLOYEE, PERMIT_DELETE_KEY, PERMIT_DELETE_BUILDING, PERMIT_DELETE_DOOR, PERMIT_DELETE_CORE, PERMIT_DELETE_CABINET, PERMIT_DELETE_HOOK, PERMIT_DELETE_KEYRING, PERMIT_DELETE_PRODUCT, PERMIT_DELETE_PART, PERMIT_DELETE_OOS, PERMIT_DELETE_UNASSIGNED, PERMIT_DELETE_REPORTS, PERMIT_TRANS_EMP_ISSUENEWKEY, PERMIT_TRANS_EMP_RETURNKEY, PERMIT_TRANS_EMP_ISSUEEXISTINGKEY, PERMIT_TRANS_EMP_TRANSFERKEY, PERMIT_TRANS_HOOK_TRANSFERKEY, PERMIT_TRANS_OOS_TRANSFERKEY, PERMIT_TRANS_UNA_TRANSFERKEY, PERMIT_TRANS_KEYRING_ATTACHNEWKEY, PERMIT_TRANS_KEYRING_DETACHKEY, PERMIT_TRANS_KEYRING_ATTACHEXISTINGKEY, PERMIT_TRANS_DOOR_INSTALLNEWCORE, PERMIT_TRANS_DOOR_REMOVECORE, PERMIT_TRANS_DOOR_INSTALLEXISTINGCORE, PERMIT_TRANS_PART_INSTALLNEWCORE, PERMIT_TRANS_PART_REMOVECORE, PERMIT_TRANS_PART_INSTALLEXISTINGCORE, PERMIT_TRANS_EMP_TRANSFERCORE, PERMIT_TRANS_DOOR_TRANSFERCORE, PERMIT_TRANS_HOOK_TRANSFERCORE, PERMIT_TRANS_OOS_TRANSFERCORE, PERMIT_TRANS_UNA_TRANSFERCORE, PERMIT_TRANS_EMP_ISSUENEWCORE, PERMIT_TRANS_EMP_RETURNCORE, PERMIT_TRANS_EMP_ISSUEEXISTINGCORE, PERMIT_TRANS_EMP_ISSUENEWKEYRING, PERMIT_TRANS_EMP_RETURNKEYRING, PERMIT_TRANS_EMP_ISSUEEXISTINGKEYRING, PERMIT_TRANS_EMP_TRANSFERKEYRING, PERMIT_TRANS_HOOK_TRANSFERKEYRING, PERMIT_TRANS_OOS_TRANSFERKEYRING, PERMIT_TRANS_UNA_TRANSFERKEYRING, PERMIT_TRANS_DOOR_INSTALLNEWPART, PERMIT_TRANS_DOOR_REMOVEPART, PERMIT_TRANS_DOOR_INSTALLEXISTINGPART, PERMIT_TRANS_EMP_TRANSFERPART, PERMIT_TRANS_HOOK_TRANSFERPART, PERMIT_TRANS_OOS_TRANSFERPART, PERMIT_TRANS_UNA_TRANSFERPART, PERMIT_TRANS_EMP_ISSUENEWPART, PERMIT_TRANS_EMP_RETURNPART, PERMIT_TRANS_EMP_ISSUEEXISTINGPART, PERMIT_TRANS_HOOK_ISSUENEWKEY, PERMIT_TRANS_HOOK_ISSUENEWKEYRING, PERMIT_TRANS_HOOK_ISSUENEWCORE, PERMIT_TRANS_HOOK_ISSUENEWPART, PERMIT_VIEW_CODES)
	SELECT	PERMISSIONID, USERID, CLONED_FROM ,  PERMIT_VIEW_MASTERKEY, PERMIT_VIEW_DEPARTMENT, PERMIT_VIEW_EMPLOYEE, PERMIT_VIEW_KEY, PERMIT_VIEW_BUILDING, PERMIT_VIEW_DOOR, PERMIT_VIEW_CORE, PERMIT_VIEW_CABINET, PERMIT_VIEW_HOOK, PERMIT_VIEW_KEYRING, PERMIT_VIEW_PRODUCT, PERMIT_VIEW_PART, PERMIT_VIEW_OOS, PERMIT_VIEW_UNASSIGNED, PERMIT_VIEW_REPORTS,  PERMIT_VIEW_FACILITY,  PERMIT_UPDATE_MASTERKEY, PERMIT_UPDATE_DEPARTMENT, PERMIT_UPDATE_EMPLOYEE, PERMIT_UPDATE_KEY, PERMIT_UPDATE_BUILDING, PERMIT_UPDATE_DOOR, PERMIT_UPDATE_CORE, PERMIT_UPDATE_CABINET, PERMIT_UPDATE_HOOK, PERMIT_UPDATE_KEYRING, PERMIT_UPDATE_PRODUCT, PERMIT_UPDATE_PART, PERMIT_UPDATE_OOS, PERMIT_UPDATE_UNASSIGNED, PERMIT_UPDATE_REPORTS, PERMIT_DELETE_MASTERKEY, PERMIT_DELETE_DEPARTMENT, PERMIT_DELETE_EMPLOYEE, PERMIT_DELETE_KEY, PERMIT_DELETE_BUILDING, PERMIT_DELETE_DOOR, PERMIT_DELETE_CORE, PERMIT_DELETE_CABINET, PERMIT_DELETE_HOOK, PERMIT_DELETE_KEYRING, PERMIT_DELETE_PRODUCT, PERMIT_DELETE_PART, PERMIT_DELETE_OOS, PERMIT_DELETE_UNASSIGNED, PERMIT_DELETE_REPORTS, PERMIT_TRANS_EMP_ISSUENEWKEY, PERMIT_TRANS_EMP_RETURNKEY, PERMIT_TRANS_EMP_ISSUEEXISTINGKEY, PERMIT_TRANS_EMP_TRANSFERKEY, PERMIT_TRANS_HOOK_TRANSFERKEY, PERMIT_TRANS_OOS_TRANSFERKEY, PERMIT_TRANS_UNA_TRANSFERKEY, PERMIT_TRANS_KEYRING_ATTACHNEWKEY, PERMIT_TRANS_KEYRING_DETACHKEY, PERMIT_TRANS_KEYRING_ATTACHEXISTINGKEY, PERMIT_TRANS_DOOR_INSTALLNEWCORE, PERMIT_TRANS_DOOR_REMOVECORE, PERMIT_TRANS_DOOR_INSTALLEXISTINGCORE, PERMIT_TRANS_PART_INSTALLNEWCORE, PERMIT_TRANS_PART_REMOVECORE, PERMIT_TRANS_PART_INSTALLEXISTINGCORE, PERMIT_TRANS_EMP_TRANSFERCORE, PERMIT_TRANS_DOOR_TRANSFERCORE, PERMIT_TRANS_HOOK_TRANSFERCORE, PERMIT_TRANS_OOS_TRANSFERCORE, PERMIT_TRANS_UNA_TRANSFERCORE, PERMIT_TRANS_EMP_ISSUENEWCORE, PERMIT_TRANS_EMP_RETURNCORE, PERMIT_TRANS_EMP_ISSUEEXISTINGCORE, PERMIT_TRANS_EMP_ISSUENEWKEYRING, PERMIT_TRANS_EMP_RETURNKEYRING, PERMIT_TRANS_EMP_ISSUEEXISTINGKEYRING, PERMIT_TRANS_EMP_TRANSFERKEYRING, PERMIT_TRANS_HOOK_TRANSFERKEYRING, PERMIT_TRANS_OOS_TRANSFERKEYRING, PERMIT_TRANS_UNA_TRANSFERKEYRING, PERMIT_TRANS_DOOR_INSTALLNEWPART, PERMIT_TRANS_DOOR_REMOVEPART, PERMIT_TRANS_DOOR_INSTALLEXISTINGPART, PERMIT_TRANS_EMP_TRANSFERPART, PERMIT_TRANS_HOOK_TRANSFERPART, PERMIT_TRANS_OOS_TRANSFERPART, PERMIT_TRANS_UNA_TRANSFERPART, PERMIT_TRANS_EMP_ISSUENEWPART, PERMIT_TRANS_EMP_RETURNPART, PERMIT_TRANS_EMP_ISSUEEXISTINGPART, PERMIT_TRANS_HOOK_ISSUENEWKEY, PERMIT_TRANS_HOOK_ISSUENEWKEYRING, PERMIT_TRANS_HOOK_ISSUENEWCORE, PERMIT_TRANS_HOOK_ISSUENEWPART, PERMIT_VIEW_CODES
	FROM RDMDB..RDM_UserPermissions
	SET IDENTITY_INSERT [dbo].[UserPermissions] OFF;

	-- User Facilities

	SET IDENTITY_INSERT [dbo].[UserFacilities] ON;
	INSERT INTO [UserFacilities]
		( ID, UserID, FacilityID, IsDefault, BuildingDefault, CabinetDefault, CoreDefault, CoremarkDefault, DepartmentDefault, OutOfServiceDefault, DoorDefault, EmployeeDefault, FacilityDefault, HookDefault, KeyDefault, KeymarkDefault, KeyringDefault, PartDefault, MasterkeyDefault, ProductDefault, ReportDefault, SiteDefault, UnassignedDefault, UserDefault)
	SELECT	ID, UserID, FacilityID, IsDefault, BuildingDefault, CabinetDefault, CoreDefault, CoremarkDefault, DepartmentDefault, OutOfServiceDefault, DoorDefault, EmployeeDefault, FacilityDefault, HookDefault, KeyDefault, KeymarkDefault, KeyringDefault, PartDefault, MasterkeyDefault, ProductDefault, ReportDefault, SiteDefault, UnassignedDefault, UserDefault
	FROM RDMDB..RDM_UserFacilities
	SET IDENTITY_INSERT [dbo].[UserFacilities] OFF;

	-- Dashboard Settings

	SET IDENTITY_INSERT [dbo].[DashboardSettings] ON;
	INSERT INTO [DashboardSettings]
		( ID, TileName, Position, Visibility, UserID)
	SELECT	ID, TileName, Position, Visibility, UserID
	FROM RDMDB..RDM_DashboardSettings
	SET IDENTITY_INSERT [dbo].[DashboardSettings] OFF;

	-- ItemHistory ( change Identity value)

		SET IDENTITY_INSERT [dbo].ItemHistory ON;
		INSERT INTO ItemHistory
			( HistoryID, ItemID, ToID, UserID, DatetimeWhen, DateReturn, DateDue, Comment, ItemParentID, ItemParentType, ToParentID, ToParentType, FacilityID )
		SELECT	HistoryID, ItemID, ToID, UserID, DatetimeWhen, DateReturn, DateDue, Comment, ItemParentID, ItemParentType, ToParentID, ToParentType, FacilityID
		FROM		RDMDB..RDM_ItemHistory hst
		SET IDENTITY_INSERT [dbo].ItemHistory OFF;
				

	INSERT  INTO [dbo].Facility_Configuration
				( FacilityID, CoreSerial, KeySerial, LockSerial )
		SELECT FacilityID, 2, 1, 2 
		FROM Facility
		WHERE FacilityID not in (SELECT FacilityID FROM Facility_Configuration);
		
	-- For calculating the stacking for all migrated selective coremarks.

		DECLARE @Stack_SystemID INT,
				@Stack_Max INT
		
		CREATE TABLE #Stack_SystemIDs (SystemID INT,SNo INT IDENTITY(1,1));

		INSERT INTO #Stack_SystemIDs ( SystemID )
		SELECT FacilityID FROM RDMDB..RDM_Facility
		--SELECT SystemID FROM MasterKeySystem s JOIN RDMDB..RDM_Facility f ON s.FacilityID = f.FacilityID
		
		SELECT @Stack_Max = MAX(SNo) FROM #Stack_SystemIDs	

		WHILE (@Stack_Max >=1)
		BEGIN

			SELECT @Stack_SystemID = SystemID FROM #Stack_SystemIDs WHERE SNo = @Stack_Max

			EXEC usp_Coremark_SelectivityStacking_Migration @Stack_SystemID

			SET @Stack_Max = @Stack_Max - 1

		END;
		

		-- For Create Coremarks for Migrated Data - Issue#140

		INSERT INTO CoreMark (KeyMarkID,CoreMarkName,FacilityID,ModifiedDate)
		SELECT KeyMarkID,KeyMarkName,s.FacilityID,GETDATE() FROM KEYMARK s  JOIN RDMDB..RDM_Facility f ON s.FacilityID = f.FacilityID
		  where s.KeyMarkLevelID NOT IN (0,12) and  s.KeyMarkID NOT IN (SELECT KeyMarkID from CoreMark where KeyMarkID is not null);

		-- To insert new Keymark for Selective coremarks
	EXEC usp_Connection 1;

	DECLARE @KSystemID VARCHAR(255),
			@SNo INT = 1,
			@Max INT,
			@MaxKeymarkID VARCHAR(10),
			@SQL_TempTable VARCHAR(500)
		
	CREATE TABLE #KeyNames (Name INT,SNo INT IDENTITY(1,1));


	INSERT INTO #KeyNames ( Name )
	SELECT SystemID FROM MasterKeySystem s JOIN RDMDB..RDM_Facility f ON s.FacilityID = f.FacilityID --WHERE s.SystemID > 1
		
	SELECT @Max = MAX(SNo) FROM #KeyNames
	SELECT @MaxKeymarkID = MAX(KeyMarkID) FROM KeyMark

	CREATE TABLE #Keymarks ( OldKeyMarkID INT , NewKeyMarkID INT IDENTITY(1,1) , CoremarkID INT);
	SET IDENTITY_INSERT #Keymarks ON;
	INSERT INTO #Keymarks (NewKeyMarkID)
	VALUES (@MaxKeymarkID)
	SET IDENTITY_INSERT #Keymarks OFF;

	DELETE FROM #Keymarks

	WHILE (@Max >=1)
	BEGIN

		SELECT @KSystemID = Name FROM #KeyNames WHERE SNo = @Max
		SET @Max = @Max - 1
	
		 INSERT INTO #Keymarks (OldKeyMarkID,CoremarkID)
		SELECT DISTINCT CM.KeyMarkID,CM.CoreMarkID
		FROM	KeyMark		KM
		JOIN	CoreMark	CM ON CM.KeyMarkID = KM.KeyMarkID
		JOIN	RDMDB..RDM_Facility f ON KM.FacilityID = f.FacilityID
		WHERE KM.KeyMarkID IN (SELECT DISTINCT CM.KeyMarkID FROM CoreMark CMI JOIN CoreMark_Sel SEL ON CMI.CoreMarkID = SEL.CoreMarkID AND CM.CoreMarkID = CMI.CoreMarkID )
		AND KM.SystemID = @KSystemID


	END;

	SET IDENTITY_INSERT Keymark ON;
	INSERT INTO KeyMark (KeyMarkID,KeyMarkName, KeyMarkStatusID, SystemID, KeyCut, KeyMarkLevelID, ParentID, Notes, FacilityID, ModifiedDate, ModifiedBy)
	SELECT DISTINCT CM.NewKeyMarkID,CC.CoreMarkName, KeyMarkStatusID, SystemID, ENCRYPTBYKEY(KEY_GUID('Aes128Key'),CONVERT(VARCHAR(50),DECRYPTBYKEY(KeyCut))), KeyMarkLevelID, ParentID, KM.Notes, KM.FacilityID, KM.ModifiedDate, KM.ModifiedBy
	FROM	KeyMark		KM
	JOIN	#Keymarks	CM ON CM.OldKeyMarkID = KM.KeyMarkID
	JOIN	CoreMark	CC ON CM.CoremarkID = CC.CoreMarkID
	SET IDENTITY_INSERT Keymark OFF;

	UPDATE CM
	SET CM.KeyMarkID = KM.NewKeyMarkID
	FROM	CoreMark	CM
	JOIN	#Keymarks	KM ON CM.CoreMarkID = KM.CoremarkID


	EXEC usp_Connection 0;

	DROP TABLE #KeyNames;
	DROP TABLE #Keymarks;


		-- Load Data into KeymarkSorting table

		TRUNCATE TABLE KeymarkSort;

		INSERT INTO KeymarkSort (KeymarkID,FacilityID,KeymarkName,SystemName,SortOrder)
		SELECT KeyMarkID,FacilityID,SplitKeyMarkName,SystemName,dbo.udf_scalar_GetAlphanumericSortValue(SplitKeyMarkName,1) FROM
			(
			SELECT
				KeyMarkID,KM.FacilityID, SplitKeyMarkName = CASE
												  WHEN KM.KeyMarkLevelID = 0 and ParentID is null and KM.KeyMarkID = CT_KeyMarkID THEN KM.KeyMarkName
												  WHEN KM.KeyMarkLevelID = 0 and ( ParentID is null OR KM.ParentID = GM_KeyMarkID )THEN 'C'''+ KM.KeyMarkName			  
												  WHEN KM.KeyMarkLevelID = 0 THEN (select CASE WHEN KeyMarkLevelID > 5 THEN Replace(KeyMarkLevel,'M','C')+ '''' + KM.KeyMarkName ELSE KM.KeyMarkName END from vw_Keymark where KeyMarkID = KM.ParentID)
												  ELSE (CASE when KM.KeyMarkLevelID IN (6,7,8,9,10,11,13) THEN (select KeyMarkLevel + '''' + KeyMarkName) ELSE KeyMarkName END)
											  END,
				KeyMarkName, ParentID, MA.SystemName

			FROM		KeyMark			KM WITH (NOLOCK) 
			LEFT JOIN	MasterKeySystem	MA WITH (NOLOCK) ON KM.SystemID = MA.SystemID
			LEFT JOIN	KeyMarkLevel	KL WITH (NOLOCK) ON KM.KeyMarkLevelID = KL.KeyMarkLevelID
	
			) KM;
		

		-- Calculate PhantomKeys for Selectivity records

		--EXEC usp_Connection 1
		
		--CREATE TABLE #Phantom ( CoremarkID INT,SystemID INT,KeymarkID INT )
		--CREATE TABLE #CoremarkIDs ( ID INT IDENTITY(1,1),CoremarkID INT )

		--INSERT INTO #CoremarkIDs (CoremarkID)
		--SELECT DISTINCT CoremarkID FROM Coremark_Sel


		--DECLARE @Max		INT
		--DECLARE @CoremarkID_P INT
		--SELECT @Max = MAX(ID) FROM #CoremarkIDs

		--WHILE (@Max >=1)
		--BEGIN

		--	SELECT @CoremarkID_P = CoremarkID FROM #CoremarkIDs WHERE ID = @Max
		--	INSERT INTO #Phantom
		--	EXEC usp_Coremark_Get_PhantomKeys_Load @CoremarkID_P

		--	SET @Max = @Max - 1

		--END

		--INSERT INTO PhantomKeys (CoremarkID,SystemID,KeymarkID)
		--SELECT CoremarkID ,SystemID ,KeymarkID
		--FROM #Phantom

		--DROP TABLE #Phantom
		--DROP TABLE #CoremarkIDs

		--EXEC usp_Connection

		-- Separating the actual serial number from the key

		UPDATE KE
		SET	KE.KeySerialNo = STUFF(KE.KeySerialNo,1,LEN(ME.KeyMarkName),'')
		FROM	[Key]	KE
		JOIN	(
					SELECT	KY.KeyID,KM.KeyMarkName
					FROM	[Key]	KY
					JOIN	KeyMark	KM ON KY.KeyMarkID = KM.KeyMarkID
				) ME ON KE.KeyID = ME.KeyID
		WHERE LEFT(KE.KeySerialNo,LEN(ME.KeyMarkName)) = ME.KeyMarkName;

		UPDATE [Key]
		SET KeySerialNo = STUFF(KeySerialNo,1,1,'')
		WHERE LEFT(KeySerialNo,1) = '-';

		-- Separating the actual serial number from the core

		UPDATE KE
		SET	KE.CoreSerialNo = STUFF(KE.CoreSerialNo,1,LEN(ME.CoreMarkName),'')
		FROM	Core	KE
		JOIN	(
					SELECT	KY.CoreID,KM.CoreMarkName
					FROM	Core		KY
					JOIN	CoreMark	KM ON KY.CoreMarkID = KM.CoreMarkID
				) ME ON KE.CoreID = ME.CoreID
		WHERE LEFT(KE.CoreSerialNo,LEN(ME.CoreMarkName)) = ME.CoreMarkName;

		UPDATE Core
		SET CoreSerialNo = STUFF(CoreSerialNo,1,1,'')
		WHERE LEFT(CoreSerialNo,1) = '-';

		-- Separating the actual serial number from the part

		UPDATE KE
		SET	KE.SerialNo = STUFF(KE.SerialNo,1,LEN(ME.ProductName),'')
		FROM	Part	KE
		JOIN	(
					SELECT	KY.PartID,KM.ProductName
					FROM	Part		KY
					JOIN	Product		KM ON KY.ProductID = KM.ProductID
				) ME ON KE.PartID = ME.PartID
		WHERE LEFT(KE.SerialNo,LEN(ME.ProductName)) = ME.ProductName;

		UPDATE Part
		SET SerialNo = STUFF(LTRIM(SerialNo),1,1,'')
		WHERE LEFT(LTRIM(SerialNo),1) = '-';


		-- Load Data into CoremarkSorting table
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoremarkSort]') AND type in (N'U'))
		BEGIN

			TRUNCATE TABLE CoremarkSort;
		
			INSERT CoremarkSort(CoremarkID,CoremarkName,FacilityID,KeymarkID,LevelID,StatusID,SysID)
			SELECT
					 CM.CoreMarkID
					,CASE WHEN KM.KeyMarkLevelID IN (6,7,8,9,10,11,13) THEN KL.KeyMarkLevel + '''' + CM.CoremarkName ELSE CM.CoremarkName END AS CoremarkName
					,CM.FacilityID
					,CM.KeyMarkID
					,KM.KeyMarkLevelID
					,KM.KeyMarkStatusID		
					,KM.SystemID
				FROM		CoreMark		CM WITH (NOLOCK)
				LEFT JOIN		KeyMark			KM WITH (NOLOCK) ON CM.KeyMarkID = KM.KeyMarkID
				LEFT JOIN	KeyMarkLevel	KL WITH (NOLOCK) ON KM.KeyMarkLevelID = KL.KeyMarkLevelID;

			UPDATE CoremarkSort SET SortOrder = dbo.udf_scalar_GetAlphanumericSortValue(CoremarkName,1);

		END;
	
DECLARE @EnableTriggers VARCHAR(MAX)

SELECT @EnableTriggers = (SELECT STUFF((SELECT ';'+'ALTER TABLE ['+OBJECT_NAME(parent_id)+'] ENABLE TRIGGER ['+name+']' FROM sys.triggers FOR XML PATH('')),1,0,''))

EXEC (@EnableTriggers);
