public async Task <ApiResult <Page <Bbs_Answer> > > GetPageUser(PageParm param)
{
var res = new ApiResult <Page <Bbs_Answer> >()
{
statusCode = (int)ApiEnum.Error
};
try
{
res.data = await Db.Queryable <Bbs_Answer, Member, Member_Group>((b, m, g) => new
JoinQueryInfos(JoinType.Inner, b.UserGuid == m.Guid
, JoinType.Inner, m.Grade == g.Guid))
.WhereIF(!string.IsNullOrEmpty(param.guid), (b, m, g) => b.QuestionGuid == param.guid) //问题
.OrderByIF(param.attr == 1, (b, m, g) => b.AddTime, OrderByType.Desc) //热门排序
.OrderBy((b, m, g) => b.IsAdopt, OrderByType.Desc)
.Select((b, m, g) => new Bbs_Answer()
{
Guid = b.Guid,
IsAdopt = b.IsAdopt,
UserGuid = b.UserGuid,
NickName = m.NickName,
HeadPic = m.HeadPic,
GroupName = g.Name,
Content = b.Content,
AddTime = b.AddTime
})
.ToPageAsync(param.page, param.limit);
res.statusCode = (int)ApiEnum.Status;
}
catch (System.Exception ex)
{
res.message = ex.Message;
}
return(res);
}
public CloudFile GetList(PageParm parm)
{
var model = new CloudFile()
{
Code = 200
};
try
{
var query = Db.Queryable <CmsImage>()
.WhereIF(parm.where != "/", m => m.ImgBig.Contains(parm.where))
.OrderBy(m => m.AddDate, OrderByType.Desc)
.ToPageAsync(parm.page, parm.limit);
var fileList = new List <ListInfo>();
if (query.Result.TotalItems != 0)
{
foreach (var item in query.Result.Items)
{
fileList.Add(new ListInfo()
{
Name = item.ImgBig,
Size = item.ImgSize,
Type = item.ImgType,
Time = item.AddDate
});
}
}
model.list = fileList;
}
catch (Exception ex)
{
model.Message = ApiEnum.Error.GetEnumText() + ex.Message;
model.Code = (int)ApiEnum.Error;
}
return(model);
}
/// <summary>
/// 获得列表
/// </summary>
/// <returns></returns>
public async Task <ApiResult <Page <SysOrganize> > > GetPagesAsync(PageParm parm)
{
var res = new ApiResult <Page <SysOrganize> >();
try
{
using (Db)
{
var query = Db.Queryable <SysOrganize>()
.WhereIF(!string.IsNullOrEmpty(parm.key), m => m.ParentGuidList.Contains(parm.key))
.OrderBy(m => m.Sort).ToPageAsync(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = await query;
}
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
Logger.Default.ProcessError((int)ApiEnum.Error, ex.Message);
}
return(await Task.Run(() => res));
}
/// <summary>
/// 分页
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <Page <GoodsSkuDto> > > GetPagesAsync(PageParm parm)
{
var res = new ApiResult <Page <GoodsSkuDto> >();
try
{
var query = Db.Queryable <ErpGoodsSku>()
.Where(m => !m.IsDel)
.WhereIF(parm.types == 1, m => m.StockSum > 0)
.WhereIF(!string.IsNullOrEmpty(parm.key), m => m.Code.Contains(parm.key))
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.BrankGuid == parm.guid)
.Select(m => new GoodsSkuDto()
{
Guid = m.Guid,
Code = m.Code,
BrankName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == m.BrankGuid).Select(g => g.Name),
StyleName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == m.StyleGuid).Select(g => g.Name),
SeasonName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == m.SeasonGuid).Select(g => g.Name),
SalePrice = m.SalePrice,
DisPrice = m.DisPrice,
StockSum = m.StockSum,
SaleSum = m.SaleSum,
AddDate = m.AddDate
})
.OrderBy(m => m.AddDate, OrderByType.Desc).ToPageAsync(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = await query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 分页
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <Page <ErpAppSetting> > > GetPagesAsync(PageParm parm)
{
var res = new ApiResult <Page <ErpAppSetting> >();
try
{
using (Db)
{
var query = Db.Queryable <ErpAppSetting>()
.Where(m => !m.IsDel)
.OrderBy(m => m.UpdateDate, OrderByType.Desc)
.ToPageAsync(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = await query;
}
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(await 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>
/// <param name="where"></param>
/// <param name="parm"></param>
/// <returns></returns>
public PagedInfo <T> GetPages(Expression <Func <T, bool> > where, PageParm parm)
{
var source = Db.Queryable <T>().Where(where);
return(source.ToPage(parm));
}
public async Task <IActionResult> GetPages([FromQuery] PageParm parm)
{
var res = await _adminService.GetPagesAsync(parm);
return(Ok(new { code = 0, msg = "success", count = res.data.TotalItems, data = res.data.Items }));
}
public async Task <IActionResult> GetPages([FromQuery] PageParm parm)
{
return(Ok(await _tagService.GetPagesAsync(parm, m => !m.IsDel, m => m.FirstLetter, DbOrderEnum.Asc)));
}
public async Task <ApiResult <IEnumerable <ProjectViewModel> > > List(int pageIndex, string Title, string ProjectTypeID, int?State)
{
var res = new ApiResult <IEnumerable <ProjectViewModel> >()
{
statusCode = (int)ApiEnum.Status
};
if (pageIndex == 0)
{
pageIndex = 1;
}
PageParm page = new PageParm(pageIndex);
FilterDefinition <ProjectInfo> filter = FilterDefinition <ProjectInfo> .Empty;
var list = new List <FilterDefinition <ProjectInfo> >();
if (!string.IsNullOrWhiteSpace(Title))
{
list.Add(Builders <ProjectInfo> .Filter.Where(s => s.Names.Contains(Title)));
}
if (!string.IsNullOrWhiteSpace(ProjectTypeID))
{
list.Add(Builders <ProjectInfo> .Filter.Eq("ProjectTypeID", ProjectTypeID));
}
if (State != null && State != 0)
{
list.Add(Builders <ProjectInfo> .Filter.Eq("State", State));
}
list.Add(Builders <ProjectInfo> .Filter.Where(m => m._id != null));
filter = Builders <ProjectInfo> .Filter.And(list);
long c = 0;
var list1 = Projectdb.GetList("Projects", filter, page.PageIndex, page.PageSize, out c);
List <ProjectViewModel> list2 = new List <ProjectViewModel>();
if (list1 != null)
{
foreach (var item in list1)
{
list2.Add(new ProjectViewModel
{
ID = C.String(item._id),
Names = item.Names,
URL = item.URL,
Remarks = item.Remarks,
State = item.State,
ProjectTypeName = ProjectTypedb.Get("ProjectType", item.ProjectTypeID).Names,
ProductionDate = Utility.GetDateFormat(item.ProductionDate),
CompletionDate = Utility.GetDateFormat(item.CompletionDate),
AddDate = Utility.GetDateFormat(item.AddDate),
});
}
res.success = true;
res.data = list2;
res.index = pageIndex;
res.count = C.Int(c);
res.size = page.PageSize;
res.pages = C.Int(c) / page.PageSize + 1;
}
else
{
res.success = false;
res.statusCode = (int)ApiEnum.Status;
}
return(await Task.Run(() => res));
}
public async Task <JsonResult> GetPages(PageParm parm)
{
var res = await _sysOrganizeService.GetPagesAsync(parm);
return(Json(new { code = 0, msg = "success", count = res.data.TotalItems, data = res.data.Items }));
}
public async Task <JsonResult> GetPages(PageParm parm)
{
var where = new Where <SettleOrder>();
where.And(d => 1 == 1);
string order_id = Request.Query["order_id"].FirstOrDefault();
string plat_order_id = Request.Query["plat_order_id"].FirstOrDefault();
string mch_id = Request.Query["mch_id"].FirstOrDefault();
string status = Request.Query["status"].FirstOrDefault();
string notify_status = Request.Query["notify_status"].FirstOrDefault();
string create_time = Request.Query["create_time"].FirstOrDefault();
string field = Request.Query["field"].FirstOrDefault();
string order = Request.Query["order"].FirstOrDefault();
if (!string.IsNullOrEmpty(order_id))
{
where.And(d => d.Order_id == order_id.SqlFilters());
}
if (!string.IsNullOrEmpty(plat_order_id))
{
where.And(d => d.Plat_order_id == plat_order_id.SqlFilters());
}
if (!string.IsNullOrEmpty(status))
{
where.And(d => d.Status == Convert.ToInt32(status));
}
if (!string.IsNullOrEmpty(mch_id))
{
where.And(d => d.Mch_id == Convert.ToInt32(mch_id));
}
if (!string.IsNullOrEmpty(notify_status))
{
where.And(d => d.Notify_status == Convert.ToInt32(notify_status));
}
if (!string.IsNullOrEmpty(create_time))
{
string[] date = create_time.Split("-");
int dtStart = MyParse.ParseTime(date[0]).ToTimeStamp();
int dtEnd = MyParse.ParseTime(date[1]).ToTimeStamp();
where.And(d => d.Create_time >= dtStart && d.Create_time <= dtEnd);
}
parm.whereClip = where;
OrderByClip orderClip = new OrderByClip("id", OrderByOperater.DESC);
if (!string.IsNullOrEmpty(field))
{
if (order.ToLower() == "asc")
{
orderClip = new OrderByClip(field.SqlFilters(), OrderByOperater.ASC);
}
else
{
orderClip = new OrderByClip(field.SqlFilters(), OrderByOperater.DESC);
}
}
parm.orderByClip = orderClip;
var res = await SettleOrderBll._.GetPagesAsync(parm);
return(Json(new { code = 0, msg = "success", count = res.data.TotalItems, data = res.data.Items }));
}
/// <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);
if (goodSku != null)
{
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 async Task <ApiResult <Page <WxMaterial> > > GetList(PageParm parm)
{
return(await _meterialService.GetPageList(parm));
}
public async Task <ApiResult <Page <ErpSupplier> > > GetSupplierList(PageParm parm)
{
return(await _supplierService.GetPagesAsync(parm));
}
public async Task <JsonResult> GetGoodsPages(PageParm parm)
{
var res = await _purchaseGoodsService.GetPagesAsync(parm);
return(Json(new { code = 0, msg = "success", count = res.data.Items.Count, data = res.data.Items }));
}
public async Task <JsonResult> GetPages(PageParm parm)
{
var res = await _settingService.GetPagesAsync(parm, m => !m.IsDel, m => m.UpdateDate, DbOrderEnum.Desc);
return(Json(new { code = 0, msg = "success", count = res.data.TotalItems, data = res.data.Items }));
}
/// <summary>
/// 主播财务报表分页信息
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public List <IncomeTemplateModel> GetAnchorReportPage(PageParm parm, ref int totalCount, ref IncomeTemplateModel sumModel)
{
var res = new List <IncomeTemplateModel>();
try
{
if (parm == null)
{
parm = new PageParm();
}
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(parm.where))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(parm.where);
}
using (var db = GetSqlSugarDB(DbConnType.QPAnchorRecordDB))
{
var query = db.Queryable <SysIncomeEntity, SysAnchor, SysAnchorInfoEntity, SysShopAnchorEntity, SysAnchorRebateEntity>((it, st, at, ot, rt) =>
new object[] {
JoinType.Left, it.AnchorID == st.id,
JoinType.Left, st.id == at.aid,
JoinType.Left, st.id == ot.AnchorID,
JoinType.Left, st.id == rt.AnchorID
})
.Where((it, st) => it.opdate >= Convert.ToDateTime(dic["startTime"]) && it.opdate < Convert.ToDateTime(dic["endTime"]))
.WhereIF(dic.ContainsKey("Name") && !string.IsNullOrEmpty(dic["Name"].ToString()), (it, st) => st.anchorName.Contains(dic["Name"].ToString()) || st.nickName.Contains(dic["Name"].ToString()))
.WhereIF(dic.ContainsKey("isColletCode") && !string.IsNullOrEmpty(dic["isColletCode"].ToString()), (it, st, at) => st.isColletCode == dic["isColletCode"].ToString())
.WhereIF(dic.ContainsKey("ShopID") && Convert.ToInt32(dic["ShopID"]) != -1, (it, st, at, ot) => ot.ShopID == Convert.ToInt32(dic["ShopID"]))
;
sumModel = query.Clone().Select((it, st, at) => new IncomeTemplateModel
{
tip_income = SqlFunc.AggregateSum(it.tip_income),
agent_income = SqlFunc.AggregateSum(it.agent_income),
Platform_income = SqlFunc.AggregateSum(it.Platform_income),
hour_income = SqlFunc.AggregateSum(it.hour_income),
agentHour_income = SqlFunc.AggregateSum(it.agentHour_income),
livetime = SqlFunc.AggregateSum(it.livetime),
}).First();
if (sumModel == null)
{
sumModel = new IncomeTemplateModel();
}
res = query.GroupBy((it, st, at, ot, rt) => new { it.AnchorID, st.anchorName, st.nickName, at.agentGold, rt.IsWorkHours, rt.LiveTime, rt.Salary, rt.TipRebate, rt.HourRebate, rt.GiftAmount })
.Select((it, st, at, ot, rt) => new IncomeTemplateModel
{
AnchorID = it.AnchorID,
AnchorName = st.anchorName,
NickName = st.nickName,
Balance = at.agentGold,
tip_income = SqlFunc.AggregateSum(it.tip_income),
agent_income = SqlFunc.AggregateSum(it.agent_income),
Platform_income = SqlFunc.AggregateSum(it.Platform_income),
hour_income = SqlFunc.AggregateSum(it.hour_income),
agentHour_income = SqlFunc.AggregateSum(it.agentHour_income),
livetime = SqlFunc.AggregateSum(it.livetime),
IsWorkHours = rt.IsWorkHours,
MinimumLiveTime = rt.LiveTime,
Salary = rt.Salary,
TipRebate = rt.TipRebate,
HourRebate = rt.HourRebate,
GiftAmount = rt.GiftAmount
})
.OrderBy(" sum(it.tip_income) desc")
.ToPageList(parm.page, parm.limit, ref totalCount);
#region 老版本写法
// var tableList = GetSqlSugarDB(DbConnType.QPAnchorRecordDB).DbMaintenance.GetTableInfoList();//获取数据库所有表名
// var query = db.Queryable<SysAnchor>()
// .WhereIF(dic.ContainsKey("Name") && !string.IsNullOrEmpty(dic["Name"].ToString()), (st) => st.username.Contains(dic["Name"].ToString()) || st.nickname.Contains(dic["Name"].ToString()))
// .WhereIF(dic.ContainsKey("isCollet") && Convert.ToInt32(dic["isCollet"]) != -1, (it) => it.isCollet == Convert.ToInt32(dic["isCollet"]))
// .WhereIF(dic.ContainsKey("isColletCode") && dic["isColletCode"].ToString() != "-1", (it) => it.isColletCode == dic["isColletCode"].ToString())
// .Select((st) => new IncomeTemplateModel
// {
// AnchorID = st.id,
// AnchorName = st.username,
// NickName = st.nickname,
// Balance = st.balance,
// isCollet = st.isCollet,
// }).WithCache(120);//缓存120秒
// res = query.Clone()
// .Mapper((it, cache) =>
// {
// if (tableList.Any(st => st.Name.Equals($@"income_{it.AnchorName}")))//判断表是否存在
// {
// //isnull(sum(tip_income),0) tip_income,
// var table = db.SqlQueryable<IncomeTemplateModel>($@"select isnull(sum(hour_income),0) hour_income,isnull(sum(agent_income),0) agent_income,isnull(sum(test_income),0) test_income
//from QPAnchorRecordDB.dbo.income_{it.AnchorName} where opdate>='{dic["startTime"].ToString()}' and opdate<'{dic["endTime"].ToString()}'")
// .First();
// it.hour_income = table.hour_income;
// it.agent_income = table.agent_income;
// it.test_income = table.test_income;
// }
// if (tableList.Any(st => st.Name.Equals($@"tip_{it.AnchorName}")))//判断表是否存在
// {
// //礼物金额
// var totalamount = db.SqlQueryable<TipTemplateModel>($@"select sum(totalamount) as totalamount from QPAnchorRecordDB.dbo.tip_{it.AnchorName}
//where sendtime>='{dic["startTime"].ToString()}' and sendtime<'{dic["endTime"].ToString()}'").First().totalamount;
// it.tip_income = totalamount;
// }
// })
// .ToList().OrderByDescending(it => it.tip_income).Skip((parm.page - 1) * parm.limit).Take(parm.limit).ToList();
// //.ToPageList(parm.page,m, ref totalCount);
// totalCount = query.Mapper((it, cache) =>//求和
// {
// if (tableList.Any(st => st.Name.Equals($@"income_{it.AnchorName}")))//判断表是否存在
// {
// //isnull(sum(tip_income),0) tip_income,
// var table = db.SqlQueryable<IncomeTemplateModel>($@"select isnull(sum(hour_income),0) hour_income,isnull(sum(agent_income),0) agent_income,isnull(sum(test_income),0) test_income
//from QPAnchorRecordDB.dbo.income_{it.AnchorName} where opdate>='{dic["startTime"].ToString()}' and opdate<'{dic["endTime"].ToString()}'")
// .First();
// hour_income += table.hour_income;
// agent_income += table.agent_income;
// test_income += table.test_income;
// }
// if (tableList.Any(st => st.Name.Equals($@"tip_{it.AnchorName}")))//判断表是否存在
// {
// //礼物金额
// var totalamount = db.SqlQueryable<TipTemplateModel>($@"select sum(totalamount) as totalamount from QPAnchorRecordDB.dbo.tip_{it.AnchorName}
//where sendtime>='{dic["startTime"].ToString()}' and sendtime<'{dic["endTime"].ToString()}'").First().totalamount;
// tip_income += totalamount;
// }
// Balance += it.Balance;
// }).ToList().Count();
// sumModel = new IncomeTemplateModel
// {
// hour_income = hour_income,
// agent_income = agent_income,
// tip_income = tip_income,
// test_income = test_income,
// Balance = Balance
// };
#endregion
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "主播财务报表分页信息", ex.Message, ex.StackTrace);
}
return(res);
}
public async Task <IActionResult> GetPages([FromQuery] PageParm parm)
{
return(Ok(await _videoService.GetWherePage(parm)));
}
public IActionResult LocalList([FromBody] PageParm parm)
{
return(Ok(_imageService.GetList(parm)));
}
public JsonResult SaleOrderGoodsList(PageParm parm, SearchParm searchParm)
{
var res = _goodsService.GetPagesAsync(parm, searchParm).Result;
return(Json(new { statusCode = 200, msg = "success", count = res.data.TotalPages, data = res.data.Items }));
}
/// <summary>
/// 获得列表,根据出库商家查询，调拨用
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <Page <GoodsSkuDto> > > GetByInOutShopPagesAsync(PageParm parm, string outShopGuid)
{
var res = new ApiResult <Page <GoodsSkuDto> >();
try
{
//根据出库商家，查询该商家下面的所有商品
var querys = Db.Queryable <ErpInOutLog, ErpGoodsSku>((log, sku) => new object[] { JoinType.Left, log.GoodsGuid == sku.Guid })
.Where((log, sku) => log.Types == 2 && log.GoodsSum > 0 && log.ShopGuid == outShopGuid)
.WhereIF(!string.IsNullOrEmpty(parm.key), (log, sku) => sku.Code == parm.key)
.WhereIF(!string.IsNullOrEmpty(parm.guid), (log, sku) => sku.BrankGuid == parm.guid)
.OrderBy((log, sku) => log.AddDate, OrderByType.Desc)
.Select((log, sku) => new GoodsSkuDto()
{
Guid = sku.Guid,
Code = sku.Code,
BrankName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == sku.BrankGuid).Select(g => g.Name),
StyleName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == sku.StyleGuid).Select(g => g.Name),
SeasonName = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == sku.SeasonGuid).Select(g => g.Name),
SalePrice = sku.SalePrice,
DisPrice = sku.DisPrice,
StockSum = log.GoodsSum,
SaleSum = sku.SaleSum,
AddDate = sku.AddDate
});
var str = querys.ToSql();
var query = querys.ToPageAsync(parm.page, parm.limit);
res.success = true;
res.message = "获取成功！";
res.data = await query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 读取列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static async Task <PagedInfo <T> > ToPageAsync <T>(this ISugarQueryable <T> source, PageParm parm)
{
var page = new PagedInfo <T>();
var total = await source.CountAsync();
page.TotalCount = total;
page.TotalPages = total / parm.PageSize;
if (total % parm.PageSize > 0)
{
page.TotalPages++;
}
page.PageSize = parm.PageSize;
page.PageIndex = parm.PageIndex;
page.DataSource = await source.OrderByIF(!string.IsNullOrEmpty(parm.Sort), $"{parm.OrderBy} {(parm.Sort == "descending" ? "desc" : "asc")}").ToPageListAsync(parm.PageIndex, parm.PageSize);
return(page);
}
public async Task <IActionResult> GetPages([FromQuery] PageParm parm)
{
return(Ok(await _memberService.GetPageList(parm)));
}
public async Task <IActionResult> GetTotals([FromQuery] PageParm parm)
{
var list = await balanceService.GetTotalAmountAsync(parm.key);
return(Ok(new { code = 0, msg = "success", count = 1, data = list }));
}
/// <summary>
/// 平台入库统计报表，入库 总数，可根据年份查询
/// </summary>
/// <returns></returns>
public Task <ApiResult <List <PlatformInStockReport> > > GetPlatformInStockReport(PageParm parm)
{
var res = new ApiResult <List <PlatformInStockReport> >();
try
{
if (string.IsNullOrEmpty(parm.key))
{
parm.key = DateTime.Now.Year.ToString();
}
var strSql = "select date_format(AddDate,'%m') AS `Months`,SUM(GoodsSum) as InCounts from erpinoutlog "
+ "where Types=1 and InTypes=1 and date_format(AddDate,'%Y')='" + parm.key + "' "
+ "group by months";
var query = Db.Ado.SqlQuery <PlatformInStockReport>(strSql);
if (query != null && query.Count > 0)
{
for (int i = 1; i < 13; i++)
{
var month = "0";
if (i < 10)
{
month = month + i.ToString();
}
else
{
month = i.ToString();
}
if (query.Find(m => m.Months == month) == null)
{
query.Add(new PlatformInStockReport()
{
Months = month
});
}
}
}
else
{
for (int i = 1; i < 13; i++)
{
var month = "0";
if (i < 10)
{
month = month + i.ToString();
}
else
{
month = i.ToString();
}
query.Add(new PlatformInStockReport()
{
Months = month
});
}
}
res.data = query.OrderBy(m => m.Months).ToList();
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
public async Task <ApiResult <Page <CmsMessage> > > GetPages(PageParm parm)
{
parm.site = parm.site = SiteTool.CurrentSite?.Guid;;
return(await _messageService.GetPagesAsync(parm, m => m.SiteGuid == parm.site, m => m.AddDate, DbOrderEnum.Desc));
}
public CloudFile LocalList(PageParm parm)
{
return(_imageService.GetList(parm));
}
public async Task <IActionResult> GetAdvListPages([FromQuery] PageParm parm)
{
var res = await _listService.GetListAsync(m => m.ClassGuid == parm.key, m => m.Sort, DbOrderEnum.Desc);
return(Ok(new { code = 0, msg = "success", count = 1, res.data }));
}