ALTER trigger TR_tAct_UpdatePersonTicketDisplay on dbo.tAct after insert,update,delete as
begin
	declare @op varchar(3)=null

	--operace
	if exists(select 1 from inserted i join deleted d on d.iActId=i.iActId)
		set @op='upd'
	else if exists(select 1 from inserted i)
		set @op='ins'
	else
		set @op='del'

	if(@op is null)
		return

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

	if( @op='ins' )
	begin
		--ma se nejaka nova udalost zapocitat jako neprectena?
		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)	--neni to Process a E-mail
			or (i.liActKindId=1 and i.liActFromPersonId is not null and p.iPersonId is null)	--mejl, neni od system
			or (i.liActKindId=9 and i.ActOperationId in (2, 3, 4, 5, 6, 7, 9, 10, 13) and i.mActNotice is not null) --Process, je v nem nejaky text
			or t.liHdTicketStartingActId is null	--vytvari se prvni udalost pozadavku

		if not exists(select 1 from @t)
			return

		--kvuli rychlosti je tu while s podminkou na @ticketId - vetsina insertu obsahuje jen jeden radek a jeden pozadavek, to bude rychle
		while exists(select 1 from @t where done=0)
		begin
			select top 1 @ticketId=id from @t where done=0

			--osoby, ktere maji prava cist pozadavek a vsechny udalosti
			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

			--jen zadatel, vidi svoje udalosti (From) a udalosti kde je (To) a pripadne dalsi, ktere muze cist (ukazat zadateli)
			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
		return
	end


	if( @op = 'upd' )
	begin
		--prepocitat jen pozadavky, u kterych se odstranuji zaznamy (dActRemoved) a pokud to je udalost, ktera jde "precist"
		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)	--neni to Process a E-mail
			or (i.liActKindId=1 and i.liActFromPersonId is not null and p.iPersonId is null)	--mejl, neni od system
			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)) --Process, je v nem nejaky text nebo priloha
			or t.liHdTicketStartingActId=i.iActId	--prvni udalost pozadavku
		
		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 (
					--osoby, ktere maji vsechny ostatni udalosti prectene
					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	--neni udalost od uzivatele
						and (a.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)	--neni to Process a E-mail
							or (a.liActKindId=1 and a.liActFromPersonId is not null and p.iPersonId is null)	--mejl, neni od system
							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)) --Process, je v nem nejaky text nebo priloha
							or t.liHdTicketStartingActId=a.iActId	--prvni udalost pozadavku
						)
						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 (
					--osoby, ktere maji vsechny zahajeni schvalovani prectene
					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 (
					--osoby, ktere maji vsechny zaznam schvalovacich kroku prectene
					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	--pozadavek byl neprecteny a odstranuje se posledni neprectena udalost
				and ptd.TicketId=@ticketId

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


	if( @op = 'del' )
	begin
		--prepocitat jen pozadavky, u kterych se trvale odstranuji zaznamy a pokud to je udalost, ktera jde "precist"
		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)	--neni to Process a E-mail
			or (d.liActKindId=1 and d.liActFromPersonId is not null and p.iPersonId is null)	--mejl, neni od system
			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)) --Process, je v nem nejaky text nebo priloha
			or t.liHdTicketStartingActId=d.iActId	--prvni udalost pozadavku
		
		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 (
					--osoby, ktere maji vsechny ostatni udalosti prectene
					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	--neni udalost od uzivatele
						and (a.liActKindId in (2, 3, 4, 5, 6, 7, 8, 10)	--neni to Process a E-mail
							or (a.liActKindId=1 and a.liActFromPersonId is not null and p.iPersonId is null)	--mejl, neni od system
							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)) --Process, je v nem nejaky text nebo priloha
							or t.liHdTicketStartingActId=a.iActId	--prvni udalost pozadavku
						)
						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 (
					--osoby, ktere maji vsechny zahajeni schvalovani prectene
					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 (
					--osoby, ktere maji vsechny zaznam schvalovacich kroku prectene
					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	--pozadavek byl neprecteny a odstranuje se posledni neprectena udalost
				and ptd.TicketId=@ticketId

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