package org.jeecg.modules.excel;

import com.google.common.base.Joiner;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.util.DateUtils;
import org.jeecg.modules.feign.SystemClient;
import org.jeecg.modules.feign.model.SysUser;
import org.jeecg.modules.project.entity.ProProject;
import org.jeecg.modules.project.entity.ProProjectLink;
import org.jeecg.modules.project.entity.ProSummary;
import org.jeecg.modules.project.vo.ProjectVo;
import org.jxls.command.CellDataUpdater;
import org.jxls.common.CellData;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.transform.poi.PoiCellData;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.transform.poi.PoiUtil;
import org.jxls.util.JxlsHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;


import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Component
public class ExcelUtils {
    @Autowired
    private SystemClient systemClient;
    @Value(value = "${jeecg.path.upload}")
    private String uploadpath;

    //读取的excel 转为项目bean
    public ProProject CoverProProject(List<Map<Integer, String>> listMap) {
        Map<String, String> usersMap = new HashMap<>();
        ProProject project = new ProProject();
        ProSummary summary = new ProSummary();

        Map<Integer, String> map0 = listMap.get(0);
        project.setXmmc(map0.get(2));//项目名称

        String xmfzr = map0.get(4);

        parseUsers(xmfzr, project, usersMap, null, 1);


        project.setBgrq(getDate(map0.get(6)));// 报告日期
        project.setXmbh(map0.get(8));//项目编号

        Map<Integer, String> map1 = listMap.get(1);
        project.setXmbj(map1.get(2));//项目背景

        Map<Integer, String> map2 = listMap.get(2);
        project.setXmmb(map2.get(2));//项目目标

        Map<Integer, String> map3 = listMap.get(3);
        summary.setJqmb(map3.get(2));//近期目标

        Map<Integer, String> map4 = listMap.get(4);

        project.setKsrq(getDate(map4.get(2)));  // 开始日期


        Map<Integer, String> map5 = listMap.get(5);
        project.setJsrq(getDate(map5.get(2)));// 结束日期

        Map<Integer, String> map6 = listMap.get(6);
        summary.setBgsx(map6.get(2));// 变更事项
        Map<Integer, String> map7 = listMap.get(7);
        //项目集code
        project.setGroupCode(map7.get(9));

        Map<Integer, String> map8 = listMap.get(8);//概述
        summary.setGs(map8.get(2));
        summary.setJdsfzc(map8.get(4));
        summary.setSfxyzc(map8.get(5));
        summary.setSfyxdd(map8.get(6));
        summary.setFxyc(map8.get(7));

        Map<Integer, String> map9 = listMap.get(9);//预测
        summary.setYc(map9.get(2));






       /* Map<Integer, String> map4 = listMap.get(4);
        String regEx = "[^0-9]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(map4.get(1));
        project.setSysj(Integer.parseInt(m.replaceAll("").trim())); //剩余时间*/

/*        Map<Integer, String> map5 = listMap.get(5);
        project.setBzgz(map5.get(1));//本周工作

        Map<Integer, String> map6 = listMap.get(6);
        project.setXzgz(map6.get(1));//下周工作*/

        //从excel第9行开始读'项目环节'
        List<ProProjectLink> linkList = new ArrayList<>();
        for (int i = 11; i < listMap.size(); i++) {
            Map<Integer, String> map = listMap.get(i);
            ProProjectLink link = new ProProjectLink();
            if (map.get(0) == null && map.get(1) == null && map.get(2) == null && map.get(3) == null && map.get(4) == null) {
                //如果多列都没数据，说明下方为空白
                break;
            }
            link.setSxh(map.get(1));//原始序号
            if (map.containsKey(2))
                link.setXmhj(map.get(2)); //项目环节
            if (map.containsKey(3))
                link.setQssj(getDate(map.get(3))); //起始时间
            if (map.containsKey(4))
                link.setYjwcsj(getDate(map.get(4))); //预计完成时间
            if (map.containsKey(5))

                link.setCxsj(getDay(map.get(5)));//持续时间
            if (map.containsKey(6))
                link.setSjwcsj(getDate(map.get(6)));//实际完成时间
            if (map.containsKey(7))
                if (!StringUtils.isEmpty(map.get(7)) && map.get(7).equals("√"))
                    link.setWcjd(1);
                else
                    link.setWcjd(0);
            //TODO 资源名称为用户真实姓名  存入系统后无法对应
            if (map.containsKey(8))
                parseUsers(map.get(8), null, usersMap, link, 2);

            //根据序号判断菜单层级
            String sxh = link.getSxh();
            //没有序号则不导入
            if (StringUtils.isEmpty(sxh)) continue;
            char[] chars = sxh.toCharArray();
            int dotCount = 0;
            char dot = '.'; //根据有几个点判断是几级菜单
            for (int j = 0; j < chars.length; j++) {
                if (chars[j] == dot) dotCount++;
            }

            //判断等级
            //1级菜单
            if (dotCount == 0) {
                link.setLevel(1);
                link.setLevel1(getDay(link.getSxh()));
            }
            //2级菜单
            else if (dotCount == 1) {
                String str1 = link.getSxh().substring(0, link.getSxh().indexOf("."));
                String str2 = link.getSxh().substring(link.getSxh().indexOf(".") + 1);
                link.setLevel(2);
                link.setLevel1(getDay(str1));
                link.setLevel2(getDay(str2));
            }
            //设置3级目录序号
            else if (dotCount == 2) {
                String str1 = link.getSxh().substring(0, link.getSxh().indexOf("."));
                String str2 = link.getSxh().substring(link.getSxh().indexOf(".") + 1, link.getSxh().lastIndexOf("."));
                String str3 = link.getSxh().substring(link.getSxh().lastIndexOf(".") + 1);
                link.setLevel(3);
                link.setLevel1(getDay(str1));
                link.setLevel2(getDay(str2));
                link.setLevel3(getDay(str3));
            }
            //4级菜单
            else if (dotCount == 3) {
                String substring1 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring2 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring3 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring4 = sxh;
                link.setLevel(4);
                link.setLevel1(getDay(substring1));
                link.setLevel2(getDay(substring2));
                link.setLevel3(getDay(substring3));
                link.setLevel4(getDay(substring4));
            }
            //5级菜单
            else if (dotCount == 4) {
                String substring1 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring2 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring3 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring4 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring5 = sxh;
                link.setLevel(5);
                link.setLevel1(getDay(substring1));
                link.setLevel2(getDay(substring2));
                link.setLevel3(getDay(substring3));
                link.setLevel4(getDay(substring4));
                link.setLevel5(getDay(substring5));
            }
            //4级菜单
            else if (dotCount == 5) {
                String substring1 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring2 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring3 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring4 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring5 = sxh.substring(0, sxh.indexOf("."));
                sxh = sxh.substring(sxh.indexOf(".") + 1);
                String substring6 = sxh;
                link.setLevel(6);
                link.setLevel1(getDay(substring1));
                link.setLevel2(getDay(substring2));
                link.setLevel3(getDay(substring3));
                link.setLevel4(getDay(substring4));
                link.setLevel5(getDay(substring5));
                link.setLevel6(getDay(substring6));
            }

            linkList.add(link);
        }


        project.setLinkList(linkList);
        project.setSummary(summary);

        return project;
    }

