package com.xzzn.ems.service.impl;

import com.xzzn.common.enums.CostType;
import com.xzzn.common.utils.DateUtils;
import com.xzzn.ems.domain.EmsAmmeterData;
import com.xzzn.ems.domain.vo.AmmeterRevenueStatisListVo;
import com.xzzn.ems.domain.vo.AmmeterStatisListVo;
import com.xzzn.ems.domain.vo.BatteryDataStatsListVo;
import com.xzzn.ems.domain.vo.ClusterStatisListVo;
import com.xzzn.ems.domain.vo.DateSearchRequest;
import com.xzzn.ems.domain.vo.ElectricDataInfoVo;
import com.xzzn.ems.domain.vo.EnergyPriceConfigVo;
import com.xzzn.ems.domain.vo.PCSCurveResponse;
import com.xzzn.ems.domain.vo.PcsStatisListVo;
import com.xzzn.ems.domain.vo.PowerStatisListVo;
import com.xzzn.ems.domain.vo.SiteMonitorDataVo;
import com.xzzn.ems.domain.vo.StackCurveResponse;
import com.xzzn.ems.domain.vo.StackStatisListVo;
import com.xzzn.ems.domain.vo.StatisAmmeterDateRequest;
import com.xzzn.ems.domain.vo.StatisClusterDateRequest;
import com.xzzn.ems.mapper.EmsAmmeterDataMapper;
import com.xzzn.ems.mapper.EmsBatteryClusterMapper;
import com.xzzn.ems.mapper.EmsBatteryDataDayMapper;
import com.xzzn.ems.mapper.EmsBatteryStackMapper;
import com.xzzn.ems.mapper.EmsDailyChargeDataMapper;
import com.xzzn.ems.mapper.EmsDailyEnergyDataMapper;
import com.xzzn.ems.mapper.EmsDevicesSettingMapper;
import com.xzzn.ems.mapper.EmsEnergyPriceConfigMapper;
import com.xzzn.ems.mapper.EmsPcsDataMapper;
import com.xzzn.ems.service.IEmsStatsReportService;
import com.xzzn.ems.service.ISysBizRemarkService;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

/**
 * 统计报表数据Service业务层处理
 *
 * @author xzzn
 * @date 2025-06-29
 */
@Service
public class EmsStatsReportServiceImpl implements IEmsStatsReportService
{
    private static final Logger log = LoggerFactory.getLogger(EmsStatsReportServiceImpl.class);

    @Autowired
    private EmsPcsDataMapper emsPcsDataMapper;
    @Autowired
    private EmsBatteryStackMapper emsBatteryStackMapper;
    @Autowired
    private EmsBatteryClusterMapper emsBatteryClusterMapper;
    @Autowired
    private EmsAmmeterDataMapper emsAmmeterDataMapper;
    @Autowired
    private EmsDevicesSettingMapper emsDevicesSettingMapper;
    @Autowired
    private EmsBatteryDataDayMapper emsBatteryDataDayMapper;
    @Autowired
    private EmsDailyChargeDataMapper emsDailyChargeDataMapper;
    @Autowired
    private EmsDailyEnergyDataMapper emsDailyEnergyDataMapper;
    @Autowired
    private EmsEnergyPriceConfigMapper emsEnergyPriceConfigMapper;
    @Autowired
    private ISysBizRemarkService sysBizRemarkService;

    private static final String STATS_REPORT_BIZ_TYPE = "stats_report";
    private static final String AMMETER_REPORT_KEY = "DBBB";
    private static final String REVENUE_REPORT_KEY = "SYBB";

    private String buildRemarkBizKey(String siteId, String dataTime) {
        String safeSiteId = siteId == null ? "" : siteId.trim();
        String safeDate = dataTime == null ? "" : dataTime.trim();
        return safeSiteId + "_" + safeDate;
    }

    private void fillAmmeterRemarks(String siteId, String reportKey, List<AmmeterStatisListVo> rows) {
        if (CollectionUtils.isEmpty(rows)) {
            return;
        }
        List<String> bizKeys = rows.stream()
                .map(row -> buildRemarkBizKey(siteId, row.getDataTime()))
                .collect(Collectors.toList());
        Map<String, String> remarkMap = sysBizRemarkService.getRemarkMap(STATS_REPORT_BIZ_TYPE, reportKey, bizKeys);
        rows.forEach(row -> row.setRemark(remarkMap.getOrDefault(buildRemarkBizKey(siteId, row.getDataTime()), "")));
    }

    private void fillRevenueRemarks(String siteId, String reportKey, List<AmmeterRevenueStatisListVo> rows) {
        if (CollectionUtils.isEmpty(rows)) {
            return;
        }
        List<String> bizKeys = rows.stream()
                .map(row -> buildRemarkBizKey(siteId, row.getDataTime()))
                .collect(Collectors.toList());
        Map<String, String> remarkMap = sysBizRemarkService.getRemarkMap(STATS_REPORT_BIZ_TYPE, reportKey, bizKeys);
        rows.forEach(row -> row.setRemark(remarkMap.getOrDefault(buildRemarkBizKey(siteId, row.getDataTime()), "")));
    }

    // 电量指标
    @Override
    public ElectricDataInfoVo getElectricDataResult(DateSearchRequest requestVo) {
        ElectricDataInfoVo electricDataInfoVo = new ElectricDataInfoVo();

        // 默认时间处理
        dealRequestTime(requestVo);
        Date startDate = requestVo.getStartDate();
        Date endDate = requestVo.getEndDate();

        // 总充总放
        String nowData = DateUtils.getDate();
        BigDecimal totalDischarge = BigDecimal.ZERO;
        BigDecimal totalCharge = BigDecimal.ZERO;
        Map<String, BigDecimal> totalMap = emsDailyChargeDataMapper.getAllSiteChargeData(nowData,requestVo.getSiteId());
        if (!CollectionUtils.isEmpty(totalMap)) {
            totalDischarge = totalMap.get("totalDischargedCap");
            totalCharge = totalMap.get("totalChargedCap");
        }
        BigDecimal efficiency = new BigDecimal(0);
        if ( totalCharge.compareTo(BigDecimal.ZERO)>0){
            efficiency = totalDischarge.divide(totalCharge, 2, RoundingMode.HALF_UP)
                    .multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP);
        }
        electricDataInfoVo.setTotalDisChargedCap(totalDischarge);
        electricDataInfoVo.setTotalChargedCap(totalCharge);
        electricDataInfoVo.setEfficiency(efficiency);

