package org.jeecg.modules.msi.webapi.service.impl;
|
|
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
|
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
|
import liquibase.util.JdbcUtils;
|
import org.apache.commons.lang3.StringUtils;
|
import org.jeecg.common.util.DateUtils;
|
import org.jeecg.modules.msi.webapi.entity.MsiWebapiJsonEntity;
|
import org.jeecg.modules.msi.webapi.mapper.MsiWebapiJsonMapper;
|
import org.jeecg.modules.msi.webapi.service.IMsiWebapiJsonService;
|
import org.jeecg.modules.msi.webapi.vo.DetailedListVo;
|
import org.jeecg.modules.msi.webapi.vo.MachineEquipentInfo;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.jdbc.datasource.DataSourceUtils;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.interceptor.TransactionAspectSupport;
|
|
import java.sql.Connection;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
@Service
|
public class MsiWebapiJsonServiceImpl extends ServiceImpl<MsiWebapiJsonMapper, MsiWebapiJsonEntity> implements IMsiWebapiJsonService {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 解析单条数据保存到单表
|
* @param data
|
* @return
|
*/
|
@Override
|
public boolean saveTableAutomationOne(List<MachineEquipentInfo> data) {
|
if (data ==null || data.isEmpty()) {
|
return false;
|
}
|
List<String> listStatus = new ArrayList<>();
|
List<String> listParameter = new ArrayList<>();
|
|
// 用于记录前一条记录的状态值
|
String prevRunningStatus = null;
|
|
for (MachineEquipentInfo machineInfo : data) {
|
System.out.println(machineInfo);
|
|
// 获取当前记录的runningStatus值
|
String currentRunningStatus = null;
|
for (DetailedListVo item : machineInfo.getItemList()) {
|
if ("runningStatus".equals(item.getItemName())) {
|
currentRunningStatus = item.getItemValue();
|
break;
|
}
|
}
|
|
// 如果当前状态与前一条相同，则跳过
|
if (currentRunningStatus != null && currentRunningStatus.equals(prevRunningStatus)) {
|
continue;
|
}
|
|
// 更新前一条记录的状态值
|
prevRunningStatus = currentRunningStatus;
|
|
// 生成SQL
|
String stutus = listSqls(machineInfo);
|
if (stutus != null && !stutus.equals("")) {
|
listStatus.add(stutus);
|
}
|
String parameter = listSqlParameter(machineInfo);
|
if (parameter != null && !parameter.equals("")) {
|
listParameter.add(parameter);
|
}
|
}
|
// 执行状态数据SQL
|
try {
|
executeBatchSql(listStatus);
|
if (!listParameter.isEmpty()) {
|
executeBatchSql(listParameter);
|
}
|
return true;
|
} catch (Exception e) {
|
log.error("执行SQL批量插入失败", e);
|
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
|
return false;
|
}
|
}
|
|
/**
|
* 批量执行SQL语句
|
* @param sqlList SQL语句列表
|
* @throws SQLException
|
*/
|
private void executeBatchSql(List<String> sqlList) throws SQLException {
|
if (CollectionUtils.isEmpty(sqlList)) {
|
return;
|
}
|
|
Connection connection = null;
|
Statement statement = null;
|
|
try {
|
connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
|
connection.setAutoCommit(false);
|
statement = connection.createStatement();
|
|
for (String sql : sqlList) {
|
statement.addBatch(sql);
|
}
|
|
statement.executeBatch();
|
connection.commit();
|
} catch (SQLException e) {
|
if (connection != null) {
|
connection.rollback();
|
}
|
throw e;
|
} finally {
|
JdbcUtils.closeStatement(statement);
|
DataSourceUtils.releaseConnection(connection, jdbcTemplate.getDataSource());
|
}
|
}
|
|
|
/**
|
* 插入到EquipmentLog
|
* @param machineInfo
|
* @return
|
*/
|
public String listSqlParameter(MachineEquipentInfo machineInfo) {
|
String listSqls = null;
|
List<DetailedListVo> itemList = machineInfo.getItemList();
|
Map<String ,DetailedListVo> itemMap = new HashMap<String ,DetailedListVo>();
|
for (DetailedListVo item: itemList) {
|
itemMap.put(item.getItemName(),item);
|
}
|
String sql = "insert into ";
|
sql = sql + "ZDH_" + machineInfo.getMachineNo() + " ";
|
sql = sql + "(EquipmentID,EquipmentName,CollectTime,runningStatus,spindleSpeed,feedRatio,runDuration,spindleDuration,progName,progStatus,toolNo) values ( ";
|
//EquipmentID
|
if (machineInfo.getMachineNo() == null || machineInfo.getMachineNo().equals("")) {
|
return null;
|
} else {
|
sql = sql + "'" + machineInfo.getMachineNo() + "', ";
|
}
|
//EquipmentName
|
sql = sql + null + ", ";
|
//CollectTime
|
// 假设 endTime 是 "MM/dd/yyyy HH:mm:ss" 格式的字符串
|
if (machineInfo.getEndTime() != null && !machineInfo.getEndTime().isEmpty()) {
|
try {
|
// 1. 将字符串解析为 Date 对象
|
SimpleDateFormat inputFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
|
Date endDate = inputFormat.parse(machineInfo.getEndTime());
|
|
// 2. 将 Date 格式化为目标 SQL 格式（如 "yyyy-MM-dd HH:mm:ss"）
|
SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
sql = sql + "'" + outputFormat.format(endDate) + "', ";
|
} catch (ParseException e) {
|
// 处理解析失败的情况（如日志记录或抛出异常）
|
throw new RuntimeException("Invalid end time format: " + machineInfo.getEndTime(), e);
|
}
|
} else {
|
// 如果 endTime 为 null 或空，使用当前时间
|
sql = sql + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "', ";
|
}
|
//runningStatus
|
if (itemMap.containsKey("runningStatus")) {
|
sql = sql + "'" + itemMap.get("runningStatus").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//spindleSpeed
|
if (itemMap.containsKey("spindleSpeed")) {
|
sql = sql + "'" + itemMap.get("spindleSpeed").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//feedRatio
|
if (itemMap.containsKey("feedRatio")) {
|
sql = sql + "'" + itemMap.get("feedRatio").getItemValue() + "', ";
|
}else {
|
sql = sql + "NULL, ";
|
}
|
//runDuration
|
if (itemMap.containsKey("runDuration")) {
|
sql = sql + "'" + itemMap.get("runDuration").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//spindleDuration
|
if (itemMap.containsKey("spindleDuration")) {
|
sql = sql + "'" + itemMap.get("spindleDuration").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//progName
|
if (itemMap.containsKey("progName")) { // 修正为 "progName"
|
sql = sql + "'" + itemMap.get("progName").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//progStatus
|
if (itemMap.containsKey("progStatus")) {
|
sql = sql + "'" + itemMap.get("progStatus").getItemValue() + "', ";
|
} else {
|
sql = sql + "NULL, ";
|
}
|
//toolNo
|
if (itemMap.containsKey("toolNo")) {
|
sql = sql + "'" + itemMap.get("toolNo").getItemValue() + "' ";
|
} else {
|
sql = sql + "NULL";
|
}
|
|
sql = sql + "); \n";
|
if (StringUtils.isBlank(listSqls) ) {
|
listSqls = sql;
|
} else {
|
listSqls += sql;
|
}
|
return listSqls;
|
}
|
|
|
/**
|
* 插入到EquipmentLog
|
* @param machineInfo
|
* @return
|
*/
|
public String listSqls(MachineEquipentInfo machineInfo) {
|
String listSqls = null;
|
List<DetailedListVo> itemList = machineInfo.getItemList();
|
String sql = "insert into EquipmentLog (EquipmentID,EquipmentName,CollectTime,Oporation,Alarm) values ( ";
|
if (machineInfo.getMachineNo() == null) {
|
return null;
|
} else {
|
sql = sql + "'" + machineInfo.getMachineNo() + "', ";
|
}
|
sql = sql + null + ", ";
|
if (machineInfo.getEndTime() != null) {
|
|
sql = sql + "'" + DateUtils.formattedDate(machineInfo.getEndTime(),DateUtils.STR_DD_MM_YYYY,DateUtils.STR_DATE_TIME_SMALL) + "', ";
|
} else {
|
return null;
|
}
|
Map<String ,DetailedListVo> itemMap = new HashMap<String ,DetailedListVo>();
|
for (DetailedListVo item: itemList) {
|
itemMap.put(item.getItemName(),item);
|
}
|
if (itemMap.containsKey("runningStatus")) {
|
int oporation = 0;
|
switch (itemMap.get("runningStatus").getItemValue()) {
|
case "0" : oporation =0;break;
|
case "1" : oporation =2;break;
|
case "2" : oporation =3;break;
|
case "3" : oporation =2;break;
|
case "4" : oporation =2;break;
|
case "10" : oporation =2;break;
|
case "13" : oporation =2;break;
|
case "14" : oporation =2;break;
|
case "10001" : oporation =2;break;
|
default: break;
|
}
|
sql = sql + " " + oporation + ", ";
|
} else {
|
return null;
|
}
|
sql = sql + null + " ); \n";
|
if (StringUtils.isBlank(listSqls) ) {
|
listSqls = sql;
|
}
|
return listSqls;
|
}
|
}
|