    /**
     * @param userStr  真实姓名
     * @param project  项目
     * @param usersMap 缓存用户真实姓名查出来的username
     * @param type     1-项目负责人 2-项目成员
     */
    private void parseUsers(String userStr, ProProject project, Map<String, String> usersMap, ProProjectLink link, int type) {
        List<String> userList = new ArrayList<>();
        if (!StringUtils.isEmpty(userStr)) {
            String[] split = userStr.split(",");
            if (split != null && split.length > 0) {
                List<String> list = Arrays.asList(split);
                list = list.stream().filter(x -> x.length() > 1).collect(Collectors.toList());
                for (int i = 0; i < list.size(); i++) {
                    String item = list.get(i);
                    if (usersMap.containsKey(item)) {
                        userList.add(usersMap.get(item));
                    } else {
                        Result<SysUser> user = systemClient.queryByName(item);
                        if (user != null && user.getResult() != null && user.getResult().getUsername() != null) {
                            userList.add(user.getResult().getUsername());
                            usersMap.put(item, user.getResult().getUsername());
                        }
                    }

                }
                if (userList.size() > 0) {
                    String join = Joiner.on(",").join(userList);
                    if (type == 1) {
                        project.setXmfzr(join);
                    } else {
                        link.setZymc(join);
                    }

                }


            }

        }
    }

