package com.whyc.service;

import com.whyc.dto.ActmStopReason;
import com.whyc.pojo.db_lithium_testdata.BattLithiumTestData;
import com.whyc.pojo.db_lithium_testdata.BattLithiumTestDataInf;
import com.whyc.util.ActionUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@Service
public class ExcelExportService {
    @Autowired(required = false)
    private SubTablePageInfoService subService;

    @Autowired(required = false)
    private BattLithiumTestDataInfService infService;

    //导出文件
    public void exportExcel1(Integer devId,  Integer testRecordCount,HttpServletResponse response) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("设备历史数据");
        String[] titiles=new String[]{"测试时间","电池组编号","单体编号","测试次数","测试类型","测试记录数"
                                    ,"组端电压(V)","测试电流(A)","测试容量(AH)","单体电压(V)","单体温度(℃)"
                                    ,"单体电流(A)","单体容量(AH)","单体能量","单体状态","故障信息"};
        int rowNum=0;
        // 创建标题行
        Row row = sheet.createRow(rowNum);
        // 填充数据
        for(int i=0;i<titiles.length;i++){
            row.createCell(i).setCellValue(titiles[i]);
        }
        rowNum++;
        List<BattLithiumTestData> list=subService.getTdataById(devId,testRecordCount);
        if(list!=null){
            //数据栏
            for (int i = 0; i < list.size(); i++) {
                sheet.createRow(rowNum);  //创建行
                BattLithiumTestData tdata=list.get(i);
                for(int j=0;j<titiles.length;j++){
                    sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
                    sheet.getRow(rowNum).createCell(1).setCellValue(tdata.getBattIdx()+1);
                    sheet.getRow(rowNum).createCell(2 ).setCellValue(tdata.getMonNum());
                    sheet.getRow(rowNum).createCell(3).setCellValue(tdata.getTestRecordCount());
                    sheet.getRow(rowNum).createCell(4).setCellValue(tdata.getTestType());
                    sheet.getRow(rowNum).createCell(5).setCellValue(tdata.getRecordNum());
                    sheet.getRow(rowNum).createCell(6).setCellValue(tdata.getGroupVol());
                    sheet.getRow(rowNum).createCell(7).setCellValue(tdata.getTestCurr());
                    sheet.getRow(rowNum).createCell(8).setCellValue(tdata.getTestCap());
                    sheet.getRow(rowNum).createCell(9 ).setCellValue(tdata.getMonVol());
                    sheet.getRow(rowNum).createCell(10 ).setCellValue(tdata.getMonTmp());
                    sheet.getRow(rowNum).createCell(11 ).setCellValue(tdata.getMonCurr());
                    sheet.getRow(rowNum).createCell(12 ).setCellValue(tdata.getMonCap());
                    sheet.getRow(rowNum).createCell(13 ).setCellValue(tdata.getMonWh());
                    sheet.getRow(rowNum).createCell(14 ).setCellValue(tdata.getMonState());
                    sheet.getRow(rowNum).createCell(15 ).setCellValue(tdata.getMonFault());
                }
                rowNum++;
            }
        }
        try {
            // 写入到输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            workbook.close();

            // 设置响应头
            String filename = "设备历史数据";
            /*response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="+
                    new String(filename.getBytes("UTF-8"), "ISO8859-1")+
                    ".xlsx");*/
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode (filename, "utf-8") + ".xlsx");
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

