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 private static final String BESTANDTYPE = "nhr";
284 private BrmoFramework brmo;
285
286 private IDatabaseConnection staging;
287 private IDatabaseConnection rsgb;
288 private final Lock sequential = new ReentrantLock(true);
289 private BasicDataSource dsRsgb;
290 private BasicDataSource dsStaging;
291
292 @BeforeEach
293 @Override
294 public void setUp() throws Exception {
295 dsStaging = new BasicDataSource();
296 dsStaging.setUrl(params.getProperty("staging.jdbc.url"));
297 dsStaging.setUsername(params.getProperty("staging.user"));
298 dsStaging.setPassword(params.getProperty("staging.passwd"));
299 dsStaging.setAccessToUnderlyingConnectionAllowed(true);
300 dsStaging.setConnectionProperties(params.getProperty("staging.options", ""));
301
302 dsRsgb = new BasicDataSource();
303 dsRsgb.setUrl(params.getProperty("rsgb.jdbc.url"));
304 dsRsgb.setUsername(params.getProperty("rsgb.user"));
305 dsRsgb.setPassword(params.getProperty("rsgb.passwd"));
306 dsRsgb.setAccessToUnderlyingConnectionAllowed(true);
307 dsRsgb.setConnectionProperties(params.getProperty("rsgb.options", ""));
308
309 staging = new DatabaseDataSourceConnection(dsStaging);
310 rsgb = new DatabaseDataSourceConnection(dsRsgb, params.getProperty("rsgb.schema"));
311
312 if (this.isOracle) {
313 staging =
314 new DatabaseConnection(
315 OracleConnectionUnwrapper.unwrap(dsStaging.getConnection()),
316 params.getProperty("staging.user").toUpperCase());
317 staging
318 .getConfig()
319 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
320 staging.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
321
322 rsgb =
323 new DatabaseConnection(
324 OracleConnectionUnwrapper.unwrap(dsRsgb.getConnection()),
325 params.getProperty("rsgb.user").toUpperCase());
326 rsgb.getConfig()
327 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
328 rsgb.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
329 } else if (this.isPostgis) {
330
331 staging
332 .getConfig()
333 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
334 rsgb.getConfig()
335 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
336 }
337
338 brmo = new BrmoFramework(dsStaging, dsRsgb, null);
339 brmo.setOrderBerichten(true);
340
341 FlatXmlDataSetBuilder fxdb = new FlatXmlDataSetBuilder();
342 fxdb.setCaseSensitiveTableNames(false);
343 IDataSet stagingDataSet =
344 fxdb.build(
345 new FileInputStream(
346 new File(
347 NhrToStagingToRsgbIntegrationTest.class
348 .getResource("/staging-empty-flat.xml")
349 .toURI())));
350
351 sequential.lock();
352
353 DatabaseOperation.CLEAN_INSERT.execute(staging, stagingDataSet);
354
355 Assumptions.assumeTrue(
356 0L == brmo.getCountBerichten("nhr", "STAGING_OK"), "Er zijn geen STAGING_OK berichten");
357 Assumptions.assumeTrue(
358 0L == brmo.getCountLaadProcessen("nhr", "STAGING_OK"),
359 "Er zijn geen STAGING_OK laadprocessen");
360 }
361
362 @AfterEach
363 public void cleanup() throws Exception {
364 brmo.closeBrmoFramework();
365
366 CleanUtil.cleanSTAGING(staging, false);
367 staging.close();
368 dsStaging.close();
369
370 CleanUtil.cleanRSGB_NHR(rsgb);
371 rsgb.close();
372 dsRsgb.close();
373
374 sequential.unlock();
375 }
376
377 @DisplayName("NHR to STAGING to RSGB")
378 @ParameterizedTest(name = "case #{index}: bestand: {0}")
379 @MethodSource("argumentsProvider")
380 public void testNhrXMLToStagingToRsgb(
381 String bestandNaam,
382 long aantalBerichten,
383 long aantalProcessen,
384 long aantalPrs,
385 long aantalSubj,
386 long aantalNiet_nat_prs,
387 long aantalNat_prs,
388 String vestgID,
389 long aantalVestg_activiteit,
390 long kvkNummer,
391 String[] sbiCodes,
392 int aantalFunctionarissen)
393 throws Exception {
394
395
396
397
398 brmo.loadFromFile(
399 BESTANDTYPE,
400 NhrToStagingToRsgbIntegrationTest.class.getResource(bestandNaam).getFile(),
401 null);
402 LOG.info("klaar met laden van berichten in staging DB.");
403
404 List<Bericht> berichten = brmo.listBerichten();
405 List<LaadProces> processen = brmo.listLaadProcessen();
406 assertNotNull(berichten, "De verzameling berichten bestaat niet.");
407 assertEquals(aantalBerichten, berichten.size(), "Het aantal berichten is niet als verwacht.");
408 assertNotNull(processen, "De verzameling processen bestaat niet.");
409 assertEquals(aantalProcessen, processen.size(), "Het aantal processen is niet als verwacht.");
410
411
412 ITable bericht =
413 staging.createQueryTable("bericht", "select * from bericht where volgordenummer=0");
414 assertEquals(1, bericht.getRowCount(), "Er zijn meer of minder dan 1 rij");
415 LOG.debug("\n\n" + bericht.getValue(0, "br_orgineel_xml") + "\n\n");
416 assertNotNull(bericht.getValue(0, "br_orgineel_xml"), "BR origineel xml is null");
417 Object berichtId = bericht.getValue(0, "id");
418
419 LOG.info("Transformeren berichten naar rsgb DB.");
420 Thread t = brmo.toRsgb();
421 t.join();
422
423
424 bericht =
425 staging.createQueryTable(
426 "bericht", "select * from bericht where br_orgineel_xml is not null");
427 assertEquals(1, bericht.getRowCount(), "Er zijn meer of minder dan 1 rij");
428 assertNotNull(
429 bericht.getValue(0, "br_orgineel_xml"), "BR origineel xml is null na transformatie");
430 assertEquals(
431 berichtId,
432 bericht.getValue(0, "id"),
433 "bericht met br_orgineel_xml moet hetzelfde id hebben na transformatie");
434
435 assertEquals(
436 aantalBerichten,
437 brmo.getCountBerichten("nhr", "RSGB_OK"),
438 "Niet alle berichten zijn OK getransformeerd");
439 berichten = brmo.listBerichten();
440 for (Bericht b : berichten) {
441 assertNotNull(b, "Bericht is 'null'");
442 assertNotNull(b.getDbXml(), "'db-xml' van bericht is 'null'");
443 }
444
445 ITable prs = rsgb.createDataSet().getTable("prs");
446 ITable niet_nat_prs = rsgb.createDataSet().getTable("niet_nat_prs");
447 ITable nat_prs = rsgb.createDataSet().getTable("nat_prs");
448 ITable subject = rsgb.createDataSet().getTable("subject");
449
450 assertEquals(aantalPrs, prs.getRowCount(), "Het aantal 'prs' records klopt niet");
451 assertEquals(
452 aantalNiet_nat_prs,
453 niet_nat_prs.getRowCount(),
454 "Het aantal 'niet_nat_prs' records klopt niet");
455 assertEquals(aantalNat_prs, nat_prs.getRowCount(), "Het aantal 'nat_prs' records klopt niet");
456 assertEquals(aantalSubj, subject.getRowCount(), "het aantal 'subject' records klopt niet");
457
458 boolean foundKvk = false;
459 for (int i = 0; i < subject.getRowCount(); i++) {
460 if (subject.getValue(i, "identif").toString().contains("nhr.maatschAct.kvk")) {
461 assertEquals(
462 kvkNummer + "", subject.getValue(i, "kvk_nummer") + "", "KVK nummer klopt niet");
463 foundKvk = true;
464 }
465 }
466 if (!foundKvk) {
467 fail("KVK nummer maatschappelijke activiteit klopt niet, verwacht te vinden: " + kvkNummer);
468 }
469
470 if (vestgID != null) {
471 ITable vestg = rsgb.createDataSet().getTable("vestg");
472 assertEquals(
473 vestgID,
474 vestg.getValue(0, "sc_identif"),
475 "De 'sc_identif' van hoofdvestiging klopt niet");
476 assertEquals(
477 sbiCodes[0],
478 vestg.getValue(0, "fk_sa_sbi_activiteit_sbi_code"),
479 "De sbi code van (hoofd)vestiging klopt niet");
480 assertEquals(
481 "Ja",
482 vestg.getValue(0, "sa_indic_hoofdactiviteit"),
483 "De 'sa_indic_hoofdactiviteit' van (hoofd)vestiging klopt niet");
484 }
485
486
487
488 ITable vestg_activiteit = rsgb.createDataSet().getTable("vestg_activiteit");
489 assertEquals(
490 aantalVestg_activiteit,
491 vestg_activiteit.getRowCount(),
492 "Het aantal 'vestg_activiteit' records klopt niet");
493
494 ITable maatschapp_activiteit = rsgb.createDataSet().getTable("maatschapp_activiteit");
495
496 assertNotNull(
497 maatschapp_activiteit.getValue(0, "fk_4pes_sc_identif"),
498 "Geen maatschappelijke activiteit -> persoon koppeling");
499
500 ITable sbi_activiteit = rsgb.createDataSet().getTable("sbi_activiteit");
501 ITable herkomst_metadata = rsgb.createDataSet().getTable("herkomst_metadata");
502 for (String sbiCode : sbiCodes) {
503 boolean foundSbiCode = false;
504 boolean foundSbiCodeMeta = false;
505 for (int i = 0; i < sbi_activiteit.getRowCount(); i++) {
506 if (sbiCode.equals(sbi_activiteit.getValue(i, "sbi_code").toString())) {
507 LOG.debug("SBI code " + sbiCode + " gevonden in sbi_activiteit");
508 foundSbiCode = true;
509 break;
510 }
511 }
512 if (!foundSbiCode) {
513 fail("SBI code niet gevonden in sbi_activiteit, verwacht te vinden: " + sbiCode);
514 }
515 for (int i = 0; i < herkomst_metadata.getRowCount(); i++) {
516 if (sbiCode.equals(herkomst_metadata.getValue(i, "waarde").toString())) {
517 LOG.debug("SBI code " + sbiCode + " gevonden in herkomst_metadata");
518 foundSbiCodeMeta = true;
519 break;
520 }
521 }
522 if (!foundSbiCodeMeta) {
523 fail("SBI code niet gevonden in herkomst_metadata, verwacht te vinden: " + sbiCode);
524 }
525 }
526
527 ITable functionaris = rsgb.createDataSet().getTable("functionaris");
528 assertEquals(
529 aantalFunctionarissen,
530 functionaris.getRowCount(),
531 "Het aantal 'functionaris' records klopt niet");
532 if (kvkNummer == 41177576) {
533
534
535
536 functionaris =
537 rsgb.createQueryTable("functionaris", "select * from functionaris order by functie ASC");
538 assertEquals("Gevolmachtigde", functionaris.getValue(aantalFunctionarissen - 1, "functie"));
539 assertEquals(
540 "Directeur", functionaris.getValue(aantalFunctionarissen - 1, "functionaristypering"));
541 assertEquals(
542 "B", functionaris.getValue(aantalFunctionarissen - 1, "volledig_beperkt_volmacht"));
543 }
544 }
545 }