public List<PrivateMessage> GetPrivateMessages(User user, PrivateMessageBoxType boxType, int pageIndex, out PagerContext pagerContext)
{
var total = PrivateMessageRepository.GetBoxCount(user.UserID, boxType);
var pageSize = SettingsManager.Current.TopicsPerPage;
var startRow = ((pageIndex - 1) * pageSize) + 1;
var totalPages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(total) / Convert.ToDouble(pageSize)));
pagerContext = new PagerContext { PageCount = totalPages, PageIndex = pageIndex, PageSize = pageSize };
return PrivateMessageRepository.GetPrivateMessages(user.UserID, boxType, startRow, pageSize);
}
public int GetBoxCount(int userID, PrivateMessageBoxType boxType)
{
var isArchived = boxType == PrivateMessageBoxType.Archive;
var sql = "SELECT COUNT(*) FROM pf_PrivateMessage P JOIN pf_PrivateMessageUser U ON P.PMID = U.PMID WHERE U.UserID = @UserID AND U.IsArchived = @IsArchived";
var count = 0;
_sqlObjectFactory.GetConnection().Using(connection =>
count = Convert.ToInt32(connection.Command(sql)
.AddParameter("@UserID", userID)
.AddParameter("@IsArchived", isArchived)
.ExecuteScalar()));
return count;
}
public List<PrivateMessage> GetPrivateMessages(int userID, PrivateMessageBoxType boxType, int startRow, int pageSize)
{
var isArchived = boxType == PrivateMessageBoxType.Archive;
const string sql =
@"DECLARE @Counter int
SET @Counter = (@StartRow + @PageSize - 1)
SET ROWCOUNT @Counter;
WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY [LastPostTime] DESC)
AS Row, P.PMID, Subject, LastPostTime, UserNames, U.LastViewDate
FROM pf_PrivateMessage P JOIN pf_PrivateMessageUser U
ON P.PMID = U.PMID WHERE U.UserID = @UserID
AND U.IsArchived = @IsArchived)
SELECT PMID, Subject, LastPostTime, UserNames, LastViewDate
FROM Entries
WHERE Row between
@StartRow and @StartRow + @PageSize - 1
SET ROWCOUNT 0";
var messsages = new List<PrivateMessage>();
_sqlObjectFactory.GetConnection().Using(connection =>
connection.Command(sql)
.AddParameter("@StartRow", startRow)
.AddParameter("@PageSize", pageSize)
.AddParameter("@UserID", userID)
.AddParameter("@IsArchived", isArchived)
.ExecuteReader()
.ReadAll(r => messsages.Add(new PrivateMessage
{
PMID = r.GetInt32(0),
Subject = r.GetString(1),
LastPostTime = r.GetDateTime(2),
UserNames = r.GetString(3),
LastViewDate = r.GetDateTime(4)
})));
return messsages;
}
