public virtual Page<HistoryQueryModel> GetPagedItems(DataTablesPageRequest pageRequest) { var query = Sql.Builder .Select(@"History.*, Series.Title as SeriesTitle, Episodes.Title as EpisodeTitle, Episodes.SeasonNumber as SeasonNumber, Episodes.EpisodeNumber as EpisodeNumber, Episodes.Overview as EpisodeOverview") .From("History") .InnerJoin("Series") .On("History.SeriesId = Series.SeriesId") .InnerJoin("Episodes") .On("History.EpisodeId = Episodes.EpisodeId"); var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1; if (!string.IsNullOrEmpty(pageRequest.Search)) { var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest)); if (!string.IsNullOrEmpty(whereClause)) query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%"); } var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest)); if (!string.IsNullOrEmpty(orderBy)) { query.Append("ORDER BY " + orderBy); } return _database.Page<HistoryQueryModel>(startPage, pageRequest.DisplayLength, query); }
public virtual Page<Log> GetPagedItems(DataTablesPageRequest pageRequest) { var query = Sql.Builder .Select(@"*") .From("Logs"); var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1; if (!string.IsNullOrEmpty(pageRequest.Search)) { var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest)); if (!string.IsNullOrEmpty(whereClause)) query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%"); } var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest)); if (!string.IsNullOrEmpty(orderBy)) { query.Append("ORDER BY " + orderBy); } return _database.Page<Log>(startPage, pageRequest.DisplayLength, query); }
public ActionResult AjaxBinding(DataTablesPageRequest pageRequest) { var pageResult = _logProvider.GetPagedItems(pageRequest); var totalItems = _logProvider.Count(); var items = pageResult.Items.Select(l => new LogModel { Time = l.Time.ToString(), Level = l.Level, Source = l.Logger, Message = l.Message, Method = l.Method, ExceptionType = l.ExceptionType, Exception = l.Exception }); return Json(new { sEcho = pageRequest.Echo, iTotalRecords = totalItems, iTotalDisplayRecords = pageResult.TotalItems, aaData = items }, JsonRequestBehavior.AllowGet); }
public ActionResult AjaxBinding(DataTablesPageRequest pageRequest) { var pageResult = _historyProvider.GetPagedItems(pageRequest); var totalItems = _historyProvider.Count(); var ignoreArticles = _configProvider.IgnoreArticlesWhenSortingSeries; var items = pageResult.Items.Select(h => new HistoryModel { HistoryId = h.HistoryId, SeriesId = h.SeriesId, EpisodeNumbering = string.Format("{0}x{1:00}", h.SeasonNumber, h.EpisodeNumber), EpisodeTitle = h.EpisodeTitle, EpisodeOverview = h.EpisodeOverview, SeriesTitle = h.SeriesTitle, SeriesTitleSorter = ignoreArticles ? h.SeriesTitle.IgnoreArticles() : h.SeriesTitle, NzbTitle = h.NzbTitle, Quality = h.Quality.ToString(), IsProper = h.IsProper, Date = h.Date.ToString(), DateSorter = h.Date.ToString("o", CultureInfo.InvariantCulture), Indexer = h.Indexer, EpisodeId = h.EpisodeId, NzbInfoUrl = h.NzbInfoUrl, ReleaseGroup = h.ReleaseGroup }); return Json(new { sEcho = pageRequest.Echo, iTotalRecords = totalItems, iTotalDisplayRecords = pageResult.TotalItems, aaData = items }, JsonRequestBehavior.AllowGet); }
public static IEnumerable<string> GetOrderByClause(DataTablesPageRequest pageRequest) { var columns = pageRequest.ColumnNames.Split(','); for (var idx = 0; idx < pageRequest.SortingCols; ++idx) { yield return string.Format("{0} {1}", columns[pageRequest.SortCol[idx]], pageRequest.SortDir[idx]); } }
public static IEnumerable<string> GetSearchClause(DataTablesPageRequest pageRequest) { var columns = pageRequest.ColumnNames.Split(','); for (var idx = 0; idx < pageRequest.Searchable.Count; ++idx) { if (pageRequest.Searchable[idx]) yield return string.Format("{0} LIKE @0", columns[idx]); } }
public ActionResult AjaxBinding(DataTablesPageRequest pageRequest) { var logs = _logProvider.GetAllLogs(); var totalCount = logs.Count(); IQueryable<Log> q = logs; if (!string.IsNullOrEmpty(pageRequest.Search)) { q = q.Where(b => b.Logger.Contains(pageRequest.Search) || b.Exception.Contains(pageRequest.Search) || b.Message.Contains(pageRequest.Search)); } int filteredCount = q.Count(); IQueryable<Log> sorted = q; for (int i = 0; i < pageRequest.SortingCols; i++) { int sortCol = pageRequest.SortCol[i]; var sortColName = sortCol == 0 ? "Time" : sortCol == 1 ? "Level" : "Logger"; var sortExpression = String.Format("{0} {1}", sortColName, pageRequest.SortDir[i]); sorted = sorted.OrderBy(sortExpression); } IQueryable<Log> filteredAndSorted = sorted; if (filteredCount > pageRequest.DisplayLength) { filteredAndSorted = sorted.Skip(pageRequest.DisplayStart).Take(pageRequest.DisplayLength); } var logModels = filteredAndSorted.ToList().Select(s => new LogModel { Time = s.Time.ToString(), Level = s.Level, Source = s.Logger, Message = s.Message, Method = s.Method, ExceptionType = s.ExceptionType, Exception = s.Exception }); return Json(new { sEcho = pageRequest.Echo, iTotalRecords = totalCount, iTotalDisplayRecords = filteredCount, aaData = logModels }, JsonRequestBehavior.AllowGet); }