public ActionResult GetStatisticList(StatisticView.StatisticWhere para) { #region 获取符合条件商品 var shProducts = new SqlHelper <CsProducts>(); var shParts = new SqlHelper <CsParts>(); if (!para.ProductName.IsNullOrEmpty()) { shProducts.AddWhere(CsProductsEnum.ProductName, para.ProductName, RelationEnum.Like); shParts.AddWhere(CsPartsEnum.PartName, para.ProductName, RelationEnum.Like); } if (para.ProductType != 0) { shProducts.AddWhere(CsProductsEnum.ProductType, para.ProductType); } if (para.PartType != 0) { shParts.AddWhere(CsPartsEnum.PartType, para.PartType); } var products = shProducts.Select(); var parts = shParts.Select(); var coms = products.Select(x => x.ProductId).Union(parts.Select(x => x.PartId)); if (!coms.Any()) { return(Json(new ArrayList())); } #endregion #region 获取未发货订单数量 var sh = new SqlHelper <StatisticView.StatisticList>("CsOrderDetail") { Alia = "cod" }; sh.AddJoin(JoinEnum.LeftJoin, "CsOrder", "co", "OrderId", "OrderId", $" AND ProductId IN ({string.Join(",", coms)})"); sh.AddShow("OrderState,ProductId,co.OrderId,OrderDate,(ProductNumber * co.OrderCopies) as ProductNumber,ChoseType,co.OrderCopies"); sh.AddWhere($" AND OrderState IN ({OrderState.支付成功.GetHashCode()},{OrderState.配货中.GetHashCode()})"); sh.AddWhere(CsOrderEnum.RowStatus, RowStatus.效.GetHashCode()); var orders = sh.Select(); #endregion #region 整合数据 var data = new List <StatisticView.StatisticList>(); foreach (var com in coms) { var total = orders .Where(x => x.ProductId == com) .Sum(x => x.ProductNumber); if (total <= 0) { continue; } var product = products.FirstOrDefault(x => x.ProductId == com); var part = parts.FirstOrDefault(x => x.PartId == com); var stock = orders .Where(x => x.ProductId == com && x.OrderState == OrderState.配货中.GetHashCode()) .Sum(x => x.ProductNumber); var unStork = orders .Where(x => x.ProductId == com && x.OrderState == OrderState.支付成功.GetHashCode()) .Sum(x => x.ProductNumber); data.Add(com < 10000 ? new StatisticView.StatisticList { ProductId = product?.ProductId, ProductName = product?.ProductName.ShowNullOrEmpty(), ProductType = ((ProductType?)product?.ProductType).ShowNullOrEmpty("未知类型"), Total = $"{total} 只 / 计 {total * product?.ProductWeight} 斤", Stock = $"{stock} 只 / 计 {stock * product?.ProductWeight} 斤", UnStork = $"{unStork} 只 / 计 {unStork * product?.ProductWeight} 斤", UnStorkInt = unStork } : new StatisticView.StatisticList { ProductId = part?.PartId, ProductName = part?.PartName.ShowNullOrEmpty(), ProductType = ((PartType?)part?.PartType).ShowNullOrEmpty("未知类型"), Total = $"{total} 只 / 计 {total * part?.PartWeight} 斤", Stock = $"{stock} 只 / 计 {stock * part?.PartWeight} 斤", UnStork = $"{unStork} 只 / 计 {unStork * part?.PartWeight} 斤", UnStorkInt = unStork }); } #endregion return(Json(data.OrderByDescending(x => x.UnStorkInt))); }
/// <summary> /// 获取当前条件的列表(用于分页查询和导出) /// </summary> /// <param name="para">条件参数</param> /// <param name="isPage">是否分页</param> /// <returns></returns> private PageInfo <CsOrderView.CsOrderPage> GetList(CsOrderView.CsOrderWhere para, bool isPage = true) { var sh = new SqlHelper <CsOrderView.CsOrderPage>("CsOrder") { PageConfig = new PageConfig { PageIndex = isPage ? para.CurrentPage : 0, PageSize = isPage ? PageSize : 0, PageSortField = "co." + CsOrderEnum.OrderId, SortEnum = SortEnum.Desc }, Alia = "co" }; sh.AddShow("co." + CsOrderEnum.OrderId); sh.AddShow(CsOrderEnum.OrderAddress); sh.AddShow(CsOrderEnum.OrderNumber); sh.AddShow("co." + CsOrderEnum.UserId); sh.AddShow(CsOrderEnum.TotalMoney); sh.AddShow(CsOrderEnum.IsInvoice); sh.AddShow(CsOrderEnum.OrderRemarks); sh.AddShow(CsOrderEnum.DiscountMoney); sh.AddShow(CsOrderEnum.ActualMoney); sh.AddShow(CsOrderEnum.OrderDate); sh.AddShow(CsOrderEnum.OrderState); sh.AddShow(CsOrderEnum.RowStatus); sh.AddShow(CsOrderEnum.DeleteDate); sh.AddShow(CsOrderEnum.DeleteDescribe); sh.AddShow(CsUsersEnum.UserName); sh.AddShow(CsUsersEnum.UserPhone); sh.AddShow(CsUsersEnum.UserSex); sh.AddShow(CsOrderEnum.SendAddress); sh.AddShow(CsOrderEnum.BillWeight); sh.AddShow(CsOrderEnum.TotalWeight); sh.AddShow(CsOrderEnum.CargoNumber); sh.AddShow(CsOrderEnum.OrderCopies); sh.AddJoin(JoinEnum.LeftJoin, "CsUsers", "cu", "UserId", "UserId"); if (para.RowStatus > -1) { sh.AddWhere(CsOrderEnum.RowStatus, para.RowStatus); } //if (para.ActualStart > 0) // sh.AddWhere(CsOrderEnum.ActualMoney, para.ActualStart, RelationEnum.GreaterEqual); //if (para.ActualEnd > 0) // sh.AddWhere(CsOrderEnum.ActualMoney, para.ActualEnd, RelationEnum.LessEqual); //if (para.DiscountStart > 0) // sh.AddWhere(CsOrderEnum.DiscountMoney, para.DiscountStart, RelationEnum.GreaterEqual); //if (para.DiscountEnd > 0) // sh.AddWhere(CsOrderEnum.DiscountMoney, para.DiscountEnd, RelationEnum.LessEqual); if (para.TotalStart > 0) { sh.AddWhere(CsOrderEnum.TotalMoney, para.TotalStart, RelationEnum.GreaterEqual); } if (para.TotalEnd > 0) { sh.AddWhere(CsOrderEnum.TotalMoney, para.TotalEnd, RelationEnum.LessEqual); } if (!para.OrderId.IsNullOrEmpty()) { sh.AddWhere(CsOrderEnum.OrderNumber, para.OrderId, RelationEnum.Like); } if (para.Status > -1) { sh.AddWhere(CsOrderEnum.OrderState, para.Status); } if (!para.UserName.IsNullOrEmpty()) { if (para.UserName.ToInt() > 0) { sh.AddWhere("cu." + CsUsersEnum.UserId, para.UserName.ToInt()); } else { sh.AddWhere("cu." + CsUsersEnum.UserName, para.UserName, RelationEnum.Like); } } if (!para.UserPhone.IsNullOrEmpty()) { sh.AddWhere("cu." + CsUsersEnum.UserPhone, para.UserPhone, RelationEnum.Like); } //#region 订单来源条件 //if (!para.OrderSource.IsNullOrEmpty() && para.OrderSource.ToInt() == ChoseType.套餐.GetHashCode()) // 电商代发 //{ // sh.AddJoin(JoinEnum.LeftJoin, "CsOrderDetail", "cod", "OrderId", "OrderId", $" AND cod.ChoseType = {ChoseType.套餐.GetHashCode()} "); //} //#endregion if (para.Time.Count > 0) { if (!para.Time[0].IsNullOrEmpty()) { sh.AddWhere(CsOrderEnum.OrderDate, para.Time[0], RelationEnum.GreaterEqual); } if (!para.Time[1].IsNullOrEmpty()) { sh.AddWhere(CsOrderEnum.OrderDate, para.Time[1], RelationEnum.LessEqual); } } if (!para.IsInvoice.IsNullOrEmpty()) { sh.AddWhere(CsOrderEnum.IsInvoice, para.IsInvoice.ToInt()); } if (!para.OrderAddress.IsNullOrEmpty()) { sh.AddWhere(CsOrderEnum.OrderAddress, para.OrderAddress, RelationEnum.Like); } return(new PageInfo <CsOrderView.CsOrderPage> { Data = sh.Select().ToList(), Total = sh.Total, Sql = sh.SqlString.ToString() }); }
public ActionResult GetCsPricePage(CsPriceView.CsPriceWhere para) { var sh = new SqlHelper <CsPriceView.CsPricePage>("CsPrice") { Alia = "price", PageConfig = new PageConfig { PageSize = PageSize, SortEnum = SortEnum.Desc, PageSortField = CsPriceEnum.PriceId.ToString(), PageIndex = para.CurrentPage } }; sh.AddShow(CsPriceEnum.PriceNumber); sh.AddShow("price." + CsPriceEnum.ProductId); sh.AddShow(CsPriceEnum.PriceDate); sh.AddShow(CsPriceEnum.PriceId); sh.AddShow(CsProductsEnum.ProductName); sh.AddShow(CsProductsEnum.ProductType); sh.AddShow(CsProductsEnum.ProductNumber); sh.AddShow(CsProductsEnum.ProductPrice + " AS CurrentPrice"); sh.AddJoin(JoinEnum.LeftJoin, "CsProducts", "product", "ProductId", "ProductId"); if (!para.ProductName.IsNullOrEmpty()) { sh.AddWhere(CsProductsEnum.ProductName, para.ProductName, RelationEnum.Like); } if (para.PriceStart > 0) { sh.AddWhere(CsPriceEnum.PriceNumber, para.PriceStart, RelationEnum.GreaterEqual); } if (para.PriceEnd > 0) { sh.AddWhere(CsPriceEnum.PriceNumber, para.PriceEnd, RelationEnum.LessEqual); } if (para.ProductType != 0) { sh.AddWhere(CsProductsEnum.ProductType, para.ProductType); } if (para.Time.Count > 0) { if (!para.Time[0].IsNullOrEmpty()) { sh.AddWhere(CsPriceEnum.PriceDate, para.Time[0], RelationEnum.GreaterEqual); } if (!para.Time[1].IsNullOrEmpty()) { sh.AddWhere(CsPriceEnum.PriceDate, para.Time[1], RelationEnum.LessEqual); } } var list = sh.Select(); return(Json(new { data = list.Select(x => new CsPriceView.CsPricePage { ProductName = $"{x.ProductName}( {x.ProductNumber} )", CurrentPrice = "¥ " + x.CurrentPrice.ToDecimal().ToString("N2"), PriceNumber = "¥ " + x.PriceNumber.ToDecimal().ToString("N2"), PriceDate = x.PriceDate.ToDate().ToString("yyyy-M-d HH:mm:ss"), PriceId = x.PriceId, ProductType = ((ProductType)x.ProductType.ToInt()).ToString() }), total = sh.Total, sql = sh.SqlString.ToString() })); }