ALTER PROCEDURE [dbo].[ca_sp_DataMigration] 
	@SourceServer nvarchar(150),
	@sourceDatabase nvarchar(150),
	@LiveEventDatabase nvarchar(150)
	
AS
BEGIN

DECLARE @sourceDB varchar(150), @OperatorID varchar(50), @i int=1, @count int
SET @sourceDB = @sourceDatabase
declare @PartitionID uniqueIdentifier
DECLARE @Query NVARCHAR(MAX)
SET NOCOUNT ON;


select top 1 @PartitionID = PartitionID from Partition WHERE PartitionName='Admin Partition'
DELETE FROM DevicePartitions
-- First Delete From MCom and Com

----============ MCom and Com Data Migration ========================
DELETE FROM [dbo].[MCom]
DELETE FROM [dbo].[Com]
Exec ('INSERT INTO [dbo].[MCom]
           ([HCSNo],[Description],[UNCName],[LastUpdated]
		   ,[caObjectID],[LastOperator],[LastWorkStation])
		SELECT [Cluster]+1 AS [HCSNo],[Description],''''AS [UNCName]
				,ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],newid() [caObjectID],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
				,UPPER(Convert(char(50), SERVERPROPERTY (''machinename''))) AS [LastWorkStation]
		FROM ['+@sourceServer+'].['+@sourceDatabase+'].[dbo].[MCom]')
		
EXEC ('UPDATE MCom SET UNCName= (SELECT uncname FROM ['+@sourceServer+'].['+@sourceDatabase+'].[dbo].[Mail] WHERE netstationid+1=MCom.HCSNo and Device=0)')
Exec ('INSERT INTO [dbo].[Com]([HCSNo],[ComPort],[Baud],[Type],[Pwd],[Passwrd],[IPAddress],[IPPort]
    ,[BinaryMode],[NoPolling],[ZoneId],[MACAddress],[RFChannel],[RFGroup]
    ,[SyncWordUpper],[SyncWordLower],[UseDHCP],[DHCPName],[SubNetMask]
	,[WirelessMode],[NetworkType],[WifiChannelNo],[SecurityType]
    ,[Authentication],[GatewayEncryption],[KeyType],[Key1Value],[SSIDName]
    ,[GatewayIPAddress],[LastUpdated],[caObjectID],[LastOperator],[LastWorkStation])
 SELECT [Cluster]+1 AS [HCSNo],[ComPort],CASE WHEN (Type=3 OR Type=10) THEN 0 ELSE [Baud] END [Baud]
	,[Type],[Pwd],[Passwrd],[IPAddress],[SvrPort],[BinaryMode],0 AS [NoPolling],[ZoneId],[MACAddress],[RFChannel],[RFGroup]
    ,[SyncWordUpper],[SyncWordLower],[UseDHCP],[DHCPName],[SubNetMask],[WirelessMode],[NetworkType],[WifiChannelNo],[SecurityType]
    ,[Authentication],[GatewayEncryption],[KeyType],[Key1Value],[SSIDName],[GatewayIPAddress],ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],newid() AS [caObjectID],
	''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator],UPPER(Convert(char(50), SERVERPROPERTY (''machinename''))) AS [LastWorkStation]
  FROM ['+@sourceServer+'].['+@sourceDatabase+'].[dbo].[Com] WHERE Type>0 OR Comport IN (SELECT [ComPort] from ['+@sourceServer+'].['+@sourceDatabase+'].dbo.Panel)')

exec ('insert into [dbo].[DevicePartitions]   Select '''+@PartitionID+''',caobjectID from MCom')

----============ Panel Data Migration ========================
DELETE FROM PanelTimeSchdule
DELETE FROM PanelHoliday
DELETE FROM [dbo].Panel
EXEC('INSERT into [dbo].Panel
	(  [PnlNo],[PanelName],[Description],[Address]
      ,[ComPort],[HCSNo],[Enabled],[Interactive]
      ,[NoFail],[XactSize],[Node],[MapId]
      ,[DeviceId],[CommPrior],[DwnLoadPrior],[PwrPrior]
      ,[Schedules],[Elevators],[ModemPrior],[LowBatPrior],[Passwrd]
      ,[CurrentPwd],[DegradeMode],[AGSize],[Location]
      ,[Remarks],[PanelType],[MaxAlp],[MaxEvt]
      ,[DefaultCalendar],[NAPCOPanel],[NoPolling]
	  ,[RespOffOnLine],[RespDownloads],[RespACPowerFail],[RespModemActivity],[RespLowBattery]
      ,[LastUpdated],[LastOperator],[LastWorkstation]
      ,[ZoneId],[UTCOffset],[LockId],[LockUpdateSchedule],[LockType],[LockAssigned]
      ,[LockChanged],[RemoteDevice],[CentralStationId],[RetrievalSchedule],[AperioType],[LockExpAddress])
SELECT ISNULL([PnlNo],0) AS PnlNo,ISNULL(NULLIF([Description],''''), ''Unknown'') AS PanelName,[Description],ISNULL([Address],0) AS Address
      ,ISNULL([ComPort],0) AS ComPort,
	   CASE WHEN EXISTS(Select Cluster from ['+@sourceServer+'].['+@sourceDb+'].[dbo].[MCom] WHERE Cluster = Panel.Cluster) THEN [Cluster]+1 ELSE NULL END AS HCSNo
	  ,ISNULL([Enabled],0) AS Enabled, ISNULL([Interactive],0) AS Interactive,ISNULL([NoFail],0) AS NoFail,ISNULL([XactSize],0) AS XactSize
	  ,ISNULL([Node],0) AS Node,[DynMapId], ISNULL([DeviceId],0) AS DeviceId,ISNULL([CommPrior],0) AS CommPrior,ISNULL([DwnLoadPrior],0) AS DwnLoadPrior
	  ,ISNULL([PwrPrior],0)AS PwrPrior,ISNULL([Schedules],0) AS Schedules,ISNULL([Elevators],0) AS Elevators,ISNULL([ModemPrior],0) AS ModemPrior
	  ,CASE WHEN LockType>0 THEN [PwrPrior] ELSE 50 END AS [LowBatPrior],ISNULL([Passwrd],0) AS Passwrd,ISNULL([CurrentPwd],0) AS CurrentPwd,ISNULL([DegradeMode],0) AS DegradeMode
	  ,ISNULL([AGSize],0) AS AGSize,[Location],[Remarks],ISNULL([PanelType],0) AS PanelType,ISNULL([MaxAlp],0) AS MaxAlp,ISNULL([MaxEvt],0) AS MaxEvt,ISNULL([DefaultCalendar],0)
	  ,ISNULL([NAPCOPanel],0) AS NAPCOPanel,0 AS [NoPolling]
	  ,CASE WHEN ([RespReq] & 1)=1 THEN 1 ELSE 0 END AS [RespOffOnLine]
	  ,CASE WHEN ([RespReq] & 2)=2 THEN 1 ELSE 0 END AS [RespDownloads]
	  ,CASE WHEN ([RespReq] & 4)=4 THEN 1 ELSE 0 END AS [RespACPowerFail]
	  ,CASE WHEN ([RespReq] & 8)=8 THEN 1 ELSE 0 END AS [RespModemActivity]
	  ,CASE WHEN ([RespReq] & 16)=16 THEN 1 ELSE 0 END AS [RespLowBattery]
	  ,ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
	  ,UPPER(Convert(char(50), SERVERPROPERTY (''machinename''))) AS [LastWorkStation]
      ,ISNULL([ZoneId],0) AS ZoneId,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset,ISNULL([LockId],0) AS LockId,ISNULL([LockUpdateSchedule],0) AS LockUpdateSchedule
      ,ISNULL([LockType],0) AS LockType,ISNULL([LockAssigned],0) AS LockAssigned,ISNULL([LockChanged],0) AS LockChanged,ISNULL([RemoteDevice],0) as RemoteDevice
	  ,ISNULL([CentralStationId],0) as CentralStationId,case when [LockType]>0 then [Node] else 0 end as [RetrievalSchedule],0 as [AperioType],ISNULL([LockExpAddress],231) as LockExpAddress
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Panel]')

exec ('insert into [dbo].[DevicePartitions]  Select ''{'+@PartitionID+'}'', caobjectID from Panel')
----============ Reader Data Migration ========================
DELETE FROM READER
EXEC ('INSERT INTO [dbo].[Reader]
           ([PnlRef],[RdrNo],[ReaderName],[Description],[Enabled],[RptByp],[BypUnlock],[OpnWait],[NoOTL],[NoXact],[Escort],[ManRule]
           ,[ExtShunt],[TZOvr],[APBOvr],[Keypad],[NoDuress],[CRForced],[CROtl],[CRDuress],[CRTrack],[CRVoid],[CRApb],[CRDenied]
           ,[Ctz],[Cctz],[Cytz],[Fatz],[Fdd],[Otl],[Dst],[APBType],[TAAPBType],[TAType],[LockCtl],[Sensor],[Strike],[Bypass],[Shunt]
           ,[DurUse],[Cyph],[Type],[MapId],[DeviceId],[DegrSchedule],[ShuntTime],[ManualPriv],[TagTimeout],[APBEntry],[APBExit],[CatCode],[CatFilter]
           ,[RepeatOTL],[OTLSchedule],[BadgeUseRegister],[NAPCOAreas],[Location],[Remarks],[BypCatControl],[RespForcedDoor],[RespOTL],[RespDuress]
		   ,[RespTrackedBadge],[RespVoidBadge],[RespAPBViolate],[RespOtherDenied],[RespBypass],[RespFreeAccess],[RespManualDoorCtrl],[RespCommonCode],[RespValidAccess],[AckBypass]
		   ,[AckFreeAccess],[AckManualDoorCtrl],[AckCommonCode],[AckValidAccess],[CatSchedule],[DvrServerID],[DvrCameraID],[VideoPriorityFrom],[VideoPriorityTo],[VideoStartTime],[VideoEndTime]
           ,[EnableRecording],[NoofSecsToRecord],[EnablePTZPreset],[PTZPresetNo],[RecordingSchedule],[LastUpdated],[caObjectID],[LastOperator],[UTCOffset],[NoSound],[AperioAddr]
		   ,[DoubleReadFA],[BypassLocks],[RexOption],[UseLedBar])
SELECT  ISNULL([PnlRef],0) AS PnlRef,ISNULL([RdrNo],0) AS RdrNo,ISNULL(NULLIF(R.[Description],''''), ''Unknown'') AS ReaderName,R.[Description],ISNULL(R.[Enabled],0) AS Enabled,ISNULL([RptByp],0) AS RptByp
		,ISNULL([BypUnlock],0) AS BypUnlock,ISNULL([OpnWait],0) AS OpnWait,0 as [NoOTL],ISNULL([NoXact],0) AS NoXact,ISNULL([Escort],0) AS Escort,ISNULL([ManRule],0) AS [ManRule],ISNULL([ExtShunt],0) AS ExtShunt
		,ISNULL([TZOvr],0) AS TZOvr,ISNULL([APBOvr],0) AS APBOvr,ISNULL([Keypad],0) AS Keypad,ISNULL([NoDuress],0) AS NoDuress,ISNULL([CRForced],0) AS CRForced,ISNULL([CROtl],0) AS CROtl,ISNULL([CRDuress],0) AS CRDuress
		,ISNULL([CRTrack],0) AS CRTrack,ISNULL([CRVoid], 0) AS CRVoid,ISNULL([CRApb],0) AS CRApb,ISNULL([CRDenied],0) AS CRDenied,ISNULL([Ctz],0) AS Ctz,ISNULL([Cctz],0) AS Cctz,ISNULL([Cytz],0) AS Cytz
		,ISNULL([Fatz],0) AS Fatz,ISNULL([Fdd],0) AS Fdd,ISNULL([Otl],0) AS Otl,ISNULL([Dst],0) AS Dst,ISNULL([APBType],0) & 7 as [APBType],case when ([APBType]>2) then [APBType]-2 else 0 end as [TAAPBType]
		,ISNULL([TAType],0) AS TAType,ISNULL([LockCtl],0) AS LockCtl,ISNULL([Sensor],0) AS Sensor,ISNULL([Strike],0) AS Strike,ISNULL([Bypass],0) AS Bypass,ISNULL([Shunt],0) AS Shunt,ISNULL([DurUse],0) AS DurUse
		,CASE WHEN [Cyph]=-1 THEN 65535 ELSE [Cyph] END AS [Cyph],ISNULL([Type],0) AS Type,R.[DynMapId],R.[DeviceId],ISNULL([DegrSchedule],0) AS DegrSchedule,ISNULL([ShuntTime],0) AS ShuntTime,ISNULL([ManualPriv],0) AS ManualPriv
		,ISNULL([TagTimeout],0) AS TagTimeout,ISNULL([APBEntry],0) AS APBEntry,ISNULL([APBExit],0) AS APBExit,ISNULL([CatCode],0)%4 AS [CatCode],ISNULL([CatCode],0)/4 AS [CatFilter],ISNULL([RepeatOTL],0) AS RepeatOTL
		,ISNULL([OTLSchedule],0) AS OTLSchedule,1 AS BadgeUseRegister,ISNULL([NAPCOAreas],0) AS NAPCOAreas,R.[Location],R.[Remarks],0 as BypCatControl,case when (R.RespReq & 1)=1 then 1 else 0 end as RespForcedDoor
		,case when (R.RespReq & 2)=2 then 1 else 0 end as RespOTL, case when (R.RespReq & 4)=4 then 1 else 0 end as RespDuress, case when (R.RespReq & 8)=8 then 1 else 0 end as RespTrackedBadge,case when (R.RespReq & 16)=16 then 1 else 0 end as RespVoidBadge
		,case when (R.RespReq & 32)=32 then 1 else 0 end as RespAPBViolate,case when (R.RespReq & 64)=64 then 1 else 0 end as RespOtherDenied, case when (R.RespReq & 128)=128 then 1 else 0 end as RespBypass,case when (R.RespReq & 256)=256 then 1 else 0 end as RespFreeAccess
		,case when (R.RespReq & 512)=512 then 1 else 0 end as RespManualDoorCtrl, case when (R.RespReq & 1024)=1024 then 1 else 0 end as RespCommonCode, case when (R.RespReq & 2048)=2048 then 1 else 0 end as RespValidAccess
		,case when (R.RespReq & 4096)=4096 then 1 else 0 end as AckBypass, case when (R.RespReq & 8192)=8192 then 1 else 0 end as AckFreeAccess, case when (R.RespReq & 16384)=16384 then 1 else 0 end as AckManualDoorCtrl
		,case when (R.RespReq & 32768)=32768 then 1 else 0 end as AckCommonCode,case when (R.RespReq & 65536)=65536 then 1 else 0 end as AckValidAccess,ISNULL(CatSchedule,0) AS CatSchedule, DvrServerID, DvrCameraID
		,ISNULL(VideoPriorityFrom,1) AS VideoPriorityFrom, ISNULL(VideoPriorityTo,70) AS VideoPriorityTo, ISNULL(VideoStartTime,50) AS VideoStartTime, ISNULL(VideoEndTime,50) AS VideoEndTime,ISNULL(EnableRecording,0) AS EnableRecording
		,ISNULL(NoofSecsToRecord,0) AS NoofSecsToRecord,ISNULL(EnablePTZPreset,0) AS EnablePTZPreset,ISNULL(PTZPresetNo,0) AS PTZPresetNo, ISNULL(RecordingSchedule,0) AS RecordingSchedule,ISNULL(R.Changed, GETUTCDATE()) as LastUpdated,newid()
		,''1EEB696C-0E28-4702-8598-C379C95B45E1'',(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset,isnull(NoSound,0) AS NoSound,isnull(AperioAddr,0) AS AperioAddr, isnull(DoubleReadFA,0) AS DoubleReadFA,isnull(BypassLocks,0) AS BypassLocks
		,isnull(RexOption,0) AS RexOption,isnull(UseLedBar,0) AS UseLedBar
	 FROM ['+@sourceServer+'].['+@sourceDatabase+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDatabase+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef')

DELETE FROM ReaderPriority
Exec ('INSERT INTO [dbo].[ReaderPriority]
			([PnlRef],[RdrNo],[FDPrior],[OTLPrior],[DuPrior],[TrkPrior],[VPrior],[APBPrior],[OTHPrior],
			[ManPrior],[BypPrior],[FreePrior],[PINPrior],[ValidPrior],
			[LastUpdated],[LastOperator],[caObjectId])
   SELECT  	[PnlRef],[RdrNo],[FDPrior],[OTLPrior],[DuPrior],[TrkPrior],[VPrior],[APBPrior],[OTHPrior],
			[ManPrior],[BypPrior],[FreePrior],[PINPrior],[ValidPrior],
			ISNULL(R.Changed,GETUTCDATE()) as LastUpdated ,''1EEB696C-0E28-4702-8598-C379C95B45E1'',NewId()
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef')

Delete from ReaderCategories
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 1, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category1=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 2, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category2=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 3, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category3=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 4, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category4=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 5, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category5=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 6, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category6=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 7, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category7=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 8, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category8=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 9, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category9=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 10, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category10=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 11, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category11=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 12, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category12=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 13, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category13=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 14, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category14=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 15, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category15=1')
Exec ('INSERT INTO [dbo].[ReaderCategories]
			([PnlRef],[RdrNo],CatNumber, [LastUpdated])
   SELECT  	[PnlRef],[RdrNo], 16, ISNULL(R.Changed,GETUTCDATE()) as LastUpdated
	 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Reader] R,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P WHERE P.PnlNo = R.PnlRef
	 and R.Category16=1')

exec ('ENABLE Trigger tr_Insert_Reader ON Reader') 
exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Reader')

--============ Input Data Migration ========================
DELETE FROM Input
EXEC ('INSERT INTO [dbo].[Input]
           ([PnlRef],[InpNo],[InputName],[Description]
           ,[Enabled],[NormOpen],[Supervised],[NoXact]
           ,[AbnormDial],[NormDial],[ConRelay],[InpTz]
           ,[InpDelay],[InpReset],[Priority],[MapId]
           ,[DeviceId],[RespReq],[Remark],[Location]
           ,[DvrServerID],[DvrCameraID],[VideoStartTime]
           ,[VideoEndTime],[VideoEventType],[EnableRecording]
           ,[NoofSecsToRecord],[AlertLimit],[LimitSchedule]
		   ,[PTZPresetNo],[EnablePTZPreset],[RecordingSchedule]
		   ,[caObjectID],[LastUpdated],[LastOperator],[LastWorkStation],UTCOffset)
SELECT ISNULL([PnlRef],0) AS PnlRef,ISNULL([InpNo],0) AS InpNo,ISNULL(NULLIF(I.[Description],''''), ''Unknown'') AS InputName,I.[Description]
      ,ISNULL(I.[Enabled],0) AS Enabled,ISNULL([NormOpen],0) AS NormOpen,ISNULL([Supervised],0) AS Supervised,ISNULL([NoXact],0) AS NoXact
      ,ISNULL([AbnormDial],0) AS AbnormDial,ISNULL([NormDial],0) AS NormDial,ISNULL([ConRelay],0) AS ConRelay,ISNULL([InpTz],0) AS InpTz
      ,CASE WHEN ISNULL([InpDelay],0)<60 THEN ISNULL([InpDelay],0) ELSE ((ISNULL([InpDelay],0)/60)+59) END AS InpDelay,ISNULL([InpReset],0) AS InpReset,ISNULL([Priority],50) AS Priority,I.[MapId]
      ,I.[DeviceId],ISNULL(I.[RespReq],0) AS RespReq,[Remark],I.[Location]
      ,[DvrServerID],CASE WHEN [DvrCameraID]>0 THEN [DvrCameraID] ELSE NULL END AS [DvrCameraID],ISNULL([VideoStartTime],50) AS VideoStartTime
      ,ISNULL([VideoEndTime],50) AS VideoEndTime,ISNULL([VideoEventType],1) AS VideoEventType,ISNULL([EnableRecording],0) AS EnableRecording
      ,ISNULL([NoofSecsToRecord],0) AS NoofSecsToRecord,ISNULL([AlertLimit],0) AS AlertLimit,ISNULL([LimitSchedule],0) AS LimitSchedule
	  ,[PTZPresetNo],ISNULL([EnablePTZPreset],0) AS EnablePTZPreset,ISNULL([RecordingSchedule],0) AS RecordingSchedule
	  ,newid() as caObjectID, ISNULL(I.Changed, GETUTCDATE()) as LastUpdated
      ,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as LastOperator,UPPER(Convert(char(50), SERVERPROPERTY (''machinename''))) AS [LastWorkStation]
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Input] I,['+@sourceServer+'].['+@sourceDb+'].[dbo].[Panel] P Where P.PnlNo = I.PnlRef')
exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Input')

----============ Relay Data Migration ========================
DELETE FROM RELAY
Exec ('INSERT INTO [dbo].[Relay]
           ([PnlRef],[RelNo],[RelayName],[Description]
           ,[Enabled],[XactRpt],[Dialup],[NormOn]
           ,[RelTime],[TrackTz],[Priority],[MapId]
           ,[DeviceId],[RespReq],[ManualPriv],[Location]
           ,[Remarks],[caObjectID],[LastUpdated],[LastOperator],UTCOffset)
SELECT ISNULL([PnlRef],0) AS PnlRef,ISNULL([RelNo],0) AS RelNo,ISNULL(NULLIF(R.[Description],''''), ''Unknown'') AS [RelayName],R.[Description]
      ,ISNULL(R.[Enabled],0) AS Enabled,ISNULL([XactRpt],0) AS XactRpt,ISNULL([Dialup],0) AS Dialup,ISNULL([NormOn],0) AS NormOn
      ,ISNULL([RelTime],0) AS RelTime,ISNULL([TrackTz],0) AS TrackTz,ISNULL([Priority],0) AS Priority,R.[DynMapId]
      ,R.[DeviceId],ISNULL(R.[RespReq],1),ISNULL([ManualPriv],0) AS ManualPriv,R.[Location]
      ,R.[Remarks],newid(),ISNULL(R.Changed,GETUTCDATE()) as LastUpdated,''1EEB696C-0E28-4702-8598-C379C95B45E1''
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset		
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Relay] R,['+@sourceServer+'].['+@sourceDb+'].[dbo].[Panel] P
  WHERE P.PnlNo=R.Pnlref')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Relay')

--============ ElevAg Migration ========================
DELETE FROM [dbo].[ElevAg]
SET @Query= 'declare @relayBinString varchar(max)
declare @relayNo varchar(4)
declare @n int
declare @TEMP table (data varchar(4))
declare @invalue varchar(20)
declare @inbinvalue binary(10)
DECLARE @PnlRef INT
DECLARE @RdrRef INT
DECLARE @AccGroup INT
DECLARE @COUNT INT
SELECT @COUNT= COUNT(PnlRef) FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[ElevAg]
IF @COUNT>0
BEGIN
	DECLARE ElevAg_Cursor CURSOR FOR 
	SELECT [PnlRef],[RdrRef],[AccGroup],[RelayList]FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[ElevAg]
	OPEN ElevAg_Cursor;  
	FETCH NEXT FROM ElevAg_Cursor INTO @PnlRef, @RdrRef, @AccGroup,@inbinvalue
	WHILE @@FETCH_STATUS=0
	BEGIN
		set @inbinvalue = (select cast(reverse(@inbinvalue) as binary(10)) [littleEndian] )
		set @invalue = substring(convert(VARCHAR(MAX),@inbinvalue,1),3,20)
		SELECT  @relayBinString = dbo.ca_fn_Hex2Bin(@invalue)
		set @n = 0
		set @relayBinString = REVERSE(@relayBinString)
		WHILE @n < 80
		BEGIN
			SET @n = @n + 1
			set @relayNo = SUBSTRING(@relayBinString, @n, 1)
			if @relayNo = ''1''
			BEGIN
				if exists(select 1 from Reader where PnlRef = @PnlRef and RdrNo = @RdrRef) 
				BEGIN
					if exists(select 1 from Relay where PnlRef = @PnlRef and RelNo = @n) 
					BEGIN
						INSERT INTO ElevAg([PnlRef],[RdrRef],[AccGroup],[RelayNumber],[LastOperator]) VALUES(@PnlRef,@RdrRef,@AccGroup,CONVERT(varchar(10), @n),''1EEB696C-0E28-4702-8598-C379C95B45E1'')
					END
				END
			END
		END
		FETCH NEXT FROM ElevAg_Cursor INTO @PnlRef, @RdrRef, @AccGroup,@inbinvalue
	END
	CLOSE ElevAg_Cursor
	DEALLOCATE ElevAg_Cursor
END'

EXEC(@Query) SET @Query=''

----============ RepeaterStatus Data Migration ========================
DELETE FROM RepeaterStatus
Exec ('INSERT INTO [RepeaterStatus]([Cluster],[ComPort],[rfAddress],[Id],[RouteIn],[RouteOut],[State],[RSSI],[FirmwareMajor],[FirmwareMinor],[Status])
  SELECT  [Cluster]+1 AS [Cluster],[ComPort],[rfAddress],[Id],[RouteIn],[RouteOut],[State],[RSSI],[FirmwareMajor],[FirmwareMinor],[Status]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[RepeaterStatus]')

----============ lOCKStatus Data Migration ========================
DELETE FROM LockStatus
EXEC('INSERT INTO LockStatus([PnlRef],[Comport],[RxRSSI],[TxRSSI],[Version],[Assigned],[LockType],[LockId],[Status],[Battery],[RepeaterAddress],[LastUpdated])
  SELECT [PnlRef],[Comport],[RxRSSI],[TxRSSI],[Version],[Assigned],[LockType],[LockId],[Status],[Battery],[RepeaterAddress],[LastUpdated]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[LockStatus]')

----============ GatewayStatus Data Migration ========================
DELETE FROM GatewayStatus
  EXEC('INSERT INTO GatewayStatus([Comport],[MACAddress],[IPAddress],[Version],[Status],[LockCount],[Cluster],[HasRepeaters],[RepeaterCount],[GroupNo],[LastUpdated])
  SELECT [Comport],[MACAddress],[IPAddress],[Version],[Status],0,[Cluster],[HasRepeaters],0,0,[LastUpdated] 
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[GatewayStatus]')


----============ Format Data Migration ========================
Exec('UPDATE ['+@sourceServer+'].['+@SourceDB+'].dbo.[Format] Set Description = ''Format'' Where Description is NULL') 
DELETE FROM [dbo].[Format]
Exec ('INSERT INTO [dbo].[Format]
           ([FmtNo],[Description],[FmtLength],[FmtType]
           ,[FmtSS],[FmtSSO],[FmtCSOff],[FmtCSLen]
		   ,[FmtDOff],[FmtDLen],[FmtES],[FmtESO]
           ,[FmtFS],[FmtFSO],[FmtCLen],[FmtCOff]
           ,[FmtILen],[FmtIOff],[FmtOCOff],[FmtOCLen]
           ,[FmtOIOff],[FmtOILen],[FmtPIOff],[FmtPILen]
           ,[FmtPOAOff],[FmtPOALen],[FmtFLen],[FmtFOff],[PivStatus]
           ,[LastUpdated],[caObjectID],[LastOperator])

SELECT [FmtNo],ISNULL(NULLIF([Description],''''), ''Unknown'') as Description,ISNULL([FmtLength],0) AS FmtLength,ISNULL([FmtType],0) AS FmtType
      ,ISNULL([FmtSS],0) AS FmtSS,ISNULL([FmtSSO],0) AS FmtSSO,ISNULL([FmtCSOff],0) AS FmtCSOff,ISNULL([FmtCSLen],0) AS FmtCSLen
      ,ISNULL([FmtDOff],0) AS FmtDOff,ISNULL([FmtDLen],0) AS FmtDLen,ISNULL([FmtES],0) AS FmtES,ISNULL([FmtESO],0) AS FmtESO
      ,ISNULL([FmtFS],0) AS FmtFS,ISNULL([FmtFSO],0) AS FmtFSO,ISNULL([FmtCLen],0) AS FmtCLen,ISNULL([FmtCOff],0) AS FmtCOff
      ,ISNULL([FmtILen],0) AS FmtILen,ISNULL([FmtIOff],0) AS FmtIOff,ISNULL([FmtOCOff],0) AS FmtOCOff,ISNULL([FmtOCLen],0) AS FmtOCLen
	  ,ISNULL([FmtOIOff],0) AS FmtOIOff,ISNULL([FmtOILen],0) AS FmtOILen,ISNULL([FmtPIOff],0) AS FmtPIOff,ISNULL([FmtPILen],0) AS FmtPILen
	  ,ISNULL([FmtPOAOff],0) AS FmtPOAOff,ISNULL([FmtPOALen],0) AS FmtPOALen,ISNULL([FmtFLen],0) AS FmtFLen,ISNULL([FmtFOff],0) AS FmtFOff,ISNULL([PivStatus],0) AS PivStatus
	  ,ISNULL(Changed, GETUTCDATE()) as LastUpdated,newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1''
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Format]')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Format')
----============ Facility Data Migration ========================
DELETE FROM [dbo].[Facility]
Exec('INSERT INTO [dbo].[Facility]
           ([PnlRef]
           ,[Facil1]
           ,[Facil2]
           ,[Facil3]
           ,[Facil4]
           ,[Facil5]
           ,[Facil6]
           ,[Facil7]
           ,[Facil8]
           ,[Facil9]
           ,[Facil10]
           ,[LastUpdated]
           ,[caObjectID]
           ,[LastOperator]
		   ,[UTCOffset])
SELECT [PnlRef]
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil1])) AS Facil1
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil2])) AS Facil2
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil3])) AS Facil3
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil4])) AS Facil4
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil5])) AS Facil5
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil6])) AS Facil6
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil7])) AS Facil7
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil8])) AS Facil8
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil9])) AS Facil9
      ,CONVERT(INT,  CONVERT(VARBINARY(2), [Facil10]))AS Facil10
	  ,ISNULL(Facility.Changed,GETUTCDATE()) as LastUpdated
	  ,newid()
	  ,''1EEB696C-0E28-4702-8598-C379C95B45E1''
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Facility] CROSS JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel]
  WHERE Facility.PnlRef=Panel.PnlNo')

