-- 1.1.16.137 SP1 update scripts

--PR 111 - customer issue
	--- Clean up created roles for those operators which used to have CA4K Administration roles and changed while update to 1.1.16 -------
	UPDATE Operators
	SET RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139'
	FROM Operators AS O INNER JOIN Roles AS R ON O.RoleID = R.RoleID
	WHERE R.RoleName LIKE 'New Role (%'

	DELETE RolePartitions
	FROM RolePartitions AS RP INNER JOIN Roles AS R ON RP.RoleID = R.RoleID
	WHERE R.RoleName LIKE 'New Role (%'


	DELETE RolePlugins 
	FROM RolePlugins AS RP INNER JOIN Roles AS R ON RP.RoleID = R.RoleID
	WHERE RoleName LIKE 'New Role (%'

	DELETE Roles
	WHERE RoleName LIKE 'New Role (%'
	------------------------------------------------------------------------------------------------------------------------------------------

	-------- SET CA4K Administration role to admin operator if not exists -----
	IF NOT EXISTS (SELECT TOP 1 1 FROM Operators WHERE OperatorID = '1EEB696C-0E28-4702-8598-C379C95B45E1' AND RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139')
	BEGIN
	UPDATE Operators SET RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139' WHERE OperatorID = '1EEB696C-0E28-4702-8598-C379C95B45E1'
	END

	-------- Delete all partitions for CA4K Administration Role except for Admin Partition -------------
	DELETE FROM RolePartitions
	WHERE RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139'
	AND PartitionID <> '3DC78C7B-C9CE-4485-A835-63D577F623E6'

	-------- Insert missing CA4K Administration Role to RolePartitions table
	IF NOT EXISTS (SELECT RoleID FROM RolePartitions WHERE RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139' AND PartitionID = '3DC78C7B-C9CE-4485-A835-63D577F623E6')
	BEGIN
	INSERT INTO RolePartitions (PartitionID, RoleID, Restriction, LastUpdated) VALUES ('3DC78C7B-C9CE-4485-A835-63D577F623E6', '82B19834-4EF0-405B-9AD1-23860AAF4139', 0, GETUTCDATE())
	END

	-------- Clean OperatorPartitions Table ----------------
	DELETE FROM OperatorPartitions
	WHERE OperatorID IN
	(
	SELECT O.OperatorID --, OP.PartitionID
	FROM OperatorPartitions OP
	INNER JOIN Operators O ON O.OperatorID = OP.OperatorID
	WHERE
	O.RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139'
	AND O.OperatorID <> '1EEB696C-0E28-4702-8598-C379C95B45E1'
	)
	AND PartitionID <> '3DC78C7B-C9CE-4485-A835-63D577F623E6'

	------- Check If Admin Partition is available for the CA4K Administration Role's Operators If not insert data into OperatorPartitions -------------
	INSERT INTO OperatorPartitions(PartitionID, OperatorID, IsPrimaryPartition)
	SELECT DISTINCT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, O.OperatorID, 1 AS IsPrimaryPartition FROM Operators O
	WHERE O.RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139'
	AND NOT EXISTS (SELECT TOP 1 1 FROM OperatorPartitions WHERE OperatorPartitions.OperatorID = O.OperatorID AND OperatorPartitions.PartitionID = '3DC78C7B-C9CE-4485-A835-63D577F623E6')

	------- Set Admin Partition to Primary Partition ----------------
	UPDATE OperatorPartitions
	SET IsPrimaryPartition = 1
	WHERE OperatorID IN
	(
		SELECT O.OperatorID FROM OperatorPartitions OP
		INNER JOIN Operators O ON O.OperatorID = OP.OperatorID
		WHERE
		O.RoleID = '82B19834-4EF0-405B-9AD1-23860AAF4139'
		AND OP.PartitionID = '3DC78C7B-C9CE-4485-A835-63D577F623E6'
	)


--PR 112 (5) - customer issue
IF NOT EXISTS (SELECT 1 FROM [ScreenObjects] WHERE [ScreenObjectID] = 145 AND [ScreenID] = 15)
BEGIN
	INSERT INTO [ScreenObjects] ( [ScreenObjectID], [ScreenID], [ObjectName], [ObjectLabel], [ObjectType], [LangObjectName]) VALUES ( 145, 15, 'Partition Filter', 'cboPartitionFind', 'Infragistics.Win.UltraWinEditors.UltraComboEditor','String_Partition_Filter') 
END


-- 1.1.16.137 SP2 update scripts

--PR # 126 (Personnel Report)
-------Append existing userfieldname field value if this field values are contains with the tables/view column name
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'Person' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'Badge' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'BadgePhoto' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'APBAreas' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'MHoliday' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'BadgeAccess' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
UPDATE UFD SET UFD.UserFieldName = UFD.UserFieldName + '_' FROM UserFieldDef AS UFD WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS COL WHERE COL.TABLE_NAME= 'ca_vw_BadgeAccessDetails' AND COL.COLUMN_NAME = UFD.UserFieldName COLLATE DATABASE_DEFAULT)
GO


ALTER TRIGGER [tr_Delete_Input] ON [dbo].[Input]
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@StationName nVarChar(50),
			@Description nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@Count int,
  			@Pnl int,
			@Inp int,
			@caObjectID uniqueidentifier,
			@UTCOffset int
SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 	
				BEGIN
					SELECT @Inp = InpNo, @Pnl = PnlRef FROM deleted
					Delete from SchedEvent WHERE Device=1 and PnlNo=@Pnl and DeviceNo=@Inp
					SELECT @old = (select PnlRef                                                                                                                           
					,InpNo                                                                                                                            
					,InputName                                                                                                                        
					,Description                                                                                                                      
					,Enabled                                                                                                                          
					,NormOpen                                                                                                                         
					,Supervised                                                                                                                       
					,NoXact                                                                                                                           
					,AbnormDial                                                                                                                       
					,NormDial                                                                                                                         
					,ConRelay                                                                                                                         
					,InpTz                                                                                                                            
					,InpDelay                                                                                                                         
					,InpReset                                                                                                                         
					,Priority                                                                                                                         
					,MapId                                                                                                                            
					,DeviceId                                                                                                                         
					,RespReq                                                                                                                          
--					,CCTVEnable                                                                                                                       
					--,CCTVAId                                                                                                                          
					--,CCTVNId                                                                                                                          
					,Location                                                                                                                         
					--,CCTVFunction                                                                                                                     
					--,TakeCCTVSnapshot                                                                                                                 
					--,AbnormalCCTVFunction                                                                                                             
					--,AbnormalTakeCCTVSnapshot                                                                                                         
					--,CCTVDelay                                                                                                                        
					--,AbnormalCCTVDelay                                                                                                                
					--,CamfuncEnable                                                                                                                    
					--,CamfuncWorkstation                                                                                                               
					,DvrServerID                                                                                                                      
					,DvrCameraID                                                                                                                      
					,VideoStartTime                                                                                                                   
					,VideoEndTime                                                                                                                     
					,VideoEventType                                                                                                                   
					,EnableRecording                                                                                                                  
					,NoofSecsToRecord                                                                                                                 
					,AlertLimit                                                                                                                       
					,LimitSchedule
					,[EnablePTZPreset]
				    ,[RecordingSchedule]
				    ,[CatReader]
				    ,[CatCode]
				    ,[Categories]
				    ,[CatSchedule]
					,caObjectID                                                                                                                       
					,LastUpdated                                                                                                                      
					,LastOperator                                                                                                                     
					,LastWorkStation                                                                                                                  
					 from deleted for XML AUTO)
					SELECT @Description = deleted.InputName FROM deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'

			Set @TableName = 'Input'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
			select @caObjectID = deleted.caObjectID FROM deleted
			select @UTCOffset = datepart(tz,sysdatetimeoffset())
			
			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName,LastOperator,caObjectID,UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName,@LastOperator,@caObjectID,@UTCOffset)

			IF @Inp > 0
			  BEGIN
				SELECT @Count= count(*) FROM Link WHERE PnlRef = @Pnl AND Inp1 = @Inp
				IF @Count>0
				BEGIN
					update Link set Inp1 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Inp1 = @Inp
				END
				SELECT @Count= count(*) FROM Link WHERE  PnlRef = @Pnl AND Inp2 = @Inp
				IF @Count>0
				BEGIN
					update Link set Inp2 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Inp2 = @Inp
				END
				SELECT @Count= count(*) FROM Link WHERE  PnlRef = @Pnl AND Inp3 = @Inp
				IF @Count>0
				BEGIN
					update Link set Inp3 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Inp3 = @Inp
				END
				SELECT @Count= count(*) FROM Link WHERE  PnlRef = @Pnl AND Inp4 = @Inp
				IF @Count>0
				BEGIN
					update Link set Inp4 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Inp4 = @Inp
				END
				SELECT @Count= count(*) FROM Link WHERE  PnlRef = @Pnl AND Inp5 = @Inp
				IF @Count>0
				BEGIN
					update Link set Inp5 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Inp5 = @Inp
				END
				SELECT @Count= count(*) FROM Shunt WHERE PnlRef = @Pnl AND InpRef = @Inp
				IF @Count>0
				BEGIN
					delete shunt where PnlRef = @Pnl AND InpRef = @Inp
				END

				SELECT @Count=COUNT(*) from MActiveLinks WHERE PnlRef = @Pnl AND Device=@Inp
				IF @Count>0
				BEGIN
					UPDATE MActiveLinks SET Device = -1, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation WHERE PnlRef = @Pnl AND Device = @Inp and EventCode between 4 and 8
				END
				
				SELECT @Count=COUNT(*) from ActiveLinks WHERE PnlRef = @Pnl AND InpNo = @Inp
				IF @Count>0
				BEGIN
					UPDATE ActiveLinks SET InpNo = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation WHERE PnlRef = @Pnl AND InpNo = @Inp
				END

				DELETE FacilityMapPoints WHERE Type = 1 AND DevNo = @Inp AND PanelNo = @Pnl
			END
		END		
	
