From a0c549301bd941c6899dfe63f21db86fb2046b5f Mon Sep 17 00:00:00 2001
From: T100738 <1821349743@qq.com>
Date: 星期日, 12 五月 2024 21:30:41 +0800
Subject: [PATCH] commit
---
product-server-data-center/src/main/java/com/product/data/center/service/CommonService.java | 105 +++++++++++++++++++++++++++++++++++++++++++---------
1 files changed, 86 insertions(+), 19 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 79bb529..97a6007 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
@@ -21,12 +21,14 @@
import com.product.datasource.entity.DataBaseEntity;
import com.product.quartz.service.impl.SysJobService;
import com.product.util.BaseUtil;
+import io.swagger.models.auth.In;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.text.DecimalFormat;
import java.time.LocalDate;
+import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Set;
@@ -57,6 +59,7 @@
@Value("${data.system.name}")
private String dataSystemName;
+ @Transactional
public void deleteCenterLog() {
try {
//鍦╩ysql鐨刬nformation_schema 琛ㄤ腑鏌ヨ鎸囧畾琛ㄦ槸鍚﹀瓨鍦�
@@ -115,43 +118,108 @@
String createTableSql = "create table " + tableNameStr + " like product_sys_data_center_log";
getBaseDao().executeUpdate(createTableSql, new Object[]{});
}
+ //鏌ヨ鍚勭被鍨嬮厤缃渶鍚庝竴娆℃垚鍔熺殑鏁版嵁
+ String sqlFilter = "SELECT * FROM (SELECT uuid\n" +
+ "FROM product_sys_data_center_log\n" +
+ "WHERE uuid IN (\n" +
+ "\tSELECT uuid\n" +
+ "\tFROM product_sys_data_center_log\n" +
+ "\tWHERE id IN (\n" +
+ "\t\tSELECT max(a.id)\n" +
+ "\t\tFROM product_sys_data_center_log a\n" +
+ "\t\t\tJOIN product_sys_data_center_log b ON a.uuid = b.pre_step_uuid\n" +
+ "\t\tWHERE a.type = 1\n" +
+ "\t\t\tAND b.type = 2\n" +
+ "\t\t\tAND (a.result = 1\n" +
+ "\t\t\t\tOR (a.deal_flag = 1\n" +
+ "\t\t\t\t\tAND a.deal_result = 1))\n" +
+ "\t\t\tAND (b.result = 1\n" +
+ "\t\t\t\tOR (b.deal_flag = 1\n" +
+ "\t\t\t\t\tAND b.deal_result = 1))\n" +
+ "\t\tGROUP BY a.config_uuid\n" +
+ "\t)\n" +
+ "\tUNION ALL\n" +
+ "\tSELECT aa.uuid\n" +
+ "\tFROM product_sys_data_center_log aa\n" +
+ "\t\tJOIN product_sys_data_center_log bb ON aa.pre_step_uuid = bb.uuid\n" +
+ "\tWHERE aa.type = 2\n" +
+ "\t\tAND bb.type = 1\n" +
+ "\t\tAND bb.id IN (\n" +
+ "\t\t\tSELECT max(a.id)\n" +
+ "\t\t\tFROM product_sys_data_center_log a\n" +
+ "\t\t\t\tJOIN product_sys_data_center_log b ON a.uuid = b.pre_step_uuid\n" +
+ "\t\t\tWHERE a.type = 1\n" +
+ "\t\t\t\tAND b.type = 2\n" +
+ "\t\t\t\tAND (a.result = 1\n" +
+ "\t\t\t\t\tOR (a.deal_flag = 1\n" +
+ "\t\t\t\t\t\tAND a.deal_result = 1))\n" +
+ "\t\t\t\tAND (b.result = 1\n" +
+ "\t\t\t\t\tOR (b.deal_flag = 1\n" +
+ "\t\t\t\t\t\tAND b.deal_result = 1))\n" +
+ "\t\t\tGROUP BY a.config_uuid\n" +
+ "\t\t)\n" +
+ "\tUNION ALL\n" +
+ "\tSELECT uuid\n" +
+ "\tFROM product_sys_data_center_log a\n" +
+ "\tWHERE a.type = 5\n" +
+ "\t\tAND detail != 3\n" +
+ "\t\tAND (a.result = 1\n" +
+ "\t\t\tOR (a.deal_flag = 1\n" +
+ "\t\t\t\tAND a.deal_result = 1))\n" +
+ ")) ddd";
//鎻掑叆鏁版嵁鏍规嵁鍒涘缓鏃堕棿鐨勫勾鏈堝垎鍒彃鍏ュ埌瀵瑰簲鐨勮〃涓�
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 ) )";
+ " and id >(select ifnull(max(id),0) from " + tableName + "_" + DateUtil.format(dateTime, "yyyyMM") + " ) and (result = 1 or ( deal_flag = 1 AND deal_result = 1 ) ) and created_utc_datetime <now() + interval -7 day";
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(" select a.id FROM product_sys_data_center_log a INNER JOIN ");
+ deleteSql.append(" product_sys_data_center_log b on a.uuid=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 ");
+ deleteSql.append(" and (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+ deleteSql.append(" and a.type=1 ");
+ deleteSql.append(" and a.uuid not in (").append(sqlFilter).append(") ");
//鍏堝垹闄ら噰闆嗘棩蹇楁暟鎹�
- getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
-
+// getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
+ DataTableEntity dt = getBaseDao().listTable(deleteSql.toString(), new Object[]{});
+ if (!DataTableEntity.isEmpty(dt)) {
+ List<Integer> ids = dt.getData().stream().map(row -> row.getInteger("id")).collect(Collectors.toList());
+ getBaseDao().delete("product_sys_data_center_log", BaseUtil.buildQuestionMarkFilter("id", ids.size(), true), ids.toArray());
+ }
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(" select a.id FROM product_sys_data_center_log a LEFT JOIN ");
+ deleteSql.append(" product_sys_data_center_log b on b.uuid=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 ");
+ deleteSql.append(" and (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+ deleteSql.append(" and a.type=2 and b.id is null ");
+ deleteSql.append(" and a.uuid not in (").append(sqlFilter).append(") ");
- getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
-
+// getBaseDao().executeUpdate(deleteSql.toString(), new Object[]{});
+ dt = getBaseDao().listTable(deleteSql.toString(), new Object[]{});
+ if (!DataTableEntity.isEmpty(dt)) {
+ List<Integer> ids = dt.getData().stream().map(row -> row.getInteger("id")).collect(Collectors.toList());
+ getBaseDao().delete("product_sys_data_center_log", BaseUtil.buildQuestionMarkFilter("id", ids.size(), true), ids.toArray());
+ }
+ deleteSql.setLength(0);
//鍐嶅垹闄ゅ叾浠栨棩蹇楁暟鎹�
- deleteSql.append(" DELETE FROM product_sys_data_center_log a ");
+ deleteSql.append(" select a.id 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());
+ deleteSql.append(" and (a.result = 1 or ( a.deal_flag = 1 AND a.deal_result = 1 ) ) ");
+ deleteSql.append(" and a.type>2 ");
+ deleteSql.append(" and a.uuid not in (").append(sqlFilter).append(") ");
+ dt = getBaseDao().listTable(deleteSql.toString(), new Object[]{});
+ if (!DataTableEntity.isEmpty(dt)) {
+ List<Integer> ids = dt.getData().stream().map(row -> row.getInteger("id")).collect(Collectors.toList());
+ getBaseDao().delete("product_sys_data_center_log", BaseUtil.buildQuestionMarkFilter("id", ids.size(), true), ids.toArray());
+ }
}
// fs = null;
@@ -167,7 +235,6 @@
throw e;
}
}
-
/**
* 鍒涘缓瀹氭椂浠诲姟
--
Gitblit v1.9.2