//整批刪除
protected void btnBatchDel_Click(object sender, EventArgs e)
{
try
{
if (SLP_SKU1.Text == "" || SLP_ItemPeriod1.Text == "")
{
ErrorMsgLabel.Text = "請輸入品號、期別";
return;
}
ParameterList.Clear();
ParameterList.Add(SLP_SKU1.Text);
ParameterList.Add(SLP_ItemPeriod1.Text);
ParameterList.Add(DateTime.Now);
ParameterList.Add(Session["UID"].ToString());
BCO.MaintainStoreGift bco = new MaintainStoreGift(ConnectionDB);
bco.BatchDel(ParameterList, null);
if (GridView1.Rows.Count > 0)
{
GridView1.DataSource = null;
GridView1.DataBind();
}
ErrorMsgLabel.Text = "整批刪除作業完成";
}
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.ImportStoreGift BCO = new BCO.ImportStoreGift(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_Excel = ReadDataFromExcelXml(arl_FileUpload_Return[1].ToString());
//檢核是否已有相同通路、門市、品號、期別 Added by Tony 2010/1/5
BCO.MaintainStoreGift bco = new BCO.MaintainStoreGift(ConnectionDB);
ArrayList ar = new ArrayList();
foreach (System.Data.DataRow dr in ds_Excel.Tables[0].Rows)
{
ar.Clear();
ar.Add(dr["ITEM"]);
ar.Add(dr["PERIOD"]);
ar.Add(dr["CHAN_NO"]);
ar.Add(dr["STORE"]);
ar.Add(System.DBNull.Value); //虛擬品號
DataTable dt = bco.QueryExistPk(ar);
if(dt.Rows.Count>0)
{
ErrorMsgLabel.Text = "品號「" + dr["ITEM"].ToString() + "」、期別「" + dr["PERIOD"].ToString() + "」、通路「" + dr["CHAN_NO"].ToString() + "」、門市「" + dr["STORE"].ToString() + "」資料已存在";
return bResult = false;
}
}
#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(ds_Excel, d_CreateDate, s_LoginUser, s_AP_FileName,chkDis.Checked);
#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;
}
protected void Btn_Export_Click(object sender, EventArgs e)
{
try
{
#region 匯出條件變數
string strItem = this.SLP_SKU1.Text;
string strPeriod = this.SLP_ItemPeriod1.Text;
string strSLP_StoreChain_S = SLP_StoreChain_S.Text;
string strSLP_StoreChain_E = SLP_StoreChain_E.Text;
string strSLP_CodeFile97_S = SLP_CodeFile97_S.Text;
string strSLP_CodeFile97_E = SLP_CodeFile97_E.Text;
string strSLP_CodeFile91_S = SLP_CodeFile91_S.Text;
string strSLP_CodeFile91_E = SLP_CodeFile91_E.Text;
string strSLP_CodeFile18_S = SLP_CodeFile18_S.Text;
string strSLP_CodeFile18_E = SLP_CodeFile18_E.Text;
string strSLP_CodeFile3_S = SLP_CodeFile3_S.Text;
string strSLP_CodeFile3_E = SLP_CodeFile3_E.Text;
#endregion
#region check是否存在OnPack贈品主檔資料
DataTable dtGift = new DataTable();
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(strItem, "string"));
ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
ParameterList.Add(GetValueSetParameter("-1", "int"));
BCO.MaintainGift bco = new MaintainGift(ConnectionDB);
dtGift = bco.QueryGift(ParameterList);
if (dtGift == null || (dtGift != null && dtGift.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "無贈品主檔資料";
return;
}
#endregion
#region check是否存在通路門市贈品資料
DataTable dtStoreGift = new DataTable();
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(strItem, "string"));
ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_E, "string"));
ParameterList.Add(SLP_SHELVE_CM3.Text); //專屬架
BCO.MaintainStoreGift bcoStoreGift = new MaintainStoreGift(ConnectionDB);
dtStoreGift = bcoStoreGift.ExportStoreGift(ParameterList);
if (dtStoreGift == null || (dtStoreGift != null && dtStoreGift.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "無通路門市贈品資料";
return;
}
#endregion
#region 匯出通路門市贈品資料
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
Worksheet sheet1 = book[1];
//設定Worksheet各column 寬度
sheet.Columns(3).Width = 70;
sheet.Columns(5).Width = 110;
sheet.Columns(6).Width = 45;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 120;
sheet1.Columns(0).Width = 30;
sheet1.Columns(2).Width = 110;
sheet1.Columns(3).Width = 45;
sheet1.Columns(4).Width = 60;
sheet1.Columns(5).Width = 120;
//sheet1.Columns(6).Hidden = true; //品號品名怕被修改到,所以產生兩個隱藏欄位
//sheet1.Columns(7).Hidden = true;
int iRow = 0;
//贈品主檔
sheet1.Name = "贈品主檔";
sheet1.PrintOptions.Orientation = PageOrientation.Landscape;
sheet1.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet1[0, iRow].Value = "序號";
sheet1[1, iRow].Value = "商品品號";
sheet1[2, iRow].Value = "商品名稱";
sheet1[3, iRow].Value = "期別";
sheet1[4, iRow].Value = "贈品名稱";
sheet1[5, iRow].Value = "虛擬品號";
//sheet1[6, iRow].Value = "贈品名稱";
//sheet1[7, iRow].Value = "虛擬品號";
iRow++;
DataRow[] drs = dtGift.Select("1=1", "VIRTUAL_NAME");
foreach (DataRow dr in drs)
{
if (dr["id"].ToString() != "0")
{
sheet1[0, iRow].Value = iRow.ToString();
sheet1[1, iRow].Value = dr["ITEM"].ToString();
sheet1[2, iRow].Value = dr["ITEM_NAME"].ToString();
sheet1[3, iRow].Value = dr["PERIOD"].ToString();
sheet1[4, iRow].Value = dr["VIRTUAL_NAME"].ToString();
sheet1[5, iRow].Value = dr["VIRTUAL_CODE"].ToString();
//sheet1[6, iRow].Value = dr["VIRTUAL_NAME"].ToString();
//sheet1[7, iRow].Value = dr["VIRTUAL_CODE"].ToString();
iRow++;
}
}
//多通路門市贈品設定
iRow = 0;
sheet.Name = "多通路門市贈品設定";
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
sheet[0, iRow].Value = "通路代號";
sheet[1, iRow].Value = "通路名稱";
sheet[2, iRow].Value = "門市代號";
sheet[3, iRow].Value = "門市名稱";
sheet[4, iRow].Value = "商品品號";
sheet[5, iRow].Value = "商品名稱";
sheet[6, iRow].Value = "期別";
sheet[7, iRow].Value = "虛擬品號";
sheet[8, iRow].Value = "贈品名稱";
iRow++;
foreach (DataRow dr in dtStoreGift.Rows)
{
sheet[0, iRow].Value = dr["CHAN_NO"].ToString();
sheet[1, iRow].Value = dr["CHAN_NAME"].ToString();
sheet[2, iRow].Value = dr["STORE"].ToString();
sheet[3, iRow].Value = dr["STORE_NAME"].ToString();
sheet[4, iRow].Value = dr["ITEM"].ToString();
sheet[5, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[6, iRow].Value = dr["PERIOD"].ToString();
Formula f = new Formula().Add("VLOOKUP").StartGroup();
f.Add(new Range(sheet[8, iRow])).Operator(',');
//f.Add(new Range(sheet1[5, 1], sheet1[5, dtGift.Rows.Count])).Operator(',');
//f.Add(new Range(sheet1[4, 1], sheet1[4, dtGift.Rows.Count])).EndGroup();
f.Add(new Range(sheet1[5, 1], sheet1[4, dtGift.Rows.Count])).Operator(',');
f.Add("2").Operator(',');
f.Add("false").EndGroup();
sheet[7, iRow].Value = f;
//sheet[7, x].Value = dr["VIRTUAL_CODE"].ToString();
sheet[8, iRow].Value = dr["VIRTUAL_NAME"].ToString();
iRow++;
}
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(string.Format("MKT14_通路贈品門市設定_{0}_{1}.xls", strItem, strPeriod), System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
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)
{ this.ErrorMsgLabel.Text = ex.ToString(); }
finally { }
}
protected void Btn_Query_Click(object sender, EventArgs e)
{
try
{
DataTable Dt = new DataTable();
#region 處理查詢
BCO.MaintainStoreGift bco = new MaintainStoreGift(ConnectionDB);
Dt = bco.QueryStoreGift(this.getParameterList());
#endregion
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "查無資料";
}
else
{
Session["MKT141_" + PageTimeStamp.Value] = Dt.Copy();
}
#region 將Key值存到Session中
ArrayList arl_Key = new ArrayList();
foreach (DataRow drRow in Dt.Rows)
{ arl_Key.Add(drRow["ID"].ToString()); }
Session["MKT142_SortKey" + this.PageTimeStamp.Value] = arl_Key;
#endregion
#region 設定查詢結果
this.GridView1.DataSource = Dt;
//設定分頁大小不得為0，如為0預設為1
if (TextBoxPagesize.Text == "0")
{
GridView1.PageSize = 1;
}
else
{
GridView1.PageSize = (TextBoxPagesize.Text == "") ? 10 : (int.Parse(TextBoxPagesize.Text) < 0) ? 10 : int.Parse(TextBoxPagesize.Text);
}
this.GridView1.PageIndex = 0;
this.GridView1.DataBind();
Btn_PurchaseCard.Enabled = true;
#endregion
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.ToString(); }
finally { }
}
protected void Btn_DeleteExec_Click(object sender, EventArgs e)
{
ErrorMsgLabel.Text = "";
if (this.hid_PageStatus.Value == "VIEW")
{
ErrorMsgLabel.Text = "";
#region 取得更新前舊值
#region 使用XML轉回DataSet
DataSet DsMaster = new DataSet();
System.IO.StringReader srM = new System.IO.StringReader(MasterOLDData.InnerHtml);
DsMaster.ReadXml(srM);
DataTable dtMasterOLD = DsMaster.Tables[0];
#endregion
#endregion
#region 處理刪除作業
#region 組合刪除條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
returnList.Add(Session["UID"].ToString());
#endregion
try
{
BCO.MaintainStoreGift bco = new BCO.MaintainStoreGift(ConnectionDB);
bco.DeleteStoreGift(returnList, dtMasterOLD.Rows[0], null);
ScriptManager.RegisterStartupScript(Page, this.GetType(), "ClientScript", "alert('刪除完成');location.replace('MKT141.aspx?Code=MKT14');", true);
}
catch (Exception ex)
{
this.Btn_Save.Visible = false;//存檔
ErrorMsgLabel.Text = ex.Message;
}
#endregion
}
}
protected void Btn_Save_Click(object sender, EventArgs e)
{
try
{
#region 資料儲存
BCO.MaintainStoreGift bco = new BCO.MaintainStoreGift(ConnectionDB);
{
switch (this.hid_PageStatus.Value)
{
case "INSERT":
#region 處理新增作業
{
DataTable dtExist = new DataTable();
dtExist = bco.QueryExistPk(this.getParameterList());
if (dtExist != null && dtExist.Rows.Count > 0)
{
ErrorMsgLabel.Text = "新增失敗, 資料已存在";
return;
}
#region 儲存新增資料
// 做資料儲存的動作
int vPID = bco.CreateStoreGift(this.getParameterList(), null);
this.hiddenID.Value = Convert.ToString(vPID);
#endregion
}
#endregion
break;
case "EDIT":
{
#region 使用XML轉回DataSet
DataTable MasterOldDt = null;
if (MasterOLDData.InnerHtml != "" && MasterOLDData.InnerHtml != "<NewDataSet />")
{
DataSet Ds = new DataSet();
System.IO.StringReader sr = new System.IO.StringReader(MasterOLDData.InnerHtml);
Ds.ReadXml(sr);
MasterOldDt = Ds.Tables[0];
}
#endregion
// 做資料儲存的動作
bco.UpdateStoreGift(this.getParameterList(),
MasterOldDt.Rows[0],
null);
break;
}
break;
}
#endregion
#region 存檔後資料重新讀取
databind_Mst();
#endregion
#region 存檔後狀態設定
this.hid_PageStatus.Value = "VIEW";
this.txt_PageStatus.Text = "VIEW";
#endregion
}
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.Message; }
finally
{
if (this.ErrorMsgLabel.Text == "")
{
SetPageStatus();
SetDefaultValue();
}
}
}
/// <summary>
/// 繫結資料 MST
/// </summary>
private void databind_Mst()
{
#region
try
{
ErrorMsgLabel.Text = "";
BCO.MaintainStoreGift bco = new BCO.MaintainStoreGift(ConnectionDB);
ParameterList.Clear();
ParameterList.Add(this.hiddenID.Value);
DataTable Dt = bco.QueryStoreGiftByID(ParameterList);
#region 使用XML放置到前端資料
MasterOLDData.InnerHtml = Dt.DataSet.GetXml();
#endregion
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
#endregion
}//databind_Mst
private void LoadData(int intID, string strItem, string strPeriod)
{
try
{
#region Bind gvGift
MaintainGift bcoGift = new MaintainGift(ConntionDB);
ParameterList.Clear();
ParameterList.Add(strItem);
ParameterList.Add(strPeriod);
ParameterList.Add(999);
DataTable Dt_Gift = bcoGift.QueryGift(ParameterList);
Session["MKT05_POP_Gift" + PageTimeStamp.Value] = Dt_Gift;
if (Dt_Gift != null && Dt_Gift.Rows.Count != 0)
{
gvGift.PageSize = 20;
gvGift.PageIndex = 0;
gvGift.DataSource = Dt_Gift;
gvGift.DataBind();
NoData_1.Visible = false;
}
else
{
NoData_1.Visible = true;
}
#endregion
#region Bind gvGiftChan
MaintainPurchaseCard bco = new MaintainPurchaseCard(ConntionDB);
ParameterList.Clear();
ParameterList.Add(intID);
ParameterList.Add(strItem);
ParameterList.Add(strPeriod);
DataTable Dt_GiftChan = bco.MTK05_POP_GiftChan(ParameterList);
Session["MKT05_POP_GiftChan" + PageTimeStamp.Value] = Dt_GiftChan;
if (Dt_GiftChan != null && Dt_GiftChan.Rows.Count != 0)
{
gvGiftChan.PageSize = 20;
gvGiftChan.PageIndex = 0;
gvGiftChan.DataSource = Dt_GiftChan;
gvGiftChan.DataBind();
NoData_2.Visible = false;
}
else
{
NoData_2.Visible = true;
}
#endregion
#region Bind 通路門市贈品
MaintainStoreGift BCO = new MaintainStoreGift(ConntionDB);
ParameterList.Clear();
ParameterList.Add(strItem);
ParameterList.Add(strPeriod);
ParameterList.Add("99999999");
DataTable Dt_GiftStore = BCO.QueryStoreGift(ParameterList);
Session["MKT05_POP_GiftStore" + PageTimeStamp.Value] = Dt_GiftStore;
if (Dt_GiftStore != null && Dt_GiftStore.Rows.Count != 0)
{
GridView1.PageSize = 20;
GridView1.PageIndex = 0;
GridView1.DataSource = Dt_GiftStore;
GridView1.DataBind();
NoData_3.Visible = false;
}
else
{
NoData_3.Visible = true;
}
#endregion
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
}
protected void Btn_Save_Click(object sender, EventArgs e)
{
try
{
if (VerifyFormData())
{
int intMainID = 0;
int intDetailID = 0;
DataTable dt = PrepareSaveData();
BCO.MaintainStoreGift bco = new BCO.MaintainStoreGift(ConnectionDB);
bco.CreateStoreGifts(dt, null);
ScriptManager.RegisterStartupScript(this, this.GetType(), "GoBackToMKT141", "alert('新增完成');location.replace('MKT141.aspx?Code=MKT14');", true);
}
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.Message; }
}