From 5bc38e6cb327e238d7bcb3cf6e39e95772cbecb8 Mon Sep 17 00:00:00 2001
From: 潘志宝 <979469083@qq.com>
Date: 星期五, 20 六月 2025 12:06:53 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java | 136 +++++++++++++++++++++++++++++++++------------
1 files changed, 100 insertions(+), 36 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 a2e3f09..3f99482 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
@@ -13,13 +13,12 @@
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.text.SimpleDateFormat;
-import java.time.YearMonth;
-import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
@@ -28,6 +27,7 @@
* @Description
* @createTime 2024年10月04日
*/
+@Slf4j
@Component
public class IndSqlUtils {
@@ -66,7 +66,7 @@
if (dataSet == null) {
return result;
}
- result.setDataSource(indItem.getDataSource());
+ result.setDataSource(dataSet.getDataSource());
StringBuilder selectSql = new StringBuilder();
IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc()) ? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
switch (statFun) {
@@ -153,20 +153,35 @@
switch (timeLimit) {
case TODAY:
whereSql.append(indItem.getTimeLabel());
- whereSql.append("='");
+ whereSql.append(" >= '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" < '");
+ calendar.add(Calendar.DAY_OF_YEAR, 1);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case YESTERDAY:
- calendar.add(Calendar.DAY_OF_YEAR, -1);
whereSql.append(indItem.getTimeLabel());
- whereSql.append("='");
+ whereSql.append(" < '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.DAY_OF_YEAR, -1);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case LAST_DAY_7:
whereSql.append(indItem.getTimeLabel());
- whereSql.append(" <= '");
+ whereSql.append(" < '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
@@ -177,7 +192,9 @@
break;
case LAST_DAY_30:
whereSql.append(indItem.getTimeLabel());
- whereSql.append(" <= '");
+ whereSql.append(" < '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
@@ -186,32 +203,51 @@
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
- case THIS_MONTH:
+ case LAST_DAY_90:
whereSql.append(indItem.getTimeLabel());
- whereSql.append("='");
- whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append(" < '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.DAY_OF_YEAR, -90);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
- case LAST_MONTH:
+ case THIS_MONTH:
whereSql.append(indItem.getTimeLabel());
- whereSql.append("' < '");
- whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append(" < '");
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
calendar.add(Calendar.MONTH, -1);
- whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
+ whereSql.append("'");
+ break;
+ case LAST_MONTH:
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" < '");
+ calendar.add(Calendar.MONTH, -1);
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.MONTH, -1);
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
whereSql.append("'");
break;
case LAST_MONTH_12:
whereSql.append(indItem.getTimeLabel());
- whereSql.append(" <= '");
- whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append(" < '");
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
- whereSql.append("' >= '");
+ whereSql.append(" >= '");
calendar.add(Calendar.MONTH, -12);
- whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
+ whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));
whereSql.append("'");
break;
case THIS_YEAR_MONTH:
@@ -227,15 +263,27 @@
whereSql.append("'");
break;
case LAST_YEAR:
- calendar.add(Calendar.YEAR, -1);
whereSql.append(indItem.getTimeLabel());
- whereSql.append("='");
+ whereSql.append(" <= '");
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" >= '");
+ calendar.add(Calendar.YEAR, -1);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
case THIS_YEAR:
whereSql.append(indItem.getTimeLabel());
- whereSql.append("='");
+ whereSql.append(" >= '");
+ calendar.set(Calendar.MINUTE, 0);
+ calendar.set(Calendar.HOUR_OF_DAY, 0);
+ calendar.set(Calendar.DAY_OF_YEAR, 1);
+ whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
+ whereSql.append("' AND ");
+ whereSql.append(indItem.getTimeLabel());
+ whereSql.append(" < '");
+ calendar.add(Calendar.YEAR, 1);
whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
whereSql.append("'");
break;
@@ -266,6 +314,7 @@
default:
break;
}
+ log.info("whereSql:" + whereSql);
result.setWhereSql(whereSql.toString());
// 拼接GROUP
@@ -284,23 +333,17 @@
return result;
}
- public static String getYearMonth(Calendar now,Integer monthStart) {
+ public static String getYearMonth(Calendar calendar,Integer monthStart,String datePattern) {
+ Calendar now = (Calendar) calendar.clone();
int day = now.get(Calendar.DAY_OF_MONTH);
- // 当前月
- if (monthStart > 0) {
- if (day < monthStart) {
- now.add(Calendar.MONTH, -1);
- }
+ if (day >= Math.abs(monthStart)) {
+ now.add(Calendar.MONTH, 1);
}
- if (monthStart < 0) {
- if (day >= Math.abs(monthStart)) {
- now.add(Calendar.MONTH, 1);
- }
- }
+ now.set(Calendar.DAY_OF_MONTH,Math.abs(monthStart));
// 定义日期格式化对象，指定格式为 yyyy-MM
- SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_MON);
+ SimpleDateFormat sdf = new SimpleDateFormat(datePattern);
// 格式化日期并返回结果
return sdf.format(now.getTime());
}
@@ -332,16 +375,37 @@
selectSql.append(" data_time");
result.setSelectSql(selectSql.toString());
+ // 根据时间粒度确定 时间格式
+ String datePattern = PATTERN_MIN;
+ if (StringUtils.isNotBlank(itemEntity.getTimeGranularity())) {
+ switch (TimeGranularityEnum.getEumByCode(itemEntity.getTimeGranularity())){
+ case TG_MIN:
+ datePattern = PATTERN_MIN;
+ break;
+ case TG_HOUR:
+ datePattern = PATTERN_HOUR;
+ break;
+ case TG_DAY:
+ datePattern = PATTERN_DAY;
+ break;
+ case TG_MON:
+ datePattern = PATTERN_MON;
+ break;
+ default:
+ datePattern = PATTERN_YEAR;
+ }
+ }
+
// 拼接WHERE
StringBuilder whereSql = new StringBuilder();
whereSql.append(" ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" <= '");
- whereSql.append(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
+ whereSql.append(DateUtils.format(endTime, datePattern));
whereSql.append("' AND ");
whereSql.append(indItem.getTimeLabel());
whereSql.append(" >= '");
- whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
+ whereSql.append(DateUtils.format(startTime, datePattern));
whereSql.append("'");
result.setWhereSql(whereSql.toString());
--
Gitblit v1.9.3