        String siteId = requestVo.getSiteId();
        String deviceId = requestVo.getDeviceId();
        // 日期筛选
        List<SiteMonitorDataVo> dataList = new ArrayList();
        //  开始日期和结束日期同一天，展示 0-24 小时数据
        if (DateUtils.isSameDay(startDate, endDate)){
            electricDataInfoVo.setUnit("时");
//            if ("021_DDS_01".equals(siteId)) {
                deviceId = "METE";
                dataList = emsAmmeterDataMapper.getChargeDataByHour(siteId,deviceId,startDate);
//            } else if ("021_FXX_01".equals(siteId)) {
//                endDate = DateUtils.addDays(endDate, 1);
//                dataList = emsPcsDataMapper.getPcsDataByHour(requestVo.getSiteId(), startDate, endDate);
//            }
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 1
                && DateUtils.differentDaysByMillisecond(endDate, startDate) < 30){
            electricDataInfoVo.setUnit("日");
            //  开始日期-结束日期大于 1 天，小于30 天，按天展示数据
            dataList = emsDailyChargeDataMapper.getTotalChargeDataByDay(siteId,startDate,endDate);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 30){
            electricDataInfoVo.setUnit("月");
            //  开始日期-结束日期大于 1 个月，按月展示数据
            dataList = emsDailyChargeDataMapper.getTotalChargeDataByMonth(siteId,startDate, endDate);
        }
        // 获取昨天昨晚一条数据，计算第一个小时的差值
        //setFirstDataDiff(dataList,siteId,deviceId);
        // 计算充放电效率
        if (!CollectionUtils.isEmpty(dataList)){

            for (SiteMonitorDataVo siteMonitorDataVo : dataList) {
                // 计算单天的效率
                BigDecimal dailyEfficiency = new BigDecimal(0);
                if ( siteMonitorDataVo.getChargedCap() != null &&
                        siteMonitorDataVo.getChargedCap().compareTo(BigDecimal.ZERO)>0){
                    dailyEfficiency = siteMonitorDataVo.getDisChargedCap().divide(siteMonitorDataVo.getChargedCap(), 2, RoundingMode.HALF_UP);
                    dailyEfficiency = dailyEfficiency.multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP);
                }
                siteMonitorDataVo.setDailyEfficiency(dailyEfficiency);
            }

