package com.dhcc.finance.util;

import com.dhcc.common.util.SnowflakeIdWorker;
import com.dhcc.finance.main.model.CwGlReportInfo;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 功能描述：资产负债表和利润表读取和生成工具
 *
 * @author jiangyoushun
 * @date 2020/7/13  10:41
 * @修改日志：
 */
@Slf4j
public class FinanceExcelUtil {
    // 资产负债表的数据写入
    public static <T> File writeBalanceSheet(Map<String, List<CwGlReportInfo>> map, Sheet sheet, XSSFWorkbook wb, String searchType) throws IllegalArgumentException{
        // 单元格样式设置
        DataFormat df = sheet.getWorkbook().createDataFormat();
        CellStyle borderStyle = wb.createCellStyle();
        CellStyle style = wb.createCellStyle();
        // 金额格式
        borderStyle.setDataFormat(df.getFormat("#,##0.00"));
        // 右对齐
        borderStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 定义字体
        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short) 10);
        cellFont.setFontName("宋体");
        borderStyle.setFont(cellFont);
        style.setFont(cellFont);

        List<CwGlReportInfo> dataList = map.get("nowMonth");
        // 表格头部信息写入
        if (searchType.equals("1") && dataList.size() > 0) {
            Row rowA = sheet.getRow(2);
            Row rowB = sheet.getRow(3);
            Cell cellA = rowA.createCell(5);
            Cell cellB = rowB.createCell(0);
            Cell cellC = rowB.createCell(5);
            cellA.setCellStyle(style);
            cellB.setCellStyle(style);
            cellC.setCellStyle(style);
            cellB.setCellValue("信托项目名称:"+dataList.get(0).getName());
            String reportDate = dataList.get(0).getReportDate();
            String xmclrString = dataList.get(0).getXmclr();
            String xmzzrString = dataList.get(0).getXmzzr();
            if (StringUtils.isNotBlank(xmclrString) || StringUtils.isNotBlank(xmzzrString)) {
                Date xmclrDate = null;
                Date xmzzrDate = null;
                try {
                    xmclrDate = new SimpleDateFormat("yyyyMMdd").parse(xmclrString);
                    xmzzrDate = new SimpleDateFormat("yyyyMMdd").parse(xmzzrString);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                String xmclrC = new SimpleDateFormat("yyyy.MM.dd").format(xmclrDate);
                String xmzzrC = new SimpleDateFormat("yyyy.MM.dd").format(xmzzrDate);
                cellC.setCellValue("信托期限："+xmclrC+"--"+xmzzrC);
            }
            Date reportDate1 = null;
            try {
                reportDate1 = new SimpleDateFormat("yyyyMMdd").parse(reportDate);

            } catch (Exception e) {
                e.printStackTrace();
            }
            String reportDate2 = new SimpleDateFormat("yyyy年MM月dd日").format(reportDate1);
            cellA.setCellValue(reportDate2);
        }
        if ((searchType.equals("2") || searchType.equals("3")) && dataList.size() > 0) {
            Row rowA = sheet.getRow(2);
            Cell cellA = rowA.createCell(5);
            cellA.setCellStyle(style);
            String reportDate = dataList.get(0).getReportDate();
            Date reportDate1 = null;
            try {
                reportDate1 = new SimpleDateFormat("yyyyMMdd").parse(reportDate);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String reportDate2 = new SimpleDateFormat("yyyy年MM月dd日").format(reportDate1);
            cellA.setCellValue(reportDate2);
        }

        Cell cell = null;
        // 遍历map集合，将不同月份的数据分别写入指定的列
        Set<String> set = map.keySet();
        Iterator<String> it = set.iterator();
        while (it.hasNext()) {
            String key = it.next();
            // 设置偏移量，用于定位循环列
            int offSet = 0;
            if ("nowMonth".equals(key)) {
                offSet = 2;
            } else if ("lastMonth".equals(key)) {
                offSet = 3;
            } else {
                offSet = 4;
            }
            List<CwGlReportInfo> listValue = map.get(key);
            for (int i = 6; i < 25; i++) {
                // 获取模板文件左右两列的科目编码
                Row row = sheet.getRow(i);
                String codeLeft = getCellValue(row.getCell(1));
                String codeRight = getCellValue(row.getCell(6));
                CwGlReportInfo leftObj = null;
                CwGlReportInfo rightObj = null;
                if (codeLeft != null) {
                    // 用科目编码来定位对象
                    for (CwGlReportInfo info : listValue) {
                        if (codeLeft.equals(info.getItemCode())) {
                            leftObj = info;
                        }
                        if (codeRight.equals(info.getItemCode())) {
                            rightObj = info;
                        }
                    }
                }
                Row row1 = sheet.getRow(i);
                // 左边列
                if (leftObj != null) {
                    BigDecimal value = leftObj.getQMBalance();
                    cell = row1.createCell(offSet);
                    if(value != null) {
                        cell.setCellValue(value.toString());
                        cell.setCellStyle(borderStyle);
                    }
                }
                // 右边列
                if (rightObj != null) {
                    BigDecimal value = rightObj.getQMBalance();
                    cell = row1.createCell(offSet+5);
                    if(value != null) {
                        cell.setCellValue(value.toString());
                        cell.setCellStyle(borderStyle);
                    }
                }
            }
        }
        File file = buildExcelFile(".\\" + new SnowflakeIdWorker(0, 0).nextId() + ".xlsx", wb);
        return file;
    }

    // 利润表写入数据
    public static <T> File writeProfit(Map<String, List<CwGlReportInfo>> map, Sheet sheet, XSSFWorkbook wb, String searchType)throws IllegalArgumentException {
        // 是否锁定
        DataFormat df = sheet.getWorkbook().createDataFormat();
        // 单元格锁定样式设置
        CellStyle borderStyle = wb.createCellStyle();
        CellStyle style = wb.createCellStyle();
        // 金额格式
        borderStyle.setDataFormat(df.getFormat("#,##0.00"));
        // 右对齐
        borderStyle.setAlignment(HorizontalAlignment.RIGHT);

        // 定义字体
        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short) 10);
        cellFont.setFontName("宋体");
        borderStyle.setFont(cellFont);
        style.setFont(cellFont);

        List<CwGlReportInfo> dataList = map.get("nowMonth");
        // 表格头部信息写入
        if (searchType.equals("1") && dataList.size() > 0) {
            Row rowA = sheet.getRow(2);
            Row rowB = sheet.getRow(3);
            Cell cellA = rowA.createCell(5);
            Cell cellB = rowB.createCell(0);
            Cell cellC = rowB.createCell(5);
            cellA.setCellStyle(style);
            cellB.setCellStyle(style);
            cellC.setCellStyle(style);
            cellB.setCellValue("信托项目名称:"+dataList.get(0).getName());
            String reportDate = dataList.get(0).getReportDate();
            String xmclrString = dataList.get(0).getXmclr();
            String xmzzrString = dataList.get(0).getXmzzr();
            if (StringUtils.isNotBlank(xmclrString) || StringUtils.isNotBlank(xmzzrString)) {
                Date xmclrDate = null;
                Date xmzzrDate = null;
                try {
                    xmclrDate = new SimpleDateFormat("yyyyMMdd").parse(xmclrString);
                    xmzzrDate = new SimpleDateFormat("yyyyMMdd").parse(xmzzrString);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                String xmclrC = new SimpleDateFormat("yyyy.MM.dd").format(xmclrDate);
                String xmzzrC = new SimpleDateFormat("yyyy.MM.dd").format(xmzzrDate);
                cellC.setCellValue("信托期限："+xmclrC+"--"+xmzzrC);
            }
            Date reportDate1 = null;
            try {
                reportDate1 = new SimpleDateFormat("yyyyMMdd").parse(reportDate);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String reportDate2 = new SimpleDateFormat("yyyy年MM月dd日").format(reportDate1);
            cellA.setCellValue(reportDate2);
        }
        if ((searchType.equals("2") || searchType.equals("3")) && dataList.size() > 0) {
            Row rowA = sheet.getRow(2);
            Cell cellA = rowA.createCell(5);
            cellA.setCellStyle(style);
            String reportDate = dataList.get(0).getReportDate();
            Date reportDate1 = null;
            try {
                reportDate1 = new SimpleDateFormat("yyyyMMdd").parse(reportDate);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String reportDate2 = new SimpleDateFormat("yyyy年MM月dd日").format(reportDate1);
            cellA.setCellValue(reportDate2);
        }

        // 遍历map集合，将不同月份的数据分别写入指定的列
        Set<String> set = map.keySet();
        Iterator<String> it = set.iterator();
        while (it.hasNext()) {
            String key = it.next();
            // 设置偏移量，用于定位循环列
            int offSet = 0;
            Boolean flag = false;
            if ("nowMonth".equals(key)) {
                offSet = 5;
                flag = true;
            } else {
                offSet = 8;
            }
            List<CwGlReportInfo> listValue = map.get(key);
            for (int i = 5; i < 29; i++) {
                Row row = sheet.getRow(i);
                // 获取科目编码
                String itemCode = getCellValue(row.getCell(4));
                CwGlReportInfo obj = null;
                if (itemCode != null) {
                    // // 用科目编码来定位对象
                    for (CwGlReportInfo info : listValue) {
                        if (itemCode.equals(info.getItemCode())) {
                            obj = info;
                        }
                    }
                }
                Row row1 = sheet.getRow(i);
                if (obj != null) {
                    BigDecimal QMBalance = obj.getQMBalance();
                    Cell cellQM = row1.createCell(offSet);
                    if (QMBalance != null) {
                        cellQM.setCellValue(QMBalance.toString());
                        cellQM.setCellStyle(borderStyle);
                    }
                    // 本年累计数以本期数为标准
                    if (flag) {
                        BigDecimal NCBalance = obj.getNCBalance();
                        Cell cellNC = row1.createCell(offSet + 6);
                        if (NCBalance != null) {
                            cellNC.setCellValue(NCBalance.toString());
                            cellNC.setCellStyle(borderStyle);
                        }
                    }
                }
            }
        }
        File file = buildExcelFile(".\\" + new SnowflakeIdWorker(0, 0).nextId() + ".xlsx", wb);
        return file;
    }

    /**
     * 生成excel文件
     *
     * @param path 生成excel路径
     * @param wb
     */
    private static File buildExcelFile(String path, Workbook wb) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        try {
            wb.write(new FileOutputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return file;
    }

    /**
     * @description: 将单元格数据转换为字符串
     * @param cell
     * @return: java.lang.String
     * @author jiangyoushun
     * @date 2020/7/29 11:41
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }
    }

    // 读取资产负债表或利润表
    public static List<CwGlReportInfo> readExcel(File file, String reportType) {
        List<CwGlReportInfo> dataList = Lists.newArrayList();
        Workbook workbook = getWorkbook(file);
        if (workbook == null) {
            return null;
        }
        try {
            if ("01".equals(reportType)) {
                dataList = readBalanceSheet(workbook);
            } else {
                dataList = readProfit(workbook);
            }
        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
        return dataList;
    }
    public static Workbook getWorkbook(File file) {
        Workbook workbook = null;
        try {
            String fileName = file.getName();
            if ("".equals(fileName)) {
                fileName = file.getName();
            }
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                log.error("上传文件格式不正确");
            }

            FileInputStream fs = new FileInputStream(file);
            if (fileName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fs);
            }
            if (fileName.endsWith("xls")) {
                workbook = new HSSFWorkbook(fs);
            }
        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        }
        return workbook;
    }

    // 读取资产负债表数据
    public static List<CwGlReportInfo> readBalanceSheet(Workbook workbook) {
        List<CwGlReportInfo> dataList = Lists.newArrayList();
        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 6; i <= 24; i++) {
                Row row = sheet.getRow(i);
                // 忽略空白行
                if (row == null) {
                    continue;
                }
                try {
                    //读取左边列科目编号、名称、金额
                    String itemName = getCellValue(row.getCell(0));
                    if (StringUtils.isNotBlank(itemName)) {
                        String itemCode = getCellValue(row.getCell(1));
                        String balance = getCellValue(row.getCell(2));
                        if (StringUtils.isNotBlank(balance)) {
                            CwGlReportInfo objLeft = new CwGlReportInfo();
                            objLeft.setItemName(itemName);
                            objLeft.setItemCode(itemCode);
                            objLeft.setQMBalance(new BigDecimal(balance));
                            objLeft.setReportType("01");
                            dataList.add(objLeft);
                        }
                    }
                } catch (Exception e) {
                    log.error(String.format("parse row:%s exception!", i), e);
                }
                //右边列
                try {
                    //读取右边列科目编号、名称、金额
                    String itemName = getCellValue(row.getCell(5));
                    if (StringUtils.isNotBlank(itemName)) {
                        String itemCode = getCellValue(row.getCell(6));
                        String balance = getCellValue(row.getCell(7));
                        if (StringUtils.isNotBlank(balance)) {
                            CwGlReportInfo objRight = new CwGlReportInfo();
                            objRight.setItemName(itemName);
                            objRight.setItemCode(itemCode);
                            objRight.setQMBalance(new BigDecimal(balance));
                            objRight.setReportType("01");
                            dataList.add(objRight);
                        }
                    }
                } catch (Exception e) {
                    log.error(String.format("parse row:%s exception!", i), e);
                }
            }
        }
        return dataList;
    }

    // 读取利润表数据
    public static List<CwGlReportInfo> readProfit(Workbook workbook) {
        List<CwGlReportInfo> dataList = Lists.newArrayList();
        if (workbook != null) {
            // 默认读取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 5; i <= 29; i++) {
                Row row = sheet.getRow(i);
                // 忽略空白行
                if (row == null) {
                    continue;
                }
                try {
                    //读取科目编号、名称、金额
                    String itemName = getCellValue(row.getCell(0));
                    if (StringUtils.isNotBlank(itemName)) {
                        String itemCode = getCellValue(row.getCell(4));
                        String balance = getCellValue(row.getCell(5));
                        if (StringUtils.isNotBlank(balance)) {
                            CwGlReportInfo obj = new CwGlReportInfo();
                            obj.setItemName(itemName);
                            obj.setItemCode(itemCode);
                            obj.setQMBalance(new BigDecimal(balance));
                            obj.setReportType("02");
                            dataList.add(obj);
                        }
                    }
                } catch (Exception e) {
                    log.error(String.format("parse row:%s exception!", i), e);
                }
            }
        }
        return dataList;
    }
}