public DataTable QueryPatternSpecByFind(ArrayList Paramslist)
        {
            #region
            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);

                DataTable dt = ALOM.doQueryByFind(Paramslist);

                return dt;

            }
            catch (Exception ex)
            {
                throw GetNewException(ex);
            }
            #endregion
        }
        public bool CopyToNewStore(Hashtable htParams, DbTransaction RootDBT)
        {
            #region
            bool IsRootTranscation = false;

            bool bResult = false;
            Int32 PID = 0;
            Hashtable htParamsMaster = new Hashtable();
            DataTable dtForCopyDetail = new DataTable();
            DataRow dr;
            dtForCopyDetail.Columns.Add("ID");
            dtForCopyDetail.Columns.Add("CREATEDATE");
            dtForCopyDetail.Columns.Add("CREATEUID");
            dtForCopyDetail.Columns.Add("UPDATEDATE");
            dtForCopyDetail.Columns.Add("UPDATEUID");
            dtForCopyDetail.Columns.Add("START_RANKQTY");
            dtForCopyDetail.Columns.Add("END_RANKQTY");
            dtForCopyDetail.Columns.Add("ADJ_QTY");

            string strCurrentId = "", strLastId = "";

            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);
                VDS_ALO_PATTERNSPEC_DETL_DBO ALOD = new VDS_ALO_PATTERNSPEC_DETL_DBO(ref USEDB);


                #region    取得相關資料

                DataTable dtForCopy = ALOM.doQueryForCopyToNewStore(htParams);
                Hashtable htTemp = new Hashtable();
                string strErrMsg = "";
                if (dtForCopy != null && dtForCopy.Rows.Count > 0)
                {
                    for (Int32 i = dtForCopy.Rows.Count - 1; i >= 0; i--)
                    {
                        htTemp.Clear();
                        htTemp.Add("ChanNo", dtForCopy.Rows[i]["new_chan_no"].ToString().Trim());
                        htTemp.Add("Store", dtForCopy.Rows[i]["new_store"].ToString().Trim());

                        if (dtForCopy.Rows[i]["s_end_date"] != DBNull.Value)
                            htTemp.Add("StartDate", ((DateTime)dtForCopy.Rows[i]["s_start_date"]).ToString("yyyy/MM/dd"));
                        else
                            htTemp.Add("StartDate", null);

                        if (dtForCopy.Rows[i]["s_end_date"] != DBNull.Value)
                            htTemp.Add("EndDate", ((DateTime)dtForCopy.Rows[i]["s_end_date"]).ToString("yyyy/MM/dd"));
                        else
                            htTemp.Add("EndDate", null);

                        if (!CheckPATTERNSPECCanAdd(htTemp, ref strErrMsg))
                        {
                            dtForCopy.Rows.RemoveAt(i);
                        }
                    }
                }
                else { throw new Exception("複製失敗,無資料可複製!"); }

                #endregion

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

                #region 啟動交易或指定RootTranscation

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

                #endregion

                if (dtForCopy != null && dtForCopy.Rows.Count > 0)
                {

                    for (Int32 i = 0; i <= dtForCopy.Rows.Count - 1; i++)
                    {
                        #region

                        //MASTER
                        strCurrentId = dtForCopy.Rows[i]["ID"].ToString().Trim();

                        if (strCurrentId != strLastId)
                        {
                            htParamsMaster.Clear();
                            htParamsMaster.Add("CODE", dtForCopy.Rows[i]["new_code"]);
                            htParamsMaster.Add("UserID", dtForCopy.Rows[i]["createuid"]);
                            htParamsMaster.Add("ChanNO", dtForCopy.Rows[i]["new_chan_no"]);
                            htParamsMaster.Add("STORE", dtForCopy.Rows[i]["new_store"]);
                            htParamsMaster.Add("ROOT_NO", dtForCopy.Rows[i]["ROOT_NO"]);
                            htParamsMaster.Add("PMA", dtForCopy.Rows[i]["PMA"]);
                            htParamsMaster.Add("PATTERN_NO", dtForCopy.Rows[i]["PATTERN_NO"]);

                            htParamsMaster.Add("SPEC_TYPE", dtForCopy.Rows[i]["spec_type"]);
                            htParamsMaster.Add("SPEC_QTY", dtForCopy.Rows[i]["spec_qty"]);
                            htParamsMaster.Add("S_START_DATE", dtForCopy.Rows[i]["s_start_date"]);
                            htParamsMaster.Add("S_END_DATE", dtForCopy.Rows[i]["s_end_date"]);
                            htParamsMaster.Add("REASON", dtForCopy.Rows[i]["reason"]);

                            //DETAIL
                            dtForCopyDetail.Clear();

                            #region 記錄明細
                            DataRow[] dRows = dtForCopy.Select("ID=" + strCurrentId + " and START_RANKQTY is not null");
                            foreach (DataRow oRow in dRows)
                            {
                                dr = dtForCopyDetail.NewRow();
                                #region old
                                //dr["ID"] = strCurrentId;
                                //dr["CREATEDATE"] = DateTime.Now;
                                //dr["CREATEUID"] = dtForCopy.Rows[k]["createuid"];
                                //dr["UPDATEDATE"] = DateTime.Now;
                                //dr["UPDATEUID"] = dtForCopy.Rows[k]["updateuid"];
                                //dr["START_RANKQTY"] = dtForCopy.Rows[k]["start_rankqty"];
                                //dr["END_RANKQTY"] = dtForCopy.Rows[k]["end_rankqty"];
                                //dr["ADJ_QTY"] = dtForCopy.Rows[k]["adj_qty"];
                                #endregion

                                #region 新增明細
                                dr["ID"] = strCurrentId;
                                dr["CREATEDATE"] = DateTime.Now;
                                dr["CREATEUID"] = oRow["createuid"];
                                dr["UPDATEDATE"] = DateTime.Now;
                                dr["UPDATEUID"] = oRow["updateuid"];
                                dr["START_RANKQTY"] = oRow["start_rankqty"];
                                dr["END_RANKQTY"] = oRow["end_rankqty"];
                                dr["ADJ_QTY"] = oRow["adj_qty"];
                                #endregion

                                dtForCopyDetail.Rows.Add(dr);
                            }
                            #endregion

                            #region 處理新增
                            PID = CreateMaster(htParamsMaster, DBT);
                            if (PID != 0)
                            {
                                if (dtForCopyDetail != null)
                                    CreateDetail(PID, dtForCopyDetail, DBT);
                            }
                            else
                            {
                                throw new Exception("複製0筆資料!");
                            }
                            #endregion

                        }

                        strLastId = dtForCopy.Rows[i]["ID"].ToString().Trim();

                        #endregion
                    }


                    #region 交易成功

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

                    #endregion


                    bResult = true;

                }
                else
                {
                    bResult = false; 
                }

                return bResult;

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

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

                #endregion

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

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }
                #endregion
            }
            #endregion
        }
        /// <summary>
        /// 基本表單新增BCO
        /// </summary>
        /// <param name="htParams">輸入變數</param>
        /// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
        /// <returns>回傳新增之ID</returns>
        public int CreateMasterAndDetail(Hashtable htParams, DataTable DetailData, DbTransaction RootDBT)
        {
            #region
            bool IsRootTranscation = false;
            Int32 PID = 0;
            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(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

                PID = CreateMaster(htParams, DBT);
                if (PID != 0)
                {
                    if (htParams["SPEC_TYPE"].ToString() == "1")//加減本數
                    {
                        if (DetailData != null)
                            CreateDetail(PID, DetailData, DBT);
                    }
                    else
                    {

                        htParams.Add("PIDForLog", PID);
                        htParams.Add("UIDForLog", htParams["UserID"]);

                        DeleteDetailAndLog(htParams, DBT);
                    }



                    #region 交易成功

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

                    #endregion
                }
                else
                {
                    throw new Exception("新增0筆資料!");
                }

                return PID;

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

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

                #endregion

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

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }
                #endregion
            }
            #endregion
        }
        public DataTable QueryPatternSpecByFindForPattern(ArrayList Paramslist)
        {
            #region
            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);
                QueryALOCommon ALOCommon = new QueryALOCommon(strConn);


                DataTable dt = ALOM.doQueryByFindForPattern(Paramslist);

                #region
                ArrayList ParameterList = new ArrayList();
                string strStstusCode = "", strStstus = "", strStstusDesc = "";
                if (dt != null)
                {
                    for (Int32 i = 0; i <= dt.Rows.Count - 1; i++)
                    {
                        strStstusCode = "";
                        strStstus = "";
                        strStstusDesc = "";

                        ParameterList.Clear();
                        ParameterList.Add(dt.Rows[i]["chan_no"]);
                        ParameterList.Add(dt.Rows[i]["store"]);

                        strStstusDesc = ALOCommon.CheckCurrentStoreState(ParameterList);
                   
                        if (strStstusDesc.Trim() != "")
                        {
                            strStstus = "未生效";
                            strStstusCode = "0";
                        }
                        else
                        {
                            strStstus = "生效";
                            strStstusCode = "1";
                        }

                        if (strStstusDesc.Trim().Length > 0)
                        {
                            if (strStstusDesc.Substring(strStstusDesc.Length - 1, 1) == ",")
                            {
                                strStstusDesc = strStstusDesc.Substring(0, strStstusDesc.Length - 1);
                            }
                        }


                        dt.Rows[i]["storestatusCode"] = strStstusCode;
                        dt.Rows[i]["storestatus"] = strStstus;
                        dt.Rows[i]["storestatusdesc"] = strStstusDesc;

                    }
                }
                #endregion

                return dt;

            }
            catch (Exception ex)
            {
                throw GetNewException(ex);
            }
            #endregion
        }
        /// <summary>
        /// 基本表單刪除BCO
        /// </summary>
        /// <param name="htParams">刪除變數</param>
        /// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
        /// <returns>回傳影響筆數</returns>
        public bool DeleteMaster(Hashtable htParams,
                                      DbTransaction RootDBT
                                      )
        {
            bool IsRootTranscation = false;
            bool bResult = false;

            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(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

                ALOM.doDelete(htParams, DBT);

                #region 交易成功

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

                #endregion

                bResult = true;
                return bResult;

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

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

                #endregion

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

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

                #endregion

            }


        }
        public bool CheckPATTERNSPECExist(Hashtable htParams)
        {
            #region
            try
            {
                bool bResult = false;
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);
                DataTable dt = ALOM.doCheckPATTERNSPECExist(htParams);

                if (dt != null && dt.Rows.Count > 0)
                {
                    if (dt.Rows[0][0].ToString().Trim() != "0")
                    {
                        bResult = false;
                    }
                    else 
                    {
                        bResult = true;
                    }
                
                }

                return bResult;
            }
            catch (Exception ex)
            {
                throw GetNewException(ex);
            }
            #endregion
        }
        /// <summary>
        /// 基本表單更新BCO
        /// </summary>
        /// <param name="htParams">輸入變數</param>
        /// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
        /// <returns>回傳更新成功與否</returns>
        public bool UpdateMasterAndDetail(Hashtable htParams, DataTable dtForDetailAddedOrModified, string strUIDForLog, DataTable dtForDetailDeleted, DbTransaction RootDBT)
        {
            #region
            bool IsRootTranscation = false;
            bool bResult = false;
            try
            {
                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(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

                bResult = UpdateMaster(htParams, DBT);

                if (bResult)
                {
                    if (htParams["NEW_SPEC_TYPE"].ToString() == "1")//加減本數
                    {
                        if (dtForDetailAddedOrModified != null || dtForDetailDeleted != null)
                            bResult = UpdateDetail(dtForDetailAddedOrModified, strUIDForLog, dtForDetailDeleted, DBT);
                    }
                    else
                    {
                        bResult = DeleteDetailAndLog(htParams, DBT);
                    }


                    #region 交易成功

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

                    #endregion
                }
                else
                {
                    throw new Exception("新增0筆資料!");
                }

                return bResult;

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

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

                #endregion

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

                if (IsRootTranscation)
                {
                    //獨立呼叫Transcation,關閉連線
                    if (Conn.State == ConnectionState.Connecting)
                    {
                        Conn.Close();
                    }
                }
                #endregion
            }
            #endregion
        }
        public bool CheckPATTERNSPECCanAdd(Hashtable htParams,ref string strErrMsg)
        {
            #region
            try
            {
                bool bResult = false;
                Int32 iErrCounts = 0;

                VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);

                MaintainStoreAssignItem BCO = new MaintainStoreAssignItem(strConn);

                #region
                DataTable dt1 = ALOM.doGetValidationDataForCheck(htParams);


                if (dt1 != null && dt1.Rows.Count > 0) 
                {
                    //是否存在於通路主檔
                    if (dt1.Rows[0][0].ToString().Trim() == "0") 
                    {
                        strErrMsg += "通路不存在,";
                        iErrCounts++;
                    }

                    //是否存在於門市主檔
                    if (dt1.Rows[0][1].ToString().Trim() == "0")
                    {
                        strErrMsg += "門市不存在,";
                        iErrCounts++;
                    }
                }
                #endregion

                if (strErrMsg.Trim().Length > 0)
                {
                    if (strErrMsg.Substring(strErrMsg.Length - 1, 1) == ",")
                    {
                        strErrMsg = strErrMsg.Substring(0, strErrMsg.Length - 1);
                    }
                }


                if (iErrCounts == 0)
                    bResult = true;
                else
                    bResult = false;

                return bResult;
            }
            catch (Exception ex)
            {
                throw GetNewException(ex);
            }
            #endregion
        }
 public DataTable QueryLastPATTERNSPECDetail(Hashtable htParams)
 {
     #region
     try
     {
         VDS_ALO_PATTERNSPEC_MAIN_DBO ALOM = new VDS_ALO_PATTERNSPEC_MAIN_DBO(ref USEDB);
         return ALOM.doQueryLastPATTERNSPECDetail(htParams);
     }
     catch (Exception ex)
     {
         throw GetNewException(ex);
     }
     #endregion
 }