[dbo].[MssWebGetScheduledMessages]
CREATE procedure [dbo].[MssWebGetScheduledMessages]
@inContactId int,
@inSysUserId int,
@inDate DateTimeOffSet = null
As
begin
select
Prikey = SakariScheduledMessage.SakariScheduledMessageID,
TextMessage = SakariScheduledMessage.TextMessage +
case when SakariUserSignature.Signature is null then ''''
else char(13) + char(10) + SakariUserSignature.Signature
end,
SentAt = SakariScheduledMessage.ToBeSentAt,
SentBy = dbo.FormatFirstNameLastName(Sysuser.FIRSTNAME, Sysuser.LASTNAME),
WasRead =
convert(bit,
case
when SakariScheduledMessage.CreatedAt > SakariContactSysuserMap.LastReadAt then 0
else 1
end )
from SakariContactSysuserMap
inner join SakariScheduledMessage on SakariContactSysuserMap.SakariContactFID = SakariScheduledMessage.SakariContactFID
left outer join Sysuser on SakariScheduledMessage.SentBy = Sysuser.SysUserID
left outer join SakariOutgoingMessage on SakariScheduledMessage.SakariScheduledMessageID = SakariOutgoingMessage.SakariScheduledMessageFID
left outer join SakariUserSignature on SakariScheduledMessage.SentBy = SakariUserSignature.SysuserFID
where
SakariContactSysuserMap.SakariContactFID = @inContactId and
SakariContactSysuserMap.SysuserFID = @inSysUserId and
SakariOutgoingMessage.SakariOutgoingMessageID is null and
((@inDate is not null and SakariScheduledMessage.ToBeSentAt >= @inDate) or
(@inDate is null))
order by SakariScheduledMessage.ToBeSentAt desc
end
GO
GRANT EXECUTE ON [dbo].[MssWebGetScheduledMessages] TO [MssExec]
GO