View Javadoc
1   package fr.ifremer.dali.service.extraction;
2   
3   /*
4    * #%L
5    * Dali :: Core
6    * %%
7    * Copyright (C) 2017 Ifremer
8    * %%
9    * This program is free software: you can redistribute it and/or modify
10   * it under the terms of the GNU Affero General Public License as published by
11   * the Free Software Foundation, either version 3 of the License, or
12   * (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 Affero General Public License
20   * along with this program.  If not, see <http://www.gnu.org/licenses/>.
21   * #L%
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   * Extraction Perform Service
81   *
82   * @author peck7 on 23/11/2017.
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      * {@inheritDoc}
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         // ensure all filters are loaded
146         extractionService.loadFilteredElements(extraction);
147 
148         // init progression model
149         progressionModel.setMessage("");
150         progressionModel.setTotal(isAggregated(outputType) ? 11 : 9);
151         long startTime = System.currentTimeMillis();
152 
153         // Extraction context
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                 // create the concat functions used to concat distinct strings (ex: DEP_NM)
188                 createConcatDistinctFunction("MEAS_CM", "VARCHAR(2000)");
189                 createConcatDistinctFunction("DEP_NM", "VARCHAR(255)");
190 
191                 // STEP 1 : Create the base table
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                 // STEP 2 : Create the raw table with full measurements data in line
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                 // STEP 3 : Build PMFM metadata
212                 buildPmfmInformation(context, outputType);
213                 progressionModel.increments(1);
214 
215                 // STEP 4 : Build qualitative values combinations from pmfm presets
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                     // Now factorizedPmfmPresets contains only valid combinations
226                     if (CollectionUtils.isEmpty(combinations)) {
227                         throw new DaliBusinessException(t("dali.service.extraction.noCombination.error"));
228                     }
229                 }
230 
231                 // STEP 5a : Clean raw data
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                 // STEP 5b : Second clean operation: remove surveys from hermetic programs if user is not allowed (Mantis #42817)
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                 // if all the rows have been removed because of filters, there is no more data to extract
251                 if (nbRowsRemoved + nbRowsRemoved2 == nbRowsInserted) {
252                     throw new DaliBusinessException(t("dali.service.extraction.noData.error"));
253                 }
254 
255                 // STEP 6 : Create pmfm tables
256                 createPmfmTables(context);
257                 progressionModel.increments(1);
258 
259                 // STEP 7 : Create tables with pmfm aggregation
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                 // STEP 8 : Create common table (with non individual measurements)
271                 createCommonTable(context, outputType, fieldNamesByAlias, decimalFormats, dateFormats);
272                 progressionModel.increments(1);
273 
274                 // STEP 9 : Create result table
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                 // STEP 10 : Final query and write to csv file
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; // throw directly
296             } catch (Exception e) {
297                 throw new DaliTechnicalException(t("dali.service.extraction.error"), e);
298             }
299         } finally {
300 
301             // drop concat functions
302             dropConcatDistinctFunction("MEAS_CM");
303             dropConcatDistinctFunction("DEP_NM");
304         }
305     }
306 
307     private void buildPmfmInformation(ExtractionContextDTO context, ExtractionOutputType outputType) {
308 
309         // Gather pmfm from raw table
310         List<ExtractionPmfmInfoDTO> pmfmInfos = getPmfmInfo(context);
311 
312         // Get pmfm ids from extraction parameter
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         // Build pmfm filter depending on output type
320         Predicate<ExtractionPmfmInfoDTO> pmfmInfoPredicate = isAggregated(outputType)
321                 ?
322                 pmfmInfo -> {
323                     // Remove pmfm on sampling operation not in pmfm preset
324                     // And clean potential error on non individual pmfm without individual number
325                     // for example survey_lb='NH2014-02' from 'DECHETS_FLOTTANTS' have pmfm=13171 (nb déchets) on sampling operation without MEAS_INDIV_ID
326                     return
327                             // keep survey non individual measurements
328                             (pmfmInfo.isSurvey() && !pmfmInfo.isIndividual())
329                                     // keep sampling operation individual measurements allowed by parameter
330                                     || (!pmfmInfo.isSurvey() && pmfmInfo.isIndividual() && pmfmIdsFromParameter.contains(pmfmInfo.getPmfmId()))
331                                     //  keep sampling operation non individual measurements not preset in parameter
332                                     || (!pmfmInfo.isSurvey() && !pmfmInfo.isIndividual() && !pmfmIdsFromParameter.contains(pmfmInfo.getPmfmId()));
333                 }
334                 :
335                 pmfmInfo -> {
336                     return
337                             // keep survey non individual measurements and all sampling operation measurements
338                             !pmfmInfo.isSurvey() || !pmfmInfo.isIndividual();
339                 };
340 
341         // Filter pmfms
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         // Affect to context
349         context.setPmfmInfos(pmfmInfos);
350 
351         // Add missing pmfm info from extraction parameter
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         // Get PMFM strategies corresponding to extraction filters and compute pmfm sort order
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         // Affect rank order
371         context.getPmfmInfos().forEach(pmfmInfo -> {
372                     Integer rankOrder = rankOrdersByPmfmId.get(pmfmInfo.getPmfmId());
373                     // put the pmfm outside a strategy at the end (Mantis #43695)
374                     pmfmInfo.setRankOrder(rankOrder != null ? rankOrder : Integer.MAX_VALUE);
375                 }
376         );
377     }
378 
379     private CombinationList buildCombinations(ExtractionContextDTO context) throws MaxCombinationExceededException {
380 
381         // Sort the Pmfm presets
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         // Sort the qualitative values in each orderedPmfmPresets
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         // Abort if nothing to do
394         if (CollectionUtils.isEmpty(orderedPmfmPresets)) return null;
395 
396         // Get preconditioned rules for programs
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         // Build a map of all preconditions by pmfm id
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         // Add unique combination id
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         // active groups depending the output type
435         xmlQuery.setGroup("complete", isComplete(outputType));
436 
437         // add mandatory period filter
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         // add mandatory program filter
454         List<String> programCodes = DaliBeans.getFilterElementsIds(context.getExtraction(), ExtractionFilterTypeValues.PROGRAM);
455         xmlQuery.bind("progCodes", Daos.getInStatementFromStringCollection(programCodes));
456 
457         // add monitoring location filter
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         // add campaign filter
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         // add department filter
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         // add sampling equipment filter
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         // add pmfm filter
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         // add referential transcribing type labels
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         // execute insertion
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         // compute alias
513         String safePmfmId = pmfmInfo.getPmfmId() < 0 ? "M" : "" + pmfmInfo.getPmfmId();
514         pmfmInfo.setAlias((pmfmInfo.isSurvey() ? "SU" : "SO") + (pmfmInfo.isIndividual() ? "I" : "") + safePmfmId);
515         // Generate pmfm table name
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         // active groups depending the output type
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         // if a taxon or taxon group filter is defined, will remove raw line not corresponding to filter (avoid blank cells in split table)
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             // Nothing to delete
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         // Get program list from context (mandatory)
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         // Collect managed programs
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         // Iterate hermetic programs and test with rec_dep_id if user is not manager
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             // Add transcribing for qualitative values (Mantis #48699)
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                 // Count inserted data
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         // Create a table for each result PMFMs
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                 // Table name use the result pmfm table name _ the combination id
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                 // Add injection query for each item in combination (replacing all bindings by the pmfm alias)
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                     // Bind table name and qualitative value
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                 // If fill zeros is not set, try to evict empty result (Mantis #39558)
666                 if (!approved) {
667 
668                     // Check if pmfm result exists
669                     long nbRowsInserted = countFrom(pmfmResultTableName);
670 
671                     // combination is approved if result exists
672                     approved = nbRowsInserted > 0;
673                 }
674 
675                 // Store in map if combination approved
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         // Active groups depending the output type
693         xmlQuery.setGroup("complete", isComplete(outputType));
694         xmlQuery.setGroup("individual", !isAggregated(outputType));
695 
696         // Add all survey non individual (ordered) measurements (is exists)
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             // Add them in injection point (if survey measurements)
705             xmlQuery.setGroup("surveyMeasurements", true);
706 
707             surveyNonIndividualMeasurements.forEach(pmfmInfo -> {
708 
709                 PmfmDTO pmfm = pmfmDao.getPmfmById(pmfmInfo.getPmfmId());
710 
711                 // Add injection query for pmfm table (replacing all bindings by the pmfm alias)
712                 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"),
713                         "PMFM_ALIAS",
714                         pmfmInfo.getAlias(),
715                         // Use this injection point
716                         "surveyMeasurements");
717 
718                 // Bind table names
719                 xmlQuery.bind(pmfmInfo.getAlias() + "_pmfmTableName", pmfmInfo.getTableName());
720 
721                 // Active value
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                 // Active join link
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                 // Build output name
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)); // no prefix
736 
737                 // Build number format
738                 if (!pmfm.getParameter().isQualitative()) {
739                     decimalFormats.put(pmfmInfo.getAlias(), getNumericFormat(pmfm));
740                 }
741             });
742 
743             // Add analyst from survey non individual measurements
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             // Disable the injection point (see Mantis #40480)
755             xmlQuery.setGroup("surveyMeasurements", false);
756         }
757 
758         // Compute field alias and format
759         prepare(xmlQuery, fieldNamesByAlias, decimalFormats, dateFormats);
760 
761         execute(xmlQuery);
762 
763         if (LOG.isDebugEnabled()) {
764 
765             // Count inserted data
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         // Active individual group
780         xmlQuery.setGroup("individual", true);
781 
782         // Add sampling operations non individual and individual measurements
783         List<ExtractionPmfmInfoDTO> individualMeasurements = context.getPmfmInfos().stream()
784                 .filter(pmfmInfo -> !pmfmInfo.isSurvey())
785                 // sort non individual first then by rank order
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                 // Add injection query for pmfm table (replacing all bindings by the pmfm alias)
796                 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"), "PMFM_ALIAS", pmfmInfo.getAlias());
797 
798                 // Bind table names
799                 xmlQuery.bind(pmfmInfo.getAlias() + "_pmfmTableName", pmfmInfo.getTableName());
800 
801                 // Active value
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                 // Active join link
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                 // Build output name
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                 // Build number format
821                 if (!pmfm.getParameter().isQualitative()) {
822                     decimalFormats.put(pmfmInfo.getAlias(), getNumericFormat(pmfm));
823                 }
824             });
825 
826             // Add measurement comment
827             xmlQuery.injectQuery(getXMLQueryFile("injectionComment"));
828             xmlQuery.bind("measCmFields", getAliasedFields(individualMeasurements, "MEAS_CM"));
829 
830             // Add analyst from individual measurements
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             // Add rows without individual measurements (with NULL columns in union sub query)
837             for (int i = 0; i < individualMeasurements.size() + 2 /* Add also 2 NULL columns for comment and analyst */; 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         // Disable individual group
857         xmlQuery.setGroup("individual", false);
858 
859         // Add all pmfm results (already ordered by combination)
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                 // Add injection query for pmfm result table (replacing all bindings by the pmfm result alias)
869                 xmlQuery.injectQuery(getXMLQueryFile("injectionPmfm"), "PMFM_ALIAS", pmfmResultAlias);
870 
871                 // Bind table names
872                 xmlQuery.bind(pmfmResultAlias + "_pmfmTableName", pmfmResultTableName);
873 
874                 // Active value
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                 // Active join link
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                 // Build output name
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         // Ignore some Fields
917         List<String> fieldsToIgnore = new ArrayList<>(ImmutableList.of("SURVEY_ID", "SAMPLING_OPER_ID"));
918         if (!isComplete(outputType)) fieldsToIgnore.add("MEAS_INDIV_ID");
919 
920         // write result
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             // Compute alias i18n key
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         // format with max decimals from pmfm definition (decimal 0 is appended)
987         return "#." + StringUtils.repeat("0", pmfm.getMaxDecimals());
988     }
989 
990     private String getDefaultNumericFormat() {
991         return "#." + StringUtils.repeat("#", 8); // 8 decimals should be enough by default (decimal 0 is erased)
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         // function with external java method
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 }