thridParty/GetDataByRESTful/src/main/java/com/jyxt/getdatabyrestful/service/impl/IBPSServiceImpl.java

package com.jyxt.getdatabyrestful.service.impl;

import com.jyxt.getdatabyrestful.service.IBPSService;
import com.jyxt.getdatabyrestful.service.LISService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class IBPSServiceImpl implements IBPSService {

    private static final Logger log = LoggerFactory.getLogger(IBPSServiceImpl.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    LISService lisService;

    @Value("${config.qryDays}")
    private String qryDays;


    public int UpdateActualCount() {
        log.info("ready to update records");
        int retVal = 1;
        // 获取LIS实际保存数量
        List<Map<String, Object>> actualList = lisService.geteActualCountList(qryDays);
        // 查询系统中待弃置列表
        List<Map<String, Object>> saveedList = geteSavedCountList();

        // 创建用于更新的数据列表
        List<Map<String, Object>> updateList = new ArrayList<>();

        // 构建actualList的查找映射：日期+专业组 -> COUNT
        Map<String, Integer> actualMap = new HashMap<>();
        for (Map<String, Object> actualItem : actualList) {
            String saveDate = (String) actualItem.get("SAVE_DATE");
            String groupName = (String) actualItem.get("GROUPNAME");
            String countStr = actualItem.get("COUNT").toString();

            String key = saveDate + "_" + groupName;
            try {
                int count = Integer.parseInt(countStr);
                actualMap.put(key, count);
            } catch (NumberFormatException e) {
                log.warn("LIS数据中COUNT字段格式错误: {}, 组别: {}", countStr, key);
            }
        }

        // 遍历saveedList，匹配并计算百分比
        for (Map<String, Object> savedItem : saveedList) {
            String id = savedItem.get("id_").toString();
            String saveDate = (String) savedItem.get("bao_cun_shi_jian_");
            String groupName = (String) savedItem.get("qi_ta_");
            String hejiStr = (String) savedItem.get("he_ji_");

            String key = saveDate + "_" + groupName;

            // 检查actualMap中是否存在对应的记录
            if (actualMap.containsKey(key)) {
                try {
                    int heji = Integer.parseInt(hejiStr);
                    int count = actualMap.get(key);

                    // 计算百分比 (he_ji_/COUNT * 100)
                    double percentage = 0.0;
                    if (count > 0) {
                        percentage = (double) heji / count * 100;
                    }

                    // 保留1位小数
                    double roundedPercentage = Math.round(percentage * 10.0) / 10.0;
                    if (roundedPercentage > 100.0) { //超过100%按100%显示
                        roundedPercentage = 100.0;
                    }

                    // 构建更新数据
                    Map<String, Object> updateData = new HashMap<>();
                    updateData.put("id_", id);
                    updateData.put("ying_bao_cun_shu_", String.valueOf(count));
                    updateData.put("bao_cun_bai_fen_b", String.valueOf(roundedPercentage));

                    updateList.add(updateData);

                } catch (NumberFormatException e) {
                    retVal = 0;
                    log.warn("数据格式转换错误 - ID: {}, he_ji_: {}, 组别: {}", id, hejiStr, key);
                }
            }
        }

        // 执行批量更新
        int updatedCount = batchUpdateRecords(updateList);
        log.info("update {} records success!", updatedCount);

        return retVal;
    }

    /**
     * 批量更新记录
     */
    private int batchUpdateRecords(List<Map<String, Object>> updateList) {
        if (updateList == null || updateList.isEmpty()) {
            return 0;
        }
        log.info("start to update records...");
        String updateSql = "UPDATE t_jyhypjlb SET ying_bao_cun_shu_ = ?, bao_cun_bai_fen_b = ? WHERE id_ = ?";

        int[] updateCounts = jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> data = updateList.get(i);
                ps.setString(1, (String) data.get("ying_bao_cun_shu_"));
                ps.setString(2, (String) data.get("bao_cun_bai_fen_b"));
                ps.setString(3, (String) data.get("id_"));
            }
            @Override
            public int getBatchSize() {
                return updateList.size();
            }
        });
        // 计算实际更新的记录数
        int totalUpdated = 0;
        for (int count : updateCounts) {
            if (count > 0) {
                totalUpdated++;
            }
        }
        return totalUpdated;
    }

    public List<Map<String, Object>> geteSavedCountList() {
        String qryUndestoryList = "SELECT id_, " +
                "CASE " +
                "  WHEN bao_cun_shi_jian_ LIKE '____-__-__ %' THEN DATE_FORMAT(STR_TO_DATE(bao_cun_shi_jian_, '%Y-%m-%d %H:%i:%s'), '%Y%m%d') " +
                "  WHEN bao_cun_shi_jian_ LIKE '____-__-__' THEN DATE_FORMAT(STR_TO_DATE(bao_cun_shi_jian_, '%Y-%m-%d'), '%Y%m%d') " +
                "  ELSE bao_cun_shi_jian_ " +
                "END AS bao_cun_shi_jian_, " +
                "qi_ta_, he_ji_ " +
                "FROM t_jyhypjlb WHERE shi_fou_guo_shen_='待弃置'";
        log.info("execute sql: " + qryUndestoryList.toString());
        return jdbcTemplate.queryForList(qryUndestoryList);
    }
}