GO


ALTER TRIGGER [dbo].[tr_Delete_Link] ON [dbo].[Link]
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@StationName nVarChar(50),
			@Description nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@Count int,
			@Pnl int,
			@Lnk int,
			@caObjectID uniqueidentifier,
			@UTCOffset int
    SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 
				BEGIN
					SELECT @Lnk = ProgNo, @Pnl = PnlRef FROM deleted
                    Delete from SchedEvent WHERE Device=3 and PnlNo=@Pnl and DeviceNo=@Lnk
					SELECT @old = (select 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       
					,caObjectID     
					,LastUpdated    
					,LastOperator   
					,LastWorkStation  from deleted for XML AUTO)
					SELECT @Description = deleted.LinkName FROM deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'

			Set @TableName = 'Link'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
			
			select @caObjectID = deleted.caObjectID FROM deleted
			select @UTCOffset = datepart(tz,sysdatetimeoffset())
			
			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName,LastOperator,caObjectID,UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName,@LastOperator,@caObjectID,@UTCOffset)
						 
			IF @Lnk > 0
			BEGIN			   
				UPDATE MActiveLinks SET Device = -1 WHERE PnlRef = @Pnl AND Device = @Lnk and EventCode between 11 and 12
			END
			DELETE FacilityMapPoints WHERE Type = 3 AND DevNo = @Lnk AND PanelNo = @Pnl
		END		

