From bbe7acfbe5a4c08d6edc91eaf81dcecf9d630e18 Mon Sep 17 00:00:00 2001
From: 潘志宝 <979469083@qq.com>
Date: 星期日, 15 十二月 2024 15:10:50 +0800
Subject: [PATCH] collectQuality
---
iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java | 283 +++++++++++++++++++++++++++++++++++++++++++++----------
1 files changed, 229 insertions(+), 54 deletions(-)
diff --git a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
index 16e866e..4636904 100644
--- a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
+++ b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java
@@ -1,16 +1,42 @@
package com.iailab.module.data.ind.collection.utils;
import com.iailab.framework.common.util.date.DateUtils;
-import com.iailab.module.data.ind.common.IndTimeRangeConst;
+import com.iailab.module.data.common.enums.IndStatFuncEnum;
+import com.iailab.module.data.common.enums.IndTimeLimitEnum;
+import com.iailab.module.data.ind.data.dto.IndDataSetDTO;
+import com.iailab.module.data.ind.data.service.IndDataSetFieldService;
+import com.iailab.module.data.ind.data.service.IndDataSetService;
+import com.iailab.module.data.ind.item.service.IndItemAtomService;
+import com.iailab.module.data.ind.item.service.IndItemDerService;
+import com.iailab.module.data.ind.item.vo.IndItemAtomVO;
+import com.iailab.module.data.ind.item.vo.IndItemDerVO;
+import com.iailab.module.data.ind.value.dto.QuerySourceValueDTO;
+import org.apache.commons.lang3.StringUtils;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Component;
import java.util.Calendar;
+import java.util.Date;
/**
* @author PanZhibao
* @Description
- * @createTime 2024年05月25日
+ * @createTime 2024年10月04日
*/
+@Component
public class IndSqlUtils {
+
+ @Autowired
+ private IndItemAtomService indItemAtomService;
+
+ @Autowired
+ private IndDataSetService indDataSetService;
+
+ @Autowired
+ private IndDataSetFieldService indDataSetFieldService;
+
+ @Autowired
+ private IndItemDerService indItemDerService;
public final static String PATTERN_YEAR = "yyyy";
@@ -18,72 +44,221 @@
public final static String PATTERN_DATE = "yyyy-MM-dd";
+ public QuerySourceValueDTO getAtomSql(String itemId) {
+ QuerySourceValueDTO result = new QuerySourceValueDTO();
+ IndItemAtomVO indItem = indItemAtomService.getByItemId(itemId);
+ if (indItem == null) {
+ return result;
+ }
+ result.setIndItemAtom(indItem);
+ IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
+ if (dataSet == null) {
+ return result;
+ }
+ result.setDataSource(indItem.getDataSource());
+ StringBuilder selectSql = new StringBuilder();
+ IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc())? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
+ switch (statFun) {
+ case AVG:
+ selectSql.append("AVG(");
+ break;
+ case COUNT:
+ selectSql.append("COUNT(");
+ break;
+ case MAX:
+ selectSql.append("MAX(");
+ break;
+ case MIN:
+ selectSql.append("MIN(");
+ break;
+ case SUM:
+ selectSql.append("SUM(");
+ break;
+ case DEFAULT:
+ selectSql.append("(");
+ break;
+ }
+ selectSql.append(indItem.getUsingField());
+ selectSql.append(") data_value");
+ result.setSelectSql(selectSql.toString());
+ result.setViewSql(dataSet.getQuerySql());
+ return result;
+ }
- public static String getSqlWhere(String timeRange, String timeGranularity) {
- StringBuilder sqlWhere = new StringBuilder();
+ public QuerySourceValueDTO getDerSql(String itemId) {
+ QuerySourceValueDTO result = getAtomSql(itemId);
+ IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
+ if (indItem == null) {
+ return result;
+ }
+ result = getAtomSql(indItem.getAtomItemId());
+
+ // 拼接SELECT
+ StringBuilder selectSql = new StringBuilder();
+ if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())){
+ selectSql.append(indItem.getDimension());
+ selectSql.append(", ");
+ selectSql.append(result.getSelectSql());
+ }
+ else if(StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())){
+ selectSql.append(result.getSelectSql());
+ }
+ else{
+ selectSql.append(result.getSelectSql());
+ selectSql.append(", ");
+ selectSql.append(indItem.getTimeLabel());
+ selectSql.append(" data_time");
+ }
+ result.setSelectSql(selectSql.toString());
+
+ // 拼接WHERE
+ StringBuilder whereSql = new StringBuilder();
+ whereSql.append(" ");
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.MILLISECOND, 0);
- switch (timeRange) {
- case IndTimeRangeConst.LAST_YEAR:
- calendar.add(Calendar.YEAR, -1);
- sqlWhere.append("ind_t.data_time = '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
- sqlWhere.append("'");
+ calendar.set(Calendar.SECOND, 0);
+ IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
+ switch (timeLimit) {
+ case TODAY:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("='");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("'");
break;
- case IndTimeRangeConst.THIS_YEAR:
- sqlWhere.append("ind_t.data_time = '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
- sqlWhere.append("'");
- break;
- case IndTimeRangeConst.THIS_YEAR_MONTH:
- sqlWhere.append("ind_t.data_time >= '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR) + "-01' ");
- sqlWhere.append("and ind_t.data_time <= '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR) + "-12' ");
- break;
- case IndTimeRangeConst.THIS_MONTH:
- sqlWhere.append("ind_t.data_time = '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
- sqlWhere.append("'");
- break;
- case IndTimeRangeConst.YESTERDAY:
+ case YESTERDAY:
calendar.add(Calendar.DAY_OF_YEAR, -1);
- sqlWhere.append("ind_t.data_time = '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- sqlWhere.append("'");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("='");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("'");
break;
- case IndTimeRangeConst.TODAY:
- sqlWhere.append("ind_t.data_time = '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- sqlWhere.append("'");
- break;
- case IndTimeRangeConst.LAST_DAY_7:
+ case LAST_DAY_7:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("' AND >= '");
calendar.add(Calendar.DAY_OF_YEAR, -7);
- sqlWhere.append("ind_t.data_time >= '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- sqlWhere.append("'");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("'");
break;
- case IndTimeRangeConst.LAST_DAY_30:
+ case LAST_DAY_30:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("' AND >= '");
calendar.add(Calendar.DAY_OF_YEAR, -30);
- sqlWhere.append("ind_t.data_time >= '");
- sqlWhere.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
- sqlWhere.append("'");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
+ whereSql.append("'");
+ break;
+ case THIS_MONTH:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("='");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append("'");
+ break;
+ case LAST_MONTH_12:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append("' AND '");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("' >= '");
+ calendar.add(Calendar.MONTH, -12);
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append("'");
+ break;
+ case THIS_YEAR_MONTH:
+ calendar.set(Calendar.MONTH, 11);
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append("' AND >= '");
+ calendar.set(Calendar.MONTH, 0);
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
+ whereSql.append("'");
+ break;
+ case LAST_YEAR:
+ calendar.add(Calendar.YEAR, -1);
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("='");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
+ whereSql.append("'");
+ break;
+ case THIS_YEAR:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append("='");
+ whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
+ whereSql.append("'");
+ break;
+ case CUSTOM:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
+ whereSql.append("'");
break;
default:
break;
-
}
- return sqlWhere.toString();
+ result.setWhereSql(whereSql.toString());
+
+ // 拼接GROUP
+ StringBuilder groupSql = new StringBuilder();
+ if (StringUtils.isNotBlank(indItem.getDimension())){
+ groupSql.append(indItem.getDimension());
+ }
+ result.setGroupSql(groupSql.toString());
+
+ // 拼接ORDER
+ StringBuilder orderBySql = new StringBuilder();
+ if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())){
+ orderBySql.append(indItem.getTimeLabel());
+ result.setOrderBySql(orderBySql.toString());
+ }
+ return result;
}
- public static String getSqlWhereByRange(String start, String end) {
- StringBuilder sqlWhere = new StringBuilder();
- sqlWhere.append("ind_t.data_time >= '");
- sqlWhere.append(start);
- sqlWhere.append("' ");
- sqlWhere.append("and ind_t.data_time <= '");
- sqlWhere.append(end);
- sqlWhere.append("' ");
- return sqlWhere.toString();
+ public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
+ QuerySourceValueDTO result = getAtomSql(itemId);
+ IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
+ if (indItem == null) {
+ return result;
+ }
+ result = getAtomSql(indItem.getAtomItemId());
+
+ // 拼接SELECT
+ StringBuilder selectSql = new StringBuilder();
+ selectSql.append(indItem.getDimension());
+ selectSql.append(", ");
+ selectSql.append(result.getSelectSql());
+ selectSql.append(", ");
+ selectSql.append(indItem.getTimeLabel());
+ selectSql.append(" data_time");
+ result.setSelectSql(selectSql.toString());
+
+ // 拼接WHERE
+ StringBuilder whereSql = new StringBuilder();
+ whereSql.append(" ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(endTime, PATTERN_MON));
+ whereSql.append("' AND >= '");
+ whereSql.append(DateUtils.format(startTime, PATTERN_MON));
+ whereSql.append("'");
+ result.setWhereSql(whereSql.toString());
+
+ // 拼接GROUP
+ StringBuilder groupSql = new StringBuilder();
+ groupSql.append(" GROUP BY ");
+ groupSql.append(indItem.getDimension());
+ result.setGroupSql(groupSql.toString());
+
+ // 拼接ORDER
+ StringBuilder orderBySql = new StringBuilder();
+ orderBySql.append(indItem.getTimeLabel());
+ result.setOrderBySql(orderBySql.toString());
+ return result;
}
}
\ No newline at end of file
--
Gitblit v1.9.3