package cn.estsh.i3plus.core.apiservice.util;

import cn.estsh.i3plus.core.api.iservice.busi.ISysConfigService;
import cn.estsh.i3plus.core.api.iservice.busi.ISysDictionaryService;
import cn.estsh.i3plus.platform.common.tool.ReflexTool;
import cn.estsh.i3plus.platform.common.tool.StringTool;
import cn.estsh.i3plus.platform.common.tool.TimeTool;
import cn.estsh.i3plus.platform.common.util.PlatformConstWords;
import cn.estsh.i3plus.pojo.base.annotation.AnnoOutputColumn;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.ImppEnumUtil;
import cn.estsh.i3plus.pojo.platform.bean.SysDictionary;
import cn.estsh.i3plus.pojo.platform.bean.SysFile;
import cn.estsh.i3plus.pojo.platform.bean.SysMessage;
import cn.estsh.impp.framework.boot.exception.ImppExceptionBuilder;
import cn.estsh.impp.framework.boot.exception.ImppExceptionEnum;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/**
 * @Description : Excel导入导出工具类
 * @Reference :
 * @Author : yunhao
 * @CreateDate : 2018-12-05 16:12
 * @Modify:
 **/
@Component
public class ExcelUtil {
    public static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    private static ISysConfigService sysConfigService;

    @Autowired
    public void setSysConfigService(ISysConfigService sysConfigService) {
        ExcelUtil.sysConfigService = sysConfigService;
    }

    private static EntityManager entityManager;

    @Autowired
    public void setEntityManager(EntityManager entityManager) {
        ExcelUtil.entityManager = entityManager;
    }

    private static ISysDictionaryService sysDictionaryService;

    @Autowired
    public void setSysDictionaryService(ISysDictionaryService sysDictionaryService){
        ExcelUtil.sysDictionaryService = sysDictionaryService;
    }

    private static RabbitTemplate rabbitTemplate;

    @Autowired
    public void setRabbitTemplate(RabbitTemplate rabbitTemplate) {
        this.rabbitTemplate = rabbitTemplate;
    }

