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]);
            }
        }
Exemple #2
0
        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;
            }
        }