/// <summary>
/// 判断销售员编码是否存在
/// </summary>
/// <param name="ctx"></param>
/// <param name="sellerNo"></param>
/// <returns></returns>
public static string GetSellerNo(Context ctx, string sellerNo)
{
string sql = string.Empty;
string salerNo = string.Empty;
if (!string.IsNullOrWhiteSpace(sellerNo))
{
sql = string.Format(@"/*dialect*/ select FNUMBER from V_BD_SALESMAN where FNUMBER = '{0}' and FFORBIDSTATUS = 'A'", sellerNo);
salerNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER"));
if (!string.IsNullOrWhiteSpace(salerNo))
{
return(salerNo);
}
else
{
//sql = string.Format(@"/*dialect*/ select FNUMBER from T_HR_EMPINFO where FEMAIL = '{0}'", DataBaseConst.SalerEmail);
//salerNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER"));
return("1177");
}
}
else
{
return("NA");
}
return(salerNo);
}
/// <summary>
/// 获取B2B客户DropShipOrderPrefix订单前缀
/// </summary>
/// <param name="ctx"></param>
/// <returns></returns>
public static string GetAUB2BDropShipOrderPrefix(Context ctx)
{
string sql = string.Format(@"/*dialect*/ select F_HS_DROPSHIPORDERPREFIX from T_BD_CUSTOMER
where FNUMBER = '{0}'
and FUSEORGID = 100035" , DataBaseConst.Param_AUB2B_customerID);
return(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(ctx, sql, "F_HS_DROPSHIPORDERPREFIX")));
}
/// <summary>
/// 根据银行账号ID获取银行账号编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="bankAccountId"></param>
/// <returns></returns>
public static string GetBankAccountNo(Context ctx, string bankAccountId)
{
if (!string.IsNullOrWhiteSpace(bankAccountId))
{
string sql = string.Format(@"/*dialect*/ select FNUMBER from T_CN_BANKACNT where FBANKACNTID = {0} and FUSEORGID = {1}", bankAccountId, ORGID);
return(string.IsNullOrWhiteSpace(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER"))) ? "" : JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER")));
}
return("");
}
public static int GetSettleCurrId(Context ctx, string settleCurrNo)
{
if (!string.IsNullOrWhiteSpace(settleCurrNo))
{
string sSettleCurrId = string.Format(@"/*dialect*/ select FCURRENCYID from T_BD_CURRENCY where FNUMBER = '{0}' ", settleCurrNo);
return(Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sSettleCurrId, "FCURRENCYID"))));
}
return(0);
}
/// <summary>
/// 根据销售订单编码获取销售订单内码
/// </summary>
/// <param name="ctx"></param>
/// <param name="saleOrderNo"></param>
/// <returns></returns>
public static int GetSaleOrderId(Context ctx, string saleOrderNo)
{
if (!string.IsNullOrWhiteSpace(saleOrderNo))
{
string sql = string.Format(@"/*dialect*/ select FID from T_SAL_ORDER where FBILLNO = '{0}' and FDOCUMENTSTATUS != 'D' and FDOCUMENTSTATUS != 'B'", saleOrderNo);
return(Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FID"))));
}
return(0);
}
/// <summary>
/// 根据组织编码获取组织ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="orgNo"></param>
/// <returns></returns>
public static int GetOrgId(Context ctx, string orgNo)
{
if (!string.IsNullOrWhiteSpace(orgNo))
{
string sOrgNo = string.Format(@"/*dialect*/ select FORGID from T_ORG_ORGANIZATIONS where FNUMBER = '{0}'", orgNo);
return(Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sOrgNo, "FORGID"))));
}
return(0);
}
/// <summary>
/// 根据付款方式获取银行账号编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="paymentMethod"></param>
/// <returns></returns>
public static string GetBankAccountId(Context ctx, string paymentMethod)
{
if (!string.IsNullOrWhiteSpace(paymentMethod))
{
string sql = string.Format(@"/*dialect*/ select F_HS_BANKACCOUNT from T_BD_SETTLETYPE where FNUMBER = '{0}'", paymentMethod, ORGID);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "F_HS_BANKACCOUNT")));
}
return(null);
}
/// <summary>
/// 根据销售员编码获取销售员ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="sellerNo"></param>
/// <returns></returns>
public static int GetSellerId(Context ctx, string sellerNo)
{
if (!string.IsNullOrWhiteSpace(sellerNo))
{
string sql = string.Format(@"/*dialect*/ select FID from V_BD_SALESMAN where FNUMBER='{0}'", sellerNo);
int sellerId = Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FID")));
return(sellerId);
}
return(0);
}
/// <summary>
/// 根据客户编码获取客户ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="obj"></param>
/// <param name="custNo"></param>
/// <returns></returns>
public static int GetCustomerId(Context ctx, string custNo, int OrgId)
{
int customerId = default(int);
if (!string.IsNullOrWhiteSpace(custNo))
{
string sCustNo = string.Format(@"/*dialect*/ select FCUSTID from T_BD_CUSTOMER where FNUMBER = '{0}' and FUSEORGID = {1}", custNo, OrgId);
customerId = Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sCustNo, "FCUSTID")));
}
return(customerId);
}
/// <summary>
/// 根据国家编码获取国家ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="countryNo"></param>
/// <returns></returns>
public static string GetCountryId(Context ctx, string countryNo)
{
string countryId = default(string);
if (!string.IsNullOrWhiteSpace(countryNo))
{
string sCountryNo = string.Format(@"/*dialect*/ select FCountry from VW_BAS_ASSISTANTDATA_CountryName where FNUMBER = '{0}'", countryNo);
countryId = JsonUtils.ConvertObjectToString(GetObject(ctx, sCountryNo, "FCountry"));
}
return(countryId);
}
/// <summary>
/// 根据客户ID获取客户编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string GetCustomerNo(Context ctx, DynamicObject obj, string fieldName)
{
string customerNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string custId = GetFieldValue(obj, fieldName);
string sCustNo = string.Format(@"/*dialect*/ select FNUMBER from T_BD_CUSTOMER where FCUSTID = {0}", Convert.ToInt32(custId));
customerNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sCustNo, "FNumber"));
}
return(customerNo);
}
/// <summary>
/// 根据品牌编码获取品牌名称
/// </summary>
/// <param name="ctx"></param>
/// <param name="brandNo"></param>
/// <returns></returns>
public static string GetBrandName(Context ctx, string brandNo)
{
if (!string.IsNullOrWhiteSpace(brandNo))
{
string sql = string.Format(@"/*dialect*/ select b.FNAME from HS_T_Brand a
inner join HS_T_BRAND_L b
on a.FID = b.FID
where a.FNUMBER = '{0}'" , brandNo);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNAME")));
}
return(null);
}
/// <summary>
/// 根据客户分组编码获取相应客户分组ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="custGroupNo"></param>
/// <returns></returns>
public static string GetCustGroupId(Context ctx, string custGroupNo)
{
if (!string.IsNullOrWhiteSpace(custGroupNo))
{
string scustGroupNo = string.Format(@"/*dialect*/ select distinct a.FCUSTTYPEID from T_BD_CUSTOMER a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.FCUSTTYPEID=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where k.FNUMBER = '{0}'", custGroupNo);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, scustGroupNo, "FCUSTTYPEID")));
}
return(null);
}
/// <summary>
/// 根据结算方式ID获取结算方式编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static string GetSettleTypeNo(Context ctx, DynamicObject obj, string fieldName)
{
string SettleTypeNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string settleTypeId = GetFieldValue(obj, fieldName);
string sSettleNo = string.Format(@"/*dialect*/ select FNUMBER from T_BD_SETTLETYPE where FID = {0}", Convert.ToInt32(settleTypeId));
SettleTypeNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sSettleNo, "FNUMBER"));
}
return(SettleTypeNo);
}
/// <summary>
/// 根据国家ID获取国家编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static string GetCountryNo(Context ctx, DynamicObject obj, string fieldName)
{
string countryNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string countryId = GetFieldValue(obj, fieldName);
string sSettleNo = string.Format(@"/*dialect*/ select FNUMBER from VW_BAS_ASSISTANTDATA_CountryName where FCountry = '{0}'", countryId);
countryNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sSettleNo, "FNUMBER"));
}
return(countryNo);
}
/// <summary>
/// 根据销售员ID获取销售员编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string GetSellerNo(Context ctx, DynamicObject obj, string fieldName)
{
string sellerNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string sellerId = GetFieldValue(obj, fieldName);
string sSellerNo = string.Format(@"/*dialect*/ select FNUMBER from V_BD_SALESMAN where FID = {0}", Convert.ToInt32(sellerId));
sellerNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sSellerNo, "FNumber"));
}
return(sellerNo);
}
/// <summary>
/// 根据物料ID获取物料编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static string GetMaterialNo(Context ctx, DynamicObject obj, string fieldName)
{
string materialNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string matId = GetFieldValue(obj, fieldName);
string sMatNo = string.Format(@"/*dialect*/ select FNumber from T_BD_MATERIAL where FMATERIALID = {0} ", Convert.ToInt32(matId));
materialNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sMatNo, "FNumber"));
}
return(materialNo);
}
/// <summary>
/// 根据物流方式ID获取物流方式的编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="shipMethodId"></param>
/// <returns></returns>
public static string GetShipMethodNo(Context ctx, int shipMethodId)
{
if (shipMethodId != 0)
{
string sql = string.Format(@"/*dialect*/ select distinct a.FNumber from T_HS_ShipMethods a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.F_HS_Channel=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where a.FID = {0}", shipMethodId);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNumber")));
}
return(null);
}
/// <summary>
/// 根据物流方式编码获取物流方式的ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="shipMethodNo"></param>
/// <returns></returns>
public static int GetShipMethodId(Context ctx, string shipMethodNo)
{
if (!string.IsNullOrWhiteSpace(shipMethodNo))
{
string sql = string.Format(@"/*dialect*/ select distinct a.FID from T_HS_ShipMethods a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.F_HS_Channel=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where a.FNumber = '{0}'", shipMethodNo);
return(Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FID"))));
}
return(0);
}
/// <summary>
/// 根据仓库ID获取仓库编码
/// </summary>
/// <param name="obj"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static string GetStockNo(Context ctx, DynamicObject obj, string fieldName)
{
string stockNo = default(string);
if (!string.IsNullOrWhiteSpace(fieldName))
{
string stockId = GetFieldValue(obj, fieldName);
string sStockNo = string.Format(@"/*dialect*/ select FNUMBER FROM T_BD_STOCK where FSTOCKID = {0}", Convert.ToInt32(stockId));
stockNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sStockNo, "FNUMBER"));
}
return(stockNo);
}
/// <summary>
/// 根据物流渠道编码获取相应的物流渠道ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="logisticsChannelNo"></param>
/// <returns></returns>
public static string GetLogisticsChannelId(Context ctx, string logisticsChannelNo)
{
if (!string.IsNullOrWhiteSpace(logisticsChannelNo))
{
string sql = string.Format(@"/*dialect*/ select distinct a.F_HS_Channel from T_HS_ShipMethods a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.F_HS_Channel=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where k.fnumber = '{0}'", logisticsChannelNo);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "fnumber")));
}
return(null);
}
/// <summary>
/// 根据付款方式编码获取付款方式ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="paymentMethodNo"></param>
/// <returns></returns>
public static string GetPaymentMethodId(Context ctx, string paymentMethodNo)
{
if (!string.IsNullOrWhiteSpace(paymentMethodNo))
{
string sql = string.Format(@"/*dialect*/ select distinct a.F_HS_PaymentModeNew from T_SAL_ORDER a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.F_HS_PaymentModeNew=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where k.fnumber = '{0}'" , paymentMethodNo);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "F_HS_PaymentModeNew")));
}
return(null);
}
/// <summary>
/// 获取信用余额
/// </summary>
/// <param name="ctx"></param>
/// <param name="custNo"></param>
/// <returns></returns>
public static decimal GetCustCreditLine(Context ctx, string custNo, string fieldName)
{
if (!string.IsNullOrWhiteSpace(custNo) && !string.IsNullOrWhiteSpace(fieldName))
{
string sql = string.Format(@"/*dialect*/ select {0} from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{1}' and b.FNUMBER = '100.01'", fieldName, custNo);
decimal credit = Convert.ToDecimal(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(ctx, sql, fieldName)));
return(Math.Round(credit, 2));
}
return(0);
}
/// <summary>
/// 获取客户结余
/// </summary>
/// <param name="ctx"></param>
/// <param name="custNo"></param>
/// <param name="orgNo"></param>
/// <returns></returns>
public static decimal GetCustBalance(Context ctx, string custNo, string orgNo)
{
if (!string.IsNullOrWhiteSpace(custNo) && !string.IsNullOrWhiteSpace(orgNo))
{
string sql = string.Format(@"/*dialect*/ select {0} from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{1}' and b.FNUMBER = '{2}'", orgNo.CompareTo("100.03") == 0 ? "F_HS_CNYBalance" : "F_HS_USDBalance", custNo, orgNo);
decimal balance = Convert.ToDecimal(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(ctx, sql, string.Format("{0}", orgNo.CompareTo("100.03") == 0 ? "F_HS_CNYBalance" : "F_HS_USDBalance"))));
return(Math.Round(balance, 2));
}
return(0);
}
/// <summary>
/// 根据价目表编码获取相应的ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="custNo"></param>
/// <param name="priceListNo"></param>
/// <returns></returns>
public static string GetPriceListId(Context ctx, string custNo, string priceListNo)
{
if (!string.IsNullOrWhiteSpace(custNo) && !string.IsNullOrWhiteSpace(priceListNo))
{
string sql = string.Format(@"/*dialect*/ select b.FID from T_BD_CUSTOMER a
inner join T_SAL_PRICELIST b
on a.FPRICELISTID = b.FID
where a.FNUMBER = '{0}' and b.FNUMBER = '{1}' and b.FSALEORGID = 100035", custNo, priceListNo);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FID")));
}
return(null);
}
/// <summary>
/// 根据员工编码获取部门ID
/// </summary>
/// <param name="ctx"></param>
/// <param name="empNo"></param>
/// <returns></returns>
public static int GetDeptId(Context ctx, string empNo)
{
int deptNo = 0;
if (!string.IsNullOrWhiteSpace(empNo))
{
string sDeptNo = string.Format(@"/*dialect*/ select b.FDEPTID from T_BD_STAFFTEMP a
inner join T_BD_DEPARTMENT b on a.FDEPTID=b.FDEPTID
inner join T_BD_STAFF c on a.FSTAFFID=c.FSTAFFID
where c.FNUMBER='{0}'", empNo);
deptNo = Convert.ToInt32(JsonUtils.ConvertObjectToString(GetObject(ctx, sDeptNo, "FDEPTID")));
}
return(deptNo);
}
/// <summary>
/// 根据物料分组获取SKU分组码
/// </summary>
/// <param name="ctx"></param>
/// <param name="materialGroup"></param>
/// <returns></returns>
public static string GetSKUCode(Context ctx, DynamicObject obj, string fieldName)
{
string materialGroup = GetFieldValue(obj, fieldName);
if (!string.IsNullOrWhiteSpace(materialGroup))
{
string sql = string.Format(@"/*dialect*/ select b.F_HS_SKUGroupCode from T_BD_MATERIAL a
inner join T_BD_MATERIALGROUP b
on a.FMaterialGroup = b.FID
where b.FNUMBER = '{0}'" , materialGroup);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "F_HS_SKUGroupCode")));
}
return(null);
}
/// <summary>
/// 根据物料编码获取仓库编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="materialNo"></param>
/// <returns></returns>
public static string GetStockNo(Context ctx, string materialNo)
{
string stockNo = null;
if (!string.IsNullOrWhiteSpace(materialNo))
{
string sql = string.Format(@"/*dialect*/ select c.FNUMBER from T_BD_MATERIAL a
inner join T_BD_MATERIALSTOCK b on a.FMATERIALID=b.FMATERIALID
inner join T_BD_STOCK c on b.FSTOCKID=c.FSTOCKID
where a.FNUMBER='{0}' and a.FUSEORGID={1}", materialNo, ORGID);
stockNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER"));
}
return(stockNo);
}
/// <summary>
/// 根据付款方式ID获取付款方式编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="obj"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static string GetPaymentNo(Context ctx, DynamicObject obj, string fieldName)
{
if (!string.IsNullOrWhiteSpace(fieldName))
{
string paymentId = GetFieldValue(obj, fieldName);
string sql = string.Format(@"/*dialect*/ select distinct k.fnumber from T_SAL_ORDER a
inner join T_BAS_ASSISTANTDATAENTRY_L j ON a.F_HS_PaymentModeNew=j.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where a.F_HS_PaymentModeNew = '{0}'", paymentId);
return(JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "fnumber")));
}
return(null);
}
/// <summary>
/// 根据付款方式编码获取结算方式编码
/// </summary>
/// <param name="ctx"></param>
/// <param name="paymentMethodId"></param>
/// <returns></returns>
public static string GetSettleTypeNo(Context ctx, string paymentMethodId)
{
if (!string.IsNullOrWhiteSpace(paymentMethodId))
{
string sql = string.Format(@"/*dialect*/ select FNUMBER from T_BD_SETTLETYPE a
inner join T_BD_SETTLETYPE_L b on a.FID = b.FID
where F_HS_WebPaymentMethod = '{0}' and b.FLOCALEID = 2052", paymentMethodId);
string settleTypeNo = JsonUtils.ConvertObjectToString(GetObject(ctx, sql, "FNUMBER"));
return(string.IsNullOrWhiteSpace(settleTypeNo) ? "" : settleTypeNo);
}
return("");
}
