iailab-plat.git

Merge remote-tracking branch 'origin/master'

dongyukun

2025-01-24 5c2fc8b317e17d0b194f27937bd9d2af8961502f

提交 \| 用户 \| 时间
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));
D	170	whereSql.append("' AND '");
	171	whereSql.append(indItem.getTimeLabel());
	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));
5bc3b4	181	whereSql.append("' AND '");
J	182	whereSql.append(indItem.getTimeLabel());
	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;
	213	case CUSTOM:
cfa57a	214	whereSql.append(indItem.getTimeLabel());
cf757d	215	whereSql.append(" <= '");
潘	216	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
2056b4	217	whereSql.append("' AND ");
cfa57a	218	whereSql.append(indItem.getTimeLabel());
2056b4	219	whereSql.append(" >= '");
cf757d	220	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
潘	221	whereSql.append("'");
	222	break;
	223	default:
	224	break;
	225	}
	226	result.setWhereSql(whereSql.toString());
	227
	228	// 拼接GROUP
	229	StringBuilder groupSql = new StringBuilder();
a2903e	230	if (StringUtils.isNotBlank(indItem.getDimension())) {
5bc3b4	231	groupSql.append(indItem.getDimension());
J	232	}
cf757d	233	result.setGroupSql(groupSql.toString());
潘	234
	235	// 拼接ORDER
	236	StringBuilder orderBySql = new StringBuilder();
a2903e	237	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
5bc3b4	238	orderBySql.append(indItem.getTimeLabel());
J	239	result.setOrderBySql(orderBySql.toString());
	240	}
cf757d	241	return result;
潘	242	}
	243
	244	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	245	QuerySourceValueDTO result = getAtomSql(itemId);
	246	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	247	if (indItem == null) {
	248	return result;
	249	}
	250	result = getAtomSql(indItem.getAtomItemId());
	251
664918	252	IndItemEntity itemEntity = indItemService.get(itemId);
潘	253	result.setItemNo(itemEntity.getItemNo());
	254
cf757d	255	// 拼接SELECT
潘	256	StringBuilder selectSql = new StringBuilder();
2cb72f	257	if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
D	258	selectSql.append(indItem.getDimension());
	259	selectSql.append(", ");
	260	selectSql.append(result.getSelectSql());
	261	} else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
	262	selectSql.append(result.getSelectSql());
	263	} else {
	264	selectSql.append(result.getSelectSql());
	265	}
a03a19	266	selectSql.append(", ");
潘	267	selectSql.append(indItem.getTimeLabel());
	268	selectSql.append(" data_time");
cf757d	269	result.setSelectSql(selectSql.toString());
潘	270
	271	// 拼接WHERE
	272	StringBuilder whereSql = new StringBuilder();
	273	whereSql.append(" ");
	274	whereSql.append(indItem.getTimeLabel());
	275	whereSql.append(" <= '");
2cb72f	276	whereSql.append(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
D	277	whereSql.append("' AND ");
	278	whereSql.append(indItem.getTimeLabel());
	279	whereSql.append(" >= '");
	280	whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
cf757d	281	whereSql.append("'");
潘	282	result.setWhereSql(whereSql.toString());
	283
	284	// 拼接GROUP
	285	StringBuilder groupSql = new StringBuilder();
2cb72f	286	if (StringUtils.isNotBlank(indItem.getDimension())) {
D	287	groupSql.append(indItem.getDimension());
	288	}
cf757d	289	result.setGroupSql(groupSql.toString());
潘	290
	291	// 拼接ORDER
	292	StringBuilder orderBySql = new StringBuilder();
2cb72f	293	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
D	294	orderBySql.append(indItem.getTimeLabel());
	295	result.setOrderBySql(orderBySql.toString());
	296	}
	297
cf757d	298	return result;
潘	299	}
	300	}