package com.ruoyi.outpatientvisit.support;
import com.ruoyi.common.core.service.IConfigService;
import com.ruoyi.outpatientvisit.mapper.PatMedOuthospMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.*;
/**
* 门诊分表维护 + 存储过程重建（从 {@code ruoyi-quartz} 的
* {@code com.ruoyi.quartz.util.CreateDataTable} 整体迁入）。
*
* 迁移对应关系
*
* | 本类方法 | 原实现 |
|---|
* | {@link #createOuthospQuarterTable()} |
* {@code CreateDataTable#createOuthospQuarterTable}（原 L35-58） |
* | {@link #createPartitionTableAndRefreshView(String)} |
* 原 L69-128（按 季度/年/月 建分表 + 刷视图） |
* | {@link #refreshOuthospView()} |
* 原 L163-420（重建存储过程 {@code sp_query_outhosp}） |
* | {@link #refreshOuthospCountProc(List, List, String)} |
* 原 L426-584（重建存储过程 {@code sp_count_outhosp}） |
*
* 业务逻辑一行未改，包括几处刻意保留的行为（见 "保留的原行为"）。
*
* 迁移时唯一改动的两处（都是为了让本类能进"只依赖 ruoyi-common"的模块）
*
* - 配置服务换接口：原来注入 {@code com.ruoyi.system.service.ISysConfigService}
* （属 ruoyi-system，本模块不能依赖）。改为
* {@link IConfigService}（ruoyi-common）——同名方法
* {@code selectConfigByKey(String)}，语义一致；
* 并用 {@code ObjectProvider} 惰性取，容器里没有实现时按"未配置"处理而不是启动失败。
* - 删除未使用字段：原类有一个 {@code @Autowired IPatMedOuthospService
* iPatMedOuthospService} 字段，全类从未被使用（已逐行确认）。
* 它属 smartor，是本类进模块的唯一硬阻碍；既然无用，直接不搬。
*
*
* 保留的原行为（勿"顺手优化"）
*
* - {@code createOuthospQuarterTable} 把所有异常吞掉只打日志（原 L55-57），
* 不向上抛 —— 这是定时任务的容错方式，原样保留；
* - 存储过程"已存在时不覆盖、只把新分表 UNION ALL 追加进去"（原 L346-413），
* 以及"DROP 前先存原 DDL、CREATE 失败立刻恢复"的保护；
* - {@code sp_query_outhosp} 与 {@code sp_count_outhosp} 的参数完全一致，
* 只是后者不分页；两处的条件片段拼接逻辑刻意分别保留（不抽公共方法），
* 以免改动引入差异；
* - {@code refreshOuthospCountProc} 里存在未被使用的中间变量
* {@code concatArgs}（原 L486-496 拼了但没用到）—— 原样保留。
*
*
* 调用方
* 定时任务 {@code RyTask#createOuthospQuarterTable} 经本模块门面调用本类，
* 不再直接依赖 ruoyi-quartz 里的建表工具。
*/
@Slf4j
@Component
public class OuthospPartitionMaintainer {
/**
* 分表模式配置键（原样保留）
*/
private static final String SPLIT_MODE_KEY = "outhosp.split.mode";
/**
* 参数配置服务。
*
* ⚠️ 用 {@code ObjectProvider} 惰性取：本模块只依赖 ruoyi-common，
* 在独立部署/测试等"容器里没有实现"的场景下不能因此启动失败。
* 取不到时按"未配置"处理（与原来 {@code selectConfigByKey} 查不到返回 null 的效果一致）。
*/
@Autowired
private ObjectProvider configServiceProvider;
@Autowired
private PatMedOuthospMapper patMedOuthospMapper;
@Autowired
private DataSource dataSource;
/**
* 定时任务入口：按 {@code sys_config: outhosp.split.mode} 建门诊分表并刷新视图。
*
* 逐行对应原 {@code CreateDataTable#createOuthospQuarterTable}（L35-58）。
*/
public void createOuthospQuarterTable() {
log.info("【createOuthospQuarterTable】开始执行门诊分表任务");
try {
// 从 sys_config 查询分表模式， key = outhosp.split.mode
String splitMode = readConfig(SPLIT_MODE_KEY);
if (org.apache.commons.lang3.StringUtils.isBlank(splitMode)) {
log.info("【createOuthospQuarterTable】未配置 outhosp.split.mode，不执行分表");
return;
}
splitMode = splitMode.trim();
if (!"季度".equals(splitMode) && !"年".equals(splitMode) && !"月".equals(splitMode)) {
log.warn("【createOuthospQuarterTable】分表模式配置非法：{}，应为 quarter/year/month，不执行分表", splitMode);
return;
}
String newTable = createPartitionTableAndRefreshView(splitMode);
if (newTable == null) {
log.info("【createOuthospQuarterTable】当前周期表已存在，无需操作");
} else {
log.info("【createOuthospQuarterTable】新建分表成功：{}，已刷新视图 v_pat_med_outhosp", newTable);
}
} catch (Exception e) {
// ⚠️ 原行为：吞掉异常只打日志（定时任务容错方式），原样保留
log.error("【createOuthospQuarterTable】建表异常", e);
}
}
/**
* 读 {@code sys_config} 键值；没有实现或读取失败返回 {@code null}
* （与原来 {@code ISysConfigService.selectConfigByKey} 查不到返回 null 一致）。
*/
private String readConfig(String key) {
if (configServiceProvider == null) {
return null;
}
try {
IConfigService svc = configServiceProvider.stream().findFirst().orElse(null);
if (svc == null) {
log.warn("【门诊分表】容器里没有 IConfigService 实现，{} 按未配置处理。", key);
return null;
}
return svc.selectConfigByKey(key);
} catch (Exception e) {
log.warn("【门诊分表】读取 {} 失败，按未配置处理：{}", key, e.toString());
return null;
}
}
/**
* 按指定模式建门诊分表并更新动态视图
* splitMode: 季度 / 年 / 月 / 不分表
* 表名：
* quarter -> pat_med_outhosp_20260103
* year -> pat_med_outhosp_2026
* month -> pat_med_outhosp_202601
*/
public String createPartitionTableAndRefreshView(String splitMode) {
LocalDate today = LocalDate.now();
int year = today.getYear();
int month = today.getMonthValue();
// 1. 根据模式计算新表名和模板表名
String newTableName;
String templateName;
if ("年".equals(splitMode)) {
// 按年：pat_med_outhosp_2026
newTableName = "pat_med_outhosp_" + year;
// 模板表：优先上一年分表
String prevYearTable = "pat_med_outhosp_" + (year - 1);
templateName = patMedOuthospMapper.tableExists(prevYearTable) > 0 ? prevYearTable : "pat_med_outhosp";
} else if ("月".equals(splitMode)) {
// 按月：pat_med_outhosp_202601
newTableName = String.format("pat_med_outhosp_%d%02d", year, month);
// 模板表：优先上一月分表
LocalDate prevMonth = today.minusMonths(1);
String prevMonthTable = String.format("pat_med_outhosp_%d%02d", prevMonth.getYear(), prevMonth.getMonthValue());
templateName = patMedOuthospMapper.tableExists(prevMonthTable) > 0 ? prevMonthTable : "pat_med_outhosp";
} else {
// 默认按季度：pat_med_outhosp_20260103
String quarterSuffix;
if (month <= 3) {
quarterSuffix = "0103";
} else if (month <= 6) {
quarterSuffix = "0406";
} else if (month <= 9) {
quarterSuffix = "0709";
} else {
quarterSuffix = "1012";
}
newTableName = "pat_med_outhosp_" + year + quarterSuffix;
templateName = resolveQuarterTemplateName(year, quarterSuffix);
}
// 2. 检查表是否已存在
if (patMedOuthospMapper.tableExists(newTableName) > 0) {
log.info("[建表] 表 {} 已存在，无需重复建表，但仍然尝试刷新存储过程（确保分表已纳入查询）", newTableName);
refreshOuthospView();
return null;
}
log.info("[建表] 模式={}，使用模板表 {} 创建 {}", splitMode, templateName, newTableName);
// 3. 创建新表（LIKE 模板表，只复制结构+索引，不含数据）
patMedOuthospMapper.createPatMedOuthosp(templateName, newTableName);
// 4. 设置自增主键
patMedOuthospMapper.createPatMedOuthospAutoAdd(newTableName);
// 5. 重建动态视图
refreshOuthospView();
log.info("[建表] 建表并刷新视图完成，新表：{}", newTableName);
return newTableName;
}
/**
* 季度分表模板确定：优先上一季度分表，没有则用主表
*/
private String resolveQuarterTemplateName(int year, String currentQuarterSuffix) {
String[][] quarters = {{"0103", "1012"}, {"0406", "0103"}, {"0709", "0406"}, {"1012", "0709"}};
String prevSuffix = null;
int prevYear = year;
for (String[] q : quarters) {
if (q[0].equals(currentQuarterSuffix)) {
prevSuffix = q[1];
if (currentQuarterSuffix.equals("0103")) {
prevYear = year - 1;
}
break;
}
}
if (prevSuffix != null) {
String prevTableName = "pat_med_outhosp_" + prevYear + prevSuffix;
if (patMedOuthospMapper.tableExists(prevTableName) > 0) {
return prevTableName;
}
}
return "pat_med_outhosp";
}
/**
* 刷新存储过程 sp_query_outhosp
* 逻辑：
* 1. 从 information_schema 动态获取 pat_med_outhosp 字段列表
* 2. 查出所有分表（后缀为4-8位纯数字）拼 UNION ALL
* 3. 根据字段是否存在动态决定存储过程参数和 WHERE 条件
* 4. 用原生 JDBC 执行（CREATE PROCEDURE 内含分号，MyBatis 无法处理）
*/
private void refreshOuthospView() {
// 1. 获取所有分表
List allTables = patMedOuthospMapper.getAllOuthospTableNames();
List partTables = allTables.stream().filter(t -> t.matches("pat_med_outhosp_[0-9]{4,8}")).sorted().collect(java.util.stream.Collectors.toList());
// 2. 动态获取主表字段列表
List columns = patMedOuthospMapper.getTableColumns("pat_med_outhosp");
Set cs = new HashSet<>(columns);
String colList = String.join(",", columns);
// 3. 拼 UNION ALL（del_flag 内嵌入 SET @sql='...' 中，用 ''0'' 转义）
StringBuilder union = new StringBuilder();
for (String tbl : partTables) {
union.append("SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0'' UNION ALL ");
}
union.append("SELECT ").append(colList).append(" FROM pat_med_outhosp WHERE del_flag=''0''");
// 4. 从 DB 元数据动态构建参数列表
// 排除无业务意义的管理字段
Set excludeCols = new HashSet<>(java.util.Arrays.asList("id", "del_flag", "create_by", "create_time", "update_by", "update_time", "remark"));
// 强制精确匹配的字段（字段名不符合 eqPattern 规则但业务上需精确查询）
Set forceEqCols = new HashSet<>(java.util.Arrays.asList("orgid", "guid", "campusid"));
// 精确匹配字段的关键词（含 no/code/flag/status/state/num/type/level/grade/sex/gender/phone/tel/id_）
java.util.regex.Pattern eqPattern = java.util.regex.Pattern.compile("(no|code|flag|status|state|num|type|level|grade|sex|gender|phone|tel|id_.*|.*_id)$", java.util.regex.Pattern.CASE_INSENSITIVE);
List> colMetaList = patMedOuthospMapper.getTableColumnMeta("pat_med_outhosp");
// [paramName, colName, kind, sqlType]
List activeParams = new ArrayList<>();
Set addedDateCols = new HashSet<>();
for (Map meta : colMetaList) {
String colName = meta.get("column_name");
String dataType = meta.get("data_type").toLowerCase();
String colType = meta.get("column_type").toUpperCase();
if (excludeCols.contains(colName)) continue;
switch (dataType) {
case "tinyint":
case "smallint":
case "int":
case "bigint":
case "integer":
case "decimal":
case "float":
case "double":
activeParams.add(new Object[]{"p_" + colName, colName, "num", colType});
break;
case "date":
case "datetime":
case "timestamp":
// 日期列生成区间参数，防止重复添加
if (addedDateCols.add(colName)) {
if (colName.equals("admitdate")) {
activeParams.add(new Object[]{"p_begin_" + colName, colName, "date_ge", "DATE"});
activeParams.add(new Object[]{"p_end_" + colName, colName, "date_le", "DATE"});
}
}
break;
default:
// varchar / char / text 等字符串类型
if (dataType.contains("char") || dataType.contains("text") || dataType.contains("enum")) {
if (colName.equals("deptcode")) {
activeParams.add(new Object[]{"p_" + colName, colName, "in", "TEXT"});
break;
}
if (colName.equals("hospitaldistrictcode")) {
activeParams.add(new Object[]{"p_" + colName, colName, "in", "TEXT"});
break;
}
String kind = forceEqCols.contains(colName) ? "eq" : (eqPattern.matcher(colName).find() ? "eq" : "like");
activeParams.add(new Object[]{"p_" + colName, colName, kind, colType});
}
break;
}
}
// 6. 拼参数定义（去除重复参数名，如 p_begin_xxx/p_end_xxx 同列）
StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_query_outhosp(");
Set addedParams = new LinkedHashSet<>();
for (Object[] m : activeParams) {
String pName = (String) m[0];
String dtype = (String) m[3];
if (addedParams.add(pName)) {
paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
}
}
paramDef.append("IN p_page_size INT,IN p_page_num INT)");
// 7. 拼存储过程主体
// 优化策略：用 @cond 变量收集所有过滤条件，然后注入到每个子查询的 WHERE 中
// 这样 MySQL 可以对每张分表直接走索引，避免全表扫描后外层再过滤
List body = new ArrayList<>();
body.add(paramDef.toString());
body.add("BEGIN");
body.add(" SET @cond='';");
// 7a. 动态拼接 @cond 条件片段
for (Object[] m : activeParams) {
String pName = (String) m[0];
String col = (String) m[1];
String kind = (String) m[2];
String line;
switch (kind) {
case "num":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
break;
case "eq":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
break;
case "like":
// 必须用 QUOTE() 把值展开到字符串里，否则 PREPARE 执行时参数名被当列名
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%'',',QUOTE(" + pName + "),',''%'')'); END IF;";
break;
case "date_ge":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
break;
case "date_le":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
break;
case "in":
// 参数为逗号分隔字符串，用 FIND_IN_SET 匹配字段在集合中
// 生成 SQL： FIND_IN_SET(col, 'val1,val2,...')
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
break;
default:
line = "";
}
if (!line.isEmpty()) body.add(line);
}
// 7b. 构建 UNION ALL SQL：每个子查询的 WHERE 内直接注入 @cond
// 最终存储过程里执行的 SQL 类似：
// SELECT cols FROM tbl1 WHERE del_flag='0' AND orgid=? AND ...
// UNION ALL SELECT cols FROM tbl2 WHERE del_flag='0' AND orgid=? AND ...
// ORDER BY update_time DESC LIMIT ? OFFSET ?
//
// 实现方式：用 CONCAT(单引号字符串, @cond, ...) 拼接
// del_flag=''0'' 在存储过程内单引号字符串中表示一个 '0'
List allQueryTables = new ArrayList<>(partTables);
allQueryTables.add("pat_med_outhosp");
StringBuilder concatArgs = new StringBuilder();
for (int i = 0; i < allQueryTables.size(); i++) {
String tbl = allQueryTables.get(i);
if (i > 0) {
concatArgs.append(",' UNION ALL SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
} else {
concatArgs.append("'SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
}
}
String orderCol = cs.contains("update_time") ? "update_time" : "id";
body.add(" SET @sql=CONCAT(" + concatArgs + ",' ORDER BY " + orderCol + " DESC');");
body.add(" IF p_page_size IS NOT NULL AND p_page_num IS NOT NULL THEN");
body.add(" SET @sql=CONCAT(@sql,' LIMIT ',p_page_size,' OFFSET ',p_page_num);");
body.add(" END IF;");
body.add(" PREPARE stmt FROM @sql;");
body.add(" EXECUTE stmt;");
body.add(" DEALLOCATE PREPARE stmt;");
body.add("END");
String procSql = String.join("\n", body);
log.debug("[刷新存储过程] DDL=\n{}", procSql);
// 8. 通过原生 JDBC 执行（CREATE PROCEDURE 含分号，MyBatis 不支持）
// 策略：
// - 存储过程不存在 → 全量创建
// - 存储过程已存在 → 只把 DB 中还没有的分表以 UNION ALL 形式追加进 SET @sql=CONCAT(...) 里，
// 其余所有条件逻辑（人工改过的模糊/区间等）原样保留，绝不覆盖
try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {
// 先查 DB 中是否存在该存储过程及其完整 DDL
String showCreateDdl = null;
try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_query_outhosp")) {
if (rs.next()) {
showCreateDdl = rs.getString("Create Procedure");
}
} catch (Exception ignored) {
// 存储过程不存在时 SHOW CREATE 会抛异常，忽略
}
if (showCreateDdl == null) {
// 存储过程不存在，全量创建
jdbcStmt.execute(procSql);
log.info("[刷新存储过程] sp_query_outhosp 首次创建，分表数量：{}", partTables.size());
} else {
// 存储过程已存在：找出 DB 里已有哪些分表，把新增的追加进去
// 找出当前 DDL 里已出现的所有 pat_med_outhosp_xxx 表名
java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
Set existingTables = new java.util.LinkedHashSet<>();
while (tblMatcher.find()) {
existingTables.add(tblMatcher.group(0));
}
// 找出本次新建后还没进入存储过程的分表
List newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());
if (newTables.isEmpty()) {
log.info("[刷新存储过程] 无新增分表，存储过程无需更新");
} else {
// 直接从 DDL 里提取字段列表（避免与 Java 动态查表字段不一致）
// DDL 里字段列表就在： SELECT 后面到 FROM 前面
java.util.regex.Matcher colMatcher = java.util.regex.Pattern.compile("SELECT\\s+(.+?)\\s+FROM\\s+pat_med_outhosp", java.util.regex.Pattern.CASE_INSENSITIVE).matcher(showCreateDdl);
String existingColList = colMatcher.find() ? colMatcher.group(1).trim() : colList;
// 精确锚点：',@cond,' UNION ALL SELECT cols FROM pat_med_outhosp WHERE
String mainTableAnchor = "',@cond,' UNION ALL SELECT " + existingColList + " FROM pat_med_outhosp WHERE ";
int mainAnchorIdx = showCreateDdl.indexOf(mainTableAnchor);
// 字段列表匹配不到时，简化定位
if (mainAnchorIdx < 0) {
// 找 ',@cond,' UNION ALL SELECT ... FROM pat_med_outhosp WHERE
// 先找 FROM pat_med_outhosp WHERE，再往前找 ',@cond,'
int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
if (fromIdx >= 0) {
String before = showCreateDdl.substring(0, fromIdx);
int condIdx = before.lastIndexOf("',@cond,'");
if (condIdx >= 0) mainAnchorIdx = condIdx;
}
}
if (mainAnchorIdx < 0) {
log.warn("[刷新存储过程] 未找到主表锚点，跳过追加");
} else {
// 新表片段：每张新表追加一段 ',@cond,' UNION ALL SELECT cols FROM 新表 WHERE del_flag=''0''
// 插在主表锚点前面，主表锚点本身（',@cond,' UNION ALL ...主表...）保持不动
StringBuilder newTablesFragment = new StringBuilder();
for (String nt : newTables) {
newTablesFragment.append("',@cond,' UNION ALL SELECT ").append(existingColList).append(" FROM ").append(nt).append(" WHERE del_flag=''0''");
}
// 插入到主表 ',@cond,' 片段前面
String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newTablesFragment + showCreateDdl.substring(mainAnchorIdx);
log.debug("[刷新存储过程] 更新后 DDL=\n{}", updatedDdl);
// 安全保护：DROP 前先保存原 DDL，CREATE 失败则立刻用原 DDL 恢复
jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_query_outhosp");
try {
jdbcStmt.execute(updatedDdl);
log.info("[刷新存储过程] sp_query_outhosp 追加新分表 {}，原有条件保持不变", newTables);
} catch (Exception createEx) {
log.error("[刷新存储过程] 新 DDL 执行失败，正在用原 DDL 恢复, updatedDdl=\n{}", updatedDdl, createEx);
try {
jdbcStmt.execute(showCreateDdl);
log.info("[刷新存储过程] sp_query_outhosp 已用原 DDL 恢复成功");
} catch (Exception rollbackEx) {
log.error("[刷新存储过程] 原 DDL 恢复也失败！存储过程丢失！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
}
}
}
}
}
} catch (Exception e) {
log.error("[刷新存储过程] sp_query_outhosp 更新失败, DDL=\n{}", procSql, e);
}
// 9. 同步刷新 count 存储过程
refreshOuthospCountProc(partTables, activeParams, colList);
}
/**
* 刷新计数存储过程 sp_count_outhosp
* 与 sp_query_outhosp 参数完全一致，只返回总条数，不分页
*/
private void refreshOuthospCountProc(List partTables, List activeParams, String colList) {
// 拼参数定义（去掉 p_page_size/p_page_num）
StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_count_outhosp(");
Set addedParams = new LinkedHashSet<>();
for (Object[] m : activeParams) {
String pName = (String) m[0];
String dtype = (String) m[3];
if (addedParams.add(pName)) {
paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
}
}
// 去掉最后的多余逗号
String paramDefStr = paramDef.toString();
if (paramDefStr.endsWith(",")) {
paramDefStr = paramDefStr.substring(0, paramDefStr.length() - 1);
}
paramDefStr += ")";
List body = new ArrayList<>();
body.add(paramDefStr);
body.add("BEGIN");
body.add(" SET @cond='';");
// 条件片段（与 sp_query_outhosp 完全一致）
for (Object[] m : activeParams) {
String pName = (String) m[0];
String col = (String) m[1];
String kind = (String) m[2];
String line;
switch (kind) {
case "num":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
break;
case "eq":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
break;
case "like":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%''," + pName + ",''%'')'); END IF;";
break;
case "date_ge":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
break;
case "date_le":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
break;
case "in":
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
break;
default:
line = "";
}
if (!line.isEmpty()) body.add(line);
}
// 拼 COUNT UNION ALL SQL：各分表各计一次再汇总
// SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM tbl1 WHERE del_flag=''0'' ...
// UNION ALL SELECT COUNT(1) cnt FROM tbl2 WHERE del_flag=''0'' ...) t
List allQueryTables = new ArrayList<>(partTables);
allQueryTables.add("pat_med_outhosp");
// ⚠️ 原行为保留：这个 concatArgs 拼出来后并未被使用（原 L486-496），原样保留
StringBuilder concatArgs = new StringBuilder();
concatArgs.append("'SELECT SUM(cnt) FROM (");
for (int i = 0; i < allQueryTables.size(); i++) {
String tbl = allQueryTables.get(i);
if (i > 0) {
concatArgs.append(" UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
} else {
concatArgs.append("SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
}
}
concatArgs.append(") t';");
// 重新拼：每个子表 WHERE del_flag=''0'' 后拼 @cond
// 存储过程内 '' 表示一个单引号，所以 del_flag=''0'' 正确
StringBuilder concatArgs2 = new StringBuilder();
for (int i = 0; i < allQueryTables.size(); i++) {
String tbl = allQueryTables.get(i);
if (i == 0) {
concatArgs2.append("'SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
} else {
concatArgs2.append(",' UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
}
}
concatArgs2.append(",' ) t'");
body.add(" SET @sql=CONCAT(" + concatArgs2 + ");");
body.add(" PREPARE stmt FROM @sql;");
body.add(" EXECUTE stmt;");
body.add(" DEALLOCATE PREPARE stmt;");
body.add("END");
String procSql = String.join("\n", body);
log.debug("[刷新count存储过程] DDL=\n{}", procSql);
try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {
String showCreateDdl = null;
try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_count_outhosp")) {
if (rs.next()) showCreateDdl = rs.getString("Create Procedure");
} catch (Exception ignored) {
}
if (showCreateDdl == null) {
jdbcStmt.execute(procSql);
log.info("[刷新count存储过程] sp_count_outhosp 首次创建，分表数量：{}", partTables.size());
} else {
// 只追加新分表，不覆盖已有条件
java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
Set existingTables = new java.util.LinkedHashSet<>();
while (tblMatcher.find()) existingTables.add(tblMatcher.group(0));
List newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());
if (newTables.isEmpty()) {
log.info("[刷新count存储过程] 无新增分表，无需更新");
return;
}
// 从 DDL 里提取 COUNT(1) cnt FROM 后面的字段模式无案，COUNT存储过程没有字段列表
// 定位主表锚点：',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE
String mainAnchor = "',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE ";
int mainAnchorIdx = showCreateDdl.indexOf(mainAnchor);
if (mainAnchorIdx < 0) {
int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
if (fromIdx >= 0) {
String before = showCreateDdl.substring(0, fromIdx);
int condIdx = before.lastIndexOf("',@cond,'");
if (condIdx >= 0) mainAnchorIdx = condIdx;
}
}
if (mainAnchorIdx < 0) {
log.warn("[刷新count存储过程] 未找到主表锚点，跳过追加");
return;
}
StringBuilder newFrag = new StringBuilder();
for (String nt : newTables) {
newFrag.append("',@cond,' UNION ALL SELECT COUNT(1) cnt FROM ").append(nt).append(" WHERE del_flag=''0''");
}
String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newFrag + showCreateDdl.substring(mainAnchorIdx);
log.debug("[刷新count存储过程] 更新后 DDL=\n{}", updatedDdl);
jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_count_outhosp");
try {
jdbcStmt.execute(updatedDdl);
log.info("[刷新count存储过程] sp_count_outhosp 追加新分表 {}", newTables);
} catch (Exception createEx) {
log.error("[刷新count存储过程] 新 DDL 失败，恢复原 DDL, updatedDdl=\n{}", updatedDdl, createEx);
try {
jdbcStmt.execute(showCreateDdl);
log.info("[刷新count存储过程] 已恢复原 DDL");
} catch (Exception rollbackEx) {
log.error("[刷新count存储过程] 恢复也失败！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
}
}
}
} catch (Exception e) {
log.error("[刷新count存储过程] sp_count_outhosp 更新失败", e);
}
}
}