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