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