exec ('update [dbo].[Facility] Set Facil1= 0 Where Facil1 IS NULL')
exec ('update [dbo].[Facility] Set Facil2= 0 Where Facil2 IS NULL')
exec ('update [dbo].[Facility] Set Facil3= 0 Where Facil3 IS NULL')
exec ('update [dbo].[Facility] Set Facil4= 0 Where Facil4 IS NULL')
exec ('update [dbo].[Facility] Set Facil5= 0 Where Facil5 IS NULL')
exec ('update [dbo].[Facility] Set Facil6= 0 Where Facil6 IS NULL')
exec ('update [dbo].[Facility] Set Facil7= 0 Where Facil7 IS NULL')
exec ('update [dbo].[Facility] Set Facil8= 0 Where Facil8 IS NULL')
exec ('update [dbo].[Facility] Set Facil9= 0 Where Facil9 IS NULL')
exec ('update [dbo].[Facility] Set Facil10= 0 Where Facil10 IS NULL')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Facility')
----============ Holidays Data Migration ========================
DELETE FROM [dbo].[MHoliday]
Exec('INSERT INTO [dbo].[MHoliday]
           ([CalendarNo]
           ,[Description]
           ,[LastUpdated]
           ,[caObjectID]
           ,[LastOperator]
		   ,UTCOffset)
SELECT [GroupNo]
      ,ISNULL(NULLIF([Description],''''), ''Unknown'') as Description
	  ,ISNULL(Changed,GETUTCDATE()) as LastUpdated
	  ,newid()
	  ,''1EEB696C-0E28-4702-8598-C379C95B45E1''
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MHoliday]')

DELETE FROM HOLIDAY
Exec('INSERT INTO [dbo].[Holiday]
           ([CalendarNo],[HolNo],[SDate]
			,[FrHr],[FrMin]
			,[EndHr],[EndMin]
			,[FromTime],[ToTime]
			,[caObjectID],[LastOperator])
 SELECT [GroupNo],[HolNo],ISNULL([SDate],Convert(date, GETDATE())) AS SDate,ISNULL([FrHr],0) as FrHr,ISNULL([FrMin],0) AS FrMin,ISNULL([EndHr],0) AS EndHr,ISNULL([EndMin],0) AS EndMin
		,ISNULL([FromTime] ,Convert(date, GETDATE())) AS FromTime,ISNULL([ToTime],Convert(date, GETDATE())) AS ToTime,newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1''
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Holiday]')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from MHoliday')

----============ Link Data Migration ========================
DELETE FROM [dbo].[Link]
Exec('INSERT INTO [dbo].[Link]
           ([PnlRef],[ProgNo],[Enabled],[Dialup],[RptXact]
           ,[LinkName],[Description],[Status],[LnkPnl],[LnkProg]
           ,[ETz],[TrkTZ],[Inp1],[Inp2],[Inp3],[Inp4],[Inp5]
           ,[On1],[On2],[On3],[On4],[On5]
           ,[Off1],[Off2],[Off3],[Off4],[Off5]
           ,[Trk1],[Trk2],[Trk3],[Trk4],[Trk5]
           ,[Priority],[MapId],[DeviceId],[RespReq]
           ,[ManualPriv],[Location],[Remarks]
           ,[caObjectID],[LastUpdated],[LastOperator],UTCOffset)
SELECT ISNULL([PnlRef],0) AS PnlRef,ISNULL([ProgNo],0) AS ProgNo,ISNULL(L.[Enabled],0) AS Enabled,ISNULL([Dialup],0) AS Dialup,ISNULL([RptXact],0) AS RptXact
      ,ISNULL(L.[Description],''Link''+ CAST(ProgNo AS VARCHAR)) as LinkName
	  ,L.[Description],ISNULL([Status],0) AS ''Status'',ISNULL([LnkPnl],0) AS LnkPnl,ISNULL([LnkProg],0) AS LnkProg
      ,ISNULL([ETz],0) AS ETz,ISNULL([TrkTZ],0) AS TrkTZ
	  ,ISNULL([Inp1],0) AS Inp1,ISNULL([Inp2],0) AS Inp2,ISNULL([Inp3],0) AS Inp3,ISNULL([Inp4],0) AS Inp4,ISNULL([Inp5],0) AS Inp5
      ,ISNULL([On1],0) AS On1,ISNULL([On2],0) AS On2,ISNULL([On3],0) AS On3,ISNULL([On4],0) AS On4,ISNULL([On5],0) AS On5
      ,ISNULL([Off1],0) AS Off1,ISNULL([Off2],0) AS Off2,ISNULL([Off3],0) AS Off3,ISNULL([Off4],0) AS Off4,ISNULL([Off5],0) AS Off5
      ,ISNULL([Trk1],0) AS Trk1,ISNULL([Trk2],0) AS Trk2,ISNULL([Trk3],0) AS Trk3,ISNULL([Trk4],0) AS Trk4,ISNULL([Trk5],0) AS Trk5
      ,ISNULL([Priority],10) AS Priority,L.[MapId],L.[DeviceId],ISNULL(L.[RespReq],0)
      ,ISNULL([ManualPriv],0) AS ManualPriv,L.[Location],L.[Remarks]
      ,newid() as caObjectID,ISNULL(L.Changed,GETUTCDATE()) as LastUpdated,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as LastOperator
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset	
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Link] L,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P
	WHERE P.PnlNo = L.PnlRef')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Link')

----============ Access Groups Data Migration ========================
ALTER TABLE BadgeAccess DISABLE TRIGGER tr_Delete_BadgeAccess
DELETE FROM BadgeAccess;
ALTER TABLE BadgeAccess ENABLE TRIGGER tr_Delete_BadgeAccess
DELETE From [AccGrp]
ALTER TABLE MAccGrp DISABLE TRIGGER tr_Delete_MAccGrp
DELETE FROM MAccGrp
ALTER TABLE MAccGrp ENABLE TRIGGER tr_Delete_MAccGrp
Exec('INSERT INTO [dbo].[MAccGrp]
           ([AgrpNo],[Description],[PointAccess]
           ,[LastUpdated],[caObjectID],[LastOperator],[UTCOffset])
SELECT [AgrpNo],[Descrip],[PointAccess]
		,ISNULL(Changed,GETUTCDATE()) as LastUpdated,newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1''
		,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MAccGrp]')

DELETE From [AccGrp]
Exec('INSERT INTO [dbo].[AccGrp]
           ([PnlRef],[Agno]
			,[Tz1],[Tz2],[Tz3],[Tz4],[Tz5],[Tz6],[Tz7],[Tz8]
			,[Tz9],[Tz10],[Tz11],[Tz12],[Tz13],[Tz14],[Tz15],[Tz16]
           ,[caObjectID],[LastUpdated],[LastOperator],[UTCOffset])
SELECT [PnlRef],[Agno]
      ,[Tz1],[Tz2],[Tz3],[Tz4],[Tz5],[Tz6],[Tz7],[Tz8]
	  ,[Tz9],[Tz10],[Tz11],[Tz12],[Tz13],[Tz14],[Tz15],[Tz16]
	  ,newid(),ISNULL(Changed,GETUTCDATE()) as LastUpdated,''1EEB696C-0E28-4702-8598-C379C95B45E1''
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[AccGrp]')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from MAccGrp WHERE AgrpNo > 0')

----============ TimeZones Data Migration ========================
DELETE FROM [dbo].[MTimezon] WHERE caObjectID<>'971B59B1-B15B-4880-B056-1D7D83337ACF'
Exec('INSERT INTO [dbo].[MTimezon]
           ([TzNum],[Description]
           ,[HolGroups],[caObjectID]
		   ,[LastUpdated],[LastOperator],[UTCOffset])
SELECT [TzNum],[Description]
      ,ISNULL([HolGroups],0) AS [HolGroups],newid() AS [caObjectID]
      ,ISNULL(Changed,GETUTCDATE()) AS [LastUpdated]
      ,CASE WHEN TzNum > 0 THEN ''1EEB696C-0E28-4702-8598-C379C95B45E1'' ELSE NULL END AS [LastOperator]
      ,CASE WHEN TzNum >0 THEN (select datediff(mi,GetUTCDate(),GetDate())) ELSE NULL END as [UTCOffset]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MTimezon] WHERE TzNum>0')

DELETE FROM [dbo].[Timezone]
Exec('INSERT INTO [dbo].[Timezone]
           ([TzNo],[DayFrom],[DayTo]
           ,[FrHr],[FrMin],[ToHr],[ToMin]
           ,[TimeFrom],[TimeTo]
           ,[caObjectId],[LastUpdated],[LastOperator],[UTCOffset])
SELECT [TzNo],[DayFrom],[DayTo]
      ,[FrHr],[FrMin],[ToHr],[ToMin]
      ,[TimeFrom],[TimeTo]
	  ,newid()AS [caObjectID],ISNULL(Timezone.Changed,GETUTCDATE()) AS [LastUpdated]
	  ,''1EEB696C-0E28-4702-8598-C379C95B45E1''AS [LastOperator]
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as [UTCOffset]	
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Timezone] cross join ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MTimezon] 
  where Timezone.TzNo=MTimezon.TzNum')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from MTimezon where TzNum > 0')

----============ Responses Data Migration ========================
DELETE FROM [dbo].[Resp]
Exec('INSERT INTO [dbo].[Resp]
           ([Description],caObjectID,LastUpdated,LastOperator)
SELECT [Description],newid(),GetUTCDate(),''1EEB696C-0E28-4702-8598-C379C95B45E1''	
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Resp]')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from Resp')

----============ Category Counters Data Migration ========================
DELETE FROM [dbo].[CategoryCounters]
Exec('INSERT INTO [dbo].[CategoryCounters]
           ([PnlNo],[CatNo],[Description]
			,[LowWater],[HiWater]
			,[EventMsg],[Priority]
           ,[RespReq],[LastUpdated]
           ,[caObjectID],[LastOperator],[UTCOffset])
SELECT P.[PnlNo],[CatNo],C.[Description]
      ,[LowWater],[HiWater]
      ,[EventMsg],[Priority],0
	  ,ISNULL(C.Changed,GETUTCDATE()) AS [LastUpdated],newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1''	
	  ,(select datediff(mi,GetUTCDate(),GetDate())) as [UTCOffset]	
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[CategoryCounters] C,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P 
  Where P.PnlNo = C.PnlNo')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from CategoryCounters')

----============ System Settings Data Migration ========================
DELETE FROM [dbo].[SystemSettings]
Exec('INSERT INTO [dbo].[SystemSettings]
           ([SysId],[SysBadgeLen],[UseAutoAck],[AutoAckPriority],[RespReqTz],[TagType],[AGCountPerBadge]
           ,[UseAPBAreas],[UseCatCounters],[UseFacBadge],[HidePinCode],[UseALP],[UseHoliday],[UseNAPCO],[UseVideo]
           ,[UseScripting],[APBResetTime],[APBResetArea],[BadgeReIssueLevel],[UseHostTz],[PrintBySchedule]
           ,[PrintPriority],[UseNAPCOPermissions],[UsePIV],[LastUpdated],[KeypadOption],[caObjectID]
           ,[LastOperator],[UseLDAPAuthentication],[UseLDAPAuthorization],[LDAPPath],[UTCOffset],[UseFacilityMaps])

SELECT [SysId],[SysBadgeLen],[AlertAutoDisp],[AlertPrtyLevel]
      ,[RespTz]
      ,case TagType WHEN(0) THEN 0 ELSE [TagType]%8 END AS TagType
      ,[AGCount] AS [AGCountPerBadge]
      ,[UseMultiAPB] AS [UseAPBAreas]
      ,[UseCatCntrs] AS [UseCatCounters]
      ,[FacBadge] AS [UseFacBadge]
	  ,[Reserved1] AS [HidePinCode]
	  ,[UseALP]
	  ,[UseHol] AS [UseHoliday]
	  ,[UseNAPCO] 
	  ,[UseDvrRemoteView] as [UseVideo]
	  ,[UseScripting]
	  ,[ResetAPBTime] AS [APBResetTime]
	  ,[ResetMApbArea] AS [APBResetArea]
	  ,case TagType WHEN(0) THEN 0 ELSE [TagType]/8 END AS BadgeReIssueLevel
	  ,[UseHostTz]
	  ,[PrnValidSched] AS [PrintBySchedule]
	  ,CASE WHEN [PrnPriority] > 99 THEN 99 ELSE [PrnPriority] END AS [PrintPriority]
	  ,[UseNAPCOPermissions]
	  ,[UsePiv] AS [UsePIV],ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],1 AS [KeypadOption]
      ,newid() AS [caObjectID],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
      ,[UseLDAPAuthenticate] AS [UseLDAPAuthentication]
      ,[UseLDAPAuthorize] AS [UseLDAPAuthorization]
      ,[LDAPPath],(select datediff(mi,GetUTCDate(),GetDate())) as [UTCOffset],[UseMaps] as [UseFacilityMaps]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[System] WHERE SysId=0')
 
----============ Workstation Settings Data Migration ========================
DELETE FROM [dbo].[WorkStationSettings]
Exec ('INSERT INTO [dbo].[WorkStationSettings]([WorkstationName],[UsePrinting],[EventPrn],[BadgingPrn],[UseBadging],[Badgingoption],[EnableVideoService],[UseAlertSound]
	  ,[AlertSoundInterval1],[AlertSoundInterval2],[AlertSoundInterval3],[AlertSoundInterval4],[ResponseSoundInterval],[AlertSoundName1],[AlertSoundName2],[AlertSoundName3]
	  ,[AlertSoundName4],[ResponseSoundName],[AlertSoundPriority1],[AlertSoundPriority2],[AlertSoundPriority3],[AlertSoundPriority4],[AlertColor1],[AlertColor2]
	  ,[AlertColor3],[AlertColor4],[TapiPhone1],[TapiPhone2],[TapiId1],[TapiId2],[CCTVSocket],[UseLinePrinterMode],[EventPrinter],[caObjectID]
	  ,[LastUpdated],[LastOperator])

SELECT (SELECT Host_Name()) AS [WorkstationName]
	   ,CASE WHEN PrnReport IS NOT NULL THEN 1 ELSE 0 END AS [UsePrinting]
	   ,[PrnReport] AS [EventPrn]
	   ,[PrnBadge] AS [BadgingPrn]
	   ,case [BadgingOption] WHEN(0) THEN 0 ELSE 1 END AS UseBadging
	   ,case [BadgingOption] WHEN(0) THEN 0 ELSE case [BadgingOption] WHEN(1) THEN 1 ELSE 2 END END AS BadgingOption
	   ,[EnableDVRRecording] AS [EnableVideoService]
	   ,[AlertSndEna] AS [UseAlertSound]
	   ,[AlertSndInterval] AS [AlertSoundInterval1]
	   ,[AlertSndInterval2] AS [AlertSoundInterval2]
	   ,[AlertSndInterval3] AS [AlertSoundInterval3]
	   ,[AlertSndInterval4] AS [AlertSoundInterval4]
	   ,[AlertSndInterval5] AS [ResponseSoundInterval]
	   ,[AlertNotifySnd] AS [AlertSoundName1]
	   ,[AlertNotifySnd2] AS [AlertSoundName2]
	   ,[AlertNotifySnd3] AS [AlertSoundName3]
	   ,[AlertNotifySnd4] AS [AlertSoundName4]
	   ,[AlertNotifySnd5] AS [ResponseSoundName]
	   ,CASE WHEN [AlertPrtyLevel]>0 THEN [AlertPrtyLevel] ELSE 1 END AS [AlertSoundPriority1]
	   ,10 AS [AlertSoundPriority2]
	   ,20 AS [AlertSoundPriority3]
	   ,30 AS [AlertSoundPriority4]
	   ,''Color[Control]'' AS [AlertColor1]
	   ,''Color[Control]'' AS [AlertColor2]
	   ,''Color[Control]'' AS [AlertColor3]
	   ,''Color[Control]'' AS [AlertColor4]
	   ,[TapiPhone1],[TapiPhone2]
	   ,[TapiId1],[TapiId2],CCTVSocket
	   ,[UseLinePrinterMode] as [UseLinePrinterMode]
	   ,[PrnEvent1] as [EventPrinter]
	   ,newid() AS [caObjectID]
	   ,ISNULL(Changed,GETUTCDATE()) AS [LastUpdated]
	   ,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[System] AS systbl INNER JOIN (SELECT TOP 1 * FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Mail)AS mail
		ON systbl.SysId=mail.NetStationId')

EXEC('UPDATE [dbo].[WorkStationSettings] SET UseWirelessLocks =CASE WHEN (select COUNT(*) from Com where Type=10)> 0 THEN 1 ELSE 0 END')
		
 	
----============ ArchiveDatabase  Data Migration ========================
Exec('declare @EventsToRetain as int 
select @EventsToRetain= EventsToRetain FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[ArchiveSettings] 
Update [dbo].[ArchiveSettings] set EventsToRetain=CASE WHEN @EventsToRetain IS NULL OR @EventsToRetain < 1000 THEN  1000 ELSE @EventsToRetain END')

----============ EPI_CONFIG Data Migration ========================
DELETE FROM [dbo].[EPI_CONFIG]
Exec('INSERT INTO [dbo].[EPI_CONFIG]
           ([Cfg_User]
           ,[Cfg_Level]
           ,[Cfg_Section]
           ,[Cfg_Name]
           ,[Cfg_Value])
SELECT [Cfg_User]
      ,[Cfg_Level]
      ,[Cfg_Section]
      ,[Cfg_Name]
      ,[Cfg_Value]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[EPI_CONFIG]')

----============ EPI_FORMAT Data Migration ========================
DELETE  FROM [dbo].[EPI_FORMAT]
Exec('INSERT INTO [dbo].[EPI_FORMAT]
           ([Format_ID]
           ,[Format_Name]
           ,[Format_Range_High]
           ,[Format_Range_Next]
           ,[Format_Range_Field]
           ,[Format_Range_Type]
           ,[Format_Layout]
           ,[templateFileID])
SELECT [Format_ID]
      ,[Format_Name]
      ,[Format_Range_High]
      ,[Format_Range_Next]
      ,[Format_Range_Field]
      ,[Format_Range_Type]
      ,[Format_Layout]
      ,[templateFileID]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[EPI_FORMAT]')
 
 exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', templateFileID from EPI_FORMAT WHERE templateFileID IS NOT NULL')

----============ GA_ALIAS Data Migration ========================
DELETE  FROM [dbo].[GA_ALIAS]
Exec('INSERT INTO [dbo].[GA_ALIAS]
           ([Id]
           ,[Table_Name]
           ,[Field_Name]
           ,[Alias_Name]
           ,[Visible])
SELECT [Id]
      ,[Table_Name]
      ,[Field_Name]
      ,[Alias_Name]
      ,[Visible]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_ALIAS]')

----============ GA_COLNAMES Data Migration ========================
DELETE  FROM [dbo].[GA_COLNAMES]

Exec('INSERT INTO [dbo].[GA_COLNAMES]
           ([ID]
           ,[Column_Name])
SELECT [ID]
      ,[Column_Name]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_COLNAMES]')

----============ GA_LAYOUTFIELDS Data Migration ========================
DELETE  FROM [dbo].[GA_LAYOUTFIELDS]
Exec('INSERT INTO [dbo].[GA_LAYOUTFIELDS]
           ([DBField])
SELECT [DBField]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_LAYOUTFIELDS]')

----============ GA_LIST Data Migration ========================
DELETE  FROM [dbo].[GA_LIST]
Exec('INSERT INTO [dbo].[GA_LIST]
           ([List_ID]
           ,[Data_Table]
           ,[Data_Column]
           ,[List_Type]
           ,[List_Table]
           ,[List_Column]
           ,[Auto_Sort])
SELECT [List_ID]
      ,[Data_Table]
      ,[Data_Column]
      ,[List_Type]
      ,[List_Table]
      ,[List_Column]
      ,[Auto_Sort]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_LIST]')

----============ GA_LISTDATA Data Migration ========================
DELETE  FROM [dbo].[GA_LISTDATA]
Exec('INSERT INTO [dbo].[GA_LISTDATA]
           ([List_ID]
           ,[List_Index]
           ,[List_Text])
SELECT [List_ID]
      ,[List_Index]
      ,[List_Text]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_LISTDATA]')

----============ GA_LOG Data Migration ========================
DELETE  FROM [dbo].[GA_LOG]
Exec('INSERT INTO [dbo].[GA_LOG]
           ([Log_ID]
           ,[Operator_ID]
           ,[Station_ID]
           ,[Log_Date]
           ,[Event]
           ,[Person_ID]
           ,[Card_ID]
           ,[Card_Code]
           ,[Info1]
           ,[Info2]
           ,[Info3]
           ,[Info4])
SELECT [Log_ID]
      ,[Operator_ID]
      ,[Station_ID]
      ,[Log_Date]
      ,[Event]
      ,[Person_ID]
      ,[Card_ID]
      ,[Card_Code]
      ,[Info1]
      ,[Info2]
      ,[Info3]
      ,[Info4]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_LOG]')

----============ GA_LOGARCHIVE Data Migration ========================
DELETE  FROM [dbo].[GA_LOGARCHIVE]
Exec('INSERT INTO [dbo].[GA_LOGARCHIVE]
           ([Log_ID]
           ,[Operator_ID]
           ,[Station_ID]
           ,[Log_Date]
           ,[Event]
           ,[Person_ID]
           ,[Card_ID]
           ,[Card_Code]
           ,[Info1]
           ,[Info2]
           ,[Info3]
           ,[Info4])
SELECT [Log_ID]
      ,[Operator_ID]
      ,[Station_ID]
      ,[Log_Date]
      ,[Event]
      ,[Person_ID]
      ,[Card_ID]
      ,[Card_Code]
      ,[Info1]
      ,[Info2]
      ,[Info3]
      ,[Info4]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[GA_LOGARCHIVE]')




----============ epiFiles data Migration ========================
DELETE  FROM [dbo].[epiFiles]
Exec('INSERT INTO [dbo].[epiFiles]
           ([templateFileID]
           ,[templateFileData])
SELECT [templateFileID]
      ,[templateFileData]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[epiFiles]')

----============ epiFiles data Migration ========================
DELETE  FROM [dbo].[GlobalVariables]
Exec('INSERT INTO [dbo].[GlobalVariables]
           ([variableName]
           ,[variableValue])
SELECT [variableName]
      ,[variableValue]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[globalVariables]')
----============ [operatorInstructions] data Migration ========================
DELETE  FROM [dbo].[operatorInstructions]
Exec('INSERT INTO [dbo].[operatorInstructions]
           ([instructionID]
           ,[instructionName]
           ,[instructionMsg]
           ,[LastUpdated]
           ,[LastOperator]
           ,[caObjectId])
SELECT [instructionID]
      ,[instructionName]
      ,[instructionMsg]
      ,ISNULL(Changed,GETUTCDATE()) AS [LastUpdated]
	  ,''1EEB696C-0E28-4702-8598-C379C95B45E1''
	  ,newid()
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[operatorInstructions]')
----============ epiFiles data Migration ========================
DELETE  FROM [dbo].[operatorInstructLink]
Exec('INSERT INTO [dbo].[operatorInstructLink]([cat],o.[pnlNo],[DeviceNo],[status],[instructionsEnabled],[instructionID],[LastUpdated]
	 ,[LastOperator],[caObjectId],[SoundEnabled],[AlertNotifySound],[SoundInterval])
     SELECT [cat],P.[pnlNo],[number]
      ,CASE WHEN cat=0 AND status=0 THEN 1	WHEN cat=0 AND status=1 THEN 3	WHEN cat=0 AND status=6 THEN 5	WHEN cat=0 AND status=8 THEN 9	WHEN cat=0 AND status=2 THEN 13 
			WHEN cat=0 AND status=24 THEN 17	WHEN cat=0 AND status=10 THEN 21	WHEN cat=0 AND status=29 THEN 27	WHEN cat=0 AND status=28 THEN 29
			WHEN cat=0 AND status=18 THEN 40	WHEN cat=0 AND status=19 THEN 42	WHEN cat=0 AND status=23 THEN 44	WHEN cat=0 AND status=11 THEN 100
			WHEN cat=0 AND status=12 THEN 102	WHEN cat=0 AND status=13 THEN 103	WHEN cat=0 AND status=14 THEN 105	WHEN cat=0 AND status=15 THEN 107
			WHEN cat=0 AND status=16 THEN 109	WHEN cat=0 AND status=17 THEN 111	WHEN cat=5 AND status=0 THEN 1	WHEN cat=5 AND status=1 THEN 2
			WHEN cat=5 AND status=2 THEN 5	WHEN cat=5 AND status=3 THEN 7	WHEN cat=5 AND status=6 THEN 9	WHEN cat=1 AND status=1 THEN 1	WHEN cat=1 AND status=0 THEN 2
			WHEN cat=2 AND status=0 THEN 0	WHEN cat=2 AND status=1 THEN 1	WHEN cat=3 AND status=0 THEN 0	WHEN cat=3 AND status=1 THEN 1	ELSE 0 END AS [status]
      ,[instructionsEnabled],[instructionID],ISNULL(o.Changed,GETUTCDATE()) AS [LastUpdated],''1EEB696C-0E28-4702-8598-C379C95B45E1'',newid(),SoundEnabled,AlertNotifySound,SoundInterval
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[operatorInstructionLinks] o,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P
  Where o.PnlNo = P.PnlNo') 	
----============ epiFiles data Migration ========================
DELETE  FROM [dbo].[MActiveLinks]
Exec('INSERT INTO [dbo].[MActiveLinks]
           ([PnlRef],[ProgNo],[Description]
			,[Priority],[Device],[EventCode]
			,[Category],[Schedule],[UnDO]
			,[UnDoProgNo],[RespReq],[ManualPriv]
			,[LastUpdated],[caObjectID],[LastOperator]
			,[LastWorkStation],[Enabled],[UTCOffset])
	SELECT M.[PnlRef],[ProgNo],M.[Description]
			,[Priority],[Device],[EventCode]
			,[Category],[Schedule],[UnDO]
			,[UnDoProgNo],0 as RespReq ,0 as ManualPriv, ISNULL(M.Changed,GETUTCDATE()) as LastUpdated
			,newid() as caObjectID,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator,'''' as LastWorkStation, M.Enabled
			,(select datediff(mi,GetUTCDate(),GetDate())) as [UTCOffset]
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MActiveLinks] M,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P
  Where M.PnlRef=P.PnlNo')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from MActiveLinks')
----============ [ActiveLinks] data Migration ========================
DELETE  FROM [dbo].[ActiveLinks]
Exec('INSERT INTO [dbo].[ActiveLinks]
           ([PnlRef],[ProgNo],[PnlNo]
			,[RelNo],[InpNo],[RdrNo]
			,[CatNo],[Control],[Active]
			,[CatReader],[caObjectID],[LastOperator]
			,[LastWorkStation])
	SELECT ISNULL(A.[PnlRef],0) AS PnlRef,ISNULL([ProgNo],0) AS ProgNo,ISNULL(A.[PnlNo],0) AS PnlNo,ISNULL([RelNo],0) AS RelNo
			,ISNULL([InpNo],0) AS InpNo,ISNULL([RdrNo],0) AS RdrNo, ISNULL([CatNo],0) AS CatNo ,
			case when [RelNo]>0 and [Control]=1 then 3
				 when [RelNo]>0 and [Control]=3 then 1
				 else [Control] end as [Control],NULL as Active,ISNULL([CatReader],0) as [CatReader]
			,newid() as caObjectID ,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator,'''' as LastWorkStation
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[ActiveLinks] A,['+@sourceServer+'].['+@sourceDB+'].[dbo].[Panel] P
  Where A.PnlRef = P.PnlNo')
----============ [APBAreas] data Migration ========================
DELETE  FROM [dbo].[APBAreas]

Exec('INSERT INTO [dbo].[APBAreas]
           ([AreaNo],[Description],[ParentArea]
			,[AlertOn],[LinkVacant],[LinkOccupied]
			,[AreaCountUp],[LinkOnCountUp],[AreaCountDown]
			,[LinkOnCountDown],[Active],[caObjectID]
			,[LastUpdated],[LastOperator],[LastWorkStation])
	SELECT [AreaNo],[Description],NULL as ParentArea,[AlertOnVacant] as AlertOn,[dbo].[ca_fn_APBAreas](ALPVacant) as LinkVacant
		,[dbo].[ca_fn_APBAreas](ALPOccupied) as LinkOccupied, [AreaCountUp],[dbo].[ca_fn_APBAreas](ALPOnCountUp) as LinkOnCountUp,
		[AreaCountDown],[dbo].[ca_fn_APBAreas](ALPOnCountDown) as LinkOnCountDown,[Active],newid() as caObjectID,
		ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],''1EEB696C-0E28-4702-8598-C379C95B45E1'' as LastOperator,'''' as LastWorkStation
  FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[APBAreas]')    

  EXEC('UPDATE [dbo].[APBAreas] SET AreaCountUp=65535 WHERE AreaCountUp > 65535
       UPDATE [dbo].[APBAreas] SET AreaCountDown=65535 WHERE AreaCountDown > 65535')

exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from APBAreas')

----============ [Nested APBAreas] data Migration ========================
DELETE FROM [dbo].[APBNestedAreas]
  
Exec('INSERT INTO [dbo].[APBNestedAreas]([Node],[AreaNo],[NestName],[Level],[Parent],[caObjectId],[LastUpdated],[LastOperator])
SELECT ATD.Node,ATD.AreaNo
		,CASE WHEN ATD.NodeLevel=0 AND ATD.ParentArea=0 THEN ATM.Description ELSE '''' END AS NestName
		,ATD.NodeLevel as Level, ATD.ParentArea as Parent
        ,NEWID() as caObjectId, getUTCDate()as LastUpdated,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as LastOperator
FROM   ['+@sourceServer+'].['+@sourceDB+'].[dbo].[APBTreeMaster] AS ATM INNER JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].[APBTreeDetail] AS ATD ON ATM.Node = ATD.Node')

----============ [LockdownAreas] data Migration ========================
ALTER TABLE LockdownAreas DISABLE TRIGGER tr_Delete_LockdownAreas
DECLARE @lockDownCount int
DECLARE @CtLockOut int
DELETE  FROM [dbo].[MLockdownAreas]

Exec('INSERT INTO [dbo].[MLockdownAreas]([AreaNo],[AreaName],[Active],[caObjectID],[LastUpdated],[LastOperator])
     SELECT [AreaNo],[Description],0,newid(),ISNULL(Changed,GETUTCDATE()) AS [LastUpdated] ,''1EEB696C-0E28-4702-8598-C379C95B45E1''
     FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[MLockoutAreas]')  

Exec('INSERT INTO [dbo].[LockdownAreas]([AreaNo],[PnlRef],[RdrNo],[LastUpdated],[caObjectID],[LastOperator])
     SELECT [AreaNo],[PnlRef],[RdrNo],ISNULL(Changed,GETUTCDATE()) AS [LastUpdated],newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1''
     FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].[LockoutAreas]')  
set @CtLockOut=0
DECLARE @SQL NVARCHAR(MAX)=N'Select @CtLockOut =(select top 1 case when LockoutGroup1>0 then 1
		 when LockoutGroup2>0 then 1
		 when LockoutGroup3>0 then 1 
		 when LockoutGroup4>0 then 1 
		 when LockoutGroup5>0 then 1 
		 else 0 end
 from ['+@sourceServer+'].['+@sourceDB+'].[dbo].[System])'
 EXEC sp_executesql @SQL, N'@CtLockOut INT OUT',@CtLockOut OUT
 print @CtLockOut
 set @lockDownCount=0
 if @CtLockOut>0
 begin
  select @lockDownCount= count(*) from MLockdownAreas
  set @lockDownCount=@lockDownCount+1
  Exec('INSERT INTO [dbo].[MLockdownAreas]([AreaNo],[AreaName],[Active],[caObjectID],[LastUpdated],[LastOperator]) 
  VALUES(' + @lockDownCount + ',''LockdownArea4K'',0,newid(),GETUTCDATE() ,''1EEB696C-0E28-4702-8598-C379C95B45E1'')')  

  Exec('INSERT INTO [dbo].[LockdownAreas]([AreaNo],[PnlRef],[RdrNo],[LastUpdated],[caObjectID],[LastOperator])
      SELECT ' + @lockDownCount + ', r.PnlRef, r.RdrNo,GETUTCDATE(),newid(),''1EEB696C-0E28-4702-8598-C379C95B45E1'' FROM Reader AS r
      LEFT OUTER JOIN  LockdownAreas AS l ON l.PnlRef = r.PnlRef AND l.RdrNo = r.RdrNo  and l.AreaNo = ' + @lockDownCount + '
      INNER JOIN  Panel AS p ON r.PnlRef = p.PnlNo AND p.PanelType  in (3,5,6,7,8,9,10,11,12,13,14,15,16,17,101,102)') 
 end
exec ('insert into [dbo].[DevicePartitions]  Select '''+@PartitionID+''', caobjectID from	MLockdownAreas ')
ALTER TABLE LockdownAreas ENABLE TRIGGER tr_Delete_LockdownAreas
----============ Badge Data Migration ========================
SET @Query = ''

DELETE FROM BadgePhoto
ALTER TABLE Badge DISABLE TRIGGER tr_Delete_Badge
DELETE FROM Badge
ALTER TABLE Badge ENABLE TRIGGER tr_Delete_Badge
ALTER TABLE Person DISABLE TRIGGER tr_Delete_Person
DELETE FROM Person
ALTER TABLE Person ENABLE TRIGGER tr_Delete_Person
DELETE FROM CompanyInfo
DELETE FROM DepartmentInfo
DELETE FROM LocationInfo
--------------------CompanyInfo-----------------
SET @Query = 'INSERT INTO dbo.CompanyInfo (CompanyID, CompanyName)
SELECT ROW_NUMBER() OVER(ORDER BY CoID ASC) AS CompanyID, CoID AS CompanyName
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge
WHERE LEN(CoID) > 0
GROUP BY CoID'
EXEC (@Query); SET @Query = ''
--------------------DepartmentInfo-----------------
SET @Query = ''
SET @Query = 'INSERT INTO dbo.DepartmentInfo (DepartmentID, DepartmentName)
SELECT ROW_NUMBER() OVER(ORDER BY Dept ASC) AS DepartmentID, Dept AS DepartmentName
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge
WHERE LEN(Dept) > 0
GROUP BY Dept'
EXEC (@Query); SET @Query = ''
--------------------LocationInfo-----------------
SET @Query = ''
SET @Query = 'INSERT INTO dbo.LocationInfo (LocationID, Location)
SELECT ROW_NUMBER() OVER(ORDER BY Location ASC) AS LocationID, Location
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge
WHERE LEN(Location) > 0
GROUP BY Location'
EXEC (@Query); SET @Query = ''
--------------------Person-----------------
CREATE TABLE [#TempBadgeMigration](
	[PersonID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
	[BadgeID] [bigint] NOT NULL DEFAULT ((0)),
	[Facility] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

SET @Query = ''
SET @Query = 'INSERT INTO #TempBadgeMigration(BadgeID, Facility)
		    SELECT Badge, Facility FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge'
EXEC (@Query); SET @Query = ''

SET @Query = ''
SET @Query = 'INSERT INTO Person
      (PersonID, Enabled, LastName, FrstName, MiddleName, Gender, DateOfBirth, DeptID, Phone, LocationID, Vehicle, License, 
		Supervisor, SupervisorID, ContPhone, SSN, 
      CompanyID, Remarks, LastUpdated, caObjectID, LastOperator, LastWorkStation)
SELECT TB.PersonID, Enabled, CASE WHEN LastName IS NULL OR LastName = '''' THEN  ''LastName?'' ELSE LastName END AS LastName,
		CASE WHEN FrstName IS NULL OR FrstName = '''' THEN ''-'' ELSE FrstName END AS FrstName, MiddleName, 
	CASE WHEN Gender=''U'' THEN 0 
		 WHEN Gender=''M'' THEN 1 
		 WHEN Gender=''F'' THEN 2
	END AS Gender, 
	DateOfBirth, (SELECT TOP 1 DepartmentID FROM dbo.DepartmentInfo WHERE DepartmentName = Dept) AS DepartmentID, Phone, 
	(SELECT TOP 1 LocationID FROM dbo.LocationInfo WHERE Location = Badge.Location) AS LocationID, Vehicle, License, 
	CASE WHEN Contact IS NULL OR Contact = '''' THEN 0 ELSE 1 END AS Supervisor, NULL AS SupervisorID, ContPhone, SSN,
	(SELECT TOP 1 CompanyID FROM dbo.CompanyInfo WHERE CompanyName = CoID) AS CompanyID, 
	Remarks, ISNULL(Changed,GETUTCDATE()) as LastUpdated, caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility'
EXEC (@Query); SET @Query = ''

--------------------Badge-----------------
SET @Query = ''
SET @Query = 'INSERT INTO [dbo].[Badge]
          (PersonID, Badge, Facility, PIVI, Pin, Issue, Enabled, Track, Resident, InitLoad, Shunt, DurUse, Escort, AccessTime, ActvDate, ExprDate, Embossed, ShuntId, SDate, 
          UseCount, VehicleTag, EPI_BadgeID, EPI_FORMAT_ID, FirstInControl, APBArea, APBSet, HolidayCalendar, MApbExempt, AgencyCode, Series, PersPIVId, OrgCategory, 
          OrgId, OrgAssoc, SiteNo, NAPCOPermissionID, LastUpdated, caObjectID, LastOperator, LastWorkStation, UTCOffset, Flag, BadgeType)'
SET @Query = @Query + 'SELECT TB.PersonID, ISNULL(Badge,0), ISNULL(Badge.Facility,0) AS Facility, ISNULL(NULLIF(PivI,''''),0) as PIVI,substring(master.dbo.fn_varbintohexstr(Pin),3,len(pin)) as Pin, 
		ISNULL(Issue,0) AS Issue, ISNULL(Enabled,0) AS Enabled, ISNULL(Track,0) AS Track, ISNULL(Resident,1) AS Resident, ISNULL(InitLoad,1) AS InitLoad, ISNULL(Shunt,0) AS Shunt, 
		ISNULL(DurUse,0) AS DurUse, ISNULL(Escort,0) AS Escort, ISNULL(AccessTime,0) AS AccessTime, dateadd(minute,-(select datediff(mi,GetUTCDate(),GetDate())),ActvDate) as ActvDate, 
		DATEADD(DD,1, dateadd(minute,-(select datediff(mi,GetUTCDate(),GetDate())),ExprDate)) as ExprDate, ISNULL(Embossed,0) as Embossed, ISNULL(AlarmShunt,0) AS ShuntId, 
		SDate,ISNULL(UseCount,0) AS UseCount, CASE WHEN APBSet>3 THEN 1 ELSE 0 END AS VehicleTag, NULL AS EPI_BadgeID, EPI_FORMAT_ID, ISNULL(FirstIn,0) AS FirstInControl, 
		ISNULL(APBArea,255) AS APBArea, ISNULL(APBSet,3) AS APBSet, ISNULL(HolidayCalendar,0) AS HolidayCalendar, ISNULL(MApbExempt,0) AS MApbExempt, ISNULL(AgencyCode,0) AS AgencyCode, 
		ISNULL(Series,0) AS Series, 0 as PersPIVId, ISNULL(OrgCat,0) AS OrgCategory, ISNULL(OrgId,0) AS OrgId, ISNULL(OrgAssoc,0) AS OrgAssoc, ISNULL(Site,0) AS SiteNo, 
		ISNULL(NAPCOPermissionID,0) as NAPCOPermissionID, ISNULL(Changed,GETUTCDATE()) as LastUpdated, caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator,
		NULL AS LastWorkStation,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset, ISNULL(Flag,0) as Flag, 
		CASE WHEN (LEN(PivI)>0 AND IsPivI=1)THEN 2 WHEN (LEN(PivI)>0 AND IsPivI=0)THEN 1 ELSE 0 END AS BadgeType
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility'
EXEC (@Query); 
EXEC('UPDATE Badge SET Pin=NULL WHERE Pin=0')
SET @Query = ''
EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT ''' + @PartitionID + ''',PersonID FROM Badge')
--------------------BadgeAccess-----------------
DECLARE @TotalBadgeAccess INT;
DECLARE @MaxAccessGroup INT;
SET @TotalBadgeAccess = 1;
SELECT @MaxAccessGroup = AGCountPerBadge from SystemSettings
WHILE (@TotalBadgeAccess <=@MaxAccessGroup)
BEGIN
	SET @Query = 'INSERT INTO dbo.BadgeAccess (Badge, Facility, AGroupNo,AGroupActivationDate,AGroupExpirationDate,PointAccess, AGSeq, 
			LastUpdated, caObjectID, LastOperator, LastWorkStation, UTCOffset)
			SELECT Badge, Facility, AGroup' + CONVERT(VARCHAR, @TotalBadgeAccess) +', NULL AS AGroupActivationDate, DATEADD(DD,1,dateadd(minute,-(select datediff(mi,GetUTCDate(),GetDate())),AG'+CONVERT(VARCHAR, @TotalBadgeAccess)+'Date)) AS AGroupExpirationDate, 
			PointAccess, '+ CONVERT(VARCHAR, @TotalBadgeAccess) +' AS AGSeq,
			ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge
			WHERE AGroup' + CONVERT(VARCHAR, @TotalBadgeAccess) +' > 0'
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @TotalBadgeAccess = @TotalBadgeAccess + 1
END

--------------------BadgeCategories-----------------
DELETE FROM BadgeCategories;
DECLARE @TotalCatNumber INT;
SET @TotalCatNumber = 1;
WHILE (@TotalCatNumber <=16)
BEGIN
	SET @Query = 'INSERT INTO BadgeCategories (Badge, Facility, CatNumber, LastUpdated, LastWorkstation)
	SELECT Badge, Facility, ' + CAST(@TotalCatNumber AS VARCHAR) + ' AS CatNumber, Changed AS LastUpdated, NULL AS LastWorkStation
	FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge
	WHERE Category' + CAST(@TotalCatNumber AS VARCHAR) + ' > 0'
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @TotalCatNumber = @TotalCatNumber + 1
END

--------------------BadgePhoto-----------------
SET @Query = 'INSERT INTO BadgePhoto(PersonID, ImageTypeID, BadgeImage, LastWorkStation, LastOperator)
SELECT TB.PersonID AS PersonID, 0 AS ImageTypeID, Photo.Photo AS BadgeImage, NULL AS LastWorkStation, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[BadgePhoto] AS Photo
	ON Badge.Badge = Photo.Badge AND Badge.Facility = Photo.Facility INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
WHERE Photo.Photo IS NOT NULL'
EXEC (@Query); SET @Query = ''; 

SET @Query = ''
SET @Query = 'INSERT INTO BadgePhoto(PersonID, ImageTypeID, BadgeImage, LastWorkStation, LastOperator)
SELECT TB.PersonID AS PersonID, 1 AS ImageTypeID, Photo.BinPhoto AS BadgeImage, NULL AS LastWorkStation, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] AS Badge INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[BadgePhoto] AS Photo
	ON Badge.Badge = Photo.Badge AND Badge.Facility = Photo.Facility INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
WHERE Photo.BinPhoto IS NOT NULL'
Exec (@Query); SET @Query = ''
--------------------BadgeUpdate-----------------
DELETE FROM BadgeUpdate
SET @Query = 'INSERT INTO BadgeUpdate(PersonID, Badge, Facility, LVPnl, LVRdr, LVDate, LastOperator, LastWorkStation)
SELECT TB.PersonID AS PersonID, Badge, Badge.Facility, ISNULL(LVPnl, 0) AS LVPnl, ISNULL(LVRdr, 0) AS LVRdr, ISNULL(LVDate, GetUTCDate()) AS LVDate, 
''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility'
EXEC (@Query); SET @Query = ''; SET @Query = ''

--------------------UserFields, UserFieldDef, UserFieldTabs-----------------
DELETE FROM UserFields
DELETE FROM UserFieldDef
DELETE FROM UserFieldTabs

DECLARE @TotalUserFieldTabs INT
SET @TotalUserFieldTabs = 1
WHILE (@TotalUserFieldTabs <=4)
BEGIN
	SET @Query = 'INSERT INTO UserFieldTabs (UserFieldTabID, UserFieldTabName, LastUpdated, caObjectId, LastOperator, LastWorkStation)
	SELECT TOP 1 ' + CAST(@TotalUserFieldTabs AS VARCHAR) + ' AS UserFieldTabID, 
		CustomTabTitle'+CASE WHEN @TotalUserFieldTabs=1 THEN '' ELSE CAST(@TotalUserFieldTabs AS VARCHAR) END+' AS UserFieldTabName, 
		ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
	FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
	WHERE CustomTabTitle' + CASE WHEN @TotalUserFieldTabs=1 THEN '' ELSE CAST(@TotalUserFieldTabs AS VARCHAR) END 
							+ ' IS NOT NULL AND CustomTabTitle' + CASE WHEN @TotalUserFieldTabs=1 THEN '' ELSE CAST(@TotalUserFieldTabs AS VARCHAR) END + ' <> '''''
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @TotalUserFieldTabs = @TotalUserFieldTabs + 1
END

--/////////////////////////////////////////
--UserFieldID in UserFieldDef table is auto increment field
--/////////////////////////////////////////

--FOR TAB 1


CREATE TABLE #TMP_UserFieldMap
(
	Value VARCHAR(100)
)

DECLARE @LastIdentity INT
DECLARE @QueryUserFieldMap NVARCHAR(512)
SET @LastIdentity = -1
DECLARE @TotalUserFieldDef INT
SET @TotalUserFieldDef = 1
WHILE (@TotalUserFieldDef <=11)
BEGIN
	SET @Query = '
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')	
		BEGIN
			INSERT INTO UserFieldDef(UserFieldTabId, UserFieldName, UserFieldType, UserFieldLength, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT  TOP 1 1 AS UserFieldTabId, REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''') AS UserFieldName, 
			CASE WHEN ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=5 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=6 
				OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=7 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=8 THEN ''1'' ELSE ''0'' END AS UserFieldType, 
			CASE WHEN ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=5 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=6 
				OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=7 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=8 THEN 10 ELSE 32 END AS UserFieldLength,
			ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
			WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
			AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''' 

		END'
		EXEC (@Query); SET @Query = '';

		SET @QueryUserFieldMap =''
		SET @QueryUserFieldMap = N'INSERT INTO [#TMP_UserFieldMap] SELECT TOP 1 ''UserField'+ CAST(@TotalUserFieldDef AS VARCHAR) +'§'' + REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''')
								FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
								WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
								AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''''
		EXEC (@QueryUserFieldMap);

		SELECT @LastIdentity = IDENT_CURRENT('UserFieldDef')
		IF EXISTS(SELECT 1 FROM UserFieldDef WHERE UserFieldID = @LastIdentity)
		BEGIN
			SET @Query = '
			IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
			BEGIN
				INSERT INTO UserFields(TB.PersonID, Badge, Facility, UserFieldID, UserFieldValue, LastUpdated, caObjectID, LastOperator, LastWorkStation)
				SELECT TB.PersonID, Badge.Badge, Badge.Facility, '+ CAST(@LastIdentity AS VARCHAR) +' AS UserFieldID, 
				CASE WHEN ' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IN (5,6,7,8) THEN CONVERT(VARCHAR(MAX), CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS DECIMAL(38, 0))) ELSE CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR(MAX)) END AS UserFieldValue,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
				FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
				WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
			END'
			EXEC (@Query); SET @Query = ''; SET @Query = ''
		END
	SET @TotalUserFieldDef = @TotalUserFieldDef + 1
END


SET @TotalUserFieldDef = 12
WHILE (@TotalUserFieldDef <=12)
BEGIN	
	SET @Query = '
	IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')	
		BEGIN
			INSERT INTO UserFieldDef(UserFieldTabId, UserFieldName,UserFieldType, UserFieldLength, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT  TOP 1 1 AS UserFieldTabId, REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''') AS UserFieldName, 
				CASE WHEN ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=5 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=6 
							OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=7 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=8 THEN ''1'' ELSE ''0'' END AS UserFieldType, 
				CASE WHEN ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=5 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=6 
							OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=7 OR ' + CAST(@TotalUserFieldDef AS VARCHAR) + '=8 THEN 10 ELSE 32 END AS UserFieldLength,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
			WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
					AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''' 
		 END'
	EXEC (@Query); SET @Query = ''; SET @Query = ''

	SET @QueryUserFieldMap =''
	SET @QueryUserFieldMap = N'INSERT INTO [#TMP_UserFieldMap] SELECT TOP 1 ''UserField'+ CAST(@TotalUserFieldDef AS VARCHAR) +'§'' + REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''')
								FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
								WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
								AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''''
	EXEC (@QueryUserFieldMap);
	
	SELECT @LastIdentity = IDENT_CURRENT('UserFieldDef')
	IF EXISTS(SELECT 1 FROM UserFieldDef WHERE UserFieldID = @LastIdentity)
	BEGIN
		SET @Query = '
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			DECLARE @UFCOUNT INT
			SET @UFCOUNT=0
			SELECT @UFCOUNT= count(Badge.Badge) FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
					WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
			IF @UFCOUNT>0
			BEGIN
				INSERT INTO UserFields(TB.PersonID, Badge, Facility, UserFieldID, UserFieldValue, LastUpdated, caObjectID, LastOperator, LastWorkStation)
				SELECT TB.PersonID, Badge.Badge, Badge.Facility, '+ CAST(@LastIdentity AS VARCHAR) +' AS UserFieldID, 
					CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR(MAX)) AS UserFieldValue,
					ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
				FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
				WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
			END
		END'
		EXEC (@Query); SET @Query = ''; SET @Query = ''
	END
	SET @TotalUserFieldDef = @TotalUserFieldDef + 1
END

--FOR TAB 2
SET @TotalUserFieldDef = 13
WHILE (@TotalUserFieldDef <=24)
BEGIN
	SET @Query = '
	
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle2 IS NOT NULL AND CustomTabTitle2 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFieldDef(UserFieldTabId, UserFieldName, UserFieldType, UserFieldLength, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TOP 1 2 AS UserFieldTabId, REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''') AS UserFieldName,
			 ''0'' AS UserFieldType, 32 AS UserFieldLength,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
			WHERE CustomTabTitle2 IS NOT NULL AND CustomTabTitle2 <> '''' 
					AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''' 
		END'
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @QueryUserFieldMap =''
	SET @QueryUserFieldMap = N'INSERT INTO [#TMP_UserFieldMap] SELECT TOP 1 ''UserField'+ CAST(@TotalUserFieldDef AS VARCHAR) +'§'' + REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''')
								FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
								WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
								AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''''
	EXEC (@QueryUserFieldMap);

	SELECT @LastIdentity = IDENT_CURRENT('UserFieldDef')
	IF EXISTS(SELECT 1 FROM UserFieldDef WHERE UserFieldID = @LastIdentity)
	BEGIN
		SET @Query = '
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle2 IS NOT NULL AND CustomTabTitle2 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFields(TB.PersonID, Badge, Facility, UserFieldID, UserFieldValue, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TB.PersonID, Badge.Badge, Badge.Facility, '+ CAST(@LastIdentity AS VARCHAR) +' AS UserFieldID, UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS UserFieldValue,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
			WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
		END'
		EXEC (@Query); SET @Query = ''; SET @Query = ''
	END
	SET @TotalUserFieldDef = @TotalUserFieldDef + 1
END

--FOR TAB 3
SET @TotalUserFieldDef = 25
WHILE (@TotalUserFieldDef <=36)
BEGIN
	SET @Query = '
	IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle3 IS NOT NULL AND CustomTabTitle3 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFieldDef(UserFieldTabId, UserFieldName, UserFieldType, UserFieldLength, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TOP 1 3 AS UserFieldTabId, REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''') AS UserFieldName,
			 ''0'' AS UserFieldType, 32 AS UserFieldLength,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
			WHERE CustomTabTitle3 IS NOT NULL AND CustomTabTitle3 <> '''' 
					AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''' 
		END'
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @QueryUserFieldMap =''
	SET @QueryUserFieldMap = N'INSERT INTO [#TMP_UserFieldMap] SELECT TOP 1 ''UserField'+ CAST(@TotalUserFieldDef AS VARCHAR) +'§'' + REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''')
								FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
								WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
								AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''''
	EXEC (@QueryUserFieldMap);
	
	SELECT @LastIdentity = IDENT_CURRENT('UserFieldDef')
	IF EXISTS(SELECT 1 FROM UserFieldDef WHERE UserFieldID = @LastIdentity)
	BEGIN
		SET @Query = '
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle3 IS NOT NULL AND CustomTabTitle3 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFields(TB.PersonID, Badge, Facility, UserFieldID, UserFieldValue, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TB.PersonID, Badge.Badge, Badge.Facility, '+ CAST(@LastIdentity AS VARCHAR) +' AS UserFieldID, UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS UserFieldValue,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
			WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
		END'
		EXEC (@Query); SET @Query = ''; SET @Query = ''
	END
	SET @TotalUserFieldDef = @TotalUserFieldDef + 1
END

--FOR TAB 4
SET @TotalUserFieldDef = 37
WHILE (@TotalUserFieldDef <= 48)
BEGIN
	SET @Query = '
	IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle3 IS NOT NULL AND CustomTabTitle3 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFieldDef(UserFieldTabId, UserFieldName,UserFieldType, UserFieldLength, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TOP 1 4 AS UserFieldTabId, REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''') AS UserFieldName, 
			''0'' AS UserFieldType, 32 AS UserFieldLength,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
			WHERE CustomTabTitle4 IS NOT NULL AND CustomTabTitle4 <> '''' 
					AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''' 
		END'
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @QueryUserFieldMap =''
	SET @QueryUserFieldMap = N'INSERT INTO [#TMP_UserFieldMap] SELECT TOP 1 ''UserField'+ CAST(@TotalUserFieldDef AS VARCHAR) +'§'' + REPLACE([CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'],'' '','''')
								FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System]
								WHERE CustomTabTitle IS NOT NULL AND CustomTabTitle <> '''' 
								AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''''
	EXEC (@QueryUserFieldMap);

	SELECT @LastIdentity = IDENT_CURRENT('UserFieldDef')
	IF EXISTS(SELECT 1 FROM UserFieldDef WHERE UserFieldID = @LastIdentity)
	BEGIN
		SET @Query = '
		IF EXISTS(SELECT 1 FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[System] WHERE CustomTabTitle3 IS NOT NULL AND CustomTabTitle3 <> '''' AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] IS NOT NULL AND [CustFldLbl'+ CAST(@TotalUserFieldDef AS VARCHAR) +'] <> '''')
		BEGIN
			INSERT INTO UserFields(TB.PersonID, Badge, Facility, UserFieldID, UserFieldValue, LastUpdated, caObjectID, LastOperator, LastWorkStation)
			SELECT TB.PersonID, Badge.Badge, Badge.Facility, '+ CAST(@LastIdentity AS VARCHAR) +' AS UserFieldID, UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS UserFieldValue,
				ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkStation
			FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Badge] INNER JOIN [#TempBadgeMigration] as TB on Badge.Badge=TB.BadgeID and Badge.Facility=TB.Facility
			WHERE UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' IS NOT NULL AND CAST(UserField' + CAST(@TotalUserFieldDef AS VARCHAR) + ' AS VARCHAR) <> '''' 
		END'
		EXEC (@Query); SET @Query = ''; 
	END
	SET @TotalUserFieldDef = @TotalUserFieldDef + 1
