iailab-plat.git

潘志宝

2024-12-15 bbe7acfbe5a4c08d6edc91eaf81dcecf9d630e18

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