public static IHtmlString PageSizer(string href, int pageIndex, int currentPageSize, int pageCount, string cssClass) { href = _removePage.Replace(href, ""); PageSizer sizer = new PageSizer(href.ToLower(), pageIndex, currentPageSize, pageCount, cssClass); return(MvcHtmlString.Create(sizer.ToString())); }
public ActionResult Index(string sitename, string order_by, string q, int? page, int? pagesize) { Site = GetSite(sitename); if (Site == null) { return PageNotFound(); } QuerySearchCriteria searchCriteria = new QuerySearchCriteria(q); if (string.IsNullOrEmpty(order_by)) { if (searchCriteria.IsValid) order_by = searchCriteria.IsFeatured ? "featured" : "recent"; else order_by = "featured"; } ViewData["Site"] = Site; SelectMenuItem("Queries"); SetHeader( "All Queries", new SubHeaderViewData { Description = "featured", Title = "Interesting queries selected by the administrators", Href = "/" + sitename + "/queries?order_by=featured", Selected = (order_by == "featured") }, new SubHeaderViewData { Description = "recent", Title = "Recently saved queries", Href = "/" + sitename + "/queries?order_by=recent", Selected = (order_by == "recent") }, new SubHeaderViewData { Description = "favorite", Title = "Favorite saved queries", Href = "/" + sitename + "/queries?order_by=favorite", Selected = (order_by == "favorite") }, new SubHeaderViewData { Description = "popular", Title = "Saved queries with the most views", Href = "/" + sitename + "/queries?order_by=popular", Selected = (order_by == "popular") }, new SubHeaderViewData { Description = "everything", Title = "All queries recently executed on the site", Href = "/" + sitename + "/queries?order_by=everything", Selected = (order_by == "everything") } ); pagesize = Math.Max(Math.Min(pagesize ?? 50, 100), 10); page = Math.Max(page ?? 1, 1); int start = ((page.Value - 1) * pagesize.Value) + 1; int finish = page.Value * pagesize.Value; bool useLatest = true; var builder = new SqlBuilder(); SqlBuilder.Template pager = null, counter = null; if (order_by != "everything") { pager = builder.AddTemplate(@" SELECT * FROM ( SELECT /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber FROM Metadata metadata /**join**/ /**leftjoin**/ /**where**/ ) AS results WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber", new { start = start, finish = finish } ); counter = builder.AddTemplate("SELECT COUNT(*) FROM Metadata metadata /**join**/ /**leftjoin**/ /**where**/"); builder.Select("metadata.RevisionId AS Id"); builder.Select("metadata.LastActivity AS LastRun"); builder.Join("Queries query ON query.Id = metadata.LastQueryId"); builder.LeftJoin("Users [user] ON metadata.OwnerId = [user].Id"); builder.Where("metadata.Hidden = 0"); if (order_by == "featured" || order_by == "recent") { if (order_by == "featured") { builder.Where("metadata.Featured = 1"); builder.Where("metadata.First = 1"); builder.OrderBy("metadata.Votes DESC"); } builder.OrderBy("metadata.LastActivity DESC"); } else { int threshold = 0; if (order_by == "popular") { builder.Where("metadata.Views > @threshold", new { threshold = threshold }); builder.OrderBy("metadata.Views DESC"); builder.OrderBy("metadata.Votes DESC"); } else { builder.Where("metadata.Votes > @threshold", new { threshold = threshold }); builder.OrderBy("metadata.Votes DESC"); builder.OrderBy("metadata.Views DESC"); } } } else if (order_by == "everything") { pager = builder.AddTemplate(@" SELECT /**select**/ FROM ( SELECT Revisions.*, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber FROM Revisions ) AS revision /**join**/ /**leftjoin**/ /**where**/ WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber", new { start = start, finish = finish } ); counter = builder.AddTemplate("SELECT COUNT(*) FROM Revisions"); builder.Select("revision.Id AS Id"); builder.Select("revision.CreationDate AS LastRun"); builder.Join(@" Metadata metadata ON ( metadata.RevisionId = revision.RootId AND metadata.OwnerId = revision.OwnerId ) OR ( metadata.RevisionId = revision.Id AND metadata.OwnerId = revision.OwnerId AND revision.RootId IS NULL ) OR ( metadata.RevisionId = revision.Id AND metadata.OwnerId IS NULL AND revision.OwnerId IS NULL )" ); builder.Join("Queries query on query.Id = revision.QueryId"); builder.LeftJoin("Users [user] ON revision.OwnerId = [user].Id"); builder.OrderBy("CreationDate DESC"); useLatest = false; } builder.Select("[user].Id as CreatorId"); builder.Select("[user].Login as CreatorLogin"); builder.Select("metadata.Title AS Name"); builder.Select("metadata.[Description] AS [Description]"); builder.Select("metadata.Votes AS FavoriteCount"); builder.Select("metadata.Views AS Views"); builder.Select("query.QueryBody AS [SQL]"); if (searchCriteria.IsValid) { builder.Where("metadata.Title LIKE @search OR metadata.[Description] LIKE @search", new { search = '%' + searchCriteria.SearchTerm + '%' }); } IEnumerable<QueryExecutionViewData> queries = Current.DB.Query<QueryExecutionViewData>( pager.RawSql, pager.Parameters ).Select<QueryExecutionViewData, QueryExecutionViewData>( (view) => { view.UseLatestLink = useLatest; view.SiteName = Site.Name.ToLower(); return view; } ); int total = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First(); string href = "/" + Site.Name.ToLower() + "/queries?order_by=" + order_by; if (searchCriteria.IsValid) { href += "&q=" + HtmlUtilities.UrlEncode(searchCriteria.RawInput); } ViewData["SearchCriteria"] = searchCriteria; ViewData["TotalQueries"] = total; ViewData["PageNumbers"] = new PageNumber( href + "&page=-1", Convert.ToInt32(Math.Ceiling(total / (decimal)pagesize)), pagesize.Value, page.Value - 1, "pager" ); ViewData["PageSizer"] = new PageSizer( href + "&pagesize=-1", page.Value, pagesize.Value, total, "page-sizer fr" ); return View(queries); }