1 package fr.ifremer.dali.service.extraction;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 import com.google.common.collect.*;
25 import fr.ifremer.dali.config.DaliConfiguration;
26 import fr.ifremer.dali.dao.administration.strategy.DaliStrategyDao;
27 import fr.ifremer.dali.dao.referential.pmfm.DaliPmfmDao;
28 import fr.ifremer.dali.dao.referential.pmfm.DaliQualitativeValueDao;
29 import fr.ifremer.dali.dao.system.extraction.DaliExtractionResultDao;
30 import fr.ifremer.dali.dao.technical.Daos;
31 import fr.ifremer.dali.decorator.DecoratorService;
32 import fr.ifremer.dali.dto.DaliBeanFactory;
33 import fr.ifremer.dali.dto.DaliBeans;
34 import fr.ifremer.dali.dto.configuration.control.ControlRuleDTO;
35 import fr.ifremer.dali.dto.configuration.control.PreconditionRuleDTO;
36 import fr.ifremer.dali.dto.configuration.filter.FilterDTO;
37 import fr.ifremer.dali.dto.configuration.programStrategy.PmfmStrategyDTO;
38 import fr.ifremer.dali.dto.configuration.programStrategy.ProgramDTO;
39 import fr.ifremer.dali.dto.enums.ExtractionFilterTypeValues;
40 import fr.ifremer.dali.dto.enums.ExtractionOutputType;
41 import fr.ifremer.dali.dto.referential.GroupingTypeDTO;
42 import fr.ifremer.dali.dto.referential.pmfm.PmfmDTO;
43 import fr.ifremer.dali.dto.referential.pmfm.QualitativeValueDTO;
44 import fr.ifremer.dali.dto.system.extraction.*;
45 import fr.ifremer.dali.service.DaliBusinessException;
46 import fr.ifremer.dali.service.DaliDataContext;
47 import fr.ifremer.dali.service.DaliServiceLocator;
48 import fr.ifremer.dali.service.DaliTechnicalException;
49 import fr.ifremer.dali.service.administration.program.ProgramStrategyService;
50 import fr.ifremer.dali.service.control.RuleListService;
51 import fr.ifremer.quadrige3.core.ProgressionCoreModel;
52 import fr.ifremer.quadrige3.core.dao.technical.Assert;
53 import fr.ifremer.quadrige3.core.dao.technical.Dates;
54 import fr.ifremer.quadrige3.core.dao.technical.Times;
55 import fr.ifremer.quadrige3.core.dao.technical.decorator.DecoratorComparator;
56 import fr.ifremer.quadrige3.core.dao.technical.factorization.Combination;
57 import fr.ifremer.quadrige3.core.dao.technical.factorization.CombinationList;
58 import fr.ifremer.quadrige3.core.dao.technical.factorization.IntegerPair;
59 import fr.ifremer.quadrige3.core.dao.technical.factorization.MaxCombinationExceededException;
60 import fr.ifremer.quadrige3.core.dao.technical.xmlQuery.XMLQuery;
61 import org.apache.commons.collections4.CollectionUtils;
62 import org.apache.commons.lang3.StringUtils;
63 import org.apache.commons.logging.Log;
64 import org.apache.commons.logging.LogFactory;
65 import org.jdom2.Element;
66 import org.springframework.stereotype.Service;
67
68 import javax.annotation.Resource;
69 import java.io.File;
70 import java.io.IOException;
71 import java.net.URL;
72 import java.util.*;
73 import java.util.concurrent.atomic.AtomicInteger;
74 import java.util.function.Predicate;
75 import java.util.stream.Collectors;
76
77 import static org.nuiton.i18n.I18n.t;
78
79
80
81
82
83
84 @Service("daliExtractionPerformService")
85 public class ExtractionPerformServiceImpl implements ExtractionPerformService {
86
87 private static final Log LOG = LogFactory.getLog(ExtractionPerformServiceImpl.class);
88
89 private static final String TABLE_NAME_PREFIX = "EXT_";
90 private static final String BASE_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "B%s";
91 private static final String RAW_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "R%s";
92 private static final String PMFM_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "P%s_%s";
93 private static final String COMMON_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "C%s";
94 private static final String RESULT_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + "E%s";
95
96 private static final String XML_QUERY_PATH = "xmlQuery/extraction";
97
98 @Resource(name = "daliExtractionService")
99 protected ExtractionService extractionService;
100
101 @Resource(name = "daliRuleListService")
102 private RuleListService ruleListService;
103
104 @Resource(name = "daliExtractionResultDao")
105 private DaliExtractionResultDao extractionResultDao;
106
107 @Resource(name = "daliPmfmDao")
108 protected DaliPmfmDao pmfmDao;
109
110 @Resource(name = "daliQualitativeValueDao")
111 private DaliQualitativeValueDao qualitativeValueDao;
112
113 @Resource(name = "daliStrategyDao")
114 private DaliStrategyDao strategyDao;
115
116 @Resource
117 protected DaliConfiguration config;
118
119 @Resource(name = "daliDataContext")
120 protected DaliDataContext dataContext;
121
122 @Resource(name = "daliProgramStrategyService")
123 protected ProgramStrategyService programStrategyService;
124
125
126
127
128 @Override
129 public void performExtraction(ExtractionDTO extraction, ExtractionOutputType outputType, File outputFile, ProgressionCoreModel progressionModel) {
130
131 Assert.notNull(extraction);
132 Assert.notNull(extraction.getId());
133 Assert.notEmpty(extraction.getFilters());
134 Assert.notNull(DaliBeans.getFilterOfType(extraction, ExtractionFilterTypeValues.PERIOD));
135 Assert.notNull(DaliBeans.getFilterOfType(extraction, ExtractionFilterTypeValues.ORDER_ITEM_TYPE));
136 Assert.notNull(DaliBeans.getFilterOfType(extraction, ExtractionFilterTypeValues.PROGRAM));
137 Assert.notNull(outputType);
138 if (isAggregated(outputType)) {
139 Assert.notNull(extraction.getParameter());
140 Assert.notEmpty(extraction.getParameter().getPmfmPresets());
141 Assert.notEmpty(extraction.getParameter().getPmfmResults());
142 }
143 Assert.notNull(outputFile);
144
145
146 extractionService.loadFilteredElements(extraction);
147
148
149 progressionModel.setMessage("");
150 progressionModel.setTotal(isAggregated(outputType) ? 11 : 9);
151 long startTime = System.currentTimeMillis();
152
153
154 ExtractionContextDTO context = DaliBeanFactory.newExtractionContextDTO();
155 context.setExtraction(extraction);
156 context.setUniqueId(System.currentTimeMillis());
157 context.setBaseTableName(String.format(BASE_TABLE_NAME_PATTERN, context.getUniqueId()));
158 context.setRawTableName(String.format(RAW_TABLE_NAME_PATTERN, context.getUniqueId()));
159 context.setCommonTableName(String.format(COMMON_TABLE_NAME_PATTERN, context.getUniqueId()));
160 context.setResultTableName(String.format(RESULT_TABLE_NAME_PATTERN, context.getUniqueId()));
161
162 if (LOG.isInfoEnabled()) {
163 LOG.info(String.format("Beginning a %s extraction (id=%s) with:", outputType, extraction.getId()));
164 LOG.info(String.format("\t date ranges: %s", DaliBeans.toString(DaliBeans.getExtractionPeriods(extraction))));
165 LOG.info(String.format("\tgéo grouping: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.ORDER_ITEM_TYPE)));
166 LOG.info(String.format("\t programs: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.PROGRAM)));
167 LOG.info(String.format("\t locations: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.LOCATION)));
168 LOG.info(String.format("\t campaigns: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.CAMPAIGN)));
169 LOG.info(String.format("\t equipment: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.SAMPLING_EQUIPMENT)));
170 LOG.info(String.format("\t departments: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.DEPARTMENT)));
171 LOG.info(String.format("\t taxons: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.TAXON)));
172 LOG.info(String.format("\ttaxon groups: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.TAXON_GROUP)));
173 LOG.info(String.format("\t pmfms: %s", DaliBeans.getFilterElementsIds(extraction, ExtractionFilterTypeValues.PMFM)));
174 if (isAggregated(outputType)) {
175 LOG.info(String.format("\tpmfms preset: %s", extraction.getParameter().getPmfmPresets().stream()
176 .map(preset -> String.format("pmfm_id:%s with qv_ids:%s", preset.getPmfm().getId(), preset.getQualitativeValues().stream()
177 .map(QualitativeValueDTO::getId).collect(Collectors.toList()))).collect(Collectors.toList())));
178 LOG.info(String.format("\tpmfms result: %s", extraction.getParameter().getPmfmResults().stream()
179 .map(PmfmDTO::getId).collect(Collectors.toList())));
180 }
181 }
182
183 try {
184
185 try {
186
187
188 createConcatDistinctFunction("MEAS_CM", "VARCHAR(2000)");
189 createConcatDistinctFunction("DEP_NM", "VARCHAR(255)");
190
191
192 long nbRowsInserted = createBaseTable(context, outputType);
193 progressionModel.increments(1);
194 if (LOG.isDebugEnabled()) {
195 LOG.debug(String.format("%s sampling operations have to be extract (temp table : %s)", nbRowsInserted, context.getBaseTableName()));
196 } else {
197 LOG.info(String.format("%s sampling operations have to be extract", nbRowsInserted));
198 }
199 if (nbRowsInserted == 0) {
200 throw new DaliBusinessException(t("dali.service.extraction.noData.error"));
201 }
202
203 nbRowsInserted = createRawTable(context, outputType);
204 progressionModel.increments(1);
205 if (LOG.isDebugEnabled()) {
206 LOG.debug(String.format("%s rows of raw data (temp table : %s)", nbRowsInserted, context.getRawTableName()));
207 } else {
208 LOG.info(String.format("%s rows of raw data", nbRowsInserted));
209 }
210
211
212 buildPmfmInformation(context, outputType);
213 progressionModel.increments(1);
214
215
216 CombinationList combinations = null;
217 if (isAggregated(outputType)) {
218 try {
219 combinations = buildCombinations(context);
220 } catch (MaxCombinationExceededException e) {
221 throw new DaliBusinessException(t("dali.service.extraction.maxCombinationsExceeded.error", config.getGridInitializationMaxCombinationCount()));
222 }
223 progressionModel.increments(1);
224
225
226 if (CollectionUtils.isEmpty(combinations)) {
227 throw new DaliBusinessException(t("dali.service.extraction.noCombination.error"));
228 }
229 }
230
231
232 int nbRowsRemoved = cleanRawData(context);
233 progressionModel.increments(1);
234
235 if (LOG.isDebugEnabled()) {
236 if (nbRowsRemoved > 0) {
237 LOG.debug(String.format("%s rows removed from raw data which not corresponding to taxon or taxon group filter", nbRowsRemoved));
238 }
239 LOG.debug(String.format("list of pmfm ids to split : %s", DaliBeans.collectProperties(context.getPmfmInfos(), ExtractionPmfmInfoDTO.PROPERTY_PMFM_ID)));
240 }
241
242
243 int nbRowsRemoved2 = cleanHermeticData(context);
244 progressionModel.increments(1);
245
246 if (LOG.isDebugEnabled() && nbRowsRemoved2 > 0) {
247 LOG.debug(String.format("%s rows removed from raw data which not corresponding to user rights (hermetic programs)", nbRowsRemoved2));
248 }
249
250
251 if (nbRowsRemoved + nbRowsRemoved2 == nbRowsInserted) {
252 throw new DaliBusinessException(t("dali.service.extraction.noData.error"));
253 }
254
255
256 createPmfmTables(context);
257 progressionModel.increments(1);
258
259
260 Multimap<ExtractionPmfmInfoDTO, Combination> pmfmResultInfos = null;
261 if (isAggregated(outputType)) {
262 pmfmResultInfos = createPmfmResultTables(context, combinations);
263 progressionModel.increments(1);
264 }
265
266 Map<String, String> fieldNamesByAlias = Maps.newHashMap();
267 Map<String, String> decimalFormats = Maps.newHashMap();
268 Map<String, String> dateFormats = Maps.newHashMap();
269
270
271 createCommonTable(context, outputType, fieldNamesByAlias, decimalFormats, dateFormats);
272 progressionModel.increments(1);
273
274
275 nbRowsInserted = isAggregated(outputType)
276 ? createAggregatedResultTable(context, pmfmResultInfos, fieldNamesByAlias, decimalFormats, dateFormats)
277 : createResultTable(context, fieldNamesByAlias, decimalFormats, dateFormats);
278 progressionModel.increments(1);
279 if (LOG.isDebugEnabled()) {
280 LOG.debug(String.format("%s rows to write (result table : %s)", nbRowsInserted, context.getResultTableName()));
281 } else {
282 LOG.info(String.format("%s rows to write", nbRowsInserted));
283 }
284
285
286 writeExtraction(context, outputType, fieldNamesByAlias, decimalFormats, dateFormats, outputFile);
287 progressionModel.increments(1);
288
289 if (LOG.isInfoEnabled()) {
290 long time = System.currentTimeMillis() - startTime;
291 LOG.info(String.format("Extraction %s performed in %s. result file is : %s", outputType, Times.durationToString(time), outputFile.getAbsolutePath()));
292 }
293
294 } catch (DaliBusinessException e) {
295 throw e;
296 } catch (Exception e) {
297 throw new DaliTechnicalException(t("dali.service.extraction.error"), e);
298 }
299 } finally {
300
301
302 dropConcatDistinctFunction("MEAS_CM");
303 dropConcatDistinctFunction("DEP_NM");
304 }
305 }
306
307 private void buildPmfmInformation(ExtractionContextDTO context, ExtractionOutputType outputType) {
308
309
310 List<ExtractionPmfmInfoDTO> pmfmInfos = getPmfmInfo(context);
311
312
313 Set<Integer> pmfmIdsFromParameter = new HashSet<>();
314 if (isAggregated(outputType)) {
315 pmfmIdsFromParameter.addAll(context.getExtraction().getParameter().getPmfmPresets().stream().map(pmfmPreset -> pmfmPreset.getPmfm().getId()).collect(Collectors.toSet()));
316 pmfmIdsFromParameter.addAll(context.getExtraction().getParameter().getPmfmResults().stream().map(PmfmDTO::getId).collect(Collectors.toSet()));
317 }
318
319
320 Predicate<ExtractionPmfmInfoDTO> pmfmInfoPredicate = isAggregated(outputType)
321 ?
322 pmfmInfo -> {
323
324
325
326 return
327
328 (pmfmInfo.isSurvey() && !pmfmInfo.isIndividual())
329
330 || (!pmfmInfo.isSurvey() && pmfmInfo.isIndividual() && pmfmIdsFromParameter.contains(pmfmInfo.getPmfmId()))
331
332 || (!pmfmInfo.isSurvey() && !pmfmInfo.isIndividual() && !pmfmIdsFromParameter.contains(pmfmInfo.getPmfmId()));
333 }
334 :
335 pmfmInfo -> {
336 return
337
338 !pmfmInfo.isSurvey() || !pmfmInfo.isIndividual();
339 };
340
341
342 pmfmInfos = pmfmInfos.stream().filter(pmfmInfoPredicate).collect(Collectors.toList());
343
344 if (CollectionUtils.isEmpty(pmfmInfos)) {
345 throw new DaliBusinessException(t("dali.service.extraction.noPmfm.error"));
346 }
347
348
349 context.setPmfmInfos(pmfmInfos);
350
351
352 pmfmIdsFromParameter.forEach(pmfmId -> {
353 ExtractionPmfmInfoDTO pmfmInfo = findPmfmInfo(context.getPmfmInfos(), pmfmId);
354 if (pmfmInfo == null) {
355 context.addPmfmInfos(newPmfmInfo(context, pmfmId, false, true));
356 }
357 });
358
359
360 List<String> programCodes = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PROGRAM);
361 Set<PmfmStrategyDTO> allPmfmStrategies = DaliBeans.getExtractionPeriods(context.getExtraction()).stream()
362 .collect(HashSet::new,
363 (pmfmStrategies, period) ->
364 pmfmStrategies.addAll(strategyDao.getPmfmStrategiesByProgramCodesAndDates(programCodes, period.getStartDate(), period.getEndDate())),
365 HashSet::addAll);
366
367 Map<Integer, Integer> rankOrdersByPmfmId = allPmfmStrategies.stream()
368 .collect(HashMap::new, (map, pmfmStrategy) -> map.put(pmfmStrategy.getPmfm().getId(), pmfmStrategy.getRankOrder()), HashMap::putAll);
369
370
371 context.getPmfmInfos().forEach(pmfmInfo -> {
372 Integer rankOrder = rankOrdersByPmfmId.get(pmfmInfo.getPmfmId());
373
374 pmfmInfo.setRankOrder(rankOrder != null ? rankOrder : Integer.MAX_VALUE);
375 }
376 );
377 }
378
379 private CombinationList buildCombinations(ExtractionContextDTO context) throws MaxCombinationExceededException {
380
381
382 List<PmfmPresetDTO> orderedPmfmPresets = context.getExtraction().getParameter().getPmfmPresets().stream()
383 .filter(pmfmPreset -> !pmfmPreset.isQualitativeValuesEmpty())
384 .sorted(Comparator.comparingInt(preset -> getPmfmInfo(context.getPmfmInfos(), preset.getPmfm().getId()).getRankOrder()))
385 .collect(Collectors.toList());
386
387
388 DecoratorService decoratorService = DaliServiceLocator.instance().getDecoratorService();
389 if (decoratorService != null)
390 orderedPmfmPresets.forEach(pmfmPresetDTO -> pmfmPresetDTO.getQualitativeValues().sort(
391 new DecoratorComparator<QualitativeValueDTO>(decoratorService.getDecoratorByType(QualitativeValueDTO.class))));
392
393
394 if (CollectionUtils.isEmpty(orderedPmfmPresets)) return null;
395
396
397 List<String> programCodes = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PROGRAM);
398 List<ControlRuleDTO> preconditionedRules = ruleListService.getPreconditionedControlRulesForProgramCodes(programCodes);
399 Multimap<Integer, PreconditionRuleDTO> preconditionRulesByPmfmId = HashMultimap.create();
400
401
402 if (CollectionUtils.isNotEmpty(preconditionedRules)) {
403 for (ControlRuleDTO preconditionedRule : preconditionedRules) {
404 for (PreconditionRuleDTO precondition : preconditionedRule.getPreconditions()) {
405
406 int basePmfmId = precondition.getBaseRule().getRulePmfms(0).getPmfm().getId();
407 int usedPmfmId = precondition.getUsedRule().getRulePmfms(0).getPmfm().getId();
408
409 preconditionRulesByPmfmId.put(basePmfmId, precondition);
410 if (precondition.isBidirectional())
411 preconditionRulesByPmfmId.put(usedPmfmId, precondition);
412 }
413
414 }
415 }
416
417 CombinationList combinations = ruleListService.buildAndFactorizeAllowedValues(
418 orderedPmfmPresets, preconditionRulesByPmfmId,
419 config.getGridInitializationMaxCombinationCount());
420
421
422 AtomicInteger combinationId = new AtomicInteger();
423 combinations.forEach(combination -> combination.setId(combinationId.incrementAndGet()));
424
425 return combinations;
426 }
427
428 private long createBaseTable(ExtractionContextDTO context, ExtractionOutputType outputType) {
429
430 XMLQuery xmlQuery = createXMLQuery("createBaseTable");
431 xmlQuery.bind("baseTableName", context.getBaseTableName());
432 xmlQuery.bind("orderItemTypeCode", getOrderItemTypeCode(context.getExtraction()));
433
434
435 xmlQuery.setGroup("complete", isComplete(outputType));
436
437
438 List<ExtractionPeriodDTO> periodFilters = DaliBeans.getExtractionPeriods(context.getExtraction());
439 Element periodFilter = xmlQuery.getFirstTag(XMLQuery.TAG_WHERE, XMLQuery.ATTR_GROUP, "periodFilter");
440 Assert.notNull(periodFilter);
441 for (int i = 0; i < periodFilters.size(); i++) {
442 XMLQuery periodFilterQuery = createXMLQuery("injectionPeriodFilter");
443 if (i > 0) {
444 periodFilterQuery.getDocumentQuery().getRootElement().setAttribute(XMLQuery.ATTR_OPERATOR, "OR");
445 }
446 String periodAlias = "PERIOD" + i;
447 periodFilterQuery.replaceAllBindings("PERIOD", periodAlias);
448 periodFilter.addContent(periodFilterQuery.getDocument().getRootElement().detach());
449 xmlQuery.bind(periodAlias + "_startDate", Dates.formatDate(periodFilters.get(i).getStartDate(), "dd/MM/yyyy"));
450 xmlQuery.bind(periodAlias + "_endDate", Dates.formatDate(periodFilters.get(i).getEndDate(), "dd/MM/yyyy"));
451 }
452
453
454 List<String> programCodes = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PROGRAM);
455 xmlQuery.bind("progCodes", Daos.getInStatementFromStringCollection(programCodes));
456
457
458 List<Integer> locationIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.LOCATION);
459 xmlQuery.setGroup("locationFilter", CollectionUtils.isNotEmpty(locationIds));
460 xmlQuery.bind("monLocIds", Daos.getInStatementFromIntegerCollection(locationIds));
461
462
463 List<Integer> campaignIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.CAMPAIGN);
464 xmlQuery.setGroup("campaignFilter", CollectionUtils.isNotEmpty(campaignIds));
465 xmlQuery.bind("campaignIds", Daos.getInStatementFromIntegerCollection(campaignIds));
466
467
468 List<Integer> departmentIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.DEPARTMENT);
469 xmlQuery.setGroup("departmentFilter", CollectionUtils.isNotEmpty(departmentIds));
470 xmlQuery.bind("depIds", Daos.getInStatementFromIntegerCollection(departmentIds));
471
472
473 List<Integer> equipmentIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.SAMPLING_EQUIPMENT);
474 xmlQuery.setGroup("equipmentFilter", CollectionUtils.isNotEmpty(equipmentIds));
475 xmlQuery.bind("equipmentIds", Daos.getInStatementFromIntegerCollection(equipmentIds));
476
477
478 List<Integer> pmfmIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PMFM);
479 xmlQuery.setGroup("pmfmFilter", CollectionUtils.isNotEmpty(pmfmIds));
480 xmlQuery.bind("pmfmIds", Daos.getInStatementFromIntegerCollection(pmfmIds));
481
482
483 xmlQuery.bind("samplingEquipmentTranscribingTypeLb", Optional.ofNullable(config.getTranscribingItemTypeLbForSamplingEquipmentNm()).orElse(""));
484 xmlQuery.bind("samplingEquipmentTranscribingTypeLbForExtraction", Optional.ofNullable(config.getTranscribingItemTypeLbForSamplingEquipmentExtraction()).orElse(""));
485 xmlQuery.bind("qualityFlagTranscribingTypeLb", Optional.ofNullable(config.getTranscribingItemTypeLbForQualFlagNm()).orElse(""));
486 xmlQuery.bind("monitoringLocationTranscribingTypeLb", Optional.ofNullable(config.getTranscribingItemTypeLbForMonLocNm()).orElse(""));
487
488
489 execute(xmlQuery);
490
491 return countFrom(context.getBaseTableName());
492 }
493
494 private List<ExtractionPmfmInfoDTO> getPmfmInfo(ExtractionContextDTO context) {
495
496 XMLQuery xmlQuery = createXMLQuery("pmfmInfo");
497 xmlQuery.bind("rawTableName", context.getRawTableName());
498
499 return extractionResultDao.query(xmlQuery.getSQLQueryAsString(), null, (resultSet, i) -> newPmfmInfo(context,
500 resultSet.getInt(1),
501 resultSet.getBoolean(2),
502 resultSet.getBoolean(3)));
503 }
504
505 private ExtractionPmfmInfoDTO newPmfmInfo(ExtractionContextDTO context, int pmfmId, boolean isSurvey, boolean isIndividual) {
506
507 ExtractionPmfmInfoDTO pmfmInfo = DaliBeanFactory.newExtractionPmfmInfoDTO();
508 pmfmInfo.setPmfmId(pmfmId);
509 pmfmInfo.setSurvey(isSurvey);
510 pmfmInfo.setIndividual(isIndividual);
511
512
513 String safePmfmId = pmfmInfo.getPmfmId() < 0 ? "M" : "" + pmfmInfo.getPmfmId();
514 pmfmInfo.setAlias((pmfmInfo.isSurvey() ? "SU" : "SO") + (pmfmInfo.isIndividual() ? "I" : "") + safePmfmId);
515
516 pmfmInfo.setTableName(String.format(PMFM_TABLE_NAME_PATTERN, context.getUniqueId(), pmfmInfo.getAlias()));
517
518 return pmfmInfo;
519 }
520
521 private long createRawTable(ExtractionContextDTO context, ExtractionOutputType outputType) {
522
523 XMLQuery xmlQuery = createXMLQuery("createRawTable");
524 xmlQuery.bind("rawTableName", context.getRawTableName());
525 xmlQuery.bind("baseTableName", context.getBaseTableName());
526
527
528 xmlQuery.setGroup("complete", isComplete(outputType));
529
530 execute(xmlQuery);
531
532 return countFrom(context.getRawTableName());
533 }
534
535 private int cleanRawData(ExtractionContextDTO context) {
536
537 XMLQuery xmlQuery = createXMLQuery("cleanRawData");
538 xmlQuery.bind("tableName", context.getRawTableName());
539
540
541 List<Integer> taxonGroupIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.TAXON_GROUP);
542 List<Integer> taxonNameIds = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.TAXON);
543
544 if (CollectionUtils.isEmpty(taxonGroupIds) && CollectionUtils.isEmpty(taxonNameIds))
545
546 return 0;
547
548 if (CollectionUtils.isNotEmpty(taxonGroupIds)) {
549 xmlQuery.setGroup("taxonGroup", true);
550 xmlQuery.bind("taxonGroupIds", Daos.getInStatementFromIntegerCollection(taxonGroupIds));
551 } else {
552 xmlQuery.setGroup("taxonGroup", false);
553 }
554
555 if (CollectionUtils.isNotEmpty(taxonNameIds)) {
556 xmlQuery.setGroup("taxonName", true);
557 xmlQuery.bind("taxonNameIds", Daos.getInStatementFromIntegerCollection(taxonNameIds));
558 } else {
559 xmlQuery.setGroup("taxonName", false);
560 }
561
562 return execute(xmlQuery);
563
564 }
565
566 private int cleanHermeticData(ExtractionContextDTO context) {
567
568
569 List<String> programCodes = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PROGRAM);
570 List<String> hermeticProgramCodes = programStrategyService.getProgramsByCodes(programCodes).stream()
571 .filter(ProgramDTO::isDepartmentHermetic)
572 .map(ProgramDTO::getCode)
573 .collect(Collectors.toList());
574
575 if (hermeticProgramCodes.isEmpty()) return 0;
576
577
578 int nbRemoves = 0;
579 Integer userId= dataContext.getRecorderPersonId();
580 Assert.notNull(userId);
581 Integer recDepId = dataContext.getRecorderDepartmentId();
582 Assert.notNull(recDepId);
583 Set<String> managedProgramCodes = programStrategyService.getManagedProgramCodesByQuserId(userId);
584
585
586 for (String hermeticProgramCode: hermeticProgramCodes) {
587 if (managedProgramCodes == null || !managedProgramCodes.contains(hermeticProgramCode)) {
588
589 XMLQuery xmlQuery = createXMLQuery("cleanHermeticData");
590 xmlQuery.bind("tableName", context.getRawTableName());
591 xmlQuery.bind("programCode", hermeticProgramCode);
592 xmlQuery.bind("recDepId", recDepId.toString());
593
594 nbRemoves += execute(xmlQuery);
595 }
596 }
597
598 return nbRemoves;
599
600 }
601
602 private void createPmfmTables(ExtractionContextDTO context) {
603
604 for (ExtractionPmfmInfoDTO pmfmInfo : context.getPmfmInfos()) {
605
606 XMLQuery xmlQuery = createXMLQuery("createPmfmTable");
607 xmlQuery.bind("pmfmTableName", pmfmInfo.getTableName());
608 xmlQuery.bind("rawTableName", context.getRawTableName());
609 xmlQuery.bind("parentId", pmfmInfo.isSurvey() ? "SURVEY_ID" : "SAMPLING_OPER_ID");
610 xmlQuery.bind("pmfmId", String.valueOf(pmfmInfo.getPmfmId()));
611 xmlQuery.bind("isSurveyMeas", pmfmInfo.isSurvey() ? "1" : "0");
612 xmlQuery.bind("measIndivId", pmfmInfo.isIndividual() ? "NOT NULL" : "NULL");
613
614 xmlQuery.bind("transcribingItemTypeLb", Optional.ofNullable(config.getTranscribingItemTypeLbForQualitativeValueNm()).orElse(""));
615 xmlQuery.bind("transcribingItemTypeLbForExtraction", Optional.ofNullable(config.getTranscribingItemTypeLbForQualitativeValueExtraction()).orElse(""));
616
617 execute(xmlQuery);
618
619 if (LOG.isDebugEnabled()) {
620
621
622 long nbRowsInserted = countFrom(pmfmInfo.getTableName());
623 LOG.debug(String.format("%s rows of pmfm raw data inserted into %s", nbRowsInserted, pmfmInfo.getTableName()));
624 }
625 }
626 }
627
628 private Multimap<ExtractionPmfmInfoDTO, Combination> createPmfmResultTables(ExtractionContextDTO context, CombinationList combinations) {
629
630 Multimap<ExtractionPmfmInfoDTO, Combination> pmfmResultInfos = ArrayListMultimap.create();
631
632
633 for (PmfmDTO pmfmResult : context.getExtraction().getParameter().getPmfmResults()) {
634
635 ExtractionPmfmInfoDTO pmfmResultInfo = getPmfmInfo(context.getPmfmInfos(), pmfmResult.getId());
636
637 for (Combination combination : combinations) {
638
639
640 String pmfmResultTableName = getPmfmResultTableName(pmfmResultInfo, combination);
641
642 if (LOG.isDebugEnabled())
643 LOG.debug(String.format("pmfmResultTableName = %s ; combination = %s", pmfmResultTableName, combination));
644
645 XMLQuery xmlQuery = createXMLQuery("createPmfmResultTable");
646 xmlQuery.bind("pmfmResultTableName", pmfmResultTableName);
647 xmlQuery.bind("pmfmTableName", pmfmResultInfo.getTableName());
648
649
650 for (IntegerPair pair : combination) {
651
652 ExtractionPmfmInfoDTO pmfmInfo = getPmfmInfo(context.getPmfmInfos(), pair.getKey());
653 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfmAggregation"), "PMFM_ALIAS", pmfmInfo.getAlias());
654
655
656 xmlQuery.bind(pmfmInfo.getAlias() + "_pmfmTableName", pmfmInfo.getTableName());
657 xmlQuery.bind(pmfmInfo.getAlias() + "_qualValueId", String.valueOf(pair.getValue()));
658
659 }
660
661 execute(xmlQuery);
662
663 boolean approved = context.getExtraction().getParameter().isFillZero();
664
665
666 if (!approved) {
667
668
669 long nbRowsInserted = countFrom(pmfmResultTableName);
670
671
672 approved = nbRowsInserted > 0;
673 }
674
675
676 if (approved)
677 pmfmResultInfos.put(pmfmResultInfo, combination);
678
679 }
680 }
681
682 return pmfmResultInfos;
683 }
684
685 private void createCommonTable(ExtractionContextDTO context, ExtractionOutputType outputType,
686 Map<String, String> fieldNamesByAlias, Map<String, String> decimalFormats, Map<String, String> dateFormats) {
687
688 XMLQuery xmlQuery = createXMLQuery("createCommonTable");
689 xmlQuery.bind("commonTableName", context.getCommonTableName());
690 xmlQuery.bind("sourceTableName", context.getRawTableName());
691
692
693 xmlQuery.setGroup("complete", isComplete(outputType));
694 xmlQuery.setGroup("individual", !isAggregated(outputType));
695
696
697 List<ExtractionPmfmInfoDTO> surveyNonIndividualMeasurements = context.getPmfmInfos().stream()
698 .filter(pmfmInfo -> !pmfmInfo.isIndividual() && pmfmInfo.isSurvey())
699 .sorted(Comparator.comparingInt(ExtractionPmfmInfoDTO::getRankOrder))
700 .collect(Collectors.toList());
701
702 if (CollectionUtils.isNotEmpty(surveyNonIndividualMeasurements)) {
703
704
705 xmlQuery.setGroup("surveyMeasurements", true);
706
707 surveyNonIndividualMeasurements.forEach(pmfmInfo -> {
708
709 PmfmDTO pmfm = pmfmDao.getPmfmById(pmfmInfo.getPmfmId());
710
711
712 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"),
713 "PMFM_ALIAS",
714 pmfmInfo.getAlias(),
715
716 "surveyMeasurements");
717
718
719 xmlQuery.bind(pmfmInfo.getAlias() + "_pmfmTableName", pmfmInfo.getTableName());
720
721
722 xmlQuery.setGroup(pmfmInfo.getAlias() + "_numerical_without_zero", !pmfm.getParameter().isQualitative());
723 xmlQuery.setGroup(pmfmInfo.getAlias() + "_numerical_with_zero", false);
724 xmlQuery.setGroup(pmfmInfo.getAlias() + "_qualitative", pmfm.getParameter().isQualitative());
725
726
727 xmlQuery.setGroup(pmfmInfo.getAlias() + "_surveyJoin", true);
728 xmlQuery.setGroup(pmfmInfo.getAlias() + "_samplingOperationJoin", false);
729 xmlQuery.setGroup(pmfmInfo.getAlias() + "_surveyJoin_individual", false);
730 xmlQuery.setGroup(pmfmInfo.getAlias() + "_samplingOperationJoin_individual", false);
731
732
733 String unitName = extractionResultDao.getPmfmUnitNameForExtraction(pmfm);
734 String pmfmName = String.format("%s%s", extractionResultDao.getPmfmNameForExtraction(pmfm), StringUtils.isNotBlank(unitName) ? "_" + unitName : "");
735 fieldNamesByAlias.put(pmfmInfo.getAlias(), DaliBeans.toFullySecuredString(pmfmName));
736
737
738 if (!pmfm.getParameter().isQualitative()) {
739 decimalFormats.put(pmfmInfo.getAlias(), getNumericFormat(pmfm));
740 }
741 });
742
743
744 xmlQuery.injectQuery(
745 getXMLQueryFile("injectionAnalyst"),
746 "surveyMeasurements"
747 );
748 xmlQuery.setGroup("survey", true);
749 xmlQuery.setGroup("samplingOperation", false);
750 xmlQuery.bind("depNmFields", getAliasedFields(surveyNonIndividualMeasurements, "DEP_NM"));
751
752 } else {
753
754
755 xmlQuery.setGroup("surveyMeasurements", false);
756 }
757
758
759 prepare(xmlQuery, fieldNamesByAlias, decimalFormats, dateFormats);
760
761 execute(xmlQuery);
762
763 if (LOG.isDebugEnabled()) {
764
765
766 long nbRowsInserted = countFrom(context.getCommonTableName());
767 LOG.debug(String.format("%s rows of common data inserted into %s", nbRowsInserted, context.getCommonTableName()));
768 }
769
770 }
771
772 private long createResultTable(ExtractionContextDTO context,
773 Map<String, String> fieldNamesByAlias, Map<String, String> decimalFormats, Map<String, String> dateFormats) {
774
775 XMLQuery xmlQuery = createXMLQuery("createResultTable");
776 xmlQuery.bind("resultTableName", context.getResultTableName());
777 xmlQuery.bind("sourceTableName", context.getCommonTableName());
778
779
780 xmlQuery.setGroup("individual", true);
781
782
783 List<ExtractionPmfmInfoDTO> individualMeasurements = context.getPmfmInfos().stream()
784 .filter(pmfmInfo -> !pmfmInfo.isSurvey())
785
786 .sorted(((Comparator<ExtractionPmfmInfoDTO>) (pmfmInfo1, pmfmInfo2) -> Boolean.compare(pmfmInfo1.isIndividual(), pmfmInfo2.isIndividual()))
787 .thenComparingInt(ExtractionPmfmInfoDTO::getRankOrder))
788 .collect(Collectors.toList());
789
790 if (CollectionUtils.isNotEmpty(individualMeasurements)) {
791 individualMeasurements.forEach(pmfmInfo -> {
792
793 PmfmDTO pmfm = pmfmDao.getPmfmById(pmfmInfo.getPmfmId());
794
795
796 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"), "PMFM_ALIAS", pmfmInfo.getAlias());
797
798
799 xmlQuery.bind(pmfmInfo.getAlias() + "_pmfmTableName", pmfmInfo.getTableName());
800
801
802 xmlQuery.setGroup(pmfmInfo.getAlias() + "_numerical_without_zero", !pmfm.getParameter().isQualitative());
803 xmlQuery.setGroup(pmfmInfo.getAlias() + "_numerical_with_zero", false);
804 xmlQuery.setGroup(pmfmInfo.getAlias() + "_qualitative", pmfm.getParameter().isQualitative());
805
806
807 xmlQuery.setGroup(pmfmInfo.getAlias() + "_surveyJoin", false);
808 xmlQuery.setGroup(pmfmInfo.getAlias() + "_samplingOperationJoin", !pmfmInfo.isIndividual());
809 xmlQuery.setGroup(pmfmInfo.getAlias() + "_surveyJoin_individual", false);
810 xmlQuery.setGroup(pmfmInfo.getAlias() + "_samplingOperationJoin_individual", pmfmInfo.isIndividual());
811
812
813 String unitName = extractionResultDao.getPmfmUnitNameForExtraction(pmfm);
814 String pmfmName = String.format("%s%s", extractionResultDao.getPmfmNameForExtraction(pmfm), StringUtils.isNotBlank(unitName) ? "_" + unitName : "");
815 String formattedPmfmName = String.format("%s%s",
816 t("dali.service.extraction.fieldNamePrefix.MEAS"),
817 pmfmName);
818 fieldNamesByAlias.put(pmfmInfo.getAlias(), DaliBeans.toFullySecuredString(formattedPmfmName));
819
820
821 if (!pmfm.getParameter().isQualitative()) {
822 decimalFormats.put(pmfmInfo.getAlias(), getNumericFormat(pmfm));
823 }
824 });
825
826
827 xmlQuery.injectQuery(getXMLQueryFile("injectionComment"));
828 xmlQuery.bind("measCmFields", getAliasedFields(individualMeasurements, "MEAS_CM"));
829
830
831 xmlQuery.injectQuery(getXMLQueryFile("injectionAnalyst"));
832 xmlQuery.setGroup("survey", false);
833 xmlQuery.setGroup("samplingOperation", true);
834 xmlQuery.bind("depNmFields", getAliasedFields(individualMeasurements, "DEP_NM"));
835
836
837 for (int i = 0; i < individualMeasurements.size() + 2 ; i++) {
838 xmlQuery.addSubSelect("groupedMeasurements", getXMLQueryFile("subSelectNull"));
839 }
840
841 }
842
843 prepare(xmlQuery, fieldNamesByAlias, decimalFormats, dateFormats);
844
845 execute(xmlQuery);
846 return countFrom(context.getResultTableName());
847 }
848
849 private long createAggregatedResultTable(ExtractionContextDTO context, Multimap<ExtractionPmfmInfoDTO, Combination> pmfmResultInfos,
850 Map<String, String> fieldNamesByAlias, Map<String, String> decimalFormats, Map<String, String> dateFormats) {
851
852 XMLQuery xmlQuery = createXMLQuery("createResultTable");
853 xmlQuery.bind("resultTableName", context.getResultTableName());
854 xmlQuery.bind("sourceTableName", context.getCommonTableName());
855
856
857 xmlQuery.setGroup("individual", false);
858
859
860 if (pmfmResultInfos != null)
861 pmfmResultInfos.forEach((pmfmResultInfo, combination) -> {
862
863 PmfmDTO pmfmResult = pmfmDao.getPmfmById(pmfmResultInfo.getPmfmId());
864
865 String pmfmResultAlias = getPmfmResultAlias(pmfmResultInfo, combination);
866 String pmfmResultTableName = getPmfmResultTableName(pmfmResultInfo, combination);
867
868
869 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"), "PMFM_ALIAS", pmfmResultAlias);
870
871
872 xmlQuery.bind(pmfmResultAlias + "_pmfmTableName", pmfmResultTableName);
873
874
875 xmlQuery.setGroup(pmfmResultAlias + "_numerical_without_zero", !context.getExtraction().getParameter().isFillZero());
876 xmlQuery.setGroup(pmfmResultAlias + "_numerical_with_zero", context.getExtraction().getParameter().isFillZero());
877 xmlQuery.setGroup(pmfmResultAlias + "_qualitative", false);
878
879
880 xmlQuery.setGroup(pmfmResultAlias + "_surveyJoin", false);
881 xmlQuery.setGroup(pmfmResultAlias + "_samplingOperationJoin", true);
882 xmlQuery.setGroup(pmfmResultAlias + "_surveyJoin_individual", false);
883 xmlQuery.setGroup(pmfmResultAlias + "_samplingOperationJoin_individual", false);
884
885
886 String pmfmResultName = DaliBeans.toFullySecuredString(extractionResultDao.getPmfmNameForExtraction(pmfmResult));
887 String unitName = DaliBeans.toFullySecuredString(extractionResultDao.getPmfmUnitNameForExtraction(pmfmResult));
888 StringJoiner combinationName = new StringJoiner("_");
889 combination.forEach(pair -> combinationName.add(DaliBeans.toFullySecuredString(
890 extractionResultDao.getQualitativeValueNameForExtraction(qualitativeValueDao.getQualitativeValueById(pair.getValue())))));
891
892 String formattedPmfmName = String.format("%s%s_%s%s",
893 t("dali.service.extraction.fieldNamePrefix.MEAS"),
894 pmfmResultName, combinationName, StringUtils.isNotBlank(unitName) ? "_" + unitName : "");
895 fieldNamesByAlias.put(pmfmResultAlias, formattedPmfmName);
896 decimalFormats.put(pmfmResultAlias, getNumericFormat(pmfmResult));
897
898 });
899
900 prepare(xmlQuery, fieldNamesByAlias, decimalFormats, dateFormats);
901 execute(xmlQuery);
902
903 return countFrom(context.getResultTableName());
904 }
905
906 private void writeExtraction(ExtractionContextDTO context, ExtractionOutputType outputType,
907 Map<String, String> fieldNamesByAlias,
908 Map<String, String> decimalFormats,
909 Map<String, String> dateFormats,
910 File outputFile)
911 throws IOException {
912
913 XMLQuery xmlQuery = createXMLQuery("selectResultTable");
914 xmlQuery.bind("resultTableName", context.getResultTableName());
915
916
917 List<String> fieldsToIgnore = new ArrayList<>(ImmutableList.of("SURVEY_ID", "SAMPLING_OPER_ID"));
918 if (!isComplete(outputType)) fieldsToIgnore.add("MEAS_INDIV_ID");
919
920
921 if (LOG.isDebugEnabled()) {
922 LOG.debug(String.format("write result into file : %s", outputFile.getAbsolutePath()));
923 }
924 extractionResultDao.dumpQueryToCSV(outputFile, xmlQuery.getSQLQueryAsString(), fieldNamesByAlias, dateFormats, decimalFormats, fieldsToIgnore);
925 }
926
927 private void prepare(XMLQuery xmlQuery, Map<String, String> fieldNamesByAlias, Map<String, String> decimalFormats, Map<String, String> dateFormats) {
928
929 xmlQuery.getFirstQueryTag().getChildren(XMLQuery.TAG_SELECT).forEach(select -> {
930
931
932 String alias = select.getAttributeValue(XMLQuery.ATTR_ALIAS);
933 fieldNamesByAlias.putIfAbsent(alias, t("dali.service.extraction.fieldName." + alias));
934
935 String type = select.getAttributeValue(XMLQuery.ATTR_TYPE);
936 if (XMLQuery.TYPE_DATE.equalsIgnoreCase(type)) {
937 dateFormats.putIfAbsent(alias, "dd/MM/yyyy");
938 } else if (XMLQuery.TYPE_NUMBER.equalsIgnoreCase(type)) {
939 decimalFormats.putIfAbsent(alias, getDefaultNumericFormat());
940 }
941
942 });
943 }
944
945 private int execute(XMLQuery xmlQuery) {
946
947 return extractionResultDao.queryUpdate(xmlQuery.getSQLQueryAsString());
948 }
949
950 private long countFrom(String tableName) {
951
952 XMLQuery xmlQuery = createXMLQuery("countFrom");
953 xmlQuery.bind("tableName", tableName);
954 return extractionResultDao.queryCount(xmlQuery.getSQLQueryAsString());
955
956 }
957
958 private ExtractionPmfmInfoDTO findPmfmInfo(Collection<ExtractionPmfmInfoDTO> pmfmInfos, int pmfmId) {
959
960 Optional<ExtractionPmfmInfoDTO> optional = pmfmInfos.stream().filter(pmfmInfo -> pmfmInfo.getPmfmId() == pmfmId).findFirst();
961 return optional.orElse(null);
962 }
963
964 private ExtractionPmfmInfoDTO getPmfmInfo(Collection<ExtractionPmfmInfoDTO> pmfmInfos, int pmfmId) {
965
966 ExtractionPmfmInfoDTO pmfmInfo = findPmfmInfo(pmfmInfos, pmfmId);
967 if (pmfmInfo == null)
968 throw new DaliTechnicalException(String.format("The PMFM id=%s is not part of the extraction context", pmfmId));
969 return pmfmInfo;
970 }
971
972 private String getPmfmResultTableName(ExtractionPmfmInfoDTO pmfmResultInfo, Combination combination) {
973 return String.format("%s_%s", pmfmResultInfo.getTableName(), combination.getId());
974 }
975
976 private String getPmfmResultAlias(ExtractionPmfmInfoDTO pmfmResultInfo, Combination combination) {
977 return String.format("%s_%s", pmfmResultInfo.getAlias(), combination.getId());
978 }
979
980 private String getNumericFormat(PmfmDTO pmfm) {
981
982 if (pmfm.getMaxDecimals() == null) {
983 return getDefaultNumericFormat();
984 }
985
986
987 return "#." + StringUtils.repeat("0", pmfm.getMaxDecimals());
988 }
989
990 private String getDefaultNumericFormat() {
991 return "#." + StringUtils.repeat("#", 8);
992 }
993
994 private String getOrderItemTypeCode(ExtractionDTO extraction) {
995 FilterDTO orderItemTypeFilter = DaliBeans.getFilterOfType(extraction, ExtractionFilterTypeValues.ORDER_ITEM_TYPE);
996 Assert.notNull(orderItemTypeFilter);
997 Assert.size(orderItemTypeFilter.getElements(), 1);
998
999 GroupingTypeDTO groupingType = (GroupingTypeDTO) orderItemTypeFilter.getElements().get(0);
1000 Assert.notNull(groupingType);
1001 Assert.notBlank(groupingType.getCode());
1002 return groupingType.getCode();
1003 }
1004
1005 private XMLQuery createXMLQuery(String queryName) {
1006 XMLQuery query = DaliServiceLocator.instance().getService("XMLQuery", XMLQuery.class);
1007 query.setQuery(getXMLQueryFile(queryName));
1008 return query;
1009 }
1010
1011 private URL getXMLQueryFile(String queryName) {
1012 URL fileURL = getClass().getClassLoader().getResource(XML_QUERY_PATH + "/" + queryName + ".xml");
1013 if (fileURL == null)
1014 throw new DaliTechnicalException(String.format("query '%s' not found in resources", queryName));
1015 return fileURL;
1016 }
1017
1018 private boolean isComplete(ExtractionOutputType outputType) {
1019 return ExtractionOutputType.COMPLETE.equals(outputType) || ExtractionOutputType.AGGREGATED_COMPLETE.equals(outputType);
1020 }
1021
1022 private boolean isAggregated(ExtractionOutputType outputType) {
1023 return ExtractionOutputType.AGGREGATED_STANDARD.equals(outputType) || ExtractionOutputType.AGGREGATED_COMPLETE.equals(outputType);
1024 }
1025
1026 private String getAliasedFields(Collection<ExtractionPmfmInfoDTO> pmfmInfos, final String fieldName) {
1027 return pmfmInfos.stream().map(pmfmInfo -> String.format("%s.%s", pmfmInfo.getAlias(), fieldName)).collect(Collectors.joining(","));
1028 }
1029
1030 private void createConcatDistinctFunction(String functionName, String valueType) {
1031
1032 dropConcatDistinctFunction(functionName);
1033
1034
1035 String query = "CREATE FUNCTION CONCAT_DISTINCT_" + functionName + "(IN_ARRAY " + valueType + " ARRAY, SEPARATOR VARCHAR(10)) RETURNS LONGVARCHAR " +
1036 "LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:fr.ifremer.dali.dto.DaliBeans.getUnifiedSQLString';";
1037
1038 extractionResultDao.queryUpdate(query, null);
1039
1040 }
1041
1042 private void dropConcatDistinctFunction(String functionName) {
1043
1044 extractionResultDao.queryUpdate("DROP FUNCTION CONCAT_DISTINCT_" + functionName + " IF EXISTS", null);
1045 }
1046
1047 }