public void ProcessRequest(HttpContext context)
{
try
{
LoginUser loginUser = new LoginUser(context, "AgentAccDetail");
if (!loginUser.Pass)//权限验证
{
return;
}
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
AgentAccDetailBLL bll = new AgentAccDetailBLL(context, loginUser);
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string agentId = context.Request["agentId"];
string agentName = context.Request["agentName"];
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
bll.LoadGrid(page, rows, agentId, agentName, startDate, endDate);
return;
}
}
catch (Exception e)
{
Message.error(context, e.Message);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
AgentAccDetailBLL bll = new AgentAccDetailBLL(context, loginSession);
System.Data.DataSet ds = new DataSet();
ds = bll.GetDataSet();
this.ReportViewer1.Reset();
this.ReportViewer1.LocalReport.ReportPath = @"Reports\AgentAccDetail.rdlc";
this.ReportViewer1.LocalReport.DisplayName = "代理商账户明细";
ReportDataSource rds = new ReportDataSource("AgentAccDetailDataSet", ds.Tables[0]);
this.ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.LocalReport.Refresh();
}
}
/// <summary>
/// 加载销售统计报表数据
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">行数</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="city">市</param>
/// <param name="county">区县</param>
/// <param name="siteId">快开厅</param>
/// <param name="agentId">代理商</param>
/// <param name="terminalId">终端号</param>
public void SalesInvoiceSummaryLoadGrid(int page, int rows, string startDate, string endDate, string city, string county, string siteId, string agentId, string terminalId)
{
string strSQL = SalesInvoiceSummarySql(startDate, endDate, city, county, siteId, agentId, terminalId);
Param param = new Param();
DataAccess db = new DataAccess(DataAccess.DBConn);
int dataTotal = 0;
try
{
db.Open();
dataTotal = int.Parse(db.ExecuteScalar(CommandType.Text, "select count(*) from (" + strSQL + ")", param).ToString());
}
catch (Exception e)
{
throw e;
}
finally
{
db.Close();
}
string strSqlData = "select * from ( select AP.* , rownum r from (" + strSQL + ") AP ";
strSqlData += " where rownum <= " + Convert.ToString(page * rows) + " ) BP where r > " + Convert.ToString((page - 1) * rows);
DataTable dt = db.ExecuteDataView(CommandType.Text, strSqlData, param).Table;
AgentAccDetailBLL agentAccDetailBLL = new AgentAccDetailBLL(context, loginSession);
List <TTAgentAccDetail> balanceList = agentAccDetailBLL.GetTTAgentAccDetailList(city, county, siteId, agentId, terminalId);
foreach (DataRow dr in dt.Rows)
{
var balanceGroupList = balanceList.Where(c => DateTime.Parse(c.createTime.Substring(0, 10)) <= DateTime.Parse(dr["dateday"].ToString()))
.OrderByDescending(x => x.createTime).GroupBy(x => x.agentId) //必须排序
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b)
.Zip(Enumerable.Range(1, t.count), (j, i) =>
new { j.balance, j.createTime, rn = i }));
var advanceBalance = balanceGroupList.Where(c => c.rn == 1).Sum(c => c.balance);
dr["advanceBalance"] = advanceBalance;
}
string str = commonDao.DataGrid(dt, dataTotal);
WebJson.Write(context, str);
}
public void ProcessRequest(HttpContext context)
{
LoginUser loginUser = new LoginUser(context, "SalesInvoiceSummaryReport");
LotteryOrderBLL bll = new LotteryOrderBLL(context, loginUser);
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
bll.SalesInvoiceSummaryLoadGrid(page, rows, startDate, endDate, city, county, siteId, agentId, terminalId);
}
//获取销售总计
if (context.Request["action"] == "totalSale")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
DataTable dt = bll.GetCommissionTotalSale(startDate, endDate, city, county, siteId, agentId, terminalId);
DataRow dr = dt.Rows[0];
context.Response.Write("{\"totalSumbetfee\":" + dr["totalSumbetfee"] + ",\"totalCommissionFee\":" + dr["totalCommissionFee"] + ",\"totalAwardAmount\":" + dr["totalAwardAmount"] + ",\"totalSumTakeCashFee\":" + dr["totalSumTakeCashFee"] + ",\"totalRefundAmount\":" + dr["totalRefundAmount"] + "}");
}
//加载市
if (context.Request["action"] == "cityListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.CityCombobox();
}
//加载区县
if (context.Request["action"] == "countyListLoad")
{
string cityId = context.Request["cityId"];
Combobox com = new Combobox(context, loginUser);
com.CountyCombobox(cityId);
}
//加载执法文书类型
if (context.Request["action"] == "siteListLoad")
{
Combobox com = new Combobox(context, loginUser);
string cityId = context.Request["cityId"];
string countyId = context.Request["countyId"];
com.SiteByAreaCombobox(cityId, countyId);
}
if (context.Request["action"] == "ToExcel")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
AgentAccDetailBLL agentAccDetailBLL = new AgentAccDetailBLL(context, loginUser);
DataTable dt = bll.SalesInvoiceSummaryReport(startDate, endDate, city, county, siteId, agentId, terminalId);
//获取预存款余额
List <TTAgentAccDetail> balanceList = agentAccDetailBLL.GetTTAgentAccDetailList(city, county, siteId, agentId, terminalId);
int dtRowsCount = dt.Rows.Count + 5;
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode(startDate + "至" + endDate + "销售统计报表.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet = workbook.CreateSheet("销售统计报表");
//设置单元的宽度
sheet.SetColumnWidth(0, 20 * 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);
#region 合并单元格
CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 6);
sheet.AddMergedRegion(regionTitle);
CellRangeAddress regionDate = new CellRangeAddress(1, 1, 0, 6);
sheet.AddMergedRegion(regionDate);
CellRangeAddress regionSearch = new CellRangeAddress(2, 2, 0, 6);
sheet.AddMergedRegion(regionSearch);
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sheet.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.SetCellValue("销售统计报表");
NOPIHelper.RegionMethod(workbook, sheet, regionTitle, NOPIHelper.Stylexls.标题);
IRow row1 = sheet.CreateRow(1);
row1.Height = 20 * 20;
ICell icell1top1 = row1.CreateCell(0);
icell1top1.SetCellValue("日期:" + startDate + "至" + endDate + "");
NOPIHelper.RegionMethod(workbook, sheet, regionDate, NOPIHelper.Stylexls.头);
IRow row2 = sheet.CreateRow(2);
row2.Height = 20 * 20;
ICell icell1top2 = row2.CreateCell(0);
var cityName = "";
var countyName = "";
var agentName = "";
var siteName = "";
AgentBLL agentBLL = new AgentBLL(context, loginUser);
SiteBLL siteBLL = new SiteBLL(context, loginUser);
AreaBLL areaBLL = new AreaBLL(context, loginUser);
if (!string.IsNullOrEmpty(agentId))
{
var agent = agentBLL.Get(agentId);
if (agent != null)
{
agentName = agent.agentName;
}
}
if (!string.IsNullOrEmpty(siteId))
{
siteName = siteBLL.Get(siteId).siteName;
}
if (!string.IsNullOrEmpty(city))
{
cityName = areaBLL.Get(city).areaName;
}
if (!string.IsNullOrEmpty(county))
{
countyName = areaBLL.Get(county).areaName;
}
icell1top2.SetCellValue("市:" + cityName + " 区县:" + countyName + " 执法文书类型:" + siteName + " 代理商编号:" + agentName + " 终端号:" + terminalId + "");//搜索条件
NOPIHelper.RegionMethod(workbook, sheet, regionSearch, NOPIHelper.Stylexls.头);
#endregion
#region 设置表头
int rowsNum = 3; //行号
IRow row3 = sheet.CreateRow(rowsNum);
row3.Height = 20 * 20;
ICell icell1top = row3.CreateCell(0);
icell1top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1top.SetCellValue("时间");
ICell icell2top = row3.CreateCell(1);
icell2top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2top.SetCellValue("预存款金额");
ICell icell3top = row3.CreateCell(2);
icell3top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell3top.SetCellValue("销售总额");
ICell icell4top = row3.CreateCell(3);
icell4top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell4top.SetCellValue("佣金金额");
ICell icell5top = row3.CreateCell(4);
icell5top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell5top.SetCellValue("中奖金额");
ICell icell6top = row3.CreateCell(5);
icell6top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell6top.SetCellValue("提现金额");
ICell icell7top = row3.CreateCell(6);
icell7top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell7top.SetCellValue("退款金额");
#endregion
#region 合计
rowsNum = 4; //行号
IRow row4 = sheet.CreateRow(rowsNum);
row4.Height = 20 * 20;
ICell icell1Total = row4.CreateCell(0);
icell1Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1Total.SetCellValue("合计");
ICell icell2Total = row4.CreateCell(1);
icell2Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2Total.SetCellValue("");
ICell icell3Total = row4.CreateCell(2);
icell3Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell3Total.SetCellFormula("SUM(C6:C" + dtRowsCount + ")");
ICell icell4Total = row4.CreateCell(3);
icell4Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell4Total.SetCellFormula("SUM(D6:D" + dtRowsCount + ")");
ICell icell5Total = row4.CreateCell(4);
icell5Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell5Total.SetCellFormula("SUM(E6:E" + dtRowsCount + ")");
ICell icell6Total = row4.CreateCell(5);
icell6Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell6Total.SetCellFormula("SUM(F6:F" + dtRowsCount + ")");
ICell icell7Total = row4.CreateCell(6);
icell7Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell7Total.SetCellFormula("SUM(G6:G" + dtRowsCount + ")");
#endregion
ICellStyle cellStyleMoney = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
ICellStyle cellStyleTime = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.时间);
ICellStyle cellStyleDefault = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.默认);
rowsNum = 5;
foreach (DataRow dr in dt.Rows)
{
/******************写入字段值*********************/
row4 = sheet.CreateRow(rowsNum);
ICell icel1DateDay = row4.CreateCell(0);
icel1DateDay.SetCellValue(dr["dateday"].ToString());
icel1DateDay.CellStyle = cellStyleTime;
var balanceGroupList = balanceList.Where(c => DateTime.Parse(c.createTime.Substring(0, 10)) <= DateTime.Parse(dr["dateday"].ToString()))
.OrderByDescending(x => x.createTime).GroupBy(x => x.agentId)//必须排序
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b)
.Zip(Enumerable.Range(1, t.count), (j, i) =>
new { j.balance, j.createTime, rn = i }));
var advanceBalance = balanceGroupList.Where(c => c.rn == 1).Sum(c => c.balance);
ICell icellAdvanceBalance = row4.CreateCell(1);
icellAdvanceBalance.SetCellValue(advanceBalance);
icellAdvanceBalance.CellStyle = cellStyleMoney;
ICell icellSumBetFee = row4.CreateCell(2);
icellSumBetFee.SetCellValue(double.Parse(dr["sumBetFee"].ToString()));
icellSumBetFee.CellStyle = cellStyleMoney;
ICell icel1CommissionFee = row4.CreateCell(3);
icel1CommissionFee.SetCellValue(double.Parse(dr["commissionFee"].ToString()));
icel1CommissionFee.CellStyle = cellStyleMoney;
ICell icel1AwardAmount = row4.CreateCell(4);
icel1AwardAmount.SetCellValue(double.Parse(dr["awardAmount"].ToString()));
icel1AwardAmount.CellStyle = cellStyleMoney;
ICell icel1SumTakeCashFee = row4.CreateCell(5);
icel1SumTakeCashFee.SetCellValue(double.Parse(dr["sumTakeCashFee"].ToString()));
icel1SumTakeCashFee.CellStyle = cellStyleMoney;
ICell icel1RefundAmount = row4.CreateCell(6);
icel1RefundAmount.SetCellValue(double.Parse(dr["refundAmount"].ToString()));
icel1RefundAmount.CellStyle = cellStyleMoney;
rowsNum++;
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
}
