package com.ots.project.tool.report.PAQ.chart;

import com.ots.project.tool.report.MAQ.base.RowData;
import com.ots.project.tool.report.MAQ.base.SeriesData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFChart;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description: PAQ图表 散点图跟柱形图数据变更
 * @author: zhijie
 * @create: 2021-06-07 23:31
 **/
@Slf4j
public class PAQChart {

    private static String p_sde23,p_im24,p_incon25;

    public static void main(String[] args) throws IOException {
        try (OutputStream os = new FileOutputStream("D:\\data\\林_PAQ_IA_CN1.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\data\\林_PAQ_IA_CN.docx"))){
            Map<String, Object> textMap = new HashMap<String, Object>();
            textMap.put("P_SDE23","12");
            textMap.put("P_IM24","50");
            textMap.put("P_INCON25","90");
            changeChart(document,textMap);
            document.write(os);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 替换PAQ图表数据
     * @param document 文档对象
     * @param textMap 报告字典键值对
     * @return
     */
    public static boolean changeChart(XWPFDocument document, Map<String, Object> textMap){
        List<SeriesData> seriesDatas = initData(textMap);
        if(seriesDatas == null){
            log.error("替换PAQ图表数据,初始化数据失败!");
            return false;
        }
        //PAQ图表数据
        changePAQChart(document,seriesDatas);
        return true;
    }

    /**
     * 判断是否为空
     * @param value
     * @return
     */
    public static boolean checkParamsIsNull(String... value) {
        for (int i = 0; i < value.length; i++) {
            if (Objects.isNull(value[i])) {
                return true;
            }
        }
        return false;
    }

    /**
     * 初始化报告数据
     * @param textMap
     * @return
     */
    public static List<SeriesData> initData(Map<String, Object> textMap){
        //获取命中数值
        p_sde23 = (String) textMap.get("P_SDE23");
        p_im24 = (String) textMap.get("P_IM24");
        p_incon25 = (String) textMap.get("P_INCON25");
        //判断是否为空
        if (checkParamsIsNull(p_sde23, p_im24, p_incon25)) {
            return null;
        }
        Double P_SDE23 = Double.valueOf(p_sde23);
        Double P_IM24 = Double.valueOf(p_im24);
        Double P_INCON25 = Double.valueOf(p_incon25);

        // 测试数据
        List<SeriesData> seriesDatas = Arrays.asList(
                new SeriesData("个人数值在此调整", Arrays.asList(
                        new RowData("个人数值在此调整", P_INCON25),
                        new RowData("个人数值在此调整", P_IM24),
                        new RowData("个人数值在此调整", P_SDE23)
                )),
                new SeriesData("主柱形数据（x轴）1", Arrays.asList(
                        new RowData("主柱形数据（x轴）1", 0),
                        new RowData("主柱形数据（x轴）1", 5),
                        new RowData("主柱形数据（x轴）1", 5)
                )),
                new SeriesData("主柱形数据（x轴）2", Arrays.asList(
                        new RowData("主柱形数据（x轴）2", 5),
                        new RowData("主柱形数据（x轴）2", 5),
                        new RowData("主柱形数据（x轴）2", 5)
                )),
                new SeriesData("辅助柱形数据", Arrays.asList(
                        new RowData("辅助柱形数据", 100),
                        new RowData("辅助柱形数据", 95),
                        new RowData("辅助柱形数据", 95)
                )),
                new SeriesData("Y轴", Arrays.asList(
                        new RowData("Y轴", 27),
                        new RowData("Y轴", 60),
                        new RowData("Y轴", 93)
                ))
        );
        return seriesDatas;
    }

    /**
     * 替换word图表 散点图跟柱形图组合图形
     * @param doc
     * @param seriesDatas
     */
    public static void changePAQChart(XWPFDocument doc,List<SeriesData> seriesDatas) {
        try {
            //动态刷新图表
            List<POIXMLDocumentPart> relations = doc.getRelations();
            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;

                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();
                    // excel内置表格
                    XSSFWorkbook workbook = chart.getWorkbook();
                    XSSFSheet sheet = workbook.getSheetAt(0);

                    //散点图
                    if (!plot.getScatterChartList().isEmpty()) {
                        CTScatterChart scatterChart = plot.getScatterChartArray(0);
                        //System.out.println("散点图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, workbook, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        //散点图数据源 XY坐标
                        List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
                        scatterDatas.add(seriesDatas.get(0));
                        scatterDatas.add(seriesDatas.get(4));
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {
                            //更新水滴文字
                            updateDLbls(ser);
                            //更新散点图缓存
                            updateScatterChart(scatterDatas, ser.getXVal(), ser.getYVal());
                            ++i;
                        }
                    }
                    //柱形图
                    if (!plot.getBarChartList().isEmpty()) {
                        CTBarChart barChart = plot.getBarChartArray(0);
                        //System.out.println("柱形图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, workbook, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        //柱形图数据源
                        List<SeriesData> barDatas = seriesDatas.subList(1,4);
                        //因为柱形图CTBarSer没按excel顺序走 调换数据位置保持对应
                        Collections.swap(barDatas, 1, 2);
                        int i = 0;
                        for (CTBarSer ser : barChart.getSerList()) {
                            //更新柱形图数据缓存
                            updateBarChart(barDatas.get(i), ser.getVal());
                            ++i;
                        }
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            log.error("PAQGS替换word图表 散点图跟柱形图组合图形失败：{}",e);
        }
    }

    /**
     * 替换水滴数值
     * @param ser
     */
    private static void updateDLbls(CTScatterSer ser) {
        Map<String, String> textMap = new HashMap<>();
        //获取命中数值
        textMap.put("%P_SDE23%",p_sde23);
        textMap.put("%P_IM24%",p_im24);
        textMap.put("%P_INCON25%",p_incon25);
        List<CTDLbl> lbls = ser.getDLbls().getDLblList();
        for (int i = 0; i < lbls.size(); i++) {
            CTDLbl lbl = lbls.get(i);
            String library = lbl.getTx().getRich().getPArray(0).getRArray(0).getT();
            log.info("水滴数值替换前{}:{}",i,lbl.getTx().getRich().getPArray(0).getRArray(0).getT());
            if(textMap.get(library) != null){
                lbl.getTx().getRich().getPArray(0).getRArray(0).setT(textMap.get(library));
            }
            log.info("水滴数值替换后{}:{}",i,lbl.getTx().getRich().getPArray(0).getRArray(0).getT());
        }
    }

    /**
     * 更新图表的关联 excel
     *
     * @param seriesDatas
     * @param workbook
     * @param sheet
     */
    protected static void refreshExcel(List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet) {
        XSSFRow title = sheet.getRow(0);
        //遍历数据数组进行excel赋值
        for (int i = 0; i < seriesDatas.size(); i++) {
            SeriesData data = seriesDatas.get(i);
            if (data.name != null && !data.name.isEmpty()) {
                // 第一行标题
                XSSFCell cell = title.getCell(i);
                if (cell == null) {
                    cell = title.createCell(i + 1);
                }
                cell.setCellValue(data.name);
            }
            int size = data.value.size();
            //遍历数据进行赋值
            for (int j = 0; j < size; j++) {
                //从第二行开始赋值
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                RowData cellValu = data.value.get(j);

                //第一列开始赋值
                XSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cell = row.createCell(i);
                }
                cell.setCellValue(cellValu.value);
            }
            //删除多余行数
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
    }


    /**
     * 更新 散点图 缓存数据
     *
     * @param seriesDatas 数据
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    protected static void updateScatterChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
                                             CTNumDataSource yDataSource) {

        //获取xy坐标数据条数
        long xNumCnt = xDataSource.getNumRef().getNumCache().getPtCount().getVal();
        long yNumCnt = yDataSource.getNumRef().getNumCache().getPtCount().getVal();

        //获取XY最新数据
        List<RowData> xRowDatas = seriesDatas.get(0).getValue();
        int xRowDataSize = xRowDatas.size();
        List<RowData> yRowDatas = seriesDatas.get(1).getValue();
        int yRowDataSize = yRowDatas.size();

        //更新X坐标缓存
        for (int i = 0; i < xRowDatas.size(); i++) {
            RowData cellValu = xRowDatas.get(i);
            CTNumVal val = xNumCnt > i ? xDataSource.getNumRef().getNumCache().getPtArray(i)
                    : xDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        //更新Y坐标缓存
        for (int i = 0; i < yRowDatas.size(); i++) {
            RowData cellValu = yRowDatas.get(i);
            CTNumVal val = yNumCnt > i ? yDataSource.getNumRef().getNumCache().getPtArray(i)
                    : yDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        // 更新对应excel的范围
        xDataSource.getNumRef().setF(
                replaceRowEnd(xDataSource.getNumRef().getF(),
                        xNumCnt,
                        xRowDataSize));
        yDataSource.getNumRef().setF(
                replaceRowEnd(yDataSource.getNumRef().getF(),
                        yNumCnt,
                        yRowDataSize));

        // 删除多的
        if (xNumCnt > xRowDataSize) {
            for (int idx = xRowDataSize; idx < xNumCnt; idx++) {
                xDataSource.getNumRef().getNumCache().removePt(xRowDataSize);
            }
        }
        if (yNumCnt > yRowDataSize) {
            for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
                yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
            }
        }
        // 更新个数
        xDataSource.getNumRef().getNumCache().getPtCount().setVal(xRowDataSize);
        // 更新个数
        yDataSource.getNumRef().getNumCache().getPtCount().setVal(yRowDataSize);
    }


    /**
     * 更新 柱形图 的缓存数据
     *
     * @param seriesDatas   数据
     * @param numDataSource 数据的缓存
     */
    protected static void updateBarChart(SeriesData seriesDatas, CTNumDataSource numDataSource) {
        //获取柱形图数据数量
        long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
        int dataSize = seriesDatas.value.size();
        for (int i = 0; i < dataSize; i++) {
            RowData cellValu = seriesDatas.value.get(i);
            CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
                    : numDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));

        }

        // 更新对应excel的范围
        numDataSource.getNumRef().setF(
                replaceRowEnd(numDataSource.getNumRef().getF(),
                        ptNumCnt,
                        dataSize));

        // 删除多的
        if (ptNumCnt > dataSize) {
            for (int idx = dataSize; idx < ptNumCnt; idx++) {
                numDataSource.getNumRef().getNumCache().removePt(dataSize);
            }
        }
        // 更新个数
        numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
    }


