iailab-plat.git

Merge remote-tracking branch 'origin/master'

Jay

2025-01-20 280ca0c6a4a1e73ab4516d4850dedb5a43541594

提交 \| 用户 \| 时间
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	calendar.add(Calendar.MONTH, -1); // 将当前日期减去一个月，得到上个月的日期
	168	whereSql.append(indItem.getTimeLabel());
	169	whereSql.append(" >= '");
	170	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON)); // 上个月的第一天
	171	calendar.add(Calendar.MONTH, 1); // 再加一个月，回到当前月
	172	whereSql.append("' AND '");
	173	whereSql.append(indItem.getTimeLabel());
	174	whereSql.append("' < '");
	175	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON)); // 当前月
	176	whereSql.append("'");
	177	break;
cf757d	178	case LAST_MONTH_12:
潘	179	whereSql.append(indItem.getTimeLabel());
	180	whereSql.append(" <= '");
	181	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
5bc3b4	182	whereSql.append("' AND '");
J	183	whereSql.append(indItem.getTimeLabel());
	184	whereSql.append("' >= '");
cf757d	185	calendar.add(Calendar.MONTH, -12);
潘	186	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	187	whereSql.append("'");
	188	break;
	189	case THIS_YEAR_MONTH:
2cb72f	190	whereSql.append(indItem.getTimeLabel());
cf757d	191	whereSql.append(" <= '");
2cb72f	192	calendar.set(Calendar.MONTH, 11);
cf757d	193	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
2cb72f	194	whereSql.append("' AND ");
D	195	whereSql.append(indItem.getTimeLabel());
	196	whereSql.append(" >= '");
cf757d	197	calendar.set(Calendar.MONTH, 0);
潘	198	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	199	whereSql.append("'");
	200	break;
	201	case LAST_YEAR:
	202	calendar.add(Calendar.YEAR, -1);
	203	whereSql.append(indItem.getTimeLabel());
	204	whereSql.append("='");
	205	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	206	whereSql.append("'");
	207	break;
	208	case THIS_YEAR:
	209	whereSql.append(indItem.getTimeLabel());
	210	whereSql.append("='");
	211	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	212	whereSql.append("'");
	213	break;
	214	case CUSTOM:
cfa57a	215	whereSql.append(indItem.getTimeLabel());
cf757d	216	whereSql.append(" <= '");
潘	217	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
2056b4	218	whereSql.append("' AND ");
cfa57a	219	whereSql.append(indItem.getTimeLabel());
2056b4	220	whereSql.append(" >= '");
cf757d	221	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
潘	222	whereSql.append("'");
	223	break;
	224	default:
	225	break;
	226	}
	227	result.setWhereSql(whereSql.toString());
	228
	229	// 拼接GROUP
	230	StringBuilder groupSql = new StringBuilder();
a2903e	231	if (StringUtils.isNotBlank(indItem.getDimension())) {
5bc3b4	232	groupSql.append(indItem.getDimension());
J	233	}
cf757d	234	result.setGroupSql(groupSql.toString());
潘	235
	236	// 拼接ORDER
	237	StringBuilder orderBySql = new StringBuilder();
a2903e	238	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
5bc3b4	239	orderBySql.append(indItem.getTimeLabel());
J	240	result.setOrderBySql(orderBySql.toString());
	241	}
cf757d	242	return result;
潘	243	}
	244
	245	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	246	QuerySourceValueDTO result = getAtomSql(itemId);
	247	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	248	if (indItem == null) {
	249	return result;
	250	}
	251	result = getAtomSql(indItem.getAtomItemId());
	252
664918	253	IndItemEntity itemEntity = indItemService.get(itemId);
潘	254	result.setItemNo(itemEntity.getItemNo());
	255
cf757d	256	// 拼接SELECT
潘	257	StringBuilder selectSql = new StringBuilder();
2cb72f	258	if (StringUtils.isNotBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
D	259	selectSql.append(indItem.getDimension());
	260	selectSql.append(", ");
	261	selectSql.append(result.getSelectSql());
	262	} else if (StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())) {
	263	selectSql.append(result.getSelectSql());
	264	} else {
	265	selectSql.append(result.getSelectSql());
	266	selectSql.append(", ");
	267	selectSql.append(indItem.getTimeLabel());
	268	selectSql.append(" data_time");
	269	}
cf757d	270	result.setSelectSql(selectSql.toString());
潘	271
	272	// 拼接WHERE
	273	StringBuilder whereSql = new StringBuilder();
	274	whereSql.append(" ");
	275	whereSql.append(indItem.getTimeLabel());
	276	whereSql.append(" <= '");
2cb72f	277	whereSql.append(DateUtils.format(endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
D	278	whereSql.append("' AND ");
	279	whereSql.append(indItem.getTimeLabel());
	280	whereSql.append(" >= '");
	281	whereSql.append(DateUtils.format(startTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
cf757d	282	whereSql.append("'");
潘	283	result.setWhereSql(whereSql.toString());
	284
	285	// 拼接GROUP
	286	StringBuilder groupSql = new StringBuilder();
2cb72f	287	if (StringUtils.isNotBlank(indItem.getDimension())) {
D	288	groupSql.append(indItem.getDimension());
	289	}
cf757d	290	result.setGroupSql(groupSql.toString());
潘	291
	292	// 拼接ORDER
	293	StringBuilder orderBySql = new StringBuilder();
2cb72f	294	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())) {
D	295	orderBySql.append(indItem.getTimeLabel());
	296	result.setOrderBySql(orderBySql.toString());
	297	}
	298
cf757d	299	return result;
潘	300	}
	301	}