GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spUpdateTicketLastAction](@TicketId INT)
AS
	BEGIN
		UPDATE [dbo].[tHdTicket] SET [LastActionDate] = (
			SELECT MAX(dAct) FROM (
			SELECT dAct FROM tAct WHERE dActRemoved IS NULL AND liActHdTicketId=@TicketId
			UNION ALL
			SELECT dHdTicketApprovalItem FROM tHdTicketApprovalItem JOIN tHdTicketApproval ON iHdTicketApprovalId = liHdTicketApprovalItemHdTicketApprovalId WHERE tHdTicketApproval.liHdTicketApprovalHdTicketId = @TicketId
			UNION ALL
			SELECT dHdTicketApprovalStarted FROM tHdTicketApproval WHERE liHdTicketApprovalHdTicketId = @TicketId
			UNION ALL
			SELECT ChangeDate FROM TicketChange WHERE TicketId = @TicketId
			UNION ALL
			SELECT Created FROM TicketRelation WHERE BeginHdTicketId = @TicketId
			UNION ALL
			SELECT Created FROM TicketRelation WHERE EndHdTicketId = @TicketId
			) A)
		 WHERE iHdTicketId=@TicketId	
	END;

go


ALTER TRIGGER [dbo].[TR_tAct_Insert_Update_Delete]
ON [dbo].[tAct]
AFTER INSERT, UPDATE, DELETE 
AS
BEGIN
	 
	DECLARE @event_type NVARCHAR(10)
	DECLARE @ticketId int

	IF EXISTS(SELECT * FROM inserted)
	BEGIN			
		IF EXISTS(SELECT * FROM deleted)
			SELECT @event_type = 'update'
		ELSE
			SELECT @event_type = 'insert'
		SELECT @ticketId = liActHdTicketId FROM inserted
	END
	ELSE IF EXISTS(SELECT * FROM deleted)
		BEGIN
			SELECT @event_type = 'delete'
			SELECT @ticketId = liActHdTicketId FROM deleted
		END
	ELSE
		SELECT @event_type = 'unknown'

	EXEC dbo.spUpdateTicketLastAction @ticketId
	
	IF EXISTS(SELECT TOP 1 1 FROM INSERTED WHERE ActOperationId = 3 OR ActOperationId = 4)
	BEGIN
		UPDATE tAct SET bWaitingForUser = 1 WHERE iActId = (SELECT TOP 1 iActId FROM inserted) AND ActOperationId = 3
		UPDATE tAct SET bWaitingForUser = 0 WHERE iActId = (SELECT TOP 1 iActId FROM inserted) AND ActOperationId = 4 
	END

	IF (@event_type =  'insert' AND EXISTS(SELECT 1 FROM inserted WHERE bWaitingForUser IS NOT NULL))
		OR (@event_type =  'update' AND EXISTS(SELECT 1 FROM inserted i, deleted d 
												WHERE ISNULL(CAST(i.bWaitingForUser AS INT), -1) <> ISNULL(CAST(d.bWaitingForUser AS INT), -1)
												OR (i.dAct <> d.dAct AND i.bWaitingForUser IS NOT NULL)
												OR i.dActRemoved is not null
												AND i.iActId = d.iActId))
		OR (@event_type =  'delete' AND	EXISTS(SELECT 1 FROM deleted d WHERE d.bWaitingForUser IS NOT NULL))
	BEGIN
		UPDATE [dbo].[tHdTicket] SET [WaitedForUserHours] =
		(
		SELECT SUM(CAST(B.dTo - B.dFrom AS float))
		FROM (
			SELECT 
				A.liActHdTicketId,
				CASE WHEN A.dFrom > OHC.dFrom THEN A.dFrom ELSE OHC.dFrom END dFrom,
				CASE WHEN A.dTo < OHC.dTo THEN A.dTo ELSE OHC.dTo END dTo
			FROM
				(
					SELECT
						A1.liActHdTicketId,
						A1.dAct AS dFrom,
						ISNULL(A2.dAct,GETUTCDATE()) AS dTo
					FROM tAct A1
					INNER JOIN tAct A2 ON A2.liActHdTicketId=A1.liActHdTicketId
						AND A2.bWaitingForUser IS NOT NULL
						AND (A2.dAct>A1.dAct OR A2.dAct=A1.dAct AND A2.iActId>A1.iActId)
						AND NOT EXISTS (
							SELECT 1
							FROM tAct A3
							WHERE A3.liActHdTicketId=A1.liActHdTicketId
								AND A3.bWaitingForUser IS NOT NULL
								AND (A3.dAct>A1.dAct OR A3.dAct=A1.dAct AND A3.iActId>A1.iActId)
								AND (A3.dAct<A2.dAct OR A3.dAct=A2.dAct AND A3.iActId<A2.iActId)
								AND A3.dActRemoved IS NULL
						)
					WHERE A1.bWaitingForUser=1
						AND A1.dActRemoved IS NULL
						AND A2.dActRemoved IS NULL
				) A
				INNER JOIN tSla SLA ON SLA.iSlaId = tHdTicket.liHdTicketSlaId
				INNER JOIN dbo.tOpeningHoursCache OHC ON OHC.liOpeningHoursId=SLA.liOpeningHoursId
					AND OHC.dFrom<A.dTo
					AND OHC.dTo>A.dFrom
				) B				
				WHERE B.liActHdTicketId=@ticketId
		) * 24.0
		WHERE iHdTicketId = @ticketId
	END

		UPDATE [dbo].[tHdTicket] SET
	[WorkHours] = (
		SELECT
			ROUND(SUM(nActWorkHours),2) AS nHdTicketWorkHours
		FROM tAct
		WHERE dActRemoved IS NULL AND liActHdTicketId = tHdTicket.iHdTicketId
		GROUP BY liActHdTicketId),
	[TravelHours] = (
		SELECT
			ROUND(SUM(nActTravelHours),2) AS nHdTicketTravelkHours
		FROM tAct
		WHERE dActRemoved IS NULL AND liActHdTicketId = tHdTicket.iHdTicketId
		GROUP BY liActHdTicketId),
	[TravelKm] = (
		SELECT
			ROUND(SUM(nActTravelKm),2) AS nHdTicketTravelKm
		FROM tAct
		WHERE dActRemoved IS NULL AND liActHdTicketId = tHdTicket.iHdTicketId
		GROUP BY liActHdTicketId),
	[HasReaction] = ISNULL((
		SELECT		
			HasReaction = 1
		FROM tAct ReactionAct
		LEFT JOIN tSla SLA ON tHdTicket.liHdTicketSlaId = SLA.iSlaId
		WHERE ReactionAct.iActId != tHdTicket.liHdTicketStartingActId
			AND ReactionAct.liActKindId NOT IN (8, 11)
			AND ((
				ReactionAct.liActKindId != 9 AND
				(ReactionAct.liActToPersonId = tHdTicket.liHdTicketUserPersonId 
				OR ReactionAct.sActToEmail = tHdTicket.sHdTicketUserEmail OR ReactionAct.sActToEmail = tHdTicket.UserEmail2 
				OR ReactionAct.sActTo LIKE ('%' + tHdTicket.sHdTicketUserEmail + '%') OR ReactionAct.sActTo LIKE ('%' + tHdTicket.UserEmail2 + '%') 
				OR ReactionAct.sActCc LIKE ('%' + tHdTicket.sHdTicketUserEmail + '%') OR ReactionAct.sActCc LIKE ('%' + tHdTicket.UserEmail2 + '%'))
				) OR (ReactionAct.liActKindId = 9 AND ReactionAct.ActOperationId IN (3,11))
				  OR (ReactionAct.liActKindId = 9 AND ReactionAct.ActOperationId = 2 AND tHdTicket.liHdTicketSolverPersonId = tHdTicket.liHdTicketUserPersonId))
			AND ReactionAct.liActFromPersonId IS NOT NULL
			AND ReactionAct.dActRemoved IS NULL
			AND ReactionAct.liActHdTicketId = @ticketId
		GROUP BY
			ReactionAct.liActHdTicketId), 0),
	[NextActionDeadline] = (CASE
			WHEN (SLA.nSlaReactionHours IS NOT NULL) AND (tHdTicket.HasReaction = 0) AND (dHdTicketResolved IS NULL) THEN FirstReactionDeadline
			WHEN (dHdTicketDeadline IS NOT NULL) AND (dHdTicketResolved IS NULL) THEN dHdTicketDeadline
			ELSE NULL END),
	[ShiftedDeadline] = (CASE
			WHEN v.SlaPause=1 THEN dbo.fDeadline(SLA.liOpeningHoursId, tHdTicket.dHdTicket, SLA.nSlaDeadlineHours + isnull(tHdTicket.WaitedForUserHours, 0)) END),
	[WaitingActId] = (
		SELECT TOP 1 iActId
		FROM tAct M
		WHERE
			liActHdTicketId = @ticketId
			AND bWaitingForUser = 1
			AND NOT EXISTS (
				SELECT 1
				FROM tAct S
				WHERE
					S.liActHdTicketId = @ticketId
					AND bWaitingForUser = 0
					AND (S.dAct > M.dAct OR S.dAct = M.dAct AND S.iActId > M.iActId)
					AND S.dActRemoved IS NULL
			)
			AND M.dActRemoved IS NULL
		ORDER BY dAct DESC, iActId DESC
	)
	FROM tHdTicket
		INNER JOIN tSla SLA ON SLA.iSlaId = tHdTicket.liHdTicketSlaId
		INNER JOIN vHdTicket v ON v.liHdTicketId = tHdTicket.iHdTicketId
	WHERE iHdTicketId = @ticketId
