commit

许鹏程

2024-01-15 36dc14af5a78be3b3eb941ddc13a22d3aaf1fe3a

commit

 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,8 +58,113 @@
    private String dataSystemName;

    public void deleteCenterLog() {
        for (int i = 0; i < 10; i++) {
            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 ");
        try {
            //在mysql的information_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;
        }
    }


 product-server-data-center/src/main/java/com/product/data/center/service/DataArchivingService.java

@@ -2,6 +2,7 @@

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.RandomUtil;
import com.alibaba.fastjson.JSONObject;
@@ -158,10 +159,16 @@
                        dao.closeConnection();
                    }
                    dao = new DataBaseEntity(deleteDataSourceArray[j]).getDao();
                    Connection connection = dao.getConnection();
                    connection.setAutoCommit(false);
                    do {
                        list = dao.getList(deleteDataTable, configFse.getString("delete_select_filter"), new Object[]{}, 1, pageSize);
//                        String sql = "SELECT  * FROM (SELECT * from " + deleteDataTable + " where " + configFse.getString("delete_select_filter") + " order by " + deleteUniqueField + " ) A  ";
//                        if (!Objects.isNull(minID) && !Objects.isNull(maxID)) {
//                            sql += " where " + deleteUniqueField + " > " + maxID;
//                        }
                        String filter = configFse.getString("delete_select_filter");
                        if (!Objects.isNull(minID) && !Objects.isNull(maxID)) {
                            filter += "  and ( " + deleteUniqueField + " > " + maxID + ")";
                        }
                        list = dao.getList(deleteDataTable, filter, new Object[]{}, deleteUniqueField, 1, pageSize);
                        WriteUtil.append("DA-删除子库数据-表名：" + deleteDataTable);
                        if (DataTableEntity.isEmpty(list)) {
                            break;
@@ -177,7 +184,6 @@
                        Map<String, Map> collectMap = validationData.getData().stream().collect(Collectors.toMap(
                                (item) -> item.getString(validationUniqueField),
                                item -> item.getValues()));
                        validationData = null;
                        List<String> deleteUniqueValueList = new ArrayList<>();
                        for (int i = 0; i < list.getRows(); i++) {
                            FieldSetEntity fs = list.getFieldSetEntity(i);
@@ -207,9 +213,9 @@
                            deleteSuccessCount += dao.deleteRInt(configFse.getString("delete_data_table"),
                                    BaseUtil.buildQuestionMarkFilter(deleteUniqueField, deleteUniqueValueList.size(), true), deleteUniqueValueList.toArray());
                            WriteUtil.append("DA-删除子库数据-已经删除条数：" + deleteSuccessCount);

                        }
                    } while (currentCount == pageSize);
                    connection.commit();
                }
                journalEntity.setResult(1);
                journalEntity.setMin_id(minID);
@@ -275,11 +281,19 @@
            String sourceTable = configFse.getString("source_table");
            boolean canExecuteClearFlag = canExecuteClear(sourceTable);
            String deleteSubLogUUID = null;

            if (canExecuteClearFlag) {
                WriteUtil.append("DA-删除扫码库》》》");
                //删除扫码库已提取到mes主库且根据配置条件过滤的数据 KT特有
                deleteSubLogUUID = this.sweepCodeLibrary(configFse);
                WriteUtil.append("DA-删除扫码库耗时：" + tempTestTimer.intervalMs());
//                deleteSubLogUUID = this.sweepCodeLibrary(configFse);
                //更改为异步执行
                ThreadUtil.execAsync(() -> {
                    try {
                        sweepCodeLibrary(configFse);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                });
            }

            //来源数据源
@@ -576,6 +590,7 @@

    /**
     * 清理归档重复数据
     *
     * @param sourceTable
     * @param targetTableSet
     * @param uniqueField
@@ -600,6 +615,7 @@

    /**
     * 清理判定，每天切换的时候可以执行一次
     *
     * @param tableName
     * @return
     */

 product-server-data-center/src/main/java/com/product/data/center/utils/WriteUtil.java

@@ -59,6 +59,6 @@
    }

    public static void append(String content) {
        append(content, false);
        append(content, true);
    }
}

 product-server-web/resources/LicenseKey.dat

@@ -1 +1 @@
79BE8AD4DA7D580701BCE59258381B293036C97EEEF79611A2D07C5F993D24A77CE11123E4A560EF07BD52D1ACE713B77268CC395013CDE6486A8C616E89A3BC800E47F9D453A786ADD137A398C0A180CED736CC5EF71381ED4D327CBF699C0EC14DCA6CAD3376254BC333D7E4CFCDE3AFE4DB2DF31CDB278A637A99465EF0837D1DCC7613A3C6CD331901B0E9B49EC665AACC84ACD365E8E33662E677266571C04D84C86060707286097BEC83C5B6BC8F0BA4B8ECDFEDC594ECD09947F980AE6C7B75A8A33ED3C8D56771437ECCA06432DA8A329C222D69FD44A750BEAB11789D8148026CA9EE49E4CD9239A894B561E560CDA0ACBA169C3F7F2AA3171F05158C98CCDD267C7DFCF6878D4C43DAF051BC452C7ABB8AEB930A709CD87C744A4CDB46C763426AC13ABF41BA7F885DE0F69C2F231234D4202DEBB0D2BA1B1D197DB005A00553EEE41C1E481B09EBEE5A44FB8B6FB9FEE04B5EB2619FED2CEC2BFB3013E3F36A0F9D0DB18200851A1C700DAB79A58FD4C468A39D2624ECF69CD414
7B6D2332DBB13421010AC91951B73AEE393AA4F5E45D8A81A6572ED29A0FB4447DF3FE2BEC7CD96B066B1AE8A658B97478DCAB2B59F0971241263C69679C721A8AF5514BD65B339FAA7B3034926730E3C0A10BE750BEFD02EF5E6D69BC6C0FB8C1C348F8AC9A60FC4AB8FB2FEA42C50CAB06A195F98259C08B6C31D545887F18799794981CB48507331CED6BEB79F5906E4004B0AC2F98E0EFD14C077055804FC7CEC36666395AD381078F5E8FB364798D6C34BDEA2C8A6093268E09421A203464C23223A30D8931DBC483E47344CF423031D5AF976CDB0FFA21F5B6B628B6849AF9155D68993343EC171BCAADEDBAABE9D583A4A011414F37B7AA8E1D4A6430845B63042C6A1344FBBEB1A94FB4011BB62C489CBCD7701D080AB8E175C45B74DB215C8C4DB5DC38B302550F805E6D3F9C86C247339259CBE2CF1AB21F676ED7BCFE01765BFC566E1A297633E94FF59CF3EAC9D0F54C6CB7BC55018A276886773A9FEB7C620BF276B43B068619ACB745A41F663CDCAF8EFF977E29E4FBCA0F18

 product-server-web/src/main/resources/application-dev.properties

@@ -85,7 +85,7 @@
# \u6B63\u5F0F\u670D\u52A1\u5668-\u91C7\u96C6
#data.source.url=jdbc:mysql://10.13.1.39:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
# \u6B63\u5F0F\u670D\u52A1\u5668-\u62A5\u8868
data.source.url=jdbc:mysql://10.13.1.39:3307/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.url=jdbc:mysql://10.13.1.39:3306/ch_kt_mes_apply?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useNewIO=true&useSSL=false&serverTimezone=Asia/Shanghai
data.source.user=root
#data.source.password=root123
#data.source.password=a123