/// <summary>
/// 取得 VDS_CRM_ORDER_MAIN && VDS_CRM_ORDER_DETL 的 Data
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataSet QUERY_CRM041_MAIN_AND_DETL(ArrayList ParameterList)
{
try
{
DataSet ds_Return = new DataSet();
DataTable dt_Return_Main = new DataTable();
DataTable dt_Return_Detl = new DataTable();
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(ref USEDB);
dt_Return_Main = VCOM.QUERY_CRM041_MAIN(ParameterList);
dt_Return_Main.TableName = "VDS_CRM_ORDER_MAIN";
dt_Return_Detl = VCOD.QUERY_CRM041_DETL(ParameterList);
dt_Return_Detl.TableName = "VDS_CRM_ORDER_DETL";
ds_Return.Tables.Add(dt_Return_Main.Copy());
ds_Return.Tables.Add(dt_Return_Detl.Copy());
dt_Return_Main.Clear();
dt_Return_Detl.Clear();
dt_Return_Main.Dispose();
dt_Return_Detl.Dispose();
return ds_Return;
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 取得手開單配本單一門市資訊
/// </summary>
/// <param name="ParameterList">
/// [@門市進貨日]、[@通路起]、[@通路迄]、[@門市起]、[@門市迄]
/// </param>
/// <returns>@查詢結果(通路代號,門市代號,門市名稱,路線,路順)</returns>
public DataTable QueryOutDisCRMStore(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_OUT_CRM_CRMSTORE(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 取得手開單配本單一門市資訊
/// </summary>
/// <param name="ParameterList">
/// [@門市進貨日]、[@通路起]、[@通路迄]、[@門市起]、[@門市迄]
/// </param>
/// <returns>@查詢結果(通路代號,門市代號,門市名稱,路線,路順)</returns>
public DataTable QueryDistinctCRMAcceptStore(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_DISTINCT_CRM_ACCEPTSTORE(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 匯出客服訂單明細表資料，請參考（[CRM16_客服訂單明細表列印(匯出報表格式)]。
/// CRM161_取得 按下"匯出"，表單類別"明細表"
/// </summary>
/// <param name="ParameterList">
/// @訂單日期起、訂單日期迄、@訂單狀況、@通路、@門市、@品號、
/// @營業所起、@營業所迄、@供應商起、@供應商迄
/// </param>
/// <returns>回傳查詢結果</returns>
public DataTable Export(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_CRM161_EXPORT(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 列印客服圖書訂單缺貨明細表，請參考（[CRM15_客服圖書訂單缺貨明細表(報表格式)]。
/// CRM151_取得 按下"列印"，表單類別"明細表"
/// </summary>
/// <param name="ParameterList">
/// @訂單日期起、訂單日期迄、@通路、@門市、@品號、
/// @營業所起、@營業所迄、@供應商起、@供應商迄、@缺貨天數選擇
/// </param>
/// <returns>回傳查詢結果</returns>
public DataTable PrintDetail(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_CRM151_PRINTDETAIL(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，查詢特殊訂單明細By品項
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@品號、@期別
/// </param>
/// <returns>@查詢結果集</returns>
public DataTable QueryCRMOrderDetil(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QueryCRMOrderDetil(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，查詢訂單記錄中的特殊配本訂單品項資訊
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@確認日期、@訂單擷轉序號起、@訂單擷轉序號迄
/// </param>
/// <returns>@查詢結果集</returns>
public DataTable QueryCrm09TransferedInfo(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.doQueryCrm09TransferedInfo(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// [CRM15_客服圖書訂單缺貨彙總表(報表格式)] 的 [訂單數]。
/// </summary>
/// <param name="ParameterList">
/// @缺貨天數選擇
/// </param>
/// <returns>回傳查詢結果</returns>
public int QUERY_CRM151_PRINTSUM_COUNT(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_CRM151_PRINTSUM_COUNT(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據傳入參數，查詢D儲區處理所需處理資訊。
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日,@門市進貨日, @群分類,
/// @擷轉序號起,@擷轉序號迄, @執行者, @執行日期
/// </param>
/// <returns>@處理訊息, @處理批號, @查詢結果資料集(cursor)</returns>
public ArrayList QueryCRMTransTmpForHQStock(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QueryCRMTransTmpForHQStock(ParameterList);
}
catch (Exception ex)
{
throw GetNewException(ex);
}
}
}
public DataTable QueryForOverDuForCheck2(ArrayList ParameterList)
{
#region
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_FOR_OVERDU_FORCHECK2(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
/// <summary>
/// 查詢訂單擷轉記錄筆數
/// </summary>
/// <param name="ParameterList">[@訂單擷轉日], [@通路]、[@門市]</param>
/// <returns>@筆數(number)</returns>
public int QueryCountsByStore(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_COUNTS_BYSTORE(ParameterList);
}
catch (Exception ex)
{
throw GetNewException(ex);
}
}
/// <summary>
/// 歸零異常品項訂單
/// </summary>
/// <param name="ParameterList">
/// @處理批號, @進貨日, @使用者
/// </param>
/// <param name="RootDBT"></param>
/// <returns></returns>
public string SetTmpOrderZero(ArrayList ParameterList, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
string strResult;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 根據條件，歸零TMP異常品項訂單
strResult = VCOM.CRM09_SetTmpOrderZero(ParameterList, DBT);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return strResult;
}
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
}
}
/// <summary>
/// 刪除 VDS_CRM_ORDER_MAIN 和 VDS_CRM_ORDER_DETL 的資料
/// </summary>
/// <param name="ParameterList">MASTER TABLE 的變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <param name="dt_Detail">DETAIL TABLE</param>
public void DeleteCRMOrder(ArrayList ParameterList, DbTransaction RootDBT, DataTable dt_Detail)
{
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(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
//2010/09/20 reverse DETL & MAIN executive progress
if (dt_Detail.Rows.Count != 0)
{
foreach (DataRow dRow in dt_Detail.Rows)
{ VCOD.DELETE_CRM041_DETL(ParameterList, DBT, dRow); }
}
VCOM.DELETE_CRM041_MAIN(ParameterList, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
}
/// <summary>
/// 根據傳入參數，檢查該(通路/門市)是否已存在未結案之同品項。
/// </summary>
/// <param name="ParameterList">@通路,@門市,@品號,@期別</param>
/// <returns>回傳查詢結果(@驗證結果(boolean: 是或否), @訂單編號)</returns>
public DataTable CheckOrderItemExist(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.CHECK_ORDERITEM_EXIST(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 新增 VDS_CRM_RECORD_MAIN 和 VDS_CRM_RECORD_DETL 的資料
/// </summary>
/// <param name="ParameterList">MASTER TABLE 的變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <param name="dt_Detail">DETAIL TABLE</param>
/// <returns>VDS_CRM_RECORD_MAIN.ID</returns>
public int CreateCRMOrder(ArrayList ParameterList, DbTransaction RootDBT, DataTable dt_Detail)
{
int i_Id = 0;
string s_Log_User = string.Empty;
DateTime d_Now;
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(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
//寫入 Master Table
i_Id = VCOM.ADD_CRM041_MAIN(ParameterList, DBT);
d_Now = DateTime.Parse(ParameterList[2].ToString());
s_Log_User = ParameterList[3].ToString();
foreach (DataRow dRow in dt_Detail.Rows)
{ VCOD.ADD_CRM041_DETL(DBT, dRow, i_Id, s_Log_User, d_Now); }
#region Commit前檢查同門市未結案品項是否重複
ArrayList CheckList = new ArrayList();
CheckList.Clear();
CheckList.Add(ParameterList[9].ToString());//CHAN_NO
CheckList.Add(ParameterList[10].ToString());//STORE
CheckList.Add(i_Id); // MASTER.ID
CheckList.Add(ParameterList[3].ToString()); //CREATEUID
string sCheck = CheckOrderItemBeforeCommit(CheckList, DBT);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return i_Id;
}
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
}
}
/// <summary>
/// 檢查是否部分訂單已擷轉
/// </summary>
/// <param name="ParameterList">變數集</param>
/// <param name="RootDBT">交易</param>
/// <returns>true:部分訂單已擷轉;false:訂單無結轉</returns>
public bool CRM07CheckWithSameItem(ArrayList ParameterList,
DbTransaction RootDBT
)
{
#region
bool IsRootTranscation = false;
bool Result = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 處理 CRM07 檢查是否有部分訂單擷轉
Result = VCOM.CheckWithSameItem(ParameterList,
RootDBT
);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
return Result;
#endregion
}
/// <summary>
/// 根據條件，更新TMP檔異常訂單明細
/// </summary>
/// <param name="ParameterList">
/// @ID, @PID, @品號, @期別, @處理批號, @分配量, @差異量(改後-改前)
/// </param>
/// <param name="RootDBT"></param>
/// <returns></returns>
public void UpdateTmpDetail(ArrayList arl_Update, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 根據條件，更新TMP檔異常訂單明細
VCOM.UpdateTmpDetail(arl_Update, DBT);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
}
/// <summary>
/// 根據條件，檢查暫存檔中，門市的異常狀況
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@執行者
/// </param>
/// <returns>@異常店數</returns>
public ArrayList CheckStoreForOverDu(ArrayList ParameterList, DbTransaction RootDBT)
{
#region
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 檢查暫存檔中，門市的異常狀況
ArrayList arl_Return = new ArrayList();
arl_Return = VCOM.CheckStoreForOverDu(ParameterList, DBT);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return arl_Return;
}
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
}
/// <summary>
/// 根據條件，查詢並回傳客服補書訂單資訊（含明細）
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable QueryCRMOrderMasterAndDetail(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_CRMORDER_MASTERANDDETAIL(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，產生訂單擷轉記錄
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@群分類、@執行者、
/// @擷轉確認選擇清單(DataSet or List記錄：ID，PID)。
/// </param>
/// <returns>
/// @處理結果(Boolean：1是0否)v_error_msg varchar2,錯誤訊息
/// @擷轉序號 varchar2,
/// @處理筆數 number
///</returns>
public ArrayList ProcessCreateCRMOrderTransfer(ArrayList ParameterList, DataTable dt_Parameter, DbTransaction RootDBT, out string s_ErrorMsg)
{
bool IsRootTranscation = false;
//string s_ErrorMsg = string.Empty;
ArrayList arl_Return = new ArrayList();
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 處理 CRM07 BUTTON[執行擷轉]鈕
#region 檢查並更新訂單擷轉處理狀態
s_ErrorMsg = VCOM.CHECK_PROCESS_FLAG(ParameterList, RootDBT);
if (s_ErrorMsg != string.Empty)
{ throw new Exception(s_ErrorMsg); }
#endregion
#region 建立訂單擷轉暫存檔資料
foreach (DataRow dRow in dt_Parameter.Rows)
{
s_ErrorMsg = VCOM.MAINTAIN_TEMP_TABLE(dRow, DBT);
if (s_ErrorMsg != string.Empty)
{ throw new Exception(s_ErrorMsg); }
}
#endregion
#region 根據條件，產生訂單擷轉記錄
arl_Return = VCOM.PROCESS_CRM_ORDER_TRANSFER(ParameterList, DBT);
#endregion
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
return arl_Return;
}
/// <summary>
/// 根據條件，查詢是否有重複品項
/// </summary>
/// <param name="ParameterList">
/// @門市進貨日、@群分類
/// </param>
/// <returns></returns>
public void CheckDup(ArrayList ParameterList,out string Err)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
VCOM.CheckDup(ParameterList , out Err);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，清除前次訂單擷轉異常記錄
/// </summary>
/// <param name="ParameterList">
/// @群分類，@登入人員
/// </param>
/// <returns>@處理訊息</returns>
public void CLEAR_PROCESS_FLAG(ArrayList ParameterList, DbTransaction RootDBT)
{
#region
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 處理 CRM07 BUTTON[執行擷轉]鈕
#region 根據條件，產生訂單擷轉記錄
VCOM.doCLEAR_PROCESS_FLAG(ParameterList, DBT);
#endregion
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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>
/// 根據條件，查詢訂單擷轉明細
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@群分類、@訂單擷轉序號
/// </param>
/// <returns></returns>
public DataTable QueryDtlByFind(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_DTL_BYFIND(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，檢查是否可轉特殊配本
/// </summary>
/// <param name="ParameterList">
///
/// </param>
/// <returns>@是否可產生特殊配本 1是0否</returns>
public int CheckCanCreateDisRecord2(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.CheckCanCreateDisRecord2(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，查詢並回前一筆傳客服補書訂單資訊（含明細）
/// </summary>
/// <param name="ParameterList">[@訂單編號]</param>
/// <returns>回傳查詢結果</returns>
public DataSet QueryPriorCRMOrder(ArrayList ParameterList)
{
try
{
DataSet ds_Return = new DataSet();
DataTable dt_Return_Main = new DataTable();
DataTable dt_Return_Detl = new DataTable();
int i_Id = 0;
string s_Log_User = string.Empty;
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(ref USEDB);
dt_Return_Main = VCOM.QUERY_PRIORCRMORDER(ParameterList);
if (dt_Return_Main.Rows.Count != 0)
{
dt_Return_Main.TableName = "VDS_CRM_ORDER_MAIN";
i_Id = int.Parse(dt_Return_Main.Rows[0]["ID"].ToString());
s_Log_User = ParameterList[1].ToString();
ParameterList.Clear();
ParameterList.Add(i_Id);
ParameterList.Add(s_Log_User);
dt_Return_Detl = VCOD.QUERY_CRM041_DETL(ParameterList);
dt_Return_Detl.TableName = "VDS_CRM_ORDER_DETL";
ds_Return.Tables.Add(dt_Return_Main.Copy());
ds_Return.Tables.Add(dt_Return_Detl.Copy());
}
else
{ ds_Return = null; }
dt_Return_Main.Clear();
dt_Return_Detl.Clear();
dt_Return_Main.Dispose();
dt_Return_Detl.Dispose();
return ds_Return;
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據條件，產生訂單擷轉記錄 2
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@群分類、@執行者、
/// @擷轉確認選擇清單(DataSet or List記錄：ID，PID)。
/// </param>
/// <returns>
/// @處理結果(Boolean：1是0否)v_error_msg varchar2,錯誤訊息
/// @擷轉序號 varchar2,
/// @處理筆數 number
///</returns>
public ArrayList ProcessCreateCRMOrderTransfer2(ArrayList ParameterList, DataTable dt_Parameter, DbTransaction RootDBT, out string s_ErrorMsg)
{
bool IsRootTranscation = false;
//string s_ErrorMsg = string.Empty;
ArrayList arl_Return = new ArrayList();
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 處理 CRM07 BUTTON[執行擷轉]鈕
#region 檢查並更新訂單擷轉處理狀態
s_ErrorMsg = VCOM.CHECK_PROCESS_FLAG(ParameterList, RootDBT);
if (s_ErrorMsg != string.Empty)
{ throw new Exception(s_ErrorMsg); }
#endregion
#region 建立訂單擷轉暫存檔資料
//2010/02/08 New Version
//---------------------------------------------
int ICount = 0;
string strErrMsg = "", s_Id = "",
strTransDate = "", strStAcceptDate = "", strRootNo = "", strUserId = "", strSessionId = "";
ParameterList.Clear();
foreach (DataRow dRow in dt_Parameter.Rows)
{
ICount++;
s_Id = dRow["ID"].ToString() + ',';
strTransDate=dRow["TRANS_DATE"].ToString();
strStAcceptDate = dRow["ST_ACCEPT_DATE"].ToString();
strRootNo = dRow["ROOT_NO"].ToString();
strUserId = dRow["USER_ID"].ToString();
strSessionId = dRow["SESSION_ID"].ToString();
if (ICount != 0 && ICount % 500 == 0)
{
s_Id = s_Id.Substring(0, s_Id.Length - 1);
ParameterList.Add(strTransDate);//pic_trans_date
ParameterList.Add(strStAcceptDate);//pic_st_accept_date
ParameterList.Add(strRootNo);//pic_root_no
ParameterList.Add(strUserId);//pic_user_id
ParameterList.Add(s_Id);//pin_id_arr
ParameterList.Add(strSessionId);//pic_session_id
strErrMsg += VCOM.MAINTAIN_TEMP_TABLE2(ParameterList, null);
ParameterList.Clear();
s_Id = "";
}
}
// 最後一筆或不為500之倍數時，在此執行
if (ICount != 0 && ICount % 500 != 0)
{
s_Id = s_Id.Substring(0, s_Id.Length - 1);
ParameterList.Clear();
ParameterList.Add(strTransDate);//pic_trans_date
ParameterList.Add(strStAcceptDate);//pic_st_accept_date
ParameterList.Add(strRootNo);//pic_root_no
ParameterList.Add(strUserId);//pic_user_id
ParameterList.Add(s_Id);//pin_id_arr
ParameterList.Add(strSessionId);//pic_session_id
strErrMsg += VCOM.MAINTAIN_TEMP_TABLE2(ParameterList, null);
}
//---------------------------------------------
#endregion
#region 根據條件，產生訂單擷轉記錄
arl_Return = VCOM.PROCESS_CRM_ORDER_TRANSFER(ParameterList, DBT);
#endregion
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
return arl_Return;
}
/// <summary>
/// 將暫存檔中特殊配本資料匯入配本記錄相關主檔。
/// 各匯入資料表需處理之資料欄位與格式說明，請『一定』要參考 UI Spec說明進行開發。
/// </summary>
/// <param name="ParameterList">
/// @門市進貨日、@執行日期、@執行者、處理批號(SessionId)
/// </param>
/// <param name="RootDBT"></param>
/// <returns>@處理結果、@錯誤訊息</returns>
public ArrayList ProcessCreateDisRecord2(ArrayList ParameterList, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
ArrayList arl_Return = new ArrayList();
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 將暫存檔中特殊配本資料匯入配本記錄相關主檔。
arl_Return = VCOM.CRM091_ProcessCreateDisRecord2(ParameterList, DBT);
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return arl_Return;
}
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
}
}
/// <summary>
/// 根據條件，取得待轉品項清單
/// </summary>
/// <param name="ParameterList">
/// @群分類、@訂單擷轉日、@門市進貨日
/// </param>
/// <returns>回傳查詢結果</returns>
public DataTable QueryForTransfer(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
return VCOM.QUERY_FOR_TRANSFER(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// Commit前判斷是否有同門市重複未結案品項
/// </summary>
/// <returns>VDS_CRM_RECORD_MAIN.ID</returns>
public string CheckOrderItemBeforeCommit(ArrayList ParameterList, DbTransaction RootDBT)
{
string Result;
bool IsRootTranscation = false;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
//執行檢查
Result = VCOM.doCHECK_ORDERITEM_BEF_COMMIT(ParameterList, DBT);
if (Result.Trim() != string.Empty)
{
throw new Exception(Result.Trim());
}
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return Result;
}
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
}
}
/// <summary>
/// 根據條件，產生訂單擷轉記錄 3
/// </summary>
/// <param name="ParameterList">
/// @訂單擷轉日、@門市進貨日、@群分類、@執行者、@PageTimeStamp_ID
/// </param>
/// <returns>
/// @pon_result、@poc_err_msg、@poc_trans_no、@pon_proc_cnt
///</returns>
public DataTable ProcessCreateCRMOrderTransfer3(ArrayList ParameterList, DbTransaction RootDBT)
{
#region
bool IsRootTranscation = false;
ArrayList arl_Return = new ArrayList();
DataTable Dt = null;
try
{
DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(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
#region 處理 CRM07 BUTTON[執行擷轉]鈕
#region 根據條件，產生訂單擷轉記錄
//arl_Return = VCOM.PROCESS_CRM_ORDER_TRANSFER3(ParameterList, DBT);
Dt = VCOM.PROCESS_CRM_ORDER_TRANSFER3(ParameterList, DBT);
#endregion
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
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
}
return Dt;
#endregion
}
