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