public void LoadByUserID(int userID, int organizationID)
{
Chats chats = new Chats(LoginUser);
chats.LoadByUserID(userID, organizationID);
if (chats.IsEmpty)
{
return;
}
StringBuilder builder = new StringBuilder();
foreach (Chat chat in chats)
{
if (builder.Length > 0)
{
builder.Append(",");
}
builder.Append(chat.ChatID.ToString());
}
using (SqlCommand command = new SqlCommand())
{
string text = @"
(
SELECT cp.*, cc.FirstName, cc.LastName, cc.Email, cc.CompanyName,
CASE WHEN DATEDIFF(second, cc.LastPing, GETUTCDATE()) < 15 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsOnline,
ISNULL((SELECT TOP 1 cm.ChatMessageID FROM ChatMessages cm WHERE cm.ChatID= cp.ChatID ORDER BY cm.ChatMessageID DESC), 0) AS 'LastPostedMessageID',
ISNULL((SELECT TOP 1 cp2.LastMessageID FROM ChatParticipants cp2 WHERE cp2.ChatID= cp.ChatID AND cp2.ParticipantType=0 AND cp2.ParticipantID=@UserID), 0) AS 'MyLastMessageID'
FROM ChatParticipants cp
LEFT JOIN ChatClients cc ON cc.ChatClientID = cp.ParticipantID
WHERE cp.ChatID IN ({0})
--AND cp.DateLeft IS NULL
AND cp.ParticipantType = 1
UNION
SELECT cp.*, u.FirstName, u.LastName, u.Email, o.Name,
CASE WHEN DATEDIFF(second, u.LastPing, GETUTCDATE()) < 15 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsOnline,
ISNULL((SELECT TOP 1 cm.ChatMessageID FROM ChatMessages cm WHERE cm.ChatID= cp.ChatID ORDER BY cm.ChatMessageID DESC), 0) AS 'LastPostedMessageID',
ISNULL((SELECT TOP 1 cp2.LastMessageID FROM ChatParticipants cp2 WHERE cp2.ChatID= cp.ChatID AND cp2.ParticipantType=0 AND cp2.ParticipantID=@UserID), 0) AS 'MyLastMessageID'
FROM ChatParticipants cp
LEFT JOIN Users u ON u.UserID = cp.ParticipantID
LEFT JOIN Organizations o ON o.OrganizationID = u.OrganizationID
WHERE NOT (cp.ParticipantID = @UserID AND cp.ParticipantType = 0)
AND cp.ChatID IN ({0})
--AND cp.DateLeft IS NULL
AND cp.ParticipantType = 0
)
ORDER BY ChatID
";
command.CommandText = string.Format(text, builder.ToString());
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@UserID", userID);
Fill(command);
}
}
