use of org.sagacity.sqltoy.config.model.UnpivotModel 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);
}
}
use of org.sagacity.sqltoy.config.model.UnpivotModel in project sagacity-sqltoy by chenrenfei.
the class UnPivotListTest method testUnpivotSingle.
@Test
public void testUnpivotSingle() {
// |------- 1月-------|------- 2月 ------|------ 3月--------|
// |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |
Object[][] values = { { "5月", "香蕉", 2000, 20000 } };
List result = CollectionUtil.arrayToDeepList(values);
UnpivotModel unpivotModel = new UnpivotModel();
unpivotModel.setColumnsToRows(new String[] { "quantity:数量,AMT:金额" });
unpivotModel.setNewColumnsLabels(new String[] { "indexName", "indexValue" });
DataSetResult resultModel = new DataSetResult();
resultModel.setLabelNames(new String[] { "month", "fruitName", "quantity", "AMT" });
resultModel.setLabelTypes(new String[] { "string", "string", "decimal", "decimal" });
LabelIndexModel labelIndexMap = new LabelIndexModel();
labelIndexMap.put("month", 0);
labelIndexMap.put("fruitname", 1);
labelIndexMap.put("quantity", 2);
labelIndexMap.put("amt", 3);
List value = UnpivotList.process(unpivotModel, resultModel, labelIndexMap, result);
for (int i = 0; i < value.size(); i++) {
System.out.println(JSON.toJSONString(result.get(i)));
}
}
use of org.sagacity.sqltoy.config.model.UnpivotModel in project sagacity-sqltoy by chenrenfei.
the class UnPivotListTest method testUnpivotMiddle.
@Test
public void testUnpivotMiddle() {
// |------- 1月-------|------- 2月 ------|------ 3月--------|
// |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |
Object[][] values = { { "5月", "香蕉", 2000, 20000, "好" }, { "5月", "苹果", 1900, 38999, "好" }, { "4月", "香蕉", 1800, 21000, "好" }, { "4月", "苹果", 1800, 400000, "好" } };
List result = CollectionUtil.arrayToDeepList(values);
UnpivotModel unpivotModel = new UnpivotModel();
unpivotModel.setColumnsToRows(new String[] { "quantity:数量,AMT:金额" });
unpivotModel.setNewColumnsLabels(new String[] { "indexName", "indexValue" });
DataSetResult resultModel = new DataSetResult();
resultModel.setLabelNames(new String[] { "month", "fruitName", "quantity", "AMT", "greet" });
resultModel.setLabelTypes(new String[] { "string", "string", "decimal", "decimal", "string" });
LabelIndexModel labelIndexMap = new LabelIndexModel();
labelIndexMap.put("month", 0);
labelIndexMap.put("fruitname", 1);
labelIndexMap.put("quantity", 2);
labelIndexMap.put("amt", 3);
List value = UnpivotList.process(unpivotModel, resultModel, labelIndexMap, result);
for (int i = 0; i < value.size(); i++) {
System.out.println(JSON.toJSONString(result.get(i)));
}
}
use of org.sagacity.sqltoy.config.model.UnpivotModel in project sagacity-sqltoy by chenrenfei.
the class UnPivotListTest method testUnpivot.
@Test
public void testUnpivot() {
// |------- 1月-------|------- 2月 ------|------ 3月--------|
// |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |交易笔 | 金额 | 收入 |
Object[][] values = { { "5月", "香蕉", 2000, 20000 }, { "5月", "苹果", 1900, 38999 }, { "4月", "香蕉", 1800, 21000 }, { "4月", "苹果", 1800, 400000 } };
List result = CollectionUtil.arrayToDeepList(values);
UnpivotModel unpivotModel = new UnpivotModel();
unpivotModel.setColumnsToRows(new String[] { "quantity:数量,AMT:金额" });
unpivotModel.setNewColumnsLabels(new String[] { "indexName", "indexValue" });
DataSetResult resultModel = new DataSetResult();
resultModel.setLabelNames(new String[] { "month", "fruitName", "quantity", "AMT" });
resultModel.setLabelTypes(new String[] { "string", "string", "decimal", "decimal" });
LabelIndexModel labelIndexMap = new LabelIndexModel();
labelIndexMap.put("month", 0);
labelIndexMap.put("fruitname", 1);
labelIndexMap.put("quantity", 2);
labelIndexMap.put("amt", 3);
List value = UnpivotList.process(unpivotModel, resultModel, labelIndexMap, result);
for (int i = 0; i < value.size(); i++) {
System.out.println(JSON.toJSONString(result.get(i)));
}
}
use of org.sagacity.sqltoy.config.model.UnpivotModel 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);
}
Aggregations