public HKVersionsModel GetPublishedNodes(string search = "", int itemsPerPage = 10, int pageNumber = 1)
{
try {
var request = new HKVersionsModel()
{
Search = search,
ItemsPerPage = itemsPerPage,
CurrentPage = pageNumber,
};
var sqlVersions = "SELECT CurDoc.nodeId, CurDoc.text AS NodeName, umbracoUser.userName AS NodeUser, CurDoc.updateDate AS PublishedDate, HistDoc.VersionsCount AS VersionsCount ";
sqlVersions += "FROM cmsDocument AS CurDoc ";
sqlVersions += "INNER JOIN umbracoUser ON CurDoc.documentUser = umbracoUser.id ";
sqlVersions += "LEFT OUTER JOIN (";
sqlVersions += "SELECT COUNT(1) as VersionsCount, nodeId ";
sqlVersions += "FROM cmsDocument ";
sqlVersions += "WHERE (published = 0) ";
sqlVersions += "GROUP BY nodeid ";
sqlVersions += ") AS HistDoc ON CurDoc.nodeId = HistDoc.nodeId ";
sqlVersions += "WHERE (CurDoc.published = 1 AND curdoc.nodeid = curdoc.nodeid) ";
sqlVersions += "ORDER BY CurDoc.nodeId; ";
using (var db = HkDbHelper.ResolveDatabase())
{
ListCurrentPublishedVersions = db.Fetch <CurrentPublishedVersionModel>(sqlVersions);
}
if (!string.IsNullOrEmpty(request.Search))
{
ListCurrentPublishedVersions = ListCurrentPublishedVersions.Where(tl => tl.NodeId > 0 && tl.NodeId.ToString().Contains(request.Search.ToLower()) ||
!String.IsNullOrEmpty(tl.NodeName) && tl.NodeName.ToLower().Contains(request.Search.ToLower()) ||
!String.IsNullOrEmpty(tl.NodeUser) && tl.NodeUser.ToLower().Contains(request.Search.ToLower())).ToList();
}
request.ListCurrentPublishedVersions = ListCurrentPublishedVersions;
var paged = CreatePagination(request);
//var paged = versionsService.GetVersions(request);
VersionsModel.CurrentPage = int.Parse(paged.PageNumber.ToString());
VersionsModel.ItemsPerPage = int.Parse(paged.PageSize.ToString());
VersionsModel.ListCurrentPublishedVersions = paged.Items.ToList();
VersionsModel.TotalItems = int.Parse(paged.TotalItems.ToString());
VersionsModel.TotalPages = int.Parse(paged.TotalPages.ToString());
return(VersionsModel);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}
/// <summary>
/// Get Versions
/// </summary>
/// <returns>Paged Versions Model</returns>
public Page <CurrentPublishedVersionModel> GetVersions(HKVersionsModel request)
{
var sqlVersions = "SELECT CurDoc.nodeId, CurDoc.text AS NodeName, umbracoUser.userName AS NodeUser, CurDoc.updateDate AS PublishedDate, HistDoc.VersionsCount AS VersionsCount ";
sqlVersions += "FROM cmsDocument AS CurDoc ";
sqlVersions += "INNER JOIN umbracoUser ON CurDoc.documentUser = umbracoUser.id ";
sqlVersions += "LEFT OUTER JOIN (";
sqlVersions += "SELECT COUNT(1) as VersionsCount, nodeId ";
sqlVersions += "FROM cmsDocument ";
sqlVersions += "WHERE (published = 0) ";
sqlVersions += "GROUP BY nodeid ";
sqlVersions += ") AS HistDoc ON CurDoc.nodeId = HistDoc.nodeId ";
sqlVersions += "WHERE (CurDoc.published = 1 AND curdoc.nodeid = curdoc.nodeid) ";
sqlVersions += "ORDER BY CurDoc.nodeId; ";
return(db.Page <CurrentPublishedVersionModel>(request.CurrentPage, request.ItemsPerPage, sqlVersions));
}
/// <summary>
/// Create Pagination
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
private PagedResult <CurrentPublishedVersionModel> CreatePagination(HKVersionsModel request)
{
try
{
var startAt = (request.CurrentPage - 1) * request.ItemsPerPage;
var PagedLogs = new PagedResult <CurrentPublishedVersionModel>(request.ListCurrentPublishedVersions.Count, request.CurrentPage, request.ItemsPerPage)
{
Items = request.ListCurrentPublishedVersions.Skip(startAt).Take(request.ItemsPerPage).ToList <CurrentPublishedVersionModel>()
};
return(PagedLogs);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}