public List <ThreadListRow> ListThreadsForBase(int messagebase)
{
List <ThreadListRow> threads = new List <ThreadListRow>();
try
{
BBSDataDataContext bbs = GetDataContext();
string query = "SELECT MT.MessageThreadId AS MessageThreadId, "
+ " IM.Subject AS Subject, "
+ " CASE WHEN LM.Posted IS NULL THEN"
+ " CASE WHEN IM.Posted IS NULL THEN"
+ " '1970-01-01 00:00:00' "
+ " ELSE IM.Posted END "
+ " ELSE LM.Posted END AS LastActivity, "
+ " U.Username AS Poster, "
+ " CASE WHEN U.UserId IS NULL THEN -1 ELSE U.UserId END As PosterId, "
+ " COUNT(RC.MessageHeaderId) AS Replies FROM MessageThreads "
+ " MT LEFT JOIN MessageHeaders IM ON (MT.InitialMessageHeaderId = IM.MessageHeaderId) "
+ " LEFT JOIN MessageHeaders LM ON (MT.MessageThreadId = LM.MessageThreadId) "
+ " LEFT JOIN MessageHeaders RC ON (MT.MessageThreadId = RC.MessageThreadId AND MT.InitialMessageHeaderId != RC.MessageThreadId) "
+ " LEFT OUTER JOIN MessageHeaders LD ON (LD.MessageThreadId = MT.MessageThreadId AND (LM.Posted < LD.Posted OR LM.Posted = LD.Posted AND LM.MessageHeaderId < LD.MessageHeaderId)) "
+ " LEFT JOIN Users U ON (LM.UserId = U.UserId) "
+ " WHERE LD.MessageHeaderId IS NULL AND MT.MessageBaseId = " + messagebase.ToString()
+ " GROUP BY MT.MessageThreadId,IM.Subject,IM.Posted,LM.Posted,U.Username, U.Userid"
;
threads = bbs.ExecuteQuery <ThreadListRow>(query).ToList <ThreadListRow>();
//int i = 1;
//foreach (ThreadListRow threadinfo in threads)
//{
// IdAndKeys idak = new IdAndKeys();
// idak.Id = threadinfo.MessageThreadId;
// idak.Keys.Add("subject", threadinfo.Subject);
// idak.Keys.Add("activity", threadinfo.LastActivity.ToString("yy-MM-dd hh:mm"));
// idak.Keys.Add("poster", threadinfo.Poster);
// idak.Keys.Add("posterid", threadinfo.PosterId.ToString());
// idak.Keys.Add("listid", i.ToString());
// i++;
// result.Add(idak);
//}
}
catch (Exception e)
{
LoggingAPI.LogEntry("Exception in DataInterface.ListThreadsForBase(" + messagebase.ToString() + "): " + e.ToString());
}
return(threads);
}
public List <int> MessageIdsInThread(int threadid)
{
List <int> result = new List <int>();
try
{
BBSDataDataContext bbs = GetDataContext();
String Query = "SELECT MessageHeaderId FROM MessageHeaders WHERE MessageThreadId = " + threadid.ToString() + "ORDER BY Posted ";
result = bbs.ExecuteQuery <int>(Query).ToList <int>();
}
catch (Exception e)
{
LoggingAPI.LogEntry("Exception in DataInterface.MessageIdsInThread(" + threadid.ToString() + "):" + e.ToString());
}
return(result);
}