END

Drop Table [#TempBadgeMigration]
DECLARE @Outputs VARCHAR(8000) 
SELECT @Outputs = COALESCE(@Outputs + '^', '') + Value FROM [#TMP_UserFieldMap]
SET @QUERY = 'INSERT INTO dbo.GlobalVariables (variableName, variableValue)
SELECT ''EPIFieldmap'' AS variableName,'''+ @Outputs +''' AS variableValue'
EXEC (@Query); SET @Query = ''; 
DROP TABLE #TMP_UserFieldMap

--============Threat Level Management============
DELETE FROM dbo.ActiveThreatLevel
DELETE FROM MActiveThreatLevel

SET @Query = 'INSERT INTO dbo.MActiveThreatLevel (LockdownID, Description, LockdownColor, Active, LockoutArea, LastUpdated, caObjectID, LastOperator, LastWorkstation, DenyAccess)
SELECT TOP 1 1 AS LockdownID, ISNULL(LockoutText1,''LockdownID1'') AS Description, ISNULL(LockoutColor1,0) AS LockdownColor, CASE WHEN (LockoutItem & 1) = 1 THEN 1 ELSE 0 END AS Active,' + CAST(@lockDownCount AS VARCHAR) + 'AS LockoutArea
		, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkstation, 0 AS DenyAccess
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System
UNION
SELECT TOP 1 2 AS LockdownID, ISNULL(LockoutText2,''LockdownID2'') AS Description, ISNULL(LockoutColor2,0) AS LockdownColor, CASE WHEN (LockoutItem & 2) = 2 THEN 1 ELSE 0 END AS Active, ' + CAST(@lockDownCount AS VARCHAR) + ' AS LockoutArea
		, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkstation, 0 AS DenyAccess
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System
UNION
SELECT TOP 1 3 AS LockdownID, ISNULL(LockoutText3,''LockdownID3'') AS Description, ISNULL(LockoutColor3,0) AS LockdownColor, CASE WHEN (LockoutItem & 4) = 4 THEN 1 ELSE 0 END AS Active,' + CAST(@lockDownCount AS VARCHAR) + ' AS LockoutArea
		, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkstation, 0 AS DenyAccess
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System
UNION
SELECT TOP 1 4 AS LockdownID, ISNULL(LockoutText4,''LockdownID4'') AS Description, ISNULL(LockoutColor4,0) AS LockdownColor, CASE WHEN (LockoutItem & 8) = 8 THEN 1 ELSE 0 END AS Active, ' + CAST(@lockDownCount AS VARCHAR) + ' AS LockoutArea
		, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkstation, 0 AS DenyAccess
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System
UNION
SELECT TOP 1 5 AS LockdownID, ISNULL(LockoutText5,''LockdownID5'') AS Description, ISNULL(LockoutColor5,0) AS LockdownColor, CASE WHEN (LockoutItem & 16) = 16 THEN 1 ELSE 0 END AS Active, ' + CAST(@lockDownCount AS VARCHAR) + ' AS LockoutArea
		, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, NULL AS LastWorkstation, 0 AS DenyAccess
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System'
EXEC (@Query); SET @Query = ''; SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.MActiveThreatLevel')

--/////////////////////ActiveThreatLevel/////////////
DECLARE @TotalLockOutGroup AS INT
SET @TotalLockOutGroup = 1
WHILE (@TotalLockOutGroup <=5)
BEGIN
	SET @QUERY =''
	SET @QUERY = 'INSERT INTO dbo.ActiveThreatLevel (LockdownID, CatNumber, LastUpdated) SELECT LockdownID, CatNumber, LastUpdated FROM '
	SET @QUERY = @QUERY + '(SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 1) = 1 THEN 1 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 2) = 2 THEN 2 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 4) = 4 THEN 3 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 8) = 8 THEN 4 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 16) = 16 THEN 5 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 32) = 32 THEN 6 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 64) = 64 THEN 7 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 128) = 128 THEN 8 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 256) = 256 THEN 9 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 512) = 512 THEN 10 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 1024) = 1024 THEN 11 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 2048) = 2048 THEN 12 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 4096) = 4096 THEN 13 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 8192) = 8192 THEN 14 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 16384) = 16384 THEN 15 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 32768) = 32768 THEN 16 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System UNION SELECT TOP 1 ' + CAST(@TotalLockOutGroup AS VARCHAR) +' AS LockdownID, CASE WHEN (LockoutGroup' + CAST(@TotalLockOutGroup AS VARCHAR) +' & 65536) = 65536 THEN 17 ELSE -1 END AS CatNumber, ISNULL(Changed,GETUTCDATE()) as LastUpdated '
	SET @QUERY = @QUERY + 'FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System) AS TBL_System WHERE CatNumber > -1'
	EXEC (@Query)
	SET @TotalLockOutGroup = @TotalLockOutGroup + 1
END
--////////////////ThreatLevelCategories/////////////////
--DECLARE @TotalCatNumber AS INT
SET @TotalCatNumber = 1
WHILE (@TotalCatNumber <=16)
BEGIN
	SET @QUERY = 'UPDATE dbo.ThreatLevelCategories
	SET Description= ISNULL((SELECT TOP 1 CatText' + CAST(@TotalCatNumber AS VARCHAR)+' FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.System),''Category'+CAST(@TotalCatNumber AS VARCHAR)+''')
	WHERE CatNumber = ' + CAST(@TotalCatNumber AS VARCHAR)+''
	EXEC (@Query); SET @Query = ''; SET @Query = ''
	SET @TotalCatNumber = @TotalCatNumber + 1
END


--============NAPCO Integration============
DELETE FROM napcoPermissions
DELETE FROM napcoMPermissions
DELETE FROM napcoDeviceStatusDefs
DELETE FROM napcoDeviceStatus
DELETE FROM napcoPanelRelayEventDef
DELETE FROM napcoPanelRelays
DELETE FROM napcoPanelZoneEventDef
DELETE FROM napcoPanelZones
DELETE FROM napcoPanelAreaEventDef
DELETE FROM napcoPanelAreas
DELETE FROM napcoPanelEventDef
DELETE FROM napcoPanels
DELETE FROM NapcoWorkstations

--/////////////////////Napco Workstations//////////////////
SET @QUERY = 'INSERT INTO dbo.NapcoWorkstations (workstationID, pollingUNCName, LastWorkStation, LastOperator, caObjectID)
SELECT workstationID, pollingUNCName, '''' AS LastWorkStation, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, newid() AS caObjectID 
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoWorkstations'
EXEC (@Query); SET @Query = ''


IF EXISTS(SELECT workstationID FROM dbo.NapcoWorkstations)
BEGIN
	--/////////////////////napco Panels//////////////////
	SET @QUERY = 'INSERT INTO dbo.napcoPanels
			(AlarmPanelID, workstationID, PanelType, PanelName, ConnectionType, PortNumber, SocketAddress, MasterProgramCode, Enabled, PCSecurityCode_1, 
			PCSecurityCode_2, PCSecurityCode_3, caObjectID, LastUpdated, LastOperator, LastWorkStation, ZoneId) 
	SELECT	AlarmPanelID, np.workstationID, PanelType, PanelName, ConnectionType, PortNumber, SocketAddress, MasterProgramCode, Enabled, PCSecurityCode_1, 
			PCSecurityCode_2, PCSecurityCode_3, np.caObjectID, ISNULL(Changed,GETUTCDate()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation, NULL AS ZoneId
	FROM	['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanels  np join dbo.napcoWorkstations nw on nw.workstationID=np.workstationID'
	EXEC (@Query); SET @Query = ''

	EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoPanels')
	
	IF EXISTS(SELECT AlarmPanelID FROM dbo.napcoPanels)
	BEGIN
		SET @QUERY = 'INSERT INTO dbo.napcoPanelUsers (AlarmPanelID, UserNumber, UserConfigured, Username, LinkToBadge, Facility, Badge, LastUpdated, caObjectID)
		SELECT npu.AlarmPanelID, UserNumber, 1 AS UserConfigured, NULL AS Username, 1 AS LinkToBadge, Facility, Badge, ISNULL(Changed,GETUTCDATE()) as LastUpdated, newid() AS caObjectID 
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelUsers npu join dbo.napcoPanels np on np.AlarmPanelID=npu.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		SET @QUERY = 'INSERT INTO dbo.napcoPanelEventDef (AlarmPanelID, statusCode, GenerateEvent, EventPriority, ResponseRequired)
		SELECT npe.AlarmPanelID, statusCode, GenerateEvent, EventPriority, ResponseRequired FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelEventDef
		npe join dbo.napcoPanels np on np.AlarmPanelID=npe.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		--/////////////////////napco Device Status//////////////////
		SET @QUERY = 'INSERT INTO dbo.napcoDeviceStatus (deviceType, alarmPanelID, deviceID, deviceStatus, lastUpdate, caObjectID)
		SELECT deviceType, nds.alarmPanelID, deviceID, deviceStatus, ISNULL(lastUpdate,GETUTCDate()) AS LastUpdated, newid() AS caObjectID
		FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[napcoDeviceStatus] nds join dbo.napcoPanels np on np.AlarmPanelID=nds.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoDeviceStatus')

		SET @QUERY = 'INSERT INTO dbo.napcoDeviceStatusDefs (deviceStatus, statusName)
		SELECT [deviceStatus], [statusName] FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[napcoDeviceStatusDefs]'
		EXEC (@Query); SET @Query = ''
		
		--/////////////////////napco Panels Relays//////////////////
		SET @QUERY = 'INSERT INTO dbo.napcoPanelRelays (AlarmPanelID, RelayNumber, RelayConfigured, RelayName, caObjectID, LastUpdated, LastOperator, LastWorkStation)
		SELECT npr.AlarmPanelID, RelayNumber, RelayConfigured, RelayName, npr.caObjectID, ISNULL(Changed,GETUTCDate()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
		FROM  ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelRelays npr join dbo.napcoPanels np on np.AlarmPanelID=npr.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoPanelRelays')

		SET @QUERY = 'INSERT INTO dbo.napcoPanelRelayEventDef (AlarmPanelID, RelayNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired)
		SELECT npre.AlarmPanelID, RelayNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelRelayEventDef npre join dbo.napcoPanels np on np.AlarmPanelID=npre.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		--/////////////////////napco Panels Zones//////////////////
		SET @QUERY = 'INSERT INTO dbo.napcoPanelZones (AlarmPanelID, ZoneNumber, AreaNumber, ZoneConfigured, ZoneName, DvrServerID, DvrCameraID, VideoStartTime, VideoEndTime, VideoPriorityFrom, 
						VideoPriorityTo, EnableRecording, NoofSecsToRecord, RecordingSchedule, caObjectID, LastUpdated, LastOperator, LastWorkStation)
		SELECT npz.AlarmPanelID, ZoneNumber, AreaNumber, ZoneConfigured, ZoneName, DvrServerID, DvrCameraID, VideoStartTime, VideoEndTime, VideoPriorityFrom, VideoPriorityTo, 
		EnableRecording, NoofSecsToRecord, RecordingSchedule, npz.caObjectID, ISNULL(Changed,GETUTCDate()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelZones npz join dbo.napcoPanels np on np.AlarmPanelID=npz.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoPanelZones')

		SET @QUERY = 'INSERT INTO dbo.napcoPanelZoneEventDef (AlarmPanelID, ZoneNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired)
		SELECT npze.AlarmPanelID, ZoneNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelZoneEventDef npze join dbo.napcoPanels np on np.AlarmPanelID=npze.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		--/////////////////////napco Panels Areas//////////////////
		SET @QUERY = 'INSERT INTO dbo.napcoPanelAreas (AlarmPanelID, AreaNumber, AreaConfigured, AreaName, alarmAreaType, ManualPriv, UseRelayControl, IsAccessControlPanel, 
					RelayControlPanel, RelayControlRelay, caObjectID, LastUpdated, LastOperator, LastWorkStation)
		SELECT npa.AlarmPanelID, AreaNumber, AreaConfigured, AreaName, alarmAreaType, ManualPriv, UseRelayControl, IsAccessControlPanel, 
		RelayControlPanel, RelayControlRelay, npa.caObjectID, ISNULL(Changed,GETUTCDate()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelAreas npa join dbo.napcoPanels np on np.AlarmPanelID=npa.AlarmPanelID'
		EXEC (@Query); SET @Query = ''

		EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoPanelAreas')

		SET @QUERY = 'INSERT INTO dbo.napcoPanelAreaEventDef (AlarmPanelID, AreaNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired, RelayControlAction)
		SELECT npae.AlarmPanelID, AreaNumber, statusCode, GenerateEvent, EventPriority, ResponseRequired, RelayControlAction
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPanelAreaEventDef npae join dbo.napcoPanels np on np.AlarmPanelID=npae.AlarmPanelID'
		EXEC (@Query); SET @Query = ''
		
		--/////////////////////napco MPermissions//////////////////
		SET @QUERY = 'INSERT INTO dbo.napcoMPermissions (PermissionID, PermissionName, LastUpdated, caObjectID, LastWorkStation, LastOperator)
		SELECT PermissionID, PermissionName, ISNULL(Changed,GETUTCDate()) AS LastUpdated, newid() AS caObjectID, '''' AS LastWorkStation, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoMPermissions
		WHERE PermissionID > 0'
		EXEC (@Query); SET @Query = ''

		EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.napcoMPermissions')

		SET @QUERY = 'INSERT INTO dbo.napcoPermissions (PermissionID, AlarmPanelID, AreaNumber, CanArm, CanDisarm)
		SELECT PermissionID, nps.AlarmPanelID, AreaNumber, CanArm, CanDisarm 
		FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.napcoPermissions nps join dbo.napcoPanels np on np.AlarmPanelID=nps.AlarmPanelID'
		EXEC (@Query); SET @Query = ''
	END
END

--============Central Station============
DELETE FROM dbo.CentralStationLinks
DELETE FROM dbo.CentralStationServer

SET @QUERY = 'INSERT INTO dbo.CentralStationServer (centralStationId, centralStationName, ipAddress, accountNumber, networkPort, macaddress)
SELECT centralStationId, centralStationName, ipAddress, accountNumber, networkPort, MacAddress 
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.CentralStationServer'
EXEC (@Query); SET @Query = ''

SET @QUERY = 'INSERT INTO dbo.CentralStationLinks (cat, pnlNo, Number, status, workstationId, facility, badge, centralStationId, codeToReport, shouldReport, areaNum, zoneNum, 
		caObjectID, LastOperator, LastWorkStation)
SELECT CASE WHEN cat=20 THEN 30 WHEN cat=21 THEN 31 WHEN cat=22 THEN 32 WHEN cat=23 THEN 33 else cat end as cat, pnlNo, Number, 
 CASE WHEN cat=0 and status=0 THEN 1
	  WHEN cat=0 and status=1 THEN 3
	  WHEN cat=0 and status=2 THEN 13
	  WHEN cat=0 and status=6 THEN 5
	  WHEN cat=0 and status=8 THEN 9
	  WHEN cat=0 and status=10 THEN 21
	  WHEN cat=0 and status=11 THEN 100
	  WHEN cat=0 and status=12 THEN 102
	  WHEN cat=0 and status=13 THEN 103
	  WHEN cat=0 and status=14 THEN 105
	  WHEN cat=0 and status=15 THEN 107
	  WHEN cat=0 and status=16 THEN 109
	  WHEN cat=0 and status=17 THEN 111
	  WHEN cat=0 and status=18 THEN 40
	  WHEN cat=0 and status=19 THEN 42
	  WHEN cat=0 and status=23 THEN 44
	  WHEN cat=0 and status=24 THEN 17
	  WHEN cat=0 and status=28 THEN 29
	  WHEN cat=0 and status=29 THEN 27
	  WHEN cat=5 and status=0 THEN 1
	  WHEN cat=5 and status=1 THEN 2
	  WHEN cat=5 and status=2 THEN 5
	  WHEN cat=5 and status=3 THEN 7
	  WHEN cat=5 and status=6 THEN 9
	  WHEN cat=1 and status=0 THEN 2
	  else status end as status
, workstationId, facility, badge, centralStationId, codeToReport, shouldReport, areaNum, zoneNum,
		newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.CentralStationLinks'
EXEC (@Query); SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.CentralStationLinks')

--============Scripting============
DELETE FROM dbo.alarmLink_ScriptLinks
DELETE FROM dbo.alarmLink_scripts
DELETE FROM dbo.MessageTemplates
DELETE FROM dbo.smsProviders
DELETE FROM dbo.snppProviders
DELETE FROM dbo.ScriptingServers

--/////////////////Scripting Servers//////////////////
SET @QUERY = 'INSERT INTO dbo.ScriptingServers (workstationID, pollingUNCName, LastWorkStation, LastOperator, caObjectID)
SELECT workstationID, pollingUNCName, '''' AS LastWorkStation, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, newid() AS caObjectID
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.scriptingServers'
EXEC (@Query); SET @Query = ''
--/////////////////Snpp Providers//////////////////
SET @QUERY = 'INSERT INTO dbo.snppProviders (snppHostID, snppHostName, snppServer, snppPort, LastOperator, LastWorkStation, caObjectID)
SELECT snppHostID, snppHostName, snppServer, snppPort, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation, newid() AS caObjectID
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.snppProviders'
EXEC (@Query); SET @Query = ''
--/////////////////SMS Providers//////////////////
SET @QUERY = 'INSERT INTO smsProviders (smsHostID,smsHostName,smsServer,smsType,smsParams,smsPassword,LastOperator,LastWorkStation,caObjectID)
SELECT SmsHostID,SmsHostName,SmsServerURL,
	   CASE WHEN SmsType=''EMAIL'' THEN 0
	        WHEN SmsType=''WEBPOST'' THEN 1 ELSE null END AS smsType,
       SmsParams,SmsPassword,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation, newid() AS caObjectID
  FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.SmsProviders'
EXEC (@Query); SET @Query = ''
--/////////////////Message Templates//////////////////
SET @QUERY = 'INSERT INTO dbo.MessageTemplates (templateID, subject, body, allowSNPP, allowEmail,include)
SELECT templateID, subject, body, allowSNPP, allowEmail,isnull(include,0) as include FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.messageTemplates'
EXEC (@Query); SET @Query = ''
--/////////////////Alarm Link Scripts//////////////////
SET @QUERY = 'INSERT INTO dbo.alarmLink_scripts (scriptID, scriptName, procName, scriptText, scriptLanguage, compiledScript, generateEvent, executeEventPriority, 
			caObjectID, LastUpdated, LastOperator, LastWorkStation)
SELECT scriptID, scriptName, procName, scriptText, ''vb'' AS scriptLanguage, compiledScript, generateEvent, executeEventPriority,
		 newid() AS caObjectID, ISNULL(Changed, GETUTCDATE()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.alarmLink_scripts'
EXEC (@Query); SET @Query = ''
EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.alarmLink_scripts')
--/////////////////Alarm Link Scripts Links//////////////////
SET @QUERY = 'INSERT INTO dbo.alarmLink_ScriptLinks (cat, pnlNo, DeviceNo, status, workstationID, facility, badge, scriptID, scriptLinkName, EventClassID, LastUpdated, scriptLinkEnabled, 
			caObjectID, LastOperator, LastWorkStation)
SELECT CASE WHEN ALSL.cat = 23 THEN 33
			WHEN ALSL.cat = 22 THEN 32
			WHEN ALSL.cat = 21 THEN 31
			WHEN ALSL.cat = 20 THEN 30
			ELSE ALSL.cat
		END AS cat, 
		pnlNo, Number AS DeviceNo, status, workstationID, facility, badge, scriptID, scriptLinkName, 
		ISNULL(CASE WHEN ALSL.cat = 23 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = 33 AND ECD.Status = ALSL.status)----NAPCO Relay
			 WHEN ALSL.cat = 22 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = 32 AND ECD.Status = ALSL.status)----NAPCO Zone
			 WHEN ALSL.cat = 21 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = 31 AND ECD.Status = ALSL.status)----NAPCO Area
			 WHEN ALSL.cat = 20 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = 30 AND ECD.Status = ALSL.status)----NAPCO Panel
			 WHEN ALSL.cat = 5 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = cat AND ECD.EventCode = ALSL.status)----DOOR
			 WHEN ALSL.cat = 4 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = cat AND ECD.EventCode = ALSL.status)----PANEL
			 WHEN ALSL.cat = 3 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = cat AND ECD.EventCode = ALSL.status)----LINK
			 WHEN ALSL.cat = 2 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = cat AND ECD.EventCode = ALSL.status)----RELAY
			 WHEN ALSL.cat = 1 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = cat AND ECD.EventCode = ALSL.status)----INPUT
			 WHEN ALSL.cat = 0 THEN (SELECT TOP 1 ECD.[EventClassID] FROM ['+@LiveEventDatabase+'].[dbo].[EventClassDefs] AS ECD WHERE ECD.DeviceCode = 0 AND CASE WHEN ALSL.status IN (28) THEN ECD.Status ELSE ECD.EventCode END = CASE WHEN ALSL.status IN (28) THEN 29 ELSE ALSL.status END)----BADGE
		END, 0) AS EventClassID, GETUTCDATE() AS LastUpdated, scriptLinkEnabled,	newid() AS caObjectID, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
FROM ['+@sourceServer+'].['+@sourceDb+'].dbo.alarmLink_ScriptLinks AS ALSL'
EXEC (@Query); SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.alarmLink_ScriptLinks')
----============ [Add Operator Partition] ========================	


----============ [SchedEvent] ========================
DELETE FROM [dbo].[SchedEvent]
SET @QUERY ='INSERT INTO [dbo].[SchedEvent]
	([StartDate],[EndDate],[Device],[PnlNo],[DeviceNo],[GroupNo]
      ,[Schedule],[FieldNo],[Description],[GroupName],[IsActive],[BadgeFrom]
      ,[BadgeTo],[FieldIn],[ValueIn],[FieldOut],[ValueOut],[Status]
      ,[LastOperator],[caObjectId],[LastUpdated])
 SELECT dateadd(minute,-(select datediff(mi,GetUTCDate(),GetDate())),StartDate), DATEADD(DD,1, dateadd(minute,-(select datediff(mi,GetUTCDate(),GetDate())),EndDate))
 ,CASE WHEN [Device]=1 THEN 0
		WHEN [Device]=3 THEN 1
		WHEN [Device]=4 THEN 2
		WHEN [Device]=7 THEN 3 END AS [Device]
 ,[PnlNo],[Number],[GroupNo],[Schedule] ,[FieldNo],[Description],[GroupName],[IsActive],[BadgeFrom],[BadgeTo],[FieldIn],[ValueIn] ,[FieldOut],[ValueOut]
 ,[Status],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator],newid() AS [caObjectID],getUTCDate() AS [LastUpdated]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].SchedEvent'
EXEC (@Query); SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.SchedEvent')


----============ [Shunt] ========================
DELETE FROM [dbo].[Shunt]
DELETE FROM [dbo].[MShunt] WHERE [ShuntId] <> 0
SET @QUERY ='INSERT INTO .[dbo].[MShunt]
	([ShuntId],[ShuntName],[Description],[Active],[LastUpdated],[caObjectID],[LastOperator])
	Select [Groups].[ItemNo] AS [ShuntId], [Groups].[Name] as [ShuntName]
		, null as [Description]
		,[Groups].[Activ] as [Active]
		,ISNULL([Groups].Changed,GETUTCDATE()) AS [LastUpdated]
		,newid() as [caObjectID]
		,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as [LastOperator]
		FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Groups]
		where [GroupId]=3 and [ItemNo]>0'
EXEC (@Query); SET @Query = ''	
EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.MShunt where [ShuntId]>0')
		
SET @QUERY ='insert into [dbo].[Shunt]
	([ShuntId],[PnlRef],[InpRef],[LastUpdated],[caObjectID],[LastOperator])
    SELECT [MShunt].[ShuntId],[PnlRef],[InpNo]
		,ISNULL([Groups].Changed,GETUTCDATE()) AS [LastUpdated]
		,newid() as [caObjectID]
		,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as [LastOperator]
		FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Input]
		INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Groups] ON [Input].[GroupNo]=[Groups].[ItemNo]
		INNER JOIN [dbo].[MShunt] on [MShunt].[ShuntName]=[Groups].[Name]
		where [GroupId]=3 and [Input].[GroupNo] > 0'
EXEC (@Query); SET @Query = ''

----============ [DVR] ========================
DELETE FROM [dbo].[DvrCamera]
DELETE FROM [dbo].[DvrConfig]

SET @QUERY ='INSERT INTO [dbo].[DvrConfig]([DvrID],[ServerName],[ServerIP],[UserName],[Password],[ConfirmPwd],[UserType],[Active],[EnableNotification],[DVRVendor],[ZoneID],[LastUpdated],[LastOperator])
	        SELECT [DvrID],[ServerName],[ServerIP],[UserName],''w.vUK/JtraA1hjfKX0..wXN0'',''w.vUK/JtraA1hjfKX0..wXN0'',[UserType],[Active],ISNULL([EnableNotification],0) AS [EnableNotification],[DVRVendor],[ZoneId],ISNULL([LastUpdated],GETUTCDATE()) as LastUpdated
			,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator] FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[DvrConfig]'
EXEC (@Query); SET @Query = ''
 
SET @QUERY ='INSERT INTO [dbo].[DvrCamera]([DvrCameraID],[DvrServerID],[CameraName],[LastUpdated],[EnableNotification],[Tzno],[Priority],[MapId],[caObjectID],[LastOperator])
	SELECT [DvrCameraID],[DvrServerID],[CameraName],ISNULL([LastUpdated],GETUTCDATE()) as LastUpdated,[EnableNotification],[Tzno],[Priority],[MapId],newid() [caObjectID],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
	FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[DvrCamera]'
EXEC (@Query); SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from dbo.DvrCamera')

----============ [Facility MAP] ========================
DELETE FROM [dbo].[FacilityMap]
EXEC('INSERT INTO [dbo].[FacilityMap]([MapId],[MapName],[MapDescription],[MapImage],[caObjectID],[LastUpdated],[LastOperator])
SELECT [MapId],[MapName],[MapDescription],[MapImage],NEWID() as [caObjectID],GETUTCDATE() as [LastUpdated],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Map]')


  DELETE FROM [dbo].[FacilityMapPoints]
  EXEC('INSERT INTO [dbo].[FacilityMapPoints]([MapId],[Type],[PanelNo],[DevNo],[PtX],[PtY])
  SELECT [MapNo],[Type],[PanelNo],[DevNo],[PtX]*500,[PtY]*250
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[MapPoints] INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Map ON Map.MapID=MapPoints.MapNo')

  EXEC('UPDATE [dbo].[FacilityMapPoints] SET [PtX]=402.876434326172 WHERE [PtX]>402.876434326172
        UPDATE [dbo].[FacilityMapPoints] SET [PtY]=7.46067428588867 WHERE [PtY]<7.46067428588867')
  
  EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from [dbo].[FacilityMap]')
----============ [MAP] ========================
DELETE FROM [dbo].[MapPoints]
DELETE FROM [dbo].[Map]
SET IDENTITY_INSERT [dbo].[Map] ON
EXEC('INSERT INTO [dbo].[Map]([MapUID],[MapID],[MapName],[MapDescription],[MapImage],[caObjectID],[MapWidth],[MapHeight],[LastUpdated],[LastOperator],[Snapshot])
  SELECT NEWID() AS [MapUID],[MapID],[MapName],[MapDescription],[MapImage],NEWID() AS [caObjectID]
		,[MapWidth],[MapHeight],GETUTCDATE() AS [LastUpdated],''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS [LastOperator],[SnapShot] AS [Snapshot]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[dynMap]')

EXEC('INSERT INTO [dbo].[MapPoints]([MapPointsUID],[MapUID],[DeviceType],[DeviceObjectID],[PointX],[PointY],[MapAPBAreaUID],[DvrCameraID],[DvrServerID],[PanelType])
	SELECT NEWID() AS [MapPointsUID],Map.MapUID as [MapUID],Type as [DeviceType],''00000000-0000-0000-0000-000000000000'' AS [DeviceObjectID],[PtX] AS [PointX],[PtY] AS [PointY],NULL AS [MapAPBAreaUID],
	[DvrCameraID],[DvrServerID],[PanelType]
	FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[dynMapPoints] dMP JOIN [dbo].[Map] ON Map.MapID=dMP.MapId')
 
 EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from [dbo].[Map]')
 SET IDENTITY_INSERT [dbo].[Map] OFF

 EXEC('UPDATE [dbo].[MapPoints] set [DeviceObjectID]= Panel.caObjectID FROM Panel INNER JOIN Map ON Panel.MapId = Map.MapID WHERE Map.[MapUID] = MapPoints.[MapUID]')
--============Operators Data Migration ========================
--============Delete all record except admin ('1EEB696C-0E28-4702-8598-C379C95B45E1')============
DELETE FROM ManualPrivilege
DELETE FROM RolePrivileges WHERE RoleID NOT IN ('82B19834-4EF0-405B-9AD1-23860AAF4139','AFC5A5C7-8834-4EB0-A4CA-307A2C38D135'
					,'2F9D40EE-3B9B-42E9-BE20-35100860298C','C123ADF5-9023-4A9D-83D7-5146DFBAEE77'
					,'F9EE89FC-1971-49BF-959F-57E23350BFD8','0DEDD8D6-C408-4B85-97C8-E34F332A406F')
DELETE FROM OperatorSettings WHERE OperatorID <> '1EEB696C-0E28-4702-8598-C379C95B45E1'
DELETE FROM Operators WHERE OperatorID <> '1EEB696C-0E28-4702-8598-C379C95B45E1'
Delete from RolePlugins where RoleID NOT IN ('82B19834-4EF0-405B-9AD1-23860AAF4139','AFC5A5C7-8834-4EB0-A4CA-307A2C38D135'
											,'2F9D40EE-3B9B-42E9-BE20-35100860298C','C123ADF5-9023-4A9D-83D7-5146DFBAEE77'
											,'F9EE89FC-1971-49BF-959F-57E23350BFD8','0DEDD8D6-C408-4B85-97C8-E34F332A406F')
DELETE FROM Roles
WHERE RoleID NOT IN ('82B19834-4EF0-405B-9AD1-23860AAF4139','AFC5A5C7-8834-4EB0-A4CA-307A2C38D135'
					,'2F9D40EE-3B9B-42E9-BE20-35100860298C','C123ADF5-9023-4A9D-83D7-5146DFBAEE77'
					,'F9EE89FC-1971-49BF-959F-57E23350BFD8','0DEDD8D6-C408-4B85-97C8-E34F332A406F')
					
					
--============ Role  ============				
SET @QUERY ='INSERT INTO  [dbo].[Roles]
	( [RoleID]
      ,[RoleName]
      ,[LastOperator]
      ,[caObjectID]
      ,[LastUpdated])
     SELECT newid() as [RoleID]
			,[Priv] as [RoleName]
			,''1EEB696C-0E28-4702-8598-C379C95B45E1'' as [LastOperator]
			,newid() as [caObjectID]
			,GetUTCDate() AS [LastUpdated]
	FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Privileg]'
EXEC (@Query); SET @Query = ''

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',caobjectID from [dbo].[Roles]')

--==============Transfer all record except admin ('1EEB696C-0E28-4702-8598-C379C95B45E1')==============
SET @Query='UPDATE dbo.Operators SET OperLName=(CASE WHEN CHARINDEX('''', Name) = 0 THEN Name ELSE Substring(Name, 1, CHARINDEX('''', Name)-1) END),
	   EmailID=OperEmailAddress,PagerID=OperSNPPPagerID, LogoffTime=op29.LogOffTime, AutoAckTime=AckTO,LastUpdated=GETUTCDATE(),
	   OperSNPPHostID=op29.OperSNPPHostID, OperUseSNPP=op29.OperUseSNPP, OperUseEmail=op29.OperUseEmail, OperUseSMS=op29.OperUseSMS,
	   OperSMSHostID=op29.OperSMSHostID,PhoneNo=op29.OperPhoneNumber, EventCount= ISNULL(EventViewCount, 100)
	   FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper op29
	   INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Privileg] pv29 on pv29.NoPriv = op29.[Priv]
	   INNER JOIN [dbo].[Roles] rl on rl.RoleName = pv29.Priv WHERE op29.ScrName =''admin'''
EXEC (@Query); SET @Query = ''

EXEC('ALTER TABLE Operators DISABLE TRIGGER tr_Insert_Operators')

SET @Query = 'INSERT INTO dbo.Operators
	(OperatorID, RoleID, OperFName, OperLName, OperLoginName, Enabled, OperPassword, 
	PhoneNo, EmailID, PagerID, LastLoggedIn, LastLoggedInUncName, LogoffTime, AutoAckTime, 
	LastUpdated, PasswordRequiresUpdate, IsGlobalAdministrator, MaxThreatLevelAllowed, CanChangeThreatLevel, 
	UseLegacyDisplay, ManualPrivileges, OperSNPPHostID, OperUseSNPP, OperUseEmail, ColorScheme, PasswordExpiresIn, LoginAttempts, DisableLogoff, 
	LanguageId,EventCount,LastOperator, LastWorkStation, UTCOffset)
SELECT newid() AS [OperatorID], CASE WHEN op29.Priv=1 THEN ''82B19834-4EF0-405B-9AD1-23860AAF4139'' ELSE RoleID END AS RoleID, 
		CASE WHEN CHARINDEX('' '', Name) = 0 THEN Name ELSE Substring(Name, 1, CHARINDEX('' '', Name)-1) END AS OperFName, 
		CASE WHEN CHARINDEX('' '', Name) > 0 THEN Substring(Name, CHARINDEX('' '', Name) + 1, LEN(Name)) ELSE '''' END AS OperLName, 
		ScrName AS OperLoginName, 1 AS Enabled, ''+6huNpVCE9fFPxrH81j6JXOlGEU='' AS OperPassword, 
		NULL AS PhoneNo, OperEmailAddress AS EmailID, OperSNPPPagerID AS PagerID, Changed AS LastLoggedIn, NULL AS LastLoggedInUncName, LogOffTime AS LogoffTime, AckTO AS AutoAckTime, 
		ISNULL(op29.Changed, GETUTCDATE()) AS LastUpdated, ''True'' AS PasswordRequiresUpdate, CASE WHEN op29.Priv=1 THEN 1 ELSE 0 END AS IsGlobalAdministrator, NULL AS MaxThreatLevelAllowed, NULL AS CanChangeThreatLevel,
		1 AS UseLegacyDisplay, NULL AS ManualPrivileges, OperSNPPHostID, OperUseSNPP, OperUseEmail, NULL AS ColorScheme, NULL AS PasswordExpiresIn, NULL AS LoginAttempts, 0 AS DisableLogoff,
		NULL AS LanguageId, ISNULL(EventViewCount, 0) as EventCount,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
		,(select datediff(mi,GetUTCDate(),GetDate())) as UTCOffset
    FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper op29
	INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Privileg] pv29 on pv29.NoPriv = op29.[Priv]
	INNER JOIN [dbo].[Roles] rl on rl.RoleName = pv29.Priv
    WHERE op29.ScrName <> ''admin'''
EXEC (@Query); SET @Query = ''

EXEC('ALTER TABLE Operators ENABLE TRIGGER tr_Insert_Operators')

EXEC ('INSERT INTO [dbo].[DevicePartitions] SELECT '''+@PartitionID+''',OperatorID from [dbo].[Operators]')


SET @Query = 'INSERT INTO dbo.OperatorSettings(OperatorID, GUIViewMode, UIViewStyle, LanguageID, MapManualControlDblClick, MapToolTipInterval, MapCloseAlert, 
              MapTheme, StatusUpdateInterval, caObjectID, LastUpdated, LastOperator, LastWorkStation)
SELECT Oper.OperatorID, NULL AS GUIViewMode, NULL AS UIViewStyle, 0 AS LanguageID, 
		ManualControlDblClick AS MapManualControlDblClick, ToolTipInterval AS MapToolTipInterval, CloseAlert AS MapCloseAlert,
		Theme AS MapTheme, NULL AS StatusUpdateInterval, newid() AS caObjectID, ISNULL(Oper3000.Changed,GETUTCDATE()) AS LastUpdated, ''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, '''' AS LastWorkStation
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName INNER JOIN (SELECT TOP 1 * FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].System) as systbl  ON Oper.OperLoginName <> ''admin'''
EXEC (@Query); SET @Query = ''

--==============Role RolePlugins==============
SET @Query='INSERT INTO [dbo].[RolePlugins](
	  [RoleID]
      ,[pluginId]
      ,[LastUpdated])
	  SELECT Roles.RoleID, guiPlugins.pluginId, GETUTCDATE() AS LastUpdated
      FROM   Roles CROSS JOIN guiPlugins
      INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].[Privileg] priv29 on priv29.[Priv]=Roles.[RoleName]'
EXEC (@Query); SET @Query = ''

--==============ManualPrivilege==============
SET @Query = 'INSERT INTO dbo.ManualPrivilege(OperatorID, ManualPrivilege)
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 1) = 1 THEN 1 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 2) = 2 THEN 2 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 4) = 4 THEN 3 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 8) = 8 THEN 4 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 16) = 16 THEN 5 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 32) = 32 THEN 6 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 64) = 64 THEN 7 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin''
UNION
SELECT Oper.OperatorID, CASE WHEN (Oper3000.ShowPrior & 128) = 128 THEN 8 ELSE -1 END AS ManualPrivilege
FROM dbo.Operators AS Oper INNER JOIN ['+@sourceServer+'].['+@sourceDb+'].[dbo].Oper AS Oper3000
	ON Oper.OperLoginName = Oper3000.ScrName
--WHERE Oper.OperLoginName <> ''admin'''
EXEC (@Query); SET @Query = ''
--============DELETE NULL VALUES============
DELETE FROM ManualPrivilege WHERE ManualPrivilege = -1

----============ [Partition] data Migration =======================
DELETE FROM Partition WHERE PartitionName <>'Admin Partition'
SET @Query = 'INSERT INTO [dbo].[Partition] ([PartitionName],[DeviceType],[LastUpdated])
      SELECT distinct Groups.Name AS PartitionName,
		COALESCE(CASE when GroupId=0 then 9
			 when GroupId=1 then 2
			 when GroupId=6 then 1
			 when GroupId=7 then 5
			 when GroupId=9 then 8
			 when GroupId=8 then 7
			 when GroupId=12 then 17 
			 when GroupId=13 then 30 
			 when GroupId=14 then 31
			 else NULL END,
			 CASE 
			 when GroupId=15 then 32 
			 when GroupId=16 then 33 
			 when GroupId=17 then 29 
			 when GroupId=19 then 6
			 when GroupId=20 then 19 
			 when GroupId=21 then 10 
			 when GroupId=22 then 21 
			 when GroupId=23 then 22 
			 else GroupId end)AS DeviceType, GETUTCDATE() AS [LastUpdated]
FROM   ['+@sourceServer+'].['+@sourceDB+'].[dbo].[Groups]
where Groups.ItemNo>0'
EXEC (@Query); SET @Query = ''

----============OperatorPartitions============
DELETE FROM [dbo].[OperatorPartitions] WHERE OperatorID <> '1EEB696C-0E28-4702-8598-C379C95B45E1'
SET @Query = 'INSERT INTO dbo.OperatorPartitions(PartitionID, OperatorID, IsPrimaryPartition,Restriction,LastUpdated)
	SELECT ISNULL(Partition.PartitionID, ''3DC78C7B-C9CE-4485-A835-63D577F623E6'') AS PartitionID, Operators.OperatorID,1 AS IsPrimaryPartition,MIN(ISNULL(Sphere.Restriction,0)) as Restriction,
	MAX(ISNULL(Sphere.Changed,GETUTCDATE())) AS LastUpdated 
	FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Oper 
		INNER JOIN [dbo].[Operators] ON Operators.OperLoginName=Oper.ScrName AND Operators.OperLoginName <> ''Admin''
		LEFT JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Sphere ON Oper.Priv = Sphere.OperPrivId and Oper.Priv>1
		LEFT JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Groups ON Sphere.GroupId = Groups.GroupId and Sphere.GroupNo=Groups.ItemNo and Groups.ItemNo>0
		LEFT JOIN [dbo].[Partition] ON Partition.PartitionName=Groups.Name
		GROUP BY ISNULL(Partition.PartitionID, ''3DC78C7B-C9CE-4485-A835-63D577F623E6''), Operators.OperatorID'
EXEC (@Query);

----============RolePartitions============
DELETE FROM [dbo].[RolePartitions] WHERE PartitionID <> '3DC78C7B-C9CE-4485-A835-63D577F623E6'
SET @Query = 'INSERT INTO dbo.RolePartitions(PartitionID, RoleID, Restriction,LastUpdated)
	SELECT Partition.PartitionID, Operators.RoleID, MIN(ISNULL(Sphere.Restriction,0)) as Restriction,
	MAX(ISNULL(Sphere.Changed,GETUTCDATE())) AS LastUpdated 
	FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Oper 
		INNER JOIN [dbo].[Operators] ON Operators.OperLoginName=Oper.ScrName AND Operators.OperLoginName <> ''Admin''
		LEFT JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Sphere ON Oper.Priv = Sphere.OperPrivId and Oper.Priv>1
		LEFT JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Groups ON Sphere.GroupId = Groups.GroupId and Sphere.GroupNo=Groups.ItemNo and Groups.ItemNo>0
		LEFT JOIN [dbo].[Partition] ON Partition.PartitionName=Groups.Name
		where Partition.PartitionID is not null
		GROUP BY Partition.PartitionID, Operators.RoleID'
EXEC (@Query);

----============RolePrivileges============

CREATE TABLE #RoleIDTable( idx SMALLINT PRIMARY KEY IDENTITY(1,1), RoleID NVARCHAR(50))
INSERT #RoleIDTable select RoleID from dbo.Roles WHERE RoleID NOT IN ('82B19834-4EF0-405B-9AD1-23860AAF4139','AFC5A5C7-8834-4EB0-A4CA-307A2C38D135'
					,'2F9D40EE-3B9B-42E9-BE20-35100860298C','C123ADF5-9023-4A9D-83D7-5146DFBAEE77'
					,'F9EE89FC-1971-49BF-959F-57E23350BFD8','0DEDD8D6-C408-4B85-97C8-E34F332A406F')

