View Javadoc
1   package net.sumaris.core.extraction.dao.trip.rdb;
2   
3   /*-
4    * #%L
5    * SUMARiS:: Core Extraction
6    * %%
7    * Copyright (C) 2018 - 2019 SUMARiS Consortium
8    * %%
9    * This program is free software: you can redistribute it and/or modify
10   * it under the terms of the GNU General Public License as
11   * published by the Free Software Foundation, either version 3 of the
12   * License, or (at your option) any later version.
13   * 
14   * This program is distributed in the hope that it will be useful,
15   * but WITHOUT ANY WARRANTY; without even the implied warranty of
16   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17   * GNU General Public License for more details.
18   * 
19   * You should have received a copy of the GNU General Public
20   * License along with this program.  If not, see
21   * <http://www.gnu.org/licenses/gpl-3.0.html>.
22   * #L%
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   * @author Ludovic Pecquot <ludovic.pecquot@e-is.pro>
67   * @author Benoit Lavenier <benoit.lavenier@e-is.pro>
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         // Init context
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         // Compute table names
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         // Expected sheet name
134         String sheetName = filter != null && filter.isPreview() ? filter.getSheetName() : null;
135 
136         // -- Execute the extraction --
137 
138         // Trip
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         // Get programs from trips
144         List<String> programLabels = getTripProgramLabels(context);
145         Preconditions.checkArgument(CollectionUtils.isNotEmpty(programLabels));
146         log.debug("Detected programs: " + programLabels);
147 
148         // Get PMFMs from strategies
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         // Station
158         rowCount = createStationTable(context);
159         if (rowCount == 0) return context;
160         if (sheetName != null && context.hasSheet(sheetName)) return context;
161 
162         // Species List
163         rowCount = createSpeciesListTable(context);
164         if (rowCount == 0) return context;
165         if (sheetName != null && context.hasSheet(sheetName)) return context;
166 
167         // Species Length
168         createSpeciesLengthTable(context);
169 
170         return context;
171     }
172 
173     /* -- protected methods -- */
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                 //pmfmInfo.setTableName(String.format(PMFM_TABLE_NAME_PATTERN, context.getId(), pmfmInfo.getAlias()));
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         // aggregate insertion
252         execute(xmlQuery);
253         long count = countFrom(context.getTripTableName());
254 
255         // Clean row using generic tripFilter
256         if (count > 0) {
257             count -= cleanRow(context.getTripTableName(), context.getFilter(), TR_SHEET_NAME);
258         }
259 
260         // Add result table to context
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         // Bind some referential ids
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         // Date filters
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         // Program tripFilter
287         xmlQuery.setGroup("programFilter", CollectionUtils.isNotEmpty(context.getProgramLabels()));
288         xmlQuery.bind("progLabels", Daos.getSqlInValueFromStringCollection(context.getProgramLabels()));
289 
290         // Location Filter
291         xmlQuery.setGroup("locationFilter", CollectionUtils.isNotEmpty(context.getLocationIds()));
292         xmlQuery.bind("locationIds", Daos.getSqlInValueFromIntegerCollection(context.getLocationIds()));
293 
294         // Recorder Department tripFilter
295         xmlQuery.setGroup("departmentFilter", CollectionUtils.isNotEmpty(context.getRecorderDepartmentIds()));
296         xmlQuery.bind("recDepIds", Daos.getSqlInValueFromIntegerCollection(context.getRecorderDepartmentIds()));
297 
298         // Vessel tripFilter
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         // aggregate insertion
310         execute(xmlQuery);
311         long count = countFrom(context.getStationTableName());
312 
313         // Clean row using generic tripFilter
314         if (count > 0) {
315             count -= cleanRow(context.getStationTableName(), context.getFilter(), HH_SHEET_NAME);
316         }
317 
318         if (count > 0) {
319             // Add result table to context
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         // Bind some PMFM ids
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         // Create raw table (with hidden columns used by sub table - e.g. SAMPLE_ID)
349         XMLQuery rawXmlQuery = createRawSpeciesListQuery(context, true/*exclude invalid station*/);
350         execute(rawXmlQuery);
351 
352         // Clean row using generic filter
353         cleanRow(context.getRawSpeciesListTableName(), context.getFilter(), SL_SHEET_NAME);
354 
355         // Create the final table (with distinct), without hidden columns
356         String tableName = context.getSpeciesListTableName();
357         XMLQuery xmlQuery = createSpeciesListQuery(context);
358         execute(xmlQuery);
359 
360         long count = countFrom(tableName);
361 
362         // Add result table to context
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         // Bind some ids
379         xmlQuery.bind("catchCategoryPmfmId", String.valueOf(PmfmEnum.DISCARD_OR_LANDING.getId()));
380 
381         // Exclude not valid station
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         // aggregate insertion
400         execute(xmlQuery);
401         long count = countFrom(context.getSpeciesLengthTableName());
402 
403         // Clean row using generic tripFilter
404         if (count > 0) {
405             count -= cleanRow(context.getSpeciesLengthTableName(), context.getFilter(), HL_SHEET_NAME);
406         }
407 
408         // Add result table to context
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         // Bind some ids
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 }