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 net.sumaris.core.dao.technical.schema.SumarisDatabaseMetadata;
27 import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
28 import net.sumaris.core.exception.DataNotFoundException;
29 import net.sumaris.core.exception.SumarisTechnicalException;
30 import net.sumaris.core.extraction.dao.technical.Daos;
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.vo.ExtractionFilterVO;
35 import net.sumaris.core.extraction.vo.ExtractionPmfmInfoVO;
36 import net.sumaris.core.extraction.vo.trip.rdb.ExtractionRdbTripContextVO;
37 import net.sumaris.core.extraction.vo.trip.rdb.ExtractionRdbTripVersion;
38 import net.sumaris.core.model.referential.location.LocationLevel;
39 import net.sumaris.core.model.referential.location.LocationLevelEnum;
40 import net.sumaris.core.model.referential.pmfm.PmfmEnum;
41 import net.sumaris.core.model.referential.pmfm.UnitEnum;
42 import net.sumaris.core.service.administration.programStrategy.ProgramService;
43 import net.sumaris.core.service.administration.programStrategy.StrategyService;
44 import net.sumaris.core.util.StringUtils;
45 import net.sumaris.core.vo.administration.programStrategy.PmfmStrategyVO;
46 import net.sumaris.core.vo.administration.programStrategy.ProgramVO;
47 import org.apache.commons.collections4.CollectionUtils;
48 import org.apache.commons.collections4.MultiValuedMap;
49 import org.apache.commons.collections4.multimap.ArrayListValuedHashMap;
50 import org.slf4j.Logger;
51 import org.slf4j.LoggerFactory;
52 import org.springframework.beans.factory.annotation.Autowired;
53 import org.springframework.context.annotation.Lazy;
54 import org.springframework.core.io.Resource;
55 import org.springframework.core.io.ResourceLoader;
56 import org.springframework.stereotype.Repository;
57 import net.sumaris.core.extraction.vo.trip.ExtractionTripFilterVO;
58 import java.io.IOException;
59 import java.net.URL;
60 import java.util.*;
61 import java.util.stream.Collectors;
62
63 import static org.nuiton.i18n.I18n.t;
64
65
66
67
68
69 @Repository("extractionRdbTripDao")
70 @Lazy
71 public class ExtractionRdbTripDaoImpl<C extends ExtractionRdbTripContextVO> extends ExtractionBaseDaoImpl implements ExtractionRdbTripDao {
72
73 private static final Logger log = LoggerFactory.getLogger(ExtractionRdbTripDaoImpl.class);
74
75 private static final String TR_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + TR_SHEET_NAME + "_%s";
76 private static final String HH_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HH_SHEET_NAME + "_%s";
77 private static final String SL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + SL_SHEET_NAME + "_%s";
78 private static final String SL_RAW_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "RAW_" + SL_SHEET_NAME + "_%s";
79 private static final String HL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HL_SHEET_NAME + "_%s";
80 private static final String CA_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + CA_SHEET_NAME + "_%s";
81
82 protected static final String XML_QUERY_PATH = "xmlQuery";
83
84 @Autowired
85 protected StrategyService strategyService;
86
87 @Autowired
88 protected ProgramService programService;
89
90 @Autowired
91 protected ResourceLoader resourceLoader;
92
93 @Autowired
94 protected SumarisDatabaseMetadata databaseMetadata;
95
96 @Override
97 public C execute(ExtractionFilterVO filter) {
98 ExtractionTripFilterVO tripFilter = toTripFilterVO(filter);
99
100
101 C context = createNewContext();
102 context.setTripFilter(tripFilter);
103 context.setFilter(filter);
104 context.setFormatName(RDB_FORMAT);
105 context.setFormatVersion(ExtractionRdbTripVersion.VERSION_1_3.getLabel());
106 context.setId(System.currentTimeMillis());
107
108 if (log.isInfoEnabled()) {
109 StringBuilder filterInfo = new StringBuilder();
110 if (filter != null) {
111 filterInfo.append("with filter:")
112 .append("\n - Program (label): ").append(tripFilter.getProgramLabel())
113 .append("\n - Location (id): ").append(tripFilter.getLocationId())
114 .append("\n - Start date: ").append(tripFilter.getStartDate())
115 .append("\n - End date: ").append(tripFilter.getEndDate())
116 .append("\n - Vessel (id): ").append(tripFilter.getVesselId())
117 .append("\n - Recorder department (id): ").append(tripFilter.getRecorderDepartmentId());
118 }
119 else {
120 filterInfo.append("(without filter)");
121 }
122 log.info(String.format("Starting extraction #%s (raw data / trips)... %s", context.getId(), filterInfo.toString()));
123 }
124
125
126 context.setTripTableName(String.format(TR_TABLE_NAME_PATTERN, context.getId()));
127 context.setStationTableName(String.format(HH_TABLE_NAME_PATTERN, context.getId()));
128 context.setRawSpeciesListTableName(String.format(SL_RAW_TABLE_NAME_PATTERN, context.getId()));
129 context.setSpeciesListTableName(String.format(SL_TABLE_NAME_PATTERN, context.getId()));
130 context.setSpeciesLengthTableName(String.format(HL_TABLE_NAME_PATTERN, context.getId()));
131 context.setSampleTableName(String.format(CA_TABLE_NAME_PATTERN, context.getId()));
132
133
134 String sheetName = filter != null && filter.isPreview() ? filter.getSheetName() : null;
135
136
137
138
139 long rowCount = createTripTable(context);
140 if (rowCount == 0) throw new DataNotFoundException(t("sumaris.extraction.noData"));
141 if (sheetName != null && context.hasSheet(sheetName)) return context;
142
143
144 List<String> programLabels = getTripProgramLabels(context);
145 Preconditions.checkArgument(CollectionUtils.isNotEmpty(programLabels));
146 log.debug("Detected programs: " + programLabels);
147
148
149 final MultiValuedMap<Integer, PmfmStrategyVO> pmfmStrategiesByProgramId = new ArrayListValuedHashMap<>();
150 programLabels.stream()
151 .map(programService::getByLabel)
152 .map(ProgramVO::getId)
153 .forEach(programId -> pmfmStrategiesByProgramId.putAll(programId, strategyService.getPmfmStrategies(programId)));
154 List<ExtractionPmfmInfoVO> pmfmInfos = getPmfmInfos(context, pmfmStrategiesByProgramId);
155 context.setPmfmInfos(pmfmInfos);
156
157
158 rowCount = createStationTable(context);
159 if (rowCount == 0) return context;
160 if (sheetName != null && context.hasSheet(sheetName)) return context;
161
162
163 rowCount = createSpeciesListTable(context);
164 if (rowCount == 0) return context;
165 if (sheetName != null && context.hasSheet(sheetName)) return context;
166
167
168 createSpeciesLengthTable(context);
169
170 return context;
171 }
172
173
174
175 protected <R extends C> R createNewContext() {
176 Class<? extends ExtractionRdbTripContextVO> contextClass = getContextClass();
177 Preconditions.checkNotNull(contextClass);
178
179 try {
180 return (R) contextClass.newInstance();
181 } catch (Exception e) {
182 throw new SumarisTechnicalException("Could not create an instance of context class " + contextClass.getName());
183 }
184 }
185
186 protected Class<? extends ExtractionRdbTripContextVO> getContextClass() {
187 return ExtractionRdbTripContextVO.class;
188 }
189
190 protected List<ExtractionPmfmInfoVO> getPmfmInfos(C context, MultiValuedMap<Integer, PmfmStrategyVO> pmfmStrategiesByProgramId) {
191
192 Map<String, String> acquisitionLevelAliases = buildAcquisitionLevelAliases(
193 pmfmStrategiesByProgramId.values().stream()
194 .map(PmfmStrategyVO::getAcquisitionLevel)
195 .collect(Collectors.toSet()));
196
197
198 List<ExtractionPmfmInfoVO> pmfmInfos = new ArrayList<>();
199 for (Integer programId : pmfmStrategiesByProgramId.keySet()) {
200 for (PmfmStrategyVO pmfmStrategy : pmfmStrategiesByProgramId.get(programId)) {
201 ExtractionPmfmInfoVOactionPmfmInfoVO.html#ExtractionPmfmInfoVO">ExtractionPmfmInfoVO pmfmInfo = new ExtractionPmfmInfoVO();
202 pmfmInfo.setProgramId(programId);
203 pmfmInfo.setAcquisitionLevel(pmfmStrategy.getAcquisitionLevel());
204 pmfmInfo.setPmfmId(pmfmStrategy.getPmfmId());
205 pmfmInfo.setRankOrder(pmfmStrategy.getRankOrder());
206
207 pmfmInfo.setAlias(acquisitionLevelAliases.get(pmfmInfo.getAcquisitionLevel()) + pmfmInfo.getPmfmId());
208
209 pmfmInfos.add(pmfmInfo);
210 }
211 }
212
213 return pmfmInfos;
214 }
215
216 protected Map<String, String> buildAcquisitionLevelAliases(Set<String> acquisitionLevels) {
217 Map<String, String> aliases = new HashMap<>();
218 for (String acquisitionLevel : acquisitionLevels) {
219 String alias = buildAlias(acquisitionLevel, "_");
220 if (aliases.values().contains(alias)) {
221 int index = 1;
222 String aliasToTest = alias + index;
223 while (aliases.values().contains(aliasToTest)) {
224 aliasToTest = alias + ++index;
225 }
226 alias = aliasToTest;
227 }
228 aliases.put(acquisitionLevel, alias);
229 }
230 return aliases;
231 }
232
233 protected String buildAlias(String string, String separator) {
234 StringBuilder result = new StringBuilder();
235 Arrays.stream(string.split(separator)).forEach(part -> result.append(part, 0, 1));
236 return result.toString();
237 }
238
239 protected List<String> getTripProgramLabels(C context) {
240
241 XMLQuery xmlQuery = createXMLQuery(context, "distinctTripProgram");
242 xmlQuery.bind("tableName", context.getTripTableName());
243
244 return query(xmlQuery.getSQLQueryAsString(), String.class);
245 }
246
247 protected long createTripTable(C context) {
248
249 XMLQuery xmlQuery = createTripQuery(context);
250
251
252 execute(xmlQuery);
253 long count = countFrom(context.getTripTableName());
254
255
256 if (count > 0) {
257 count -= cleanRow(context.getTripTableName(), context.getFilter(), TR_SHEET_NAME);
258 }
259
260
261 if (count > 0) {
262 context.addTableName(context.getTripTableName(),
263 TR_SHEET_NAME,
264 xmlQuery.getHiddenColumnNames(),
265 xmlQuery.hasDistinctOption());
266 log.debug(String.format("Trip table: %s rows inserted", count));
267 }
268 return count;
269 }
270
271 protected XMLQuery createTripQuery(C context) {
272 XMLQuery xmlQuery = createXMLQuery(context, "createTripTable");
273 xmlQuery.bind("tripTableName", context.getTripTableName());
274
275
276 xmlQuery.bind("nbOperationPmfmId", String.valueOf(PmfmEnum.NB_OPERATION.getId()));
277 Integer countryLocationLevelId = getReferentialIdByUniqueLabel(LocationLevel.class, LocationLevelEnum.COUNTRY.getLabel());
278 xmlQuery.bind("countryLocationLevelId", String.valueOf(countryLocationLevelId));
279
280
281 xmlQuery.setGroup("startDateFilter", context.getStartDate() != null);
282 xmlQuery.bind("startDate", Daos.getSqlToDate(context.getStartDate()));
283 xmlQuery.setGroup("endDateFilter", context.getEndDate() != null);
284 xmlQuery.bind("endDate", Daos.getSqlToDate(context.getEndDate()));
285
286
287 xmlQuery.setGroup("programFilter", CollectionUtils.isNotEmpty(context.getProgramLabels()));
288 xmlQuery.bind("progLabels", Daos.getSqlInValueFromStringCollection(context.getProgramLabels()));
289
290
291 xmlQuery.setGroup("locationFilter", CollectionUtils.isNotEmpty(context.getLocationIds()));
292 xmlQuery.bind("locationIds", Daos.getSqlInValueFromIntegerCollection(context.getLocationIds()));
293
294
295 xmlQuery.setGroup("departmentFilter", CollectionUtils.isNotEmpty(context.getRecorderDepartmentIds()));
296 xmlQuery.bind("recDepIds", Daos.getSqlInValueFromIntegerCollection(context.getRecorderDepartmentIds()));
297
298
299 xmlQuery.setGroup("vesselFilter", CollectionUtils.isNotEmpty(context.getVesselIds()));
300 xmlQuery.bind("vesselIds", Daos.getSqlInValueFromIntegerCollection(context.getVesselIds()));
301
302 return xmlQuery;
303 }
304
305 protected long createStationTable(C context) {
306
307 XMLQuery xmlQuery = createStationQuery(context);
308
309
310 execute(xmlQuery);
311 long count = countFrom(context.getStationTableName());
312
313
314 if (count > 0) {
315 count -= cleanRow(context.getStationTableName(), context.getFilter(), HH_SHEET_NAME);
316 }
317
318 if (count > 0) {
319
320 context.addTableName(context.getStationTableName(),
321 HH_SHEET_NAME,
322 xmlQuery.getHiddenColumnNames(),
323 xmlQuery.hasDistinctOption());
324 log.debug(String.format("Station table: %s rows inserted", count));
325 }
326
327 return count;
328 }
329
330 protected XMLQuery createStationQuery(C context) {
331
332 XMLQuery xmlQuery = createXMLQuery(context, "createStationTable");
333 xmlQuery.bind("tripTableName", context.getTripTableName());
334 xmlQuery.bind("stationTableName", context.getStationTableName());
335
336
337 xmlQuery.bind("meshSizePmfmId", String.valueOf(PmfmEnum.SMALLER_MESH_GAUGE_MM.getId()));
338 xmlQuery.bind("mainFishingDepthPmfmId", String.valueOf(PmfmEnum.GEAR_DEPTH_M.getId()));
339 xmlQuery.bind("mainWaterDepthPmfmId", String.valueOf(PmfmEnum.BOTTOM_DEPTH_M.getId()));
340 xmlQuery.bind("selectionDevicePmfmId", String.valueOf(PmfmEnum.SELECTIVITY_DEVICE.getId()));
341 xmlQuery.bind("normalProgressPmfmId", String.valueOf(PmfmEnum.TRIP_PROGRESS.getId()));
342
343 return xmlQuery;
344 }
345
346 protected long createSpeciesListTable(C context) {
347
348
349 XMLQuery rawXmlQuery = createRawSpeciesListQuery(context, true);
350 execute(rawXmlQuery);
351
352
353 cleanRow(context.getRawSpeciesListTableName(), context.getFilter(), SL_SHEET_NAME);
354
355
356 String tableName = context.getSpeciesListTableName();
357 XMLQuery xmlQuery = createSpeciesListQuery(context);
358 execute(xmlQuery);
359
360 long count = countFrom(tableName);
361
362
363 if (count > 0) {
364 context.addTableName(tableName,
365 SL_SHEET_NAME,
366 xmlQuery.getHiddenColumnNames(),
367 xmlQuery.hasDistinctOption());
368 log.debug(String.format("Species list table: %s rows inserted", count));
369 }
370 return count;
371 }
372
373 protected XMLQuery createRawSpeciesListQuery(C context, boolean excludeInvalidStation) {
374 XMLQuery xmlQuery = createXMLQuery(context, "createRawSpeciesListTable");
375 xmlQuery.bind("stationTableName", context.getStationTableName());
376 xmlQuery.bind("rawSpeciesListTableName", context.getRawSpeciesListTableName());
377
378
379 xmlQuery.bind("catchCategoryPmfmId", String.valueOf(PmfmEnum.DISCARD_OR_LANDING.getId()));
380
381
382 xmlQuery.setGroup("excludeInvalidStation", excludeInvalidStation);
383
384 return xmlQuery;
385 }
386
387 protected XMLQuery createSpeciesListQuery(C context) {
388 XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesListTable");
389 xmlQuery.bind("rawSpeciesListTableName", context.getRawSpeciesListTableName());
390 xmlQuery.bind("speciesListTableName", context.getSpeciesListTableName());
391
392 return xmlQuery;
393 }
394
395 protected long createSpeciesLengthTable(C context) {
396
397 XMLQuery xmlQuery = createSpeciesLengthQuery(context);
398
399
400 execute(xmlQuery);
401 long count = countFrom(context.getSpeciesLengthTableName());
402
403
404 if (count > 0) {
405 count -= cleanRow(context.getSpeciesLengthTableName(), context.getFilter(), HL_SHEET_NAME);
406 }
407
408
409 if (count > 0) {
410 context.addTableName(context.getSpeciesLengthTableName(),
411 HL_SHEET_NAME,
412 xmlQuery.getHiddenColumnNames(),
413 xmlQuery.hasDistinctOption());
414 log.debug(String.format("Species length table: %s rows inserted", count));
415 }
416 return count;
417 }
418
419
420 protected XMLQuery createSpeciesLengthQuery(C context) {
421 XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesLengthTable");
422 xmlQuery.bind("stationTableName", context.getStationTableName());
423 xmlQuery.bind("rawSpeciesListTableName", context.getRawSpeciesListTableName());
424 xmlQuery.bind("speciesLengthTableName", context.getSpeciesLengthTableName());
425
426
427 xmlQuery.bind("sexPmfmId", String.valueOf(PmfmEnum.SEX.getId()));
428 xmlQuery.bind("lengthTotalCmPmfmId", String.valueOf(PmfmEnum.LENGTH_TOTAL_CM.getId()));
429 xmlQuery.bind("lengthCarapaceCmPmfmId", String.valueOf(PmfmEnum.LENGTH_CARAPACE_CM.getId()));
430 xmlQuery.bind("centimeterUnitId", String.valueOf(UnitEnum.CM.getId()));
431 xmlQuery.bind("millimeterUnitId", String.valueOf(UnitEnum.MM.getId()));
432
433 return xmlQuery;
434 }
435
436 protected int execute(XMLQuery xmlQuery) {
437 return queryUpdate(xmlQuery.getSQLQueryAsString());
438 }
439
440 protected long countFrom(String tableName) {
441 XMLQuery xmlQuery = createXMLQuery("countFrom");
442 xmlQuery.bind("tableName", tableName);
443 return queryCount(xmlQuery.getSQLQueryAsString());
444 }
445
446 protected String getQueryFullName(C context, String queryName) {
447 Preconditions.checkNotNull(context);
448 Preconditions.checkNotNull(context.getFormatName());
449 Preconditions.checkNotNull(context.getFormatVersion());
450
451 return String.format("%s/v%s/%s",
452 context.getFormatName(),
453 context.getFormatVersion().replaceAll("[.]", "_"),
454 queryName);
455 }
456
457 protected XMLQuery createXMLQuery(C context, String queryName) {
458 return createXMLQuery(getQueryFullName(context, queryName));
459 }
460
461 protected XMLQuery createXMLQuery(String queryName) {
462 XMLQuery query = createXMLQuery();
463 query.setQuery(getXMLQueryClasspathURL(queryName));
464 return query;
465 }
466
467 protected URL getXMLQueryURL(C context, String queryName) {
468 return getXMLQueryClasspathURL(getQueryFullName(context, queryName));
469 }
470
471 protected URL getXMLQueryClasspathURL(String queryName) {
472 Resource resource = resourceLoader.getResource(ResourceLoader.CLASSPATH_URL_PREFIX + XML_QUERY_PATH + "/" + queryName + ".xml");
473 if (!resource.exists())
474 throw new SumarisTechnicalException(t("sumaris.extraction.xmlQuery.notFound", queryName));
475 try {
476 return resource.getURL();
477 } catch (IOException e) {
478 throw new SumarisTechnicalException(e);
479 }
480 }
481
482 protected int cleanRow(String tableName, ExtractionFilterVO filter, String sheetName) {
483 Preconditions.checkNotNull(tableName);
484 if (filter == null) return 0;
485
486 SumarisTableMetadata table = databaseMetadata.getTable(tableName.toLowerCase());
487 Preconditions.checkNotNull(table);
488
489 String whereClauseContent = SumarisTableMetadatas.getSqlWhereClauseContent(table, filter, sheetName, table.getAlias());
490 if (StringUtils.isBlank(whereClauseContent)) return 0;
491
492 String deleteQuery = table.getDeleteQuery(String.format("NOT(%s)", whereClauseContent));
493 return queryUpdate(deleteQuery);
494 }
495
496
497 }