/// <summary>
/// Allows end of reqeust code to clean up this request's DB.
/// </summary>
public static void DisposeDB()
{
DataExplorerDatabase db = null;
if (Context != null)
{
db = Context.Items["DB"] as DataExplorerDatabase;
}
else
{
db = CallContext.GetData("DB") as DataExplorerDatabase;
}
if (db != null)
{
db.Dispose();
if (Context != null)
{
Context.Items["DB"] = null;
}
else
{
CallContext.SetData("DB", null);
}
}
}
public ActionResult Show(int id, string name, string order_by, int?page)
{
User user = !Current.User.IsAnonymous && Current.User.Id == id ? Current.User : Current.DB.Users.Get(id);
if (user == null)
{
return(PageNotFound());
}
// if this user has a display name, and the title is missing or does not match, permanently redirect to it
if (user.UrlTitle.HasValue() && (string.IsNullOrEmpty(name) || name != user.UrlTitle))
{
return(PageMovedPermanentlyTo(string.Format("/users/{0}/{1}", user.Id, HtmlUtilities.URLFriendly(user.Login)) + Request.Url.Query));
}
DataExplorerDatabase db = Current.DB;
SetHeader(user.Login);
SelectMenuItem("Users");
var profileTabs = new SubHeader
{
Selected = order_by,
Items = new List <SubHeaderViewData>
{
new SubHeaderViewData
{
Description = "edited",
Title = "Recently edited queries",
Href = "/users/" + user.Id + "?order_by=edited",
Default = true,
},
new SubHeaderViewData
{
Description = "favorite",
Title = "Favorite queries",
Href = "/users/" + user.Id + "?order_by=favorite"
},
new SubHeaderViewData
{
Description = "recent",
Title = "Recently executed queries",
Href = "/users/" + user.Id + "?order_by=recent"
}
}
};
ViewData["UserQueryHeaders"] = profileTabs;
page = Math.Max(page ?? 1, 1);
int?pagesize = 15; // In case we decide to make this a query param
int start = ((page.Value - 1) * pagesize.Value) + 1;
int finish = page.Value * pagesize.Value;
string message;
var builder = new SqlBuilder();
var pager = builder.AddTemplate(@"
SELECT
*
FROM
(
SELECT
/**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
FROM
Queries q
/**join**/
/**leftjoin**/
/**where**/
) AS results
WHERE
RowNumber BETWEEN @start AND @finish
ORDER BY
RowNumber",
new { start = start, finish = finish }
);
var counter = builder.AddTemplate("SELECT COUNT(*) FROM Queries q /**join**/ /**leftjoin**/ /**where**/");
if (order_by == "recent")
{
builder.Select("re.RevisionId AS Id");
builder.Select("re.LastRun");
builder.Select("s.Name AS SiteName");
builder.Where("re.UserId = @user", new { user = id });
builder.Join("Revisions r ON r.QueryId = q.Id");
builder.Join("RevisionExecutions re ON re.RevisionId = r.Id");
builder.Join("Sites s ON s.Id = re.SiteId");
builder.Join(@"QuerySets qs ON qs.Id = r.OriginalQuerySetId");
builder.OrderBy("re.LastRun DESC");
message = user.Id == CurrentUser.Id ?
"You have never ran any queries" : "No queries ran recently";
}
else
{
builder.Select("qs.CurrentRevisionId AS Id");
builder.Select("qs.LastActivity AS LastRun");
builder.Join("Revisions r on r.QueryId = q.Id");
builder.Join("QuerySets qs on qs.CurrentRevisionId = r.Id");
if (order_by == "favorite")
{
builder.Join(@"
Votes v ON
v.QuerySetId = qs.Id AND
v.UserId = @user AND
v.VoteTypeId = @vote",
new { user = id, vote = (int)VoteType.Favorite }
);
builder.OrderBy("v.Id DESC");
message = user.Id == CurrentUser.Id ?
"You have no favorite queries, click the star icon on a query to favorite it" : "No favorites";
}
else
{
builder.Where("qs.OwnerId = @user", new { user = id });
builder.Where("qs.Hidden = 0");
builder.OrderBy("qs.LastActivity DESC");
message = user.Id == CurrentUser.Id ?
"You haven't edited any queries" : "No queries";
}
}
builder.Select("qs.Id as QuerySetId");
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]");
builder.LeftJoin("Users u ON u.Id = qs.OwnerId");
var queries = Current.DB.Query <QueryExecutionViewData>(
pager.RawSql,
pager.Parameters
).Select <QueryExecutionViewData, QueryExecutionViewData>(
(view) =>
{
view.SiteName = (view.SiteName ?? Site.Name).ToLower();
return(view);
}
);
int total = Current.DB.Query <int>(counter.RawSql, counter.Parameters).First();
ViewData["Href"] = string.Format("/users/{0}/{1}", user.Id, HtmlUtilities.URLFriendly(user.Login)) + "?order_by=" + profileTabs.Selected;
ViewData["Queries"] = new PagedList <QueryExecutionViewData>(queries, page.Value, pagesize.Value, false, total);
if (!queries.Any())
{
ViewData["EmptyMessage"] = message;
}
return(View(user));
}
