
ALTER FUNCTION [dbo].[ftRelations]
(
	@ticketId int,
	@userId int
)
RETURNS
@rel TABLE 
(
	Relation nvarchar(30),
	iHdTicketId int,
	sHdTicket nvarchar(1024),
	sHdSection nvarchar(255),
	sHdTicketStateNotice nvarchar(255),
	sHdTicketCustomWorkflowUrl nvarchar(1024),
	sHdTicketMessageTag nvarchar(255),
	TrId int,
	dHdTicketResolved datetime,
	ItemOrder int
)
AS
BEGIN
	INSERT INTO @rel
	SELECT Relation,
		vTicketRelation.iHdTicketId,
		sHdTicket,
		sHdSection,
		sHdTicketStateNotice,
		sHdSectionHdTicketCustomWorkflowUrl as sHdTicketCustomWorkflowUrl,
		sHdTicketMessageTag,
		TrId,
		dHdTicketResolved,
		ItemOrder = ROW_NUMBER() OVER(ORDER BY Relation, HDT.iHdTicketId)
	FROM
		(
			SELECT LocalizedBeginName as Relation, EndHdTicketId as iHdTicketId, vTicketRelation.id as TrId 
			FROM vTicketRelation 
			LEFT JOIN vTicketRelationTypeLoc ON TicketRelationTypeId = vTicketRelationTypeLoc.id and vTicketRelationTypeLoc.RelationTypeLocaleId = ISNULL((select iPersonLocaleId from tPerson where iPersonId = @userId), (select iDbLocaleId from tDb))
			WHERE BeginHdTicketId = @ticketId

			UNION

			SELECT LocalizedEndName as Relation, BeginHdTicketId as iHdTicketId, vTicketRelation.id as TrId 
			FROM vTicketRelation 
			LEFT JOIN vTicketRelationTypeLoc ON TicketRelationTypeId = vTicketRelationTypeLoc.id and vTicketRelationTypeLoc.RelationTypeLocaleId = ISNULL((select iPersonLocaleId from tPerson where iPersonId = @userId), (select iDbLocaleId from tDb))
			WHERE EndHdTicketId = @ticketId
		) AS vTicketRelation
		LEFT JOIN tHdTicket HDT ON vTicketRelation.iHdTicketId = HDT.iHdTicketId
		LEFT JOIN tHdSection HDS ON iHdSectionId = liHdTicketHdSectionId
		JOIN vHdTicketPersonRead PR ON PR.liPersonId=@userId AND PR.liHdTicketId=HDT.iHdTicketId
	OPTION (RECOMPILE)

	RETURN
END
;