    public static Integer getDay(String str) {
        if (!StringUtils.isEmpty(str)) {
            try {
                String regEx = "[^0-9]";
                Pattern p = Pattern.compile(regEx);
                Matcher m = p.matcher(str);
                String res = m.replaceAll("").trim();
                return Integer.parseInt(res);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    public static Date getDate(String str) {
        if (!StringUtils.isEmpty(str)) {
            try {
                if (str.contains("年")) {

                    return DateUtils.str2Date(str, new SimpleDateFormat("yyyy年MM月dd日"));
                } else {
                    return DateUtils.str2Date(str, new SimpleDateFormat("yyyy/MM/dd"));
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    public String exportProject(List<Map<String, Object>> pmo, List<String> sheetNames,List<Map<String, Object>> lcb, List<String> lcbSheetNames,List<Map<String, Object>> ylb, List<String> ylbSheetNames,List<ProjectVo> dtProList, List<String> dtSheetNames) throws Exception {
//        String start = DateUtils.weekToDayStartStr(DateUtils.getCurYear(), DateUtils.getCurWeek(), "yyyy-MM-dd");
//        String weekInMonth = DateUtils.getWeekInMonth(DateUtils.str2Date(start, DateUtils.date_sdf.get()));
        String end = DateUtils.weekToFriDayEndStr(DateUtils.getCurYear(),DateUtils.getCurWeek(), "yyyy-MM-dd");
        String weekInMonth = DateUtils.getWeekInMonth(end,2);
        String templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "pro" + File.separator + "tp_project.xlsx";
        String filePath = "export" + File.separator + "项目工作看板-" + weekInMonth + DateUtils.date2Str(new Date(),DateUtils.timeFormat.get()) + ".xlsx";

        String fileName = uploadpath + File.separator + filePath;
        File templateFile = new File(templateFileName);

        templateFile.deleteOnExit();
        InputStream is = new FileInputStream(templateFile);
        File file = new File(fileName);
        // 流
        OutputStream os = new BufferedOutputStream(new FileOutputStream(file));


        Context context = PoiTransformer.createInitialContext();
        context.putVar("departments", pmo);
        context.putVar("sheetNames", sheetNames);

        context.putVar("lcbProList", lcb);
        context.putVar("lcbSheetNames", lcbSheetNames);

        context.putVar("ylbProList", ylb);
        context.putVar("ylbSheetNames", ylbSheetNames);

        context.putVar("dtProList", dtProList);
        context.putVar("dtSheetNames", dtSheetNames);


        JxlsHelper
                .getInstance()
                .setUseFastFormulaProcessor(false)
                .setDeleteTemplateSheet(true)
                .processTemplate(is, os, context);

      return filePath;
        // context.putVar("myCellUpdater", new MyCellUpdater());
          /*  // 获取文件配置
            JxlsHelper helper = JxlsHelper.getInstance();
            // 绑定 输入输出
            PoiTransformer transformer = (PoiTransformer) helper.createTransformer(is, os);
            // 获取 工作簿
            Workbook workbook = transformer.getWorkbook();
            // 获取表
            Sheet sheet = workbook.getSheetAt(0);
            // 强制 表格执行公式
            sheet.setForceFormulaRecalculation(true);
            // 放入数据
            helper.processTemplate(context, transformer);*/


    }

    public static class MyCellUpdater implements CellDataUpdater {

        @Override
        public void updateCellData(CellData cellData, CellRef targetCell, Context context) {


            PoiCellData poiCellData = (PoiCellData) cellData;

            poiCellData.getCellStyle().setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            System.err.println(poiCellData);
        }
    }
}