SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Siva 
-- Create date: 03/31/2017
-- Description:	Data Migration from CA3000 to CA4K
-- =============================================
ALTER PROCEDURE [dbo].[ca_sp_DataMigrationEvent] 
	@SourceServer nvarchar(150),
	@sourceDatabase nvarchar(150),
	@LiveConfigDatabase nvarchar(150),
	@AppendEvents bit
	
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;


 --Audit
   SET @Query = ''
   if @AppendEvents = 1
   begin
	SET @Query = 'DELETE FROM [dbo].[DBAudit]'
	EXEC (@Query); 
	SET @Query = ''
   end;

   SET @Query = '
   INSERT INTO [dbo].[DBAudit]([AuditId],[RevisionStamp],[TableName],[OperatorName],[StationName]
      ,[Actions],[Description],[LastOperator],[UTCOffset])
	  SELECT [AuditId],[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 = '';
    if @AppendEvents = 1
   begin
	SET @Query = 'DELETE FROM [dbo].[Event]'
	EXEC (@Query); 
	SET @Query = ''
   end;

 SET @Query = '
 INSERT INTO [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 [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''
															  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'' 
															  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'' 
															  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 [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''
															  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'' 
															  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''  
															  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 [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''
															  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'' 
															  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''
															  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 [dbo].[Event] SET caObjectID=(CASE WHEN [Cat] IN (0,5) THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[Reader] WHERE Reader.PnlRef = [PnlNo] AND Reader.RdrNo = [DeviceNo])
			WHEN [Cat] = 1 THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[Input] WHERE Input.PnlRef = [PnlNo] AND Input.InpNo = [DeviceNo])
			WHEN [Cat] = 2 THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[Relay] WHERE Relay.PnlRef = [PnlNo] AND Relay.RelNo = [DeviceNo])
			WHEN [Cat] = 3 THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[Link] WHERE Link.PnlRef = [PnlNo] AND Link.ProgNo = [DeviceNo])
			WHEN [Cat] IN (4,7) THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[Panel] WHERE Panel.PnlNo=[PnlNo]) 
			WHEN [Cat] = 10 THEN (SELECT TOP 1 caObjectID FROM ['+@LiveConfigDatabase+'].[dbo].[MActiveLinks] WHERE MActiveLinks.PnlRef = [PnlNo] AND MActiveLinks.ProgNo = [DeviceNo])
			ELSE ''00000000-0000-0000-0000-000000000000'' END)'
  EXEC (@Query); SET @Query = ''
  
END



GO


