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

Example 1 with PivotModel

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

the class SqlXMLConfigParse method parseCalculator.

/**
 * @todo 解析对sqltoy查询结果的处理逻辑定义
 * @param sqlToyConfig
 * @param sqlElt
 */
private static void parseCalculator(SqlToyConfig sqlToyConfig, Element sqlElt) {
    List elements = sqlElt.elements();
    Element elt;
    String eltName;
    List resultProcessor = new ArrayList();
    for (int i = 0; i < elements.size(); i++) {
        elt = (Element) elements.get(i);
        eltName = elt.getName();
        // 旋转(只能进行一次旋转)
        if (eltName.equals("pivot")) {
            PivotModel pivotModel = new PivotModel();
            if (elt.attribute("group-columns") != null)
                pivotModel.setGroupCols(trimParams(elt.attributeValue("group-columns").toLowerCase().split(",")));
            if (elt.attribute("category-columns") != null)
                pivotModel.setCategoryCols(trimParams(elt.attributeValue("category-columns").toLowerCase().split(",")));
            if (elt.attribute("category-sql") != null)
                pivotModel.setCategorySql(elt.attributeValue("category-sql"));
            String[] pivotCols = new String[2];
            pivotCols[0] = elt.attributeValue("start-column").toLowerCase();
            if (elt.attribute("end-column") != null)
                pivotCols[1] = elt.attributeValue("end-column").toLowerCase();
            else
                pivotCols[1] = pivotCols[0];
            if (elt.attribute("default-value") != null) {
                String defaultValue = elt.attributeValue("default-value");
                if (elt.attribute("default-type") != null) {
                    String defaultType = elt.attributeValue("default-type");
                    try {
                        pivotModel.setDefaultValue(BeanUtil.convertType(defaultValue, defaultType));
                    } catch (Exception e) {
                    }
                } else
                    pivotModel.setDefaultValue(defaultValue);
            }
            pivotModel.setPivotCols(pivotCols);
            resultProcessor.add(pivotModel);
        } else // 列转行
        if (eltName.equals("unpivot")) {
            if (elt.attribute("columns") != null && elt.attribute("values-as-column") != null) {
                UnpivotModel unpivotModel = new UnpivotModel();
                String[] columns = elt.attributeValue("columns").split(",");
                String[] realCols = new String[columns.length];
                String[] colsAlias = new String[columns.length];
                int index = 0;
                String[] temp;
                for (String column : columns) {
                    temp = column.split(":");
                    realCols[index] = temp[0].trim().toLowerCase();
                    colsAlias[index] = temp[temp.length - 1];
                    index++;
                }
                unpivotModel.setColumns(realCols);
                unpivotModel.setColsAlias(colsAlias);
                // 多列变成行时转成的列名称
                unpivotModel.setAsColumn(elt.attributeValue("values-as-column"));
                // 变成行的列标题作为的新列名称
                if (elt.attribute("labels-as-column") != null)
                    unpivotModel.setLabelsColumn(elt.attributeValue("labels-as-column"));
                // 必须要有2个或以上列
                if (index > 1)
                    resultProcessor.add(unpivotModel);
            }
        } else // 汇总合计
        if (eltName.equals("summary")) {
            SummaryModel summaryModel = new SummaryModel();
            // 是否逆向汇总
            if (elt.attribute("reverse") != null) {
                summaryModel.setReverse(Boolean.parseBoolean(elt.attributeValue("reverse")));
                summaryModel.setGlobalReverse(summaryModel.isReverse());
            }
            // 汇总合计涉及的列
            if (elt.attribute("columns") != null)
                summaryModel.setSummaryCols(elt.attributeValue("columns").toLowerCase());
            // 保留小数点位数
            if (elt.attribute("radix-size") != null)
                summaryModel.setRadixSize(Integer.parseInt(elt.attributeValue("radix-size")));
            else
                summaryModel.setRadixSize(-1);
            // 汇总所在位置
            if (elt.attribute("sum-site") != null)
                summaryModel.setSumSite(elt.attributeValue("sum-site"));
            // sum和average值左右拼接时的连接字符串
            if (elt.attribute("link-sign") != null)
                summaryModel.setLinkSign(elt.attributeValue("link-sign"));
            // 全局汇总
            Element globalSummary = elt.element("global");
            if (globalSummary != null) {
                if (globalSummary.attribute("label-column") != null)
                    summaryModel.setGlobalLabelColumn(globalSummary.attributeValue("label-column").toLowerCase());
                if (globalSummary.attribute("average-label") != null)
                    summaryModel.setGlobalAverageTitle(globalSummary.attributeValue("average-label"));
                // 汇总分组列
                if (globalSummary.attribute("group-column") != null)
                    summaryModel.setGroupColumn(globalSummary.attributeValue("group-column").toLowerCase());
                // 全局汇总合计是否逆向
                if (globalSummary.attribute("reverse") != null)
                    summaryModel.setGlobalReverse(Boolean.parseBoolean(globalSummary.attributeValue("reverse")));
                if (globalSummary.attribute("sum-label") != null)
                    summaryModel.setGlobalSumTitle(globalSummary.attributeValue("sum-label"));
            }
            // 分组汇总
            List<Element> groupElts = elt.elements("group");
            if (groupElts != null && !groupElts.isEmpty()) {
                GroupMeta[] groupMetas = new GroupMeta[groupElts.size()];
                int index = 0;
                for (Element groupElt : groupElts) {
                    GroupMeta groupMeta = new GroupMeta();
                    groupMeta.setGroupColumn(groupElt.attributeValue("group-column").toLowerCase());
                    if (groupElt.attribute("average-label") != null)
                        groupMeta.setAverageTitle(groupElt.attributeValue("average-label"));
                    if (groupElt.attribute("sum-label") != null)
                        groupMeta.setSumTitle(groupElt.attributeValue("sum-label"));
                    if (groupElt.attribute("label-column") != null)
                        groupMeta.setLabelColumn(groupElt.attributeValue("label-column"));
                    groupMetas[index] = groupMeta;
                    index++;
                }
                summaryModel.setGroupMeta(groupMetas);
            }
            resultProcessor.add(summaryModel);
        }
    }
    // 加入sqltoyModel
    if (!resultProcessor.isEmpty()) {
        sqlToyConfig.setResultProcessor(resultProcessor);
    }
}

