/// <summary>
/// 查询多条记录
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <SaleOrderDto> > > GetPagesAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <SaleOrderDto> >();
try
{
var query = Db.Queryable <ErpSaleOrder, ErpShops>((eso, es) => new object[] { JoinType.Left, eso.ShopGuid == es.Guid })
.WhereIF(!string.IsNullOrEmpty(parm.guid), (eso, es) => eso.ShopGuid == parm.guid)
.WhereIF(parm.types != 0, (eso, es) => eso.ActivityTypes == parm.types)
.WhereIF(searchParm.saleType != 0, (eso, es) => eso.SaleType == searchParm.saleType)
.WhereIF(searchParm.activityTypes != 0, (eso, es) => eso.ActivityTypes == searchParm.activityTypes)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.etime),
(eso, es) => eso.AddDate >= Convert.ToDateTime(searchParm.btime) && eso.AddDate <= Convert.ToDateTime(searchParm.etime))
.OrderBy((eso, es) => eso.AddDate, OrderByType.Desc)
.Select((eso, es) => new SaleOrderDto()
{
Number = eso.Number,
ShopName = es.ShopName,
ActivityTypes = SqlFunc.ToString(eso.ActivityTypes),
SaleType = SqlFunc.ToString(eso.SaleType),
Counts = eso.Counts,
ActivityName = eso.ActivityName,
Money = eso.Money,
RealMoney = eso.RealMoney,
AddDate = eso.AddDate
})
.ToPage(parm.page, parm.limit);
//循环商品订单，查询订单下面的商品
var orderNumbers = query.Items?.Select(m => m.Number).ToList();
var orderGood = ErpSaleOrderGoodsDb.GetList(m => orderNumbers.Contains(m.OrderNumber));
foreach (var item in query.Items)
{
var list = new List <SaleOrderGoodsDto>();
foreach (var row in orderGood)
{
if (item.Number == row.OrderNumber)
{
var goodSku = ErpGoodsSkuDb.GetById(row.GoodsGuid);
list.Add(new SaleOrderGoodsDto()
{
Counts = row.Counts,
Code = goodSku.Code,
GoodsName = SysCodeDb.GetById(goodSku.BrankGuid).Name + SysCodeDb.GetById(goodSku.StyleGuid).Name
});
}
}
item.Goods = list;
}
res.success = true;
res.message = "获取成功！";
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
public JsonResult SaleOrderList(PageParm parm, AppSearchParm searchParm)
{
var res = _orderService.GetPagesAsync(parm, searchParm).Result;
var list = res.data.Items?.Select(m => new {
m.Number,
m.Counts,
m.RealMoney,
m.Goods,
AddDate = m.AddDate.ToString().Replace("/", "-").Replace("T", " ")
});
return(Json(new { statusCode = 200, msg = "success", count = res.data.TotalPages, data = list }));
}
/// <summary>
/// 查询营业额
/// </summary>
/// <returns></returns>
public Task <ApiResult <DayTurnover> > GetTurnover(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <DayTurnover>();
try
{
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1);
DateTime dayTime = Convert.ToDateTime(now.AddDays(1).ToShortDateString() + " 00:00:00");
//订单数
var orderSum = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Count();
//订单金额
var orderMoney = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.RealMoney);
//查询退单金额
var backMoney = Db.Queryable <ErpBackGoods>()
.Where(m => m.Status == 1)
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.BackMoney);
res.data = new DayTurnover()
{
OrderSum = orderSum,
Money = orderMoney - backMoney
};
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
/// <summary>
/// 查询多条记录
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <ShopSkuDto> > > GetPagesAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <ShopSkuDto> >();
try
{
var query = Db.Queryable <ErpShopSku, ErpShops, ErpGoodsSku>((t1, t2, t3) => new object[] {
JoinType.Left, t1.ShopGuid == t2.Guid,
JoinType.Left, t1.SkuGuid == t2.Guid
})
.WhereIF(!string.IsNullOrEmpty(parm.guid), (t1, t2, t3) => t1.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.brand), (t1, t2, t3) => t3.BrankGuid == searchParm.brand)
.OrderByIF(parm.orderType == 1, (t1, t2, t3) => t1.Sale, OrderByType.Desc)
.OrderBy((t1, t2, t3) => t1.Stock, OrderByType.Desc)
.Select((t1, t2, t3) => new ShopSkuDto()
{
Guid = t1.SkuGuid,
Code = t1.SkuCode,
BrankName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t3.BrankGuid).Select(g => g.Name),
StyleName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t3.StyleGuid).Select(g => g.Name),
SeasonName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t3.SeasonGuid).Select(g => g.Name),
Stock = t1.Stock,
Sale = t1.Sale
})
.ToPage(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
/// <summary>
/// 查询库存剩余数量和销售数量
/// 可根据店铺查询，日期，品牌
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <StockSaleNum> > > GetStockNumByShopAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <StockSaleNum> >();
try
{
var query = Db.Queryable <ErpShopSku, ErpGoodsSku>((t1, t2) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid })
.Where((t1, t2) => t1.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.brand), (t1, t2) => t2.BrankGuid == searchParm.brand)
.OrderByIF(parm.orderType == 1, (t1, t2) => t1.Sale, OrderByType.Desc)
.OrderByIF(parm.orderType == 2, (t1, t2) => t1.Stock, OrderByType.Desc)
.Select((t1, t2) => new StockSaleNum()
{
Guid = t2.Guid,
Code = t2.Code,
Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name),
Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name),
Stock = t1.Stock,
returnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid).Sum(g => g.ReturnCount)
}).ToPage(parm.page, parm.limit);
//根据日期查询
var guidList = query.Items.Select(m => m.Guid).ToList();
if (parm.types == 0)
{
//所有
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid);
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 1)
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
//本日
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.DateIsSame(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), dayTime));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 2)
{
//本月
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1);
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), d1, d2));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 3)
{
//自定义时间
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
/// <summary>
/// 获得列表 不包含订单下面的商品列表
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <SaleOrderDto> > > GetPagesNoGoodsAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <SaleOrderDto> >();
try
{
string beginTime = string.Empty, endTime = string.Empty;
if (!string.IsNullOrEmpty(parm.time))
{
var timeRes = Utils.SplitString(parm.time, '-');
beginTime = timeRes[0].Trim();
endTime = timeRes[1].Trim();
}
var query = Db.Queryable <ErpSaleOrder, ErpShops>((eso, es) => new object[] { JoinType.Left, eso.ShopGuid == es.Guid })
.WhereIF(!string.IsNullOrEmpty(parm.guid), (eso, es) => eso.ShopGuid == parm.guid)
.WhereIF(parm.types != 0, (eso, es) => eso.ActivityTypes == parm.types)
.WhereIF(searchParm.saleType != 0, (eso, es) => eso.SaleType == searchParm.saleType)
.WhereIF(searchParm.activityTypes != 0, (eso, es) => eso.ActivityTypes == searchParm.activityTypes)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.etime),
(eso, es) => eso.AddDate >= Convert.ToDateTime(beginTime) && eso.AddDate <= Convert.ToDateTime(endTime))
.OrderBy((eso, es) => eso.Number, OrderByType.Desc)
.OrderBy((eso, es) => eso.AddDate, OrderByType.Desc)
.Select((eso, es) => new SaleOrderDto()
{
Number = eso.Number,
ShopName = es.ShopName,
ActivityTypes = SqlFunc.ToString(eso.ActivityTypes),
SaleType = SqlFunc.ToString(eso.SaleType),
Counts = eso.Counts,
ActivityName = eso.ActivityName,
Money = eso.Money,
RealMoney = eso.RealMoney,
TheImage = eso.TheImage,
AddDate = eso.AddDate
})
.ToPage(parm.page, parm.limit);
foreach (var item in query.Items)
{
item.ActivityName = !string.IsNullOrEmpty(item.ActivityName) && item.ActivityName != "undefined" ? item.ActivityName : "无活动";
}
res.success = true;
res.message = "获取成功！";
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
public async Task <ApiResult <Page <SaleOrderDto> > > GetSaleOrderPages(PageParm parm, AppSearchParm searchParm)
{
return(await _orderService.GetPagesNoGoodsAsync(parm, searchParm));
}
public async Task <JsonResult> GetStockByShop(PageParm parm, AppSearchParm searchParm)
{
var res = await _inventoryService.GetStockNumByShopAsync(parm, searchParm);
return(Json(new { code = 0, msg = "success", count = res.data?.TotalItems, data = res.data?.Items }));
}
public Task <ApiResult <DayTurnover> > GetTurnover(PageParm parm, AppSearchParm searchParm)
{
return(_inventoryService.GetTurnover(parm, searchParm));
}
public JsonResult Inventory(PageParm parm, AppSearchParm searchParm)
{
var res = _inventoryService.GetStockNumByShopAsync(parm, searchParm).Result;
return(Json(new { statusCode = 200, msg = "success", count = res.data?.TotalPages, data = res.data?.Items }));
}
