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

@@ -4,10 +4,11 @@
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.entity.IndItemEntity;
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.service.IndItemService;
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;
@@ -33,7 +34,7 @@
    private IndDataSetService indDataSetService;

    @Autowired
    private IndDataSetFieldService indDataSetFieldService;
    private IndItemService indItemService;

    @Autowired
    private IndItemDerService indItemDerService;
@@ -50,6 +51,8 @@
        if (indItem == null) {
            return result;
        }
        IndItemEntity itemEntity = indItemService.get(itemId);
        result.setItemNo(itemEntity.getItemNo());
        result.setIndItemAtom(indItem);
        IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
        if (dataSet == null) {
@@ -57,7 +60,7 @@
        }
        result.setDataSource(indItem.getDataSource());
        StringBuilder selectSql = new StringBuilder();
        IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc())? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
        IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc()) ? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
        switch (statFun) {
            case AVG:
                selectSql.append("AVG(");
@@ -91,19 +94,19 @@
        if (indItem == null) {
            return result;
        }
        IndItemEntity itemEntity = indItemService.get(itemId);
        result.setItemNo(itemEntity.getItemNo());
        result = getAtomSql(indItem.getAtomItemId());

        // 拼接SELECT
        StringBuilder selectSql = new StringBuilder();
        if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())){
        if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
            selectSql.append(indItem.getDimension());
            selectSql.append(", ");
            selectSql.append(result.getSelectSql());
        }
        if(StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())){
        } else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
            selectSql.append(result.getSelectSql());
        }
        else{
        } else {
            selectSql.append(result.getSelectSql());
            selectSql.append(", ");
            selectSql.append(indItem.getTimeLabel());
@@ -136,7 +139,9 @@
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append("' AND >= '");
                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("'");
@@ -145,7 +150,9 @@
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
                whereSql.append("' AND >= '");
                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("'");
@@ -156,11 +163,22 @@
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append("'");
                break;
            case LAST_MONTH:
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("' < '");
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.add(Calendar.MONTH, -1);
                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 '");
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("' >= '");
                calendar.add(Calendar.MONTH, -12);
@@ -168,10 +186,13 @@
                whereSql.append("'");
                break;
            case THIS_YEAR_MONTH:
                calendar.set(Calendar.MONTH, 11);
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" <= '");
                calendar.set(Calendar.MONTH, 11);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append("' AND >= '");
                whereSql.append("' AND ");
                whereSql.append(indItem.getTimeLabel());
                whereSql.append(" >= '");
                calendar.set(Calendar.MONTH, 0);
                whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
                whereSql.append("'");
@@ -187,6 +208,17 @@
                whereSql.append(indItem.getTimeLabel());
                whereSql.append("='");
                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:
@@ -206,14 +238,14 @@

        // 拼接GROUP
        StringBuilder groupSql = new StringBuilder();
        if (StringUtils.isNotBlank(indItem.getDimension())){
        if (StringUtils.isNotBlank(indItem.getDimension())) {
            groupSql.append(indItem.getDimension());
        }
        result.setGroupSql(groupSql.toString());

        // 拼接ORDER
        StringBuilder orderBySql = new StringBuilder();
        if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())){
        if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
            orderBySql.append(indItem.getTimeLabel());
            result.setOrderBySql(orderBySql.toString());
        }
@@ -228,11 +260,20 @@
        }
        result = getAtomSql(indItem.getAtomItemId());

        IndItemEntity itemEntity = indItemService.get(itemId);
        result.setItemNo(itemEntity.getItemNo());

        // 拼接SELECT
        StringBuilder selectSql = new StringBuilder();
        selectSql.append(indItem.getDimension());
        selectSql.append(", ");
        selectSql.append(result.getSelectSql());
        if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
            selectSql.append(indItem.getDimension());
            selectSql.append(", ");
            selectSql.append(result.getSelectSql());
        } else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
            selectSql.append(result.getSelectSql());
        } else {
            selectSql.append(result.getSelectSql());
        }
        selectSql.append(", ");
        selectSql.append(indItem.getTimeLabel());
        selectSql.append(" data_time");
@@ -243,22 +284,28 @@
        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(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
        whereSql.append("' AND ");
        whereSql.append(indItem.getTimeLabel());
        whereSql.append(" >= '");
        whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
        whereSql.append("'");
        result.setWhereSql(whereSql.toString());

        // 拼接GROUP
        StringBuilder groupSql = new StringBuilder();
        groupSql.append(" GROUP BY ");
        groupSql.append(indItem.getDimension());
        if (StringUtils.isNotBlank(indItem.getDimension())) {
            groupSql.append(indItem.getDimension());
        }
        result.setGroupSql(groupSql.toString());

        // 拼接ORDER
        StringBuilder orderBySql = new StringBuilder();
        orderBySql.append(indItem.getTimeLabel());
        result.setOrderBySql(orderBySql.toString());
        if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
            orderBySql.append(indItem.getTimeLabel());
            result.setOrderBySql(orderBySql.toString());
        }

        return result;
    }
}