1 package net.sumaris.core.extraction.dao.trip.rdb;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 import com.google.common.base.Preconditions;
26 import com.google.common.collect.*;
27 import net.sumaris.core.dao.technical.SortDirection;
28 import net.sumaris.core.dao.technical.schema.SumarisDatabaseMetadata;
29 import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
30 import net.sumaris.core.exception.SumarisTechnicalException;
31 import net.sumaris.core.extraction.dao.technical.ExtractionBaseDaoImpl;
32 import net.sumaris.core.extraction.dao.technical.XMLQuery;
33 import net.sumaris.core.extraction.dao.technical.schema.SumarisTableMetadatas;
34 import net.sumaris.core.extraction.dao.technical.table.ExtractionTableDao;
35 import net.sumaris.core.extraction.dao.trip.AggregationTripDao;
36 import net.sumaris.core.extraction.vo.*;
37 import net.sumaris.core.extraction.vo.trip.rdb.AggregationRdbTripContextVO;
38 import net.sumaris.core.extraction.vo.trip.rdb.ExtractionRdbTripVersion;
39 import net.sumaris.core.model.referential.pmfm.PmfmEnum;
40 import net.sumaris.core.model.technical.extraction.rdb.ProductRdbStation;
41 import net.sumaris.core.service.administration.programStrategy.ProgramService;
42 import net.sumaris.core.service.administration.programStrategy.StrategyService;
43 import net.sumaris.core.util.StringUtils;
44 import net.sumaris.core.vo.technical.extraction.ExtractionProductVO;
45 import org.slf4j.Logger;
46 import org.slf4j.LoggerFactory;
47 import org.springframework.beans.factory.annotation.Autowired;
48 import org.springframework.context.annotation.Lazy;
49 import org.springframework.core.io.Resource;
50 import org.springframework.core.io.ResourceLoader;
51 import org.springframework.stereotype.Repository;
52
53 import java.io.IOException;
54 import java.net.URL;
55 import java.util.List;
56 import java.util.Map;
57 import java.util.Set;
58 import java.util.stream.Collectors;
59 import java.util.stream.Stream;
60
61 import static org.nuiton.i18n.I18n.t;
62
63
64
65
66 @Repository("aggregationRdbDao")
67 @Lazy
68 public class AggregationRdbTripDaoImpl<
69 C extends AggregationRdbTripContextVO,
70 F extends ExtractionFilterVO,
71 S extends AggregationStrataVO>
72 extends ExtractionBaseDaoImpl
73 implements AggregationRdbTripDao<C, F, S>, AggregationTripDao {
74
75 private static final Logger log = LoggerFactory.getLogger(AggregationRdbTripDaoImpl.class);
76
77 private static final String TR_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + TR_SHEET_NAME + "_%s";
78 private static final String HH_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HH_SHEET_NAME + "_%s";
79 private static final String SL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + SL_SHEET_NAME + "_%s";
80 private static final String HL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HL_SHEET_NAME + "_%s";
81 private static final String CA_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + CA_SHEET_NAME + "_%s";
82
83 private static List<String> SPACE_STRATA = ImmutableList.of("area", "rect", "square");
84 private static List<String> TIME_STRATA = ImmutableList.of("year", "quarter", "month");
85 private static Map<String, List<String>> AGG_STRATA_BY_SHEETNAME = ImmutableMap.<String, List<String>>builder()
86 .put(HH_SHEET_NAME, ImmutableList.of(COLUMN_TRIP_COUNT, COLUMN_STATION_COUNT))
87 .build();
88
89
90 @Autowired
91 protected StrategyService strategyService;
92
93 @Autowired
94 protected ProgramService programService;
95
96 @Autowired
97 protected ResourceLoader resourceLoader;
98
99 @Autowired
100 protected SumarisDatabaseMetadata databaseMetadata;
101
102 @javax.annotation.Resource(name = "extractionRdbTripDao")
103 protected ExtractionRdbTripDao extractionRdbTripDao;
104
105 @Autowired
106 protected ExtractionTableDao extractionTableDao;
107
108 @Override
109 public <R extends C> R aggregate(ExtractionProductVO source, F filter) {
110
111
112 R context = createNewContext();
113 context.setTripFilter(extractionRdbTripDao.toTripFilterVO(filter));
114 context.setFilter(filter);
115 context.setFormatName(RDB_FORMAT);
116 context.setFormatVersion(ExtractionRdbTripVersion.VERSION_1_3.getLabel());
117 context.setId(System.currentTimeMillis());
118
119
120 context.setTripTableName(String.format(TR_TABLE_NAME_PATTERN, context.getId()));
121 context.setStationTableName(String.format(HH_TABLE_NAME_PATTERN, context.getId()));
122 context.setSpeciesListTableName(String.format(SL_TABLE_NAME_PATTERN, context.getId()));
123 context.setSpeciesLengthTableName(String.format(HL_TABLE_NAME_PATTERN, context.getId()));
124 context.setSampleTableName(String.format(CA_TABLE_NAME_PATTERN, context.getId()));
125
126
127 String sheetName = filter != null && filter.isPreview() ? filter.getSheetName() : null;
128
129
130 long rowCount;
131
132
133
134
135
136 rowCount = createStationTable(source, context);
137 if (rowCount == 0) return context;
138 if (sheetName != null && context.hasSheet(sheetName)) return context;
139
140
141
142
143
144
145
146
147
148 return context;
149
150 }
151
152 public AggregationResultVO read(String tableName, F filter, S strata, int offset, int size, String sortAttribute, SortDirection direction) {
153 Preconditions.checkNotNull(tableName);
154 Preconditions.checkNotNull(strata);
155
156 Set<String> groupByColumnNames = Sets.newLinkedHashSet();
157 Map<String, ExtractionTableDao.SQLAggregatedFunction> aggColumns = Maps.newHashMap();
158
159
160 {
161 String spaceStrata = strata.getSpaceColumnName() != null ? strata.getSpaceColumnName().toLowerCase() : COLUMN_AREA;
162
163
164 if (COLUMN_ALIAS.containsKey(spaceStrata)) spaceStrata = COLUMN_ALIAS.get(spaceStrata);
165
166 switch (spaceStrata) {
167 case COLUMN_SQUARE:
168 groupByColumnNames.add(COLUMN_SQUARE);
169 case COLUMN_STATISTICAL_RECTANGLE:
170 groupByColumnNames.add(COLUMN_STATISTICAL_RECTANGLE);
171 case COLUMN_AREA:
172 default:
173 groupByColumnNames.add(COLUMN_AREA);
174 }
175 }
176
177
178 {
179 String timeStrata = strata.getTimeColumnName() != null ? strata.getTimeColumnName().toLowerCase() : COLUMN_YEAR;
180 switch (timeStrata) {
181 case COLUMN_MONTH:
182 groupByColumnNames.add(COLUMN_MONTH);
183 case COLUMN_QUARTER:
184 groupByColumnNames.add(COLUMN_QUARTER);
185 case COLUMN_YEAR:
186 default:
187 groupByColumnNames.add(COLUMN_YEAR);
188 }
189 }
190
191
192 {
193 String aggStrata = strata.getAggColumnName() != null ? strata.getAggColumnName().toLowerCase() : COLUMN_STATION_COUNT;
194 ExtractionTableDao.SQLAggregatedFunction function = strata.getAggFunction() != null ?
195 ExtractionTableDao.SQLAggregatedFunction.valueOf(strata.getAggFunction().toUpperCase()) :
196 ExtractionTableDao.SQLAggregatedFunction.SUM;
197 aggColumns.put(aggStrata, function);
198 }
199
200 ExtractionResultVO rows = extractionTableDao.getTableGroupByRows(tableName, filter, groupByColumnNames, aggColumns,
201 offset, size, sortAttribute, direction);
202
203 AggregationResultVOggregationResultVO.html#AggregationResultVO">AggregationResultVO result = new AggregationResultVO(rows);
204
205 result.setSpaceStrata(SPACE_STRATA);
206 result.setTimeStrata(TIME_STRATA);
207 if (filter.getSheetName() != null) {
208 result.setAggStrata(AGG_STRATA_BY_SHEETNAME.get(filter.getSheetName()));
209 }
210
211 return result;
212 }
213
214
215
216
217 protected <C extends AggregationRdbTripContextVO> C createNewContext() {
218 Class<? extends AggregationRdbTripContextVO> contextClass = getContextClass();
219 Preconditions.checkNotNull(contextClass);
220
221 try {
222 return (C) contextClass.newInstance();
223 } catch (Exception e) {
224 throw new SumarisTechnicalException("Could not create an instance of context class " + contextClass.getName());
225 }
226 }
227
228 protected Class<? extends AggregationRdbTripContextVO> getContextClass() {
229 return AggregationRdbTripContextVO.class;
230 }
231
232 protected long createStationTable(ExtractionProductVO source, AggregationRdbTripContextVO context) {
233
234 String tableName = context.getStationTableName();
235 XMLQuery xmlQuery = createStationQuery(source, context);
236
237
238 execute(xmlQuery);
239 long count = countFrom(tableName);
240
241 if (count == 0) return 0;
242
243
244 count -= cleanRow(tableName, context.getFilter(), HH_SHEET_NAME);
245
246
247 Map<String, List<String>> columnValues = null;
248 if (context.isEnableAnalyze()) {
249 columnValues = analyzeRow(tableName, xmlQuery, COLUMN_YEAR);
250 }
251
252
253 context.addTableName(tableName, HH_SHEET_NAME,
254 xmlQuery.getHiddenColumnNames(),
255 getSpatialColumnNames(xmlQuery),
256 columnValues);
257 log.debug(String.format("Station table: %s rows inserted", count));
258
259 return count;
260 }
261
262 protected XMLQuery createStationQuery(ExtractionProductVO source, AggregationRdbTripContextVO context) {
263
264 String rawStationTableName = source.getTableNameBySheetName(ExtractionRdbTripDao.HH_SHEET_NAME)
265 .orElseThrow(() -> new SumarisTechnicalException("Missing HH table"));
266 SumarisTableMetadata rawStationTable = databaseMetadata.getTable(rawStationTableName);
267
268
269 XMLQuery xmlQuery = createXMLQuery(context, "createStationTable");
270 xmlQuery.bind("rawTripTableName", source.getTableNameBySheetName(ExtractionRdbTripDao.TR_SHEET_NAME)
271 .orElseThrow(() -> new SumarisTechnicalException("Missing TR table")));
272 xmlQuery.bind("rawStationTableName", rawStationTableName);
273 xmlQuery.bind("stationTableName", context.getStationTableName());
274
275 xmlQuery.setGroup("gearType", rawStationTable.getColumnMetadata(ProductRdbStation.COLUMN_GEAR_TYPE) != null);
276
277 return xmlQuery;
278 }
279
280 protected long createSpeciesListTable(AggregationRdbTripContextVO context) {
281
282 XMLQuery xmlQuery = createSpeciesListQuery(context, true);
283
284
285 execute(xmlQuery);
286 long count = countFrom(context.getSpeciesListTableName());
287
288
289 if (count > 0) {
290 count -= cleanRow(context.getSpeciesListTableName(), context.getFilter(), SL_SHEET_NAME);
291 }
292
293
294 if (count > 0) {
295 context.addTableName(context.getSpeciesListTableName(), SL_SHEET_NAME);
296 log.debug(String.format("Species list table: %s rows inserted", count));
297 }
298 return count;
299 }
300
301 protected XMLQuery createSpeciesListQuery(AggregationRdbTripContextVO context, boolean excludeInvalidStation) {
302 XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesListTable");
303 xmlQuery.bind("stationTableName", context.getStationTableName());
304 xmlQuery.bind("speciesListTableName", context.getSpeciesListTableName());
305
306
307 xmlQuery.bind("catchCategoryPmfmId", String.valueOf(PmfmEnum.DISCARD_OR_LANDING.getId()));
308
309
310 xmlQuery.setGroup("excludeInvalidStation", excludeInvalidStation);
311
312 return xmlQuery;
313 }
314
315
316 protected long createSpeciesLengthTable(AggregationRdbTripContextVO context) {
317
318 XMLQuery xmlQuery = createSpeciesLengthQuery(context);
319
320
321 execute(xmlQuery);
322 long count = countFrom(context.getSpeciesLengthTableName());
323
324
325 if (count > 0) {
326 count -= cleanRow(context.getSpeciesLengthTableName(), context.getFilter(), HL_SHEET_NAME);
327 }
328
329
330 if (count > 0) {
331 context.addTableName(context.getSpeciesLengthTableName(), HL_SHEET_NAME);
332 log.debug(String.format("Species length table: %s rows inserted", count));
333 }
334 return count;
335 }
336
337 protected XMLQuery createSpeciesLengthQuery(AggregationRdbTripContextVO context) {
338 XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesLengthTable");
339 xmlQuery.bind("stationTableName", context.getStationTableName());
340 xmlQuery.bind("speciesListTableName", context.getSpeciesListTableName());
341 xmlQuery.bind("speciesLengthTableName", context.getSpeciesLengthTableName());
342
343
344 xmlQuery.bind("sexPmfmId", String.valueOf(PmfmEnum.SEX.getId()));
345 xmlQuery.bind("lengthTotalCmPmfmId", String.valueOf(PmfmEnum.LENGTH_TOTAL_CM.getId()));
346
347 return xmlQuery;
348 }
349
350 protected int execute(XMLQuery xmlQuery) {
351 return queryUpdate(xmlQuery.getSQLQueryAsString());
352 }
353
354 protected long countFrom(String tableName) {
355 return extractionTableDao.getRowCount(tableName);
356 }
357
358 protected String getQueryFullName(ExtractionContextVO context, String queryName) {
359 Preconditions.checkNotNull(context);
360 Preconditions.checkNotNull(context.getFormatName());
361 Preconditions.checkNotNull(context.getFormatVersion());
362
363 return String.format("%s/v%s/aggregation/%s",
364 context.getFormatName(),
365 context.getFormatVersion().replaceAll("[.]", "_"),
366 queryName);
367 }
368
369 protected XMLQuery createXMLQuery(ExtractionContextVO context, String queryName) {
370 return createXMLQuery(getQueryFullName(context, queryName));
371 }
372
373 protected XMLQuery createXMLQuery(String queryName) {
374 XMLQuery query = createXMLQuery();
375 query.setQuery(getXMLQueryClasspathURL(queryName));
376 return query;
377 }
378
379 protected URL getXMLQueryURL(ExtractionContextVO context, String queryName) {
380 return getXMLQueryClasspathURL(getQueryFullName(context, queryName));
381 }
382
383 protected URL getXMLQueryClasspathURL(String queryName) {
384 Resource resource = resourceLoader.getResource(ResourceLoader.CLASSPATH_URL_PREFIX + XML_QUERY_PATH + "/" + queryName + ".xml");
385 if (!resource.exists())
386 throw new SumarisTechnicalException(t("sumaris.extraction.xmlQuery.notFound", queryName));
387 try {
388 return resource.getURL();
389 } catch (IOException e) {
390 throw new SumarisTechnicalException(e);
391 }
392 }
393
394 protected int cleanRow(String tableName, ExtractionFilterVO filter, String sheetName) {
395 Preconditions.checkNotNull(tableName);
396 if (filter == null) return 0;
397
398
399 SumarisTableMetadata table = databaseMetadata.getTable(tableName.toLowerCase());
400 Preconditions.checkNotNull(table);
401
402 String whereClauseContent = SumarisTableMetadatas.getSqlWhereClauseContent(table, filter, sheetName, table.getAlias());
403 if (StringUtils.isBlank(whereClauseContent)) return 0;
404
405 String deleteQuery = table.getDeleteQuery(String.format("NOT(%s)", whereClauseContent));
406 return queryUpdate(deleteQuery);
407 }
408
409
410 protected Map<String, List<String>> analyzeRow(final String tableName, XMLQuery xmlQuery, String... includedNumericColumnNames) {
411 Preconditions.checkNotNull(tableName);
412 Preconditions.checkNotNull(xmlQuery);
413
414 return Stream.concat(xmlQuery.getNotNumericColumnNames().stream(), Stream.of(includedNumericColumnNames))
415 .collect(Collectors.toMap(
416 c -> c,
417 c -> query(String.format("SELECT DISTINCT %s FROM %s where %s IS NOT NULL", c, tableName, c), Object.class)
418 .stream().map(String::valueOf).collect(Collectors.toList())
419 )
420 );
421 }
422
423 protected Set<String> getSpatialColumnNames(final XMLQuery xmlQuery) {
424 return xmlQuery.getVisibleColumnNames()
425 .stream()
426 .map(c -> c.toLowerCase())
427 .filter(SPACE_STRATA::contains)
428 .collect(Collectors.toSet());
429 }
430 }