protected void Excel()
{
string fileName = "";
string[] strFieldsName = { };
string[] strFields = new string[] { };
DataTable dt = null;
BLL.statisticBLL bll = new BLL.statisticBLL();
Dictionary <string, string> dict = getDict();
if (string.IsNullOrEmpty(_group))
{
fileName = "客源收益分析-明细列表";
strFieldsName = new string[] { "订单号", "客源", "活动名称", "活动地点", "活动结束日期", "税费成本", "业务性质", "应收金额", "应付金额", "业务毛利", "区域", "业务员", "设计策划人员", "执行人员" };
strFields = new string[] { "o_id", "c_name", "o_content", "o_address", "o_edate", "o_financeCust", "na_name", "shou", "fu", "profit", "op_area", "op_name", "person3", "person4" };
dt = bll.getRevenueAnalysisListData(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
}
else
{
switch (_group)
{
case "1": //区域
fileName = "客源收益分析-区域分组";
strFieldsName = new string[] { "区域", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "op_area", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "op_area", "op_area,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "2": //客源
fileName = "客源收益分析-客源分组";
strFieldsName = new string[] { "客源", "区域", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "c_name", "op_area", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "c_name,op_area", "c_name,op_area,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "3": //业务员
fileName = "客源收益分析-业务员分组";
strFieldsName = new string[] { "业务员", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "op_number/op_area/op_name", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "op_name,op_area,op_number", "op_name,op_area,op_number,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "4": //设计策划人员
fileName = "客源收益分析-设计策划人员分组";
strFieldsName = new string[] { "设计策划人员", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "op_number/op_area/op_name", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData2(dict, this.pageSize, this.page, "p3.op_name,p3.op_area,p3.op_number", "p3.op_number asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "5": //执行人员
fileName = "客源收益分析-执行人员分组";
strFieldsName = new string[] { "执行人员", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "op_number/op_area/op_name", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData3(dict, this.pageSize, this.page, "p3.op_name,p3.op_area,p3.op_number", "p3.op_number asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "6": //月份
fileName = "客源收益分析-月份分组";
strFieldsName = new string[] { "月份", "区域", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "oYear/oMonth", "op_area", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "datepart(year,o_edate) oYear, datepart(month,o_edate) oMonth,op_area", "datepart(year,o_edate), datepart(month,o_edate),op_area,o_financeCust", "datepart(year,o_edate) asc,datepart(month,o_edate) asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
case "7": //业务性质
fileName = "客源收益分析-业务性质分组";
strFieldsName = new string[] { "业务性质", "区域", "应收金额", "应付金额", "业务毛利" };
strFields = new string[] { "na_name", "op_area", "shou", "fu", "profit" };
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "na_name,na_sort,op_area", "na_name,na_sort,op_area,o_financeCust", "na_sort asc", out this.totalCount, out _tShou, out _tFu, out _tProfit, false).Tables[0];
break;
default:
break;
}
}
ExcelHelper.Write(HttpContext.Current, dt, fileName, fileName, strFields, strFieldsName, string.Format("{0}.xlsx", fileName));
}
private void RptBind()
{
DataTable dt = null;
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
Dictionary <string, string> dict = getDict();
if (string.IsNullOrEmpty(_group))
{
dt = bll.getRevenueAnalysisListData(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList.DataSource = dt;
rptList.DataBind();
rptList.Visible = true;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = false;
}
else
{
switch (_group)
{
case "1": //区域
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "op_area", "op_area,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList1.DataSource = dt;
rptList1.DataBind();
rptList.Visible = false;
rptList1.Visible = true;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = false;
break;
case "2": //客源
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "c_name,op_area", "c_name,op_area,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList2.DataSource = dt;
rptList2.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = true;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = false;
break;
case "3": //业务员
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "op_name,op_area,op_number", "op_name,op_area,op_number,o_financeCust", "op_area asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList3.DataSource = dt;
rptList3.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = true;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = false;
break;
case "4": //策划人员
case "8": //设计人员
dt = bll.getRevenueAnalysisListData2(dict, this.pageSize, this.page, "p3.op_name,p3.op_area,p3.op_number", "op_number asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList4.DataSource = dt;
rptList4.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = true;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = false;
break;
case "5": //执行人员
dt = bll.getRevenueAnalysisListData3(dict, this.pageSize, this.page, "p3.op_name,p3.op_area,p3.op_number", "op_number asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList5.DataSource = dt;
rptList5.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = true;
rptList6.Visible = false;
rptList7.Visible = false;
break;
case "6": //月份
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "datepart(year,o_edate) oYear, datepart(month,o_edate) oMonth,op_area", "datepart(year,o_edate), datepart(month,o_edate),op_area,o_financeCust", "datepart(year,o_edate) asc,datepart(month,o_edate) asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList6.DataSource = dt;
rptList6.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = true;
rptList7.Visible = false;
break;
case "7": //业务性质
dt = bll.getRevenueAnalysisListData1(dict, this.pageSize, this.page, "na_name,na_sort,op_area", "na_name,na_sort,op_area,o_financeCust", "na_sort asc", out this.totalCount, out _tShou, out _tFu, out _tProfit).Tables[0];
rptList7.DataSource = dt;
rptList7.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
rptList5.Visible = false;
rptList6.Visible = false;
rptList7.Visible = true;
break;
default:
break;
}
}
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pShou += Utils.ObjToDecimal(dr["shou"], 0);
_pFu += Utils.ObjToDecimal(dr["fu"], 0);
_pProfit += Utils.ObjToDecimal(dr["profit"], 0);
}
}
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = Utils.CombUrlTxt("RevenueAnalysis_list.aspx", "page={0}&txtsDate={1}&txteDate={2}&ddllock={3}&action={4}&hide_place={5}&hide_nature={6}&txtCusName={7}&hCusId={8}&cbIsCust={9}&hide_employee1={10}&hide_employee3={11}&hide_employee4={12}&ddlGroup={13}", "__id__", _sMonth, _eMonth, _lockstatus, action, _area, _nature, _cusName, _cid, _isCust, _person1, _person3, _person4, _group);
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
pCount.Text = dt.Rows.Count.ToString();
pShou.Text = _pShou.ToString();
pFu.Text = _pFu.ToString();
pProfit.Text = _pProfit.ToString();
tCount.Text = totalCount.ToString();
tShou.Text = _tShou.ToString();
tFu.Text = _tFu.ToString();
tProfit.Text = _tProfit.ToString();
}