/// <summary>
/// CRM13_2報表(列印鈕)-彙總表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM13_2(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMRecordReport BCO = new CRMModel.QueryCRMRecordReport(ConntionDB);
DataTable Dt = null;
Dt = BCO.PrintSum(ParameterList);
if (Dt == null || Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
return Dt;
#endregion
}
/// <summary>
/// BUTTON 列印
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void but_Print_Click(object sender, EventArgs e)
{
#region
try
{
#region 設定 hid_PageStauts 狀態
this.hid_PageStauts.Value = "open";
#endregion
#region 檢查必填欄位是否輸入
string sb = "";
sb = CheckPage();
if (sb != "")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "隨便寫", sb, true);
return;
}
#endregion
if (this.rdb_Report_Status.SelectedValue == "0")//CRM13_客服線上服務明細表(報表格式)
{
#region
#region 從資料庫讀取資料
#region 組SQL字串
System.Text.StringBuilder srb_SQL = new System.Text.StringBuilder();
srb_SQL.Append(@"
select M.BUSDATE, M.CHAN_NO || SC.name as CHAN_NO,
D.ONLINE_NO || C.EXPLAIN as ONLINE_NOANDNAME,
M.STORE || S.STORE_NAME as STOREANDNAME, S.Z_O, RV.ROUTE as ROUTE_ID,
RV.STEP as ROUTE_STEP,M.DEAL_CONTENT
from VDS_CRM_RECORD_MAIN M, VDS_CRM_RECORD_DETL D, VDS_STM_STORECHAN SC,
VDS_STM_STORE_UNIQUE S, SYS_CODE_DETL C, VDS_STM_ROUTE_STORE_VIEW RV
where M.ID = D.PID
and M.CHAN_NO = SC.CODE
and C.SCODE = D.ONLINE_NO
and C.CATEGORY = 'C1'
and S.STORE = M.STORE and S.CHAN_NO =M.CHAN_NO
and S.CHAN_NO = RV.CHAN_NO and S.STORE = RV.STORE
and RV.EFF_START <= sysdate
and nvl(RV.EFF_END,to_date('99991231','YYYYMMDD')) > sysdate
");
srb_SQL.Append(ToMakeSQL().ToString());
srb_SQL.Append(" order by M.BUSDATE,M.CHAN_NO,D.ONLINE_NO");
#endregion
ArrayList ParameterList = new ArrayList();
ParameterList.Clear();
ParameterList.Add(srb_SQL.ToString());
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("CRM13_客服線上服務明細表(報表格式)");
DataTable dt_Return = new DataTable();
BCO.QueryCRMRecordReport bco = new BCO.QueryCRMRecordReport(ConntionDB);
dt_Return = bco.PrintDetail(ParameterList);
#endregion
if (dt_Return.Rows.Count == 0)
{
ScriptManager.RegisterStartupScript(this.up_ErrorMsg, typeof(UpdatePanel), "隨便寫", "alert('查無資料');", true);
return;
}
else
{
#region 利用CrystalReport列印報表
Show_Report2(dt_Return, "Detail");
Session["Rpt_Data_Detail" + PageTimeStamp.Value] = dt_Return;
#endregion
}
#endregion
}
else if (this.rdb_Report_Status.SelectedValue == "1")//CRM13_客服線上服務彙總表(報表格式)
{
#region
#region 從資料庫讀取資料
#region 組SQL字串
System.Text.StringBuilder srb_SQL = new System.Text.StringBuilder();
srb_SQL.Append(@"
select D.ONLINE_NO || C.EXPLAIN as ONLINE_NOANDNAME, count(1) as count
from VDS_CRM_RECORD_MAIN M, VDS_CRM_RECORD_DETL D, SYS_CODE_DETL C,
VDS_STM_STORE_UNIQUE S
where M.ID = D.PID
and M.STORE = S.STORE
and D.ONLINE_NO = C.SCODE
and C.CATEGORY = 'C1'
");
srb_SQL.Append(ToMakeSQL().ToString());
srb_SQL.Append(" group by D.ONLINE_NO || C.EXPLAIN");
srb_SQL.Append(" order by D.ONLINE_NO || C.EXPLAIN ");
#endregion
ArrayList ParameterList = new ArrayList();
ParameterList.Clear();
ParameterList.Add(srb_SQL.ToString());
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("CRM13_客服線上服務彙總表(報表格式)");
DataTable dt_Return = new DataTable();
BCO.QueryCRMRecordReport bco = new BCO.QueryCRMRecordReport(ConntionDB);
dt_Return = bco.PrintSum(ParameterList);
#endregion
if (dt_Return.Rows.Count == 0)
{
ScriptManager.RegisterStartupScript(this.up_ErrorMsg, typeof(UpdatePanel), "隨便寫", "alert('查無資料');", true);
return;
}
else
{
#region CrystalReport列印報表
Show_Report2(dt_Return, "Gather");
Session["Rpt_Data_Gather" + PageTimeStamp.Value] = dt_Return;
#endregion
}
#endregion
}
#region 設定 hid_PageStauts 狀態
#endregion
}
catch (Exception ex)
{
WaringLogProcess(ex.Message);
this.ErrorMsgLabel.Text = ex.Message;
}
#endregion
}
/// <summary>
/// BUTTON 匯出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void but_Out_Click(object sender, EventArgs e)
{
#region
try
{
if (this.rdb_Report_Status.SelectedValue == "0")//代表選擇明細表
{
#region 檢查必填欄位是否輸入
string sb = "";
sb = CheckPage();
if (sb != "")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "CRM131.aspx", sb, true);
return;
}
#endregion
#region 從資料庫讀取資料
#region 組SQL字串
System.Text.StringBuilder srb_SQL = new System.Text.StringBuilder();
srb_SQL.Append(@"
select TO_CHAR(M.BUSDATE,'YYYY/MM/DD') as BUSDATE, M.CHAN_NO, SC.name, D.ONLINE_NO,
(select C.EXPLAIN
from SYS_CODE_DETL C
where C.CATEGORY = 'C1'
and C.SCODE = D.ONLINE_NO) as ONLINE_NO_NAME, M.STORE,
S.STORE_NAME, S.Z_O,
(select C.EXPLAIN
from SYS_CODE_DETL C
where C.CATEGORY = '97'
and C.SCODE = S.Z_O) as Z_O_NAME, RV.ROUTE as ROUTE_ID,
RV.STEP as ROUTE_STEP,M.DEAL_CONTENT
from VDS_CRM_RECORD_MAIN M, VDS_CRM_RECORD_DETL D, VDS_STM_STORECHAN SC,
VDS_STM_STORE_UNIQUE S,VDS_STM_ROUTE_STORE_VIEW RV
where M.ID = D.PID and M.ENABLE=1
and M.CHAN_NO = SC.CODE
and S.CHAN_NO = M.CHAN_NO and S.STORE = M.STORE
and RV.STORE = M.STORE and RV.CHAN_NO =M.CHAN_NO
and RV.EFF_START <= sysdate
and nvl(RV.EFF_END,to_date('99991231','YYYYMMDD')) > sysdate
");
srb_SQL.Append(ToMakeSQL().ToString());
srb_SQL.Append(" order by M.BUSDATE,M.CHAN_NO,D.ONLINE_NO");
#endregion
ArrayList ParameterList = new ArrayList();
ParameterList.Clear();
ParameterList.Add(srb_SQL.ToString());
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("CRM131_客服線上服務明細表(檔案格式)");
DataTable dt_Return = new DataTable();
BCO.QueryCRMRecordReport bco = new BCO.QueryCRMRecordReport(ConntionDB);
dt_Return = bco.Export(ParameterList);
#endregion
if (dt_Return.Rows.Count == 0)
{
ScriptManager.RegisterStartupScript(this.up_ErrorMsg, typeof(UpdatePanel), "隨便寫", "alert('查無資料');", true);
return;
}
else
{
#region 利用Crystal Report 匯出 Excel
string s_FileName = HttpUtility.UrlEncode("客服線上紀錄表.xls", System.Text.Encoding.UTF8);
string s_rptFilePath = Server.MapPath("./REPORT/CRM131/CRM131_Excel_Detl.rpt");
LoadCrystalReport(s_FileName, s_rptFilePath, dt_Return);
#endregion
}
}
}
catch (Exception ex)
{
WaringLogProcess(ex.Message);
this.ErrorMsgLabel.Text = ex.Message;
}
#endregion
}
/// <summary>
/// CRM13_3報表(匯出鈕)
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM13_3(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMRecordReport BCO = new CRMModel.QueryCRMRecordReport(ConntionDB);
DataTable Dt = BCO.Export(ParameterList);
if (Dt == null || Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
//設定使用第三方元件匯出EXCEL
if (ReportOutputFormat == "EXCEL")
{
#region 表頭
Dt.Columns[0].ColumnName = "來電日";
Dt.Columns[1].ColumnName = "通路";
Dt.Columns[2].ColumnName = "通路名稱";
Dt.Columns[3].ColumnName = "原因代號";
Dt.Columns[4].ColumnName = "原因說明";
Dt.Columns[5].ColumnName = "店號";
Dt.Columns[6].ColumnName = "店名";
Dt.Columns[7].ColumnName = "營業所";
Dt.Columns[8].ColumnName = "營業所名稱";
Dt.Columns[9].ColumnName = "路線";
Dt.Columns[10].ColumnName = "路順";
Dt.Columns[11].ColumnName = "處理敘述 ";
#endregion
EOT = ReportList.ExcelOtherType.Normal;
}
return Dt;
#endregion
}
