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 }