package com.zy.third.erp.task;
|
|
import com.alibaba.fastjson.JSON;
|
import com.baomidou.mybatisplus.mapper.EntityWrapper;
|
import com.core.exception.CoolException;
|
import com.zy.asrs.entity.LocDetl;
|
import com.zy.asrs.entity.LocDetlChangelog;
|
import com.zy.asrs.entity.OrderDetl;
|
import com.zy.asrs.entity.OrderDetlChangelog;
|
import com.zy.asrs.mapper.LocDetlChangelogMapper;
|
import com.zy.asrs.mapper.OrderDetlChangelogMapper;
|
import com.zy.asrs.service.OrderDetlService;
|
import com.zy.common.service.erp.ErpSqlServer;
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.scheduling.annotation.Scheduled;
|
import org.springframework.stereotype.Component;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Slf4j
|
@Component
|
public class ERPOrderDetlScheduler {
|
@Autowired
|
private ErpSqlServer erpSqlServer;
|
@Autowired
|
private OrderDetlService orderDetlService;
|
@Autowired
|
private OrderDetlChangelogMapper OrderDetlChangelogMapper;
|
@Value("${erp.enabled}")
|
private Boolean erpEnabled;
|
@Transactional(rollbackFor = Throwable.class)
|
@Scheduled(cron = "0 */30 * * * ?")
|
public void ERPOrderDetlScheduler() {
|
if (!erpEnabled) return;
|
boolean result = false;
|
List<OrderDetlChangelog> logs = OrderDetlChangelogMapper.selectUnprocessedLogs();
|
List<OrderDetl> orderDetlList = orderDetlService.selectList(new EntityWrapper<>()); // 获取主表所有数据
|
|
for (OrderDetl item : orderDetlList) {
|
try {
|
// 根据唯一标识 (order_no, matnr, out_order_no) 检查副表是否已有数据
|
boolean exists = checkIfExistsInErp(item);
|
if (!exists) {
|
// 如果副表中没有记录，插入新数据
|
insertIntoErp(item);
|
}
|
} catch (Exception e) {
|
log.error("处理ERP同步失败，OrderDetl ID：" + item.getOrderNo(), e);
|
}
|
}
|
for (OrderDetlChangelog changelog : logs) {
|
try {
|
String action = changelog.getActionType();
|
OrderDetl item = parseJsonToItem(changelog.getData());
|
Map<String, Object> content = locDetlToMap(item);
|
|
switch (action) {
|
case "UPDATE":
|
try {
|
// 解析 JSON
|
Map<String, Object> dataMap = JSON.parseObject(changelog.getData(), Map.class);
|
Map<String, Object> afterMap = (Map<String, Object>) dataMap.get("after");
|
// 拼接 UPDATE SQL
|
StringBuilder setSql = new StringBuilder();
|
afterMap.forEach((k, v) -> {
|
if (v == null) {
|
setSql.append("[").append(k).append("]=NULL,");
|
} else if (v instanceof Number) {
|
setSql.append("[").append(k).append("]=").append(v).append(",");
|
} else {
|
setSql.append("[").append(k).append("]='").append(((String)v).replace("'", "''")).append("',");
|
}
|
});
|
setSql.deleteCharAt(setSql.length() - 1); // 去掉最后逗号
|
String updateSql =
|
"UPDATE man_order_detl_erp SET " + setSql +
|
" WHERE order_no='" + escapeSql((String)afterMap.get("order_no")) +
|
"' AND out_order_no='" + escapeSql((String)afterMap.get("out_order_no")) +
|
"' AND matnr='" + escapeSql((String)afterMap.get("matnr")) + "'";
|
if(erpSqlServer.update(updateSql) > 0){
|
log.info("同步ERP更新成功: orderNo={}, matnr={}", afterMap.get("order_no"), afterMap.get("matnr"));
|
result = true;
|
}else{
|
log.info("同步ERP更新失败: orderNo={}, matnr={}", afterMap.get("order_no"), afterMap.get("matnr"));
|
}
|
} catch (Exception e) {
|
log.error("更新ERP失败: locNo=" + changelog.getOrderNo(), e);
|
}
|
break;
|
case "DELETE":
|
String deleteSql = "DELETE FROM man_order_detl_erp WHERE " +
|
"order_no='" + escapeSql(item.getOrderNo()) + "' AND " +
|
"out_order_no='" + escapeSql(item.getOutOrderNo()) + "' AND " +
|
"matnr='" + escapeSql(item.getMatnr()) + "' AND " +
|
"anfme=" + item.getAnfme();
|
if(erpSqlServer.update(deleteSql) > 0){
|
log.info("同步ERP删除成功: orderNo={},matnr={}", item.getOrderNo(),item.getMatnr());
|
result = true;
|
}else{
|
log.info("同步ERP删除失败: orderNo={},matnr={}", item.getOrderNo(),item.getMatnr());
|
}
|
break;
|
default:
|
log.error("未知操作类型：" + action);
|
}
|
if(result){
|
OrderDetlChangelogMapper.markProcessed(changelog.getId());
|
}else {
|
throw new CoolException("同步失败");
|
}
|
} catch (Exception e) {
|
log.error("同步ERP失败，日志ID：" + changelog.getId(), e);
|
}
|
}
|
}
|
/**
|
* 检查副表 man_order_detl_erp 是否已经存在此记录
|
*/
|
private boolean checkIfExistsInErp(OrderDetl item) {
|
String checkSql = "SELECT * FROM man_order_detl_erp WHERE order_no = '" + escapeSql(item.getOrderNo()) + "' AND " +
|
"out_order_no = '" + escapeSql(item.getOutOrderNo()) + "' AND " +
|
"matnr = '" + escapeSql(item.getMatnr()) + "'";
|
List<Map<String, Object>> result = erpSqlServer.select(checkSql); // 使用 ErpSqlServer 查询副表是否已有记录
|
if (result != null && !result.isEmpty()) {
|
return result.size() > 0;
|
}
|
return false;
|
}
|
|
/**
|
* 插入数据到副表 man_order_detl_erp
|
*/
|
private void insertIntoErp(OrderDetl item) {
|
Map<String, Object> content = locDetlToMap(item); // 将主表实体转换为Map
|
String insertSql = buildInsertSql("man_order_detl_erp", content);
|
if (erpSqlServer.update(insertSql) > 0) {
|
log.info("同步ERP插入成功: orderNo={}, matnr={}", item.getOrderNo(), item.getMatnr());
|
} else {
|
log.info("同步ERP插入失败: orderNo={}, matnr={}", item.getOrderNo(), item.getMatnr());
|
}
|
}
|
|
/**
|
* 拼接 INSERT SQL
|
*/
|
private String buildInsertSql(String tableName, Map<String, Object> content) {
|
StringBuilder columns = new StringBuilder();
|
StringBuilder values = new StringBuilder();
|
content.forEach((k, v) -> {
|
columns.append("[").append(k).append("],");
|
values.append(toSqlValue(v)).append(",");
|
});
|
columns.deleteCharAt(columns.length() - 1); // 去掉最后逗号
|
values.deleteCharAt(values.length() - 1); // 去掉最后逗号
|
|
return "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")";
|
}
|
/** 拼接 UPDATE SQL */
|
private String buildUpdateSql(String tableName, Map<String, Object> content, String whereClause) {
|
StringBuilder setSql = new StringBuilder();
|
content.forEach((k, v) -> {
|
setSql.append(k).append("=").append(toSqlValue(v)).append(",");
|
});
|
setSql.deleteCharAt(setSql.length() - 1);
|
return "UPDATE " + tableName + " SET " + setSql + " WHERE " + whereClause;
|
}
|
/** JSON 转实体 */
|
private OrderDetl parseJsonToItem(String dataJson) {
|
return JSON.parseObject(dataJson, OrderDetl.class);
|
}
|
/** 转换为 SQL 可用的值 */
|
private String toSqlValue(Object value) {
|
if (value == null) return "NULL";
|
if (value instanceof String) return "'" + escapeSql((String) value) + "'";
|
if (value instanceof Date) return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value) + "'";
|
return value.toString();
|
}
|
|
/** SQL 注入简单转义 */
|
private String escapeSql(String str) {
|
if (str == null) return "";
|
return str.replace("'", "''");
|
}
|
/** 实体转 Map */
|
private Map<String, Object> locDetlToMap(OrderDetl item) {
|
Map<String, Object> map = new HashMap<>();
|
map.put("order_no", item.getOrderNo());
|
map.put("out_order_no", item.getOutOrderNo());
|
map.put("matnr", item.getMatnr());
|
map.put("anfme",item.getAnfme());
|
map.put("qty",item.getQty());
|
map.put("maktx", item.getMaktx());
|
map.put("batch", item.getBatch());
|
map.put("specs", item.getSpecs());
|
map.put("model", item.getModel());
|
map.put("color", item.getColor());
|
map.put("brand", item.getBrand());
|
map.put("unit", item.getUnit());
|
map.put("price", item.getPrice());
|
map.put("sku", item.getSku());
|
map.put("units", item.getUnits());
|
map.put("barcode", item.getBarcode());
|
map.put("origin", item.getOrigin());
|
map.put("manu", item.getManu());
|
map.put("manu_date", item.getManuDate());
|
map.put("item_num", item.getItemNum());
|
map.put("safe_qty", item.getSafeQty());
|
map.put("weight", item.getWeight());
|
map.put("length", item.getLength());
|
map.put("volume", item.getVolume());
|
map.put("three_code", item.getThreeCode());
|
map.put("supp", item.getSupp());
|
map.put("supp_code", item.getSuppCode());
|
map.put("be_batch", item.getBeBatch());
|
map.put("dead_time", item.getDeadTime());
|
map.put("dead_warn", item.getDeadWarn());
|
map.put("source", item.getSource());
|
map.put("inspect", item.getInspect());
|
map.put("danger", item.getDanger());
|
map.put("status", item.getStatus());
|
map.put("create_by", item.getCreateBy());
|
map.put("create_time", item.getCreateTime());
|
map.put("update_by", item.getUpdateBy());
|
map.put("update_time", item.getUpdateTime());
|
map.put("memo", item.getMemo());
|
map.put("work_qty", item.getWorkQty());
|
map.put("auto_id", item.getAutoId());
|
map.put("lu_hao", item.getLuHao());
|
map.put("temp1", item.getTemp1());
|
map.put("temp2", item.getTemp2());
|
map.put("temp3", item.getTemp3());
|
map.put("ware_id", item.getWareId());
|
map.put("ware_name", item.getWareName());
|
map.put("packing", item.getPacking());
|
map.put("i_no", item.getINo());
|
map.put("pro_type", item.getProType());
|
return map;
|
}
|
|
}
|
