Examples with SummaryGroupMeta org.sagacity.sqltoy.config.model.SummaryGroupMeta used on opensource projects

Example 1 with SummaryGroupMeta

use of org.sagacity.sqltoy.config.model.SummaryGroupMeta in project sagacity-sqltoy by chenrenfei.

the class GroupSummaryTest method testSummary.

@Test
public void testSummary() {
    Object[][] values = new Object[][] { { "202101", "手机", 100, 2000 }, { "202101", "电脑", 90, null }, { "202102", "手机", 80, 1700 }, { "202102", "电脑", 60, 7900 } };
    List dataSet = new ArrayList();
    for (int i = 0; i < values.length; i++) {
        List row = new ArrayList();
        for (int j = 0; j < values[i].length; j++) {
            row.add(values[i][j]);
        }
        dataSet.add(row);
    }
    SummaryColMeta[] colMetas = new SummaryColMeta[2];
    SummaryColMeta colMeta1 = new SummaryColMeta();
    colMeta1.setColIndex(2);
    colMeta1.setAveSkipNull(true);
    colMeta1.setSummaryType(1);
    SummaryColMeta colMeta2 = new SummaryColMeta();
    colMeta2.setColIndex(3);
    colMeta2.setAveSkipNull(true);
    colMeta2.setSummaryType(3);
    colMetas[0] = colMeta1;
    colMetas[1] = colMeta2;
    SummaryColMeta[] colMetas1 = new SummaryColMeta[2];
    SummaryColMeta colMeta11 = new SummaryColMeta();
    colMeta11.setColIndex(2);
    colMeta11.setAveSkipNull(true);
    colMeta11.setSummaryType(1);
    SummaryColMeta colMeta21 = new SummaryColMeta();
    colMeta21.setColIndex(3);
    colMeta21.setAveSkipNull(true);
    colMeta21.setSummaryType(3);
    colMetas1[0] = colMeta11;
    colMetas1[1] = colMeta21;
    SummaryGroupMeta[] groupMetas = new SummaryGroupMeta[2];
    SummaryGroupMeta globalMeta = new SummaryGroupMeta();
    globalMeta.setLabelIndex(0);
    globalMeta.setAverageTitle("平均值");
    globalMeta.setSumTitle("总计");
    globalMeta.setSumSite("top");
    globalMeta.setRowSize(2);
    globalMeta.setSummaryType(3);
    globalMeta.setSummaryCols(colMetas);
    groupMetas[0] = globalMeta;
    SummaryGroupMeta groupMeta = new SummaryGroupMeta();
    groupMeta.setGroupCols(new Integer[] { 0 });
    // groupMeta.setAverageTitle("平均值");
    groupMeta.setSumTitle("小计");
    groupMeta.setLabelIndex(0);
    globalMeta.setSummaryType(1);
    groupMeta.setRowSize(1);
    groupMeta.setSummaryCols(colMetas1);
    groupMeta.setSumSite("left");
    groupMetas[1] = groupMeta;
    CollectionUtil.groupSummary(dataSet, groupMetas, false, null);
    for (int i = 0; i < dataSet.size(); i++) {
        System.err.println(JSON.toJSONString(dataSet.get(i)));
    }
}

Example 2 with SummaryGroupMeta

use of org.sagacity.sqltoy.config.model.SummaryGroupMeta in project sagacity-sqltoy by chenrenfei.

the class SqlXMLConfigParse method parseCalculator.

/**
 * @todo 解析对sqltoy查询结果的计算处理逻辑定义(包含:旋转、汇总等)
 * @param sqlToyConfig
 * @param sqlElt
 * @param local
 * @throws Exception
 */
