/// <summary>
/// CRM14_1報表(列印鈕)-明細表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM14_1(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMTicketReport BCO = new CRMModel.QueryCRMTicketReport(ConntionDB);
DataTable Dt = null;
Dt = BCO.PrintDetail(ParameterList);
if (Dt == null || Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
return Dt;
#endregion
}
/// <summary>
/// CRM02_1報表(匯出鈕)
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM02_1(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMTicketReport BCO = new CRMModel.QueryCRMTicketReport(ConntionDB);
DataTable Dt = BCO.QueryCRMRecordAndTicketByFind(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 = "主管簽核日";
#endregion
EOT = ReportList.ExcelOtherType.Normal;
}
return Dt;
#endregion
}
/// <summary>
/// CRM14_2報表(列印鈕)-彙總表
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM14_2(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMTicketReport BCO = new CRMModel.QueryCRMTicketReport(ConntionDB);
DataTable dt_Return = null, Dt = null;
dt_Return = BCO.PrintSum(ParameterList);
if (dt_Return != null && dt_Return.Rows.Count > 0)
{
#region 將資料彙總成匯總報表
#region 建立彙總TABLE
DataTable dt_Result = new DataTable();
for (int i = 0; i < 9; i++)
{
DataColumn dt_Colum = new DataColumn();
if (i == 0)
{ dt_Colum.ColumnName = "PROC_UNIT"; }
else if (i == 1)
{ dt_Colum.ColumnName = "HANDLE_OK"; }//處理完成
else if (i == 2)
{ dt_Colum.ColumnName = "HANDLE_ING"; }//處理中
else if (i == 3)
{ dt_Colum.ColumnName = "HANDLE_NO"; }//未處理
else if (i == 4)
{ dt_Colum.ColumnName = "BEHIND_HANDLE_OK"; }//逾期處理完成
else if (i == 5)
{ dt_Colum.ColumnName = "BEHIND_HANDLE_ING"; }//逾期處理中
else if (i == 6)
{ dt_Colum.ColumnName = "BEHIND_HANDLE_NO"; }//逾期未處理
else if (i == 7)
{ dt_Colum.ColumnName = "TOTAL_COUNT"; }//總開單數
else if (i == 8)
{ dt_Colum.ColumnName = "ID"; }//ID
if (i == 0)
{ dt_Colum.DataType = System.Type.GetType("System.String"); }
else
{ dt_Colum.DataType = System.Type.GetType("System.Int32"); }
dt_Result.Columns.Add(dt_Colum);
}
#endregion
#region 將資料庫查詢的資料整理寫入彙總TABLE
foreach (DataRow dRow_Return in dt_Return.Rows)
{
if (dt_Result.Select("PROC_UNIT = '" + dRow_Return["PROC_UNIT"].ToString() + "'").Length == 0)
{
DataRow d_NewRow = dt_Result.NewRow();
d_NewRow["PROC_UNIT"] = dRow_Return["PROC_UNIT"].ToString();
d_NewRow["HANDLE_OK"] = 0;
d_NewRow["HANDLE_ING"] = 0;
d_NewRow["HANDLE_NO"] = 0;
d_NewRow["BEHIND_HANDLE_OK"] = 0;
d_NewRow["BEHIND_HANDLE_ING"] = 0;
d_NewRow["BEHIND_HANDLE_NO"] = 0;
d_NewRow["TOTAL_COUNT"] = 0;
dt_Result.Rows.Add(d_NewRow);
}
DataRow dRow = dt_Result.Select("PROC_UNIT = '" + dRow_Return["PROC_UNIT"].ToString() + "'")[0];
int i_RowValue = 0;
int i_RowTotalCount = 0;
string s_RowName = string.Empty;
if (dRow_Return["HANDLE_STATUS"].ToString() == "1")
{ s_RowName = "HANDLE_NO"; }
else if (dRow_Return["HANDLE_STATUS"].ToString() == "2")
{ s_RowName = "HANDLE_ING"; }
else if (dRow_Return["HANDLE_STATUS"].ToString() == "3")
{ s_RowName = "HANDLE_OK"; }
else if (dRow_Return["HANDLE_STATUS"].ToString() == "4")
{ s_RowName = "BEHIND_HANDLE_NO"; }
else if (dRow_Return["HANDLE_STATUS"].ToString() == "5")
{ s_RowName = "BEHIND_HANDLE_ING"; }
else if (dRow_Return["HANDLE_STATUS"].ToString() == "6")
{ s_RowName = "BEHIND_HANDLE_OK"; }
i_RowValue = int.Parse(dRow[s_RowName].ToString());
i_RowTotalCount = int.Parse(dRow["TOTAL_COUNT"].ToString());
dRow[s_RowName] = i_RowValue + 1;
dRow["TOTAL_COUNT"] = i_RowTotalCount + 1;
}
DataView dv = dt_Result.DefaultView;
dv.Sort = "PROC_UNIT";
Dt = dv.ToTable();
#endregion
#endregion
}
if (Dt == null || Dt.Rows.Count == 0)
{
throw new Exception("查無資料");
}
return Dt;
#endregion
}
/// <summary>
/// CRM14_3報表(匯出鈕)
/// </summary>
/// <param name="ParameterList">變數清單</param>
/// <param name="ReportOutputFormat">報表格式(PDF,EXCEL)</param>
/// <param name="EOT">匯出Excel方式</param>
/// <returns>回傳查詢結果</returns>
public DataTable CRM14_3(ArrayList ParameterList,
string ReportOutputFormat,
ref ReportList.ExcelOtherType EOT
)
{
#region
CRMModel.QueryCRMTicketReport BCO = new CRMModel.QueryCRMTicketReport(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 = "處理敘述 ";
#endregion
EOT = ReportList.ExcelOtherType.Normal;
}
return Dt;
#endregion
}
/// <summary>
/// BUTTON TEST列印 按下按鈕
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
try
{
#region 從資料庫讀取資料
#region 組SQL字串
System.Text.StringBuilder srb_SQL = new System.Text.StringBuilder();
srb_SQL.Append(@"
select T.CRM_DATE, T.STORE || S.STORE_NAME as STOREANDNAME,
S.Z_O || (select C.EXPLAIN
from SYS_CODE_DETL C
where C.CATEGORY = '97'
and C.SCODE = S.Z_O) as Z_OANDNAME, U.name,
T.REQUEST_NO || (select C.EXPLAIN
from SYS_CODE_DETL C
where C.CATEGORY = 'C1'
and C.SCODE = T.REQUEST_NO) as REQUEST_NOANDNAME,
T.REQUEST_STATEMENT
from VDS_CRM_TICKET T, VDS_STM_STORE S, SYS_USER U
where T.ENABLE = 1
and T.STORE = S.STORE
and T.BUSUID = U.CODE(+)
and S.SAL_ID = U.CODE
");
srb_SQL.Append("order by T.CHAN_NO, T.CRM_DATE, T.STORE");
#endregion
ParameterList.Clear();
ParameterList.Add(srb_SQL.ToString());
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("CRM141_客服記錄明細表(報表格式)");
DataTable dt_Return = new DataTable();
BCO.QueryCRMTicketReport bco = new BCO.QueryCRMTicketReport(ConntionDB);
dt_Return = bco.PrintDetail(ParameterList);
#endregion
#region 利用CrystalReport列印報表
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_DateRange = string.Empty;
string s_par_StoreChain = string.Empty;
string s_par_CodeFile = string.Empty;
string s_par_Role = string.Empty;
string s_par_User = string.Empty;
string s_par_Handle_Status = string.Empty;
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./../REPORT/CRSTM08R03.rpt"));
report.SetDataSource(dt_Return);
Session["CRSTM08R03"] = report;
s_par_Program_ID = "s_par_Program_ID";
s_par_LoginUser = Session["UID"].ToString();
ParameterFields par1 = new ParameterFields();
AddParameter("par_Program_ID", s_par_Program_ID, par1);
ParameterFields par2 = new ParameterFields();
AddParameter("par_LoginUser", s_par_LoginUser, par2);
//this.CryView.ReportSource = null;
this.CryView.ReportSource = (ReportDocument)Session["CRSTM08R03"];
this.CryView.ParameterFieldInfo.Add(par1[0]);
this.CryView.ParameterFieldInfo.Add(par2[0]);
#endregion
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.ToString(); }
}
/// <summary>
/// 從資料數讀取資料
/// </summary>
/// <returns></returns>
private DataTable GetQueryData()
{
#region
try
{
#region 組SQL字串
System.Text.StringBuilder srb_SQL = new System.Text.StringBuilder();
srb_SQL.Append(@"
select M.CRM_DATE, M.CRM_NO,
(select R1.name
from SYS_USER R1
where R1.CODE = M.BUSUID) as BUSUID_NAME, M.KD, M.REQUEST_NO,
M.REQUEST_STATEMENT,
(select R1.name
from SYS_USER R1
where R1.CODE = M.PROC_MAN) as PROC_MAN_NAME, M.CLOSE_DATE,
M.CONFIRM_DATE, M.ID
from VDS_CRM_MAINA_VIEW M
");
srb_SQL.Append(ToMakeSQL().ToString());
#endregion
ArrayList ParameterList = new ArrayList();
ParameterList.Clear();
ParameterList.Add(srb_SQL.ToString());
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("CRM021_按下Button[查詢]");
DataTable dt_Return = new DataTable();
BCO.QueryCRMTicketReport bco = new BCO.QueryCRMTicketReport(ConntionDB);
dt_Return = bco.QueryCRMRecordAndTicketByFind(ParameterList);
return dt_Return;
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
