iailab-plat.git

潘志宝

2025-01-16 664918f1849102cd6bbb500a55acad19b9f68e6d

提交 \| 用户 \| 时间
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();
cfa57a	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();
2056b4	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());
2056b4	107	}
902dd0	108	else if(StringUtils.isBlank(indItem.getDimension()) && StringUtils.isNotBlank(result.getIndItemAtom().getStatFunc())){
2056b4	109	selectSql.append(result.getSelectSql());
J	110	}
	111	else{
5bc3b4	112	selectSql.append(result.getSelectSql());
J	113	selectSql.append(", ");
	114	selectSql.append(indItem.getTimeLabel());
	115	selectSql.append(" data_time");
cfa57a	116	}
cf757d	117	result.setSelectSql(selectSql.toString());
潘	118
	119	// 拼接WHERE
	120	StringBuilder whereSql = new StringBuilder();
	121	whereSql.append(" ");
	122	Calendar calendar = Calendar.getInstance();
	123	calendar.set(Calendar.MILLISECOND, 0);
	124	calendar.set(Calendar.SECOND, 0);
	125	IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
	126	switch (timeLimit) {
	127	case TODAY:
	128	whereSql.append(indItem.getTimeLabel());
	129	whereSql.append("='");
	130	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	131	whereSql.append("'");
	132	break;
	133	case YESTERDAY:
	134	calendar.add(Calendar.DAY_OF_YEAR, -1);
	135	whereSql.append(indItem.getTimeLabel());
	136	whereSql.append("='");
	137	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	138	whereSql.append("'");
	139	break;
	140	case LAST_DAY_7:
	141	whereSql.append(indItem.getTimeLabel());
	142	whereSql.append(" <= '");
	143	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	144	whereSql.append("' AND >= '");
	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));
	153	whereSql.append("' AND >= '");
	154	calendar.add(Calendar.DAY_OF_YEAR, -30);
	155	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	156	whereSql.append("'");
	157	break;
	158	case THIS_MONTH:
	159	whereSql.append(indItem.getTimeLabel());
	160	whereSql.append("='");
	161	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	162	whereSql.append("'");
	163	break;
	164	case LAST_MONTH_12:
	165	whereSql.append(indItem.getTimeLabel());
	166	whereSql.append(" <= '");
	167	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
5bc3b4	168	whereSql.append("' AND '");
J	169	whereSql.append(indItem.getTimeLabel());
	170	whereSql.append("' >= '");
cf757d	171	calendar.add(Calendar.MONTH, -12);
潘	172	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	173	whereSql.append("'");
	174	break;
	175	case THIS_YEAR_MONTH:
	176	calendar.set(Calendar.MONTH, 11);
	177	whereSql.append(" <= '");
	178	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	179	whereSql.append("' AND >= '");
	180	calendar.set(Calendar.MONTH, 0);
	181	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	182	whereSql.append("'");
	183	break;
	184	case LAST_YEAR:
	185	calendar.add(Calendar.YEAR, -1);
	186	whereSql.append(indItem.getTimeLabel());
	187	whereSql.append("='");
	188	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	189	whereSql.append("'");
	190	break;
	191	case THIS_YEAR:
	192	whereSql.append(indItem.getTimeLabel());
	193	whereSql.append("='");
	194	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	195	whereSql.append("'");
	196	break;
	197	case CUSTOM:
cfa57a	198	whereSql.append(indItem.getTimeLabel());
cf757d	199	whereSql.append(" <= '");
潘	200	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
2056b4	201	whereSql.append("' AND ");
cfa57a	202	whereSql.append(indItem.getTimeLabel());
2056b4	203	whereSql.append(" >= '");
cf757d	204	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
潘	205	whereSql.append("'");
	206	break;
	207	default:
	208	break;
	209	}
	210	result.setWhereSql(whereSql.toString());
	211
	212	// 拼接GROUP
	213	StringBuilder groupSql = new StringBuilder();
5bc3b4	214	if (StringUtils.isNotBlank(indItem.getDimension())){
J	215	groupSql.append(indItem.getDimension());
	216	}
cf757d	217	result.setGroupSql(groupSql.toString());
潘	218
	219	// 拼接ORDER
	220	StringBuilder orderBySql = new StringBuilder();
5bc3b4	221	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())){
J	222	orderBySql.append(indItem.getTimeLabel());
	223	result.setOrderBySql(orderBySql.toString());
	224	}
cf757d	225	return result;
潘	226	}
	227
	228	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	229	QuerySourceValueDTO result = getAtomSql(itemId);
	230	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	231	if (indItem == null) {
	232	return result;
	233	}
	234	result = getAtomSql(indItem.getAtomItemId());
	235
664918	236	IndItemEntity itemEntity = indItemService.get(itemId);
潘	237	result.setItemNo(itemEntity.getItemNo());
	238
cf757d	239	// 拼接SELECT
潘	240	StringBuilder selectSql = new StringBuilder();
	241	selectSql.append(indItem.getDimension());
	242	selectSql.append(", ");
	243	selectSql.append(result.getSelectSql());
	244	selectSql.append(", ");
	245	selectSql.append(indItem.getTimeLabel());
	246	selectSql.append(" data_time");
	247	result.setSelectSql(selectSql.toString());
	248
	249	// 拼接WHERE
	250	StringBuilder whereSql = new StringBuilder();
	251	whereSql.append(" ");
	252	whereSql.append(indItem.getTimeLabel());
	253	whereSql.append(" <= '");
	254	whereSql.append(DateUtils.format(endTime, PATTERN_MON));
	255	whereSql.append("' AND >= '");
	256	whereSql.append(DateUtils.format(startTime, PATTERN_MON));
	257	whereSql.append("'");
	258	result.setWhereSql(whereSql.toString());
	259
	260	// 拼接GROUP
	261	StringBuilder groupSql = new StringBuilder();
	262	groupSql.append(" GROUP BY ");
	263	groupSql.append(indItem.getDimension());
	264	result.setGroupSql(groupSql.toString());
	265
	266	// 拼接ORDER
	267	StringBuilder orderBySql = new StringBuilder();
	268	orderBySql.append(indItem.getTimeLabel());
	269	result.setOrderBySql(orderBySql.toString());
	270	return result;
	271	}
	272	}