泛微e9 OA中通过Java SDK获取金蝶云星空中的表单数据 - ToB企服应用市场:ToB评测及商务社交产业平台

package com.customization.suppy.cron;
import com.customization.suppy.pojo.Appayable;
import com.google.gson.*;
import com.kingdee.bos.webapi.sdk.K3CloudApi;
import org.junit.Test;
import weaver.conn.RecordSet;
import weaver.formmode.data.ModeDataIdUpdate;
import weaver.formmode.setup.ModeRightInfo;
import weaver.interfaces.schedule.BaseCronJob;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class SyncAPPayableCron extends BaseCronJob {
K3CloudApi client = new K3CloudApi();
private static final Gson GSON = new Gson();
HashMap<Integer, String> supplierMap = new HashMap<>();
HashMap<Integer, String> unitNameMap = new HashMap<>();
HashMap<Integer, String> materialMap = new HashMap<>();
HashMap<Integer, String> orderMap = new HashMap<>();
/**
* 定时任务
*/
@Override
public void execute() {
// SYN_PERSON_TABLE_NAME 建模表名
String SYN_PERSON_TABLE_NAME = "uf_AP_Payable";
// SYN_PERSON_FLOW_ID 模块id
int SYN_PERSON_FLOW_ID = 14;
RecordSet recordSet = new RecordSet();
try {
// 首先获取所有的数据
List<Appayable> apPayableList = getAPPayableList();
// 遍历list，通过FBillNo来判断
for (Appayable appayable : apPayableList) {
if (appayable.getFBillNo().equals(" "))continue;
String sql = "select id from uf_AP_Payable where FBillNo= '"+appayable.getFBillNo()+"';";
recordSet.executeQuery(sql);
if (recordSet.next()) {
String mainid = recordSet.getString("id");
// 代表是重复的数据，再查询明细表，将数据插入到明细表
String sql_detail = "select id from uf_AP_Payable_dt1 where FMATERIALID='"+appayable.getFMATERIALID()+"'";
recordSet.executeQuery(sql_detail);
if (recordSet.next()) {
// 代表此物料编码已经存在了，直接跳过该条数据
} else {
// 代表需要插入明细表
String insertSql = "INSERT INTO uf_AP_Payable_dt1 (mainid, FBillNo, FMATERIALID, FMaterialDesc, FModel, FPRICEUNIT,FPriceQty,FTaxPrice,FALLAMOUNTFOR_D) " +
"VALUES (" + mainid + ", '" + appayable.getFOrderNumber() + "', '" + appayable.getFMATERIALID() + "','" + appayable.getFMaterialDesc() + "','" + appayable.getFModel() + "','" + appayable.getFPRICEUNIT() + "'," + appayable.getFPriceQty() + "," + appayable.getFTaxPrice() + "," + appayable.getFALLAMOUNTFOR_D() + ")";
recordSet.executeUpdate(insertSql);
}
} else {
// 新增建模表，然后直接将所有数据更新到建模表当中。
// 获取现在的时间
LocalDate currentDate = LocalDate.now();
LocalTime currentTime = LocalTime.now();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH-mm-ss");
String _currentTime = currentTime.format(formatter);
// 通过代码创建一条建模数据
int id = ModeDataIdUpdate.getInstance().getModeDataNewIdByUUID(SYN_PERSON_TABLE_NAME, SYN_PERSON_FLOW_ID, 1, 1, currentDate.toString(), _currentTime);
ModeRightInfo ModeRightInfo = new ModeRightInfo();
ModeRightInfo.setNewRight(true);
// 新建的时候添加共享
ModeRightInfo.editModeDataShare(1, SYN_PERSON_FLOW_ID, id);
// 新建的时候添加文档共享
ModeRightInfo.addDocShare(1, SYN_PERSON_FLOW_ID, id);
// 调整主表数据
String updateSql = "UPDATE uf_AP_Payable " +
" SET FBillNo = '" + appayable.getFBillNo() + "', " +
" FSUPPLIER = '" + appayable.getFSUPPLIER() + "', " +
" FENDDATE_H = '" + appayable.getFENDDATE_H() + "', " +
" FDATE = '" + appayable.getFDATE() + "', " +
" FEntryTaxRate = " + appayable.getFEntryTaxRate() +
" WHERE id = " + id;
recordSet.executeUpdate(updateSql);
// 调整明细表数据
String insertSql = "INSERT INTO uf_AP_Payable_dt1 (mainid, FBillNo, FMATERIALID, FMaterialDesc, FModel, FPRICEUNIT,FPriceQty,FTaxPrice,FALLAMOUNTFOR_D) " +
"VALUES (" + id + ", '" + appayable.getFOrderNumber() + "', '" + appayable.getFMATERIALID() + "','" + appayable.getFMaterialDesc() + "','" + appayable.getFModel() + "','" + appayable.getFPRICEUNIT() + "'," + appayable.getFPriceQty() + "," + appayable.getFTaxPrice() + "," + appayable.getFALLAMOUNTFOR_D() + ")";
recordSet.executeUpdate(insertSql);
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 获取所有的供应商名称并且将其传入 supplierMap 当中
*/
public void getAllSupplier() throws Exception {
// 定义每页获取的数据条数，这里可根据接口最大允许值设置（例如10000）
int limit = 10000;
// 从第0条数据开始读取
int startRow = 0;
while (true) {
JsonObject params = new JsonObject();
params.addProperty("FormId", "BD_Supplier");
params.addProperty("FieldKeys", "FSUPPLIERID,FName");
params.addProperty("StartRow", startRow);
params.addProperty("Limit", limit);
params.addProperty("IsSortBySeq", false);
String jsonData = GSON.toJson(params);
// 调用接口获取一页数据
List<List<Object>> result = client.executeBillQuery(jsonData);
if (result == null || result.isEmpty()) {
// 当返回结果为空时，说明所有数据已经读取完毕，退出循环
break;
}
for (List<Object> obj : result) {
supplierMap.put((Integer) obj.get(0), (String) obj.get(1));
}
startRow += result.size();
}
}
/**
* 获取所有的计量单位并且存在 unitNameMap 当中
*/
public void getAllUnitName() throws Exception {
// 定义每页获取的数据条数，这里可根据接口最大允许值设置（例如10000）
int limit = 10000;
// 从第0条数据开始读取
int startRow = 0;
while (true) {
JsonObject params = new JsonObject();
params.addProperty("FormId", "BD_UNIT");
params.addProperty("FieldKeys", "FUNITID,FName");
params.addProperty("StartRow", startRow);
params.addProperty("Limit", limit);
params.addProperty("IsSortBySeq", false);
String jsonData = GSON.toJson(params);
// 调用接口获取一页数据
List<List<Object>> result = client.executeBillQuery(jsonData);
if (result == null || result.isEmpty()) {
// 当返回结果为空时，说明所有数据已经读取完毕，退出循环
break;
}
for (List<Object> obj : result) {
unitNameMap.put((Integer) obj.get(0), (String) obj.get(1));
}
startRow += result.size();
}
}
/**
* 获取所有的物料信息，然后将物料的id转成物料的编码
*/
public void getAllMaterial() throws Exception {
// 定义每页获取的数据条数，这里可根据接口最大允许值设置（例如10000）
int limit = 10000;
// 从第0条数据开始读取
int startRow = 0;
while (true) {
JsonObject params = new JsonObject();
params.addProperty("FormId", "BD_MATERIAL");
params.addProperty("FieldKeys", "FMATERIALID,FNumber");
params.addProperty("StartRow", startRow);
params.addProperty("Limit", limit);
params.addProperty("IsSortBySeq", false);
String jsonData = GSON.toJson(params);
// 调用接口获取一页数据
List<List<Object>> result = client.executeBillQuery(jsonData);
if (result == null || result.isEmpty()) {
// 当返回结果为空时，说明所有数据已经读取完毕，退出循环
break;
}
for (List<Object> obj : result) {
materialMap.put((Integer) obj.get(0), (String) obj.get(1));
}
startRow += result.size();
}
}
/**
* 存采购明细内码和采购订单号
*/
public void getAllOrderNumber() throws Exception {
// 定义每页获取的数据条数，这里可根据接口最大允许值设置（例如10000）
int limit = 10000;
// 从第0条数据开始读取
int startRow = 0;
while (true) {
JsonObject params = new JsonObject();
params.addProperty("FormId", "PUR_PurchaseOrder");
params.addProperty("FieldKeys", "FPOOrderEntry_FEntryID,FPURCHASEORDERNO");
params.addProperty("StartRow", startRow);
params.addProperty("Limit", limit);
params.addProperty("IsSortBySeq", false);
String jsonData = GSON.toJson(params);
// 调用接口获取一页数据
List<List<Object>> result = client.executeBillQuery(jsonData);
if (result == null || result.isEmpty()) {
// 当返回结果为空时，说明所有数据已经读取完毕，退出循环
break;
}
for (List<Object> obj : result) {
orderMap.put((Integer) obj.get(0), (String) obj.get(1));
}
startRow += result.size();
}
}
/**
* 获取金蝶中应付单的完整数据
*/
public List<Appayable> getAPPayableList() throws Exception {
getAllSupplier();
getAllUnitName();
getAllMaterial();
getAllOrderNumber();
K3CloudApi client = new K3CloudApi();
Gson gson = new Gson();
ArrayList<Appayable> appayables = new ArrayList<>();
// 定义每页获取的数据条数，这里可根据接口最大允许值设置（例如10000）
int limit = 10000;
// 从第0条数据开始读取
int startRow = 0;
while (true) {
// 构建请求的 JSON 对象
JsonObject jsonObject = new JsonObject();
jsonObject.addProperty("FormId", "AP_Payable");
jsonObject.addProperty("FieldKeys", "FBillNo,FSUPPLIERID,FENDDATE_H,FDATE,FEntryTaxRate,FOrderEntryID,FMATERIALID,FMaterialDesc,FModel,FPRICEUNITID,FPriceQty,FTaxPrice,FALLAMOUNTFOR_D");
jsonObject.addProperty("FilterString", "");
jsonObject.addProperty("OrderString", "");
jsonObject.addProperty("TopRowCount", 0);
jsonObject.addProperty("StartRow", startRow);
jsonObject.addProperty("Limit", limit);
jsonObject.addProperty("SubSystemId", "");
String jsonData = gson.toJson(jsonObject);
// 调用接口获取一页数据
List<List<Object>> result = client.executeBillQuery(jsonData);
if (result == null || result.isEmpty()) {
// 当返回结果为空时，说明所有数据已经读取完毕，退出循环
break;
}
// 处理当前页数据
for (List<Object> obj : result) {
Appayable appayable = new Appayable();
// 注意：此处的类型转换需要与实际返回的数据类型相符
appayable.setFBillNo((String) obj.get(0));
appayable.setFSUPPLIER(supplierMap.get((Integer) obj.get(1)));
appayable.setFENDDATE_H(((String) obj.get(2)).substring(0,10));
appayable.setFDATE(((String) obj.get(3)).substring(0,10));
appayable.setFEntryTaxRate((Double) obj.get(4));
appayable.setFOrderNumber(orderMap.get((Integer)obj.get(5)));
appayable.setFMATERIALID(materialMap.get((Integer) obj.get(6)));
appayable.setFMaterialDesc((String) obj.get(7));
appayable.setFModel((String) obj.get(8));
appayable.setFPRICEUNIT(unitNameMap.get((Integer) obj.get(9)));
appayable.setFPriceQty((Double) obj.get(10));
appayable.setFTaxPrice((Double) obj.get(11));
appayable.setFALLAMOUNTFOR_D((Double) obj.get(12));
appayables.add(appayable);
}
// 累加已读取的行数，准备读取下一页
startRow += result.size();
}
return appayables;
}
/* @Test
public void test() throws Exception {
List<Appayable> apPayableList = getAPPayableList();
for (Appayable appayable : apPayableList) {
System.out.println(appayable);
}
}*/
}

复制代码