package com.mvc.service.tyrq;

import com.mvc.model.StationAccModel;
import com.mvc.model.TAireadModel;
import com.mvc.netty.dealconstant.ResourcePath;
import com.mvc.netty.dealconstant.ResourceTag;
import com.mvc.service.copy.TAireadService;
import com.mvc.service.taizhang.StationAccService;
import com.mvc.utils.DateUtil;
import com.mvc.utils.ImageUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.temporal.ChronoUnit;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Transactional
public class TyrqDataExportService {

    @Resource
    private Environment env;

    @Resource
    private TAireadService aireadService;

    @Resource
    private StationAccService stationAccService;

    //是否导出图片(单表)
    private boolean isExportImage = true;

    /**
     * 单表数据导出
     *
     * @param response
     * @param stationCode
     * @param startTime
     * @param endTime
     */
    public void exportSingleMeterData(HttpServletResponse response, String stationCode, String startTime, String endTime) {
        try {
            Map<String, Object> map = aireadService.findAllTAireadByStationCode(stationCode, startTime, endTime, 1, 1000);
            List<TAireadModel> list = (List<TAireadModel>) map.get("list");

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(stationCode);

            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();

            // 创建单元格样式并设置居中
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置边框样式
            /*cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
            cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            cellStyle.setBorderRight(BorderStyle.THIN); // 右*/

            //标题加粗
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            cellStyle.setFont(font);

            XSSFRow row0 = sheet.createRow(0);
            XSSFCell cell0 = null;
            //创建表头
            for (SingleMeterColumn column : SingleMeterColumn.values()) {
                if (column.getType() == 0 || (isExportImage && column.getType() == 1)) {
                    cell0 = row0.createCell(column.getIndex());
                    cell0.setCellValue(column.getName());
                    cell0.setCellStyle(cellStyle);

                    //设置列宽-POI中Sheet列宽是通过字符个数来确定的，列宽单位为一个字符宽度的1/256
                    sheet.setColumnWidth(column.getIndex(), column.getWidth() * 256);
                }
            }

            //序号列样式,水平垂直居中,不加粗
            CellStyle dataFormatStyle = workbook.createCellStyle();
            dataFormatStyle.setAlignment(HorizontalAlignment.CENTER);
            dataFormatStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataFormatStyle.setBorderTop(BorderStyle.THIN); // 上边框
            dataFormatStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            dataFormatStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            dataFormatStyle.setBorderRight(BorderStyle.THIN); // 右

            /*CellStyle otherStyle = workbook.createCellStyle();
            otherStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置边框样式
            otherStyle.setBorderTop(BorderStyle.THIN); // 上边框
            otherStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            otherStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            otherStyle.setBorderRight(BorderStyle.THIN); // 右*/

            //处理内容
            int rowNo = 1;
            for (int i = list.size() - 1; i >= 0; i--) {
                TAireadModel model = list.get(i);
                XSSFRow row = sheet.createRow(rowNo);
                XSSFCell cell = row.createCell(SingleMeterColumn.SERIAL_NUMBER.getIndex());//序号
                cell.setCellValue(rowNo);
                //cell.setCellStyle(dataFormatStyle);

                cell = row.createCell(SingleMeterColumn.STATION_CODE.getIndex());//抄表编号
                cell.setCellValue(model.getStationCode());
                //cell.setCellStyle(otherStyle);

                cell = row.createCell(SingleMeterColumn.STATION_NAME.getIndex());//客户名称
                cell.setCellValue(model.getStationName());
                //cell.setCellStyle(otherStyle);

                cell = row.createCell(SingleMeterColumn.TOTAL_USAGE.getIndex());//累计用气量
                cell.setCellValue(toDoubleWithTwoDecimalPlaces(model.getResultValue()));
                //cell.setCellStyle(otherStyle);

                cell = row.createCell(SingleMeterColumn.SAMPLE_TIME.getIndex());//抄表时间
                cell.setCellValue(DateUtil.formateDate(model.getSampleTime(), DateUtil.PATTERN_DATA_NORMAL));
                //cell.setCellStyle(otherStyle);

                //大图优先
                String jpgName = ImageUtil.isImageByExtension(model.getBigJpgUrl()) ? model.getBigJpgUrl() : model.getSmallJpgUrl();
                //jpgName = "E:/shangchuan/24010300017_20240312001716_B.jpg";
                if (isExportImage && ImageUtil.isImageByExtension(jpgName)) {
                    row.createCell(SingleMeterColumn.METER_IMAGE.getIndex());
                    row.createCell(SingleMeterColumn.METER_IMAGE.getIndex() + 1);
                    //先替换URL
                    jpgName = jpgName.replaceAll(env.getProperty(ResourceTag.ResourceUrlTag.getTag()),env.getProperty(ResourceTag.UploadPathTag.getTag()));
                    //去掉/image/
                    jpgName = jpgName.replaceAll(ResourcePath.Image.getPath(),"");
                    //本地测试时使用
                    ////jpgName = jpgName.replaceAll("https://www.xzeai.com:6090",env.getProperty(ResourceTag.UploadPathTag.getTag()));
                    File file = new File(jpgName);
                    if (file.isFile() && file.exists()) {
                        FileInputStream fileInputStream = new FileInputStream(file);
                        byte[] picbyte = new byte[(int) file.length()];
                        fileInputStream.read(picbyte);
                        fileInputStream.close();
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, SingleMeterColumn.METER_IMAGE.getIndex(), rowNo, SingleMeterColumn.METER_IMAGE.getIndex() + 1, rowNo + 1);
                        //XSSFClientAnchor anchor = new XSSFClientAnchor(-10, -10, 0, 0, SingleMeterColumn.METER_IMAGE.getIndex(), rowNo, SingleMeterColumn.METER_IMAGE.getIndex() + 1, rowNo + 1);
                        patriarch.createPicture(anchor, workbook.addPicture(picbyte, XSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                }
                if (isExportImage) {
                    row.setHeightInPoints(70);
                }
                rowNo++;
            }

            // 冻结首行
            sheet.createFreezePane(0, 1); // 参数分别代表列分隔和行分隔，0表示不冻结任何列，1表示冻结第一行之后的行

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] bytes = os.toByteArray();

            String fileName = stationCode + "_" + startTime.substring(0, 8) + "_" + endTime.substring(0, 8) + ".xlsx";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(bytes);
            outputStream.close();
            os.close();
            workbook.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 根据站代码导出多表
     *
     * @param unitCode
     */
    public void exportMultipleMeterDataByUnitCode(HttpServletResponse response, String unitCode, String startTime, String endTime) {
        try {
            List<StationAccModel> accModels = stationAccService.findStationAccByUnitCode(unitCode);

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("全部表");

            creatHeader(workbook, sheet);

            LocalDate today = LocalDate.now();
            // 使用TemporalAdjusters获取今年的第一天
            LocalDate firstDayOfYear = today.with(TemporalAdjusters.firstDayOfYear());
            // 将LocalDate转换为Date
            Date firstDayOfYearAsDate = Date.from(firstDayOfYear.atStartOfDay(ZoneId.systemDefault()).toInstant());

            Date dtStartTime = DateUtil.parseTimeStr(startTime, DateUtil.PATTERN_DATA_UNSIGNED);
            //查询的开始时间是否是今年
            boolean isThisYear = dtStartTime.compareTo(firstDayOfYearAsDate) > -1;

            int no = 1;
            for (StationAccModel accModel : accModels) {
                XSSFRow row = sheet.createRow(no);

                XSSFCell cell = row.createCell(MultipleMeterColumn.SERIAL_NUMBER.getIndex());
                cell.setCellValue(no);

                cell = row.createCell(MultipleMeterColumn.STATION_CODE.getIndex());//抄表编号
                cell.setCellValue(accModel.getStationCode());

                cell = row.createCell(MultipleMeterColumn.STATION_NAME.getIndex());//客户名称
                cell.setCellValue(accModel.getStationName());

                fillRowData(accModel.getStationCode(), startTime, endTime, firstDayOfYearAsDate, isThisYear, row,workbook);
                no++;
            }
            sheet.createFreezePane(0, 1); // 参数分别代表列分隔和行分隔，0表示不冻结任何列，1表示冻结第一行之后的行
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] bytes = os.toByteArray();

            String fileName = "全部表_" + startTime.substring(0, 8) + "_" + endTime.substring(0, 8) + ".xlsx";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(bytes);
            outputStream.close();
            os.close();
            workbook.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void creatHeader(XSSFWorkbook workbook, XSSFSheet sheet) {
        // 创建单元格样式并设置居中
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //标题加粗
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);

        //创建表头
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        for (MultipleMeterColumn column : MultipleMeterColumn.values()) {
            cell = row.createCell(column.getIndex());//序号
            cell.setCellValue(column.getName());
            cell.setCellStyle(cellStyle);

            sheet.setColumnWidth(column.getIndex(), column.getWidth() * 256);
        }
    }

    private void fillRowData(String stationCode, String startTime, String endTime, Date firstDay, Boolean isThisYear, XSSFRow row,XSSFWorkbook workbook) {
        //测试使用
        /*if(stationCode.equals("3512133801")){
            System.out.println(stationCode);
        }*/

        //非分比的单元格格式
        CellStyle style = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("0.00%")); // 设置百分比格式，保留两位小数

        LocalDate today = LocalDate.now();
        // 使用TemporalAdjusters获取今年的第一天
        LocalDate firstDayOfYear = today.with(TemporalAdjusters.firstDayOfYear());

        //开始时间在今年则使用今天第一天作为开始时间,否则使用原始的startTime
        String queryStartTime = isThisYear ? DateUtil.formateDate(firstDay, DateUtil.PATTERN_DATA_UNSIGNED) : startTime;
        String queryEndTime = DateUtil.getCurrentTime(DateUtil.PATTERN_DATA_UNSIGNED);

        Date dtStartTime = DateUtil.parseTimeStr(startTime, DateUtil.PATTERN_DATA_UNSIGNED);
        Date dtEndTime = DateUtil.parseTimeStr(endTime, DateUtil.PATTERN_DATA_UNSIGNED);

        Map<String, Object> map = aireadService.findAllTAireadByStationCode(stationCode, queryStartTime, queryEndTime, 1, 1000);
        List<TAireadModel> list = (List<TAireadModel>) map.get("list");

        //查询时间范围内的数据
        List<TAireadModel> temp = list.stream().filter
                (s -> s.getSampleTime().compareTo(dtStartTime) > -1
                        && s.getSampleTime().compareTo(dtEndTime) < 1
                        && s.getResultValue() != null
                        && s.getResultValue().compareTo(BigDecimal.ZERO) > 0).collect(Collectors.toList());
        if (temp.size() > 1) {
            TAireadModel first = temp.get(temp.size() - 1);
            TAireadModel last = temp.get(0);

            //正常情况至少是一天的数据,但实际会有
            long mins = DateUtil.getMinsbetweenTime(first.getSampleTime(), last.getSampleTime());
            if (mins > 60) {
                XSSFCell cell = row.createCell(MultipleMeterColumn.SEL_START_TIME.getIndex());//查询时间的有效开始时间
                cell.setCellValue(DateUtil.formateDate(first.getSampleTime(), DateUtil.PATTERN_DATA_NORMAL));

                cell = row.createCell(MultipleMeterColumn.SEL_START_USAGE.getIndex());//选定时间起始表底
                cell.setCellValue(toDoubleWithTwoDecimalPlaces(first.getResultValue()));

                cell = row.createCell(MultipleMeterColumn.SEL_END_TIME.getIndex());//查询时间的有效结束时间
                cell.setCellValue(DateUtil.formateDate(last.getSampleTime(), DateUtil.PATTERN_DATA_NORMAL));

                cell = row.createCell(MultipleMeterColumn.SEL_END_USAGE.getIndex());//选定时间结束表底
                cell.setCellValue(toDoubleWithTwoDecimalPlaces(last.getResultValue()));

                cell = row.createCell(MultipleMeterColumn.SEL_USAGE.getIndex());//选定时间用量
                BigDecimal value = last.getResultValue().subtract(first.getResultValue());
                cell.setCellValue(toDoubleWithTwoDecimalPlaces(value));

                cell = row.createCell(MultipleMeterColumn.SEL_AVE_USAGE.getIndex());//选定时间日均用量
                //日均用量--(天数计算使用的是实际时间,有数据的,并不是查询条件中的开始结束时间)
                cell.setCellValue(getDailyAverageUsage(value, first.getSampleTime(), last.getSampleTime()));
            }
        }
        //选定时间畅通率(包含无效识别值的)
        XSSFCell cell = row.createCell(MultipleMeterColumn.SEL_OPERA_RATE.getIndex());
        temp = list.stream().filter
                (s -> s.getSampleTime().compareTo(dtStartTime) > -1
                        && s.getSampleTime().compareTo(dtEndTime) < 1).collect(Collectors.toList());
        long days = DateUtil.daysBetween(dtStartTime, dtEndTime) + 1 ;//一天一条,天数=条数(+1是应该包含最后一天)
        if (temp.size() > 0 && days > 0) {
            cell.setCellStyle(style);
            int num = getValidNumber(temp);
            double rate = num /  (double)days;
            cell.setCellValue(rate);
        }

        //本年数据
        temp = list.stream().filter(s -> s.getSampleTime().compareTo(firstDay) > -1 &&
                s.getResultValue() != null &&
                s.getResultValue().compareTo(BigDecimal.ZERO) > 0).collect(Collectors.toList());
        if (temp.size() > 1) {
            TAireadModel first = temp.get(temp.size() - 1);
            TAireadModel last = temp.get(0);

            cell = row.createCell(MultipleMeterColumn.YEAR_START_TIME.getIndex());//本年有效开始时间
            cell.setCellValue(DateUtil.formateDate(first.getSampleTime(), DateUtil.PATTERN_DATA_NORMAL));

            cell = row.createCell(MultipleMeterColumn.YEAR_START_USAGE.getIndex());//本年起始表底
            cell.setCellValue(toDoubleWithTwoDecimalPlaces(first.getResultValue()));

            cell = row.createCell(MultipleMeterColumn.YEAR_END_TIME.getIndex());//本年有效结束时间
            cell.setCellValue(DateUtil.formateDate(last.getSampleTime(), DateUtil.PATTERN_DATA_NORMAL));

            cell = row.createCell(MultipleMeterColumn.YEAR_END_USAGE.getIndex());//本年当前表底
            cell.setCellValue(toDoubleWithTwoDecimalPlaces(last.getResultValue()));

            cell = row.createCell(MultipleMeterColumn.YEAR_USAGE.getIndex());//本年用量
            BigDecimal value = last.getResultValue().subtract(first.getResultValue());
            cell.setCellValue(toDoubleWithTwoDecimalPlaces(value));

            cell = row.createCell(MultipleMeterColumn.YEAR_AVE_USAGE.getIndex());//本年日均用量
            //日均用量(年)(天数计算使用的是实际时间,有数据的,并不是查询条件中的开始结束时间)(用分钟计算,因为开始结束时间不绝对是整点)
            cell.setCellValue(getDailyAverageUsage(value, first.getSampleTime(), last.getSampleTime()));
        }

        //选定时间畅通率(包含无效识别值的)
        cell = row.createCell(MultipleMeterColumn.YEAR_OPERA_RATE.getIndex());
        temp = list.stream().filter(s -> s.getSampleTime().compareTo(firstDay) > -1).collect(Collectors.toList());
        days = ChronoUnit.DAYS.between(firstDayOfYear, today) + 1 ;//一天一条,天数=条数(+1是应该包含最后一天)
        if (temp.size() > 0 && days > 0) {
            cell.setCellStyle(style);
            int num = getValidNumber(temp);
            double rate = num / (double)days;
            cell.setCellValue(rate);
        }
    }

    /**
     * 获取有效的记录数(同一天只保留一条)
     *
     * @param list
     * @return
     */
    private int getValidNumber(List<TAireadModel> list) {
        Set<LocalDate> uniqueDates = new HashSet<>();
        // 遍历List，将每个CustomObject的myDate转换为LocalDate，并添加到Set中
        for (TAireadModel model : list) {
            Date sampleTime = model.getSampleTime();
            LocalDate localDate = sampleTime.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            uniqueDates.add(localDate);
        }
        return uniqueDates.size();
    }

    /**
     * 格式化为Double
     *
     * @param value
     * @return
     */
    private double toDoubleWithTwoDecimalPlaces(BigDecimal value) {
        value = value == null ? BigDecimal.ZERO : value;
        value = value.setScale(2, RoundingMode.HALF_UP);
        return value.doubleValue();
    }

    /**
     * 获取每日平均用量
     *
     * @param value
     * @param startTime
     * @param endTime
     * @return
     */
    private double getDailyAverageUsage(BigDecimal value, Date startTime, Date endTime) {
        //相差分钟数
        long mins = DateUtil.getMinsbetweenTime(startTime, endTime);
        if (mins == 0) {
            return 0;
        }
        //平均每分钟用量
        BigDecimal minsUsage = value.divide(new BigDecimal(mins), new MathContext(5, RoundingMode.HALF_UP));
        //获取每天用量
        BigDecimal dayUsage = minsUsage.multiply(new BigDecimal(60 * 24));
        return toDoubleWithTwoDecimalPlaces(dayUsage);
    }

    /**
     * 单表列结构
     */
    enum SingleMeterColumn {
        SERIAL_NUMBER("序号", 0, 8, 0),
        STATION_CODE("抄表编号", 1, 12, 0),
        STATION_NAME("客户名称", 2, 40, 0),
        TOTAL_USAGE("累计用量", 3, 15, 0),
        SAMPLE_TIME("抄表时间", 4, 21, 0),
        METER_IMAGE("图片", 5, 21, 1);

        SingleMeterColumn(String name, Integer index, Integer width, Integer type) {
            this.name = name;
            this.index = index;
            this.width = width;
            this.type = type;
        }

        private String name;
        private Integer index;
        private Integer width;
        private Integer type;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public Integer getIndex() {
            return index;
        }

        public void setIndex(Integer index) {
            this.index = index;
        }

        public Integer getWidth() {
            return width;
        }

        public void setWidth(Integer width) {
            this.width = width;
        }

        public Integer getType() {
            return type;
        }

        public void setType(Integer type) {
            this.type = type;
        }
    }

    /**
     * 多表列结构
     */
    enum MultipleMeterColumn {
        SERIAL_NUMBER("序号", 0, 7),
        STATION_CODE("抄表编号", 1, 12),
        STATION_NAME("客户名称", 2, 40),
        SEL_START_TIME("选定实际开始时间", 3, 20),
        SEL_START_USAGE("选定时间起始表底", 4, 20),
        SEL_END_TIME("选定实际结束时间", 5, 20),
        SEL_END_USAGE("选定时间结束表底", 6, 20),
        SEL_USAGE("选定时间用量", 7, 15),
        SEL_AVE_USAGE("选定时间日均用量", 8, 20),
        SEL_OPERA_RATE("选定时间畅通率", 9, 15),
        YEAR_START_TIME("本年实际开始时间", 10, 20),
        YEAR_START_USAGE("本年起始表底", 11, 15),
        YEAR_END_TIME("本年实际结束时间", 12, 20),
        YEAR_END_USAGE("本年当前表底", 13, 15),
        YEAR_USAGE("本年用量", 14, 15),
        YEAR_AVE_USAGE("本年日均用量", 15, 15),
        YEAR_OPERA_RATE("本年畅通率", 16, 15);

        MultipleMeterColumn(String name, Integer index, Integer width) {
            this.name = name;
            this.index = index;
            this.width = width;
        }

        private String name;
        private Integer index;
        private Integer width;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public Integer getIndex() {
            return index;
        }

        public void setIndex(Integer index) {
            this.index = index;
        }

        public Integer getWidth() {
            return width;
        }

        public void setWidth(Integer width) {
            this.width = width;
        }
    }
}
