create procedure [dbo].[MssWebGetMessages]
@inContactId int,
@inSysUserId int
As
begin
select     SakariMessages.Prikey ,
        SakariMessages.IsInbound ,
        SakariMessages.TextMessage,
        SakariMessages.SentAt,
        SakariMessages.SentBy,
        SakariMessages.ReceivedFrom,
        SakariMessages.MessageStatus,
        SakariMessages.WasRead,
        SakariMessages.ErrorMessage,
        SakariMessages.ErrorCode
        from
        (select
                PriKey = SakariIncomingMessage.SakariIncomingMessageID,
                IsInbound = 1,
                TextMessage = SakariIncomingMessage.TextMessage,
                SentAt = SakariIncomingMessage.CreatedAt,
                SentBy = null,
                ReceivedFrom = SakariContact.ContactName,
                MessageStatus = null,
                WasRead =
                    convert(bit,
                        case
                            when SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt then 0
                            else 1
                        end ),
                ErrorMessage = null,
                ErrorCode = null
            from SakariContactSysuserMap
            inner join SakariIncomingMessage on SakariIncomingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID
            left outer join Sysuser on SakariContactSysuserMap.SysuserFID = Sysuser.SysUserID
            left outer join SakariContact on SakariContact.SakariContactID = SakariIncomingMessage.SakariContactFID
            where SakariContactSysuserMap.SakariContactFID = @inContactId and
            SakariContactSysuserMap.SysuserFID = @inSysUserId
            union all
            select
                PriKey = SakariOutgoingMessage.SakariOutgoingMessageID,
                IsInbound = 0,
                TextMessage = SakariOutgoingMessage.TextMessage,
                SentAt = SakariOutgoingMessage.CreatedAt,
                SentBy = dbo.FormatFirstNameLastName(Sysuser.FIRSTNAME, Sysuser.LASTNAME),
                ReceivedFrom = null,
                MessageStatus = SakariMessageStatusName ,
                WasRead =
                    convert(bit,
                        case
                            when SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and isnull(dbo.SakariOutgoingMessage.SentBy, 0) <> @inSysUserId then 0
                            else 1
                        end),
                ErrorMessage = SakariOutgoingMessage.ErrorMessage,
                ErrorCode = SakariOutgoingMessage.ErrorCode
        
            from SakariContactSysuserMap
            inner join SakariOutgoingMessage on  SakariContactSysuserMap.SakariContactFID = SakariOutgoingMessage.SakariContactFID
            left outer join Sysuser on SakariOutgoingMessage.SentBy = Sysuser.SysUserID
            left outer join SakariContact on SakariContact.SakariContactID = SakariOutgoingMessage.SakariContactFID
            inner join SakariMessageStatus on SakariOutgoingMessage.SakariMessageStatusFID = SakariMessageStatus.SakariMessageStatusID
            where
            SakariContactSysuserMap.SakariContactFID = @inContactId and
            SakariContactSysuserMap.SysuserFID = @inSysUserId
            union all
            select
                Prikey = SakariScheduledMessage.SakariScheduledMessageID,
                IsInbound = 0,
                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),
                ReceivedFrom = null,
                MessageStatus = 'Scheduled',
                WasRead =
                    convert(bit,
                        case
                            when SakariScheduledMessage.CreatedAt > SakariContactSysuserMap.LastReadAt then 0
                            else 1
                        end ),
                ErrorMessage = null,
                ErrorCode = null
            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 ) As SakariMessages
End
GO
GRANT EXECUTE ON  [dbo].[MssWebGetMessages] TO [MssExec]
GO