public ActionResult GetAnchorReportPage(PageParm parm)
{
int totalCount = 0;
var sumModel = new IncomeTemplateModel();
var res = sysUserAnchorLogic.GetAnchorReportPage(parm, ref totalCount, ref sumModel);
var result = new
{
code = 0,
msg = "success",
data = res,
count = totalCount,// pageData.Count
totalRow = new
{
hour_income = sumModel.hour_income.ToString(),
agent_income = sumModel.agent_income.ToString(),
tip_income = sumModel.tip_income.ToString(),
Platform_income = sumModel.Platform_income.ToString(),
agentHour_income = sumModel.agentHour_income.ToString(),
livetime = sumModel.livetime.ToString()
}
};
return(Content(result.ToJson()));
}
/// <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);
}