Example 2 with PivotModel

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

the class ResultUtils method getPivotCategory.

/**
 * @todo 提取数据旋转对应的sql查询结果
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param conn
 * @param dialect
 * @return
 * @throws Exception
 */
public static List getPivotCategory(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Connection conn, String dialect) throws Exception {
    if (sqlToyConfig.getResultProcessor() != null && !sqlToyConfig.getResultProcessor().isEmpty()) {
        List resultProcessors = sqlToyConfig.getResultProcessor();
        Object processor;
        for (int i = 0; i < resultProcessors.size(); i++) {
            processor = resultProcessors.get(i);
            // 数据旋转只能存在一个
            if (processor instanceof PivotModel) {
                PivotModel pivotModel = (PivotModel) processor;
                if (pivotModel.getCategorySql() != null) {
                    SqlToyConfig pivotSqlConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyContext.getSqlToyConfig(pivotModel.getCategorySql(), SqlType.search), queryExecutor, dialect, false);
                    SqlToyResult pivotSqlToyResult = SqlConfigParseUtils.processSql(pivotSqlConfig.getSql(), queryExecutor.getParamsName(pivotSqlConfig), queryExecutor.getParamsValue(pivotSqlConfig));
                    List pivotCategory = SqlUtil.findByJdbcQuery(pivotSqlToyResult.getSql(), pivotSqlToyResult.getParamsValue(), null, null, conn);
                    // 行转列返回
                    return CollectionUtil.convertColToRow(pivotCategory, null);
                }
            }
        }
    }
    return null;
}

Example 3 with PivotModel

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

the class ResultUtils method getPivotCategory.

/**
 * @todo 提取数据旋转对应的sql查询结果
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param conn
 * @param dbType
 * @param dialect
 * @return
 * @throws Exception
 */
public static List getPivotCategory(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Connection conn, final Integer dbType, String dialect) throws Exception {
    List resultProcessors = new ArrayList();
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (!sqlToyConfig.getResultProcessor().isEmpty()) {
        resultProcessors.addAll(sqlToyConfig.getResultProcessor());
    }
    // QueryExecutor中扩展的计算
    if (extend != null && !extend.calculators.isEmpty()) {
        resultProcessors.addAll(extend.calculators);
    }
    Object processor;
    for (int i = 0; i < resultProcessors.size(); i++) {
        processor = resultProcessors.get(i);
        // 数据旋转只能存在一个
        if (processor instanceof PivotModel) {
            PivotModel pivotModel = (PivotModel) processor;
            if (pivotModel.getCategorySql() != null) {
                SqlToyConfig pivotSqlConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyContext.getSqlToyConfig(pivotModel.getCategorySql(), SqlType.search, ""), queryExecutor, dialect, false);
                SqlToyResult pivotSqlToyResult = SqlConfigParseUtils.processSql(pivotSqlConfig.getSql(dialect), extend.getParamsName(pivotSqlConfig), extend.getParamsValue(sqlToyContext, pivotSqlConfig), dialect);
                List pivotCategory = SqlUtil.findByJdbcQuery(sqlToyContext.getTypeHandler(), pivotSqlToyResult.getSql(), pivotSqlToyResult.getParamsValue(), null, null, null, conn, dbType, sqlToyConfig.isIgnoreEmpty(), null, SqlToyConstants.FETCH_SIZE, -1);
                // 行转列返回
                return CollectionUtil.convertColToRow(pivotCategory, null);
            }
        }
    }
    return null;
}

Example 4 with PivotModel

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

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

the class ResultUtils method calculate.

/**
 * @todo 非存储过程模式调用结果计算处理器
 * @param sqlToyConfig
 * @param dataSetResult
 * @param pivotCategorySet
 * @param debug
 * @throws Exception
 */
public static void calculate(SqlToyConfig sqlToyConfig, DataSetResult dataSetResult, List pivotCategorySet, boolean debug) throws Exception {
    if (sqlToyConfig.getResultProcessor() != null) {
        List items = dataSetResult.getRows();
        List resultProcessors = sqlToyConfig.getResultProcessor();
        Object processor;
        HashMap<String, Integer> labelIndexMap = new HashMap<String, Integer>();
        String realLabelName;
        String[] fields = dataSetResult.getLabelNames();
        for (int i = 0, n = fields.length; i < n; i++) {
            realLabelName = fields[i].toLowerCase();
            if (realLabelName.indexOf(":") != -1)
                realLabelName = realLabelName.substring(0, realLabelName.indexOf(":")).trim();
            labelIndexMap.put(realLabelName, i);
        }
        for (int i = 0; i < resultProcessors.size(); i++) {
            processor = resultProcessors.get(i);
            // 数据旋转
            if (processor instanceof PivotModel) {
                items = pivotResult((PivotModel) processor, labelIndexMap, items, pivotCategorySet, debug);
            } else if (processor instanceof UnpivotModel) {
                items = unPivotResult((UnpivotModel) processor, dataSetResult, labelIndexMap, items);
            } else {
                // 数据汇总合计
                groupSummary((SummaryModel) processor, labelIndexMap, items);
            }
        }
        dataSetResult.setRows(items);
    }
}