package org.jeecg.modules.iot.service.impl; import com.alibaba.fastjson.JSONObject; import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import org.apache.commons.collections.CollectionUtils; import org.apache.http.HttpResponse; import org.apache.http.client.HttpClient; import org.apache.http.client.methods.HttpPost; import org.apache.http.entity.StringEntity; import org.apache.http.impl.client.HttpClients; import org.apache.http.util.EntityUtils; import org.jeecg.common.api.vo.Result; import org.jeecg.modules.iot.depository.DbConfig; import org.jeecg.modules.iot.depository.MysqlDataWriter; import org.jeecg.modules.iot.entity.*; import org.jeecg.modules.iot.mapper.EmptyParameterMapper; import org.jeecg.modules.iot.mapper.RealParameterMapper; import org.jeecg.modules.iot.mdc.entity.MdcDriveTypeParamConfig; import org.jeecg.modules.iot.mdc.service.IMdcDriveTypeParamConfigService; import org.jeecg.modules.iot.service.IEmptyParameterService; import org.jeecg.modules.iot.service.IEquipmentService; import org.jeecg.modules.iot.service.IServerDeployService; import org.jeecg.modules.iot.util.AESUtil; import org.jeecg.modules.iot.util.HttpClientUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.*; import java.util.*; import java.util.Date; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.stream.Collectors; /** * @Description: 虚设备参数 * @Author: cuikaidong * @Date: 2025-1-7 * @Version: V1.0 */ @Service public class EmptyParameterServiceImpl extends ServiceImpl implements IEmptyParameterService { @Value("${databaseType}") private String databaseType; @Value("${ftp.address}") private String ftpAddress; @Resource private RealParameterMapper realParameterMapper; @Autowired private IEquipmentService equipmentService; @Autowired private IServerDeployService serverDeployService; @Autowired private DataSource dataSource; @Autowired private IMdcDriveTypeParamConfigService driveTypeParamConfigService; @Override @Transactional(rollbackFor = Exception.class) public Result addEmptyParameter(EmptyParameter emptyParameter) { // 验证编码名称是否重复 if (findEmptyParameterByName(emptyParameter)) { return Result.error("参数名称已存在！"); } emptyParameter.setParameterCode(findEmptyParameterCode(emptyParameter.getEquipmentId())); emptyParameter.setDataLength("1"); baseMapper.insert(emptyParameter); // 查询设备信息 Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); if (equipment == null) { return Result.error("设备信息不存在！"); } // 验证mdc是否存在驱动参数 boolean englishNameBoolean = driveTypeParamConfigService.selectEnglishNameByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName()); if (!englishNameBoolean) { // 给mdc写入驱动参数 MdcDriveTypeParamConfig mdcDriveTypeParamConfig = new MdcDriveTypeParamConfig(); mdcDriveTypeParamConfig.setControlSystemType(equipment.getControlSystem()); mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName()); mdcDriveTypeParamConfig.setChineseName(emptyParameter.getParameterDescribe()); mdcDriveTypeParamConfig.setShowFlag("Y"); mdcDriveTypeParamConfig.setCurveGenerationFlags("N"); driveTypeParamConfigService.save(mdcDriveTypeParamConfig); } return Result.ok("添加成功！"); } @Override @Transactional(rollbackFor = Exception.class) public Result addBatchEmptyParameter(List emptyParameters) { Integer parameterCode = findEmptyParameterCode(emptyParameters.get(0).getEquipmentId()); final int[] code = {parameterCode - 1}; for (EmptyParameter e : emptyParameters) { code[0] = code[0] + 1; e.setParameterCode(code[0]); // 验证编码名称是否重复 if (findEmptyParameterByName(e)) { return Result.error(e.getParameterName() + "参数名称已存在！"); } } this.saveBatch(emptyParameters); // 查询设备信息 Equipment equipment = equipmentService.getById(emptyParameters.get(0).getEquipmentId()); if (equipment == null) { return Result.error("设备信息不存在！"); } List driveTypeParamConfigs = new ArrayList<>(); for (EmptyParameter emptyParameter : emptyParameters) { // 验证mdc是否存在驱动参数 boolean englishNameBoolean = driveTypeParamConfigService.selectEnglishNameByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName()); if (!englishNameBoolean) { // 给mdc写入驱动参数 MdcDriveTypeParamConfig mdcDriveTypeParamConfig = new MdcDriveTypeParamConfig(); mdcDriveTypeParamConfig.setControlSystemType(equipment.getControlSystem()); mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName()); mdcDriveTypeParamConfig.setChineseName(emptyParameter.getParameterDescribe()); mdcDriveTypeParamConfig.setShowFlag("Y"); mdcDriveTypeParamConfig.setCurveGenerationFlags("N"); driveTypeParamConfigs.add(mdcDriveTypeParamConfig); } } if (driveTypeParamConfigs.size() > 0) { driveTypeParamConfigService.saveBatch(driveTypeParamConfigs); } return Result.ok("批量添加成功！"); } @Override public void editEmptyParameter(EmptyParameter emptyParameter) { // 查询设备信息 Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); emptyParameter.setCreateTime(new Date()); EmptyParameter emptyParameter1 = baseMapper.selectById(emptyParameter.getId()); baseMapper.updateById(emptyParameter); if (!emptyParameter1.getParameterName().equals(emptyParameter.getParameterName())) { MdcDriveTypeParamConfig mdcDriveTypeParamConfig = driveTypeParamConfigService.selectMdcDriveTypeParamConfigByEquipment(equipment.getControlSystem(), emptyParameter1.getParameterName()); mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName()); driveTypeParamConfigService.updateById(mdcDriveTypeParamConfig); } } @Override public void deleteEmptyParameter(String id) { EmptyParameter emptyParameter = baseMapper.selectById(id); if (emptyParameter.getType().equals("0")) { //删除虚设备时调用` Map param = new HashMap<>(); Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); ServerDeploy serverDeploy = serverDeployService.getById(equipment.getServerId()); param.put("path", ftpAddress + serverDeploy.getServerCode() + "/script"); HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/ProjectPath", param); param.clear(); param.put("deviceid", emptyParameter.getEquipmentId()); param.put("id", equipment.getEqptName()); HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/DeleteParameterScript", param); } baseMapper.deleteById(id); // 查询设备信息 Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); // 删除mdc设备参数信息 MdcDriveTypeParamConfig mdcDriveTypeParamConfig = driveTypeParamConfigService.selectMdcDriveTypeParamConfigByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName()); if (mdcDriveTypeParamConfig != null) { driveTypeParamConfigService.removeById(mdcDriveTypeParamConfig); } } @Override public List findParameterByEquipmentIds(Set ids) { return new LambdaQueryChainWrapper<>(baseMapper).in(EmptyParameter::getEquipmentId, ids).list(); } @Override public Result handleCompile(EmptyParameter emptyParameter) { Map referenceInfo = new HashMap<>(); // 验证脚本实设备 List attributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+")); if (!attributes.isEmpty()) { List realParameterList = realParameterMapper.findRealParameterList(attributes); // 判断实设备参数数量是否相等 if (attributes.size() != realParameterList.size()) { // 求差集 Set difference1 = new HashSet<>(attributes); // 使用 Stream API 将 name 字段转换为 Set Set difference2 = realParameterList.stream() .map(RealParameter::getAddress).collect(Collectors.toSet()); difference1.removeAll(difference2); return Result.error(difference1 + "属性有误！！！"); } realParameterList.forEach(r -> { if (r.getSystemDataType().isEmpty()) { referenceInfo.put(r.getAddress(), r.getParameterType()); } else { referenceInfo.put(r.getAddress(), r.getSystemDataType()); } }); } // 验证虚设备 List empAttributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+")); if (!empAttributes.isEmpty()) { List emptyParameterList = new ArrayList<>(); Set difference = new HashSet<>(empAttributes); if (!attributes.isEmpty()) { // 求差集 difference.removeAll(attributes); List www = new ArrayList<>(difference); // 查询数据库中匹配的数量 if (!www.isEmpty()) { emptyParameterList = baseMapper.findEmptyParameterList(www); } } else { emptyParameterList = baseMapper.findEmptyParameterList(empAttributes); } // 使用 HashSet 去重 Set personSet = new HashSet<>(emptyParameterList); // 判断虚设备参数数量是否相等 if (difference.size() != personSet.size()) { // 求差集 Set difference1 = new HashSet<>(difference); // 使用 Stream API 将 name 字段转换为 Set Set difference2 = personSet.stream() .map(EmptyParameter::getAddress).collect(Collectors.toSet()); difference1.removeAll(difference2); return Result.error(difference1 + "属性有误！！！"); } personSet.forEach(p -> { if (p.getSystemDataType().isEmpty()) { referenceInfo.put(p.getAddress(), p.getParameterType()); } else { referenceInfo.put(p.getAddress(), p.getSystemDataType()); } }); } // --------AES_CBC加密--------- String cbcResult = AESUtil.encryptCBC(emptyParameter.getScript(), "xhVs6DRXLfUGxw+AhtfQdpQGoa+8SA9d"); try { ScriptCompilation scriptCompilation = new ScriptCompilation(); Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); scriptCompilation.setDeviceId(equipment.getEqptCode()); scriptCompilation.setParameterCode(cbcResult); scriptCompilation.setParameterDataType(emptyParameter.getParameterType()); scriptCompilation.setParameterName(emptyParameter.getParameterName()); scriptCompilation.setReferenceinfo(referenceInfo); String jsonPayload = JSONObject.toJSONString(scriptCompilation); // 创建 HttpClient 实例 HttpClient httpClient = HttpClients.createDefault(); // 创建 HttpPost 请求实例 HttpPost httpPost = new HttpPost("http://localhost:3002/ScriptCompiler/ScriptCompiler"); // 设置请求头，指定内容类型为 JSON，编码为 UTF-8 httpPost.setHeader("Content-Type", "application/json; charset=UTF-8"); // 创建 StringEntity 并指定编码为 UTF-8 StringEntity entity = new StringEntity(jsonPayload, "UTF-8"); httpPost.setEntity(entity); // 执行请求并获取响应 HttpResponse response = httpClient.execute(httpPost); // 获取响应实体内容 String responseBody = EntityUtils.toString(response.getEntity(), "UTF-8"); boolean isContain = responseBody.contains("编译成功"); if (isContain) { return Result.ok(responseBody); } else { // 输出响应内容 return Result.error(responseBody); } } catch (Exception e) { e.printStackTrace(); } return Result.error("编译失败"); } @Override public Result preserveScript(EmptyParameter emptyParameter) { Map referenceInfo = new HashMap<>(); // 验证脚本实设备 List attributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+")); if (!attributes.isEmpty()) { List realParameterList = realParameterMapper.findRealParameterList(attributes); realParameterList.forEach(r -> { if (r.getSystemDataType().isEmpty()) { referenceInfo.put(r.getAddress(), r.getParameterType()); } else { referenceInfo.put(r.getAddress(), r.getSystemDataType()); } }); } // 验证虚设备 List empAttributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+")); if (!empAttributes.isEmpty()) { List emptyParameterList = new ArrayList<>(); Set difference = new HashSet<>(empAttributes); if (!attributes.isEmpty()) { // 求差集 difference.removeAll(attributes); List www = new ArrayList<>(difference); // 查询数据库中匹配的数量 if (!www.isEmpty()) { emptyParameterList = baseMapper.findEmptyParameterList(www); } } else { emptyParameterList = baseMapper.findEmptyParameterList(empAttributes); } // 使用 HashSet 去重 Set personSet = new HashSet<>(emptyParameterList); personSet.forEach(p -> { if (p.getSystemDataType().isEmpty()) { referenceInfo.put(p.getAddress(), p.getParameterType()); } else { referenceInfo.put(p.getAddress(), p.getSystemDataType()); } }); } // --------AES_CBC加密--------- String cbcResult = AESUtil.encryptCBC(emptyParameter.getScript(), "xhVs6DRXLfUGxw+AhtfQdpQGoa+8SA9d"); try { ScriptCompilation scriptCompilation = new ScriptCompilation(); Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId()); scriptCompilation.setDeviceId(equipment.getEqptCode()); scriptCompilation.setParameterCode(cbcResult); scriptCompilation.setParameterDataType(emptyParameter.getParameterType()); scriptCompilation.setParameterName(emptyParameter.getParameterName()); scriptCompilation.setReferenceinfo(referenceInfo); String jsonPayload = JSONObject.toJSONString(scriptCompilation); // 创建 HttpClient 实例 HttpClient httpClient = HttpClients.createDefault(); Map param = new HashMap<>(); // 发送文件地址 ServerDeploy serverDeploy = serverDeployService.getById(equipment.getServerId()); param.put("path", ftpAddress + serverDeploy.getServerCode() + "/script"); HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/ProjectPath", param); // 创建 HttpPost 请求实例 HttpPost httpPost = new HttpPost("http://localhost:3002/ScriptCompiler/ScriptSave"); // 设置请求头，指定内容类型为 JSON，编码为 UTF-8 httpPost.setHeader("Content-Type", "application/json; charset=UTF-8"); // 创建 StringEntity 并指定编码为 UTF-8 StringEntity entity = new StringEntity(jsonPayload, "UTF-8"); httpPost.setEntity(entity); // 执行请求并获取响应 HttpResponse response = httpClient.execute(httpPost); // 获取响应实体内容 String responseBody = EntityUtils.toString(response.getEntity(), "UTF-8"); boolean isContain = responseBody.contains("保存成功"); // 将 JSON 字符串转换为 JSONObject cn.hutool.json.JSONObject jsonObject = new cn.hutool.json.JSONObject(responseBody); // 访问 JSON 对象的属性 String message = jsonObject.getStr("Message"); if (isContain) { // 保存脚本标记列表 StringBuilder scriptTagList = new StringBuilder(); for (String key : referenceInfo.keySet()) { if (scriptTagList.toString().equals("")) { scriptTagList = new StringBuilder(key); } else { scriptTagList.append("|").append(key); } } // 保存属性 emptyParameter.setScriptTagList(scriptTagList.toString()); // 保存脚本 emptyParameter.setScriptContent(emptyParameter.getScript()); baseMapper.updateById(emptyParameter); return Result.ok(message); } else { // 输出响应内容 return Result.error(message); } } catch (Exception e) { e.printStackTrace(); } return Result.error("编译失败"); } @Override public Integer findEmptyParameterCode(String equipmentId) { List list = new LambdaQueryChainWrapper<>(baseMapper) .eq(EmptyParameter::getEquipmentId, equipmentId) .orderByDesc(EmptyParameter::getParameterCode) .list(); if (list.size() == 0) { return 1; } return list.get(0).getParameterCode() + 1; } @Override public Boolean findEmptyParameterByName(EmptyParameter emptyParameter) { List realParameters = new LambdaQueryChainWrapper<>(baseMapper) .eq(EmptyParameter::getEquipmentId, emptyParameter.getEquipmentId()) .eq(EmptyParameter::getParameterName, emptyParameter.getParameterName()) .list(); return realParameters.size() > 0; } @Override public void createEmptyEmptyField(Date deployDate, String serverId) { List emptyParameterList = baseMapper.findEmptyParameterListByTime(deployDate, serverId); Map> emptyParameterListMap = emptyParameterList.stream() .collect(Collectors.groupingBy(EmptyParameter::getEqptCode)); emptyParameterListMap.forEach((eqptCode, parameterList) -> { // 将所有的 '-' 替换为 '_' String code = eqptCode.replace("-", "_"); Equipment equipment = equipmentService.findEquipmentByCode(eqptCode); List columnDefinitions = new ArrayList<>(); Set columnNames = new HashSet<>(); // 遍历当前设备下的所有参数 parameterList.forEach(param -> { columnNames.add(param.getParameterName()); if (databaseType.equals("SqlServer")) { columnDefinitions.add("NVARCHAR(100) DEFAULT ''"); } else if (databaseType.equals("MySql")) { columnDefinitions.add("VARCHAR(100) CHARACTER SET utf8mb4"); } }); if (CollectionUtils.isEmpty(columnNames)){ return; } if (databaseType.equals("SqlServer")) { addSqlserverColumns(equipment.getControlSystem() + '_' + code, new ArrayList<>(columnDefinitions), new ArrayList<>(columnNames)); } else if (databaseType.equals("MySql")) { addMySqlColumns(equipment.getControlSystem() + '_' + code, new ArrayList<>(columnDefinitions), new ArrayList<>(columnNames)); } }); } /** * // 批量导入字段（SQL Server 版本） * * @param tableName * @param columnDefinitions * @param columnNames */ public void addSqlserverColumns(String tableName, List columnDefinitions, List columnNames) { if (columnDefinitions.size() != columnNames.size()) { throw new IllegalArgumentException("字段定义和字段名的数量必须一致"); } // 转义表名中的方括号，防止SQL注入 tableName = tableName.replace("[", "[[").replace("]", "]]"); StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.append("ALTER TABLE").append("[") .append(tableName).append("] ADD "); boolean hasNewColumns = false; for (int i = 0; i < columnDefinitions.size(); i++) { String columnName = columnNames.get(i); if (sqlServeBatchIsColumnExists(tableName, columnName)) { System.out.println("字段已存在，无需添加: " + columnName); continue; } String columnDefinition = columnDefinitions.get(i); if (hasNewColumns) { sqlBuilder.append(", "); } sqlBuilder.append("[").append(columnName.replace("[", "[[").replace("]", "]]")).append("] ") .append(columnDefinition); hasNewColumns = true; } String sql = sqlBuilder.toString(); if (!hasNewColumns) { System.out.println("没有需要添加的新字段"); return; } try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) { System.out.println("执行SQL: " + sql); // 调试用 stmt.executeUpdate(sql); System.out.println("字段批量添加成功"); } catch (SQLException e) { System.err.println("批量添加字段失败: " + sql); e.printStackTrace(); } } /** * 验证字段是否存在（SQL Server 版本） */ private boolean sqlServeBatchIsColumnExists(String tableName, String columnName) { // 自动转义表名（处理方括号和特殊字符） String quotedTable = "[" + tableName.replace("]", "]]") + "]"; String sql = "SELECT COUNT(*) FROM sys.columns " + "WHERE object_id = OBJECT_ID(?) AND name = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { // 设置第一个参数：转义后的表名 stmt.setString(1, quotedTable); // 设置第二个参数：字段名 stmt.setString(2, columnName); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) { return rs.getInt(1) > 0; } } } catch (SQLException e) { // log.error("检查字段 {} 在表 {} 中是否存在时出错", columnName, tableName, e); return false; } return false; } /** * 批量导入字段（MySQL 版本） * * @param tableName 表名 * @param columnDefinitions 列定义列表，格式如 "VARCHAR(255) NOT NULL" * @param columnNames 列名列表 */ public void addMySqlColumns(String tableName, List columnDefinitions, List columnNames) { if (columnDefinitions.size() != columnNames.size()) { throw new IllegalArgumentException("字段定义和字段名的数量必须一致"); } tableName = sanitizeSqlIdentifier(tableName); // 对每个列单独执行 ALTER TABLE 语句 for (int i = 0; i < columnNames.size(); i++) { String columnName = sanitizeSqlIdentifier(columnNames.get(i)); if (columnName.isEmpty()) { System.out.println("忽略无效字段名: " + columnNames.get(i)); continue; } if (mySqlBatchIsColumnExists(tableName, columnName)) { System.out.println("字段已存在，无需添加: " + columnName); continue; } String columnDefinition = columnDefinitions.get(i); // 确保格式正确 columnDefinition = formatMySqlColumnDefinition(columnDefinition); String sql = String.format( "ALTER TABLE `%s` ADD `%s` %s", tableName, columnName, columnDefinition ); try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) { System.out.println("执行SQL: " + sql); stmt.executeUpdate(sql); System.out.println("字段添加成功: " + columnName); } catch (SQLException e) { System.err.println("添加字段失败: " + sql); e.printStackTrace(); } } } // 确保 MySQL 列定义格式正确 private String formatMySqlColumnDefinition(String definition) { // 修正 VARCHAR 格式 definition = definition.replaceAll("VARCHAR\\s*\\(\\s*(\\d+)\\s*\\)", "VARCHAR($1)"); // 确保 CHARACTER SET 紧跟数据类型 definition = definition.replaceAll("(VARCHAR\\(\\d+\\))\\s+(CHARACTER SET)", "$1 $2"); // 移除多余空格 definition = definition.replaceAll("\\s+", " "); return definition; } // 清理 SQL 标识符 private String sanitizeSqlIdentifier(String identifier) { if (identifier == null || identifier.isEmpty()) { return "_invalid_"; } return identifier.replaceAll("[^a-zA-Z0-9_]", "_"); } /** * 检查MySQL表中是否存在指定列 * * @param tableName 表名 * @param columnName 列名 * @return true if column exists */ private boolean mySqlBatchIsColumnExists(String tableName, String columnName) { String escapedTableName = tableName.replace("`", "``"); String escapedColumnName = columnName.replace("`", "``"); String sql = "SELECT COUNT(*) FROM information_schema.columns " + "WHERE table_name = ? AND column_name = ? " + "AND table_schema = DATABASE()"; try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, escapedTableName); pstmt.setString(2, escapedColumnName); try (ResultSet rs = pstmt.executeQuery()) { if (rs.next()) { return rs.getInt(1) > 0; } } } catch (SQLException e) { e.printStackTrace(); } return false; } /** * 根据表达式查询脚本中包含的属性 * * @param script 脚本 * @param regex 表达式 * @return 属性 */ Set getAttributes(String script, String regex) { Set scriptList = new HashSet<>(); // 编译正则表达式 Pattern pattern = Pattern.compile(regex); // 创建 Matcher 对象 Matcher matcher = pattern.matcher(script); // 查找所有匹配的子字符串 while (matcher.find()) { // 获取当前匹配到的内容 scriptList.add(matcher.group()); } return scriptList; } }