SELECT * FROM #RoleIDTable
DECLARE [RolePriv] CURSOR LOCAL
FOR
	SELECT RoleID from #RoleIDTable ORDER BY idx
OPEN [RolePriv]
BEGIN TRY
	DECLARE  @RoleID UNIQUEIDENTIFIER
	DECLARE  @msgRole nvarchar(max)
	FETCH NEXT FROM [RolePriv] INTO @RoleID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC ca_sp_InsertRolePrivileges @RoleID ,0
		FETCH NEXT FROM [RolePriv] INTO @RoleID
	END
END TRY
BEGIN CATCH
	set @msgRole=(SELECT ERROR_MESSAGE())
END CATCH
CLOSE [RolePriv]
DEALLOCATE [RolePriv]
DROP TABLE #RoleIDTable

-------Parent ------------
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,3,4,5) THEN 3 
						WHEN Priv_Det.PrivLevel = 2 THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aBadgesIn'' THEN 26
WHEN Priv_Det.MenuItemName=''aAuditTrail'' THEN 27
WHEN Priv_Det.MenuItemName=''aFindUsage'' THEN 13
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''


SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aPanels'' THEN 1
WHEN Priv_Det.MenuItemName=''aReaders'' THEN 2
WHEN Priv_Det.MenuItemName=''aInputs'' THEN 3
WHEN Priv_Det.MenuItemName=''aRelays'' THEN 4
WHEN Priv_Det.MenuItemName=''aLinks'' THEN 5
WHEN Priv_Det.MenuItemName=''aMonitor'' THEN 6
WHEN Priv_Det.MenuItemName=''aCategories'' THEN 7
WHEN Priv_Det.MenuItemName=''aThreatLevelPlaceholder'' THEN 8
WHEN Priv_Det.MenuItemName=''aOperatorInstructionLinks'' THEN 9
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aFacilityCodes'' THEN 10
WHEN Priv_Det.MenuItemName=''aAccessGroups'' THEN 11
WHEN Priv_Det.MenuItemName=''aLockdownAreas'' THEN 12
WHEN Priv_Det.MenuItemName=''aTimeSchedules'' THEN 14
WHEN Priv_Det.MenuItemName=''aPersonnel'' THEN 15 
WHEN Priv_Det.MenuItemName=''aSystemSettings'' THEN 18
WHEN Priv_Det.MenuItemName=''aBadgeFormats'' THEN 19
WHEN Priv_Det.MenuItemName=''aAPBAreas'' THEN 21
WHEN Priv_Det.MenuItemName=''aSchedOpt'' THEN 23
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aOperatorInstructions'' THEN 28
WHEN Priv_Det.MenuItemName=''aHolidaySchedules'' THEN 29
WHEN Priv_Det.MenuItemName=''aDvrConfig'' THEN 30
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

