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

@@ -16,6 +16,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;

@@ -160,30 +163,29 @@
            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:
                calendar.add(Calendar.MONTH, -1); // 将当前日期减去一个月，得到上个月的日期
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON)); // 上个月的第一天
                calendar.add(Calendar.MONTH, 1); // 再加一个月，回到当前月
                whereSql.append("' AND '");
                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(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("' AND '");
                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:
@@ -211,6 +213,17 @@
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
                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("' 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("'");
                break;
            case CUSTOM:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
@@ -220,6 +233,9 @@
                whereSql.append(" >= '");
                whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
                whereSql.append("'");
                break;
            case ALL:
                whereSql.append("1 = 1");
                break;
            default:
                break;
@@ -240,6 +256,27 @@
            result.setOrderBySql(orderBySql.toString());
        }
        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) {
@@ -263,10 +300,10 @@
            selectSql.append(result.getSelectSql());
        } else {
            selectSql.append(result.getSelectSql());
            selectSql.append(", ");
            selectSql.append(indItem.getTimeLabel());
            selectSql.append(" data_time");
        }
        selectSql.append(", ");
        selectSql.append(indItem.getTimeLabel());
        selectSql.append(" data_time");
        result.setSelectSql(selectSql.toString());

        // 拼接WHERE