            electricDataInfoVo.setSevenDayDisChargeStats(dataList);
        }
        return electricDataInfoVo;
    }

    private void setFirstDataDiff(List<SiteMonitorDataVo> dataList, String siteId, String deviceId) {
        BigDecimal yestTotalDisChargeDate = new BigDecimal(0);
        BigDecimal yestTotalChargeDate = new BigDecimal(0);

        String yestDate = DateUtils.getYesterdayDate();
        if (dataList != null && dataList.size()>0){
            SiteMonitorDataVo firstData = dataList.get(0);
            if ("021_DDS_01".equals(siteId)){
                EmsAmmeterData yestData = emsAmmeterDataMapper.getYestLatestDate(siteId,deviceId,yestDate);
                if (yestData != null) {
                    yestTotalDisChargeDate = yestData.getCurrentReverseActiveTotal();
                    yestTotalChargeDate = yestData.getCurrentForwardActiveTotal();
                }
            } else if ("021_FXX_01".equals(siteId)) {

            }
            firstData.setChargedCap(firstData.getTotalChargedCap() == null ? BigDecimal.ZERO
                    : firstData.getTotalChargedCap().subtract(yestTotalChargeDate));
            firstData.setDisChargedCap(firstData.getTotalDisChargedCap() == null ? BigDecimal.ZERO
                    : firstData.getTotalDisChargedCap().subtract(yestTotalDisChargeDate));
        }
    }

    // pcs曲线数据
    @Override
    public List<PCSCurveResponse> getPCSDataResult(DateSearchRequest requestVo) {
        List<PCSCurveResponse> responseList = new ArrayList<>();
        List<PcsStatisListVo>  dataList = new ArrayList();

        // 默认时间-7天
        dealRequestTime(requestVo);
        Date startDate = requestVo.getStartDate();
        Date endDate = requestVo.getEndDate();

//        //  开始日期和结束日期同一天，展示 0-24 小时数据
//        if (DateUtils.isSameDay(startDate, endDate)){
//            endDate = DateUtils.addDays(endDate, 1);
//            requestVo.setEndDate(endDate);
//            dataList = emsPcsDataMapper.getPcsActivePowerByHour(requestVo);
//        }
        //  开始日期和结束日期同一天，展示5分钟一组数据点
        if (DateUtils.isSameDay(startDate, endDate)){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            dataList = emsPcsDataMapper.getPcsActivePowerByMinutes(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 1
                && DateUtils.differentDaysByMillisecond(endDate, startDate) < 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 天，小于30 天，按天展示数据
            dataList = emsPcsDataMapper.getPcsActivePowerByDay(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 个月，按月展示数据
            dataList = emsPcsDataMapper.getPcsActivePowerByMonth(requestVo);
        }

        // 数据格式转换 按deviceId分组
        if (dataList != null && dataList.size()>0){
            responseList = dataList.stream()
                    .collect(Collectors.groupingBy(PcsStatisListVo::getDeviceId))
                    // 将Map转换为Set<Entry>后流转
                    .entrySet().stream()
                    // 映射为PCSCurveResponse对象
                    .map(entry -> {
                        PCSCurveResponse response = new PCSCurveResponse();
                        response.setDeviceId(entry.getKey());
                        response.setDataList(entry.getValue());
                        return response;
                    })
                    // 收集为List
                    .collect(Collectors.toList());
        }
        return responseList;
    }

    // 电池堆曲线
    @Override
    public List<StackCurveResponse> getStackDataResult(DateSearchRequest requestVo) {
        List<StackCurveResponse> responseList = new ArrayList<>();
        List<StackStatisListVo> dataList = new ArrayList();
        // 默认时间-7天
        dealRequestTime(requestVo);
        Date startDate = requestVo.getStartDate();
        Date endDate = requestVo.getEndDate();

//        //  开始日期和结束日期同一天，展示 0-24 小时数据
//        if (DateUtils.isSameDay(startDate, endDate)){
//            endDate = DateUtils.addDays(endDate, 1);
//            requestVo.setEndDate(endDate);
//            dataList = emsBatteryStackMapper.getStackDataByHour(requestVo);
//        }
        //  开始日期和结束日期同一天，按5分钟一个数据点展示数据
        if (DateUtils.isSameDay(startDate, endDate)){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            dataList = emsBatteryStackMapper.getStackDataByMinute(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 1
                && DateUtils.differentDaysByMillisecond(endDate, startDate) < 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 天，小于30 天，按天展示数据
            dataList = emsBatteryStackMapper.getStackDataByDay(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 个月，按月展示数据
            dataList = emsBatteryStackMapper.getStackDataByMonth(requestVo);
        }

        // 数据格式转换 按deviceId分组
        if (dataList != null && dataList.size()>0){
            responseList = dataList.stream()
                    .collect(Collectors.groupingBy(StackStatisListVo::getDeviceId))
                    // 将Map转换为Set<Entry>后流转
                    .entrySet().stream()
                    // 映射为PCSCurveResponse对象
                    .map(entry -> {
                        StackCurveResponse response = new StackCurveResponse();
                        response.setDeviceId(entry.getKey());
                        response.setDataList(entry.getValue());
                        return response;
                    })
                    // 收集为List
                    .collect(Collectors.toList());
        }
        return responseList;
    }

    // 电池温度
    @Override
    public List<ClusterStatisListVo> getClusterDataResult(StatisClusterDateRequest requestVo) {
        List<ClusterStatisListVo> dataList = new ArrayList();
        // 时间不传默认当天
        if (requestVo.getDateTime() == null) {
            String now = DateUtils.getDate();
            requestVo.setDateTime(DateUtils.dateTime(DateUtils.YYYY_MM_DD, now));
        }

        // 根据时间获取每小时最新数据
        dataList = emsBatteryClusterMapper.getClusterDataByHour(requestVo);
        return dataList;
    }

    @Override
    public List<Map<String, Object>> getLoadNameList(String siteId) {
        return emsDevicesSettingMapper.getAmmeterNameList(siteId);
    }

    // 电表报表
    @Override
    public List<AmmeterStatisListVo> getAmmeterDataResult(StatisAmmeterDateRequest requestVo){
        List<AmmeterStatisListVo>  dataList = emsDailyEnergyDataMapper.getDataBySiteId(requestVo.getSiteId(),requestVo.getStartTime(),requestVo.getEndTime());
        if (dataList == null || dataList.size()==0){
            return new ArrayList<>();
        }

        BigDecimal activePower = new BigDecimal(0);
        BigDecimal reactivePower = new BigDecimal(0);
        /*AmmeterStatisListVo totalVo = new AmmeterStatisListVo();
            totalVo.setDataTime("汇总");*/
        for (AmmeterStatisListVo ammeterStatisListVo : dataList) {
            BigDecimal effect = new BigDecimal(0);
            activePower = (ammeterStatisListVo.getActivePeakKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getActivePeakKwh())
                    .add((ammeterStatisListVo.getActiveHighKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getActiveHighKwh()))
                    .add((ammeterStatisListVo.getActiveFlatKwh()  == null ? BigDecimal.ZERO : ammeterStatisListVo.getActiveFlatKwh()))
                    .add((ammeterStatisListVo.getActiveValleyKwh()  == null ? BigDecimal.ZERO : ammeterStatisListVo.getActiveValleyKwh()));
            reactivePower = (ammeterStatisListVo.getReActivePeakKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getReActivePeakKwh())
                    .add((ammeterStatisListVo.getReActiveHighKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getReActiveHighKwh()))
                    .add((ammeterStatisListVo.getReActiveFlatKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getReActiveFlatKwh()))
                    .add((ammeterStatisListVo.getReActiveValleyKwh() == null ? BigDecimal.ZERO : ammeterStatisListVo.getReActiveValleyKwh()));

            ammeterStatisListVo.setActiveTotalKwh(activePower);
            ammeterStatisListVo.setReActiveTotalKwh(reactivePower);
            if ( activePower != null && reactivePower !=null
                    && activePower.compareTo(BigDecimal.ZERO) > 0
                    && reactivePower.compareTo(BigDecimal.ZERO) > 0){
                effect = reactivePower.divide(activePower, 2, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP);
            }
            // 每天的效率
            ammeterStatisListVo.setEffect(effect);

            // 处理汇总数据
            //dealWithAmmeterTotalDate(ammeterStatisListVo,totalVo);
        }

        // 处理汇总的效率
            /*BigDecimal totalActive = totalVo.getActiveTotalKwh();
            BigDecimal totalReactive = totalVo.getReActiveTotalKwh();
            if ( totalActive != null && totalReactive !=null
                    && totalActive.compareTo(BigDecimal.ZERO) > 0
                    && totalReactive.compareTo(BigDecimal.ZERO) >= 0){
                BigDecimal totalEffect = new BigDecimal(0);
                totalEffect = totalReactive.divide(totalActive, 2, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP);
                totalVo.setEffect(totalEffect);
            }
            dataList.add(totalVo);*/

        fillAmmeterRemarks(requestVo.getSiteId(), AMMETER_REPORT_KEY, dataList);
        return dataList;
    }

    @Override
    public List<AmmeterRevenueStatisListVo> getAmmeterRevenueDataResult(StatisAmmeterDateRequest requestVo) {
        log.info("收益报表查询开始, siteId: {}, startTime: {}, endTime: {}",
                requestVo.getSiteId(), requestVo.getStartTime(), requestVo.getEndTime());
        List<AmmeterRevenueStatisListVo> resultList = emsDailyEnergyDataMapper.getRevenueDataBySiteId(
                requestVo.getSiteId(), requestVo.getStartTime(), requestVo.getEndTime());
        if (CollectionUtils.isEmpty(resultList)) {
            log.warn("收益报表查询结果为空, siteId: {}, startTime: {}, endTime: {}",
                    requestVo.getSiteId(), requestVo.getStartTime(), requestVo.getEndTime());
            return Collections.emptyList();
        }
        //计算每天总收益和当日实际收益（放电总-充电总）
        resultList.forEach(ammeterRevenue -> {
            BigDecimal activePeakPrice = nz(ammeterRevenue.getActivePeakPrice());
            BigDecimal activeHighPrice = nz(ammeterRevenue.getActiveHighPrice());
            BigDecimal activeFlatPrice = nz(ammeterRevenue.getActiveFlatPrice());
            BigDecimal activeValleyPrice = nz(ammeterRevenue.getActiveValleyPrice());
            BigDecimal reActivePeakPrice = nz(ammeterRevenue.getReActivePeakPrice());
            BigDecimal reActiveHighPrice = nz(ammeterRevenue.getReActiveHighPrice());
            BigDecimal reActiveFlatPrice = nz(ammeterRevenue.getReActiveFlatPrice());
            BigDecimal reActiveValleyPrice = nz(ammeterRevenue.getReActiveValleyPrice());

            ammeterRevenue.setActiveTotalPrice(activePeakPrice.add(activeHighPrice).add(activeFlatPrice).add(activeValleyPrice));
            ammeterRevenue.setReActiveTotalPrice(reActivePeakPrice.add(reActiveHighPrice).add(reActiveFlatPrice).add(reActiveValleyPrice));
            // 实际收益按“放电总价-充电总价”口径计算
            ammeterRevenue.setActualRevenue(ammeterRevenue.getReActiveTotalPrice().subtract(ammeterRevenue.getActiveTotalPrice()));
        });

        int weatherMissingCount = 0;
        int allPriceZeroCount = 0;
        for (AmmeterRevenueStatisListVo row : resultList) {
            String weatherDesc = row.getWeatherDesc();
            if (weatherDesc == null || weatherDesc.trim().isEmpty() || "--".equals(weatherDesc.trim())) {
                weatherMissingCount++;
            }
            boolean allPriceZero = safeCompareToZero(row.getActivePeakPrice())
                    && safeCompareToZero(row.getActiveHighPrice())
                    && safeCompareToZero(row.getActiveFlatPrice())
                    && safeCompareToZero(row.getActiveValleyPrice())
                    && safeCompareToZero(row.getReActivePeakPrice())
                    && safeCompareToZero(row.getReActiveHighPrice())
                    && safeCompareToZero(row.getReActiveFlatPrice())
                    && safeCompareToZero(row.getReActiveValleyPrice());
            if (allPriceZero) {
                allPriceZeroCount++;
            }
            log.info("收益报表明细, date: {}, dayType: {}, weather: {}, charge[尖:{} 峰:{} 平:{} 谷:{} 总:{}], discharge[尖:{} 峰:{} 平:{} 谷:{} 总:{}], actualRevenue: {}",
                    row.getDataTime(),
                    row.getDayType(),
                    row.getWeatherDesc(),
                    row.getActivePeakPrice(),
                    row.getActiveHighPrice(),
                    row.getActiveFlatPrice(),
                    row.getActiveValleyPrice(),
                    row.getActiveTotalPrice(),
                    row.getReActivePeakPrice(),
                    row.getReActiveHighPrice(),
                    row.getReActiveFlatPrice(),
                    row.getReActiveValleyPrice(),
                    row.getReActiveTotalPrice(),
                    row.getActualRevenue());
        }
        log.info("收益报表查询完成, siteId: {}, startTime: {}, endTime: {}, totalRows: {}, weatherMissingRows: {}, allPriceZeroRows: {}",
                requestVo.getSiteId(),
                requestVo.getStartTime(),
                requestVo.getEndTime(),
                resultList.size(),
                weatherMissingCount,
                allPriceZeroCount);

        fillRevenueRemarks(requestVo.getSiteId(), REVENUE_REPORT_KEY, resultList);
        return resultList;
    }

    private boolean safeCompareToZero(BigDecimal value) {
        return value == null || value.compareTo(BigDecimal.ZERO) == 0;
    }

    private BigDecimal nz(BigDecimal value) {
        return value == null ? BigDecimal.ZERO : value;
    }

    public static AmmeterRevenueStatisListVo calculateDailyBill(AmmeterStatisListVo ammeter, List<EnergyPriceConfigVo> priceList) {
        AmmeterRevenueStatisListVo ammeterRevenue = new AmmeterRevenueStatisListVo();
        ammeterRevenue.setDataTime(ammeter.getDataTime());
        if (CollectionUtils.isEmpty(priceList)) {
            return ammeterRevenue;
        }
        EnergyPriceConfigVo price = priceList.get(0);
        ammeterRevenue.setActivePeakPrice(ammeter.getActivePeakKwh().multiply(price.getPeak()));
        ammeterRevenue.setActiveHighPrice(ammeter.getActiveHighKwh().multiply(price.getHigh()));
        ammeterRevenue.setActiveFlatPrice(ammeter.getActiveFlatKwh().multiply(price.getFlat()));
        ammeterRevenue.setActiveValleyPrice(ammeter.getActiveValleyKwh().multiply(price.getValley()));
        ammeterRevenue.setReActivePeakPrice(ammeter.getReActivePeakKwh().multiply(price.getPeak()));
        ammeterRevenue.setReActiveHighPrice(ammeter.getReActiveHighKwh().multiply(price.getHigh()));
        ammeterRevenue.setReActiveFlatPrice(ammeter.getReActiveFlatKwh().multiply(price.getFlat()));
        ammeterRevenue.setReActiveValleyPrice(ammeter.getReActiveValleyKwh().multiply(price.getValley()));
        ammeterRevenue.setActiveTotalPrice(ammeterRevenue.getActivePeakPrice().add(ammeterRevenue.getActiveHighPrice()).add(ammeterRevenue.getActiveFlatPrice()).add(ammeterRevenue.getActiveValleyPrice()));
        ammeterRevenue.setReActiveTotalPrice(ammeterRevenue.getReActivePeakPrice().add(ammeterRevenue.getReActiveHighPrice()).add(ammeterRevenue.getReActiveFlatPrice()).add(ammeterRevenue.getReActiveValleyPrice()));
        return ammeterRevenue;
    }

    private static boolean isInTimeRange(String startTimeStr, String endTimeStr, String timePartStr) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm");
        // 解析时间字符串为LocalTime对象
        LocalTime checkTime = LocalTime.parse(timePartStr, formatter);
        // 定义时间范围
        LocalTime startTime = LocalTime.parse(startTimeStr, formatter);
        LocalTime endTime = LocalTime.parse(endTimeStr, formatter);
        // 判断时间是否在指定范围内
        return checkTime.compareTo(startTime) >= 0
                && checkTime.compareTo(endTime) < 0;
    }

    private static void calculateByCostType(AmmeterStatisListVo ammeter, EnergyPriceConfigVo priceConfig, AmmeterRevenueStatisListVo ammeterRevenue) {
        switch (CostType.getEnumByCode(priceConfig.getCostType())) {
            case PEAK:
                ammeterRevenue.setActivePeakPrice(ammeterRevenue.getActivePeakPrice().add(ammeter.getActiveTotalKwh().multiply(priceConfig.getPeak())));
                ammeterRevenue.setReActivePeakPrice(ammeterRevenue.getReActivePeakPrice().add(ammeter.getReActiveTotalKwh().multiply(priceConfig.getPeak())));
                break;
            case HIGH:
                ammeterRevenue.setActiveHighPrice(ammeterRevenue.getActiveHighPrice().add(ammeter.getActiveTotalKwh().multiply(priceConfig.getHigh())));
                ammeterRevenue.setReActiveHighPrice(ammeterRevenue.getReActiveHighPrice().add(ammeter.getReActiveTotalKwh().multiply(priceConfig.getHigh())));
                break;
            case FLAT:
                ammeterRevenue.setActiveFlatPrice(ammeterRevenue.getActiveFlatPrice().add(ammeter.getActiveTotalKwh().multiply(priceConfig.getFlat())));
                ammeterRevenue.setReActiveFlatPrice(ammeterRevenue.getReActiveFlatPrice().add(ammeter.getReActiveTotalKwh().multiply(priceConfig.getFlat())));
                break;
            case VALLEY:
                ammeterRevenue.setActiveValleyPrice(ammeterRevenue.getActiveValleyPrice().add(ammeter.getActiveTotalKwh().multiply(priceConfig.getValley())));
                ammeterRevenue.setReActiveValleyPrice(ammeterRevenue.getReActiveValleyPrice().add(ammeter.getReActiveTotalKwh().multiply(priceConfig.getValley())));
                break;
            default:
                break;
        }
    }

    private static List<LocalDate> generateTargetDates(String startDateStr, String endDateStr) {
        // 定义日期格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 解析日期字符串
        LocalDate startDate = LocalDate.parse(startDateStr, formatter);
        LocalDate endDate = LocalDate.parse(endDateStr, formatter);

        List<LocalDate> targetDates = new ArrayList<>();
        targetDates.add(startDate); // 添加起始日期
        while (startDate.isBefore(endDate)) {
            startDate = startDate.plusDays(1);    // 递增1天
            targetDates.add(startDate); // 添加递增后的日期
        }
        return targetDates;
    }

    private void dealWithAmmeterTotalDate(AmmeterStatisListVo ammeterStatisListVo, AmmeterStatisListVo totalVo) {
        // 有功
        totalVo.setActiveTotalKwh(totalVo.getActiveTotalKwh().add(ammeterStatisListVo.getActiveTotalKwh()));
        totalVo.setActivePeakKwh(totalVo.getActivePeakKwh().add(ammeterStatisListVo.getActivePeakKwh()));
        totalVo.setActiveHighKwh(totalVo.getActiveHighKwh().add(ammeterStatisListVo.getActiveHighKwh()));
        totalVo.setActiveFlatKwh(totalVo.getActiveFlatKwh().add(ammeterStatisListVo.getActiveFlatKwh()));
        totalVo.setActiveValleyKwh(totalVo.getActiveValleyKwh().add(ammeterStatisListVo.getActiveValleyKwh()));
        // 无功
        totalVo.setReActiveTotalKwh(totalVo.getReActiveTotalKwh().add(ammeterStatisListVo.getReActiveTotalKwh()));
        totalVo.setReActivePeakKwh(totalVo.getReActivePeakKwh().add(ammeterStatisListVo.getReActivePeakKwh()));
        totalVo.setReActiveHighKwh(totalVo.getReActiveHighKwh().add(ammeterStatisListVo.getReActiveHighKwh()));
        totalVo.setReActiveFlatKwh(totalVo.getReActiveFlatKwh().add(ammeterStatisListVo.getReActiveFlatKwh()));
        totalVo.setReActiveValleyKwh(totalVo.getReActiveValleyKwh().add(ammeterStatisListVo.getReActiveValleyKwh()));
    }

    // 功率曲线
    @Override
    public List<PowerStatisListVo> getPowerDataList(DateSearchRequest requestVo) {
        List<PowerStatisListVo> dataList = new ArrayList<>();

        Date startDate = requestVo.getStartDate();
        Date endDate = requestVo.getEndDate();
        if (startDate == null || endDate == null) {
            // 默认按分钟展示-前一个小时数据
            LocalDateTime oneHourAgo = LocalDateTime.now().minusHours(1).truncatedTo(ChronoUnit.MINUTES);
            startDate = DateUtils.toDate(oneHourAgo);
            requestVo.setStartDate(startDate);
            requestVo.setEndDate(DateUtils.getNowDate());
//            dataList = emsAmmeterDataMapper.getPowerDataByMinutes(requestVo);
//            // 生成目标时间点列表（每分钟一个）
//            List<LocalDateTime> targetMinutes = new ArrayList<>(60);
//            LocalDateTime current = oneHourAgo;
//
//            for (int i = 0; i < 60; i++) {
//                targetMinutes.add(current); // 添加当前分钟
//                current = current.plusMinutes(1);     // 递增1分钟
//            }
//
//            // 填充数据
//            if (dataList != null && dataList.size() >= 0){
//                dataList = fillEveryMinutesData(dataList,targetMinutes);
//            }
        }
        //  开始日期和结束日期同一天，展示 0-24 小时数据
        else if (DateUtils.isSameDay(startDate, endDate)){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
//            dataList = emsAmmeterDataMapper.getPowerDataByHour(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 1
                && DateUtils.differentDaysByMillisecond(endDate, startDate) < 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 天，小于30 天，按天展示数据
//            dataList = emsAmmeterDataMapper.getPowerDataByDay(requestVo);
        } else if (DateUtils.differentDaysByMillisecond(endDate, startDate) >= 30){
            endDate = DateUtils.addDays(endDate, 1);
            requestVo.setEndDate(endDate);
            //  开始日期-结束日期大于 1 个月，按月展示数据
//            dataList = emsAmmeterDataMapper.getPowerDataByMonth(requestVo);
        }
        // 都按照5分钟一个数据点展示数据
        dataList = emsAmmeterDataMapper.getPowerDataByMinute(requestVo);

        // 负荷功率
        dealDataPower(requestVo.getSiteId(),dataList);
        return dataList.stream().sorted(Comparator.comparing(PowerStatisListVo::getStatisDate)).collect(Collectors.toList());
    }

    private List<PowerStatisListVo> fillEveryMinutesData(List<PowerStatisListVo> dataList, List<LocalDateTime> targetMinutes) {

        List<PowerStatisListVo> powerStatisListVoList = new ArrayList<>();
        if (targetMinutes == null || targetMinutes.isEmpty()) {
            return dataList;
        }

        // 数组转化成Map便于后续获取数据
        Map<String, PowerStatisListVo> dataMap = dataList.stream()
                .map(data -> new PowerStatisListVo(
                    data.getStatisDate(),
                    data.getGridPower(),
                    data.getStoragePower(),
                    data.getPvPower()
                )).collect(Collectors.toMap(
                        PowerStatisListVo::getStatisDate,
                        data -> data,
                        (existing, replacement) -> replacement
                ));

        PowerStatisListVo lastData = null;
        // 遍历时间数组
        for (LocalDateTime targetMinute : targetMinutes) {
            String tarteDate = DateUtils.convertToString(targetMinute);
            PowerStatisListVo currentData = dataMap.get(tarteDate);
            if (currentData == null && lastData != null) {
                BigDecimal pvPower = lastData.getPvPower()==null?new BigDecimal(0) :lastData.getGridPower();
                BigDecimal storagePower = lastData.getStoragePower()==null?new BigDecimal(0) :lastData.getGridPower();
                BigDecimal gridPower = lastData.getGridPower()==null?new BigDecimal(0) :lastData.getGridPower();
                currentData = new PowerStatisListVo(tarteDate.toString(),gridPower,storagePower,pvPower);
            } else if (currentData == null && lastData == null){
                // 开头无数据，所有字段设为null
                currentData = new PowerStatisListVo(tarteDate.toString(), new BigDecimal(0), new BigDecimal(0), new BigDecimal(0));
            }
            lastData = currentData;
            powerStatisListVoList.add(currentData);
        }
        return powerStatisListVoList;
    }

    private void dealDataPower(String siteId, List<PowerStatisListVo> dataList) {
        if (dataList == null || dataList.size() == 0){
            return;
        }

        for (PowerStatisListVo powerStatisListVo : dataList) {
            BigDecimal gridPower = powerStatisListVo.getGridPower() == null ? BigDecimal.ZERO : powerStatisListVo.getGridPower();
            BigDecimal storagePower = powerStatisListVo.getStoragePower() == null ? BigDecimal.ZERO : powerStatisListVo.getStoragePower();
            BigDecimal pvPower = powerStatisListVo.getPvPower() == null ? BigDecimal.ZERO : powerStatisListVo.getPvPower();
            BigDecimal loadPower = new BigDecimal(0);

            // 负荷功率=电网功率+光伏功率-储能功率
            loadPower = gridPower.add(pvPower).subtract(storagePower);
            powerStatisListVo.setLoadPower(loadPower);
//            // 电动所：负荷功率=电网功率+光伏功率-储能功率
//            if (SiteEnum.DDS.getCode().equals(siteId)){
//                loadPower = gridPower.add(pvPower).subtract(storagePower);
//                powerStatisListVo.setLoadPower(loadPower);
//            } else if (SiteEnum.FX.getCode().equals(siteId)){
//                // 奉贤：负荷功率=电网功率-储能功率；
//                loadPower = gridPower.subtract(storagePower);
//                powerStatisListVo.setLoadPower(loadPower);
//            }
        }
    }

    // 统计入参时间处理
    public void dealRequestTime(DateSearchRequest requestVo){

        Date startDate = requestVo.getStartDate();
        Date endDate = requestVo.getEndDate();
        if (startDate == null && endDate == null) {
            // 如果没有传时间，默认从今天往前7天
            LocalDate sevenDaysAgo = LocalDate.now().minusDays(6);
            startDate = DateUtils.toDate(sevenDaysAgo);
            endDate = new Date();
            requestVo.setStartDate(startDate);
            requestVo.setEndDate(endDate);
        }
    }

    /**
     * 获取单个单体电池数据-默认7天
     * @param requestVo
     * @return
     */
    @Override
    public List<BatteryDataStatsListVo> getSingleBatteryData(DateSearchRequest requestVo) {
        // 默认时间-7天
        dealRequestTime(requestVo);
        return emsBatteryDataDayMapper.getBatteryDayData(requestVo);
    }

    @Override
    public void exportAmmeterData(HttpServletResponse response, StatisAmmeterDateRequest requestVo) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("电表报表");
        sheet.setDefaultColumnWidth(10);
        sheet.setDefaultRowHeightInPoints(25);

        // 设置第一行
        Row row1 = sheet.createRow(0);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("汇总");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("充电量");
        Cell cell3 = row1.createCell(6);
        cell3.setCellValue("放电量");
        Cell cell4 = row1.createCell(11);
        cell4.setCellValue("效率");
        Cell cell5Top = row1.createCell(12);
        cell5Top.setCellValue("备注");

        // 合并充电量列
        CellRangeAddress mergeRegion1 = new CellRangeAddress(0, 0, 1, 5);
        sheet.addMergedRegion(mergeRegion1);

        // 合并放电量列
        CellRangeAddress mergeRegion2 = new CellRangeAddress(0, 0, 6, 10);
        sheet.addMergedRegion(mergeRegion2);

        // 设置第二行
        Row row2 = sheet.createRow(1);
        Cell cell5 = row2.createCell(0);
        cell5.setCellValue("日期");
        Cell cell6 = row2.createCell(1);
        cell6.setCellValue("尖");
        Cell cell7 = row2.createCell(2);
        cell7.setCellValue("峰");
        Cell cell8 = row2.createCell(3);
        cell8.setCellValue("平");
        Cell cell9 = row2.createCell(4);
        cell9.setCellValue("谷");
        Cell cell10 = row2.createCell(5);
        cell10.setCellValue("总");
        Cell cell11 = row2.createCell(6);
        cell11.setCellValue("尖");
        Cell cell12 = row2.createCell(7);
        cell12.setCellValue("峰");
        Cell cell13 = row2.createCell(8);
        cell13.setCellValue("平");
        Cell cell14 = row2.createCell(9);
        cell14.setCellValue("谷");
        Cell cell15 = row2.createCell(10);
        cell15.setCellValue("总");
        Cell cell16 = row2.createCell(11);
        cell16.setCellValue("");
        Cell cell17 = row2.createCell(12);
        cell17.setCellValue("备注");

        // 设置背景颜色
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 应用样式到第一行和第二行
        Iterator<Cell> row1CellIterator = row1.cellIterator();
        while (row1CellIterator.hasNext()) {
            int i = row1CellIterator.next().getColumnIndex();
            row1.getCell(i).setCellStyle(headerStyle);
        }
        Iterator<Cell> row2CellIterator = row2.cellIterator();
        while (row2CellIterator.hasNext()) {
            int i = row2CellIterator.next().getColumnIndex();
            row2.getCell(i).setCellStyle(headerStyle);
        }

        // 设置数据行颜色
        CellStyle row1Style = workbook.createCellStyle();
        row1Style.setAlignment(HorizontalAlignment.CENTER);
        row1Style.setVerticalAlignment(VerticalAlignment.CENTER);
        row1Style.setBorderTop(BorderStyle.THIN);
        row1Style.setBorderBottom(BorderStyle.THIN);
        row1Style.setBorderLeft(BorderStyle.THIN);
        row1Style.setBorderRight(BorderStyle.THIN);
        row1Style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        row1Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        CellStyle row2Style = workbook.createCellStyle();
        row2Style.setAlignment(HorizontalAlignment.CENTER);
        row2Style.setVerticalAlignment(VerticalAlignment.CENTER);
        row2Style.setBorderTop(BorderStyle.THIN);
        row2Style.setBorderBottom(BorderStyle.THIN);
        row2Style.setBorderLeft(BorderStyle.THIN);
        row2Style.setBorderRight(BorderStyle.THIN);
        row2Style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        row2Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        int rowIndex = 2;
        // 查询电量报表数据,添加数据行
        List<AmmeterStatisListVo> ammeterDataList = getAmmeterDataResult(requestVo);
        for (int i = rowIndex; i < ammeterDataList.size() + rowIndex; i++) {
            AmmeterStatisListVo ammeterStatisVo = ammeterDataList.get(i - rowIndex);
            Row dataRow = sheet.createRow(i);
            Cell dataCell1 = dataRow.createCell(0);
            dataCell1.setCellValue(ammeterStatisVo.getDataTime());
            Cell dataCell2 = dataRow.createCell(1);
            dataCell2.setCellValue(ammeterStatisVo.getActivePeakKwh().doubleValue());
            Cell dataCell3 = dataRow.createCell(2);
            dataCell3.setCellValue(ammeterStatisVo.getActiveHighKwh().doubleValue());
            Cell dataCell4 = dataRow.createCell(3);
            dataCell4.setCellValue(ammeterStatisVo.getActiveFlatKwh().doubleValue());
            Cell dataCell5 = dataRow.createCell(4);
            dataCell5.setCellValue(ammeterStatisVo.getActiveValleyKwh().doubleValue());
            Cell dataCell6 = dataRow.createCell(5);
            dataCell6.setCellValue(ammeterStatisVo.getActiveTotalKwh().doubleValue());
            Cell dataCell7 = dataRow.createCell(6);
            dataCell7.setCellValue(ammeterStatisVo.getReActivePeakKwh().doubleValue());
            Cell dataCell8 = dataRow.createCell(7);
            dataCell8.setCellValue(ammeterStatisVo.getReActiveHighKwh().doubleValue());
            Cell dataCell9 = dataRow.createCell(8);
            dataCell9.setCellValue(ammeterStatisVo.getReActiveFlatKwh().doubleValue());
            Cell dataCell10 = dataRow.createCell(9);
            dataCell10.setCellValue(ammeterStatisVo.getReActiveValleyKwh().doubleValue());
            Cell dataCell11 = dataRow.createCell(10);
            dataCell11.setCellValue(ammeterStatisVo.getReActiveTotalKwh().doubleValue());
            Cell dataCell12 = dataRow.createCell(11);
            dataCell12.setCellValue(ammeterStatisVo.getEffect().doubleValue());
            Cell dataCell13 = dataRow.createCell(12);
            dataCell13.setCellValue(ammeterStatisVo.getRemark() == null ? "" : ammeterStatisVo.getRemark());

            // 根据行号设置背景色
            if (i % 2 == 0) {
                for (int k = 0; k < dataRow.getLastCellNum(); k++) {
                    dataRow.getCell(k).setCellStyle(row1Style);
                }
            } else {
                for (int k = 0; k < dataRow.getLastCellNum(); k++) {
                    dataRow.getCell(k).setCellStyle(row2Style);
                }
            }
        }

        try
        {
            String fileName = "电表报表"  + "_" + UUID.randomUUID() + ".xlsx";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            log.error("导出电表报表异常{}", e.getMessage());
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
        }
    }

    @Override
    public void exportAmmeterRevenueData(HttpServletResponse response, StatisAmmeterDateRequest requestVo) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("收益报表");
        sheet.setDefaultColumnWidth(10);
        sheet.setDefaultRowHeightInPoints(25);

        // 设置第一行
        Row row1 = sheet.createRow(0);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("汇总");
        Cell cell2 = row1.createCell(3);
        cell2.setCellValue("充电价格");
        Cell cell3 = row1.createCell(8);
        cell3.setCellValue("放电价格");
        Cell cell4 = row1.createCell(13);
        cell4.setCellValue("实际收益");
        Cell cell5Top = row1.createCell(14);
        cell5Top.setCellValue("备注");

        // 合并充电量列
        CellRangeAddress mergeRegion1 = new CellRangeAddress(0, 0, 3, 7);
        sheet.addMergedRegion(mergeRegion1);

        // 合并放电量列
        CellRangeAddress mergeRegion2 = new CellRangeAddress(0, 0, 8, 12);
        sheet.addMergedRegion(mergeRegion2);

        // 设置第二行
        Row row2 = sheet.createRow(1);
        Cell cell5 = row2.createCell(0);
        cell5.setCellValue("日期");
        Cell cell6 = row2.createCell(1);
        cell6.setCellValue("日期类型");
        Cell cell7 = row2.createCell(2);
        cell7.setCellValue("天气情况");
        Cell cell8 = row2.createCell(3);
        cell8.setCellValue("尖");
        Cell cell9 = row2.createCell(4);
        cell9.setCellValue("峰");
        Cell cell10 = row2.createCell(5);
        cell10.setCellValue("平");
        Cell cell11 = row2.createCell(6);
        cell11.setCellValue("谷");
        Cell cell12 = row2.createCell(7);
        cell12.setCellValue("总");
        Cell cell13 = row2.createCell(8);
        cell13.setCellValue("尖");
        Cell cell14 = row2.createCell(9);
        cell14.setCellValue("峰");
        Cell cell15 = row2.createCell(10);
        cell15.setCellValue("平");
        Cell cell16 = row2.createCell(11);
        cell16.setCellValue("谷");
        Cell cell17 = row2.createCell(12);
        cell17.setCellValue("总");
        Cell cell18 = row2.createCell(13);
        cell18.setCellValue("实际收益");
        Cell cell19 = row2.createCell(14);
        cell19.setCellValue("备注");

        // 设置背景颜色
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 应用样式到第一行和第二行
        Iterator<Cell> row1CellIterator = row1.cellIterator();
        while (row1CellIterator.hasNext()) {
            int i = row1CellIterator.next().getColumnIndex();
            row1.getCell(i).setCellStyle(headerStyle);
        }
        Iterator<Cell> row2CellIterator = row2.cellIterator();
        while (row2CellIterator.hasNext()) {
            int i = row2CellIterator.next().getColumnIndex();
            row2.getCell(i).setCellStyle(headerStyle);
        }

        // 设置数据行颜色
        CellStyle row1Style = workbook.createCellStyle();
        row1Style.setAlignment(HorizontalAlignment.CENTER);
        row1Style.setVerticalAlignment(VerticalAlignment.CENTER);
        row1Style.setBorderTop(BorderStyle.THIN);
        row1Style.setBorderBottom(BorderStyle.THIN);
        row1Style.setBorderLeft(BorderStyle.THIN);
        row1Style.setBorderRight(BorderStyle.THIN);
        row1Style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        row1Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        CellStyle row2Style = workbook.createCellStyle();
        row2Style.setAlignment(HorizontalAlignment.CENTER);
        row2Style.setVerticalAlignment(VerticalAlignment.CENTER);
        row2Style.setBorderTop(BorderStyle.THIN);
        row2Style.setBorderBottom(BorderStyle.THIN);
        row2Style.setBorderLeft(BorderStyle.THIN);
        row2Style.setBorderRight(BorderStyle.THIN);
        row2Style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        row2Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 查询电量报表数据,添加数据行
        BigDecimal activeTotalPrice = BigDecimal.ZERO;
        BigDecimal activePeakPrice = BigDecimal.ZERO;
        BigDecimal activeHighPrice = BigDecimal.ZERO;
        BigDecimal activeFlatPrice = BigDecimal.ZERO;
        BigDecimal activeValleyPrice = BigDecimal.ZERO;
        BigDecimal reActiveTotalPrice = BigDecimal.ZERO;
        BigDecimal reActivePeakPrice = BigDecimal.ZERO;
        BigDecimal reActiveHighPrice = BigDecimal.ZERO;
        BigDecimal reActiveFlatPrice = BigDecimal.ZERO;
        BigDecimal reActiveValleyPrice = BigDecimal.ZERO;
        BigDecimal actualRevenue = BigDecimal.ZERO;
        List<AmmeterRevenueStatisListVo> ammeterRevenueDataList = getAmmeterRevenueDataResult(requestVo);
        int rowIndex = 2;
        int lastRowIndex = ammeterRevenueDataList.size() + rowIndex;
        for (int i = rowIndex; i < lastRowIndex; i++) {
            AmmeterRevenueStatisListVo ammeterRevenueStatisVo = ammeterRevenueDataList.get(i - rowIndex);
            Row dataRow = sheet.createRow(i);
            Cell dataCell1 = dataRow.createCell(0);
            dataCell1.setCellValue(ammeterRevenueStatisVo.getDataTime());
            Cell dataCell2 = dataRow.createCell(1);
            dataCell2.setCellValue(ammeterRevenueStatisVo.getDayType() == null ? "" : ammeterRevenueStatisVo.getDayType());
            Cell dataCell3 = dataRow.createCell(2);
            dataCell3.setCellValue(ammeterRevenueStatisVo.getWeatherDesc() == null ? "" : ammeterRevenueStatisVo.getWeatherDesc());
            Cell dataCell4 = dataRow.createCell(3);
            dataCell4.setCellValue(ammeterRevenueStatisVo.getActivePeakPrice().doubleValue());
            Cell dataCell5 = dataRow.createCell(4);
            dataCell5.setCellValue(ammeterRevenueStatisVo.getActiveHighPrice().doubleValue());
            Cell dataCell6 = dataRow.createCell(5);
            dataCell6.setCellValue(ammeterRevenueStatisVo.getActiveFlatPrice().doubleValue());
            Cell dataCell7 = dataRow.createCell(6);
            dataCell7.setCellValue(ammeterRevenueStatisVo.getActiveValleyPrice().doubleValue());
            Cell dataCell8 = dataRow.createCell(7);
            dataCell8.setCellValue(ammeterRevenueStatisVo.getActiveTotalPrice().doubleValue());
            Cell dataCell9 = dataRow.createCell(8);
            dataCell9.setCellValue(ammeterRevenueStatisVo.getReActivePeakPrice().doubleValue());
            Cell dataCell10 = dataRow.createCell(9);
            dataCell10.setCellValue(ammeterRevenueStatisVo.getReActiveHighPrice().doubleValue());
            Cell dataCell11 = dataRow.createCell(10);
            dataCell11.setCellValue(ammeterRevenueStatisVo.getReActiveFlatPrice().doubleValue());
            Cell dataCell12 = dataRow.createCell(11);
            dataCell12.setCellValue(ammeterRevenueStatisVo.getReActiveValleyPrice().doubleValue());
            Cell dataCell13 = dataRow.createCell(12);
            dataCell13.setCellValue(ammeterRevenueStatisVo.getReActiveTotalPrice().doubleValue());
            Cell dataCell14 = dataRow.createCell(13);
            dataCell14.setCellValue(ammeterRevenueStatisVo.getActualRevenue().doubleValue());
            Cell dataCell15 = dataRow.createCell(14);
            dataCell15.setCellValue(ammeterRevenueStatisVo.getRemark() == null ? "" : ammeterRevenueStatisVo.getRemark());

            // 根据行号设置背景色
            if (i % 2 == 0) {
                for (int k = 0; k < dataRow.getLastCellNum(); k++) {
                    dataRow.getCell(k).setCellStyle(row1Style);
                }
            } else {
                for (int k = 0; k < dataRow.getLastCellNum(); k++) {
                    dataRow.getCell(k).setCellStyle(row2Style);
                }
            }

            // 最后一行合计
            activePeakPrice = activePeakPrice.add(ammeterRevenueStatisVo.getActivePeakPrice());
            activeHighPrice = activeHighPrice.add(ammeterRevenueStatisVo.getActiveHighPrice());
            activeFlatPrice = activeFlatPrice.add(ammeterRevenueStatisVo.getActiveFlatPrice());
            activeValleyPrice = activeValleyPrice.add(ammeterRevenueStatisVo.getActiveValleyPrice());
            activeTotalPrice = activeTotalPrice.add(ammeterRevenueStatisVo.getActiveTotalPrice());
            reActivePeakPrice = reActivePeakPrice.add(ammeterRevenueStatisVo.getReActivePeakPrice());
            reActiveHighPrice = reActiveHighPrice.add(ammeterRevenueStatisVo.getReActiveHighPrice());
            reActiveFlatPrice = reActiveFlatPrice.add(ammeterRevenueStatisVo.getReActiveFlatPrice());
            reActiveValleyPrice = reActiveValleyPrice.add(ammeterRevenueStatisVo.getReActiveValleyPrice());
            reActiveTotalPrice = reActiveTotalPrice.add(ammeterRevenueStatisVo.getReActiveTotalPrice());
            actualRevenue = actualRevenue.add(ammeterRevenueStatisVo.getActualRevenue());
        }

        if (!CollectionUtils.isEmpty(ammeterRevenueDataList)) {
            // 设置第后一行
            Row lastRow = sheet.createRow(lastRowIndex);
            Cell lastRowCell1 = lastRow.createCell(0);
            lastRowCell1.setCellValue("合计");
            Cell lastRowCell2 = lastRow.createCell(1);
            lastRowCell2.setCellValue("-");
            Cell lastRowCell3 = lastRow.createCell(2);
            lastRowCell3.setCellValue("-");
            Cell lastRowCell4 = lastRow.createCell(3);
            lastRowCell4.setCellValue(activePeakPrice.doubleValue());
            Cell lastRowCell5 = lastRow.createCell(4);
            lastRowCell5.setCellValue(activeHighPrice.doubleValue());
            Cell lastRowCell6 = lastRow.createCell(5);
            lastRowCell6.setCellValue(activeFlatPrice.doubleValue());
            Cell lastRowCell7 = lastRow.createCell(6);
            lastRowCell7.setCellValue(activeValleyPrice.doubleValue());
            Cell lastRowCell8 = lastRow.createCell(7);
            lastRowCell8.setCellValue(activeTotalPrice.doubleValue());
            Cell lastRowCell9 = lastRow.createCell(8);
            lastRowCell9.setCellValue(reActivePeakPrice.doubleValue());
            Cell lastRowCell10 = lastRow.createCell(9);
            lastRowCell10.setCellValue(reActiveHighPrice.doubleValue());
            Cell lastRowCell11 = lastRow.createCell(10);
            lastRowCell11.setCellValue(reActiveFlatPrice.doubleValue());
            Cell lastRowCell12 = lastRow.createCell(11);
            lastRowCell12.setCellValue(reActiveValleyPrice.doubleValue());
            Cell lastRowCell13 = lastRow.createCell(12);
            lastRowCell13.setCellValue(reActiveTotalPrice.doubleValue());
            Cell lastRowCell14 = lastRow.createCell(13);
            lastRowCell14.setCellValue(actualRevenue.doubleValue());
            Cell lastRowCell15 = lastRow.createCell(14);
            lastRowCell15.setCellValue("");
            Iterator<Cell> lastRowCellIterator = lastRow.cellIterator();
            while (lastRowCellIterator.hasNext()) {
                int i = lastRowCellIterator.next().getColumnIndex();
                lastRow.getCell(i).setCellStyle(headerStyle);
            }
        }

        try
        {
            String fileName = "收益报表"  + "_" + UUID.randomUUID() + ".xlsx";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            log.error("导出收益报表异常{}", e.getMessage());
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
        }
    }
}