    /**
     * 替换 形如： Sheet1!$A$2:$A$4 的字符
     *
     * @param range
     * @return
     */
    public static String replaceRowEnd(String range, long oldSize, long newSize) {
        Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
        Matcher matcher = pattern.matcher(range);
        if (matcher.find()) {
            long old = Long.parseLong(matcher.group(2));
            return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
        }
        return range;
    }

    /**
     * 获取图表有哪些类型
     * @param part
     */
    public static void getChartType(POIXMLDocumentPart part){
        XWPFChart chart = (XWPFChart) part;
        //根据属性第一列名称切换数据类型
        CTChart ctChart = chart.getCTChart();
        CTPlotArea plotArea = ctChart.getPlotArea();

        if (!plotArea.getBarChartList().isEmpty()) {
            System.out.println("柱状图{"+plotArea.getBarChartList().size()+"}");
        }
        if (!plotArea.getPieChartList().isEmpty()) {
            System.out.println("饼图{"+plotArea.getPieChartList().size()+"}");
        }
        if (!plotArea.getLineChartList().isEmpty()) {
            System.out.println("线形图{"+plotArea.getLineChartList().size()+"}");
        }
        if (!plotArea.getBar3DChartList().isEmpty()) {
            System.out.println("柱状图3D{"+plotArea.getBar3DChartList().size()+"}");
        }
        if (!plotArea.getScatterChartList().isEmpty()) {
            System.out.println("散点图{"+plotArea.getScatterChartList().size()+"}");
        }
    }

}