跨平台数据对接

The First:

C6读取Kingdee database 的表t_rp_PayApplyBill (没有从表数据传递)字段读取主表数据库语句：SELECT top 1 'EBF'+REPLICATE('0',8-LEN((v1.FBillID-9594)))+CAST((v1.FBillID-9594) as varchar) FMainID,v1.FNumber FBillNo,v1.FBillID,'' FUserID,t1.FName FEmper,'' FFDeptID,'' FDeptName,v1.FDate,v1.FNumber,v1.FText,t3.FName FCurrency,v1.FExplanation,t8.FName_CHS FSourbilltype,v1.FRecClientName_cn,case when V1.FComboBox4=1 THEN v1.FRPBank ELSE t4.F_118 END FBank, t4.FName FCustomer,case when V1.FComboBox4=1 then t4.FAccount else t4.F_117 end FAccount,t5.FName FSettle,v1.FDecimal,t2.FName FDeptName,ISNULL(v1.famountFor,0)famountFor, dbo.lm_L2U(v1.famountFor,0)FMoneyMAX,v1.FTransDate_CN,'',v1.FSettleNo,t7.FName FBiller,GETDATE()FBillTime,v1.FExchangerate,v1.FApproveAmount,case V1.FPayType WHEN '1' THEN '购货款' WHEN '2' then '预付款' WHEN '3' THEN '应收退款' WHEN '4' THEN '费用报销' WHEN '5' THEN '费用借款' WHEN '6' THEN '其他付款' ELSE '费用预付款' end FPayType,v1.FComboBox1,v1.FInteger,v1.FDecimal1 FROM dbo.t_rp_PayApplyBill V1 LEFT JOIN dbo.t_rp_PayApplyBillEntry u1 ON u1.FBillID=v1.FBillID LEFT JOIN dbo.t_Emp t1 ON v1.FEmployee=t1.FItemID LEFT JOIN dbo.t_Department t2 ON v1.FDepartment=t2.FItemID LEFT JOIN dbo.t_Currency t3 ON v1.FCurrencyID=t3.FCurrencyID LEFT JOIN dbo.t_Supplier t4 ON  v1.FCustomer=t4.FItemID LEFT JOIN dbo.t_Settle t5 ON v1.FSettle=t5.FItemID LEFT JOIN dbo.t_User t7 ON v1.FPreparer=t7.FUserID LEFT JOIN dbo.ICClassType t8 ON u1.FClassID_SRC=t8.FID  where v1.FBillID=%S AND t1.FName <>'administrator'and v1.FComboBox <>'Y' and t7.FName is not null and (v1.FComboBox1 = '非项目类' OR v1.FComboBox1 = '特殊类（设备质保金、零星工程及发包）') GROUP BY v1.FNumber,v1.FBillID,t1.FName,v1.FDate,v1.FNumber,v1.FText,t3.FName ,v1.FExplanation,t8.FName_CHS ,v1.FRecClientName_cn,v1.FRPBank,t4.FName , t4.FAccount , t4.F_117 ,t5.FName ,v1.FDecimal,t2.FName ,v1.famountFor, v1.famountFor,v1.FTransDate_CN,v1.FSettleNo,t7.FName,t4.F_118,v1.FExchangerate,v1.FApproveAmount,v1.FComboBox4,v1.FComboBox1,FPayType,V1.FInteger,v1.FDecimal1 order by v1.FBillID 

读取读取Kingdee database 的表t_rp_PayApplyBill 字段值到C6的database表PaymentApp(对应赋值的表PaymentApp字段名)中：

FMainID,FBillNO,FK3ID,FUserID,FUserName,FDeptID,FDeptName,FDate,FNumber,FContractNo,FCurrency,FPayReason,FClassID_SRC,FSKUnit,FRPBank,FOrgType,FBankAcct,FSettle,FRatio,FPayClientName_cn,FMoneyMIN,FMoneyMAX,FAppDate,FInsDate,FNote,FBiller,FBillTime,FBB,FJEBWB,FFKLX,FFKXM,FSQCS,FLJJE

The Second:

