public DataTable QuerySwitch(QueryType QT, ArrayList ParameterList)
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
DataTable Dt;
try
{
switch (QT)
{
case QueryType.QueryAPLDATE:
Dt = VCPD.DO_QUERY_APLDATE(ParameterList);
break;
case QueryType.QueryStore:
Dt = VCPD.DO_CRM011_QUERY_STORE(ParameterList);
break;
case QueryType.CRM122_Store_Chain_All:
Dt = VCPD.do_CRM122_Store_Chain_All();
break;
default:
Dt = new DataTable();
break;
}
return Dt;
}
catch (Exception ex)
{
throw GetNewException(ex);
}
}
/// <summary>
/// 根據"門市"AND"通路"查詢VDS_STM_STORE_UNIQUE
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable QUERY_STORE_BY_STOREANDCHAN(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_STORE_BY_STOREANDCHAN(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// CRM131_取得 按下"列印"，表單類別"彙總表"
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable PrintSum(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_DYNAMIC_SQL(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// CRM021_按下BUTTON 查詢
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable QueryCRMRecordAndTicketByFind(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_DYNAMIC_SQL(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 取得登入人員的角色名稱
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable QUERY_LOGIN_CODEANDNAME(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_LOGIN_CODEANDNAME(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 取得所有有效通路
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>所有有效通路 </returns>
public DataTable QueryStoreChainByAll(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_STORE_CHAIN_ALL(ParameterList);
}
catch (Exception ex)
{
throw GetNewException(ex);
}
}
/// <summary>
/// 查詢 VDS_CRM_RECORD_MAIN 和 VDS_CRM_RECORD_DETL 和 VDS_STM_STORE 的資料
/// 根據畫面上使用者輸入的條件
/// </summary>
/// <param name="ParameterList">MASTER TABLE 的變數</param>
/// <returns>VDS_CRM_RECORD_MAIN 和 VDS_CRM_RECORD_DETL 和 VDS_STM_STORE 的資料</returns>
public DataSet QueryCRMRecordByFind2(ArrayList ParameterList)
{
#region
try
{
DataSet ds_Return = new DataSet();
DataTable dt_Master = new DataTable();
DataTable dt_Detail = new DataTable();
DataTable dt_Store = new DataTable();
DBO.VDS_CRM_RECORD_MAINDBO VCRM = new VDS_CRM_RECORD_MAINDBO(ref USEDB);
DBO.VDS_CRM_RECORD_DETLDBO VCRD = new VDS_CRM_RECORD_DETLDBO(ref USEDB);
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
dt_Master = VCRM.QUERY_CRM011_MAIN_BY_CONDITONS(ParameterList);
if (dt_Master.Rows.Count != 0 )
{
#region 根據 Master 所抓出來來的資料，來讀取 Detail 的資料
int i_Pid = int.Parse(dt_Master.Rows[0]["ID"].ToString());
string s_UpdateUid = ParameterList[9].ToString();
DateTime d_UpdateDate = DateTime.Parse(ParameterList[10].ToString());
dt_Detail = VCRD.QUERY_CRM011_DETL_BY_PID(i_Pid, s_UpdateUid, d_UpdateDate);
#endregion
#region 根據 Master 所抓出來來的資料，來讀取 VDS_STM_STORE 的資料
ArrayList ParameterList_Store = new ArrayList();
ParameterList_Store.Add(dt_Master.Rows[0]["STORE"]);
ParameterList_Store.Add(dt_Master.Rows[0]["CHAN_NO"]);
ParameterList_Store.Add(ParameterList[9]);
ParameterList_Store.Add(ParameterList[10]);
dt_Store = VCPD.DO_CRM011_QUERY_STORE(ParameterList_Store);
#endregion
#region 設定回傳 DataSet
dt_Master.TableName = "VDS_CRM_RECORD_MAIN";
dt_Detail.TableName = "VDS_CRM_RECORD_DETL";
dt_Store.TableName = "VDS_STM_STORE";
ds_Return.Tables.Add(dt_Master.Copy());
ds_Return.Tables.Add(dt_Detail.Copy());
ds_Return.Tables.Add(dt_Store.Copy());
dt_Master.Dispose();
dt_Detail.Dispose();
dt_Store.Dispose();
#endregion
}
return ds_Return;
}
catch (Exception ex)
{
throw GetNewException(ex);
}
#endregion
}
/// <summary>
/// 查詢 VDS_CRM_RECORD_MAIN 和 VDS_CRM_RECORD_DETL 和 VDS_STM_STORE 的資料
/// 根據 VDS_CRM_RECORD_MAIN.CODE
/// </summary>
/// <param name="ParameterList">MASTER TABLE 的變數</param>
/// <returns>VDS_CRM_RECORD_MAIN 和 VDS_CRM_RECORD_DETL 和 VDS_STM_STORE 的資料</returns>
public DataSet QueryCRMRecordByCode(ArrayList ParameterList)
{
try
{
DataSet ds_Return = new DataSet();
DataTable dt_Master = new DataTable();
DataTable dt_Detail = new DataTable();
DataTable dt_Store = new DataTable();
DBO.VDS_CRM_RECORD_MAINDBO VCRM = new VDS_CRM_RECORD_MAINDBO(ref USEDB);
DBO.VDS_CRM_RECORD_DETLDBO VCRD = new VDS_CRM_RECORD_DETLDBO(ref USEDB);
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
dt_Master = VCRM.QUERY_CRM011_MAIN_BY_CODE(ParameterList);
if (dt_Master.Rows.Count != 0)
{
dt_Detail = VCRD.QUERY_CRM011_DETL_BY_PID(int.Parse(dt_Master.Rows[0]["ID"].ToString()), (string)ParameterList[1], (DateTime)ParameterList[2]);
ArrayList ParameterList_Store = new ArrayList();
ParameterList_Store.Add(dt_Master.Rows[0]["STORE"]);
ParameterList_Store.Add(dt_Master.Rows[0]["CHAN_NO"]);
ParameterList_Store.Add(ParameterList[1]);
ParameterList_Store.Add(ParameterList[2]);
dt_Store = VCPD.DO_CRM011_QUERY_STORE(ParameterList_Store);
dt_Master.TableName = "VDS_CRM_RECORD_MAIN";
dt_Detail.TableName = "VDS_CRM_RECORD_DETL";
dt_Store.TableName = "VDS_STM_STORE";
ds_Return.Tables.Add(dt_Master.Copy());
ds_Return.Tables.Add(dt_Detail.Copy());
ds_Return.Tables.Add(dt_Store.Copy());
dt_Master.Dispose();
dt_Detail.Dispose();
dt_Store.Dispose();
}
return ds_Return;
}
catch (Exception ex)
{
throw GetNewException(ex);
}
}
/// <summary>
/// 根據門市選取通路與通路名稱
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public DataTable GetChanNoAndNameByStore(ArrayList ParameterList)
{
try
{
sb.Remove(0, sb.Length);
sb.AppendFormat("select sc.code as CHAN_NO, sc.name as CHAN_NAME " +
" from VDS_STM_STORECHAN sc , VDS_STM_STORE_UNIQUE su "+
" where sc.Enable =1 and su.Enable =1 and sc.CODE= su.Chan_No" +
" and su.Store ='{0}'",
ParameterList[0].ToString());
string UserID = ParameterList[1].ToString();
ParameterList.Clear();
ParameterList.Add(sb.ToString());
ParameterList.Add(UserID);
ParameterList.Add("取得通路代號、名稱");
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QUERY_DYNAMIC_SQL(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 根據使用者代號取得使用者名稱與角色
/// </summary>
/// <param name="s_UID"></param>
/// <returns></returns>
public void GetUserAndRoleName(string s_UID,ref string sUserName,ref string sRoleName)
{
try
{
sb.Remove(0, sb.Length);
sb.AppendFormat("select u.NAME as USER_NAME, r.NAME as ROLE_NAME "+
" from SYS_USER u, SYS_USERROLEREL ur, SYS_ROLE r "+
" where u.enable=1 and u.code ='{0}'"+
" and ur.enable=1 and u.code = ur.usercode(+) "+
" and r.enable=1 and ur.rolecode = r.code(+)", s_UID);
ArrayList ParameterList = new ArrayList();
ParameterList.Clear();
ParameterList.Add(sb.ToString());
ParameterList.Add(s_UID);
ParameterList.Add("取得使用者與角色名稱");
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
DataTable dt = VCPD.QUERY_DYNAMIC_SQL(ParameterList);
if (dt != null && dt.Rows.Count > 0)
{
sUserName = dt.Rows[0][0].ToString().Trim() == "" ? s_UID : dt.Rows[0][0].ToString().Trim();
sRoleName = dt.Rows[0][1].ToString().Trim() == "" ? "無對應角色" : dt.Rows[0][1].ToString().Trim();
}
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 檢查一段碼、二段碼是否屬於該品號、期別
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public bool CheckItemPeriodBarcode(ArrayList ParameterList)
{
try
{
sb.Remove(0, sb.Length);
sb.AppendFormat(@"select ITEM, PERIOD, BARCODE, PERIOD_BARCODE " +
" from VDS_ITM_PERIOD_MAIN " +
" where Enable=1 and ITEM ='{0}' and PERIOD = '{1}'" +
" and BARCODE='{2}' and PERIOD_BARCODE='{3}' ",
ParameterList[0].ToString(),
ParameterList[1].ToString(),
ParameterList[2].ToString(),
ParameterList[3].ToString()
);
string UserID = ParameterList[4].ToString();
ParameterList.Clear();
ParameterList.Add(sb.ToString());
ParameterList.Add(UserID);
ParameterList.Add("檢查一段碼、二段碼是否屬於該品號、期別");
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
DataTable dt = VCPD.QUERY_DYNAMIC_SQL(ParameterList);
return (dt.Rows.Count > 0);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 檢查期別是否屬於該品號
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public bool CheckItemAndPeriod(ArrayList ParameterList)
{
try
{
sb.Remove(0, sb.Length);
sb.AppendFormat(@"select ITEM, PERIOD from VDS_ITM_PERIOD_MAIN " +
" where ITEM ='{0}' and PERIOD = '{1}' and Enable=1 ",
ParameterList[0].ToString(),
ParameterList[1].ToString());
string UserID = ParameterList[2].ToString();
ParameterList.Clear();
ParameterList.Add(sb.ToString());
ParameterList.Add(UserID);
ParameterList.Add("檢查期別是否屬於該品號");
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
DataTable dt = VCPD.QUERY_DYNAMIC_SQL(ParameterList);
return (dt.Rows.Count > 0);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
/// <summary>
/// 檢查門市是否屬於該通路
/// </summary>
/// <param name="ParameterList"></param>
/// <returns>回傳查詢結果</returns>
public bool CheckChanAndStore(ArrayList ParameterList)
{
try
{
sb.Remove(0, sb.Length);
sb.AppendFormat(@"select CHAN_NO, STORE from VDS_STM_STORE_UNIQUE " +
" where CHAN_NO ='{0}' and STORE= '{1}' and Enable=1 ",
ParameterList[0].ToString(),
ParameterList[1].ToString());
string UserID = ParameterList[2].ToString();
ParameterList.Clear();
ParameterList.Add(sb.ToString());
ParameterList.Add(UserID);
ParameterList.Add("檢查門市是否屬於該通路");
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
DataTable dt = VCPD.QUERY_DYNAMIC_SQL(ParameterList);
return (dt.Rows.Count > 0);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}
public DataTable QueryUserByRole(ArrayList ParameterList)
{
try
{
DBO.VDS_CRM_PUBLIC_DBO VCPD = new VDS_CRM_PUBLIC_DBO(ref USEDB);
return VCPD.QueryUserByRole(ParameterList);
}
catch (Exception ex)
{ throw GetNewException(ex); }
}