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); }
public ActionResult Index(string sitename, string order_by, string q, int? page, int? pagesize) { Site site; if (!TryGetSite(sitename, out site)) { return site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/queries", site.TinyName.ToLower() )); } Site = site; QuerySearchCriteria searchCriteria = new QuerySearchCriteria(q); if (string.IsNullOrEmpty(order_by)) { if (searchCriteria.IsValid) { order_by = searchCriteria.IsFeatured ? "featured" : "recent"; } else { order_by = CurrentUser.DefaultQuerySort ?? "featured"; } } if (!searchCriteria.IsValid) { CurrentUser.DefaultQuerySort = order_by; } ViewData["Site"] = Site; SelectMenuItem("Queries"); var pagesizeProvided = pagesize.HasValue; if (!Current.User.IsAnonymous && !pagesizeProvided) { pagesize = Current.User.DefaultQueryPageSize; } pagesize = Math.Max(Math.Min(pagesize ?? 50, 100), 10); page = Math.Max(page ?? 1, 1); if (!Current.User.IsAnonymous) { Current.User.DefaultQueryPageSize = pagesize; } int start = ((page.Value - 1) * pagesize.Value) + 1; int finish = page.Value * pagesize.Value; 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 QuerySets qs /**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 QuerySets qs /**join**/ /**leftjoin**/ /**where**/"); builder.Select("qs.Id as QuerySetId"); builder.Select("qs.LastActivity AS LastRun"); builder.Join("Revisions r ON r.Id = qs.CurrentRevisionId"); builder.Join("Queries q ON q.Id = r.QueryId"); builder.LeftJoin("Users u ON qs.OwnerId = u.Id"); builder.Where("qs.Hidden = 0"); builder.Where("qs.Title is not null"); builder.Where("qs.Title <> ''"); if (order_by == "featured" || order_by == "recent") { if (order_by == "featured") { builder.Where("qs.Featured = 1"); builder.OrderBy("qs.Votes DESC"); } builder.OrderBy("qs.LastActivity DESC"); } else { int threshold = 0; if (order_by == "popular") { builder.Where("qs.Views > @threshold", new { threshold = threshold }); builder.OrderBy("qs.Views DESC"); builder.OrderBy("qs.Votes DESC"); } else { order_by = "favorite"; builder.Where("qs.Votes > @threshold", new { threshold = threshold }); builder.OrderBy("qs.Votes DESC"); builder.OrderBy("qs.Views DESC"); } } if (searchCriteria.IsValid) { builder.Where("qs.Title LIKE @search OR qs.[Description] LIKE @search", new { search = '%' + searchCriteria.SearchTerm + '%' }); } } else if (order_by == "everything") { pager = builder.AddTemplate(@" SELECT /**select**/ FROM ( SELECT r.*, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber FROM Revisions r ) r /**join**/ /**leftjoin**/ WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber", new { start = start, finish = finish } ); counter = builder.AddTemplate("SELECT COUNT(*) FROM Revisions r"); builder.Select("r.Id AS RevisionId"); builder.Select("qs.Id as QuerySetId"); builder.Select("r.CreationDate AS LastRun"); builder.Join("QuerySetRevisions qr on qr.RevisionId = r.Id "); builder.Join("QuerySets qs on qs.Id = qr.QuerySetId"); builder.Join("Queries q on q.Id = r.QueryId"); builder.LeftJoin("Users u ON r.OwnerId = u.Id"); builder.OrderBy("CreationDate DESC"); } builder.Select("u.Id as CreatorId"); builder.Select("u.Login as CreatorLogin"); builder.Select("qs.Title AS Name"); builder.Select("qs.[Description] AS [Description]"); builder.Select("qs.Votes AS FavoriteCount"); builder.Select("qs.Views AS Views"); builder.Select("q.QueryBody AS [SQL]"); IEnumerable<QueryExecutionViewData> queries = Current.DB.Query<QueryExecutionViewData>( pager.RawSql, pager.Parameters ).Select( (view) => { view.SiteName = Site.TinyName.ToLower(); return view; } ); int total = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First(); sitename = Site.TinyName.ToLower(); var href = "/" + sitename + "/queries?order_by={0}"; var extra = ""; if (searchCriteria.IsValid) { extra += "&q=" + HtmlUtilities.UrlEncode(searchCriteria.RawInput); } // TODO: This is a hack to avoid misrepresenting the fact we aren't actually doing the search in this case if (order_by == "everything") { searchCriteria = new QuerySearchCriteria(null); } ViewData["SearchCriteria"] = searchCriteria; ViewData["Href"] = string.Format(href, order_by) + extra; if (Current.User.IsAnonymous && pagesizeProvided) { extra += "&pagesize=" + pagesize.Value; } SetHeader( "All Queries", new SubHeaderViewData { Description = "featured", Title = "Interesting queries selected by the administrators", Href = string.Format(href, "featured") + extra, Selected = (order_by == "featured") }, new SubHeaderViewData { Description = "recent", Title = "Recently saved queries", Href = string.Format(href, "recent") + extra, Selected = (order_by == "recent") }, new SubHeaderViewData { Description = "favorite", Title = "Favorite saved queries", Href = string.Format(href, "favorite") + extra, Selected = (order_by == "favorite") }, new SubHeaderViewData { Description = "popular", Title = "Saved queries with the most views", Href = string.Format(href, "popular") + extra, Selected = (order_by == "popular") }, new SubHeaderViewData { Description = "everything", Title = "All queries recently executed on the site", Href = string.Format(href, "everything") + extra, Selected = (order_by == "everything") } ); return View(new PagedList<QueryExecutionViewData>(queries, page.Value, pagesize.Value, false, total)); }