public void SetHeader(string title, string selected, params SubHeaderViewData[] tabs) { ViewData["Header"] = Header = new SubHeader(title) { Selected = selected, Items = tabs }; }
public void SetHeader(string title, params SubHeaderViewData[] tabs) { ViewData["Header"] = new SubHeader(title) {Items = tabs}; }
public ActionResult Show(int id, string name, string order_by, int? page) { User user = Current.DB.Users.FirstOrDefault(row => row.Id == 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); } DBContext db = Current.DB; SetHeader(user.Login); SelectMenuItem("Users"); order_by = order_by ?? "edited"; ViewData["UserQueryHeaders"] = new SubHeader { Items = new List<SubHeaderViewData> { new SubHeaderViewData { Description = "edited", Title = "Recently edited queries", Href = "/users/" + user.Id + "?order_by=edited", Selected = (order_by == "edited") }, new SubHeaderViewData { Description = "favorite", Title = "Favorite queries", Href = "/users/" + user.Id + "?order_by=favorite", Selected = (order_by == "favorite") }, new SubHeaderViewData { Description = "recent", Title = "Recently executed queries", Href = "/users/" + user.Id + "?order_by=recent", Selected = (order_by == "recent") } } }; 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; bool useLatest = false; string message; var builder = new SqlBuilder(); var pager = builder.AddTemplate(@" SELECT * FROM ( SELECT /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber FROM Queries query /**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 query /**join**/ /**leftjoin**/ /**where**/"); if (order_by == "recent") { builder.Select("execution.RevisionId AS Id"); builder.Select("execution.LastRun"); builder.Select("site.Name AS SiteName"); builder.Join("QueryExecutions execution ON execution.QueryId = query.Id"); builder.Join("Sites site ON site.Id = execution.SiteId"); builder.Join("Revisions ON Revisions.Id = execution.RevisionId AND execution.UserId = @user", new { user = id }); builder.Join(@" Metadata metadata ON ( metadata.RevisionId = Revisions.RootId AND metadata.OwnerId = Revisions.OwnerId ) OR ( metadata.RevisionId = Revisions.Id AND metadata.OwnerId = Revisions.OwnerId AND Revisions.RootId IS NULL ) OR ( metadata.RevisionId = Revisions.Id AND metadata.OwnerId IS NULL AND Revisions.OwnerId IS NULL )" ); builder.OrderBy("execution.LastRun DESC"); message = user.Id == CurrentUser.Id ? "You have never ran any queries" : "No queries ran recently"; } else { builder.Select("metadata.RevisionId AS Id"); builder.Select("metadata.LastActivity AS LastRun"); builder.Join("Metadata metadata on metadata.LastQueryId = query.Id"); if (order_by == "favorite") { builder.Join(@" Votes ON Votes.RootId = metadata.RevisionId AND ( Votes.OwnerId = metadata.OwnerId OR (Votes.OwnerId IS NULL AND metadata.OwnerID IS NULL) ) AND Votes.UserId = @user AND Votes.VoteTypeId = @vote", new { user = id, vote = (int)VoteType.Favorite } ); builder.OrderBy("metadata.Votes DESC"); useLatest = true; 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("metadata.OwnerId = @user", new { user = id }); builder.Where("metadata.Hidden = 0"); builder.OrderBy("metadata.LastActivity DESC"); message = user.Id == CurrentUser.Id ? "You haven't edited any queries" : "No queries"; } } 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]"); builder.LeftJoin("Users [user] ON [user].Id = metadata.OwnerId"); var queries = Current.DB.Query<QueryExecutionViewData>( pager.RawSql, pager.Parameters ).Select<QueryExecutionViewData, QueryExecutionViewData>( (view) => { view.UseLatestLink = useLatest; view.SiteName = (view.SiteName ?? Site.Name).ToLower(); return view; } ); int total = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First(); string href = string.Format("/users/{0}/{1}", user.Id, HtmlUtilities.URLFriendly(user.Login)) + "?order_by=" + order_by; ViewData["Queries"] = queries; ViewData["PageNumbers"] = new PageNumber( href + "&page=-1", Convert.ToInt32(Math.Ceiling(total / (decimal)pagesize)), pagesize.Value, page.Value - 1, "pager" ); if (!queries.Any()) { ViewData["EmptyMessage"] = message; } return View(user); }
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); }