public Page <Customer> GetCustomers(DataTablesPageRequest pageRequest)
{
var query = Sql.Builder
.Select("*")
.From("Customer");
if (!string.IsNullOrEmpty(pageRequest.Search))
{
var whereClause = string.Join(" OR ", GetSearchClause(pageRequest));
if (!string.IsNullOrEmpty(whereClause))
{
query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%");
}
}
var orderBy = string.Join(",", GetOrderByClause(pageRequest));
if (!string.IsNullOrEmpty(orderBy))
{
query.Append("ORDER BY " + orderBy);
}
var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
return(_database.Page <Customer>(startPage, pageRequest.DisplayLength, query));
}
public virtual Page <HistoryQueryModel> GetPagedItems(DataTablesPageRequest pageRequest)
{
var query = Sql.Builder
.Select(@"History.*, Series.Title as SeriesTitle, Episodes.Title as EpisodeTitle,
Episodes.SeasonNumber as SeasonNumber, Episodes.EpisodeNumber as EpisodeNumber,
Episodes.Overview as EpisodeOverview")
.From("History")
.InnerJoin("Series")
.On("History.SeriesId = Series.SeriesId")
.InnerJoin("Episodes")
.On("History.EpisodeId = Episodes.EpisodeId");
var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
if (!string.IsNullOrEmpty(pageRequest.Search))
{
var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest));
if (!string.IsNullOrEmpty(whereClause))
{
query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%");
}
}
var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest));
if (!string.IsNullOrEmpty(orderBy))
{
query.Append("ORDER BY " + orderBy);
}
return(_database.Page <HistoryQueryModel>(startPage, pageRequest.DisplayLength, query));
}
public ActionResult CustomerPage(DataTablesPageRequest pageRequest)
{
var petaPocoPage = _repository.GetCustomers(pageRequest);
var pageResponse = DataTablesFormat.PageResponse(pageRequest, petaPocoPage);
return(Json(pageResponse));
}
public ActionResult AjaxBinding(DataTablesPageRequest pageRequest)
{
var pageResult = _logProvider.GetPagedItems(pageRequest);
var totalItems = _logProvider.Count();
var items = pageResult.Items.Select(l => new LogModel
{
Time = l.Time.ToString(),
Level = l.Level,
Source = l.Logger,
Message = l.Message,
Method = l.Method,
ExceptionType = l.ExceptionType,
Exception = l.Exception
});
return(Json(new
{
sEcho = pageRequest.Echo,
iTotalRecords = totalItems,
iTotalDisplayRecords = pageResult.TotalItems,
aaData = items
},
JsonRequestBehavior.AllowGet));
}
public virtual Page <Log> GetPagedItems(DataTablesPageRequest pageRequest)
{
var query = Sql.Builder
.Select(@"*")
.From("Logs");
var startPage = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
if (!string.IsNullOrEmpty(pageRequest.Search))
{
var whereClause = string.Join(" OR ", SqlBuilderHelper.GetSearchClause(pageRequest));
if (!string.IsNullOrEmpty(whereClause))
{
query.Append("WHERE " + whereClause, "%" + pageRequest.Search + "%");
}
}
var orderBy = string.Join(",", SqlBuilderHelper.GetOrderByClause(pageRequest));
if (!string.IsNullOrEmpty(orderBy))
{
query.Append("ORDER BY " + orderBy);
}
return(_database.Page <Log>(startPage, pageRequest.DisplayLength, query));
}
public ActionResult AjaxBinding(DataTablesPageRequest pageRequest)
{
var pageResult = _historyProvider.GetPagedItems(pageRequest);
var totalItems = _historyProvider.Count();
var ignoreArticles = _configProvider.IgnoreArticlesWhenSortingSeries;
var items = pageResult.Items.Select(h => new HistoryModel
{
HistoryId = h.HistoryId,
SeriesId = h.SeriesId,
EpisodeNumbering = string.Format("{0}x{1:00}", h.SeasonNumber, h.EpisodeNumber),
EpisodeTitle = h.EpisodeTitle,
EpisodeOverview = h.EpisodeOverview,
SeriesTitle = h.SeriesTitle,
SeriesTitleSorter = ignoreArticles ? h.SeriesTitle.IgnoreArticles() : h.SeriesTitle,
NzbTitle = h.NzbTitle,
Quality = h.Quality.ToString(),
IsProper = h.IsProper,
Date = h.Date.ToString(),
DateSorter = h.Date.ToString("o", CultureInfo.InvariantCulture),
Indexer = h.Indexer,
EpisodeId = h.EpisodeId,
NzbInfoUrl = h.NzbInfoUrl,
ReleaseGroup = h.ReleaseGroup
});
return(Json(new
{
sEcho = pageRequest.Echo,
iTotalRecords = totalItems,
iTotalDisplayRecords = pageResult.TotalItems,
aaData = items
},
JsonRequestBehavior.AllowGet));
}
public static IEnumerable <string> GetOrderByClause(DataTablesPageRequest pageRequest)
{
var columns = pageRequest.ColumnNames.Split(',');
for (var idx = 0; idx < pageRequest.SortingCols; ++idx)
{
yield return(string.Format("{0} {1}", columns[pageRequest.SortCol[idx]], pageRequest.SortDir[idx]));
}
}
public async Task <PaginatedList <Booking> > GetBookingsAsync(DataTablesPageRequest pageRequest)
{
var bookingsQuery = _context.Bookings
.Include(b => b.Activity)
.Include(b => b.User)
.Where(b => b.Id > 0);
var page = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
return(await PaginatedList <Booking> .CreateAsync(bookingsQuery.AsNoTracking(), page, pageRequest.DisplayLength));
}
public static IEnumerable <string> GetSearchClause(DataTablesPageRequest pageRequest)
{
var columns = pageRequest.ColumnNames.Split(',');
for (var idx = 0; idx < pageRequest.Searchable.Count; ++idx)
{
if (pageRequest.Searchable[idx])
{
yield return(string.Format("{0} LIKE @0", columns[idx]));
}
}
}
public async Task <IActionResult> FetchBookings(DataTablesPageRequest pageRequest)
{
var bookingsQuery = _bookingsService.FetchAllBookingsWithCentre();
var page = (pageRequest.DisplayLength == 0) ? 1 : pageRequest.DisplayStart / pageRequest.DisplayLength + 1;
var bookings = await PaginatedList <Booking> .CreateAsync(bookingsQuery.AsNoTracking(), page, pageRequest.DisplayLength);
return(Json(new DataTablesPageResponse <Booking>
{
Draw = 1,
RecordsTotal = bookings.Count(),
RecordsFiltered = bookings.Count(),
Data = bookings.ToList()
}));
}
public async Task <JsonResult> GetData(DataTablesPageRequest model, string dataSource)
{
// this datasource thing is here because I'm thinking of figuring out how to make the whole iqueryable part
// completely dynamic, then changing the "GetOrders" action to a "GetData" or (or whatever relevant name) action
switch (dataSource)
{
case "Orders":
Page <OrderViewModel> ordersPage = SearchOrdersQuery(model);
var ordersPageResponse = new
{
model.draw,
recordsTotal = ordersPage.TotalItems,
recordsFiltered = ordersPage.TotalDisplayItems,
data = ordersPage.Items.ToArray()
};
return(Json(ordersPageResponse));
case "Accounts":
Page <AccountViewModel> accountsPage = SearchAccountsQuery(model);
var accountsPageResponse = new
{
model.draw,
recordsTotal = accountsPage.TotalItems,
recordsFiltered = accountsPage.TotalDisplayItems,
data = accountsPage.Items.ToArray()
};
return(Json(accountsPageResponse));
case "Users":
Page <UserViewModel> usersPage = SearchUsersQuery(model);
var usersPageResponse = new
{
model.draw,
recordsTotal = usersPage.TotalItems,
recordsFiltered = usersPage.TotalDisplayItems,
data = usersPage.Items.ToArray()
};
return(Json(usersPageResponse));
}
return(Json("Not Implemented Exception"));
}
public ActionResult ViewOrderRequestsWithPaging(DataTablesPageRequest gridParams)
{
int page = gridParams.DisplayStart / (gridParams.DisplayLength == 0 ? 1 : gridParams.DisplayLength);
//countryProg.Id
Dictionary <String, String> options = new Dictionary <string, string>();
options.Add("CountryProgrammeId", countryProg.Id.ToString().ToUpper());
OrderRequestPagePacket packet = orderRequestService.getPagedOrderRequests(page, gridParams.DisplayLength, options);
return(Json(new
{
iTotalRecords = packet.TotalOrders,
iTotalDisplayRecords = packet.TotalOrders,
aaData = packet.Orders,
sEcho = gridParams.Echo
},
JsonRequestBehavior.AllowGet));
}
public static object PageResponse(DataTablesPageRequest pageRequest, Page <Customer> report)
{
return(new
{
sEcho = pageRequest.Echo,
iTotalRecords = report.TotalItems,
iTotalDisplayRecords = report.TotalItems,
sColumns = pageRequest.ColumnNames,
aaData = (from i in report.Items
select new[]
{
i.Id.ToString(CultureInfo.InvariantCulture),
i.Last,
i.First,
i.Street,
i.City,
i.State,
i.Zip,
i.Phone,
i.Email
}).ToList()
});
}
public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
var pageRequest = new DataTablesPageRequest
{
Echo = BindDataTablesRequestParam <Int32>(bindingContext, "sEcho"),
DisplayStart = BindDataTablesRequestParam <Int32>(bindingContext, "iDisplayStart"),
DisplayLength = BindDataTablesRequestParam <Int32>(bindingContext, "iDisplayLength"),
ColumnNames = BindDataTablesRequestParam <string>(bindingContext, "sColumns"),
Columns = BindDataTablesRequestParam <Int32>(bindingContext, "iColumns"),
Search = BindDataTablesRequestParam <string>(bindingContext, "sSearch"),
Regex = BindDataTablesRequestParam <bool>(bindingContext, "bRegex"),
SortingCols = BindDataTablesRequestParam <Int32>(bindingContext, "iSortingCols"),
DataProp = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "mDataProp_"),
RegexColumns = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bRegex_"),
Searchable = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bSearchable_"),
Sortable = BindDataTablesRequestParam <bool>(controllerContext.HttpContext.Request, "bSortable_"),
SortCol = BindDataTablesRequestParam <Int32>(controllerContext.HttpContext.Request, "iSortCol_"),
SearchColumns = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "sSearch_"),
SortDir = BindDataTablesRequestParam <string>(controllerContext.HttpContext.Request, "sSortDir_")
};
return(pageRequest);
}
private Page <OrderViewModel> SearchOrdersQuery(DataTablesPageRequest model)
{
IQueryable <OrderViewModel> query = from order in _context.Orders
join address in _context.Addresses on order.AddressId equals address.Id
join user in _context.Users on order.UserId equals user.Id
select new OrderViewModel()
{
Id = order.Id,
Created = order.Created.ConvertTimeToLocal(),
OrderNumber = order.OrderNumber,
User = user.Email,
Location = address.City,
Total = order.SubTotal,
Cin7Id = order.Cin7ID,
Action = "<a class='button is-block secondary' style='height: 24px;' href='/Admin/SingleOrder/" + order.OrderNumber + "'>View</a>",
};
Page <OrderViewModel> page = new Page <OrderViewModel>
{
TotalItems = query.Count()
};
string orderByName = string.Empty;
Order orderBy = new Order();
if (model.order != null)
{
orderBy = model.order.First();
orderByName = model.columns[orderBy.column].name;
}
// Apply OR filters to IQueryable
#region ApplyFilters
string searchTerm = model.search.value;
Predicate <OrderViewModel> globalPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> idPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> createdPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> orderNumberPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> userPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> locationPredicate = PredicateExtensions.False <OrderViewModel>();
Predicate <OrderViewModel> totalPredicate = PredicateExtensions.False <OrderViewModel>();
bool anyGlobal = false,
anyId = false,
anyCreated = false,
anyOrderNumber = false,
anyUser = false,
anyLocation = false,
anyTotal = false;
foreach (var column in model.columns)
{
string columnSearchTerm = column.search.value;
switch (column.name)
{
case "Id":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Id);
}
else
{
query = query.OrderByDescending(q => q.Id);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => o.Id.ToString().Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => o.Id.ToString().Contains(columnSearchTerm);
idPredicate = idPredicate.Or(colPredicate);
anyId = true;
}
}
break;
case "Created":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Created);
}
else
{
query = query.OrderByDescending(q => q.Created);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => o.Created.ToString().Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => o.Created.ToString().Contains(columnSearchTerm);
createdPredicate = createdPredicate.Or(colPredicate);
anyCreated = true;
}
}
break;
case "OrderNumber":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.OrderNumber);
}
else
{
query = query.OrderByDescending(q => q.OrderNumber);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => !string.IsNullOrEmpty(o.OrderNumber) ? o.OrderNumber.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => !string.IsNullOrEmpty(o.OrderNumber) ? o.OrderNumber.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
orderNumberPredicate = orderNumberPredicate.Or(colPredicate);
anyOrderNumber = true;
}
}
break;
case "User":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.User);
}
else
{
query = query.OrderByDescending(q => q.User);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => !string.IsNullOrEmpty(o.User) ? o.User.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => !string.IsNullOrEmpty(o.User) ? o.User.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
userPredicate = userPredicate.Or(colPredicate);
anyUser = true;
}
}
break;
case "Location":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Location);
}
else
{
query = query.OrderByDescending(q => q.Location);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => !string.IsNullOrEmpty(o.Location) ? o.Location.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => !string.IsNullOrEmpty(o.Location) ? o.Location.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
locationPredicate = locationPredicate.Or(colPredicate);
anyLocation = true;
}
}
break;
case "Total":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Total);
}
else
{
query = query.OrderByDescending(q => q.Total);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(OrderViewModel o) => o.Total.ToString().Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(OrderViewModel o) => o.Total.ToString().Contains(columnSearchTerm);
totalPredicate = totalPredicate.Or(colPredicate);
anyTotal = true;
}
}
break;
}
}
if (anyGlobal)
{
bool funcOr(OrderViewModel l) => globalPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyId)
{
bool funcOr(OrderViewModel l) => idPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyCreated)
{
bool funcOr(OrderViewModel l) => createdPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyOrderNumber)
{
bool funcOr(OrderViewModel l) => orderNumberPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyUser)
{
bool funcOr(OrderViewModel l) => userPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyLocation)
{
bool funcOr(OrderViewModel l) => locationPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyTotal)
{
bool funcOr(OrderViewModel l) => totalPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
#endregion
var startPage = (model.length == 0) ? 1 : model.start / model.length;
page.CurrentPage = startPage;
page.ItemsPerPage = model.length;
page.TotalPages = query.Count() / page.ItemsPerPage;
page.TotalDisplayItems = query.Count();
page.Items = query.Skip(startPage * model.length).Take(model.length).ToList();
return(page);
}
private Page <UserViewModel> SearchUsersQuery(DataTablesPageRequest model)
{
IQueryable <UserViewModel> query = from user in _context.Users
from accountUser in _context.AccountUsers.Where(au => au.UserId == user.Id).DefaultIfEmpty()
from account in _context.Accounts.Where(a => a.Id == accountUser.AccountId).DefaultIfEmpty()
//join accountUser in _context.AccountUsers on user.Id equals accountUser.UserId
//join account in _context.Accounts on accountUser.AccountId equals account.Id
select new UserViewModel()
{
Id = user.Id,
FirstName = user.FirstName,
LastName = user.LastName,
Email = user.Email,
Username = user.UserName,
Account = account != null ? account.CompanyName : string.Empty,
//Account = "",
Type = user.Type.ToString(),
UserLink = "<a class='button is-primary' href='/Admin/UserView?id=" + user.Id + "'>View</a>"
};
Page <UserViewModel> page = new Page <UserViewModel>
{
TotalItems = query.Count()
};
string orderByName = string.Empty;
Order orderBy = new Order();
if (model.order != null)
{
orderBy = model.order.First();
orderByName = model.columns[orderBy.column].name;
}
// Apply OR filters to IQueryable
#region ApplyFilters
string searchTerm = model.search.value;
Predicate <UserViewModel> globalPredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> idPredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> firstNamePredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> lastNamePredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> emailPredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> usernamePredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> accountPredicate = PredicateExtensions.False <UserViewModel>();
Predicate <UserViewModel> typePredicate = PredicateExtensions.False <UserViewModel>();
bool anyGlobal = false,
anyId = false,
anyFirstName = false,
anyLastName = false,
anyEmail = false,
anyUsername = false,
anyAccount = false,
anyType = false;
foreach (var column in model.columns)
{
string columnSearchTerm = column.search.value;
switch (column.name)
{
case "Id":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Id);
}
else
{
query = query.OrderByDescending(q => q.Id);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => o.Id.ToString().Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => o.Id.ToString().Contains(columnSearchTerm);
idPredicate = idPredicate.Or(colPredicate);
anyId = true;
}
}
break;
case "FirstName":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.FirstName);
}
else
{
query = query.OrderByDescending(q => q.FirstName);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => !string.IsNullOrEmpty(o.FirstName) ? (o.FirstName.ToUpper().ToString().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => !string.IsNullOrEmpty(o.FirstName) ? (o.FirstName.ToUpper().ToString().Contains(columnSearchTerm.ToUpper())) : false;
firstNamePredicate = firstNamePredicate.Or(colPredicate);
anyFirstName = true;
}
}
break;
case "LastName":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.LastName);
}
else
{
query = query.OrderByDescending(q => q.LastName);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => !string.IsNullOrEmpty(o.LastName) ? (o.LastName.ToUpper().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => !string.IsNullOrEmpty(o.LastName) ? (o.LastName.ToUpper().Contains(columnSearchTerm.ToUpper())) : false;
lastNamePredicate = lastNamePredicate.Or(colPredicate);
anyLastName = true;
}
}
break;
case "Email":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Email);
}
else
{
query = query.OrderByDescending(q => q.Email);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => !string.IsNullOrEmpty(o.Email) ? (o.Email.ToUpper().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => !string.IsNullOrEmpty(o.Email) ? (o.Email.ToUpper().Contains(columnSearchTerm.ToUpper())) : false;
emailPredicate = emailPredicate.Or(colPredicate);
anyEmail = true;
}
}
break;
case "Username":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Username);
}
else
{
query = query.OrderByDescending(q => q.Username);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => !string.IsNullOrEmpty(o.Username) ? (o.Username.ToUpper().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => !string.IsNullOrEmpty(o.Username) ? (o.Username.ToUpper().Contains(columnSearchTerm.ToUpper())) : false;
usernamePredicate = usernamePredicate.Or(colPredicate);
anyUsername = true;
}
}
break;
case "Account":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Account);
}
else
{
query = query.OrderByDescending(q => q.Account);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => !string.IsNullOrEmpty(o.Account) ? (o.Account.ToUpper().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => !string.IsNullOrEmpty(o.Account) ? (o.Account.ToUpper().Contains(columnSearchTerm.ToUpper())) : false;
accountPredicate = accountPredicate.Or(colPredicate);
anyAccount = true;
}
}
break;
case "Type":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Type);
}
else
{
query = query.OrderByDescending(q => q.Type);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(UserViewModel o) => string.IsNullOrEmpty(o.Type) ? (o.Type.ToUpper().Contains(searchTerm.ToUpper())) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(UserViewModel o) => string.IsNullOrEmpty(o.Type) ? (o.Type.ToUpper().Contains(columnSearchTerm.ToUpper())) : false;
typePredicate = typePredicate.Or(colPredicate);
anyType = true;
}
}
break;
}
}
if (anyGlobal)
{
bool funcOr(UserViewModel l) => globalPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyId)
{
bool funcOr(UserViewModel l) => idPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyFirstName)
{
bool funcOr(UserViewModel l) => firstNamePredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyLastName)
{
bool funcOr(UserViewModel l) => lastNamePredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyEmail)
{
bool funcOr(UserViewModel l) => emailPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyUsername)
{
bool funcOr(UserViewModel l) => usernamePredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyAccount)
{
bool funcOr(UserViewModel l) => accountPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyType)
{
bool funcOr(UserViewModel l) => typePredicate(l);
query = query.Where(funcOr).AsQueryable();
}
#endregion
var startPage = (model.length == 0) ? 1 : model.start / model.length;
page.CurrentPage = startPage;
page.ItemsPerPage = model.length;
page.TotalPages = query.Count() / page.ItemsPerPage;
page.TotalDisplayItems = query.Count();
page.Items = query.Skip(startPage * model.length).Take(model.length).ToList();
return(page);
}
private Page <AccountViewModel> SearchAccountsQuery(DataTablesPageRequest model)
{
IQueryable <AccountViewModel> query = from account in _context.Accounts
join address in _context.Addresses on account.AddressId equals address.Id
select new AccountViewModel()
{
Id = account.Id,
CompanyName = account.CompanyName ?? "",
RegistrationNumber = account.RegistrationNumber ?? "",
VatNumber = account.VatNumber ?? "",
PhoneNumber = account.PhoneNumber ?? "",
Email = account.PhoneNumber ?? "",
Address = string.Format("{0} {1}, {2}, {3}",
address.Address1,
address.Address2,
address.City,
address.PostCode),
Limit = account.Limit,
Action = account.TermAccount ? account.ThirtyDayTermApproved
? "<a class='button is-danger' href='/Admin/AccountManager?id=" + account.Id + "&handler=Disable'>Disable</a>"
: "<a class='button is-success' href='/Admin/AccountManager?id=" + account.Id + "&handler=Approve'>Approve</a>"
: "",
AccountLink = "<a class='button is-primary' href='/Admin/AccountView?id=" + account.Id + "'>View</a>"
};
Page <AccountViewModel> page = new Page <AccountViewModel>
{
TotalItems = query.Count()
};
string orderByName = string.Empty;
Order orderBy = new Order();
if (model.order != null)
{
orderBy = model.order.First();
orderByName = model.columns[orderBy.column].name;
}
// Apply OR filters to IQueryable
#region ApplyFilters
string searchTerm = model.search.value;
Predicate <AccountViewModel> globalPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> idPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> companyNamePredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> registrationNumberPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> vatNumbnerPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> phoneNumberPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> emailPredicate = PredicateExtensions.False <AccountViewModel>();
Predicate <AccountViewModel> addressPredicate = PredicateExtensions.False <AccountViewModel>();
bool anyGlobal = false,
anyId = false,
anyCompanyName = false,
anyRegistrationNumber = false,
anyVatNumber = false,
anyPhoneNumber = false,
anyEmail = false,
anyAddress = false;
foreach (var column in model.columns)
{
string columnSearchTerm = column.search.value;
switch (column.name)
{
case "Id":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Id);
}
else
{
query = query.OrderByDescending(q => q.Id);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => o.Id.ToString().Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => o.Id.ToString().Contains(columnSearchTerm);
idPredicate = idPredicate.Or(colPredicate);
anyId = true;
}
}
break;
case "CompanyName":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.CompanyName);
}
else
{
query = query.OrderByDescending(q => q.CompanyName);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => !string.IsNullOrEmpty(o.CompanyName) ? o.CompanyName.ToUpper().ToString().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => !string.IsNullOrEmpty(o.CompanyName) ? o.CompanyName.ToUpper().ToString().Contains(columnSearchTerm.ToUpper()) : false;
companyNamePredicate = companyNamePredicate.Or(colPredicate);
anyCompanyName = true;
}
}
break;
case "RegistrationNumber":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.RegistrationNumber);
}
else
{
query = query.OrderByDescending(q => q.RegistrationNumber);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => !string.IsNullOrEmpty(o.RegistrationNumber) ? o.RegistrationNumber.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => !string.IsNullOrEmpty(o.RegistrationNumber) ? o.RegistrationNumber.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
registrationNumberPredicate = registrationNumberPredicate.Or(colPredicate);
anyRegistrationNumber = true;
}
}
break;
case "VatNumber":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.VatNumber);
}
else
{
query = query.OrderByDescending(q => q.VatNumber);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => !string.IsNullOrEmpty(o.VatNumber) ? o.VatNumber.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => !string.IsNullOrEmpty(o.VatNumber) ? o.VatNumber.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
vatNumbnerPredicate = vatNumbnerPredicate.Or(colPredicate);
anyVatNumber = true;
}
}
break;
case "PhoneNumber":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.PhoneNumber);
}
else
{
query = query.OrderByDescending(q => q.PhoneNumber);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => o.PhoneNumber.Contains(searchTerm);
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => o.PhoneNumber.Contains(columnSearchTerm);
phoneNumberPredicate = phoneNumberPredicate.Or(colPredicate);
anyPhoneNumber = true;
}
}
break;
case "Email":
if (column.name == orderByName)
{
if (orderBy?.dir == "asc")
{
query = query.OrderBy(q => q.Email);
}
else
{
query = query.OrderByDescending(q => q.Email);
}
}
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => !string.IsNullOrEmpty(o.Email) ? o.Email.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => !string.IsNullOrEmpty(o.Email) ? o.Email.ToUpper().Contains(columnSearchTerm.ToUpper()) : false;
emailPredicate = emailPredicate.Or(colPredicate);
anyEmail = true;
}
}
break;
case "Address":
if (column.searchable)
{
if (!string.IsNullOrEmpty(searchTerm))
{
bool globalp(AccountViewModel o) => !string.IsNullOrEmpty(o.Address) ? o.Address.ToUpper().Contains(searchTerm.ToUpper()) : false;
globalPredicate = globalPredicate.Or(globalp);
anyGlobal = true;
}
if (!string.IsNullOrEmpty(columnSearchTerm))
{
bool colPredicate(AccountViewModel o) => o.Address.ToUpper().Contains(columnSearchTerm.ToUpper());
addressPredicate = addressPredicate.Or(colPredicate);
anyAddress = true;
}
}
break;
}
}
if (anyGlobal)
{
bool funcOr(AccountViewModel l) => globalPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyId)
{
bool funcOr(AccountViewModel l) => idPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyCompanyName)
{
bool funcOr(AccountViewModel l) => companyNamePredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyRegistrationNumber)
{
bool funcOr(AccountViewModel l) => registrationNumberPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyVatNumber)
{
bool funcOr(AccountViewModel l) => vatNumbnerPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyPhoneNumber)
{
bool funcOr(AccountViewModel l) => phoneNumberPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyEmail)
{
bool funcOr(AccountViewModel l) => emailPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
if (anyAddress)
{
bool funcOr(AccountViewModel l) => addressPredicate(l);
query = query.Where(funcOr).AsQueryable();
}
#endregion
var startPage = (model.length == 0) ? 1 : model.start / model.length;
page.CurrentPage = startPage;
page.ItemsPerPage = model.length;
page.TotalPages = query.Count() / page.ItemsPerPage;
page.TotalDisplayItems = query.Count();
page.Items = query.Skip(startPage * model.length).Take(model.length).ToList();
return(page);
}
