iailab-plat.git

潘志宝

2024-10-09 c0b8cf32504dd1a42780bb1ee06ae8a7b0d7b120

提交 \| 用户 \| 时间
cf757d	1	package com.iailab.module.data.ind.collection.utils;
潘	2
	3	import com.iailab.framework.common.util.date.DateUtils;
	4	import com.iailab.module.data.common.enums.IndStatFuncEnum;
	5	import com.iailab.module.data.common.enums.IndTimeLimitEnum;
	6	import com.iailab.module.data.ind.data.dto.IndDataSetDTO;
	7	import com.iailab.module.data.ind.data.service.IndDataSetService;
	8	import com.iailab.module.data.ind.item.service.IndItemAtomService;
	9	import com.iailab.module.data.ind.item.service.IndItemDerService;
	10	import com.iailab.module.data.ind.item.vo.IndItemAtomVO;
	11	import com.iailab.module.data.ind.item.vo.IndItemDerVO;
	12	import com.iailab.module.data.ind.value.dto.QuerySourceValueDTO;
	13	import org.springframework.beans.factory.annotation.Autowired;
	14	import org.springframework.stereotype.Component;
	15
	16	import java.util.Calendar;
	17	import java.util.Date;
	18
	19	/**
	20	* @author PanZhibao
	21	* @Description
	22	* @createTime 2024年10月04日
	23	*/
	24	@Component
	25	public class IndSqlUtils {
	26
	27	@Autowired
	28	private IndItemAtomService indItemAtomService;
	29
	30	@Autowired
	31	private IndDataSetService indDataSetService;
	32
	33	@Autowired
	34	private IndItemDerService indItemDerService;
	35
	36	public final static String PATTERN_YEAR = "yyyy";
	37
	38	public final static String PATTERN_MON = "yyyy-MM";
	39
	40	public final static String PATTERN_DATE = "yyyy-MM-dd";
	41
	42	public QuerySourceValueDTO getAtomSql(String itemId) {
	43	QuerySourceValueDTO result = new QuerySourceValueDTO();
	44	IndItemAtomVO indItem = indItemAtomService.getByItemId(itemId);
	45	if (indItem == null) {
	46	return result;
	47	}
	48	IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
	49	if (dataSet == null) {
	50	return result;
	51	}
	52	result.setDataSource(indItem.getDataSource());
	53	StringBuilder selectSql = new StringBuilder();
	54	IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(indItem.getStatFunc());
	55	switch (statFun) {
	56	case AVG:
	57	selectSql.append("AVG(");
	58	break;
	59	case COUNT:
	60	selectSql.append("COUNT(");
	61	break;
	62	case MAX:
	63	selectSql.append("MAX(");
	64	break;
	65	case MIN:
	66	selectSql.append("MIN(");
	67	break;
	68	case SUM:
	69	selectSql.append("SUM(");
	70	break;
	71	default:
	72	selectSql.append("(");
	73	break;
	74	}
	75	selectSql.append(indItem.getUsingField());
	76	selectSql.append(") data_value");
	77	result.setSelectSql(selectSql.toString());
	78	result.setViewSql(dataSet.getQuerySql());
	79	return result;
	80	}
	81
	82	public QuerySourceValueDTO getDerSql(String itemId) {
	83	QuerySourceValueDTO result = getAtomSql(itemId);
	84	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	85	if (indItem == null) {
	86	return result;
	87	}
	88	result = getAtomSql(indItem.getAtomItemId());
	89
	90	// 拼接SELECT
	91	StringBuilder selectSql = new StringBuilder();
	92	selectSql.append(indItem.getDimension());
	93	selectSql.append(", ");
	94	selectSql.append(result.getSelectSql());
	95	selectSql.append(", ");
	96	selectSql.append(indItem.getTimeLabel());
	97	selectSql.append(" data_time");
	98	result.setSelectSql(selectSql.toString());
	99
	100	// 拼接WHERE
	101	StringBuilder whereSql = new StringBuilder();
	102	whereSql.append(" ");
	103	Calendar calendar = Calendar.getInstance();
	104	calendar.set(Calendar.MILLISECOND, 0);
	105	calendar.set(Calendar.SECOND, 0);
	106	IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
	107	switch (timeLimit) {
	108	case TODAY:
	109	whereSql.append(indItem.getTimeLabel());
	110	whereSql.append("='");
	111	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	112	whereSql.append("'");
	113	break;
	114	case YESTERDAY:
	115	calendar.add(Calendar.DAY_OF_YEAR, -1);
	116	whereSql.append(indItem.getTimeLabel());
	117	whereSql.append("='");
	118	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	119	whereSql.append("'");
	120	break;
	121	case LAST_DAY_7:
	122	whereSql.append(indItem.getTimeLabel());
	123	whereSql.append(" <= '");
	124	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	125	whereSql.append("' AND >= '");
	126	calendar.add(Calendar.DAY_OF_YEAR, -7);
	127	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	128	whereSql.append("'");
	129	break;
	130	case LAST_DAY_30:
	131	whereSql.append(indItem.getTimeLabel());
	132	whereSql.append(" <= '");
	133	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	134	whereSql.append("' AND >= '");
	135	calendar.add(Calendar.DAY_OF_YEAR, -30);
	136	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	137	whereSql.append("'");
	138	break;
	139	case THIS_MONTH:
	140	whereSql.append(indItem.getTimeLabel());
	141	whereSql.append("='");
	142	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	143	whereSql.append("'");
	144	break;
	145	case LAST_MONTH_12:
	146	whereSql.append(indItem.getTimeLabel());
	147	whereSql.append(" <= '");
	148	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	149	whereSql.append("' AND >= '");
	150	calendar.add(Calendar.MONTH, -12);
	151	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	152	whereSql.append("'");
	153	break;
	154	case THIS_YEAR_MONTH:
	155	calendar.set(Calendar.MONTH, 11);
	156	whereSql.append(" <= '");
	157	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	158	whereSql.append("' AND >= '");
	159	calendar.set(Calendar.MONTH, 0);
	160	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	161	whereSql.append("'");
	162	break;
	163	case LAST_YEAR:
	164	calendar.add(Calendar.YEAR, -1);
	165	whereSql.append(indItem.getTimeLabel());
	166	whereSql.append("='");
	167	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	168	whereSql.append("'");
	169	break;
	170	case THIS_YEAR:
	171	whereSql.append(indItem.getTimeLabel());
	172	whereSql.append("='");
	173	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	174	whereSql.append("'");
	175	break;
	176	case CUSTOM:
	177	whereSql.append(" <= '");
	178	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
	179	whereSql.append("' AND >= '");
	180	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
	181	whereSql.append("'");
	182	break;
	183	default:
	184	break;
	185	}
	186	result.setWhereSql(whereSql.toString());
	187
	188	// 拼接GROUP
	189	StringBuilder groupSql = new StringBuilder();
	190	groupSql.append(" GROUP BY ");
	191	groupSql.append(indItem.getDimension());
	192	result.setGroupSql(groupSql.toString());
	193
	194	// 拼接ORDER
	195	StringBuilder orderBySql = new StringBuilder();
	196	orderBySql.append(indItem.getTimeLabel());
	197	result.setOrderBySql(orderBySql.toString());
	198	return result;
	199	}
	200
	201	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	202	QuerySourceValueDTO result = getAtomSql(itemId);
	203	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	204	if (indItem == null) {
	205	return result;
	206	}
	207	result = getAtomSql(indItem.getAtomItemId());
	208
	209	// 拼接SELECT
	210	StringBuilder selectSql = new StringBuilder();
	211	selectSql.append(indItem.getDimension());
	212	selectSql.append(", ");
	213	selectSql.append(result.getSelectSql());
	214	selectSql.append(", ");
	215	selectSql.append(indItem.getTimeLabel());
	216	selectSql.append(" data_time");
	217	result.setSelectSql(selectSql.toString());
	218
	219	// 拼接WHERE
	220	StringBuilder whereSql = new StringBuilder();
	221	whereSql.append(" ");
	222	whereSql.append(indItem.getTimeLabel());
	223	whereSql.append(" <= '");
	224	whereSql.append(DateUtils.format(endTime, PATTERN_MON));
	225	whereSql.append("' AND >= '");
	226	whereSql.append(DateUtils.format(startTime, PATTERN_MON));
	227	whereSql.append("'");
	228	result.setWhereSql(whereSql.toString());
	229
	230	// 拼接GROUP
	231	StringBuilder groupSql = new StringBuilder();
	232	groupSql.append(" GROUP BY ");
	233	groupSql.append(indItem.getDimension());
	234	result.setGroupSql(groupSql.toString());
	235
	236	// 拼接ORDER
	237	StringBuilder orderBySql = new StringBuilder();
	238	orderBySql.append(indItem.getTimeLabel());
	239	result.setOrderBySql(orderBySql.toString());
	240	return result;
	241	}
	242	}