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 {

@@ -186,32 +186,49 @@
                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(" <= '");
                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:
@@ -266,6 +283,7 @@
            default:
                break;
        }
        log.info("whereSql:" + whereSql);
        result.setWhereSql(whereSql.toString());

        // 拼接GROUP
@@ -284,23 +302,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 +344,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());