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,209 @@

    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;
        }
        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())){
            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(" ");
        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 >= '");
                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();
        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;
    }

    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;
    }
}