package com.iailab.module.data.ind.collection.utils;
|
|
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.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年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";
|
|
public final static String PATTERN_MON = "yyyy-MM";
|
|
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 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);
|
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 YESTERDAY:
|
calendar.add(Calendar.DAY_OF_YEAR, -1);
|
whereSql.append(indItem.getTimeLabel());
|
whereSql.append("='");
|
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
|
whereSql.append("'");
|
break;
|
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);
|
whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
|
whereSql.append("'");
|
break;
|
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);
|
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;
|
}
|
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 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;
|
}
|
}
|