------------Child -----------------
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aPanels'' THEN 1
WHEN Priv_Det.MenuItemName=''aReaders'' THEN 2
WHEN Priv_Det.MenuItemName=''aInputs'' THEN 3
WHEN Priv_Det.MenuItemName=''aRelays'' THEN 4
WHEN Priv_Det.MenuItemName=''aLinks'' THEN 5
WHEN Priv_Det.MenuItemName=''aMonitor'' THEN 6
WHEN Priv_Det.MenuItemName=''aCategories'' THEN 7
WHEN Priv_Det.MenuItemName=''aThreatLevelPlaceholder'' THEN 8
WHEN Priv_Det.MenuItemName=''aOperatorInstructionLinks'' THEN 9
END) AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aFacilityCodes'' THEN 10
WHEN Priv_Det.MenuItemName=''aAccessGroups'' THEN 11
WHEN Priv_Det.MenuItemName=''aLockdownAreas'' THEN 12
WHEN Priv_Det.MenuItemName=''aFindUsage'' THEN 13
WHEN Priv_Det.MenuItemName=''aTimeSchedules'' THEN 14
WHEN Priv_Det.MenuItemName=''aPersonnel'' THEN 15 
WHEN Priv_Det.MenuItemName=''aSystemSettings'' THEN 18
WHEN Priv_Det.MenuItemName=''aBadgeFormats'' THEN 19
WHEN Priv_Det.MenuItemName=''aAPBAreas'' THEN 21
END) AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aSchedOpt'' THEN 23
WHEN Priv_Det.MenuItemName=''aBadgesIn'' THEN 26
WHEN Priv_Det.MenuItemName=''aAuditTrail'' THEN 27
WHEN Priv_Det.MenuItemName=''aOperatorInstructions'' THEN 28
WHEN Priv_Det.MenuItemName=''aHolidaySchedules'' THEN 29
WHEN Priv_Det.MenuItemName=''aDvrConfig'' THEN 30
END) AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

