public static Predicate <T> OrAll <T> (IEnumerable <Predicate <T> > conditions)
{
Predicate <T> result = PredicateExtensions.False <T>();
foreach (Predicate <T> cond in conditions)
{
result = result.Or <T>(cond);
}
return(result);
}
public void PredicateExtensionBuildsAndExpressionWithFalse()
{
const string expected = "i => ((i > 0) AndAlso False)";
Expression <Func <int, bool> > exp1 = i => i > 0;
Expression <Func <int, bool> > falseExp = PredicateExtensions.False <int>();
Expression <Func <int, bool> > andExp = exp1.And(falseExp);
var results = _testArray.Where(andExp.Compile());
Assert.AreEqual(0, results.Count());
Assert.AreEqual(expected, andExp.ToString());
}
public void PredicateExtensionBuildsOrExpressionWithFalseFromExpressionEnumerable()
{
const string expected = "i => (((((i > 0) OrElse (i < 5)) OrElse (i > -1)) OrElse (i < 6)) OrElse False)";
Expression <Func <int, bool> > exp1 = i => i > 0;
Expression <Func <int, bool> > exp2 = i => i < 5;
Expression <Func <int, bool> > exp3 = i => i > -1;
Expression <Func <int, bool> > exp4 = i => i < 6;
Expression <Func <int, bool> > falseExp = PredicateExtensions.False <int>();
Expression <Func <int, bool> > orExp = new[] { exp1, exp2, exp3, exp4, falseExp }.Or();
var results = _testArray.Where(orExp.Compile());
Assert.AreEqual(5, results.Count());
Assert.AreEqual(expected, orExp.ToString());
}
//获取后台订单列表
public static List <SOFullInfo> GetSOInfos(List <ListFilterField> FilterField, List <ListOrderField> OrderField
, int PageSize, int PageIndex, out long TotalRowCount, out AlertMessage alertMessage)
{
TotalRowCount = 0;
alertMessage = null;
//权限检查
if (!AUTH.PermissionCheck(ModuleInfo, ActionInfos.Where(i => i.Name == "List").FirstOrDefault(), out string Message))
{
alertMessage = new AlertMessage {
Message = Message, Type = AlertType.warning
};
return(null);
}
//查询列表
using (var EF = new EF())
{
var WxCustomerTypeID = CustomerType.FirstOrDefault(i => i.Name == "WxUser").ID;
IEnumerable <SOFullInfo> DataList = (from soInfo in EF.SalesOrders
join soCustomer in (
from wxCustomer in EF.SOCustomerInfos
join wxUser in EF.WxUserInfos on wxCustomer.CustomerID equals wxUser.ID
where wxCustomer.CustomerTypeID == WxCustomerTypeID
select new { wxCustomer, wxUser }
) on soInfo.ID equals soCustomer.wxCustomer.SOID
join soItems in EF.SalesOrderItems on soInfo.ID equals soItems.SOID
join goodsInfo in EF.GoodsInfos on soItems.GoodsID equals goodsInfo.ID
join goodsCounter in EF.GoodsCounter on soItems.CounterID equals goodsCounter.ID
join goodsSpec in EF.GoodsSpecs on goodsCounter.GoodsSpecID equals goodsSpec.ID into t1
from goodsSpec in t1.DefaultIfEmpty()
join soPay in EF.SOPayInfos on soInfo.ID equals soPay.SOID
join soReceipt in EF.SOReceiveInfos on soInfo.ID equals soReceipt.SOID
join storeInfo in EF.StoreInfos on soReceipt.StoreID equals storeInfo.ID
select new SOFullInfo
{
ID = soInfo.ID,
BillNO = soInfo.BillNO,
CreateTime = soInfo.CreateTime,
UpdateTime = soInfo.UpdateTime,
StateID = soInfo.StateID,
PayTypeID = soPay.PayTypeID,
CustomerID = soCustomer.wxCustomer.CustomerID,
CustomerName = soCustomer.wxUser.NickName,
GoodsID = goodsInfo.ID,
GoodsName = goodsInfo.Name,
GoodsTitle = goodsInfo.Title,
GoodsSpecID = goodsCounter.GoodsSpecID,
GoodsSpecValues = goodsSpec.SpecValues,
Quantity = soItems.Quantity,
Price = soItems.Price,
Amount = soItems.Amount,
Consignee = soReceipt.Consignee,
Phone = soReceipt.Phone,
ReceiveTypeID = soReceipt.ReceiveTypeID,
StoreID = soReceipt.StoreID,
StoreName = storeInfo.Name,
StoreTitle = storeInfo.Title,
Address = soReceipt.Address,
//AddressCheck = null,
});
//筛选
foreach (var item in FilterField)
{
if (item.Name == "BillNO" && item.Value.Count > 0)
{
var predicate = PredicateExtensions.False <SOFullInfo>();
foreach (var t in item.Value)
{
var KWPart = t.ToLower();
switch (item.CmpareMode)
{
case FilterCmpareMode.Equal:
predicate = predicate.Or(p => p.BillNO.ToLower() == KWPart);
break;
case FilterCmpareMode.Like:
predicate = predicate.Or(p => p.BillNO.ToLower().Contains(KWPart));
break;
}
}
DataList = DataList.Where(predicate.Compile());
}
else if (item.Name == "StateID" && item.Value.Count > 0)
{
var predicate = PredicateExtensions.False <SOFullInfo>();
predicate = predicate.Or(p => item.Value.Contains(p.StateID.ToString()));
DataList = DataList.Where(predicate.Compile());
}
else if (item.Name == "ReceiptTypeID" && item.Value.Count > 0)
{
var predicate = PredicateExtensions.False <SOFullInfo>();
predicate = predicate.Or(p => item.Value.Contains(p.ReceiveTypeID.ToString()));
DataList = DataList.Where(predicate.Compile());
}
}
//排序
if (OrderField.Count == 0)
{
DataList = DataList.OrderByDescending(i => i.ID);
}
else
{
foreach (var item in OrderField)
{
switch (item.Mode)
{
case OrderByMode.Asc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name)
select list;
break;
case OrderByMode.Desc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name) descending
select list;
break;
}
}
}
//分页
TotalRowCount = DataList.Count();
if (TotalRowCount == 0)
{
return(null);
}
int PageCount = (int)Math.Ceiling((double)TotalRowCount / PageSize);
if (PageIndex > PageCount)
{
PageIndex = PageCount;
}
var PageList = DataList.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList();
//补充
foreach (var item in PageList)
{
item.StateTitle = State.FirstOrDefault(i => i.ID == item.StateID).Title;
item.PayTypeTitle = PayType.FirstOrDefault(i => i.ID == item.PayTypeID).Title;
item.ReceiveTypeTitle = ReceiveType.FirstOrDefault(i => i.ID == item.ReceiveTypeID).Title;
}
//返回
return(PageList);
}
}
//获取用户列表
public static List<UserInfoList> GetUserInfos(List<ListFilterField> FilterField, List<ListOrderField> OrderField
, int PageSize, int PageIndex, out long TotalRowCount, out AlertMessage alertMessage)
{
TotalRowCount = 0;
alertMessage = null;
//权限检查
if (!AUTH.PermissionCheck(ModuleInfo, ActionInfos.Where(i => i.Name == "List").FirstOrDefault(), out string Message))
{
alertMessage = new AlertMessage { Message = Message, Type = AlertType.warning };
return null;
}
//查询列表
using (var EF = new EF())
{
IEnumerable<UserInfoList> DataList = from userInfos in EF.UserInfos
select new UserInfoList
{
ID = userInfos.ID,
Name = userInfos.Name,
StateID = userInfos.StateID,
UpdateTime = userInfos.UpdateTime
};
//筛选
foreach (var item in FilterField)
{
if (item.Name == "Name" && item.Value.Count > 0)
{
var predicate = PredicateExtensions.False<UserInfoList>(); //设置为False，所有and条件都应该放在or之后，如where (type=1 or type=14) and status==0
foreach (var t in item.Value)
{
var KWPart = t.ToLower();
switch (item.CmpareMode)
{
case FilterCmpareMode.Equal:
predicate = predicate.Or(p => p.Name.ToLower() == KWPart);
break;
case FilterCmpareMode.Like:
predicate = predicate.Or(p => p.Name.ToLower().Contains(KWPart));
break;
}
}
DataList = DataList.Where(predicate.Compile());
}
}
//排序
if (OrderField.Count == 0) { DataList = DataList.OrderByDescending(i => i.ID); }
else
{
foreach (var item in OrderField)
{
switch (item.Mode)
{
case OrderByMode.Asc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name)
select list;
break;
case OrderByMode.Desc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name) descending
select list;
break;
}
}
}
//分页
TotalRowCount = DataList.Count();
if (TotalRowCount == 0) { return null; }
int PageCount = (int)Math.Ceiling((double)TotalRowCount / PageSize);
if (PageIndex > PageCount) { PageIndex = PageCount; }
return DataList.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList();
}
}
//获取列表
public static List <StoreFullInfo> GetStoreInfos(List <ListFilterField> FilterField, List <ListOrderField> OrderField
, int PageSize, int PageIndex, out long TotalRowCount, out AlertMessage alertMessage)
{
TotalRowCount = 0;
alertMessage = null;
//权限检查
if (!AUTH.PermissionCheck(ModuleInfo, ActionInfos.Where(i => i.Name == "List").FirstOrDefault(), out string Message))
{
alertMessage = new AlertMessage {
Message = Message, Type = AlertType.warning
};
return(null);
}
//查询列表
using (var EF = new EF())
{
IEnumerable <StoreFullInfo> DataList = (from storeInfo in EF.StoreInfos
join fileInfo in EF.FileInfos on storeInfo.LogoFileID equals fileInfo.ID into temp1
from fileInfo in temp1.DefaultIfEmpty()
join fileExtName in EF.FileExtName on fileInfo.ExtNameID equals fileExtName.ID into temp2
from fileExtName in temp2.DefaultIfEmpty()
select new StoreFullInfo
{
ID = storeInfo.ID,
Name = storeInfo.Name,
Title = storeInfo.Title,
Enabled = storeInfo.Enabled,
LogoFileID = storeInfo.LogoFileID,
FileGUID = fileInfo.GUID,
FileName = fileInfo.Name,
FileExtName = fileExtName.Name,
});
//筛选
foreach (var item in FilterField)
{
if (item.Name == "NameAndTitle" && item.Value.Count > 0)
{
var predicate = PredicateExtensions.False <StoreFullInfo>(); //设置为False，所有and条件都应该放在or之后，如where (type=1 or type=14) and status==0
foreach (var t in item.Value)
{
var KWPart = t.ToLower();
switch (item.CmpareMode)
{
case FilterCmpareMode.Equal:
predicate = predicate.Or(p => p.Name.ToLower() == KWPart || p.Title.ToLower() == KWPart);
break;
case FilterCmpareMode.Like:
predicate = predicate.Or(p => p.Name.ToLower().Contains(KWPart) || p.Title.ToLower().Contains(KWPart));
break;
}
}
DataList = DataList.Where(predicate.Compile());
}
}
//排序
if (OrderField.Count == 0)
{
DataList = DataList.OrderByDescending(i => i.ID);
}
else
{
foreach (var item in OrderField)
{
switch (item.Mode)
{
case OrderByMode.Asc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name)
select list;
break;
case OrderByMode.Desc:
DataList = from list in DataList
orderby OrderBy.GetPropertyValue(list, item.Name) descending
select list;
break;
}
}
}
//分页
TotalRowCount = DataList.Count();
if (TotalRowCount == 0)
{
return(null);
}
int PageCount = (int)Math.Ceiling((double)TotalRowCount / PageSize);
if (PageIndex > PageCount)
{
PageIndex = PageCount;
}
return(DataList.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList());
}
}
/// <summary>
/// 根据下拉框查询列表
/// </summary>
/// <param name="goodInfo"></param>
/// <returns></returns>
public JObject GetGoodList(vw_MallGoodMainInfo goodInfo, Pagination page)
{
using (var db = new RepositoryBase())
{
JObject ret = new JObject();
PaginationResult <List <vw_MallGoodMainInfo> > ResultPage = new PaginationResult <List <vw_MallGoodMainInfo> >();
var expression = PredicateExtensions.True <vw_MallGoodMainInfo>();
if (goodInfo != null)
{
if (!string.IsNullOrEmpty(goodInfo.GoodNo))
{
expression = expression.And(t => t.GoodNo == goodInfo.GoodNo.Trim());
}
if (goodInfo.GameId != null && goodInfo.GameId > 0)//游戏Id
{
expression = expression.And(t => t.GameId == goodInfo.GameId);
}
if (goodInfo.GameGroupId != null && goodInfo.GameGroupId > 0)//大区Id
{
expression = expression.And(t => t.GameGroupId == goodInfo.GameGroupId);
}
if (goodInfo.GameServerId != null && goodInfo.GameServerId > 0)//服务器Id
{
expression = expression.And(t => t.GameServerId == goodInfo.GameServerId);
}
if (!string.IsNullOrEmpty(goodInfo.ServerName) && goodInfo.ServerName.Contains("Across:"))
{
expression = expression.And(t => goodInfo.ServerName.Contains(t.GameServerId.ToString()));
}
if (goodInfo.GoodTypeId != null && goodInfo.GoodTypeId > 0) //类型Id
{
expression = expression.And(t => t.GoodTypeId == goodInfo.GoodTypeId);
}
if (!string.IsNullOrEmpty(goodInfo.Gender)) //性别
{
expression = expression.And(t => t.Gender == goodInfo.Gender);
}
if (goodInfo.HasQQFriend != null) //是否有QQ好友
{
expression = expression.And(t => t.HasQQFriend == goodInfo.HasQQFriend);
}
if (goodInfo.HasIdSealedRecord != null) //是否有封禁记录
{
expression = expression.And(t => t.HasIdSealedRecord == goodInfo.HasIdSealedRecord);
}
if (goodInfo.Stock != null && goodInfo.Stock > 0) //库存
{
expression = expression.And(t => t.Stock == goodInfo.Stock);
}
else
{
expression = expression.And(t => t.Stock > 0); //售完商品列表
}
if (!string.IsNullOrEmpty(goodInfo.GoodKeyWord)) //商品关键字
{
expression = expression.And(t => t.GoodTitle.Contains(goodInfo.GoodKeyWord));
}
if (goodInfo.Status != null && !string.IsNullOrEmpty(goodInfo.Status)) //商品状态
{
if (goodInfo.Status == "Processed") //当是已处理的时候分为两种状态
{
expression = expression.And(t => t.Status == "PutOnsale" || t.Status == "PutDownsale");
}
else
{
expression = expression.And(t => t.Status == goodInfo.Status);
}
}
else
{
expression = expression.And(t => t.Status == "PutOnsale");//默认不传的情况下查询所有的上架商品
}
if (goodInfo != null)
{
if (goodInfo.startTime != null) //开始时间
{
expression = expression.And(t => t.AddTime >= goodInfo.startTime);
}
if (goodInfo.endTime != null) //结束时间
{
expression = expression.And(t => t.AddTime <= goodInfo.endTime);
}
if (!string.IsNullOrEmpty(goodInfo.QQLv))//QQ等级 6to10
{
expression = expression.And(t => t.QQLv == goodInfo.QQLv);
}
if (goodInfo.MinPrice != null)//最低价格
{
expression = expression.And(t => t.GoodPrice >= goodInfo.MinPrice);
}
if (goodInfo.MaxPrice != null)//最高价格
{
expression = expression.And(t => t.GoodPrice <= goodInfo.MaxPrice);
}
if (goodInfo.ProfessionCodes != null && goodInfo.ProfessionCodes.Where(c => c != "1").Count() > 0)//1代表的是全职业的情况
{
Expression <Func <vw_MallGoodMainInfo, bool> > expression2 = PredicateExtensions.False <vw_MallGoodMainInfo>();
expression2 = expression2.Or(t => goodInfo.ProfessionCodes.Contains(t.ProfessionCode1))
.Or(t => goodInfo.ProfessionCodes.Contains(t.ProfessionCode2))
.Or(t => goodInfo.ProfessionCodes.Contains(t.ProfessionCode3));
expression = expression.And(expression2);
}
}
}
var query = db.FindList(page ?? Pagination.GetDefaultPagination("GoodNo"), out page, expression);
var Games = query.GroupBy(g => g.GameId).Select(g => new { Id = g.FirstOrDefault().GameId, Name = g.FirstOrDefault().GameName }).ToList();
ret.Add("Games", JToken.FromObject(Games));
ResultPage.datas = query.ToList();//暂时以GoodNo排序，以后做活。
if (page != null)
{
ResultPage.pagination = page;
}
ret.Add(ResultInfo.Result, true);
ret.Add(ResultInfo.Content, JToken.FromObject(ResultPage));
return(ret);
}
}
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 <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 <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);
}