GO


ALTER TRIGGER [tr_Delete_MActiveLinks] ON dbo.MActiveLinks
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@StationName nVarChar(50),
			@Description nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@Count int,
			@PnlRef int,
			@ActivityLink int,
			@caObjectID uniqueidentifier,
			@UTCOffset int
	SELECT @PnlRef = PnlRef, @ActivityLink = ProgNo from deleted
    SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 
				BEGIN	
					SELECT @old = (select * from deleted for XML AUTO)
					SELECT @Description = deleted.Description FROM deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'
			Set @TableName = 'MActiveLinks'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
		    SELECT @caObjectID = deleted.caObjectID FROM deleted
	        SELECT @UTCOffset = datepart(tz,sysdatetimeoffset())

			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName, LastOperator, caObjectID, UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName, @LastOperator, @caObjectID, @UTCOffset)
						 
			IF @ActivityLink > 0
			BEGIN
				UPDATE MActiveLinks SET Device = -1 WHERE PnlRef = @PnlRef AND Device = @ActivityLink and EventCode between 37 and 38			   
			END

		END

GO


ALTER TRIGGER [tr_Delete_Reader] ON [dbo].[Reader]
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@StationName nVarChar(50),
			@Description nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@Count int,
			@Pnl int,
			@Rdr int,
			@colName nVarchar(10),
			@mAct int,
			@SQL1 nVarchar(200),
			@caObjectID uniqueidentifier,
			@UTCOffset int
			
    SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 	
				BEGIN
					SELECT @Rdr = RdrNo, @Pnl = PnlRef FROM deleted
					Delete from SchedEvent WHERE Device=0 and PnlNo=@Pnl and DeviceNo=@rdr
					SELECT @old = (select 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           
						,TAType            
						,LockCtl           
						,Sensor            
						,Strike            
						,Bypass            
						,Shunt             
						,DurUse            
						,Cyph              
						,Type              
						,MapId             
						,DeviceId          
						,DegrSchedule      
						,ShuntTime         
						,ManualPriv        
						,TagTimeout        
						,APBEntry          
						,APBExit           
						,CatCode           
						,CatFilter
						,CatSchedule
						,RepeatOTL         
						,OTLSchedule       
						,NAPCOAreas        
						,Location          
						,RespForcedDoor    
						,RespOTL           
						,RespDuress        
						,RespTrackedBadge  
						,RespVoidBadge     
						,RespAPBViolate    
						,RespOtherDenied   
						,RespBypass        
						,RespFreeAccess    
						,RespManualDoorCtrl
						,RespCommonCode    
						,RespValidAccess   
						,AckBypass         
						,AckFreeAccess     
						,AckManualDoorCtrl 
						,AckCommonCode     
						,AckValidAccess    
						,TAAPBType      
						,BadgeUseRegister   
						,LastUpdated       
						,caObjectID        
						,LastOperator      
						,LastWorkStation 
						,NoSound
						,AperioAddr
						,DoubleReadFA
						,BypassLocks
						,RexOption
						,UseLedBar
						,ElevServerId
						,GenerateAlert
						,DecReader   from deleted for XML AUTO)
					SELECT @Description = deleted.ReaderName FROM deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'

			Set @TableName = 'Reader'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
			SELECT @caObjectID = deleted.caObjectID FROM deleted
			SELECT @UTCOffset = datepart(tz,sysdatetimeoffset())

			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName, LastOperator, caObjectID, UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName, @LastOperator, @caObjectID, @UTCOffset)
			
			IF @Pnl > 0 and @Rdr > 0
			BEGIN			   
				DELETE FacilityMapPoints WHERE Type = 5 AND DevNo = @Rdr AND PanelNo = @Pnl

			    SET @mAct=(SELECT Count(Device) FROM MActiveLinks WHERE PnlRef = @Pnl AND Device = @Rdr AND EventCode IN (13, 14, 15, 16, 17, 18, 19, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 36, 39, 40, 41, 42, 43, 44, 45, 54 ))		   
				IF @mAct>0
				BEGIN
					UPDATE MActiveLinks SET Device = -1,LastOperator=@LastOperator,LastWorkStation=@LastWorkStation 
						WHERE PnlRef = @Pnl AND Device = @Rdr AND EventCode IN (13, 14, 15, 16, 17, 18, 19, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 36, 39, 40, 41, 42, 43, 44, 45, 54 ) 						
			    END
				UPDATE ActiveLinks SET RdrNo = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation WHERE PnlRef = @Pnl AND RdrNo = @Rdr
			END

			SET @colName='Tz'+CONVERT(nVarChar(10),@Rdr)
			SET @SQL1='update AccGrp set '+@colName+'=0, LastWorkStation='+''''+@LastWorkStation+''''+',LastOperator='+''''+CONVERT(nVarChar(36),@LastOperator)+''''+' where PnlRef ='+CONVERT(nVarChar(10), @Pnl)
			EXEC(@SQL1)
		END 