-------- Parent 

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,3,4,5) THEN 3 
						WHEN Priv_Det.PrivLevel=2 THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aDoors'' THEN 34
WHEN Priv_Det.MenuItemName=''aControlRelays'' THEN 35
WHEN Priv_Det.MenuItemName=''aControlLinks'' THEN 36
WHEN Priv_Det.MenuItemName=''aControlALPs'' THEN 51
WHEN Priv_Det.MenuItemName=''aNAPCOControl'' THEN 52
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,3,4,5) THEN 3
						WHEN Priv_Det.PrivLevel=2 THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aWirelessLock'' THEN 53
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aCOMPorts'' THEN 32
WHEN Priv_Det.MenuItemName=''aModemNodes'' THEN 33
WHEN Priv_Det.MenuItemName=''aActivityLinks'' THEN 38
WHEN Priv_Det.MenuItemName=''aOperatorResponses'' THEN 39
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 40
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 41
 WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 42
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 43
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 44
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 45
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 46
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 47
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 48
WHEN Priv_Det.MenuItemName=''aOperators'' THEN 50
WHEN Priv_Det.MenuItemName=''aNAPCOPanels'' THEN 54
WHEN Priv_Det.MenuItemName=''aNAPCORelays'' THEN 55
WHEN Priv_Det.MenuItemName=''aNAPCOZones'' THEN 56
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aNAPCOAreas'' THEN 57
WHEN Priv_Det.MenuItemName=''aNAPCOPermissions'' THEN 58
WHEN Priv_Det.MenuItemName=''aScriptEditor'' THEN 59
WHEN Priv_Det.MenuItemName=''aScriptLinks'' THEN 60
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''


