protected void Page_Load(object sender, EventArgs e)
{
try
{
#region 清空Message
this.ErrorMsgLabel.Text = string.Empty;
this.RightMsgLabel.Text = string.Empty;
#endregion
if (!IsPostBack)
{
#region Attributes
this.but_Close.Attributes["onclick"] += "window.close();";
#endregion
#region 取得異常資料
#region 傳入參數
ParameterList.Clear();
ParameterList.Add(Session["UID"].ToString());
#endregion
#region 連結資料庫
BCO.ProcessImportPOS bco = new BCO.ProcessImportPOS(ConntionDB);
DataTable dt_Return = bco.QueryErrorByFind(ParameterList);
#endregion
#region 檢查回傳資料
if (dt_Return.Rows.Count == 0)
{
this.ErrorMsgLabel.Text = "查無資料";
return;
}
else
{
this.gv_Result.DataSource = dt_Return;
this.gv_Result.DataBind();
}
#endregion
#endregion
}
}
catch (Exception ex)
{
WaringLogProcess(ex.Message);
this.ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 取得期別
/// </summary>
/// <param name="strITEM"></param>
/// <param name="strITEM_MAP"></param>
/// <param name="strBARCODE"></param>
/// <param name="strPERIOD_BARCODE"></param>
/// <returns></returns>
private void Get_CA_Period(string strITEM, string strITEM_MAP, string strBARCODE, string strPERIOD_BARCODE,out string strNewItem,out string strNewPeriod)
{
ArrayList arrPara = new ArrayList();
ProcessImportPOS bco = new ProcessImportPOS(ConnnectionString);
arrPara.Clear();
arrPara.Add(DBPara(strITEM, ParaType.Varchar2));
arrPara.Add(DBPara(strITEM_MAP, ParaType.Varchar2));
arrPara.Add(DBPara(strBARCODE, ParaType.Varchar2));
arrPara.Add(DBPara(strPERIOD_BARCODE, ParaType.Varchar2));
bco.Get_CA_Period(arrPara, out strNewItem, out strNewPeriod);
}
/// <summary>
/// 除CB外的匯入
/// </summary>
/// <param name="strGroupNo"></param>
/// <param name="strFileName"></param>
private void LoadFilePOS_OTHER(string strGroupNo, string strFileName)
{
string[] bResult;
ProcessImportPOS bco = new ProcessImportPOS(ConnnectionString);
try
{
#region 轉入暫存檔
DbTransaction transaction = bco.GetTransaction();
try
{
ParameterList.Clear();
ParameterList.Add(LoginID);
bco.ClearTempTablesX(ParameterList);
DataTable dtExcel = Load_CSV(strFileName, false);
string strID = string.Empty;
string strSALE_DATE = string.Empty;
string strSTORE = string.Empty;
string strSTORE_NAME = string.Empty;
string strAREA_NAME = string.Empty;
string strITEM = string.Empty;
string strITEM_MAP = string.Empty;
string strBARCODE = string.Empty;
string strPERIOD_BARCODE = string.Empty; //二段碼
string strPERIOD = string.Empty;
string strITEM_NAME = string.Empty;
string strPMA = string.Empty;
string strAUTHOR = string.Empty;
string strCREATEDATE = string.Empty;
string strVENDOR = string.Empty;
string strPUBLISHER = string.Empty;
string strSALE_QTY = string.Empty;
string strCREATEUID = string.Empty;
string strSTATUS = string.Empty;
string strDATASOURCE = string.Empty;
string strDATATYPE = string.Empty;
string strMEMO = string.Empty;
bool bCheck;
string strTemp = string.Empty;
string strErrMsg = string.Empty;
string strYYYYMMDD = string.Empty;
string strFolderName = string.Empty;
int iPosS = 0;
int iPosE = 0;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
if (dtExcel.Rows[i][0].ToString().Trim() != "")
{
strCREATEUID = LoginID;
strSTATUS = "1"; //先預設為正常資料
strYYYYMMDD = GetBasename(strFileName).Substring(5, 8);
iPosE = strFileName.LastIndexOf("\\");
iPosS = strFileName.Substring(0, iPosE - 1).LastIndexOf("\\");
strFolderName = strFileName.Substring(iPosS + 1, iPosE - iPosS - 1);
//為了與WEB匯入使用同樣的DATASOURCE取日期和VENDOR方式,所以手動組了一段相同規則的檔名
strDATASOURCE = "XXXXX" + strYYYYMMDD + "XXXXXX_" + strFolderName + ". 實際檔名:" + GetBasename(strFileName);
strDATATYPE = "1";
strMEMO = string.Empty;
int iID = i + 1;
strID = iID.ToString();
//銷售日期
strSALE_DATE = "";
strTemp = dtExcel.Rows[i][0].ToString().Trim();
if (strTemp == "")
{
strSTATUS = "2";
strMEMO += "銷售日期不得為空白;";
}
else
{
strSALE_DATE = CDateToADate(strTemp, out bCheck); //檢查民國日期是否正確並回傳西元日期
//strSALE_DATE = CheckADate(strTemp, out bCheck); //檢查西元日期格式是否正確
if (bCheck == false)
{
strSTATUS = "2";
strMEMO += "銷售日期資料錯誤[" + strTemp + "];";
}
}
//分店編號
strSTORE = "";
strTemp = dtExcel.Rows[i][1].ToString().Trim();
strErrMsg = CheckLength(strTemp, 10, "分店編號", true);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strSTORE = strTemp;
}
//分店簡稱
strSTORE_NAME = "";
strTemp = dtExcel.Rows[i][2].ToString().Trim();
strErrMsg = CheckLength(strTemp, 50, "分店簡稱", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strSTORE_NAME = strTemp;
}
//區部名稱
strAREA_NAME = "";
strTemp = dtExcel.Rows[i][3].ToString().Trim();
strErrMsg = CheckLength(strTemp, 6, "區部名稱", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strAREA_NAME = strTemp;
}
//廠商貨號
strITEM = "";
strTemp = dtExcel.Rows[i][4].ToString().Trim();
strErrMsg = CheckLength(strTemp, 10, "廠商貨號", true);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strITEM = strTemp;
}
//金石堂碼
strITEM_MAP = "";
strTemp = dtExcel.Rows[i][5].ToString().Trim();
strErrMsg = CheckLength(strTemp, 13, "金石堂碼", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strITEM_MAP = strTemp;
}
//原印條碼
strBARCODE = "";
strTemp = dtExcel.Rows[i][6].ToString().Trim();
strErrMsg = CheckLength(strTemp, 13, "原印條碼", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strBARCODE = strTemp;
}
//二段碼
strPERIOD_BARCODE = "";
strTemp = dtExcel.Rows[i][7].ToString().Trim();
strErrMsg = CheckLength(strTemp, 13, "二段碼", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strPERIOD_BARCODE = strTemp;
}
//商品名稱
strITEM_NAME = "";
strTemp = dtExcel.Rows[i][8].ToString().Trim();
strErrMsg = CheckLength(strTemp, 42, "商品名稱", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strITEM_NAME = strTemp;
}
//大類名稱
strPMA = "";
strTemp = dtExcel.Rows[i][9].ToString().Trim();
strErrMsg = CheckLength(strTemp, 10, "大類名稱", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strPMA = strTemp;
}
//作者
strAUTHOR = "";
strTemp = dtExcel.Rows[i][10].ToString().Trim();
strErrMsg = CheckLength(strTemp, 12, "作者", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strAUTHOR = strTemp;
}
//建檔日
strTemp = dtExcel.Rows[i][11].ToString().Trim();
strCREATEDATE = CDateToADate(strTemp, out bCheck); //檢查民國日期是否正確並回傳西元日期
//strCREATEDATE = CheckADate(strTemp, out bCheck); //檢查西元日期格式是否正確
if (bCheck == false)
{
strCREATEDATE = "";
strSTATUS = "2";
strMEMO += "建檔日資料錯誤[" + strTemp + "];";
}
//供應商編號
strVENDOR = "";
strTemp = dtExcel.Rows[i][12].ToString().Trim();
strErrMsg = CheckLength(strTemp, 6, "供應商編號", true);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strVENDOR = strTemp;
}
//出版社編號
strPUBLISHER = "";
strTemp = dtExcel.Rows[i][13].ToString().Trim();
strErrMsg = CheckLength(strTemp, 5, "出版社", false);
if (strErrMsg != "")
{
strSTATUS = "2";
strMEMO += strErrMsg;
}
else
{
strPUBLISHER = strTemp;
}
//銷售量
strTemp = dtExcel.Rows[i][14].ToString().Trim();
strSALE_QTY = CheckNumber(strTemp, 5, out strErrMsg);
if (strErrMsg != "")
{
strSALE_QTY = string.Empty;
strSTATUS = "2";
strMEMO += "銷售量資料" + strErrMsg + "[" + strTemp + "];";
}
//期別碼:需使用到 品號,金石堂碼,條碼,二段碼 來取出期別
Get_CA_Period(strITEM, strITEM_MAP, strBARCODE, strPERIOD_BARCODE, out strITEM, out strPERIOD);
//讓PKG_VDS_CAM07C.CheckTempX_CA去檢查,這邊只要傳X值過去就可以了.
//if (strPERIOD == "X")
//{
// strSTATUS = "2";
// strMEMO += "資料庫查無此品號";
//}
ParameterList.Clear();
ParameterList.Add(DBPara(strID, ParaType.Number)); //N_ID IN VARCHAR2,
ParameterList.Add(DBPara(strSALE_DATE, ParaType.Date)); //D_SALE_DATE IN DATE,
ParameterList.Add(DBPara(strSTORE, ParaType.Varchar2)); //V_STORE IN VARCHAR2,
ParameterList.Add(DBPara(strSTORE_NAME, ParaType.Varchar2));//V_STORE_NAME IN VARCHAR2,
ParameterList.Add(DBPara(strAREA_NAME, ParaType.Varchar2)); //V_AREA_NAME IN VARCHAR2,
ParameterList.Add(DBPara(strITEM, ParaType.Varchar2)); //V_ITEM IN VARCHAR2,
ParameterList.Add(DBPara(strITEM_MAP, ParaType.Varchar2)); //V_ITEM_MAP IN VARCHAR2,
ParameterList.Add(DBPara(strBARCODE, ParaType.Varchar2)); //V_BARCODE IN VARCHAR2,
ParameterList.Add(DBPara(strPERIOD, ParaType.Varchar2)); //V_PERIOD IN VARCHAR2,
ParameterList.Add(DBPara(strITEM_NAME, ParaType.Varchar2)); //V_ITEM_NAME IN VARCHAR2,
ParameterList.Add(DBPara(strPMA, ParaType.Varchar2)); //V_PMA IN VARCHAR2,
ParameterList.Add(DBPara(strAUTHOR, ParaType.Varchar2)); //V_AUTHOR IN VARCHAR2,
ParameterList.Add(DBPara(strCREATEDATE, ParaType.Date)); //D_CREATEDATE IN DATE,
ParameterList.Add(DBPara(strVENDOR, ParaType.Varchar2)); //V_VENDOR IN VARCHAR2,
ParameterList.Add(DBPara(strPUBLISHER, ParaType.Varchar2)); //V_PUBLISHER IN VARCHAR2,
ParameterList.Add(DBPara(strSALE_QTY, ParaType.Number)); //N_SALE_QTY IN NUMBER,
ParameterList.Add(DBPara(strCREATEUID, ParaType.Varchar2)); //V_CREATEUID IN VARCHAR2,
ParameterList.Add(DBPara(strSTATUS, ParaType.Number)); //N_STATUS IN NUMBER,
ParameterList.Add(DBPara(strDATASOURCE, ParaType.Varchar2));//V_DATASOURCE IN VARCHAR2,
ParameterList.Add(DBPara(strDATATYPE, ParaType.Number)); //N_DATATYPE IN NUMBER,
ParameterList.Add(DBPara(strMEMO, ParaType.Varchar2)); //V_MEMO IN VARCHAR2
bco.LoadDataToTempTableX(transaction, ParameterList);
}
}
transaction.Commit();
}
catch (Exception ex)
{
if (transaction != null)
transaction.Rollback();
throw ex;
}
#endregion
if (strGroupNo == "CA")
{
ParameterList.Clear();
ParameterList.Add(DBPara(LoginID, ParaType.Varchar2));
bco.CheckTempX_CA(null, ParameterList); //CA的檢查
}
else
{
ParameterList.Clear();
ParameterList.Add(DBPara(LoginID, ParaType.Varchar2));
ParameterList.Add(DBPara(strGroupNo, ParaType.Varchar2));
bco.CheckTempX_OTHER(null, ParameterList); //非CA的檢查
}
DataTable dtResult = bco.QueryCheckTempX(ParameterList);
int iUploadOK = 0;
int iUploadNG = 0;
DataRow[] FindRows = dtResult.Select("STATUS=1");
if (FindRows.Length > 0)
iUploadOK = int.Parse(FindRows[0]["cnt"].ToString());
FindRows = dtResult.Select("STATUS=2");
if (FindRows.Length > 0)
{
iUploadNG = int.Parse(FindRows[0]["cnt"].ToString());
throw new System.Exception("資料檢核有誤");
}
if ((iUploadOK > 0) && (iUploadNG == 0))
{
#region 轉入系統
ParameterList.Clear();
ParameterList.Add(DBPara("OTHER", ParaType.Varchar2));
ParameterList.Add(DBPara(LoginID, ParaType.Varchar2));
ParameterList.Add(DBPara("0", ParaType.Varchar2));
ParameterList.Add(DBPara(strGroupNo, ParaType.Varchar2));
bResult = bco.LoadPOSX(ParameterList);
iUploadOK = int.Parse(bResult[0]);
iUploadNG = int.Parse(bResult[1]);
if (bResult[2].ToString() != "")
{
throw new System.Exception(bResult[2].ToString());
}
else
{
//載入及統計處理成功
}
#endregion
}
if (iUploadNG > 0)
{
throw new System.Exception("匯入錯誤筆數大於1");
}
}
catch (Exception ex)
{
throw new System.Exception(ex.Message.Split(Environment.NewLine.Replace(Environment.NewLine, "~").ToCharArray())[0]);
}
}
/// <summary>
/// CB匯入
/// </summary>
/// <param name="strFileName"></param>
private void LoadFilePOS_CB(string strFileName)
{
string[] bResult;
string user = LoginID;
ProcessImportPOS bco = new ProcessImportPOS(ConnnectionString);
DbTransaction transaction = bco.GetTransaction();
try
{
#region 轉入暫存檔
try
{
int intID = 0;
bco.ClearTempTables();
using (StreamReader sr = new StreamReader(strFileName, System.Text.Encoding.GetEncoding(950)))
{
string s = null;
intID = 0;
while ((s = sr.ReadLine()) != null)
{
intID = intID + 1;
bco.LoadDataToTempTable(transaction, s, intID);
}
sr.Close();
transaction.Commit();
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
#region 資料檢查
ParameterList.Clear();
ParameterList.Add(DBPara(LoginID, ParaType.Varchar2));
bco.LOADTEMPFILE_POS2(null, ParameterList); //把VDS_CAM_CHAN_LOG 複製到 VDS_CAM_CHAN_LOG_X
bco.CheckTempX_CB(null, ParameterList); //CB資料檢查
DataTable dtResult = bco.QueryCheckTempX(ParameterList);
int iUploadOK = 0;
int iUploadNG = 0;
DataRow[] FindRows = dtResult.Select("STATUS=1");
if (FindRows.Length > 0)
iUploadOK = int.Parse(FindRows[0]["cnt"].ToString());
FindRows = dtResult.Select("STATUS=2");
if (FindRows.Length > 0)
{
iUploadNG = int.Parse(FindRows[0]["cnt"].ToString());
throw new System.Exception("資料檢核有誤");
}
#endregion
if ((iUploadOK > 0) && (iUploadNG == 0))
{
#region 轉入系統
CAMCommon bco1 = new CAMCommon();
ParameterList.Clear();
ParameterList.Add(bco1.GetValueSetParameter("CB", "string", false));
ParameterList.Add(bco1.GetValueSetParameter(LoginID, "string", false));
ParameterList.Add(bco1.GetValueSetParameter("0", "string", false));
bResult = bco.LoadPOS(ParameterList);
if (bResult[2].ToString() != "")
{
if (bResult[2].ToString().IndexOf("日期編號己存在,請重新確認") > 0)
{
ParameterList.Clear();
ParameterList.Add(bco1.GetValueSetParameter("CB", "string", false));
ParameterList.Add(bco1.GetValueSetParameter(LoginID, "string", false));
ParameterList.Add(bco1.GetValueSetParameter("1", "string", false));
bResult = bco.LoadPOS(ParameterList);
if (bResult[2].ToString() != "")
{
throw new System.Exception(bResult[2].ToString());
}
else
{
//載入及統計處理成功
}
}
else
{
throw new System.Exception(bResult[2].ToString());
}
}
else
{
//載入及統計處理成功
}
#endregion
}
if (iUploadNG > 0)
{
throw new System.Exception("匯入失敗筆數大於1");
}
}
catch (Exception ex)
{
throw new System.Exception(ex.Message.Split(Environment.NewLine.Replace(Environment.NewLine, "~").ToCharArray())[0]); //直接取第一個
}
}
/// <summary>
/// 寫LOG檔
/// </summary>
private void WriteLogFile(string strLogMsg, bool bOK)
{
ProcessImportPOS bco = new ProcessImportPOS(ConnnectionString);
bco.LogAdd(LogName, bOK, LogID.ToString("000") + "." + SubStr(strLogMsg, 0, 240));
LogID++;
}