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

Example 1 with RowsChainRelativeModel

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

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

the class ResultUtils method calculate.

/**
 * @todo 对查询结果进行计算处理:字段脱敏、格式化、数据旋转、同步环比、分组汇总等
 * @param desensitizeProvider
 * @param sqlToyConfig
 * @param dataSetResult
 * @param pivotCategorySet
 * @param extend
 * @return
 */
public static boolean calculate(DesensitizeProvider desensitizeProvider, SqlToyConfig sqlToyConfig, DataSetResult dataSetResult, List pivotCategorySet, QueryExecutorExtend extend) {
    List items = dataSetResult.getRows();
    // 数据为空直接跳出处理
    if (items == null || items.isEmpty()) {
        return false;
    }
    boolean changedCols = false;
    List<SecureMask> secureMasks = sqlToyConfig.getSecureMasks();
    List<FormatModel> formatModels = sqlToyConfig.getFormatModels();
    List resultProcessors = new ArrayList();
    if (!sqlToyConfig.getResultProcessor().isEmpty()) {
        resultProcessors.addAll(sqlToyConfig.getResultProcessor());
    }
    if (extend != null && !extend.calculators.isEmpty()) {
        resultProcessors.addAll(extend.calculators);
    }
    // 整理列名称跟index的对照map
    LabelIndexModel labelIndexMap = null;
    if (!secureMasks.isEmpty() || !formatModels.isEmpty() || (extend != null && (!extend.secureMask.isEmpty() || !extend.colsFormat.isEmpty())) || !resultProcessors.isEmpty()) {
        labelIndexMap = wrapLabelIndexMap(dataSetResult.getLabelNames());
    }
    // 字段脱敏
    if (!secureMasks.isEmpty()) {
        secureMask(desensitizeProvider, items, secureMasks.iterator(), labelIndexMap);
    }
    // 自动格式化
    if (!formatModels.isEmpty()) {
        formatColumn(items, formatModels.iterator(), labelIndexMap);
    }
    // 扩展脱敏和格式化处理
    if (extend != null) {
        if (!extend.secureMask.isEmpty()) {
            secureMask(desensitizeProvider, items, extend.secureMask.values().iterator(), labelIndexMap);
        }
        if (!extend.colsFormat.isEmpty()) {
            formatColumn(items, extend.colsFormat.values().iterator(), labelIndexMap);
        }
    }
    // 计算
    if (!resultProcessors.isEmpty()) {
        Object processor;
        for (int i = 0; i < resultProcessors.size(); i++) {
            processor = resultProcessors.get(i);
            // 数据旋转(行转列)
            if (processor instanceof PivotModel) {
                items = pivotResult((PivotModel) processor, labelIndexMap, items, pivotCategorySet);
                changedCols = true;
            } else // 列转行
            if (processor instanceof UnpivotModel) {
                items = UnpivotList.process((UnpivotModel) processor, dataSetResult, labelIndexMap, items);
            } else if (processor instanceof SummaryModel) {
                // 数据汇总合计
                GroupSummary.process((SummaryModel) processor, labelIndexMap, items);
            } else if (processor instanceof ColsChainRelativeModel) {
                // 列数据环比
                ColsChainRelative.process((ColsChainRelativeModel) processor, labelIndexMap, items);
                changedCols = true;
            } else if (processor instanceof RowsChainRelativeModel) {
                // 行数据环比
                RowsChainRelative.process((RowsChainRelativeModel) processor, labelIndexMap, items);
            } else if (processor instanceof ReverseModel) {
                // 数据反序
                ReverseList.process((ReverseModel) processor, labelIndexMap, items);
            }
        }
        dataSetResult.setRows(items);
    }
    return changedCols;
}

Example 3 with RowsChainRelativeModel

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

the class ChainRelativeTest method testRowsChainRelative.

@Test
public void testRowsChainRelative() {
    // |月份 | 产品 |交易笔数 | 环比 | 金额 | 环比 | 收入 | 环比 |
    // | 5月 | 香蕉 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |
    // | 5月 | 苹果 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |
    // | 4月 | 香蕉 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |0
    // | 4月 | 苹果 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |1
    // | 3月 | 香蕉 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |
    // | 3月 | 苹果 | 2000 | 环比 | 金额 | 环比 | 收入 | 环比 |
    Object[][] values = { { "5月", "香蕉", 2000 }, { "5月", "苹果", 1900 }, { "4月", "香蕉", 1800 }, { "4月", "苹果", 1800 }, { "3月", "香蕉", 1600 }, { "3月", "苹果", 1700 } };
    List result = CollectionUtil.arrayToDeepList(values);
    RowsChainRelativeModel rowsRelative = new RowsChainRelativeModel();
    rowsRelative.setGroupColumn("1");
    rowsRelative.setReduceOne(false);
    rowsRelative.setRelativeColumns(new String[] { "2" });
    rowsRelative.setFormat("#.00%");
    rowsRelative.setReverse(true);
    LabelIndexModel labelIndexMap = new LabelIndexModel();
    RowsChainRelative.process(rowsRelative, labelIndexMap, result);
    for (int i = 0; i < result.size(); i++) {
        System.out.println(JSON.toJSONString(result.get(i)));
    }
}