usky-power/fiveep-service/src/main/java/com/bizmatics/service/job/SiteDailyElectricityCostTask.java

package com.bizmatics.service.job;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;

/**
 * 日电费计算定时任务
 * @author fyc
 * @email yuchuan.fu@chinausky.com
 * @date 2025/8/22
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class SiteDailyElectricityCostTask implements ApplicationRunner {

    private final JdbcTemplate jdbcTemplate;

    /** 应用启动后立即执行一次 */
    @Override
    public void run(ApplicationArguments args) {
        log.info("立即执行一次日电费记录");
        // calculateDailyElectricityCost();
    }

    // 每天凌晨0点过5分执行，计算前一天的用电费用
    @Scheduled(cron = "0 5 0 * * ?")
    public void calculateDailyElectricityCost() {
        LocalDate yesterday = LocalDate.now().minusDays(1);
        // LocalDate yesterday = LocalDate.now();
        // LocalDate yesterday = LocalDate.parse("2025-07-19");
        log.info("开始计算{}的用电费用", yesterday);

        try {
            calculateElectricityCostForDate(yesterday);
            log.info("{}的用电费用计算完成", yesterday);
        } catch (Exception e) {
            log.error("计算{}用电费用时发生错误", yesterday, e);
        }
    }

    public void calculateElectricityCostForDate(LocalDate date) {
        // 获取所有需要处理的站点和设备
        Map<Integer, List<String>> siteDevicesMap = getSiteDevicesMap();

        log.info("找到{}个站点需要处理", siteDevicesMap.size());

        for (Map.Entry<Integer, List<String>> entry : siteDevicesMap.entrySet()) {
            Integer siteId = entry.getKey();
            List<String> deviceCodes = entry.getValue();

            log.info("处理站点{}，包含{}个设备", siteId, deviceCodes.size());

            for (String deviceCode : deviceCodes) {
                try {
                    calculateSiteDeviceCost(siteId, deviceCode, date);
                    log.debug("站点{}设备{}在{}的电费计算完成", siteId, deviceCode, date);
                } catch (Exception e) {
                    log.error("计算站点{}设备{}在{}的电费时出错", siteId, deviceCode, date, e);
                }
            }
        }
    }

    private Map<Integer, List<String>> getSiteDevicesMap() {
        Map<Integer, List<String>> siteDevicesMap = new HashMap<>();

        String sql = "SELECT site_id, device_code FROM device WHERE device_type = '1' AND enable = 1";

        jdbcTemplate.query(sql, rs -> {
            Integer siteId = rs.getInt("site_id");
            String deviceCode = rs.getString("device_code");

            if (siteId != 0 && deviceCode != null) {
                siteDevicesMap.computeIfAbsent(siteId, k -> new ArrayList<>()).add(deviceCode);
            }
        });

        return siteDevicesMap;
    }

    private void calculateSiteDeviceCost(Integer siteId, String deviceCode, LocalDate date) {
        // 获取该站点的时段电价配置
        List<TimePriceConfig> timePriceConfigs = getTimePriceConfigs(siteId, date.getMonthValue());

        if (timePriceConfigs.isEmpty()) {
            log.warn("站点{}在{}年{}月没有电价配置", siteId, date.getYear(), date.getMonthValue());
            return;
        }

        // 获取该设备在指定日期的电力数据
        List<ElectricityData> electricityDataList = getElectricityData(deviceCode, date);

        if (electricityDataList.size() < 2) {
            log.warn("设备{}在{}的电力数据不足，只有{}条记录", deviceCode, date, electricityDataList.size());
            return;
        }

        // 按时间排序电力数据
        electricityDataList.sort(Comparator.comparing(ElectricityData::getDataTime));

        // 初始化费用统计
        BigDecimal sharpPeakCost = BigDecimal.ZERO;
        BigDecimal peakCost = BigDecimal.ZERO;
        BigDecimal flatCost = BigDecimal.ZERO;
        BigDecimal valleyCost = BigDecimal.ZERO;
        BigDecimal totalCost = BigDecimal.ZERO;

        // 计算每个时间段的电费
        for (int i = 1; i < electricityDataList.size(); i++) {
            ElectricityData prev = electricityDataList.get(i - 1);
            ElectricityData curr = electricityDataList.get(i);

            // 计算用电量（当前值减去前一个值）
            double usage = curr.getEpp() - prev.getEpp();

            if (usage <= 0) {
                log.debug("设备{}在{}到{}时间段用电量为负或零，跳过", deviceCode,
                        prev.getDataTime(), curr.getDataTime());
                continue;
            }

            BigDecimal electricityUsage = BigDecimal.valueOf(usage);

            // 确定数据点所在的时间段（取中间时间点）
            LocalDateTime startTime = prev.getDataTime();
            LocalDateTime endTime = curr.getDataTime();
            LocalTime midTime = startTime.plusSeconds(
                    (endTime.toLocalTime().toSecondOfDay() - startTime.toLocalTime().toSecondOfDay()) / 2
            ).toLocalTime();

            TimePriceConfig config = findTimePriceConfig(timePriceConfigs, midTime);

            if (config == null) {
                log.debug("设备{}在时间{}没有找到对应的电价配置", deviceCode, midTime);
                continue;
            }

            // 计算电费：用电量 × 电价
            BigDecimal cost = electricityUsage.multiply(config.getElectricityPrice());
            totalCost = totalCost.add(cost).setScale(2, RoundingMode.HALF_UP);

            switch (config.getPeakValleyAttribute()) {
                case 1: // 尖
                    sharpPeakCost = sharpPeakCost.add(cost).setScale(2, RoundingMode.HALF_UP);
                    break;
                case 2: // 峰
                    peakCost = peakCost.add(cost).setScale(2, RoundingMode.HALF_UP);
                    break;
                case 3: // 平
                    flatCost = flatCost.add(cost).setScale(2, RoundingMode.HALF_UP);
                    break;
                case 4: // 谷
                    valleyCost = valleyCost.add(cost).setScale(2, RoundingMode.HALF_UP);
                    break;
                default:
                    log.warn("未知的峰谷属性: {}", config.getPeakValleyAttribute());
            }
        }

        // 保存计算结果
        saveElectricityRecord(siteId, deviceCode, date, sharpPeakCost, peakCost,
                flatCost, valleyCost, totalCost);

        log.info("站点{}设备{}在{}的电费计算完成：尖峰{}元，峰{}元，平{}元，谷{}元，总计{}元",
                siteId, deviceCode, date, sharpPeakCost, peakCost, flatCost, valleyCost, totalCost);
    }

    private List<TimePriceConfig> getTimePriceConfigs(Integer siteId, int month) {
        String sql = "SELECT price_type, electricity_price, start_time, end_time, " +
                "peak_valley_attribute FROM electricity_time_price " +
                "WHERE site_id = ? AND month = ? ORDER BY start_time";

        return jdbcTemplate.query(sql, new Object[]{siteId, month}, new RowMapper<TimePriceConfig>() {
            @Override
            public TimePriceConfig mapRow(ResultSet rs, int rowNum) throws SQLException {
                TimePriceConfig config = new TimePriceConfig();
                config.setPriceType(rs.getInt("price_type"));
                config.setElectricityPrice(rs.getBigDecimal("electricity_price"));

                // 处理时间字段
                java.sql.Time startTime = rs.getTime("start_time");
                java.sql.Time endTime = rs.getTime("end_time");
                if (startTime != null) config.setStartTime(startTime.toLocalTime());
                if (endTime != null) config.setEndTime(endTime.toLocalTime());

                config.setPeakValleyAttribute(rs.getInt("peak_valley_attribute"));
                return config;
            }
        });
    }

    private List<ElectricityData> getElectricityData(String deviceCode, LocalDate date) {
        String sql = "SELECT Epp, dataTime FROM ht_analog_data " +
                "WHERE deviceName = ? AND freezingTime = ? " +
                "ORDER BY dataTime";

        return jdbcTemplate.query(sql, new Object[]{deviceCode, date}, new RowMapper<ElectricityData>() {
            @Override
            public ElectricityData mapRow(ResultSet rs, int rowNum) throws SQLException {
                ElectricityData data = new ElectricityData();
                data.setEpp(rs.getDouble("Epp"));

                java.sql.Timestamp dataTime = rs.getTimestamp("dataTime");
                if (dataTime != null) {
                    data.setDataTime(dataTime.toLocalDateTime());
                }

                return data;
            }
        });
    }

    private TimePriceConfig findTimePriceConfig(List<TimePriceConfig> configs, LocalTime time) {
        for (TimePriceConfig config : configs) {
            LocalTime startTime = config.getStartTime();
            LocalTime endTime = config.getEndTime();

            if (startTime != null && endTime != null) {
                // 处理跨天的时间段（如23:00-01:00）
                if (startTime.isAfter(endTime)) {
                    if (!time.isBefore(startTime) || time.isBefore(endTime)) {
                        return config;
                    }
                } else {
                    if (!time.isBefore(startTime) && time.isBefore(endTime)) {
                        return config;
                    }
                }
            }
        }
        return null;
    }

    private void saveElectricityRecord(Integer siteId, String deviceCode, LocalDate date,
                                       BigDecimal sharpPeakCost, BigDecimal peakCost,
                                       BigDecimal flatCost, BigDecimal valleyCost,
                                       BigDecimal totalCost) {
        String sql = "INSERT INTO site_electricity_record " +
                "(site_id, device_code, date, peak_cost, flat_cost, " +
                "valley_cost, sharp_peak_cost, total_cost) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?) " +
                "ON DUPLICATE KEY UPDATE " +
                "peak_cost = VALUES(peak_cost), " +
                "flat_cost = VALUES(flat_cost), " +
                "valley_cost = VALUES(valley_cost), " +
                "sharp_peak_cost = VALUES(sharp_peak_cost), " +
                "total_cost = VALUES(total_cost)";

        try {
            jdbcTemplate.update(sql,
                    siteId,
                    deviceCode,
                    date,
                    peakCost,
                    flatCost,
                    valleyCost,
                    sharpPeakCost,
                    totalCost
            );
            log.debug("成功保存站点{}设备{}的电费记录", siteId, deviceCode);
        } catch (Exception e) {
            log.error("保存站点{}设备{}的电费记录失败", siteId, deviceCode, e);
            // 这里只是记录错误，不会影响其他设备的处理
        }
    }

    // 内部配置类
    @lombok.Data
    private static class TimePriceConfig {
        private int priceType;
        private BigDecimal electricityPrice;
        private LocalTime startTime;
        private LocalTime endTime;
        private int peakValleyAttribute;
    }

    // 电力数据类
    @lombok.Data
    private static class ElectricityData {
        private double epp;
        private LocalDateTime dataTime;
    }

}