Ejemplo n.º 1
0
        public IEnumerable <Models.RankedCharacter> GetData(out int totalRecords, int roster, string globalSearch, int?limitOffset, int?limitRowCount, string orderBy, bool asc)
        {
            IEnumerable <Models.RankedCharacter> result = null;

            using (var context = new Models.WGODBContext())
            {
                context.Configuration.AutoDetectChangesEnabled = false;

                // Create the sql
                string orderByClause = $"order by { (string.IsNullOrWhiteSpace(orderBy) || orderBy.CompareTo("default") == 0 ? "level desc, Equipped_iLevel desc, Max_iLevel desc, AchievementPoints desc, name asc" : $"{orderBy}{(asc ? "" : " desc")}")}";
                string sql           = $"SELECT Name, Level, Class, AchievementPoints, Max_iLevel, Equipped_iLevel, LastUpdated, Realm, Roster, Modified_Level, Modified_Max_iLevel, Modified_Equipped_iLevel, Modified_AchievementPoints," +
                                       $"ROW_NUMBER() OVER ({orderByClause}) as Rank " +
                                       $"FROM Characters " +
                                       $"where roster = 1 " +
                                       $"{orderByClause}";

                // Get the results
                result = context.Database.SqlQuery <Models.RankedCharacter>(sql);

                // Check to see if there is a search
                if (!string.IsNullOrWhiteSpace(globalSearch))
                {
                    result = result.Where(p => p.Name.ToLower().Contains(globalSearch.ToLower()));
                }

                // Get the total record count
                totalRecords = result.Count();

                // Paging
                if (limitOffset.HasValue && limitRowCount.HasValue)
                {
                    result = result.Skip(limitOffset.Value).Take(limitRowCount.Value);
                }

                // Return data as a List
                result = result.ToList();
            }
Ejemplo n.º 2
0
        /// <summary>
        /// Register all the MVCGrid.Net
        /// </summary>
        public static void RegisterGrids()
        {
            #region " MVCGrid.Net Example "

            /* Example:
             * MVCGridDefinitionTable.Add("UsageExample", new MVCGridBuilder<YourModelItem>()
             *  .WithAuthorizationType(AuthorizationType.AllowAnonymous)
             *  .AddColumns(cols =>
             *  {
             *      // Add your columns here
             *      cols.Add().WithColumnName("UniqueColumnName")
             *          .WithHeaderText("Any Header")
             *          .WithValueExpression(i => i.YourProperty); // use the Value Expression to return the cell text for this column
             *      cols.Add().WithColumnName("UrlExample")
             *          .WithHeaderText("Edit")
             *          .WithValueExpression((i, c) => c.UrlHelper.Action("detail", "demo", new { id = i.Id }));
             *  })
             *  .WithRetrieveDataMethod((context) =>
             *  {
             *      // Query your data here. Obey Ordering, paging and filtering parameters given in the context.QueryOptions.
             *      // Use Entity Framework, a module from your IoC Container, or any other method.
             *      // Return QueryResult object containing IEnumerable<YouModelItem>
             *
             *      return new QueryResult<YourModelItem>()
             *      {
             *          Items = new List<YourModelItem>(),
             *          TotalRecords = 0 // if paging is enabled, return the total number of records of all pages
             *      };
             *
             *  })
             * );
             */
            #endregion

            #region " The Search Page Grid "
            // The Search Page
            MVCGridDefinitionTable.Add("SearchGrid", new MVCGridBuilder <Models.Character>()
                                       .WithAuthorizationType(AuthorizationType.AllowAnonymous)
                                       .AddColumns(cols =>
            {
                // Add your columns here
                cols.Add().WithColumnName("Name")
                .WithHeaderText("Name")
                .WithSorting(true)
                .WithValueTemplate("<a href='{Value}'>{Model.Name}</a>", false)
                .WithValueExpression((p, c) => c.UrlHelper.Action("Character", "WGO", new { name = p.Name, realm = p.Realm }));
                cols.Add().WithColumnName("Level")
                .WithHeaderText("Level")
                .WithSorting(true)
                .WithValueExpression(i => i.Level.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Level) ? "table-success" : "");
                cols.Add().WithColumnName("Class")
                .WithSorting(true)
                .WithValueExpression(i => i.Class);
                cols.Add().WithColumnName("AchievementPoints")
                .WithHeaderText("Achievement Points")
                .WithSorting(true)
                .WithValueExpression(i => i.AchievementPoints.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_AchievementPoints) ? "table-success" : "");
                cols.Add().WithColumnName("MaxiLevel")
                .WithHeaderText("Max iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Max_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Max_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("EquippediLevel")
                .WithHeaderText("Equipped iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Equipped_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Equipped_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("LastModified")
                .WithHeaderText("Last Modified")
                .WithSorting(true)
                .WithValueExpression(i => i.LastUpdated.ToString());
            })
                                       .WithPageParameterNames("name")
                                       .WithPageParameterNames("realm")
                                       //.WithQueryOnPageLoad(false) // this does not seem to work - Grid will flash, but then will disappear
                                       .WithPreloadData(false)
                                       .WithSorting(true, "EquippediLevel")
                                       .WithRetrieveDataMethod((context) =>
            {
                // Query your data here. Obey Ordering, paging and filtering parameters given in the context.QueryOptions.
                // Use Entity Framework, a module from your IoC Container, or any other method.
                // Return QueryResult object containing IEnumerable<YouModelItem>
                var options            = context.QueryOptions;
                string searchName      = options.GetPageParameterString("name");
                string searchRealm     = options.GetPageParameterString("realm");
                Models.WGODBContext db = new Models.WGODBContext();
                var query = db.Characters.Where(s => s.Name == searchName && s.Realm == searchRealm).OrderByDescending(s => s.Equipped_iLevel);

                int itemCount = query.Count();

                return(new QueryResult <Models.Character>()
                {
                    // Return the List of Characters
                    Items = query,

                    // if paging is enabled, return the total number of records of all pages
                    TotalRecords = query.Count()
                });
            })
                                       );
            #endregion

            #region " The Guild Page Grid "
            // The Guild Page
            MVCGridDefinitionTable.Add("GuildRoster", new MVCGridBuilder <Models.RankedCharacter>()
                                       .WithAuthorizationType(AuthorizationType.AllowAnonymous)
                                       .WithSorting(sorting: true, defaultSortColumn: "default", defaultSortDirection: SortDirection.Dsc)
                                       .WithPaging(paging: true, itemsPerPage: 10, allowChangePageSize: true, maxItemsPerPage: 100)
                                       .WithAdditionalQueryOptionNames("search")
                                       .AddColumns(cols =>
            {
                cols.Add().WithColumnName("Rank")
                .WithHeaderText("#")
                .WithValueExpression(i => i.Rank.ToString());
                cols.Add().WithColumnName("Name")
                .WithHeaderText("Name")
                .WithSorting(true)
                .WithValueTemplate("<a href='{Value}'>{Model.Name}</a>", false)
                .WithValueExpression((p, c) => c.UrlHelper.Action("Character", "WGO", new { name = p.Name, realm = p.Realm }));
                cols.Add().WithColumnName("Level")
                .WithHeaderText("Level")
                .WithSorting(true)
                .WithValueExpression(i => i.Level.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Level) ? "table-success" : "");
                cols.Add().WithColumnName("Class")
                .WithSorting(true)
                .WithValueExpression(i => i.Class);
                cols.Add().WithColumnName("AchievementPoints")
                .WithHeaderText("Achievement Points")
                .WithSorting(true)
                .WithValueExpression(i => i.AchievementPoints.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_AchievementPoints) ? "table-success" : "");
                cols.Add().WithColumnName("Max_iLevel")
                .WithHeaderText("Max iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Max_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Max_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("Equipped_iLevel")
                .WithHeaderText("Equipped iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Equipped_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Equipped_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("LastUpdated")
                .WithHeaderText("Last Updated")
                .WithSorting(true)
                .WithValueExpression(i => i.LastUpdated.ToString())
                .WithCellCssClassExpression(i => "small");
                cols.Add("Rescan").WithHtmlEncoding(false)
                .WithSorting(false)
                .WithHeaderText(" ")
                .WithValueExpression((p, c) => c.UrlHelper.Action("Rescan", "WGO", new { name = p.Name, realm = p.Realm }))
                .WithValueTemplate("<a href='{Value}' class='btn btn-primary' role='button' onclick=$.blockUI()>Rescan</a>");
            })
                                       .WithRetrieveDataMethod((context) =>
            {
                // Query your data here. Obey Ordering, paging and filtering parameters given in the context.QueryOptions.
                var options         = context.QueryOptions;
                string globalSearch = options.GetAdditionalQueryOptionString("search") == null ? string.Empty : options.GetAdditionalQueryOptionString("search");
                string sortColumn   = options.GetSortColumnData <string>() == null ? string.Empty : options.GetSortColumnData <string>().ToLower();
                var result          = new QueryResult <Models.RankedCharacter>();

                // Dependency Setup...
                int totalRecords = 0;
                var repo         = DependencyResolver.Current.GetService <IRankedCharacterRepository>();

                // Check to see if the dependency was found
                if (repo != null)
                {
                    var test            = repo.GetData(out totalRecords, WGOConstants.GuildRoster, globalSearch, options.GetLimitOffset(), options.GetLimitRowcount(), sortColumn, options.SortDirection == SortDirection.Asc);
                    result.TotalRecords = totalRecords;
                    result.Items        = test;
                }

                return(result);
            })
                                       );
            #endregion

            #region " The Raid Page Grid "
            // The Raid page
            MVCGridDefinitionTable.Add("RaidRoster", new MVCGridBuilder <Models.Character>()
                                       .WithAuthorizationType(AuthorizationType.AllowAnonymous)
                                       .AddColumns(cols =>
            {
                // Add your columns here
                cols.Add().WithColumnName("Role")
                .WithHeaderText("Role")
                .WithSorting(true)
                .WithValueExpression(i => i.Role);
                cols.Add().WithColumnName("Name")
                .WithHeaderText("Name")
                .WithSorting(true)
                .WithValueTemplate("<a href='{Value}'>{Model.Name}</a>", false)
                .WithValueExpression((p, c) => c.UrlHelper.Action("Character", "WGO", new { name = p.Name, realm = p.Realm }));
                cols.Add().WithColumnName("Level")
                .WithHeaderText("Level")
                .WithSorting(true)
                .WithValueExpression(i => i.Level.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Level) ? "table-success" : "");
                cols.Add().WithColumnName("Class")
                .WithSorting(true)
                .WithValueExpression(i => i.Class);
                cols.Add().WithColumnName("AchievementPoints")
                .WithHeaderText("Achievement Points")
                .WithSorting(true)
                .WithValueExpression(i => i.AchievementPoints.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_AchievementPoints) ? "table-success" : "");
                cols.Add().WithColumnName("MaxiLevel")
                .WithHeaderText("Max iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Max_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Max_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("EquippediLevel")
                .WithHeaderText("Equipped iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Equipped_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Equipped_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("LastModified")
                .WithHeaderText("Last Modified")
                .WithSorting(true)
                .WithValueExpression(i => i.LastUpdated.ToString());
                cols.Add("Recan").WithHtmlEncoding(false)
                .WithSorting(false)
                .WithHeaderText(" ")
                .WithValueExpression((p, c) => c.UrlHelper.Action("Rescan", "WGO", new { name = p.Name, realm = p.Realm, role = p.Role }))
                .WithValueTemplate("<a href='{Value}' class='btn btn-primary' role='button' onclick=$.blockUI()>Rescan</a>");
                cols.Add("Delete").WithHtmlEncoding(false)
                .WithSorting(false)
                .WithHeaderText(" ")
                .WithValueExpression((p, c) => c.UrlHelper.Action("Delete", "WGO", new { name = p.Name, realm = p.Realm, role = p.Role }))
                .WithValueTemplate("<a href='{Value}' class='btn btn-danger' role='button' onclick=$.blockUI()>Delete</a>");
            })
                                       .WithPreloadData(false)
                                       .WithSorting(sorting: true, defaultSortColumn: "EquippediLevel", defaultSortDirection: SortDirection.Dsc)
                                       .WithPaging(true, 20)
                                       .WithClientSideLoadingCompleteFunctionName("hideLoading")
                                       .WithRetrieveDataMethod((context) =>
            {
                var options         = context.QueryOptions;
                string globalSearch = options.GetAdditionalQueryOptionString("search");
                string sortColumn   = options.GetSortColumnData <string>();
                var result          = new QueryResult <Models.Character>();

                // Get the current data now...
                using (var db = new Models.WGODBContext())
                {
                    // Get the data
                    var query = db.Characters.AsQueryable().Where(s => s.Roster == WGOConstants.RaidRoster);

                    // Sort the data
                    switch (options.SortColumnName.ToLower())
                    {
                    case "role":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;

                    case "name":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Name) : query.OrderByDescending(p => p.Name);
                        break;

                    case "level":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Level) : query.OrderByDescending(p => p.Level);
                        break;

                    case "class":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Class) : query.OrderByDescending(p => p.Class);
                        break;

                    case "achievementpoints":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.AchievementPoints) : query.OrderByDescending(p => p.AchievementPoints);
                        break;

                    case "maxilevel":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Max_iLevel).ThenBy(p => p.Name);
                        break;

                    case "equippedilevel":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;

                    case "lastmodified":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.LastUpdated) : query.OrderByDescending(p => p.LastUpdated);
                        break;

                    default:
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;
                    }

                    // Get the full record count, before the paging
                    result.TotalRecords = query.Count();

                    // Paging
                    if (options.GetLimitOffset().HasValue)
                    {
                        query = query.Skip(options.GetLimitOffset().Value).Take(options.GetLimitRowcount().Value);
                    }

                    // Done!
                    result.Items = query.ToList();
                }

                return(result);
            })
                                       );
            #endregion

            #region " The My Roster Page Grid "
            // The Raid page
            MVCGridDefinitionTable.Add("MyRoster", new MVCGridBuilder <Models.Character>()
                                       .WithAuthorizationType(AuthorizationType.AllowAnonymous)
                                       .AddColumns(cols =>
            {
                // Add your columns here
                cols.Add().WithColumnName("Role")
                .WithHeaderText("Role")
                .WithSorting(true)
                .WithValueExpression(i => i.Role);
                cols.Add().WithColumnName("Name")
                .WithHeaderText("Name")
                .WithSorting(true)
                .WithValueTemplate("<a href='{Value}'>{Model.Name}</a>", false)
                .WithValueExpression((p, c) => c.UrlHelper.Action("Character", "WGO", new { name = p.Name, realm = p.Realm }));
                cols.Add().WithColumnName("Level")
                .WithHeaderText("Level")
                .WithSorting(true)
                .WithValueExpression(i => i.Level.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Level) ? "table-success" : "");
                cols.Add().WithColumnName("Class")
                .WithSorting(true)
                .WithValueExpression(i => i.Class);
                cols.Add().WithColumnName("AchievementPoints")
                .WithHeaderText("Achievement Points")
                .WithSorting(true)
                .WithValueExpression(i => i.AchievementPoints.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_AchievementPoints) ? "table-success" : "");
                cols.Add().WithColumnName("MaxiLevel")
                .WithHeaderText("Max iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Max_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Max_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("EquippediLevel")
                .WithHeaderText("Equipped iLevel")
                .WithSorting(true)
                .WithValueExpression(i => i.Equipped_iLevel.ToString())
                .WithCellCssClassExpression(p => DateModifiedLately(p.Modified_Equipped_iLevel) ? "table-success" : "");
                cols.Add().WithColumnName("LastModified")
                .WithHeaderText("Last Modified")
                .WithSorting(true)
                .WithValueExpression(i => i.LastUpdated.ToString());
                cols.Add("Recan").WithHtmlEncoding(false)
                .WithSorting(false)
                .WithHeaderText(" ")
                .WithValueExpression((p, c) => c.UrlHelper.Action("Rescan", "WGO", new { name = p.Name, realm = p.Realm, role = p.Role }))
                .WithValueTemplate("<a href='{Value}' class='btn btn-primary' role='button' onclick=$.blockUI()>Rescan</a>");
                cols.Add("Delete").WithHtmlEncoding(false)
                .WithSorting(false)
                .WithHeaderText(" ")
                .WithValueExpression((p, c) => c.UrlHelper.Action("Delete", "WGO", new { name = p.Name, realm = p.Realm, role = p.Role }))
                .WithValueTemplate("<a href='{Value}' class='btn btn-danger' role='button' onclick=$.blockUI()>Delete</a>");
            })
                                       .WithPreloadData(false)
                                       .WithSorting(sorting: true, defaultSortColumn: "EquippediLevel", defaultSortDirection: SortDirection.Dsc)
                                       .WithPaging(true, 20)
                                       .WithClientSideLoadingCompleteFunctionName("hideLoading")
                                       .WithRetrieveDataMethod((context) =>
            {
                var options         = context.QueryOptions;
                string globalSearch = options.GetAdditionalQueryOptionString("search");
                string sortColumn   = options.GetSortColumnData <string>();
                var result          = new QueryResult <Models.Character>();

                // Get the current data now...
                using (var db = new Models.WGODBContext())
                {
                    // Get the data
                    var query = db.Characters.AsQueryable().Where(s => s.Roster == WGOConstants.MyRoster);

                    // Sort the data
                    switch (options.SortColumnName.ToLower())
                    {
                    case "role":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;

                    case "name":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Name) : query.OrderByDescending(p => p.Name);
                        break;

                    case "level":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Level) : query.OrderByDescending(p => p.Level);
                        break;

                    case "class":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.Class) : query.OrderByDescending(p => p.Class);
                        break;

                    case "achievementpoints":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.AchievementPoints) : query.OrderByDescending(p => p.AchievementPoints);
                        break;

                    case "maxilevel":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Max_iLevel).ThenBy(p => p.Name);
                        break;

                    case "equippedilevel":
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;

                    case "lastmodified":
                        query = options.SortDirection == SortDirection.Asc ? query.OrderBy(p => p.LastUpdated) : query.OrderByDescending(p => p.LastUpdated);
                        break;

                    default:
                        query = query.OrderByDescending(p => p.Role).ThenByDescending(p => p.Level).ThenByDescending(p => p.Equipped_iLevel).ThenBy(p => p.Name);
                        break;
                    }

                    // Get the full record count, before the paging
                    result.TotalRecords = query.Count();

                    // Paging
                    if (options.GetLimitOffset().HasValue)
                    {
                        query = query.Skip(options.GetLimitOffset().Value).Take(options.GetLimitRowcount().Value);
                    }

                    // Done!
                    result.Items = query.ToList();
                }

                return(result);
            })
                                       );
            #endregion
        }