private void RptBind(string _strWhere, string _orderby)
{
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
string _where = "";
Dictionary <string, string> dict = getDict();
DataTable dt = bll.getAchievementStatisticData(dict, this.pageSize, this.page, _orderby, out this.totalCount, out _tOrderCount, out _tOrderShou, out _tUnIncome, out _tOrderFu, out _tUnCost, out _tOrderProfit, out _tCust, out _tProfit).Tables[0];
rptList.DataSource = dt;
rptList.DataBind();
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = Utils.CombUrlTxt("AchievementStatistic_list.aspx", "page={0}&txtsDate={1}&txteDate={2}&ddlstatus={3}&ddllock={4}&hide_place={5}&hide_employee2={6}&cbIsRemove={7}&cbIsCust={8}&action={9}&ddltype={10}", "__id__", _sMonth, _eMonth, _status, _lockstatus, _area, _person, _isRemove, _isCust, action, _type);
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pOrderCount += Utils.ObjToInt(dr["oCount"]);
_pOrderShou += Utils.ObjToDecimal(dr["shou"], 0);
_pUnIncome += Utils.ObjToDecimal(dr["unIncome"], 0);
_pOrderFu += Utils.ObjToDecimal(dr["fu"], 0);
_pUnCost += Utils.ObjToDecimal(dr["unCost"], 0);
_pOrderProfit += Utils.ObjToDecimal(dr["oProfit"], 0);
_pCust += Utils.ObjToDecimal(dr["oCust"], 0);
_pProfit += Utils.ObjToDecimal(dr["bProfit"], 0);
}
}
pCount.Text = dt.Rows.Count.ToString();
pOrderCount.Text = _pOrderCount.ToString();
pOrderShou.Text = _pOrderShou.ToString();
pUnIncome.Text = _pUnIncome.ToString();
pOrderFu.Text = _pOrderFu.ToString();
pUnCost.Text = _pUnCost.ToString();
pOrderProfit.Text = _pOrderProfit.ToString();
pCust.Text = _pCust.ToString();
pProfit.Text = _pProfit.ToString();
tCount.Text = totalCount.ToString();
tOrderCount.Text = _tOrderCount.ToString();
tOrderShou.Text = _tOrderShou.ToString();
tUnIncome.Text = _tUnIncome.ToString();
tOrderFu.Text = _tOrderFu.ToString();
tUnCost.Text = _tUnCost.ToString();
tOrderProfit.Text = _tOrderProfit.ToString();
tCust.Text = _tCust.ToString();
tProfit.Text = _tProfit.ToString();
}
private void RptBind(string _strWhere, string _orderby)
{
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.getReceiveOrderAnalyzeData(this.pageSize, this.page, _strWhere, _orderby, out _tcount3, out _tcount5, out _tcount, out this.totalCount).Tables[0];
this.rptList.DataSource = dt;
this.rptList.DataBind();
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = backUrl();
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
int _pcount3 = 0, _pcount5 = 0, _pcount = 0;
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pcount3 += Utils.ObjToInt(dr["type3"], 0);
_pcount5 += Utils.ObjToInt(dr["type5"], 0);
_pcount += Utils.ObjToInt(dr["sumType"], 0);
}
}
pCount.Text = dt.Rows.Count.ToString();
pOrder3Count.Text = _pcount3.ToString();
pOrder5Count.Text = _pcount5.ToString();
pOrderCount.Text = _pcount.ToString();
tCount.Text = this.totalCount.ToString();
tOrder3Count.Text = _tcount3.ToString();
tOrder5Count.Text = _tcount5.ToString();
tOrderCount.Text = _tcount.ToString();
txtsDate.Text = _sdate;
txteDate.Text = _edate;
txtPerson.Text = _person;
txtDepart.Text = _depart;
ddlstatus.SelectedValue = _status;
ddldstatus.SelectedValue = _dstatus;
ddllock.SelectedValue = _lockstatus;
ddlarea.SelectedValue = _area;
ddlorderarea.SelectedValue = _orderarea;
}
protected void Excel()
{
BLL.statisticBLL bll = new BLL.statisticBLL();
Dictionary <string, string> dict = getDict();
DataTable dt = bll.getAreaAchievementStatisticData(dict, this.pageSize, this.page, "de_area", out this.totalCount, out _tOrderCount, out _tShou, out _tUnIncome, out _tFu, out _tUnCost, out _tCust, out _tProfit, false).Tables[0];
string fileName = "区域业绩统计";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("区域");
headRow.CreateCell(1).SetCellValue("订单数量");
headRow.CreateCell(2).SetCellValue("应收总额");
headRow.CreateCell(3).SetCellValue("非考核收入");
headRow.CreateCell(4).SetCellValue("应付总额");
headRow.CreateCell(5).SetCellValue("非考核成本");
headRow.CreateCell(6).SetCellValue("订单税费");
headRow.CreateCell(7).SetCellValue("业绩利润");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["de_area"]) + "-" + Utils.ObjectToStr(dt.Rows[i]["de_subname"]));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oCount"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["shou"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["unIncome"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["unCost"]));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_financeCust"]));
row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["profit"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_sdate = DTRequest.GetString("txtsDate");
_edate = DTRequest.GetString("txteDate");
_person = DTRequest.GetString("txtPerson");
_depart = DTRequest.GetString("txtDepart");
_status = DTRequest.GetString("ddlstatus");
_dstatus = DTRequest.GetString("ddldstatus");
_lockstatus = DTRequest.GetString("ddllock");
_area = DTRequest.GetString("ddlarea");
_orderarea = DTRequest.GetString("ddlorderarea");
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.getReceiveOrderAnalyzeData(this.pageSize, this.page, CombSqlTxt(), " op_number asc", out _tcount3, out _tcount5, out _tcount, out this.totalCount, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=策划与设计.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("人员");
headRow.CreateCell(1).SetCellValue("岗位");
headRow.CreateCell(2).SetCellValue("策划订单数");
headRow.CreateCell(3).SetCellValue("设计订单数");
headRow.CreateCell(4).SetCellValue("合计订单数");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["op_number"].ToString() + "(" + dt.Rows[i]["op_name"].ToString() + ")");
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["detaildepart"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["type3"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["type5"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["sumType"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_orderid = DTRequest.GetFormString("txtOrderID");
_cusName = DTRequest.GetFormString("txtCusName");
_cid = DTRequest.GetFormString("hCusId");
_status = DTRequest.GetFormString("ddlstatus");
_dstatus = DTRequest.GetFormString("ddldstatus");
_lockstatus = DTRequest.GetFormString("ddllock");
_pushstatus = DTRequest.GetFormString("ddlispush");
_flag = DTRequest.GetFormString("ddlflag");
_content = DTRequest.GetFormString("txtContent");
_address = DTRequest.GetFormString("txtAddress");
_sign = DTRequest.GetFormString("ddlsign");
_money = DTRequest.GetFormString("txtMoney");
_person1 = DTRequest.GetFormString("txtPerson1");
_person3 = DTRequest.GetFormString("txtPerson3");
_person5 = DTRequest.GetFormString("txtPerson5");
_sdate = DTRequest.GetFormString("txtsDate");
_edate = DTRequest.GetFormString("txteDate");
_sdate1 = DTRequest.GetFormString("txtsDate1");
_edate1 = DTRequest.GetFormString("txteDate1");
_area = DTRequest.GetFormString("ddlarea");
_sign1 = DTRequest.GetFormString("ddlsign1");
_money1 = DTRequest.GetFormString("txtMoney1");
_sign2 = DTRequest.GetFormString("ddlsign2");
_money2 = DTRequest.GetFormString("txtMoney2");
_sign3 = DTRequest.GetFormString("ddlsign3");
_money3 = DTRequest.GetFormString("txtMoney3");
_sign4 = DTRequest.GetFormString("ddlsign4");
_money4 = DTRequest.GetFormString("txtMoney4");
_sign5 = DTRequest.GetFormString("ddlsign5");
_money5 = DTRequest.GetFormString("txtMoney5");
_orderarea = DTRequest.GetFormString("ddlorderarea");
_sdate2 = DTRequest.GetFormString("txtsDate2");
_edate2 = DTRequest.GetFormString("txteDate2");
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.GetList(this.pageSize, this.page, "1=1" + CombSqlTxt(), "o_addDate desc,o_id desc", out this.totalCount, out money1, out money2, out money3, out money4, out money5, out money6, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=订单分析.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("订单号");
headRow.CreateCell(1).SetCellValue("活动名称");
headRow.CreateCell(2).SetCellValue("地点");
headRow.CreateCell(3).SetCellValue("客户");
headRow.CreateCell(4).SetCellValue("活动日期");
headRow.CreateCell(5).SetCellValue("归属地");
headRow.CreateCell(6).SetCellValue("订单状态");
headRow.CreateCell(7).SetCellValue("锁单状态");
headRow.CreateCell(8).SetCellValue("业务员");
headRow.CreateCell(9).SetCellValue("策划人员");
headRow.CreateCell(10).SetCellValue("设计人员");
headRow.CreateCell(11).SetCellValue("应收款");
headRow.CreateCell(12).SetCellValue("未收款");
headRow.CreateCell(13).SetCellValue("应付款");
headRow.CreateCell(14).SetCellValue("未付款");
headRow.CreateCell(15).SetCellValue("税费");
headRow.CreateCell(16).SetCellValue("业绩利润");
headRow.CreateCell(17).SetCellValue("确认时间");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
headRow.GetCell(10).CellStyle = titleCellStyle;
headRow.GetCell(11).CellStyle = titleCellStyle;
headRow.GetCell(12).CellStyle = titleCellStyle;
headRow.GetCell(13).CellStyle = titleCellStyle;
headRow.GetCell(14).CellStyle = titleCellStyle;
headRow.GetCell(15).CellStyle = titleCellStyle;
headRow.GetCell(16).CellStyle = titleCellStyle;
headRow.GetCell(17).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 15 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 20 * 256);
sheet.SetColumnWidth(12, 20 * 256);
sheet.SetColumnWidth(13, 20 * 256);
sheet.SetColumnWidth(14, 20 * 256);
sheet.SetColumnWidth(15, 20 * 256);
sheet.SetColumnWidth(16, 20 * 256);
sheet.SetColumnWidth(17, 25 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_content"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_address"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(4).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_sdate"]).Value.ToString("yyyy-MM-dd") + "/" + ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(5).SetCellValue(new MettingSys.BLL.department().getAreaText(dt.Rows[i]["o_place"].ToString()));
row.CreateCell(6).SetCellValue(BusinessDict.fStatus()[Utils.ObjToByte(dt.Rows[i]["o_status"])]);
row.CreateCell(7).SetCellValue(BusinessDict.lockStatus()[Utils.ObjToByte(dt.Rows[i]["o_lockStatus"])]);
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["op_name"]));
row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["person3"]));
row.CreateCell(10).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["person4"]));
row.CreateCell(11).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["shou"], 0).ToString());
row.CreateCell(12).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["weishou"], 0).ToString());
row.CreateCell(13).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["fu"], 0).ToString());
row.CreateCell(14).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["weifu"], 0).ToString());
row.CreateCell(15).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["o_financeCust"], 0).ToString());
row.CreateCell(16).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["profit"], 0).ToString());
row.CreateCell(17).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_statusTime"]) == "" ? "" : Utils.ObjectToDateTime(dt.Rows[i]["o_statusTime"]).ToString("yyyy-MM-dd HH:mm:ss"));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
row.GetCell(10).CellStyle = cellStyle;
row.GetCell(11).CellStyle = cellStyle;
row.GetCell(12).CellStyle = cellStyle;
row.GetCell(13).CellStyle = cellStyle;
row.GetCell(14).CellStyle = cellStyle;
row.GetCell(15).CellStyle = cellStyle;
row.GetCell(16).CellStyle = cellStyle;
row.GetCell(17).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
private void RptBind(string _strWhere, string _orderby)
{
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.GetList(this.pageSize, this.page, _strWhere, _orderby, out this.totalCount, out money1, out money2, out money3, out money4, out money5, out money6).Tables[0];
this.rptList.DataSource = dt;
this.rptList.DataBind();
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = backUrl();
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
pCount.Text = dt.Rows.Count.ToString();
decimal _pmoney1 = 0, _pmoney2 = 0, _pmoney3 = 0, _pmoney4 = 0, _pmoney5 = 0, _pmoney6 = 0;
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pmoney1 += Utils.ObjToDecimal(dr["shou"], 0);
_pmoney2 += Utils.ObjToDecimal(dr["weishou"], 0);
_pmoney3 += Utils.ObjToDecimal(dr["fu"], 0);
_pmoney4 += Utils.ObjToDecimal(dr["weifu"], 0);
_pmoney5 += Utils.ObjToDecimal(dr["o_financeCust"], 0);
_pmoney6 += Utils.ObjToDecimal(dr["profit"], 0);
}
}
tCount.Text = totalCount.ToString();
pMoney1.Text = _pmoney1.ToString();
pMoney2.Text = _pmoney2.ToString();
pMoney3.Text = _pmoney3.ToString();
pMoney4.Text = _pmoney4.ToString();
pMoney5.Text = _pmoney5.ToString();
pMoney6.Text = _pmoney6.ToString();
tMoney1.Text = money1.ToString();
tMoney2.Text = money2.ToString();
tMoney3.Text = money3.ToString();
tMoney4.Text = money4.ToString();
tMoney5.Text = money5.ToString();
tMoney6.Text = money6.ToString();
txtOrderID.Text = _orderid;
txtCusName.Text = _cusName;
hCusId.Value = _cid;
ddlstatus.SelectedValue = _status;
ddldstatus.SelectedValue = _dstatus;
ddllock.SelectedValue = _lockstatus;
txtContent.Text = _content;
txtAddress.Text = _address;
ddlsign.SelectedValue = _sign;
txtMoney.Text = _money;
txtPerson1.Text = _person1;
txtPerson3.Text = _person3;
txtPerson5.Text = _person5;
txtsDate.Text = _sdate;
txteDate.Text = _edate;
txtsDate1.Text = _sdate1;
txteDate1.Text = _edate1;
ddlarea.SelectedValue = _area;
ddlsign1.SelectedValue = _sign1;
txtMoney1.Text = _money1;
ddlsign2.SelectedValue = _sign2;
txtMoney2.Text = _money2;
ddlsign3.SelectedValue = _sign3;
txtMoney3.Text = _money3;
ddlsign4.SelectedValue = _sign4;
txtMoney4.Text = _money4;
ddlsign5.SelectedValue = _sign5;
txtMoney5.Text = _money5;
ddlorderarea.SelectedValue = _orderarea;
ddlmethod.SelectedValue = _method;
}
protected void Excel()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
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))
{
#region
fileName = "供应商支出分析-明细列表";
dt = bll.getExpendAnalyzeData(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("订单号");
headRow.CreateCell(1).SetCellValue("供应商");
headRow.CreateCell(2).SetCellValue("活动名称");
headRow.CreateCell(3).SetCellValue("活动地点");
headRow.CreateCell(4).SetCellValue("活动结束日期");
headRow.CreateCell(5).SetCellValue("业务性质");
headRow.CreateCell(6).SetCellValue("业务明细");
headRow.CreateCell(7).SetCellValue("应付金额");
headRow.CreateCell(8).SetCellValue("区域");
headRow.CreateCell(9).SetCellValue("业务员");
headRow.CreateCell(10).SetCellValue("地接添加人");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
headRow.GetCell(10).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 15 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_content"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_address"]));
row.CreateCell(4).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-mm-dd"));
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_detail"]));
row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.CreateCell(8).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["op_name"]));
row.CreateCell(10).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_personName"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
row.GetCell(10).CellStyle = cellStyle;
}
}
#endregion
}
else
{
switch (_group)
{
case "1": //供应商
#region
fileName = "供应商支出分析-供应商分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "c_name,c_type,c_business,op_area,na_name", "c_name,c_type,c_business,op_area,na_name", "c_name asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("供应商");
headRow.CreateCell(1).SetCellValue("客户类别");
headRow.CreateCell(2).SetCellValue("业务范围");
headRow.CreateCell(3).SetCellValue("区域");
headRow.CreateCell(4).SetCellValue("业务性质");
headRow.CreateCell(5).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(1).SetCellValue(MettingSys.Common.BusinessDict.customerType()[Utils.ObjToByte(dt.Rows[i]["c_type"])]);
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_business"]));
row.CreateCell(3).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
}
}
#endregion
break;
case "2": //区域
#region
fileName = "供应商支出分析-区域分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "op_area,na_name", "op_area,na_name", "op_area asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("区域");
headRow.CreateCell(1).SetCellValue("业务性质");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
case "3": //月份
#region
fileName = "供应商支出分析-月份分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "datepart(year,o_edate) oYear,datepart(month,o_edate) oMonth,na_name", "datepart(year,o_edate),datepart(month,o_edate),na_name", "datepart(year,o_edate) asc,datepart(month,o_edate) asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("月份");
headRow.CreateCell(1).SetCellValue("业务性质");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oYear"]) + "/" + Utils.ObjectToStr(dt.Rows[i]["oMonth"]));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
case "4": //业务性质
#region
fileName = "供应商支出分析-业务性质分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "na_name,na_sort,fin_detail", "na_name,na_sort,fin_detail", "na_sort asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("业务性质");
headRow.CreateCell(1).SetCellValue("业务明细");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_detail"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
default:
break;
}
}
//ExcelHelper.Write(HttpContext.Current, dt, fileName, fileName, strFields, strFieldsName, string.Format("{0}.xlsx", fileName));
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
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.getExpendAnalyzeData(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _tFu).Tables[0];
rptList.DataSource = dt;
rptList.DataBind();
rptList.Visible = true;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
}
else
{
switch (_group)
{
case "1": //供应商
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "c_name,c_type,c_business,op_area,na_name", "c_name,c_type,c_business,op_area,na_name", "c_name asc", out this.totalCount, out _tFu).Tables[0];
rptList1.DataSource = dt;
rptList1.DataBind();
rptList.Visible = false;
rptList1.Visible = true;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = false;
break;
case "2": //区域
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "op_area,na_name", "op_area,na_name", "op_area asc", out this.totalCount, out _tFu).Tables[0];
rptList2.DataSource = dt;
rptList2.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = true;
rptList3.Visible = false;
rptList4.Visible = false;
break;
case "3": //月份
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "datepart(year,o_edate) oYear,datepart(month,o_edate) oMonth,na_name", "datepart(year,o_edate),datepart(month,o_edate),na_name", "datepart(year,o_edate) asc,datepart(month,o_edate) asc", out this.totalCount, out _tFu).Tables[0];
rptList3.DataSource = dt;
rptList3.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = true;
rptList4.Visible = false;
break;
case "4": //业务性质
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "na_name,na_sort,fin_detail", "na_name,na_sort,fin_detail", "na_sort asc", out this.totalCount, out _tFu).Tables[0];
rptList4.DataSource = dt;
rptList4.DataBind();
rptList.Visible = false;
rptList1.Visible = false;
rptList2.Visible = false;
rptList3.Visible = false;
rptList4.Visible = true;
break;
default:
break;
}
}
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pFu += Utils.ObjToDecimal(dr["fu"], 0);
}
}
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = Utils.CombUrlTxt("ExpendAnalyze_list.aspx", "page={0}&txtsDate={1}&txteDate={2}&action={3}&hide_place={4}&hide_nature={5}&txtCusName={6}&hCusId={7}&hide_employee1={8}&hide_employee3={9}&ddlGroup={10}&ddllock={11}", "__id__", _sMonth, _eMonth, action, _area, _nature, _cusName, _cid, _person1, _person3, _group, _lockstatus);
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
pCount.Text = dt.Rows.Count.ToString();
pFu.Text = _pFu.ToString();
tCount.Text = totalCount.ToString();
tFu.Text = _tFu.ToString();
}
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();
}
