public void show() { #region 表1 string companyName = ""; string companyIds = ""; string companyCode = ""; if (ddlCompany.Text == "-1") { foreach (ListItem item in ddlCompany.Items) { if (item.Value != "-1") { companyName += "+" + item.Value.Split(',')[0]; companyIds += "," + item.Value.Split(',')[1]; } } } else { companyName = ddlCompany.Text.Split(',')[0]; companyIds = ddlCompany.Text.Split(',')[1]; companyCode = ddlCompany.Text.Split(',')[2]; } companyIds = companyIds.Trim(','); lblSimpName1.Text = companyName.Trim('+'); lblSimpName.Text = companyName.Trim('+'); lblYearMonth.Text = ddlYear.Text + ddlMonth.Text; lblYearMonth1.Text = ddlYear.Text + ddlMonth.Text; //var com= ddlCompany.SelectedItem. as TB_Company; //查询所有费用 //公共 comm_propertyList = CommService.GetListArray(string.Format(" CaiYear='{0}' and CompId in ({1})", ddlYear.Text + ddlMonth.Text, companyIds)); //个性 spec_propertyList = SpecService.GetListArray(string.Format(" CaiYear='{0}' and CompId in ({1})", ddlYear.Text + ddlMonth.Text, companyIds)); //所有 all_propertyList = this.FIN_PropertyService.GetListArray(""); allCount = all_propertyList.Count + 4; DateTime fristDate = Convert.ToDateTime(ddlYear.Text + "-1-1"); DateTime endDate = Convert.ToDateTime(ddlYear.Text + "-" + ddlMonth.Text + "-" + DateTime.DaysInMonth(fristDate.Year, Convert.ToInt32(ddlMonth.Text))); //公里数 UseCarDetailList = useCarSer.Get_Report(fristDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), companyCode, ddlUser.Text); //销售总金额 reportDetailList = reportSer.Get_Report(fristDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), companyCode, ddlUser.Text); SellTotal = reportDetailList.Sum(t => t.SellTotal); lblSellTotal.Text = SellTotal.ToString(); //根据用户来计算总公里数 RoadLongTotal = 0; foreach (var m in reportDetailList) { RoadLongTotal += UseCarDetailList.Where(t => t.UserName == m.AE).Sum(t => t.RoadLong); } lblCarTotal.Text = RoadLongTotal.ToString(); lblSumCarTotal.Text = lblCarTotal.Text; #endregion string aeSql = ""; if (ddlCompany.Text != "-1" && ddlUser.Text != "-1") { aeSql = string.Format(" and exists (select id from tb_User where ID={0} and appName=id)", ddlUser.Text); } else if (ddlCompany.Text != "-1" && ddlUser.Text == "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); aeSql = string.Format(" and exists (select id from tb_User where {0} and appName=id)", where); } if (ddlIsClose.Text != "-1") { aeSql += " and IsClose=" + ddlIsClose.Text; } if (ddlJieIsSelected.Text != "-1") { aeSql += " and JieIsSelected=" + ddlJieIsSelected.Text; } //if (ddlPOTyle.Text != "-1") //{ // aeSql += " and CG_POOrder.POType=" + ddlPOTyle.Text; //} string POType = ""; if (cbAll.Checked == false) { string ids = ""; foreach (ListItem item in cbListPoType.Items) { if (item.Selected) { ids += item.Value + ","; } } if (!string.IsNullOrEmpty(ids)) { aeSql += " and CG_POOrder.POType in (" + ids.Trim(',') + ")"; } } string col1 = " ,0 as KouInvoTotal"; string kouInvoTotal = ""; string tiaojian1 = ""; string tiaojian2 = ""; string tiaojian3 = ""; string tiaojian4 = ""; if (ddlZhangQI.Text != "-1") { var StartTime = Convert.ToDateTime(txtStartTime.Text); var fuhao = ""; var fuhao_E = ""; if (ddlZhangQI.Text == "1") { fuhao = "<"; fuhao_E = ">="; } if (ddlZhangQI.Text == "2") { fuhao = "<="; fuhao_E = ">"; } col1 = ",sum(KouInvoTotal) as KouInvoTotal"; BaseKeyValue baseKeyModel = new BaseKeyValueService().GetModel(1); kouInvoTotal = ", case when isnull(sum(goodSellTotal),0)=0 or avg(WaiInvoTotal) is null then 0 else abs(sum(maoli))-abs(sum(maoli))*isnull(avg(WaiInvoTotal),0)/sum(goodSellTotal) end as KouInvoTotal "; tiaojian1 = string.Format(@"left join ( select CG_POOrder.PONO,sum(case when datediff(day,MinOutDate,TB_ToInvoice.DaoKuanDate) {0} {1} then Total else 0 end) as WaiInvoTotal from CG_POOrder left join TB_ToInvoice on CG_POOrder.PONo=TB_ToInvoice.PoNo and TB_ToInvoice.state='通过' left join (select min(CreateTime) as MinOutDate,PONO from Sell_OrderOutHouse where Status='通过' group by PONO ) as MinOutPO on MinOutPO.PONo=CG_POOrder.PoNo where CG_POOrder.IFZhui=0 and CG_POOrder.PODate between '{2} 00:00:00' and '{3} 23:59:59' {4} and GuestType='企业用户' and IsSpecial=0 and JieIsSelected=1 group by CG_POOrder.PoNo having datediff(day,min(MinOutDate),isnull(max(TB_ToInvoice.DaoKuanDate),getdate())) {5}{1} ) as ntb3 on CG_POOrder.PONo=ntb3.PONo", fuhao, baseKeyModel.TypeValue, StartTime.ToString("yyyy-MM-dd"), StartTime.Year + "-12-31", aeSql + POType, fuhao_E); tiaojian2 = string.Format(@"left join ( select CG_POOrder.PONO,sum(case when datediff(day,MinOutDate,TB_ToInvoice.DaoKuanDate) {0} {1} then Total else 0 end) as WaiInvoTotal from CG_POOrder left join TB_ToInvoice on CG_POOrder.PONo=TB_ToInvoice.PoNo and TB_ToInvoice.state='通过' left join (select min(CreateTime) as MinOutDate,PONO from Sell_OrderOutHouse where Status='通过' group by PONO ) as MinOutPO on MinOutPO.PONo=CG_POOrder.PoNo where CG_POOrder.IFZhui=0 and CG_POOrder.PODate between '{2} 00:00:00' and '{3} 23:59:59' {4} and GuestType='政府部门' and IsSpecial=0 and JieIsSelected=1 group by CG_POOrder.PoNo having datediff(day,min(MinOutDate),isnull(max(TB_ToInvoice.DaoKuanDate),getdate())){5}{1}) as ntb3 on CG_POOrder.PONo=ntb3.PONo", fuhao, baseKeyModel.TypeValue, StartTime.ToString("yyyy-MM-dd"), StartTime.Year + "-12-31", aeSql + POType, fuhao_E); tiaojian3 = string.Format(@"left join ( select CG_POOrder.PONO,sum(case when datediff(day,MinOutDate,TB_ToInvoice.DaoKuanDate) {0} {1} then Total else 0 end) as WaiInvoTotal from CG_POOrder left join TB_ToInvoice on CG_POOrder.PONo=TB_ToInvoice.PoNo and TB_ToInvoice.state='通过' left join (select min(CreateTime) as MinOutDate,PONO from Sell_OrderOutHouse where Status='通过' group by PONO ) as MinOutPO on MinOutPO.PONo=CG_POOrder.PoNo where CG_POOrder.IFZhui=0 and CG_POOrder.PODate between '{2} 00:00:00' and '{3} 23:59:59' {4} and GuestType='企业用户' and IsSpecial=0 and IsSelected=1 and JieIsSelected=1 group by CG_POOrder.PoNo having datediff(day,min(MinOutDate),isnull(max(TB_ToInvoice.DaoKuanDate),getdate())){5}{1}) as ntb3 on CG_POOrder.PONo=ntb3.PONo", fuhao, baseKeyModel.TypeValue, StartTime.ToString("yyyy-MM-dd"), StartTime.Year + "-12-31", aeSql + POType, fuhao_E); tiaojian4 = string.Format(@"left join ( select CG_POOrder.PONO,sum(case when datediff(day,MinOutDate,TB_ToInvoice.DaoKuanDate) {0} {1} then Total else 0 end) as WaiInvoTotal from CG_POOrder left join TB_ToInvoice on CG_POOrder.PONo=TB_ToInvoice.PoNo and TB_ToInvoice.state='通过' left join (select min(CreateTime) as MinOutDate,PONO from Sell_OrderOutHouse where Status='通过' group by PONO ) as MinOutPO on MinOutPO.PONo=CG_POOrder.PoNo where CG_POOrder.IFZhui=0 and CG_POOrder.PODate between '{2} 00:00:00' and '{3} 23:59:59' {4} and GuestType='政府部门' and IsSpecial=0 and IsSelected=1 and JieIsSelected=1 group by CG_POOrder.PoNo having datediff(day,min(MinOutDate),isnull(max(TB_ToInvoice.DaoKuanDate),getdate())){5}{1}) as ntb3 on CG_POOrder.PONo=ntb3.PONo", fuhao, baseKeyModel.TypeValue, StartTime.ToString("yyyy-MM-dd"), StartTime.Year + "-12-31", aeSql + POType, fuhao_E); } var allDs = DBHelp.getDataSet(string.Format(@"select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and EXISTS (select ID from CG_POOrder where PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal {3} from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal {4} from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo {5} where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='企业用户' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal {3} from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal {4} from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo {6} where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='政府部门' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal {3} from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal {4} from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo {7} where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='企业用户' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and IsSelected=1 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal {3} from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal {4} from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo {8} where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='政府部门' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and IsSelected=1 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE) as allNewTb group by AE; ", endDate.ToString("yyyy-MM-dd"), aeSql, fristDate.ToString("yyyy-MM-dd"), col1, kouInvoTotal, tiaojian1, tiaojian2, tiaojian3, tiaojian4)); string whereAE = ""; DataTable dt1 = allDs.Tables[0]; if (dt1.Rows.Count > 0) { whereAE = " and LoginName in ("; } allList = new List <AllSellTotalModel>(); foreach (DataRow dr in dt1.Rows) { allList.Add(new AllSellTotalModel { AE = dr[0].ToString(), PoTotal = Convert.ToDecimal(dr[1]), PoLiRunTotal = Convert.ToDecimal(dr[2]) }); whereAE += string.Format("'{0}',", dr[0].ToString()); } whereAE = whereAE.Trim(','); if (whereAE != "") { whereAE += ")"; } if (whereAE != "") { VAN_OA.Dal.SysUserService userSer = new VAN_OA.Dal.SysUserService(); user = userSer.getAllUserByLoginName_Report(whereAE); } else { user = new List <User>(); } DataTable dt2 = allDs.Tables[1]; foreach (DataRow dr in dt2.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_QZ = tureLiRun; model.MaoLi_QZ = maoLiTotal; model.SellTotal_QZ = goodSellTotal; model.sellFPTotal_QZ = sellFpTotal; model.KouTotal_QZ = Convert.ToDecimal(dr[5]); } DataTable dt3 = allDs.Tables[2]; foreach (DataRow dr in dt3.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_ZZ = tureLiRun; model.MaoLi_ZZ = maoLiTotal; model.SellTotal_ZZ = goodSellTotal; model.sellFPTotal_ZZ = sellFpTotal; model.KouTotal_ZZ = Convert.ToDecimal(dr[5]); } DataTable dt4 = allDs.Tables[3]; foreach (DataRow dr in dt4.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_QXZ = tureLiRun; model.MaoLi_QXZ = maoLiTotal; model.SellTotal_QXZ = goodSellTotal; model.sellFPTotal_QXZ = sellFpTotal; model.KouTotal_QXZ = Convert.ToDecimal(dr[5]); } DataTable dt5 = allDs.Tables[4]; foreach (DataRow dr in dt5.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_ZXZ = tureLiRun; model.MaoLi_ZXZ = maoLiTotal; model.SellTotal_ZXZ = goodSellTotal; model.sellFPTotal_ZXZ = sellFpTotal; model.KouTotal_ZXZ = Convert.ToDecimal(dr[5]); } }
public void show() { #region 表1 string companyName = ""; string companyIds = ""; string companyCode = ""; if (ddlCompany.Text == "-1") { foreach (ListItem item in ddlCompany.Items) { if (item.Value != "-1") { companyName += "+" + item.Value.Split(',')[0]; companyIds += "," + item.Value.Split(',')[1]; } } } else { companyName = ddlCompany.Text.Split(',')[0]; companyIds = ddlCompany.Text.Split(',')[1]; companyCode = ddlCompany.Text.Split(',')[2]; } companyIds = companyIds.Trim(','); lblSimpName1.Text = companyName.Trim('+'); lblSimpName.Text = companyName.Trim('+'); lblYearMonth.Text = ddlYear.Text + ddlMonth.Text; lblYearMonth1.Text = ddlYear.Text + ddlMonth.Text; //var com= ddlCompany.SelectedItem. as TB_Company; //查询所有费用 //公共 comm_propertyList = CommService.GetListArray(string.Format(" CaiYear='{0}' and CompId in ({1})", ddlYear.Text + ddlMonth.Text, companyIds)); //this.FIN_PropertyService.GetListArray_CommCost(ddlYear.Text + ddlMonth.Text, companyIds); //个性 spec_propertyList = SpecService.GetListArray(string.Format(" CaiYear='{0}' and CompId in ({1})", ddlYear.Text + ddlMonth.Text, companyIds)); // this.FIN_PropertyService.GetListArray_SpecCost(ddlYear.Text + ddlMonth.Text, companyIds, "-1"); //所有 all_propertyList = this.FIN_PropertyService.GetListArray(""); allCount = all_propertyList.Count + 4; DateTime fristDate = Convert.ToDateTime(ddlYear.Text + "-1-1"); DateTime endDate = Convert.ToDateTime(ddlYear.Text + "-" + ddlMonth.Text + "-" + DateTime.DaysInMonth(fristDate.Year, Convert.ToInt32(ddlMonth.Text))); //公里数 UseCarDetailList = useCarSer.Get_Report(ddlYear.Text, companyCode, ddlUser.Text); //RoadLongTotal=UseCarDetailList.Sum(t => t.RoadLong); //lblCarTotal.Text = RoadLongTotal.ToString(); //lblSumCarTotal.Text = lblCarTotal.Text; //销售总金额 reportDetailList = reportSer.Get_Report(fristDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), companyCode, ddlUser.Text, ddlJieIsSelected.Text); SellTotal = reportDetailList.Sum(t => t.SellTotal); lblSellTotal.Text = SellTotal.ToString(); //根据用户来计算总公里数 RoadLongTotal = 0; foreach (var m in reportDetailList) { RoadLongTotal += UseCarDetailList.Where(t => t.UserName == m.AE).Sum(t => t.RoadLong); } //RoadLongTotal = UseCarDetailList.Sum(t => t.RoadLong); lblCarTotal.Text = RoadLongTotal.ToString(); lblSumCarTotal.Text = lblCarTotal.Text; #endregion //string allSimpNames = ""; string aeSql = ""; if (ddlCompany.Text != "-1" && ddlUser.Text != "-1") { aeSql = string.Format(" and exists (select id from tb_User where ID={0} and appName=id)", ddlUser.Text); } else if (ddlCompany.Text != "-1" && ddlUser.Text == "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); aeSql = string.Format(" and exists (select id from tb_User where {0} and appName=id)", where); } if (ddlIsClose.Text != "-1") { aeSql += " and IsClose=" + ddlIsClose.Text; } if (ddlJieIsSelected.Text != "-1") { aeSql += " and JieIsSelected=" + ddlJieIsSelected.Text; } var allDs = DBHelp.getDataSet(string.Format(@"select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and EXISTS (select ID from CG_POOrder where PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='企业用户' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='政府部门' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE ) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='企业用户' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and IsSelected=1 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE) as allNewTb group by AE; select AE,sum(goodSellTotal) as goodSellTotal,sum(maoliTotal) as maoliTotal,sum(InvoTotal)-sum(goodTotal) as TrueLiRun,sum(SellFPTotal) as SellFPTotal from ( select CG_POOrder.PONo, sum(goodSellTotal) as goodSellTotal,sum(goodTotal)+sum(t_goodTotalChas) as goodTotal, sum(maoli) as maoliTotal, AE, isnull(avg(InvoTotal),0) as InvoTotal,isnull(avg(SellFPTotal),0) as SellFPTotal from CG_POOrder left join JXC_REPORT on CG_POOrder.PONo=JXC_REPORT.PONo left join (select PoNo,SUM(Total) as InvoTotal from TB_ToInvoice where TB_ToInvoice.state='通过' group by PoNo) as newtable1 on CG_POOrder.PONo=newtable1.PONo left join (select SUM(total) as SellFPTotal,PONo from Sell_OrderFP where Status='通过' group by PONo) as ntb2 on CG_POOrder.PONo=ntb2.PONo where ifzhui=0 and CG_POOrder.Status='通过' and CG_POOrder.PODate<='{0} 23:59:59' and CG_POOrder.PODate>='{2} 00:00:00' {1} and GuestType='政府部门' and EXISTS (select ID from CG_POOrder where --AppName=9 AND PONO=JXC_REPORT.PONO and IsSpecial=0 and IsSelected=1 and JieIsSelected=1) GROUP BY CG_POOrder.PONo,AE) as allNewTb group by AE; ", endDate.ToString("yyyy-MM-dd"), aeSql, fristDate.ToString("yyyy-MM-dd"))); string whereAE = ""; DataTable dt1 = allDs.Tables[0]; if (dt1.Rows.Count > 0) { whereAE = " and LoginName in ("; } allList = new List <AllSellTotalModel>(); foreach (DataRow dr in dt1.Rows) { allList.Add(new AllSellTotalModel { AE = dr[0].ToString(), PoTotal = Convert.ToDecimal(dr[1]), PoLiRunTotal = Convert.ToDecimal(dr[2]) }); whereAE += string.Format("'{0}',", dr[0].ToString()); } whereAE = whereAE.Trim(','); if (whereAE != "") { whereAE += ")"; } if (whereAE != "") { VAN_OA.Dal.SysUserService userSer = new VAN_OA.Dal.SysUserService(); user = userSer.getAllUserByLoginName_Report(whereAE); } else { user = new List <User>(); } DataTable dt2 = allDs.Tables[1]; foreach (DataRow dr in dt2.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_QZ = tureLiRun; model.MaoLi_QZ = maoLiTotal; model.SellTotal_QZ = goodSellTotal; model.sellFPTotal_QZ = sellFpTotal; } DataTable dt3 = allDs.Tables[2]; foreach (DataRow dr in dt3.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_ZZ = tureLiRun; model.MaoLi_ZZ = maoLiTotal; model.SellTotal_ZZ = goodSellTotal; model.sellFPTotal_ZZ = sellFpTotal; } DataTable dt4 = allDs.Tables[3]; foreach (DataRow dr in dt4.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_QXZ = tureLiRun; model.MaoLi_QXZ = maoLiTotal; model.SellTotal_QXZ = goodSellTotal; model.sellFPTotal_QXZ = sellFpTotal; } DataTable dt5 = allDs.Tables[4]; foreach (DataRow dr in dt5.Rows) { string ae = dr[0].ToString(); var goodSellTotal = Convert.ToDecimal(dr[1]); var maoLiTotal = Convert.ToDecimal(dr[2]); var tureLiRun = Convert.ToDecimal(dr[3]); var sellFpTotal = Convert.ToDecimal(dr[4]); var model = allList.Find(t => t.AE == ae); model.TureliRun_ZXZ = tureLiRun; model.MaoLi_ZXZ = maoLiTotal; model.SellTotal_ZXZ = goodSellTotal; model.sellFPTotal_ZXZ = sellFpTotal; } }