Merge branch 'DEVELOP'
diff --git a/src/main/asciidoc/architectureDocumentation/architectureDocumentation.adoc b/src/main/asciidoc/architectureDocumentation/architectureDocumentation.adoc
index 6a79087..5fd44f1 100644
--- a/src/main/asciidoc/architectureDocumentation/architectureDocumentation.adoc
+++ b/src/main/asciidoc/architectureDocumentation/architectureDocumentation.adoc
@@ -1,4 +1,4 @@
-////
+////
*******************************************************************************
* Copyright (c) 2019 Contributors to the Eclipse Foundation
*
@@ -29,7 +29,7 @@
=== Requirements Overview
-Many user modules of an openKONSEQUENZ installion need contact datas for their
+Many user modules of an openKONSEQUENZ installion need contact data for their
daily business. Furthermore they have to fulfil the regulatory requirement
of the General Data Protection Regulation (GDPR).
@@ -49,17 +49,17 @@
* *Flexibility* The reference platform shall grant that different systems and modules from different vendors/developers can interact and interoperate, and may be exchanged or recombined.
* *Availability* All platform modules that are running on the platform can only be as available as the platform same for user modules that are based on platform modules.
* *Maintainability* (and testability as part of maintainability) The platform and its platform modules shall be used longer than 15 years.
-* *Integration performance* New implemented functionality of oK own modules and external modules shall be included fast / automatically.
+* *Integration performance* New implemented functionality of oK's own modules and external modules shall be included fast / automatically.
* *Security* The platform and its modules need to underly security-by-design
The main quality goals of the core module Contact Base Data are:
* *Functionality* The core module must fulfil the functional requirements mentioned in the section before
-* *Ergonomics* The web interface must be realized according to oK-GUI-Styleguide.
+* *Ergonomics* The web interface must be realized according to oK-GUI-styleguide.
* *Good documentation* (i.e. code and architecture documentation) makes code changes easier and automatic
tests facilitate rigorous verification.
* *Modifiability* (and testability as part of modifiability)
-* *Integration performance* The core module must be easy integratable in different production environments.
+* *Integration performance* The core module's integration into different production environments has to be easy.
The following documents contain the quality goals in detail:
@@ -69,7 +69,7 @@
The architecture is based on the AC-Handbook. The quality demands are described in the QC-Handbook.
Both specifications were fully complied with in the project, so that a high quality is given.
-The code quality regarding static code analysis and unit test code coverage on the backend and fronend sides
+The code quality regarding static code analysis and unit test code coverage on the backend and frontend sides
are ensured by the use of sonarqube. The rule set and the qualtity gate are defined by the default, the
so called "sonar way".
@@ -478,9 +478,9 @@
=== Solution Strategy
-The module 'Contact Base Data' bases on a three-tier architecture:
+The module 'Contact Base Data' is based on a three-tier architecture:
-. *Frontend* - The GUI is implemented as a web-frontend with rich-client functionalities.
+. *Frontend* - The GUI is implemented as a web-frontend with rich-client functionality.
. *Backend* - The business functionalities are implemented in the backend tier. It provides the business functions via RESTful Webservices.
. *Database* - The database stores all module specific data.
@@ -493,7 +493,7 @@
. *UI* - Represents the graphical user interface and consumes the services from the business logic component via RESTful webservices.
. *Business Logic* - Realizes the business functionality and the data storage of the module. The
-module itself is split up in several components due to the requirement to use
+module itself is split up into several components due to the requirement to use
microservices.
.Module components
@@ -542,7 +542,7 @@
==== ContactBaseDataFE (frontend tier)
-The frontend component implements the concept of a single-page application (SPA). The framework used is Angular5.
+The frontend component implements the concept of a single-page application (SPA). The framework used is Angular 8.
It divides the contactBaseDataFE into three layers:
@@ -701,7 +701,7 @@
The component "Flyway" is used to make to distribute structural
or content related changes to the database.
-The database is built out of the scripts in the directory "db/migrations". Every sql
+The database is built out of the scripts in the directory "db/migrations". Every SQL
script contains the complete db script for the contact base data database (in different versions).
The highest version number indicates the currently valid script.
@@ -719,12 +719,12 @@
This yml-file can be divided into different configuration profiles.
When starting the backend-service one has the possibility to specify
-the active profile
+the active profile.
* *spring.datasource* configuration section for the database connection
* *flyway.enabled* If enabled=true then the database migrations
- will automatically performed when starting the application
- (this parameter should normally be set to "false"
+ will be performed automatically when starting the application
+ (this parameter should normally be set to "false")
* *server.max-http-header-size* Maximum size for the http-headers
* *jwt.tokenHeader* Name of the http-header which carries the authentication-token.
(should be "Authorization")
@@ -732,10 +732,13 @@
as Authorization-token. (This won't work for calls to other modules
like the Auth'n'Auth-Modul, because the token will be out of date)
* *authNAuthService.ribbon.listOfServers* Here one can configure the base
- url to the Auth'n'Auth-Service
+ URL to the Auth'n'Auth-service
* *ldap.enabled* If set to "true" the ldap functionality will be enabled
-* *ldap.scheduling.enabled* (true or false) swtiches the ldap synchronisation on/off
-
+* *ldap.scheduling.enabled* (true or false) switches the ldap synchronisation on/off
+* *ldap-sync.db-id-mapping.telephone-number-id* the primary ID for "telephone number" row in table REF_COMMUNICATION_TYPE.
+ (Default 1) If set to -1 ldapmapping is disable for "telephone number"
+* *ldap-sync.db-id-mapping.mail-id* the primary ID for "mail" row in table REF_COMMUNICATION_TYPE.
+ (Default 2) If set to -1 ldapmapping is disable for "telephone number"
=== CI- and CD-Components
@@ -759,13 +762,13 @@
. "SNAPSHOT" or "DEVELOP"
. "MASTER" or "TRUNC"
-The running development is exclusively made on the Snapshot-Branch. Every time
+The running development is exclusively made on the snapshot-branch. Every time
a developer checks in (pushes) code to the repository, an automatic build
-starts on the hudson ci-server. If the Snapshot-build is successful, then the result
-of that build is directly deployed on the Dev-environment.
+starts on the hudson ci-server. If the snapshot-build is successful, then the result
+of that build is directly deployed on the dev-environment.
At the end of a scrum sprint or when a big user story is realized, all
-the code changes are merged from the *Snapshot*-Branch to the *Trunc*.
+code changes are merged from the *SNAPSHOT*-Branch to the *TRUNC*.
This automatically triggers the build and the deployment on the
Q-environment.
@@ -790,10 +793,10 @@
|CNCU|Central Network Control Unit||
|DAO|Data Access Objects||
|DTO|Data Transfer Object||
-|DSO|Distribution System Operator|Verteilnetz-betreiber (VNB)|Manages the distribution network for energy, gas or water.
+|DSO|Distribution System Operator|Verteilnetzbetreiber (VNB)|Manages the distribution network for energy, gas or water.
|EPL|Eclipse Public License||Underlying license model for Eclipse projects like contact-base-data@openK
|ESB|Enterprise Service Bus||Central instance to exchange data to overcome point-to-point connections.
|oK|openKONSEQUENZ|openKONSEQUENZ|Name of the consortium of DSOs
|QC|Quality Committee|Qualitätskomitee|Gives framework and constraints according to quality for oK projects.
-|SCADA|Supervisory Control and Data Acquisition|Netzleitsystem|System, that allows DSOs view/control actual parameters of their power grid.
+|SCADA|Supervisory Control and Data Acquisition|Netzleitsystem|System, that allows DSOs to view/control actual parameters of their power grid.
|========================================================
diff --git a/src/main/asciidoc/img/abbrechen.png b/src/main/asciidoc/img/abbrechen.png
index cf98a3a..4c4abb2 100644
--- a/src/main/asciidoc/img/abbrechen.png
+++ b/src/main/asciidoc/img/abbrechen.png
Binary files differ
diff --git a/src/main/asciidoc/img/abmelden.png b/src/main/asciidoc/img/abmelden.png
index 1a8799f..db1eb04 100644
--- a/src/main/asciidoc/img/abmelden.png
+++ b/src/main/asciidoc/img/abmelden.png
Binary files differ
diff --git a/src/main/asciidoc/img/admin-menue-symbol.png b/src/main/asciidoc/img/admin-menue-symbol.png
new file mode 100644
index 0000000..24c32dd
--- /dev/null
+++ b/src/main/asciidoc/img/admin-menue-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/adresse-anlegen.png b/src/main/asciidoc/img/adresse-anlegen.png
new file mode 100644
index 0000000..f73886d
--- /dev/null
+++ b/src/main/asciidoc/img/adresse-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/adresseliste-gefuellt.png b/src/main/asciidoc/img/adresseliste-gefuellt.png
new file mode 100644
index 0000000..34504c3
--- /dev/null
+++ b/src/main/asciidoc/img/adresseliste-gefuellt.png
Binary files differ
diff --git a/src/main/asciidoc/img/adressliste-leer.png b/src/main/asciidoc/img/adressliste-leer.png
new file mode 100644
index 0000000..d09b5a3
--- /dev/null
+++ b/src/main/asciidoc/img/adressliste-leer.png
Binary files differ
diff --git a/src/main/asciidoc/img/adresstyp-anlegen.png b/src/main/asciidoc/img/adresstyp-anlegen.png
new file mode 100644
index 0000000..eb6ff9e
--- /dev/null
+++ b/src/main/asciidoc/img/adresstyp-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/adresstypen-uebersicht.png b/src/main/asciidoc/img/adresstypen-uebersicht.png
new file mode 100644
index 0000000..dce5123
--- /dev/null
+++ b/src/main/asciidoc/img/adresstypen-uebersicht.png
Binary files differ
diff --git a/src/main/asciidoc/img/anpsrechpartner-leer.png b/src/main/asciidoc/img/anpsrechpartner-leer.png
new file mode 100644
index 0000000..5c0436e
--- /dev/null
+++ b/src/main/asciidoc/img/anpsrechpartner-leer.png
Binary files differ
diff --git a/src/main/asciidoc/img/anrede-anlegen.png b/src/main/asciidoc/img/anrede-anlegen.png
new file mode 100644
index 0000000..f5f34a2
--- /dev/null
+++ b/src/main/asciidoc/img/anrede-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/ansprechpartner-anlegen.png b/src/main/asciidoc/img/ansprechpartner-anlegen.png
new file mode 100644
index 0000000..f57a9ca
--- /dev/null
+++ b/src/main/asciidoc/img/ansprechpartner-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/ansprechpartner-gefuellt.png b/src/main/asciidoc/img/ansprechpartner-gefuellt.png
new file mode 100644
index 0000000..3b44ee2
--- /dev/null
+++ b/src/main/asciidoc/img/ansprechpartner-gefuellt.png
Binary files differ
diff --git a/src/main/asciidoc/img/auge-symbol.png b/src/main/asciidoc/img/auge-symbol.png
index 6bb5e6f..455abd2 100644
--- a/src/main/asciidoc/img/auge-symbol.png
+++ b/src/main/asciidoc/img/auge-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/auswahl-kontakt-anlegen.png b/src/main/asciidoc/img/auswahl-kontakt-anlegen.png
new file mode 100644
index 0000000..3b57b74
--- /dev/null
+++ b/src/main/asciidoc/img/auswahl-kontakt-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/auswahl-kontakttypen.png b/src/main/asciidoc/img/auswahl-kontakttypen.png
new file mode 100644
index 0000000..5d1bc27
--- /dev/null
+++ b/src/main/asciidoc/img/auswahl-kontakttypen.png
Binary files differ
diff --git a/src/main/asciidoc/img/auswahl-sortierung-2.png b/src/main/asciidoc/img/auswahl-sortierung-2.png
new file mode 100644
index 0000000..319866f
--- /dev/null
+++ b/src/main/asciidoc/img/auswahl-sortierung-2.png
Binary files differ
diff --git a/src/main/asciidoc/img/auswahl-sortierung.png b/src/main/asciidoc/img/auswahl-sortierung.png
new file mode 100644
index 0000000..63ef815
--- /dev/null
+++ b/src/main/asciidoc/img/auswahl-sortierung.png
Binary files differ
diff --git a/src/main/asciidoc/img/dialog-nach-abbrechen.png b/src/main/asciidoc/img/dialog-nach-abbrechen.png
new file mode 100644
index 0000000..fa13b42
--- /dev/null
+++ b/src/main/asciidoc/img/dialog-nach-abbrechen.png
Binary files differ
diff --git a/src/main/asciidoc/img/externe-person-1.png b/src/main/asciidoc/img/externe-person-1.png
new file mode 100644
index 0000000..3b451b8
--- /dev/null
+++ b/src/main/asciidoc/img/externe-person-1.png
Binary files differ
diff --git a/src/main/asciidoc/img/haus-symbol.png b/src/main/asciidoc/img/haus-symbol.png
new file mode 100644
index 0000000..4ace3d2
--- /dev/null
+++ b/src/main/asciidoc/img/haus-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/interne-person-1.png b/src/main/asciidoc/img/interne-person-1.png
new file mode 100644
index 0000000..03754b1
--- /dev/null
+++ b/src/main/asciidoc/img/interne-person-1.png
Binary files differ
diff --git a/src/main/asciidoc/img/interne-person-2.png b/src/main/asciidoc/img/interne-person-2.png
new file mode 100644
index 0000000..61fac82
--- /dev/null
+++ b/src/main/asciidoc/img/interne-person-2.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationskanaele-leer.png b/src/main/asciidoc/img/kommunikationskanaele-leer.png
new file mode 100644
index 0000000..1ddc182
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationskanaele-leer.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationskanal-anlegen.png b/src/main/asciidoc/img/kommunikationskanal-anlegen.png
new file mode 100644
index 0000000..55407a6
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationskanal-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationskanal-gefuellt.png b/src/main/asciidoc/img/kommunikationskanal-gefuellt.png
new file mode 100644
index 0000000..83bf398
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationskanal-gefuellt.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationstyp-nur-ansicht.png b/src/main/asciidoc/img/kommunikationstyp-nur-ansicht.png
new file mode 100644
index 0000000..80950be
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationstyp-nur-ansicht.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationstypen-anlegen.png b/src/main/asciidoc/img/kommunikationstypen-anlegen.png
new file mode 100644
index 0000000..4590ad0
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationstypen-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/kommunikationstypen-uebersicht.png b/src/main/asciidoc/img/kommunikationstypen-uebersicht.png
new file mode 100644
index 0000000..6fd7f21
--- /dev/null
+++ b/src/main/asciidoc/img/kommunikationstypen-uebersicht.png
Binary files differ
diff --git a/src/main/asciidoc/img/kontakte_uebersichtstabelle.png b/src/main/asciidoc/img/kontakte_uebersichtstabelle.png
new file mode 100644
index 0000000..72d4f4d
--- /dev/null
+++ b/src/main/asciidoc/img/kontakte_uebersichtstabelle.png
Binary files differ
diff --git a/src/main/asciidoc/img/loesch-symbol.png b/src/main/asciidoc/img/loesch-symbol.png
new file mode 100644
index 0000000..22af7fa
--- /dev/null
+++ b/src/main/asciidoc/img/loesch-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/meldung-hinweis.png b/src/main/asciidoc/img/meldung-hinweis.png
new file mode 100644
index 0000000..fefb6ad
--- /dev/null
+++ b/src/main/asciidoc/img/meldung-hinweis.png
Binary files differ
diff --git a/src/main/asciidoc/img/meldung-loeschen.png b/src/main/asciidoc/img/meldung-loeschen.png
new file mode 100644
index 0000000..9d70d50
--- /dev/null
+++ b/src/main/asciidoc/img/meldung-loeschen.png
Binary files differ
diff --git a/src/main/asciidoc/img/modul-zuordnen.png b/src/main/asciidoc/img/modul-zuordnen.png
new file mode 100644
index 0000000..3b56d58
--- /dev/null
+++ b/src/main/asciidoc/img/modul-zuordnen.png
Binary files differ
diff --git a/src/main/asciidoc/img/modulzuordnung-gefuellt.png b/src/main/asciidoc/img/modulzuordnung-gefuellt.png
new file mode 100644
index 0000000..0ab1681
--- /dev/null
+++ b/src/main/asciidoc/img/modulzuordnung-gefuellt.png
Binary files differ
diff --git a/src/main/asciidoc/img/modulzuordnung-leer.png b/src/main/asciidoc/img/modulzuordnung-leer.png
new file mode 100644
index 0000000..20923b9
--- /dev/null
+++ b/src/main/asciidoc/img/modulzuordnung-leer.png
Binary files differ
diff --git a/src/main/asciidoc/img/neu.png b/src/main/asciidoc/img/neu.png
new file mode 100644
index 0000000..b9c37be
--- /dev/null
+++ b/src/main/asciidoc/img/neu.png
Binary files differ
diff --git a/src/main/asciidoc/img/oeffnen-modul.png b/src/main/asciidoc/img/oeffnen-modul.png
index c6ef04c..e031a76 100644
--- a/src/main/asciidoc/img/oeffnen-modul.png
+++ b/src/main/asciidoc/img/oeffnen-modul.png
Binary files differ
diff --git a/src/main/asciidoc/img/personentypen-anlegen.png b/src/main/asciidoc/img/personentypen-anlegen.png
new file mode 100644
index 0000000..4988a36
--- /dev/null
+++ b/src/main/asciidoc/img/personentypen-anlegen.png
Binary files differ
diff --git a/src/main/asciidoc/img/personentypen-uebersicht.png b/src/main/asciidoc/img/personentypen-uebersicht.png
new file mode 100644
index 0000000..67e3f2f
--- /dev/null
+++ b/src/main/asciidoc/img/personentypen-uebersicht.png
Binary files differ
diff --git a/src/main/asciidoc/img/speichern.png b/src/main/asciidoc/img/speichern.png
index d24aeb4..74aec3a 100644
--- a/src/main/asciidoc/img/speichern.png
+++ b/src/main/asciidoc/img/speichern.png
Binary files differ
diff --git a/src/main/asciidoc/img/stift-symbol.png b/src/main/asciidoc/img/stift-symbol.png
index c892d04..3c392e5 100644
--- a/src/main/asciidoc/img/stift-symbol.png
+++ b/src/main/asciidoc/img/stift-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/such-symbol.png b/src/main/asciidoc/img/such-symbol.png
new file mode 100644
index 0000000..2207a59
--- /dev/null
+++ b/src/main/asciidoc/img/such-symbol.png
Binary files differ
diff --git a/src/main/asciidoc/img/suche-beispiel.png b/src/main/asciidoc/img/suche-beispiel.png
new file mode 100644
index 0000000..8777abc
--- /dev/null
+++ b/src/main/asciidoc/img/suche-beispiel.png
Binary files differ
diff --git a/src/main/asciidoc/img/tabellen-menue.png b/src/main/asciidoc/img/tabellen-menue.png
new file mode 100644
index 0000000..943eced
--- /dev/null
+++ b/src/main/asciidoc/img/tabellen-menue.png
Binary files differ
diff --git a/src/main/asciidoc/img/tabellenspalten-verschieben.png b/src/main/asciidoc/img/tabellenspalten-verschieben.png
new file mode 100644
index 0000000..0953ee8
--- /dev/null
+++ b/src/main/asciidoc/img/tabellenspalten-verschieben.png
Binary files differ
diff --git a/src/main/asciidoc/img/uebersicht-anrede.png b/src/main/asciidoc/img/uebersicht-anrede.png
new file mode 100644
index 0000000..4c9163f
--- /dev/null
+++ b/src/main/asciidoc/img/uebersicht-anrede.png
Binary files differ
diff --git a/src/main/asciidoc/img/unternehmen-2.png b/src/main/asciidoc/img/unternehmen-2.png
new file mode 100644
index 0000000..a494ec9
--- /dev/null
+++ b/src/main/asciidoc/img/unternehmen-2.png
Binary files differ
diff --git a/src/main/asciidoc/img/unternehmen.png b/src/main/asciidoc/img/unternehmen.png
new file mode 100644
index 0000000..d2e344b
--- /dev/null
+++ b/src/main/asciidoc/img/unternehmen.png
Binary files differ
diff --git a/src/main/asciidoc/userDocumentation/userDocumentation.adoc b/src/main/asciidoc/userDocumentation/userDocumentation.adoc
new file mode 100644
index 0000000..0309859
--- /dev/null
+++ b/src/main/asciidoc/userDocumentation/userDocumentation.adoc
@@ -0,0 +1,714 @@
+= Benutzerhandbuch: Kontaktstammdaten
+
+<<<
+
+
+* Version: 0.1
+* Datum: 21.02.2020
+* Status: In Bearbeitung
+
+<<<
+
+:Author: Dr. Michel Alessandrini
+:Email: michel.alessandrini@pta.de
+
+:Date: 2020-02-21
+:Revision: 0.1
+
+:icons:
+
+:source-highlighter: highlightjs
+:highlightjs-theme: solarized_dark
+
+:lang: de
+:encoding: utf-8
+
+:imagesdir: ../img
+:iconsdir: ../img/icons
+
+:toc:
+:toclevels: 4
+:toc-title: Inhaltsverzeichnis
+:toc-placement!:
+:sectanchors:
+:numbered:
+
+toc::[]
+
+<<<
+
+== Sinn und Zweck des Moduls Kontaktstammdaten
+
+Das Modul Kontaktstammdaten ist ein Domain-Modul der openKONSEQUENZ.
+
+Das Modul Kontaktstammdaten übernimmt die Verwaltung aller Kontaktdaten
+in einer openKONSEQUENZ-Installation. D.h. es synchronisiert sich mit den
+Benutzern des Auth&Auth-Moduls oder mit dem Unternehmens-LDAP. Des Weiteren
+verwaltet es alle (Internen und Externen) Personen sowie Firmen, die in den
+User Modulen benötigt werden.
+
+Das Modul Kontaktstammdaten ist somit die zentrale Stelle zur Verwaltung
+aller Kontakte und personenbezogener Daten in einer openKONSEQUENZ-Installation.
+Es setzt die DSGVO-Vorgaben um.
+
+<<<
+
+
+[[uebersicht-benutzerrollen]]
+== Benutzerrollen und Aufgaben
+Zur Bearbeitung von Kontaktstammdaten sind folgende Benutzerrollen
+definiert.
+
+[[lesende-rolle]]
+=== Lesende Rolle
+TODO: Am Ende die Links setzen
+
+* <<uebersicht-aller-kontakte,Darf alle Kontakte sehen>>
+
+=== Schreibende Rolle
+TODO: Am Ende die Links setzen
+
+* <<lesende-rolle,Darf alles, was die Lesende Rolle darf>>
+* <<kontakt-erstellen,Darf neue Kontakte anlegen>>
+* <<kontakt-bearbeiten,Darf bestehende Kontakte bearbeiten>>
+
+=== Administrator
+TODO: Am Ende die Links setzen
+
+* <<schreibene-rolle,Darf alles, was die Schreibende Rolle darf>>
+* <<kontakt-loeschen,Darf bestehende Kontakte löschen>>
+* <<stammdaten-pflege,Darf Stammdaten anlegen, bearbeiten, löschen>>
+* <<dsgvo-funktionen,Darf DSGVO-Funktionen ausführen>>
+
+<<<
+
+== Verwaltung der Kontakte
+
+[[uebersicht-aller-kontakte]]
+=== Übersicht
+Die Übersichtsseite stellt standardmäßig alle Kontakte
+in einer Tabelle dar.
+
+.Übersicht der Kontakte
+[options="header,footer"]
+image::kontakte_uebersichtstabelle.png[]
+
+Die Übersichtstabelle zeigt zu jedem Kontakt folgende Informationen an:
+
+.Felder in der Tabellenübersicht
+[options="header,footer"]
+|=========================================================
+|Feld|Erläuterung
+|Name
+a|
+ * Name des Kontakts (Personen- oder Firmen-Name)
+
+|E-Mail
+a|
+
+* E-Mail-Adresse des Kontakts
+
+|Kontakttyp
+a|
+
+* Unternehmen
+* Interne Person
+* Externe Person
+
+|Notiz
+a|
+
+* Bemerkung bzw. Hinweis zu einem Kontakt
+
+|Hauptadresse
+a|
+
+* Primäre Anschrift
+
+|(ohne Überschrift))
+a|
+
+* Bearbeitungssymbole, siehe Abschnitt <<uebersicht-bearbeitungssymbole, Bearbeitungssymbole>>
+
+|=========================================================
+
+[[uebersicht-bearbeitungssymbole]]
+=== Kontakt anlegen, anzeigen, bearbeiten und löschen
+
+Jeder Kontakt kann je nach Benutzerrolle direkt aus der Tabelle angezeigt,
+bearbeitet oder gelöscht werden.
+
+==== Kontakt anzeigen
+
+Über das folgende Symbol in der Tabelle wird der entsprechende Kontakt
+zur Anzeige geöffnet.
+
+.Auge-Symbol zum Anzeigen eines Kontakts
+[options="header,footer"]
+image::auge-symbol.png[]
+
+CAUTION: Das Auge-Symbol ist nur bei der "Lesenden Rolle" sichtbar
+
+==== Kontakt bearbeiten
+
+Über das folgende Symbol in der Tabelle wird der entsprechende Kontakt
+zur Beareitung geöffnet.
+
+.Stift-Symbol zum Bearbeiten eines Kontakts
+[options="header,footer"]
+image::stift-symbol.png[]
+
+CAUTION: Das Stift-Symbol ist nur bei der "Schreibenden Rolle" und der Rolle "Administrator" sichtbar.
+
+==== Kontakt löschen
+
+Über das folgende Symbol in der Tabelle wird der entsprechende Kontakt
+gelöscht.
+
+.Löschen-Symbol zum Entfernen eines Kontakts
+[options="header,footer"]
+image::loesch-symbol.png[]
+
+CAUTION: Das Mülleimer-Symbol ist nur bei der Rolle "Administrator" sichtbar.
+
+CAUTION: Die Löschfunktion steht nur beim Kontakttyp "Ansprechpartner" zur Verfügung.
+
+Nach Klick auf das Löschen-Symbol erscheint ein Dialog, der mit "Ja" zu bestätigen ist.
+
+.Dialog nach Klick auf Löschen
+[options="header,footer"]
+image:meldung-loeschen.png[]
+
+==== Kontakt anlegen
+
+[[kontakt-erstellen]]
+Ist ein Benutzer mit der Benutzerrolle "Schreibende Rolle" angemeldet,
+so besteht die Möglichkeit über die Schaltfläche "Neuen Kontakt anlegen" einen
+neuen Kontakt anzulegen.
+
+.Schaltfläche zum Erstellen von Kontakten
+[options="header,footer"]
+image:auswahl-kontakt-anlegen.png[]
+
+Hier muss der Bearbeiter den Kontakttyp bestimmen:
+
+.Kontakttypen
+[options="header,footer"]
+|=========================================================
+|Kontakttyp|Erläuterung
+|Interner Kontakt
+a|
+ * Eine natürliche Person, die dem Unternehmen angehört
+
+|Externer Kontakt
+a|
+* Eine natürliche Person, die dem Unternehmen nicht angehört
+
+|Unternehmen
+a|
+* Eine juristische Person
+|=========================================================
+
+Um den Kontakt zu speichern, muss die folgende Schaltfläche betätigt werden.
+
+.Schaltfläche zum Speichern
+[options="header,footer"]
+image:speichern.png[]
+
+Wenn die neu erfassten Daten nicht gespeichert werden sollen, dann ist folgende Schaltfläche zu drücken:
+.Schaltfläche zum Abbrechen (ohne speichern)
+[options="header,footer"]
+image:abbrechen.png[]
+
+Anschließend erscheint ein Dialog, der mit "Ja" zu bestätigen ist.
+
+.Dialog nach Abbrechen
+[options="header,footer"]
+image:dialog-nach-abbrechen.png[]
+
+
+=== Kontakttypen
+
+==== Interne Person
+Die Basisinformationen eines Kontakts befindet sich im oberen
+Teil des Benutzerdialogs.
+
+Pflichtfelder sind farblich markiert. Zunächst sind diese rot. Erst wenn die
+Inhalte entsprechend eingetragen sind, ändert sich die Markierung auf grün.
+
+.Basis-Informationen eines Kontakts am Beispiel einer internen Person
+[options="header,footer"]
+image::interne-person-1.png[]
+
+Eine interne Person besitzt folgende Attribute:
+
+.Attribute einer internen Person
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Vorname |nein |Textfeld |Vorname der natürlichen Person
+|Name |ja |Textfeld |Nachname der natürlichen Person
+|Titel |nein |Textfeld |z.B. Prof. oder Dr.
+|Anrede |nein |Auswahlfeld |Auswahl einer Anrede, z.B. Herr oder Frau
+|Personentyp |nein |Auswahlfeld |Auswahl eines Personentyps
+|LDAP User |nein |Textfeld |Bezeichnung des Benutzers im Unternehmens-LDAP. Wird zur Synchronisation verwendet.
+|Portal User |nein |Textfeld |Bezeichnung des Benutzers im Portal. Wird zur Synchronisation verwendet.
+|Abteilung |nein |Textfeld |Abteilung der Person
+|Notiz |nein |Textfeld |Beliebige Hinweise zur Person
+|=========================================================
+
+==== Externe Person
+Die Basisinformationen eines Kontakts befindet sich im oberen
+Teil des Benutzerdialogs.
+
+Pflichtfelder sind farblich markiert. Zunächst sind diese rot. Erst wenn die
+Inhalte entsprechend eingetragen sind, ändert sich die Markierung auf grün.
+
+.Basis-Informationen eines Kontakts am Beispiel einer externen Person
+[options="header,footer"]
+image::externe-person-1.png[]
+
+Eine externe Person besitzt folgende Attribute:
+
+.Attribute einer externen Person
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Vorname |nein |Textfeld |Vorname der natürlichen Person
+|Name |ja |Textfeld |Nachname der natürlichen Person
+|Titel |nein |Textfeld |z.B. Prof. oder Dr.
+|Anrede |nein |Auswahlfeld |Auswahl einer Anrede, z.B. Herr oder Frau
+|Personentyp |nein |Auswahlfeld |Auswahl eines Personentyps
+|Notiz |nein |Textfeld |Beliebige Hinweise zur Person
+|=========================================================
+
+==== Unternehmen
+Die Basisinformationen eines Kontakts befindet sich im oberen
+Teil des Benutzerdialogs.
+
+Pflichtfelder sind farblich markiert. Zunächst sind diese rot. Erst wenn die
+Inhalte entsprechend eingetragen sind, ändert sich die Markierung auf grün.
+
+.Basis-Informationen eines Kontakts am Beispiel eines Unternehmens
+[options="header,footer"]
+image::unternehmen.png[]
+
+Ein unternehmen besitzt folgende Attribute:
+
+.Attribute eines Unternehmens
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Name |ja |Textfeld |Nachname der natürlichen Person
+|Rechtsform |nein |Textfeld |z.B. GmbH oder AG
+|Handelsregisternummer |nein |Textfeld |
+|Notiz |nein |Textfeld |Beliebige Hinweise zum unternehmen
+|=========================================================
+
+
+=== Weitere Informationen zu einem Kontakt
+Die Detailinformationen zu einem Kontakt befinden sich im unteren
+Teil des Benutzerdialogs.
+
+CAUTION: Bei der Neu-Anlage eines Kontakts sind die Details zunächst nicht sichtbar. Erst nach dem Speichern des neuen Kontakts werden die folgenden Detailinformationen angezeigt.
+
+.Detail-Informationen eines Kontakts
+[options="header,footer"]
+image::interne-person-2.png[]
+
+Dieser Bereich ist in folgende Abschnitte unterteilt:
+
+* Adressliste
+* Kommunikationskanäle
+* Modulzuordnung
+* Ansprechpartner (nur bei Unternehmen)
+
+
+==== Adressliste
+Die Adressliste beinhaltet alle Adressen der internen Person. Eine Adresse besteht aus folgenden Attributen:
+
+.Attribute einer Adresse (Kontakt-Detailinformationen)
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Adresstyp |ja |Auswahlfeld |Auswahl eines Adresstyps
+|Hauptadresse |nein |Checkbox |Kennzeichen, ob dies die Hauptadresse ist (nur relevant, wenn mehrere Adressen vorhanden)
+|Postleitzahl |nein |Zahl |Adressdaten
+|Ort |nein |Textfeld |Adressdaten
+|Adresse (Zusatz) |nein |Textfeld |Adressdaten
+|Straße |nein |Textfeld |Adressdaten
+|Hausnummer |nein |Textfeld |Adressdaten
+|WGS84-Zone |nein |Textfeld |
+|Längengrad |nein |Textfeld |
+|Breitengrad |nein |Textfeld |
+|Google Maps URL |nein |Textfeld |
+|Notiz |nein |Textfeld |Beliebige Hinweise zur Person
+|=========================================================
+
+Der geöffnete Bereich sieht folgendermaßen aus:
+
+.Tabellenansicht der Adressen unter einem Kontakt
+[options="header,footer"]
+image::adresseliste-gefuellt.png[]
+
+Über die Schaltfläche "Neue Adresse anlegen" kann eine Adresse angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage einer Adresse unter einem Kontakt
+[options="header,footer"]
+image::adresse-anlegen.png[]
+
+
+==== Kommunikationskanäle
+Die Kommunikationskanäle beinhalten alle Kontaktinformationen. Eine Kommunikationskanal besteht aus folgenden Attributen:
+
+.Attribute eines Kommunikationskanals (Kontakt-Detailinformationen)
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Typ |ja |Auswahlfeld |Auswahl eines Kommunikationskanals
+|Daten |nein |Textfeld |KOntaktinformationen des entsprechenden Kommunikationskanals
+|Notiz |nein |Textfeld |Beliebige Hinweise zum Kommunikationskanal
+|=========================================================
+
+Der geöffnete Bereich sieht folgendermaßen aus:
+
+.Tabellenansicht der Kommunikationskanäle unter einem Kontakt
+[options="header,footer"]
+image::kommunikationskanal-gefuellt.png[]
+
+Über die Schaltfläche "Neuen Kommunikationskanal anlegen" kann ein Kommunikationskanal angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Kommunikationskanals unter einem Kontakt
+[options="header,footer"]
+image::kommunikationskanal-anlegen.png[]
+
+
+==== Modulzuordnung
+Die Modulzuordnung legt fest in welchen Modulen der Kontakt genutzt wird.
+Des Weiteren wird hier hinterlegt, wie lange die Kontaktdaten minestens aufbewahrt werden müssen
+und wann diese ggf. abläuft.
+Eine Modulzuordnung besteht aus folgenden Attributen:
+
+.Attribute einer Modulzuordnung (Kontakt-Detailinformationen)
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Modulname |ja |Auswahlfeld |Auswahl eines openKONSEQUENZ-Moduls
+|Ablaufdatum |nein |Datum |Zeitpunkt, ab wann die Kontaktdaten gemäß DSGVO gelöscht werden müssen
+|Löschsperre bis |nein |Datum |Zeitpunkt, bis wann die Kontaktdaten gemäß DSGVO nicht gelöscht werden dürfen
+|Notiz |nein |Textfeld |Beliebige Hinweise zum Modul
+|=========================================================
+
+Der geöffnete Bereich sieht folgendermaßen aus:
+
+.Tabellenansicht der Modulzuordnungen unter einem Kontakt
+[options="header,footer"]
+image::modulzuordnung-gefuellt.png[]
+
+Über die Schaltfläche "Neue Modulzuordnung anlegen" kann eine Modulzuordnung angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage einer Modulzuordnung unter einem Kontakt
+[options="header,footer"]
+image::modul-zuordnen.png[]
+
+==== Ansprechpartner
+Die Ansprechpartner sind Persoen, die zu einem Unternehmen gehören. Deshalb gibt es diese Daten nur bei dem Kontakttyp Unternehmen.
+Ein Ansprechpartner beitzt folgende Attribute:
+
+.Attribute eines Ansprechpartners (Kontakt-Detailinformationen)
+[options="header,footer"]
+|=========================================================
+|Attribut |Pflichtfeld |Inhaltsart |Beschreibung
+|Vorname |nein |Textfeld |Vorname des Ansprechpartners
+|Name |ja |Textfeld |Nachname des Ansprechpartners
+|Titel |nein |Textfeld |z.B. Prof. oder Dr.
+|Anrede |nein |Auswahlfeld |Auswahl einer Anrede, z.B. Herr oder Frau
+|Personentyp |nein |Auswahlfeld |Auswahl eines Personentyps
+|Notiz |nein |Textfeld |Beliebige Hinweise zum Ansprechpartner
+|=========================================================
+
+Der geöffnete Bereich sieht folgendermaßen aus:
+
+.Tabellenansicht der Ansprechpartner unter einem Kontakt
+[options="header,footer"]
+image::ansprechpartner-gefuellt.png[]
+
+Über die Schaltfläche "Neuen Ansprechpartner anlegen" kann ein Ansprechpartner angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Ansprechpartners unter einem Kontakt
+[options="header,footer"]
+image::ansprechpartner-anlegen.png[]
+
+<<<
+
+[[dsgvo-funktionen]]
+== DSGVO-Funktionen
+
+TODO: Allgemein beschreiben
+
+
+[[dsgvo-erfassen]]
+=== Erfassung der DSGVO-Informationen zu einem Kontakt
+
+TODO: siehe Modulzuordnung
+
+[[dsgvo-anonymisieren]]
+=== Abgelaufene Kontakte identifizieren
+
+TODO
+
+[[dsgvo-loeschsperre]]
+=== Löschsperre bei Kontakten
+
+TODO
+
+[[dsgvo-anonymisieren]]
+=== Kontakt anonymisieren
+
+TODO
+
+
+
+
+<<<
+
+[[verwaltung-stammdaten]]
+== Verwaltung der Stammdaten
+Die Funktionen zur Verwaltung der Stammdaten verbirgt sich hinter dem folgenden
+Symbol in der Menüleiste:
+
+.Symbol zum Aufruf der Stammdaten-Verwaltung
+[options="header,footer"]
+image::admin-menue-symbol.png[]
+
+CAUTION: Die Stammdaten-Verwaltung ist nur für die Rolle "Administrator" verfügbar.
+
+Es können folgende Stammdaten bearbeitet werden:
+
+* Anreden
+* Adresstypen
+* Kommunikationstypen
+* Personentypen
+
+=== Anreden
+Nach dem Aufruf der Anreden-Verwaltung erscheint die Übersicht mit allen bereits
+angelegten Anreden:
+
+.Tabellenansicht der Anreden
+[options="header,footer"]
+image::uebersicht-anrede.png[]
+
+Über die Schaltfläche "Neu" kann einer Eintrag angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Anreden
+[options="header,footer"]
+image::anrede-anlegen.png[]
+
+
+=== Adresstypen
+Nach dem Aufruf der Adresstypen-Verwaltung erscheint die Übersicht mit allen bereits
+angelegten Adresstypen:
+
+.Tabellenansicht der Adresstypen
+[options="header,footer"]
+image::adresstypen-uebersicht.png[]
+
+Über die Schaltfläche "Neu" kann einer Eintrag angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Adresstyps
+[options="header,footer"]
+image::adresstyp-anlegen.png[]
+
+
+=== Kommunikationstypen
+Nach dem Aufruf der Kommunikationstypen-Verwaltung erscheint die Übersicht mit allen bereits
+angelegten Kommunikationstypen:
+
+.Tabellenansicht der Kommunikationstypen
+[options="header,footer"]
+image::kommunikationstypen-uebersicht.png[]
+
+Über die Schaltfläche "Neu" kann einer Eintrag angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Kommunikationstypens
+[options="header,footer"]
+image::kommunikationstypen-anlegen.png[]
+
+Einige Einträge sind in der Standardkonfiguration enthalten und dürfen somit
+nicht geändert werden. Diese sind in der Übersichtstabelle mit einem Auge-Symbol
+gekennzeichnet. Wird der entsprechende Eintrag über dieses Symbol geöffnet,
+so können keine Anpassungen vorgenommen werden.
+
+.Maske zur Anlage eines Kommunikationstypens
+[options="header,footer"]
+image::kommunikationstyp-nur-ansicht.png[]
+
+
+=== Personentypen
+Nach dem Aufruf der Personentypen-Verwaltung erscheint die Übersicht mit allen bereits
+angelegten Personentypen:
+
+.Tabellenansicht der Personentypen
+[options="header,footer"]
+image::personentypen-uebersicht.png[]
+
+Über die Schaltfläche "Neu" kann einer Eintrag angelegt werden.
+Anschließend öffnet sich die folgende Maske, die auch bei der Bearbeitung zum Einsatz kommt:
+
+.Maske zur Anlage eines Personentyps
+[options="header,footer"]
+image::personentypen-anlegen.png[]
+
+
+[[meldungen-hinweise]]
+== Hinweise und Meldungen nach Benutzeraktionen
+Nach Benutzeraktionen erscheinen in bestimmten Fällen Meldungen des Systems,
+um den Benutzer:
+
+* Handlungsanweisungen zu geben,
+* über Fehler des Systems zu informieren.
+
+=== Erfolg einer Aktion
+Ist eine Benutzeraktion erfolgreich, so erscheint keine Meldung.
+
+=== Handlungsanweisung
+In einigen Situationen muss der Benutzer weitere Aktionen ausführen, damit die
+Handlung erfolgreich abgeschlossen werden kann. Diese gelb hinterlegten Hinweise
+erscheinen im folgenden Fall:
+
+* Fehlenden Informationen (Pflichtfelder füllen)
+
+.Hinweis Pflichtfelder
+[options="header,footer"]
+image::meldung-hinweis.png[]
+
+Diese Meldungen bleiben für fünf Sekunden sichtbar und verschwinden dann wieder
+automatisch.
+
+=== Fehlermeldungen
+Wenn Aktionen aufgrund technischer Gegebenheiten nicht erfolgreich durchgeführt
+werden können, erscheinen rot hinterlegte Fehlermeldungen. Das kann u.a. in
+folgenden Situationen passieren:
+
+* Speichern fehlgeschlagen
+* Web Services nicht verfügbar
+
+.Fehlermeldung
+[options="header,footer"]
+image::meldung-fehler.png[]
+
+Diese Meldungen bleiben solange sichtbar bis sie durch den Benutzer über das
+kleine x geschlossen werden. In diesen Fällen ist der Administrator zu
+informieren.
+
+<<<
+
+== Allgemeine Funktionen
+
+=== An- und abmelden
+
+==== Modul "Kontaktstammdaten" aus Portal starten
+Nach erfolgreicher Anmeldung am openKONSEQUENZ-Portal und mit entsprechender
+Berechtigung für das Modul "Kontaktstammdaten" ist die Kachel für das Modul
+sichtbar. Mit einem Klick auf die Kachel startet das Modul "Kontaktstammdaten"
+in einem neuen Fenster.
+
+.Kachel zum Starten des Moduls "Kontaktstammdaten"
+[options="header,footer"]
+image::oeffnen-modul.png[]
+
+==== Abmelden
+Die Schaltfläche zum Abmelden befindet sich hinter dem Benutzernamen.
+
+.Abmelden vom openKONSEQUENZ-System
+[options="header,footer"]
+image::abmelden.png[]
+
+CAUTION: Beim Klick auf "Abmelden" erfolgt nicht nur die Abmeldung vom Modul
+Kontaktstammdaten, sondern die Abmeldung vom Portal. D.h. alle parallel geöffneten
+openKONSEQUENZ-Module (wie z.B. Betriebstagebuch, Geplante Netzmaßnahme) werden
+automatisch mit abgemeldet.
+
+Um die parallel geöffneten openKONSEQUENZ-Module nicht mit abzumelden, ist
+lediglich das Browserfenster mit dem Modul Kontaktstammdaten zu schließen.
+
+=== Home-Button
+Über den folgenden Home-Button in der Menüleiste kann jederzeit zur Tabellenansicht
+zurückgekehrt werden.
+
+.Home-Button
+[options="header,footer"]
+image::haus-symbol.png[]
+
+CAUTION: DEr Aufruf des Home-Buttons setzt die Suche, Filter und Sortierfunktion wieder zurück, sodass die Standardansicht wieder hergestellt ist.
+
+[[suche]]
+=== Suche
+In der Übersicht ist eine Such-Funktion integriert, die eingegebenen Zeichen in
+den Datensätzen der Kontaktstammdaten sucht. Bei der Suche werden alle Felder
+berücksichtigt.
+
+.Suchfunktion
+[options="header,footer"]
+image::suche-beispiel.png[]
+
+Das Auswahlfeld "Alle Kontakttypen" bestimmt den Suchumfang:
+
+* Alle Kontakttypen
+* Interne Personen
+* Externe Personen
+* Unternehmen
+
+Die Suche wird entweder über die "Enter-Taste" oder über das Lupen-Symbol
+ausgeführt:
+
+.Schaltfläche zum Starten der Suche
+[options="header,footer"]
+image::such-symbol.png[]
+
+
+=== Sortieren
+Die Sortierung in der Haupttabelle funktioniert über zwei Auswahlfelder:
+
+.Auswahl des Attributs für die Sortierung
+[options="header,footer"]
+image::auswahl-sortierung.png[]
+
+.Auswahl der Reihenfolge
+[options="header,footer"]
+image::auswahl-sortierung-2.png[]
+
+Nach Auswahl der entsprechenden Felder erfolgt die Suche automatisch.
+
+=== Hilfe
+TODO
+
+=== Synchronisierung mit Verzeichnisdiensten
+Die Aktivierung der Synchronisierung der Kontaktstammdaten mit den Verzeichnisdiensten
+LDAP und/oder Auth&Auth erfolgt durch den Administrator.
+
+
+<<<
+
+== Allgemeiner Hinweis
+Die Anwendung ist für folgende Endgeräte optimiert:
+
+* Desktop-PC
+* Tablet
+
+Es unterstützt folgende Browser:
+
+* Google Chrome
+* Firefox
+
+Die Bildschirmauflösung bzw. die Fenstergröße des Browsers sollte folgende
+Werte nicht unterschreiten:
+
+* 1.024 x 768 Pixel
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/api/AuthNAuthApi.java b/src/main/java/org/eclipse/openk/contactbasedata/api/AuthNAuthApi.java
index 9d79918..2f852bd 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/api/AuthNAuthApi.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/api/AuthNAuthApi.java
@@ -19,10 +19,11 @@
import org.eclipse.openk.contactbasedata.model.LoginCredentials;
import org.eclipse.openk.contactbasedata.viewmodel.UserModule;
import org.springframework.cloud.openfeign.FeignClient;
-import org.springframework.http.ResponseEntity;
-import org.springframework.web.bind.annotation.*;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.RequestBody;
+import org.springframework.web.bind.annotation.RequestHeader;
-import javax.xml.ws.Response;
import java.util.List;
@FeignClient(name = "${services.authNAuth.name}")
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/config/LdapSchedulerConfig.java b/src/main/java/org/eclipse/openk/contactbasedata/config/LdapSchedulerConfig.java
index a3b3c78..cb9b953 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/config/LdapSchedulerConfig.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/config/LdapSchedulerConfig.java
@@ -9,8 +9,6 @@
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
-import org.springframework.scheduling.annotation.SchedulingConfigurer;
-import org.springframework.scheduling.config.ScheduledTaskRegistrar;
@Log4j2
@Configuration
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/controller/AuthNAuthController.java b/src/main/java/org/eclipse/openk/contactbasedata/controller/AuthNAuthController.java
index e69b82f..bbea729 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/controller/AuthNAuthController.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/controller/AuthNAuthController.java
@@ -14,14 +14,11 @@
*/
package org.eclipse.openk.contactbasedata.controller;
-import com.fasterxml.jackson.core.JsonProcessingException;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiResponse;
import io.swagger.annotations.ApiResponses;
import lombok.extern.log4j.Log4j2;
import org.eclipse.openk.contactbasedata.service.AuthNAuthService;
-import org.eclipse.openk.contactbasedata.service.LdapService;
-import org.eclipse.openk.contactbasedata.viewmodel.LdapUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
@@ -30,9 +27,6 @@
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
-import java.net.URISyntaxException;
-import java.util.List;
-
@Log4j2
@RestController
@RequestMapping("/authnauth")
@@ -45,7 +39,7 @@
@ApiResponses(value = {@ApiResponse(code = 200, message = "Erfolgreich durchgeführt")})
@ResponseStatus(HttpStatus.OK)
@GetMapping( "/loginTest")
- public ResponseEntity<Object> loginTest() throws URISyntaxException, JsonProcessingException {
+ public ResponseEntity<Object> loginTest() {
authNAuthService.synchronizeAuthNAuth();
return ResponseEntity.ok().build();
}
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/model/RefCommunicationType.java b/src/main/java/org/eclipse/openk/contactbasedata/model/RefCommunicationType.java
index 961727b..e1aa62b 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/model/RefCommunicationType.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/model/RefCommunicationType.java
@@ -31,8 +31,6 @@
private UUID uuid;
private String type;
private String description;
- private boolean editable;
- private boolean mappingLdap;
private boolean typeEmail;
}
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/service/AuthNAuthService.java b/src/main/java/org/eclipse/openk/contactbasedata/service/AuthNAuthService.java
index 746a8ee..d5c6009 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/service/AuthNAuthService.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/service/AuthNAuthService.java
@@ -1,34 +1,24 @@
package org.eclipse.openk.contactbasedata.service;
-import com.fasterxml.jackson.core.JsonProcessingException;
-import com.fasterxml.jackson.databind.ObjectMapper;
-import com.fasterxml.jackson.databind.ObjectReader;
-import feign.Response;
+
import lombok.extern.log4j.Log4j2;
-import org.apache.commons.lang.StringUtils;
import org.eclipse.openk.contactbasedata.api.AuthNAuthApi;
-import org.eclipse.openk.contactbasedata.exceptions.NotFoundException;
-import org.eclipse.openk.contactbasedata.model.*;
-import org.eclipse.openk.contactbasedata.repository.CommunicationRepository;
-import org.eclipse.openk.contactbasedata.repository.CommunicationTypeRepository;
+import org.eclipse.openk.contactbasedata.model.JwtToken;
+import org.eclipse.openk.contactbasedata.model.KeyCloakUser;
+import org.eclipse.openk.contactbasedata.model.LoginCredentials;
+import org.eclipse.openk.contactbasedata.model.TblInternalPerson;
import org.eclipse.openk.contactbasedata.repository.InternalPersonRepository;
-import org.eclipse.openk.contactbasedata.service.util.LdapUserAttributesMapper;
-import org.eclipse.openk.contactbasedata.viewmodel.LdapUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
-import org.springframework.http.ResponseEntity;
-import org.springframework.ldap.core.LdapTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
-import org.springframework.web.client.RestTemplate;
-import java.net.URI;
-import java.net.URISyntaxException;
-import java.util.*;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;
-import static org.springframework.ldap.query.LdapQueryBuilder.query;
-
@Log4j2
@Service
public class AuthNAuthService {
@@ -80,7 +70,7 @@
for (TblInternalPerson tblInternalPerson : internalPersonList) {
KeyCloakUser keyCloakUserToSync = userRefToKeycloakMap.get(tblInternalPerson.getUserRef());
- boolean attributesChanged = mapKeycloakUserToInternaPerson(tblInternalPerson, keyCloakUserToSync);
+ boolean attributesChanged = mapKeycloakUserToInternalPerson(tblInternalPerson, keyCloakUserToSync);
if (attributesChanged) {
internalPersonListSynchronized.add(tblInternalPerson);
@@ -89,7 +79,7 @@
return internalPersonListSynchronized;
}
- private boolean mapKeycloakUserToInternaPerson(TblInternalPerson tblInternalPerson, KeyCloakUser keyCloakUserToSync) {
+ private boolean mapKeycloakUserToInternalPerson(TblInternalPerson tblInternalPerson, KeyCloakUser keyCloakUserToSync) {
boolean attributesChanged = false;
if (keyCloakUserToSync == null) {
tblInternalPerson.setUserRef(null);
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/service/CommunicationTypeService.java b/src/main/java/org/eclipse/openk/contactbasedata/service/CommunicationTypeService.java
index e38a7b6..6009672 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/service/CommunicationTypeService.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/service/CommunicationTypeService.java
@@ -21,16 +21,25 @@
import org.eclipse.openk.contactbasedata.repository.CommunicationTypeRepository;
import org.eclipse.openk.contactbasedata.viewmodel.CommunicationTypeDto;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
+import java.util.Objects;
import java.util.UUID;
import java.util.stream.Collectors;
@Log4j2
@Service
public class CommunicationTypeService {
+
+ @Value("${ldap-sync.db-id-mapping.telephone-number-id}")
+ private Long telephoneNumberId;
+
+ @Value("${ldap-sync.db-id-mapping.mail-id}")
+ private Long mailId;
+
@Autowired
private CommunicationTypeRepository communicationTypeRepository;
@@ -39,15 +48,37 @@
public CommunicationTypeDto findCommunicationType(UUID communicationTypeUuid) {
- return communicationTypeMapper.toCommunicationTypeDto(
- communicationTypeRepository.findByUuid(communicationTypeUuid).orElseThrow(NotFoundException::new)
- );
+
+ CommunicationTypeDto communicationTypeDto = communicationTypeMapper.
+ toCommunicationTypeDto(communicationTypeRepository.findByUuid(communicationTypeUuid).
+ orElseThrow(NotFoundException::new));
+
+ if (Objects.equals(telephoneNumberId, communicationTypeDto.getId())
+ || Objects.equals(mailId, communicationTypeDto.getId())) {
+ communicationTypeDto.setMappingLdap(true);
+ } else {
+ communicationTypeDto.setEditable(true);
+ }
+
+ return communicationTypeDto;
}
public List<CommunicationTypeDto> findCommunicationTypes() {
- return communicationTypeRepository.findAll().stream()
- .map(communicationTypeMapper::toCommunicationTypeDto)
- .collect(Collectors.toList());
+
+ List<CommunicationTypeDto> communicationTypeDtoList = communicationTypeRepository.findAll().stream()
+ .map(communicationTypeMapper::toCommunicationTypeDto)
+ .collect(Collectors.toList());
+
+ for (CommunicationTypeDto communicationTypeDto : communicationTypeDtoList) {
+ if (Objects.equals(telephoneNumberId, communicationTypeDto.getId())
+ || Objects.equals(mailId, communicationTypeDto.getId())) {
+ communicationTypeDto.setMappingLdap(true);
+ } else {
+ communicationTypeDto.setEditable(true);
+ }
+ }
+
+ return communicationTypeDtoList;
}
@Transactional
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/service/CompanyService.java b/src/main/java/org/eclipse/openk/contactbasedata/service/CompanyService.java
index 1f76b0e..a1bf356 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/service/CompanyService.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/service/CompanyService.java
@@ -141,7 +141,6 @@
private void mapEmail( ContactPersonDto dto, Map<UUID, TblContactPerson> cpMap ) {
- // TODO: Move to Mapper
TblCommunication tblComm = cpMap.get(dto.getContactUuid()).getContact()
.getCommunications().stream()
.filter(comm -> comm.getRefCommunicationType().isTypeEmail())
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/service/ContactPersonService.java b/src/main/java/org/eclipse/openk/contactbasedata/service/ContactPersonService.java
index 05adbda..35b9f72 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/service/ContactPersonService.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/service/ContactPersonService.java
@@ -117,7 +117,6 @@
.filter(comm -> comm.getRefCommunicationType().isTypeEmail())
.findFirst().orElse(null);
- // TODO: Move to Mapper
destDto.setEmail(tblComm != null ? tblComm.getCommunicationData() : null );
return destDto;
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/service/UserService.java b/src/main/java/org/eclipse/openk/contactbasedata/service/UserService.java
index 1d946ca..a5a1408 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/service/UserService.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/service/UserService.java
@@ -18,7 +18,6 @@
import org.eclipse.openk.contactbasedata.model.KeyCloakUser;
import org.eclipse.openk.contactbasedata.viewmodel.UserModule;
import org.springframework.beans.factory.annotation.Autowired;
-import org.springframework.beans.factory.annotation.Value;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/viewmodel/CommunicationTypeDto.java b/src/main/java/org/eclipse/openk/contactbasedata/viewmodel/CommunicationTypeDto.java
index 20926b6..ae40d0a 100644
--- a/src/main/java/org/eclipse/openk/contactbasedata/viewmodel/CommunicationTypeDto.java
+++ b/src/main/java/org/eclipse/openk/contactbasedata/viewmodel/CommunicationTypeDto.java
@@ -14,6 +14,7 @@
*/
package org.eclipse.openk.contactbasedata.viewmodel;
+import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@@ -25,6 +26,9 @@
@Data
public class CommunicationTypeDto implements Serializable {
+ @JsonIgnore
+ private Long id;
+
@JsonProperty("id")
private UUID uuid;
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index 557f8a3..4385016 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -78,9 +78,6 @@
cors:
corsEnabled: false
-logging:
- file: contact-base-data.log
-
---
spring:
@@ -138,8 +135,8 @@
lastname: true
firstname: true
scheduling:
- enabled: true
- cron-expression: '*/10 * * * * *'
+ enabled: false
+ cron-expression: '*/12 * * * * *'
technical-userpassword: admin
technical-username: admin
diff --git a/src/main/resources/application_localdev.yml b/src/main/resources/application_localdev.yml
index df6c57e..cbd9380 100644
--- a/src/main/resources/application_localdev.yml
+++ b/src/main/resources/application_localdev.yml
@@ -76,6 +76,3 @@
cors:
corsEnabled: false
-logging:
- file: contact-base-data.log
-
diff --git a/src/main/resources/db/migration/V0_16__CREATE_CBD_DB.sql b/src/main/resources/db/migration/V0_16__CREATE_CBD_DB.sql
new file mode 100644
index 0000000..13f1439
--- /dev/null
+++ b/src/main/resources/db/migration/V0_16__CREATE_CBD_DB.sql
@@ -0,0 +1,711 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+-- CREATE ROLE CBD_SERVICE LOGIN
+-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
+-- ALTER ROLE CBD_SERVICE with password 'cbd_service';
+-- Insert new Columns into table REF_COMMUNICATION_TYPE ('EDITABLE, 'MAPPING_LDAP')
+
+-- ---------------------------------------------
+-- DROPS
+-- ---------------------------------------------
+DROP TABLE IF EXISTS public.VERSION CASCADE;
+
+DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMMUNICATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMMUNICATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMPANY CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMPANY_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_EXTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_EXTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_INTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_INTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_ADDRESS_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_ADDRESS_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_PERSON_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_PERSON_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_COMMUNICATION_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_COMMUNICATION_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_SALUTATION CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_SALUTATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT_ID_SEQ;
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE public.VERSION
+(
+ ID integer NOT NULL,
+ VERSION character varying(50) NOT NULL,
+ CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.VERSION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VERSION TO CBD_SERVICE;
+
+INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ CONTACT_TYPE character varying(3),
+ NOTE character varying(255),
+ ANONYMIZED boolean,
+ CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY (ID)
+);
+
+
+
+ALTER TABLE public.TBL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e', 'COM', 'company 1 (id=1)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fc7f598b-0d51-46bb-9563-99851fe6a3ad', 'COM', 'company 2 (id=2)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '556b91be-6d57-432f-93ed-65604dd6e5cd', 'C_P', 'contact person 1 (id=3)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '116380e3-25c5-4179-b40a-8abebe10fe07', 'C_P', 'contact person 2 (id=4)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7782179b-fb79-4370-8f71-f4c71470d006', 'I_P', 'internal person 1 (id=5)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8963aa38-d021-4dc9-bd70-d3734ccd20c4', 'I_P', 'internal person 2 (id=6)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'c862d604-5766-43d6-a7e8-a4bac2bd01e1', 'E_P', 'external person 1 (id=7)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fa3d981b-a7d6-4965-a623-cdbc69404153', 'E_P', 'external person 2 (id=8)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ceba21e9-e685-483b-840e-ad167860a696', 'COM', 'anonymous company A (id=9)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7889c814-9752-4e4e-a9fe-b46f36a38ccd', 'COM', 'anonymous company B (id=10)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '618a01a3-f348-44cc-9ddd-c9df946b0212', 'C_P', 'anoymous contact person A1 (id=11)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '4ed82474-3878-457a-baef-c28b8e486f25', 'C_P', 'anoymous contact persion A2 (id=12)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8fe41b90-d10c-4a70-8fde-0990286ad3c6', 'I_P', 'internal person 3 (id=13)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ab804610-d6a4-4803-a4a1-3f6cb742b2a4', 'I_P', 'internal person 4 (id=14)', null );
+
+
+CREATE UNIQUE INDEX idx_tbl_contact_contact_type ON public.TBL_CONTACT ( ID ASC );
+CREATE UNIQUE INDEX idx_cntct_uuid ON public.TBL_CONTACT (UUID);
+CREATE INDEX idx_cntct_anonym ON public.TBL_CONTACT (ANONYMIZED);
+
+
+-- ---------------------------------------------
+-- TABLE REF_ADDRESS_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_address_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_address_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_ADDRESS_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_address_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY (ID)
+);
+
+ALTER TABLE public.REF_ADDRESS_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_ADDRESS_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_address_type_uuid ON public.REF_ADDRESS_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '3802e681-9396-434e-b19c-5fedcec40ba7', 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79', 'Privatadresse', 'private Anschrift' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '70fd0811-f674-4f3a-96a7-7ae29fc95188', 'Lieferadresse', 'Adresse für Lieferungen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_PERSON_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_person_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_person_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_PERSON_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_person_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_PERSON_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_PERSON_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_person_type_uuid ON public.REF_PERSON_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '47ce68b7-6d44-453e-b421-19020fd791b5', 'Rechtsanwalt', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'a7522c72-14d0-4e9d-afe3-bfcb3ffbec10', 'Geschäftsführer', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '2eb4885e-7363-4918-90ed-b7d5d84cfd3f', 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_COMMUNICATION_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_communication_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_communication_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_COMMUNICATION_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_communication_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ EDITABLE boolean,
+ MAPPING_LDAP boolean,
+ TYPE_EMAIL boolean,
+ CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_COMMUNICATION_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_COMMUNICATION_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_communication_type_uuid ON public.REF_COMMUNICATION_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '4757ca3a-72c2-4f13-a2f6-ce092e3eadf4', 'E-Mail', 'E-Mail Adresse', false, true, true );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '77028572-ff57-4c1d-999a-78fa3fcbc1cd', 'Mobil', '', false, true, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'f7d5b343-00c2-4d7f-8e03-009aad3d90f7', 'Festnetz', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '2bfe40f9-c4eb-4d2e-855f-6b0883912846', 'Fax', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'd00d1a61-c8e7-43b2-959f-66e986731441', 'WhatsApp', '', true, false, false );
+
+-- ---------------------------------------------
+-- TABLE REF_SALUTATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_salutation_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_salutation_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_SALUTATION
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_salutation_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_SALUTATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_SALUTATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_salutation_uuid ON public.REF_SALUTATION ( UUID ASC );
+
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '90119f18-5562-425d-9a36-3dd58ea125e5', 'Herr', 'Anrede männlich' );
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '4e873baa-e4f5-4585-8b16-2db8fac66538', 'Frau', 'Anrede weiblich' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_address_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_address_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ADDRESS
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_address_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_ADDRESS_TYPE bigint,
+ IS_MAIN_ADDRESS boolean,
+ POSTCODE character varying(30),
+ COMMUNITY character varying(255),
+ COMMUNITY_SUFFIX character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ WGS_84_ZONE character varying(255),
+ LATITUDE character varying(255),
+ LONGITUDE character varying(255),
+ URL_MAP character varying(255),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_ADDRESS__ADDRESS_TYPE_ID_FKEY FOREIGN KEY (FK_ADDRESS_TYPE)
+ REFERENCES public.REF_ADDRESS_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_ADDRESS
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_ADDRESS TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_address_uuid ON public.TBL_ADDRESS ( UUID ASC );
+
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '37e800fe-64f0-4834-8b83-8453cbb936a5', 2, 1, true, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar');
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '8a1202ae-2532-474e-8367-a1f0e13e9fbd', 1, 2, false, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr');
+
+
+-- ---------------------------------------------
+-- TABLE TBL_COMMUNICATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_communication_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_communication_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_COMMUNICATION
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_communication_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMMUNICATION_TYPE bigint,
+ COMMUNICATION_DATA character varying(1024),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_COMMUNICATION__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_COMMUNICATION__COMMUNICATION_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE)
+ REFERENCES public.REF_COMMUNICATION_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMMUNICATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMMUNICATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_communication_uuid ON public.TBL_COMMUNICATION ( UUID ASC );
+
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '25f6d7cc-b168-4dd5-a36d-6f14b2f956e9', 2, 2, 'bitte melden Sie sich bei uns', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'a5fa380e-8f33-4ea7-9416-e03d11b91cae', 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'c0dcef80-ca07-48b7-a3ed-2c99c4388928', 1, 1, 'info@bigbang.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'd19d9e10-d2dd-4383-84ec-2fe96421c0a3', 2, 1, 'info@pharmapeekltd.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '234b63e4-d8db-48ab-899f-0320903c01af', 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '11e25c02-de00-430d-b6cd-f02f7c60e026', 5, 1, 'PaulineF@gmx.net', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '9ca29c3b-e189-4ce9-9401-15001c769627', 6, 1, 'mo@gmail.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '05505b1a-61df-45c0-b006-64165cbadfa2', 7, 1, 'MGruebelSport@fogger.mil', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'b52dabaf-d156-4fd0-a07c-510673112a15', 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...');
+
+-- ---------------------------------------------
+-- TABLE TBL_COMPANY
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_company_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_company_id_seq
+ OWNER TO CBD_SERVICE;
+
+
+CREATE TABLE public.TBL_COMPANY
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_company_id_seq'::regclass),
+ COMPANY_NAME character varying(255),
+ COMPANY_TYPE character varying(30),
+ HR_NUMBER character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMPANY
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMPANY TO CBD_SERVICE;
+
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMPANY_ID bigint NOT NULL,
+ CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_CONTACT_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID)
+ REFERENCES public.TBL_COMPANY (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_CONTACT_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2);
+
+-- ---------------------------------------------
+-- TABLE TBL_EXTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_external_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_external_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_EXTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_external_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_EXTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_EXTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_EXTERNAL_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7);
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8);
+
+
+-- ---------------------------------------------
+-- TABLE TBL_INTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_internal_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_internal_person_id_seq
+ OWNER TO CBD_SERVICE;
+CREATE TABLE public.TBL_INTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_internal_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ DEPARTMENT character varying(255),
+ UID character varying(255),
+ USER_REF character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_INTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_INTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_INTERNAL_PERSON TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON public.TBL_INTERNAL_PERSON ( UID ASC );
+CREATE UNIQUE INDEX idx_tbl_internal_person_user_ref ON public.TBL_INTERNAL_PERSON ( USER_REF ASC );
+
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14);
+-- ---------------------------------------------
+-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_assignment_modul_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_assignment_modul_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ASSIGNMENT_MODUL_CONTACT
+(
+ id bigint NOT NULL DEFAULT nextval('tbl_assignment_modul_contact_id_seq'::regclass),
+ uuid uuid NOT NULL,
+ fk_contact_id bigint NOT NULL,
+ modul_name character varying(255),
+ assignment_date timestamp,
+ expiring_date timestamp,
+ deletion_lock_until timestamp,
+ assignment_note character varying(2048),
+ CONSTRAINT tbl_assignment_modul_contact_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_assignment_modul_contact_id_fkey FOREIGN KEY (fk_contact_id)
+ REFERENCES public.tbl_contact (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_assignment_modul_contact
+ OWNER TO cbd_service;
+GRANT ALL ON TABLE public.tbl_assignment_modul_contact TO cbd_service;
+
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( 'dfc2979c-40e3-11ea-b77f-2e728ce88125', 1, 'Betriebstagebuch', '2020-01-19 10:23:54', '2021-01-19 00:00:00', '2021-01-19 00:00:00', 'seit Januar zugeordnet');
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( '4009bce2-40e5-11ea-b77f-2e728ce88125', 1, 'Störinfos', '2020-02-27 10:23:54', '2019-02-27 00:00:00', '2019-02-27 00:00:00', 'seit Februar zugeordnet');
+
+-- -------------------------------------
+-- VIEWS -------------------------------
+-- -------------------------------------
+DROP VIEW IF EXISTS VW_GENERAL_CONTACT CASCADE;
+
+CREATE VIEW VW_GENERAL_CONTACT
+AS
+SELECT g.id,
+ c.uuid,
+ g.name,
+ c.contact_type,
+ g.fk_contact_id,
+ g.company_name,
+ g.company_type,
+ g.company_id,
+ g.fk_salutation_id,
+ g.fk_ref_person_type_id,
+ g.title,
+ g.first_name,
+ g.last_name,
+ g.department,
+ c.note,
+ c.anonymized
+FROM tbl_contact c
+INNER JOIN (
+
+SELECT company.id,
+ company_name as name,
+ fk_contact_id,
+ company_name,
+ company_type,
+ cntct.uuid as company_id,
+ null as fk_salutation_id,
+ null as fk_ref_person_type_id,
+ null as title,
+ null as first_name,
+ null as last_name,
+ null as department
+FROM tbl_company company
+INNER JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id
+
+UNION
+
+SELECT p.id,
+ COALESCE(p.last_name, '')
+ || CASE WHEN p.last_name is NOT NULL AND p.first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name,
+ p.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ company_contact.uuid as company_id,
+ p.fk_salutation_id,
+ p.fk_ref_person_type_id,
+ p.title,
+ p.first_name,
+ p.last_name,
+ null as department
+FROM tbl_contact_person p
+INNER JOIN tbl_company c ON c.id = p.fk_company_id
+INNER JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN last_name is NOT NULL AND first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ department
+FROM tbl_internal_person
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN last_name is NOT NULL AND first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ null as department
+FROM tbl_external_person
+ ) g
+ON g.fk_contact_id = c.ID;
+
+ALTER VIEW public.VW_GENERAL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_GENERAL_CONTACT TO CBD_SERVICE;
+
+
+DROP VIEW IF EXISTS VW_DETAILED_CONTACT;
+
+CREATE VIEW VW_DETAILED_CONTACT
+AS
+SELECT c.id,
+ c.uuid,
+ c.name,
+ c.contact_type,
+ c.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ c.company_id,
+ c.anonymized,
+ s.uuid as salutation_uuid,
+ t.uuid as person_type_uuid,
+ c.title,
+ c.first_name,
+ c.last_name,
+ c.department,
+ c.note,
+ s.type as salutation_type,
+ t.type as person_type,
+ a.street,
+ a.housenumber,
+ a.community,
+ com.communication_data as email,
+
+ UPPER(
+ COALESCE(c.name, '') || '|@|'
+ || COALESCE(company_name, '') || '|@|'
+ || COALESCE(c.company_type, '') || '|@|'
+ || COALESCE(c.title, '') || '|@|'
+ || COALESCE(c.first_name, '') || '|@|'
+ || COALESCE(c.last_name, '') || '|@|'
+ || COALESCE(c.department, '') || '|@|'
+ || COALESCE(c.note, '') || '|@|'
+ || COALESCE(s.type, '') || '|@|'
+ || COALESCE(t.type, '') || '|@|'
+ || COALESCE(a.street, '') || '|@|'
+ || COALESCE(a.housenumber, '') || '|@|'
+ || COALESCE(a.community, '') || '|@|'
+ || COALESCE(com.communication_data, '')
+ )as searchfield
+FROM VW_GENERAL_CONTACT c
+LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id
+LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = true
+LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id
+LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id AND com.fk_communication_type =
+ (SELECT ct.id FROM ref_communication_type ct WHERE ct.type_email = true)) ;
+
+ALTER VIEW public.VW_DETAILED_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_DETAILED_CONTACT TO CBD_SERVICE;
+
+
+
+
diff --git a/src/main/resources/db/migration/V0_17__CREATE_CBD_DB.sql b/src/main/resources/db/migration/V0_17__CREATE_CBD_DB.sql
new file mode 100644
index 0000000..ba559dd
--- /dev/null
+++ b/src/main/resources/db/migration/V0_17__CREATE_CBD_DB.sql
@@ -0,0 +1,711 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+-- CREATE ROLE CBD_SERVICE LOGIN
+-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
+-- ALTER ROLE CBD_SERVICE with password 'cbd_service';
+-- Insert new Columns into table REF_COMMUNICATION_TYPE ('EDITABLE, 'MAPPING_LDAP')
+
+-- ---------------------------------------------
+-- DROPS
+-- ---------------------------------------------
+DROP TABLE IF EXISTS public.VERSION CASCADE;
+
+DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMMUNICATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMMUNICATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMPANY CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMPANY_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_EXTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_EXTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_INTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_INTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_ADDRESS_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_ADDRESS_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_PERSON_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_PERSON_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_COMMUNICATION_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_COMMUNICATION_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_SALUTATION CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_SALUTATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT_ID_SEQ;
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE public.VERSION
+(
+ ID integer NOT NULL,
+ VERSION character varying(50) NOT NULL,
+ CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.VERSION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VERSION TO CBD_SERVICE;
+
+INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ CONTACT_TYPE character varying(3),
+ NOTE character varying(255),
+ ANONYMIZED boolean,
+ CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY (ID)
+);
+
+
+
+ALTER TABLE public.TBL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e', 'COM', 'company 1 (id=1)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fc7f598b-0d51-46bb-9563-99851fe6a3ad', 'COM', 'company 2 (id=2)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '556b91be-6d57-432f-93ed-65604dd6e5cd', 'C_P', 'contact person 1 (id=3)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '116380e3-25c5-4179-b40a-8abebe10fe07', 'C_P', 'contact person 2 (id=4)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7782179b-fb79-4370-8f71-f4c71470d006', 'I_P', 'internal person 1 (id=5)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8963aa38-d021-4dc9-bd70-d3734ccd20c4', 'I_P', 'internal person 2 (id=6)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'c862d604-5766-43d6-a7e8-a4bac2bd01e1', 'E_P', 'external person 1 (id=7)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fa3d981b-a7d6-4965-a623-cdbc69404153', 'E_P', 'external person 2 (id=8)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ceba21e9-e685-483b-840e-ad167860a696', 'COM', 'anonymous company A (id=9)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7889c814-9752-4e4e-a9fe-b46f36a38ccd', 'COM', 'anonymous company B (id=10)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '618a01a3-f348-44cc-9ddd-c9df946b0212', 'C_P', 'anoymous contact person A1 (id=11)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '4ed82474-3878-457a-baef-c28b8e486f25', 'C_P', 'anoymous contact persion A2 (id=12)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8fe41b90-d10c-4a70-8fde-0990286ad3c6', 'I_P', 'internal person 3 (id=13)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ab804610-d6a4-4803-a4a1-3f6cb742b2a4', 'I_P', 'internal person 4 (id=14)', null );
+
+
+CREATE UNIQUE INDEX idx_tbl_contact_contact_type ON public.TBL_CONTACT ( ID ASC );
+CREATE UNIQUE INDEX idx_cntct_uuid ON public.TBL_CONTACT (UUID);
+CREATE INDEX idx_cntct_anonym ON public.TBL_CONTACT (ANONYMIZED);
+
+
+-- ---------------------------------------------
+-- TABLE REF_ADDRESS_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_address_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_address_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_ADDRESS_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_address_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY (ID)
+);
+
+ALTER TABLE public.REF_ADDRESS_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_ADDRESS_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_address_type_uuid ON public.REF_ADDRESS_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '3802e681-9396-434e-b19c-5fedcec40ba7', 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79', 'Privatadresse', 'private Anschrift' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '70fd0811-f674-4f3a-96a7-7ae29fc95188', 'Lieferadresse', 'Adresse für Lieferungen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_PERSON_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_person_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_person_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_PERSON_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_person_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_PERSON_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_PERSON_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_person_type_uuid ON public.REF_PERSON_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '47ce68b7-6d44-453e-b421-19020fd791b5', 'Rechtsanwalt', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'a7522c72-14d0-4e9d-afe3-bfcb3ffbec10', 'Geschäftsführer', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '2eb4885e-7363-4918-90ed-b7d5d84cfd3f', 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_COMMUNICATION_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_communication_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_communication_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_COMMUNICATION_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_communication_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ EDITABLE boolean,
+ MAPPING_LDAP boolean,
+ TYPE_EMAIL boolean,
+ CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_COMMUNICATION_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_COMMUNICATION_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_communication_type_uuid ON public.REF_COMMUNICATION_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '4757ca3a-72c2-4f13-a2f6-ce092e3eadf4', 'E-Mail', 'E-Mail Adresse', false, true, true );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '77028572-ff57-4c1d-999a-78fa3fcbc1cd', 'Mobil', '', false, true, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'f7d5b343-00c2-4d7f-8e03-009aad3d90f7', 'Festnetz', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '2bfe40f9-c4eb-4d2e-855f-6b0883912846', 'Fax', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'd00d1a61-c8e7-43b2-959f-66e986731441', 'WhatsApp', '', true, false, false );
+
+-- ---------------------------------------------
+-- TABLE REF_SALUTATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_salutation_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_salutation_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_SALUTATION
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_salutation_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_SALUTATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_SALUTATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_salutation_uuid ON public.REF_SALUTATION ( UUID ASC );
+
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '90119f18-5562-425d-9a36-3dd58ea125e5', 'Herr', 'Anrede männlich' );
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '4e873baa-e4f5-4585-8b16-2db8fac66538', 'Frau', 'Anrede weiblich' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_address_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_address_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ADDRESS
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_address_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_ADDRESS_TYPE bigint,
+ IS_MAIN_ADDRESS boolean,
+ POSTCODE character varying(30),
+ COMMUNITY character varying(255),
+ COMMUNITY_SUFFIX character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ WGS_84_ZONE character varying(255),
+ LATITUDE character varying(255),
+ LONGITUDE character varying(255),
+ URL_MAP character varying(255),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_ADDRESS__ADDRESS_TYPE_ID_FKEY FOREIGN KEY (FK_ADDRESS_TYPE)
+ REFERENCES public.REF_ADDRESS_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_ADDRESS
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_ADDRESS TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_address_uuid ON public.TBL_ADDRESS ( UUID ASC );
+
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '37e800fe-64f0-4834-8b83-8453cbb936a5', 2, 1, true, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar');
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '8a1202ae-2532-474e-8367-a1f0e13e9fbd', 1, 2, false, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr');
+
+
+-- ---------------------------------------------
+-- TABLE TBL_COMMUNICATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_communication_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_communication_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_COMMUNICATION
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_communication_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMMUNICATION_TYPE bigint,
+ COMMUNICATION_DATA character varying(1024),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_COMMUNICATION__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_COMMUNICATION__COMMUNICATION_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE)
+ REFERENCES public.REF_COMMUNICATION_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMMUNICATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMMUNICATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_communication_uuid ON public.TBL_COMMUNICATION ( UUID ASC );
+
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '25f6d7cc-b168-4dd5-a36d-6f14b2f956e9', 2, 2, 'bitte melden Sie sich bei uns', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'a5fa380e-8f33-4ea7-9416-e03d11b91cae', 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'c0dcef80-ca07-48b7-a3ed-2c99c4388928', 1, 1, 'info@bigbang.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'd19d9e10-d2dd-4383-84ec-2fe96421c0a3', 2, 1, 'info@pharmapeekltd.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '234b63e4-d8db-48ab-899f-0320903c01af', 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '11e25c02-de00-430d-b6cd-f02f7c60e026', 5, 1, 'PaulineF@gmx.net', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '9ca29c3b-e189-4ce9-9401-15001c769627', 6, 1, 'mo@gmail.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '05505b1a-61df-45c0-b006-64165cbadfa2', 7, 1, 'MGruebelSport@fogger.mil', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'b52dabaf-d156-4fd0-a07c-510673112a15', 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...');
+
+-- ---------------------------------------------
+-- TABLE TBL_COMPANY
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_company_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_company_id_seq
+ OWNER TO CBD_SERVICE;
+
+
+CREATE TABLE public.TBL_COMPANY
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_company_id_seq'::regclass),
+ COMPANY_NAME character varying(255),
+ COMPANY_TYPE character varying(30),
+ HR_NUMBER character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMPANY
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMPANY TO CBD_SERVICE;
+
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMPANY_ID bigint NOT NULL,
+ CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_CONTACT_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID)
+ REFERENCES public.TBL_COMPANY (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_CONTACT_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2);
+
+-- ---------------------------------------------
+-- TABLE TBL_EXTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_external_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_external_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_EXTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_external_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_EXTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_EXTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_EXTERNAL_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7);
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8);
+
+
+-- ---------------------------------------------
+-- TABLE TBL_INTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_internal_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_internal_person_id_seq
+ OWNER TO CBD_SERVICE;
+CREATE TABLE public.TBL_INTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_internal_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ DEPARTMENT character varying(255),
+ UID character varying(255),
+ USER_REF character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_INTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_INTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_INTERNAL_PERSON TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON public.TBL_INTERNAL_PERSON ( UID ASC );
+CREATE UNIQUE INDEX idx_tbl_internal_person_user_ref ON public.TBL_INTERNAL_PERSON ( USER_REF ASC );
+
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14);
+-- ---------------------------------------------
+-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_assignment_modul_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_assignment_modul_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ASSIGNMENT_MODUL_CONTACT
+(
+ id bigint NOT NULL DEFAULT nextval('tbl_assignment_modul_contact_id_seq'::regclass),
+ uuid uuid NOT NULL,
+ fk_contact_id bigint NOT NULL,
+ modul_name character varying(255),
+ assignment_date timestamp,
+ expiring_date timestamp,
+ deletion_lock_until timestamp,
+ assignment_note character varying(2048),
+ CONSTRAINT tbl_assignment_modul_contact_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_assignment_modul_contact_id_fkey FOREIGN KEY (fk_contact_id)
+ REFERENCES public.tbl_contact (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_assignment_modul_contact
+ OWNER TO cbd_service;
+GRANT ALL ON TABLE public.tbl_assignment_modul_contact TO cbd_service;
+
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( 'dfc2979c-40e3-11ea-b77f-2e728ce88125', 1, 'Betriebstagebuch', '2020-01-19 10:23:54', '2021-01-19 00:00:00', '2021-01-19 00:00:00', 'seit Januar zugeordnet');
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( '4009bce2-40e5-11ea-b77f-2e728ce88125', 1, 'Störinfos', '2020-02-27 10:23:54', '2019-02-27 00:00:00', '2019-02-27 00:00:00', 'seit Februar zugeordnet');
+
+-- -------------------------------------
+-- VIEWS -------------------------------
+-- -------------------------------------
+DROP VIEW IF EXISTS VW_GENERAL_CONTACT CASCADE;
+
+CREATE VIEW VW_GENERAL_CONTACT
+AS
+SELECT g.id,
+ c.uuid,
+ g.name,
+ c.contact_type,
+ g.fk_contact_id,
+ g.company_name,
+ g.company_type,
+ g.company_id,
+ g.fk_salutation_id,
+ g.fk_ref_person_type_id,
+ g.title,
+ g.first_name,
+ g.last_name,
+ g.department,
+ c.note,
+ c.anonymized
+FROM tbl_contact c
+INNER JOIN (
+
+SELECT company.id,
+ company_name as name,
+ fk_contact_id,
+ company_name,
+ company_type,
+ cntct.uuid as company_id,
+ null as fk_salutation_id,
+ null as fk_ref_person_type_id,
+ null as title,
+ null as first_name,
+ null as last_name,
+ null as department
+FROM tbl_company company
+INNER JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id
+
+UNION
+
+SELECT p.id,
+ COALESCE(p.last_name, '')
+ || CASE WHEN p.last_name is NOT NULL OR p.first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name,
+ p.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ company_contact.uuid as company_id,
+ p.fk_salutation_id,
+ p.fk_ref_person_type_id,
+ p.title,
+ p.first_name,
+ p.last_name,
+ null as department
+FROM tbl_contact_person p
+INNER JOIN tbl_company c ON c.id = p.fk_company_id
+INNER JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN last_name is NOT NULL OR first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ department
+FROM tbl_internal_person
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN last_name is NOT NULL OR first_name IS NOT NULL THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ null as department
+FROM tbl_external_person
+ ) g
+ON g.fk_contact_id = c.ID;
+
+ALTER VIEW public.VW_GENERAL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_GENERAL_CONTACT TO CBD_SERVICE;
+
+
+DROP VIEW IF EXISTS VW_DETAILED_CONTACT;
+
+CREATE VIEW VW_DETAILED_CONTACT
+AS
+SELECT c.id,
+ c.uuid,
+ c.name,
+ c.contact_type,
+ c.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ c.company_id,
+ c.anonymized,
+ s.uuid as salutation_uuid,
+ t.uuid as person_type_uuid,
+ c.title,
+ c.first_name,
+ c.last_name,
+ c.department,
+ c.note,
+ s.type as salutation_type,
+ t.type as person_type,
+ a.street,
+ a.housenumber,
+ a.community,
+ com.communication_data as email,
+
+ UPPER(
+ COALESCE(c.name, '') || '|@|'
+ || COALESCE(company_name, '') || '|@|'
+ || COALESCE(c.company_type, '') || '|@|'
+ || COALESCE(c.title, '') || '|@|'
+ || COALESCE(c.first_name, '') || '|@|'
+ || COALESCE(c.last_name, '') || '|@|'
+ || COALESCE(c.department, '') || '|@|'
+ || COALESCE(c.note, '') || '|@|'
+ || COALESCE(s.type, '') || '|@|'
+ || COALESCE(t.type, '') || '|@|'
+ || COALESCE(a.street, '') || '|@|'
+ || COALESCE(a.housenumber, '') || '|@|'
+ || COALESCE(a.community, '') || '|@|'
+ || COALESCE(com.communication_data, '')
+ )as searchfield
+FROM VW_GENERAL_CONTACT c
+LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id
+LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = true
+LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id
+LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id AND com.fk_communication_type =
+ (SELECT ct.id FROM ref_communication_type ct WHERE ct.type_email = true)) ;
+
+ALTER VIEW public.VW_DETAILED_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_DETAILED_CONTACT TO CBD_SERVICE;
+
+
+
+
diff --git a/src/main/resources/db/migration/V0_18__CREATE_CBD_DB.sql b/src/main/resources/db/migration/V0_18__CREATE_CBD_DB.sql
new file mode 100644
index 0000000..03b1ac9
--- /dev/null
+++ b/src/main/resources/db/migration/V0_18__CREATE_CBD_DB.sql
@@ -0,0 +1,711 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+-- CREATE ROLE CBD_SERVICE LOGIN
+-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
+-- ALTER ROLE CBD_SERVICE with password 'cbd_service';
+-- Insert new Columns into table REF_COMMUNICATION_TYPE ('EDITABLE, 'MAPPING_LDAP')
+
+-- ---------------------------------------------
+-- DROPS
+-- ---------------------------------------------
+DROP TABLE IF EXISTS public.VERSION CASCADE;
+
+DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMMUNICATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMMUNICATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMPANY CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMPANY_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_EXTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_EXTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_INTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_INTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_ADDRESS_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_ADDRESS_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_PERSON_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_PERSON_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_COMMUNICATION_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_COMMUNICATION_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_SALUTATION CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_SALUTATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT_ID_SEQ;
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE public.VERSION
+(
+ ID integer NOT NULL,
+ VERSION character varying(50) NOT NULL,
+ CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.VERSION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VERSION TO CBD_SERVICE;
+
+INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ CONTACT_TYPE character varying(3),
+ NOTE character varying(255),
+ ANONYMIZED boolean,
+ CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY (ID)
+);
+
+
+
+ALTER TABLE public.TBL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e', 'COM', 'company 1 (id=1)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fc7f598b-0d51-46bb-9563-99851fe6a3ad', 'COM', 'company 2 (id=2)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '556b91be-6d57-432f-93ed-65604dd6e5cd', 'C_P', 'contact person 1 (id=3)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '116380e3-25c5-4179-b40a-8abebe10fe07', 'C_P', 'contact person 2 (id=4)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7782179b-fb79-4370-8f71-f4c71470d006', 'I_P', 'internal person 1 (id=5)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8963aa38-d021-4dc9-bd70-d3734ccd20c4', 'I_P', 'internal person 2 (id=6)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'c862d604-5766-43d6-a7e8-a4bac2bd01e1', 'E_P', 'external person 1 (id=7)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fa3d981b-a7d6-4965-a623-cdbc69404153', 'E_P', 'external person 2 (id=8)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ceba21e9-e685-483b-840e-ad167860a696', 'COM', 'anonymous company A (id=9)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7889c814-9752-4e4e-a9fe-b46f36a38ccd', 'COM', 'anonymous company B (id=10)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '618a01a3-f348-44cc-9ddd-c9df946b0212', 'C_P', 'anoymous contact person A1 (id=11)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '4ed82474-3878-457a-baef-c28b8e486f25', 'C_P', 'anoymous contact persion A2 (id=12)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8fe41b90-d10c-4a70-8fde-0990286ad3c6', 'I_P', 'internal person 3 (id=13)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ab804610-d6a4-4803-a4a1-3f6cb742b2a4', 'I_P', 'internal person 4 (id=14)', null );
+
+
+CREATE UNIQUE INDEX idx_tbl_contact_contact_type ON public.TBL_CONTACT ( ID ASC );
+CREATE UNIQUE INDEX idx_cntct_uuid ON public.TBL_CONTACT (UUID);
+CREATE INDEX idx_cntct_anonym ON public.TBL_CONTACT (ANONYMIZED);
+
+
+-- ---------------------------------------------
+-- TABLE REF_ADDRESS_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_address_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_address_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_ADDRESS_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_address_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY (ID)
+);
+
+ALTER TABLE public.REF_ADDRESS_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_ADDRESS_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_address_type_uuid ON public.REF_ADDRESS_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '3802e681-9396-434e-b19c-5fedcec40ba7', 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79', 'Privatadresse', 'private Anschrift' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '70fd0811-f674-4f3a-96a7-7ae29fc95188', 'Lieferadresse', 'Adresse für Lieferungen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_PERSON_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_person_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_person_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_PERSON_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_person_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_PERSON_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_PERSON_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_person_type_uuid ON public.REF_PERSON_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '47ce68b7-6d44-453e-b421-19020fd791b5', 'Rechtsanwalt', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'a7522c72-14d0-4e9d-afe3-bfcb3ffbec10', 'Geschäftsführer', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '2eb4885e-7363-4918-90ed-b7d5d84cfd3f', 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_COMMUNICATION_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_communication_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_communication_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_COMMUNICATION_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_communication_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ EDITABLE boolean,
+ MAPPING_LDAP boolean,
+ TYPE_EMAIL boolean,
+ CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_COMMUNICATION_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_COMMUNICATION_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_communication_type_uuid ON public.REF_COMMUNICATION_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '4757ca3a-72c2-4f13-a2f6-ce092e3eadf4', 'E-Mail', 'E-Mail Adresse', false, true, true );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '77028572-ff57-4c1d-999a-78fa3fcbc1cd', 'Mobil', '', false, true, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'f7d5b343-00c2-4d7f-8e03-009aad3d90f7', 'Festnetz', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( '2bfe40f9-c4eb-4d2e-855f-6b0883912846', 'Fax', '', true, false, false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, EDITABLE, MAPPING_LDAP, TYPE_EMAIL) VALUES ( 'd00d1a61-c8e7-43b2-959f-66e986731441', 'WhatsApp', '', true, false, false );
+
+-- ---------------------------------------------
+-- TABLE REF_SALUTATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_salutation_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_salutation_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_SALUTATION
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_salutation_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_SALUTATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_SALUTATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_salutation_uuid ON public.REF_SALUTATION ( UUID ASC );
+
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '90119f18-5562-425d-9a36-3dd58ea125e5', 'Herr', 'Anrede männlich' );
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '4e873baa-e4f5-4585-8b16-2db8fac66538', 'Frau', 'Anrede weiblich' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_address_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_address_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ADDRESS
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_address_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_ADDRESS_TYPE bigint,
+ IS_MAIN_ADDRESS boolean,
+ POSTCODE character varying(30),
+ COMMUNITY character varying(255),
+ COMMUNITY_SUFFIX character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ WGS_84_ZONE character varying(255),
+ LATITUDE character varying(255),
+ LONGITUDE character varying(255),
+ URL_MAP character varying(255),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_ADDRESS__ADDRESS_TYPE_ID_FKEY FOREIGN KEY (FK_ADDRESS_TYPE)
+ REFERENCES public.REF_ADDRESS_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_ADDRESS
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_ADDRESS TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_address_uuid ON public.TBL_ADDRESS ( UUID ASC );
+
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '37e800fe-64f0-4834-8b83-8453cbb936a5', 2, 1, true, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar');
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '8a1202ae-2532-474e-8367-a1f0e13e9fbd', 1, 2, false, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr');
+
+
+-- ---------------------------------------------
+-- TABLE TBL_COMMUNICATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_communication_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_communication_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_COMMUNICATION
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_communication_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMMUNICATION_TYPE bigint,
+ COMMUNICATION_DATA character varying(1024),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_COMMUNICATION__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_COMMUNICATION__COMMUNICATION_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE)
+ REFERENCES public.REF_COMMUNICATION_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMMUNICATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMMUNICATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_communication_uuid ON public.TBL_COMMUNICATION ( UUID ASC );
+
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '25f6d7cc-b168-4dd5-a36d-6f14b2f956e9', 2, 2, 'bitte melden Sie sich bei uns', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'a5fa380e-8f33-4ea7-9416-e03d11b91cae', 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'c0dcef80-ca07-48b7-a3ed-2c99c4388928', 1, 1, 'info@bigbang.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'd19d9e10-d2dd-4383-84ec-2fe96421c0a3', 2, 1, 'info@pharmapeekltd.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '234b63e4-d8db-48ab-899f-0320903c01af', 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '11e25c02-de00-430d-b6cd-f02f7c60e026', 5, 1, 'PaulineF@gmx.net', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '9ca29c3b-e189-4ce9-9401-15001c769627', 6, 1, 'mo@gmail.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '05505b1a-61df-45c0-b006-64165cbadfa2', 7, 1, 'MGruebelSport@fogger.mil', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'b52dabaf-d156-4fd0-a07c-510673112a15', 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...');
+
+-- ---------------------------------------------
+-- TABLE TBL_COMPANY
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_company_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_company_id_seq
+ OWNER TO CBD_SERVICE;
+
+
+CREATE TABLE public.TBL_COMPANY
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_company_id_seq'::regclass),
+ COMPANY_NAME character varying(255),
+ COMPANY_TYPE character varying(30),
+ HR_NUMBER character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMPANY
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMPANY TO CBD_SERVICE;
+
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMPANY_ID bigint NOT NULL,
+ CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_CONTACT_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID)
+ REFERENCES public.TBL_COMPANY (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_CONTACT_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2);
+
+-- ---------------------------------------------
+-- TABLE TBL_EXTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_external_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_external_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_EXTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_external_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_EXTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_EXTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_EXTERNAL_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7);
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8);
+
+
+-- ---------------------------------------------
+-- TABLE TBL_INTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_internal_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_internal_person_id_seq
+ OWNER TO CBD_SERVICE;
+CREATE TABLE public.TBL_INTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_internal_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ DEPARTMENT character varying(255),
+ UID character varying(255),
+ USER_REF character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_INTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_INTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_INTERNAL_PERSON TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON public.TBL_INTERNAL_PERSON ( UID ASC );
+CREATE UNIQUE INDEX idx_tbl_internal_person_user_ref ON public.TBL_INTERNAL_PERSON ( USER_REF ASC );
+
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14);
+-- ---------------------------------------------
+-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_assignment_modul_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_assignment_modul_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ASSIGNMENT_MODUL_CONTACT
+(
+ id bigint NOT NULL DEFAULT nextval('tbl_assignment_modul_contact_id_seq'::regclass),
+ uuid uuid NOT NULL,
+ fk_contact_id bigint NOT NULL,
+ modul_name character varying(255),
+ assignment_date timestamp,
+ expiring_date timestamp,
+ deletion_lock_until timestamp,
+ assignment_note character varying(2048),
+ CONSTRAINT tbl_assignment_modul_contact_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_assignment_modul_contact_id_fkey FOREIGN KEY (fk_contact_id)
+ REFERENCES public.tbl_contact (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_assignment_modul_contact
+ OWNER TO cbd_service;
+GRANT ALL ON TABLE public.tbl_assignment_modul_contact TO cbd_service;
+
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( 'dfc2979c-40e3-11ea-b77f-2e728ce88125', 1, 'Betriebstagebuch', '2020-01-19 10:23:54', '2021-01-19 00:00:00', '2021-01-19 00:00:00', 'seit Januar zugeordnet');
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( '4009bce2-40e5-11ea-b77f-2e728ce88125', 1, 'Störinfos', '2020-02-27 10:23:54', '2019-02-27 00:00:00', '2019-02-27 00:00:00', 'seit Februar zugeordnet');
+
+-- -------------------------------------
+-- VIEWS -------------------------------
+-- -------------------------------------
+DROP VIEW IF EXISTS VW_GENERAL_CONTACT CASCADE;
+
+CREATE VIEW VW_GENERAL_CONTACT
+AS
+SELECT g.id,
+ c.uuid,
+ g.name,
+ c.contact_type,
+ g.fk_contact_id,
+ g.company_name,
+ g.company_type,
+ g.company_id,
+ g.fk_salutation_id,
+ g.fk_ref_person_type_id,
+ g.title,
+ g.first_name,
+ g.last_name,
+ g.department,
+ c.note,
+ c.anonymized
+FROM tbl_contact c
+INNER JOIN (
+
+SELECT company.id,
+ company_name as name,
+ fk_contact_id,
+ company_name,
+ company_type,
+ cntct.uuid as company_id,
+ null as fk_salutation_id,
+ null as fk_ref_person_type_id,
+ null as title,
+ null as first_name,
+ null as last_name,
+ null as department
+FROM tbl_company company
+INNER JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id
+
+UNION
+
+SELECT p.id,
+ COALESCE(p.last_name, '')
+ || CASE WHEN COALESCE(p.last_name, '') <> '' AND COALESCE(p.first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name,
+ p.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ company_contact.uuid as company_id,
+ p.fk_salutation_id,
+ p.fk_ref_person_type_id,
+ p.title,
+ p.first_name,
+ p.last_name,
+ null as department
+FROM tbl_contact_person p
+INNER JOIN tbl_company c ON c.id = p.fk_company_id
+INNER JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ department
+FROM tbl_internal_person
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ null as department
+FROM tbl_external_person
+ ) g
+ON g.fk_contact_id = c.ID;
+
+ALTER VIEW public.VW_GENERAL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_GENERAL_CONTACT TO CBD_SERVICE;
+
+
+DROP VIEW IF EXISTS VW_DETAILED_CONTACT;
+
+CREATE VIEW VW_DETAILED_CONTACT
+AS
+SELECT c.id,
+ c.uuid,
+ c.name,
+ c.contact_type,
+ c.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ c.company_id,
+ c.anonymized,
+ s.uuid as salutation_uuid,
+ t.uuid as person_type_uuid,
+ c.title,
+ c.first_name,
+ c.last_name,
+ c.department,
+ c.note,
+ s.type as salutation_type,
+ t.type as person_type,
+ a.street,
+ a.housenumber,
+ a.community,
+ com.communication_data as email,
+
+ UPPER(
+ COALESCE(c.name, '') || '|@|'
+ || COALESCE(company_name, '') || '|@|'
+ || COALESCE(c.company_type, '') || '|@|'
+ || COALESCE(c.title, '') || '|@|'
+ || COALESCE(c.first_name, '') || '|@|'
+ || COALESCE(c.last_name, '') || '|@|'
+ || COALESCE(c.department, '') || '|@|'
+ || COALESCE(c.note, '') || '|@|'
+ || COALESCE(s.type, '') || '|@|'
+ || COALESCE(t.type, '') || '|@|'
+ || COALESCE(a.street, '') || '|@|'
+ || COALESCE(a.housenumber, '') || '|@|'
+ || COALESCE(a.community, '') || '|@|'
+ || COALESCE(com.communication_data, '')
+ )as searchfield
+FROM VW_GENERAL_CONTACT c
+LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id
+LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = true
+LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id
+LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id AND com.fk_communication_type =
+ (SELECT ct.id FROM ref_communication_type ct WHERE ct.type_email = true)) ;
+
+ALTER VIEW public.VW_DETAILED_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_DETAILED_CONTACT TO CBD_SERVICE;
+
+
+
+
diff --git a/src/main/resources/db/migration/V0_19__CREATE_CBD_DB.sql b/src/main/resources/db/migration/V0_19__CREATE_CBD_DB.sql
new file mode 100644
index 0000000..a6cc25d
--- /dev/null
+++ b/src/main/resources/db/migration/V0_19__CREATE_CBD_DB.sql
@@ -0,0 +1,709 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+-- CREATE ROLE CBD_SERVICE LOGIN
+-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
+-- ALTER ROLE CBD_SERVICE with password 'cbd_service';
+-- Insert new Columns into table REF_COMMUNICATION_TYPE ('EDITABLE, 'MAPPING_LDAP')
+
+-- ---------------------------------------------
+-- DROPS
+-- ---------------------------------------------
+DROP TABLE IF EXISTS public.VERSION CASCADE;
+
+DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMMUNICATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMMUNICATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_COMPANY CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_COMPANY_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_EXTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_EXTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_INTERNAL_PERSON CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_INTERNAL_PERSON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_CONTACT_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_ADDRESS_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_ADDRESS_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_PERSON_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_PERSON_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_COMMUNICATION_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_COMMUNICATION_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_SALUTATION CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_SALUTATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT_ID_SEQ;
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE public.VERSION
+(
+ ID integer NOT NULL,
+ VERSION character varying(50) NOT NULL,
+ CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.VERSION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VERSION TO CBD_SERVICE;
+
+INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ CONTACT_TYPE character varying(3),
+ NOTE character varying(255),
+ ANONYMIZED boolean,
+ CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY (ID)
+);
+
+
+
+ALTER TABLE public.TBL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e', 'COM', 'company 1 (id=1)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fc7f598b-0d51-46bb-9563-99851fe6a3ad', 'COM', 'company 2 (id=2)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '556b91be-6d57-432f-93ed-65604dd6e5cd', 'C_P', 'contact person 1 (id=3)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '116380e3-25c5-4179-b40a-8abebe10fe07', 'C_P', 'contact person 2 (id=4)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7782179b-fb79-4370-8f71-f4c71470d006', 'I_P', 'internal person 1 (id=5)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8963aa38-d021-4dc9-bd70-d3734ccd20c4', 'I_P', 'internal person 2 (id=6)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'c862d604-5766-43d6-a7e8-a4bac2bd01e1', 'E_P', 'external person 1 (id=7)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fa3d981b-a7d6-4965-a623-cdbc69404153', 'E_P', 'external person 2 (id=8)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ceba21e9-e685-483b-840e-ad167860a696', 'COM', 'anonymous company A (id=9)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7889c814-9752-4e4e-a9fe-b46f36a38ccd', 'COM', 'anonymous company B (id=10)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '618a01a3-f348-44cc-9ddd-c9df946b0212', 'C_P', 'anoymous contact person A1 (id=11)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '4ed82474-3878-457a-baef-c28b8e486f25', 'C_P', 'anoymous contact persion A2 (id=12)', true );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8fe41b90-d10c-4a70-8fde-0990286ad3c6', 'I_P', 'internal person 3 (id=13)', null );
+INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ab804610-d6a4-4803-a4a1-3f6cb742b2a4', 'I_P', 'internal person 4 (id=14)', null );
+
+
+CREATE UNIQUE INDEX idx_tbl_contact_contact_type ON public.TBL_CONTACT ( ID ASC );
+CREATE UNIQUE INDEX idx_cntct_uuid ON public.TBL_CONTACT (UUID);
+CREATE INDEX idx_cntct_anonym ON public.TBL_CONTACT (ANONYMIZED);
+
+
+-- ---------------------------------------------
+-- TABLE REF_ADDRESS_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_address_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_address_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_ADDRESS_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_address_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY (ID)
+);
+
+ALTER TABLE public.REF_ADDRESS_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_ADDRESS_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_address_type_uuid ON public.REF_ADDRESS_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '3802e681-9396-434e-b19c-5fedcec40ba7', 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79', 'Privatadresse', 'private Anschrift' );
+INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '70fd0811-f674-4f3a-96a7-7ae29fc95188', 'Lieferadresse', 'Adresse für Lieferungen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_PERSON_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_person_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_person_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_PERSON_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_person_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_PERSON_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_PERSON_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_person_type_uuid ON public.REF_PERSON_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '47ce68b7-6d44-453e-b421-19020fd791b5', 'Rechtsanwalt', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'a7522c72-14d0-4e9d-afe3-bfcb3ffbec10', 'Geschäftsführer', '' );
+INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '2eb4885e-7363-4918-90ed-b7d5d84cfd3f', 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' );
+
+
+-- ---------------------------------------------
+-- TABLE REF_COMMUNICATION_TYPE
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_communication_type_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_communication_type_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_COMMUNICATION_TYPE
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_communication_type_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ TYPE_EMAIL boolean,
+ CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_COMMUNICATION_TYPE
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_COMMUNICATION_TYPE TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_communication_type_uuid ON public.REF_COMMUNICATION_TYPE ( UUID ASC );
+
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '4757ca3a-72c2-4f13-a2f6-ce092e3eadf4', 'E-Mail', 'E-Mail Adresse', true );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '77028572-ff57-4c1d-999a-78fa3fcbc1cd', 'Festnetz', '', false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( 'f7d5b343-00c2-4d7f-8e03-009aad3d90f7', 'Mobil', '', false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '2bfe40f9-c4eb-4d2e-855f-6b0883912846', 'Fax', '', false );
+INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( 'd00d1a61-c8e7-43b2-959f-66e986731441', 'WhatsApp', '', false );
+
+-- ---------------------------------------------
+-- TABLE REF_SALUTATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_salutation_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_salutation_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.REF_SALUTATION
+(
+ ID bigint NOT NULL DEFAULT nextval('ref_salutation_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ TYPE character varying(30),
+ DESCRIPTION character varying(255),
+ CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID)
+);
+ALTER TABLE public.REF_SALUTATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.REF_SALUTATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_ref_salutation_uuid ON public.REF_SALUTATION ( UUID ASC );
+
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '90119f18-5562-425d-9a36-3dd58ea125e5', 'Herr', 'Anrede männlich' );
+INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '4e873baa-e4f5-4585-8b16-2db8fac66538', 'Frau', 'Anrede weiblich' );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_address_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_address_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ADDRESS
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_address_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_ADDRESS_TYPE bigint,
+ IS_MAIN_ADDRESS boolean,
+ POSTCODE character varying(30),
+ COMMUNITY character varying(255),
+ COMMUNITY_SUFFIX character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ WGS_84_ZONE character varying(255),
+ LATITUDE character varying(255),
+ LONGITUDE character varying(255),
+ URL_MAP character varying(255),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_ADDRESS__ADDRESS_TYPE_ID_FKEY FOREIGN KEY (FK_ADDRESS_TYPE)
+ REFERENCES public.REF_ADDRESS_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_ADDRESS
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_ADDRESS TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_address_uuid ON public.TBL_ADDRESS ( UUID ASC );
+
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '37e800fe-64f0-4834-8b83-8453cbb936a5', 2, 1, true, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar');
+INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '8a1202ae-2532-474e-8367-a1f0e13e9fbd', 1, 2, false, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr');
+
+
+-- ---------------------------------------------
+-- TABLE TBL_COMMUNICATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_communication_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_communication_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_COMMUNICATION
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_communication_id_seq'::regclass),
+ UUID uuid NOT NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMMUNICATION_TYPE bigint,
+ COMMUNICATION_DATA character varying(1024),
+ NOTE character varying(255),
+
+ CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_COMMUNICATION__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_COMMUNICATION__COMMUNICATION_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE)
+ REFERENCES public.REF_COMMUNICATION_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMMUNICATION
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMMUNICATION TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_communication_uuid ON public.TBL_COMMUNICATION ( UUID ASC );
+
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '25f6d7cc-b168-4dd5-a36d-6f14b2f956e9', 2, 2, 'bitte melden Sie sich bei uns', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'a5fa380e-8f33-4ea7-9416-e03d11b91cae', 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'c0dcef80-ca07-48b7-a3ed-2c99c4388928', 1, 1, 'info@bigbang.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'd19d9e10-d2dd-4383-84ec-2fe96421c0a3', 2, 1, 'info@pharmapeekltd.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '234b63e4-d8db-48ab-899f-0320903c01af', 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '11e25c02-de00-430d-b6cd-f02f7c60e026', 5, 1, 'PaulineF@gmx.net', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '9ca29c3b-e189-4ce9-9401-15001c769627', 6, 1, 'mo@gmail.com', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '05505b1a-61df-45c0-b006-64165cbadfa2', 7, 1, 'MGruebelSport@fogger.mil', 'Info...');
+INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'b52dabaf-d156-4fd0-a07c-510673112a15', 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...');
+
+-- ---------------------------------------------
+-- TABLE TBL_COMPANY
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_company_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_company_id_seq
+ OWNER TO CBD_SERVICE;
+
+
+CREATE TABLE public.TBL_COMPANY
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_company_id_seq'::regclass),
+ COMPANY_NAME character varying(255),
+ COMPANY_TYPE character varying(30),
+ HR_NUMBER character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_COMPANY
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_COMPANY TO CBD_SERVICE;
+
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 );
+INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_CONTACT_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_contact_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_contact_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_CONTACT_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_contact_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint NULL,
+ FK_CONTACT_ID bigint NOT NULL,
+ FK_COMPANY_ID bigint NOT NULL,
+ CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_CONTACT_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_CONTACT_PERSON__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID)
+ REFERENCES public.TBL_COMPANY (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_CONTACT_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_CONTACT_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3);
+INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2);
+
+-- ---------------------------------------------
+-- TABLE TBL_EXTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_external_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_external_person_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_EXTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_external_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_EXTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_EXTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_EXTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_EXTERNAL_PERSON TO CBD_SERVICE;
+
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7);
+INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8);
+
+
+-- ---------------------------------------------
+-- TABLE TBL_INTERNAL_PERSON
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_internal_person_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_internal_person_id_seq
+ OWNER TO CBD_SERVICE;
+CREATE TABLE public.TBL_INTERNAL_PERSON
+(
+ ID bigint NOT NULL DEFAULT nextval('tbl_internal_person_id_seq'::regclass),
+ FIRST_NAME character varying(255),
+ LAST_NAME character varying(255),
+ TITLE character varying(255),
+ FK_SALUTATION_ID bigint,
+ FK_REF_PERSON_TYPE_ID bigint,
+ DEPARTMENT character varying(255),
+ UID character varying(255),
+ USER_REF character varying(255),
+ FK_CONTACT_ID bigint NOT NULL,
+ CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID),
+ CONSTRAINT TBL_INTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
+ REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
+ REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_INTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
+ REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+ALTER TABLE public.TBL_INTERNAL_PERSON
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.TBL_INTERNAL_PERSON TO CBD_SERVICE;
+
+CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON public.TBL_INTERNAL_PERSON ( UID ASC );
+CREATE UNIQUE INDEX idx_tbl_internal_person_user_ref ON public.TBL_INTERNAL_PERSON ( USER_REF ASC );
+
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13);
+INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14);
+-- ---------------------------------------------
+-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_assignment_modul_contact_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_assignment_modul_contact_id_seq
+ OWNER TO CBD_SERVICE;
+
+CREATE TABLE public.TBL_ASSIGNMENT_MODUL_CONTACT
+(
+ id bigint NOT NULL DEFAULT nextval('tbl_assignment_modul_contact_id_seq'::regclass),
+ uuid uuid NOT NULL,
+ fk_contact_id bigint NOT NULL,
+ modul_name character varying(255),
+ assignment_date timestamp,
+ expiring_date timestamp,
+ deletion_lock_until timestamp,
+ assignment_note character varying(2048),
+ CONSTRAINT tbl_assignment_modul_contact_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_assignment_modul_contact_id_fkey FOREIGN KEY (fk_contact_id)
+ REFERENCES public.tbl_contact (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_assignment_modul_contact
+ OWNER TO cbd_service;
+GRANT ALL ON TABLE public.tbl_assignment_modul_contact TO cbd_service;
+
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( 'dfc2979c-40e3-11ea-b77f-2e728ce88125', 1, 'Betriebstagebuch', '2020-01-19 10:23:54', '2021-01-19 00:00:00', '2021-01-19 00:00:00', 'seit Januar zugeordnet');
+INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( '4009bce2-40e5-11ea-b77f-2e728ce88125', 1, 'Störinfos', '2020-02-27 10:23:54', '2019-02-27 00:00:00', '2019-02-27 00:00:00', 'seit Februar zugeordnet');
+
+-- -------------------------------------
+-- VIEWS -------------------------------
+-- -------------------------------------
+DROP VIEW IF EXISTS VW_GENERAL_CONTACT CASCADE;
+
+CREATE VIEW VW_GENERAL_CONTACT
+AS
+SELECT g.id,
+ c.uuid,
+ g.name,
+ c.contact_type,
+ g.fk_contact_id,
+ g.company_name,
+ g.company_type,
+ g.company_id,
+ g.fk_salutation_id,
+ g.fk_ref_person_type_id,
+ g.title,
+ g.first_name,
+ g.last_name,
+ g.department,
+ c.note,
+ c.anonymized
+FROM tbl_contact c
+INNER JOIN (
+
+SELECT company.id,
+ company_name as name,
+ fk_contact_id,
+ company_name,
+ company_type,
+ cntct.uuid as company_id,
+ null as fk_salutation_id,
+ null as fk_ref_person_type_id,
+ null as title,
+ null as first_name,
+ null as last_name,
+ null as department
+FROM tbl_company company
+INNER JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id
+
+UNION
+
+SELECT p.id,
+ COALESCE(p.last_name, '')
+ || CASE WHEN COALESCE(p.last_name, '') <> '' AND COALESCE(p.first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name,
+ p.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ company_contact.uuid as company_id,
+ p.fk_salutation_id,
+ p.fk_ref_person_type_id,
+ p.title,
+ p.first_name,
+ p.last_name,
+ null as department
+FROM tbl_contact_person p
+INNER JOIN tbl_company c ON c.id = p.fk_company_id
+INNER JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ department
+FROM tbl_internal_person
+
+UNION
+
+SELECT id,
+ COALESCE(last_name, '')
+ || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END
+ || COALESCE(first_name, '') as name,
+ fk_contact_id,
+ null as company_name,
+ null as company_type,
+ null as company_id,
+ fk_salutation_id,
+ fk_ref_person_type_id,
+ title,
+ first_name,
+ last_name,
+ null as department
+FROM tbl_external_person
+ ) g
+ON g.fk_contact_id = c.ID;
+
+ALTER VIEW public.VW_GENERAL_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_GENERAL_CONTACT TO CBD_SERVICE;
+
+
+DROP VIEW IF EXISTS VW_DETAILED_CONTACT;
+
+CREATE VIEW VW_DETAILED_CONTACT
+AS
+SELECT c.id,
+ c.uuid,
+ c.name,
+ c.contact_type,
+ c.fk_contact_id,
+ c.company_name,
+ c.company_type,
+ c.company_id,
+ c.anonymized,
+ s.uuid as salutation_uuid,
+ t.uuid as person_type_uuid,
+ c.title,
+ c.first_name,
+ c.last_name,
+ c.department,
+ c.note,
+ s.type as salutation_type,
+ t.type as person_type,
+ a.street,
+ a.housenumber,
+ a.community,
+ com.communication_data as email,
+
+ UPPER(
+ COALESCE(c.name, '') || '|@|'
+ || COALESCE(company_name, '') || '|@|'
+ || COALESCE(c.company_type, '') || '|@|'
+ || COALESCE(c.title, '') || '|@|'
+ || COALESCE(c.first_name, '') || '|@|'
+ || COALESCE(c.last_name, '') || '|@|'
+ || COALESCE(c.department, '') || '|@|'
+ || COALESCE(c.note, '') || '|@|'
+ || COALESCE(s.type, '') || '|@|'
+ || COALESCE(t.type, '') || '|@|'
+ || COALESCE(a.street, '') || '|@|'
+ || COALESCE(a.housenumber, '') || '|@|'
+ || COALESCE(a.community, '') || '|@|'
+ || COALESCE(com.communication_data, '')
+ )as searchfield
+FROM VW_GENERAL_CONTACT c
+LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id
+LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = true
+LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id
+LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id AND com.fk_communication_type =
+ (SELECT ct.id FROM ref_communication_type ct WHERE ct.type_email = true)) ;
+
+ALTER VIEW public.VW_DETAILED_CONTACT
+ OWNER TO CBD_SERVICE;
+GRANT ALL ON TABLE public.VW_DETAILED_CONTACT TO CBD_SERVICE;
+
+
+
+
diff --git a/src/main/resources/logback-spring.xml b/src/main/resources/logback-spring.xml
new file mode 100644
index 0000000..46b1915
--- /dev/null
+++ b/src/main/resources/logback-spring.xml
@@ -0,0 +1,40 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<configuration>
+
+ <property name="LOGS" value="./logs"/>
+
+ <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
+ <layout class="ch.qos.logback.classic.PatternLayout">
+ <Pattern>
+ %black(%d{ISO8601}) %highlight(%-5level) [%blue(%t)] %yellow(%c{60}): %msg%n%throwable
+ </Pattern>
+ </layout>
+ </appender>
+
+ <appender name="RollingFile" class="ch.qos.logback.core.rolling.RollingFileAppender">
+ <file>${LOGS}/contact-base-data.log</file>
+ <encoder
+ class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
+ <Pattern>%d %p %c{60} [%t] %m%n</Pattern>
+ </encoder>
+
+ <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
+ <fileNamePattern>${LOGS}/archived/contact-base-data-%d{yyyy-MM-dd}.gz</fileNamePattern>
+ <MaxHistory>30</MaxHistory>
+ <cleanHistoryOnStart>true</cleanHistoryOnStart>
+ </rollingPolicy>
+ </appender>
+
+ <!-- LOG everything at INFO level -->
+ <root level="info">
+ <appender-ref ref="RollingFile"/>
+ <appender-ref ref="Console"/>
+ </root>
+
+ <!-- LOG "com.baeldungorg.eclipse.openk.contactbasedata*" at INFO level -->
+ <logger name="org.eclipse.openk.contactbasedata" level="info" additivity="false">
+ <appender-ref ref="RollingFile"/>
+ <appender-ref ref="Console"/>
+ </logger>
+
+</configuration>
\ No newline at end of file
diff --git a/src/test/java/org/eclipse/openk/contactbasedata/config/TestConfiguration.java b/src/test/java/org/eclipse/openk/contactbasedata/config/TestConfiguration.java
index 7056b3a..de5b02c 100644
--- a/src/test/java/org/eclipse/openk/contactbasedata/config/TestConfiguration.java
+++ b/src/test/java/org/eclipse/openk/contactbasedata/config/TestConfiguration.java
@@ -15,6 +15,7 @@
package org.eclipse.openk.contactbasedata.config;
import org.eclipse.openk.contactbasedata.ContactBaseDataApplication;
+import org.eclipse.openk.contactbasedata.api.AuthNAuthApi;
import org.eclipse.openk.contactbasedata.mapper.*;
import org.eclipse.openk.contactbasedata.service.*;
import org.eclipse.openk.contactbasedata.service.util.LdapUserAttributesMapper;
@@ -78,6 +79,9 @@
LdapUserAttributesMapper ldapUserAttributesMapper() { return new LdapUserAttributesMapper(); };
@MockBean
+ private AuthNAuthApi authNAuthApi;
+
+ @MockBean
private LdapTemplate ldapTemplate;
@Bean
@@ -129,4 +133,7 @@
@Bean
public LdapService myLdapService() { return new LdapService(); }
+
+ @Bean
+ public AuthNAuthService myAuthNAuthService() { return new AuthNAuthService(); }
}
diff --git a/src/test/java/org/eclipse/openk/contactbasedata/service/AuthNAuthServiceTest.java b/src/test/java/org/eclipse/openk/contactbasedata/service/AuthNAuthServiceTest.java
new file mode 100644
index 0000000..27aba4b
--- /dev/null
+++ b/src/test/java/org/eclipse/openk/contactbasedata/service/AuthNAuthServiceTest.java
@@ -0,0 +1,81 @@
+/*
+ *******************************************************************************
+ * Copyright (c) 2019 Contributors to the Eclipse Foundation
+ *
+ * See the NOTICE file(s) distributed with this work for additional
+ * information regarding copyright ownership.
+ *
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v. 2.0 which is available at
+ * http://www.eclipse.org/legal/epl-2.0.
+ *
+ * SPDX-License-Identifier: EPL-2.0
+ *******************************************************************************
+ */
+
+package org.eclipse.openk.contactbasedata.service;
+
+import org.assertj.core.util.Lists;
+import org.eclipse.openk.contactbasedata.api.AuthNAuthApi;
+import org.eclipse.openk.contactbasedata.config.TestConfiguration;
+import org.eclipse.openk.contactbasedata.model.JwtToken;
+import org.eclipse.openk.contactbasedata.model.LoginCredentials;
+import org.eclipse.openk.contactbasedata.model.TblInternalPerson;
+import org.eclipse.openk.contactbasedata.repository.CommunicationTypeRepository;
+import org.eclipse.openk.contactbasedata.repository.InternalPersonRepository;
+import org.eclipse.openk.contactbasedata.support.MockDataHelper;
+import org.junit.jupiter.api.Test;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Qualifier;
+import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
+import org.springframework.boot.test.mock.mockito.MockBean;
+import org.springframework.test.context.ContextConfiguration;
+
+import java.util.List;
+
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.Mockito.*;
+
+@DataJpaTest
+@ContextConfiguration(classes = {TestConfiguration.class})
+
+public class AuthNAuthServiceTest {
+ private static final long MAIL_ID = 1L;
+ private static final long TEL_ID = 2L;
+
+ @Qualifier("myAuthNAuthService")
+ @Autowired
+ private AuthNAuthService authNAuthService ;
+
+ @Autowired
+ private AuthNAuthApi authNAuthApi;
+
+ @MockBean
+ private InternalPersonService internalPersonService;
+
+ @MockBean
+ private InternalPersonRepository internalPersonRepository;
+
+ @MockBean
+ private CommunicationTypeRepository communicationTypeRepository;
+
+ @Test
+ public void shouldSynchronizeAuthnAuthRunThrou1() {
+ List<TblInternalPerson> internalPeople = MockDataHelper.mockTblInternalPersonPage().getContent();
+ internalPeople.get(1).setUserRef("karlK");
+ when(internalPersonRepository.saveAll(any( List.class ))).thenReturn(Lists.emptyList());
+ when( internalPersonRepository.findByUserRefNotNull() ).thenReturn(internalPeople);
+
+ JwtToken jwtToken = new JwtToken();
+ jwtToken.setAccessToken("ds2ds2d23ad3242");
+ when( authNAuthApi.login(any(LoginCredentials.class))).thenReturn(jwtToken);
+ when( authNAuthApi.logout(jwtToken.getAccessToken())).thenReturn(null);
+ when( authNAuthApi.getKeycloakUsers(jwtToken.getAccessToken())).thenReturn(MockDataHelper.mockKeycloakUsers());
+
+ authNAuthService.synchronizeAuthNAuth();
+
+ verify( authNAuthApi, times(1)).logout(anyString());
+ verify( internalPersonRepository, times(1 )).saveAll(any(List.class));
+ }
+
+}
diff --git a/src/test/java/org/eclipse/openk/contactbasedata/service/util/LdapUserAttributesMapperTest.java b/src/test/java/org/eclipse/openk/contactbasedata/service/util/LdapUserAttributesMapperTest.java
new file mode 100644
index 0000000..7f31a76
--- /dev/null
+++ b/src/test/java/org/eclipse/openk/contactbasedata/service/util/LdapUserAttributesMapperTest.java
@@ -0,0 +1,72 @@
+package org.eclipse.openk.contactbasedata.service.util;
+
+import org.eclipse.openk.contactbasedata.ContactBaseDataApplication;
+import org.eclipse.openk.contactbasedata.support.MockDataHelper;
+import org.eclipse.openk.contactbasedata.viewmodel.LdapUser;
+import org.junit.jupiter.api.Test;
+import org.mockito.Mockito;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.test.context.SpringBootTest;
+import org.springframework.test.context.ActiveProfiles;
+
+import javax.naming.NamingException;
+import javax.naming.directory.Attribute;
+import javax.naming.directory.Attributes;
+import java.util.Arrays;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.mockito.Mockito.when;
+
+@SpringBootTest(classes = ContactBaseDataApplication.class)
+@ActiveProfiles("test") // Todo: Find a better way to configure the tests
+public class LdapUserAttributesMapperTest {
+
+ @Autowired
+ LdapUserAttributesMapper ldapUserAttributesMapper;
+
+ @Test
+ public void shouldMapFromAttributesCorrectly() throws NamingException {
+ Attributes attributes = Mockito.mock(Attributes.class);
+
+ String[] fields = {"uid", "cn", "sn", "givenname", "title", "mail", "department", "phone"};
+
+ Arrays.stream(fields).forEach( x -> {
+ Attribute attr = MockDataHelper.mockLdapAttribute('_' + x + "_");
+ when(attributes.get(x)).thenReturn(attr);
+ });
+
+ LdapUser ldapUser = ldapUserAttributesMapper.mapFromAttributes(attributes);
+ assertEquals("_uid_", ldapUser.getUid());
+ assertEquals("_cn_", ldapUser.getFullName());
+ assertEquals("_sn_", ldapUser.getLastName());
+ assertEquals("_givenname_", ldapUser.getFirstName());
+ assertEquals("_title_", ldapUser.getTitle());
+ assertEquals("_mail_", ldapUser.getMail());
+ assertEquals("_department_", ldapUser.getDepartment());
+ assertEquals("_phone_", ldapUser.getTelephoneNumber());
+
+ }
+
+ @Test
+ public void shouldMapFromAttributesEmpty() throws NamingException {
+ Attributes attributes = Mockito.mock(Attributes.class);
+
+ String[] fields = {"uid", "cn", "sn", "givenname", "title", "mail", "department", "phone"};
+
+ Arrays.stream(fields).forEach( x -> {
+ when(attributes.get(x)).thenReturn(null);
+ });
+
+ LdapUser ldapUser = ldapUserAttributesMapper.mapFromAttributes(attributes);
+ assertEquals(null, ldapUser.getUid());
+ assertEquals(null, ldapUser.getFullName());
+ assertEquals(null, ldapUser.getLastName());
+ assertEquals(null, ldapUser.getFirstName());
+ assertEquals(null, ldapUser.getTitle());
+ assertEquals(null, ldapUser.getMail());
+ assertEquals(null, ldapUser.getDepartment());
+ assertEquals(null, ldapUser.getTelephoneNumber());
+
+ }
+
+}
diff --git a/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java b/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java
index bde9bac..ed5fc35 100644
--- a/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java
+++ b/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java
@@ -21,9 +21,14 @@
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
+import javax.naming.NamingException;
+import javax.naming.directory.Attribute;
import java.sql.Date;
import java.util.*;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
public class MockDataHelper {
public static Version mockVersion() {
@@ -322,8 +327,6 @@
communicationType.setUuid(UUID.randomUUID());
communicationType.setDescription("Fax");
communicationType.setType("Fax");
- communicationType.setEditable(true);
- communicationType.setMappingLdap(false);
return communicationType;
}
@@ -736,6 +739,14 @@
return ldapUser;
}
+ public static KeyCloakUser mockKeyCloakUser() {
+ KeyCloakUser keyCloakUser = new KeyCloakUser();
+ keyCloakUser.setUsername("ludwigL");
+ keyCloakUser.setFirstName("Ludwig");
+ keyCloakUser.setLastName("Lumpensammler");
+ return keyCloakUser;
+ }
+
public static List<LdapUser> mockLdapUsers() {
List<LdapUser> list = new ArrayList<>();
LdapUser ldapUser2 = new LdapUser();
@@ -748,4 +759,24 @@
return list;
}
+ public static List<KeyCloakUser> mockKeycloakUsers() {
+ List<KeyCloakUser> list = new ArrayList<>();
+ KeyCloakUser keyCloakUser = new KeyCloakUser();
+ keyCloakUser.setFirstName("Karl");
+ keyCloakUser.setLastName("Kleinfink");
+ keyCloakUser.setUsername("karlK");
+ list.add( mockKeyCloakUser() );
+ list.add( keyCloakUser );
+ return list;
+ }
+
+ public static Attribute mockLdapAttribute(String value ) {
+ Attribute attribute = mock( Attribute.class);
+ try {
+ when(attribute.get()).thenReturn(value);
+ } catch (NamingException e) {
+ return null;
+ }
+ return attribute;
+ }
}
