public int searchCountByCondition(OrderInfoQuery t)
{
LayerDbContext context = new LayerDbContext();
var result = context.orderInfo.Where(x => x.id > 0);
result = string.IsNullOrEmpty(t.orderNo) ? result : result.Where(x => x.orderNo.Contains(t.orderNo));
result = string.IsNullOrEmpty(t.status) ? result : result.Where(x => x.status == t.status);
result = !t.createdDateBegin.HasValue ? result : result.Where(x => x.createdDate >= t.createdDateBegin);
result = !t.createdDateEnd.HasValue ? result : result.Where(x => x.createdDate <= t.createdDateEnd);
result = !t.receivedDateBegin.HasValue ? result : result.Where(x => x.receivedDate >= t.receivedDateBegin);
result = !t.receivedDateEnd.HasValue ? result : result.Where(x => x.receivedDate <= t.receivedDateEnd);
return(result.Count());
}
/// <summary>
/// 后台订单分页
/// </summary>
/// <param name="orderInfoquery"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagingDataSet <OrderInfo> GetPageOrderInfo(OrderInfoQuery orderInfoquery, int pageIndex, int pageSize)
{
int totalCount = 0;
int totalPage = 0;
string whereSql = string.Empty;
string orderBy = string.Empty;
HandleOrderByEunm(orderInfoquery.OrderInfoSortBy, out orderBy);
object pars = new object();
whereSql = HandleQueryBySqlable(orderInfoquery, out pars);
return(GetPageListByCache <long>(pageIndex, pageSize, out totalCount, out totalPage, whereSql, pars, orderBy, i => i.Orderid));
}
public List <OrderInfo> searchByCondition(Pager <List <OrderInfo> > pager, OrderInfoQuery t)
{
LayerDbContext context = new LayerDbContext();
int start = (pager.page - 1) * pager.recPerPage;
var result = context.orderInfo.Where(x => x.id > 0);
result = string.IsNullOrEmpty(t.orderNo) ? result : result.Where(x => x.orderNo.Contains(t.orderNo));
result = string.IsNullOrEmpty(t.status) ? result : result.Where(x => x.status == t.status);
result = !t.createdDateBegin.HasValue ? result : result.Where(x => x.createdDate >= t.createdDateBegin);
result = !t.createdDateEnd.HasValue ? result : result.Where(x => x.createdDate <= t.createdDateEnd);
result = !t.receivedDateBegin.HasValue ? result : result.Where(x => x.receivedDate >= t.receivedDateBegin);
result = !t.receivedDateEnd.HasValue ? result : result.Where(x => x.receivedDate <= t.receivedDateEnd);
result = result.OrderByDescending(x => x.orderNo);
result = result.Skip(start).Take(pager.recPerPage);
return(result.ToList());
}
/// <summary>
/// 管理订单
/// </summary>
/// <param name="keyword"></param>
/// <param name="type"></param>
/// <param name="mode"></param>
/// <param name="rights"></param>
/// <param name="sort"></param>
/// <param name="status"></param>
/// <param name="pay"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public IActionResult ManageOrders(KeyWordEnum keyword, OrderType?type, OrderLogisticsMode?mode, OrderRightsStatus?rights, OrderInfoSortBy?sort, OrderModifyStatus?status, OrderPayType?pay, int?pageIndex, int pageSize = 20)
{
var from = RequestHelper.GetQueryParams(Request);
ViewData["currentUser"] = UserContext.CurrentUser;
string keyvalue = from["keyvalue"];
OrderInfoQuery query = new OrderInfoQuery();
var isUse = from["isUse"];
var isCompleteComment = from["isCompleteComment"];
if (!string.IsNullOrWhiteSpace(isUse))
{
query.IsUse = isUse.AsBool();
}
if (!string.IsNullOrWhiteSpace(isCompleteComment))
{
query.IsCompleteComment = isCompleteComment.AsBool();
}
if (!string.IsNullOrWhiteSpace(keyvalue))
{
ViewData["Keyvalue"] = keyvalue;
switch (keyword)
{
case KeyWordEnum.Buyers:
query.Buyers = from["keyvalue"];
break;
case KeyWordEnum.BuyersPhone:
query.BuyersPhone = from["keyvalue"];
break;
case KeyWordEnum.OrderNumber:
query.OrderNumber = from["keyvalue"];
break;
case KeyWordEnum.WaybillNumber:
query.WaybillNumber = from["keyvalue"];
break;
case KeyWordEnum.OtherOrderNumber:
query.OtherOrderNumber = from["keyvalue"];
break;
}
}
if (sort.HasValue)
{
query.OrderInfoSortBy = sort.Value;
ViewData["Sort"] = query.OrderInfoSortBy;
}
decimal result = decimal.Zero;
if (decimal.TryParse(from["realPriceLowerLimit"], out result))
{
query.RealPriceLowerLimit = from["realPriceLowerLimit"].AsDecimal();
ViewData["RealPriceLowerLimit"] = query.RealPriceLowerLimit;
}
if (decimal.TryParse(from["realPriceUpperLimit"], out result))
{
query.RealPriceUpperLimit = from["realPriceUpperLimit"].AsDecimal();
ViewData["RealPriceUpperLimit"] = query.RealPriceUpperLimit;
}
DateTime dateResult = DateTime.MinValue;
if (DateTime.TryParse(from["orderTimeLowerLimit"], out dateResult))
{
query.OrderTimeLowerLimit = dateResult;
ViewData["OrderTimeLowerLimit"] = query.OrderTimeLowerLimit;
}
if (DateTime.TryParse(from["orderTimeUpperLimit"], out dateResult))
{
query.OrderTimeUpperLimit = dateResult;
ViewData["OrderTimeUpperLimit"] = query.OrderTimeUpperLimit;
}
if (!string.IsNullOrWhiteSpace(query.OrderNumber))
{
ViewData["OrderNumber"] = query.OrderNumber;
}
if (!string.IsNullOrWhiteSpace(query.WaybillNumber))
{
ViewData["WaybillNumber"] = query.WaybillNumber;
}
Dictionary <int, string> keywordValues = Utility.GetDictionaryByEnumMemberInfo(typeof(KeyWordEnum));
ViewData["Keyword"] = new SelectList(keywordValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.KeyWordEnum);
//订单类型
if (type.HasValue)
{
query.OrderType = type;
}
Dictionary <int, string> typeValues = Utility.GetDictionaryByEnumMemberInfo(typeof(OrderType));
ViewData["Type"] = new SelectList(typeValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.OrderType);
//维权方式
if (rights.HasValue)
{
query.OrderRightsStatus = rights;
}
Dictionary <int, string> rightsValues = Utility.GetDictionaryByEnumMemberInfo(typeof(OrderRightsStatus));
ViewData["Rights"] = new SelectList(rightsValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.OrderRightsStatus);
//物流方式
if (mode.HasValue)
{
query.OrderType = type;
}
Dictionary <int, string> logisticsValues = Utility.GetDictionaryByEnumMemberInfo(typeof(OrderLogisticsMode));
ViewData["Logistics"] = new SelectList(logisticsValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.OrderLogisticsMode);
if (status.HasValue)
{
query.OrderInfoStatus = status;
ViewData["StatusValue"] = (int)status.Value;
}
else
{
ViewData["StatusValue"] = 0;
}
Dictionary <int, string> statusValues = Utility.GetDictionaryByEnumMemberInfo(typeof(OrderModifyStatus));
ViewData["Status"] = new SelectList(statusValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.OrderInfoStatus);
if (pay.HasValue)
{
query.OrderPayType = pay;
}
Dictionary <int, string> payValues = Utility.GetDictionaryByEnumMemberInfo(typeof(OrderPayType));
ViewData["Pay"] = new SelectList(payValues.Select(w => new { text = w.Value, value = w.Key }), "value", "text", query.OrderPayType);
Dictionary <bool, string> commentValues = new Dictionary <bool, string> {
{ true, "已完成评价" }, { false, "未完成评价" }
};
ViewData["IsCompleteComment"] = new SelectList(commentValues.Select(w => new { text = w.Value, value = w.Key.ToString().ToLower() }), "value", "text", query.IsCompleteComment);
Dictionary <bool, string> useValues = new Dictionary <bool, string> {
{ true, "已使用优惠券" }, { false, "未使用优惠券" }
};
ViewData["IsUse"] = new SelectList(useValues.Select(w => new { text = w.Value, value = w.Key.ToString().ToLower() }), "value", "text", query.IsUse);
pageIndex = pageIndex ?? 1;
PagingDataSet <OrderInfo> paging = orderInfoService.GetPageOrderInfo(query, pageIndex.Value, pageSize);
if (pageIndex > 1 && (paging == null || paging.Count() == 0))
{
paging = orderInfoService.GetPageOrderInfo(query, pageSize, pageIndex.Value - 1);
}
return(View(paging));
}
public ActionResult search(Pager <List <OrderInfo> > pager, OrderInfoQuery condition)
{
ViewBag.condition = condition;
pager = service.searchByCondition(pager, condition);
return(View("Index", pager));
}
/// <summary>
/// 后台订单分页
/// </summary>
/// <param name="orderInfoquery"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagingDataSet <OrderInfo> GetPageOrderInfo(OrderInfoQuery orderInfoquery, int pageIndex, int pageSize)
{
return(orderInfoRepository.GetPageOrderInfo(orderInfoquery, pageIndex, pageSize));
}
public Pager <List <OrderInfo> > searchByCondition(Pager <List <OrderInfo> > pager, OrderInfoQuery condition)
{
Task <List <OrderInfo> > task = Task.Factory.StartNew(() => dao.searchByCondition(pager, condition));
pager.data = task.Result;
Task <int> countTask = Task.Factory.StartNew(() => dao.searchCountByCondition(condition));
pager.recTotal = countTask.Result;
return(pager);
}
/// <summary>
/// 处理SQL条件
/// </summary>
/// <param name="orderInfoQuery"></param>
/// <param name="pars"></param>
/// <returns></returns>
private string HandleQueryBySqlable(OrderInfoQuery orderInfoQuery, out object pars)
{
Dictionary <string, object> paramsDictionary = new Dictionary <string, object>();
var sqlTable = new Sqlable();
sqlTable.Sql = new StringBuilder();
if (!string.IsNullOrWhiteSpace(orderInfoQuery.Buyers))
{
sqlTable = sqlTable.Where("buyers_name = @Buyers");
paramsDictionary.Add("Buyers", orderInfoQuery.Buyers);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.BuyersPhone))
{
sqlTable = sqlTable.Where("buyers_phone = @BuyersPhone");
paramsDictionary.Add("BuyersPhone", orderInfoQuery.BuyersPhone);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.OrderNumber))
{
sqlTable = sqlTable.Where("order_number = @OrderNumber");
paramsDictionary.Add("OrderNumber", orderInfoQuery.OrderNumber);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.WaybillNumber))
{
sqlTable = sqlTable.Where("waybill_number = @WaybillNumber");
paramsDictionary.Add("WaybillNumber", orderInfoQuery.WaybillNumber);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.OtherOrderNumber))
{
sqlTable = sqlTable.Where("other_transaction_code = @OtherOrderNumber");
paramsDictionary.Add("OtherOrderNumber", orderInfoQuery.OtherOrderNumber);
}
if (orderInfoQuery.IsUse.HasValue)
{
sqlTable = sqlTable.Where("is_use = @IsUse");
paramsDictionary.Add("IsUse", orderInfoQuery.IsUse);
}
if (orderInfoQuery.IsCompleteComment.HasValue)
{
sqlTable = sqlTable.Where("is_complete_comment = @IsCompleteComment");
paramsDictionary.Add("IsCompleteComment", orderInfoQuery.IsCompleteComment);
}
if (orderInfoQuery.OrderType.HasValue)
{
sqlTable = sqlTable.Where("order_type = @OrderType");
paramsDictionary.Add("OrderType", (int)orderInfoQuery.OrderType.Value);
}
if (orderInfoQuery.OrderRightsStatus.HasValue)
{
sqlTable = sqlTable.Where("rights_status = @OrderRightsStatus");
paramsDictionary.Add("OrderRightsStatus", (int)orderInfoQuery.OrderRightsStatus.Value);
}
if (orderInfoQuery.OrderLogisticsMode.HasValue)
{
sqlTable = sqlTable.Where("logistics_type = @OrderLogisticsMode");
paramsDictionary.Add("OrderLogisticsMode", (int)orderInfoQuery.OrderLogisticsMode.Value);
}
if (orderInfoQuery.OrderInfoStatus.HasValue)
{
sqlTable = sqlTable.Where("status = @Status");
paramsDictionary.Add("Status", (int)orderInfoQuery.OrderInfoStatus.Value);
}
if (orderInfoQuery.OrderPayType.HasValue)
{
sqlTable = sqlTable.Where("pay_type = @PayType");
paramsDictionary.Add("PayType", (int)orderInfoQuery.OrderPayType.Value);
}
if (orderInfoQuery.RealPriceLowerLimit.HasValue)
{
sqlTable = sqlTable.Where("real_price >= @RealPriceLowerLimit");
paramsDictionary.Add("RealPriceLowerLimit", orderInfoQuery.RealPriceLowerLimit);
}
if (orderInfoQuery.RealPriceUpperLimit.HasValue)
{
sqlTable = sqlTable.Where("real_price <= @RealPriceUpperLimit");
paramsDictionary.Add("RealPriceUpperLimit", orderInfoQuery.RealPriceUpperLimit);
}
if (orderInfoQuery.OrderTimeLowerLimit.HasValue)
{
sqlTable = sqlTable.Where("dete_created >= @OrderTimeLowerLimit");
paramsDictionary.Add("OrderTimeLowerLimit", orderInfoQuery.OrderTimeLowerLimit.Value);
}
if (orderInfoQuery.OrderTimeUpperLimit.HasValue)
{
sqlTable = sqlTable.Where("dete_created <= @OrderTimeUpperLimit");
paramsDictionary.Add("OrderTimeUpperLimit", orderInfoQuery.OrderTimeUpperLimit.Value);
}
pars = paramsDictionary;
foreach (var item in sqlTable.Where)
{
sqlTable.Sql.Append(item);
}
return(sqlTable.Sql.ToString().TrimStart(" AND".ToCharArray()));
}