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
25
26 import com.google.common.collect.ImmutableList;
27 import com.google.common.collect.Lists;
28 import fr.ifremer.dali.config.DaliConfiguration;
29 import fr.ifremer.dali.dao.DaliDatabaseResource;
30 import fr.ifremer.dali.dao.system.extraction.DaliExtractionResultDao;
31 import fr.ifremer.dali.dto.DaliBeanFactory;
32 import fr.ifremer.dali.dto.DaliBeans;
33 import fr.ifremer.dali.dto.configuration.filter.FilterDTO;
34 import fr.ifremer.dali.dto.enums.ExtractionFilterTypeValues;
35 import fr.ifremer.dali.dto.enums.ExtractionOutputType;
36 import fr.ifremer.dali.dto.system.extraction.ExtractionDTO;
37 import fr.ifremer.dali.dto.system.extraction.ExtractionParameterDTO;
38 import fr.ifremer.dali.dto.system.extraction.ExtractionPeriodDTO;
39 import fr.ifremer.dali.dto.system.extraction.PmfmPresetDTO;
40 import fr.ifremer.dali.service.DaliServiceLocator;
41 import fr.ifremer.dali.service.DaliTechnicalException;
42 import fr.ifremer.dali.service.StatusFilter;
43 import fr.ifremer.dali.service.administration.program.ProgramStrategyService;
44 import fr.ifremer.dali.service.referential.ReferentialService;
45 import fr.ifremer.quadrige3.core.ProgressionCoreModel;
46 import fr.ifremer.quadrige3.core.dao.technical.xmlQuery.XMLQuery;
47 import fr.ifremer.quadrige3.ui.core.dto.QuadrigeBean;
48 import org.apache.commons.collections4.CollectionUtils;
49 import org.apache.commons.logging.Log;
50 import org.apache.commons.logging.LogFactory;
51 import org.junit.*;
52
53 import java.io.BufferedReader;
54 import java.io.File;
55 import java.io.IOException;
56 import java.net.URISyntaxException;
57 import java.net.URL;
58 import java.nio.charset.StandardCharsets;
59 import java.nio.file.Files;
60 import java.time.LocalDate;
61 import java.util.*;
62
63 import static org.junit.Assert.*;
64
65
66
67
68
69
70 public class ExtractionServiceTest {
71
72 private static final Log LOG = LogFactory.getLog(ExtractionServiceTest.class);
73
74 @ClassRule
75 public static final DaliDatabaseResource dbResource = DaliDatabaseResource.writeDb();
76
77 private ExtractionService service;
78
79 private ExtractionPerformService performService;
80
81 private ProgramStrategyService programStrategyService;
82
83 private ReferentialService referentialService;
84
85 private DaliConfiguration config;
86
87 private XMLQuery xmlQuery;
88
89 private DaliExtractionResultDao resultDao;
90
91 @Before
92 public void setUp() {
93 service = DaliServiceLocator.instance().getExtractionService();
94 performService = DaliServiceLocator.instance().getExtractionPerformService();
95 programStrategyService = DaliServiceLocator.instance().getProgramStrategyService();
96 referentialService = DaliServiceLocator.instance().getReferentialService();
97 config = DaliConfiguration.getInstance();
98 xmlQuery = DaliServiceLocator.instance().getService("XMLQuery", XMLQuery.class);
99 resultDao = DaliServiceLocator.instance().getService("daliExtractionResultDao", DaliExtractionResultDao.class);
100 }
101
102 @Test
103 public void createExtraction() {
104
105 ExtractionDTO extraction = DaliBeanFactory.newExtractionDTO();
106 extraction.setName("extraction test");
107
108
109 ExtractionPeriodDTO period1 = DaliBeanFactory.newExtractionPeriodDTO();
110 period1.setStartDate(LocalDate.of(2015,1,1));
111 period1.setEndDate(LocalDate.of(2015,12,31));
112 ExtractionPeriodDTO period2 = DaliBeanFactory.newExtractionPeriodDTO();
113 period2.setStartDate(LocalDate.of(2014,1,1));
114 period2.setEndDate(LocalDate.of(2014,12,31));
115 FilterDTO periodFilter = DaliBeanFactory.newFilterDTO();
116 periodFilter.setFilterTypeId(ExtractionFilterTypeValues.PERIOD.getFilterTypeId());
117 periodFilter.setElements(ImmutableList.of(period1, period2));
118 extraction.addFilters(periodFilter);
119
120
121 FilterDTO filter1 = DaliBeanFactory.newFilterDTO();
122 filter1.setFilterTypeId(ExtractionFilterTypeValues.PROGRAM.getFilterTypeId());
123 filter1.setElements(programStrategyService.getWritablePrograms());
124 filter1.setFilterLoaded(true);
125 extraction.addFilters(filter1);
126
127
128 FilterDTO groupingFilter = DaliBeanFactory.newFilterDTO();
129 groupingFilter.setFilterTypeId(ExtractionFilterTypeValues.ORDER_ITEM_TYPE.getFilterTypeId());
130 groupingFilter.setElements(Collections.singletonList(referentialService.getGroupingTypes().get(0)));
131 groupingFilter.setFilterLoaded(true);
132 extraction.addFilters(groupingFilter);
133
134 ExtractionDTO reloadedExtraction = saveAndReload(extraction);
135 assertExtractionEquals(extraction, reloadedExtraction);
136
137
138 period1.setStartDate(LocalDate.of(2015,3,2));
139 period1.setEndDate(LocalDate.of(2015,8,28));
140 reloadedExtraction = saveAndReload(extraction);
141 assertExtractionEquals(extraction, reloadedExtraction);
142
143
144 FilterDTO filter2 = DaliBeanFactory.newFilterDTO();
145 filter2.setFilterTypeId(ExtractionFilterTypeValues.PMFM.getFilterTypeId());
146 filter2.setElements(referentialService.getPmfms(StatusFilter.ACTIVE).subList(2, 5));
147 filter2.setFilterLoaded(true);
148 extraction.addFilters(filter2);
149
150 reloadedExtraction = saveAndReload(extraction);
151 assertExtractionEquals(extraction, reloadedExtraction);
152
153
154 extraction.removeFilters(filter2);
155 reloadedExtraction = saveAndReload(extraction);
156 assertExtractionEquals(extraction, reloadedExtraction);
157
158
159 service.deleteExtractions(Lists.newArrayList(extraction.getId()));
160 }
161
162 @Test
163 public void performExtraction() {
164
165
166 ExtractionDTO extraction = DaliBeanFactory.newExtractionDTO();
167 extraction.setId(-2);
168 extraction.setName("extraction test");
169
170
171 ExtractionPeriodDTO period = DaliBeanFactory.newExtractionPeriodDTO();
172 period.setStartDate(LocalDate.of(2014,1,1));
173 period.setEndDate(LocalDate.of(2016,1,1));
174 FilterDTO periodFilter = DaliBeanFactory.newFilterDTO();
175 periodFilter.setFilterTypeId(ExtractionFilterTypeValues.PERIOD.getFilterTypeId());
176 periodFilter.setElements(Collections.singletonList(period));
177 extraction.addFilters(periodFilter);
178
179
180 FilterDTO groupingFilter = DaliBeanFactory.newFilterDTO();
181 groupingFilter.setFilterTypeId(ExtractionFilterTypeValues.ORDER_ITEM_TYPE.getFilterTypeId());
182 groupingFilter.setElements(Collections.singletonList(referentialService.getGroupingTypes().get(0)));
183 groupingFilter.setFilterLoaded(true);
184 extraction.addFilters(groupingFilter);
185
186
187 FilterDTO filter1 = DaliBeanFactory.newFilterDTO();
188 filter1.setFilterTypeId(ExtractionFilterTypeValues.PROGRAM.getFilterTypeId());
189 filter1.setElements(ImmutableList.of(programStrategyService.getWritableProgramByCode("REMIS")));
190 filter1.setFilterLoaded(true);
191 extraction.addFilters(filter1);
192
193
194 FilterDTO filter2 = DaliBeanFactory.newFilterDTO();
195 filter2.setFilterTypeId(ExtractionFilterTypeValues.LOCATION.getFilterTypeId());
196 filter2.setElements(referentialService.getLocations(StatusFilter.ALL));
197 filter2.setFilterLoaded(true);
198 extraction.addFilters(filter2);
199
200
201 ExtractionParameterDTO parameter = DaliBeanFactory.newExtractionParameterDTO();
202 parameter.setFillZero(true);
203 PmfmPresetDTO preset1 = DaliBeanFactory.newPmfmPresetDTO();
204 preset1.setPmfm(referentialService.getPmfm(4));
205 preset1.addQualitativeValues(referentialService.getQualitativeValue(2));
206 preset1.addQualitativeValues(referentialService.getQualitativeValue(3));
207 PmfmPresetDTO preset2 = DaliBeanFactory.newPmfmPresetDTO();
208 preset2.setPmfm(referentialService.getPmfm(24));
209 preset2.addQualitativeValues(referentialService.getQualitativeValue(4));
210 preset2.addQualitativeValues(referentialService.getQualitativeValue(5));
211 preset2.addQualitativeValues(referentialService.getQualitativeValue(6));
212 parameter.setPmfmPresets(ImmutableList.of(preset1, preset2));
213
214 parameter.setPmfmResults(ImmutableList.of(referentialService.getPmfm(1)));
215
216 extraction.setParameter(parameter);
217
218
219 File outputFile1 = new File(config.getTempDirectory(), "extraction1.csv");
220 performService.performExtraction(extraction, ExtractionOutputType.AGGREGATED_STANDARD, outputFile1, new ProgressionCoreModel());
221 assertCsvFileNbLines(outputFile1, 4);
222
223
224 FilterDTO filter3 = DaliBeanFactory.newFilterDTO();
225 filter3.setFilterTypeId(ExtractionFilterTypeValues.PMFM.getFilterTypeId());
226 filter3.setElements(Lists.<QuadrigeBean>newArrayList(referentialService.getPmfm(21)));
227 filter3.setFilterLoaded(true);
228 extraction.addFilters(filter3);
229
230 File outputFile2 = new File(config.getTempDirectory(), "extraction2.csv");
231 performService.performExtraction(extraction, ExtractionOutputType.AGGREGATED_STANDARD, outputFile2, new ProgressionCoreModel());
232 assertCsvFileNbLines(outputFile2, 3);
233
234
235 FilterDTO filter4 = DaliBeanFactory.newFilterDTO();
236 filter4.setFilterTypeId(ExtractionFilterTypeValues.TAXON_GROUP.getFilterTypeId());
237 filter4.setElements(Lists.<QuadrigeBean>newArrayList(referentialService.getTaxonGroup(1)));
238 filter4.setFilterLoaded(true);
239 extraction.addFilters(filter4);
240
241 File outputFile3 = new File(config.getTempDirectory(), "extraction3.csv");
242 performService.performExtraction(extraction, ExtractionOutputType.AGGREGATED_STANDARD, outputFile3, new ProgressionCoreModel());
243 assertCsvFileNbLines(outputFile3, 3);
244
245
246 FilterDTO filter5 = DaliBeanFactory.newFilterDTO();
247 filter5.setFilterTypeId(ExtractionFilterTypeValues.TAXON.getFilterTypeId());
248 filter5.setElements(Lists.<QuadrigeBean>newArrayList(referentialService.getTaxon(1)));
249 filter5.setFilterLoaded(true);
250 extraction.addFilters(filter5);
251
252 File outputFile4 = new File(config.getTempDirectory(), "extraction4.csv");
253 try {
254 performService.performExtraction(extraction, ExtractionOutputType.AGGREGATED_STANDARD, outputFile4, new ProgressionCoreModel());
255 fail("should throw exception");
256 } catch (Exception e) {
257 assertNotNull(e);
258 }
259 }
260
261
262
263
264 @Ignore
265 @Test
266 public void performExtractionNew() {
267
268
269 ExtractionDTO extraction = DaliBeanFactory.newExtractionDTO();
270 extraction.setId(-3);
271 extraction.setName("extraction test (par xml)");
272
273
274 ExtractionPeriodDTO period1 = DaliBeanFactory.newExtractionPeriodDTO();
275 period1.setStartDate(LocalDate.of(2016,1,1));
276 period1.setEndDate(LocalDate.of(2020,12,31));
277 ExtractionPeriodDTO period2 = DaliBeanFactory.newExtractionPeriodDTO();
278 period2.setStartDate(LocalDate.of(2000,1,1));
279 period2.setEndDate(LocalDate.of(2010,12,31));
280 FilterDTO periodFilter = DaliBeanFactory.newFilterDTO();
281 periodFilter.setFilterTypeId(ExtractionFilterTypeValues.PERIOD.getFilterTypeId());
282 periodFilter.setElements(ImmutableList.of(period1, period2));
283 extraction.addFilters(periodFilter);
284
285
286 FilterDTO groupingFilter = DaliBeanFactory.newFilterDTO();
287 groupingFilter.setFilterTypeId(ExtractionFilterTypeValues.ORDER_ITEM_TYPE.getFilterTypeId());
288 groupingFilter.setElements(Collections.singletonList(referentialService.getGroupingTypes().get(0)));
289 groupingFilter.setFilterLoaded(true);
290 extraction.addFilters(groupingFilter);
291
292
293 FilterDTO filter1 = DaliBeanFactory.newFilterDTO();
294 filter1.setFilterTypeId(ExtractionFilterTypeValues.PROGRAM.getFilterTypeId());
295 filter1.setElements(ImmutableList.of(programStrategyService.getWritableProgramByCode("DECHETS_PLAGES")));
296 filter1.setFilterLoaded(true);
297 extraction.addFilters(filter1);
298
299
300
301
302
303
304
305
306
307 ExtractionParameterDTO parameter = DaliBeanFactory.newExtractionParameterDTO();
308 parameter.setFillZero(false);
309 PmfmPresetDTO preset1 = DaliBeanFactory.newPmfmPresetDTO();
310 preset1.setPmfm(referentialService.getPmfm(13314));
311 preset1.setQualitativeValues(ImmutableList.of(
312 referentialService.getQualitativeValue(60001599),
313 referentialService.getQualitativeValue(60001601),
314 referentialService.getQualitativeValue(60001602),
315 referentialService.getQualitativeValue(60001603),
316 referentialService.getQualitativeValue(60001610),
317 referentialService.getQualitativeValue(60001611),
318 referentialService.getQualitativeValue(60001618),
319 referentialService.getQualitativeValue(60001620),
320 referentialService.getQualitativeValue(60001623)
321 ));
322 PmfmPresetDTO preset2 = DaliBeanFactory.newPmfmPresetDTO();
323 preset2.setPmfm(referentialService.getPmfm(13322));
324 preset2.setQualitativeValues(ImmutableList.of(
325 referentialService.getQualitativeValue(60001580),
326 referentialService.getQualitativeValue(60001586),
327 referentialService.getQualitativeValue(60001589)
328 ));
329 List<PmfmPresetDTO> pmfmPresets = ImmutableList.of(preset1, preset2);
330 parameter.setPmfmPresets(pmfmPresets);
331
332 parameter.setPmfmResults(ImmutableList.of(referentialService.getPmfm(13171)));
333
334 extraction.setParameter(parameter);
335
336
337 File outputFile1 = new File("target", "extractionViaXML-" + System.currentTimeMillis() + ".csv");
338 performService.performExtraction(extraction, ExtractionOutputType.AGGREGATED_COMPLETE, outputFile1, new ProgressionCoreModel());
339
340 }
341
342 @Test
343 public void countDataWithXMLQuery() {
344
345 xmlQuery.setQuery(getXMLQueryFile("countFrom"));
346 xmlQuery.bind("tableName", "SURVEY");
347
348 xmlQuery.addWhere(getXMLQueryFile("testWhere"));
349 xmlQuery.bind("condition", "1=1");
350
351 String query = xmlQuery.getSQLQueryAsString();
352
353 Long count = resultDao.queryCount(query, null);
354
355 Assert.assertNotNull(count);
356
357 System.out.println("result count = " + count);
358
359 }
360
361 private File getXMLQueryFile(String queryName) {
362
363 URL fileURL = getClass().getClassLoader().getResource("xmlQuery/extraction/" + queryName + ".xml");
364 try {
365 return new File(Objects.requireNonNull(fileURL).toURI());
366 } catch (URISyntaxException | NullPointerException e) {
367 throw new DaliTechnicalException(String.format("query '%s' not found in resources", queryName));
368 }
369
370 }
371
372 private void assertCsvFileNbLines(File csvFile, int expectedNbLines) {
373
374 assertTrue(csvFile.canRead());
375 try {
376 BufferedReader reader = Files.newBufferedReader(csvFile.toPath(), StandardCharsets.UTF_8);
377 int nbLines = 0;
378 String line = readLine(reader);
379 while (line != null) {
380 nbLines++;
381 line = readLine(reader);
382 }
383 assertEquals(expectedNbLines, nbLines);
384
385 } catch (IOException e) {
386 fail(e.getLocalizedMessage());
387 }
388 }
389
390 private String readLine(BufferedReader reader) throws IOException {
391 String line = reader.readLine();
392 if (LOG.isDebugEnabled()) {
393 LOG.debug(line);
394 }
395 return line;
396 }
397
398 private ExtractionDTO saveAndReload(ExtractionDTO extraction) {
399 extraction.setDirty(true);
400 service.saveExtractions(Lists.newArrayList(extraction));
401
402 assertNotNull(extraction.getId());
403 assertFalse(extraction.isDirty());
404
405
406 List<ExtractionDTO> extractions = service.getExtractions(extraction.getId(), null);
407
408 assertNotNull(extractions);
409 assertEquals(1, extractions.size());
410
411 ExtractionDTO reloadedExtraction = extractions.get(0);
412 service.loadFilteredElements(reloadedExtraction);
413
414 return reloadedExtraction;
415 }
416
417 private void assertExtractionEquals(ExtractionDTO expectedExtraction, ExtractionDTO extractionToTest) {
418 assertNotNull(expectedExtraction);
419 assertNotNull(extractionToTest);
420 assertEquals(expectedExtraction.getId(), extractionToTest.getId());
421 assertEquals(expectedExtraction.getName(), extractionToTest.getName());
422 assertEquals(expectedExtraction.getUser(), extractionToTest.getUser());
423 assertEquals(expectedExtraction.isDirty(), extractionToTest.isDirty());
424 assertFiltersEquals(expectedExtraction.getFilters(), extractionToTest.getFilters());
425 }
426
427 private void assertFiltersEquals(Collection<FilterDTO> expectedFilters, Collection<FilterDTO> filtersToTest) {
428 if (CollectionUtils.isEmpty(expectedFilters)) {
429 assertTrue(CollectionUtils.isEmpty(filtersToTest));
430 return;
431 } else {
432 assertTrue(CollectionUtils.isNotEmpty(filtersToTest));
433 assertEquals(expectedFilters.size(), filtersToTest.size());
434 }
435
436 Map<Integer, FilterDTO> filtersToTestMap = DaliBeans.mapByProperty(filtersToTest, FilterDTO.PROPERTY_FILTER_TYPE_ID);
437 for (FilterDTO expectedFilter : expectedFilters) {
438 FilterDTO filterToTest = filtersToTestMap.get(expectedFilter.getFilterTypeId());
439 assertNotNull(filterToTest);
440 assertEquals(expectedFilter.getFilterTypeId(), filterToTest.getFilterTypeId());
441 assertEquals(expectedFilter.getName(), filterToTest.getName());
442
443 if (CollectionUtils.isEmpty(expectedFilter.getElements())) {
444 assertTrue(CollectionUtils.isEmpty(filterToTest.getElements()));
445 } else {
446 assertTrue(CollectionUtils.isNotEmpty(filterToTest.getElements()));
447 assertEquals(expectedFilter.getElements().size(), filterToTest.getElements().size());
448
449 QuadrigeBean[] expectedElements = expectedFilter.getElements().toArray(new QuadrigeBean[0]);
450 QuadrigeBean[] elementsToTest = filterToTest.getElements().toArray(new QuadrigeBean[0]);
451 Arrays.sort(expectedElements);
452 Arrays.sort(elementsToTest);
453 assertArrayEquals(expectedElements, elementsToTest);
454 }
455 }
456 }
457
458 }