From 914921a6b272b18a436f05422e33f9d963f48577 Mon Sep 17 00:00:00 2001
From: 许鹏程 <1821349743@qq.com>
Date: 星期四, 29 六月 2023 10:36:31 +0800
Subject: [PATCH] poi、easyexcel、poi-tl升级 ，合并空调中的报表、数据源模块

---
 src/main/java/com/product/server/report/util/QuerySqlParseUtil.java |  326 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 326 insertions(+), 0 deletions(-)

diff --git a/src/main/java/com/product/server/report/util/QuerySqlParseUtil.java b/src/main/java/com/product/server/report/util/QuerySqlParseUtil.java
new file mode 100644
index 0000000..f00b391
--- /dev/null
+++ b/src/main/java/com/product/server/report/util/QuerySqlParseUtil.java
@@ -0,0 +1,326 @@
+package com.product.server.report.util;
+
+import com.alibaba.fastjson.JSONObject;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+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.config.ErrorCode;
+import com.product.datasource.config.DataBaseType;
+import com.product.datasource.dao.Dao;
+import com.product.server.report.config.CmnConst;
+import com.product.server.report.config.SystemCode;
+import com.product.util.BaseUtil;
+import org.apache.commons.lang3.StringUtils;
+
+import java.util.*;
+
+public class QuerySqlParseUtil {
+
+	/**
+	 * 鑾峰彇涓庤〃鐩稿叧鐨勫勾琛� SQL杩囨护鏉′欢
+	 *
+	 * @param tableNames 琛ㄥ悕鏁扮粍
+	 * @return
+	 */
+	public static String getTableOfYearSQLFilter(Object[] tableNames) {
+		StringBuilder tableFilter = new StringBuilder();
+		for (int i = 0; i < tableNames.length; i++) {
+			String tableName = (String) tableNames[i];
+			if (!BaseUtil.strIsNull(tableName)) {
+				if (i > 0) {
+					tableFilter.append(" AND ");
+				} else {
+					tableFilter.append(" WHERE ");
+				}
+				tableFilter.append("table_name LIKE ?");
+			} else {
+				throw new BaseException("", "");
+			}
+		}
+		return tableFilter.toString();
+	}
+
+	/**
+	 * 鐢熸垚闇�姹係QL
+	 * 澶勭悊锛氬皢鎵�鏈夊勾琛ㄨ仈鍚堬紝鐢熸垚涓存椂鏌ヨSQL
+	 * 妗堜緥锛歐ITH tableName1 AS (SELECT field1,field2... FROM tableName1_2022 UNION ALL SELECT field1,field2... FROM tableName1_2023),
+	 * tableName2 AS (SELECT field3,field4... FROM tableName2_2022 UNION ALL SELECT field3,field4... FROM tableName2_2023)
+	 *
+	 * @param tableNames  琛ㄥ悕鏁扮粍
+	 * @param dtYearTable 琛ㄥ悕涓庡勾琛ㄥ悕闆�
+	 * @return
+	 */
+	public static String createFinalQuerySQL(Object[] tableNames, DataTableEntity dtYearTable) {
+		StringBuilder tableFilter = new StringBuilder();
+		JSONObject jsonTableAndYear = convertJson(dtYearTable);
+		StringBuilder withSql = new StringBuilder();
+		for (int i = 0; i < tableNames.length; i++) {
+			String tableName = (String) tableNames[i];
+			String tableYear = (String) jsonTableAndYear.get(tableName);
+			if (!BaseUtil.strIsNull(tableYear)) {
+				if (i == 0) {
+					withSql.append(" WITH ");
+				}
+				withSql.append(tableName);
+				withSql.append(" AS(");
+				String[] tableYears = tableYear.split(",");
+				if (tableYear.length() > 1) {
+					for (int j = 0; j < tableYears.length; j++) {
+						withSql.append(" SELECT * FROM ");
+						withSql.append(tableYears[j]);
+						if (j < tableYears.length - 1) {
+							withSql.append(" UNION ALL ");
+						}
+					}
+				} else {
+					withSql.append(" SELECT * FROM ");
+					withSql.append(tableYears[0]);
+				}
+				withSql.append("),");
+			}
+		}
+		if (tableFilter.toString().endsWith(",")) {
+			tableFilter.toString().substring(0, tableFilter.toString().length() - 1);
+		}
+
+		return tableFilter.toString();
+	}
+
+	/**
+	 * DataTableEntity 杞� JSONObject
+	 * JSONObject<琛ㄥ悕,"琛ㄥ悕_2022,琛ㄥ悕_2023,琛ㄥ悕_2024...">
+	 *
+	 * @param dtYearTable
+	 * @return
+	 */
+	public static JSONObject convertJson(DataTableEntity dtYearTable) {
+		JSONObject jsonTableAndYear = new JSONObject();
+		for (int i = 0; i < dtYearTable.getRows(); i++) {
+			FieldSetEntity fseYearTable = dtYearTable.getFieldSetEntity(i);
+			String tableName = fseYearTable.getString(CmnConst.TABLE_NAME);
+			String tableYear = fseYearTable.getString("table_year");
+			jsonTableAndYear.put(tableName, tableYear);
+		}
+		return jsonTableAndYear;
+	}
+
+	/**
+	 * 鑾峰彇mysql搴撴墍鏈夎〃鍚嶏紝渚嬪锛岀粰琛ㄥ悕test锛岃幏鍙栨暟鎹簱涓墍鏈夌殑test骞翠唤鍒嗚〃锛宼est_2019锛宼est_2020...
+	 *
+	 * @param dao
+	 * @param dbName    鏁版嵁搴撳悕绉�
+	 * @param tableName 琛ㄥ悕
+	 * @return
+	 */
+	public static Set<String> getAllTableName(Dao dao, String dbName, String tableName) {
+		return getAllTableName(dao, null, dbName, tableName);
+	}
+
+	public static Set<String> getAllTableName(BaseDao baseDao, String dbName, String tableName) {
+		return getAllTableName(null, baseDao, dbName, tableName);
+	}
+
+	private static Set<String> getAllTableName(Dao dao, BaseDao baseDao, String dbName, String tableName) {
+		if (dao != null && !DataBaseType.MYSQL.equals(dao.getDataBaseType())) {
+			throw new BaseException(ErrorCode.NONSUPPORT_DATABASE_TYPE);
+		}
+		String sql = "select table_name from information_schema.`TABLES` where table_schema=? and table_name like concat(?,'%') order by table_name";
+		DataTableEntity dte = new DataTableEntity();
+		if (dao != null) {
+			dte = dao.getList(sql, new Object[]{dbName, tableName});
+		}
+		if (baseDao != null) {
+			dte = baseDao.listTable(sql, new Object[]{dbName, tableName});
+		}
+		Set<String> set = Sets.newLinkedHashSet();
+		FieldSetEntity fse;
+		String curtableName;
+		for (int i = 0; i < dte.getRows(); i++) {
+			fse = dte.getFieldSetEntity(i);
+			curtableName = StringUtils.isEmpty(fse.getString("TABLE_NAME")) ? fse.getString("table_name") : fse.getString("TABLE_NAME");
+			if (curtableName.matches(tableName + "_?\\d{4,6}")) {
+				set.add(curtableName);
+			}
+		}
+		return set;
+	}
+
+	/**
+	 * mysql-鑾峰彇with鑱斿悎琛ㄦ煡璇ql
+	 *
+	 * @param tableAlias        琛ㄥ埆鍚嶏紝with涔嬪悗鐨勯偅涓�
+	 * @param tableCollection   鍒嗚〃闆嗗悎锛屼緥濡傚勾浠藉垎琛紝灏辨槸澶氫釜骞翠唤琛ㄧ殑闆嗗悎
+	 * @param fieldCollection   鏌ヨ瀛楁闆嗗悎锛屾病鏈夊彲浠ョ洿鎺ョ粰null锛屼細榛樿鏌ヨ鎵�鏈夊瓧娈点�傜壒娈婏細鑻ユ槸瑕佹煡璇㈠綋鍓嶈〃鍚嶏紝閭ｄ箞瀛楁鍚嶄负鈥渰#table_name#}鈥濓紝涔嬪悗鐨勫埆鍚嶄负鈥淿table_name鈥�
+	 *                          涔熷氨鏄鍋囪瀛楁涓� name,{#table_name#} 琛ㄥ悕涓簍est_table锛岄偅涔堣В鏋愮殑sql涓衡�渟elect name,'test_table' _table_name from test_table鈥�
+	 * @param onlyStructureFlag 鍙煡璇㈢粨鏋勭殑鏍囪瘑锛岃嫢涓簍rue锛屽垯鍙煡璇㈢粨鏋勶紝鍗充负with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
+	 * @param filter            杩囨护鏉′欢
+	 * @param tableAliasAlias   琛ㄥ埆鍚嶇殑鍒悕锛屽綋with鍚庡瓨鍦ㄦ煡璇㈢浉鍚岀殑琛ㄤ絾鏄潯浠朵笉鍚岀殑鏃跺�欎娇鐢ㄨ〃鍒悕鐨勫埆鍚嶄綔涓簑ith鍚庣殑鍒悕
+	 * @return
+	 */
+	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter, String tableAliasAlias) {
+		StringBuilder sql = new StringBuilder(128);
+		if (StringUtils.isEmpty(tableAlias) || tableCollection == null || tableCollection.isEmpty()) {
+			return sql;
+		}
+		StringBuilder content = new StringBuilder(128);
+		tableCollection.forEach(tableName -> {
+			if (content.length() > 0) {
+				content.append("\n    union all");
+			}
+			String fieldContent = "*";
+			if (fieldCollection != null && !fieldCollection.isEmpty()) {
+				fieldContent = BaseUtil.collection2String(fieldCollection);
+				fieldContent = fieldContent.replace("{#table_name#}", "'" + tableName + "' _table_name");
+			}
+			content.append("\n    select ").append(fieldContent).append(" from ").append(tableName);
+			if (!StringUtils.isEmpty(filter) && !StringUtils.isEmpty(filter.trim())) {
+				content.append(" ").append(filter.replaceAll("\\s*\\n\\s*", " "));
+			}
+		});
+		sql.append(tableAliasAlias).append(" as (").append(content);
+		if (onlyStructureFlag) {
+			sql.append(" limit 1");
+		}
+		sql.append("\n)");
+		return sql;
+	}
+
+	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter) {
+		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, filter, tableAlias);
+	}
+
+	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag) {
+		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, null);
+	}
+
+	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection) {
+		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, false);
+	}
+
+	/**
+	 * mysql-鑾峰彇with鑱斿悎琛ㄦ煡璇ql
+	 *
+	 * @param tableAlias      琛ㄥ埆鍚嶏紝with涔嬪悗鐨勯偅涓�
+	 * @param tableCollection 鍒嗚〃闆嗗悎锛屼緥濡傚勾浠藉垎琛紝灏辨槸澶氫釜骞翠唤琛ㄧ殑闆嗗悎
+	 * @return
+	 */
+	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection) {
+		return getUnionTableSql(tableAlias, tableCollection, null);
+	}
+
+	/**
+	 * 瑙ｆ瀽sql涓殑鍒嗚〃锛屽皢鍏惰浆鍖栦负with璇彞锛屽叾绗﹀彿鍐呯殑鍐呭浣滀负琛ㄥ埆鍚嶄娇鐢�
+	 *
+	 * @param baseDao
+	 * @param sql               寰呰В鏋愮殑sql
+	 * @param year              鏌ヨ鏈�杩戠殑骞存暟锛�0鎴栬�呰礋鏁拌〃绀烘墍鏈夛紝鎸夌収褰撳墠涓虹涓�骞达紝鍙煡璇㈠綋骞达紝鍒欑粰1锛涙煡璇㈠綋鍓嶅拰鍘诲勾锛屽垯缁�2锛涙煡璇㈠綋骞淬�佸幓骞淬�佸墠骞达紝鍒欑粰3锛屼互姝ょ被鎺�...
+	 * @param onlyStructureFlag 鍙煡璇㈢粨鏋勭殑鏍囪瘑锛岃嫢涓簍rue锛屽垯鍙煡璇㈢粨鏋勶紝鍗充负with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
+	 * @return
+	 */
+	public static String parseSplitTableSql(BaseDao baseDao, String sql, int year, boolean onlyStructureFlag) {
+		boolean allTableFlag = year <= 0;
+		StringBuilder parseSql = new StringBuilder(128);
+		String regexp = "\\[=((?!\\[=).)*=\\]";
+		List<String> suitList = BaseUtil.getSuitContent(sql, regexp, true);
+		if (suitList.isEmpty()) {
+			return sql;
+		} else {
+			Set<String> suitSet = Sets.newLinkedHashSet();
+			suitSet.addAll(suitList);
+
+			// 鑾峰彇闇�瑕佺殑骞翠唤
+			List<String> yearList = Lists.newArrayList();
+			if (!allTableFlag) {
+				Calendar c = Calendar.getInstance();
+				for (int i = 0; i < year; i++) {
+					yearList.add(String.valueOf(c.get(Calendar.YEAR)));
+					c.add(Calendar.YEAR, -1);
+				}
+			}
+			// 鑾峰彇瀵瑰簲瀛樺湪琛�
+			String tableInfo;
+			String tableAlias;// 琛ㄥ埆鍚嶏紝鏌ヨ鐢ㄧ殑鍓嶇紑锛岄�氬父with鍚庣殑鍒悕
+			String tableAliasAlias;// 琛ㄥ埆鍚嶇殑鍒悕锛屽綋with鍚庡瓨鍦ㄦ煡璇㈢浉鍚岀殑琛ㄤ絾鏄潯浠朵笉鍚岀殑鏃跺�欎娇鐢ㄨ〃鍒悕鐨勫埆鍚嶄綔涓簑ith鍚庣殑鍒悕
+			Set<String> allTableSet;
+			Set<String> needTableSet;
+			for (String suitContent : suitSet) {
+				Set<String> fieldSet = Sets.newLinkedHashSet();
+				String filterInfo = null;
+				needTableSet = Sets.newLinkedHashSet();
+				tableInfo = suitContent.replace("[=", "").replace("=]", "").trim();
+				if (tableInfo.contains("{")) {
+					tableAlias = tableInfo.substring(tableInfo.indexOf("{") + 1, tableInfo.indexOf("}"));
+					if (tableAlias.contains(" ")) {
+						tableAliasAlias = tableAlias.substring(tableAlias.lastIndexOf(" ") + 1);
+						tableAlias = tableAlias.substring(0, tableAlias.indexOf(" "));
+					} else {
+						tableAliasAlias = tableAlias;
+					}
+					int fromIndex = tableInfo.indexOf("}") + 1;
+					if (tableInfo.indexOf("{", fromIndex) > -1) {
+						String fieldInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.indexOf("}", fromIndex));
+						if (!StringUtils.isEmpty(fieldInfo)) {
+							fieldSet.addAll(Arrays.asList(fieldInfo.split(",")));
+						}
+						fromIndex = tableInfo.indexOf("}", fromIndex) + 1;
+						if (tableInfo.indexOf("{", fromIndex) > -1) {
+							filterInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.lastIndexOf("}"));
+						}
+					}
+				} else {
+					tableAlias = tableInfo;
+					tableAliasAlias = tableAlias;
+				}
+				sql = sql.replace(suitContent, tableAliasAlias);
+				allTableSet = getAllTableName(baseDao, baseDao.getDataBaseName(), tableAlias);
+				if (allTableFlag) {
+					needTableSet = allTableSet;
+				} else {
+					if (!yearList.isEmpty()) {
+						StringBuilder regexpSb = new StringBuilder(64);
+						for (String yearStr : yearList) {
+							if (regexpSb.length() > 0) {
+								regexpSb.append("|");
+							}
+							regexpSb.append(tableAlias).append("_").append(yearStr).append("\\d{0,2}");
+						}
+						regexp = regexpSb.toString();
+						for (String tableName : allTableSet) {
+							if (tableName.matches(regexp)) {
+								needTableSet.add(tableName);
+							}
+						}
+					}
+				}
+				// 鑾峰彇sql
+				StringBuilder contentSb = getUnionTableSql(tableAlias, needTableSet, fieldSet, onlyStructureFlag, filterInfo, tableAliasAlias);
+				if (contentSb.length() > 0) {
+					if (parseSql.length() > 0) {
+						parseSql.append(",");
+					}
+					parseSql.append(contentSb);
+				} else {
+					throw new BaseException(SystemCode.NO_MATCH_TABLE.getValue(), SystemCode.NO_MATCH_TABLE.getText() + " table_name:" + tableAlias);
+				}
+			}
+			return new StringBuilder().append("with ").append(parseSql).append("\n").append(sql).toString();
+		}
+	}
+
+	public static String parseSplitTableSql(BaseDao baseDao, String sql, int year) {
+		return parseSplitTableSql(baseDao, sql, year, false);
+	}
+
+	public static String parseSplitTableSql(BaseDao baseDao, String sql, boolean onlyStructureFlag) {
+		return parseSplitTableSql(baseDao, sql, 0, onlyStructureFlag);
+	}
+
+	public static String parseSplitTableSql(BaseDao baseDao, String sql) {
+		return parseSplitTableSql(baseDao, sql, 0, false);
+	}
+
+
+}

--
Gitblit v1.9.2