private void QueryData()
{
spSysTime1.InnerHtml = spSysTime2.InnerHtml = spSysTime3.InnerHtml = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
string StartDate = "";
string EndDate = "";
bool bl = LoadTitle(out StartDate, out EndDate);
if (!bl)
{
ShowMsgHelper.ExecuteScript("操作失败，日期格式错误！");
return;
}
Hashtable ht = new Hashtable();
ht["AdminHotelId"] = hdAdminHotelId.Value;
ht["HotelId"] = ddlHotel.SelectedValue;
ht["PaySource"] = hdPaySource.Value;
ht["Transaction_Type"] = hdTransaction_Type.Value;
ht["StartDate"] = StartDate;
ht["EndDate"] = EndDate;
ht["DateType"] = hdDateType.Value;
ht["ReportType"] = hdReportType.Value;
DataTable dt = DataFactory.SqlDataBase().GetDataTableProc("P_Hotel_Bill_Report", ht);
string title = "";
switch (hdReportType.Value)
{
case "1":
title = "财务对账汇总表";
rptData1.DataSource = dt;
rptData1.DataBind();
#region ** 导出 **
if (hdExport.Value == "1" && dt != null && dt.Rows.Count > 0)
{
dt.Columns["Pay_Type"].ColumnName = "资金渠道";
dt.Columns["Income_Money"].ColumnName = "收入";
dt.Columns["Income_Number"].ColumnName = "收入笔数";
dt.Columns["Refund_Money"].ColumnName = "支出";
dt.Columns["Refund_Number"].ColumnName = "支出笔数";
dt.Columns["Total_Money"].ColumnName = "收支金额";
dt.Columns["Service_Charge"].ColumnName = "手续费";
dt.Columns["DayNetAmount"].ColumnName = "期末净额";
dt.Columns.Remove("SerialNumber");
dt.Columns.Remove("PaySource");
dt.Columns.Remove("Transaction_Type");
ExcelHelper.ExportExcel(dt, title);
}
#endregion
break;
case "2":
title = "财务对账明细表";
rptData2.DataSource = dt;
rptData2.DataBind();
#region ** 导出 **
if (hdExport.Value == "1" && dt != null && dt.Rows.Count > 0)
{
dt.Columns["PaySource"].ColumnName = "资金渠道";
dt.Columns["Transaction_Type"].ColumnName = "收款场景";
dt.Columns["Transaction_Time"].ColumnName = "交易时间";
dt.Columns["Order_Numbe"].ColumnName = "商户订单号";
dt.Columns["Transaction_State"].ColumnName = "交易状态";
dt.Columns["Total_Money"].ColumnName = "收款金额";
dt.Columns["Refund_Numbe"].ColumnName = "商户退款单号";
dt.Columns["Refund_Money"].ColumnName = "退款金额";
dt.Columns["Service_Charge"].ColumnName = "手续费";
ExcelHelper.ExportExcel(dt, title);
}
#endregion
break;
case "3":
title = "财务对账日统计表";
rptData3.DataSource = dt;
rptData3.DataBind();
#region ** 导出 **
if (hdExport.Value == "1" && dt != null && dt.Rows.Count > 0)
{
dt.Columns["Bill_Date"].ColumnName = "账单日期";
dt.Columns["Income_Money"].ColumnName = "收入";
dt.Columns["Income_Number"].ColumnName = "收入笔数";
dt.Columns["Refund_Money"].ColumnName = "支出";
dt.Columns["Refund_Number"].ColumnName = "支出笔数";
dt.Columns["Total_Money"].ColumnName = "收支金额";
dt.Columns["Service_Charge"].ColumnName = "手续费";
dt.Columns["DayNetAmount"].ColumnName = "日终净额";
ExcelHelper.ExportExcel(dt, title);
}
#endregion
break;
default:
break;
}
spTitle.InnerHtml = title;
}
private bool LoadTitle(out string StartDate, out string EndDate)
{
StartDate = "";
EndDate = "";
DataTable dtTitle = new DataTable();
dtTitle.Columns.Add("Name", typeof(String));
string dt = hdReportType.Value;
if (dt == "0")
{
dtTitle.Rows.Add("合计");
spReportDate.InnerHtml = "全部";
}
else if (dt == "1")
{
DateTime dtDay = Convert.ToDateTime(txtDate.Value);
spReportDate.InnerHtml = dtDay.ToString("yyyy年MM月dd日");
dtTitle.Rows.Add(txtDate.Value);
StartDate = txtDate.Value;
EndDate = Convert.ToDateTime(txtDate.Value).AddDays(1).ToString("yyyy-MM-dd");
}
else if (dt == "2")
{
DateTime dtMonth = Convert.ToDateTime(txtStartMonth.Value + "-01");
spReportDate.InnerHtml = dtMonth.ToString("yyyy年MM月");
int days = DateTime.DaysInMonth(dtMonth.Year, dtMonth.Month);
for (int i = 0; i < days; i++)
{
dtTitle.Rows.Add(dtMonth.AddDays(i).ToString("dd"));
}
dtTitle.Rows.Add("合计");
StartDate = dtMonth.ToString("yyyy-MM-dd");
EndDate = dtMonth.AddMonths(1).ToString("yyyy-MM-dd");
}
else if (dt == "3")
{
DateTime dtYear = Convert.ToDateTime(ddlYear.SelectedValue + "-01-01");
spReportDate.InnerHtml = ddlYear.SelectedValue + "年";
dtTitle.Rows.Add("01月");
dtTitle.Rows.Add("02月");
dtTitle.Rows.Add("03月");
dtTitle.Rows.Add("04月");
dtTitle.Rows.Add("05月");
dtTitle.Rows.Add("06月");
dtTitle.Rows.Add("07月");
dtTitle.Rows.Add("08月");
dtTitle.Rows.Add("09月");
dtTitle.Rows.Add("10月");
dtTitle.Rows.Add("11月");
dtTitle.Rows.Add("12月");
dtTitle.Rows.Add("合计");
StartDate = dtYear.ToString("yyyy-MM-dd");
EndDate = dtYear.AddYears(1).ToString("yyyy-MM-dd");
}
else if (dt == "4")
{
DateTime sd = Convert.ToDateTime(txtStartDate.Value);
DateTime ed = Convert.ToDateTime(txtEndDate.Value);
int day4 = (ed - sd).Days;
if (day4 > 31)
{
ShowMsgHelper.ExecuteScript("操作失败！日期跨度不能超过一个月");
return(false);
}
for (int i = 0; i <= day4; i++)
{
dtTitle.Rows.Add(sd.AddDays(i).ToString("dd"));
}
dtTitle.Rows.Add("合计");
StartDate = sd.ToString("yyyy-MM-dd");
EndDate = ed.AddDays(1).ToString("yyyy-MM-dd");
}
rptTitle.DataSource = dtTitle;
rptTitle.DataBind();
return(true);
}