package com.product.data.center.service;
|
|
import cn.hutool.core.util.ArrayUtil;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.data.center.config.CmnConst;
|
import com.product.data.center.config.ErrorCode;
|
import com.product.data.center.entity.MoBaseEntity;
|
import com.product.datasource.dao.Dao;
|
import com.product.datasource.entity.DataBaseEntity;
|
import com.product.datasource.entity.UpdateFilterEntity;
|
import com.product.util.BaseUtil;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* @Author cheng
|
* @Date 2022/12/26 14:41
|
* @Desc 制令单数据同步
|
*/
|
@Service
|
public class MoBaseSyncService {
|
|
@Resource
|
private BaseDao baseDao;
|
|
public BaseDao getBaseDao() {
|
return baseDao;
|
}
|
|
public void runTask() {
|
FieldSetEntity fs = getBaseDao().getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATA_SYNC_MES, "LENGTH(data_source)>1 limit 1", null, false);
|
if (FieldSetEntity.isEmpty(fs)) {
|
throw new BaseException(ErrorCode.MO_NUMBER_SYNC_DATASOURCE_ERROR);
|
}
|
String dataSource = fs.getString("data_source");
|
DataBaseEntity dbe = new DataBaseEntity(dataSource);
|
Dao dao = dbe.getDao();
|
DataTableEntity dt = dao.getList(" SELECT MO_NUMBER FROM T_WIP_TRACKING WHERE IN_STATION_TIME>=TRUNC(SYSDATE) GROUP BY MO_NUMBER");
|
if (DataTableEntity.isEmpty(dt)) {
|
return;
|
}
|
|
List<MoBaseEntity> moBaseEntities = new ArrayList<>();
|
Object[] moNumbers = dt.getData().stream().map(item -> item.getString("mo_number")).toArray();
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\nSELECT NVL(COUNT(1),F.INPUT_QTY) COUNT,F.MO_NUMBER,F.TARGET_QTY,F.MO_CLOSE_DATE,F.MO_START_DATE ");
|
sql.append("\nFROM T_WIP_DETAIL T");
|
sql.append("\nRIGHT JOIN T_PM_MO_BASE F");
|
sql.append("\nON T.MO_NUMBER=F.MO_NUMBER AND T.GROUP_NAME = F.DEFAULT_GROUP");
|
sql.append("\nWHERE t.ERROR_FLAG='0' AND ");
|
sql.append(BaseUtil.buildQuestionMarkFilter("T.MO_NUMBER", moNumbers.length, true));
|
sql.append(" GROUP BY F.MO_NUMBER,F.INPUT_QTY,F.TARGET_QTY,F.MO_CLOSE_DATE,F.MO_START_DATE ");
|
DataTableEntity list = dao.getList(sql.toString(), moNumbers);
|
if (!DataTableEntity.isEmpty(list)) {
|
//开始工序.替换T_PM_MO_BASE T 表中INPUT_QTY字段
|
for (int i = 0; i < list.getRows(); i++) {
|
String moNumber = list.getString(i, "mo_number");
|
int count = list.getInt(i, "count");
|
MoBaseEntity moBase = getMoBase(moNumber, moBaseEntities);
|
moBase.setTargetQTY(list.getInt(i, "target_qty"));
|
moBase.setMoCloseDate(list.getDate(i, "mo_close_date"));
|
moBase.setMoStartDate(list.getDate(i, "mo_start_date"));
|
moBase.setInputQTY(count);
|
}
|
}
|
sql.setLength(0);
|
sql.append(" SELECT NVL(COUNT(1),F.OUTPUT_QTY) COUNT,F.MO_NUMBER,F.TARGET_QTY,F.MO_CLOSE_DATE,F.MO_START_DATE");
|
sql.append("\n FROM T_WIP_DETAIL T");
|
sql.append("\n RIGHT JOIN T_PM_MO_BASE F");
|
sql.append("\n ON T.MO_NUMBER =F.MO_NUMBER AND T.GROUP_NAME=F.END_GROUP");
|
sql.append("\n WHERE T.ERROR_FLAG ='0' AND ");
|
sql.append(BaseUtil.buildQuestionMarkFilter("F.MO_NUMBER", moNumbers.length, true));
|
sql.append("\nGROUP BY F.MO_NUMBER,F.OUTPUT_QTY ,F.TARGET_QTY,F.MO_CLOSE_DATE,F.MO_START_DATE");
|
list = dao.getList(sql.toString(), moNumbers);
|
if (!DataTableEntity.isEmpty(list)) {
|
//开始工序.替换T_PM_MO_BASE T 表中OUTPUT_QTY字段
|
for (int i = 0; i < list.getRows(); i++) {
|
String moNumber = list.getString(i, "mo_number");
|
int count = list.getInt(i, "count");
|
MoBaseEntity moBase = getMoBase(moNumber, moBaseEntities);
|
moBase.setTargetQTY(list.getInt(i, "target_qty"));
|
moBase.setMoCloseDate(list.getDate(i, "mo_close_date"));
|
moBase.setMoStartDate(list.getDate(i, "mo_start_date"));
|
moBase.setOutputQTY(count);
|
}
|
}
|
//更新开始日期的moNumber
|
Object[] updateStartDateMoNumber = moBaseEntities.stream().filter(moBaseEntity -> moBaseEntity.getInputQTY() == moBaseEntity.getTargetQTY()).map(item -> item.getMoNumber()).toArray();
|
if (!ArrayUtil.isEmpty(updateStartDateMoNumber)) {
|
sql.setLength(0);
|
sql.append(" select MAX(IN_STATION_TIME) MAX,F.MO_NUMBER");
|
sql.append("\nFROM T_WIP_DETAIL T");
|
sql.append("\nRIGHT JOIN T_PM_MO_BASE F");
|
sql.append("\nON T.MO_NUMBER = F.MO_NUMBER");
|
sql.append("\nAND T.GROUP_NAME = F.DEFAULT_GROUP");
|
sql.append("\nWHERE T.ERROR_FLAG = '0' AND ");
|
sql.append(BaseUtil.buildQuestionMarkFilter("F.MO_NUMBER", updateStartDateMoNumber.length, true));
|
sql.append(" GROUP BY F.MO_NUMBER ");
|
list = dao.getList(sql.toString(), updateStartDateMoNumber);
|
if (!DataTableEntity.isEmpty(list)) {
|
// 更新T_PM_MO_BASE.MO_START_DATE字段
|
for (int i = 0; i < list.getRows(); i++) {
|
String moNumber = list.getString(i, "mo_number");
|
MoBaseEntity moBase = getMoBase(moNumber, moBaseEntities);
|
moBase.setMoStartDate(list.getDate(i, "max"));
|
}
|
}
|
}
|
//更新结束日期的moNumber
|
Object[] updateStartEndMoNumber = moBaseEntities.stream().filter(moBaseEntity -> moBaseEntity.getOutputQTY() == moBaseEntity.getTargetQTY()).map(item -> item.getMoNumber()).toArray();
|
if (!ArrayUtil.isEmpty(updateStartEndMoNumber)) {
|
sql.setLength(0);
|
sql.append(" SELECT MAX(IN_STATION_TIME) MAX,F.MO_NUMBER");
|
sql.append("\nFROM T_WIP_DETAIL T");
|
sql.append("\nRIGHT JOIN T_PM_MO_BASE F");
|
sql.append("\nON T.MO_NUMBER = F.MO_NUMBER");
|
sql.append("\nAND T.GROUP_NAME = F.END_GROUP");
|
sql.append("\nWHERE T.ERROR_FLAG = '0' AND ");
|
sql.append(BaseUtil.buildQuestionMarkFilter("F.MO_NUMBER", updateStartDateMoNumber.length, true));
|
sql.append(" GROUP BY F.MO_NUMBER ");
|
list = dao.getList(sql.toString(), updateStartEndMoNumber);
|
if (!DataTableEntity.isEmpty(list)) {
|
// 更新T_PM_MO_BASE.MO_END_DATE字段
|
for (int i = 0; i < list.getRows(); i++) {
|
String moNumber = list.getString(i, "mo_number");
|
MoBaseEntity moBase = getMoBase(moNumber, moBaseEntities);
|
moBase.setMoCloseDate(list.getDate(i, "max"));
|
}
|
}
|
}
|
DataTableEntity data = new DataTableEntity();
|
moBaseEntities.stream().forEach(item -> {
|
FieldSetEntity ff = new FieldSetEntity();
|
ff.setTableName("T_PM_MO_BASE");
|
if (StringUtils.isEmpty(item.getMoNumber())) {
|
return;
|
}
|
boolean isUpdate = false;
|
ff.setValue("mo_number", item.getMoNumber());
|
if (item.getInputQTY() > 0) {
|
ff.setValue("input_qty", item.getInputQTY());
|
isUpdate = true;
|
}
|
if (item.getOutputQTY() > 0) {
|
ff.setValue("output_qty", item.getOutputQTY());
|
isUpdate = true;
|
}
|
if (item.getMoStartDate() != null) {
|
ff.setValue("mo_start_date", item.getMoStartDate());
|
isUpdate = true;
|
}
|
if (item.getMoCloseDate() != null) {
|
ff.setValue("mo_close_date", item.getMoCloseDate());
|
isUpdate = true;
|
}
|
if (isUpdate) {
|
data.addFieldSetEntity(ff);
|
}
|
});
|
if (!DataTableEntity.isEmpty(data)) {
|
// System.out.println(data);
|
dao.updateBatch(data, new UpdateFilterEntity("mo_number=?", new String[]{"mo_number"}), true);
|
}
|
dao.closeConnection();
|
}
|
|
private MoBaseEntity getMoBase(String moNumber, List<MoBaseEntity> moBaseEntities) {
|
if (moBaseEntities.size() > 0) {
|
int index = moBaseEntities.indexOf(moNumber);
|
if (index > -1) {
|
return moBaseEntities.get(index);
|
}
|
}
|
MoBaseEntity moBaseEntity = new MoBaseEntity();
|
moBaseEntity.setMoNumber(moNumber);
|
moBaseEntities.add(moBaseEntity);
|
return moBaseEntity;
|
}
|
|
|
}
|