            // 写入响应流并关闭
            response.getOutputStream().write(byteArrayOutputStream.toByteArray());
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //导出文件A200
    public void exportExcelA200(Integer devId,  Integer testRecordCount,HttpServletResponse response) {
        Workbook wb = new XSSFWorkbook();
        //测试报告封面
        exportExcelCoverA200(devId,testRecordCount,wb);
        Sheet sheet = wb.createSheet("测试数据详情");
        // 创建一个单元格样式，并设置字体大小为5
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

        Font font = wb.createFont();
        font.setFontHeightInPoints((short)20);
        cellStyle.setFont(font);

        String[] titiles=new String[]{"Time(H:M:S)","Group V(V)","Group A(A)","Cap (AH)","Temp H(°C)","Temp L(°C)"};
        List<BattLithiumTestData> list=subService.getTdataByIdWithListA200(devId,testRecordCount);

        int rowNum=0;
        // 创建标题行
        Row row = sheet.createRow(rowNum);
        row.setHeightInPoints(20.0f);
        // 填充数据
        for(int i=0;i<titiles.length;i++){
            row.createCell(i).setCellValue(titiles[i]);
        }
        if(list!=null&&list.size()>0){
            List<Integer> monNums=list.get(0).getMonNums();
            for (int i=0;i<monNums.size();i++){
                row.createCell(i+titiles.length).setCellValue("V"+monNums.get(i));
            }
        }
        rowNum++;

        if(list!=null){
            //数据栏
            for (int i = 0; i < list.size(); i++) {
                Row row1 =  sheet.createRow(rowNum);  //创建行
                row1.setHeightInPoints(20.0f);
                BattLithiumTestData tdata=list.get(i);
                sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
                sheet.getRow(rowNum).createCell(1).setCellValue(String.format("%.1f",tdata.getGroupVol()));
                sheet.getRow(rowNum).createCell(2).setCellValue(String.format("%.1f",tdata.getTestCurr()));
                sheet.getRow(rowNum).createCell(3).setCellValue(String.format("%.1f",tdata.getTestCap()));
                sheet.getRow(rowNum).createCell(4).setCellValue(String.format("%.1f",tdata.getMaxTemp()));
                sheet.getRow(rowNum).createCell(5).setCellValue(String.format("%.1f",tdata.getMinTemp()));
                if (tdata.getMonVols() != null) {
                    List<Float> monVols = tdata.getMonVols();
                    for (int j = 0; j < monVols.size(); j++) {
                        sheet.getRow(rowNum).createCell(6 + j).setCellValue(String.format("%.3f", monVols.get(j)));
                    }
                }
                rowNum++;
            }
        }
        try {
            // 写入到输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            wb.write(byteArrayOutputStream);
            wb.close();

            // 设置响应头
            String filename = "设备历史数据";
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode (filename, "utf-8") + ".xlsx");
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

            // 写入响应流并关闭
            response.getOutputStream().write(byteArrayOutputStream.toByteArray());
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //测试报告封面
    private void exportExcelCoverA200(Integer devId, Integer testRecordCount, Workbook wb) {
        Sheet sheet = wb.createSheet("测试报告封面");
        // 创建一个单元格样式，并设置字体大小为5
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

        Font font = wb.createFont();
        font.setFontHeightInPoints((short)20);
        cellStyle.setFont(font);

        //获取放电inf
        BattLithiumTestDataInf tinf=infService.getTinfExport(devId,testRecordCount);
        String[] titiles33=new String[]{"PACK/模组号:","测试开始时间:","测试结束时间:","数据类型:","停止原因:","预放容量(AH):"
                ,"放电电流(A):","测试时长(h:m:s):","单串下限(V):","组端下限(V):","电芯串数:","最高温度(°C):","最低温度(°C):","采集频率(秒):"};
        String[] titiles34=new String[]{tinf.getBatteryName(),ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), "放电数据",tinf.getStopTypeReason(),String.format("%.1f",tinf.getTestCap())
                ,String.format("%.1f",tinf.getTestCurr()),ActionUtil.secToTime(tinf.getTestTimelong()),String.format("%.1f",tinf.getMonVolLow()),String.format("%.1f",tinf.getGroupVolLow()),String.valueOf(tinf.getBatteryNumber())
                ,String.format("%.1f",tinf.getMaxTemp()),String.format("%.1f",tinf.getMinTemp()),String.valueOf(tinf.getSaveInverter())};

        String[] titiles23=new String[]{"PACK/模组号:","测试开始时间:","测试结束时间:","数据类型:","停止原因:","充电限流(A):"
               ,"测试时长(h:m:s):","单串上限(V):","电流阀值(A):","电芯串数:","充电限压(V)","最高温度(°C):","最低温度(°C):","采集频率(秒):"};
        String[] titiles24=new String[]{tinf.getBatteryName(),ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), "充电数据",tinf.getStopTypeReason(),String.format("%.1f",tinf.getTestCurr())
                ,ActionUtil.secToTime(tinf.getTestTimelong()),String.format("%.1f",tinf.getMonVolHigh()),String.format("%.1f",tinf.getTestcurrLimit()),String.valueOf(tinf.getBatteryNumber())
                ,String.format("%.1f",tinf.getCharVoltage()),String.format("%.1f",tinf.getMaxTemp()),String.format("%.1f",tinf.getMinTemp()),String.valueOf(tinf.getSaveInverter())};


