commit

cheng

2024-01-16 7279b6314b350cc0c865d82dbaa4d0624e4f7b3f

commit

 product-server-data-center/src/main/java/com/product/data/center/controller/CommonController.java

@@ -35,6 +35,8 @@
    @Resource
    SyncDelRecordService syncDelRecordService;



    @PostMapping("/mes-sync/save/{version}")
    @ApiVersion(1)
    public String saveMesSyncDataConfig(HttpServletRequest request) {

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

@@ -115,42 +115,95 @@
                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(" DELETE a 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[]{});

                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(" DELETE a 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[]{});

                deleteSql.setLength(0);
                //再删除其他日志数据
                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 ");
                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(") ");
                getBaseDao().executeUpdate(deleteSql.toString());
            }

@@ -167,7 +220,6 @@
            throw e;
        }
    }


    /**
     * 创建定时任务