create procedure [dbo].[MssWebGetScheduledMessages]
@inContactId int,
@inSysUserId int,
@inBefore DateTimeOffSet
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
        SakariScheduledMessage.ToBeSentAt <  @inBefore
    order by SakariScheduledMessage.ToBeSentAt desc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetScheduledMessages] TO [MssExec]
GO