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); } }