END;

go

ALTER TRIGGER [dbo].[T_tAct_Insert] ON [dbo].[tAct]
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @iActId int
	SELECT @iActId = iActId FROM inserted
	IF EXISTS(select top 1 1 from inserted where dRecordCreated is null)
		UPDATE tAct SET dRecordCreated = GETUTCDATE() WHERE iActId = @iActId AND dRecordCreated IS NULL
END;

go

ALTER TABLE [dbo].[tAct] disable trigger TR_tAct_UpdatePersonTicketDisplay;

go

create trigger [dbo].[TR_tAct_Insert_UpdatePersonTicketDisplay] on [dbo].[tAct] after insert as
begin
	set nocount on
	declare @t table (id int primary key, done bit default 0)
	declare @ticketId int = 0

	insert @t (id)
	select i.liActHdTicketId
	from inserted i
		join tHdTicket t on t.iHdTicketId=i.liActHdTicketId
		left join tPerson p on p.iPersonId=i.liActFromPersonId and p.bPersonSystem=1
	where i.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)				or (i.liActKindId=1 and i.liActFromPersonId is not null and p.iPersonId is null)				or (i.liActKindId=9 and i.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and i.mActNotice is not null) 			or t.liHdTicketStartingActId is null	
	if not exists(select 1 from @t)
		return

			while exists(select 1 from @t where done=0)
	begin
		select top 1 @ticketId=id from @t where done=0

					update ptd
		set ptd.EventsNotDisplayed=1
		from PersonTicketDisplay ptd
			join inserted i on i.liActHdTicketId=ptd.TicketId
			join vHdTicketPersonRead vpr on i.liActHdTicketId=vpr.liHdTicketId and vpr.liPersonId=ptd.PersonId
		where ptd.EventsNotDisplayed=0
			and ptd.PersonId!=i.liActFromPersonId
			and vpr.OnlyRequester=0
			and vpr.liHdTicketId=@ticketId

					update ptd
		set ptd.EventsNotDisplayed=1
		from PersonTicketDisplay ptd
			join inserted i on i.liActHdTicketId=ptd.TicketId
			join vHdTicketPersonRead vpr on i.liActHdTicketId=vpr.liHdTicketId and vpr.liPersonId=ptd.PersonId
			join tActHd ahd on ahd.liActHdActId=i.iActId
		where ptd.EventsNotDisplayed=0
			and ptd.PersonId!=i.liActFromPersonId
			and vpr.OnlyRequester=1
			and vpr.liHdTicketId=@ticketId
			and (i.liActToPersonId=ptd.PersonId or ahd.bActHdUserRead=1)

		update @t set done=1 where id=@ticketId
	end