GO


ALTER TRIGGER [tr_Delete_Relay] ON [dbo].[Relay]
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@StationName nVarChar(50),
			@Description nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@Count int,
			@Rel int,
			@Pnl int,
			@caObjectID uniqueidentifier,
			@UTCOffset int
    SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 	
				BEGIN
					SELECT @Rel = RelNo, @Pnl = PnlRef FROM deleted
					Delete from SchedEvent WHERE Device=2 and PnlNo=@Pnl and DeviceNo=@Rel
					SELECT @old = (select PnlRef          
						,RelNo           
						,RelayName       
						,Description     
						,Enabled         
						,XactRpt         
						,Dialup          
						,NormOn          
						,RelTime         
						,TrackTz         
						,Priority        
						,MapId           
						,DeviceId        
						,RespReq         
						,ManualPriv      
						,Location        
						,caObjectID      
						,LastUpdated     
						,LastOperator    
						,LastWorkStation   from deleted for XML AUTO)
					SELECT @Description = deleted.RelayName FROM deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'

			Set @TableName = 'Relay'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
			
			select @caObjectID = deleted.caObjectID FROM deleted
			select @UTCOffset = datepart(tz,sysdatetimeoffset())

			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName,LastOperator,caObjectID,UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName,@LastOperator,@caObjectID,@UTCOffset)

			IF @Rel>0
			BEGIN
				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND On1=@Rel
				if @Count>0
				begin
					update Link set On1 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND On1 = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND On2=@Rel
				if @Count>0
				begin
					update Link set On2 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND On2  = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND On3=@Rel
				if @Count>0
				begin
					update Link set On3 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND On3  = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND On4=@Rel
				if @Count>0
				begin
					update Link set On4 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND On4  = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND On5=@Rel
				if @Count>0
				begin
					update Link set On5 = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND On5  = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Off1=@Rel
				if @Count>0
				begin
					update Link set Off1= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Off1 = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Off2=@Rel
				if @Count>0
				begin
					update Link set Off2= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Off2 = @Rel
				end

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Off3=@Rel
				if @Count>0
				begin
					update Link set Off3= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Off3 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Off4=@Rel
				if @Count>0
				begin
					update Link set Off4= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Off4 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Off5=@Rel
				if @Count>0
				begin
					update Link set Off5= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Off5 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Trk1=@Rel
				if @Count>0
				begin
					update Link set Trk1= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Trk1 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Trk2=@Rel
				if @Count>0
				begin
					update Link set Trk2= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Trk2 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Trk3=@Rel
				if @Count>0
				begin
					update Link set Trk3= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Trk3 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Trk4=@Rel
				if @Count>0
				begin
					update Link set Trk4= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Trk4 = @Rel
				end 

				SELECT @Count= count(*) from Link where PnlRef = @Pnl AND Trk5=@Rel
				if @Count>0
				begin
					update Link set Trk5= 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation where PnlRef = @Pnl AND Trk5 = @Rel
				end 

				SELECT @Count=COUNT(*) from MActiveLinks WHERE PnlRef = @Pnl AND Device=@Rel
				IF @Count>0
				BEGIN
					UPDATE MActiveLinks SET Device = -1, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation WHERE PnlRef = @Pnl AND Device = @Rel and EventCode between 9 and 10
				END
				
				SELECT @Count=COUNT(*) from ActiveLinks WHERE PnlRef = @Pnl AND RelNo = @Rel
				IF @Count>0
				BEGIN
					UPDATE ActiveLinks SET RelNo = 0, LastOperator=@LastOperator, LastWorkStation=@LastWorkStation WHERE PnlRef = @Pnl AND RelNo = @Rel
				END

				DELETE FacilityMapPoints WHERE Type = 2 AND DevNo = @Rel AND PanelNo = @Pnl
			  END
		
		END

