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