/// <summary> /// 筛选需要保存或更新的客户资料 /// </summary> /// <param name="ctx"></param> /// <param name="srcDatas"></param> /// <returns></returns> public override Dictionary <SynOperationType, IEnumerable <AbsSynchroDataInfo> > EntityDataSource(Context ctx, IEnumerable <AbsSynchroDataInfo> srcDatas) { Dictionary <SynOperationType, IEnumerable <AbsSynchroDataInfo> > dict = new Dictionary <SynOperationType, IEnumerable <AbsSynchroDataInfo> >(); List <AbsSynchroDataInfo> lstDiff = null; List <string> K3CustNos = null; List <string> synCustNos = null; List <string> existNos = null; #region string sql = string.Format(@"/*dialect*/ select distinct FNUMBER from T_BD_CUSTOMER"); DynamicObjectCollection coll = SQLUtils.GetObjects(ctx, sql); if (coll != null && coll.Count > 0) { K3CustNos = new List <string>(); foreach (var item in coll) { K3CustNos.Add(JsonUtils.ConvertObjectToString(item["FNUMBER"])); } coll = null; } List <K3CustomerInfo> custs = ConvertAbsSynchroObject(srcDatas); if (custs != null && custs.Count > 0) { synCustNos = new List <string>(); foreach (var cust in custs) { synCustNos.Add(cust.FNumber); } } if (K3CustNos != null && K3CustNos.Count > 0) { existNos = K3CustNos.Intersect(synCustNos).ToList(); } else { dict.Add(SynOperationType.SAVE, srcDatas); return(dict); } if (existNos != null) { var same = from c in custs where existNos.Contains(c.FNumber) select c; var diff = from c in custs where existNos.Contains(c.FNumber) == false select c; //if (same != null && same.Count() > 0) //{ // lstSame = same.ToList<AbsSynchroDataInfo>(); // if (lstSame != null && lstSame.Count > 0) // { // dict.Add(SynOperationType.UPDATE, lstSame); // } //} if (diff != null && diff.Count() > 0) { lstDiff = diff.ToList <AbsSynchroDataInfo>(); if (lstDiff != null && lstDiff.Count > 0) { dict.Add(SynOperationType.SAVE, lstDiff); } } } return(dict); #endregion }
/// <summary> /// 将需要同步的客户转化为JSON格式(客户集合) /// </summary> /// <param name="sourceDatas"></param> /// <param name="operationType"></param> /// <returns></returns> public override JObject BuildSynchroDataJsons(IEnumerable <AbsSynchroDataInfo> sourceDatas, SynOperationType operationType) { JObject root = null; int batchCount = BatchCount(sourceDatas); if (operationType == SynOperationType.SAVE) { root = new JObject(); root.Add("NeedUpDateFields", new JArray("")); root.Add("NeedReturnFields", new JArray("FNumber")); root.Add("IsDeleteEntry", "false"); root.Add("SubSystemId", ""); root.Add("IsVerifyBaseDataField", "true"); root.Add("BatchCount", batchCount); root.Add("Model", ConvertSynObjToJObj(sourceDatas, operationType)); return(root); } //数据更新时的Json格式 else if (operationType == SynOperationType.UPDATE) { //更新单据时,表体信息必须填写明细表体的主键 root = new JObject(); root.Add("NeedUpDateFields", new JArray("")); root.Add("NeedReturnFields", new JArray("FNumber")); root.Add("IsDeleteEntry", "false"); root.Add("SubSystemId", ""); root.Add("IsVerifyBaseDataField", "true"); root.Add("BatchCount", batchCount); if (sourceDatas != null) { if (sourceDatas.Count() > 0) { JArray model = new JArray(); for (int i = 0; i < sourceDatas.Count(); i++) { K3CustomerInfo custData = sourceDatas.ElementAt(i) as K3CustomerInfo; string sFCustId = string.Format(@"/*dialect*/ select FCUSTID,FUseOrgId from T_BD_CUSTOMER where FNumber = '{0}'", custData.FNumber); DynamicObjectCollection coll = SQLUtils.GetObjects(this.K3CloudContext, sFCustId); if (coll.Count > 0) { foreach (var c in coll) { if (c["FCUSTID"] != null) { JObject baseData = ConvertSynObjToJObj(sourceDatas.ElementAt(i), operationType); //主组织记录 if (Convert.ToInt32(JsonUtils.ConvertObjectToString(c["FUseOrgId"])) == 1) { //主组织记录中的销售员和销售部门不能被更新 baseData.Add("FCUSTID", Convert.ToInt32(JsonUtils.ConvertObjectToString(c["FCUSTID"]))); } else { baseData.Add("FCUSTID", Convert.ToInt32(JsonUtils.ConvertObjectToString(c["FCUSTID"]))); JObject FSELLER = new JObject(); FSELLER.Add("FNumber", custData.FSELLER); baseData.Add("FSELLER", FSELLER); JObject FSALDEPTID = new JObject(); FSALDEPTID.Add("FNumber", custData.FSALDEPTID); baseData.Add("FSALDEPTID", FSALDEPTID); } model.Add(baseData); } } } } root.Add("Model", model); return(root); } } } return(null); }
public void LoadGameobjectNames() { GameobjectNames = SQLUtils.GetGameobjectNames(ConnectionString()); }
public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result) { K3SalOrderInfo order = null; List <K3SalOrderInfo> orders = null; result = new HttpResponseResult(); result.Success = true; if (objects != null && objects.Count > 0) { orders = new List <K3SalOrderInfo>(); foreach (var item in objects) { if (item != null) { if (SQLUtils.GetBillTypeNo(this.Context, item, "BillTypeId_Id").CompareTo("XSDD01_SYS") == 0 && SQLUtils.GetSaleOrderSourceNo(this.Context, item, "F_HS_SaleOrderSource_Id").CompareTo("XXBJDD") == 0 && SQLUtils.GetFieldValue(item, "DocumentStatus").CompareTo("C") == 0 && SQLUtils.GetFieldValue(item, "BillNo").StartsWith("SO") && !SQLUtils.GetFieldValue(item, "BillNo").Contains("_") && SQLUtils.GetFieldValue(item, "SaleOrgId_Id").CompareTo("100035") == 0) { order = new K3SalOrderInfo(); K3SaleOrderFinance finance = GetOrderFinance(item, "SaleOrderFinance");//财务信息 order.SrcNo = SQLUtils.GetFieldValue(item, "BillNo"); order.FBillNo = SQLUtils.GetFieldValue(item, "BillNo"); //订单号 order.F_HS_OriginOnlineOrderNo = SQLUtils.GetFieldValue(item, "F_HS_OriginOnlineOrderNo"); //原线上订单单号 order.FDate = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "Date")); //订单日期 order.PurseDate = TimeHelper.GetTimeStamp(order.FDate); //订单日期(时间戳) order.FNote = SQLUtils.GetFieldValue(item, "FNote"); //备注 DynamicObject cust = item["CustId"] as DynamicObject; //客户 order.FCustId = SQLUtils.GetFieldValue(cust, "Number"); DynamicObject realCust = item["F_HS_B2CCustId"] as DynamicObject;//客户真实ID order.F_HS_B2CCustId = SQLUtils.GetFieldValue(realCust, "Number"); DynamicObject saler = item["SalerId"] as DynamicObject; order.FSalerId = SQLUtils.GetFieldValue(saler, "Number"); DynamicObject source = item["F_HS_SaleOrderSource"] as DynamicObject; order.OrderSource = SQLUtils.GetFieldValue(source, "FNumber"); //订单来源 order.F_HS_PaymentStatus = SQLUtils.GetFieldValue(item, "F_HS_PaymentStatus"); //付款状态 order.FSettleCurrId = finance.FSettleCurrID; //结算币别 order.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "ExchangeRate")); //汇率 DynamicObject country = item["F_HS_RecipientCountry"] as DynamicObject; order.F_HS_RecipientCountry = SQLUtils.GetFieldValue(country, "Number"); //国家 order.F_HS_DeliveryProvinces = SQLUtils.GetFieldValue(item, "F_HS_DeliveryProvinces"); //省份 order.F_HS_DeliveryCity = SQLUtils.GetFieldValue(item, "F_HS_DeliveryCity"); //城市 order.F_HS_DeliveryAddress = SQLUtils.GetFieldValue(item, "F_HS_DeliveryAddress"); //具体地址 order.F_HS_PostCode = SQLUtils.GetFieldValue(item, "F_HS_PostCode"); //邮编 order.F_HS_DeliveryName = SQLUtils.GetFieldValue(item, "F_HS_DeliveryName"); //收货人 order.F_HS_BillAddress = SQLUtils.GetFieldValue(item, "F_HS_BillAddress"); //账单地址 order.F_HS_MobilePhone = SQLUtils.GetFieldValue(item, "F_HS_MobilePhone"); //联系人手机 order.F_HS_ShippingMethod = SQLUtils.GetFieldValue(item, "F_HS_ShippingMethod"); //发货方式 DynamicObject met = item["F_HS_PaymentModeNew"] as DynamicObject; order.F_HS_PaymentModeNew = SQLUtils.GetFieldValue(met, "Number"); //付款方式 order.FCustLevel = SQLUtils.GetCustGroupNo(this.Context, item, "F_HS_FGroup_Id"); //客户分组 order.FNote = SQLUtils.GetFieldValue(item, "Note"); //备注 order.F_HS_Channel = SQLUtils.GetFieldValue(item, "F_HS_OnlineOrderWay"); //下单方式 order.F_HS_CouponAmount = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_CouponAmount")); //优惠券金额 //order.F_HS_Shipping = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_Shipping"));//运费 order.F_HS_Points = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_Points")); //积分 order.OrderEntry = GetOrderEntry(item, "SaleOrderEntry", order); //订单明细 order.F_HS_Total = order.F_HS_Subtotal - order.F_HS_CouponAmount - order.F_HS_IntegralDeduction - order.F_HS_DiscountedAmount + order.F_HS_Shipping; //优惠后金额 order.FApproveDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(item, "ApproveDate"))); order.F_HS_CollectionTime = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "F_HS_CollectionTime")); //CEO特批已到款时间 order.PayedTime = TimeHelper.GetTimeStamp(order.F_HS_CollectionTime); //CEO特批已到款时间(时间戳) orders.Add(order); } } } } return(orders); }
/// <summary> /// 获取未生成收款单的销售订单(定时任务) /// </summary> /// <param name="ctx"></param> /// <returns></returns> private List <K3SalOrderInfo> GetNonSynRevSalOrder(Context ctx) { List <K3SalOrderInfo> orders = null; K3SalOrderInfo order = null; string sql = string.Format(@"/*dialect*/ select distinct FBillNo,FDate,j.FNUMBER as FCUSTID,j.FNUMBER as F_HS_B2CCustId,m.FNUMBER as FSalerId,n.FNUMBER as FSALEDEPTID,z.FNUMBER as FSaleOrgId,o.FNumber as FSettleCurrId,F_HS_RateToUSA,a.F_HS_TransactionID,F_HS_PayTotal,q.FNUMBER as F_HS_PaymentModeNew from T_SAL_ORDER a inner join T_SAL_ORDERENTRY b on b.FID = a.FID inner join T_SAL_ORDERENTRY_F c on c.FENTRYID = b.FENTRYID and c.FID = b.FID inner join T_SAL_ORDERFIN d on d.FID = a.FID inner join T_BD_CUSTOMER e on e.FCUSTID= a.FCUSTID inner join T_BAS_ASSISTANTDATAENTRY_L f ON a.F_HS_SaleOrderSource=f.FENTRYID inner join T_BAS_ASSISTANTDATAENTRY g ON f.FentryID=g.FentryID left join T_BAS_BILLTYPE h on a.FBILLTypeID=h.FBILLTypeID inner join T_ORG_ORGANIZATIONS z on a.FSALEORGID=z.FORGID inner join T_BD_CUSTOMER j on j.FCUSTID = a.F_HS_B2CCUSTID inner join T_BD_CURRENCY o on o.FCURRENCYID = d.FSettleCurrId inner join T_BAS_ASSISTANTDATAENTRY_L p ON a.F_HS_PaymentModeNew=p.FENTRYID inner join T_BAS_ASSISTANTDATAENTRY q ON q.FentryID=p.FentryID inner join V_BD_SALESMAN m on m.FID = a.FSALERID inner join T_BD_DEPARTMENT n on n.FDEPTID = m.FDEPTID where a.FDOCUMENTSTATUS = 'C'and a.FsaleOrgID = 100035 and a.FCANCELSTATUS<>'B' and h.FNUMBER='XSDD01_SYS' and z.FNUMBER='100.01' and g.FNUMBER = 'HCWebProcessingOder' and a.FCREATEDATE >= DATEADD(MONTH,-1,GETDATE()) and a.fbillno not in (select l.FRECEIVEITEM from T_AR_RECEIVEBILL k inner join T_AR_RECEIVEBILLENTRY l on l.FID = K.FID where k.FCREATEDATE >= DATEADD(MONTH,-1,GETDATE()) and l.FRECEIVEITEM not like 'SO%' and l.FRECEIVEITEM <> '')" ); DynamicObjectCollection coll = SQLUtils.GetObjects(K3CloudContext, sql); if (coll != null && coll.Count > 0) { orders = new List <K3SalOrderInfo>(); foreach (var item in coll) { if (item != null) { order = new K3SalOrderInfo(); order.FBillNo = SQLUtils.GetFieldValue(item, "FBillNo"); order.FDate = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "FDate")); order.FCustId = SQLUtils.GetFieldValue(item, "FCUSTID"); order.F_HS_B2CCustId = SQLUtils.GetFieldValue(item, "FCUSTID"); order.FSalerId = SQLUtils.GetFieldValue(item, "FSalerId"); order.FSaleDeptId = SQLUtils.GetFieldValue(item, "FSALEDEPTID"); order.FSaleOrgId = SQLUtils.GetFieldValue(item, "FSaleOrgId"); order.FSettleCurrId = SQLUtils.GetFieldValue(item, "FSettleCurrId"); order.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(item, "F_HS_RateToUSA")); order.F_HS_TransactionID = SQLUtils.GetFieldValue(item, "F_HS_TransactionID"); order.F_HS_PayTotal = SQLUtils.GetFieldValue(item, "F_HS_PayTotal"); order.F_HS_PaymentModeNew = SQLUtils.GetFieldValue(item, "F_HS_PaymentModeNew"); orders.Add(order); } } } return(orders); }
/// <summary> /// 获取同步数据 /// </summary> /// <param name="ctx"></param> /// <param name="objects"></param> /// <returns></returns> public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result) { K3BatchAdjust just = null; List <K3BatchAdjust> justs = null; result = new HttpResponseResult(); result.Success = true; if (objects != null && objects.Count > 0) { justs = new List <K3BatchAdjust>(); foreach (var obj in objects) { if (obj != null) { bool yNInSync = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_YNInSync")); if (!yNInSync) { if (GetK3BatchAdjustEntry(ctx, obj) != null && GetK3BatchAdjustEntry(ctx, obj).Count > 0) { just = new K3BatchAdjust(); just.FBillNo = SQLUtils.GetFieldValue(obj, "BillNo"); just.SrcNo = just.FBillNo; just.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "Date"))); just.F_HS_YNInSync = yNInSync; just.Entry = GetK3BatchAdjustEntry(ctx, obj); justs.Add(just); } } } } } return(justs); }
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); }
public override HttpResponseResult ExecuteSynchro(IEnumerable <AbsSynchroDataInfo> sourceDatas, List <SynchroLog> logs, SynOperationType operationType) { HttpResponseResult result = new HttpResponseResult(); List <K3SalOrderInfo> second = null; List <string> numbers = null; List <string> auditSuccNos = null; if (sourceDatas == null || sourceDatas.Count() == 0) { result = new HttpResponseResult(); result.Success = false; result.Message = "没有需要同步的数据!"; } if (operationType == SynOperationType.UPDATE) { try { second = sourceDatas.Select(o => (K3SalOrderInfo)o).ToList(); if (second != default(List <K3SalOrderInfo>)) { if (second.Count > 0) { numbers = new List <string>(); auditSuccNos = new List <string>(); foreach (var item in second) { if (IsExist(this.K3CloudContext, item)) { char isSameAdress = default(char); if (item.F_HS_IsSameAdress) { isSameAdress = '1'; } else { isSameAdress = '0'; } string uSql = string.Format(@"/*dialect*/ update T_SAL_ORDER set F_HS_PAYMENTSTATUS = '{0}',F_HS_SALEORDERSOURCE = '5a97d3123e9dff',F_HS_BillAddress = '{1}',F_HS_IsSameAdress = '{2}',F_HS_PaymentModeNew = '{4}' where FBILLNO = '{3}' and FDOCUMENTSTATUS != 'C' and FDOCUMENTSTATUS != 'D' and FDOCUMENTSTATUS != 'B' and FBILLNO <>''", item.F_HS_PaymentStatus, SQLUtils.DealQuotes(item.F_HS_BillAddress), isSameAdress, item.FBillNo, SQLUtils.GetPaymentMethodId(this.K3CloudContext, item.F_HS_PaymentModeNew)); int count = DBUtils.Execute(this.K3CloudContext, uSql); if (count > 0 && item.F_HS_PaymentStatus != null && item.F_HS_PaymentStatus.CompareTo("3") == 0) { numbers.Add(item.FBillNo); } } else { auditSuccNos.Add(item.FBillNo); result.Message += "编码为【" + item.FBillNo + "】的订单已经审核" + System.Environment.NewLine; result.Success = false; } } if (numbers != null && numbers.Count > 0) { //单据提交 result = ExecuteOperate(SynOperationType.SUBMIT, numbers, null, null); if (result != null && result.SuccessEntityNos != null && result.SuccessEntityNos.Count > 0) { //单据审核 result = ExecuteOperate(SynOperationType.AUDIT, result.SuccessEntityNos, null, null); if (result != null && result.SuccessEntityNos != null && result.SuccessEntityNos.Count > 0) { List <K3SalOrderInfo> auditOrders = null; //审核成功后的销售订单 if (result.Success) { auditOrders = GetSelectedSalOrders(first, result.SuccessEntityNos); } else { auditOrders = GetSelectedSalOrders(first, GetAuditedSalOrderNos(this.K3CloudContext, second)); } //第二次同步后的销售订单信息和第一次同步的销售订单信息合成新的销售订单列表信息(获取完整的销售订单信息) auditOrders = CombineSalOrder(second, auditOrders); //从redis再次获取数据 if (auditOrders == null || auditOrders.Count == 0) { //auditOrders = GetAuditedSalOrderDatas(ctx, second); if (second != null) { auditOrders = GetSalOrdersByDb(this.K3CloudContext, second.Select(o => o.FBillNo).ToList()); } } //同步收款单 if (auditOrders != null) { Dictionary <SynOperationType, IEnumerable <AbsSynchroDataInfo> > dict = new Dictionary <SynOperationType, IEnumerable <AbsSynchroDataInfo> >(); dict.Add(SynOperationType.SAVE, auditOrders); HttpResponseResult respone = SynchroDataHelper.SynchroDataToK3(this.K3CloudContext, SynchroDataType.ReceiveBill, true, null, dict); //收款单同步成功后删除销售订单记录 if (respone != null) { first.RemoveWhere(o => respone.SuccessEntityNos.Contains(o.FBillNo)); } } //更新客户下单次数 StatisticsOrderCount(this.K3CloudContext, result.SuccessEntityNos); //审核成功后删除Redis中的数据(销售订单第二次同步) RemoveRedisData(this.K3CloudContext, result.SuccessEntityNos); } } } } } else { LogUtils.WriteSynchroLog(this.K3CloudContext, this.DataType, "未找到需要同步的数据!"); } } catch (Exception ex) { LogUtils.WriteSynchroLog(this.K3CloudContext, this.DataType, "数据批量更新过程中出现异常,异常信息:" + ex.Message + System.Environment.NewLine + ex.StackTrace); } } return(result); }
/// <summary> /// 3.3 计算明细余额充值金额USD、信用额度充值金额USD ///3.3.1 若客户.信用额度USD>0,则: ///3.3.1.1 若明细.实收金额USD>=客户.信用额度USD-客户.剩余信用额度USD,则: ///明细.信用额度充值金额USD=客户.信用额度USD-客户.剩余信用额度USD ///明细.余额充值金额USD=明细.实收金额USD-明细.信用额度充值金额USD ///3.3.1.2 若明细.实收金额USD<客户.信用额度USD-客户.剩余信用额度USD, 则: ///明细.信用额度充值金额USD=明细.实收金额USD ///明细.余额充值金额USD=0 ///3.3.2 若客户.信用额度USD==0,则: ///明细.信用额度充值金额USD=0 ///明细.余额充值金额USD=明细.实收金额USD /// </summary> /// <param name="ctx"></param> /// <param name="obj"></param> /// <returns></returns> private List <ReceiveBillEntry> GetReceiveBillEntry(Context ctx, DynamicObject obj) { List <ReceiveBillEntry> entrys = null; ReceiveBillEntry entry = null; if (obj != null) { DynamicObjectCollection coll = obj["RECEIVEBILLENTRY"] as DynamicObjectCollection; DynamicObject cust = obj["F_HS_B2CCUSTID"] as DynamicObject; string custNo = SQLUtils.GetFieldValue(cust, "Number"); DynamicObject curr = obj["CURRENCYID"] as DynamicObject; string currNo = SQLUtils.GetFieldValue(curr, "Number"); decimal custCredit = LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_CreditLineUSD"); decimal resiCustCredit = LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_SurplusCreditUSD"); decimal rateToUSA = GetExchangeRate(ctx, currNo); if (coll != null && coll.Count > 0) { entrys = new List <ReceiveBillEntry>(); foreach (var item in coll) { if (item != null) { entry = new ReceiveBillEntry(); entry.FEntryID = Convert.ToInt32(SQLUtils.GetFieldValue(item, "Id")); entry.FREALRECAMOUNTFOR_D = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(item, "REALRECAMOUNTFOR")), 2); DynamicObject stype = item["SETTLETYPEID"] as DynamicObject; entry.FSETTLETYPEID = SQLUtils.GetFieldValue(stype, "Number"); entry.F_HS_YNRecharge = Convert.ToBoolean(SQLUtils.GetFieldValue(item, "F_HS_YNRecharge")); entry.F_HS_SynchronizedRecharge = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_SynchronizedRecharge")); decimal receivedTurnUSD = Math.Round((entry.FREALRECAMOUNTFOR_D / rateToUSA), 2); if (custCredit > 0) { if (receivedTurnUSD >= (custCredit - resiCustCredit)) { entry.F_HS_CreditLineRechargeUSD = custCredit - resiCustCredit; entry.F_HS_BalanceRechargeUSD = receivedTurnUSD - entry.F_HS_CreditLineRechargeUSD; } else { entry.F_HS_CreditLineRechargeUSD = receivedTurnUSD; entry.F_HS_BalanceRechargeUSD = 0; } } else if (custCredit == 0) { entry.F_HS_CreditLineRechargeUSD = 0; entry.F_HS_BalanceRechargeUSD = receivedTurnUSD; } entrys.Add(entry); } } } } return(entrys); }
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 receive = data as AbsDataInfo; if (receive != null) { decimal balance = LogHelper.GetCustBalance(ctx, receive.F_HS_B2CCustId, receive.FSaleOrgId); if (receive.F_HS_RateToUSA > 0) { string billNo = receive.FBillNo.Contains("_") ? receive.FBillNo.Split('_')[0] : receive.FBillNo; sql += string.Format(@"/*dialect*/ update a set a.F_HS_SynchronizedRecharge ='{0}',a.F_HS_ReceivedTurnUSD = {1} ,a.F_HS_CreditLineRechargeUSD = '{7}',a.F_HS_BalanceRechargeUSD = '{8}' from T_AR_RECEIVEBILLENTRY a inner join T_AR_RECEIVEBILL b on a.FID = b.FID inner join T_BD_CUSTOMER c on b.F_HS_B2CCustId = c.FCUSTID inner join T_ORG_ORGANIZATIONS d on b.FSALEORGID = d.FORGID where a.F_HS_YNRecharge = '{2}' and c.FNumber = '{3}' and b.FBillNo = '{4}' and d.FNUMBER = '{5}' and a.FENTRYID = {6}" , "1", receive.FRealAmountFor_USD, "1", receive.F_HS_B2CCustId , billNo, receive.FSaleOrgId, receive.FEntryId, receive.F_HS_CreditLineRechargeUSD, receive.F_HS_BalanceRechargeUSD) + System.Environment.NewLine; sql += string.Format(@"/*dialect*/ update a set a.F_HS_RateToUSA ={0} from T_AR_RECEIVEBILL a inner join T_AR_RECEIVEBILLENTRY 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 b.F_HS_YNRecharge = '{1}'and c.FNumber = '{2}' and a.FBillNo = '{3}' and d.FNUMBER = '{4}'", receive.F_HS_RateToUSA, "1", receive.F_HS_B2CCustId, billNo, receive.FSaleOrgId) + System.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_RECEIVEBILL b on a.FCUSTID = b.F_HS_B2CCustId left join T_AR_RECEIVEBILLENTRY 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}'", receive.F_HS_BalanceRechargeUSD, receive.F_HS_B2CCustId, receive.FSaleOrgId , receive.FSaleOrgId.CompareTo("100.03") == 0 && receive.FSettleCurrId.CompareTo("CNY") == 0 ? string.Format(@",a.F_HS_CNYBalance = a.F_HS_CNYBalance + {0}", receive.FRealAmountFor) : "" , string.Format(@",a.F_HS_SurplusCreditUSD = a.F_HS_SurplusCreditUSD + {0}", receive.F_HS_CreditLineRechargeUSD)) + Environment.NewLine; sql = "'" + SQLUtils.DealQuotes(sql) + "'"; execSql += string.Format(@"EXEC(" + sql + ")") + System.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}' ) ", receive.F_HS_B2CCustId, receive.FSaleOrgId, i, i, i, i, receive.F_HS_B2CCustId , receive.FSaleOrgId, i, receive.F_HS_B2CCustId, receive.FSaleOrgId, i) + System.Environment.NewLine; if (receive.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, receive.F_HS_B2CCustId, 1), receive.F_HS_BalanceRechargeUSD / receive.F_HS_RateToUSA , receive.ChangedType, receive.ChangedCause, receive.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, receive.FSettleCurrId) , receive.F_HS_BalanceRechargeUSD, DateTime.Now, ctx.UserId, billNo, receive.FEntryId, receive.NeedFreezed == false ? 0 : 1, receive.Remark, i, i , SQLUtils.GetOrgId(ctx, receive.FSaleOrgId), i, receive.F_HS_RateToUSA, "余额") + System.Environment.NewLine; } if (receive.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, receive.F_HS_B2CCustId, 1), receive.F_HS_CreditLineRechargeUSD , receive.ChangedType, receive.ChangedCause, receive.F_HS_RateToUSA, SQLUtils.GetSettleCurrId(ctx, receive.FSettleCurrId) , receive.F_HS_CreditLineRechargeUSD / receive.F_HS_RateToUSA, DateTime.Now, ctx.UserId, billNo, receive.FEntryId, receive.NeedFreezed == false ? 0 : 1, receive.Remark, i, i , SQLUtils.GetOrgId(ctx, receive.FSaleOrgId), i, receive.F_HS_RateToUSA, "剩余信用额度") + System.Environment.NewLine; } sql = string.Empty; i++; } } else { LogUtils.WriteSynchroLog(ctx, this.DataType, "获取【" + receive.FSettleCurrId + "币别】兑美元的实时汇率失败"); } } } } } catch (Exception ex) { LogUtils.WriteSynchroLog(ctx, this.DataType, ex.Message + System.Environment.NewLine + ex.StackTrace); } return(execSql); }
private string LoadFromText(string name, params object[] args) { return(SQLUtils.LoadCommandFromText(ScriptsPath, name, ScriptsFormat, args)); }
public void InsertTag(int videoId, TagRecord tr) { string cmd = $"INSERT INTO {TagsTable} VALUES({videoId}, {(int) tr.Value}, '{SQLUtils.ToSQLDateTime(tr.Added)}')"; ExecuteNonQuery(cmd); }
public List <SynchroLog> GetSynchroLogDatas(Context ctx, string sql) { List <SynchroLog> logs = null; SynchroLog log = null; DynamicObjectCollection coll = GetObjects(ctx, sql); if (coll != null && coll.Count > 0) { logs = new List <SynchroLog>(); foreach (var item in coll) { if (item != null) { log = new SynchroLog(); log.FDataSourceType = (SynchroDataType)Enum.Parse(typeof(SynchroDataType), SQLUtils.GetFieldValue(item, "FDataSourceType")); log.sourceId = SQLUtils.GetFieldValue(item, "FDataSourceId"); log.K3BillNo = SQLUtils.GetFieldValue(item, "FBILLNO"); log.BeginTime = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "FSynchroTime")); log.IsSuccess = Convert.ToInt32(SQLUtils.GetFieldValue(item, "IsSuccess")); log.ErrInfor = SQLUtils.GetFieldValue(item, "FErrInfor"); log.FDataSourceTypeDesc = SQLUtils.GetFieldValue(item, "FDataSourceTypeDesc"); log.FOperateId = SQLUtils.GetFieldValue(item, "FHSOperateId"); logs.Add(log); } } } return(logs); }
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); }
static void Main(string[] args) { logger.Info("Batch Loader starts..."); string connstr = ConfigurationManager.AppSettings["SQLConnection"]; string stagingTable = ConfigurationManager.AppSettings["StagingTableName"]; string sp_OHLC = ConfigurationManager.AppSettings["InsertOHLC"]; string clearStagingTable = ConfigurationManager.AppSettings["ClearStagingTable"]; using (SQLUtils sql = new SQLUtils(connstr)) { try { string query = ConfigurationManager.AppSettings["SelectSerityMaster"]; DataTable dtSecurities = new DataTable(); sql.GetTableFromQuery(query, dtSecurities); string headers = string.Empty; sql.RunCommand(clearStagingTable); foreach (DataRow row in dtSecurities.Rows) { try { string ticker = row["Ticker"].ToString(); DateTime lastDivDate = HistoricalStockDownloader.GetLatestDivDate(ticker); if (row["InsertDate"] == DBNull.Value || lastDivDate.Date >= DateTime.Today.Date) { List<HistoricalStock> retval = HistoricalStockDownloader.DownloadDataAll(ticker, out headers); sql.BulkInsert<HistoricalStock>(stagingTable, retval); sql.RunCommand(sp_OHLC); logger.Info(string.Format("Historical quotes download completed for {0}", ticker)); } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); } } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); } } logger.Info("Batch Loader completes..."); #if DEBUG Console.ReadLine(); #endif }
/// <summary> /// 获取发货通知单 /// </summary> /// <returns></returns> private List <DeliveryNotice> GetDeliveryNotices(Context ctx) { DeliveryNotice notice = new DeliveryNotice(); List <DeliveryNotice> notices = new List <DeliveryNotice>(); List <string> numbers = GetSelectedShipmentNos(); DynamicObjectCollection coll = GetObjects(ctx, numbers); var group = from c in coll where c["FDelBillNo"] != null group c by c["FDelBillNo"] into g select g; if (group != null && group.Count() > 0) { notices = new List <DeliveryNotice>(); foreach (var item in group) { if (item != null) { notice = new DeliveryNotice(); foreach (var obj in item) { if (obj != null) { notice.FShipmentBillNo.Add(SQLUtils.GetFieldValue(obj, "FShpBillNo")); } } notice.FBillNo = SQLUtils.GetFieldValue(item.ElementAt(0), "FDelBillNo"); notice.F_HS_DeliveryName = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_DeliveryName"); notice.F_HS_MobilePhone = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_MobilePhone"); notice.F_HS_DeliveryCity = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_DeliveryCity"); notice.F_HS_DeliveryProvinces = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_DeliveryProvinces"); notice.F_HS_DeliveryAddress = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_DeliveryAddress"); notice.F_HS_RecipientCountry = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_RecipientCountry"); notice.F_HS_PostCode = SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_PostCode"); notice.orderFin = new K3SaleOrderFinance(); notice.orderFin.FSettleCurrID = SQLUtils.GetFieldValue(item.ElementAt(0), "FSettleCurrID"); notice.orderFin.FBillAmount = Convert.ToDecimal(JsonUtils.ConvertObjectToString(SQLUtils.GetFieldValue(item.ElementAt(0), "FBillAmount"))); notice.F_HS_ShippingMethod = SQLUtils.GetFieldValue(item.ElementAt(0), "logisticsNo"); notice.F_HS_AllTotalWeight = Convert.ToDecimal(SQLUtils.GetFieldValue(item.ElementAt(0), "F_HS_AllTotalWeight")); notice.FDecAmount = Convert.ToDecimal(SQLUtils.GetFieldValue(item.ElementAt(0), "FDecAmount")); List <DynamicObject> entry = item.ToList(); SetDeliveryNoticeEntry(ctx, entry, notice); Dimension dim = new Dimension(); dim.Length = SQLUtils.GetFieldValue(item.ElementAt(0), "FLENGTH"); dim.Width = SQLUtils.GetFieldValue(item.ElementAt(0), "FWIDTH"); dim.Height = SQLUtils.GetFieldValue(item.ElementAt(0), "FHIGH"); dim.Units = "CM"; Package pac = new Package(); pac.Weight = Convert.ToDecimal(SQLUtils.GetFieldValue(item.ElementAt(0), "FBILLINGWEIGHT")) / 100; pac.Units = "KG"; pac.Dimension = dim; notice.Packages.Add(pac); notices.Add(notice); } } } return(notices); }
public void BindDataToView(Context ctx, IDynamicFormView formView, string sql, string entryName) { DynamicObjectCollection coll = GetObjects(ctx, sql); int i = 0; if (!string.IsNullOrWhiteSpace(entryName)) { formView.Model.DeleteEntryData(entryName); if (coll != null && coll.Count > 0) { foreach (var item in coll) { if (item != null) { formView.Model.InsertEntryRow(entryName, i); if (!string.IsNullOrWhiteSpace(SQLUtils.GetFieldValue(item, "F_HS_UseOrgId"))) { formView.Model.SetItemValueByID("F_HS_UseOrgId", SQLUtils.GetFieldValue(item, "F_HS_UseOrgId"), i); } else { formView.Model.SetItemValueByID("F_HS_UseOrgId", "100035", i); } formView.Model.SetItemValueByID("F_HS_Customer", SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID"), i); //formView.Model.SetValue("F_HS_Customer", SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID"), i); formView.Model.SetValue("F_HS_ChangeAmount", SQLUtils.GetFieldValue(item, "changedAmount"), i); formView.Model.SetValue("F_HS_TradeType", SQLUtils.GetFieldValue(item, "F_HS_TradeType"), i); formView.Model.SetValue("F_HS_ChangedType", GetChangeTypeDesc(SQLUtils.GetFieldValue(item, "changedType")), i); formView.Model.SetValue("F_HS_ChangedCause", GetChangeCauseDesc(SQLUtils.GetFieldValue(item, "changedCause")), i); formView.Model.SetValue("F_HS_BalanceAmount", SQLUtils.GetFieldValue(item, "balanceAmount"), i); formView.Model.SetValue("F_HS_RateToUSA", SQLUtils.GetFieldValue(item, "F_HS_RateToUSA"), i); //formView.Model.SetValue("F_HS_SettleCurrId", SQLUtils.GetFieldValue(item, "FSETTLECURRID"), i); formView.Model.SetItemValueByID("F_HS_SettleCurrId", SQLUtils.GetFieldValue(item, "FSETTLECURRID"), i); formView.Model.SetValue("F_HS_ChangedAmountUSD", SQLUtils.GetFieldValue(item, "changedAmountUSA"), i); formView.Model.SetValue("F_HS_BalanceAmountUSD", SQLUtils.GetFieldValue(item, "balanceAmountUSA"), i); formView.Model.SetValue("F_HS_CNYBalance", SQLUtils.GetFieldValue(item, "F_HS_CNYBalance"), i); formView.Model.SetValue("F_HS_UpdateTime", SQLUtils.GetFieldValue(item, "updateTime"), i); //formView.Model.SetValue("F_HS_UpdateUser", SQLUtils.GetFieldValue(item, "updateUser"), i); formView.Model.SetItemValueByID("F_HS_UpdateUser", SQLUtils.GetFieldValue(item, "updateUser"), i); formView.Model.SetValue("F_HS_BillNo", SQLUtils.GetFieldValue(item, "FBillNo"), i); formView.Model.SetValue("F_HS_EntryId", SQLUtils.GetFieldValue(item, "fentryID"), i); formView.Model.SetValue("F_HS_NeedFreezed", SQLUtils.GetFieldValue(item, "needfreezed"), i); formView.Model.SetValue("F_HS_Remark", SQLUtils.GetFieldValue(item, "remark"), i); i++; } } formView.UpdateView(entryName); } else { this.View.ShowErrMessage("", "没有需要查询的数据", MessageBoxType.Error); } } }
/// <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 void LoadData(Context ctx) { string billNo = Convert.ToString(this.View.Model.GetValue("F_HS_BillNumber")); string changeType = Convert.ToString(this.View.Model.GetValue("F_HS_ChangeType")); DynamicObject cust = this.View.Model.GetValue("F_HS_CustNo") as DynamicObject; string custNo = SQLUtils.GetFieldValue(cust, "Number"); DynamicObject curr = this.View.Model.GetValue("F_HS_Currency") as DynamicObject; string currNo = SQLUtils.GetFieldValue(curr, "Number"); string createTime = Convert.ToString(this.View.Model.GetValue("F_HS_CreateDate")); string endTime = Convert.ToString(this.View.Model.GetValue("F_HS_EndDate")); sql.AppendLine(string.Format(@"/*dialect*/ select top 1000 F_HS_UseOrgId,F_HS_B2CCUSTID,changedAmount,changedType,F_HS_TradeType,changedCause,balanceAmount ,F_HS_RateToUSA,FSETTLECURRID,changedAmountUSA,balanceAmountUSA,a.F_HS_CNYBalance,updateTime,updateUser,FBillNo ,fentryID,needfreezed,remark from HS_T_customerBalance a inner join T_BD_CURRENCY b on b.FCURRENCYID = a.FSETTLECURRID inner join T_SEC_user c on c.FUSERID = a.updateUser inner join T_BD_CUSTOMER d on d.FCUSTID = a.F_HS_B2CCUSTID ")); if (!string.IsNullOrWhiteSpace(billNo)) { sql.Append(System.Environment.NewLine + string.Format(@"where a.FBillNo = '{0}'", billNo)); if (!string.IsNullOrWhiteSpace(changeType)) { sql.Append(Environment.NewLine + string.Format(@"and changedType = '{0}'", changeType)); } if (!string.IsNullOrWhiteSpace(custNo)) { sql.Append(Environment.NewLine + string.Format(@"and d.FNumber = '{0}'", custNo)); } if (!string.IsNullOrWhiteSpace(currNo)) { sql.Append(Environment.NewLine + string.Format(@"and b.FNumber = '{0}'", currNo)); } if (!string.IsNullOrWhiteSpace(createTime)) { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } else { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, createTime = createTime.Replace("00:00:00", "23:59:59"))); } } } else { if (!string.IsNullOrWhiteSpace(changeType)) { sql.Append(Environment.NewLine + string.Format(@"where changedType = '{0}'", changeType)); if (!string.IsNullOrWhiteSpace(custNo)) { sql.Append(Environment.NewLine + string.Format(@"and d.FNumber = '{0}'", custNo)); } if (!string.IsNullOrWhiteSpace(currNo)) { sql.Append(Environment.NewLine + string.Format(@"and b.FNumber = '{0}'", currNo)); } if (!string.IsNullOrWhiteSpace(createTime)) { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } else { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, createTime = createTime.Replace("00:00:00", "23:59:59"))); } } } else { if (!string.IsNullOrWhiteSpace(custNo)) { sql.Append(Environment.NewLine + string.Format(@"where d.FNumber = '{0}'", custNo)); if (!string.IsNullOrWhiteSpace(currNo)) { sql.Append(Environment.NewLine + string.Format(@"and b.FNumber = '{0}'", currNo)); } if (!string.IsNullOrWhiteSpace(createTime)) { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } else { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, createTime = createTime.Replace("00:00:00", "23:59:59"))); } } } else { if (!string.IsNullOrWhiteSpace(currNo)) { sql.Append(Environment.NewLine + string.Format(@"where b.FNumber = '{0}'", currNo)); if (!string.IsNullOrWhiteSpace(createTime)) { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } else { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", createTime, createTime = createTime.Replace("00:00:00", "23:59:59"))); } } else { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"and a.updateTime between '{0}' and '{1}'", endTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } } } else { if (!string.IsNullOrWhiteSpace(createTime)) { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"where a.updateTime between '{0}' and '{1}'", createTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } else { sql.Append(Environment.NewLine + string.Format(@"where a.updateTime between '{0}' and '{1}'", createTime, createTime = createTime.Replace("00:00:00", "23:59:59"))); } } else { if (!string.IsNullOrWhiteSpace(endTime)) { sql.Append(Environment.NewLine + string.Format(@"where a.updateTime between '{0}' and '{1}'", endTime, endTime = endTime.Replace("00:00:00", "23:59:59"))); } } } } } } sql.Append(Environment.NewLine + string.Format(@"order by updateTime desc")); try { BindDataToView(ctx, this.View, sql.ToString(), "F_HS_Entity"); sql.Clear(); } catch (Exception ex) { this.View.ShowErrMessage(ex.ToString(), "操作出错了!" + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace, MessageBoxType.Error); } }
/// <summary> /// 调价单明细 /// </summary> /// <param name="ctx"></param> /// <param name="obj"></param> /// <returns></returns> private List <K3BatchAdjustEntry> GetK3BatchAdjustEntry(Context ctx, DynamicObject obj) { DynamicObjectCollection coll = obj["SAL_BATCHADJUSTENTRY"] as DynamicObjectCollection; K3BatchAdjustEntry entry = null; List <K3BatchAdjustEntry> entrys = null; if (entrys == null || entrys.Count == 0) { if (coll != null && coll.Count > 0) { entrys = new List <K3BatchAdjustEntry>(); foreach (var item in coll) { if (item != null) { bool entryInSync = Convert.ToBoolean(SQLUtils.GetFieldValue(item, "F_HS_YNEntryInSync")); if (!entryInSync) { entry = new K3BatchAdjustEntry(); entry.FEntryId = Convert.ToInt32(SQLUtils.GetFieldValue(item, "Id")); entry.FAdjustType = SQLUtils.GetFieldValue(item, "AdjustType"); entry.FAfterPrice = SQLUtils.GetFieldValue(item, "AfterPrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "AfterPrice"); DynamicObject price = item["PriceListId"] as DynamicObject; entry.FPriceListId = SQLUtils.GetFieldValue(price, "Number"); entry.FPriceLevel = SQLUtils.GetFieldValue(price, "Name"); DynamicObject currency = price["CurrencyId"] as DynamicObject; entry.FCurrencyId = SQLUtils.GetFieldValue(currency, "Number"); DynamicObject material = item["MaterialId"] as DynamicObject; entry.FMaterialId = SQLUtils.GetFieldValue(material, "Number"); if (this.Direction == SynchroDirection.ToB2B) { Material mat = SQLUtils.GetMaterial(ctx, entry.FMaterialId); if (mat != null) { entry.F_HS_IsOil = mat.F_HS_IsOil; entry.F_HS_PRODUCTSTATUS = mat.F_HS_PRODUCTSTATUS; entry.F_HS_DropShipOrderPrefix = mat.F_HS_DropShipOrderPrefix; } } entry.FAfterEffDate = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "AfterEffDate")).ToString("yyyy-MM-dd"); entry.FAfterUnEffDate = Convert.ToDateTime(SQLUtils.GetFieldValue(item, "AfterUnEffDate")).ToString("yyyy-MM-dd");; DynamicObject plst = item["PriceListId"] as DynamicObject; entry.F_HS_YNSpecialPrice = Convert.ToBoolean(SQLUtils.GetFieldValue(plst, "F_HS_YNSpecialPrice")); entry.F_HS_YNEntryInSync = entryInSync; if (!entry.F_HS_YNSpecialPrice) { //如果批量调价单调的特价价目表,则忽略调后美国包邮单价、调后澳洲包邮单价、调后美国不包邮单价、调后澳洲不包邮单价、调后欧洲单价的同步 entry.F_HS_AfterUSPrice = SQLUtils.GetFieldValue(item, "F_HS_AfterUSPrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterUSPrice"); entry.F_HS_AfterAUPrice = SQLUtils.GetFieldValue(item, "F_HS_AfterAUPrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterAUPrice"); entry.F_HS_AfterEUPrice = SQLUtils.GetFieldValue(item, "F_HS_AfterEUPrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterEUPrice"); entry.F_HS_AfterUSNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterUSNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterUSNoPostagePrice"); entry.F_HS_AfterAUNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterAUNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterAUNoPostagePrice"); entry.F_HS_AfterJPNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterJPNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterJPNoPostagePrice"); entry.F_HS_AfterKRNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterKRNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterKRNoPostagePrice"); entry.F_HS_AfterUKNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterUKNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterUKNoPostagePrice"); entry.F_HS_AfterDENoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterDENoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterDENoPostagePrice"); entry.F_HS_AfterFRNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterFRNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterFRNoPostagePrice"); entry.F_HS_AfterEUNoPostagePrice = SQLUtils.GetFieldValue(item, "F_HS_AfterEUNoPostagePrice").Equals("0") ? "" : SQLUtils.GetFieldValue(item, "F_HS_AfterEUNoPostagePrice"); double diffDate = GetDiffDate(DateTime.Now, Convert.ToDateTime(entry.FAfterUnEffDate)); if (diffDate >= 50) { entrys.Add(entry); } } else { entrys.Add(entry); } } } } } } if (entrys != null && entrys.Count > 0) { string prefix = SQLUtils.GetAUB2BDropShipOrderPrefix(ctx); if (this.Direction == SynchroDirection.ToB2B) { return(entrys.Where(m => m != null && !string.IsNullOrWhiteSpace(m.F_HS_IsOil) && ((!m.F_HS_PRODUCTSTATUS.Equals("SPTC") && m.F_HS_IsOil.Equals("3")) || (m.FMaterialId.Length == 13 && m.FMaterialId.Substring(m.FMaterialId.Length - 3, 3).Equals(prefix))) ).ToList()); } else { return(entrys.Where(m => m != null && !m.FMaterialId.Substring(m.FMaterialId.Length - 3, 3).Equals(prefix) ).ToList()); } } return(null); }
public override void CreateNewData(BizDataEventArgs e) { base.CreateNewData(e); DynamicObjectCollection coll = GetObjects(this.Context, initSql); DynamicObjectType dtType = this.View.BusinessInfo.GetDynamicObjectType(); EntryEntity entity = (EntryEntity)this.View.BusinessInfo.GetEntity("F_HS_Entity"); DynamicObject objData = new DynamicObject(dtType); DynamicObject entityObj = null; if (coll != null && coll.Count > 0) { int seq = 1; foreach (var item in coll) { if (item != null) { if (entity != null) { entityObj = new DynamicObject(entity.DynamicObjectType); entity.DynamicProperty.GetValue <DynamicObjectCollection>(objData).Add(entityObj); entityObj["seq"] = seq; //entityObj["F_HS_Customer_Id"] = Convert.ToInt32(SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID")); //this.View.Model.SetValue("F_HS_Customer", SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID"),seq); //this.View.Model.DataObject["F_HS_Customer_Id"] = SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID"); BaseDataField orgId = this.View.BillBusinessInfo.GetField("F_HS_UseOrgId") as BaseDataField; DynamicObject orgObj = Kingdee.BOS.ServiceHelper.BusinessDataServiceHelper.LoadSingle(this.Context, SQLUtils.GetFieldValue(item, "F_HS_UseOrgId"), orgId.RefFormDynamicObjectType); entityObj["F_HS_UseOrgId"] = orgObj; BaseDataField cust = this.View.BillBusinessInfo.GetField("F_HS_Customer") as BaseDataField; DynamicObject bdObj = Kingdee.BOS.ServiceHelper.BusinessDataServiceHelper.LoadSingle(this.Context, SQLUtils.GetFieldValue(item, "F_HS_B2CCUSTID"), cust.RefFormDynamicObjectType); entityObj["F_HS_Customer"] = bdObj; entityObj["F_HS_ChangeAmount"] = SQLUtils.GetFieldValue(item, "changedAmount"); entityObj["F_HS_ChangedType"] = GetChangeTypeDesc(SQLUtils.GetFieldValue(item, "changedType")); entityObj["F_HS_TradeType"] = SQLUtils.GetFieldValue(item, "F_HS_TradeType"); entityObj["F_HS_ChangedCause"] = GetChangeCauseDesc(SQLUtils.GetFieldValue(item, "changedCause")); entityObj["F_HS_BalanceAmount"] = SQLUtils.GetFieldValue(item, "balanceAmount"); entityObj["F_HS_RateToUSA"] = SQLUtils.GetFieldValue(item, "F_HS_RateToUSA"); entityObj["F_HS_SettleCurrId_Id"] = SQLUtils.GetFieldValue(item, "FSETTLECURRID"); //this.View.Model.SetValue("F_HS_SettleCurrId", SQLUtils.GetFieldValue(item, "FSETTLECURRID"), seq); //BaseDataField curr = this.View.BusinessInfo.GetField("F_HS_SettleCurrId") as BaseDataField; //curr.RefIDDynamicProperty.SetValue(this.View.Model.DataObject, SQLUtils.GetFieldValue(item, "FSETTLECURRID")); //this.View.Model.DataObject["F_HS_SettleCurrId_Id"] = SQLUtils.GetFieldValue(item, "FSETTLECURRID"); //this.Model.SetItemValueByID("F_HS_SettleCurrId", 7, seq); //this.View.UpdateView("F_HS_SettleCurrId",seq); BaseDataField curr = this.View.BillBusinessInfo.GetField("F_HS_SettleCurrId") as BaseDataField; DynamicObject cdObj = Kingdee.BOS.ServiceHelper.BusinessDataServiceHelper.LoadSingle(this.Context, SQLUtils.GetFieldValue(item, "FSETTLECURRID"), curr.RefFormDynamicObjectType); entityObj["F_HS_SettleCurrId"] = cdObj; //BaseDataField fldCustomer = formMetadata.BusinessInfo.GetField("FMaterialId") as BaseDataField; //var materialObj = Kingdee.BOS.ServiceHelper.BusinessDataServiceHelper.LoadSingle(this.Context, materialId, fldMaterial.RefFormDynamicObjectType); //fldMaterial.RefIDDynamicProperty.SetValue(dynamicRow, materialId); //fldMaterial.DynamicProperty.SetValue(dynamicRow, materialObj); entityObj["F_HS_ChangedAmountUSD"] = SQLUtils.GetFieldValue(item, "changedAmountUSA"); entityObj["F_HS_BalanceAmountUSD"] = SQLUtils.GetFieldValue(item, "balanceAmountUSA"); entityObj["F_HS_CNYBalance"] = SQLUtils.GetFieldValue(item, "F_HS_CNYBalance"); entityObj["F_HS_UpdateTime"] = SQLUtils.GetFieldValue(item, "updateTime"); BaseDataField user = this.View.BillBusinessInfo.GetField("F_HS_UpdateUser") as BaseDataField; DynamicObject udObj = Kingdee.BOS.ServiceHelper.BusinessDataServiceHelper.LoadSingle(this.Context, SQLUtils.GetFieldValue(item, "updateUser"), user.RefFormDynamicObjectType); entityObj["F_HS_UpdateUser"] = udObj; entityObj["F_HS_BillNo"] = SQLUtils.GetFieldValue(item, "FBillNo"); entityObj["F_HS_EntryId"] = SQLUtils.GetFieldValue(item, "fentryID"); entityObj["F_HS_NeedFreezed"] = SQLUtils.GetFieldValue(item, "needfreezed") == "1" ? true : false; entityObj["F_HS_Remark"] = SQLUtils.GetFieldValue(item, "remark"); seq++; } } } e.BizDataObject = objData; } }
public void LoadCreatureNames() { CreatureNames = SQLUtils.GetCreatureNames(ConnectionString()); }
private void done_button_Click(object sender, RoutedEventArgs e) { Thread t = new Thread(solveAndStoreVts); /*Закрыть смену, списать использованный ресурс и занести готовые лампы на склад*/ void solveAndStoreVts() { done_button.Dispatcher.BeginInvoke(new System.Action(delegate() { string fact = Interaction.InputBox(Messages._PRODUCED_VT_FACT); DataRowView data_row = (DataRowView)processing_scores_dg.SelectedItem; /*Получаем ряд в таблице, содержащий № смены, если он выбран*/ try { string shiftValue = data_row.Row.ItemArray[0].ToString(); string markValue = data_row.Row.ItemArray[2].ToString(); string planValue = data_row.Row.ItemArray[5].ToString(); string factValue = data_row.Row.ItemArray[6].ToString(); /*Получаем ряд в таблице, содержащий чекбоксы*/ string checkd = data_row.Row.ItemArray[7].ToString(); /* Выбираем количество всех типов ресурса для изготовления ламп */ string allResourcesStr; SQLUtils.runQuery("SELECT count(*) AS all_resources FROM storage_contains", "all_resources", t1); allResourcesStr = t1.Content.ToString(); /*Записываем это количество как границу для цикла */ int allResources = int.Parse(allResourcesStr); /*Проверяет если процесс не был завершён (не стоит галка в таблице)*/ if (!(checkd.Equals("True"))) { bool is_number = int.TryParse(fact, out int n); if (fact != "") { if (is_number) { SQLUtils.runQuery(query.update("processing", "done = 1", "shift = " + shiftValue)); SQLUtils.runQuery(query.update("processing", "fact = " + fact, "shift = " + shiftValue)); /*Коллекция со списком коэффициентов затрат для каждого ресурса*/ ArrayList shiftCosts = new ArrayList(); /*Предопределённый нулевой элемент коллекции, так как в базе данных ресурсы считаются с 1*/ shiftCosts.Add("0.00"); for (int resCostId = 1; resCostId <= allResources; resCostId++) { string s; /*Выбирает единицы затраты каждого resource.id*/ SQLUtils.runQuery("SELECT costPerShift FROM resource WHERE id = " + resCostId, "costPerShift", t1); s = t1.Content.ToString(); /*Добавляем каждую {resCostId}-ую затрату в коллекцию выше*/ shiftCosts.Add(s); } for (int resId = 1; resId <= allResources; resId++) { /*Вычитаем затраченное количество со склада для каждого ресурса. Единицы затраты записаны в коллекцию*/ SQLUtils.runQuery("UPDATE storage_contains SET count = (count - (" + planValue + "*" + Convert.ToString(shiftCosts[resId]).Replace(",", ".") + ") / 100) WHERE resourceId = " + resId); } /*Прибавляем фактически изготовленную партию на склад*/ SQLUtils.runQuery("UPDATE vt JOIN processing ON vt.id = processing.vtId JOIN vt_lots ON vt.id = vt_lots.vtId SET vt_lots.count = (vt_lots.count + " + fact + ") WHERE processing.shift = " + shiftValue + " AND vt.mark = " + "\"" + markValue + "\""); t1.IsEnabled = true; debug_textbox.Text = "Смена №" + shiftValue + " закрыта.\n"; SQLUtils.showTable(VTDataGridQueries.processingQuery, VTManagerConfig.planCols, processing_scores_dg); } else { debug_textbox.Text = Messages._FACT_ABOVE_ZERO; } } else { debug_textbox.Text = Messages._FACT_NOT_SET; } } else { new VTManagerDialog(Messages._WARNING_MESSAGE, Messages._PROCESS_HAS_BEEN_SOLVED); } } catch (System.NullReferenceException) { debug_textbox.Text = Messages._NO_ENTRY_SELECTED; } })); } t.Name = "Идёт процесс закрытия смены..."; t.Start(); }
public DynamicObjectCollection GetObjects(Context ctx, string sql) { return(SQLUtils.GetObjects(ctx, sql)); }
/// <summary> /// 保存物流跟踪明细 /// </summary> /// <param name="ctx"></param> /// <returns></returns> private int SaveLogisticsTrace(Context ctx, List <DeliveryNotice> notices) { int count = 0; lock (objLock) { if (notices != null && notices.Count > 0) { foreach (var notice in notices) { if (notice != null) { if (notice.TraceEntry != null && notice.TraceEntry.Count > 0) { foreach (var trace in notice.TraceEntry) { string sql = string.Format(@"/*dialect*/ insert into {0} (FID,FEntryID,F_HS_SHIPMETHODS,F_HS_DELIDATE,F_HS_CARRYBILLNO,F_HS_QUERYURL,F_HS_IsTrack) select @FID as FID,@FEntryID as FEntryID,@F_HS_SHIPMETHODS as F_HS_SHIPMETHODS,@F_HS_DELIDATE as F_HS_DELIDATE,@F_HS_CARRYBILLNO as F_HS_CARRYBILLNO, @F_HS_QUERYURL as F_HS_QUERYURL,@F_HS_IsTrack as F_HS_IsTrack ", HSTableConst.HS_T_LogisTrack); try { var para = new List <SqlParam>(); para.Add(new SqlParam("@FID", KDDbType.Int32, GetFId(ctx, notice.FBillNo))); para.Add(new SqlParam("@FEntryID", KDDbType.Int32, GetPrimaryKey(ctx, HSTableConst.HS_T_LogisTrack, "FEntryID"))); para.Add(new SqlParam("@F_HS_SHIPMETHODS", KDDbType.Int32, SQLUtils.GetShipMethodId(ctx, "CN-FEDEX"))); para.Add(new SqlParam("@F_HS_DELIDATE", KDDbType.Date, DateTime.Now)); para.Add(new SqlParam("@F_HS_CARRYBILLNO", KDDbType.String, trace.F_HS_CarryBillNO)); para.Add(new SqlParam("@F_HS_QUERYURL", KDDbType.String, HSDeliveryNoticeConst.F_HS_QUERYURL)); para.Add(new SqlParam("@F_HS_IsTrack", KDDbType.String, "0")); count += ServiceHelper.GetService <IDBService>().Execute(ctx, sql, para); if (count > 0) { string sql_ = string.Format(@"/*dialect*/ insert into {0} (FID,FBILLNO,F_HS_CARRYBILLNO,F_HS_SHIPMETHODS,F_HS_DOCUMENTSTATUS) values ({1},'{2}','{3}','{4}','{5}')", HSTableConst.HS_T_LogisticsInfo, GetFId(ctx, notice.FBillNo), notice.FBillNo, "449044304137821", SQLUtils.GetShipMethodId(ctx, "CN-FEDEX"), LogisticsQuery.Query ); int count_ = DBUtils.Execute(ctx, sql_); } } catch (Exception ex) { this.View.ShowErrMessage("", "发货通知单单号【" + notice.FBillNo + "】物流跟踪明细更新失败" + System.Environment.NewLine + System.Environment.NewLine + ex.Message + ex.StackTrace); } } } } } } } //UpdateShipment(ctx,notices); return(count); }
/// <summary> /// 设备采集数据录入 /// </summary> /// <param name="tableName"></param> /// <param name="jdatas"></param> /// <returns></returns> public bool AddDataInfo(string tableName, List <JObject> jdatas) { try { StringBuilder sb = new StringBuilder(); for (int k = 0; k < jdatas.Count; k++) { StringBuilder fieldname = new StringBuilder(); StringBuilder fieldvalue = new StringBuilder(); //获得对象的所有字段名 var itemProperties = (jdatas[k]).Properties().ToList(); for (int i = 0; i < itemProperties.Count; i++) { //var v = item.Name + ":" + item.Value; switch (itemProperties[i].Value.Type) { case JTokenType.Integer: fieldvalue.AppendFormat(itemProperties[i].Value.ToString()); break; case JTokenType.Float: fieldvalue.AppendFormat(itemProperties[i].Value.ToString()); break; case JTokenType.String: fieldvalue.AppendFormat("'" + itemProperties[i].Value.ToString() + "'"); break; case JTokenType.Date: fieldvalue.AppendFormat("'{0}'", Convert.ToDateTime(itemProperties[i].Value).ToString("yyyy-MM-dd HH:mm:ss.fff")); break; case JTokenType.Null: //fieldvalue.AppendFormat(null); break; default: fieldvalue.AppendFormat("'" + itemProperties[i].Value.ToString() + "'"); break; } if (itemProperties[i].Value.Type != JTokenType.Null) { //fieldname.AppendFormat("\"" + itemProperties[i].Name + "\""); fieldname.AppendFormat(itemProperties[i].Name); if (i < itemProperties.Count - 1) { fieldname.AppendFormat(","); fieldvalue.AppendFormat(","); } } } sb.AppendFormat("insert into {0} ({1}) values ({2});", tableName, fieldname.ToString(), fieldvalue.ToString()); } this.dbHelper = new DBHelper(tempSQLCoonectStr); string sql = SQLUtils.genarateSQL(sb.ToString(), this.dbHelper.sqlConnectionType); int count = dbHelper.ExecuteNonQuery(CommandType.Text, sql); if (count > 0) { return(true); } else { return(false); } } catch (Exception e) { //日志处理 Loghelper.WriteErrorLog("设备采集数据录入失败", e); lr.AddLogInfo(e.ToString(), "设备采集数据录入失败", tableName, "Error"); //throw e; } return(false); }
private int GetFEntryId(Context ctx, int FId) { string sql = string.Format(@"/*dialect*/ select FEntryID from {0} where FID = {1}", HSTableConst.HS_T_LogisTrack, FId); return(Convert.ToInt32(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(ctx, sql, "FEntryID")))); }
/// <summary> /// 将需要同步的会员(用户)数据进行打包(单个客户) /// </summary> /// <param name="sourceData"></param> /// <param name="log"></param> /// <param name="operationType"></param> /// <returns></returns> public override JObject BuildSynchroDataJson(AbsSynchroDataInfo sourceData, SynchroLog log, SynOperationType operationType) { JObject root = new JObject(); root.Add("NeedUpDateFields", new JArray("")); root.Add("IsDeleteEntry", "false"); root.Add("SubSystemId", ""); root.Add("IsVerifyBaseDataField", "true"); if (operationType == SynOperationType.SAVE) { root.Add("Model", ConvertSynObjToJObj(sourceData, operationType)); return(root); } //数据更新时的Json格式 else { //更新单据时,表体信息必须填写明细表体的主键 K3CustomerInfo custData = sourceData as K3CustomerInfo; string sFCustId = string.Format(@"/*dialect*/ select FCUSTID from T_BD_CUSTOMER where FNumber = '{0}'", custData.FNumber); string sMainFCustId = string.Format(@"/*dialect*/ select FCUSTID from T_BD_CUSTOMER where FNumber = '{0}' and FUseOrgId != 1", custData.FNumber); int subFCustId = Convert.ToInt32(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(this.K3CloudContext, sMainFCustId, "FCUSTID"))); DynamicObjectCollection coll = SQLUtils.GetObjects(this.K3CloudContext, sFCustId); JArray model = new JArray(); if (coll.Count > 0) { foreach (var item in coll) { if (item["FCUSTID"] != null) { JObject baseData = ConvertSynObjToJObj(sourceData, operationType); baseData.Add("FCUSTID", Convert.ToInt32(JsonUtils.ConvertObjectToString(item["FCUSTID"]))); if (subFCustId != Convert.ToInt32(JsonUtils.ConvertObjectToString(item["FCUSTID"]))) { K3CustomerInfo soData = sourceData as K3CustomerInfo; baseData = ConvertSynObjToJObj(soData, operationType); baseData.Add("FCUSTID", Convert.ToInt32(JsonUtils.ConvertObjectToString(item["FCUSTID"]))); } model.Add(baseData); } } } root.Add("Model", model); return(root); } }
private int GetPrimaryKey(Context ctx, string tableName, string fieldName) { string sql = string.Format(@"/*dialect*/ select MAX(" + fieldName + ") " + fieldName + " from {0} ", tableName); return(Convert.ToInt32(JsonUtils.ConvertObjectToString(SQLUtils.GetObject(ctx, sql, fieldName))) + 1); }
/// <summary> /// 客户分配完后所做的操作,更新--提交--审核 /// </summary> /// <param name="ctx"></param> /// <param name="sourceDatas"></param> /// <param name="operationType"></param> /// <returns></returns> public JObject BuildSyschroDataJsonForAfterAllot(Context ctx, List <AbsSynchroDataInfo> sourceDatas, SynOperationType operationType) { JObject root = null; if (operationType == SynOperationType.SAVE) { root = new JObject(); root.Add("NeedUpDateFields", new JArray("")); root.Add("NeedReturnFields", new JArray("FNumber")); root.Add("IsDeleteEntry", "false"); root.Add("SubSystemId", ""); root.Add("IsVerifyBaseDataField", "true"); root.Add("Model", ConvertSynObjToJObj(sourceDatas, operationType)); return(root); } //数据更新时的Json格式 else if (operationType == SynOperationType.UPDATE) { //更新单据时,表体信息必须填写明细表体的主键 root = new JObject(); root.Add("NeedUpDateFields", new JArray("")); root.Add("NeedReturnFields", new JArray("FNumber")); root.Add("IsDeleteEntry", "false"); root.Add("SubSystemId", ""); root.Add("IsVerifyBaseDataField", "true"); if (sourceDatas != null) { if (sourceDatas.Count > 0) { JArray model = new JArray(); for (int i = 0; i < sourceDatas.Count; i++) { K3CustomerInfo custData = sourceDatas[i] as K3CustomerInfo; string sFCustId = string.Format(@"/*dialect*/ select FCUSTID,FUseOrgId from T_BD_CUSTOMER where FNumber = '{0}' and FUseOrgId = {1}", custData.FNumber, ORGID); DynamicObjectCollection coll = SQLUtils.GetObjects(this.K3CloudContext, sFCustId); if (coll.Count > 0) { foreach (var c in coll) { if (c["FCUSTID"] != null) { JObject baseData = ConvertSynObjToJObj(sourceDatas[i], operationType); baseData.Add("FCUSTID", Convert.ToInt32(JsonUtils.ConvertObjectToString(c["FCUSTID"]))); JObject FSELLER = new JObject(); FSELLER.Add("FNumber", string.IsNullOrEmpty(custData.FSELLER) ? "NA" : custData.FSELLER); baseData.Add("FSELLER", FSELLER); JObject FSALDEPTID = new JObject(); FSALDEPTID.Add("FNumber", string.IsNullOrEmpty(custData.FSALDEPTID) ? "BM000001" : custData.FSALDEPTID); baseData.Add("FSALDEPTID", FSALDEPTID); model.Add(baseData); } } } } root.Add("Model", model); return(root); } } } return(null); }
private List <K3SalOrderInfo> GetK3SalOrderInfos(List <DynamicObject> objs) { List <K3SalOrderInfo> orders = null; K3SalOrderInfo order = null; string message = string.Empty; if (objs != null && objs.Count > 0) { orders = new List <K3SalOrderInfo>(); foreach (var obj in objs) { if (obj != null) { string documentstatus = SQLUtils.GetFieldValue(obj, "DocumentStatus"); bool isSyn = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_YNSyncCollection")); DynamicObject oSrc = obj["F_HS_SaleOrderSource"] as DynamicObject; string oSource = SQLUtils.GetFieldValue(oSrc, "FNumber"); if (documentstatus.CompareTo("C") == 0 && !isSyn && oSource.CompareTo("HCWebProcessingOder") == 0) { if (Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_PayTotal")) > 0) { order = new K3SalOrderInfo(); order.FDate = Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "Date")); order.FBillNo = SQLUtils.GetFieldValue(obj, "BillNo"); DynamicObject cust = obj["CustId"] as DynamicObject; order.FCustId = SQLUtils.GetFieldValue(cust, "Number"); DynamicObject b2cCust = obj["F_HS_B2CCustId"] as DynamicObject; order.F_HS_B2CCustId = SQLUtils.GetFieldValue(b2cCust, "Number"); DynamicObject saler = obj["SalerId"] as DynamicObject; order.FSalerId = SQLUtils.GetFieldValue(saler, "Number"); DynamicObject saleOrg = obj["SaleOrgId"] as DynamicObject; order.FSaleOrgId = SQLUtils.GetFieldValue(saleOrg, "Number"); DynamicObject saleDept = obj["SaleDeptId"] as DynamicObject; order.FSaleDeptId = SQLUtils.GetFieldValue(saleDept, "Number"); DynamicObject pay = obj["F_HS_PaymentModeNew"] as DynamicObject; order.F_HS_PaymentModeNew = SQLUtils.GetFieldValue(pay, "FNumber"); order.F_HS_TransactionID = SQLUtils.GetFieldValue(obj, "F_HS_TransactionID"); order.F_HS_PayTotal = SQLUtils.GetFieldValue(obj, "F_HS_PayTotal"); order.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_RateToUSA")); K3SaleOrderFinance fin = GetK3SaleOrderFinance(obj); if (fin != null) { order.FSettleCurrId = fin.FSettleCurrID; } orders.Add(order); } else { message += "销售订单:【" + SQLUtils.GetFieldValue(obj, "BillNo") + "】的Pay_Total的金额小于零,不允许生成收款单!" + Environment.NewLine; } } else { message += "销售订单:【" + SQLUtils.GetFieldValue(obj, "BillNo") + "】收款单生成只能是processing和未生成收款单的销售订单" + Environment.NewLine; } } } } if (!string.IsNullOrWhiteSpace(message)) { _result = new HttpResponseResult(); _result.Message = message; _result.Success = false; } return(orders); }