public DataTable QueryByFind(ArrayList ParameterList)
 {
     try
     {
         DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
         DataTable dt;
         dt = dbo.doQueryByFind(ParameterList);
         return dt;
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
        public bool DeleteChainItemInfo(ArrayList ParameterList, DbTransaction RootDBT)
        {

            bool IsRootTranscation = false;
            int iResult = 0;
            bool bResult = false;
            try
            {

                MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(ref USEDB);

                //判斷是否有傳入Root Transcation 
                IsRootTranscation = (RootDBT == null) ? true : false;

                #region 啟動交易或指定RootTranscation

                if (IsRootTranscation)
                {
                    //獨立呼叫啟動Transcation
                    Conn = USEDB.CreateConnection();
                    Conn.Open();
                    DBT = Conn.BeginTransaction();
                }
                else
                {
                    DBT = RootDBT;
                }
                #endregion
                //0.OLD_UPDATEDATE
                //1.OLD_UPDATEUID
                //2.UPDATEDATE
                //3.UPDATEUID            
                //4.CHAN_NO
                //5.ITEM
                //6.PERIOD      
                iResult = DBO.doDeleteChainItemInfo(DBT, ParameterList);

                #region 交易成功

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation成立
                    DBT.Commit();
                    bResult = true;
                }

                #endregion


            }
            catch (Exception ex)
            {
                #region 交易失敗

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation失敗
                    DBT.Rollback();
                }

                #endregion

                throw ex;
            }
            finally
            {
                #region 判斷是否關閉交易連線

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }

                #endregion
            }
            return bResult;
        }
        public bool UpdateChainItemInfo(ArrayList ParameterList, DbTransaction RootDBT)
        {
            bool IsRootTranscation = false;
            int iResult = 0;
            bool bResult = false;
            string strID = string.Empty;
            try
            {

                MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(ref USEDB);

                //判斷是否有傳入Root Transcation 
                IsRootTranscation = (RootDBT == null) ? true : false;

                #region 啟動交易或指定RootTranscation

                if (IsRootTranscation)
                {
                    //獨立呼叫啟動Transcation
                    Conn = USEDB.CreateConnection();
                    Conn.Open();
                    DBT = Conn.BeginTransaction();
                }
                else
                {
                    DBT = RootDBT;
                }
                #endregion

                //0.NAME
                //1.OLD_UPDATEDATE
                //2.OLD_UPDATEUID
                //3.UPDATEDATE
                //4.UPDATEUID
                //5.ENABLE
                //6.CHAN_NO
                //7.ITEM
                //8.PERIOD
                //9.CHAN_CODE
                //10.EAN_CODE
                //11.BARCODE
                //12.PERIOD_BARCODE
                //13.DATASOURCE
                //14.MEMO
                //15.PRICES
                //16.COVER_PRICE
                //17.COST_PRICE
                //18.CHECK_FLAG
                //19.VDS_PRICE   

                iResult = DBO.doUpdateChainItemInfo(DBT, ParameterList);

                //更新 [期別通路檔].對應通路之通路對照號
                //6.CHAN_NO
                //7.ITEM
                //8.PERIOD
                //9.CHAN_CODE
                ArrayList Para = new ArrayList();
                Para.Clear();
                Para.Add(ParameterList[6].ToString());
                Para.Add(ParameterList[7].ToString());
                Para.Add(ParameterList[8].ToString());
                Para.Add(ParameterList[9].ToString());
                iResult = DBO.doUpdateITEM_MAP(DBT, Para);


                #region 交易成功

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation成立
                    DBT.Commit();
                    bResult = true;
                }

                #endregion


            }
            catch (Exception ex)
            {
                #region 交易失敗

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation失敗
                    DBT.Rollback();
                }

                #endregion

                throw ex;
            }
            finally
            {
                #region 判斷是否關閉交易連線

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }

                #endregion
            }
            return bResult;
        }