C6读取Kingdee database 的表t_SelfOrder（有从表数据传递）字段读取主表数据库语句：

select 'COF'+REPLICATE('0',8-LEN((v1.FID-51959)))+CAST((v1.FID-51959) as varchar) FMainID,v1.FID,FBillNo,'',t1.FName,isnull(t2.FItemID,''),t2.FName,FDate,FBillNo,t3.FName,t4.FName,t5.FName FSettleID,FAmount1,FCustomerType,t6.FName,v1.FExchangeRate,0,0,0,0,v1.FExplanation1,v1.FText,t7.FName FBiller,GETDATE() FBillTime,'' FISO,'' FVer,2,0,v1.FAmount4 from t_SelfOrder v1 left join t_User t1 on v1.FBillerID=t1.FUserID left join t_Department t2 on v1.FDepartment=t2.FItemID left join t_Organization t3 on v1.FCustomer=t3.FitemID left join t_Emp t4 on t4.FItemID=v1.FEmployee left join t_Settle t5 on t5.FItemID=FBase1 left join t_Currency t6 on t6.FCurrencyID=v1.FCurrencyID left join t_Emp t7 on t7.FItemID=v1.FBase 

where v1.FID=%S and t1.FName<>'Administrator' and v1.FDepartment = 107137 order by v1.FID

读取Kingdee从表：

select v1.FID,v1.FIndex,v1.FEntryID,'COF'+REPLICATE('0',8-LEN((v1.FID-51959)))+CAST((v1.FID-51959) as varchar),t1.FNumber,t1.FName,v1.FQuantity,t2.FName,v1.FSecQty,FFactPriceFor,v1.FAmountFor,v1.FContractQty,v1.FExplanation,v2.FReceiveDate,isnull(v2.FAmount,0),v1.FDate2 from t_SelfOrderEntry v1 left join t_SelfOrder vv1 on v1.fid=vv1.FID left join t_User tt1 on vv1.FBillerID=tt1.FUserID left  join t_SelfOrderScheme v2 on v1.FID=v2.FID and v1.FIndex=v2.FIndex  left join t_ICItem t1 on v1.FProductID=t1.FItemID left join t_MeasureUnit t2 on t2.FMeasureUnitID=v1.FUnitID 

where v1.FID=%S and tt1.FName<>'Administrator' and vv1.FDepartment = 107137

读取读取Kingdee database 的表t_SelfOrder 字段值到C6的database表SalesOrder(对应赋值的表SalesOrder字段名)中：

FMainID,FK3ID,FBillNO,FUserID,FUserName,FDeptID,FDeptName,FDate,FOrderBillNO,FCustName,FEmp,FSettle,FSubscription,FOrgType,FCurrency,FExchangeRate,FAllQty,FAllNUM,FAllMoney,FRecMoney,FDigest,FNote,FBiller,FBillTime,FISONO,FVer,FAppFlag,FIfWriteBack,FPreSubscription

C6从表：

FK3ID,FK3Index,FK3EntryID,FMainID,FNumber,FName,FQty,FUnit,FNum,FPrice,FMoney,FZxNum,FNote,FRecdate,FRecmoney,FConsignDate

PS:

FSchemeSQL（Kingdee）:

select v1.FID,v1.FIndex,'COF'+REPLICATE('0',8-LEN((v1.FID-51959)))+CAST((v1.FID-51959) as varchar),v1.FReceiveDate,v1.FAMount from t_SelfOrderScheme v1 left join t_SelfOrder vv1 on v1.fid=vv1.FID left join t_User tt1 on vv1.FBillerID=tt1.FUserID where v1.FID=%S and tt1.FName<>'Administrator' and vv1.FDepartment  = 107137

FC6SchemeCol (C6):

FK3ID,FIndex,FMainID,FReceiveDate,FAMount 

C6 更新 Kingdee数据库数据：

select SQL:

Select FK3ID,FMainID,FUserID From lm306_SalesOrder   Where FIfWriteBack = 0 and FEND='1'

更新SQL:

update t_SelfOrder %S  Where FID = %S 

The Last:

FLastID如何获取