ALTER PROCEDURE [dbo].[spNotifyMacros] @iHdTicketId INT AS
BEGIN
SET NOCOUNT ON

insert into #T (iHdTicketId, sNotifyMacrosSubjectPrefix, mNotifyMacrosStyle, mNotifyMacrosSeePortal, mNotifyMacrosSignature, mNotifyMacrosDetails)
select
iHdTicketId,
sHdTicketMessageTag + ' ' +
	case when len(sHdTicket) > 40 then substring(sHdTicket, 0, 37) + '...' else sHdTicket end as sNotifyMacrosSubjectPrefix,
	'body
	{
		font-family: Verdana, Tahoma, Arial;
		font-size: 10pt;
	}
	td
	{
		font-family: Verdana, Tahoma, Arial;
		font-size: 8pt;
		padding-top: 1px;
		padding-bottom: 1px;
		vertical-align: top;
	}' as mNotifyMacrosStyle,
	case when len(tHd.WebAppURL)>0 then '<p>' + N'For more information please refer also to ' + 
	'<a href="' + WebAppURL + '/Ticket/' + convert(nvarchar(20),iHdTicketId) + '">
	' + tHd.WebAppURL + '/Ticket/' + convert(nvarchar(20),iHdTicketId) + '</a>.</p>' else '' end as mNotifyMacrosSeePortal,
	'<p>'+dbo.fHtmlString(hds.sHdSection) +'<br>'+
	case when len(hds.sHdSectionPhone)>0 then N'phone: ' + dbo.fHtmlString(hds.sHdSectionPhone) else '' end + '</p>' as mNotifyMacrosSignature,
'<table border=0 cellspacing=0 cellpadding=0>
	<tr>
		<td>' + N'Request number:' +'</td>
		<td>&nbsp;</td>
		<td>' + dbo.fHtmlString(sHdTicketMessageTag) + '</td>
	</tr>
	<tr>
		<td>' + N'Requester:' + '</td>
		<td>&nbsp;</td>
		<td>' + dbo.fHtmlString(sHdTicketUser) +
			case when len(sHdTicketUserCompany) > 0 then '/' + dbo.fHtmlString(sHdTicketUserCompany) else '' end +
			case when len(sHdTicketUserEmail) > 0 then '/' + dbo.fHtmlString(sHdTicketUserEmail) else '' end +
			case when len(sHdTicketUserPhone) > 0 then '/' + dbo.fHtmlString(sHdTicketUserPhone) else '' end +
			case when len(sHdTicketUserMobile) > 0 then '/' + dbo.fHtmlString(sHdTicketUserMobile) else '' end +
			case when len(convert(nvarchar(200),mHdTicketUserContact)) > 0 then '/' + dbo.fHtmlString(convert(nvarchar(200),mHdTicketUserContact)) else '' end + '
	</tr>
	<tr>
		<td>' + N'Service:' + '</td>
		<td>&nbsp;</td>
		<td>' + dbo.fHtmlString(hds.sHdSection) + '</td>
	</tr>
	<tr>
		<td>' + N'Request name:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when sHdTicket is null then '' else dbo.fHtmlString(sHdTicket) end +'</td>
	</tr>
	<tr>
		<td>' + N'Request text:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when len(convert(nvarchar(4000),sa.mActNotice))>1024 then dbo.fHtmlString(convert(nvarchar(1020),sa.mActNotice)+'...') else dbo.fHtmlString(convert(nvarchar(4000),case when sa.mActNotice is null then '' else sa.mActNotice end)) end + '</td>
	</tr>
	<tr>
		<td>' + N'Submitted:' + '</td>
		<td>&nbsp;</td>
		<td>' + case when dHdTicket is null then '-' else dbo.fHtmlString(convert(nvarchar(255),dHdTicket,104) + ' ' + substring(convert(nvarchar(255),dHdTicket,108),1,5)) end + '</td>
	</tr>
	<tr>
		<td>' + N'Due date:' + '</td>
		<td>&nbsp;</td>
		<td>' + case when dHdTicketDeadline is null then '-' else dbo.fHtmlString(convert(nvarchar(255),dHdTicketDeadline,104) + ' ' + substring(convert(nvarchar(255),dHdTicketDeadline,108),1,5)) end +'</td>
	</tr>
	<tr>
		<td>' + N'Solver:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when psolver.sPerson is null then '' else dbo.fHtmlString(psolver.sPerson) end +'</td>
	</tr>
	<tr>
		<td>' + N'Device number:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when sHdTicketDeviceCode is null then '-' else dbo.fHtmlString(sHdTicketDeviceCode) end +'</td>
	</tr>
	<tr>
		<td>' + N'Building:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when b.sBuilding is null then '-' else dbo.fHtmlString(b.sBuilding) end +'</td>
	</tr>
	<tr>
		<td>' + N'Room:' + '</td>
		<td>&nbsp;</td>
		<td>'+case when r.sRoom is null then '-' else dbo.fHtmlString(r.sRoom) end+'</td>
	</tr>
</table>
' as mNotifyMacrosDetails
from
((((((tHdTicket as hdt
left join tHdSection as hds on hds.iHdSectionId=hdt.liHdTicketHdSectionId)
left join tPerson as puser on puser.iPersonId=hdt.liHdTicketUserPersonId)
left join tPerson as psolver on psolver.iPersonId=hdt.liHdTicketSolverPersonId)
left join tAct as sa on sa.iActId=hdt.liHdTicketStartingActId)
left join tRoom as r on r.iRoomId=hdt.liHdTicketRoomId)
left join tBuilding as b on b.iBuildingId=r.liRoomBuildingId)
cross join (SELECT TOP 1 sPropertyValue AS WebAppURL FROM tProperty WHERE sProperty = 'WebAppURL') tHd
where hdt.iHdTicketId=@iHdTicketId
END;