/// <summary> /// MKT10報表 /// </summary> /// <param name="ParameterList">變數清單</param> /// <returns>回傳查詢結果</returns> public DataTable MKT13_1(ArrayList ParameterList) { MKTModel.MaintainMarketSurveyRecords bco = new MKTModel.MaintainMarketSurveyRecords(ConntionDB); DataTable Dt = bco.QuerySurveyRec(ParameterList); if (Dt.Rows.Count == 0) { throw new Exception("查無資料"); } else { #region 將上市日期轉為無時間的格式 DataTable dt = Dt.Clone(); dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String"); foreach (DataRow dr in Dt.Rows) { DataRow DR = dt.NewRow(); for (int i = 0; i < Dt.Columns.Count; i++) DR[i] = dr[i]; if (DR["PUBLISH_DATE"] != System.DBNull.Value) DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString(); dt.Rows.Add(DR); } #endregion return dt; } }
protected void Btn_ExportSurvey_Click(object sender, EventArgs e) { try { ErrorMsgLabel.Text = ""; #region 資料檢查 if (SLP_CodeFile1.Text == "" || SLP_CodeFile2.Text == "") { ErrorMsgLabel.Text = "營業所不可選全部"; return; } if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) != 0) { ErrorMsgLabel.Text = "只能選一個營業所"; return; } if (dSurveyDate.Text == "") { ErrorMsgLabel.Text = "市調日期不可空白"; return; } #endregion DataTable dt = new DataTable(); #region 查詢市調情報資料 //取得查詢參數: 營業所,市調日期 ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所 ParameterList.Add(GetValueSetParameter(dSurveyDate.Text, "date")); //市調日期 ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員 2010/09/24 modified ParameterList.Add(GetValueSetParameter("9999", "int")); //ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2 ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄) BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB); dt = bco.QuerySurveyRec(ParameterList); #endregion #region 將上市日期轉為無時間的格式 DataTable Dt = dt.Clone(); if (dt.Rows.Count > 0) { Dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String"); foreach (DataRow dr in dt.Rows) { DataRow DR = Dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) DR[i] = dr[i]; if (DR["PUBLISH_DATE"] != System.DBNull.Value) DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString(); Dt.Rows.Add(DR); } dt.Dispose(); dt = null; } #endregion if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) { //若不存在市調情報資料,無法匯出市調表 ErrorMsgLabel.Text = "查無資料"; } else { //若已存在市調情報資料,匯出市調表 #region 匯出市調表 //設定輸出檔名 string s_FileName = ""; s_FileName = HttpUtility.UrlEncode("市調表.xls", System.Text.Encoding.UTF8); #region 設定Excel Worksheet //Excel Worksheet initilize ExcelXmlWorkbook book = new ExcelXmlWorkbook(); Worksheet sheet = book[0]; sheet.Name = "市調表"; //設定Worksheet橫印 sheet.PrintOptions.Orientation = PageOrientation.Landscape; sheet.PrintOptions.SetMargins(0.5, 0.7, 0.5, 0.6); //設定Worksheet各column 寬度 sheet.Columns(0).Width = 55; sheet.Columns(1).Width = 130; sheet.Columns(2).Width = 43; sheet.Columns(3).Width = 58; sheet.Columns(4).Width = 30; sheet.Columns(5).Width = 42; sheet.Columns(6).Width = 30; sheet.Columns(7).Width = 30; sheet.Columns(8).Width = 30; sheet.Columns(9).Width = 30; sheet.Columns(10).Width = 42; sheet.Columns(11).Width = 30; sheet.Columns(12).Width = 30; sheet.Columns(13).Width = 30; sheet.Columns(14).Width = 30; sheet.Columns(15).Width = 42; sheet.Columns(16).Width = 30; sheet.Columns(17).Width = 30; sheet.Columns(18).Width = 30; sheet.Columns(19).Width = 30; //設定外框線 sheet.Border.Sides = BorderSides.All; sheet.Border.LineStyle = Borderline.Continuous; #endregion int iRow = 0; //Worksheet 列數 string strSTORE = ""; ArrayList alTITLE = new ArrayList();//營業所,市調人員ArrayList ArrayList alSTORE = new ArrayList();//店號,店名ArrayList int iStoreCount = 0; //總店數 //營業所,市調人員ArrayList foreach (DataRow dr in Dt.Rows) { alTITLE.Add(new string[] { dr["EXPLAIN"].ToString(), dr["INVESTIGATOR"].ToString() }); break; //(營業所,市調人員)都相同, 取第一筆即可 } int iRowStore = 3;//每行店數 int iStoreCol = 5;//每個店有幾個欄位 foreach (string[] ss in alTITLE) { //取得店號,店名,市調人員ArrayList foreach (DataRow dr in Dt.Select("ITEM='" + Dt.Rows[0]["ITEM"].ToString() + "'")) { alSTORE.Add(new string[] { dr["STORE"].ToString(), dr["STORE_NAME"].ToString(), dr["INVESTIGATOR"].ToString() });//20121204 顏筱盈 } //總店數 iStoreCount = alSTORE.Count; for (int iStoreArea = 0; iStoreArea < Convert.ToInt32(Math.Ceiling(alSTORE.Count / (decimal)iRowStore)); iStoreArea++) { #region 設定Header列資訊 ArrayList alQTY = new ArrayList(); sheet[0, iRow].Value = ss[0]; //營業所 //sheet[1, iRow].Value = ss[1]; //市調人員 sheet[1, iRow].Value = ""; //20121204 顏筱盈 sheet[2, iRow].Value = "期別"; sheet[3, iRow].Value = "上市日期"; sheet[4, iRow].Value = "市調"; new Range(sheet[0, iRow + 1], sheet[3, iRow + 1]).Merge(); sheet[4, iRow + 1].Value = "次數"; for (int iStoreNo = (iRowStore * iStoreArea); iStoreNo < (iRowStore * iStoreArea + iRowStore); iStoreNo++) { if (iStoreNo >= alSTORE.Count) break; string[] ss1 = (string[])alSTORE[iStoreNo]; sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow].Value = ss1[0];//店號 //sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1];//店名 sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1] + " ( " +ss1[2] + " )";//店名,市調人員 20121204 顏筱盈 new Range(sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow], sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow]).Merge(); sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow + 1].Value = "發書"; alQTY.Add((iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5); sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow + 1].Value = "補書"; sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 7, iRow + 1].Value = "退書"; sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 8, iRow + 1].Value = "庫存"; sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow + 1].Value = "備註"; } #endregion iRow = iRow + 2; int iItemCount = 0; //商品項數 //取得商品相關資料 foreach (DataRow dr in Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea])[0] + "'", "ITEM,ROUTE,STEP")) { sheet[0, iRow].Value = dr["ITEM"].ToString(); //品號 sheet[1, iRow].Value = dr["ITEM_NAME"].ToString(); //品名 sheet[2, iRow].Value = dr["PERIOD"].ToString(); //期別 sheet[3, iRow].Value = dr["PUBLISH_DATE"].ToString(); //上市日期 sheet[4, iRow].Value = dr["SURVEY_TIME"].ToString(); //市調次數 int iStore = 0; foreach (object o in alQTY) { DataRow[] drQty = Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea + iStore])[0] + "'" + " AND ITEM='" + dr["ITEM"].ToString() + "'" + " AND PERIOD='" + dr["PERIOD"].ToString() + "'", "ITEM,ROUTE,STEP"); sheet[Convert.ToInt32(o), iRow].Value = drQty[0]["INCOME_QTY"].ToString(); //發書 sheet[Convert.ToInt32(o) + 1, iRow].Value = drQty[0]["REPLINISHMENT_QTY"].ToString(); //補書 sheet[Convert.ToInt32(o) + 2, iRow].Value = drQty[0]["RETURN_QTY"].ToString(); //退書 iStore++; } iRow++; iItemCount++; } iRow++; //商品總項數設定為20,不足則填入空列數 if (iItemCount != 0 & iItemCount < 20) { for (int iAdd = iItemCount; iAdd < 20; iAdd++) { iRow++; } } } } #region 報表最後一頁特別處理 double dStoreCal = (double)(iStoreCount / (decimal)iRowStore); int iNewBeginRow = Convert.ToInt32(Math.Ceiling(dStoreCal)) * 23; //報表最後一頁,最後一列,最後一格 給一空白, 這樣最後一頁印出才會完整 if (Math.Ceiling(dStoreCal) % 2 == 1) { sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1 + 23].Value = " "; } else { sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1].Value = " "; } #endregion //設定報表 縮放比例 int iPageCount = Convert.ToInt32(Math.Ceiling(dStoreCal / 2.0)); sheet.PrintOptions.SetFitToPage(1, iPageCount); //Sets excel's fit to page property (int width,int height) //File download Response.Charset = "big5"; Response.ContentType = "application/vnd.ms-excel;charset='utf-8'"; Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名 System.IO.MemoryStream stream = new System.IO.MemoryStream(); book.Export(stream); string producedExcel = GetStringFromMemoryStream(stream); stream.Close(); Response.Write(producedExcel); Response.Flush(); Response.End(); #endregion } } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; } }
protected void Btn_ExportResult_Click(object sender, EventArgs e) { try { #region 資料檢查 if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) > 0) { ErrorMsgLabel.Text = "營業所(起)不可大於營業所(迄)"; return; } if (dSurveyDate.Text == "") { ErrorMsgLabel.Text = "市調日期不可空白"; return; } #endregion DataTable Dt = new DataTable(); #region 查詢市調情報資料 //取得查詢參數: 營業所,市調日期 ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(this.SLP_CodeFile1.Text, "string")); //營業所 ParameterList.Add(GetValueSetParameter(this.dSurveyDate.Text, "date")); //市調日期 ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員 2010/09/30 modified ParameterList.Add(GetValueSetParameter("9999", "int")); //ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2 ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄) BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB); Dt = bco.QuerySurveyRec(ParameterList); #endregion if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) { //若不存在市調情報資料,無法匯出市調結果 ErrorMsgLabel.Text = "查無資料"; } else { #region 將上市日期轉為無時間的格式 DataTable dt = Dt.Clone(); dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String"); foreach (DataRow dr in Dt.Rows) { DataRow DR = dt.NewRow(); for (int i = 0; i < Dt.Columns.Count; i++) DR[i] = dr[i]; if (DR["PUBLISH_DATE"] != System.DBNull.Value) DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString(); dt.Rows.Add(DR); } Dt.Dispose(); Dt = null; #endregion //若已存在市調情報資料,匯出市調結果 #region 匯出市調結果 string s_FileName = ""; //設定輸出檔名 string s_rptFilePath = ""; //設定報表路徑 s_rptFilePath = Server.MapPath("./REPORT/MKT131/MKT131_Excel.rpt"); s_FileName = HttpUtility.UrlEncode("市調情報資料.xls", System.Text.Encoding.UTF8); LoadCrystalReport(s_FileName, s_rptFilePath, dt, "EXCEL"); #endregion } } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; } }
protected void Btn_Query_Click(object sender, EventArgs e) { try { #region 資料檢查 if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) > 0) { ErrorMsgLabel.Text = "營業所(起)不可大於營業所(迄)"; return; } if (dSurveyDate.Text == "") { ErrorMsgLabel.Text = "市調日期不可空白"; return; } #endregion DataTable Dt = new DataTable(); #region 處理查詢 BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB); Dt = bco.QuerySurveyRec(this.getParameterList()); #endregion if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) ErrorMsgLabel.Text = "查無資料"; else Session["MKT131_" + PageTimeStamp.Value] = Dt.Copy(); #region 設定查詢結果 this.GridView1.DataSource = Dt; this.GridView1.PageSize = (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) < 1) ? 10 : int.Parse(TextBoxPagesize.Text); this.GridView1.PageIndex = 0; this.GridView1.DataBind(); #endregion } catch (Exception ex) { this.ErrorMsgLabel.Text = ex.ToString(); } finally { } }