1 package net.sumaris.core.extraction.service;
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.ImmutableList;
27 import com.google.common.collect.Sets;
28 import net.sumaris.core.config.SumarisConfiguration;
29 import net.sumaris.core.dao.technical.SortDirection;
30 import net.sumaris.core.dao.technical.extraction.ExtractionProductDao;
31 import net.sumaris.core.dao.technical.model.IEntity;
32 import net.sumaris.core.dao.technical.schema.SumarisDatabaseMetadata;
33 import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
34 import net.sumaris.core.exception.DataNotFoundException;
35 import net.sumaris.core.exception.SumarisTechnicalException;
36 import net.sumaris.core.extraction.dao.technical.Daos;
37 import net.sumaris.core.extraction.dao.technical.csv.ExtractionCsvDao;
38 import net.sumaris.core.extraction.dao.technical.schema.SumarisTableMetadatas;
39 import net.sumaris.core.extraction.dao.technical.table.ExtractionTableColumnOrder;
40 import net.sumaris.core.extraction.dao.technical.table.ExtractionTableDao;
41 import net.sumaris.core.extraction.dao.trip.cost.ExtractionCostTripDao;
42 import net.sumaris.core.extraction.dao.trip.free.ExtractionFreeTripDao;
43 import net.sumaris.core.extraction.dao.trip.rdb.ExtractionRdbTripDao;
44 import net.sumaris.core.extraction.dao.trip.survivalTest.ExtractionSurvivalTestDao;
45 import net.sumaris.core.extraction.utils.ExtractionBeans;
46 import net.sumaris.core.extraction.vo.*;
47 import net.sumaris.core.extraction.vo.filter.ExtractionTypeFilterVO;
48 import net.sumaris.core.extraction.vo.trip.ExtractionTripFilterVO;
49 import net.sumaris.core.model.referential.StatusEnum;
50 import net.sumaris.core.model.referential.location.Location;
51 import net.sumaris.core.model.referential.location.LocationLevelEnum;
52 import net.sumaris.core.service.referential.LocationService;
53 import net.sumaris.core.service.referential.ReferentialService;
54 import net.sumaris.core.util.*;
55 import net.sumaris.core.vo.technical.extraction.ExtractionProductTableVO;
56 import net.sumaris.core.vo.technical.extraction.ExtractionProductVO;
57 import net.sumaris.core.vo.technical.extraction.ProductFetchOptions;
58 import org.apache.commons.collections4.CollectionUtils;
59 import org.apache.commons.collections4.ListUtils;
60 import org.apache.commons.collections4.SetUtils;
61 import org.apache.commons.io.FileUtils;
62 import org.apache.commons.lang3.ArrayUtils;
63 import org.apache.commons.lang3.mutable.MutableInt;
64 import org.nuiton.i18n.I18n;
65 import org.slf4j.Logger;
66 import org.slf4j.LoggerFactory;
67 import org.springframework.beans.factory.annotation.Autowired;
68 import org.springframework.context.annotation.Lazy;
69 import org.springframework.core.task.TaskExecutor;
70 import org.springframework.jdbc.datasource.DataSourceUtils;
71 import org.springframework.stereotype.Service;
72
73 import javax.annotation.Nullable;
74 import javax.annotation.PostConstruct;
75 import javax.annotation.Resource;
76 import javax.sql.DataSource;
77 import java.io.File;
78 import java.io.IOException;
79 import java.sql.Connection;
80 import java.sql.SQLException;
81 import java.util.*;
82 import java.util.stream.Collectors;
83
84
85
86
87 @Service("extractionService")
88 @Lazy
89 public class ExtractionServiceImpl implements ExtractionService {
90
91 private static final Logger log = LoggerFactory.getLogger(ExtractionServiceImpl.class);
92
93 @Autowired
94 protected SumarisConfiguration configuration;
95
96 @Autowired
97 protected DataSource dataSource;
98
99 @Resource(name = "extractionRdbTripDao")
100 protected ExtractionRdbTripDao extractionRdbTripDao;
101
102 @Resource(name = "extractionCostTripDao")
103 protected ExtractionCostTripDao extractionCostTripDao;
104
105 @Resource(name = "extractionFreeTripDao")
106 protected ExtractionFreeTripDao extractionFreeTripDao;
107
108 @Resource(name = "extractionSurvivalTestDao")
109 protected ExtractionSurvivalTestDao extractionSurvivalTestDao;
110
111 @Autowired
112 protected ExtractionProductDao extractionProductDao;
113
114 @Autowired
115 protected ExtractionTableDao extractionTableDao;
116
117 @Autowired
118 protected ExtractionCsvDao extractionCsvDao;
119
120 @Autowired
121 protected LocationService locationService;
122
123 @Autowired
124 protected ReferentialService referentialService;
125
126 @Autowired
127 protected SumarisDatabaseMetadata databaseMetadata;
128
129 @Autowired(required = false)
130 protected TaskExecutor taskExecutor = null;
131
132 @Autowired
133 private ExtractionService self;
134
135 @PostConstruct
136 protected void afterPropertiesSet() {
137
138
139 if (configuration.isInitStatisticalRectangles()) {
140 initRectangleLocations();
141 }
142 }
143
144 @Override
145 public List<ExtractionTypeVO> findByFilter(ExtractionTypeFilterVO filter) {
146 ImmutableList.Builder<ExtractionTypeVO> builder = ImmutableList.builder();
147 filter = filter != null ? filter : new ExtractionTypeFilterVO();
148
149
150 if (filter.getCategory() == null || filter.getCategory().equalsIgnoreCase(ExtractionCategoryEnum.LIVE.name())) {
151 builder.addAll(getLiveExtractionTypes());
152 }
153
154
155 if (filter.getCategory() == null || filter.getCategory().equalsIgnoreCase(ExtractionCategoryEnum.PRODUCT.name())) {
156 builder.addAll(getProductExtractionTypes(filter));
157 }
158
159 return builder.build();
160 }
161
162 @Override
163 public ExtractionResultVO executeAndRead(ExtractionTypeVO type, ExtractionFilterVO filter, int offset, int size, String sort, SortDirection direction) {
164
165 ExtractionTypeVO checkedType = ExtractionBeans.checkAndFindType(this.getAllExtractionTypes(), type);
166 ExtractionCategoryEnum category = ExtractionCategoryEnum.valueOf(checkedType.getCategory().toUpperCase());
167
168
169 filter.setPreview(true);
170
171 filter = filter != null ? filter : new ExtractionFilterVO();
172
173 switch (category) {
174 case PRODUCT:
175 ExtractionProductVO product = extractionProductDao.getByLabel(checkedType.getLabel(),
176 ProductFetchOptions.MINIMAL_WITH_TABLES);
177 return readProductRows(product, filter, offset, size, sort, direction);
178 case LIVE:
179 String formatName = checkedType.getLabel();
180 return extractRawDataAndRead(formatName, filter, offset, size, sort, direction);
181 default:
182 throw new SumarisTechnicalException(String.format("Extraction of category %s not implemented yet !", type.getCategory()));
183 }
184 }
185
186 @Override
187 public ExtractionResultVO read(ExtractionContextVO context, ExtractionFilterVO filter,
188 int offset, int size, String sort, SortDirection direction) {
189 Preconditions.checkNotNull(context);
190
191 filter = filter != null ? filter : new ExtractionFilterVO();
192
193 String tableName;
194 if (StringUtils.isNotBlank(filter.getSheetName())) {
195 tableName = context.getTableNameBySheetName(filter.getSheetName());
196 } else {
197 tableName = context.getTableNames().iterator().next();
198 }
199
200
201 if (tableName == null) return createEmptyResult();
202
203
204
205 ExtractionFilterVOFilterVO.html#ExtractionFilterVO">ExtractionFilterVO rowsFilter = new ExtractionFilterVO();
206 rowsFilter.setIncludeColumnNames(filter.getIncludeColumnNames());
207 rowsFilter.setExcludeColumnNames(SetUtils.union(
208 SetUtils.emptyIfNull(filter.getIncludeColumnNames()),
209 SetUtils.emptyIfNull(context.getHiddenColumns(tableName))
210 ));
211
212
213 boolean enableDistinct = filter.isDistinct() || CollectionUtils.isNotEmpty(rowsFilter.getExcludeColumnNames())
214 && context.isDistinctEnable(tableName);
215 rowsFilter.setDistinct(enableDistinct);
216
217
218 return extractionTableDao.getTableRows(tableName, rowsFilter, offset, size, sort, direction);
219
220 }
221
222 @Override
223 public File executeAndDump(ExtractionTypeVO type, ExtractionFilterVO filter) throws IOException {
224
225 ExtractionTypeVO checkedType = ExtractionBeans.checkAndFindType(getAllExtractionTypes(), type);
226 ExtractionCategoryEnum category = ExtractionCategoryEnum.valueOf(checkedType.getCategory().toUpperCase());
227
228 filter = filter != null ? filter : new ExtractionFilterVO();
229
230
231 filter.setPreview(false);
232
233 switch (category) {
234 case PRODUCT:
235 ExtractionProductVO product = extractionProductDao.getByLabel(checkedType.getLabel(),
236 ProductFetchOptions.builder()
237 .withRecorderDepartment(false)
238 .withRecorderPerson(false)
239 .withColumns(false)
240 .build());
241 return dumpProductToFile(product, filter);
242 case LIVE:
243 ExtractionRawFormatEnum format = ExtractionRawFormatEnum.valueOf(checkedType.getLabel().toUpperCase());
244 return extractRawDataAndDumpToFile(format, filter);
245 default:
246 throw new SumarisTechnicalException(String.format("Extraction of category %s not implemented yet !", type.getCategory()));
247 }
248
249 }
250
251 @Override
252 public ExtractionContextVO execute(ExtractionTypeVO type, ExtractionFilterVO filter) {
253
254 ExtractionTypeVO checkedType = ExtractionBeans.checkAndFindType(getAllExtractionTypes(), type);
255 ExtractionCategoryEnum category = ExtractionCategoryEnum.valueOf(checkedType.getCategory().toUpperCase());
256
257 filter = filter != null ? filter : new ExtractionFilterVO();
258
259
260 filter.setPreview(false);
261
262 switch (category) {
263 case PRODUCT:
264 throw new IllegalArgumentException("execute not implemented yet for product");
265
266
267 case LIVE:
268 ExtractionRawFormatEnum format = ExtractionRawFormatEnum.valueOf(checkedType.getLabel().toUpperCase());
269 return extractRawData(format, filter);
270 default:
271 throw new SumarisTechnicalException(String.format("Extraction of category %s not implemented yet !", type.getCategory()));
272 }
273
274 }
275
276 @Override
277 public File executeAndDumpTrips(ExtractionRawFormatEnum format, ExtractionTripFilterVO tripFilter) {
278
279 ExtractionFilterVO filter = extractionRdbTripDao.toExtractionFilterVO(tripFilter);
280 return extractRawDataAndDumpToFile(format, filter);
281 }
282
283 @Override
284 public void clean(ExtractionContextVO context) {
285 Preconditions.checkNotNull(context);
286
287 if (CollectionUtils.isEmpty(context.getTableNames())) return;
288
289 context.getTableNames().stream()
290
291 .filter(tableName -> tableName != null && tableName.startsWith("EXT_"))
292 .forEach(extractionTableDao::dropTable);
293 }
294
295 @Override
296 public ExtractionProductVO toProductVO(ExtractionContextVO source) {
297 if (source == null) return null;
298 Preconditions.checkNotNull(source.getLabel());
299
300 ExtractionProductVOon/ExtractionProductVO.html#ExtractionProductVO">ExtractionProductVO target = new ExtractionProductVO();
301
302 String format = source.getFormatName();
303 if (StringUtils.isNotBlank(format)) {
304 target.setLabel(StringUtils.changeCaseToUnderscore(format).toUpperCase());
305 } else {
306 target.setLabel(source.getLabel());
307 }
308 target.setName(String.format("Extraction #%s", source.getId()));
309
310 target.setTables(SetUtils.emptyIfNull(source.getTableNames())
311 .stream()
312 .map(t -> {
313 ExtractionProductTableVOxtractionProductTableVO.html#ExtractionProductTableVO">ExtractionProductTableVO table = new ExtractionProductTableVO();
314 table.setLabel(source.getSheetName(t));
315 table.setName(t);
316 table.setTableName(t);
317 return table;
318 })
319 .collect(Collectors.toList()));
320
321 return target;
322 }
323
324 @Override
325 public ExtractionTypeVO/../../../net/sumaris/core/extraction/vo/ExtractionTypeVO.html#ExtractionTypeVO">ExtractionTypeVO save(ExtractionTypeVO type, ExtractionFilterVO filter) {
326 Preconditions.checkNotNull(type);
327
328
329 ExtractionProductVO target = null;
330 try {
331 target = extractionProductDao.getByLabel(type.getLabel(), ProductFetchOptions.builder()
332 .withTables(false)
333 .build());
334 } catch (Throwable t) {
335
336 }
337
338 if (target == null) {
339 target = new ExtractionProductVO();
340 target.setLabel(type.getLabel());
341 }
342
343
344 ExtractionContextVO context;
345 {
346 ExtractionTypeVOionTypeVO.html#ExtractionTypeVO">ExtractionTypeVO cleanType = new ExtractionTypeVO();
347 cleanType.setLabel(type.getFormat());
348 cleanType.setCategory(type.getCategory());
349 context = execute(cleanType, filter);
350 }
351 toProductVO(context, target);
352
353
354 target.setStatusId(type.getStatusId());
355
356
357 target = extractionProductDao.save(target);
358
359
360 return toExtractionTypeVO(target);
361 }
362
363
364
365 protected List<ExtractionTypeVO> getAllExtractionTypes() {
366 return findByFilter(new ExtractionTypeFilterVO());
367 }
368
369 protected List<ExtractionTypeVO> getProductExtractionTypes(ExtractionTypeFilterVO filter) {
370 Preconditions.checkNotNull(filter);
371
372
373 if (ArrayUtils.isEmpty(filter.getStatusIds())) {
374 filter.setStatusIds(new Integer[]{StatusEnum.ENABLE.getId(), StatusEnum.TEMPORARY.getId()});
375 }
376
377 return ListUtils.emptyIfNull(
378 extractionProductDao.findByFilter(filter, ProductFetchOptions.builder()
379 .withRecorderDepartment(true)
380 .withTables(true)
381 .build()))
382 .stream()
383 .map(this::toExtractionTypeVO)
384 .collect(Collectors.toList());
385 }
386
387 protected List<ExtractionTypeVO> getLiveExtractionTypes() {
388 MutableInt id = new MutableInt(-1);
389 return Arrays.stream(ExtractionRawFormatEnum.values())
390 .map(format -> {
391 ExtractionTypeVOtractionTypeVO.html#ExtractionTypeVO">ExtractionTypeVO type = new ExtractionTypeVO();
392 type.setId(id.getValue());
393 type.setLabel(format.name().toLowerCase());
394 type.setCategory(ExtractionCategoryEnum.LIVE.name().toLowerCase());
395 type.setSheetNames(format.getSheetNames());
396 type.setStatusId(StatusEnum.TEMPORARY.getId());
397 id.decrement();
398 return type;
399 })
400 .collect(Collectors.toList());
401 }
402
403
404 protected ExtractionResultVO extractRawDataAndRead(String formatStr, ExtractionFilterVO filter,
405 int offset, int size, String sort, SortDirection direction) {
406 Preconditions.checkNotNull(formatStr);
407
408 ExtractionRawFormatEnum format = ExtractionRawFormatEnum.valueOf(formatStr.toUpperCase());
409
410 filter.setPreview(true);
411
412
413 if (IEntity.Fields.ID.equalsIgnoreCase(sort)) {
414 sort = null;
415 }
416
417
418 ExtractionContextVO context;
419 try {
420 context = extractRawData(format, filter);
421 } catch (DataNotFoundException e) {
422 return createEmptyResult();
423 }
424
425 try {
426
427 return read(context, filter, offset, size, sort, direction);
428 } finally {
429
430 asyncClean(context);
431 }
432 }
433
434 protected File extractRawDataAndDumpToFile(ExtractionRawFormatEnum format, ExtractionFilterVO filter) {
435 Preconditions.checkNotNull(format);
436
437
438 ExtractionContextVO context = extractRawData(format, filter);
439
440 commitIfHsqldb();
441 log.info(String.format("Dumping tables of extraction #%s to files...", context.getId()));
442
443
444 return dumpTablesToFile(context, null );
445 }
446
447 protected ExtractionResultVO readProductRows(ExtractionProductVO product, ExtractionFilterVO filter, int offset, int size, String sort, SortDirection direction) {
448 Preconditions.checkNotNull(product);
449 Preconditions.checkNotNull(filter);
450 Preconditions.checkArgument(offset >= 0);
451 Preconditions.checkArgument(size <= 1000, "maximum value for 'size' is: 1000");
452 Preconditions.checkArgument(size >= 0, "'size' must be greater or equals to 0");
453
454
455 String tableName = ExtractionBeans.getTableName(product, filter.getSheetName());
456
457
458 return extractionTableDao.getTableRows(tableName, filter, offset, size, sort, direction);
459 }
460
461 protected File dumpProductToFile(ExtractionProductVO product, ExtractionFilterVO filter) throws IOException {
462 Preconditions.checkNotNull(product);
463 Preconditions.checkNotNull(filter);
464
465
466 ExtractionProductContextVOctContextVO.html#ExtractionProductContextVO">ExtractionProductContextVO context = new ExtractionProductContextVO(product);
467 context.setId(System.currentTimeMillis());
468
469
470 return dumpTablesToFile(context, filter);
471 }
472
473 protected ExtractionContextVO extractRawData(ExtractionRawFormatEnum format,
474 ExtractionFilterVO filter) {
475
476 ExtractionContextVO context;
477
478 switch (format) {
479 case RDB:
480 context = extractionRdbTripDao.execute(filter);
481 break;
482 case COST:
483 context = extractionCostTripDao.execute(filter);
484 break;
485 case FREE:
486 context = extractionFreeTripDao.execute(filter);
487 break;
488 case SURVIVAL_TEST:
489 context = extractionSurvivalTestDao.execute(filter);
490 break;
491 default:
492 throw new SumarisTechnicalException("Unknown extraction type: " + format);
493 }
494
495 return context;
496 }
497
498 protected File dumpTablesToFile(ExtractionContextVO context,
499 @Nullable ExtractionFilterVO filter) {
500 Preconditions.checkNotNull(context);
501 Preconditions.checkNotNull(context.getLabel());
502
503 if (CollectionUtils.isEmpty(context.getTableNames())) return null;
504
505
506 log.debug(String.format("Extraction #%s > Creating CSV files...", context.getId()));
507
508 String dateStr = Dates.formatDate(new Date(context.getId()), "yyyy-MM-dd-HHmm");
509 String basename = context.getLabel() + "-" + dateStr;
510
511 final ExtractionFiExtractionFilterVOlter = filter != null ? filter : new ExtractionFilterVO();
512 final Set<String> defaultExcludeColumns = SetUtils.emptyIfNull(tableFilter.getExcludeColumnNames());
513 final boolean defaultEnableDistinct = filter != null && filter.isDistinct();
514
515 File outputDirectory = createTempDirectory(basename);
516 List<File> outputFiles = context.getTableNames().stream()
517 .map(tableName -> {
518 try {
519
520 Set<String> hiddenColumns = context.getHiddenColumns(tableName);
521
522 boolean enableDistinct = defaultEnableDistinct ||
523
524 (CollectionUtils.isNotEmpty(hiddenColumns) && context.isDistinctEnable(tableName));
525
526 tableFilter.setExcludeColumnNames(SetUtils.union(defaultExcludeColumns,
527 SetUtils.emptyIfNull(hiddenColumns)));
528 tableFilter.setDistinct(enableDistinct);
529
530
531 File tempCsvFile = new File(outputDirectory, context.getSheetName(tableName) + ".csv");
532 dumpTableToFile(tableName, tableFilter, tempCsvFile);
533 return tempCsvFile;
534 } catch (IOException e) {
535 log.error(String.format("Could not generate CSV file for table {%s}", tableName), e);
536 throw new SumarisTechnicalException(e);
537 }
538 })
539 .collect(Collectors.toList());
540
541
542 File outputFile;
543
544
545 if (outputFiles.size() == 1) {
546 File uniqueFile = outputFiles.get(0);
547 basename = String.format("%s-%s-%s.%s",
548 context.getLabel(),
549 Files.getNameWithoutExtension(uniqueFile),
550 dateStr,
551 Files.getExtension(uniqueFile));
552 outputFile = new File(outputDirectory.getParent(), basename);
553 try {
554 FileUtils.moveFile(uniqueFile, outputFile);
555 } catch (IOException e) {
556 throw new SumarisTechnicalException(e);
557 }
558 log.debug(String.format("Extraction file created at {%s}", outputFile.getPath()));
559 }
560
561
562 else {
563 outputFile = new File(outputDirectory.getParent(), basename + ".zip");
564 log.debug(String.format("Creating extraction file {%s}...", outputFile.getPath()));
565 try {
566 ZipUtils.compressFilesInPath(outputDirectory, outputFile, false);
567 } catch (IOException e) {
568 throw new SumarisTechnicalException(e);
569 }
570 log.debug(String.format("Extraction file created at {%s}", outputFile.getPath()));
571 }
572
573
574 asyncClean(context);
575
576 return outputFile;
577 }
578
579 protected void dumpTableToFile(String tableName, ExtractionFilterVO filter, File outputFile) throws IOException {
580 SumarisTableMetadata table;
581 try {
582 table = databaseMetadata.getTable(tableName);
583 } catch (Exception e) {
584 log.debug(String.format("Table %s not found. Skipping", tableName));
585 return;
586 }
587
588 boolean enableDistinct = filter != null && filter.isDistinct();
589
590 Set<String> columnNames = table.getColumnNames();
591 String[] orderedColumnNames = ExtractionTableColumnOrder.COLUMNS_BY_TABLE.get(tableName);
592 if (orderedColumnNames != null) {
593 columnNames = Sets.newLinkedHashSet(Arrays.asList(orderedColumnNames));
594 }
595
596 if (CollectionUtils.isNotEmpty(filter.getExcludeColumnNames())) {
597 columnNames = columnNames.stream()
598 .filter(column -> !filter.getExcludeColumnNames().contains(column))
599 .collect(Collectors.toCollection(LinkedHashSet::new));
600 }
601
602 String whereClause = SumarisTableMetadatas.getSqlWhereClause(table, filter);
603 String query = table.getSelectQuery(enableDistinct, columnNames, whereClause, null, null);
604
605 extractionCsvDao.dumpQueryToCSV(outputFile, query,
606 getAliasByColumnMap(columnNames),
607 null,
608 null,
609 null);
610
611 }
612
613 public void asyncClean(ExtractionContextVO context) {
614 if (taskExecutor == null) {
615 clean(context);
616 } else {
617 taskExecutor.execute(() -> {
618 try {
619
620 self.clean(context);
621 } catch (Exception e) {
622 log.warn("Error while cleaning extraction tables", e);
623 }
624 });
625 }
626 }
627
628 protected void initRectangleLocations() {
629
630 long statisticalRectanglesCount = referentialService.countByLevelId(Location.class.getSimpleName(), LocationLevelEnum.RECTANGLE_ICES.getId())
631 + referentialService.countByLevelId(Location.class.getSimpleName(), LocationLevelEnum.RECTANGLE_CGPM_GFCM.getId());
632 if (statisticalRectanglesCount == 0) {
633 locationService.insertOrUpdateRectangleLocations();
634 }
635
636
637 long square10minCount = referentialService.countByLevelId(Location.class.getSimpleName(), LocationLevelEnum.SQUARE_10.getId());
638 if (square10minCount == 0) {
639
640 }
641
642 if (statisticalRectanglesCount == 0 || square10minCount == 0) {
643
644
645
646
647
648
649 }
650
651
652 }
653
654 protected File createTempDirectory(String dirName) {
655 try {
656 File outputDirectory = new File(configuration.getTempDirectory(), dirName);
657 int counter = 1;
658 while (outputDirectory.exists()) {
659 outputDirectory = new File(configuration.getTempDirectory(), dirName + "_" + counter++);
660 }
661 FileUtils.forceMkdir(outputDirectory);
662 return outputDirectory;
663 } catch (IOException e) {
664 throw new SumarisTechnicalException("Could not create temporary directory for extraction", e);
665 }
666 }
667
668 protected Map<String, String> getAliasByColumnMap(Set<String> tableNames) {
669 return tableNames.stream()
670 .collect(Collectors.toMap(
671 columnName -> columnName.toUpperCase(),
672 StringUtils::underscoreToChangeCase));
673 }
674
675 protected ExtractionResultVO createEmptyResult() {
676 ExtractionResultVOtionResultVO.html#ExtractionResultVO">ExtractionResultVO result = new ExtractionResultVO();
677 result.setColumns(ImmutableList.of());
678 result.setTotal(0);
679 result.setRows(ImmutableList.of());
680 return result;
681 }
682
683
684 protected ExtractionTypeVO toExtractionTypeVO(ExtractionProductVO product) {
685 ExtractionTypeVOtractionTypeVO.html#ExtractionTypeVO">ExtractionTypeVO type = new ExtractionTypeVO();
686 toExtractionTypeVO(product, type);
687 return type;
688 }
689
690 protected void toExtractionTypeVO(ExtractionProductVO source, ExtractionTypeVO target) {
691
692 Beans.copyProperties(source, target);
693
694
695 target.setLabel(source.getLabel().toLowerCase());
696
697
698 target.setCategory(ExtractionCategoryEnum.PRODUCT.name().toLowerCase());
699
700
701 Collection<String> sheetNames = source.getSheetNames();
702 if (CollectionUtils.isNotEmpty(sheetNames)) {
703 target.setSheetNames(sheetNames.toArray(new String[sheetNames.size()]));
704 }
705
706
707 target.setRecorderDepartment(source.getRecorderDepartment());
708 }
709
710 protected void toProductVO(ExtractionContextVO source, ExtractionProductVO target) {
711
712 target.setLabel(source.getLabel().toUpperCase() + "-" + source.getId());
713 target.setName(String.format("Extraction #%s", source.getId()));
714
715 target.setTables(SetUtils.emptyIfNull(source.getTableNames())
716 .stream()
717 .map(t -> {
718 String sheetName = source.getSheetName(t);
719 ExtractionProductTableVOxtractionProductTableVO.html#ExtractionProductTableVO">ExtractionProductTableVO table = new ExtractionProductTableVO();
720 table.setLabel(sheetName);
721 table.setName(getNameBySheet(source.getFormatName(), sheetName));
722 table.setTableName(t);
723 return table;
724 })
725 .collect(Collectors.toList()));
726 }
727
728 protected String getNameBySheet(String format, String sheetName) {
729 return I18n.t(String.format("sumaris.extraction.%s.%s", format.toUpperCase(), sheetName.toUpperCase()));
730 }
731
732 protected void commitIfHsqldb() {
733 Connection conn = DataSourceUtils.getConnection(dataSource);
734 try {
735 if (Daos.isHsqlDatabase(conn) && DataSourceUtils.isConnectionTransactional(conn, dataSource)) {
736 try {
737 conn.commit();
738 } catch (SQLException e) {
739 log.warn("Cannot execute intermediate commit: " + e.getMessage(), e);
740 }
741 }
742 }
743 finally {
744 DataSourceUtils.releaseConnection(conn, dataSource);
745 }
746 }
747 }