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

Example 1 with SummaryColMeta

use of org.sagacity.sqltoy.config.model.SummaryColMeta 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 SummaryColMeta

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

the class GroupSummary method createColMeta.

/**
 * @TODO 创建每个分组的汇总列配置信息(其中存放了汇总值、汇总的数据个数，所以必须每个分组创建独立的实例)
 * @param summaryCols
 * @param summaryModel
 * @param sumColList
 * @param aveColList
 * @return
 */
private static SummaryColMeta[] createColMeta(Integer[] summaryCols, SummaryModel summaryModel, List<Integer> sumColList, List<Integer> aveColList) {
    SummaryColMeta[] colMetas = new SummaryColMeta[summaryCols.length];
    RoundingMode[] roundingModes = summaryModel.getRoundingModes();
    int roundingSize = (roundingModes == null) ? 0 : roundingModes.length;
    int aveIndex = 0;
    Integer[] radixSizes = summaryModel.getRadixSize();
    int radixSizeLen = (radixSizes == null) ? 0 : radixSizes.length;
    for (int i = 0; i < summaryCols.length; i++) {
        SummaryColMeta colMeta = new SummaryColMeta();
        colMeta.setAveSkipNull(summaryModel.isAverageSkipNull());
        colMeta.setSummaryType(0);
        colMeta.setColIndex(summaryCols[i]);
        if (radixSizeLen == 1) {
            colMeta.setRadixSize(radixSizes[0]);
        }
        if (roundingSize == 1) {
            colMeta.setRoundingMode(roundingModes[0]);
        }
        // 存在汇总:1
        if (sumColList.contains(summaryCols[i])) {
            colMeta.setSummaryType(colMeta.getSummaryType() + 1);
        }
        // 存在求平均:2
        if (aveColList.contains(summaryCols[i])) {
            colMeta.setSummaryType(colMeta.getSummaryType() + 2);
            if (roundingSize > 1 && aveIndex < roundingSize) {
                if (roundingModes[aveIndex] != null) {
                    colMeta.setRoundingMode(roundingModes[aveIndex]);
                }
            }
            if (radixSizeLen > 1 && aveIndex < radixSizeLen) {
                if (radixSizes[aveIndex] != null) {
                    colMeta.setRadixSize(radixSizes[aveIndex]);
                }
            }
            aveIndex++;
        }
        colMetas[i] = colMeta;
    }
    return colMetas;
}

Example 3 with SummaryColMeta

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

the class CollectionUtil method createSummaryRow.

/**
 * @TODO 构造分组汇总行数据
 * @param row
 * @param groupMeta
 * @param linkSign
 * @return
 */
