From 914921a6b272b18a436f05422e33f9d963f48577 Mon Sep 17 00:00:00 2001
From: 许鹏程 <1821349743@qq.com>
Date: 星期四, 29 六月 2023 10:36:31 +0800
Subject: [PATCH] poi、easyexcel、poi-tl升级 ，合并空调中的报表、数据源模块
---
src/main/java/com/product/server/report/service/DataListReportService.java | 653 +++++++++++++++++++++++++++++-----------------------------
1 files changed, 324 insertions(+), 329 deletions(-)
diff --git a/src/main/java/com/product/server/report/service/DataListReportService.java b/src/main/java/com/product/server/report/service/DataListReportService.java
index ef59fc0..adc1c2b 100644
--- a/src/main/java/com/product/server/report/service/DataListReportService.java
+++ b/src/main/java/com/product/server/report/service/DataListReportService.java
@@ -1,5 +1,6 @@
package com.product.server.report.service;
+import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
@@ -18,15 +19,24 @@
import com.product.core.service.support.AbstractBaseService;
import com.product.core.service.support.QueryFilterService;
import com.product.core.spring.context.SpringMVCContextHolder;
+import com.product.module.data.service.SystemDataExportService;
import com.product.server.report.config.CmnConst;
import com.product.server.report.config.ReportCode;
import com.product.server.report.entity.ReportColumn;
import com.product.server.report.entity.ReportEntity;
+import com.product.server.report.util.QuerySqlParseUtil;
import com.product.util.BaseUtil;
import com.product.util.SystemParamReplace;
+import org.jsoup.Jsoup;
+import org.jsoup.nodes.Document;
+import org.jsoup.nodes.Element;
+import org.jsoup.select.Elements;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
+import javax.annotation.Resource;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
@@ -34,6 +44,7 @@
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
+import java.util.stream.Collectors;
/**
* Copyright 漏 6c
@@ -54,6 +65,77 @@
private QueryFilterService queryFilterService;
@Autowired
private RouterService routerService;
+ @Resource
+ private SystemDataExportService exportService;
+
+ public void outReportExcel(HttpServletResponse response, FieldSetEntity fse) throws BaseException, IOException {
+ ReportEntity reportEntity = getReport(fse);
+ List<List<ReportColumn>> reportHeader = reportEntity.getReportHeader();
+
+ List<List<String>> headerTemp = processingMergeData(reportHeader);
+ List<List<String>> header = headerTemp.get(0).stream().map(item -> new ArrayList<String>()).collect(Collectors.toList());
+ int size = headerTemp.get(0).size();
+ for (int i = 0; i < size; i++) {
+ for (int k = 0; k < headerTemp.size(); k++) {
+ header.get(i).add(headerTemp.get(k).get(i));
+ }
+ }
+
+ List<List<ReportColumn>> reportData = reportEntity.getReportData();
+ if (!CollectionUtil.isEmpty(reportEntity.getReportTail())) {
+ reportData.addAll(reportEntity.getReportTail());
+ }
+
+ List<List<String>> data = processingMergeData(reportData);
+ exportService.writeExcel(header, data, "娴嬭瘯", response);
+ }
+
+
+ private static List<List<String>> processingMergeData(List<List<ReportColumn>> data) {
+ if (CollectionUtil.isEmpty(data)) {
+ return null;
+ }
+ List<List<String>> resultData = data.stream().map(item -> new ArrayList<String>()).collect(Collectors.toList());
+ for (int i = 0; i < data.size(); i++) {
+ List<ReportColumn> columns = data.get(i);
+ for (ReportColumn column : columns) {
+ //璺ㄨ
+ int rowspan = column.getRowspanInt() < 1 ? 1 : column.getRowspanInt();
+ //璺ㄥ垪
+ int colspan = column.getColspanInt() < 1 ? 1 : column.getColspanInt();
+ List<String> currentCell = new ArrayList<>();
+ for (int k = 0; k < colspan; k++) {
+ currentCell.add(column.getContent(true));
+ }
+ for (int j = 0; j < rowspan; j++) {
+ resultData.get(j + i).addAll(currentCell);
+ }
+ }
+ }
+ return resultData;
+ }
+
+ public static void main(String[] args) {
+ List<List<ReportColumn>> data = new ArrayList<>();
+ List<ReportColumn> columns = new ArrayList<>();
+ ReportColumn column = new ReportColumn();
+ column.setRowspan(2);
+ column.setColspan(4);
+ column.setContent("璺ㄨ鏁帮細2锛岃法鍒楁暟锛�4");
+ columns.add(column);
+ data.add(columns);
+ columns = new ArrayList<>();
+ data.add(columns);
+ for (int i = 0; i < 3; i++) {
+ column = new ReportColumn();
+ column.setContent("璺ㄨ鏁帮細1锛岃法鍒楁暟锛�1,涓嬫爣:" + i);
+ columns.add(column);
+ }
+ processingMergeData(data);
+ }
+
+
+
/**
* 鍔犺浇鎵�鏈夌紦瀛�
@@ -134,10 +216,8 @@
if (preX != x || preY != y) {
fieldConfigObj = new JSONObject();
areaList.add(fieldConfigObj);
- } else if (areaList.size() > 0) {
- fieldConfigObj = areaList.get(areaList.size() - 1);
} else {
- continue;
+ fieldConfigObj = areaList.get(areaList.size() - 1);
}
fieldConfigObj.put(attrFse.getString(CmnConst.ATTR_NAME), attrFse.getString(CmnConst.ATTR_VALUE));
fieldConfigObj.put(CmnConst.ATTR_X, x);
@@ -258,10 +338,11 @@
*
* @return
*/
- public ReportEntity getReportEntity(FieldSetEntity fse) {
- ReportEntity report = null;
+ public ReportEntity getReport(FieldSetEntity fse) {
+ ReportEntity reportEntity = new ReportEntity();
String reportConfigUUID = fse.getUUID();
if (!StringUtils.isEmpty(fse.getString("~report_config_uuid~"))) {
+ //瀛愭姤琛ㄤ細浼犲叆姝ey浣滀负鎶ヨ〃鐨剈uid 瑙勯伩鍙傛暟涓湁uuid鐨刱ey
reportConfigUUID = fse.getString("~report_config_uuid~");
}
DataTableEntity reportConfigDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_REPORT_CONFIG, new String[]{reportConfigUUID});
@@ -330,177 +411,7 @@
FieldSetEntity reportSourceFse = reportSourceDte.getFieldSetEntity(0);
int curPage = fse.getInteger(CmnConst.CPAGE) == null ? 1 : fse.getInteger(CmnConst.CPAGE);
- JSONObject resultObj = new JSONObject();
- // 棣栨鍔犺浇锛岄粯璁ゆ坊鍔犳潯浠�
- Map<String, Map<String, String>> outerMap = Maps.newHashMap();
- if (fse.getBoolean(CmnConst.FIRST_LOAD)) {
- DataTableEntity allFilterDte = getDefaultSearchFilter(reportSourceFse.getUUID());
- Map<String, DataTableEntity> subMap = Maps.newHashMap();
- if (!DataTableEntity.isEmpty(allFilterDte)) {
- subMap.put("systemFieldMeta", allFilterDte);
- }
- DataTableEntity defaultFilterDte = new DataTableEntity();
-
- FieldSetEntity tempFse;
-
- Map<String, String> innerMap;
- for (int i = 0; i < allFilterDte.getRows(); i++) {
- tempFse = allFilterDte.getFieldSetEntity(i);
- if (StringUtils.isEmpty(tempFse.getString(CmnConst.LOGICAL_VALUE))) {
- continue;
- }
- defaultFilterDte.addFieldSetEntity(tempFse);
- innerMap = Maps.newHashMap();
- innerMap.put(CmnConst.LOGICAL_OPERATOR, tempFse.getString(CmnConst.LOGICAL_OPERATOR));
- innerMap.put(CmnConst.LOGICAL_VALUE, tempFse.getString(CmnConst.LOGICAL_VALUE));
- innerMap.put(CmnConst.FIELD_TYPE, tempFse.getString(CmnConst.FIELD_TYPE));
- outerMap.put(tempFse.getString(CmnConst.FIELD_NAME), innerMap);
- }
-// resultObj.put("filterInfo", outerMap);
-
- if (!DataTableEntity.isEmpty(defaultFilterDte)) {
- subMap.put("systemSeniorQueryString", defaultFilterDte);
- }
- fse.setSubData(subMap);
- }
-
- DataTableEntity recordDte = getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, null);
- // 鏄惁娣诲姞鎬诲悎璁�
- boolean totalStatisticsFlag = "1".equalsIgnoreCase(reportConfigFse.getString(CmnConst.LAST_TOTAL));
- String totalName = totalStatisticsFlag ? (StringUtils.isEmpty(reportConfigFse.getString(CmnConst.TOTAL_NAME)) ? "鎬昏" : reportConfigFse.getString(CmnConst.TOTAL_NAME)) : "";
-
-// JSONObject checkObj = checkRecordDte(recordDte);
-// if (CmnConst.FALSE.equals(checkObj.getString(CmnConst.RETURN_ATTR_RESULT))) {
-// return checkObj;
-// }
-
-// StringBuilder tableStyle = new StringBuilder(32);
-// String widthType = reportConfigFse.getString(CmnConst.REPORT_WIDTH_TYPE);
-// String width = reportConfigFse.getString(CmnConst.REPORT_WIDTH_VALUE);
-// if (!StringUtils.isEmpty(width)) {
-// if ("1".equals(widthType)) {
-// tableStyle.append(" style=\"width:").append(width).append("%\"");
-// } else if ("0".equals(widthType)) {
-// tableStyle.append(" style=\"width:").append(width).append("px\"");
-// }
-// }
-
- if (CmnConst.REPORT_TYPE_COMMON.equals(reportType)) {
- report = commonReportService.getReportEntity(recordDte, totalName, reportConfigMap);
- //TODO
- report.setFilterInfo(outerMap);
- } else if (CmnConst.REPORT_TYPE_GROUP.equals(reportType)) {
- // 鐗规畩澶勭悊棰濆鏌ヨ鍐呭
- recordDte.addFieldSetEntity(getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, reportConfigMap));
-// resultObj.putAll(groupReportService.getReport(recordDte, totalName, reportConfigMap, tableStyle));
- }
- //TODO
-
-// report.setFilterInfo(outerMap);
- if (CmnConst.FALSE.equals(resultObj.getString(CmnConst.RETURN_ATTR_RESULT))) {
- resultObj.put(CmnConst.RETURN_ATTR_RESULT, true);
- resultObj.put(CmnConst.RETURN_ATTR_MESSAGE, "鑾峰彇鎶ヨ〃澶辫触锛�");
- } else {
- // 鍒嗛〉鍙傛暟
- SQLEntity sqlEntity = recordDte.getSqle();
- if ("1".equals(reportConfigFse.getString(CmnConst.IS_PAGE)) && sqlEntity != null) {
- report.setPage(true);
- report.setTotalPage(sqlEntity.getTotalpage());
- report.setPageSize(StringUtils.isEmpty(reportConfigFse.getString(CmnConst.PAGE_SIZE)) ? 0 : reportConfigFse.getInteger(CmnConst.PAGE_SIZE));
- report.setPageIndex(curPage);
-// resultObj.put(CmnConst.IS_PAGE, 1);
-// resultObj.put(CmnConst.CPAGE, curPage);
-// resultObj.put("totalCount", sqlEntity.getTotalCount());
-// resultObj.put("totalpage", sqlEntity.getTotalpage());
-// resultObj.put("pagesize", StringUtils.isEmpty(reportConfigFse.getString(CmnConst.PAGE_SIZE)) ? 0 : reportConfigFse.getInteger(CmnConst.PAGE_SIZE));
- }
- }
-// if (!DataTableEntity.isEmpty(recordDte)) {
-// resultObj.put("current_page_count", recordDte.getRows());
-// }
- report.setSystemFieldMeta(getSearchInfo(reportSourceFse.getUUID()));
- report.setReportType(reportConfigFse.getString("type_uuid"));
-// resultObj.put("systemFieldMeta", );
- resultObj.put("report_type", reportConfigFse.getString("type_uuid"));
-// System.out.println(resultObj.getString("html"));
-
- return report;
- }
-
- /**
- * 鑾峰彇鎶ヨ〃
- *
- * @return
- */
- public JSONObject getReport(FieldSetEntity fse) {
- String reportConfigUUID = fse.getUUID();
- DataTableEntity reportConfigDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_REPORT_CONFIG, new String[]{reportConfigUUID});
- if (BaseUtil.dataTableIsEmpty(reportConfigDte)) {
- throw new BaseException(ReportCode.GET_CACHE_FIAL.getValue(), ReportCode.GET_CACHE_FIAL.getText() + ":" + CmnConst.CACHE_REPORT_CONFIG);
- }
- FieldSetEntity reportConfigFse = reportConfigDte.getFieldSetEntity(0);
- if (!"1".equals(reportConfigFse.getString(CmnConst.IS_VALID))) {
- throw new BaseException(ReportCode.INVALID_REPORT.getValue(), ReportCode.INVALID_REPORT.getText());
- }
-
- DataTableEntity reportTypeDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_REPORT_TYPE, new String[]{reportConfigFse.getString(CmnConst.TYPE_UUID)});
- if (BaseUtil.dataTableIsEmpty(reportTypeDte)) {
- throw new BaseException(ReportCode.GET_CACHE_FIAL.getValue(), ReportCode.GET_CACHE_FIAL.getText() + ":" + CmnConst.CACHE_REPORT_TYPE);
- }
- FieldSetEntity reportTypeFse = reportTypeDte.getFieldSetEntity(0);
-
- StringBuilder sort = new StringBuilder(128);
- // 鑾峰彇鎶ヨ〃缂撳瓨淇℃伅
- Map<Integer, List<JSONObject>> reportConfigMap = getReportConfig(reportConfigUUID, sort);
- // 鏍规嵁鍏蜂綋鐨刦unction_uuid鍜宐utton_uuid鑾峰彇瀵瑰簲鐨勮矾鐢憋紝鎷煎噾url
- String url;
- JSONObject valueObj;
- for (Map.Entry<Integer, List<JSONObject>> entry : reportConfigMap.entrySet()) {
- if (entry.getValue() != null) {
- for (JSONObject tempValueObj : entry.getValue()) {
- if (entry.getValue() != null) {
- for (Map.Entry<String, Object> innerEntry : tempValueObj.entrySet()) {
- if (innerEntry.getValue() instanceof JSONObject) {
- valueObj = (JSONObject) innerEntry.getValue();
- if (!StringUtils.isEmpty(valueObj.getString(CmnConst.SUB_REPORT))) {
- url = valueObj.getString(CmnConst.SUB_REPORT);
- if (!StringUtils.isEmpty(valueObj.getString(CmnConst.ATTR_URL_PARAM))) {
- url += "?" + valueObj.getString(CmnConst.ATTR_URL_PARAM);
- }
- valueObj.put(CmnConst.ATTR_URL, url);
- valueObj.put("~isSubReport~", true);
- } else {
- if (!StringUtils.isEmpty(valueObj.getString(CmnConst.FUNCTION_UUID)) && !StringUtils.isEmpty(valueObj.getString(CmnConst.BUTTON_UUID))) {
- url = routerService.functionSkipByButtonUuid(valueObj.getString(CmnConst.FUNCTION_UUID), valueObj.getString(CmnConst.BUTTON_UUID));
- if (StringUtils.isEmpty(url)) {
- valueObj.remove(CmnConst.ATTR_URL);
- } else {
- if (!StringUtils.isEmpty(valueObj.getString(CmnConst.ATTR_URL_PARAM))) {
- url += "?" + valueObj.getString(CmnConst.ATTR_URL_PARAM);
- }
- valueObj.put(CmnConst.ATTR_URL, url);
- }
- } else {
- valueObj.remove(CmnConst.ATTR_URL);
- }
- }
- }
- }
- }
- }
- }
- }
- // 鑾峰彇鎶ヨ〃绫诲瀷
- String reportType = reportTypeFse.getString(CmnConst.TYPE_NAME);
- // 鑾峰彇鏁版嵁婧�
- DataTableEntity reportSourceDte = DataPoolCacheImpl.getInstance().getCacheData(CmnConst.CACHE_REPORT_DATASOURCE_CONFIG, new String[]{reportConfigFse.getString(CmnConst.DATASOURCE_UUID)});
- if (BaseUtil.dataTableIsEmpty(reportSourceDte)) {
- throw new BaseException(ReportCode.GET_REPORT_DATASOURCE_FIAL.getValue(), ReportCode.GET_REPORT_DATASOURCE_FIAL.getText());
- }
- FieldSetEntity reportSourceFse = reportSourceDte.getFieldSetEntity(0);
- int curPage = fse.getInteger(CmnConst.CPAGE) == null ? 1 : fse.getInteger(CmnConst.CPAGE);
-
- JSONObject resultObj = new JSONObject();
+// JSONObject resultObj = new JSONObject();
// 棣栨鍔犺浇锛岄粯璁ゆ坊鍔犳潯浠�
if (fse.getBoolean(CmnConst.FIRST_LOAD)) {
DataTableEntity allFilterDte = getDefaultSearchFilter(reportSourceFse.getUUID());
@@ -525,67 +436,69 @@
innerMap.put(CmnConst.FIELD_TYPE, tempFse.getString(CmnConst.FIELD_TYPE));
outerMap.put(tempFse.getString(CmnConst.FIELD_NAME), innerMap);
}
- resultObj.put("filterInfo", outerMap);
-
+// resultObj.put("filterInfo", outerMap);
+ reportEntity.setFilterInfo(outerMap);
if (!DataTableEntity.isEmpty(defaultFilterDte)) {
subMap.put("systemSeniorQueryString", defaultFilterDte);
}
fse.setSubData(subMap);
}
- DataTableEntity recordDte = getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, null);
+ List<String> unpivotInfoContainer = Lists.newArrayList();
+ DataTableEntity recordDte = getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, null, unpivotInfoContainer);
// 鏄惁娣诲姞鎬诲悎璁�
boolean totalStatisticsFlag = "1".equalsIgnoreCase(reportConfigFse.getString(CmnConst.LAST_TOTAL));
String totalName = totalStatisticsFlag ? (StringUtils.isEmpty(reportConfigFse.getString(CmnConst.TOTAL_NAME)) ? "鎬昏" : reportConfigFse.getString(CmnConst.TOTAL_NAME)) : "";
-// JSONObject checkObj = checkRecordDte(recordDte);
-// if (CmnConst.FALSE.equals(checkObj.getString(CmnConst.RETURN_ATTR_RESULT))) {
-// return checkObj;
-// }
-
- StringBuilder tableStyle = new StringBuilder(32);
- String widthType = reportConfigFse.getString(CmnConst.REPORT_WIDTH_TYPE);
- String width = reportConfigFse.getString(CmnConst.REPORT_WIDTH_VALUE);
- if (!StringUtils.isEmpty(width)) {
- if ("1".equals(widthType)) {
- tableStyle.append(" style=\"width:").append(width).append("%\"");
- } else if ("0".equals(widthType)) {
- tableStyle.append(" style=\"width:").append(width).append("px\"");
- }
- }
-
+// StringBuilder tableStyle = new StringBuilder(32);
+// String widthType = reportConfigFse.getString(CmnConst.REPORT_WIDTH_TYPE);
+// String width = reportConfigFse.getString(CmnConst.REPORT_WIDTH_VALUE);
+// if (!StringUtils.isEmpty(width)) {
+// if ("1".equals(widthType)) {
+// tableStyle.append(" style=\"width:").append(width).append("%\"");
+// } else if ("0".equals(widthType)) {
+// tableStyle.append(" style=\"width:").append(width).append("px\"");
+// }
+// }
+ List<List<ReportColumn>>[] report = null;
if (CmnConst.REPORT_TYPE_COMMON.equals(reportType)) {
- resultObj.putAll(commonReportService.getReport(recordDte, totalName, reportConfigMap, tableStyle));
+ report = commonReportService.getReport(recordDte, totalName, reportConfigMap, unpivotInfoContainer);
+
} else if (CmnConst.REPORT_TYPE_GROUP.equals(reportType)) {
// 鐗规畩澶勭悊棰濆鏌ヨ鍐呭
- recordDte.addFieldSetEntity(getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, reportConfigMap));
- resultObj.putAll(groupReportService.getReport(recordDte, totalName, reportConfigMap, tableStyle));
+ recordDte.addFieldSetEntity(getRecordDte(sort, curPage, reportSourceFse, fse, reportConfigFse, reportConfigMap, null));
+ report = groupReportService.getReport(recordDte, totalName, reportConfigMap);
+ }
+ if (report != null) {
+ List<List<ReportColumn>> head = report[0];
+ List<List<ReportColumn>> data = report[1];
+ List<List<ReportColumn>> tail = report[2];
+ reportEntity.setReportHeader(head);
+ reportEntity.setReportData(data);
+ reportEntity.setReportTail(tail);
}
- if (CmnConst.FALSE.equals(resultObj.getString(CmnConst.RETURN_ATTR_RESULT))) {
- resultObj.put(CmnConst.RETURN_ATTR_RESULT, true);
- resultObj.put(CmnConst.RETURN_ATTR_MESSAGE, "鑾峰彇鎶ヨ〃澶辫触锛�");
+
+ reportEntity.setReportConfigUid(reportConfigFse.getUUID());
+ reportEntity.setReportName(reportConfigFse.getString(CmnConst.REPORT_NAME));
+
+ // 鍒嗛〉鍙傛暟
+ SQLEntity sqlEntity = recordDte.getSqle();
+ if ("1".equals(reportConfigFse.getString(CmnConst.IS_PAGE)) && sqlEntity != null) {
+ reportEntity.setPage(true);
+ reportEntity.setPageIndex(curPage);
+ reportEntity.setTotalCount(sqlEntity.getTotalCount());
+ reportEntity.setPageSize(StringUtils.isEmpty(reportConfigFse.getString(CmnConst.PAGE_SIZE)) ? 0 : reportConfigFse.getInteger(CmnConst.PAGE_SIZE));
} else {
- // 鍒嗛〉鍙傛暟
- SQLEntity sqlEntity = recordDte.getSqle();
- if ("1".equals(reportConfigFse.getString(CmnConst.IS_PAGE)) && sqlEntity != null) {
- resultObj.put(CmnConst.IS_PAGE, 1);
- resultObj.put(CmnConst.CPAGE, curPage);
- resultObj.put("totalCount", sqlEntity.getTotalCount());
- resultObj.put("totalpage", sqlEntity.getTotalpage());
- resultObj.put("pagesize", StringUtils.isEmpty(reportConfigFse.getString(CmnConst.PAGE_SIZE)) ? 0 : reportConfigFse.getInteger(CmnConst.PAGE_SIZE));
- } else {
- resultObj.put(CmnConst.IS_PAGE, 0);
- }
+ reportEntity.setPage(false);
}
- if (!DataTableEntity.isEmpty(recordDte)) {
- resultObj.put("current_page_count", recordDte.getRows());
- }
- resultObj.put("systemFieldMeta", getSearchInfo(reportSourceFse.getUUID()));
- resultObj.put("report_type", reportConfigFse.getString("type_uuid"));
-// System.out.println(resultObj.getString("html"));
-
- return resultObj;
+// if (!DataTableEntity.isEmpty(recordDte)) {
+// resultObj.put("current_page_count", recordDte.getRows());
+//
+// }
+ reportEntity.setReportType(reportConfigFse.getString("type_uuid"));
+ reportEntity.setSystemFieldMeta(getSearchInfo(reportSourceFse.getUUID()));
+ return reportEntity;
}
/**
@@ -628,10 +541,17 @@
* @param reportConfigFse
* @return
*/
- private DataTableEntity getRecordDte(StringBuilder sort, int curPage, FieldSetEntity reportSourceFse, FieldSetEntity fse, FieldSetEntity reportConfigFse, Map<Integer, List<JSONObject>> reportConfigMap) {
+ private DataTableEntity getRecordDte(StringBuilder sort, int curPage, FieldSetEntity reportSourceFse, FieldSetEntity fse, FieldSetEntity reportConfigFse, Map<Integer, List<JSONObject>> reportConfigMap, Collection<String> unpivotInfoContainer) {
boolean spTimeFlag = reportConfigMap != null;
String sql;
String sqlText = reportSourceFse.getString(CmnConst.SQL_TEXT);
+ Map<String, List<String>> queryFilterMap = queryFilterService.getQueryFilterMore(fse);
+ // [=table_name=]鍐欐硶澶勭悊鈥斺�旀浛鎹负瀵瑰簲鐨剋ith璇彞,0-杩戞湡锛�1-鎵�鏈�
+ int year = queryFilterMap == null || queryFilterMap.get("query_time") == null || "0".equals(queryFilterMap.get("query_time").get(0)) ? 2 : 0;
+ sqlText = QuerySqlParseUtil.parseSplitTableSql(baseDao, sqlText, year);
+ // {==}鍐欐硶澶勭悊-鏇挎崲涓哄搴旂殑琛屽垪杞崲璇彞
+ sqlText = dealUnpivot(sqlText, queryFilterMap, unpivotInfoContainer);
+
DataTableEntity recordDte = new DataTableEntity();
if (spTimeFlag) {
@@ -641,7 +561,6 @@
Set<String> spTimeStatisticsTypeSet = Sets.newHashSet();
spTimeStatisticsTypeSet.addAll(getFieldAndSpTimeStatisticsTypeSet(baseFieldSet, spTimeStatisticsFieldMap, reportConfigMap.get(0)));
spTimeStatisticsTypeSet.addAll(getFieldAndSpTimeStatisticsTypeSet(baseFieldSet, spTimeStatisticsFieldMap, reportConfigMap.get(2)));
- Map<String, List<String>> queryFilterMap = queryFilterService.getQueryFilterMore(fse);
Set<String> selectedFieldSet;
String headContent;
DataTableEntity tempDte;
@@ -726,6 +645,154 @@
}
/**
+ * 瀛愭柟娉�-澶勭悊琛屽垪杞崲鐨勮鍙�
+ *
+ * @param sql 寰呭鐞嗙殑sql
+ * @param queryFilterMap 杩囨护鏉′欢map
+ * @param unpivotInfoContainer 琛屽垪杞崲瀹瑰櫒锛岃鏂规硶浼氬皢澶勭悊濂戒簡鐨勫唴瀹圭洿鎺ユ斁鍏ヨ瀹瑰櫒涓�
+ * @return
+ */
+ private String dealUnpivot(String sql, Map<String, List<String>> queryFilterMap, Collection<String> unpivotInfoContainer) {
+ String regexp = "\\{=.+=\\}";
+ List<String> suitList = getSuitContent(sql, regexp);
+ String tempStr;
+ List<String> fieldInfoList;
+ String[] fieldArr;
+ String filter;
+ String startTimeStr;
+ String finalTimeStr;
+ int beginIndex;
+ SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ List<String> timeList;
+ for (String content : suitList) {
+ tempStr = content.substring(2, content.length() - 2).trim();
+ fieldInfoList = getSuitContent(tempStr, "\\{\\$((?!\\{\\$).)+\\$\\}");
+ for (String fieldInfo : fieldInfoList) {
+ fieldArr = fieldInfo.substring(2, fieldInfo.length() - 2).trim().split(" ");
+ if (fieldArr.length != 3) {
+ continue;
+ }
+ if ("date".equalsIgnoreCase(fieldArr[1])) {
+ if (queryFilterMap == null || queryFilterMap.isEmpty()) {
+ return sql.replaceAll(regexp, "");
+ }
+ filter = queryFilterMap.get(fieldArr[0]).get(0);
+ beginIndex = filter.indexOf("str_to_date('") + "str_to_date('".length();
+ startTimeStr = filter.substring(beginIndex, beginIndex + 19);
+ beginIndex = filter.lastIndexOf("str_to_date('") + "str_to_date('".length();
+ finalTimeStr = filter.substring(beginIndex, beginIndex + 19);
+ try {
+ timeList = enumTime(dateFormat.parse(startTimeStr), dateFormat.parse(finalTimeStr), fieldArr[2]);
+ } catch (Exception e) {
+ SpringMVCContextHolder.getSystemLogger().error("transfer error: " + filter);
+ timeList = Lists.newArrayList();
+ }
+ unpivotInfoContainer.addAll(timeList);
+ tempStr = joinSql(timeList, tempStr, fieldInfo);
+ } else if ("prompt".equalsIgnoreCase(fieldArr[1])) {
+ String promptName = fieldArr[2];
+ DataTableEntity dte;
+ StringBuilder promptSql = new StringBuilder();
+ FieldSetEntity fse;
+ if (promptName.startsWith("銆�")) {
+ promptSql.append("select dict_label show_value,dict_value real_value from product_sys_dict where is_used=1 and dict_name=?");
+ if (queryFilterMap != null && !queryFilterMap.isEmpty() && queryFilterMap.get(fieldArr[0]) != null && !StringUtils.isEmpty(queryFilterMap.get(fieldArr[0]).get(1))) {
+ promptSql.append(" and (dict_value").append(queryFilterMap.get(fieldArr[0]).get(1)).append(")");
+ }
+ dte = baseDao.listTable(promptSql.toString(), new Object[]{promptName.substring(1, promptName.length() - 1)});
+ } else {
+ fse = baseDao.getFieldSetEntityByFilter("product_sys_prompt", "prompt_name=?", new Object[]{promptName}, false);
+ promptSql.append("select ").append(fse.getString("view_fields")).append(" show_value,").append(fse.getString("value_field")).append(" real_value from ").append(fse.getString("source_table"));
+ if (!StringUtils.isEmpty(fse.getString("filter"))) {
+ promptSql.append(" where (").append(fse.getString("filter")).append(")");
+ }
+ if (queryFilterMap != null && !queryFilterMap.isEmpty() && queryFilterMap.get(fieldArr[0]) != null && !StringUtils.isEmpty(queryFilterMap.get(fieldArr[0]).get(1))) {
+ if (!StringUtils.isEmpty(fse.getString("filter"))) {
+ promptSql.append(" and");
+ } else {
+ promptSql.append(" where");
+ }
+ promptSql.append(" (").append(fse.getString("value_field")).append(queryFilterMap.get(fieldArr[0]).get(1)).append(")");
+ }
+ dte = baseDao.listTable(promptSql.toString(), new Object[]{});
+ }
+ for (int i = 0; i < dte.getRows(); i++) {
+ fse = dte.getFieldSetEntity(i);
+ unpivotInfoContainer.add(fse.getString("show_value"));
+ }
+ tempStr = joinSql(dte, tempStr, fieldInfo);
+ }
+ }
+ sql = sql.replace(content, tempStr.contains("{$") ? "" : tempStr);
+ }
+ return sql;
+ }
+
+ private String joinSql(Collection<String> collection, String unpivotContent, String fieldInfo) {
+ StringBuilder unpivotSb = new StringBuilder(128);
+ for (String singleStr : collection) {
+ if (unpivotSb.length() > 0) {
+ unpivotSb.append("\n ");
+ }
+ unpivotSb.append(unpivotContent.replace(fieldInfo, singleStr)).append(" '").append(singleStr).append("'");
+ }
+ return unpivotSb.toString();
+ }
+
+ private String joinSql(DataTableEntity dte, String unpivotContent, String fieldInfo) {
+ StringBuilder unpivotSb = new StringBuilder(128);
+ for (int i = 0; i < dte.getRows(); i++) {
+ FieldSetEntity fse = dte.getFieldSetEntity(i);
+ if (unpivotSb.length() > 0) {
+ unpivotSb.append("\n ");
+ }
+ unpivotSb.append(unpivotContent.replace(fieldInfo, fse.getString("real_value"))).append(" '").append(fse.getString("show_value")).append("'");
+ }
+ return unpivotSb.toString();
+ }
+
+ /**
+ * 鎸夌収鏃堕棿姝ラ暱鏋氫妇鏃堕棿鑼冨洿涔嬪唴鐨勬椂闂�
+ *
+ * @param startTime
+ * @param finalTime
+ * @param formatInfo
+ * @return
+ */
+ public static List<String> enumTime(Date startTime, Date finalTime, String formatInfo) {
+ Calendar c1 = Calendar.getInstance();
+ Calendar c2 = Calendar.getInstance();
+ c1.setTime(startTime);
+ c2.setTime(finalTime);
+ int step = 1;
+ String format = formatInfo;
+ if (formatInfo.contains("[")) {
+ step = Integer.parseInt(formatInfo.substring(formatInfo.indexOf("[") + 1, formatInfo.indexOf("]")));
+ format = formatInfo.substring(0, formatInfo.indexOf("["));
+ }
+ SimpleDateFormat dateFormat = new SimpleDateFormat(format);
+ List<String> timeList = Lists.newArrayList();
+ if (formatInfo.contains("H")) {
+ enumTime(c1, c2, timeList, dateFormat, step, Calendar.HOUR);
+ } else if (formatInfo.contains("d")) {
+ enumTime(c1, c2, timeList, dateFormat, step, Calendar.DATE);
+ } else if (formatInfo.contains("M")) {
+ enumTime(c1, c2, timeList, dateFormat, step, Calendar.MONTH);
+ } else if (formatInfo.contains("y")) {
+ enumTime(c1, c2, timeList, dateFormat, step, Calendar.YEAR);
+ }
+ return timeList;
+ }
+
+ private static void enumTime(Calendar c1, Calendar c2, List<String> timeList, SimpleDateFormat dateFormat, int step, int param) {
+ if (c1.compareTo(c2) <= 0) {
+ timeList.add(dateFormat.format(c1.getTime()));
+ c1.add(param, step);
+ enumTime(c1, c2, timeList, dateFormat, step, param);
+ }
+ }
+
+ /**
* 瀛愭柟娉�-getRecordDte-鍔犺浇鍙傜収
*
* @param recordDte
@@ -768,6 +835,8 @@
*/
private String replaceSqlContent(String sqlText, FieldSetEntity fse, StringBuilder sort, FieldSetEntity reportConfigFse, int curPage, String selectedFields, Map<String, List<String>> queryFilterMap) {
queryFilterMap = queryFilterMap == null ? queryFilterService.getQueryFilterMore(fse) : queryFilterMap;
+ // 澶勭悊鎺夎繎鏈熷拰鎵�鏈夌殑杩囨护鏉′欢
+ queryFilterMap.remove("query_time");
selectedFields = StringUtils.isEmpty(selectedFields) ? "*" : selectedFields;
// 鏇挎崲sql涓殑绯荤粺鍙傛暟
sqlText = SystemParamReplace.replaceParams(sqlText, fse);
@@ -812,7 +881,7 @@
}
}
// 鍓旈櫎寮傚父鍖哄煙
- sqlText = sqlText.replaceAll(CmnConst.REGEXP_FILTER_AREA, "").replaceAll(CmnConst.REGEXP_ALL_TYPE, "null").replaceAll(CmnConst.REGEXP_ALWAYS_TRUE, "");
+ sqlText = sqlText.replaceAll(CmnConst.REGEXP_FILTER_AREA, "").replaceAll(CmnConst.REGEXP_ALL_TYPE, "null").replaceAll(CmnConst.REGEXP_ALWAYS_TRUE, "").replaceAll("(\\t*\\n){2,}", "\n");
String queryFilter = "";
if (signCount > 0) {
for (Map.Entry<String, List<String>> entry : residueQueryFilterMap.entrySet()) {
@@ -825,7 +894,7 @@
queryFilter += " (" + entry.getValue().get(2) + ") ";
}
} else {
- queryFilter = queryFilterMap.isEmpty() ? queryFilterService.getQueryFilter(fse) : queryFilterMap.get(CmnConst.SYSTEM_SENIOR_QUERY_STRING).get(0);
+ queryFilter = queryFilterMap.isEmpty() || queryFilterMap.size() == 1 ? queryFilterService.getQueryFilter(fse) : queryFilterMap.get(CmnConst.SYSTEM_SENIOR_QUERY_STRING).get(0);
}
StringBuilder sql = new StringBuilder(1024);
@@ -889,9 +958,17 @@
// 涓婃湡
spDealFilterForPrePeriod(tempQueryFilterMap, queryFilterMap, curField);
}
+ filter = tempQueryFilterMap.get(curField).get(num);
+ suitContent = suitContent.replace(innerSuitContent, filter);
+ } else if ("split".equals(spStatisticsWay)) {
+ // 鍙槸鎷嗗垎骞舵彁鍙栧尯鍩熺殑鍊硷紝鍙湁鑾峰彇鑼冨洿鐨勫�肩殑鏃跺�欏彲浠ヨ繖鏍锋搷浣�
+ List<String> paramList = tempQueryFilterMap.get(curField);
+ String[] timeArr = paramList.get(0).split("~");
+ suitContent = suitContent.replace(innerSuitContent, timeArr[Integer.parseInt(spStatisticsWayValue)]);
+ } else {
+ filter = tempQueryFilterMap.get(curField).get(num);
+ suitContent = suitContent.replace(innerSuitContent, filter);
}
- filter = tempQueryFilterMap.get(curField).get(num);
- suitContent = suitContent.replace(innerSuitContent, filter);
residueQueryFilterMap.remove(curField);
if (getSuitContent(suitContent, CmnConst.REGEXP_ALL_TYPE).isEmpty() && suitContent.matches(CmnConst.REGEXP_FILTER_AREA)) {
suitContent = suitContent.substring(2, suitContent.length() - 2);
@@ -1069,6 +1146,7 @@
tempObj.put("table_name", reportSourceFieldFse.getString(CmnConst.TABLE_NAME));
tempObj.put(CmnConst.SEARCH_TYPE, reportSourceFieldFse.getObject(CmnConst.SEARCH_TYPE));
tempObj.put("is_multiple", "2".equals(reportSourceFieldFse.getString(CmnConst.SEARCH_TYPE)));
+ tempObj.put("field_prompt_filter", reportSourceFieldFse.getString("field_prompt_filter"));
}
}
}
@@ -1133,7 +1211,8 @@
* @param locationType 浣嶇疆绫诲瀷锛宧ead-澶撮儴锛宼ail-灏鹃儴
* @return
*/
- public StringBuilder getTitleHtml(List<JSONObject> list, int totalColCount, Map<String, Set<String>> headAndTailTitleDataMap, String locationType) {
+ public List<List<ReportColumn>> getTitleRows(List<JSONObject> list, int totalColCount, Map<String, Set<String>> headAndTailTitleDataMap, String locationType) {
+ List<List<ReportColumn>> reportColumnList = new ArrayList<>();
StringBuilder html = new StringBuilder(1024);
int colspan;
int preRow = 0;
@@ -1141,7 +1220,6 @@
int curRow;
int curCol;
String value;
- String style;
for (JSONObject singleObj : list) {
curRow = singleObj.getIntValue(CmnConst.ATTR_Y);
curCol = singleObj.getIntValue(CmnConst.ATTR_X);
@@ -1149,15 +1227,6 @@
colspan = colspan < 0 ? totalColCount : Math.max(1, colspan);
value = replaceFormDataAndSysData(singleObj.getString(CmnConst.ATTR_SHOW_NAME), headAndTailTitleDataMap, singleObj);
value = value == null ? "" : value;
- if ("1".equals(singleObj.getString(CmnConst.ATTR_IS_TITLE))) {
- style = " class=\"" + CmnConst.CLASS_TR_REPORT_TITLE + "\"";
- } else {
- if ("head".equals(locationType)) {
- style = " class=\"" + CmnConst.CLASS_TR_HEAD + "\"";
- } else {
- style = " class=\"" + CmnConst.CLASS_TR_TAIL + "\"";
- }
- }
if (preRow < curRow) {
if (preRow == 0) {
html.append("\n<tr ");
@@ -1165,7 +1234,7 @@
html.append(getAimNumTdPlaceholder(totalColCount - preCol, 1));
html.append("\n</tr>\n<tr ");
}
- html.append(style).append(">\n ");
+ html.append(">\n ");
if (preRow == 0 && preCol == 0 && preCol < curCol - 1) {
html.append(getAimNumTdPlaceholder(curCol - preCol - 1, 1));
}
@@ -1189,75 +1258,25 @@
}
html.append(getAimNumTdPlaceholder(totalColCount - preCol, 1));
html.append("\n</tr>");
- return html;
- }
-
- /**
- * 鎶ヨ〃-瑙ｆ瀽-鑾峰彇澶撮儴鎴栬�呭熬閮ㄦ爣棰楬tml
- *
- * @param list 鎶ヨ〃閰嶇疆淇℃伅锛岀紦瀛榣ist
- * @param totalColCount 鎬诲垪鏁�
- * @param headAndTailTitleDataMap 澶撮儴銆佸熬閮ㄦ爣棰樺尯鏁版嵁瀛楁map
- * @param locationType 浣嶇疆绫诲瀷锛宧ead-澶撮儴锛宼ail-灏鹃儴
- * @return
- */
- public List<List<ReportColumn>> getTitle(List<JSONObject> list, int totalColCount, Map<String, Set<String>> headAndTailTitleDataMap, String locationType) {
- List<List<ReportColumn>> row = new ArrayList<>();
- StringBuilder html = new StringBuilder(1024);
- int colspan;
- int preRow = 0;
- int preCol = 0;
- int curRow;
- int curCol;
- String value;
- String style;
-
- List<ReportColumn> reportColumns = new ArrayList<>();
- for (JSONObject singleObj : list) {
- curRow = singleObj.getIntValue(CmnConst.ATTR_Y);
- curCol = singleObj.getIntValue(CmnConst.ATTR_X);
- colspan = singleObj.getIntValue(CmnConst.ATTR_COLSPAN);
- colspan = colspan < 0 ? totalColCount : Math.max(1, colspan);
- value = replaceFormDataAndSysData(singleObj.getString(CmnConst.ATTR_SHOW_NAME), headAndTailTitleDataMap, singleObj);
- value = value == null ? "" : value;
- if ("1".equals(singleObj.getString(CmnConst.ATTR_IS_TITLE))) {
- style = " class=\"" + CmnConst.CLASS_TR_REPORT_TITLE + "\"";
- } else {
- if ("head".equals(locationType)) {
- style = " class=\"" + CmnConst.CLASS_TR_HEAD + "\"";
- } else {
- style = " class=\"" + CmnConst.CLASS_TR_TAIL + "\"";
- }
+ // 瑙ｆ瀽html
+ Document doc = Jsoup.parse(html.toString());
+ Elements trs = doc.select("tr");
+ Elements tds;
+ List<ReportColumn> reportColumn;
+ ReportColumn column;
+ for (Element tr : trs) {
+ tds = tr.select("td");
+ reportColumn = new ArrayList<>();
+ for (Element td : tds) {
+ column = new ReportColumn();
+ column.setColspan(Integer.parseInt(BaseUtil.ifNull(td.attr("colspan"), "1")));
+ column.setRowspan(Integer.parseInt(BaseUtil.ifNull(td.attr("rowspan"), "1")));
+ column.setContent(td.text());
+ reportColumn.add(column);
}
- if (preRow < curRow) {
- if (preRow != 0) {
- row.add(getAimNumTdPlaceholderColumn(totalColCount - preCol, 1));
- }
- if (preRow == 0 && preCol == 0 && preCol < curCol - 1) {
- row.add(getAimNumTdPlaceholderColumn(curCol - preCol - 1, 1));
- }
- preCol = curCol;
- }
- if (preCol < curCol - 1) {
- row.add(getAimNumTdPlaceholderColumn(curCol - preCol - 1, 1));
- }
- if ("1".equals(singleObj.getString(CmnConst.ATTR_IS_TITLE))) {
- colspan = totalColCount;
- }
- ReportColumn column = new ReportColumn();
- column.setColspan(colspan);
- column.setContent(value);
- reportColumns.add(column);
- preRow = curRow;
- preCol = curCol + singleObj.getIntValue(CmnConst.ATTR_COLSPAN) - 1;
- if ("1".equals(singleObj.getString(CmnConst.ATTR_IS_TITLE))) {
- preCol = totalColCount;
- }
+ reportColumnList.add(reportColumn);
}
- row.add(reportColumns);
- reportColumns.addAll(getAimNumTdPlaceholderColumn(totalColCount - preCol, 1));
-// row.add();
- return row;
+ return reportColumnList;
}
/**
@@ -1736,30 +1755,6 @@
public List<String> getSuitContent(String str, String regexp) {
return getSuitContent(str, regexp, "");
- }
-
- /**
- * 鑾峰彇鎸囧畾涓暟鐨則d鍗犱綅
- *
- * @param num
- * @return
- */
- public List<ReportColumn> getAimNumTdPlaceholderColumn(int num, int type) {
- List<ReportColumn> columns = new ArrayList<>();
- if (num <= 0) {
- return columns;
- }
- if (type == 0) {
- for (int i = 0; i < num; i++) {
- columns.add(new ReportColumn());
- }
- } else {
-
- ReportColumn column = new ReportColumn();
- column.setColspan(num);
- columns.add(column);
- }
- return columns;
}
/**
--
Gitblit v1.9.2