예제 #4
0
        public ArrayList FileToTmp1(DataSet ds_Excel, string strLoginUser, string strStoreChain, string strOrg_FileName)
        {

            ArrayList arl_Return = new ArrayList();
            int i_FileToTemp_Total_count = 0;

            string strErrMsg = "";
            Int32 iErrCount = 0;
            ArrayList ParameterList = new ArrayList();
            ArrayList pList = new ArrayList();

            try
            {

                //獨立呼叫啟動Transcation
                Conn = USEDB.CreateConnection();
                Conn.Open();
                DBT = Conn.BeginTransaction();

                ParameterList.Clear();
                DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);


                // 先清除前一次,同一使用者執行的匯入暫存資訊與錯誤資訊,並將 Excel 資料匯入 TEMP TABLE
                ParameterList.Clear();
                ParameterList.Add(strLoginUser);
                dbo.doDeleteTmp1(ParameterList);

                //0.V_CREATEDATE 
                //1.V_CREATEUID      
                //2.V_ID             
                //3.V_UPDATEDATE     
                //4.V_UPDATEUID      
                //5.V_CHAIN_CODE     
                //6.V_ITEM_CODE      
                //7.V_TEP_CODE       
                //8.V_ITEM_NAME      
                //9.V_MEMO           
                //10.V_YEAR           
                //11.V_PERIOD_CODE    
                //12.V_COVER_DATE     
                //13.V_PRICES         
                //14.V_BARCODE        
                //15.V_EAN_CODE       
                //16.V_PUBLISH_TYPE   
                //17.V_PRE_ACCEPT_DATE
                //18.V_ERROR_MEMO     
                //19.V_DATASOURCE     
                //20.V_STATUS         
                //21.V_DATATYPE        
                string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                int iRow = 0;
                foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
                {
                    iRow++;
                    if (iRow > 1)
                    {
                        if (
                            (dRow[1].ToString().Trim() != "") ||
                            (dRow[2].ToString().Trim() != "") ||
                            (dRow[3].ToString().Trim() != "") ||
                            (dRow[4].ToString().Trim() != "") ||
                            (dRow[5].ToString().Trim() != "") ||
                            (dRow[6].ToString().Trim() != "") ||
                            (dRow[8].ToString().Trim() != "") ||
                            (dRow[9].ToString().Trim() != "") ||
                            (dRow[10].ToString().Trim() != "") ||
                            (dRow[11].ToString().Trim() != "") ||
                            (dRow[12].ToString().Trim() != "") ||
                            (dRow[14].ToString().Trim() != "")
                           )//不為空值才執行
                        {
                            iErrCount = 0;
                            strErrMsg = "";

                            ParameterList.Clear();
                            ParameterList.Add(strNowTime);   //0.V_CREATEDATE 
                            ParameterList.Add(strLoginUser); //1.V_CREATEUID      
                            ParameterList.Add(iRow);         //2.V_ID             
                            ParameterList.Add(strNowTime);   //3.V_UPDATEDATE     
                            ParameterList.Add(strLoginUser); //4.V_UPDATEUID    
                            ParameterList.Add(strStoreChain);//5.V_CHAIN_CODE                                

                            //6.V_ITEM_CODE (10)

                            ParameterList.Add(CheckLength(dRow[1].ToString().Trim(), 10, "高見品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //7.V_TEP_CODE (10)
                            ParameterList.Add(CheckLength(dRow[2].ToString().Trim(), 10, "台英品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //8.V_ITEM_NAME (30)
                            //ParameterList.Add(CheckLength(dRow[3].ToString(), 30, "品名", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
                            ParameterList.Add(SubStr(dRow[3].ToString().Trim(), 0, 30));//自動截斷字串

                            //9.V_MEMO (500)
                            ParameterList.Add(CheckLength(dRow[4].ToString().Trim(), 500, "備註", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //10.V_YEAR (4)                            
                            ParameterList.Add(CheckLength(dRow[5].ToString().Trim(), 4, "高見期別(年)", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //11.V_PERIOD_CODE (6)
                            ParameterList.Add(CheckLength(dRow[5].ToString().Trim() + dRow[6].ToString().Trim(), 6, "高見期別(年月)", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //12.V_COVER_DATE (10)
                            ParameterList.Add(CheckLength(dRow[8].ToString().Trim(), 20, "封面日期", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //13.V_PRICES (10)        
                            ParameterList.Add(CheckLength(dRow[9].ToString().Trim(), 10, "價格", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //14.V_BARCODE (20)        
                            ParameterList.Add(CheckLength(dRow[10].ToString().Trim(), 20, "原印碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //15.V_EAN_CODE (20)     
                            ParameterList.Add(CheckLength(dRow[11].ToString().Trim(), 30, "EAN條碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //16.V_PUBLISH_TYPE (10)
                            ParameterList.Add(CheckLength(dRow[12].ToString().Trim(), 10, "刊別", strErrMsg, iErrCount, out strErrMsg, out iErrCount));

                            //17.V_PRE_ACCEPT_DATE (10)                            
                            if (dRow[14].ToString().Trim().Length < 10)
                                ParameterList.Add(dRow[14].ToString().Trim());
                            else
                                ParameterList.Add(dRow[14].ToString().Trim().Substring(0, 10));//17.V_PRE_ACCEPT_DATE

                            //高見品號不得為空值
                            if (dRow[1].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "高見品號不得為空值;";
                            }

                            //高見期別不得為空值
                            if ((dRow[5].ToString().Trim() + dRow[6].ToString().Trim()) == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "高見期別不得為空值;";
                            }

                            //價格不得為空值
                            if (dRow[9].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "價格不得為空值;";
                            }
                            else
                            {
                                double dblPrice = 0;
                                if (double.TryParse(dRow[9].ToString().Trim(), out dblPrice) == false)
                                {
                                    iErrCount++;
                                    strErrMsg = strErrMsg + "價格非數值資料;";
                                }
                            }

                            //條碼不得為空值
                            if (dRow[11].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "EAN條碼不得為空值;";
                            }

                            ParameterList.Add(strErrMsg);//18.V_ERROR_MEMO     
                            ParameterList.Add(strOrg_FileName);//19.V_DATASOURCE     

                            //20.V_STATUS  1:正常資料 2:錯誤資料       
                            if (iErrCount != 0)
                            {
                                ParameterList.Add("2");
                            }
                            else
                            {
                                ParameterList.Add("1");
                            }
                            ParameterList.Add("1");//21.V_DATATYPE 1:File To Temp 2:Temp To DB

                            dbo.doFileToTmp1(ParameterList, DBT);

                            i_FileToTemp_Total_count += 1;
                        }
                    }
                }

                DBT.Commit();

                arl_Return.Add("TRUE");
                arl_Return.Add(i_FileToTemp_Total_count);

            }
            catch (Exception ex)
            {
                DBT.Rollback();
                arl_Return.Add("FALSE");
                arl_Return.Add(ex.Message);
                throw ex;
            }

            return arl_Return;
        }
        public bool CreateChainItemInfo(ArrayList ParameterList, out string strID, DbTransaction RootDBT)
        {
            bool IsRootTranscation = false;
            int iResult = 0;
            bool bResult = false;
            try
            {

                MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(ref USEDB);

                //判斷是否有傳入Root Transcation 
                IsRootTranscation = (RootDBT == null) ? true : false;

                #region 啟動交易或指定RootTranscation

                if (IsRootTranscation)
                {
                    //獨立呼叫啟動Transcation
                    Conn = USEDB.CreateConnection();
                    Conn.Open();
                    DBT = Conn.BeginTransaction();
                }
                else
                {
                    DBT = RootDBT;
                }
                #endregion


                iResult = DBO.doCreateChainItemInfo(DBT, ParameterList);
                //6.CHAN_NO
                //7.ITEM
                //8.PERIOD
                //9.CHAN_CODE
                ArrayList Para = new ArrayList();
                Para.Clear();
                Para.Add(ParameterList[6].ToString());
                Para.Add(ParameterList[7].ToString());
                Para.Add(ParameterList[8].ToString());
                Para.Add(ParameterList[9].ToString());
                iResult = DBO.doUpdateITEM_MAP(DBT, Para);
                #region 交易成功

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation成立
                    DBT.Commit();
                    bResult = true;
                }

                #endregion

                //6.CHAN_NO
                //7.ITEM
                //8.PERIOD
                Para.Clear();
                Para.Add(ParameterList[6].ToString());
                Para.Add(ParameterList[7].ToString());
                Para.Add(ParameterList[8].ToString());
                DataTable dt = QueryID(Para);

                strID = dt.Rows[0]["ID"].ToString().Trim();

            }
            catch (Exception ex)
            {
                #region 交易失敗

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation失敗
                    DBT.Rollback();
                }

                #endregion

                throw ex;
            }
            finally
            {
                #region 判斷是否關閉交易連線

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }

                #endregion
            }
            return bResult;
        }
예제 #6
0
        private int CheckData(string strITEM, string strBARCODE, string strPERIOD_BARCODE, string strPRICES, string strCOVER_PRICE, string strCOST_PRICE)
        {
            //檢查匯入項目為 1:新增 2:異動 3:既有品項
            int iResult = 0;
            bool bExistBARCODE = false;        //期別主檔1段碼存在否           (只用BARCODE查詢)
            bool bExistPERIOD_BARCODE = false; //期別主檔2段碼存在否(1段碼存在)(只用BARCODE查詢)
            bool bExistPERIOD = false;        //期別主檔2段碼存在 (用ITEM和2段碼查詢)
            bool bExistItem = false;           //商品主檔存在對應商品品號
            DataTable dt = null;
            ArrayList ParameterList = new ArrayList();

            DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
            //用一段碼查詢期別主檔
            ParameterList.Clear();
            ParameterList.Add("");
            ParameterList.Add(strBARCODE);
            ParameterList.Add("");
            ParameterList.Add("");
            dt = dbo.doQueryITM_PERIOD(ParameterList);
            if (dt.Rows.Count > 0)
            {
                bExistBARCODE = true;
                DataRow[] FindRows = dt.Select("PERIOD_BARCODE='" + strPERIOD_BARCODE + "' or PERIOD_BARCODE is null");
                if (FindRows.Length > 0)
                {
                    bExistPERIOD_BARCODE = true;
                }
            }
            //用品號和二段碼查詢期別主檔           
            ParameterList.Clear();
            ParameterList.Add(strITEM);
            ParameterList.Add("");
            ParameterList.Add(strPERIOD_BARCODE);
            ParameterList.Add("");
            dt = dbo.doQueryITM_PERIOD(ParameterList);
            if (dt.Rows.Count > 0)
            {
                bExistPERIOD = true;
            }

            //查詢商品主檔是否存在品號
            ParameterList.Clear();
            ParameterList.Add(strITEM);
            dt = dbo.doQueryITM_ITEM(ParameterList);
            if (dt.Rows.Count > 0)
            {
                bExistItem = true;
            }

            if ((bExistItem == true) && (bExistBARCODE == false) && (bExistPERIOD == true))
            {
                //1. 商品主檔存在對應商品品號,但  2段碼符合,1段碼不符=>註記原因:調價。
                iResult = 2;//異動品項
            }
            else if ((bExistPERIOD_BARCODE == true) && (bExistBARCODE == true) && (bExistItem == true))
            {

                //假如是異動品項 要判斷是否為調價
                ParameterList.Clear();
                ParameterList.Add(strITEM);
                ParameterList.Add(strBARCODE);
                ParameterList.Add(strPERIOD_BARCODE);
                ParameterList.Add("");
                DataTable dtIPM = dbo.doQueryITM_PERIOD(ParameterList);
                if (dtIPM.Rows.Count > 0)
                {
                    string strIPM_Price = dtIPM.Rows[0]["PRICE"].ToString();
                    if (
                        (strIPM_Price != strPRICES) ||
                        (strIPM_Price != strCOVER_PRICE) ||
                        (strIPM_Price != strCOST_PRICE)
                       )
                    {
                        iResult = 2;//異動品項 調價
                    }
                    else
                    {
                        //1. 商品主檔存在對應品號,且商品期別檔之2段碼、1段碼均相符者。 
                        //   並將[通路對照號]回寫到[期別通路檔]對應通路對照號的通路品號。
                        iResult = 3;//即有品項
                    }
                }
                else
                {
                    //1. 商品主檔存在對應品號,且商品期別檔之2段碼、1段碼均相符者。 
                    //   並將[通路對照號]回寫到[期別通路檔]對應通路對照號的通路品號。
                    iResult = 3;//即有品項
                }

            }
            else
            {
                iResult = 4;//應該不會有這個狀況
            }

            return iResult;
        }
예제 #7
0
        public ArrayList TmpToDB(string strLoginUser, string strStoreChain)
        {
            ArrayList arl_Return = new ArrayList();
            int i_Total_count = 0;
            int i_Right_count = 0;
            int i_Wrong_count = 0;

            string strErrMsg = "";

            Int32 iErrCount = 0;
            ArrayList ParameterList = new ArrayList();
            ArrayList pList = new ArrayList();

            try
            {

                //獨立呼叫啟動Transcation
                Conn = USEDB.CreateConnection();
                Conn.Open();
                DBT = Conn.BeginTransaction();

                ParameterList.Clear();
                DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);

                //0.V_NAME           
                //1.V_CREATEDATE     
                //2.V_CREATEUID      
                //3.V_UPDATEDATE     
                //4.V_UPDATEUID      
                //5.V_ENABLE         
                //6.V_CHAN_NO        
                //7.V_ITEM           
                //8.V_PERIOD         
                //9.V_CHAN_CODE      
                //10.V_EAN_CODE       
                //11.V_BARCODE        
                //12.V_PERIOD_BARCODE 
                //13.V_DATASOURCE     
                //14.V_MEMO           
                //15.V_PRICES         
                //16.V_COVER_PRICE    
                //18.17.V_COST_PRICE     
                //19.V_CHECK_FLAG     
                //20.V_VDS_PRICE 

                string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ParameterList.Clear();
                ParameterList.Add(strLoginUser);
                DataTable dt = dbo.doQueryTemp(ParameterList);

                foreach (DataRow dr in dt.Rows)
                {
                    i_Total_count++;
                    string strMemo = "";
                    string strITEM = dr["ITEM"].ToString().Trim();
                    string strBARCODE = dr["BARCODE"].ToString().Trim();
                    string strPERIOD_BARCODE = dr["PERIOD_BARCODE"].ToString().Trim();
                    int iCheckState = 0;
                    string strExistTmp2 = dr["t2_exist"].ToString().Trim();
                    if (strExistTmp2 != "")
                    {
                        iCheckState = CheckData(strITEM, strBARCODE, strPERIOD_BARCODE, dr["PRICES"].ToString(), dr["COVER_PRICE"].ToString(), dr["COST_PRICE"].ToString());
                    }
                    else
                    {
                        iCheckState = 1;//新增 Tmp1有 Tmp2沒有
                    }

                    //新增到 vds_mkt_chainiteminfo 通路對照檔
                    if (dr["PERIOD"].ToString().Trim() != "XXXXXX")
                    {
                        ParameterList.Clear();
                        ParameterList.Add(dr["ITEM"].ToString());          //0.V_NAME
                        ParameterList.Add(strNowTime);                     //1.V_CREATEDATE 
                        ParameterList.Add(strLoginUser);                   //2.V_CREATEUID                                  
                        ParameterList.Add(strNowTime);                     //3.V_UPDATEDATE     
                        ParameterList.Add(strLoginUser);                   //4.V_UPDATEUID    
                        ParameterList.Add("1");                            //5.V_ENABLE         
                        ParameterList.Add(dr["CHAN_NO"].ToString());       //6.V_CHAN_NO        
                        ParameterList.Add(dr["ITEM"].ToString());          //7.V_ITEM           
                        ParameterList.Add(dr["PERIOD"].ToString());        //8.V_PERIOD         
                        ParameterList.Add(dr["CHAN_CODE"].ToString());     //9.V_CHAN_CODE      
                        ParameterList.Add(dr["EAN_CODE"].ToString());      //10.V_EAN_CODE       
                        ParameterList.Add(dr["BARCODE"].ToString());       //11.V_BARCODE        
                        ParameterList.Add(dr["PERIOD_BARCODE"].ToString());//12.V_PERIOD_BARCODE 
                        ParameterList.Add("2");                            //13.V_DATASOURCE     
                        ParameterList.Add(dr["MEMO"].ToString());          //14.V_MEMO           
                        ParameterList.Add(dr["PRICES"].ToString());        //15.V_PRICES         
                        ParameterList.Add(dr["COVER_PRICE"].ToString());   //16.V_COVER_PRICE    
                        ParameterList.Add(dr["COST_PRICE"].ToString());    //18.17.V_COST_PRICE     
                        ParameterList.Add(iCheckState);                    //19.V_CHECK_FLAG 1:新增 2:異動 3:即有品項
                        ParameterList.Add("0");                            //20.V_VDS_PRICE 
                        dbo.doCreateChainItemInfo(DBT, ParameterList);
                    }

                    if (iCheckState == 3)
                    {
                        //假如是既有商品 更新 [期別通路檔].對應通路之通路對照號
                        //0.CHAN_NO
                        //1.ITEM
                        //2.PERIOD
                        //3.CHAN_CODE                        
                        ParameterList.Clear();
                        ParameterList.Add(dr["CHAN_NO"].ToString());
                        ParameterList.Add(dr["ITEM"].ToString());
                        ParameterList.Add(dr["PERIOD"].ToString());
                        ParameterList.Add(dr["CHAN_CODE"].ToString());
                        dbo.doUpdateITEM_MAP(DBT, ParameterList);
                    }

                    //更新 VDS_MKT_CHAINITEMINFO_TMP1 的 DATATYPE 變成 2:TMP TO DB
                    //0.V_ID
                    //1.V_UID
                    //2.V_STATUS
                    //3.V_DATATYPE
                    //4.V_ERROR_MEMO
                    if (iCheckState == 2)
                    {
                        strMemo = "調價";
                    }
                    else if (iCheckState == 4)
                    {
                        strMemo = "其他例外錯誤";
                    }
                    ParameterList.Clear();
                    ParameterList.Add(dr["ID"].ToString());
                    ParameterList.Add(strLoginUser);
                    ParameterList.Add("");
                    ParameterList.Add("2");
                    ParameterList.Add(strMemo);

                    dbo.doUpdateTmp(DBT, ParameterList);
                    i_Right_count++;

                    //i_Wrong_count++;                    
                }

                DBT.Commit();

                arl_Return.Add("TRUE");
                arl_Return.Add(i_Total_count);
                arl_Return.Add(i_Right_count);
                arl_Return.Add(i_Wrong_count);

            }
            catch (Exception ex)
            {
                DBT.Rollback();
                arl_Return.Add("FALSE");
                arl_Return.Add(ex.Message);
                throw ex;
            }

            return arl_Return;
        }
예제 #8
0
 //取得Tmp2成功和錯誤的筆數
 public ArrayList GetTmp2Count(string strLoginUser)
 {
     ArrayList ParameterList = new ArrayList();
     ArrayList arl_Return = new ArrayList();
     int i_FileToTemp_Right_count = 0;
     int i_FileToTemp_Wrong_count = 0;
     DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
     //取得正確和錯誤筆數
     ParameterList.Clear();
     ParameterList.Add(strLoginUser);
     DataTable dtCounts = dbo.QueryTemp2Count(ParameterList);
     foreach (DataRow dr in dtCounts.Rows)
     {
         if (dr["status"].ToString() == "2")
         {
             i_FileToTemp_Wrong_count = int.Parse(dr["counts"].ToString());
         }
         if (dr["status"].ToString() == "1")
         {
             i_FileToTemp_Right_count = int.Parse(dr["counts"].ToString());
         }
     }
     arl_Return.Add(i_FileToTemp_Right_count);
     arl_Return.Add(i_FileToTemp_Wrong_count);
     return arl_Return;
 }
예제 #9
0
        //把Temp1和Temp2做其他例外檢查
        public void CheckOther(string strLoginUser)
        {
            ArrayList ParameterList = new ArrayList();
            DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);

            //1.檢查Tmp1&Tmp2的EAN是否完全對應
            //2.檢查Temp2的誠品編號不能重複
            ParameterList.Clear();
            ParameterList.Add(strLoginUser);
            dbo.doCheckTmp(ParameterList);
        }
예제 #10
0
        public ArrayList FileToTmp2(DataSet ds_Excel, string strLoginUser, string strStoreChain, string strOrg_FileName)
        {
            ArrayList arl_Return = new ArrayList();
            int i_FileToTemp_Total_count = 0;

            string strErrMsg = "";
            Int32 iErrCount = 0;
            ArrayList ParameterList = new ArrayList();
            ArrayList pList = new ArrayList();

            try
            {

                //獨立呼叫啟動Transcation
                Conn = USEDB.CreateConnection();
                Conn.Open();
                DBT = Conn.BeginTransaction();

                ParameterList.Clear();
                DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);


                // 先清除前一次,同一使用者執行的匯入暫存資訊與錯誤資訊,並將 Excel 資料匯入 TEMP TABLE
                ParameterList.Clear();
                ParameterList.Add(strLoginUser);
                dbo.doDeleteTmp2(ParameterList);

                //0.V_ID
                //1.V_CREATEDATE
                //2.V_CREATEUID
                //3.V_UPDATEDATE
                //4.V_UPDATEUID
                //5.V_EAN_CODE
                //6.V_ITEM
                //7.V_CHAN_CODE
                //8.V_ITEM_NAME
                //9.V_PRICES
                //10.V_PRICES_CURRENCY
                //11.V_COVER_PRICE
                //12.V_COVER_PRICE_CURRENCY
                //13.V_COST_PRICE
                //14.V_COST_PRICE_CURRENCY
                //15.V_DISCOUNT
                //16.V_PUBLISH_TYPE
                //17.V_PUBLISH_TYPE_MEMO
                //18.V_LANGUAGE_CODE
                //19.V_LANGUAGE_MEMO
                //20.V_SOURCE
                //21.V_MAIN_FACTORY_CODE
                //22.V_PUBLISH_CODE
                //23.V_BUY_GROUP
                //24.V_MATERIEL_GROUP
                //25.V_MATERIEL_GROUP_MEMO
                //26.V_MADE_DATE
                //27.V_AUTHOR
                //28.V_MATERIAL
                //29.V_SIZES
                //30.V_ERROR_MSG
                //31.V_ERROR_MEMO
                //32.V_DATASOURCE
                //33.V_STATUS,
                //34.V_DATATYPE

                string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                int iRow = 1;
                int iCol1 = 0; //假如第1個Column是空格的 iCol1=1,假如第1個是EAN則iCol1=-1,因為第1個Column沒值時,Excel讀取後第1個Col會忽略掉.


                if (ds_Excel.Tables[0].Columns[0].ColumnName.Trim() == "EAN")
                    iCol1 = -1;
                else
                    iCol1 = 0;


                foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
                {
                    if (dRow[1 + iCol1].ToString().Trim() != "EAN")
                    {
                        if ((dRow[1 + iCol1].ToString().Trim() != "") || (dRow[3 + iCol1].ToString().Trim() != "") || (dRow[4 + iCol1].ToString().Trim() != "")) //不為空值才執行
                        {
                            iErrCount = 0;
                            strErrMsg = "";

                            ParameterList.Clear();
                            ParameterList.Add(iRow);         //0.V_ID             
                            ParameterList.Add(strNowTime);   //1.V_CREATEDATE 
                            ParameterList.Add(strLoginUser); //2.V_CREATEUID                                  
                            ParameterList.Add(strNowTime);   //3.V_UPDATEDATE     
                            ParameterList.Add(strLoginUser); //4.V_UPDATEUID    
                            ParameterList.Add(CheckLength(dRow[1 + iCol1].ToString(), 30, "EAN條碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //5.V_EAN_CODE
                            ParameterList.Add(CheckLength(dRow[3 + iCol1].ToString(), 10, "廠商貨號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //6.V_ITEM
                            ParameterList.Add(CheckLength(dRow[4 + iCol1].ToString(), 20, "通路對照號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //7.V_CHAN_CODE     

                            //ParameterList.Add(CheckLength(dRow[6 + iCol].ToString(), 30, "商品名稱", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //8.V_ITEM_NAME
                            ParameterList.Add(SubStr(dRow[6 + iCol1].ToString(), 0, 30));//8.V_ITEM_NAME 自動截斷字串

                            ParameterList.Add(CheckLength(dRow[7 + iCol1].ToString(), 10, "售價", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //9.V_PRICES
                            ParameterList.Add(CheckLength(dRow[9 + iCol1].ToString(), 10, "售價幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //10.V_PRICES_CURRENCY                            
                            ParameterList.Add(CheckLength(dRow[10 + iCol1].ToString(), 10, "Cover Price", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //11.V_COVER_PRICE
                            ParameterList.Add(CheckLength(dRow[11 + iCol1].ToString(), 10, "Cover Price 幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //12.V_COVER_PRICE_CURRENCY
                            ParameterList.Add(CheckLength(dRow[12 + iCol1].ToString(), 10, "進貨基準價", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //13.V_COST_PRICE
                            ParameterList.Add(CheckLength(dRow[13 + iCol1].ToString(), 10, "進貨基準價幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //14.V_COST_PRICE_CURRENCY
                            ParameterList.Add(CheckLength(dRow[14 + iCol1].ToString(), 10, "折扣", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //15.V_DISCOUNT
                            ParameterList.Add(CheckLength(dRow[15 + iCol1].ToString(), 10, "版別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //16.V_PUBLISH_TYPE
                            ParameterList.Add(CheckLength(dRow[16 + iCol1].ToString(), 50, "版別說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //17.V_PUBLISH_TYPE_MEMO
                            ParameterList.Add(CheckLength(dRow[17 + iCol1].ToString(), 10, "語言別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //18.V_LANGUAGE_CODE
                            ParameterList.Add(CheckLength(dRow[18 + iCol1].ToString(), 10, "語言別說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //19.V_LANGUAGE_MEMO
                            ParameterList.Add(CheckLength(dRow[19 + iCol1].ToString(), 10, "貨源", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //20.V_SOURCE
                            ParameterList.Add(CheckLength(dRow[20 + iCol1].ToString(), 10, "主廠商編號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //21.V_MAIN_FACTORY_CODE
                            ParameterList.Add(CheckLength(dRow[21 + iCol1].ToString(), 10, "出版商編號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //22.V_PUBLISH_CODE
                            ParameterList.Add(CheckLength(dRow[22 + iCol1].ToString(), 10, "採購群組", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //23.V_BUY_GROUP
                            ParameterList.Add(CheckLength(dRow[23 + iCol1].ToString(), 10, "物料群組", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //24.V_MATERIEL_GROUP
                            ParameterList.Add(CheckLength(dRow[24 + iCol1].ToString(), 50, "物料群組說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //25.V_MATERIEL_GROUP_MEMO
                            ParameterList.Add(CheckLength(dRow[25 + iCol1].ToString(), 10, "製造日期", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //26.V_MADE_DATE
                            ParameterList.Add(CheckLength(dRow[26 + iCol1].ToString(), 50, "作者名", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //27.V_AUTHOR
                            ParameterList.Add(CheckLength(dRow[27 + iCol1].ToString(), 10, "材質", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //28.V_MATERIAL
                            ParameterList.Add(CheckLength(dRow[28 + iCol1].ToString(), 10, "SIZE", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //29.V_SIZES                                                    

                            //誠品編號不得空值
                            if (dRow[4 + iCol1].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "誠品編號不得空值;";
                            }

                            int iTemp = 0;
                            //售價不得空值
                            if (dRow[7 + iCol1].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "售價不得空值;";
                            }
                            else if (int.TryParse(dRow[7 + iCol1].ToString().Trim(), out iTemp) == false)
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "售價非數值格式;";
                            }
                            //Cover Price不得空值
                            if (dRow[10 + iCol1].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "Cover Price不得空值;";
                            } if (int.TryParse(dRow[10 + iCol1].ToString().Trim(), out iTemp) == false)
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "Cover Price非數值格式;";
                            }
                            //進貨基準價不得空值
                            if (dRow[12 + iCol1].ToString().Trim() == "")
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "進貨基準價不得空值";
                            } if (int.TryParse(dRow[12 + iCol1].ToString().Trim(), out iTemp) == false)
                            {
                                iErrCount++;
                                strErrMsg = strErrMsg + "進貨基準價非數值格式;";
                            }

                            ParameterList.Add(strErrMsg);       //30.V_ERROR_MSG     
                            ParameterList.Add(strErrMsg);       //31.V_ERROR_MEMO     
                            ParameterList.Add(strOrg_FileName); //32.V_DATASOURCE     

                            //33.V_STATUS  1:正常資料 2:錯誤資料       
                            if (iErrCount != 0)
                            {
                                ParameterList.Add("2");
                            }
                            else
                            {
                                ParameterList.Add("1");
                            }
                            ParameterList.Add("1");//34.V_DATATYPE 1:File To Temp 2:Temp To DB

                            dbo.doFileToTmp2(ParameterList, DBT);

                            i_FileToTemp_Total_count += 1;
                        }
                    }
                    iRow++;
                }

                DBT.Commit();

                arl_Return.Add("TRUE");
                arl_Return.Add(i_FileToTemp_Total_count);
            }
            catch (Exception ex)
            {
                DBT.Rollback();
                arl_Return.Add("FALSE");
                arl_Return.Add(ex.Message);
                throw ex;
            }

            return arl_Return;
        }