/// <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 }