        int rowNum=5;
        if(tinf.getTestType()==3){//放电
            for (int i=0;i<titiles33.length;i++){
                Row row =  sheet.createRow(rowNum);  //创建行
                row.setHeightInPoints(20.0f);
                row.createCell(3).setCellValue(titiles33[i]);
                row.createCell(4).setCellValue(titiles34[i]);
                rowNum++;
            }
        }else{
            for (int i=0;i<titiles23.length;i++){
                Row row =  sheet.createRow(rowNum);  //创建行
                row.setHeightInPoints(20.0f);
                row.createCell(3).setCellValue(titiles23[i]);
                row.createCell(4).setCellValue(titiles24[i]);
                rowNum++;
            }
        }
    }
    //ACTM导出
    public void exportExcelActm(Integer devId, Integer testRecordCount, HttpServletResponse response) {
        Workbook wb = new XSSFWorkbook();
        //测试报告封面
        exportExcelCoverActm(devId,testRecordCount,wb);
        Sheet sheet = wb.createSheet("测试数据");
        // 创建一个单元格样式，并设置字体大小为5
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

        Font font = wb.createFont();
        font.setFontHeightInPoints((short)20);
        cellStyle.setFont(font);

        String[] titiles=new String[]{"测试时长"};
        List<BattLithiumTestData> list=subService.getTdataByIdWithListActm(devId,testRecordCount);

        int rowNum=0;
        // 创建标题行
        Row row = sheet.createRow(rowNum);
        row.setHeightInPoints(20.0f);
        // 填充数据
        for(int i=0;i<titiles.length;i++){
            row.createCell(i).setCellValue(titiles[i]);
        }
        if(list!=null&&list.size()>0){
            List<Integer> monNums=list.get(0).getMonNums();
            for (int i=0;i<monNums.size();i++){
                row.createCell(4*i+titiles.length).setCellValue(monNums.get(i)+"#电压");
                row.createCell(4*i+titiles.length+1).setCellValue(monNums.get(i)+"#电流");
                row.createCell(4*i+titiles.length+2).setCellValue(monNums.get(i)+"#容量（AH）");
                row.createCell(4*i+titiles.length+3).setCellValue(monNums.get(i)+"#能量（WH）");
            }
        }
        rowNum++;

        if(list!=null){
            //数据栏
            for (int i = 0; i < list.size(); i++) {
                Row row1 =  sheet.createRow(rowNum);  //创建行
                row1.setHeightInPoints(20.0f);
                BattLithiumTestData tdata=list.get(i);
                sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
                List<Float> monVols = tdata.getMonVols();
                List<Float> monCurrs = tdata.getMonCurrs();
                List<Float> monCaps = tdata.getMonCaps();
                List<Float> monWhs = tdata.getMonWhs();
                List<Integer> monNums=list.get(i).getMonNums();
                for (int j = 0; j < monNums.size(); j++) {
                    sheet.getRow(rowNum).createCell(4*j+1 ).setCellValue(String.format("%.3f", monVols.get(j)));
                    sheet.getRow(rowNum).createCell(4*j+1+1).setCellValue(String.format("%.3f", monCurrs.get(j)));
                    sheet.getRow(rowNum).createCell(4*j+1+2).setCellValue(String.format("%.3f", monCaps.get(j)));
                    sheet.getRow(rowNum).createCell(4*j+1+3).setCellValue(String.format("%.3f", monWhs.get(j)));


                }
                rowNum++;
            }
        }
        try {
            // 写入到输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            wb.write(byteArrayOutputStream);
            wb.close();

            // 设置响应头
            String filename = "设备历史数据";
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode (filename, "utf-8") + ".xlsx");
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

            // 写入响应流并关闭
            response.getOutputStream().write(byteArrayOutputStream.toByteArray());
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //测试报告封面
    private void exportExcelCoverActm(Integer devId, Integer testRecordCount, Workbook wb) {
        Sheet sheet = wb.createSheet("模组配置");
        // 创建一个单元格样式，并设置字体大小为5
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

        Font font = wb.createFont();
        font.setFontHeightInPoints((short)20);
        cellStyle.setFont(font);

        //获取放电inf
        BattLithiumTestDataInf tinf=infService.getTinfExport(devId,testRecordCount);

        String[] titiles3=new String[]{"电池包序号:","模组编号:","工作模式:","电池类型:","电池串数:","电压阀值(v):"
                ,"工作电流(A):","开始测试时间:","结束测试时间:","停止原因:"};
        String[] titiles4=new String[]{String.valueOf(tinf.getBattIdx()+1),tinf.getBatteryName(), ActmStopReason.getTestType(tinf.getTestType()), ActmStopReason.getBattType(tinf.getBattType()),String.valueOf(tinf.getBatteryNumber()),String.format("%.1f",tinf.getVolThreshold())
                ,String.format("%.1f",tinf.getTestCurr()), ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), tinf.getStopTypeReason()};

        int rowNum=5;
        for (int i=0;i<titiles3.length;i++){
            Row row =  sheet.createRow(rowNum);  //创建行
            row.setHeightInPoints(20.0f);
            row.createCell(3).setCellValue(titiles3[i]);
            row.createCell(4).setCellValue(titiles4[i]);
            rowNum++;
        }
    }
}