iailab-plat.git

潘志宝

3 天以前 4a64b287565285bae467463b5158e82053ebbf75

提交 \| 用户 \| 时间
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;
664918	8	import com.iailab.module.data.ind.item.entity.IndItemEntity;
cf757d	9	import com.iailab.module.data.ind.item.service.IndItemAtomService;
潘	10	import com.iailab.module.data.ind.item.service.IndItemDerService;
664918	11	import com.iailab.module.data.ind.item.service.IndItemService;
cf757d	12	import com.iailab.module.data.ind.item.vo.IndItemAtomVO;
潘	13	import com.iailab.module.data.ind.item.vo.IndItemDerVO;
	14	import com.iailab.module.data.ind.value.dto.QuerySourceValueDTO;
cfa57a	15	import org.apache.commons.lang3.StringUtils;
cf757d	16	import org.springframework.beans.factory.annotation.Autowired;
潘	17	import org.springframework.stereotype.Component;
	18
	19	import java.util.Calendar;
	20	import java.util.Date;
	21
	22	/**
	23	* @author PanZhibao
	24	* @Description
	25	* @createTime 2024年10月04日
	26	*/
	27	@Component
	28	public class IndSqlUtils {
	29
	30	@Autowired
	31	private IndItemAtomService indItemAtomService;
	32
	33	@Autowired
	34	private IndDataSetService indDataSetService;
cfa57a	35
J	36	@Autowired
664918	37	private IndItemService indItemService;
cf757d	38
潘	39	@Autowired
	40	private IndItemDerService indItemDerService;
	41
	42	public final static String PATTERN_YEAR = "yyyy";
	43
	44	public final static String PATTERN_MON = "yyyy-MM";
	45
	46	public final static String PATTERN_DATE = "yyyy-MM-dd";
	47
	48	public QuerySourceValueDTO getAtomSql(String itemId) {
	49	QuerySourceValueDTO result = new QuerySourceValueDTO();
	50	IndItemAtomVO indItem = indItemAtomService.getByItemId(itemId);
	51	if (indItem == null) {
	52	return result;
	53	}
664918	54	IndItemEntity itemEntity = indItemService.get(itemId);
潘	55	result.setItemNo(itemEntity.getItemNo());
2056b4	56	result.setIndItemAtom(indItem);
cf757d	57	IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
潘	58	if (dataSet == null) {
	59	return result;
	60	}
	61	result.setDataSource(indItem.getDataSource());
	62	StringBuilder selectSql = new StringBuilder();
a2903e	63	IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc()) ? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
cf757d	64	switch (statFun) {
潘	65	case AVG:
	66	selectSql.append("AVG(");
	67	break;
	68	case COUNT:
	69	selectSql.append("COUNT(");
	70	break;
	71	case MAX:
	72	selectSql.append("MAX(");
	73	break;
	74	case MIN:
	75	selectSql.append("MIN(");
	76	break;
	77	case SUM:
	78	selectSql.append("SUM(");
	79	break;
cfa57a	80	case DEFAULT:
cf757d	81	selectSql.append("(");
潘	82	break;
	83	}
	84	selectSql.append(indItem.getUsingField());
	85	selectSql.append(") data_value");
	86	result.setSelectSql(selectSql.toString());
	87	result.setViewSql(dataSet.getQuerySql());
	88	return result;
	89	}
	90
	91	public QuerySourceValueDTO getDerSql(String itemId) {
	92	QuerySourceValueDTO result = getAtomSql(itemId);
	93	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	94	if (indItem == null) {
	95	return result;
	96	}
664918	97	IndItemEntity itemEntity = indItemService.get(itemId);
潘	98	result.setItemNo(itemEntity.getItemNo());
cf757d	99	result = getAtomSql(indItem.getAtomItemId());
潘	100
	101	// 拼接SELECT
	102	StringBuilder selectSql = new StringBuilder();
a2903e	103	if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
cfa57a	104	selectSql.append(indItem.getDimension());
J	105	selectSql.append(", ");
5bc3b4	106	selectSql.append(result.getSelectSql());
a2903e	107	} else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
2056b4	108	selectSql.append(result.getSelectSql());
a2903e	109	} else {
5bc3b4	110	selectSql.append(result.getSelectSql());
J	111	selectSql.append(", ");
	112	selectSql.append(indItem.getTimeLabel());
	113	selectSql.append(" data_time");
cfa57a	114	}
cf757d	115	result.setSelectSql(selectSql.toString());
潘	116
	117	// 拼接WHERE
	118	StringBuilder whereSql = new StringBuilder();
	119	whereSql.append(" ");
	120	Calendar calendar = Calendar.getInstance();
	121	calendar.set(Calendar.MILLISECOND, 0);
	122	calendar.set(Calendar.SECOND, 0);
	123	IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
	124	switch (timeLimit) {
	125	case TODAY:
	126	whereSql.append(indItem.getTimeLabel());
	127	whereSql.append("='");
	128	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	129	whereSql.append("'");
	130	break;
	131	case YESTERDAY:
	132	calendar.add(Calendar.DAY_OF_YEAR, -1);
	133	whereSql.append(indItem.getTimeLabel());
	134	whereSql.append("='");
	135	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	136	whereSql.append("'");
	137	break;
	138	case LAST_DAY_7:
	139	whereSql.append(indItem.getTimeLabel());
	140	whereSql.append(" <= '");
	141	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
2cb72f	142	whereSql.append("' AND ");
D	143	whereSql.append(indItem.getTimeLabel());
	144	whereSql.append(" >= '");
cf757d	145	calendar.add(Calendar.DAY_OF_YEAR, -7);
潘	146	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	147	whereSql.append("'");
	148	break;
	149	case LAST_DAY_30:
	150	whereSql.append(indItem.getTimeLabel());
	151	whereSql.append(" <= '");
	152	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
2cb72f	153	whereSql.append("' AND ");
D	154	whereSql.append(indItem.getTimeLabel());
	155	whereSql.append(" >= '");
cf757d	156	calendar.add(Calendar.DAY_OF_YEAR, -30);
潘	157	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	158	whereSql.append("'");
	159	break;
	160	case THIS_MONTH:
	161	whereSql.append(indItem.getTimeLabel());
	162	whereSql.append("='");
	163	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	164	whereSql.append("'");
	165	break;
a2903e	166	case LAST_MONTH:
D	167	whereSql.append(indItem.getTimeLabel());
	168	whereSql.append("' < '");
0723ba	169	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
4a64b2	170	whereSql.append("' AND ");
0723ba	171	whereSql.append(indItem.getTimeLabel());
D	172	whereSql.append(" >= '");
	173	calendar.add(Calendar.MONTH, -1);
	174	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
a2903e	175	whereSql.append("'");
D	176	break;
cf757d	177	case LAST_MONTH_12:
潘	178	whereSql.append(indItem.getTimeLabel());
	179	whereSql.append(" <= '");
	180	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
4a64b2	181	whereSql.append("' AND ");
5bc3b4	182	whereSql.append(indItem.getTimeLabel());
J	183	whereSql.append("' >= '");
cf757d	184	calendar.add(Calendar.MONTH, -12);
潘	185	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	186	whereSql.append("'");
	187	break;
	188	case THIS_YEAR_MONTH:
2cb72f	189	whereSql.append(indItem.getTimeLabel());
cf757d	190	whereSql.append(" <= '");
2cb72f	191	calendar.set(Calendar.MONTH, 11);
cf757d	192	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
2cb72f	193	whereSql.append("' AND ");
D	194	whereSql.append(indItem.getTimeLabel());
	195	whereSql.append(" >= '");
cf757d	196	calendar.set(Calendar.MONTH, 0);
潘	197	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	198	whereSql.append("'");
	199	break;
	200	case LAST_YEAR:
	201	calendar.add(Calendar.YEAR, -1);
	202	whereSql.append(indItem.getTimeLabel());
	203	whereSql.append("='");
	204	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	205	whereSql.append("'");
	206	break;
	207	case THIS_YEAR:
	208	whereSql.append(indItem.getTimeLabel());
	209	whereSql.append("='");
	210	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	211	whereSql.append("'");
	212	break;
cca586	213	case LAST_HOUR_24:
潘	214	whereSql.append(indItem.getTimeLabel());
e1579f	215	whereSql.append(" <= '");
cca586	216	whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
4a64b2	217	whereSql.append("' AND ");
cca586	218	whereSql.append(indItem.getTimeLabel());
潘	219	whereSql.append(" >= '");
	220	calendar.add(Calendar.HOUR_OF_DAY, -24);
	221	whereSql.append(DateUtils.format(calendar.getTime(), DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
	222	whereSql.append("'");
	223	break;
cf757d	224	case CUSTOM:
cfa57a	225	whereSql.append(indItem.getTimeLabel());
cf757d	226	whereSql.append(" <= '");
潘	227	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
2056b4	228	whereSql.append("' AND ");
cfa57a	229	whereSql.append(indItem.getTimeLabel());
2056b4	230	whereSql.append(" >= '");
cf757d	231	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
潘	232	whereSql.append("'");
	233	break;
	234	default:
	235	break;
	236	}
	237	result.setWhereSql(whereSql.toString());
	238
	239	// 拼接GROUP
	240	StringBuilder groupSql = new StringBuilder();
a2903e	241	if (StringUtils.isNotBlank(indItem.getDimension())) {
5bc3b4	242	groupSql.append(indItem.getDimension());
J	243	}
cf757d	244	result.setGroupSql(groupSql.toString());
潘	245
	246	// 拼接ORDER
	247	StringBuilder orderBySql = new StringBuilder();
a2903e	248	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
5bc3b4	249	orderBySql.append(indItem.getTimeLabel());
J	250	result.setOrderBySql(orderBySql.toString());
	251	}
cf757d	252	return result;
潘	253	}
	254
	255	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	256	QuerySourceValueDTO result = getAtomSql(itemId);
	257	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	258	if (indItem == null) {
	259	return result;
	260	}
	261	result = getAtomSql(indItem.getAtomItemId());
	262
