package com.mvc.controller.taizhang;

import com.mvc.model.*;
import com.mvc.netty.dealconstant.ResourcePath;
import com.mvc.netty.dealconstant.ResourceTag;
import com.mvc.service.TAffiliatedunitService;
import com.mvc.service.copy.TAireadService;
import com.mvc.service.taizhang.StationAccService;
import com.mvc.utils.DateUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.List;

@Api(tags = "统计信息下载接口")
@RequestMapping("/FileDownLoadAction")
@Controller
public class FileDownLoadAction {

    @Autowired
    private TAireadService tAireadService;

    @Resource
    private Environment env;

    @Autowired
    private TAffiliatedunitService tAffiliatedunitService;

    @Autowired
    private StationAccService stationAccService;

    @ApiOperation("根据用户单位名称导出月用水统计量")
    @RequestMapping(value = "/getTongji_excel/{unitCode}/{time}", method = RequestMethod.GET)
    @ResponseBody
    public void getTongji_excel(HttpServletResponse response, @PathVariable("unitCode") String unitCode, @PathVariable("time") String time) {
        try {
            TAffiliatedunitModel unitModel = tAffiliatedunitService.findTAffiliatedunit(unitCode);
            if (unitModel == null) {
                response.setStatus(5501);           //代表 导出单位错误
                return;
            }

            int month = Integer.valueOf(time.substring(4, 6));
            String templatePath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "tj_template" + File.separator + unitModel.getUnitName() + ".xlsx";
            File fileexcel = new File(templatePath);
            if (!fileexcel.exists()) {
                templatePath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "tj_template" + File.separator + "通用模板.xlsx";
                fileexcel = new File(templatePath);
                if (!fileexcel.exists()) {
                    response.setStatus(5502);       //代表 当前单位没有模板
                    return;
                } else {
                    getTongjiexcelByGlobalTemplate(response, unitModel, time, templatePath);
                    return;
                    //通用模板机制,优先使用本单位模板,没有则使用通用模板
                }
            }
            FileInputStream in = new FileInputStream(templatePath);
            //创建一个excel文件，名称为：
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow row0 = sheet.getRow(0);
            int colum = 0;
            for (int i = 0; i < 100; i++) {
                XSSFCell cell = row0.getCell(i);
                String cellValue = cell.getStringCellValue();
                if (cellValue != null && cellValue.indexOf("站点编号") >= 0) {
                    colum = i;
                    break;
                }
            }
            if (colum > 0) {
                Calendar calendar = Calendar.getInstance();
                calendar.set(Calendar.MONTH, month - 1);
                int actualMaximum = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                calendar.set(Calendar.DAY_OF_MONTH, actualMaximum);
                String date = DateUtil.formateDate(calendar.getTime(), "yyyy-MM-dd");
                XSSFCell cell_head = row0.getCell(colum + 1);
                cell_head.setCellValue(date + "累计用水量");
                for (int i = 1; i < lastRowNum; i++) {
                    XSSFRow currentrow = sheet.getRow(i);
                    XSSFCell cell = currentrow.getCell(colum);
                    XSSFCell cell1 = currentrow.getCell(colum + 1);
                    String code = cell.getStringCellValue();
                    if (StringUtils.isNotEmpty(code)) {
                        TAireadModel trairedModel = tAireadService.findDataByMonth(code, time, "1");
                        if (trairedModel != null && trairedModel.getResultValue() != null) {
                            cell1.setCellValue(trairedModel.getResultValue().toString());//设置读数信息
                        }
                        if (trairedModel != null && trairedModel.getSmallJpgUrl() != null) {
                            File file = new File(trairedModel.getSmallJpgUrl());
                            //File file = new File("E:/shangchuan/picture/rawdata/2022-09/202205188310_20220901091344992_M.jpg");   //测试
                            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, colum + 2, i, colum + 3, i + 1);
                                patriarch.createPicture(anchor, workbook.addPicture(picbyte, XSSFWorkbook.PICTURE_TYPE_JPEG));
                            }
                        }
                    }
                }
            }
            in.close();
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] bytes = os.toByteArray();

            String filename = URLEncoder.encode(unitModel.getUnitName() + month + "月用水量.xlsx", "UTF-8");
            response.setContentType("application/octet-stream");
            //response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(unitModel.getUnitName() + month + "月用水量.xlsx", "UTF-8"));
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(bytes);
            outputStream.close();
            os.close();
            workbook.close();
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
/*        BufferedImage bufferImg = null;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        String imageUrl = "https://www.xzeai.com:6090/image/rawdata/2022-09/112112001410_20220903080114077_M.jpg";

        //获取图片后缀
        String imageType = imageUrl.substring(imageUrl.length()-3);
        File imageFile = new File("./temp." + imageType);
        FileUtils.copyURLToFile(new URL(imageUrl), imageFile);
        bufferImg = ImageIO.read(imageFile);
        ImageIO.write(bufferImg, imageType, byteArrayOut);

        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        //XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        //anchor主要用于设置图片的属性
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 4, 4);

        XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, (short) 5, 5, (short) 6, 6);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor2, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
       workbook.write(fileOutputStream);
        workbook.close();
        System.out.println("wanb");*/
    }

    /**
     * 使用通用模板生成Excel
     *
     * @param response
     * @param unitModel
     * @param time
     * @param templatePath
     */
    private void getTongjiexcelByGlobalTemplate(HttpServletResponse response, TAffiliatedunitModel unitModel, String time, String templatePath) {
        try {
            FileInputStream in = new FileInputStream(templatePath);
            //创建一个excel文件，名称为：
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            int lastRowNum = sheet.getLastRowNum();
            int month = Integer.valueOf(time.substring(4, 6));
            List<YslModel> yslModelList = stationAccService.findYslByStationCodeAndTime(unitModel.getUnitCode(), time);
            short rowHeight = 900;
            for (int i = 0; i < yslModelList.size(); i++) {
                int num = i + 1;
                YslModel yslModel = yslModelList.get(i);
                XSSFRow currentrow = sheet.createRow(num);
                currentrow.setHeight(rowHeight);
                currentrow.createCell(0).setCellValue(num);                        //序号
                currentrow.createCell(1).setCellValue(yslModel.getUnitname());     //用水单位
                currentrow.createCell(2).setCellValue(yslModel.getStationName());  //站名
                currentrow.createCell(3).setCellValue(yslModel.getStationCode());  //站代码

                TAireadModel trairedModel = tAireadService.findDataByMonth(yslModel.getStationCode(), time, "1");
                if (trairedModel != null && trairedModel.getResultValue() != null) {
                    if (yslModel.getSampleTime() != null) {
                        String sampleTime = DateUtil.formateDate(trairedModel.getSampleTime(), "yyyy-MM-dd HH:mm");
                        currentrow.createCell(4).setCellValue(sampleTime);                 //采样时间
                    }
                    currentrow.createCell(5).setCellValue(trairedModel.getResultValue().toString());//设置读数信息
                    if (trairedModel != null && StringUtils.isNotEmpty(trairedModel.getSmallJpgUrl())) {
                        File file = new File(trairedModel.getSmallJpgUrl());
                        //File file = new File("E:/shangchuan/picture/rawdata/2022-09/202205188310_20220901091344992_M.jpg");   //测试
                        if (file.isFile() && file.exists()) {
                            currentrow.createCell(6);
                            currentrow.createCell(7);
                            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, 6, num, 7, num + 1);
                            patriarch.createPicture(anchor, workbook.addPicture(picbyte, XSSFWorkbook.PICTURE_TYPE_JPEG));
                        }
                    }
                }
            }

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

            String filename = URLEncoder.encode(unitModel.getUnitName() + month + "月用水量.xlsx", "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();
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @ApiOperation("微信小程序导出excel报表功能")
    @RequestMapping(value = "/getTongji_excel_weixin/{unitCode}/{time}", method = RequestMethod.GET)
    @ResponseBody
    public String getTongji_excel(@PathVariable("unitCode") String unitCode, @PathVariable("time") String time) {
        try {
            TAffiliatedunitModel unitModel = tAffiliatedunitService.findTAffiliatedunit(unitCode);
            if (unitModel == null) {
                //代表出单位错误
                return "";
            }
            int month = Integer.valueOf(time.substring(4, 6));
            String templatePath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "tj_template" + File.separator + unitModel.getUnitName() + ".xlsx";
            File fileexcel = new File(templatePath);
            if (!fileexcel.exists()) {
                templatePath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "tj_template" + File.separator + "通用模板.xlsx";
                fileexcel = new File(templatePath);
                if (!fileexcel.exists()) {
                    //代表当前单位没有模板
                    return "";
                } else {
                    String resPath = getTongjiexcelByGlobalTemplate_weixin(unitModel, time, templatePath);
                    return resPath;
                    //通用模板机制,优先使用本单位模板,没有则使用通用模板
                }
            }
            FileInputStream in = new FileInputStream(templatePath);
            //创建一个excel文件，名称为：
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow row0 = sheet.getRow(0);
            int colum = 0;
            for (int i = 0; i < 100; i++) {
                XSSFCell cell = row0.getCell(i);
                String cellValue = cell.getStringCellValue();
                if (cellValue != null && cellValue.indexOf("站点编号") >= 0) {
                    colum = i;
                    break;
                }
            }
            if (colum > 0) {
                Calendar calendar = Calendar.getInstance();
                calendar.set(Calendar.MONTH, month - 1);
                int actualMaximum = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                calendar.set(Calendar.DAY_OF_MONTH, actualMaximum);
                String date = DateUtil.formateDate(calendar.getTime(), "yyyy-MM-dd");
                XSSFCell cell_head = row0.getCell(colum + 1);
                cell_head.setCellValue(date + "累计用水量");
                for (int i = 1; i < lastRowNum; i++) {
                    XSSFRow currentrow = sheet.getRow(i);
                    XSSFCell cell = currentrow.getCell(colum);
                    XSSFCell cell1 = currentrow.getCell(colum + 1);
                    String code = cell.getStringCellValue();
                    if (StringUtils.isNotEmpty(code)) {
                        TAireadModel trairedModel = tAireadService.findDataByMonth(code, month, "1");
                        if (trairedModel != null && trairedModel.getResultValue() != null) {
                            cell1.setCellValue(trairedModel.getResultValue().toString());//设置读数信息
                        }
                        if (trairedModel != null && trairedModel.getSmallJpgUrl() != null) {
                            File file = new File(trairedModel.getSmallJpgUrl());
                            //File file = new File("E:/shangchuan/picture/rawdata/2022-09/202205188310_20220901091344992_M.jpg");   //测试
                            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, colum + 2, i, colum + 3, i + 1);
                                patriarch.createPicture(anchor, workbook.addPicture(picbyte, XSSFWorkbook.PICTURE_TYPE_JPEG));
                            }
                        }
                    }
                }
            }

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] bytes = os.toByteArray();
            in.close();
            os.close();
            workbook.close();
            String downloadpath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "wx_download" + File.separator + unitModel.getUnitName() + ".xlsx";
            File downfile = new File(downloadpath);
            downfile.createNewFile();
            FileOutputStream fileOutputStream = new FileOutputStream(downfile);
            fileOutputStream.write(bytes);
            fileOutputStream.close();
            String res = env.getProperty(ResourceTag.ResourceUrlTag.getTag()) + File.separator + ResourcePath.download.getPath() + "wx_download" + File.separator + unitModel.getUnitName() + ".xlsx";
            return res;
        } catch (IOException e) {
            e.printStackTrace();
            return "";
        }

    }

    /**
     * 使用通用模板生成Excel
     *
     * @param unitModel
     * @param time
     * @param templatePath
     * @return
     */
    private String getTongjiexcelByGlobalTemplate_weixin(TAffiliatedunitModel unitModel, String time, String templatePath) {
        try {
            FileInputStream in = new FileInputStream(templatePath);
            //创建一个excel文件，名称为：
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            int lastRowNum = sheet.getLastRowNum();
            int month = Integer.valueOf(time.substring(4, 6));
            List<YslModel> yslModelList = stationAccService.findYslByStationCodeAndTime(unitModel.getUnitCode(), time);
            short rowHeight = 900;
            for (int i = 0; i < yslModelList.size(); i++) {
                int num = i + 1;
                YslModel yslModel = yslModelList.get(i);
                XSSFRow currentrow = sheet.createRow(num);
                currentrow.setHeight(rowHeight);
                currentrow.createCell(0).setCellValue(num);                        //序号
                currentrow.createCell(1).setCellValue(yslModel.getUnitname());     //用水单位
                currentrow.createCell(2).setCellValue(yslModel.getStationName());  //站名
                currentrow.createCell(3).setCellValue(yslModel.getStationCode());  //站代码

                TAireadModel trairedModel = tAireadService.findDataByMonth(yslModel.getStationCode(), month, "1");
                if (trairedModel != null && trairedModel.getResultValue() != null) {
                    if (yslModel.getSampleTime() != null) {
                        String sampleTime = DateUtil.formateDate(trairedModel.getSampleTime(), "yyyy-MM-dd HH:mm");
                        currentrow.createCell(4).setCellValue(sampleTime);                 //采样时间
                    }
                    currentrow.createCell(5).setCellValue(trairedModel.getResultValue().toString());//设置读数信息
                    if (trairedModel != null && StringUtils.isNotEmpty(trairedModel.getSmallJpgUrl())) {
                        File file = new File(trairedModel.getSmallJpgUrl());
                        //File file = new File("E:/shangchuan/picture/rawdata/2022-09/202205188310_20220901091344992_M.jpg");   //测试
                        if (file.isFile() && file.exists()) {
                            currentrow.createCell(6);
                            currentrow.createCell(7);
                            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, 6, num, 7, num + 1);
                            patriarch.createPicture(anchor, workbook.addPicture(picbyte, XSSFWorkbook.PICTURE_TYPE_JPEG));
                        }
                    }
                }
            }


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

            in.close();
            os.close();
            workbook.close();

            String downloadpath = env.getProperty(ResourceTag.DownloadPathTag.getTag()) + "wx_download" + File.separator + unitModel.getUnitName() + ".xlsx";
            File downfile = new File(downloadpath);
            downfile.createNewFile();
            FileOutputStream fileOutputStream = new FileOutputStream(downfile);
            fileOutputStream.write(bytes);
            fileOutputStream.close();
            String res = env.getProperty(ResourceTag.ResourceUrlTag.getTag()) + File.separator + ResourcePath.download.getPath() + "wx_download" + File.separator + unitModel.getUnitName() + ".xlsx";
            return res;
        } catch (IOException e) {
            e.printStackTrace();
            return "";
        }
    }

}
