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