package com.unis.base.server.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.unis.base.common.vo.DKdxxsjyVO;
import com.unis.base.common.vo.QyxypjDcVO;
import com.unis.base.common.vo.QyxypjDr;
import com.unis.base.server.config.DataListener;
import com.unis.base.server.dao.QyxypjDao;
import com.unis.base.server.domain.CompanyDomain;
import com.unis.base.server.domain.DKdxxsjyDomain;
import com.unis.base.server.entity.DKdxxsjyDO;
import com.unis.base.server.entity.QyxypjDcDO;
import com.unis.base.server.service.DKdxxsjyService;
import com.unis.base.server.service.QyxypjService;
import com.unis.common.utils.JsonUtils;
import com.unis.common.utils.ResponseWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Slf4j
@Transactional(rollbackFor = Exception.class)
public class QyxypjServiceImpl implements QyxypjService {

    @Resource
    private QyxypjDao qyxypjDao;

    @Override
    public ResponseWrapper importQyxypjDr(MultipartFile file) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            List<QyxypjDr> dataList = new ArrayList<>();
            ExcelReaderBuilder readBuilder = EasyExcel.read(file.getInputStream(), QyxypjDr.class, new DataListener(dataList));
            // 设置要读取的 sheet，以及从第几行开始读取（这里设置为第2行，即索引为1）
            // 读取 Excel
            readBuilder.sheet(0).headRowNumber(1).doRead();
//            EasyExcel.read(file.getInputStream(), TyJyccKhxx.class, new DataListener(dataList)).sheet().doRead();
            if (dataList.size() == 0) {
                return ResponseWrapper.error("导入数据为空");
            } else {
                // 先查询QyxypjDcDO表中是否有这些数据当中的评价年度数据的数据,如果有则删除
                String pjnd = dataList.get(0).getPjnd();
                QyxypjDcDO qyxypjDcDO1 = new QyxypjDcDO();
                qyxypjDcDO1.setPjnd(pjnd);
                qyxypjDao.delete(qyxypjDcDO1);
                for (QyxypjDr qyxypjDr : dataList) {
//                    // 将QyxypjDr转换为QyxypjDcDO
                    QyxypjDcDO qyxypjDcDO = new QyxypjDcDO();
                    BeanUtils.copyProperties(qyxypjDr, qyxypjDcDO);
                    qyxypjDcDO.setId(UUID.randomUUID().toString().replace("-", ""));
                    qyxypjDcDO.setZtmc(qyxypjDr.getQymc());
                    qyxypjDcDO.setJgjg("天津市" + qyxypjDr.getSjdw().replace("粮食行业行政管理部门", "发展和改革委员会"));
                    qyxypjDcDO.setJgjgtyshxydm(getFgwtyshxydm(qyxypjDcDO.getJgjg()));
                    //信用分数对应分数，换算公式为：先将分数转成bigdecimal格式，然后“分数/120 X 1000”，最终四舍五入保留整数
                    if (StringUtils.isNotBlank(qyxypjDr.getFs())) {
                        qyxypjDcDO.setXydf(String.valueOf(Math.round(Double.parseDouble(qyxypjDr.getFs()) / 120 * 1000)));
                    }
                    // 按照评价结果粮食企业信用等级从高到低依次分为五个等级，950 分及以上为A级(优秀)、900 分至949分为B级(良好)、750 分至899 分为 C级(中等)、600 分至 749分为D级(较差)、600分以为E级(差)
                    if (StringUtils.isNotBlank(qyxypjDcDO.getXydf())) {
                        if (Integer.parseInt(qyxypjDcDO.getXydf()) >= 950) {
                            qyxypjDcDO.setPjjg("A");
                        } else if (Integer.parseInt(qyxypjDcDO.getXydf()) >= 900 && Integer.parseInt(qyxypjDcDO.getXydf()) < 950) {
                            qyxypjDcDO.setPjjg("B");
                        } else if (Integer.parseInt(qyxypjDcDO.getXydf()) >= 750 && Integer.parseInt(qyxypjDcDO.getXydf()) < 900) {
                            qyxypjDcDO.setPjjg("C");
                        } else if (Integer.parseInt(qyxypjDcDO.getXydf()) >= 600 && Integer.parseInt(qyxypjDcDO.getXydf()) < 750) {
                            qyxypjDcDO.setPjjg("D");
                        } else {
                            qyxypjDcDO.setPjjg("E");
                        }
                    } else {
                        qyxypjDcDO.setPjjg("E");
                    }
                    qyxypjDcDO.setPjjgtyshxydm("11120000MB18850906");
                    qyxypjDcDO.setPjjgqc("天津市粮食和物资储备局");
                    qyxypjDcDO.setPjmc("粮食行业信用评价");
                    qyxypjDcDO.setPjnd(qyxypjDr.getPjnd());
                    qyxypjDcDO.setPjrq(sdf.format(new Date()));
                    qyxypjDcDO.setFbsj(sdf.format(new Date()));
                    // 评价结果有效期默认一年,也就是pjrq+1年，比如2023年11月2日评价，那么有效期为2024年11月1日
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(sdf.parse(qyxypjDcDO.getPjrq()));
                    calendar.add(Calendar.YEAR, 1);
                    calendar.add(Calendar.DAY_OF_MONTH, -1);
                    qyxypjDcDO.setPjyxq(sdf.format(calendar.getTime()));
                    qyxypjDcDO.setZt("待提交");
                    // 新增
                    qyxypjDao.insert(qyxypjDcDO);
                }
                return ResponseWrapper.success("导入成功");
//                return ResponseWrapper.success(dataList);
            }
        } catch (Exception e) {
            log.error("企业信用评价模版导入异常", e);
            return ResponseWrapper.error("企业信用评价模版导入异常");
        }
    }

    @Override
    public PageInfo<QyxypjDcDO> queryPageList(Integer pageNum, Integer pageSize, QyxypjDcVO qyxypjDcVO) {
        PageHelper.startPage(pageNum, pageSize);
        List<QyxypjDcDO> qyxypjDcDOList = qyxypjDao.queryPageList(qyxypjDcVO);
        PageInfo<QyxypjDcDO> pageInfo = new PageInfo<>(qyxypjDcDOList);
        return pageInfo;
    }

    @Override
    public void update(QyxypjDcDO qyxypjDcDO) {
        qyxypjDao.updateByPrimaryKeySelective(qyxypjDcDO);
    }

    @Override
    public List<QyxypjDcDO> queryAllList(QyxypjDcVO qyxypjDcVO) {
        return qyxypjDao.queryPageList(qyxypjDcVO);
    }

    @Override
    public ResponseWrapper exportQyxypj(HttpServletResponse response, QyxypjDcVO qyxypjDcVO) {
        try {
            List<QyxypjDcDO> qyxypjDcDOS = qyxypjDao.queryPageList(qyxypjDcVO);

            // 设置响应类型和编码
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String pjnd = qyxypjDcVO.getPjnd();
            // 设置响应头，防止中文乱码
            String fileName = "";
            if (StringUtils.isNotBlank(pjnd)) {
                fileName = pjnd + "年度粮食行业信用评价表";
            } else {
                fileName = "粮食行业信用评价表";
            }
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            } catch (Exception e) {
                e.printStackTrace();
            }
            Workbook workbook = new XSSFWorkbook();
            // 创建单个工作表并填充数据
            String sheetName = "";
            if (StringUtils.isNotBlank(pjnd)) {
                sheetName = pjnd + "年度粮食行业信用评价表";
            } else {
                sheetName = "粮食行业信用评价表";
            }
            Sheet sheet = workbook.createSheet(sheetName);

            // 创建第一行并设置标题
            Row firstRow = sheet.createRow(0);
            Cell firstCell = firstRow.createCell(0);
            // 设置单元格字体宋体加粗
            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setBold(true);
            font.setFontHeightInPoints((short) 21);
            CellStyle style = workbook.createCellStyle();
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setWrapText(true);
            style.setBorderBottom(BorderStyle.THIN); // 下边框
            style.setBorderLeft(BorderStyle.THIN); // 左边框
            style.setBorderTop(BorderStyle.THIN); // 上边框
            style.setBorderRight(BorderStyle.THIN); // 右边框
            firstCell.setCellStyle(style); // 设置样式
            if (StringUtils.isNotBlank(pjnd)) {
                firstCell.setCellValue(pjnd + "年度粮食行业信用评价表");
            } else {
                firstCell.setCellValue("粮食行业信用评价表");
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12)); // 合并单元格
            fillQyxypjSheet(workbook, sheet, qyxypjDcDOS);

            // 设置自适应列宽
            for (int i = 0; i < 15; i++) {
                sheet.autoSizeColumn(i);
            }

            // 将工作簿写入输出流
            workbook.write(response.getOutputStream());
            workbook.close();
            return ResponseWrapper.success("导出成功");
        } catch (Exception e) {
            log.error("企业信用评价导出异常", e);
            return ResponseWrapper.error("企业信用评价导出异常");
        }
    }

    private void fillQyxypjSheet(Workbook workbook, Sheet sheet, List<QyxypjDcDO> qyxypjDcDOS) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);

        // 创建标题行,主体名称	统一社会信用代码	监管机构	监管机构统一社会信用代码	评价名称	信用得分	评价结果（等级）	评价日期	评价年度	评价有效期	发布时间	评价机关全称	评价机关统一社会信用代码
        List<String> titleList = Arrays.asList("主体名称", "统一社会信用代码", "监管机构", "监管机构统一社会信用代码", "评价名称", "信用得分", "评价结果（等级）", "评价日期", "评价年度", "评价有效期", "发布时间", "评价机关全称", "评价机关统一社会信用代码");
        Row firstTitleRow = sheet.createRow(1);
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setWrapText(true);
        headerStyle.setFont(font);
        headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        headerStyle.setBorderTop(BorderStyle.THIN); // 上边框
        headerStyle.setBorderRight(BorderStyle.THIN); // 右边框

        for (int i = 0; i < titleList.size(); i++) {
            Cell cell = firstTitleRow.createCell(i);
            cell.setCellValue(titleList.get(i));
            cell.setCellStyle(headerStyle);
        }

        // 创建数据行
        int rowIndex = 2;
        Font font1 = workbook.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font1);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);

        for (QyxypjDcDO qyxypjDcDO : qyxypjDcDOS) {
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getZtmc()) ? qyxypjDcDO.getZtmc() : "");
            cell0.setCellStyle(cellStyle);
            Cell cell1 = row.createCell(1);
            cell1.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getTyshxydm()) ? qyxypjDcDO.getTyshxydm() : "");
            cell1.setCellStyle(cellStyle);
            Cell cell2 = row.createCell(2);
            cell2.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getJgjg()) ? qyxypjDcDO.getJgjg() : "");
            cell2.setCellStyle(cellStyle);
            Cell cell3 = row.createCell(3);
            cell3.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getJgjgtyshxydm()) ? qyxypjDcDO.getJgjgtyshxydm() : "");
            cell3.setCellStyle(cellStyle);
            Cell cell4 = row.createCell(4);
            cell4.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjmc()) ? qyxypjDcDO.getPjmc() : "");
            cell4.setCellStyle(cellStyle);
            Cell cell5 = row.createCell(5);
            cell5.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getXydf()) ? qyxypjDcDO.getXydf() : "");
            cell5.setCellStyle(cellStyle);
            Cell cell6 = row.createCell(6);
            cell6.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjjg()) ? qyxypjDcDO.getPjjg() : "");
            cell6.setCellStyle(cellStyle);
            Cell cell7 = row.createCell(7);
            cell7.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjrq()) ? qyxypjDcDO.getPjrq() : "");
            cell7.setCellStyle(cellStyle);
            Cell cell8 = row.createCell(8);
            cell8.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjnd()) ? qyxypjDcDO.getPjnd() : "");
            cell8.setCellStyle(cellStyle);
            Cell cell9 = row.createCell(9);
            cell9.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjyxq()) ? qyxypjDcDO.getPjyxq() : "");
            cell9.setCellStyle(cellStyle);
            Cell cell10 = row.createCell(10);
            cell10.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getFbsj()) ? qyxypjDcDO.getFbsj() : "");
            cell10.setCellStyle(cellStyle);
            Cell cell11 = row.createCell(11);
            cell11.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjjgqc()) ? qyxypjDcDO.getPjjgqc() : "");
            cell11.setCellStyle(cellStyle);
            Cell cell12 = row.createCell(12);
            cell12.setCellValue(StringUtils.isNotBlank(qyxypjDcDO.getPjjgtyshxydm()) ? qyxypjDcDO.getPjjgtyshxydm() : "");
            cell12.setCellStyle(cellStyle);
            rowIndex++;
        }
        // 自适应列宽
        for (int i = 0; i < titleList.size(); i++) {
            sheet.autoSizeColumn(i);
        }

    }

    @Override
    public String getNewPjnd() {
        String newPjnd = qyxypjDao.getNewPjnd();
        return newPjnd;
    }

    @Override
    public void updateBatch(String ids, QyxypjDcDO qyxypjDcDO) {
        // 选择批量编辑
        if (StringUtils.isNotBlank(ids)){
            String[] split = ids.split(",");
            for (String s : split) {
                QyxypjDcDO qyxypjDcDO1 = new QyxypjDcDO();
                BeanUtils.copyProperties(qyxypjDcDO, qyxypjDcDO1);
                qyxypjDcDO1.setId(s);
                qyxypjDao.updateByPrimaryKeySelective(qyxypjDcDO1);
            }
        } else {
            // 选择批量提交
            QyxypjDcDO qyxypjDcDO1 = new QyxypjDcDO();
            BeanUtils.copyProperties(qyxypjDcDO, qyxypjDcDO1);
            qyxypjDao.updateBatch(qyxypjDcDO1);
        }
    }

    @Override
    public void updateBatchTj(String ids) {
        if (StringUtils.isNotBlank(ids)){
            String[] split = ids.split(",");
            for (String s : split) {
                QyxypjDcDO qyxypjDcDO = new QyxypjDcDO();
                qyxypjDcDO.setId(s);
                qyxypjDcDO.setZt("已提交");
                qyxypjDao.updateByPrimaryKeySelective(qyxypjDcDO);
            }
        } else {
            // 将所有待提交的数据都提交
            qyxypjDao.updateBatchTj();
        }
    }

    @Override
    public void delete(String id) {
        qyxypjDao.deleteByPrimaryKey(id);
    }

    public String getFgwtyshxydm(String fgwmc) {
        String tyshxydm;
        switch (fgwmc) {
            case "天津市东丽区发展和改革委员会":
                tyshxydm = "11120110000176778B";
                break;
            case "天津市北辰区发展和改革委员会":
                tyshxydm = "11120113000188787T";
                break;
            case "天津市南开区发展和改革委员会":
                tyshxydm = "1112010400015283XU";
                break;
            case "天津市和平区发展和改革委员会":
                tyshxydm = "11120101000140820F";
                break;
            case "天津市宁河区发展和改革委员会":
                tyshxydm = "111202210001927781";
                break;
            case "天津市宝坻区发展和改革委员会":
                tyshxydm = "11120224000204804R";
                break;
            case "天津市武清区发展和改革委员会":
                tyshxydm = "1112022220001967955";
                break;
            case "天津市河东区发展和改革委员会":
                tyshxydm = "11120102000144848M";
                break;
            case "天津市河北区发展和改革委员会":
                tyshxydm = "11120105000156830D";
                break;
            case "天津市河西区发展和改革委员会":
                tyshxydm = "11120103000148854Y";
                break;
            case "天津市津南区发展和改革委员会":
                tyshxydm = "111201120001848585";
                break;
            case "天津市滨海新区发展和改革委员会":
                tyshxydm = "11120116550355797J";
                break;
            case "天津市粮食和物资储备局":
                tyshxydm = "11120000MB18850906";
                break;
            case "天津市红桥区发展和改革委员会":
                tyshxydm = "111201060001608482";
                break;
            case "天津市蓟州区发展和改革委员会":
                tyshxydm = "111202250002088058";
                break;
            case "天津市西青区发展和改革委员会":
                tyshxydm = "11120111000180793Q";
                break;
            case "天津市静海区发展和改革委员会":
                tyshxydm = "11120223000200846Q";
                break;
            default:
                tyshxydm = ""; // 如果没有匹配的委员会名称，返回空字符串
                break;
        }
        return tyshxydm;
    }
}
