iailab-plat.git

Merge remote-tracking branch 'origin/master'

潘志宝

2024-10-15 856d69ce678173e0a8e165bcb0135037d20b48ee

提交 \| 用户 \| 时间
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	}
	53	IndDataSetDTO dataSet = indDataSetService.getDet(indItem.getDataSet());
	54	if (dataSet == null) {
	55	return result;
	56	}
	57	result.setDataSource(indItem.getDataSource());
	58	StringBuilder selectSql = new StringBuilder();
cfa57a	59	IndStatFuncEnum statFun = IndStatFuncEnum.getEumByCode(StringUtils.isBlank(indItem.getStatFunc())? IndStatFuncEnum.DEFAULT.getCode() : indItem.getStatFunc());
cf757d	60	switch (statFun) {
潘	61	case AVG:
	62	selectSql.append("AVG(");
	63	break;
	64	case COUNT:
	65	selectSql.append("COUNT(");
	66	break;
	67	case MAX:
	68	selectSql.append("MAX(");
	69	break;
	70	case MIN:
	71	selectSql.append("MIN(");
	72	break;
	73	case SUM:
	74	selectSql.append("SUM(");
	75	break;
cfa57a	76	case DEFAULT:
cf757d	77	selectSql.append("(");
潘	78	break;
	79	}
	80	selectSql.append(indItem.getUsingField());
	81	selectSql.append(") data_value");
	82	result.setSelectSql(selectSql.toString());
	83	result.setViewSql(dataSet.getQuerySql());
	84	return result;
	85	}
	86
	87	public QuerySourceValueDTO getDerSql(String itemId) {
	88	QuerySourceValueDTO result = getAtomSql(itemId);
	89	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	90	if (indItem == null) {
	91	return result;
	92	}
	93	result = getAtomSql(indItem.getAtomItemId());
	94
	95	// 拼接SELECT
	96	StringBuilder selectSql = new StringBuilder();
cfa57a	97	if (StringUtils.isNotBlank(indItem.getDimension())){
J	98	selectSql.append(indItem.getDimension());
	99	selectSql.append(", ");
5bc3b4	100	selectSql.append(result.getSelectSql());
J	101	}else{
	102	selectSql.append(result.getSelectSql());
	103	selectSql.append(", ");
	104	selectSql.append(indItem.getTimeLabel());
	105	selectSql.append(" data_time");
cfa57a	106	}
cf757d	107	result.setSelectSql(selectSql.toString());
潘	108
	109	// 拼接WHERE
	110	StringBuilder whereSql = new StringBuilder();
	111	whereSql.append(" ");
	112	Calendar calendar = Calendar.getInstance();
	113	calendar.set(Calendar.MILLISECOND, 0);
	114	calendar.set(Calendar.SECOND, 0);
	115	IndTimeLimitEnum timeLimit = IndTimeLimitEnum.getEumByCode(indItem.getTimeLimit());
	116	switch (timeLimit) {
	117	case TODAY:
	118	whereSql.append(indItem.getTimeLabel());
	119	whereSql.append("='");
	120	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	121	whereSql.append("'");
	122	break;
	123	case YESTERDAY:
	124	calendar.add(Calendar.DAY_OF_YEAR, -1);
	125	whereSql.append(indItem.getTimeLabel());
	126	whereSql.append("='");
	127	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	128	whereSql.append("'");
	129	break;
	130	case LAST_DAY_7:
	131	whereSql.append(indItem.getTimeLabel());
	132	whereSql.append(" <= '");
	133	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	134	whereSql.append("' AND >= '");
	135	calendar.add(Calendar.DAY_OF_YEAR, -7);
	136	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	137	whereSql.append("'");
	138	break;
	139	case LAST_DAY_30:
	140	whereSql.append(indItem.getTimeLabel());
	141	whereSql.append(" <= '");
	142	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	143	whereSql.append("' AND >= '");
	144	calendar.add(Calendar.DAY_OF_YEAR, -30);
	145	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_DATE));
	146	whereSql.append("'");
	147	break;
	148	case THIS_MONTH:
	149	whereSql.append(indItem.getTimeLabel());
	150	whereSql.append("='");
	151	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	152	whereSql.append("'");
	153	break;
	154	case LAST_MONTH_12:
	155	whereSql.append(indItem.getTimeLabel());
	156	whereSql.append(" <= '");
	157	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