GO

ALTER TRIGGER [tr_Delete_DvrConfig] ON [dbo].[DvrConfig]
FOR DELETE AS
	DECLARE @old XML,
			@TableName nvarchar(50),
			@OperatorName nVarChar(50),
			@Description nVarChar(50),
			@StationName nVarChar(50),
			@LastWorkStation nvarchar (50),
			@LastOperator uniqueidentifier,
			@DVRID uniqueidentifier,
			@Count int,
			@UTCOffset int
    SELECT @Count = count(*) from deleted
	IF @Count > 0 
		BEGIN
			IF @Count = 1 
				BEGIN	
					SELECT @old = (select * from deleted for XML AUTO)
					SELECT @DVRID = (Select DvrID From deleted)
					SELECT @Description = deleted.ServerName from deleted
				END
			ELSE
				SET @Description = CONVERT(VarChar,@Count) + ' Rows'
			Set @TableName = 'DvrConfig'
			SELECT TOP 1 @LastWorkStation = deleted.LastWorkStation FROM deleted
			SELECT TOP 1 @LastOperator = deleted.LastOperator FROM deleted
			Select @UTCOffset = datediff(mi,GetUTCDate(),GetDate())

			exec ca_sp_OperatorWorkStation @LastWorkStation, @LastOperator, @OperatorName = @OperatorName OUTPUT, @StationName = @StationName OUTPUT
		 
			INSERT INTO DBAudit( Tablename, Description,Actions,RevisionStamp,OldData,OperatorName ,StationName,UTCOffset)
						 VALUES(@TableName,@Description,'D'    ,GetUTCDate()    ,@old   ,@OperatorName,@StationName,@UTCOffset)

			DELETE FROM DVRCAMERA WHERE DVRServerID = @DVRID

			UPDATE [Reader] SET [DvrServerID] = NULL, [DvrCameraID] = NULL WHERE [DvrServerID] = @DVRID
			UPDATE [Input]  SET [DvrServerID] = NULL, [DvrCameraID] = NULL WHERE [DvrServerID] = @DVRID
		END
