SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER PROCEDURE [dbo].[up_MergeUsersInForeignTables]
		@deletedUserId int,
		@selectedUserId int
	AS
	BEGIN

		--#region Task Creator
		update t
		set CreatorId = @selectedUserId
		from Task as t
		where CreatorId = @deletedUserId
		--#endregion
		--#region Task IssuerId
		update t
		set t.IssuerId = @selectedUserId
		from Task as t
		where t.IssuerId = @deletedUserId
		--#endregion
		--#region Service 
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from ServiceUser as t
		where	t.UserId = @deletedUserId 
				and (select count(*) from ServiceUser where ServiceId = t.ServiceId and UserId = @selectedUserId) = 0
		--удалим остальное
		delete from ServiceUser
		where	UserId = @deletedUserId
		--#endregion
	
		--#region TaskExecutor
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from TaskExecutor as t
		where	t.UserId = @deletedUserId 
				and (select count(*) from TaskExecutor where TaskId = t.TaskId and UserId = @selectedUserId) = 0
		--удалим остальное
		delete from TaskExecutor
		where	UserId = @deletedUserId
		--#endregion

		--#region TaskParticipant
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from TaskParticipant as t
		where	t.UserId = @deletedUserId 
				and (select count(*) from TaskParticipant where TaskId = t.TaskId and UserId = @selectedUserId) = 0
		--удалим остальное
		delete from TaskParticipant
		where	UserId = @deletedUserId
		--#endregion

		--#region ExecutorGroupExecutor
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from ExecutorGroupExecutor as t
		where	t.UserId = @deletedUserId 
				and (select count(*) from TaskParticipant where ExecutorGroupId = t.ExecutorGroupId and UserId = @selectedUserId) = 0	
		--удалим остальное
		delete from ExecutorGroupExecutor
		where	UserId = @deletedUserId
		--#endregion

		--#region ServiceLevelFile
		update t
		set t.CreatorId = @selectedUserId
		from ServiceLevelFile as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region Gauge
		update t
		set t.UserId = @selectedUserId
		from Gauge as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region UserSchedule
		update t
		set t.UserId = @selectedUserId
		from UserSchedule as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region KBDocumentFile
		update t
		set t.CreatorId = @selectedUserId
		from KBDocumentFile as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region ServiceLevelRuleUser
		update t
		set t.UserId = @selectedUserId
		from ServiceLevelRuleUser as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region ExecutorGroupObserver
		update t
		set t.UserId = @selectedUserId
		from ExecutorGroupObserver as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region ServiceStatusRuleExecutor ExecutorId
		update t
		set t.ExecutorId = @selectedUserId
		from ServiceStatusRuleExecutor as t
		where t.ExecutorId = @deletedUserId
		--#endregion

		--#region ServiceStatusRuleExecutor ObserverId
		update t
		set t.ObserverId = @selectedUserId
		from ServiceStatusRuleExecutor as t
		where t.ObserverId = @deletedUserId
		--#endregion

		--#region UserFile
		update t
		set t.UserId = @selectedUserId
		from UserFile as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region IMAccountRuleUser
		update t
		set t.UserId = @selectedUserId
		from IMAccountRuleUser as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region TaskFile
		update t
		set t.CreatorId = @selectedUserId
		from TaskFile as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region VisitedTask
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from VisitedTask as t
		where	t.UserId = @deletedUserId 
				and (select count(*) from VisitedTask where TaskId = t.TaskId and UserId = @selectedUserId) = 0
		--удалим остальное
		delete from VisitedTask
		where	UserId = @deletedUserId
		--#endregion

		--#region TaskExpenses UserId
		update t
		set t.UserId = @selectedUserId
		from TaskExpenses as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region TaskExpenses EditorId
		update t
		set t.EditorId = @selectedUserId
		from TaskExpenses as t
		where t.EditorId = @deletedUserId
		--#endregion

        --#region TaskExpenses CreatorID
		update t
		set t.CreatorId = @selectedUserId
		from TaskExpenses as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region Task CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from Task as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region Task EditorId
		update t
		set t.EditorId = @selectedUserId
		from Task as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region FilterNotificationRule
		update t
		set t.UserId = @selectedUserId
		from FilterNotificationRule as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region TaskLifetime
		update t
		set t.EditorId = @selectedUserId
		from TaskLifetime as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region Filter
		update t
		set t.UserId = @selectedUserId
		from Filter as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region CoordinationList
		update t
		set t.InitiatorId = @selectedUserId
		from CoordinationList as t
		where t.InitiatorId = @deletedUserId
		--#endregion

		--#region Coordinator
		update t
		set t.UserId = @selectedUserId
		from Coordinator as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region FilterPermission
		update t
		set t.UserId = @selectedUserId
		from FilterPermission as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region TaskHistory CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from TaskHistory as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region TaskHistory EditorId
		update t
		set t.EditorId = @selectedUserId
		from TaskHistory as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region ServiceEscalationRuleUser
		update t
		set t.UserId = @selectedUserId
		from ServiceEscalationRuleUser as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region ServiceExecutorRule ExecutorId
		update t
		set t.ExecutorId = @selectedUserId
		from ServiceExecutorRule as t
		where t.ExecutorId = @deletedUserId
		--#endregion

		--#region ServiceExecutorRule ObserverId
		update t
		set t.ObserverId = @selectedUserId
		from ServiceExecutorRule as t
		where t.ObserverId = @deletedUserId
		--#endregion

		--#region ServiceElected
		update t
		set t.UserId = @selectedUserId
		from ServiceElected as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region ServiceExternalTransition
		update t
		set t.UserId = @selectedUserId
		from ServiceExternalTransition as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region ServiceOwner
		update t
		set t.UserId = @selectedUserId
		from ServiceOwner as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region NotificationRule
		update t
		set t.UserId = @selectedUserId
		from NotificationRule as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region Service
		update t
		set t.EditorId = @selectedUserId
		from [Service] as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region KBFolderPermission
		update t
		set t.UserId = @selectedUserId
		from KBFolderPermission as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region CompanyManager
		update t
		set t.UserId = @selectedUserId
		from CompanyManager as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region CompanyDocument CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from CompanyDocument as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region CompanyDocument EditorId
		update t
		set t.EditorId = @selectedUserId
		from CompanyDocument as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region CompanyDocumentHistory CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from CompanyDocumentHistory as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region CompanyDocumentHistory EditorId
		update t
		set t.EditorId = @selectedUserId
		from CompanyDocumentHistory as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region AssetGroup CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from AssetGroup as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region AssetGroup EditorId
		update t
		set t.EditorId = @selectedUserId
		from AssetGroup as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region Asset CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from Asset as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region Asset EditorId
		update t
		set t.EditorId = @selectedUserId
		from Asset as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region Asset OwnerId
		update t
		set t.OwnerId = @selectedUserId
		from Asset as t
		where t.OwnerId = @deletedUserId
		--#endregion

		--#region AssetFile
		update t
		set t.UserId = @selectedUserId
		from AssetFile as t
		where t.UserId = @deletedUserId
		--#endregion

		--#region AssetHistory CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from AssetHistory as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region AssetHistory EditorId
		update t
		set t.EditorId = @selectedUserId
		from AssetHistory as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region AssetHistory OwnerId
		update t
		set t.OwnerId = @selectedUserId
		from AssetHistory as t
		where t.OwnerId = @deletedUserId
		--#endregion

		--#region AssetLink CreatorId
		update t
		set t.CreatorId = @selectedUserId
		from AssetLink as t
		where t.CreatorId = @deletedUserId
		--#endregion

		--#region AssetLink EditorId
		update t
		set t.EditorId = @selectedUserId
		from AssetLink as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region AssetLink SecondUserId
		update t
		set t.SecondUserId = @selectedUserId
		from AssetLink as t
		where t.SecondUserId = @deletedUserId
		--#endregion

		--#region Company DirectorId
		update t
		set t.DirectorId = @selectedUserId
		from Company as t
		where t.DirectorId = @deletedUserId
		--#endregion

		--#region Company ContactPersonId
		update t
		set t.ContactPersonId = @selectedUserId
		from Company as t
		where t.ContactPersonId = @deletedUserId
		--#endregion

		--#region Role
		update t
		set t.EditorId = @selectedUserId
		from [Role] as t
		where t.EditorId = @deletedUserId
		--#endregion

		--#region Service 
		--заменим где нет дубликатов
		update t
		set UserId = @selectedUserId
		from WorkflowStatusMatrix as t
		where	t.UserId = @deletedUserId 
				and (	select count(*) 
						from WorkflowStatusMatrix 
						where WorkflowId = t.WorkflowId 
								and FromStatusId = t.FromStatusId
								and ToStatusId = t.ToStatusId
								and UserId = @selectedUserId) = 0
		--удалим остальное
		delete from WorkflowStatusMatrix
		where	UserId = @deletedUserId
		--#endregion
	
	END