iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java

@@ -3,6 +3,7 @@
import com.iailab.framework.common.util.date.DateUtils;
import com.iailab.module.data.common.enums.IndStatFuncEnum;
import com.iailab.module.data.common.enums.IndTimeLimitEnum;
import com.iailab.module.data.enums.TimeGranularityEnum;
import com.iailab.module.data.ind.data.dto.IndDataSetDTO;
import com.iailab.module.data.ind.data.service.IndDataSetService;
import com.iailab.module.data.ind.item.entity.IndItemEntity;
@@ -16,6 +17,9 @@
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;

@@ -43,7 +47,11 @@

    public final static String PATTERN_MON = "yyyy-MM";

    public final static String PATTERN_DATE = "yyyy-MM-dd";
    public final static String PATTERN_DAY = "yyyy-MM-dd";

    public final static String PATTERN_HOUR = "yyyy-MM-dd HH";

    public static final String PATTERN_MIN = "yyyy-MM-dd HH:mm:ss";

    public QuerySourceValueDTO getAtomSql(String itemId) {
        QuerySourceValueDTO result = new QuerySourceValueDTO();
@@ -83,6 +91,7 @@
        }
        selectSql.append(indItem.getUsingField());
        selectSql.append(") data_value");
        //selectSql.append(") \"data_value\"");
        result.setSelectSql(selectSql.toString());
        result.setViewSql(dataSet.getQuerySql());
        return result;
@@ -121,115 +130,138 @@
        calendar.set(Calendar.MILLISECOND, 0);
        calendar.set(Calendar.SECOND, 0);
        IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
        // 根据时间粒度确定 时间格式
        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;
            }
        }
        switch (timeLimit) {
            case TODAY:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("='");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                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(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case LAST_DAY_7:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.add(Calendar.DAY_OF_YEAR, -7);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case LAST_DAY_30:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.add(Calendar.DAY_OF_YEAR, -30);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case THIS_MONTH:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("='");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
                whereSql.append("'");
                break;
            case LAST_MONTH:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("' < '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.add(Calendar.MONTH, -1);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
                whereSql.append("'");
                break;
            case LAST_MONTH_12:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("' >= '");
                calendar.add(Calendar.MONTH, -12);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));
                whereSql.append("'");
                break;
            case THIS_YEAR_MONTH:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                calendar.set(Calendar.MONTH, 11);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.set(Calendar.MONTH, 0);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                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(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case THIS_YEAR:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("='");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case LAST_HOUR_24:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.add(Calendar.HOUR_OF_DAY, -24);
                whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));
                whereSql.append("'");
                break;
            case CUSTOM:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
                whereSql.append(DateUtils.format(indItem.getTimeEnd(), datePattern));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
                whereSql.append(DateUtils.format(indItem.getTimeStart(), datePattern));
                whereSql.append("'");
                break;
            case ALL:
                whereSql.append("1 = 1");
                break;
            default:
                break;
@@ -252,6 +284,27 @@
        return result;
    }

    public static String getYearMonth(Calendar now,Integer monthStart) {
        int day = now.get(Calendar.DAY_OF_MONTH);

        // 当前月
        if (monthStart > 0) {
            if (day < monthStart) {
                now.add(Calendar.MONTH, -1);
            }
        }

        if (monthStart < 0) {
            if (day >= Math.abs(monthStart)) {
                now.add(Calendar.MONTH, 1);
            }
        }
        // 定义日期格式化对象，指定格式为 yyyy-MM
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_MON);
        // 格式化日期并返回结果
        return sdf.format(now.getTime());
    }

    public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
        QuerySourceValueDTO result = getAtomSql(itemId);
        IndItemDerVO indItem = indItemDerService.getByItemId(itemId);