private static List createSummaryRow(List row, SummaryGroupMeta groupMeta, String linkSign) {
    List<List> result = new ArrayList();
    // 汇总类别
    int rowSize = groupMeta.getRowSize();
    int labelIndex = groupMeta.getLabelIndex();
    // 构造sum、ave数据行
    for (int i = 0; i < rowSize; i++) {
        List rowData = new ArrayList();
        // 填充空值构造等长集合
        for (int j = 0; j < row.size(); j++) {
            rowData.add(null);
        }
        // 补充分组列前面的上层分组的数据
        if (labelIndex > 0) {
            for (int k = 0; k < labelIndex; k++) {
                rowData.set(k, row.get(k));
            }
        }
        result.add(rowData);
    }
    List sumList = null;
    List aveList = null;
    // 平均和汇总分两行展示
    if (rowSize == 2) {
        if (groupMeta.getSumSite().equals("top")) {
            sumList = result.get(0);
            aveList = result.get(1);
        } else {
            sumList = result.get(1);
            aveList = result.get(0);
        }
        // 设置标题
        sumList.set(labelIndex, groupMeta.getSumTitle());
        aveList.set(labelIndex, groupMeta.getAverageTitle());
    } else {
        // 单行，平均和汇总共一行数据
        sumList = result.get(0);
        if (groupMeta.getSummaryType() == 3) {
            if (groupMeta.getSumSite().equals("left")) {
                sumList.set(labelIndex, groupMeta.getSumTitle() + linkSign + groupMeta.getAverageTitle());
            } else {
                sumList.set(labelIndex, groupMeta.getAverageTitle() + linkSign + groupMeta.getSumTitle());
            }
        } else if (groupMeta.getSummaryType() == 1) {
            sumList.set(labelIndex, groupMeta.getSumTitle());
        } else if (groupMeta.getSummaryType() == 2) {
            sumList.set(labelIndex, groupMeta.getAverageTitle());
        }
    }
    // 汇总值、平均值
    BigDecimal sumValue;
    BigDecimal aveValue;
    String sumStr = "--";
    String aveStr = "--";
    for (SummaryColMeta colMeta : groupMeta.getSummaryCols()) {
        sumValue = colMeta.getSumValue();
        // 计算平均值
        if (sumValue.equals(BigDecimal.ZERO)) {
            aveValue = BigDecimal.ZERO;
        } else {
            if (colMeta.isAveSkipNull()) {
                aveValue = sumValue.divide(BigDecimal.valueOf(colMeta.getRowCount() - colMeta.getNullCount()), colMeta.getRadixSize(), colMeta.getRoundingMode());
            } else {
                aveValue = sumValue.divide(BigDecimal.valueOf(colMeta.getRowCount()), colMeta.getRadixSize(), colMeta.getRoundingMode());
            }
        }
        // 汇总和平均为2行记录
        if (rowSize == 2) {
            // 汇总
            if (colMeta.getSummaryType() == 1 || colMeta.getSummaryType() == 3) {
                sumList.set(colMeta.getColIndex(), sumValue);
            }
            // 求平均
            if (colMeta.getSummaryType() == 2 || colMeta.getSummaryType() == 3) {
                aveList.set(colMeta.getColIndex(), aveValue);
            }
        } else {
            // 单行数据同时存在平均和汇总
            if (groupMeta.getSummaryType() == 3) {
                if (colMeta.getSummaryType() == 1) {
                    sumStr = sumValue.toPlainString();
                    aveStr = "--";
                } else if (colMeta.getSummaryType() == 2) {
                    aveStr = aveValue.toPlainString();
                    sumStr = "--";
                } else if (colMeta.getSummaryType() == 3) {
                    sumStr = sumValue.toPlainString();
                    aveStr = aveValue.toPlainString();
                }
                if (groupMeta.getSumSite().equals("left")) {
                    // {总计 / 平均 } 或 { -- / 平均 } 风格
                    sumList.set(colMeta.getColIndex(), sumStr + linkSign + aveStr);
                } else {
                    sumList.set(colMeta.getColIndex(), aveStr + linkSign + sumStr);
                }
            } else {
                if (colMeta.getSummaryType() == 1) {
                    sumList.set(colMeta.getColIndex(), sumValue);
                } else if (colMeta.getSummaryType() == 2) {
                    sumList.set(colMeta.getColIndex(), aveValue);
                }
            }
        }
    }
    return result;
}

Example 4 with SummaryColMeta

use of org.sagacity.sqltoy.config.model.SummaryColMeta 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;
    }
}

Example 5 with SummaryColMeta

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

the class CollectionUtil method calculateTotal.

/**
 * @TODO 计算汇总
 * @param row
 * @param groupMeta
 */
private static void calculateTotal(List row, SummaryGroupMeta groupMeta) {
    Object cellValue;
    for (SummaryColMeta colMeta : groupMeta.getSummaryCols()) {
        cellValue = row.get(colMeta.getColIndex());
        colMeta.setRowCount(colMeta.getRowCount() + 1);
        // 空值
        if (cellValue == null || "".equals(cellValue.toString().trim())) {
            colMeta.setNullCount(colMeta.getNullCount() + 1);
        } else {
            colMeta.setSumValue(colMeta.getSumValue().add(new BigDecimal(cellValue.toString().replace(",", ""))));
        }
    }
}