GO
	
	ALTER VIEW [dbo].[ca_vw_BadgesGetForParam]
	AS
	--Author:AEL Team

	SELECT P.PersonID, B.Badge,B.Facility,
		ISNULL(P.LastName,'') AS LastName,  
		ISNULL(P.FrstName,'') AS FirstName,
		B.APBSet,
		IsNull(P.DeptID, '') AS Department,
		IsNull(cast(P.LocationID as varchar(50)), '') AS Location,
		IsNull(cast(P.CompanyID as varchar(50)), '') AS Company,
		IsNull(cast(P.SupervisorID as varchar(50)), '') AS Supervisor	
	FROM dbo.Person P
	INNER JOIN Badge B ON P.PersonID = B.PersonID
    -----------------------
	GO
	
	ALTER VIEW [dbo].[ca_vw_GetAccessGroupListData]
	AS
	--Author:AEL Team
	/*
	when select [0-No access] report purpose then 
	SELECT DISTINCT BadgeID, LastName, FirstName 
	FROM vw_GetAccessGroupListData
	*/
		SELECT  
			BA.AGroupNo		AS AccessGroupID,
			B.Badge			AS BadgeID, 
			B.Facility, 
			P.LastName		AS LastName, 
			P.FrstName		AS FirstName,
			P.PersonID		AS PersonID,
			B.Enabled
		FROM dbo.Badge B INNER JOIN dbo.BadgeAccess BA ON B.Badge = BA.Badge AND B.Facility = BA.Facility
		INNER JOIN dbo.Person P ON P.PersonID = B.PersonID
	---------------
	GO
	
	ALTER VIEW [dbo].[ca_vw_GetReaderAccessListData]
	AS
	--Author:AEL Team

		SELECT  B.Badge 	AS BadgeID,
			B.Facility,
			P.LastName 	AS LastName, 
			P.FrstName 	AS FirstName, 
			P.PersonID     AS PersonID,
			BA.AGroupNo 	AS AccessGroupID, 
			M.Description 	AS AccessGroupName, 
			V.ReaderID 	AS ReaderID, 
			V.PanelID 	AS PanelID, 
			--V.TimeZoneID 	AS TimeZoneID, 
			MT.Description 	AS TimeZoneName,
			B.Enabled as Enabled
		FROM dbo.Badge B 
		INNER JOIN dbo.BadgeAccess BA ON B.Badge = BA.Badge
		INNER JOIN dbo.MAccGrp M ON M.AgrpNo = BA.AGroupNo AND BA.Badge = B.Badge AND BA.Facility=B.Facility 
		INNER JOIN ca_vw_ReaderAccessGroupTimeZone V ON V.AccessGroupID = M.AGrpNO 
		INNER JOIN dbo.MTimeZon MT ON MT.TzNum = V.TimeZoneID
		INNER JOIN dbo.Person P ON P.PersonID = B.PersonID
	------------------------
	GO
	ALTER VIEW [dbo].[ca_vw_GetPersonnelAccessListData]
	AS
	--Author:AEL Team

	SELECT 
		V.BadgeID		AS BadgeID,
		V.Facility,
		V.PersonID    AS PersonID,
		V.PanelID		AS PanelID, 
		V.ReaderID		AS ReaderID, 
		R.ReaderName, 
		V.TimeZoneName  	AS TimeZoneName,
		V.AccessGroupName	AS AccessGroupName,
        R.caObjectID
	FROM dbo.Reader R
	INNER JOIN dbo.ca_vw_GetReaderAccessListData V ON V.ReaderID = R.RdrNo AND V.PanelID = R.PnlRef

	GO

	ALTER PROCEDURE [dbo].[ca_sp_ChunkResultSetCount]( 
	@Where varchar(2000) ,
	@Table varchar(3000) ,
	@ObjectID varchar(30) ,
	@OperatorID UniqueIdentifier ,
	@Partitioned bit ,
	@ConfigDb varchar(3000),
	@TotalCount int OUTPUT)	
