public static List <FixedPricePackageDtl> GetPackagePrices(Int64 pkg_xid)
{
List <FixedPricePackageDtl> dtl_list = new List <FixedPricePackageDtl>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_fixedprice_pkg_price
WHERE pkg_xid=:pkg_xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("pkg_xid", pkg_xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
foreach (DataRow dr in ds.Tables[0].Rows)
{
dtl_list.Add(new FixedPricePackageDtl()
{
XID = dr.ToInt64("xid"),
PKG_XID = dr.ToInt64("pkg_xid"),
PRICE_COND = dr.ToStringEx("price_cond"),
PRICE = dr.ToDouble("price")
});
}
return(dtl_list);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
// 取得折扣主規則
public static B2dDiscountMst GetDiscountMst(Int64 xid)
{
List <B2dDiscountMst> mst_list = new List <B2dDiscountMst>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_mst WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
DataRow dr = ds.Tables[0].Rows[0];
var mst = new B2dDiscountMst()
{
XID = dr.ToInt64("xid"),
DISC_NAME = dr.ToStringEx("disc_name"),
DISC_PERCENT = dr.ToDouble("disc_percent"),
DISC_TYPE = dr.ToStringEx("disc_type"),
S_DATE = dr.ToDateTimeEx("s_date"),
E_DATE = dr.ToDateTimeEx("e_date"),
STATUS = dr.ToStringEx("status"),
RULE_STATUS = dr.ToStringEx("rule_status")
};
return(mst);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static JObject GetSupplierList(string pms_source)
{
var obj = new JObject();
try
{
String sql = @" SELECT xid,pms_supplier_name,pms_supplier_id,kkday_supplier_oid,scm_account,scm_password,pms_source
FROM pms_suppliers
WHERE
1=1
AND pms_source = :PMS_SOURCE ";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("PMS_SOURCE", pms_source)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Startup.Instance.npg_conn, CommandType.Text, sql, sqlParams);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
}
catch (Exception ex)
{
_log.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
// 折扣明細清單
public static B2dDiscountDtl GetDiscountDtl(Int64 xid)
{
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_dtl WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid),
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
DataRow dr = ds.Tables[0].Rows[0];
var dtl = new B2dDiscountDtl()
{
XID = dr.ToInt64("xid"),
MST_XID = dr.ToInt64("mst_xid"),
DISC_TYPE = dr.ToStringEx("disc_type"),
DISC_LIST = dr.ToStringEx("disc_list"),
DISC_LIST_NAME = dr.ToStringEx("disc_list_name"),
WHITELIST = dr.ToStringEx("whitelist")
};
return(dtl);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static B2dDiscountCurrAmt GetDiscountCurrAmt(Int64 xid)
{
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_curr_amt WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
DataRow dr = ds.Tables[0].Rows[0];
var currAmt = new B2dDiscountCurrAmt()
{
XID = dr.ToInt64("xid"),
MST_XID = dr.ToInt64("mst_xid"),
CURRENCY = dr.ToStringEx("currency"),
AMOUNT = dr.ToDouble("amount")
};
return(currAmt);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Gets the black list.撈所有的黑名單
/// </summary>
/// <returns>The black list.</returns>
public static JObject GetBlackList()
{
var obj = new JObject();
try
{
String sql = @"SELECT b.prod_no
FROM b2b.b2d_list_price_blacks b ";
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
// 取得
public static B2dVoucherAddon GetVoucherAddon(Int64 comp_xid)
{
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_voucher_addon
WHERE company_xid=:company_xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", comp_xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
DataRow dr = ds.Tables[0].Rows[0];
var vouchAddon = new B2dVoucherAddon()
{
XID = dr.ToInt64("xid"),
COMPANY_XID = dr.ToInt64("company_xid"),
COMPANY_NAME = dr.ToStringEx("company_name"),
EMAIL = dr.ToStringEx("company_email"),
LOGO_URL = dr.ToStringEx("company_logo_url"),
TEL = dr.ToStringEx("company_tel"),
ADDRESS = dr.ToStringEx("company_address")
};
return(vouchAddon);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public DataTable SqlConsulta(string connectionString, string nameRelation, string whereList, string ordersList, string fieldsList)
{
if (string.IsNullOrEmpty(nameRelation))
{
throw new ArgumentException("nameTable");
}
string conditionsOrder = string.Empty;
if (!string.IsNullOrEmpty(whereList))
{
conditionsOrder = " where " + whereList;
}
if (!string.IsNullOrEmpty(ordersList))
{
conditionsOrder = conditionsOrder + " order by " + ordersList;
}
string listFields = fieldsList ?? "*";
string sql = string.Format("select {0} from {1} {2}", listFields, nameRelation, conditionsOrder);
DataTable dt = null;
switch (TypeDataBase.ToLower())
{
case "postgresql":
dt = NpgsqlHelper.ExecuteDataset(connectionString, CommandType.Text, sql).Tables[0];
break;
case "sqlserver":
dt = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, sql).Tables[0];
break;
}
return(dt);
}
//public static DataSet GetAllUser() {
// DataSet ds = null;
// try {
// string strsql = "select * from is4.users";
// ds = NpgsqlHelper.ExecuteDataset(Website.Instance.ERP_DB, CommandType.Text, strsql);
// }
// catch (Exception ex) {
// Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
// throw ex;
// }
// return ds;
//}
// 取得使用者清單
public static List <CustomUser> GetAllUser()
{
List <CustomUser> ulist = new List <CustomUser>();
try {
string strsql = "select * from is4.users where status='01'";
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.IS4_DB, CommandType.Text, strsql);
// Console.WriteLine("DB連線："+$"{Website.Instance.IS4_DB}");
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
CustomUser user = new CustomUser()
{
SubjectId = dr.ToStringEx("XID"),
UserName = dr.ToStringEx("USER_NO"),
Password = dr.ToStringEx("USER_PASS"),
IsActive = dr.ToStringEx("STATUS").Equals("01") ? true : false//00 diseable
};
ulist.Add(user);
}
}
} catch (Exception ex) {
Console.WriteLine($"{ex.Message} {ex.StackTrace} {Website.Instance.IS4_DB}");
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(ulist);
}
// 取得分銷商「我的帳號」資訊
public static B2dUserProfile GetProfile(string email)
{
try
{
string sqlStmt = @"SELECT a.xid, a.user_uuid, a.email, a.account_type,
a.name_first, a.name_last, a.name_last || a.name_first AS name, a.department, a.gender_title,
a.job_title, a.tel, a.enable, b.xid AS comp_xid, b.comp_name, b.comp_tel_country_code, b.comp_invoice,
b.comp_url, b.comp_address
FROM b2b.b2d_account a
JOIN b2b.b2d_company b ON a.company_xid=b.xid
WHERE a.email=:email";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("email", email)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
B2dUserProfile b2dAccount = new B2dUserProfile()
{
XID = dr.ToInt64("xid"),
UUID = dr.ToStringEx("user_uuid"),
EMAIL = dr.ToStringEx("email"),
USER_TYPE = dr.ToStringEx("account_type"),
NAME_FIRST = dr.ToStringEx("name_first"),
NAME_LAST = dr.ToStringEx("name_last"),
NAME = dr.ToStringEx("name"),
DEPARTMENT = dr.ToStringEx("department"),
GENDER_TITLE = dr.ToStringEx("gender_title"),
JOB_TITLE = dr.ToStringEx("job_title"),
TEL = dr.ToStringEx("tel"),
ENABLE = dr.ToBoolean("enable"),
COMPANY_XID = dr.ToInt64("comp_xid"),
COMPANY_NAME = dr.ToStringEx("comp_name"),
TEL_AREA = dr.ToStringEx("comp_tel_country_code"),
INVOICE_NO = dr.ToStringEx("comp_invoice"),
URL = dr.ToStringEx("comp_url"),
ADDRESS = dr.ToStringEx("comp_address")
};
return(b2dAccount);
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(null);
}
// 取得分銷商所有固定價產品
public static List <FixedPriceProductEx> GetFixedPriceProds(Int64 comp_xid, string filter, int skip, int size, string sorting)
{
List <FixedPriceProductEx> prods = new List <FixedPriceProductEx>();
try
{
string sqlStmt = @"SELECT a.*, b.comp_currency, b.comp_name
FROM b2b.b2d_fixedprice_prod a
JOIN b2b.b2d_company b ON a.company_xid=b.xid
WHERE a.company_xid=:company_xid {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("company_xid", comp_xid),
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
prods.Add(new FixedPriceProductEx()
{
XID = dr.ToInt64("xid"),
PROD_NO = dr.ToStringEx("prod_no"),
PROD_NAME = dr.ToStringEx("prod_name"),
STATE = dr.ToStringEx("state"),
CURRENCY = dr.ToStringEx("comp_currency"),
COMPANY_NAME = dr.ToStringEx("comp_name"),
COMPANY_XID = dr.ToInt64("company_xid")
});
}
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(prods);
}
public DataTable ExecuteStoreProcedure(string sqlQuery, params object[] parameterValues)
{
DataTable dt = null;
switch (TypeDataBase.ToLower())
{
case "postgresql":
dt = NpgsqlHelper.ExecuteDataset(ConnectionString, sqlQuery, parameterValues).Tables[0];
break;
case "sqlserver":
dt = SqlHelper.ExecuteDataset(ConnectionString, sqlQuery, parameterValues).Tables[0];
break;
}
return(dt);
}
public DataSet ExecuteStoreProcedureDs(string sqlQuery, params object[] parameterValues)
{
DataSet ds = null;
switch (TypeDataBase.ToLower())
{
case "postgresql":
ds = NpgsqlHelper.ExecuteDataset(ConnectionString, sqlQuery, parameterValues);
break;
case "sqlserver":
ds = SqlHelper.ExecuteDataset(ConnectionString, sqlQuery, parameterValues);
break;
}
return(ds);
}
public static B2dCompany GetCompany(Int64 xid)
{
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_company WHERE xid=:XID";
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, new NpgsqlParameter("XID", xid));
DataRow dr = ds.Tables[0].Rows[0];
var company = new B2dCompany()
{
XID = dr.ToInt64("xid"),
STATUS = dr.ToStringEx("status"),
COMP_COOP_MODE = dr.ToStringEx("comp_coop_mode"),
PAYMENT_TYPE = dr.ToStringEx("payment_type"),
MANAGER_ACCOUNT_XID = dr.ToInt64("manager_account_xid"),
PARENT_COMP_XID = dr.ToInt64("parent_comp_xid"),
COMP_NAME = dr.ToStringEx("comp_name"),
COMP_URL = dr.ToStringEx("comp_url"),
COMP_LICENSE = dr.ToStringEx("comp_license"),
COMP_LICENSE_2 = dr.ToStringEx("comp_license_2"),
COMP_LOCALE = dr.ToStringEx("comp_locale"),
COMP_CURRENCY = dr.ToStringEx("comp_currency"),
COMP_INVOICE = dr.ToStringEx("comp_invoice"),
COMP_COUNTRY = dr.ToStringEx("comp_country"),
COMP_TEL_COUNTRY_CODE = dr.ToStringEx("comp_tel_country_code"),
COMP_TEL = dr.ToStringEx("comp_tel"),
COMP_ADDRESS = dr.ToStringEx("comp_address"),
CHARGE_MAN_FIRST = dr.ToStringEx("charge_man_first"),
CHARGE_MAN_LAST = dr.ToStringEx("charge_man_last"),
CREDITCARD_NO = dr.ToStringEx("creditcard_no"),
CREDITCARD_VALID = dr.ToStringEx("creditcard_valid"),
CREDITCARD_CVC = dr.ToStringEx("creditcard_cvc"),
CONTACT_USER = dr.ToStringEx("contact_user"),
CONTACT_USER_EMAIL = dr.ToStringEx("contact_user_email"),
FINANCE_USER = dr.ToStringEx("finance_user"),
SALES_USER = dr.ToStringEx("sales_user")
};
return(company);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static List <B2dDiscountMst> GetDiscountMst(Int64 company_xid)
{
NpgsqlConnection conn = new NpgsqlConnection(Website.Instance.SqlConnectionString);
List <B2dDiscountMst> mst_list = new List <B2dDiscountMst>();
try
{
conn.Open();
string sqlStmt = @"SELECT A.company_xid, B.comp_name, C.*
FROM b2b.b2d_comp_disc_map A
JOIN b2b.b2d_company B ON A.company_xid=B.xid
JOIN b2b.b2d_discount_mst C ON A.disc_mst_xid=C.xid AND C.status='01'
WHERE B.xid=:company_xid
ORDER BY comp_name
";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", company_xid)
};
var ds = NpgsqlHelper.ExecuteDataset(conn, CommandType.Text, sqlStmt, sqlParams);
foreach (DataRow dr in ds.Tables[0].Rows)
{
mst_list.Add(new B2dDiscountMst()
{
XID = dr.ToInt64("xid"),
DISC_NAME = dr.ToStringEx("disc_name"),
DISC_PERCENT = dr.ToDouble("disc_percent"),
DISC_TYPE = dr.ToStringEx("disc_type"),
S_DATE = dr.ToDateTimeEx("s_date"),
E_DATE = dr.ToDateTimeEx("e_date"),
RULE_STATUS = dr.ToStringEx("rule_status")
});
}
conn.Close();
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
conn.Close();
throw ex;
}
return(mst_list);
}
// 取得折扣主規則
public static List <B2dDiscountMst> GetDiscountMsts(string filter, int skip, int size, string sorting)
{
List <B2dDiscountMst> mst_list = new List <B2dDiscountMst>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_mst
WHERE 1=1 {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
foreach (DataRow dr in ds.Tables[0].Rows)
{
mst_list.Add(new B2dDiscountMst()
{
XID = dr.ToInt64("xid"),
DISC_NAME = dr.ToStringEx("disc_name"),
DISC_PERCENT = dr.ToDouble("disc_percent"),
DISC_TYPE = dr.ToStringEx("disc_type"),
S_DATE = dr.ToDateTimeEx("s_date"),
E_DATE = dr.ToDateTimeEx("e_date"),
RULE_STATUS = dr.ToStringEx("rule_status"),
STATUS = dr.ToStringEx("status"),
CRT_USER = dr.ToStringEx("crt_user"),
CRT_DATETIME = dr.ToDateTime("crt_datetime")
});
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(mst_list);
}
// 取得固定價所有套餐
public static List <FixedPricePackageEx> GetFixedPricePackages(Int64 prod_xid, string filter, int skip, int size, string sorting)
{
List <FixedPricePackageEx> prods = new List <FixedPricePackageEx>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_fixedprice_prod_pkg
WHERE prod_xid=:prod_xid {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("prod_xid", prod_xid),
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
foreach (DataRow dr in ds.Tables[0].Rows)
{
prods.Add(new FixedPricePackageEx()
{
XID = dr.ToInt64("xid"),
PKG_NO = dr.ToStringEx("pkg_no"),
PKG_NAME = dr.ToStringEx("pkg_name"),
ONLINE_SDATE = dr.ToDateTime("online_sdate"),
ONLINE_EDATE = dr.ToDateTime("online_edate"),
PROD_XID = dr.ToInt64("prod_xid"),
Prices = GetPackagePrices(dr.ToInt64("xid"))
});
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(prods);
}
// 從DB取現有token
public static string GetToken(Int64 xid)
{
var token = "";
var sqlStmt = @"SELECT api_token
FROM b2b.b2d_account_api
WHERE xid=" + xid;
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt);
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
token = dr.ToStringEx("api_token");
}
}
return(token);
}
// 取得所有分銷商使用者列表
public static List <B2dBlacklistProduct> GetBlacklistProds(string filter, int skip, int size, string sorting)
{
List <B2dBlacklistProduct> prods = new List <B2dBlacklistProduct>();
try
{
string sqlStmt = @"SELECT *
FROM b2b.b2d_list_price_blacks
WHERE 1=1 {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
prods.Add(new B2dBlacklistProduct()
{
XID = dr.ToInt64("xid"),
PROD_NO = dr.ToStringEx("prod_no"),
PROD_NAME = dr.ToStringEx("prod_name")
});
}
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(prods);
}
// 折扣明細清單
public static List <B2dDiscountDtl> GetDiscountDtls(Int64 mst_xid, string filter, int skip, int size, string sorting)
{
List <B2dDiscountDtl> dtl_lst = new List <B2dDiscountDtl>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_dtl
WHERE mst_xid=:mst_xid {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip),
new NpgsqlParameter("mst_xid", mst_xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
foreach (DataRow dr in ds.Tables[0].Rows)
{
dtl_lst.Add(new B2dDiscountDtl()
{
XID = dr.ToInt64("xid"),
MST_XID = dr.ToInt64("mst_xid"),
DISC_TYPE = dr.ToStringEx("disc_type"),
DISC_LIST = dr.ToStringEx("disc_list"),
DISC_LIST_NAME = dr.ToStringEx("disc_list_name"),
WHITELIST = dr.ToStringEx("whitelist")
});
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(dtl_lst);
}
// 折扣外幣加減價
public static List <B2dDiscountCurrAmt> GetDiscountCurrAmts(Int64 mst_xid,
string filter, int skip, int size, string sorting)
{
List <B2dDiscountCurrAmt> curr_amt_lst = new List <B2dDiscountCurrAmt>();
try
{
string sqlStmt = @"SELECT * FROM b2b.b2d_discount_curr_amt
WHERE mst_xid=:mst_xid {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip),
new NpgsqlParameter("mst_xid", mst_xid)
};
var ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
foreach (DataRow dr in ds.Tables[0].Rows)
{
curr_amt_lst.Add(new B2dDiscountCurrAmt()
{
XID = dr.ToInt64("xid"),
MST_XID = dr.ToInt64("mst_xid"),
CURRENCY = dr.ToStringEx("currency"),
AMOUNT = dr.ToDouble("amount")
});
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(curr_amt_lst);
}
// 取得分銷商所有固定價產品
public static FixedPriceProductEx GetFixedPriceProd(Int64 xid)
{
try
{
FixedPriceProductEx _prod = null;
string sqlStmt = @"SELECT a.*, b.comp_currency
FROM b2b.b2d_fixedprice_prod a
JOIN b2b.b2d_company b ON a.company_xid=b.xid
WHERE a.xid=:xid ";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
_prod = new FixedPriceProductEx()
{
XID = dr.ToInt64("xid"),
PROD_NO = dr.ToStringEx("prod_no"),
PROD_NAME = dr.ToStringEx("prod_name"),
STATE = dr.ToStringEx("state"),
CURRENCY = dr.ToStringEx("comp_currency"),
COMPANY_XID = dr.ToInt64("company_xid")
};
}
return(_prod);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Gets the user.
/// </summary>
/// <returns>The user.</returns>
/// <param name="email">Email.</param>
/// <param name="pw">Pw.</param>
public static JObject GetUser(string email, string pw)
{
var obj = new JObject();
try {
String sql = @"SELECT B.comp_name,B.comp_locale,B.comp_currency,
B.contact_user_email, B.payment_type, A.*
FROM b2b.b2d_account A
JOIN b2b.b2d_company B ON A.company_xid = B.xid
WHERE A.enable = TRUE
AND A.email = :email
AND A.password = :pw ";
NpgsqlParameter[] np = new NpgsqlParameter[] {
new NpgsqlParameter("email", email),
new NpgsqlParameter("pw", pw)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
} catch (Exception ex) {
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
/// <summary>
/// Gets the currency.
/// </summary>
/// <returns>The currency.</returns>
/// <param name="locale">Locale.</param>
public static JObject GetCurrency(string locale)
{
var obj = new JObject();
try
{
String sql = @"SELECT CUR.currency,CUR.name
FROM b2b.b2d_currency CUR
WHERE
1=1
AND CUR.locale = :locale ";
NpgsqlParameter[] np = new NpgsqlParameter[] {
new NpgsqlParameter("locale", locale)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
// 取得所有分銷商使用者列表
public static List <B2dAccount> GetAccounts(string filter, int skip, int size, string sorting, Int64?comp_xid = 0)
{
List <B2dAccount> accounts = new List <B2dAccount>();
try
{
string sqlStmt = "";
if (comp_xid == 0)
{
sqlStmt = @"SELECT a.xid, a.user_uuid, a.email, a.account_type,
a.name_first, a.name_last, a.name_last || a.name_first AS name, a.department, a.gender_title,
a.job_title, a.tel, a.enable, b.xid AS comp_xid, b.comp_name, b.comp_locale, b.comp_currency,
b.comp_tel_country_code
FROM b2b.b2d_account a
JOIN b2b.b2d_company b ON a.company_xid=b.xid
WHERE 1=1 {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
}
else
{
sqlStmt = @"SELECT a.xid, a.user_uuid, a.email, a.account_type,
a.name_first, a.name_last, a.name_last || a.name_first AS name, a.department, a.gender_title,
a.job_title, a.tel, a.enable, b.xid AS comp_xid, b.comp_name, b.comp_locale, b.comp_currency,
b.comp_tel_country_code
FROM b2b.b2d_account a
JOIN b2b.b2d_company b ON a.company_xid=b.xid
WHERE 1=1 AND company_xid=:company_xid{FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
}
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip),
new NpgsqlParameter("company_xid", comp_xid)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
accounts.Add(new B2dAccount()
{
XID = dr.ToInt64("xid"),
UUID = dr.ToStringEx("user_uuid"),
EMAIL = dr.ToStringEx("email"),
NAME_FIRST = dr.ToStringEx("name_first"),
NAME_LAST = dr.ToStringEx("name_last"),
NAME = dr.ToStringEx("name"),
COMPANY_XID = dr.ToInt64("comp_xid"),
COMPANY_NAME = dr.ToStringEx("comp_name"),
DEPARTMENT = dr.ToStringEx("department"),
ENABLE = dr.ToBoolean("enable"),
GENDER_TITLE = dr.ToStringEx("gender_title"),
JOB_TITLE = dr.ToStringEx("job_title"),
CURRENCY = dr.ToStringEx("comp_currency"),
LOCALE = dr.ToStringEx("comp_locale"),
TEL_AREA = dr.ToStringEx("comp_tel_country_code"),
TEL = dr.ToStringEx("tel"),
USER_TYPE = dr.ToStringEx("account_type")
});
}
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(accounts);
}
public static JObject GetDiscRuleList(Int64 comp_xid)
{
var obj = new JObject();
try
{
String sql = @"SELECT MST.*,DISC.*,coalesce(AMT.Amount,0) AS AMT,AMT.currency AS currency
FROM (
SELECT A.xid AS mst_xid,
(SELECT regexp_replace(xpath('.//text()', ('<items>' || XMLAGG(XMLELEMENT(name C, CATE)) || '</items>')::xml)::text, '[{}]', '', 'g') AS CATE
FROM (
SELECT mst_xid, disc_list||'^'||whitelist AS CATE
FROM b2b.b2d_discount_dtl dtl WHERE dtl.disc_type='type2'
)G WHERE G.mst_xid=A.xid
)AS main_cat_wb,
(SELECT regexp_replace(xpath('.//text()', ('<items>' || XMLAGG(XMLELEMENT(name C, PRODNO)) || '</items>')::xml)::text, '[{}]', '', 'g') AS PRODNO
FROM (
SELECT mst_xid, disc_list||'^'||whitelist AS PRODNO
FROM b2b.b2d_discount_dtl dtl WHERE dtl.disc_type='type1'
)G WHERE G.mst_xid=A.xid
)AS prod_no_wb ,
(SELECT regexp_replace(xpath('.//text()', ('<items>' || XMLAGG(XMLELEMENT(name C, dtl_xid)) || '</items>')::xml)::text, '[{}]', '', 'g') AS PRODNO
FROM (
SELECT mst_xid, dtl.xid AS dtl_xid
FROM b2b.b2d_discount_dtl dtl
)G WHERE G.mst_xid=A.xid
)AS disc_dtl_xid
FROM b2b.b2d_discount_mst A
GROUP BY A.xid
)DISC
INNER JOIN b2b.b2d_discount_mst MST ON DISC.mst_xid = MST.xid
LEFT JOIN b2b.b2d_comp_disc_map MAPP ON MST.xid = MAPP.disc_mst_xid
LEFT JOIN b2b.b2d_company COMP ON MAPP.company_xid = COMP.xid
LEFT JOIN b2b.b2d_discount_curr_amt AMT ON MAPP.disc_mst_xid = AMT.mst_xid AND AMT.currency = COMP.comp_currency
WHERE 1=1
AND TO_DATE(TO_CHAR(current_date,'YYYY-MM-dd'),'YYYY-MM-dd') BETWEEN MST.S_DATE AND MST.E_DATE AND MST.status='01'
AND MAPP.company_xid = :COMPANY_XID
ORDER BY mst.xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("COMPANY_XID", comp_xid)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql, sqlParams);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
public static UserAccount UserAuth(string email, string password)
{
Npgsql.NpgsqlConnection conn = new NpgsqlConnection(Website.Instance.SqlConnectionString);
UserAccount _account = null;
try
{
// 連接Posgresql
conn.Open();
string sqlStmt = @"SELECT *, name_last || name_first AS Name
FROM b2b.b2d_account_kkday
WHERE enable=true AND LOWER(email)=LOWER(:email) AND password=:password";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("email", email),
new NpgsqlParameter("password", password)
};
var ds = NpgsqlHelper.ExecuteDataset(conn, CommandType.Text, sqlStmt, sqlParams);
// 檢查是否為有效KKday使用者
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
_account = new KKdayAccount()
{
XID = dr.ToInt64("xid"),
UUID = dr.ToStringEx("user_uuid"),
EMAIL = dr.ToStringEx("email"),
NAME = dr.ToStringEx("name"),
NAME_FIRST = dr.ToStringEx("name_first"),
NAME_LAST = dr.ToStringEx("name_last"),
DEPARTMENT = dr.ToStringEx("department"),
ENABLE = dr.ToBoolean("enable"),
//GENDER_TITLE = dr.ToStringEx("gender_title"),
//JOB_TITLE = dr.ToStringEx("job_title"),
STAFF_NO = dr.ToStringEx("staff_no"),
ROLES = dr.ToStringEx("roles"),
LOCALE = dr.ToStringEx("locale")
};
}
// 檢查是否為分銷商有效使用者
else
{
sqlStmt = @"SELECT a.xid, a.user_uuid, a.email, a.name_first, a.name_last,
a.name_last || a.name_first AS name, a.department, a.job_title, a.enable, a.gender_title,
b.xid as comp_xid, b.comp_name, b.comp_locale AS locale, b.comp_currency AS currency
FROM b2b.b2d_account a
JOIN b2b.b2d_company b ON a.company_xid=b.xid AND b.status='03' --已核准
WHERE enable=true AND LOWER(email)=LOWER(:email) AND password=:password";
sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("email", email),
new NpgsqlParameter("password", password)
};
ds = NpgsqlHelper.ExecuteDataset(conn, CommandType.Text, sqlStmt, sqlParams);
//已核准分銷商登入
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
_account = new B2dAccount()
{
XID = dr.ToInt64("xid"),
UUID = dr.ToStringEx("user_uuid"),
EMAIL = dr.ToStringEx("email"),
NAME = dr.ToStringEx("name"),
NAME_FIRST = dr.ToStringEx("name_first"),
NAME_LAST = dr.ToStringEx("name_last"),
COMPANY_XID = dr.ToInt64("comp_xid"),
COMPANY_NAME = dr.ToStringEx("comp_name"),
DEPARTMENT = dr.ToStringEx("department"),
ENABLE = dr.ToBoolean("enable"),
GENDER_TITLE = dr.ToStringEx("gender_title"),
JOB_TITLE = dr.ToStringEx("job_title"),
CURRENCY = dr.ToStringEx("currency"),
LOCALE = dr.ToStringEx("locale")
};
}
//待審中分銷商登入
else
{
sqlStmt = @"SELECT a.xid, a.user_uuid, a.email, a.name_first, a.name_last,
a.name_last || a.name_first AS name, a.department, a.job_title, a.enable, a.gender_title,
b.xid as comp_xid, b.comp_name, b.comp_locale AS locale, b.comp_currency AS currency
FROM b2b.b2d_account a
JOIN b2b.b2d_company b ON a.company_xid=b.xid AND b.status!='03' --除了已核准外
WHERE enable=false AND LOWER(email)=LOWER(:email) AND password=:password";
sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("email", email),
new NpgsqlParameter("password", password)
};
ds = NpgsqlHelper.ExecuteDataset(conn, CommandType.Text, sqlStmt, sqlParams);
DataRow dr = ds.Tables[0].Rows[0];
_account = new B2dAccount()
{
XID = dr.ToInt64("xid"),
UUID = dr.ToStringEx("user_uuid"),
EMAIL = dr.ToStringEx("email"),
NAME = dr.ToStringEx("name"),
ENABLE = dr.ToBoolean("enable"),
};
}
}
conn.Close();
}
catch (Exception ex)
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
return(_account);
}
public static List <B2dCompany> GetCompanies(string filter, int skip, int size, string sorting)
{
try
{
List <B2dCompany> companies = new List <B2dCompany>();
string sqlStmt = @"SELECT * FROM b2b.b2d_company
WHERE 1=1 {FILTER}
{SORTING}
LIMIT :Size OFFSET :Skip";
sqlStmt = sqlStmt.Replace("{FILTER}", !string.IsNullOrEmpty(filter) ? filter : string.Empty);
sqlStmt = sqlStmt.Replace("{SORTING}", !string.IsNullOrEmpty(sorting) ? "ORDER BY " + sorting : string.Empty);
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("Size", size),
new NpgsqlParameter("Skip", skip)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
foreach (DataRow dr in ds.Tables[0].Rows)
{
companies.Add(new B2dCompany()
{
XID = dr.ToInt64("xid"),
STATUS = dr.ToStringEx("status"),
COMP_COOP_MODE = dr.ToStringEx("comp_coop_mode"),
PAYMENT_TYPE = dr.ToStringEx("payment_type"),
MANAGER_ACCOUNT_XID = dr.ToInt64("manager_account_xid"),
PARENT_COMP_XID = dr.ToInt64("parent_comp_xid"),
COMP_NAME = dr.ToStringEx("comp_name"),
COMP_URL = dr.ToStringEx("comp_url"),
COMP_LICENSE = dr.ToStringEx("comp_license"),
COMP_LICENSE_2 = dr.ToStringEx("comp_license_2"),
COMP_LOCALE = dr.ToStringEx("comp_locale"),
COMP_CURRENCY = dr.ToStringEx("comp_currency"),
COMP_INVOICE = dr.ToStringEx("comp_invoice"),
COMP_COUNTRY = dr.ToStringEx("comp_country"),
COMP_TEL_COUNTRY_CODE = dr.ToStringEx("comp_tel_country_code"),
COMP_TEL = dr.ToStringEx("comp_tel"),
COMP_ADDRESS = dr.ToStringEx("comp_address"),
CHARGE_MAN_FIRST = dr.ToStringEx("charge_man_first"),
CHARGE_MAN_LAST = dr.ToStringEx("charge_man_last"),
CREDITCARD_NO = dr.ToStringEx("creditcard_no"),
CREDITCARD_VALID = dr.ToStringEx("creditcard_valid"),
CREDITCARD_CVC = dr.ToStringEx("creditcard_cvc"),
CONTACT_USER = dr.ToStringEx("contact_user"),
CONTACT_USER_EMAIL = dr.ToStringEx("contact_user_email"),
FINANCE_USER = dr.ToStringEx("finance_user"),
SALES_USER = dr.ToStringEx("sales_user")
});
}
return(companies);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}