ALTER trigger [dbo].[TR_tHdTicket_CalculateTicketStateId] on [dbo].[tHdTicket] after insert,update as
begin
	set nocount on
		update t set TicketStateId=ISNULL(ts.TicketStateId, d.TicketStateId)
	from
		inserted i
		join tHdTicket t on t.iHdTicketId=i.iHdTicketId
		join (
			select
				t.iHdTicketId,
				coalesce(ts.id,tsr.id,ts2.id,ts2r.id,ts3.id) TicketStateId
			from tHdTicket t
				join tHdSection s on s.iHdSectionId=t.liHdTicketHdSectionId
				join TicketType tt on tt.id=s.TicketTypeId
				left join TicketState ts  on ts.TicketState=t.sHdTicketStateNotice  and ts.TicketTypeId=s.TicketTypeId  and ts.Removed is null
				left join TicketState tsr on tsr.TicketState=t.sHdTicketStateNotice and tsr.TicketTypeId=s.TicketTypeId and tsr.Removed is not null 
				left join TicketState ts2 on ts2.TicketState=t.sHdTicketStateNotice and	ts2.TicketStateBehaviorId in (4,6,7,9) and ts2.Removed is null
				left join TicketState ts2r on ts2r.TicketState=t.sHdTicketStateNotice and ts2r.TicketStateBehaviorId in (4,6,7,9) and ts2r.Removed is not null
				left join (
					select
						ts.TicketState,
						min(ts.id) id
					from TicketState ts
					group by TicketState
				) ts3 on ts3.TicketState=t.sHdTicketStateNotice
				
		) ts on ts.iHdTicketId=t.iHdTicketId
		left join deleted d on d.iHdTicketId=i.iHdTicketId
	where i.sHdTicketStateNotice!=isnull(d.sHdTicketStateNotice,'')

		if( @@rowcount = 0 )
		update t set t.TicketStateId=ISNULL(tsi.id, d.TicketStateId)
		from inserted i
			join tHdTicket t on t.iHdTicketId=i.iHdTicketId
			join deleted d on d.iHdTicketId=i.iHdTicketId
			join tHdSection si on si.iHdSectionId=i.liHdTicketHdSectionId
			join tHdSection sd on sd.iHdSectionId=d.liHdTicketHdSectionId
			join TicketState tsd on tsd.id=d.TicketStateId
			left join TicketState tsi on tsi.TicketState=i.sHdTicketStateNotice and (tsi.TicketTypeId=si.TicketTypeId or tsi.TicketTypeId is null)
		where d.liHdTicketHdSectionId!=i.liHdTicketHdSectionId
			and si.TicketTypeId!=sd.TicketTypeId
			and isnull(i.TicketStateId,0)!=isnull(tsi.id,0)
			and tsi.Removed is null
	set nocount off
end

GO

ALTER FUNCTION [dbo].[ftTicketStateListIncResolved]
(
	@TicketId INT
)
RETURNS @list TABLE
(
	id INT NOT NULL,
	TicketState NVARCHAR(64) NOT NULL,
	[Order] INT NOT NULL,
	TicketStateBehaviorId INT NOT NULL
)
AS
BEGIN
	DECLARE @TicketTypeId INT
	SELECT @TicketTypeId = 
	(
		SELECT sec.TicketTypeId
		FROM tHdSection sec 
		JOIN tHdTicket t ON sec.iHdSectionId = t.liHdTicketHdSectionId
		WHERE t.iHdTicketId = @TicketId
	)
	
	DECLARE @CurrentState NVARCHAR(64), @CurrentId INT, @Resolved BIT, @Closed BIT
	SELECT @CurrentState = 
	(
			SELECT sHdTicketStateNotice FROM tHdTicket WHERE iHdTicketId = @TicketId
	)
	SET @CurrentId = 
	(
		SELECT ISNULL(
		(SELECT TicketStateId FROM tHdTicket WHERE iHdTicketId = @TicketId), 
		(SELECT TOP 1 id
			FROM TicketState
			WHERE TicketState = @CurrentState
				AND (TicketTypeId = @TicketTypeId OR TicketTypeId IS NULL)
			ORDER BY TicketTypeId DESC)
		)
	)
	SET @Resolved = 
	(
		CASE 
			WHEN (SELECT dHdTicketResolved FROM tHdTicket WHERE iHdTicketId = @TicketId) IS NULL
				THEN 0
				ELSE 1
		END
	)
	SET @Closed = 
	(
		CASE 
			WHEN (SELECT ClosedDate FROM tHdTicket WHERE iHdTicketId = @TicketId) IS NULL
				THEN 0
				ELSE 1
		END
	)

		IF @CurrentId IS NOT NULL AND (SELECT CASE WHEN IsApprovalState = 1 THEN 1 ELSE 0 END FROM TicketState WHERE id = @CurrentId) = 1 BEGIN
			INSERT INTO @list (id, TicketState, [Order], TicketStateBehaviorId)
			select id, TicketState, [Order], TicketStateBehaviorId
			from TicketState
			where id in (select OnApprovalStateId from TicketState where id = @CurrentId
				union
				select OnRejectedStateId from TicketState where id = @CurrentId
				union
				select OnCanceledStateId from TicketState where id = @CurrentId
				union
				select OnExpiredStateId from TicketState where id = @CurrentId)
			and TicketStateBehaviorId not in (4,9,6,7) and Removed is null
		END
		ELSE BEGIN  
			INSERT INTO @list (id, TicketState, [Order], TicketStateBehaviorId)
			SELECT id, TicketState, [Order], TicketStateBehaviorId
			FROM TicketState 
			WHERE TicketTypeId = @TicketTypeId
				AND TicketStateBehaviorId IN (1, 2, 3, 10, 11)
				AND TicketState != @CurrentState 
				AND Removed is null

			IF @CurrentId IS NOT NULL AND (SELECT TransitToAllStates FROM TicketState WHERE id = @CurrentId) = 0 
				AND (SELECT TicketTypeId FROM TicketState WHERE id = @CurrentId) = @TicketTypeId
					DELETE FROM @list WHERE id NOT IN (SELECT EndTicketStateId FROM TicketStateRelation WHERE BeginTicketStateId = @CurrentId)
		END		

		IF @CurrentId IS NOT NULL BEGIN
		IF @CurrentId NOT IN (SELECT id FROM @list) BEGIN
			INSERT INTO @list (id, TicketState, [Order], TicketStateBehaviorId)
				SELECT id, TicketState, [Order], TicketStateBehaviorId FROM TicketState WHERE id = @CurrentId
		END
	END
	ELSE BEGIN
		INSERT INTO @list (id, TicketState, [Order], TicketStateBehaviorId)
		VALUES(0, @CurrentState, 0, 10)
	END

	IF @Resolved = 0 BEGIN
		DELETE FROM @list WHERE TicketStateBehaviorId = 11
		END

	IF @Closed = 1 BEGIN
		DELETE FROM @list WHERE TicketStateBehaviorId <> 11
		END

	RETURN
END