AS
BEGIN
	
DECLARE	  @sql nVarChar(4000)
			, @vWhere nVarChar(2000)
			, @Partitionstr varchar(3000);
	
	
	IF @Where <> ''
	BEGIN
		IF @Partitioned = 1
		BEGIN
			SET @vWhere = ' AND ' + @Where;
		END
		ELSE
		BEGIN
		  SET @vWhere = ' WHERE ' + @Where;
		END
	END
	ELSE
	BEGIN
	  SET @vWhere = '';
	END

	IF @Partitioned = 1
	BEGIN
		IF @OperatorID <> '1EEB696C-0E28-4702-8598-C379C95B45E1'
		BEGIN
			SET @Partitionstr=' where ('+@ObjectID+' in (SELECT ['+@ConfigDb+'].dbo.devicepartitions.caObjectID FROM ['+@ConfigDb+'].dbo.OperatorPartitions  inner join  ['+@ConfigDb+'].dbo.devicepartitions   on ['+@ConfigDb+'].dbo.devicepartitions.PartitionId = ['+@ConfigDb+'].dbo.OperatorPartitions.Partitionid where ['+@ConfigDb+'].dbo.OperatorPartitions.OperatorID =@pOperatorID)) ';
		END
		ELSE
		BEGIN
			SET @Partitionstr=' where ('+@ObjectID+' in (SELECT ['+@ConfigDb+'].dbo.devicepartitions.caObjectID FROM ['+@ConfigDb+'].dbo.OperatorPartitions  inner join  ['+@ConfigDb+'].dbo.devicepartitions   on ['+@ConfigDb+'].dbo.devicepartitions.PartitionId = ['+@ConfigDb+'].dbo.OperatorPartitions.Partitionid where ['+@ConfigDb+'].dbo.OperatorPartitions.OperatorID =@pOperatorID) or '+@ObjectID+' not in (SELECT ['+@ConfigDb+'].dbo.devicepartitions.caObjectID FROM  ['+@ConfigDb+'].dbo.devicepartitions)) ';
		END
	END
	ELSE
	BEGIN
		SET @Partitionstr = '';
	END

	if(CHARINDEX('person p', @Table)>0)
		SET @sql = N'select @TotalCount = count (distinct  p.PersonId) from '+@Table+@Partitionstr+@vWhere;
	else
		SET @sql = N'select @TotalCount = count (*) from '+@Table+@Partitionstr+@vWhere;

	EXEC sp_executesql @sql, N'@TotalCount int output, @pOperatorID UniqueIdentifier',  @TotalCount output, @pOperatorID= @OperatorID;
	Print @sql

	--RETURN @TotalCount;
END

