iailab-plat.git

Merge remote-tracking branch 'origin/master'

houzhongjian

2025-01-17 60fa13f76afe72a6f48416de2904d58164859382

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