5bc3b4	158	whereSql.append("' AND '");
J	159	whereSql.append(indItem.getTimeLabel());
	160	whereSql.append("' >= '");
cf757d	161	calendar.add(Calendar.MONTH, -12);
潘	162	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	163	whereSql.append("'");
	164	break;
	165	case THIS_YEAR_MONTH:
	166	calendar.set(Calendar.MONTH, 11);
	167	whereSql.append(" <= '");
	168	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	169	whereSql.append("' AND >= '");
	170	calendar.set(Calendar.MONTH, 0);
	171	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_MON));
	172	whereSql.append("'");
	173	break;
	174	case LAST_YEAR:
	175	calendar.add(Calendar.YEAR, -1);
	176	whereSql.append(indItem.getTimeLabel());
	177	whereSql.append("='");
	178	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	179	whereSql.append("'");
	180	break;
	181	case THIS_YEAR:
	182	whereSql.append(indItem.getTimeLabel());
	183	whereSql.append("='");
	184	whereSql.append(DateUtils.format(calendar.getTime(), PATTERN_YEAR));
	185	whereSql.append("'");
	186	break;
	187	case CUSTOM:
cfa57a	188	whereSql.append(indItem.getTimeLabel());
cf757d	189	whereSql.append(" <= '");
潘	190	whereSql.append(DateUtils.format(indItem.getTimeEnd(), PATTERN_MON));
cfa57a	191	whereSql.append("' AND '");
J	192	whereSql.append(indItem.getTimeLabel());
	193	whereSql.append("' >= '");
cf757d	194	whereSql.append(DateUtils.format(indItem.getTimeStart(), PATTERN_MON));
潘	195	whereSql.append("'");
	196	break;
	197	default:
	198	break;
	199	}
	200	result.setWhereSql(whereSql.toString());
	201
	202	// 拼接GROUP
	203	StringBuilder groupSql = new StringBuilder();
5bc3b4	204	if (StringUtils.isNotBlank(indItem.getDimension())){
J	205	groupSql.append(indItem.getDimension());
	206	}
cf757d	207	result.setGroupSql(groupSql.toString());
潘	208
	209	// 拼接ORDER
	210	StringBuilder orderBySql = new StringBuilder();
5bc3b4	211	if (StringUtils.isNotBlank(indItem.getDimension()) && indItem.getDimension().equals(indItem.getTimeLabel())){
J	212	orderBySql.append(indItem.getTimeLabel());
	213	result.setOrderBySql(orderBySql.toString());
	214	}
cf757d	215	return result;
潘	216	}
	217
	218	public QuerySourceValueDTO getDerSql(String itemId, Date startTime, Date endTime) {
	219	QuerySourceValueDTO result = getAtomSql(itemId);
	220	IndItemDerVO indItem = indItemDerService.getByItemId(itemId);
	221	if (indItem == null) {
	222	return result;
	223	}
	224	result = getAtomSql(indItem.getAtomItemId());
	225
	226	// 拼接SELECT
	227	StringBuilder selectSql = new StringBuilder();
	228	selectSql.append(indItem.getDimension());
	229	selectSql.append(", ");
	230	selectSql.append(result.getSelectSql());
	231	selectSql.append(", ");
	232	selectSql.append(indItem.getTimeLabel());
	233	selectSql.append(" data_time");
	234	result.setSelectSql(selectSql.toString());
	235
	236	// 拼接WHERE
	237	StringBuilder whereSql = new StringBuilder();
	238	whereSql.append(" ");
	239	whereSql.append(indItem.getTimeLabel());
	240	whereSql.append(" <= '");
	241	whereSql.append(DateUtils.format(endTime, PATTERN_MON));
	242	whereSql.append("' AND >= '");
	243	whereSql.append(DateUtils.format(startTime, PATTERN_MON));
	244	whereSql.append("'");
	245	result.setWhereSql(whereSql.toString());
	246
	247	// 拼接GROUP
	248	StringBuilder groupSql = new StringBuilder();
	249	groupSql.append(" GROUP BY ");
	250	groupSql.append(indItem.getDimension());
	251	result.setGroupSql(groupSql.toString());
	252
	253	// 拼接ORDER
	254	StringBuilder orderBySql = new StringBuilder();
	255	orderBySql.append(indItem.getTimeLabel());
	256	result.setOrderBySql(orderBySql.toString());
	257	return result;
	258	}
	259	}