-------- Child 
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aCOMPorts'' THEN 32
WHEN Priv_Det.MenuItemName=''aModemNodes'' THEN 33
WHEN Priv_Det.MenuItemName=''aDoors'' THEN 34
WHEN Priv_Det.MenuItemName=''aControlRelays'' THEN 35
WHEN Priv_Det.MenuItemName=''aControlLinks'' THEN 36
WHEN Priv_Det.MenuItemName=''aActivityLinks'' THEN 38
WHEN Priv_Det.MenuItemName=''aOperatorResponses'' THEN 39
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 40
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 41
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 42
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 43
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 44
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 45
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 46
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 47
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 48
WHEN Priv_Det.MenuItemName=''aOperators'' THEN 50
WHEN Priv_Det.MenuItemName=''aControlALPs'' THEN 51
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE 
WHEN Priv_Det.MenuItemName=''aNAPCOControl'' THEN 52
WHEN Priv_Det.MenuItemName=''aNAPCOPanels'' THEN 54
WHEN Priv_Det.MenuItemName=''aNAPCORelays'' THEN 55
WHEN Priv_Det.MenuItemName=''aNAPCOZones'' THEN 56
WHEN Priv_Det.MenuItemName=''aNAPCOAreas'' THEN 57
WHEN Priv_Det.MenuItemName=''aNAPCOPermissions'' THEN 58
WHEN Priv_Det.MenuItemName=''aScriptEditor'' THEN 59
WHEN Priv_Det.MenuItemName=''aScriptLinks'' THEN 60
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

----------------Manual Archive
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,3,4,5) THEN 3 
						WHEN Priv_Det.PrivLevel = 2 THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aHistoryBackup'' THEN 71
WHEN Priv_Det.MenuItemName=''aViewHistory'' THEN 72
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

----------Parent 
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 62
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 63
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 64
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 65
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 66
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 67
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 68
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 69
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aCentralStationReporting'' THEN 70
WHEN Priv_Det.MenuItemName=''aDvrConfig'' THEN 73
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 74
WHEN Priv_Det.MenuItemName=''aLockdownDoors'' THEN 75
WHEN Priv_Det.MenuItemName in (''aThreatLevelActivate1'',''aThreatLevelActivate2'',''aThreatLevelActivate3'',''aThreatLevelActivate4'',''aThreatLevelActivate5'') THEN 76
WHEN Priv_Det.MenuItemName=''aLockdownAPBArea'' THEN 77
WHEN Priv_Det.MenuItemName=''aDVRRemoteView'' THEN 79
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 80
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 81
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 82
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 83
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 84
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 85
WHEN Priv_Det.MenuItemName=''aWirelessLock'' THEN 106
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

----------Child
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 62
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 63
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 64
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 65
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 66
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 67
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 68
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 69
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aCentralStationReporting'' THEN 70
WHEN Priv_Det.MenuItemName=''aHistoryBackup'' THEN 71
WHEN Priv_Det.MenuItemName=''aViewHistory'' THEN 72
WHEN Priv_Det.MenuItemName=''aDvrConfig'' THEN 73
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 74
WHEN Priv_Det.MenuItemName=''aLockdownDoors'' THEN 75
WHEN Priv_Det.MenuItemName=''aLockdownAPBArea'' THEN 77
WHEN Priv_Det.MenuItemName=''aDVRRemoteView'' THEN 79
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 80
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 81
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 82
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 83
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 84
WHEN Priv_Det.MenuItemName=''aConfigurationReports'' THEN 85
WHEN Priv_Det.MenuItemName=''aWirelessLock'' THEN 106
END)  AND ScreenObjectID > 0'
EXEC (@Query); SET @Query = ''


----------------Script Wizard
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aScriptWizard'' THEN 78
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

----------------ThreatLevel
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName in (''aThreatLevelActivate1'',''aThreatLevelActivate2'',''aThreatLevelActivate3'',''aThreatLevelActivate4'',''aThreatLevelActivate5'') THEN 76
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

----------------Map
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 101
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 102
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 103
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 104
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 105
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 107
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 108
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 4 
WHEN Priv_Det.PrivLevel = 2 THEN 8 
WHEN Priv_Det.PrivLevel = 3 THEN 5
WHEN Priv_Det.PrivLevel = 4 THEN 6
WHEN Priv_Det.PrivLevel = 5 THEN 7 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aMaps'' THEN 109
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 8 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName =''aMaps'' THEN 110
END) AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''
------------- Lockdown Status
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName IN(''miLockdown'') THEN 6
END) AND ScreenObjectID=7'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName IN(''miManual'') THEN 6
END) AND ScreenObjectID=8'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName IN(''miReader'') THEN 6
END) AND ScreenObjectID=9'
EXEC (@Query); SET @Query = ''


SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN(1,2,3,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''miRemoveWorkstations'' THEN 22 END) AND ScreenObjectID=3'
EXEC (@Query); SET @Query = ''
-----------------------Alert Grid
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertAcknowledge'' THEN 17 END) AND ScreenObjectID=2'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAckAll'' THEN 17 END) AND ScreenObjectID=3'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertMap'' THEN 17 END) AND ScreenObjectID=8'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertPhoto'' THEN 17 END) AND ScreenObjectID=6'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertRespond'' THEN 17 END) AND ScreenObjectID=4'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertsControl'' THEN 17 END) AND ScreenObjectID=5'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aAlertVideo'' THEN 17 END) AND ScreenObjectID=7'
EXEC (@Query); SET @Query = ''
-----------------------
-----------------------Event Grid
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventMap'' THEN 24 END) AND ScreenObjectID=8'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventPhoto'' THEN 24 END) AND ScreenObjectID=6'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventRespond'' THEN 24 END) AND ScreenObjectID=3'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventControl'' THEN 24 END) AND ScreenObjectID=4'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventVideo'' THEN 24 END) AND ScreenObjectID=7'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aEventRecent'' THEN 24 END) AND ScreenObjectID=5'
EXEC (@Query); SET @Query = ''

-----Help
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,3,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aCAAbout'' THEN 111 END) AND ScreenObjectID=3'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,3,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''aCAHelp'' THEN 111 END) AND ScreenObjectID=1'
EXEC (@Query); SET @Query = ''

SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,3,4,5) THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID=(CASE
WHEN Priv_Det.MenuItemName=''DeleteEntry1'' THEN 6 END) AND ScreenObjectID=11'
EXEC (@Query); SET @Query = ''

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

-------------Parent 
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel IN (1,2,3,4,5) THEN 3 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID in (select ScreenId from Screens where ScreenName in(''Report Hardware'',''Report Holiday''
,''Report Inlist'',''Report Links'',''Report Operator'',''Report System Settings'',''Report Time And Attendance'',''Report Time Schedules'',''Report Access Groups'',''Report Activity Links''
,''Report Alert Response'',''Report Audit Trail'',''Report Badges'',''Report Events'',''Report Facility Codes'',''Report Personnel'',''Report Import Personnel'',''Report Inport Export Utility'',''Report Inport Photos'',''Report Modem''
,''Report System Health'',''Report Attendance'') AND  Priv_Det.MenuItemName=''aConfigurationReports'') AND ScreenObjectID=0'
EXEC (@Query); SET @Query = ''

-------------Child
SET @QUERY ='UPDATE [dbo].[RolePrivileges] 
SET SecurityLevel=(CASE WHEN Priv_Det.PrivLevel = 1 THEN 1 ELSE 0 END)
FROM ['+@sourceServer+'].['+@sourceDB+'].[dbo].Priv_Det 
		JOIN ['+@sourceServer+'].['+@sourceDB+'].[dbo].Privileg ON Priv_Det.PrivCode=Privileg.NoPriv
		JOIN Roles on Roles.RoleName=Privileg.Priv
WHERE RolePrivileges.RoleID= Roles.RoleID AND RolePrivileges.ScreenID in (select ScreenId from Screens where ScreenName in(''Report Hardware'',''Report Holiday''
,''Report Inlist'',''Report Links'',''Report Operator'',''Report System Settings'',''Report Time And Attendance'',''Report Time Schedules'',''Report Access Groups'',''Report Activity Links''
,''Report Alert Response'',''Report Audit Trail'',''Report Badges'',''Report Events'',''Report Facility Codes'',''Report Personnel'',''Report Import Personnel'',''Report Inport Export Utility'',''Report Inport Photos'',''Report Modem''
,''Report System Health'',''Report Attendance'') AND  Priv_Det.MenuItemName=''aConfigurationReports'') AND ScreenObjectID>0'
EXEC (@Query); SET @Query = ''
---============Operator Group============

----Panel
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Pnl.caObjectID
	FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	INNER JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Panel ON Panel.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Panel] Pnl ON Pnl.PnlNo=Panel.PnlNo
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
	where Groups.Groupid=6 and Groups.itemno>0')

----Reader
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Rdr4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Reader Rdr29 ON Rdr29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Reader] Rdr4K ON Rdr4K.RdrNo=Rdr29.RdrNo and Rdr4K.PnlRef=Rdr29.PnlRef
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=1 and Groups.itemno>0')
	
----Inputs
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, In4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Input In29 ON In29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Input] In4K ON In4K.InpNo=In29.InpNo and In4K.PnlRef=In29.PnlRef
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=3 and Groups.itemno>0')
 
----Relay
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Rl4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Relay Rl29 ON Rl29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Relay] Rl4K ON Rl4K.RelNo=Rl29.RelNo and Rl4K.PnlRef=Rl29.PnlRef
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=4 and Groups.itemno>0')

----Link
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Ln4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Link Ln29 ON Ln29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Link] Ln4K ON Ln4K.ProgNo=Ln29.ProgNo and Ln4K.PnlRef=Ln29.PnlRef
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=7 and Groups.itemno>0')

----ActivityLink
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, MALn4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.MActiveLinks MALn29 ON MALn29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[MActiveLinks] MALn4K ON MALn4K.ProgNo=MALn29.ProgNo and MALn4K.PnlRef=MALn29.PnlRef
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=19 and Groups.itemno>0')

----Access Group
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, MG4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.MAccGrp MG29 ON MG29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[MAccGrp] MG4K ON MG4K.AgrpNo=MG29.AgrpNo
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=9 and Groups.itemno>0')

----Time Schedule 
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Mz4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.MTimezon Mz29 ON Mz29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[MTimezon] Mz4K ON Mz4K.TzNum=Mz29.TzNum
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=8 and Groups.itemno>0')

----APB Areas  
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Ar4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.APBAreas Ar29 ON Ar29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[APBAreas] Ar4K ON Ar4K.AreaNo=Ar29.AreaNo
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=22 and Groups.itemno>0')

----LockdownAreas 
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, MLA4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.MLockoutAreas MLA29 ON MLA29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[MLockdownAreas] MLA4K ON MLA4K.AreaNo=MLA29.AreaNo
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=23 and Groups.itemno>0')

----DVR Camera
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, DC4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.DvrCamera DC29 ON DC29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[DvrCamera] DC4K ON DC4K.DvrCameraID=DC29.DvrCameraID and DC4K.DvrServerID=DC29.DvrServerID
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=12 and Groups.itemno>0')

----NAPCOPanel
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, NP4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.napcoPanels NP29 ON NP29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[napcoPanels] NP4K ON NP4K.AlarmPanelID=NP29.AlarmPanelID and NP4K.workstationID=NP29.workstationID
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=13 and Groups.itemno>0')

----NAPCOArea
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, NPA4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.napcoPanelAreas NPA29 ON NPA29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[napcoPanelAreas] NPA4K ON NPA4K.AlarmPanelID=NPA29.AlarmPanelID and NPA4K.AreaNumber=NPA29.AreaNumber
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=14 and Groups.itemno>0')

----NAPCOZone
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, NPZ4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.napcoPanelZones NPZ29 ON NPZ29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[napcoPanelZones] NPZ4K ON NPZ4K.AlarmPanelID=NPZ29.AlarmPanelID and NPZ4K.AreaNumber=NPZ29.AreaNumber and NPZ4K.ZoneNumber=NPZ29.ZoneNumber
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=15 and Groups.itemno>0')

----NAPCORelay
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, NPR4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.napcoPanelRelays NPR29 ON NPR29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[napcoPanelRelays] NPR4K ON NPR4K.AlarmPanelID=NPR29.AlarmPanelID and NPR4K.RelayNumber=NPR29.RelayNumber
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=16 and Groups.itemno>0')

----Personnel
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Bg4K.PersonID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.Badge Bg29 ON Bg29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Badge] Bg4K ON Bg4K.Badge=Bg29.Badge and Bg4K.Facility=Bg29.Facility
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=0 and Groups.itemno>0')

----Napco Permission
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, NPM4K.caObjectID
    FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.napcoMPermissions NPM29 ON NPM29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[napcoMPermissions] NPM4K ON NPM4K.PermissionID=NPM29.PermissionID
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
    where Groups.Groupid=17 and Groups.itemno>0')

----MAP
EXEC('INSERT INTO [dbo].[DevicePartitions]([PartitionID],[caObjectID])
	SELECT Pt.PartitionID, Mp4K.caObjectID
	FROM ['+@sourceServer+'].['+@sourceDB+'].dbo.Groups 
	inner JOIN ['+@sourceServer+'].['+@sourceDB+'].dbo.dynMap dyM29 ON dyM29.GroupNo = Groups.Itemno
	INNER JOIN [dbo].[Map] Mp4K ON Mp4K.MapID=dyM29.MapId
	INNER JOIN [dbo].[Partition] Pt ON Pt.PartitionName=Groups.Name
	where Groups.Groupid=20 and Groups.itemno>0')

 --Audit
   SET @Query = ''
   
   SET @Query = '
   DELETE FROM [dbo].[DBAudit]
   INSERT INTO [dbo].[DBAudit]([RevisionStamp],[TableName],[OperatorName],[StationName]
      ,[Actions],[Description],[LastOperator],[UTCOffset])
	  SELECT[RevisionStamp],[TableName],[OperatorName],[StationName]
      ,case when [Actions]=''A'' then ''I'' when [Actions]=''E'' then ''U'' else [Actions] end
      ,[Description] ,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS LastOperator, [UTCOffset]
  FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].[DBAudit]'
  EXEC (@Query); 
  SET @Query = ''
 
 --Event
 SET @Query = '';
 SET @Query = 'DELETE FROM ['+@LiveEventDatabase+'].[dbo].[Event]
 INSERT INTO ['+@LiveEventDatabase+'].[dbo].Event(Seqno,Priority,Cat,PnlNo,EDate,DeviceNo,Status,Facno,Badge,Class,Description,Name,Arch,AckOpr,AckTStamp,Actions,RespReq,caObjectID,Tag,HasPhoto,HasVideo,Pending,UTCOffset,Sphere)'