private static void parseCalculator(SqlToyConfig sqlToyConfig, Element sqlElt, String local) throws Exception {
    NodeList elements = sqlElt.getChildNodes();
    Element elt;
    String eltName;
    List resultProcessor = new ArrayList();
    for (int i = 0; i < elements.getLength(); i++) {
        if (elements.item(i).getNodeType() == Node.ELEMENT_NODE) {
            elt = (Element) elements.item(i);
            eltName = elt.getNodeName();
            // 旋转(只能进行一次旋转)
            if (eltName.equals(local.concat("pivot"))) {
                PivotModel pivotModel = new PivotModel();
                if (elt.hasAttribute("group-columns")) {
                    pivotModel.setGroupCols(trimParams(elt.getAttribute("group-columns").toLowerCase().split("\\,")));
                }
                if (elt.hasAttribute("category-columns")) {
                    pivotModel.setCategoryCols(trimParams(elt.getAttribute("category-columns").toLowerCase().split("\\,")));
                }
                if (elt.hasAttribute("category-sql")) {
                    pivotModel.setCategorySql(elt.getAttribute("category-sql"));
                }
                String[] startEndCols = new String[2];
                startEndCols[0] = elt.getAttribute("start-column").toLowerCase();
                if (elt.hasAttribute("end-column")) {
                    startEndCols[1] = elt.getAttribute("end-column").toLowerCase();
                } else {
                    startEndCols[1] = startEndCols[0];
                }
                if (elt.hasAttribute("default-value")) {
                    String defaultValue = elt.getAttribute("default-value");
                    if (elt.hasAttribute("default-type")) {
                        String defaultType = elt.getAttribute("default-type").toLowerCase();
                        try {
                            pivotModel.setDefaultValue(BeanUtil.convertType(defaultValue, defaultType));
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    } else {
                        pivotModel.setDefaultValue(defaultValue);
                    }
                }
                pivotModel.setStartEndCols(startEndCols);
                resultProcessor.add(pivotModel);
            } else // 列转行
            if (eltName.equals(local.concat("unpivot"))) {
                UnpivotModel unpivotModel = new UnpivotModel();
                XMLUtil.setAttributes(elt, unpivotModel);
                if (unpivotModel.getColumnsToRows().length > 1) {
                    resultProcessor.add(unpivotModel);
                }
            } else // 汇总合计
            if (eltName.equals(local.concat("summary"))) {
                SummaryModel summaryModel = new SummaryModel();
                // 是否逆向汇总
                if (elt.hasAttribute("reverse")) {
                    summaryModel.setReverse(Boolean.parseBoolean(elt.getAttribute("reverse")));
                }
                // 汇总合计涉及的列
                if (elt.hasAttribute("sum-columns")) {
                    summaryModel.setSummaryCols(elt.getAttribute("sum-columns").toLowerCase());
                } else if (elt.hasAttribute("columns")) {
                    summaryModel.setSummaryCols(elt.getAttribute("columns").toLowerCase());
                }
                // 计算平均值的列
                if (elt.hasAttribute("average-columns")) {
                    summaryModel.setAverageCols(elt.getAttribute("average-columns").toLowerCase());
                }
                // 保留小数点位数(2022-2-23 扩展成数组，便于给不同平均值列设置不同的小数位)
                if (elt.hasAttribute("average-radix-sizes")) {
                    summaryModel.setRadixSize(trimParamsToInt(elt.getAttribute("average-radix-sizes").split("\\,")));
                } else if (elt.hasAttribute("radix-size")) {
                    summaryModel.setRadixSize(trimParamsToInt(elt.getAttribute("radix-size").split("\\,")));
                }
                if (elt.hasAttribute("average-rounding-modes")) {
                    String[] roundingModeAry = trimParams(elt.getAttribute("average-rounding-modes").toUpperCase().split("\\,"));
                    RoundingMode[] roudingModes = new RoundingMode[roundingModeAry.length];
                    String roundingMode;
                    RoundingMode roundMode = null;
                    for (int k = 0; k < roundingModeAry.length; k++) {
                        roundingMode = roundingModeAry[k];
                        if (roundingMode.equals("HALF_UP")) {
                            roundMode = RoundingMode.HALF_UP;
                        } else if (roundingMode.equals("HALF_DOWN")) {
                            roundMode = RoundingMode.HALF_DOWN;
                        } else if (roundingMode.equals("ROUND_DOWN")) {
                            roundMode = RoundingMode.DOWN;
                        } else if (roundingMode.equals("ROUND_UP")) {
                            roundMode = RoundingMode.UP;
                        } else {
                            roundMode = RoundingMode.HALF_UP;
                        }
                        roudingModes[k] = roundMode;
                    }
                    summaryModel.setRoundingModes(roudingModes);
                }
                // 汇总所在位置
                if (elt.hasAttribute("sum-site")) {
                    summaryModel.setSumSite(elt.getAttribute("sum-site"));
                }
                // sum和average值左右拼接时的连接字符串
                if (elt.hasAttribute("link-sign")) {
                    summaryModel.setLinkSign(elt.getAttribute("link-sign"));
                }
                // 求平均时是否过滤掉null的记录
                if (elt.hasAttribute("average-skip-null")) {
                    summaryModel.setAverageSkipNull(Boolean.parseBoolean(elt.getAttribute("average-skip-null")));
                }
                NodeList nodeList = elt.getElementsByTagName(local.concat("global"));
                List<SummaryGroupMeta> groupMetaList = new ArrayList<SummaryGroupMeta>();
                // 全局汇总
                if (nodeList.getLength() > 0) {
                    SummaryGroupMeta globalMeta = new SummaryGroupMeta();
                    Element globalSummary = (Element) nodeList.item(0);
                    if (globalSummary.hasAttribute("label-column")) {
                        globalMeta.setLabelColumn(globalSummary.getAttribute("label-column").toLowerCase());
                    }
                    if (globalSummary.hasAttribute("average-label")) {
                        globalMeta.setAverageTitle(globalSummary.getAttribute("average-label"));
                    }
                    // 汇总分组列
                    if (globalSummary.hasAttribute("group-column")) {
                        globalMeta.setGroupColumn(globalSummary.getAttribute("group-column").toLowerCase());
                    }
                    if (globalSummary.hasAttribute("sum-label")) {
                        globalMeta.setSumTitle(globalSummary.getAttribute("sum-label"));
                    }
                    if (globalSummary.hasAttribute("reverse")) {
                        globalMeta.setGlobalReverse(Boolean.parseBoolean(globalSummary.getAttribute("reverse")));
                    }
                    if (summaryModel.isReverse()) {
                        globalMeta.setGlobalReverse(false);
                    }
                    groupMetaList.add(globalMeta);
                }
                // 分组汇总
                nodeList = elt.getElementsByTagName(local.concat("group"));
                if (nodeList.getLength() > 0) {
                    Element groupElt;
                    for (int j = 0; j < nodeList.getLength(); j++) {
                        groupElt = (Element) nodeList.item(j);
                        SummaryGroupMeta groupMeta = new SummaryGroupMeta();
                        groupMeta.setGroupColumn(groupElt.getAttribute("group-column").toLowerCase());
                        if (groupElt.hasAttribute("average-label")) {
                            groupMeta.setAverageTitle(groupElt.getAttribute("average-label"));
                        }
                        if (groupElt.hasAttribute("sum-label")) {
                            groupMeta.setSumTitle(groupElt.getAttribute("sum-label"));
                        }
                        if (groupElt.hasAttribute("label-column")) {
                            groupMeta.setLabelColumn(groupElt.getAttribute("label-column"));
                        }
                        groupMetaList.add(groupMeta);
                    }
                }
                if (!groupMetaList.isEmpty()) {
                    SummaryGroupMeta[] groupMetas = new SummaryGroupMeta[groupMetaList.size()];
                    groupMetaList.toArray(groupMetas);
                    summaryModel.setGroupMeta(groupMetas);
                }
                resultProcessor.add(summaryModel);
            } else // 列与列进行比较
            if (eltName.equals(local.concat("cols-chain-relative"))) {
                ColsChainRelativeModel colsRelativeModel = new ColsChainRelativeModel();
                XMLUtil.setAttributes(elt, colsRelativeModel);
                resultProcessor.add(colsRelativeModel);
            } else // 行与行进行比较
            if (eltName.equals(local.concat("rows-chain-relative"))) {
                RowsChainRelativeModel rowsRelativeModel = new RowsChainRelativeModel();
                XMLUtil.setAttributes(elt, rowsRelativeModel);
                resultProcessor.add(rowsRelativeModel);
            } else // 集合数据顺序颠倒
            if (eltName.equals(local.concat("reverse"))) {
                ReverseModel reverseModel = new ReverseModel();
                XMLUtil.setAttributes(elt, reverseModel);
                resultProcessor.add(reverseModel);
            }
        }
    }
    // 加入sqlToyConfig
    sqlToyConfig.setResultProcessor(resultProcessor);
}

Example 3 with SummaryGroupMeta

use of org.sagacity.sqltoy.config.model.SummaryGroupMeta in project sagacity-sqltoy by chenrenfei.

the class GroupSummary method process.

public static void process(SummaryModel summaryModel, LabelIndexModel labelIndexMap, List result) {
    // 记录小于2条无需汇总计算
    if (result == null || result.size() < 2) {
        return;
    }
    // 计算的列，columns="1..result.width()-1"
    int dataWidth = ((List) result.get(0)).size();
    List<Integer> sumColList = parseColumns(labelIndexMap, summaryModel.getSummaryCols(), dataWidth);
    List<Integer> aveColList = parseColumns(labelIndexMap, summaryModel.getAverageCols(), dataWidth);
    Set<Integer> summaryColsSet = new LinkedHashSet<Integer>();
    for (Integer index : sumColList) {
        summaryColsSet.add(index);
    }
    for (Integer index : aveColList) {
        summaryColsSet.add(index);
    }
    // 未设置分组和汇总计算列信息
    if (summaryModel.getGroupMeta() == null || summaryModel.getGroupMeta().length == 0 || summaryColsSet.size() == 0) {
        throw new IllegalArgumentException("summary计算未正确配置sum-columns或average-columns或group分组信息!");
    }
    // 全部计算列
    Integer[] summaryCols = new Integer[summaryColsSet.size()];
    summaryColsSet.toArray(summaryCols);
    // 同时存在求和、求平均
    boolean bothSumAverage = !sumColList.isEmpty() && !aveColList.isEmpty();
    // 组织分组配置
    String sumSite;
    for (SummaryGroupMeta groupMeta : summaryModel.getGroupMeta()) {
        sumSite = (summaryModel.getSumSite() == null) ? "top" : summaryModel.getSumSite().toLowerCase();
        List<Integer> groupColsList = parseColumns(labelIndexMap, groupMeta.getGroupColumn(), dataWidth);
        Integer[] groupCols = new Integer[groupColsList.size()];
        groupColsList.toArray(groupCols);
        // 分组列
        groupMeta.setGroupCols(groupCols);
        if (bothSumAverage) {
            if (StringUtil.isNotBlank(groupMeta.getSumTitle()) && StringUtil.isNotBlank(groupMeta.getAverageTitle())) {
                groupMeta.setSummaryType(3);
            } else if (StringUtil.isNotBlank(groupMeta.getAverageTitle())) {
                groupMeta.setSummaryType(2);
            } else // summaryType默认为1即sum计算
            {
                groupMeta.setSummaryType(1);
            }
        } else if (!sumColList.isEmpty()) {
            groupMeta.setSummaryType(1);
        } else if (!aveColList.isEmpty()) {
            groupMeta.setSummaryType(2);
        }
        groupMeta.setSumSite(sumSite);
        // 分组的标题列
        groupMeta.setLabelIndex(NumberUtil.isInteger(groupMeta.getLabelColumn()) ? Integer.parseInt(groupMeta.getLabelColumn()) : labelIndexMap.get(groupMeta.getLabelColumn().toLowerCase()));
        // 汇总和求平均分两行组装,update 2022-2-28 增加每个分组是否同时有汇总标题和求平均标题，允许不同分组只有汇总或求平均
        if (groupMeta.getSummaryType() == 3 && (sumSite.equals("top") || sumSite.equals("bottom"))) {
            groupMeta.setRowSize(2);
        }
        groupMeta.setSummaryCols(createColMeta(summaryCols, summaryModel, sumColList, aveColList));
    }
    CollectionUtil.groupSummary(result, summaryModel.getGroupMeta(), summaryModel.isReverse(), summaryModel.getLinkSign());
}

Example 4 with SummaryGroupMeta

use of org.sagacity.sqltoy.config.model.SummaryGroupMeta in project sagacity-sqltoy by chenrenfei.

the class CollectionUtil method groupSummary.

/**
 * @TODO 分组汇总计算
 * @param sumData
 * @param groupMetas
 * @param isReverse
 * @param linkSign
 */
public static void groupSummary(List sumData, SummaryGroupMeta[] groupMetas, boolean isReverse, String linkSign) {
    // 分组计算，数据集合少于2条没有必要计算
    if (sumData == null || sumData.size() < 2 || groupMetas == null || groupMetas.length == 0) {
        return;
    }
    // 内部子分组自动加上父级分组的列,规避重复
    Set<Integer> groupCols = new LinkedHashSet<Integer>();
    for (SummaryGroupMeta groupMeta : groupMetas) {
        if (groupMeta.getGroupCols() != null && groupMeta.getGroupCols().length > 0) {
            for (int index : groupMeta.getGroupCols()) {
                groupCols.add(index);
            }
        }
        // 子分组合并父分组的列
        if (groupCols.size() > 0) {
            Integer[] cols = new Integer[groupCols.size()];
            groupCols.toArray(cols);
            groupMeta.setGroupCols(cols);
        }
        // 转小写
        if (groupMeta.getSumSite() != null) {
            groupMeta.setSumSite(groupMeta.getSumSite().toLowerCase());
        } else {
            groupMeta.setSumSite("");
        }
    }
    // 进行数据逆转，然后统一按照顺序计算，结果再进行逆向处理
    if (isReverse) {
        Collections.reverse(sumData);
        for (SummaryGroupMeta groupMeta : groupMetas) {
            if (groupMeta.getSumSite() != null) {
                // sum和ave 是两行上下模式，逆序
                if ("top".equals(groupMeta.getSumSite())) {
                    groupMeta.setSumSite("bottom");
                } else if ("bottom".equals(groupMeta.getSumSite())) {
                    groupMeta.setSumSite("top");
                }
            }
        }
    }
    summaryList(sumData, groupMetas, StringUtil.isBlank(linkSign) ? " / " : linkSign);
    // 将结果反转
    if (isReverse) {
        Collections.reverse(sumData);
    }
}

Example 5 with SummaryGroupMeta

use of org.sagacity.sqltoy.config.model.SummaryGroupMeta in project sagacity-sqltoy by chenrenfei.

the class CollectionUtil method summaryList.

/**
 * @TODO 进行汇总计算
 * @param dataSet
 * @param groupMetas
 * @param linkSign
 */
private static void summaryList(List<List> dataSet, SummaryGroupMeta[] groupMetas, String linkSign) {
    List<List> iterList = new ArrayList();
    for (List item : dataSet) {
        iterList.add(item);
    }
    int groupSize = groupMetas.length;
    SummaryGroupMeta groupMeta;
    List row;
    List preRow = iterList.get(0);
    int dataSize = iterList.size();
    int addRows = 0;
    List sumRows;
    for (int i = 0; i < dataSize; i++) {
        row = iterList.get(i);
        // 从最明细分组开始(从里而外)
        for (int j = groupSize; j > 0; j--) {
            groupMeta = groupMetas[j - 1];
            // 判断分组字段值是否相同
            if (isEquals(row, preRow, i, groupMeta.getGroupCols())) {
                // 汇总计算
                calculateTotal(row, groupMeta);
            } else {
                sumRows = createSummaryRow(preRow, groupMeta, linkSign);
                // 插入汇总行(可能存在sum、ave 两行数据)
                dataSet.addAll(i + addRows, sumRows);
                // 累加增加的记录行数
                addRows = addRows + sumRows.size();
                // 重置分组的列计算的汇总相关的值(sum、rowCount、nullRowCount)
                for (SummaryColMeta colMeta : groupMeta.getSummaryCols()) {
                    colMeta.setNullCount(0);
                    colMeta.setSumValue(BigDecimal.ZERO);
                    colMeta.setRowCount(0);
                }
                calculateTotal(row, groupMeta);
            }
            // 最后一行
            if (i == dataSize - 1) {
                // 全局汇总的置顶
                if (groupMeta.isGlobalReverse()) {
                    dataSet.add(0, createSummaryRow(row, groupMeta, linkSign));
                } else {
                    dataSet.addAll(createSummaryRow(row, groupMeta, linkSign));
                }
            }
        }
        preRow = row;
    }
}