package com.whyc.service;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.whyc.dto.Real.ExportTinfDataDto;
import com.whyc.dto.Response;
import com.whyc.dto.Statistic.MonStic;
import com.whyc.dto.Statistic.SticMonRes;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_batt_testdata.BatttestdataId;
import com.whyc.pojo.db_batt_testdata.BatttestdataInf;
import com.whyc.pojo.db_user.User;
import com.whyc.util.ActionUtil;
import com.whyc.util.ServletUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class ExportService {

    @Autowired
    private BatttestdataInfService battTinfService;

    @Autowired
    private SubTablePageInfoService subTablePageInfoService;

    @Resource
    private CommonMapper commonMapper;


    //单节数量统计导出（1.2.14）
    public void exportBattTinfStatistic(HttpServletRequest req, HttpServletResponse resp, MonStic stic) throws ParseException {
        String topPic = stic.getTopPic();            //头部柱状图
        String goodVolPic = stic.getGoodVolPic();            //优秀电压图
        String goodResPic = stic.getGoodResPic();        //优秀内阻
        String badVolPic = stic.getBadVolPic();            //劣化电压图
        String badResPic = stic.getBadResPic();        //劣化内阻
        String damageVolPic = stic.getDamageVolPic();            //损坏电压图
        String damageResPic = stic.getDamageResPic();        //损坏内阻
        User uinf= ActionUtil.getUser();
        stic.setUid(uinf.getId());
        Map<String,Object> map= battTinfService.getMonStatistic(stic);
        List<byte[]> bytes = new ArrayList<byte[]>();
        //处理图片
        try {
            if (ServletUtils.isNotNull(topPic)) {
                String[] url = topPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(goodVolPic)) {
                String[] url = goodVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(goodResPic)) {
                String[] url = goodResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(badVolPic)) {
                String[] url = badVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(badResPic)) {
                String[] url = badResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(damageVolPic)) {
                String[] url = damageVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(damageResPic)) {
                String[] url = damageResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "单节数量统计-"+nowFormat;

        HSSFWorkbook wb = new HSSFWorkbook();
        //单体性能统计表
        createMonSicPic(wb, bytes);
        List<SticMonRes> goodList=(List<SticMonRes>) map.get("goodlist");
        //创建优秀单体数据表
        createMonSicTable(wb, goodList,"优秀单体数据表");
        List<SticMonRes> badList=(List<SticMonRes>) map.get("badlist");
        //创建劣化单体数据表
        createMonSicTable(wb, badList,"劣化单体数据表");
        List<SticMonRes> damageList=(List<SticMonRes>) map.get("damagelist");
        //创建损坏单体数据表
        createMonSicTable(wb, damageList,"损坏单体数据表");

        try {
            // 转码防止乱码
            resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode ( excelName+".xlsx", "utf-8"));
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建优秀/劣化/损坏单体数据表
    private void createMonSicTable(HSSFWorkbook wb, List<SticMonRes> list,String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(15);    //设置默认列宽，实际上回多出2个字符，不知道为什么
        sheet.setDefaultRowHeight((short) (1 * 256));//设置默认行高，表示2个字符的高度
        int rownum = 1;

        String[] arrTh = new String[]{
                "机房名称","设备名称","电池组名称","单体编号","浮充电压(V)","单体内阻(mΩ)"
        };
        sheet.createRow(rownum);
        for(int i = 0 ;i<arrTh.length;i++){
            sheet.getRow(rownum).createCell(i+1).setCellValue(arrTh[i]);
        }
        rownum++;
        if(list!=null&&list.size()>=0){
            for(SticMonRes res:list){
                sheet.createRow(rownum);
                sheet.getRow(rownum).createCell(1).setCellValue(res.getStationName());
                sheet.getRow(rownum).createCell(2).setCellValue(res.getDevName());
                sheet.getRow(rownum).createCell(3).setCellValue(res.getBattgroupName());
                sheet.getRow(rownum).createCell(4).setCellValue(res.getMonNum());
                sheet.getRow(rownum).createCell(5).setCellValue(res.getMonVol());
                sheet.getRow(rownum).createCell(6).setCellValue(res.getMonRes());
                rownum++;
            }
        }
    }

    //单体性能统计表
    private void createMonSicPic(HSSFWorkbook wb, List<byte[]> bytes) {

        HSSFSheet sheet = wb.createSheet("单体性能统计表");
        sheet.setDefaultColumnWidth(15);    //设置默认列宽，实际上回多出2个字符，不知道为什么
        sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高，表示2个字符的高度
        //字体格式-加粗
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBold(true);
        cellStyle.setFont(font);

        //图片元素
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        int rowNum = 1;
        int rowNumAdd=25;
        //插入图片
        int picNum = 0;
        String[] picName = new String[]{
                "单体性能统计图", "优秀单体电压图", "优秀单体内阻图", "劣化单体电压图", "劣化单体内阻图", "损坏单体电压图", "损坏单体内阻图"
        };
        for (int i = 0; i < picName.length; i++) {
            sheet.createRow(rowNum - 1);
            sheet.getRow(rowNum - 1).createCell(0).setCellValue(picName[i]);
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255, (short) 0, rowNum, (short) 10, rowNum + rowNumAdd);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
            picNum++;
            rowNum = rowNum+rowNumAdd+5;
        }
    }
    //历史测试数据导出
    public void exportTinfDataByTestRecordCount(HttpServletRequest req, HttpServletResponse resp, ExportTinfDataDto dto) throws IOException {
       Map<String,Object> map=new HashMap<>();
        //历史测试数据导出
        BatttestdataInf tinf = battTinfService.exportTinfDataByTestRecordCount(dto.getBattgroupId(), dto.getTestRecordCount());
        String tableName ="db_batt_testdata.tb_batttestdata_"+dto.getBattgroupId();
        //获取具体的放电数据
        List<BatttestdataId> dataList= subTablePageInfoService.exportTinfDataByTestRecordCount(dto.getBattgroupId(),dto.getTestRecordCount(),tableName);
        map.put("dataList",dataList);
        BASE64Decoder decoder = new BASE64Decoder();
        if(!dto.getPicGroupVol().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupVol().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picGroupVol",volImage);
        }
        if(!dto.getPicBattVol().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicBattVol().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picBattVol",volImage);
        }
        if(!dto.getPicGroupCurr().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupCurr().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);
            map.put("picGroupCurr",volImage);
        }
        //获取导出模板地址
        ClassPathResource classPathResource = new ClassPathResource("excel_templates/batt_mon_static_template.xlsx");
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
        try {
            String fileName = "历史测试数据表.xls";
            resp.setContentType("application/vnd.ms-excel");
            resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            resp.flushBuffer();
            wb.write(resp.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}