end;

go

create trigger [dbo].[TR_tAct_Update_UpdatePersonTicketDisplay] on [dbo].[tAct] after update as
begin
	set nocount on

	declare @t table (id int primary key, done bit default 0)
	declare @ticketId int = 0


	insert @t (id)
		select i.liActHdTicketId
		from inserted i 
			join deleted d on i.iActId=d.iActId and i.dActRemoved is not null and d.dActRemoved is null
			join tHdTicket t on t.iHdTicketId=i.liActHdTicketId
			left join tPerson p on p.iPersonId=i.liActFromPersonId and p.bPersonSystem=1
			left join (select liDocumentActId from tDocument group by liDocumentActId) do on do.liDocumentActId=i.iActId
		where i.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)				or (i.liActKindId=1 and i.liActFromPersonId is not null and p.iPersonId is null)				or (i.liActKindId=9 and i.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and (i.mActNotice is not null or do.liDocumentActId  is not null)) 			or t.liHdTicketStartingActId=i.iActId			
		if not exists(select 1 from @t)
			return

		while exists(select 1 from @t where done=0)
		begin
			select top 1 @ticketId=id from @t where done=0

			update ptd set ptd.EventsNotDisplayed=0
			from PersonTicketDisplay ptd
				join (
										select tpr.liPersonId
					from inserted i
						join deleted d on d.iActId=i.iActId and i.dActRemoved is not null and d.dActRemoved is null
						join tAct a on a.liActHdTicketId=i.liActHdTicketId
						join tHdTicket t on t.iHdTicketId=i.liActHdTicketId
						join vHdTicketPersonRead tpr on tpr.liHdTicketId=i.liActHdTicketId
						left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.ActId=a.iActId
						left join tPerson p on p.iPersonId=a.liActFromPersonId and p.bPersonSystem=1
						left join tActHd ahd on ahd.liActHdActId=a.iActId
						left join (select distinct liDocumentActId from tDocument) doc on doc.liDocumentActId=a.iActId
					where i.iActId!=a.iActId
							and a.liActFromPersonId!=tpr.liPersonId							and (a.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)								or (a.liActKindId=1 and a.liActFromPersonId is not null and p.iPersonId is null)								or (a.liActKindId=9 and a.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and (a.mActNotice is not null or doc.liDocumentActId is not null)) 							or t.liHdTicketStartingActId=a.iActId							)
						and (tpr.OnlyRequester=0 or (tpr.OnlyRequester=1 and (a.liActFromPersonId=t.liHdTicketUserPersonId or a.liActToPersonId=t.liHdTicketUserPersonId or isnull(ahd.bActHdUserRead,0)=1)))
						and tpr.liHdTicketId=@ticketId
					group by tpr.liPersonId
					having min(isnull(ped.Id,0))>0
				) act on act.liPersonId=ptd.PersonId
				join (
										select
						tpr.liPersonId
					from inserted i
						join deleted d on d.iActId=i.iActId and i.dActRemoved is not null and d.dActRemoved is null
						join tHdTicketApproval ta on ta.liHdTicketApprovalHdTicketId=i.liActHdTicketId
						join vHdTicketPersonRead tpr on tpr.liHdTicketId=i.liActHdTicketId and tpr.OnlyRequester=0
						left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.TicketApprovalId=ta.iHdTicketApprovalId
					where ta.liHdTicketApprovalStartedPersonId!=tpr.liPersonId
						and tpr.liHdTicketId=@ticketId
					group by tpr.liPersonId
					having min(isnull(ped.Id,0))>0
				) appr on appr.liPersonId=ptd.PersonId
				join (
										select
						tpr.liPersonId
					from inserted i
						join deleted d on d.iActId=i.iActId and i.dActRemoved is not null and d.dActRemoved is null
						join tHdTicketApproval ta on ta.liHdTicketApprovalHdTicketId=i.liActHdTicketId
						join tHdTicketApprovalItem tai on tai.liHdTicketApprovalItemHdTicketApprovalId=ta.iHdTicketApprovalId
						join vHdTicketPersonRead tpr on tpr.liHdTicketId=i.liActHdTicketId and tpr.OnlyRequester=0
						left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.TicketApprovalItemId=tai.iHdTicketApprovalItemId
					where tai.liHdTicketApprovalItemPersonId!=tpr.liPersonId
						and tpr.liHdTicketId=@ticketId
					group by tpr.liPersonId
					having min(isnull(ped.Id,0))>0
				) apprit on apprit.liPersonId=ptd.PersonId
			where ptd.EventsNotDisplayed=1					and ptd.TicketId=@ticketId

			update @t set done=1 where id=@ticketId
		end
		return