664918	263	IndItemEntity itemEntity = indItemService.get(itemId);
潘	264	result.setItemNo(itemEntity.getItemNo());
	265
cf757d	266	// 拼接SELECT
潘	267	StringBuilder selectSql = new StringBuilder();
2cb72f	268	if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
D	269	selectSql.append(indItem.getDimension());
	270	selectSql.append(", ");
	271	selectSql.append(result.getSelectSql());
	272	} else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
	273	selectSql.append(result.getSelectSql());
	274	} else {
	275	selectSql.append(result.getSelectSql());
	276	}
a03a19	277	selectSql.append(", ");
潘	278	selectSql.append(indItem.getTimeLabel());
	279	selectSql.append(" data_time");
cf757d	280	result.setSelectSql(selectSql.toString());
潘	281
	282	// 拼接WHERE
	283	StringBuilder whereSql = new StringBuilder();
	284	whereSql.append(" ");
	285	whereSql.append(indItem.getTimeLabel());
	286	whereSql.append(" <= '");
2cb72f	287	whereSql.append(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
D	288	whereSql.append("' AND ");
	289	whereSql.append(indItem.getTimeLabel());
	290	whereSql.append(" >= '");
	291	whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
cf757d	292	whereSql.append("'");
潘	293	result.setWhereSql(whereSql.toString());
	294
	295	// 拼接GROUP
	296	StringBuilder groupSql = new StringBuilder();
2cb72f	297	if (StringUtils.isNotBlank(indItem.getDimension())) {
D	298	groupSql.append(indItem.getDimension());
	299	}
cf757d	300	result.setGroupSql(groupSql.toString());
潘	301
	302	// 拼接ORDER
	303	StringBuilder orderBySql = new StringBuilder();
2cb72f	304	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
D	305	orderBySql.append(indItem.getTimeLabel());
	306	result.setOrderBySql(orderBySql.toString());
	307	}
	308
cf757d	309	return result;
潘	310	}
	311	}