1
2
3
4 package nl.b3p;
5
6 import static org.junit.jupiter.api.Assertions.assertEquals;
7 import static org.junit.jupiter.api.Assertions.assertNotNull;
8 import static org.junit.jupiter.api.Assertions.fail;
9 import static org.junit.jupiter.params.provider.Arguments.arguments;
10
11 import java.io.File;
12 import java.io.FileInputStream;
13 import java.util.List;
14 import java.util.concurrent.locks.Lock;
15 import java.util.concurrent.locks.ReentrantLock;
16 import java.util.stream.Stream;
17 import nl.b3p.brmo.loader.BrmoFramework;
18 import nl.b3p.brmo.loader.entity.Bericht;
19 import nl.b3p.brmo.loader.entity.LaadProces;
20 import nl.b3p.brmo.test.util.database.dbunit.CleanUtil;
21 import nl.b3p.jdbc.util.converter.OracleConnectionUnwrapper;
22 import org.apache.commons.dbcp2.BasicDataSource;
23 import org.apache.commons.logging.Log;
24 import org.apache.commons.logging.LogFactory;
25 import org.dbunit.database.DatabaseConfig;
26 import org.dbunit.database.DatabaseConnection;
27 import org.dbunit.database.DatabaseDataSourceConnection;
28 import org.dbunit.database.IDatabaseConnection;
29 import org.dbunit.dataset.IDataSet;
30 import org.dbunit.dataset.ITable;
31 import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
32 import org.dbunit.ext.oracle.Oracle10DataTypeFactory;
33 import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
34 import org.dbunit.operation.DatabaseOperation;
35 import org.junit.jupiter.api.AfterEach;
36 import org.junit.jupiter.api.Assumptions;
37 import org.junit.jupiter.api.BeforeEach;
38 import org.junit.jupiter.api.DisplayName;
39 import org.junit.jupiter.params.ParameterizedTest;
40 import org.junit.jupiter.params.provider.Arguments;
41 import org.junit.jupiter.params.provider.MethodSource;
42
43
44
45
46
47
48
49
50
51 public class NhrToStagingToRsgbIntegrationTest extends AbstractDatabaseIntegrationTest {
52
53 private static final Log LOG = LogFactory.getLog(NhrToStagingToRsgbIntegrationTest.class);
54
55 static Stream<Arguments> argumentsProvider() {
56 return Stream.of(
57
58
59
60
61
62
63 arguments(
64 "/mantis10752/52019667.xml",
65 2,
66 1,
67 2,
68 3,
69 1,
70 1,
71 "nhr.comVestg.000021991235",
72 1,
73 52019667,
74 new String[] {"86912"},
75 0),
76
77 arguments(
78 "/nhr-v3/52019667.anon.xml",
79 2,
80 1,
81 2,
82 3,
83 1,
84 1,
85 "nhr.comVestg.000021991235",
86 1,
87 52019667,
88 new String[] {"86912"},
89 0),
90
91 arguments(
92 "/nhr-v3/16029104.anon.xml",
93 3,
94 1,
95 2 + 6,
96 3 + 6,
97 2,
98 0 + 6,
99 "nhr.comVestg.000002706229",
100 1,
101 16029104,
102 new String[] {"7500"},
103 6),
104
105 arguments(
106 "/nhr-v3/34122633,32076598.anon.xml",
107 3,
108 1,
109 2 + 1,
110 3 + 1,
111 2 + 1,
112 0,
113 "nhr.comVestg.000019315708",
114 1,
115 34122633,
116 new String[] {"6202"},
117 1),
118
119 arguments(
120 "/nhr-v3/40480283.anon.xml",
121 2,
122 1,
123 2 + 4,
124 2 + 4,
125 2,
126 0 + 4,
127 null,
128 0,
129 40480283,
130 new String[] {"93152"},
131 4),
132
133 arguments(
134 "/nhr-v3/41177576.anon.xml",
135 2,
136 1,
137 2 + 12,
138 2 + 12,
139 2,
140 0 + 12,
141 null,
142 0,
143 41177576,
144 new String[] {"91042", "0161", "0150"},
145 12),
146
147 arguments(
148 "/nhr-v3/32122905.anon.xml",
149 2,
150 1,
151 2 + 4,
152 2 + 4,
153 2,
154 0 + 4,
155 null,
156 0,
157 32122905,
158 new String[] {"7112"},
159 4),
160
161 arguments(
162 "/nhr-v3/52813150.anon.xml",
163 1 + 1 + 1 + 7 ,
164 1,
165 2,
166 3 + 7 ,
167 2,
168 0,
169 "nhr.nietComVestg.000022724362",
170 1 + 7,
171 52813150,
172 new String[] {"8411", "8411", "8411", "8411", "8411", "8411", "8411", "8411"},
173 0),
174
175 arguments(
176 "/nhr-v3/30263544.anon.xml",
177 1 + 1 + 1 + 13 ,
178 1,
179 2,
180 3 + 13 ,
181 2,
182 0,
183 "nhr.comVestg.000012461547",
184 1 + 13,
185 30263544,
186 new String[] {"91042"},
187 0),
188
189 arguments(
190 "/nhr-v3/33257455,23052007.anon.xml",
191 1 + 1 + 1 + 8 ,
192 1,
193 12,
194 13 + 8 ,
195 3,
196 9,
197 "nhr.comVestg.000019483104",
198 45,
199 33257455,
200 new String[] {
201 "8010", "4652", "85592", "6202", "4321", "8010", "4652", "85592", "6202", "4321",
202 "8010", "4652", "85592", "6202", "4321", "8010", "4652", "85592", "6202", "4321",
203 "8010", "4652", "85592", "6202", "4321", "8010", "85592", "4652", "6202", "4321",
204 "8010", "4652", "85592", "6202", "4321", "8010", "4652", "85592", "6202", "4321",
205 "8010", "4652", "85592", "6202", "4321"
206 },
207 10),
208
209 arguments(
210 "/nhr-v3/maatschact_br_origineel.anon.xml",
211
212 2,
213
214 1,
215
216 2,
217
218 1 + 1 + 1,
219
220 1,
221
222 1,
223
224 "nhr.comVestg.000002137054",
225
226 2,
227
228 39056023,
229
230 new String[] {"46699", "69203"},
231
232 0),
233 arguments(
234 "/nhr-v3/rechtspersoon_br_origineel.anon.xml",
235
236 28,
237
238 1,
239
240 6,
241
242 32,
243
244 3,
245
246 3,
247
248 "nhr.comVestg.000016037251",
249
250 28,
251
252 29048285,
253
254 new String[] {"88101"},
255
256 4),
257 arguments(
258 "/nhr-v3/samenwvb_br_origineel.anon.xml",
259
260 3,
261
262 1,
263
264 4,
265
266 5,
267
268 3,
269
270 1,
271
272 "nhr.comVestg.000019909101",
273
274 1,
275
276 24456672,
277
278 new String[] {"86221"},
279
280 2),
281
282
283 arguments(
284
285 "/nhr-v3/289535.anon.xml",
286
287 3,
288
289 1,
290
291 3,
292
293 4,
294
295 3,
296
297 0,
298
299 "nhr.comVestg.000046583130",
300
301 5,
302
303 80216269,
304
305 new String[] {"47919", "46499", "47722", "47712", "47721"},
306
307 1),
308 arguments(
309
310 "/nhr-v3/289538.anon.xml",
311
312 3,
313
314 1,
315
316 4,
317
318 5,
319
320 3,
321
322 1,
323
324 "nhr.comVestg.000032230524",
325
326 1,
327
328 63300486,
329
330 new String[] {"46384"},
331
332 2),
333 arguments(
334
335 "/nhr-v3/289541.anon.xml",
336
337 3,
338
339 1,
340
341 4,
342
343 5,
344
345 3,
346
347 1,
348
349 "nhr.comVestg.000018129943",
350
351 1,
352
353 39052953,
354
355 new String[] {"4662"},
356
357 2),
358 arguments(
359
360 "/nhr-v3/289544.anon.xml",
361
362 3,
363
364 1,
365
366 6,
367
368 7,
369
370 3,
371
372 3,
373
374 "nhr.comVestg.000058716262",
375
376 3,
377
378 93152825,
379
380 new String[] {"7112", "4120", "2511"},
381
382 4),
383 arguments(
384 "/nhr-v3/289547.anon.xml",
385
386 3,
387
388 1,
389
390 2,
391
392 3,
393
394 2,
395
396 0,
397
398 "nhr.comVestg.000062377655",
399
400 1,
401
402 97113131,
403
404 new String[] {"4637"},
405
406 0)
407
408 );
409 }
410
411 private static final String BESTANDTYPE = "nhr";
412 private BrmoFramework brmo;
413
414 private IDatabaseConnection staging;
415 private IDatabaseConnection rsgb;
416 private final Lock sequential = new ReentrantLock(true);
417 private BasicDataSource dsRsgb;
418 private BasicDataSource dsStaging;
419
420 @BeforeEach
421 @Override
422 public void setUp() throws Exception {
423 dsStaging = new BasicDataSource();
424 dsStaging.setUrl(params.getProperty("staging.jdbc.url"));
425 dsStaging.setUsername(params.getProperty("staging.user"));
426 dsStaging.setPassword(params.getProperty("staging.passwd"));
427 dsStaging.setAccessToUnderlyingConnectionAllowed(true);
428 dsStaging.setConnectionProperties(params.getProperty("staging.options", ""));
429
430 dsRsgb = new BasicDataSource();
431 dsRsgb.setUrl(params.getProperty("rsgb.jdbc.url"));
432 dsRsgb.setUsername(params.getProperty("rsgb.user"));
433 dsRsgb.setPassword(params.getProperty("rsgb.passwd"));
434 dsRsgb.setAccessToUnderlyingConnectionAllowed(true);
435 dsRsgb.setConnectionProperties(params.getProperty("rsgb.options", ""));
436
437 staging = new DatabaseDataSourceConnection(dsStaging);
438 rsgb = new DatabaseDataSourceConnection(dsRsgb, params.getProperty("rsgb.schema"));
439
440 if (this.isOracle) {
441 staging =
442 new DatabaseConnection(
443 OracleConnectionUnwrapper.unwrap(dsStaging.getConnection()),
444 params.getProperty("staging.user").toUpperCase());
445 staging
446 .getConfig()
447 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
448 staging.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
449
450 rsgb =
451 new DatabaseConnection(
452 OracleConnectionUnwrapper.unwrap(dsRsgb.getConnection()),
453 params.getProperty("rsgb.user").toUpperCase());
454 rsgb.getConfig()
455 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
456 rsgb.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
457 } else if (this.isPostgis) {
458
459 staging
460 .getConfig()
461 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
462 rsgb.getConfig()
463 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
464 }
465
466 brmo = new BrmoFramework(dsStaging, dsRsgb, null);
467 brmo.setOrderBerichten(true);
468
469 FlatXmlDataSetBuilder fxdb = new FlatXmlDataSetBuilder();
470 fxdb.setCaseSensitiveTableNames(false);
471 IDataSet stagingDataSet =
472 fxdb.build(
473 new FileInputStream(
474 new File(
475 NhrToStagingToRsgbIntegrationTest.class
476 .getResource("/staging-empty-flat.xml")
477 .toURI())));
478
479 sequential.lock();
480
481 DatabaseOperation.CLEAN_INSERT.execute(staging, stagingDataSet);
482
483 Assumptions.assumeTrue(
484 0L == brmo.getCountBerichten("nhr", "STAGING_OK"), "Er zijn geen STAGING_OK berichten");
485 Assumptions.assumeTrue(
486 0L == brmo.getCountLaadProcessen("nhr", "STAGING_OK"),
487 "Er zijn geen STAGING_OK laadprocessen");
488 }
489
490 @AfterEach
491 public void cleanup() throws Exception {
492 brmo.closeBrmoFramework();
493
494 CleanUtil.cleanSTAGING(staging, false);
495 CleanUtil.cleanRSGB_NHR(rsgb);
496 staging.close();
497 dsStaging.close();
498 rsgb.close();
499 dsRsgb.close();
500
501 sequential.unlock();
502 }
503
504 @DisplayName("NHR to STAGING to RSGB")
505 @ParameterizedTest(name = "case #{index}: bestand: {0}")
506 @MethodSource("argumentsProvider")
507 public void testNhrXMLToStagingToRsgb(
508 String bestandNaam,
509 long aantalBerichten,
510 long aantalProcessen,
511 long aantalPrs,
512 long aantalSubj,
513 long aantalNiet_nat_prs,
514 long aantalNat_prs,
515 String vestgID,
516 long aantalVestg_activiteit,
517 long kvkNummer,
518 String[] sbiCodes,
519 int aantalFunctionarissen)
520 throws Exception {
521
522
523
524
525 brmo.loadFromFile(
526 BESTANDTYPE,
527 NhrToStagingToRsgbIntegrationTest.class.getResource(bestandNaam).getFile(),
528 null);
529 LOG.info("klaar met laden van berichten in staging DB.");
530
531 List<Bericht> berichten = brmo.listBerichten();
532 List<LaadProces> processen = brmo.listLaadProcessen();
533 assertNotNull(berichten, "De verzameling berichten bestaat niet.");
534 assertEquals(aantalBerichten, berichten.size(), "Het aantal berichten is niet als verwacht.");
535 assertNotNull(processen, "De verzameling processen bestaat niet.");
536 assertEquals(aantalProcessen, processen.size(), "Het aantal processen is niet als verwacht.");
537
538
539 ITable bericht =
540 staging.createQueryTable("bericht", "select * from bericht where volgordenummer=0");
541 assertEquals(1, bericht.getRowCount(), "Er zijn meer of minder dan 1 rij");
542 LOG.debug("\n\n" + bericht.getValue(0, "br_orgineel_xml") + "\n\n");
543 assertNotNull(bericht.getValue(0, "br_orgineel_xml"), "BR origineel xml is null");
544 Object berichtId = bericht.getValue(0, "id");
545
546 LOG.info("Transformeren berichten naar rsgb DB.");
547 Thread t = brmo.toRsgb();
548 t.join();
549
550
551 bericht =
552 staging.createQueryTable(
553 "bericht", "select * from bericht where br_orgineel_xml is not null");
554 assertEquals(1, bericht.getRowCount(), "Er zijn meer of minder dan 1 rij");
555 assertNotNull(
556 bericht.getValue(0, "br_orgineel_xml"), "BR origineel xml is null na transformatie");
557 assertEquals(
558 berichtId,
559 bericht.getValue(0, "id"),
560 "bericht met br_orgineel_xml moet hetzelfde id hebben na transformatie");
561
562 assertEquals(
563 aantalBerichten,
564 brmo.getCountBerichten("nhr", "RSGB_OK"),
565 "Niet alle berichten zijn OK getransformeerd");
566 berichten = brmo.listBerichten();
567 for (Bericht b : berichten) {
568 assertNotNull(b, "Bericht is 'null'");
569 assertNotNull(b.getDbXml(), "'db-xml' van bericht is 'null'");
570 }
571
572 ITable prs = rsgb.createDataSet().getTable("prs");
573 ITable niet_nat_prs = rsgb.createDataSet().getTable("niet_nat_prs");
574 ITable nat_prs = rsgb.createDataSet().getTable("nat_prs");
575 ITable subject = rsgb.createDataSet().getTable("subject");
576
577 assertEquals(aantalPrs, prs.getRowCount(), "Het aantal 'prs' records klopt niet");
578 assertEquals(
579 aantalNiet_nat_prs,
580 niet_nat_prs.getRowCount(),
581 "Het aantal 'niet_nat_prs' records klopt niet");
582 assertEquals(aantalNat_prs, nat_prs.getRowCount(), "Het aantal 'nat_prs' records klopt niet");
583 assertEquals(aantalSubj, subject.getRowCount(), "het aantal 'subject' records klopt niet");
584
585 boolean foundKvk = false;
586 for (int i = 0; i < subject.getRowCount(); i++) {
587 if (subject.getValue(i, "identif").toString().contains("nhr.maatschAct.kvk")) {
588 assertEquals(
589 kvkNummer + "", subject.getValue(i, "kvk_nummer") + "", "KVK nummer klopt niet");
590 foundKvk = true;
591 }
592 }
593 if (!foundKvk) {
594 fail("KVK nummer maatschappelijke activiteit klopt niet, verwacht te vinden: " + kvkNummer);
595 }
596
597 if (vestgID != null) {
598 ITable vestg = rsgb.createDataSet().getTable("vestg");
599 assertEquals(
600 vestgID,
601 vestg.getValue(0, "sc_identif"),
602 "De 'sc_identif' van hoofdvestiging klopt niet");
603 assertEquals(
604 sbiCodes[0],
605 vestg.getValue(0, "fk_sa_sbi_activiteit_sbi_code"),
606 "De sbi code van (hoofd)vestiging klopt niet");
607 assertEquals(
608 "Ja",
609 vestg.getValue(0, "sa_indic_hoofdactiviteit"),
610 "De 'sa_indic_hoofdactiviteit' van (hoofd)vestiging klopt niet");
611 }
612
613
614
615 ITable vestg_activiteit = rsgb.createDataSet().getTable("vestg_activiteit");
616 assertEquals(
617 aantalVestg_activiteit,
618 vestg_activiteit.getRowCount(),
619 "Het aantal 'vestg_activiteit' records klopt niet");
620
621 ITable maatschapp_activiteit = rsgb.createDataSet().getTable("maatschapp_activiteit");
622
623 assertNotNull(
624 maatschapp_activiteit.getValue(0, "fk_4pes_sc_identif"),
625 "Geen maatschappelijke activiteit -> persoon koppeling");
626
627 ITable sbi_activiteit = rsgb.createDataSet().getTable("sbi_activiteit");
628 ITable herkomst_metadata = rsgb.createDataSet().getTable("herkomst_metadata");
629 for (String sbiCode : sbiCodes) {
630 boolean foundSbiCode = false;
631 boolean foundSbiCodeMeta = false;
632 for (int i = 0; i < sbi_activiteit.getRowCount(); i++) {
633 if (sbiCode.equals(sbi_activiteit.getValue(i, "sbi_code").toString())) {
634 LOG.debug("SBI code " + sbiCode + " gevonden in sbi_activiteit");
635 foundSbiCode = true;
636 break;
637 }
638 }
639 if (!foundSbiCode) {
640 fail("SBI code niet gevonden in sbi_activiteit, verwacht te vinden: " + sbiCode);
641 }
642 for (int i = 0; i < herkomst_metadata.getRowCount(); i++) {
643 if (sbiCode.equals(herkomst_metadata.getValue(i, "waarde").toString())) {
644 LOG.debug("SBI code " + sbiCode + " gevonden in herkomst_metadata");
645 foundSbiCodeMeta = true;
646 break;
647 }
648 }
649 if (!foundSbiCodeMeta) {
650 fail("SBI code niet gevonden in herkomst_metadata, verwacht te vinden: " + sbiCode);
651 }
652 }
653
654 ITable functionaris = rsgb.createDataSet().getTable("functionaris");
655 assertEquals(
656 aantalFunctionarissen,
657 functionaris.getRowCount(),
658 "Het aantal 'functionaris' records klopt niet");
659 if (kvkNummer == 41177576) {
660
661
662
663 functionaris =
664 rsgb.createQueryTable("functionaris", "select * from functionaris order by functie ASC");
665 assertEquals("Gevolmachtigde", functionaris.getValue(aantalFunctionarissen - 1, "functie"));
666 assertEquals(
667 "Directeur", functionaris.getValue(aantalFunctionarissen - 1, "functionaristypering"));
668 assertEquals(
669 "B", functionaris.getValue(aantalFunctionarissen - 1, "volledig_beperkt_volmacht"));
670 }
671 }
672 }