end;

go

create trigger [dbo].[TR_tAct_Delete_UpdatePersonTicketDisplay] on [dbo].[tAct] after delete as
begin
	set nocount on

	declare @t table (id int primary key, done bit default 0)
	declare @ticketId int = 0


	insert @t (id)
		select d.liActHdTicketId
		from deleted d 
			join tHdTicket t on t.iHdTicketId=d.liActHdTicketId
			left join tPerson p on p.iPersonId=d.liActFromPersonId and p.bPersonSystem=1
			left join (select liDocumentActId from tDocument group by liDocumentActId) do on do.liDocumentActId=d.iActId
		where d.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)				
			or (d.liActKindId=1 and d.liActFromPersonId is not null and p.iPersonId is null)
			or (d.liActKindId=9 and d.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and (d.mActNotice is not null or do.liDocumentActId  is not null))
			or t.liHdTicketStartingActId=d.iActId			
		group by d.liActHdTicketId

	if not exists(select 1 from @t)
		return

	while exists(select 1 from @t where done=0)
	begin
		select top 1 @ticketId=id from @t where done=0

		update ptd set ptd.EventsNotDisplayed=0
		from PersonTicketDisplay ptd
			join (
									select tpr.liPersonId
				from deleted d
					join tAct a on a.liActHdTicketId=d.liActHdTicketId
					join tHdTicket t on t.iHdTicketId=d.liActHdTicketId
					join vHdTicketPersonRead tpr on tpr.liHdTicketId=d.liActHdTicketId
					left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.ActId=a.iActId
					left join tPerson p on p.iPersonId=a.liActFromPersonId and p.bPersonSystem=1
					left join tActHd ahd on ahd.liActHdActId=a.iActId
					left join (select distinct liDocumentActId from tDocument) doc on doc.liDocumentActId=a.iActId
				where d.iActId!=a.iActId
						and a.liActFromPersonId!=tpr.liPersonId							and (a.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)								or (a.liActKindId=1 and a.liActFromPersonId is not null and p.iPersonId is null)								or (a.liActKindId=9 and a.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and (a.mActNotice is not null or doc.liDocumentActId is not null)) 							or t.liHdTicketStartingActId=a.iActId							)
					and (tpr.OnlyRequester=0 or (tpr.OnlyRequester=1 and (a.liActFromPersonId=t.liHdTicketUserPersonId or a.liActToPersonId=t.liHdTicketUserPersonId or isnull(ahd.bActHdUserRead,0)=1)))
					and tpr.liHdTicketId=@ticketId
				group by tpr.liPersonId
				having min(isnull(ped.Id,0))>0
			) act on act.liPersonId=ptd.PersonId
			join (
									select
					tpr.liPersonId
				from deleted d
					join tHdTicketApproval ta on ta.liHdTicketApprovalHdTicketId=d.liActHdTicketId
					join vHdTicketPersonRead tpr on tpr.liHdTicketId=d.liActHdTicketId and tpr.OnlyRequester=0
					left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.TicketApprovalId=ta.iHdTicketApprovalId
				where ta.liHdTicketApprovalStartedPersonId!=tpr.liPersonId
					and tpr.liHdTicketId=@ticketId
				group by tpr.liPersonId
				having min(isnull(ped.Id,0))>0
			) appr on appr.liPersonId=ptd.PersonId
			join (
									select
					tpr.liPersonId
				from deleted d
					join tHdTicketApproval ta on ta.liHdTicketApprovalHdTicketId=d.liActHdTicketId
					join tHdTicketApprovalItem tai on tai.liHdTicketApprovalItemHdTicketApprovalId=ta.iHdTicketApprovalId
					join vHdTicketPersonRead tpr on tpr.liHdTicketId=d.liActHdTicketId and tpr.OnlyRequester=0
					left join PersonEventDisplay ped on ped.PersonId=tpr.liPersonId and ped.TicketApprovalItemId=tai.iHdTicketApprovalItemId
				where tai.liHdTicketApprovalItemPersonId!=tpr.liPersonId
					and tpr.liHdTicketId=@ticketId
				group by tpr.liPersonId
				having min(isnull(ped.Id,0))>0
			) apprit on apprit.liPersonId=ptd.PersonId
		where ptd.EventsNotDisplayed=1					and ptd.TicketId=@ticketId

		update @t set done=1 where id=@ticketId
	end
	return
end;

ALTER TABLE [dbo].[tAct] ENABLE TRIGGER [TR_tAct_Insert_UpdatePersonTicketDisplay];

ALTER TABLE [dbo].[tAct] ENABLE TRIGGER [TR_tAct_Update_UpdatePersonTicketDisplay];

ALTER TABLE [dbo].[tAct] ENABLE TRIGGER [TR_tAct_Delete_UpdatePersonTicketDisplay];

go

drop index IX_PersonTicketDisplay_TicketId_PersonId on PersonTicketDisplay;

go

alter table PersonTicketDisplay add Id int Identity(1,1);

go

create nonclustered index IX_PersonTicketDisplay_TicketId_PersonId on PersonTicketDisplay (TicketId, PersonId);

go

create clustered index IX_PersonTicketDisplay_Id on PersonTicketDisplay (Id);