From 36dc14af5a78be3b3eb941ddc13a22d3aaf1fe3a Mon Sep 17 00:00:00 2001
From: 许鹏程 <1821349743@qq.com>
Date: 星期一, 15 一月 2024 18:30:46 +0800
Subject: [PATCH] commit

---
 product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java |  118 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 117 insertions(+), 1 deletions(-)

diff --git a/product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java b/product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java
index 46071e5..79bb529 100644
--- a/product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java
+++ b/product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java
@@ -1,5 +1,9 @@
 package com.product.data.center.service;
 
+import cn.hutool.core.date.DateField;
+import cn.hutool.core.date.DateTime;
+import cn.hutool.core.date.DateUtil;
+import cn.hutool.core.date.LocalDateTimeUtil;
 import cn.hutool.core.util.IdUtil;
 import com.product.admin.service.PublicService;
 import com.product.common.lang.StringUtils;
@@ -14,6 +18,7 @@
 import com.product.data.center.config.ErrorCode;
 import com.product.data.center.service.ide.ICommonService;
 import com.product.data.center.utils.CallBackReturnValue;
+import com.product.datasource.entity.DataBaseEntity;
 import com.product.quartz.service.impl.SysJobService;
 import com.product.util.BaseUtil;
 import org.springframework.beans.factory.annotation.Value;
@@ -21,9 +26,13 @@
 
 import javax.annotation.Resource;
 import java.text.DecimalFormat;
+import java.time.LocalDate;
 import java.util.Date;
