package com.zy.third.erp.task;
|
|
import com.zy.asrs.entity.LocDetl;
|
import com.zy.asrs.entity.LocDetlChangelog;
|
import com.zy.asrs.mapper.LocDetlChangelogMapper;
|
import com.zy.common.service.erp.ErpSqlServer;
|
import com.alibaba.fastjson.JSON;
|
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.List;
|
import java.util.Map;
|
import java.util.HashMap;
|
|
@Slf4j
|
@Component
|
public class ERPLocDetlScheduler {
|
|
@Autowired
|
private ErpSqlServer erpSqlServer;
|
|
@Value("${erp.enabled}")
|
private Boolean erpEnabled;
|
|
@Autowired
|
private LocDetlChangelogMapper locDetlChangelogMapper;
|
|
@Transactional(rollbackFor = Throwable.class)
|
@Scheduled(cron = "${erp.refreshtime}")
|
public void LocDetlScheduler() {
|
if (!erpEnabled) return;
|
|
List<LocDetlChangelog> logs = locDetlChangelogMapper.selectUnprocessedLogs();
|
|
for (LocDetlChangelog changelog : logs) {
|
try {
|
String action = changelog.getActionType();
|
LocDetl item = parseJsonToItem(changelog.getData());
|
Map<String, Object> content = locDetlToMap(item);
|
|
switch (action) {
|
case "INSERT":
|
String insertSql = buildInsertSql("asr_loc_detl", content);
|
erpSqlServer.update(insertSql);
|
log.info("同步ERP新增成功: locNo={}", item.getLocNo());
|
break;
|
|
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 asr_loc_detl SET " + setSql + " WHERE loc_no='" + escapeSql((String)afterMap.get("loc_no")) + "' AND zpallet='" + escapeSql((String)afterMap.get("zpallet")) + "'";
|
erpSqlServer.update(updateSql);
|
|
log.info("同步ERP更新成功: locNo={}, zpallet={}", afterMap.get("loc_no"), afterMap.get("zpallet"));
|
} catch (Exception e) {
|
log.error("更新ERP失败: locNo=" + changelog.getLocNo(), e);
|
}
|
|
case "DELETE":
|
String deleteSql = "DELETE FROM asr_loc_detl WHERE loc_no='" + escapeSql(item.getLocNo()) + "'";
|
erpSqlServer.update(deleteSql);
|
log.info("同步ERP删除成功: locNo={}", item.getLocNo());
|
break;
|
|
default:
|
log.error("未知操作类型：" + action);
|
}
|
|
locDetlChangelogMapper.markProcessed(changelog.getId());
|
|
} catch (Exception e) {
|
log.error("同步ERP失败，日志ID：" + changelog.getId(), e);
|
}
|
}
|
}
|
|
/** JSON 转实体 */
|
private LocDetl parseJsonToItem(String dataJson) {
|
return JSON.parseObject(dataJson, LocDetl.class);
|
}
|
|
/** 实体转 Map */
|
private Map<String, Object> locDetlToMap(LocDetl item) {
|
Map<String, Object> map = new HashMap<>();
|
map.put("loc_no", item.getLocNo());
|
map.put("zpallet", item.getZpallet());
|
map.put("anfme", item.getAnfme());
|
map.put("matnr", item.getMatnr());
|
map.put("maktx", item.getMaktx());
|
map.put("batch", item.getBatch());
|
map.put("order_no", item.getOrderNo());
|
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("modi_user", item.getModiUser());
|
map.put("modi_time", item.getModiTime());
|
map.put("appe_user", item.getAppeUser());
|
map.put("appe_time", item.getAppeTime());
|
map.put("memo", item.getMemo());
|
map.put("store_date", item.getStoreDate());
|
map.put("out_order_no", item.getOutOrderNo());
|
map.put("lu_hao", item.getLuHao());
|
map.put("temp1", item.getTemp1());
|
map.put("temp2", item.getTemp2());
|
map.put("temp3", item.getTemp3());
|
map.put("pro_type", item.getProType());
|
map.put("packing", item.getPacking());
|
map.put("ware_id", item.getWareId());
|
map.put("ware_name", item.getWareName());
|
map.put("i_no", item.getINo());
|
return map;
|
}
|
|
/** 拼接 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(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;
|
}
|
|
/** 转换为 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("'", "''");
|
}
|
}
|
