public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result)
{
List <AbsDataInfo> reFunds = null;
AbsDataInfo reFund = null;
result = new HttpResponseResult();
result.Success = true;
if (objects != null && objects.Count > 0)
{
reFunds = new List <AbsDataInfo>();
foreach (var obj in objects)
{
if (obj != null)
{
DynamicObject rMethod = obj["F_HS_RefundMethod"] as DynamicObject;
string reFundMethod = SQLUtils.GetFieldValue(rMethod, "FNumber");
string document = SQLUtils.GetFieldValue(obj, "DOCUMENTSTATUS");
if (!string.IsNullOrWhiteSpace(reFundMethod) && !string.IsNullOrWhiteSpace(document))
{
if (reFundMethod.CompareTo("TKDKHYE") == 0 && document.CompareTo("C") == 0)
{
reFund = new AbsDataInfo();
reFund.FDate = SQLUtils.GetFieldValue(obj, "DATE");
reFund.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo");
DynamicObject cust = obj["F_HS_B2CCustId"] as DynamicObject;
reFund.F_HS_B2CCustId = SQLUtils.GetFieldValue(cust, "Number");
DynamicObject curr = obj["SETTLECUR"] as DynamicObject;
reFund.FSettleCurrId = SQLUtils.GetFieldValue(curr, "Number");
reFund.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_RateToUSA"));
reFund.F_HS_BalanceRechargeUSD = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "REALREFUNDAMOUNTFOR"));
DynamicObject org = obj["SETTLEORGID"] as DynamicObject;
reFund.FSaleOrgId = SQLUtils.GetFieldValue(org, "Number");
DynamicObject dept = obj["SALEDEPTID"] as DynamicObject;
reFund.FSaleDeptId = SQLUtils.GetFieldValue(dept, "Number");
DynamicObject seller = obj["SALEERID"] as DynamicObject;
reFund.FSalerId = SQLUtils.GetFieldValue(seller, "Number");
reFund.F_HS_BalanceReceivableNo = SQLUtils.GetFieldValue(obj, "F_HS_BalanceReceivableNo");
DynamicObjectCollection entry = obj["REFUNDBILLENTRY"] as DynamicObjectCollection;
reFund.FSettleTypeId = GetSettleType(entry);
reFunds.Add(reFund);
}
}
}
}
}
return(reFunds);
}
private IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, DynamicObjectCollection coll)
{
AbsDataInfo data = null;
List <AbsDataInfo> datas = null;
if (coll != null && coll.Count > 0)
{
datas = new List <AbsDataInfo>();
foreach (var item in coll)
{
if (item != null)
{
data = new AbsDataInfo(SynchroDataType.SaleOrder);
data.SrcNo = SQLUtils.GetFieldValue(item, "FBillNo");
data.FBillNo = data.SrcNo;
data.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(item, "FDate")));
data.F_HS_B2CCustId = SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID");
data.F_HS_BalanceRechargeUSD = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_USDBalancePayments"));
data.FSettleCurrId = SQLUtils.GetFieldValue(item, "FSettleCurrId");
data.FSettleTypeId = SQLUtils.GetFieldValue(item, "F_HS_PaymentModeNew");
data.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_RateToUSA"));
data.FSaleOrgId = SQLUtils.GetFieldValue(item, "UseOrgId");
data.F_HS_SaleOrderSource = SQLUtils.GetFieldValue(item, "F_HS_SaleOrderSource");
data.FCancelStatus = SQLUtils.GetFieldValue(item, "FCancelStatus");
data.FBusinessTime = SQLUtils.GetFieldValue(item, "FCancelDate");
data.F_HS_BalanceDeducted = SQLUtils.GetFieldValue(item, "F_HS_BalanceDeducted").Equals("1") ? true : false;
datas.Add(data);
}
}
}
return(datas);
}
/// <summary>
/// 点击“CEO特批已到款”后设置付款状态
/// </summary>
/// <param name="ctx"></param>
private void SetBillStatus(Context ctx, AbsSynchroDataInfo info)
{
if (info != null)
{
AbsDataInfo order = info as AbsDataInfo;
this.View.Model.SetValue("F_HS_BalanceDeducted", order.F_HS_BalanceDeducted);
if (order.F_HS_CheckBox)
{
this.View.Model.SetValue("F_HS_CheckBox", true);
this.View.InvokeFormOperation("Save");
if (this.DataType != SynchroDataType.DropShippingSalOrder)
{
this.View.Model.SetValue("F_HS_PaymentStatus", "3");
bool success = this.View.InvokeFormOperation("Save");
if (success)
{
this.View.ShowMessage("付款状态已设置为已到款！", MessageBoxType.Notice);
}
else
{
this.View.ShowMessage("付款状态设置失败！", MessageBoxType.Notice);
}
}
else
{
if (!order.F_HS_BalanceDeducted)
{
if (order.FDocumentStatus.Equals(BillDocumentStatus.Audit))
{
if (!order.F_HS_PaymentStatus.Equals("3"))
{
if (order.FBillAmount <= order.FCustBalanceAmount)
{
if (!string.IsNullOrWhiteSpace(order.FDocumentStatus))
{
if (order.FDocumentStatus.Equals(BillDocumentStatus.Audit))
{
this.View.InvokeFormOperation("UnAudit");
this.View.InvokeFormOperation("Save");
this.View.InvokeFormOperation("Submit");
this.View.InvokeFormOperation("Audit");
}
}
}
}
}
}
}
}
else
{
this.View.Model.SetValue("F_HS_CheckBox", false);
this.View.ShowErrMessage("该订单不是CEO特批已到款", "错误提示", MessageBoxType.Error);
}
}
}
private void SetPayment(AbsDataInfo info)
{
if (info != null)
{
if (info.FBillAmount > info.FCustBalance)
{
info.F_HS_BalanceRechargeUSD = -info.FCustBalance;
info.F_HS_CreditLineRechargeUSD = -(info.FBillAmount + info.F_HS_BalanceRechargeUSD);
}
else
{
info.F_HS_BalanceRechargeUSD = -info.FBillAmount;
info.F_HS_CreditLineRechargeUSD = 0;
}
}
}
/// <summary>
/// 获取销售订单相关信息
/// </summary>
/// <param name="ctx"></param>
/// <returns></returns>
private AbsSynchroDataInfo GetK3Data(Context ctx)
{
AbsDataInfo order = new AbsDataInfo();
order.F_HS_CheckBox = Convert.ToBoolean(this.View.Model.GetValue("F_HS_CheckBox"));
DynamicObject source = this.View.Model.GetValue("F_HS_SaleOrderSource") as DynamicObject;
string sType = SQLUtils.GetFieldValue(source, "FNumber");
order.F_HS_SaleOrderSource = sType;
this.SaleOrderSource = sType;
if (this.DataType.Equals(SynchroDataType.DropShippingSalOrder))
{
order.FDocumentStatus = JsonUtils.ConvertObjectToString(this.View.Model.GetValue("FDocumentStatus"));
order.F_HS_PaymentStatus = JsonUtils.ConvertObjectToString(this.View.Model.GetValue("F_HS_PaymentStatus"));
order.FCancelStatus = JsonUtils.ConvertObjectToString(this.View.Model.GetValue("FCancelStatus"));
order.F_HS_BalanceDeducted = Convert.ToBoolean(this.View.Model.GetValue("F_HS_BalanceDeducted"));
DynamicObject cust = this.View.Model.GetValue("F_HS_B2CCustId") as DynamicObject;
string custNo = SQLUtils.GetFieldValue(cust, "Number");
decimal rateToUSA = Convert.ToDecimal(this.View.Model.GetValue("F_HS_RateToUSA"));
order.FBillAmount = Convert.ToDecimal(this.View.Model.GetValue("FBillAmount")) / rateToUSA;
decimal useUsdBalance = Math.Round(Convert.ToDecimal(this.View.Model.GetValue("F_HS_USDBalancePayments")), 2) / rateToUSA;
order.F_HS_BalanceRechargeUSD = Math.Round(Convert.ToDecimal(this.View.Model.GetValue("F_HS_USDBalancePayments")), 2) / rateToUSA;
decimal useCredit = Math.Round(Convert.ToDecimal(this.View.Model.GetValue("F_HS_USDBalancePayments")), 2) / rateToUSA;
order.F_HS_CreditLineRechargeUSD = Math.Round(Convert.ToDecimal(this.View.Model.GetValue("F_HS_USDBalancePayments")), 2) / rateToUSA;
decimal custBalance = Math.Round(LogHelper.GetCustBalance(ctx, custNo, "100.01"));
decimal custCredit = Math.Round(LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_SurplusCreditUSD"));
order.FCustBalanceAmount = custBalance + custCredit;
}
return(order);
}
public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result)
{
List <AbsDataInfo> reFunds = null;
AbsDataInfo reFund = null;
result = new HttpResponseResult();
result.Success = true;
if (objects != null && objects.Count > 0)
{
reFunds = new List <AbsDataInfo>();
foreach (var obj in objects)
{
if (obj != null)
{
string reFundMethod = SQLUtils.GetReFundMethod(ctx, obj, "F_HS_RefundMethod_Id");
bool isSyn = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_YNSync"));
DynamicObject org = obj["SALEORGID"] as DynamicObject;
string orgNo = SQLUtils.GetFieldValue(org, "Number");
string custNo = SQLUtils.GetCustomerNo(ctx, obj, "F_HS_B2CCUSTID_Id");
if (!isSyn)
{
if (!string.IsNullOrWhiteSpace(reFundMethod))
{
List <ReFundBillEntry> entrys = GetReFundEntry(ctx, obj);
if (reFundMethod.CompareTo("TKDKHYE") == 0 || reFundMethod.CompareTo("TJF") == 0)
{
if (entrys != null && entrys.Count > 0)
{
foreach (var entry in entrys)
{
if (entry != null)
{
reFund = new AbsDataInfo(this.DataType);
reFund.FSaleOrgId = orgNo;
reFund.SrcNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryId;
reFund.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryId;
reFund.FEntryId = entry.FEntryId;
reFund.F_HS_RefundMethod = reFundMethod;
reFund.F_HS_RefundType = SQLUtils.GetReFundType(ctx, obj, "F_HS_RefundType_Id");
reFund.F_HS_B2CCustId = custNo;
reFund.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "DATE")));
reFund.FSettleCurrId = SQLUtils.GetSettleCurrNo(ctx, obj, "CURRENCYID_Id");
reFund.F_HS_RateToUSA = GetExchangeRate(ctx, reFund.FSettleCurrId);
reFund.FSettleTypeId = entry.FSettleTypeId;
reFund.FBusinessTime = SQLUtils.GetFieldValue(obj, "ApproveDate");
if (reFund.F_HS_RefundMethod.CompareTo("TJF") == 0)
{
reFund.F_HS_BalanceRechargeUSD = 0;
}
else if (reFund.F_HS_RefundMethod.CompareTo("TKDKHYE") == 0)
{
reFund.F_HS_BalanceRechargeUSD = entry.F_HS_BalanceRechargeUSD;
reFund.F_HS_CreditLineRechargeUSD = entry.F_HS_CreditLineRechargeUSD;
if (reFund.F_HS_CreditLineRechargeUSD < 0)
{
reFund.F_HS_BalanceRechargeUSD = entry.FRealReFundAmountFor_D / reFund.F_HS_RateToUSA;
}
}
reFunds.Add(reFund);
}
}
}
}
if (reFundMethod.CompareTo("JYE01") == 0)
{
decimal balance = LogHelper.GetCustBalance(ctx, custNo, orgNo);
decimal amount = 0;
int count = 0;
if (entrys != null && entrys.Count > 0)
{
foreach (var entry in entrys)
{
count++;
amount += entry.FRealReFundAmountFor_D;
if (count < entrys.Count)
{
continue;
}
if (balance > 0 && amount <= balance)
{
foreach (var en in entrys)
{
if (entry != null)
{
reFund = new AbsDataInfo(this.DataType);
reFund.FSaleOrgId = orgNo;
reFund.SrcNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + en.FEntryId;
reFund.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + en.FEntryId;
reFund.FEntryId = en.FEntryId;
reFund.F_HS_RefundMethod = reFundMethod;
reFund.F_HS_RefundType = SQLUtils.GetReFundType(ctx, obj, "F_HS_RefundType_Id");
reFund.F_HS_B2CCustId = custNo;
reFund.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "DATE")));
reFund.FSettleCurrId = SQLUtils.GetSettleCurrNo(ctx, obj, "CURRENCYID_Id");
reFund.F_HS_RateToUSA = GetExchangeRate(ctx, reFund.FSettleCurrId);
reFund.FSettleTypeId = en.FSettleTypeId;
reFund.F_HS_BalanceRechargeUSD = -en.FRealReFundAmountFor_D / GetExchangeRate(ctx, "USD");
reFunds.Add(reFund);
}
}
}
}
}
}
}
}
else
{
result.Success = false;
result.Message = "收款退款单已同步" + Environment.NewLine;
LogUtils.WriteSynchroLog(ctx, SynchroDataType.ReFundBill, result.Message);
}
}
}
}
return(reFunds);
}
public override string GetExecuteUpdateSql(Context ctx, List <AbsSynchroDataInfo> datas)
{
string sql = string.Empty;
string execSql = string.Empty;
int i = 0;
try
{
if (datas != null && datas.Count > 0)
{
foreach (var data in datas)
{
if (data != null)
{
if (data != null)
{
AbsDataInfo reFund = data as AbsDataInfo;
if (reFund != null)
{
if (reFund.F_HS_RateToUSA > 0)
{
string billNo = reFund.FBillNo.Contains("_") ? reFund.FBillNo.Split('_')[0] : reFund.FBillNo;
sql += string.Format(@"/*dialect*/ update a set a.F_HS_YNSync ='{0}',a.F_HS_RateToUSA = {1}
from T_AR_REFUNDBILL a
inner join T_AR_REFUNDBILLENTRY b on a.FID = b.FID
inner join T_BD_CUSTOMER c on a.F_HS_B2CCustId = c.FCUSTID
inner join T_ORG_ORGANIZATIONS d on a.FSALEORGID = d.FORGID
where a.F_HS_YNSync = '{2}'and c.FNumber = '{3}'
and a.FBillNo = '{4}'
and d.FNUMBER = '{5}'", "1", reFund.F_HS_RateToUSA, "0", reFund.F_HS_B2CCustId, billNo, reFund.FSaleOrgId) + Environment.NewLine;
sql += string.Format(@"/*dialect*/ update a set a.F_HS_RefundTurnUSD = {0},F_HS_BalanceRechargeUSD = {4},F_HS_CreditLineRechargeUSD = {5}
from T_AR_REFUNDBILLENTRY a
inner join T_AR_REFUNDBILL b on b.FID = a.FID
inner join T_ORG_ORGANIZATIONS c on b.FSALEORGID = c.FORGID
where b.FBILLNO = '{1}'
and c.FNUMBER = '{2}'
and a.FENTRYID = {3}" , reFund.FRealAmountFor_USD, billNo, reFund.FSaleOrgId, reFund.FEntryId
, reFund.F_HS_BalanceRechargeUSD, reFund.F_HS_CreditLineRechargeUSD) + Environment.NewLine;
sql += string.Format(@"/*dialect*/ update a set a.F_HS_USDBalance = a.F_HS_USDBalance + {0}{3}{4}
from T_BD_CUSTOMER a
left join T_AR_REFUNDBILL b on a.FCUSTID = b.F_HS_B2CCustId
left join T_AR_REFUNDBILLENTRY c on b.FID = c.FID
inner join T_ORG_ORGANIZATIONS d on a.FUSEORGID = d.FORGID
where a.FNUMBER = '{1}'
and d.FNUMBER = '{2}'", reFund.F_HS_BalanceRechargeUSD, reFund.F_HS_B2CCustId, reFund.FSaleOrgId
, reFund.FSaleOrgId.CompareTo("100.03") == 0 && reFund.FSettleCurrId.CompareTo("CNY") == 0 ? string.Format(@",a.F_HS_CNYBalance = a.F_HS_CNYBalance + {0}"
, reFund.FRealAmountFor) : "", string.Format(@",a.F_HS_SurplusCreditUSD = a.F_HS_SurplusCreditUSD + {0}", reFund.F_HS_CreditLineRechargeUSD)) + System.Environment.NewLine;
sql = "'" + SQLUtils.DealQuotes(sql) + "'";
execSql += string.Format(@"EXEC(" + sql + ")") + Environment.NewLine;
execSql += string.Format(@"declare @balance{2} decimal(10,2)
declare @cnybalance{4} decimal(10,2)
declare @creditbalance{11} decimal(10,2)
set @balance{3} = ( select F_HS_USDBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{0}' and b.FNUMBER = '{1}'
)
set @cnybalance{5} = ( select F_HS_CNYBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{6}' and b.FNUMBER = '{7}'
)
set @creditbalance{8} = ( select F_HS_SurplusCreditUSD from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{9}' and b.FNUMBER = '{10}'
)
", reFund.F_HS_B2CCustId, reFund.FSaleOrgId, i, i, i, i, reFund.F_HS_B2CCustId, reFund.FSaleOrgId, i, reFund.F_HS_B2CCustId, reFund.FSaleOrgId, i) + System.Environment.NewLine;
if (reFund.F_HS_BalanceRechargeUSD > 0)
{
execSql += string.Format(@"/*dialect*/ insert into HS_T_customerBalance(F_HS_TradeType,F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,changedCause
,balanceAmount,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,F_HS_CNYBalance,updateTime
,updateUser,FBillNo,fentryID,needfreezed,remark) values ('{18}','{15}','{0}',{1},'{2}','{3}',@balance{13}*{17},{4},{5}
,{6},@balance{14}, @cnybalance{16},'{7}',{8},'{9}',{10},{11},'{12}') ", SQLUtils.GetCustomerId(ctx, reFund.F_HS_B2CCustId, 1), reFund.F_HS_BalanceRechargeUSD * reFund.F_HS_RateToUSA
, reFund.ChangedType, reFund.ChangedCause, reFund.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, reFund.FSettleCurrId)
, reFund.F_HS_BalanceRechargeUSD, DateTime.Now, ctx.UserId, billNo, reFund.FEntryId, reFund.NeedFreezed == false ? 0 : 1, reFund.Remark, i, i
, SQLUtils.GetOrgId(ctx, reFund.FSaleOrgId), i, reFund.F_HS_RateToUSA, "余额") + Environment.NewLine;
}
if (reFund.F_HS_CreditLineRechargeUSD > 0)
{
execSql += string.Format(@"/*dialect*/ insert into HS_T_customerBalance(F_HS_TradeType,F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,changedCause
,balanceAmount,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,F_HS_CNYBalance,updateTime
,updateUser,FBillNo,fentryID,needfreezed,remark) values ('{18}','{15}','{0}',{1},'{2}','{3}',@creditbalance{13}*{17},{4},{5}
,{6},@creditbalance{14}, @cnybalance{16},'{7}',{8},'{9}',{10},{11},'{12}') ", SQLUtils.GetCustomerId(ctx, reFund.F_HS_B2CCustId, 1), reFund.F_HS_CreditLineRechargeUSD * reFund.F_HS_RateToUSA
, reFund.ChangedType, reFund.ChangedCause, reFund.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, reFund.FSettleCurrId)
, reFund.F_HS_CreditLineRechargeUSD, DateTime.Now, ctx.UserId, billNo, reFund.FEntryId, reFund.NeedFreezed == false ? 0 : 1, reFund.Remark, i, i
, SQLUtils.GetOrgId(ctx, reFund.FSaleOrgId), i, reFund.F_HS_RateToUSA, "剩余信用额度") + Environment.NewLine;
}
sql = string.Empty;
i++;
}
else
{
LogUtils.WriteSynchroLog(ctx, this.DataType, "获取【" + reFund.FSettleCurrId + "币别】兑美元的实时汇率失败");
}
}
}
}
}
}
}
catch (Exception ex)
{
LogUtils.WriteSynchroLog(ctx, this.DataType, ex.Message + System.Environment.NewLine + ex.StackTrace);
}
return(execSql);
}
public override string GetExecuteUpdateSql(Context ctx, List <AbsSynchroDataInfo> datas)
{
string sql = string.Empty;
string execSql = string.Empty;
int i = 0;
try
{
if (datas != null && datas.Count > 0)
{
foreach (var data in datas)
{
if (data != null)
{
AbsDataInfo order = data as AbsDataInfo;
if (order != null)
{
if (order.F_HS_RateToUSA > 0)
{
//pending单作废成功
if (order.F_HS_SaleOrderSource.CompareTo("HCWebPendingOder") == 0 && order.FCancelStatus.CompareTo("B") == 0 &&
order.FRealAmountFor > 0 && !order.F_HS_BalanceDeducted)
{
execSql += string.Format(@"/*dialect*/ update a set a.F_HS_BalanceDeducted = '{0}'
from T_SAL_ORDER a
inner join T_BD_CUSTOMER b on a.FCUSTID = b.FCUSTID
inner join T_ORG_ORGANIZATIONS c on a.FSALEORGID = c.FORGID
where a.FBillNo = '{1}'
and c.FNUMBER = '{2}'", "1", order.FBillNo, order.FSaleOrgId) + System.Environment.NewLine;
}
else
{
if (order.F_HS_SaleOrderSource.CompareTo("DropShippingOrder") == 0)
{
sql += string.Format(@"/*dialect*/ update a set a.F_HS_BalanceDeducted = '{0}' ,a.F_HS_BalancePayments = {1},a.F_HS_USDBalancePayments = {2}
,a.F_HS_CreditLineUSDPayments = {3},a.F_HS_NeedPayAmount = 0,a.F_HS_PaymentStatus = '3'
from T_SAL_ORDER a
inner join T_BD_CUSTOMER b on a.FCUSTID = b.FCUSTID
inner join T_ORG_ORGANIZATIONS c on a.FSALEORGID = c.FORGID
where a.FBillNo = '{4}'
and b.FNUMBER = '{5}'
and c.FNUMBER = '{6}'", "1", -order.F_HS_BalanceRecharge, -order.F_HS_BalanceRechargeUSD, -order.F_HS_CreditLineRechargeUSD, order.FBillNo, order.F_HS_B2CCustId, "100.01");
sql += string.Format(@"/*dialect*/ update a set a.F_HS_USDBalance = a.F_HS_USDBalance + {0},a.F_HS_SurplusCreditUSD = F_HS_SurplusCreditUSD + {1}
from T_BD_CUSTOMER a
left join T_SAL_ORDER b on a.FCUSTID = b.FCUSTID
inner join T_ORG_ORGANIZATIONS c on a.FUSEORGID = c.FORGID
where a.FNUMBER = '{2}'
and c.FNUMBER = '{3}'", order.F_HS_BalanceRechargeUSD, order.F_HS_CreditLineRechargeUSD, order.F_HS_B2CCustId, "100.01") + System.Environment.NewLine;
}
else
{
sql += string.Format(@"/*dialect*/ update a set a.F_HS_USDBalance = a.F_HS_USDBalance + {0}{3}
from T_BD_CUSTOMER a
left join T_SAL_ORDER b on a.FCUSTID = b.FCUSTID
inner join T_ORG_ORGANIZATIONS c on a.FUSEORGID = c.FORGID
where a.FNUMBER = '{1}'
and c.FNUMBER = '{2}'", order.FRealAmountFor_USD, order.F_HS_B2CCustId, order.FSaleOrgId, order.FSaleOrgId.CompareTo("100.03") == 0 && order.FSettleCurrId.CompareTo("CNY") == 0 ? string.Format(@",a.F_HS_CNYBalance = a.F_HS_CNYBalance + {0}", order.FRealAmountFor) : "") + System.Environment.NewLine;
sql += string.Format(@"/*dialect*/ update a set a.F_HS_BalanceDeducted = '{0}'
from T_SAL_ORDER a
inner join T_ORG_ORGANIZATIONS c on a.FSALEORGID = c.FORGID
where a.FBillNo = '{1}'
and c.FNUMBER = '{2}'", "1", order.FBillNo, order.FSaleOrgId) + System.Environment.NewLine;
}
sql = "'" + SQLUtils.DealQuotes(sql) + "'";
execSql += string.Format(@"EXEC(" + sql + ")") + System.Environment.NewLine;
if (order.F_HS_SaleOrderSource.CompareTo("DropShippingOrder") == 0)
{
execSql += string.Format(@"declare @balance{0} decimal(10,2)
declare @cnybalance{1} decimal(10,2)
declare @creditbalance{2} decimal(10,2)
set @balance{3} = ( select F_HS_USDBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{4}' and b.FNUMBER = '{5}'
)
set @cnybalance{6} = ( select F_HS_CNYBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{7}' and b.FNUMBER = '{8}'
)
set @creditbalance{9} = ( select F_HS_SurplusCreditUSD from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{10}' and b.FNUMBER = '{11}'
)
", i, i, i, i, order.F_HS_B2CCustId, order.FSaleOrgId, i, order.F_HS_B2CCustId, order.FSaleOrgId, i, order.F_HS_B2CCustId, order.FSaleOrgId) + System.Environment.NewLine;
if (order.F_HS_BalanceRechargeUSD < 0)
{
execSql += string.Format(@"/*dialect*/ insert into HS_T_customerBalance(F_HS_TradeType,F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,changedCause
,balanceAmount,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,F_HS_CNYBalance,updateTime
,updateUser,FBillNo,fentryID,needfreezed,remark) values ('{0}',{1},{2},{3},'{4}','{5}',@balance{6}*{7},{8},{9}
,{10},@balance{11}, @cnybalance{12},'{13}',{14},'{15}',{16},{17},'{18}')", "余额", SQLUtils.GetOrgId(ctx, order.FSaleOrgId), SQLUtils.GetCustomerId(ctx, order.F_HS_B2CCustId, 1)
, order.F_HS_BalanceRechargeUSD * order.F_HS_RateToUSA, order.ChangedType, order.ChangedCause, i, order.F_HS_RateToUSA, order.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, order.FSettleCurrId)
, order.F_HS_BalanceRechargeUSD, i, i, DateTime.Now, ctx.UserId, order.FBillNo, order.FEntryId, order.NeedFreezed == false ? 0 : 1, order.Remark
);
}
if (order.F_HS_CreditLineRechargeUSD < 0)
{
execSql += string.Format(@"/*dialect*/ insert into HS_T_customerBalance(F_HS_TradeType,F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,changedCause
,balanceAmount,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,F_HS_CNYBalance,updateTime
,updateUser,FBillNo,fentryID,needfreezed,remark) values ('{0}',{1},{2},{3},'{4}','{5}',@creditbalance{6}*{7},{8},{9}
,{10},@creditbalance{11}, @cnybalance{12},'{13}',{14},'{15}',{16},{17},'{18}') "
, "信用额度", SQLUtils.GetOrgId(ctx, order.FSaleOrgId), SQLUtils.GetCustomerId(ctx, order.F_HS_B2CCustId, 1)
, order.F_HS_CreditLineRechargeUSD * order.F_HS_RateToUSA, order.ChangedType, order.ChangedCause, i, order.F_HS_RateToUSA, order.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, order.FSettleCurrId)
, order.F_HS_CreditLineRechargeUSD, i, i, DateTime.Now, ctx.UserId, order.FBillNo, order.FEntryId, order.NeedFreezed == false ? 0 : 1, order.Remark
) + System.Environment.NewLine;
}
}
else
{
execSql += string.Format(@"declare @balance{2} decimal(10,2)
declare @cnybalance{4} decimal(10,2)
set @balance{3} = ( select F_HS_USDBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{0}' and b.FNUMBER = '{1}'
)
set @cnybalance{5} = ( select F_HS_CNYBalance from T_BD_CUSTOMER a
inner join T_ORG_ORGANIZATIONS b on a.FUSEORGID = b.FORGID
where a.FNUMBER = '{6}' and b.FNUMBER = '{7}'
)
", order.F_HS_B2CCustId, order.FSaleOrgId, i, i, i, i, order.F_HS_B2CCustId, order.FSaleOrgId) + System.Environment.NewLine;
execSql += string.Format(@"/*dialect*/ insert into HS_T_customerBalance(F_HS_TradeType,F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,changedCause
,balanceAmount,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,F_HS_CNYBalance,updateTime
,updateUser,FBillNo,fentryID,needfreezed,remark) values ('{18}','{15}','{0}',{1},'{2}','{3}',@balance{13}*{17},{4},{5}
,{6},@balance{14}, @cnybalance{16},'{7}',{8},'{9}',{10},{11},'{12}') ", SQLUtils.GetCustomerId(ctx, order.F_HS_B2CCustId, 1), order.FRealAmountFor
, order.ChangedType, order.ChangedCause, order.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, order.FSettleCurrId)
, order.FRealAmountFor_USD, DateTime.Now, ctx.UserId, order.FBillNo, order.FEntryId, order.NeedFreezed == false ? 0 : 1, order.Remark, i, i
, SQLUtils.GetOrgId(ctx, order.FSaleOrgId), i, order.F_HS_RateToUSA, "余额") + System.Environment.NewLine;
}
sql = string.Empty;
i++;
}
}
else
{
LogUtils.WriteSynchroLog(ctx, this.DataType, "获取【" + order.FSettleCurrId + "币别】兑美元的实时汇率失败");
throw new Exception("获取【" + order.FSettleCurrId + "币别】兑美元的实时汇率失败！");
}
}
}
}
}
}
catch (Exception ex)
{
LogUtils.WriteSynchroLog(ctx, this.DataType, ex.Message + System.Environment.NewLine + ex.StackTrace);
}
return(execSql);
}
public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result)
{
List <AbsDataInfo> orders = null;
AbsDataInfo order = null;
result = new HttpResponseResult();
result.Success = true;
if (objects != null && objects.Count > 0)
{
orders = new List <AbsDataInfo>();
foreach (var obj in objects)
{
if (obj != null)
{
bool deducted = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_BalanceDeducted"));
bool isApproval = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_CheckBox"));
if (!deducted)
{
DynamicObject cust = obj["F_HS_B2CCustId"] as DynamicObject;
string custNo = SQLUtils.GetFieldValue(cust, "Number");
string currNo = GetOrderFinance(obj).FSettleCurrID;
string billNo = SQLUtils.GetFieldValue(obj, "BillNo");
DynamicObject sOrg = obj["SALEORGID"] as DynamicObject;
string orgNo = SQLUtils.GetFieldValue(sOrg, "Number");
DynamicObject src = obj["F_HS_SaleOrderSource"] as DynamicObject;
string sType = SQLUtils.GetFieldValue(src, "FNumber");
decimal rateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_RateToUSA"));
string documentStatus = SQLUtils.GetFieldValue(obj, "DocumentStatus");
decimal orderAmount = Math.Round(GetOrderFinance(obj).FBillAllAmount / rateToUSA, 2);
decimal useCnyBalance = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_CNYBalancePayments")), 2);
decimal useBalance = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_BalancePayments")), 2);
decimal useUsdBalance = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_USDBalancePayments")), 2);
decimal useCredit = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_CreditLineUSDPayments")), 2);
decimal useAmount = 0;
if (this.DataType == SynchroDataType.SaleOrder || this.DataType == SynchroDataType.SaleOrderOffline)
{
if (orgNo.CompareTo("100.03") != 0)
{
useAmount = useUsdBalance + useCredit;
}
else
{
useAmount = useCnyBalance + useCredit * rateToUSA;
}
}
if (sType.CompareTo("DropShippingOrder") == 0)
{
useAmount = orderAmount;
}
if (useAmount > 0)
{
decimal custBalance = 0;
decimal custSurplus = LogHelper.GetCustBalance(ctx, custNo, orgNo);
decimal custCredit = LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_SurplusCreditUSD");
if (this.DataType == SynchroDataType.SaleOrder || this.DataType == SynchroDataType.SaleOrderOffline)
{
custBalance = custSurplus;
}
else
{
custBalance = custSurplus + custCredit;
}
if (useAmount <= custBalance)
{
order = new AbsDataInfo(this.DataType);
order.FSettleCurrId = currNo;
if (order != null)
{
order.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_RateToUSA"));
order.F_HS_SaleOrderSource = sType;
DynamicObject org = obj["SALEORGID"] as DynamicObject;
order.FSaleOrgId = SQLUtils.GetFieldValue(org, "Number");
order.FBillNo = billNo;
order.SrcNo = order.FBillNo;
order.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "Date")));
order.F_HS_B2CCustId = custNo;
order.FCancelStatus = SQLUtils.GetFieldValue(obj, "CancelStatus");
order.FBusinessTime = SQLUtils.GetFieldValue(obj, "ApproveDate");
order.FCustBalance = custSurplus;
order.FCustBalanceAmount = custBalance;
order.F_HS_BalanceRecharge = -useBalance;
order.F_HS_BalanceRechargeUSD = -useUsdBalance;
order.F_HS_CreditLineRechargeUSD = -useCredit;
if (sType.CompareTo("DropShippingOrder") == 0)
{
order.FBillAmount = orderAmount;
SetPayment(order);
}
orders.Add(order);
}
}
else
{
if (!string.IsNullOrWhiteSpace(sType))
{
if ((this.DataType.CompareTo(SynchroDataType.DropShippingSalOrder) == 0 && !isApproval) ||
this.DataType.CompareTo(SynchroDataType.SaleOrder) == 0 || this.DataType.CompareTo(SynchroDataType.SaleOrderOffline) == 0)
{
result.Success = false;
result.Message = "单据编码【" + billNo + "】客户【" + custNo + "】余额支付大于客户结余！" + Environment.NewLine;
LogUtils.WriteSynchroLog(ctx, this.DataType, result.Message);
}
}
}
}
}
else
{
result.Success = false;
result.Message = "客户余额已扣减！" + Environment.NewLine;
LogUtils.WriteSynchroLog(ctx, this.DataType, result.Message);
}
}
}
}
return(orders);
}
/// <summary>
/// 收款明细
/// </summary>
/// <param name="ctx"></param>
/// <param name="data"></param>
/// <returns></returns>
private JArray BuildReceiveBillEntry(Context ctx, AbsSynchroDataInfo data)
{
JArray FRECEIVEBILLENTRY = null;
JObject baseData = null;
if (data != null)
{
if (data.GetType() == typeof(K3SalOrderInfo))
{
K3SalOrderInfo order = data as K3SalOrderInfo;
FRECEIVEBILLENTRY = new JArray();
baseData = new JObject();
JObject FSETTLETYPEID = new JObject();//结算方式"JSFS01_SYS"
FSETTLETYPEID.Add("FNumber", SQLUtils.GetSettleTypeNo(K3CloudContext, SQLUtils.GetPaymentMethodId(K3CloudContext, order.F_HS_PaymentModeNew)));
baseData.Add("FSETTLETYPEID", FSETTLETYPEID);
JObject FPURPOSEID = new JObject();//收款用途
FPURPOSEID.Add("FNumber", "SFKYT02_SYS");
baseData.Add("FPURPOSEID", FPURPOSEID);
baseData.Add("FRECEIVEITEMTYPE", "1"); //预收项目类型
baseData.Add("FRECEIVEITEM", order.FBillNo); //销售订单
baseData.Add("FSaleOrderID", SQLUtils.GetSaleOrderId(K3CloudContext, order.FBillNo)); //销售订单内码
if (Convert.ToDecimal(order.F_HS_PayTotal) > 0)
{
if (order.FSettleCurrId.CompareTo("JPY") == 0)
{
baseData.Add("FRECTOTALAMOUNTFOR", Math.Truncate(Convert.ToDecimal(order.F_HS_PayTotal) * order.F_HS_RateToUSA));//财务实收金额
}
else
{
baseData.Add("FRECTOTALAMOUNTFOR", Convert.ToDecimal(order.F_HS_PayTotal) * order.F_HS_RateToUSA);//财务实收金额
}
}
else if (string.IsNullOrWhiteSpace(order.F_HS_PayTotal))
{
if (order.FSettleCurrId.CompareTo("JPY") == 0)
{
baseData.Add("FRECTOTALAMOUNTFOR", Math.Truncate(order.F_HS_Total));//财务实收金额
}
else
{
baseData.Add("FRECTOTALAMOUNTFOR", order.F_HS_Total);//财务实收金额
}
}
JObject FACCOUNTID = new JObject();//我方银行账号
FACCOUNTID.Add("FNumber", SQLUtils.GetBankAccountNo(K3CloudContext, SQLUtils.GetBankAccountId(K3CloudContext, order.F_HS_PaymentModeNew)));
baseData.Add("FACCOUNTID", FACCOUNTID);
baseData.Add("FSETTLENO", order.F_HS_TransactionID.Trim());//结算号
FRECEIVEBILLENTRY.Add(baseData);
}
else if (data.GetType() == typeof(AbsDataInfo))
{
AbsDataInfo info = data as AbsDataInfo;
FRECEIVEBILLENTRY = new JArray();
baseData = new JObject();
JObject FSETTLETYPEID = new JObject();//结算方式"JSFS01_SYS"
FSETTLETYPEID.Add("FNumber", info.FSettleTypeId);
baseData.Add("FSETTLETYPEID", FSETTLETYPEID);
JObject FPURPOSEID = new JObject();//收款用途
FPURPOSEID.Add("FNumber", "SFKYT02_SYS");
baseData.Add("FPURPOSEID", FPURPOSEID);
if (Convert.ToDecimal(info.FRealAmountFor) > 0)
{
baseData.Add("FRECTOTALAMOUNTFOR", info.FRealAmountFor);//财务实收金额
}
JObject FACCOUNTID = new JObject();//我方银行账号
FACCOUNTID.Add("FNumber", SQLUtils.GetBankAccountNo(K3CloudContext, SQLUtils.GetBankAccountId(K3CloudContext, info.FSettleTypeId)));
baseData.Add("FACCOUNTID", FACCOUNTID);
baseData.Add("FSETTLENO", "");//结算号
baseData.Add("F_HS_YNRecharge", false);
baseData.Add("F_HS_SynchronizedRecharge", false);
FRECEIVEBILLENTRY.Add(baseData);
}
}
return(FRECEIVEBILLENTRY);
}
public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result)
{
List <AbsDataInfo> receives = null;
AbsDataInfo receive = null;
result = new HttpResponseResult();
result.Success = true;
if (objects != null && objects.Count > 0)
{
receives = new List <AbsDataInfo>();
foreach (var obj in objects)
{
if (obj != null)
{
DynamicObject cust = obj["F_HS_B2CCUSTID"] as DynamicObject;
string custNo = SQLUtils.GetFieldValue(cust, "Number");
List <ReceiveBillEntry> entrys = GetReceiveBillEntry(ctx, obj);
if (entrys != null && entrys.Count > 0)
{
foreach (var entry in entrys)
{
if (entry != null)
{
if (entry.F_HS_YNRecharge)
{
if (!entry.F_HS_SynchronizedRecharge)
{
receive = new AbsDataInfo(this.DataType);
DynamicObject org = obj["SALEORGID"] as DynamicObject;
receive.FSaleOrgId = SQLUtils.GetFieldValue(org, "Number");
receive.SrcNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryID;
receive.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryID;
receive.FEntryId = entry.FEntryID;
receive.F_HS_B2CCustId = custNo;
receive.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "DATE")));
DynamicObject curr = obj["CURRENCYID"] as DynamicObject;
receive.FSettleCurrId = SQLUtils.GetFieldValue(curr, "Number");
receive.F_HS_RateToUSA = GetExchangeRate(ctx, receive.FSettleCurrId);
receive.FSettleTypeId = entry.FSETTLETYPEID;
receive.FBusinessTime = SQLUtils.GetFieldValue(obj, "ApproveDate");
receive.F_HS_BalanceRechargeUSD = entry.F_HS_BalanceRechargeUSD;
receive.F_HS_CreditLineRechargeUSD = entry.F_HS_CreditLineRechargeUSD;
if (receive.F_HS_CreditLineRechargeUSD < 0)
{
entry.F_HS_BalanceRechargeUSD = entry.FREALRECAMOUNTFOR_D / receive.F_HS_RateToUSA;
}
receives.Add(receive);
}
else
{
result.Success = false;
result.Message = string.Format(@"收款单【{0}】已同步", receive.FBillNo);
}
}
}
}
}
}
}
}
return(receives);
}
