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