SET @Query = @Query+ 'SELECT Seqno,CAST(Priority AS INT) AS Priority,CAST((SELECT TOP  1 DeviceCode FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''ACTIVITY Link UnDo'' THEN ''LINK Event Link Un-do'' 
															  WHEN Class=''ACTIVITY Link'' THEN ''LINK Event Link Activate'' 
														      WHEN Class=''LINK ACTIVATE'' THEN ''LINK I/O Activate'' 
															  WHEN Class=''LINK DEACTIVATE'' THEN ''LINK I/O Deactivate'' 
															  WHEN Class=''LOCK Manual Unlock'' THEN ''DOOR Manual Unlock''
															  WHEN Class=''LOCK Manual lock'' THEN ''DOOR Manual lock''
															  WHEN Class=''LOCK Manual Enable'' THEN ''DOOR Manual Enable''
															  WHEN Class=''LOCK Manual Disable'' THEN ''DOOR Manual Disable''
															  WHEN Class=''LOCK Manual Lock/Disable'' THEN ''DOOR Manual Lock/Disable''
														      ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Cat,'
	SET @Query = @Query+ 'CAST(PnlNo AS INT) AS PnlNo,EDate,Number AS DeviceNo
	,CAST((SELECT TOP  1 Status FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''ACTIVITY Link UnDo'' THEN ''LINK Event Link Un-do'' 
															  WHEN Class=''ACTIVITY Link'' THEN ''LINK Event Link Activate'' 
														      WHEN Class=''LINK ACTIVATE'' THEN ''LINK I/O Activate'' 
															  WHEN Class=''LINK DEACTIVATE'' THEN ''LINK I/O Deactivate'' 
															  WHEN Class=''LOCK Manual Unlock'' THEN ''DOOR Manual Unlock''
															  WHEN Class=''LOCK Manual lock'' THEN ''DOOR Manual lock''
															  WHEN Class=''LOCK Manual Enable'' THEN ''DOOR Manual Enable''
															  WHEN Class=''LOCK Manual Disable'' THEN ''DOOR Manual Disable''
															  WHEN Class=''LOCK Manual Lock/Disable'' THEN ''DOOR Manual Lock/Disable''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Status,'
	SET @Query = @Query+ 'CAST(Facno AS INT) AS Facno,Badge,
	(SELECT TOP  1 EventClassName FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''ACTIVITY Link UnDo'' THEN ''LINK Event Link Un-do'' 
															  WHEN Class=''ACTIVITY Link'' THEN ''LINK Event Link Activate'' 
														      WHEN Class=''LINK ACTIVATE'' THEN ''LINK I/O Activate'' 
															  WHEN Class=''LINK DEACTIVATE'' THEN ''LINK I/O Deactivate'' 
															  WHEN Class=''LOCK Manual Unlock'' THEN ''DOOR Manual Unlock''
															  WHEN Class=''LOCK Manual lock'' THEN ''DOOR Manual lock''
															  WHEN Class=''LOCK Manual Enable'' THEN ''DOOR Manual Enable''
															  WHEN Class=''LOCK Manual Disable'' THEN ''DOOR Manual Disable''
															  WHEN Class=''LOCK Manual Lock/Disable'' THEN ''DOOR Manual Lock/Disable''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS Class,'
   SET @Query = @Query+ 'Description,Name,CAST(Arch AS INT) AS Arch,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS AckOpr,AckTStamp,Actions,RespReq, newid() AS caObjectID
   ,Tag,HasPhoto, 0 AS HasVideo,0 AS Pending,UTCOffset,Sphere
   FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Event'
  EXEC (@Query); SET @Query = ''

  SET @Query = '';
 SET @Query = 'DELETE FROM ['+@LiveEventDatabase+'].[dbo].[Event]
 INSERT INTO ['+@LiveEventDatabase+'].[dbo].Event(Seqno,Priority,Cat,PnlNo,EDate,DeviceNo,Status,Facno,Badge,Class,Description,Name,Arch,AckOpr,AckTStamp,Actions,RespReq,caObjectID,Tag,HasPhoto,HasVideo,Pending,UTCOffset,Sphere)'

SET @Query = @Query+ 'SELECT Seqno,CAST(Priority AS INT) AS Priority,CAST((SELECT TOP  1 DeviceCode FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''LOCK Manual Lock/Enable'' THEN ''DOOR Manual Lock/Enable''
															  WHEN Class=''LOCK Manual Unlock/Disable'' THEN ''DOOR Manual Unlock/Disable''
															  WHEN Class=''LOCK Manual Unlock/Enable'' THEN ''DOOR Manual Unlock/Enable''
															  WHEN Class=''LOCK FREE ACCESS START'' THEN ''DOOR Free Access Start''
															  WHEN Class=''LOCK FREE ACCESS END'' THEN ''DOOR Free Access End''
															  WHEN Class=''LOCK BYPASS'' THEN ''DOOR Bypass''
														      WHEN Class=''Alarm Panel Event'' THEN ''Relay On'' 
														      WHEN Class=''BADGE VIOLATE CARD Expired'' THEN ''BADGE Expired by Date'' 
															  WHEN Class=''BADGE CLOCK-IN'' THEN ''BADGE Clock In''
														      ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Cat,'
	SET @Query = @Query+ 'CAST(PnlNo AS INT) AS PnlNo,EDate,Number AS DeviceNo
	,CAST((SELECT TOP  1 Status FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''LOCK Manual Lock/Enable'' THEN ''DOOR Manual Lock/Enable''
															  WHEN Class=''LOCK Manual Unlock/Disable'' THEN ''DOOR Manual Unlock/Disable''
															  WHEN Class=''LOCK Manual Unlock/Enable'' THEN ''DOOR Manual Unlock/Enable''
															  WHEN Class=''LOCK FREE ACCESS START'' THEN ''DOOR Free Access Start''
															  WHEN Class=''LOCK FREE ACCESS END'' THEN ''DOOR Free Access End''
															  WHEN Class=''LOCK BYPASS'' THEN ''DOOR Bypass''
														      WHEN Class=''Alarm Panel Event'' THEN ''Relay On'' 
														      WHEN Class=''BADGE VIOLATE CARD Expired'' THEN ''BADGE Expired by Date'' 
															  WHEN Class=''BADGE CLOCK-IN'' THEN ''BADGE Clock In''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Status,'
	SET @Query = @Query+ 'CAST(Facno AS INT) AS Facno,Badge,
	(SELECT TOP  1 EventClassName FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''LOCK Manual Lock/Enable'' THEN ''DOOR Manual Lock/Enable''
															  WHEN Class=''LOCK Manual Unlock/Disable'' THEN ''DOOR Manual Unlock/Disable''
															  WHEN Class=''LOCK Manual Unlock/Enable'' THEN ''DOOR Manual Unlock/Enable''
															  WHEN Class=''LOCK FREE ACCESS START'' THEN ''DOOR Free Access Start''
															  WHEN Class=''LOCK FREE ACCESS END'' THEN ''DOOR Free Access End''
															  WHEN Class=''LOCK BYPASS'' THEN ''DOOR Bypass''
														      WHEN Class=''Alarm Panel Event'' THEN ''Relay On'' 
														      WHEN Class=''BADGE VIOLATE CARD Expired'' THEN ''BADGE Expired by Date'' 
															  WHEN Class=''BADGE CLOCK-IN'' THEN ''BADGE Clock In'' 
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS Class,'
   SET @Query = @Query+ 'Description,Name,CAST(Arch AS INT) AS Arch,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS AckOpr,AckTStamp,Actions,RespReq, newid() AS caObjectID
   ,Tag,HasPhoto, 0 AS HasVideo,0 AS Pending,UTCOffset,Sphere
   FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Event'
  EXEC (@Query); SET @Query = ''

  SET @Query = '';
 SET @Query = 'DELETE FROM ['+@LiveEventDatabase+'].[dbo].[Event]
 INSERT INTO ['+@LiveEventDatabase+'].[dbo].Event(Seqno,Priority,Cat,PnlNo,EDate,DeviceNo,Status,Facno,Badge,Class,Description,Name,Arch,AckOpr,AckTStamp,Actions,RespReq,caObjectID,Tag,HasPhoto,HasVideo,Pending,UTCOffset,Sphere)'

SET @Query = @Query+ 'SELECT Seqno,CAST(Priority AS INT) AS Priority,CAST((SELECT TOP  1 DeviceCode FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''BADGE CLOCK-OUT'' THEN ''BADGE Clock Out''
															  WHEN Class=''TROUBLE Line OPEN'' THEN ''INPUT TROUBLE Line Open''
															  WHEN Class=''TROUBLE Line SHORT'' THEN ''INPUT TROUBLE Line Short'' 
															  WHEN Class=''VIDEO'' THEN ''SYSTEM'' 
															  WHEN Class=''BADGE NAPCO ARM Request'' THEN ''BADGE NAPCO Panel Arm Request'' 
															  WHEN Class=''BADGE VIOLATE LOCKOUT'' THEN ''BADGE Lockout'' 
															  WHEN Class=''TAMPER ABNORMAL'' THEN ''INPUT Tamper Abnormal''
															  WHEN Class=''TAMPER NORMAL'' THEN ''INPUT Tamper Normal''
														      ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Cat,'
	SET @Query = @Query+ 'CAST(PnlNo AS INT) AS PnlNo,EDate,Number AS DeviceNo
	,CAST((SELECT TOP  1 Status FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''BADGE CLOCK-OUT'' THEN ''BADGE Clock Out''
															  WHEN Class=''TROUBLE Line OPEN'' THEN ''INPUT TROUBLE Line Open''
															  WHEN Class=''TROUBLE Line SHORT'' THEN ''INPUT TROUBLE Line Short'' 
															  WHEN Class=''VIDEO'' THEN ''SYSTEM'' 
															  WHEN Class=''BADGE NAPCO ARM Request'' THEN ''BADGE NAPCO Panel Arm Request'' 
															  WHEN Class=''BADGE VIOLATE LOCKOUT'' THEN ''BADGE Lockout'' 
															  WHEN Class=''TAMPER ABNORMAL'' THEN ''INPUT Tamper Abnormal''
															  WHEN Class=''TAMPER NORMAL'' THEN ''INPUT Tamper Normal''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Status,'
	SET @Query = @Query+ 'CAST(Facno AS INT) AS Facno,Badge,
	(SELECT TOP  1 EventClassName FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''BADGE CLOCK-OUT'' THEN ''BADGE Clock Out''
															  WHEN Class=''TROUBLE Line OPEN'' THEN ''INPUT TROUBLE Line Open''
															  WHEN Class=''TROUBLE Line SHORT'' THEN ''INPUT TROUBLE Line Short'' 
															  WHEN Class=''VIDEO'' THEN ''SYSTEM'' 
															  WHEN Class=''BADGE NAPCO ARM Request'' THEN ''BADGE NAPCO Panel Arm Request'' 
															  WHEN Class=''BADGE VIOLATE LOCKOUT'' THEN ''BADGE Lockout'' 
															  WHEN Class=''TAMPER ABNORMAL'' THEN ''INPUT Tamper Abnormal''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS Class,'
   SET @Query = @Query+ 'Description,Name,CAST(Arch AS INT) AS Arch,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS AckOpr,AckTStamp,Actions,RespReq, newid() AS caObjectID
   ,Tag,HasPhoto, 0 AS HasVideo,0 AS Pending,UTCOffset,Sphere
   FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Event'
  EXEC (@Query); SET @Query = ''

  SET @Query = '';
 SET @Query = 'DELETE FROM ['+@LiveEventDatabase+'].[dbo].[Event]
 INSERT INTO ['+@LiveEventDatabase+'].[dbo].Event(Seqno,Priority,Cat,PnlNo,EDate,DeviceNo,Status,Facno,Badge,Class,Description,Name,Arch,AckOpr,AckTStamp,Actions,RespReq,caObjectID,Tag,HasPhoto,HasVideo,Pending,UTCOffset,Sphere)'

SET @Query = @Query+ 'SELECT Seqno,CAST(Priority AS INT) AS Priority,CAST((SELECT TOP  1 DeviceCode FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''TAMPER NORMAL'' THEN ''INPUT Tamper Normal'' 
															  WHEN Class=''LOCK FREE ACCESS START'' OR Class =''LOCK FREE ACCESS END'' THEN ''LOCK Data'' 
															  WHEN Class=''Alarm Shunt On'' THEN ''Alarm Shunt ON'' 
															  WHEN Class=''Alarm Shunt Off'' THEN ''Alarm Shunt OFF'' 
														      ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Cat,'
	SET @Query = @Query+ 'CAST(PnlNo AS INT) AS PnlNo,EDate,Number AS DeviceNo
	,CAST((SELECT TOP  1 Status FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''TAMPER NORMAL'' THEN ''INPUT Tamper Normal''  
															  WHEN Class=''LOCK FREE ACCESS START'' OR Class =''LOCK FREE ACCESS END'' THEN ''LOCK Data'' 
															  WHEN Class=''Alarm Shunt On'' THEN ''Alarm Shunt ON'' 
															  WHEN Class=''Alarm Shunt Off'' THEN ''Alarm Shunt OFF''
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS INT) AS Status,'
	SET @Query = @Query+ 'CAST(Facno AS INT) AS Facno,Badge,
	(SELECT TOP  1 EventClassName FROM ['+@LiveEventDatabase+'].[dbo].EventClassDefs 
		WHERE EventClassName COLLATE DATABASE_DEFAULT = (CASE WHEN Class=''TAMPER NORMAL'' THEN ''INPUT Tamper Normal''
															  WHEN Class=''LOCK FREE ACCESS START'' OR Class =''LOCK FREE ACCESS END'' THEN ''LOCK Data'' 
															  WHEN Class=''Alarm Shunt On'' THEN ''Alarm Shunt ON'' 
															  WHEN Class=''Alarm Shunt Off'' THEN ''Alarm Shunt OFF''  
															  ELSE Class END COLLATE DATABASE_DEFAULT))AS Class,'
   SET @Query = @Query+ 'Description,Name,CAST(Arch AS INT) AS Arch,''1EEB696C-0E28-4702-8598-C379C95B45E1'' AS AckOpr,AckTStamp,Actions,RespReq, newid() AS caObjectID
   ,Tag,HasPhoto, 0 AS HasVideo,0 AS Pending,UTCOffset,Sphere
   FROM ['+@sourceServer+'].['+@sourceDb+'].[dbo].Event'
  EXEC (@Query); SET @Query = ''
  
  SET @Query = '';
  SET @Query = 'UPDATE ['+@LiveEventDatabase+'].[dbo].[Event] SET caObjectID=(CASE WHEN [Cat] IN (0,5) THEN (SELECT TOP 1 caObjectID FROM Reader WHERE Reader.PnlRef = [PnlNo] AND Reader.RdrNo = [DeviceNo])
			WHEN [Cat] = 1 THEN (SELECT TOP 1 caObjectID FROM Input WHERE Input.PnlRef = [PnlNo] AND Input.InpNo = [DeviceNo])
			WHEN [Cat] = 2 THEN (SELECT TOP 1 caObjectID FROM Relay WHERE Relay.PnlRef = [PnlNo] AND Relay.RelNo = [DeviceNo])
			WHEN [Cat] = 3 THEN (SELECT TOP 1 caObjectID FROM Link WHERE Link.PnlRef = [PnlNo] AND Link.ProgNo = [DeviceNo])
			WHEN [Cat] IN (4,7) THEN (SELECT TOP 1 caObjectID FROM Panel WHERE Panel.PnlNo=[PnlNo]) 
			WHEN [Cat] = 10 THEN (SELECT TOP 1 caObjectID FROM MActiveLinks WHERE MActiveLinks.PnlRef = [PnlNo] AND MActiveLinks.ProgNo = [DeviceNo])
			ELSE ''00000000-0000-0000-0000-000000000000'' END)'
  EXEC (@Query); SET @Query = ''
 
  DECLARE [DuplicateUserField] CURSOR LOCAL
	FOR
    SELECT Uf.UserFieldID, Uf.UserFieldName FROM UserFieldDef AS Uf
    INNER JOIN (SELECT Ufd.UserFieldName, COUNT(*) AS dupCount
                FROM UserFieldDef AS Ufd
                GROUP BY UserFieldName
                HAVING COUNT(*)>1
            ) Ufd ON Uf.UserFieldName=Ufd.UserFieldName
	ORDER BY UserFieldName, UserFieldID
	OPEN [DuplicateUserField]
	BEGIN TRY
		DECLARE @UserFieldID INT, @UserFieldName NVARCHAR(50), @Inc INT
		DECLARE  @msg nvarchar(max)
		DECLARE @str nvarchar(60), @lastUp nvarchar(50)
		FETCH NEXT FROM [DuplicateUserField] INTO @UserFieldID, @UserFieldName;
		SET @Inc=0
		SET @lastUp=''
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if(@lastUp<>@UserFieldName)
				SET @Inc=0

			IF @Inc = 0  
				SET @str=@UserFieldName
			else
				SET @str=@UserFieldName+ CONVERT(VARCHAR(10),@Inc)

			SET @lastUp=@UserFieldName
			UPDATE UserFieldDef SET UserFieldName=@str WHERE UserFieldID=@UserFieldID
			FETCH NEXT FROM [DuplicateUserField] INTO @UserFieldID, @UserFieldName;
			SET @Inc=@Inc+1
		END
	END TRY
BEGIN CATCH
    set @msg=(SELECT ERROR_MESSAGE())
END CATCH
CLOSE [DuplicateUserField]
DEALLOCATE [DuplicateUserField]


-- Merge same name partitions
SELECT NEWID() AS [PartitionID], [PartitionName], 0 [DeviceType], MAX([LastUpdated]) AS [LastUpdated] INTO [#DISTINCT_PARTITIONS]
FROM [dbo].[Partition]
WHERE [DeviceType] > 0
GROUP BY [PartitionName]
ORDER BY PartitionName,[DeviceType]

---------------CURSOR Start--------------------
DECLARE CUR_DISTINCT_PARTITIONS CURSOR LOCAL
FOR  SELECT [PartitionID], [PartitionName] FROM [#DISTINCT_PARTITIONS] ORDER BY PartitionName

OPEN CUR_DISTINCT_PARTITIONS
BEGIN
    declare @V_PartitionID  AS UNIQUEIDENTIFIER
    declare @V_PartitionName  AS VARCHAR(100)
    FETCH NEXT FROM CUR_DISTINCT_PARTITIONS INTO @V_PartitionID, @V_PartitionName
    WHILE @@FETCH_STATUS = 0
    BEGIN
		--------------------------INSERT INTO [OperatorPartitions] TABLE------------------------------
        INSERT INTO [OperatorPartitions](PartitionID,OperatorID,IsPrimaryPartition,Restriction,LastUpdated)
		SELECT DISTINCT @V_PartitionID AS PartitionID, OP.OperatorID, 1 AS IsPrimaryPartition, CONVERT(SMALLINT,0) AS Restriction,GETDATE() FROM [Partition] AS P INNER JOIN OperatorPartitions AS OP ON P.PartitionID = OP.PartitionID 
		WHERE PartitionName = @V_PartitionName

		--------------------------INSERT INTO [RolePartitions] TABLE------------------------------
		INSERT INTO [RolePartitions](PartitionID,RoleID,Restriction,LastUpdated)
		SELECT DISTINCT @V_PartitionID AS PartitionID, RoleID, CONVERT(SMALLINT,0) AS Restriction, GETDATE() AS LastUpdated FROM [Partition] AS P INNER JOIN [RolePartitions] AS RP ON P.PartitionID = RP.PartitionID 
		WHERE PartitionName = @V_PartitionName

		--------------------------INSERT INTO [DevicePartitions] TABLE------------------------------
		INSERT INTO [DevicePartitions]
		SELECT DISTINCT @V_PartitionID AS PartitionID, caObjectID FROM [Partition] AS P INNER JOIN [DevicePartitions] AS DP ON P.PartitionID = DP.PartitionID 
		WHERE PartitionName = @V_PartitionName

        FETCH NEXT FROM CUR_DISTINCT_PARTITIONS INTO @V_PartitionID, @V_PartitionName
    END 
END
CLOSE CUR_DISTINCT_PARTITIONS
DEALLOCATE CUR_DISTINCT_PARTITIONS
---------------END CURSOR Start--------------------

-------------Inserting partition which not belongs to Admin Operators-----------
INSERT INTO [DevicePartitions]
SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, P.PartitionID
FROM [Partition] AS P
WHERE NOT EXISTS(SELECT 1 FROM [DevicePartitions] AS DP WHERE P.PartitionID = DP.caObjectID AND DP.PartitionID = '3DC78C7B-C9CE-4485-A835-63D577F623E6')
ORDER BY PartitionName

-------------Delete partitions which are device type not All Devices-----------
DELETE  FROM [dbo].[Partition] WHERE [DeviceType] > 0

-------------Delete partitions which are device type not All Devices-----------
INSERT INTO [dbo].[Partition]
SELECT * FROM [#DISTINCT_PARTITIONS]

DROP TABLE [#DISTINCT_PARTITIONS]


--Disable Badges - wipe out activation date since this will be activation again by schduler
update badge set ActvDate = null where Enabled = 0 and ActvDate <= GETUTCDATE() 

update dbo.operators set EventCount = case when EventCount > 300 then 300 else case when EventCount < 1 then 1 else EventCount end end
update dbo.operators set LogoffTime = case when LogoffTime > 9999 then 9999 else case when LogoffTime < 1 then 1 else LogoffTime end end
update dbo.operators set AutoAckTime = case when AutoAckTime > 9999 then 9999 else case when AutoAckTime < 1 then 1 else AutoAckTime end end

--------------Panel Time Schedule for each Panel------------------------
DECLARE [PanelTimeSchedule] CURSOR LOCAL
	FOR
    SELECT PnlNo FROM Panel
	OPEN [PanelTimeSchedule]
	BEGIN TRY
		DECLARE @PanelNo INT
		SET @msg=''
		FETCH NEXT FROM [PanelTimeSchedule] INTO @PanelNo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@PanelNo>0)
			BEGIN
				INSERT INTO PanelTimeSchdule(LocalTzNo,PanelTzNo,PanelNo,caObjectId,LastUpdated)
				Select Schedule AS LocalTzNo, Schedule AS PanelTzNo, PnlRef AS PanelNo, NEWID() as caObjectId, GETUTCDate() as LastUpdated
				FROM
				(
				SELECT PnlRef, Ctz AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND Ctz > 0
				UNION
				SELECT PnlRef, Cctz AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND Cctz > 0
				UNION
				SELECT PnlRef, Cytz AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND Cytz > 0
				UNION
				SELECT PnlRef, Fatz AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND Fatz > 0
				UNION
				SELECT PnlRef, DegrSchedule AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND DegrSchedule > 0
				UNION
				SELECT PnlRef, OTLSchedule AS Schedule FROM Reader WHERE PnlRef = @PanelNo AND OTLSchedule > 0
				UNION
				SELECT PnlRef, TrackTz AS Schedule FROM Relay WHERE PnlRef = @PanelNo AND TrackTz > 0
				UNION
				SELECT PnlRef, InpTz AS Schedule FROM Input WHERE PnlRef = @PanelNo AND InpTz > 0
				UNION
				SELECT PnlRef, LimitSchedule AS Schedule FROM Input WHERE PnlRef = @PanelNo AND LimitSchedule > 0
				UNION
				SELECT PnlRef, ETz AS Schedule FROM Link WHERE PnlRef = @PanelNo AND ETz > 0
				UNION
				SELECT PnlRef, TrkTZ AS Schedule FROM Link WHERE PnlRef = @PanelNo AND TrkTZ > 0
				UNION
				SELECT PnlRef, Schedule FROM MActiveLinks WHERE PnlRef = @PanelNo AND Schedule > 0
				UNION
				SELECT PnlRef, Tz1 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz1 > 0
				UNION
				SELECT PnlRef, Tz2 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz2 > 0
				UNION
				SELECT PnlRef, Tz3 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz3 > 0
				UNION
				SELECT PnlRef, Tz4 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz4 > 0
				UNION
				SELECT PnlRef, Tz5 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz5 > 0
				UNION
				SELECT PnlRef, Tz6 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz6 > 0
				UNION
				SELECT PnlRef, Tz7 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz7 > 0
				UNION
				SELECT PnlRef, Tz8 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz8 > 0
				UNION
				SELECT PnlRef, Tz9 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz9 > 0
				UNION
				SELECT PnlRef, Tz10 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz10 > 0
				UNION
				SELECT PnlRef, Tz11 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz11 > 0
				UNION
				SELECT PnlRef, Tz12 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz12 > 0
				UNION
				SELECT PnlRef, Tz13 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz13 > 0
				UNION
				SELECT PnlRef, Tz14 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz14 > 0
				UNION
				SELECT PnlRef, Tz15 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz15 > 0
				UNION
				SELECT PnlRef, Tz16 AS Schedule FROM AccGrp WHERE PnlRef = @PanelNo AND Tz16 > 0	
				UNION
				SELECT PnlNo AS PnlRef, Schedule FROM SchedEvent WHERE PnlNo = @PanelNo AND Schedule > 0
				) AS TAB

				FETCH NEXT FROM [PanelTimeSchedule] INTO @PanelNo
			END
		END
	END TRY
BEGIN CATCH
    set @msg=(SELECT ERROR_MESSAGE())
END CATCH
CLOSE [PanelTimeSchedule]
DEALLOCATE [PanelTimeSchedule]

--------------Holiday for each Panel------------------------
DECLARE [PanelHoliday] CURSOR LOCAL
	FOR
    SELECT PnlNo FROM Panel
	OPEN [PanelHoliday]
	BEGIN TRY
		SET @msg=''
		DECLARE @pPanelNo INT
		FETCH NEXT FROM [PanelHoliday] INTO @pPanelNo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@pPanelNo>0)
			BEGIN
				INSERT INTO PanelHoliday(LocalCalendarNo, PanelCalendarNo, PanelNo, caObjectId, LastUpdated)
				SELECT Holiday AS LocalCalendarNo, Holiday AS PanelCalendarNo, PnlNo AS PanelNo,NEWID() as caObjectId, GETUTCDate() as LastUpdated
				FROM (
					SELECT PnlNo, DefaultCalendar AS Holiday FROM Panel WHERE PnlNo = @pPanelNo AND DefaultCalendar > 0
					UNION ALL
					SELECT PnlNo, Holiday FROM (
					SELECT DISTINCT AG.PnlRef AS PnlNo, B.HolidayCalendar AS Holiday, B.Badge
					FROM AccGrp AS AG INNER JOIN BadgeAccess AS BA ON BA.AGroupNo = AG.Agno INNER JOIN Badge AS B ON B.Badge = BA.Badge AND B.Facility = BA.Facility 
					WHERE B.HolidayCalendar > 0 AND AG.PnlRef = @pPanelNo
					) AS BADGE_TAB
				) AS TAB 
				GROUP BY PnlNo, Holiday
				ORDER BY Holiday

				FETCH NEXT FROM [PanelHoliday] INTO @pPanelNo
			END
		END
	END TRY
BEGIN CATCH
    set @msg=(SELECT ERROR_MESSAGE())
END CATCH
CLOSE [PanelHoliday]
DEALLOCATE [PanelHoliday]

END