+import java.util.List;
+import java.util.Set;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
+import java.util.stream.Collectors;
 
 /**
  * @Author cheng
@@ -49,7 +58,114 @@
 	private String dataSystemName;
 
 	public void deleteCenterLog() {
-		this.getBaseDao().executeUpdate("DELETE FROM product_sys_data_center_log WHERE uuid in (select * from product_sys_data_center_log_del_v) limit 10000");
+		try {
+			//鍦╩ysql鐨刬nformation_schema 琛ㄤ腑鏌ヨ鎸囧畾琛ㄦ槸鍚﹀瓨鍦�
+			String tableName = "da_product_sys_data_center_log";
+
+			//褰掓。7澶╁墠鐨勬暟鎹�
+			//鎴鏃ユ湡
+			Date endTime = DateUtil.offset(new Date(), DateField.DAY_OF_YEAR, -7);
+
+			//鏍煎紡鍖栨棩鏈�
+			String endTimeStr = DateUtil.format(endTime, "yyyy-MM-dd");
+
+			//鏍规嵁鎴鏃ユ湡鏌ヨ琛ㄤ腑鏈�澶с�佹渶灏忕殑鍒涘缓鏃堕棿鏍规嵁鍒涘缓鏃堕棿杩涜鍒嗚〃鎸夋湀鍒嗚〃
+			String sql = "select min(created_utc_datetime) as minTime,max(created_utc_datetime) as maxTime from product_sys_data_center_log where created_utc_datetime <now() + interval -7 day and (result = 1 or ( deal_flag = 1 AND deal_result = 1 ) )";
+
+			FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(sql, new Object[]{}, false);
+
+			if (fs == null) {
+				return;
+			}
+			//鏈�灏忔椂闂�
+			Date minTime = fs.getDate("minTime");
+
+			//鏈�澶ф椂闂�
+			Date maxTime = fs.getDate("maxTime");
+
+			//鏍规嵁鏈�灏忔椂闂村拰鏈�澶ф椂闂磋幏鍙栧尯闂村唴锛堝寘鍚渶灏忔椂闂村拰鏈�澶ф椂闂达級鐨勫勾鏈堟棩鏈熸牸寮忎负yyyy-MM
+
+			List<DateTime> tableSuffixList = DateUtil.rangeToList(minTime, maxTime, DateField.MONTH);
+
+			//鏌ヨ鏁版嵁搴撲腑鏄惁瀛樺湪琛�
+
+			Set<String> tableNameSet = tableSuffixList.stream().map(e -> tableName + "_" + DateUtil.format(e, "yyyyMM")).collect(Collectors.toSet());
+
+			//鏌ヨ鏁版嵁搴撲腑瀛樺湪鐨勮〃
+			String dataBaseName = getBaseDao().getDataBaseName();
+			sql = "select TABLE_NAME table_name from information_schema.tables where table_schema = ? and " + BaseUtil.buildQuestionMarkFilter("TABLE_NAME", tableNameSet.size(), true);
+
+			Object[] params = new Object[tableNameSet.size() + 1];
+			params[0] = dataBaseName;
+			int index = 1;
+			for (String tableNameStr : tableNameSet) {
+				params[index] = tableNameStr;
+				index++;
+			}
+			DataTableEntity dataTableEntity = getBaseDao().listTable(sql, params);
+
+			if (!DataTableEntity.isEmpty(dataTableEntity)) {
+				for (int i = 0; i < dataTableEntity.getRows(); i++) {
+					String tableNameStr = dataTableEntity.getString(i, "table_name");
+					tableNameSet.remove(tableNameStr);
+				}
+			}
+			//鍒涘缓涓嶅瓨鍦ㄧ殑琛紝琛ㄧ粨鏋勪笌鍘熻〃涓�鑷�
+			for (String tableNameStr : tableNameSet) {
+				String createTableSql = "create table " + tableNameStr + " like product_sys_data_center_log";
+				getBaseDao().executeUpdate(createTableSql, new Object[]{});
+			}
+			//鎻掑叆鏁版嵁鏍规嵁鍒涘缓鏃堕棿鐨勫勾鏈堝垎鍒彃鍏ュ埌瀵瑰簲鐨勮〃涓�
+			for (DateTime dateTime : tableSuffixList) {
+				String insertSql = "insert into " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " select * from product_sys_data_center_log where created_utc_datetime >= str_to_date(concat(?, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s') and created_utc_datetime < str_to_date(concat(?, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s') + interval 1 month" +
+						" and id >(select ifnull(max(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) and (result = 1 or ( deal_flag = 1 AND deal_result = 1 ) )";
+				getBaseDao().executeUpdate(insertSql, new Object[]{DateUtil.format(dateTime, "yyyy-MM"), DateUtil.format(dateTime, "yyyy-MM")});
+				//鍒犻櫎鏁版嵁宸叉彃鍏ョ殑鏁版嵁
+				StringBuilder deleteSql = new StringBuilder();
+				deleteSql.append(" DELETE FROM product_sys_data_center_log a INNER JOIN ");
+				deleteSql.append(" product_sys_data_center_log b on a.id=b.pre_step_uuid and a.type=1 and b.type=2 ");
+				deleteSql.append(" where ");
+				deleteSql.append(" a.id>= (select ifnull(min(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" and a.id <= (select ifnull(max(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+				deleteSql.append(" a.type=1 ");
+				//鍏堝垹闄ら噰闆嗘棩蹇楁暟鎹�
+				getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
+
+				deleteSql.setLength(0);
+				//鍐嶅垹闄ゆ彁鍙栨棩蹇楁暟鎹�
+				deleteSql.append(" DELETE FROM product_sys_data_center_log a LEFT JOIN ");
+				deleteSql.append(" product_sys_data_center_log b on b.id=a.pre_step_uuid and b.type=1 and a.type=2 ");
+				deleteSql.append(" where ");
+				deleteSql.append(" a.id>= (select ifnull(min(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" and a.id <= (select ifnull(max(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+				deleteSql.append(" a.type=2 and b.id is null  ");
+
+				getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
+
+				//鍐嶅垹闄ゅ叾浠栨棩蹇楁暟鎹�
+				deleteSql.append(" DELETE FROM product_sys_data_center_log a  ");
+				deleteSql.append(" where ");
+				deleteSql.append(" a.id>= (select ifnull(min(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" and a.id <= (select ifnull(max(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) ");
+				deleteSql.append(" (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+				deleteSql.append(" a.type>2 ");
+				getBaseDao().executeUpdate(deleteSql.toString());
+			}
+
+//			fs = null;
+//			do {
+//				this.getBaseDao().executeUpdate("DELETE FROM product_sys_data_center_log WHERE uuid in (select * from (select * from product_sys_data_center_log_del_v limit 10000) a ) ");
+//				//妫�鏌ユ槸鍚﹁繕鏈夋暟鎹�
+//				sql = "select 1 as del_count from product_sys_data_center_log_del_v limit 1";
+//				//妫�鏌ユ槸鍚﹁繕鏈夋暟鎹�
+//				fs = getBaseDao().getFieldSetEntityBySQL(sql, new Object[]{}, false);
+//			} while (fs != null && fs.getInteger("del_count") != null && fs.getInteger("del_count") > 0);
+		} catch (Exception e) {
+			e.printStackTrace();
+			throw e;
+		}
 	}
 
 

--
Gitblit v1.9.2