[dbo].[MssWebGetConversationListByUser]
CREATE procedure [dbo].[MssWebGetConversationListByUser]
@inSysUserId int
as
begin
;with ContactAndUpdateCount as
(
select
ContactID = dbo.SakariContactSysuserMap.SakariContactFID,
UpdateCount = IncomingMessageUpdateTally.Count + OutgoingMessageUpdateTally.Count,
ScheduledMessageCount = ScheduledMessageTally.Count
from SakariContactSysuserMap
cross apply
(
select count(*) Count
from SakariIncomingMessage
where SakariIncomingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID and
SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt
) as IncomingMessageUpdateTally
cross apply
(
select count(*) Count
from dbo.SakariOutgoingMessage
where
SakariOutgoingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID and
SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and
( SakariOutgoingMessage.ErrorMessage is not null or SakariOutgoingMessage.SentBy <> isnull( @inSysUserID, -1 ) )
) OutgoingMessageUpdateTally
cross apply
(
select count(*) Count
from SakariScheduledMessage
left outer join SakariOutgoingMessage on SakariScheduledMessage.SakariScheduledMessageID = SakariOutgoingMessage.SakariScheduledMessageFID
where
SakariOutgoingMessage.SakariOutgoingMessageID is null and
SakariScheduledMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID
) ScheduledMessageTally
where SakariContactSysuserMap.SysuserFID = @inSysUserID
)
select
OrderId = Orders.PriKey,
OrderNumber = coalesce(
Orders.OrderNo,
Orders.ArchivedOrderNo + ' [A]' ) ,
AccountId = SakariAccount.AccountID,
CustomerName = dbo.FormatFirstNameLastName(Orders.LastName, Orders.FirstName ),
ContactName = SakariContact.ContactName,
ContactPhone = SakariContact.PhoneNumber,
contactPriKey = SakariContact.SakariContactID,
UpdateCount = Data.UpdateCount ,
ScheduledMessageCount = Data.ScheduledMessageCount,
LastUpdatedAt = SakariContact.LastUpdatedAt
from ContactAndUpdateCount Data
inner join SakariContact on Data.ContactID = SakariContact.SakariContactID
left outer join SakariContactOrderMap on Data.ContactID = SakariContactOrderMap.SakariContactFID
inner join SakariAccount on SakariContact.SakariAccountFID = SakariAccount.SakariAccountID
left outer join Orders on SakariContactOrderMap.OrderFID = Orders.PriKey
order by LastUpdatedAt desc
end
GO
GRANT EXECUTE ON [dbo].[MssWebGetConversationListByUser] TO [MssExec]
GO