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; } }