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