/// <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 GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { #region 處理刪除作業 try { BCO.MaintainMarketSurveyRecords bco = new BCO.MaintainMarketSurveyRecords(ConnectionDB); DataTable dt = (DataTable)Session["MKT132_MAIN_" + PageTimeStamp.Value]; int iRow = (this.GridView1.PageSize * this.GridView1.PageIndex) + e.RowIndex; ParameterList.Clear(); ParameterList.Add(Session["UID"].ToString()); bco.DeleteMasterAndDetail(ParameterList, dt.Rows[iRow], null); ScriptManager.RegisterStartupScript(Page, this.GetType(), "ClientScript", "alert('刪除完成');", true); #region 刪除後狀態設定 this.hid_PageStatus.Value = "VIEW"; this.txt_PageStatus.Text = "VIEW"; this.hiddenID.Value = "0";//設定檢視市調商品 PID=0, 表示沒資料, 不顯示市調商品 SLP_StoreChain1.Text = "";//設定檢視通路 SLP_Store1.Text = "";//設定檢視門市 panel_ViewDetl_Store.Visible = false;//設定檢視通路,門市不顯示 #endregion } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; return; } finally { if (this.ErrorMsgLabel.Text == "") { SetPageStatus(); } } #endregion }
protected void Btn_Delete_Click(object sender, EventArgs e) { try { //檢查是否有市調情報資料 if (this.GridView1.Rows.Count == 0) { ErrorMsgLabel.Text = "無市調情報資料"; return; } ParameterList.Clear(); ParameterList.Add(Session["UID"].ToString()); BCO.MaintainMarketSurveyRecords bco = new BCO.MaintainMarketSurveyRecords(ConnectionDB); bco.DeleteMultiMasterAndDetail(ParameterList, (DataTable)(Session["MKT132_MAIN_" + PageTimeStamp.Value]), null); ScriptManager.RegisterStartupScript(Page, this.GetType(), "ClientScript", "alert('刪除完成');location.replace('MKT131.aspx?&Code=MKT13');", true); #region 存檔後狀態設定 this.hid_PageStatus.Value = "VIEW"; this.txt_PageStatus.Text = "VIEW"; this.hiddenID.Value = "0";//設定檢視市調商品 PID=0, 表示沒資料, 不顯示市調商品 SLP_StoreChain1.Text = "";//設定檢視通路 SLP_Store1.Text = "";//設定檢視門市 panel_ViewDetl_Store.Visible = false;//設定檢視通路,門市不顯示 #endregion } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; return; } finally { if (this.ErrorMsgLabel.Text == "") { SetPageStatus(); } } }
protected void Btn_Save_Click(object sender, EventArgs e) { try { //檢查是否有市調情報資料 if (this.GridView1.Rows.Count == 0) { ErrorMsgLabel.Text = "無市調情報資料"; return; } #region 收集本次市調商品資料 DataTable dtDetail = (DataTable)(Session["MKT132_DTL_" + PageTimeStamp.Value]); foreach (GridViewRow gvRow in this.gvDtl.Rows) { string strNEW_STOCKS_QTY = ((TextBox)gvRow.Cells[11].Controls[1]).Text; string strNEW_MEMO = ((TextBox)gvRow.Cells[12].Controls[1]).Text; int iRow = gvRow.RowIndex; //備註 dtDetail.Rows[iRow]["NEW_MEMO"] = GetValueSetParameter(strNEW_MEMO, "string"); //庫存 dtDetail.Rows[iRow]["NEW_STOCKS_QTY"] = GetValueSetParameter(((TextBox)gvRow.Cells[11].Controls[1]).Text, "int"); } dtDetail.AcceptChanges(); Session["MKT132_DTL_" + PageTimeStamp.Value] = dtDetail; #endregion ParameterList.Clear(); ParameterList.Add(this.txtInvestigator.Text); //市調人員 ParameterList.Add(Session["UID"].ToString()); BCO.MaintainMarketSurveyRecords bco = new BCO.MaintainMarketSurveyRecords(ConnectionDB); bco.UpdateMasterAndDetail(int.Parse(this.hiddenID.Value), ParameterList, (DataTable)(Session["MKT132_MAIN_" + PageTimeStamp.Value]), dtDetail, null); #region 存檔後狀態設定 this.hid_PageStatus.Value = "VIEW"; this.txt_PageStatus.Text = "VIEW"; this.hiddenID.Value = "0";//設定檢視市調商品 PID=0, 表示沒資料, 不顯示市調商品 SLP_StoreChain1.Text = "";//設定檢視通路 SLP_Store1.Text = "";//設定檢視門市 panel_ViewDetl_Store.Visible = false;//設定檢視通路,門市不顯示 #endregion } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; return; } finally { if (this.ErrorMsgLabel.Text == "") { SetPageStatus(); } } }
}//設定各種模式預設值 /// <summary> /// 繫結資料 MST /// </summary> private void databind_Mst() { #region try { DataTable Dt = new DataTable(); BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB); ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(this.SLP_CodeFile1.Text, "string")); //營業所 ParameterList.Add(GetValueSetParameter(this.dSurveyDate.Text, "date")); //市調日期 ParameterList.Add(GetValueSetParameter("", "string")); //市調人員 不納入查詢條件 //if (Request.QueryString["Z_O_SUB"] != null) //{ // ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2 // Dt = bco.QuerySurveyRecMain_1(ParameterList); //} //else // Dt = bco.QuerySurveyRecMain(ParameterList); ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄) Dt = bco.QuerySurveyRecMain_1(ParameterList); if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) ErrorMsgLabel.Text = "查無資料"; else this.txtInvestigator.Text = Dt.Rows[0]["INVESTIGATOR"].ToString(); #region 設定查詢結果 this.GridView1.DataSource = Dt; this.GridView1.PageSize = 20;// (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) < 0) ? 10 : int.Parse(TextBoxPagesize.Text); this.GridView1.PageIndex = 0; this.GridView1.DataBind(); Session["MKT132_MAIN_" + PageTimeStamp.Value] = Dt; #endregion } catch (Exception ex) { this.ErrorMsgLabel.Text = ex.ToString(); } finally { } #endregion }//databind_Mst
protected void Btn_Execute_Click(object sender, EventArgs e) { try { ErrorMsgLabel.Text = ""; string strZ_O = this.SLP_CodeFile1.Text; string strOldChanNo = this.SLP_StoreChain_OLD.Text; string strOldStore = this.SLP_Store_OLD.Text; string strSurveyDate = this.txtSURVEY_DATE.Text; string strNewChanNo = this.SLP_StoreChain_NEW.Text; string strNewStore = this.SLP_Store_NEW.Text; #region 門市 營業所Check if (SLP_CodeFile1.Text != "") { #region 原門市 營業所Check ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(strOldStore, "string")); BCO.MaintainMarketSurveyRecords bcoOldZ_O = new BCO.MaintainMarketSurveyRecords(ConnectionDB); DataTable dtZ_O = bcoOldZ_O.QueryZ_OByStore(ParameterList); if (dtZ_O == null || dtZ_O.Rows.Count == 0 || dtZ_O.Rows[0][0].ToString() != strZ_O) { ErrorMsgLabel.Text = "原門市不屬於此營業所範圍"; return; } #endregion #region 新門市 營業所Check ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(strNewStore, "string")); BCO.MaintainMarketSurveyRecords bcoNewZ_O = new BCO.MaintainMarketSurveyRecords(ConnectionDB); DataTable dtZ_ONew = bcoNewZ_O.QueryZ_OByStore(ParameterList); if (dtZ_ONew == null || dtZ_ONew.Rows.Count == 0 || dtZ_ONew.Rows[0][0].ToString() != strZ_O) { ErrorMsgLabel.Text = "新門市不屬於此營業所範圍"; return; } #endregion } #endregion #region 檢查原門市資料是否已存在市調計畫 ParameterList.Clear(); ParameterList.Add(""); ParameterList.Add(""); ParameterList.Add(GetValueSetParameter(strOldChanNo, "string")); ParameterList.Add(GetValueSetParameter(strOldStore, "string")); ParameterList.Add(GetValueSetParameter(strSurveyDate, "date")); ParameterList.Add(GetValueSetParameter(Session["UID"].ToString(), "string")); ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所 BCO.MaintainMarketSurveyRecords bcoMainOld = new BCO.MaintainMarketSurveyRecords(ConnectionDB); DataTable dtExistMainOld = bcoMainOld.QueryExistPk(ParameterList); if (dtExistMainOld == null || dtExistMainOld.Rows.Count == 0) { ErrorMsgLabel.Text = "原門市資料不存在市調計畫,無法複製"; return; } #endregion #region 組合複製市調計畫條件至ArrayList ParameterList.Clear(); ParameterList.Add(GetValueSetParameter(strOldChanNo, "string")); ParameterList.Add(GetValueSetParameter(strOldStore, "string")); ParameterList.Add(GetValueSetParameter(strNewChanNo, "string")); ParameterList.Add(GetValueSetParameter(strNewStore, "string")); ParameterList.Add(GetValueSetParameter(strSurveyDate, "date")); ParameterList.Add(GetValueSetParameter(Session["UID"].ToString(), "string")); ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所 #endregion #region 新門市必需存在於市調門市檔 BCO.MaintainMarketSurveyStore bco = new BCO.MaintainMarketSurveyStore(ConnectionDB); DataTable dtExist = bco.QueryExistPk(ParameterList); if (dtExist == null || dtExist.Rows.Count == 0) { ErrorMsgLabel.Text = "新門市不存在於市調門市檔"; return; } #endregion #region 檢查新門市資料是否已存在市調計畫 BCO.MaintainMarketSurveyRecords bcoMain = new BCO.MaintainMarketSurveyRecords(ConnectionDB); DataTable dtExistMain = bcoMain.QueryExistPk(ParameterList); if (dtExistMain != null && dtExistMain.Rows.Count > 0) { ErrorMsgLabel.Text = "複製市調計畫至新門市失敗, 新門市資料已存在市調計畫"; return; } #endregion #region 複製市調計畫至新門市 BCO.MaintainMarketSurveyRecords bcoCopy = new BCO.MaintainMarketSurveyRecords(ConnectionDB); // 執行複製市調計畫至新門市的動作 bcoCopy.CopyToNewStore(ParameterList, null); #endregion this.ErrorMsgLabel.Text = "複製市調計畫成功"; #region 關掉POP視窗 //ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Window", "alert('儲存成功');window.close();", true); #endregion } catch (Exception ex) { this.ErrorMsgLabel.Text = ex.ToString(); } finally { } }
/// <summary> /// 繫結舊有營業所,市調日期資料 /// </summary> private void databind_Old() { #region try { 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("", "string")); //市調人員 不納入查詢條件 #endregion #region 處理查詢 BCO.MaintainMarketSurveyRecords bcoMain = new MaintainMarketSurveyRecords(ConnectionDB); Dt = bcoMain.QuerySurveyRecMain(ParameterList); #endregion if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) { Session["MKT133_MAIN_OLD" + PageTimeStamp.Value] = null; return; } this.txtInvestigator.Text = Dt.Rows[0]["INVESTIGATOR"].ToString(); #region 設定查詢結果 this.GridView1.DataSource = Dt; this.GridView1.PageSize = 20;// (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) < 0) ? 10 : int.Parse(TextBoxPagesize.Text); this.GridView1.PageIndex = 0; this.GridView1.DataBind(); Session["MKT133_MAIN_" + PageTimeStamp.Value] = Dt.Copy(); Session["MKT133_MAIN_OLD" + PageTimeStamp.Value] = Dt.Copy(); #endregion BCO.MaintainMarketSurveyRecordsDtl bco = new BCO.MaintainMarketSurveyRecordsDtl(ConnectionDB); ParameterList.Clear(); ParameterList.Add(Dt.Rows[0]["ID"].ToString()); DataTable dtDtl = bco.QuerySurveyRecDetlByPID(ParameterList); //設定市調商品 column DataTable dt = new DataTable(); DataColumn dcNew; dcNew = new DataColumn("ITEM", Type.GetType("System.String")); dt.Columns.Add(dcNew); dcNew = new DataColumn("PERIOD", Type.GetType("System.String")); dt.Columns.Add(dcNew); dcNew = new DataColumn("PUBLISH_DATE", Type.GetType("System.String")); dt.Columns.Add(dcNew); dcNew = new DataColumn("SURVEY_TIME", Type.GetType("System.Int32")); dt.Columns.Add(dcNew); //產生20筆資料供編輯 for (int i = 0; i < 20; i++) { DataRow drNew; drNew = dt.NewRow(); if (i < dtDtl.Rows.Count) { drNew["ITEM"] = dtDtl.Rows[i]["ITEM"].ToString(); drNew["PERIOD"] = dtDtl.Rows[i]["PERIOD"].ToString(); //drNew["PUBLISH_DATE"] = ((DateTime)dtDtl.Rows[i]["PUBLISH_DATE"]).ToString("yyyy/MM/dd"); drNew["SURVEY_TIME"] = GetValueSetParameter(dtDtl.Rows[i]["SURVEY_TIME"].ToString(), "int"); } else { drNew["ITEM"] = ""; drNew["PERIOD"] = ""; drNew["PUBLISH_DATE"] = DBNull.Value; drNew["SURVEY_TIME"] = DBNull.Value; } dt.Rows.Add(drNew); } dt.AcceptChanges(); this.gvDtl.DataSource = dt; this.gvDtl.DataBind(); } catch (Exception ex) { this.ErrorMsgLabel.Text = ex.ToString(); } finally { } #endregion }//databind_Old
protected void Btn_Save_Click(object sender, EventArgs e) { ErrorMsgLabel.Text = ""; try { //檢查是否設定本次市調門市 if (this.GridView1.Rows.Count == 0) { ErrorMsgLabel.Text = "請先設定本次市調門市"; return; } #region 收集本次市調商品資料 Hashtable htDetail = new Hashtable(); foreach (GridViewRow gvRow in this.gvDtl.Rows) { if (((TextBox)gvRow.Cells[4].Controls[1]).Text != "") //市調次數<> "" { string strItem = ((TextBox)gvRow.Cells[1].Controls[1].FindControl("TextBoxCode")).Text; string strPeriod = ((TextBox)gvRow.Cells[2].Controls[1].FindControl("TextBoxCode")).Text; string strItemPeriod = strItem + strPeriod; if (!htDetail.ContainsKey(strItemPeriod)) { ArrayList al = new ArrayList(); al.Add(strItem); al.Add(strPeriod); al.Add(((TextBox)gvRow.Cells[3].Controls[1]).Text); //上市日期 al.Add(((TextBox)gvRow.Cells[4].Controls[1]).Text); //市調次數 htDetail.Add(strItemPeriod, al); } else { ErrorMsgLabel.Text = "商品資料重複:" + strItem + ",期別:" + strPeriod; return; } } } #endregion //檢查是否設定本次市調商品 if (htDetail.Count == 0) { ErrorMsgLabel.Text = "請先設定本次市調商品"; return; } #region 取得新增作業共用參數: 市調日期,市調人員,user id //因為z_o為pk,不能空白,當選擇空白時,預設營業所1=全部... add by jsy 2009/12/30 string v_zo = SLP_CodeFile1.Text; if (v_zo == "") { v_zo = "1"; } ParameterList.Clear(); ParameterList.Add(dSurveyDate.Text); //市調日期 ParameterList.Add(txtInvestigator.Text); //市調人員 ParameterList.Add(Session["UID"].ToString()); ParameterList.Add(v_zo); //營業所 #endregion #region 處理新增作業 DataTable dtNewMaster = (DataTable)(Session["MKT133_MAIN_" + PageTimeStamp.Value]); BCO.MaintainMarketSurveyRecords bco = new BCO.MaintainMarketSurveyRecords(ConnectionDB); int v_ID = 0; if (Session["MKT133_MAIN_OLD" + PageTimeStamp.Value] == null) { //新增Master&Detail v_ID = bco.CreateMultiMasterAndDetail(ParameterList, (DataTable)(Session["MKT133_MAIN_" + PageTimeStamp.Value]), htDetail, null); } else { //先刪除舊Master&Detail,再新增新Master&Detail DataTable dtOldMaster = (DataTable)(Session["MKT133_MAIN_OLD" + PageTimeStamp.Value]); v_ID = bco.NewMultiMasterDetailWithOld(ParameterList, dtOldMaster, dtNewMaster, htDetail, null); } if (v_ID == 0) { throw new Exception("新增0筆資料!"); } #endregion //新增作業完成後,畫面導向編輯檢視畫面 Response.Redirect(string.Format("MKT132.aspx?Mode=VIEW&Code=MKT13&Z_O={0}&SURVEY_DATE={1}&INVESTIGATOR={2}", SLP_CodeFile1.Text, dSurveyDate.Text, txtInvestigator.Text), false); } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; return; } }
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_Maintain_Click(object sender, EventArgs e) { try { ErrorMsgLabel.Text = ""; #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/29 modified //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.QuerySurveyRecMain_1(ParameterList); #endregion if (Dt == null || (Dt != null && Dt.Rows.Count <= 0)) { //若不存在市調情報資料 ErrorMsgLabel.Text = "不存在市調情報資料,請新增市調情報"; return; } else { //若已存在市調情報資料,至編輯檢視市調情報維護畫面 Response.Redirect(string.Format("MKT132.aspx?Mode=VIEW&Code=MKT13&Z_O={0}&SURVEY_DATE={1}&INVESTIGATOR={2}&Z_O_SUB={3}", SLP_CodeFile1.Text, dSurveyDate.Text, txtInvestigator.Text, SLP_CodeFile2.Text), false); } } 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 { } }
//執行匯入 protected void but_Temp_To_DB_Click(object sender, EventArgs e) { try { ArrayList arl_Return = null; string s_LoginUser = string.Empty; string s_UploadPath_File = string.Empty; string s_BackPath_File = string.Empty; s_LoginUser = Session["UID"].ToString();//登入人員 BCO.MaintainMarketSurveyRecords BCO = new BCO.MaintainMarketSurveyRecords(ConnectionDB); UploadStatusPanel1.ImportSum = 0; UploadStatusPanel1.ImportOK = 0; UploadStatusPanel1.ImportNG = 0; //將資料由 TEMP TABLE 新增至正式 TABLE arl_Return = BCO.UpdateTmpToDB(s_LoginUser); if (arl_Return[0].ToString() == "TRUE") { if (arl_Return[1].ToString() == "0") { ErrorMsgLabel.Text = "找不到匯入資料,請重新執行匯入動作"; } else { //匯入結果 UploadStatusPanel1.ImportSum = int.Parse(arl_Return[1].ToString()); UploadStatusPanel1.ImportOK = int.Parse(arl_Return[2].ToString()); UploadStatusPanel1.ImportNG = 0; ErrorMsgLabel.Text = "執行匯入完成"; } } else { ErrorMsgLabel.Text = arl_Return[1].ToString(); } but_Temp_To_DB.Enabled = false; but_File_To_Temp.Enabled = true; } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; } }
private bool ClientFileToServerTemp() { bool bResult = false; try { if (FileUpload1.FileName == "") return bResult = false; string s_UploadPath = string.Empty; string s_BackPath = string.Empty; string s_LoginUser = string.Empty; ArrayList arl_FileUpload_Return = null; ArrayList arl_FileToTmp_Return = null; BCO.MaintainMarketSurveyRecords BCO = new BCO.MaintainMarketSurveyRecords(ConnectionDB); #region 上傳檔案到AP端 //取得要上傳的路徑 s_UploadPath = Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["UploadPath"]).Replace("MKT\\", "") + "\\MKT\\"; s_LoginUser = Session["UID"].ToString(); arl_FileUpload_Return = BCO.FileUpload(s_UploadPath, FileUpload1, s_LoginUser); #endregion if (arl_FileUpload_Return[0].ToString() == "FALSE")//檔案上傳至AP端錯誤 { bResult = false;//檔案上傳至AP端錯誤,後面直接不做了 ErrorMsgLabel.Text = arl_FileUpload_Return[1].ToString(); } else if (arl_FileUpload_Return[0].ToString() == "TRUE")//檔案上傳至AP端正常 { #region // 讀取 Excel 資料 DataSet ds = Load_Excel(arl_FileUpload_Return[1].ToString()); DataTable dtExcel = ExcelToTemp(ds.Tables[0]); ArrayList Para = new ArrayList(); foreach (System.Data.DataRow dr in dtExcel.Rows) { //Check Cell Type if ((dr["Z_O_ALL"].ToString().Trim() != "") && (dr["Z_O_ALL"].ToString().Trim() != "全部")) { dr["ERROR"] = dr["ERROR"].ToString() + "全部欄位只允許輸入[全部]或空白;"; } DateTime dSURVEY_DATE; if (DateTime.TryParse(dr["SURVEY_DATE"].ToString(), out dSURVEY_DATE) == false) { dr["SURVEY_DATE"] = DBNull.Value; dr["ERROR"] = dr["ERROR"].ToString() + "市調日期欄位非日期格式;"; } else { dr["SURVEY_DATE"] = dSURVEY_DATE.ToString("yyyy/MM/dd"); } int iSTOCKS_QTY = 0; if (int.TryParse(dr["STOCKS_QTY"].ToString(), out iSTOCKS_QTY) == false) { dr["STOCKS_QTY"] = "0"; dr["ERROR"] = dr["ERROR"].ToString() + "庫存欄位非數值格式;"; } //Check Cell length string strErrMsg = string.Empty; dr["Z_O"] = CheckLength(dr["Z_O"].ToString(), 2, "營業所", strErrMsg, out strErrMsg); dr["Z_O_NAME"] = CheckLength(dr["Z_O_NAME"].ToString(), 50, "營業所名稱", strErrMsg, out strErrMsg); dr["CHAN_NO"] = CheckLength(dr["CHAN_NO"].ToString(), 2, "通路代號", strErrMsg, out strErrMsg); dr["CHAN_NAME"] = CheckLength(dr["CHAN_NAME"].ToString(), 50, "通路名稱", strErrMsg, out strErrMsg); dr["STORE"] = CheckLength(dr["STORE"].ToString(), 10, "門市", strErrMsg, out strErrMsg); dr["STORE_NAME"] = CheckLength(dr["STORE_NAME"].ToString(), 50, "門市名稱", strErrMsg, out strErrMsg); dr["ITEM"] = CheckLength(dr["ITEM"].ToString(), 10, "品號", strErrMsg, out strErrMsg); dr["ITEM_NAME"] = CheckLength(dr["ITEM_NAME"].ToString(), 30, "品名", strErrMsg, out strErrMsg); dr["PERIOD"] = CheckLength(dr["PERIOD"].ToString(), 6, "期別", strErrMsg, out strErrMsg); dr["SURVEY_DATE"] = CheckLength(dr["SURVEY_DATE"].ToString(), 10, "市調日期", strErrMsg, out strErrMsg); dr["STOCKS_QTY"] = CheckLength(dr["STOCKS_QTY"].ToString(), 8, "庫存", strErrMsg, out strErrMsg); dr["MEMO"] = CheckLength(dr["MEMO"].ToString(), 20, "備註", strErrMsg, out strErrMsg); dr["ERROR"] = dr["ERROR"].ToString() + strErrMsg; dtExcel.AcceptChanges(); //檢核是否有重覆的營業所/通路/門市/市調日期/品號/期別 DataRow[] FindDupRow = dtExcel.Select("Z_O='" + dr["Z_O"].ToString() + "' And CHAN_NO='" + dr["CHAN_NO"].ToString() + "' And STORE='" + dr["STORE"].ToString() + "' And SURVEY_DATE='" + dr["SURVEY_DATE"].ToString() + "' And ITEM='" + dr["ITEM"].ToString() + "' And PERIOD='" + dr["PERIOD"].ToString() + "'"); if (FindDupRow.Length > 1) { for (int i = 0; i < FindDupRow.Length; i++) { if (i > 0) //第一筆重覆值不寫入錯誤 { if (FindDupRow[i]["SEQ"].ToString() == dr["SEQ"].ToString()) { FindDupRow[i]["ERROR"] = FindDupRow[i]["ERROR"].ToString() + "與第" + FindDupRow[0]["SEQ"].ToString() + "行資料重覆"; dtExcel.AcceptChanges(); break; } } } } //檢核是否有 營業所/通路/門市/市調日期/品號/期別 是否與DB的資料相符 Para.Clear(); Para.Add(dr["Z_O_ALL"]); Para.Add(dr["Z_O"]); Para.Add(dr["CHAN_NO"]); Para.Add(dr["STORE"]); Para.Add(dr["SURVEY_DATE"]); Para.Add(dr["ITEM"]); Para.Add(dr["PERIOD"]); DataTable dtCheck = BCO.CheckImportExist(Para); if (dtCheck.Rows.Count == 0) { DataRow[] FindRows = dtExcel.Select("SEQ='" + dr["SEQ"].ToString() + "'"); FindRows[0]["ERROR"] = FindRows[0]["ERROR"] + "找不到對應的資料;"; dtExcel.AcceptChanges(); } } #region 將檔案匯入TmpTable DateTime d_CreateDate = Convert.ToDateTime(arl_FileUpload_Return[2]); s_AP_FileName = string.Empty; s_AP_FileName = arl_FileUpload_Return[3].ToString(); arl_FileToTmp_Return = BCO.FileToTmp(dtExcel, d_CreateDate, s_LoginUser, s_AP_FileName); #endregion if (arl_FileToTmp_Return[0].ToString() == "FALSE") { but_Temp_To_DB.Enabled = false; bResult = false; ErrorMsgLabel.Text = arl_FileToTmp_Return[1].ToString(); return bResult = false; } else if (arl_FileToTmp_Return[0].ToString() == "TRUE") { if (((bool)arl_FileToTmp_Return[1]) == false) { this.but_Temp_To_DB.Enabled = false; }//代表內容邏輯不正確 else { this.but_Temp_To_DB.Enabled = true; } #region 將資料從 UploadPath 移至 BackPath s_BackPath = Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["BackPath"]).Replace("MKT\\", "") + "\\MKT\\"; s_UploadPath += s_AP_FileName; s_BackPath += s_AP_FileName; System.IO.File.Copy(s_UploadPath, s_BackPath); System.IO.File.Delete(s_UploadPath); #endregion #region 將結果顯示在 UploadStatusPanel 中 //上傳結果 this.UploadStatusPanel1.UploadSum = ((int)arl_FileToTmp_Return[2]); this.UploadStatusPanel1.UploadOK = ((int)arl_FileToTmp_Return[3]); this.UploadStatusPanel1.UploadNG = ((int)arl_FileToTmp_Return[4]); //匯入結果 this.UploadStatusPanel1.ImportSum = 0; this.UploadStatusPanel1.ImportOK = 0; this.UploadStatusPanel1.ImportNG = 0; iProcessingCounts = ((int)arl_FileToTmp_Return[2]); #endregion if ((int)arl_FileToTmp_Return[4] == 0) //異常筆數 = 0 { if ((int)arl_FileToTmp_Return[2] == 0) //處理總筆數 = 0 { ErrorMsgLabel.Text = "無資料匯入 !!"; but_Temp_To_DB.Enabled = false; } else { ErrorMsgLabel.Text = "檔案已匯入成功,請確認無誤後,按「執行匯入」鈕,將資料匯入到資料庫"; but_Temp_To_DB.Enabled = true; } but_Unusual_Report.Enabled = false; //檢視異常報表鈕 ButtonQuery.Enabled = false; //查詢鈕 } else { if ((int)arl_FileToTmp_Return[2] == 0) //處理總筆數 = 0 { ErrorMsgLabel.Text = "無資料匯入 !!"; but_Unusual_Report.Enabled = false; //檢視異常報表鈕 ButtonQuery.Enabled = false; //查詢鈕 } else { but_Unusual_Report.Enabled = true; //檢視異常報表鈕 ButtonQuery.Enabled = true; //查詢鈕 } but_Temp_To_DB.Enabled = false; //執行匯入鈕 } if ((int)arl_FileToTmp_Return[4] == 0) bResult = true; else bResult = false; } return bResult; #endregion } } catch (Exception ex) { ErrorMsgLabel.Text = ex.Message; return bResult = false; } return bResult; }
private void QueryData(string strQueryType) //strQueryType = 2 -> 檢視異常報表,strDATATYPE = 1 -> 查詢 { #region string s_LoginUser = string.Empty; DataTable dt_Return = new DataTable(); ArrayList ParameterList = new ArrayList(); s_LoginUser = Session["UID"].ToString();//登入人員 ParameterList.Clear(); if (strQueryType == "1") { ParameterList.Add(txt_User.Text.Trim()); ParameterList.Add(txt_Date.Text.Trim()); ParameterList.Add("2"); } else { ParameterList.Add(s_LoginUser); ParameterList.Add(DateTime.Now.ToString("yyyy/MM/dd")); ParameterList.Add("2"); } Int32 iTopRowNum; Int32.TryParse(TextBoxRowCountLimit.Text.Trim(), out iTopRowNum); ParameterList.Add(iTopRowNum); BCO.MaintainMarketSurveyRecords BCO = new BCO.MaintainMarketSurveyRecords(ConnectionDB); dt_Return = BCO.QueryTmp(ParameterList); if (dt_Return.Rows.Count > 0) { string SessionIDName = string.Format("{0}_{1}", PAGE_DT_01, PageTimeStamp.Value); Session["SessionID"] = SessionIDName; Session[SessionIDName] = dt_Return; gv_ErrorData.DataSource = dt_Return.DefaultView; gv_ErrorData.PageSize = (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) <= 0) ? 10 : int.Parse(TextBoxPagesize.Text); gv_ErrorData.PageIndex = 0; gv_ErrorData.DataBind(); } else { gv_ErrorData.DataBind(); ErrorMsgLabel.Text = "查無資料"; } #endregion }