EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', 'true'
GO

DECLARE @NoSound NVARCHAR(20)
SET @NoSound = 'NoSound'
declare @caDBVersion int, @caDBType int 
exec ca_sp_DatabaseVersion @caDBVersion OUTPUT,@caDBType OUTPUT
IF @caDBType = 0 
BEGIN
	Update dbo.Reader set @NoSound = 0  where @NoSound is null

	exec('update dbo.Input set  VideoEventType = 1 where VideoEventType = null;
		 update dbo.Input set  RecordingSchedule = 1 where RecordingSchedule = null;
		 update dbo.Input set  VideoStartTime = 50 where VideoStartTime = 0;
		 update dbo.Input set  VideoEndTime = 50 where VideoEndTime = 0;')

	exec('UPDATE dbo.Com SET Baud = NULL WHERE Type = 10')

	exec ('update dbo.operators set EventCount = 300 where EventCount > 300;
		   update dbo.operators set LogoffTime = 9999 where LogoffTime > 9999;')

 -- This section is only usefull when update from CA4K beta release for users who had admin privileges and less number of devices in their partition
  	CREATE TABLE #TMP_ROLES
    (
  	  RoleID UNIQUEIDENTIFIER,
  	  OperatorID UNIQUEIDENTIFIER,
  	  RoleName NVARCHAR(1000),
  	  LastOperator UNIQUEIDENTIFIER
    )
  
    INSERT INTO #TMP_ROLES
    SELECT NEWID() RoleID, [OperatorID], 'New Role (' + [OperLoginName] + ')' AS RoleName, '1EEB696C-0E28-4702-8598-C379C95B45E1' as LastOperator from (
  	  SELECT distinct 
		   O.[OperatorID]
  		  ,O.[RoleID]
  		  ,O.[OperLoginName]
  	  FROM [Operators] O
  	  INNER JOIN [Roles] R ON R.RoleID = O.RoleID
  	  INNER JOIN [OperatorPartitions] OP ON OP.OperatorID = O.OperatorID
  	  WHERE O.[RoleID] = '82B19834-4EF0-405B-9AD1-23860AAF4139' and OP.[PartitionID] <> '3DC78C7B-C9CE-4485-A835-63D577F623E6'
    ) AS TAB_ROLES
    
	IF EXISTS(SELECT 1 FROM #TMP_ROLES)
	BEGIN
	 	INSERT INTO [Roles] ([RoleID], [RoleName], [LastOperator]) SELECT RoleID, RoleName, LastOperator FROM #TMP_ROLES
  
		INSERT INTO RolePlugins (RoleID, pluginId)
		SELECT ROLES.RoleID, P.pluginId FROM 
		( 
  			SELECT N'2fb18291-24fc-4c94-be0a-961d568c3704'	as pluginId     union
  			SELECT N'5c269286-19ec-491b-9ed0-5ce58a59d472'	as pluginId	union
  			SELECT N'c1faa462-1e95-4f5f-ac90-56906da8d277'	as pluginId	union
  			SELECT N'2a369362-fa7d-41cd-ba0f-a73d08c1cee5'	as pluginId	union
  			SELECT N'087c90e4-3886-45de-b741-d1d72bc5a4d4'	as pluginId	union
  			SELECT N'ad41ebe0-90a3-4efe-845a-329b86f5f268'	as pluginId	union
  			SELECT N'9ab5fd17-d971-4a36-b84d-104a8f6dd901'	as pluginId	union
  			SELECT N'85f9faec-30a9-4040-840f-8445da537c4a'	as pluginId	union
  			SELECT N'9a7e0c9f-7df7-4c7e-a83d-6ee8c1bdb340'	as pluginId	union
  			SELECT N'f0c63b93-fb9b-4a45-8c8b-079a483bb5cf'	as pluginId	union
  			SELECT N'e72d425b-b6f2-498e-a85e-251327bfbb18'	as pluginId	union
  			SELECT N'bafca1bc-1968-427d-b865-171ef6f31fd9'	as pluginId	union
  			SELECT N'0a431dea-5039-412e-a578-25e991ecfc6b'	as pluginId	union
  			SELECT N'75a541fe-fa44-45ee-a32c-ad14660c8d83'	as pluginId	union
  			SELECT N'f046efde-1b3b-4e1e-a9d0-e8443f7dc96d'	as pluginId	union
  			SELECT N'088cb9a2-e5df-49fd-8bfd-e56a74465a52'	as pluginId	union
  			SELECT N'60b08fd7-cd5d-4b47-9f1e-63378822c270'	as pluginId	union
  			SELECT N'1c2231e9-137b-4fa5-994f-dcfbd91d9766'	as pluginId	union
  			SELECT N'd6f2ba94-92ad-421e-9200-219d75c2b901'	as pluginId	union
  			SELECT N'df3744df-8af7-41c2-9956-dd02e4f9e615'	as pluginId	union
  			SELECT N'74191911-7E00-41F6-B203-32D739307F45'	as pluginId	union
  			SELECT N'D0FB68C3-51DB-4525-9D5B-3259EB527694'	as pluginId	union
  			SELECT N'DC2D62CA-7380-46CC-9D66-5BE19A5040A2'	as pluginId	union
  			SELECT N'7A39C363-7078-4CBC-8D68-B5320026CAD2'	as pluginId	union
  			SELECT N'A66B238B-4BE8-4196-918D-EE13A50B964F'	as pluginId	union
  			SELECT N'212CADEF-98A8-4B65-B364-A617FBB17293'	as pluginId	union
  			SELECT N'F38A64E0-E1F4-4A37-B94E-3C64BBF605ED'	as pluginId	union
  			SELECT N'F38A64E0-E1F4-4A37-B94E-3C64BBF605EE'	as pluginId	
		) P
		CROSS JOIN #TMP_ROLES  AS ROLES

		INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
		SELECT N'3dc78c7b-c9ce-4485-a835-63d577f623e6' AS [PartitionID], R.caObjectID AS [caObjectID] FROM [Roles] AS R
		INNER JOIN #TMP_ROLES AS T ON R.RoleID = T.RoleID

  		DECLARE @RoleID AS UNIQUEIDENTIFIER
  		DECLARE ROLES_Cursor CURSOR  
  			FOR SELECT RoleID FROM #TMP_ROLES
  		OPEN ROLES_Cursor  
  		FETCH NEXT FROM ROLES_Cursor INTO @RoleID
			
  		WHILE @@FETCH_STATUS = 0  
  		BEGIN  
  			exec ca_sp_InsertRolePrivileges @RoleID, 0
			UPDATE [RolePrivileges] SET SecurityLevel = 3 WHERE ScreenObjectID = 0 AND RoleID = @RoleID
  			FETCH NEXT FROM ROLES_Cursor INTO @RoleID
  		END
  		CLOSE ROLES_Cursor  
		DEALLOCATE ROLES_Cursor

		UPDATE O
		SET O.RoleID = ROLES.RoleID
		FROM #TMP_ROLES AS ROLES INNER JOIN [Operators] AS O ON  ROLES.OperatorID = O.OperatorID
	END
    DROP TABLE #TMP_ROLES
	--END This section is only usefull when update from CA4K beta release for users who had admin privileges and less number of devices in their partition

	DELETE FROM RolePartitions
	exec('INSERT INTO [dbo].[RolePartitions]([PartitionID],[RoleID])
    SELECT distinct OperatorPartitions.PartitionID, Operators.RoleID
    FROM   Operators INNER JOIN OperatorPartitions ON Operators.OperatorID = OperatorPartitions.OperatorID')

	UPDATE [Timezone] 
	SET [TimeFrom] = DATEADD(YEAR, 2006 - YEAR([TimeFrom]), [TimeFrom]), [TimeTo] = DATEADD(YEAR, 2006 - YEAR([TimeTo]), [TimeTo])
	WHERE YEAR([TimeFrom]) < 2006 OR YEAR([TimeTo]) < 2006

	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT N'3dc78c7b-c9ce-4485-a835-63d577f623e6' AS [PartitionID], D.DvrID AS [caObjectID] FROM [DvrConfig] AS D
	WHERE D.DvrID <> N'00000000-0000-0000-0000-000000000000'
	AND NOT EXISTS (SELECT TOP 1 1 FROM DevicePartitions WHERE [caObjectID] = D.DvrID)

	INSERT INTO RolePlugins ([RoleID], [pluginId])
	SELECT N'82B19834-4EF0-405B-9AD1-23860AAF4139', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'82B19834-4EF0-405B-9AD1-23860AAF4139' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575') union
	SELECT N'AFC5A5C7-8834-4EB0-A4CA-307A2C38D135', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'AFC5A5C7-8834-4EB0-A4CA-307A2C38D135' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575') union
	SELECT N'2F9D40EE-3B9B-42E9-BE20-35100860298C', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'2F9D40EE-3B9B-42E9-BE20-35100860298C' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575') union
	SELECT N'C123ADF5-9023-4A9D-83D7-5146DFBAEE77', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'C123ADF5-9023-4A9D-83D7-5146DFBAEE77' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575') union
	SELECT N'F9EE89FC-1971-49BF-959F-57E23350BFD8', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'F9EE89FC-1971-49BF-959F-57E23350BFD8' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575') union
	SELECT N'0DEDD8D6-C408-4B85-97C8-E34F332A406F', N'5FC9C206-8A23-494C-BAAF-A2F1B0514575' WHERE NOT EXISTS (SELECT TOP 1 1 FROM RolePlugins WHERE [RoleID] = N'0DEDD8D6-C408-4B85-97C8-E34F332A406F' AND [pluginId] = N'5FC9C206-8A23-494C-BAAF-A2F1B0514575')

	--Operator Partition 
	SELECT PartitionID, Operators.OperatorID,1 as IsPrimaryPartition, RolePartitions.LastUpdated
	INTO [#TMP_RolePartitions]
	FROM RolePartitions inner join Operators on Operators.RoleID = RolePartitions.RoleID
	Where NOT EXISTS (SELECT TOP 1 1 FROM OperatorPartitions WHERE OperatorPartitions.OperatorID = Operators.OperatorID)

	UPDATE [#TMP_RolePartitions]
	SET IsPrimaryPartition = 0
	FROM [#TMP_RolePartitions] AS TAB1
	WHERE EXISTS (
			SELECT 1 FROM (
				SELECT max(cast(PartitionID as nvarchar(50))) MAXPartitionID, OperatorID, MAX(LastUpdated) MAXLastUpdated
				FROM [#TMP_RolePartitions]
				group by OperatorID
				HAVING COUNT(PartitionID) > 1
			) TAB2
			WHERE TAB2.OperatorID = TAB1.OperatorID AND TAB2.MAXPartitionID != TAB1.PartitionID  
			)
	INSERT INTO OperatorPartitions(PartitionID,OperatorID,IsPrimaryPartition,LastUpdated)
		SELECT PartitionID,OperatorID,IsPrimaryPartition,LastUpdated FROM [#TMP_RolePartitions]

	DROP TABLE [#TMP_RolePartitions]

	--Non Partition Panel insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM Panel
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition APBAreas insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM APBAreas
	WHERE AreaNo NOT IN(0,1,255) AND caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition Badge insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, PersonID FROM Badge
	WHERE PersonID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition Input insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM Input
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition Link insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM Link
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MAccGrp insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MAccGrp
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MActiveLinks insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MActiveLinks
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MActiveThreatLevel insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MActiveThreatLevel
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MCom insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MCom
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MHoliday insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MHoliday
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MLockdownAreas insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MLockdownAreas
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition MShunt insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM MShunt
	WHERE ShuntId>0 AND caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition Reader insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM Reader
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)

	--Non Partition Relay insert into DevicePartition with Admin
	INSERT INTO DevicePartitions ([PartitionID], [caObjectID])
	SELECT '3DC78C7B-C9CE-4485-A835-63D577F623E6' AS PartitionID, caObjectID FROM Relay
	WHERE caObjectID NOT IN (SELECT caObjectID FROM DevicePartitions)
	
	-- Insert Default values int DevicePartitions
	-- MTimezone 'Not Used'
	INSERT INTO DevicePartitions
	SELECT PartitionID,	'971B59B1-B15B-4880-B056-1D7D83337ACF' as caObjectID 
	FROM Partition
	WHERE NOT EXISTS(SELECT 1 FROM DevicePartitions WHERE DevicePartitions.PartitionID = Partition.PartitionID AND DevicePartitions.caObjectID = '971B59B1-B15B-4880-B056-1D7D83337ACF')
	
	 --1. Replace LastName=LN and Firstname=FN if LastName or Firstname is empty or null
	 UPDATE [dbo].[Person] SET LastName='LN' where LastName IS NULL OR LastName =''
	 UPDATE [dbo].[Person] SET FrstName='FN' where FrstName IS NULL OR FrstName =''
 
	 --2. Delete Person if there are no Badge records.
	 DELETE FROM dbo.Person WHERE NOT EXISTS(SELECT PersonID FROM Badge WHERE Badge.PersonID=Person.PersonID)
 
	 --3. Delete BadgeAccess data if there is no Badge record.
	 DELETE FROM dbo.BadgeAccess WHERE NOT EXISTS(SELECT Badge FROM dbo.Badge WHERE Badge.Badge=BadgeAccess.Badge and Badge.Facility=BadgeAccess.Facility)
 
	 --4. Delete BadgeCategories data if there is no Badge record.
	 DELETE FROM dbo.BadgeCategories WHERE NOT EXISTS(SELECT Badge FROM dbo.Badge WHERE Badge.Badge=BadgeCategories.Badge and Badge.Facility=BadgeCategories.Facility)

END
--IF @caDBType = 1 
--BEGIN
--	delete from dbo.Station 
--END
GO 