    /**
     * 导出
     *
     * @param data        导出的数据
     * @param exportClass 导出数据类型
     * @param exportCol   需要导出的列
     */
    public static File exportData(File excelFile,List data, Class exportClass, String[] exportCol) throws Exception {
        FileOutputStream fos = null;
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            fos = new FileOutputStream(excelFile);

            //建立新的sheet对象（excel的表单）
            HSSFSheet sheet = workbook.createSheet(exportClass.getSimpleName());

            // 创建表头
            HSSFRow tableHeader = sheet.createRow(0);

            // 类数据
            Field[] fields = new Field[exportCol.length];
            Field[] allFields = ReflexTool.getAllField(exportClass.getName());
            String colName;

            // 获取字段中文名 优先使用 OutputColumn.name
            for (int i = 0; i < exportCol.length; i++) {
                for (int j = 0 ; j < allFields.length ; j++){
                    if(exportCol[i].equals(allFields[j].getName())){
                        fields[i] = allFields[j];

                        colName = null;

                        if (fields[i].isAnnotationPresent(AnnoOutputColumn.class)) {
                            colName = fields[i].getAnnotation(AnnoOutputColumn.class).name();
                        }

                        if (fields[i].isAnnotationPresent(ApiParam.class) && StringUtils.isBlank(colName)) {
                            colName = fields[i].getAnnotation(ApiParam.class).value();
                        }
                        tableHeader.createCell(i, CellType.STRING).setCellValue(colName);
                    }
                }
            }

            //加载数据至excel对象
            HSSFRow hssfRow;
            Method method;
            AnnoOutputColumn outputColumn;
            Object cellValue;

            for (int i = 0; i < data.size(); i++) {
                hssfRow = sheet.createRow(i + 1);
                for (int j = 0; j < fields.length; j++) {
                    fields[j].setAccessible(true);
                    cellValue = fields[j].get(data.get(i));

                    // 判断是否存在引用关系
                    if (fields[j].isAnnotationPresent(AnnoOutputColumn.class)) {
                        outputColumn = fields[j].getAnnotation(AnnoOutputColumn.class);

                        // 判断是否为枚举字段
                        if (outputColumn.refClass().isEnum()) {
                            method = outputColumn.refClass().getDeclaredMethod(
                                    outputColumn.refForeignKey() + "Of" + StringTool.toUpperCaseFirstOne(outputColumn.value()),
                                    outputColumn.refClass().getDeclaredMethod("get"
                                            + StringTool.toUpperCaseFirstOne(outputColumn.refForeignKey())).getReturnType());
                            cellValue = method.invoke(data.get(i), cellValue);
                        }else if(outputColumn.refClass().equals(SysDictionary.class) && cellValue != null){
                            cellValue = sysDictionaryService.getSysDictionaryByParentCodeAndValue(outputColumn.refForeignKey(), String.valueOf(cellValue)).getName();
                        } else if(!outputColumn.refClass().equals(Object.class) && !outputColumn.refClass().equals(SysDictionary.class)){
                            cellValue = selectByProperty(outputColumn.refClass(), outputColumn.value(), outputColumn.refForeignKey(), cellValue);
                        }
                    }

                    // excel 文本框最大长度
                    if(String.valueOf(cellValue).length() > 30000){
                        hssfRow.createCell(j, CellType.STRING).setCellValue(String.valueOf(cellValue).substring(0,30000));
                    }else{
                        hssfRow.createCell(j, CellType.STRING).setCellValue(String.valueOf(cellValue));
                    }

                }
            }

            workbook.write(fos);
            fos.flush();
            return excelFile;
        } finally {
            try {
                workbook.close();
                fos.close();
            } catch (IOException e) {
                LOGGER.error("IOException!", e);
            }
        }
    }

    /**
     * 导入
     *
     * @param fileName    文件名
     * @param inputStream 输入流
     * @param importClass 导入类型
     * @return 导入数据集合
     */
    public static <T> List<T> importData(String fileName, InputStream inputStream, Class importClass) throws Exception {
        List dataList = new ArrayList<T>();
        String fileType = StringTool.getStringFileSuffix(fileName, false);

        try {
            //根据文件类型及文件输入流新建工作簿对象
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.CORE.getCode())
                        .setErrorCode(ImppExceptionEnum.IO_EXCEPTION_FILE.getCode())
                        .setErrorDetail("Excel 您导入的excel格式不正确")
                        .setErrorSolution("请检查导入文件格式")
                        .build();
            }

            // 校验sheet是否超过最大行数
            Sheet sheet = wb.getSheet(importClass.getSimpleName());
            int maxRow = Integer.parseInt(sysConfigService.getSysConfigByCode(PlatformConstWords.EXCEL_IMPORT_MAX_ROW).getConfigValue());
            if (sheet != null && sheet.getLastRowNum() > maxRow) {
                throw ImppExceptionBuilder.newInstance()
                        .setSystemID(CommonEnumUtil.SOFT_TYPE.CORE.getCode())
                        .setErrorCode(ImppExceptionEnum.VARIFY_EXCEPTION.getCode())
                        .setErrorDetail("Excel 数据超过最大行数")
                        .setErrorSolution("请检查是否有空行,或分批导入")
                        .build();
            }

            // excel列名与字段名映射
            Map<String, Field> colName = new HashMap<>();
            for (Field field : ReflexTool.getAllField(importClass.getName())) {
                if (field.isAnnotationPresent(AnnoOutputColumn.class)
                        && !StringUtils.isBlank(field.getAnnotation(AnnoOutputColumn.class).name())) {
                    colName.put(field.getAnnotation(AnnoOutputColumn.class).name(), field);
                } else if (field.isAnnotationPresent(ApiParam.class)) {
                    colName.put(field.getAnnotation(ApiParam.class).value(), field);
                }
            }

            // 创建表头
            Row tableHeader = sheet.getRow(0);
            Field[] fields = new Field[tableHeader.getLastCellNum()];

            for (int i = 0; i < tableHeader.getLastCellNum(); i++) {
                fields[i] = colName.get(tableHeader.getCell(i).getStringCellValue());
            }

            Row row;
            Object obj;
            Object cellValue = null;
            AnnoOutputColumn inputColumn;
            Method method;

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                obj = importClass.newInstance();
                for (int j = 0; j < fields.length; j++) {
                    // 判断是否存在引用关系
                    if (fields[j].isAnnotationPresent(AnnoOutputColumn.class)) {
                        inputColumn = fields[j].getAnnotation(AnnoOutputColumn.class);
                        row.getCell(j).setCellType(CellType.STRING);
                        cellValue = row.getCell(j).getStringCellValue();

                        // 判断是否为枚举字段
                        if (inputColumn.refClass().isEnum()) {
                            method = inputColumn.refClass().getDeclaredMethod(
                                    inputColumn.value() + "Of" + StringTool.toUpperCaseFirstOne(inputColumn.refForeignKey()),
                                    inputColumn.refClass().getDeclaredMethod("get"
                                            + StringTool.toUpperCaseFirstOne(inputColumn.value())).getReturnType());
                            cellValue = method.invoke(null,cellValue);
                        }else if(inputColumn.refClass().equals(SysDictionary.class) && cellValue != null){
                            cellValue = sysDictionaryService.getSysDictionaryByParentCodeAndName(inputColumn.refForeignKey(), String.valueOf(cellValue)).getName();
                        } else if(!inputColumn.refClass().equals(Object.class) && !inputColumn.refClass().equals(SysDictionary.class)){
                            cellValue = selectByProperty(inputColumn.refClass(), inputColumn.refForeignKey(), inputColumn.value(), cellValue);
                        }
                    }else{
                        cellValue = getExcelCell(row.getCell(j),fields[j].getType());
                    }

                    fields[j].setAccessible(true);
                    fields[j].set(obj,cellValue);
                }
                dataList.add(obj);
            }

        } finally {
            inputStream.close();
        }
        return dataList;
    }

    /**
     * 导入模板
     *
     * @param exportClass
     */
    public static byte[] importTemplate(Class exportClass) {
        File excelFile = null;
        FileOutputStream fos = null;
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook workbook = new HSSFWorkbook();

        try {
            //创建临时文件
            excelFile = File.createTempFile(exportClass.getSimpleName() + new Date().getTime(), "Tp.xls");
            LOGGER.info("临时文件所在的本地路径：" + excelFile.getCanonicalPath());
            fos = new FileOutputStream(excelFile);

            //建立新的sheet对象（excel的表单）
            HSSFSheet sheet = workbook.createSheet(exportClass.getSimpleName());

            // 创建表头
            HSSFRow tableHeader = sheet.createRow(0);

            // 类数据
            Field[] fields = ReflexTool.getAllField(exportClass.getName());
            int col = 0;

            for (int i = 0; i < fields.length; i++) {
                if (fields[i].isAnnotationPresent(AnnoOutputColumn.class)) {
                    // 是否隐藏列
                    if(!fields[i].getAnnotation(AnnoOutputColumn.class).hidden()){
                        if (fields[i].isAnnotationPresent(AnnoOutputColumn.class)) {
                            tableHeader.createCell(col, CellType.STRING).setCellValue(fields[i].getAnnotation(AnnoOutputColumn.class).name());
                        }

                        // 优先使用 AnnoOutputColumn.name()
                        if (fields[i].isAnnotationPresent(ApiParam.class) && StringUtils.isBlank(fields[i].getAnnotation(AnnoOutputColumn.class).name())) {
                            tableHeader.createCell(col, CellType.STRING).setCellValue(fields[i].getAnnotation(ApiParam.class).value());
                        }
                        col++;
                    }
                } else {
                    if (fields[i].isAnnotationPresent(ApiParam.class)) {
                        tableHeader.createCell(col, CellType.STRING).setCellValue(fields[i].getAnnotation(ApiParam.class).value());
                        col++;
                    }
                }
            }

            workbook.write(fos);
            return FileUtil.file2Byte(excelFile.getPath());
        } catch (IOException e) {
            throw ImppExceptionBuilder.newInstance()
                    .setSystemID(CommonEnumUtil.SOFT_TYPE.CORE.getCode())
                    .setErrorCode(ImppExceptionEnum.IO_EXCEPTION.getCode())
                    .setErrorDetail("Excel导出错误")
                    .build();
        } finally {
            try {
                workbook.close();
                fos.close();
                excelFile.deleteOnExit();
            } catch (IOException e) {
                LOGGER.error("IOException!", e);
            }
        }
    }

    /**
     * 获取实体列集合
     * @param pojoClass
     * @return
     */
    public static Map getColName(Class pojoClass) {
        Map colName = new LinkedHashMap();
        Field[] fields = ReflexTool.getAllField(pojoClass.getName());

        for (int i = 0; i < fields.length; i++) {
            if (fields[i].isAnnotationPresent(ApiParam.class)) {
                colName.put(fields[i].getName(),fields[i].getAnnotation(ApiParam.class).value());
            }

            if (fields[i].isAnnotationPresent(AnnoOutputColumn.class)) {
                // 判断是否隐藏
                if(fields[i].getAnnotation(AnnoOutputColumn.class).hidden()){
                    colName.remove(fields[i].getName());
                }
                // 如果AnnoOutputColumn存在name属性则优先使用
                if(!StringUtils.isBlank(fields[i].getAnnotation(AnnoOutputColumn.class).name())){
                    colName.put(fields[i].getName(),fields[i].getAnnotation(AnnoOutputColumn.class).name());
                }
            }
        }
        return colName;
    }

    /**
     *  查询外键信息
     * @param persistentClass
     * @param colName
     * @param propertyName
     * @param value
     * @return
     */
    public static Object selectByProperty(Class persistentClass, String colName, String propertyName, Object value) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String queryString = "select model." + colName + " from " + persistentClass.getSimpleName()
                + " as model where model." + propertyName + "= :" + propertyName;
        return entityManager.createQuery(queryString).setParameter(propertyName, value).getSingleResult();
    }

    /**
     * 获取excel单元格值
     * @param cell
     * @param cellClass
     * @return
     */
    private static Object getExcelCell(Cell cell, Class cellClass) {
        if (cell != null) {
            cell.setCellType(CellType.STRING);

            if ("".equals(cell.getStringCellValue()) || "null".equals(cell.getStringCellValue())) {
                return null;
            } else if (cellClass == String.class) {
                return cell.getStringCellValue();
            } else if (cellClass == Integer.class) {
                return Integer.parseInt(cell.getStringCellValue());
            } else if (cellClass == Long.class) {
                return Long.parseLong(cell.getStringCellValue());
            } else if (cellClass == Double.class) {
                return Long.parseLong(cell.getStringCellValue());
            }
        }
        return null;
    }

    /**
     * 发送包含文件列表的站内信
     * @param fileList
     * @param userId
     */
    public static void sendStationLetter(List<SysFile> fileList, Long userId){
        StringBuffer letter = new StringBuffer();
        letter.append("导出文件列表：");
        for (SysFile sysFile:fileList) {
            letter.append("<a href='/impp/operate/sys-file/download/"+sysFile.getId() + "'>");
            letter.append(sysFile.getFileOriginalName());
            letter.append("</a></br>");
        }

        // 消息提示
        SysMessage sysMessage = new SysMessage();
        sysMessage.setMessageSenderNameRdd(CommonEnumUtil.SOFT_TYPE.CORE.getDescription());
        sysMessage.setMessageTitle("数据导出");
        sysMessage.setMessageContent(letter.toString());
        sysMessage.setMessageType(ImppEnumUtil.MESSAGE_TYPE.LETTER.getValue());
        sysMessage.setMessageContentType(ImppEnumUtil.MESSAGE_TYPE_CONTENT.HTML.getValue());
        sysMessage.setMessageSendTime(TimeTool.getNowTime(true));
        sysMessage.setIsSystem(CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
        sysMessage.setMessageReceiversId(String.valueOf(userId));

        rabbitTemplate.convertAndSend(PlatformConstWords.IMPP_MESSAGE_LETTER_QUEUE,sysMessage);
    }

}
