TP-20240927-importfixes (#115)

Co-authored-by: Timotheus Pokorra <timotheus.pokorra@solidcharity.com>
Co-authored-by: Dev und Test fuer hsadminng <hsh03-hsngdev@h50.hostsharing.net>
Reviewed-on: #115
Reviewed-by: Michael Hoennig <michael.hoennig@hostsharing.net>
Co-authored-by: Timotheus Pokorra <timotheus.pokorra@hostsharing.net>
Co-committed-by: Timotheus Pokorra <timotheus.pokorra@hostsharing.net>

doc/business-glossary-de.md

@@ -79,12 +79,26 @@ der Person des VIP-Contact (_Holder_) zur repräsentierten Person (_Anchor_) dar
 
 ### Operations-Contact
 
-Ein _Operations-_Contact_ ist_ eine natürliche Person, die für einen Geschäftspartner technischer Ansprechpartner ist
+Ein _Operations-_Contact_ ist_ eine natürliche Person, die für einen Geschäftspartner technischer Ansprechpartner ist.
+
+Ein Seiteneffekt ist, dass diese Person im Ticketsystem Znuny direkt dem Geschäftspartner zugeordnet werden kann.
+
+Im Legacy System waren das die Kontakte mit der Rolle `operation` und `silent`.
 
 Implementiert ist der _Operations-Contact_ als eine besondere Form der [Relation](#Relation)
 der Person des _Operations-Contact_ (_Holder_) zur repräsentierten Person (_Anchor_) dargestellt.
 
 
+### OperationsAlert-Contact
+
+Ein _OperationsAlert-_Contact_ ist_ eine natürliche Person, die für einen Geschäftspartner bei technischen Probleme kontaktiert werden soll.
+
+Im Legacy System waren das die Kontakte mit der Rolle `operation`.
+
+Implementiert ist der _OperationsAlert-Contact_ als eine besondere Form der [Relation](#Relation)
+der Person des _OperationsAlert-Contact_ (_Holder_) zur repräsentierten Person (_Anchor_) dargestellt.
+
+
 ### Subscriber-Contact
 
 Ein _Subscriber-_Contact_ ist_ eine natürliche Person, die für einen Geschäftspartner eine bestimmte Mailingliste abonniert.

src/main/java/net/hostsharing/hsadminng/hs/office/relation/HsOfficeRelationType.java

@@ -8,5 +8,6 @@ public enum HsOfficeRelationType {
     VIP_CONTACT,
     DEBITOR,
     OPERATIONS,
+    OPERATIONS_ALERT,
     SUBSCRIBER
 }

src/main/resources/api-definition/hs-office/hs-office-relation-schemas.yaml

@@ -13,6 +13,7 @@ components:
                 - REPRESENTATIVE
                 - VIP_CONTACT
                 - OPERATIONS
+                - OPERATIONS_ALERT
                 - SUBSCRIBER
 
         HsOfficeRelation:

src/main/resources/db/changelog/5-hs-office/503-relation/5030-hs-office-relation.sql

@@ -12,6 +12,7 @@ CREATE TYPE hs_office.RelationType AS ENUM (
     'DEBITOR',
     'VIP_CONTACT',
     'OPERATIONS',
+    'OPERATIONS_ALERT',
     'SUBSCRIBER');
 
 CREATE CAST (character varying as hs_office.RelationType) WITH INOUT AS IMPLICIT;

src/main/resources/db/changelog/5-hs-office/511-coopshares/5116-hs-office-coopshares-migration.sql

@@ -7,7 +7,7 @@
 --changeset michael.hoennig:hs-office-coopshares-MIGRATION-mapping endDelimiter:--//
 -- ----------------------------------------------------------------------------
 
-CREATE TABLE hs_office.coopsharestransaction_legacy_id
+CREATE TABLE hs_office.coopsharetx_legacy_id
 (
     uuid            uuid NOT NULL REFERENCES hs_office.coopsharetx(uuid),
     member_share_id  integer NOT NULL
@@ -19,10 +19,10 @@ CREATE TABLE hs_office.coopsharestransaction_legacy_id
 --changeset michael.hoennig:hs-office-coopshares-MIGRATION-sequence endDelimiter:--//
 -- ----------------------------------------------------------------------------
 
-CREATE SEQUENCE IF NOT EXISTS hs_office.coopsharestransaction_legacy_id_seq
+CREATE SEQUENCE IF NOT EXISTS hs_office.coopsharetx_legacy_id_seq
     AS integer
     START 1000000000
-    OWNED BY hs_office.coopsharestransaction_legacy_id.member_share_id;
+    OWNED BY hs_office.coopsharetx_legacy_id.member_share_id;
 --//
 
 
@@ -30,9 +30,9 @@ CREATE SEQUENCE IF NOT EXISTS hs_office.coopsharestransaction_legacy_id_seq
 --changeset michael.hoennig:hs-office-coopshares-MIGRATION-default endDelimiter:--//
 -- ----------------------------------------------------------------------------
 
-ALTER TABLE hs_office.coopsharestransaction_legacy_id
+ALTER TABLE hs_office.coopsharetx_legacy_id
     ALTER COLUMN member_share_id
-        SET DEFAULT nextVal('hs_office.coopsharestransaction_legacy_id_seq');
+        SET DEFAULT nextVal('hs_office.coopsharetx_legacy_id_seq');
 
 --/
 
@@ -41,8 +41,8 @@ ALTER TABLE hs_office.coopsharestransaction_legacy_id
 -- ----------------------------------------------------------------------------
 
 CALL base.defineContext('schema-migration');
-INSERT INTO hs_office.coopsharestransaction_legacy_id(uuid, member_share_id)
+INSERT INTO hs_office.coopsharetx_legacy_id(uuid, member_share_id)
-    SELECT uuid, nextVal('hs_office.coopsharestransaction_legacy_id_seq') FROM hs_office.coopsharetx;
+    SELECT uuid, nextVal('hs_office.coopsharetx_legacy_id_seq') FROM hs_office.coopsharetx;
 --/
 
 
@@ -58,8 +58,8 @@ begin
         raise exception 'invalid usage of trigger';
     end if;
 
-    INSERT INTO hs_office.coopsharestransaction_legacy_id VALUES
+    INSERT INTO hs_office.coopsharetx_legacy_id VALUES
-        (NEW.uuid, nextVal('hs_office.coopsharestransaction_legacy_id_seq'));
+        (NEW.uuid, nextVal('hs_office.coopsharetx_legacy_id_seq'));
 
     return NEW;
 end; $$;
@@ -83,7 +83,7 @@ begin
         raise exception 'invalid usage of trigger';
     end if;
 
-    DELETE FROM hs_office.coopsharestransaction_legacy_id
+    DELETE FROM hs_office.coopsharetx_legacy_id
            WHERE uuid = OLD.uuid;
 
     return OLD;

src/main/resources/db/changelog/9-hs-global/9100-hs-integration-schema.sql

@@ -0,0 +1,8 @@
+--liquibase formatted sql
+
+
+-- ============================================================================
+--changeset timotheus.pokorra:hs-integration-SCHEMA endDelimiter:--//
+-- ----------------------------------------------------------------------------
+CREATE SCHEMA hs_integration;
+--//

src/main/resources/db/changelog/9-hs-global/9110-integration-kimai.sql

@@ -0,0 +1,18 @@
+--liquibase formatted sql
+
+-- ============================================================================
+--changeset timotheus.pokorra:hs-global-integration-kimai endDelimiter:--//
+-- TODO.impl: also select column debitorNumber and do not filter anymore for '00'
+CREATE OR REPLACE VIEW hs_integration.time_customer AS
+ SELECT p.partnernumber, debitor.defaultprefix
+    FROM hs_office.partner p
+    JOIN hs_office.relation AS pRel
+        ON pRel.type = 'PARTNER'
+        AND pRel.uuid = p.partnerRelUuid
+    JOIN hs_office.relation AS dRel
+        ON dRel.type = 'DEBITOR'
+        AND dRel.anchorUuid = pRel.holderUuid
+    JOIN hs_office.debitor AS debitor
+        ON debitor.debitorreluuid = dRel.uuid
+        AND debitor.debitornumbersuffix = '00';
+--//

src/main/resources/db/changelog/9-hs-global/9120-integration-znuny.sql

@@ -0,0 +1,102 @@
+
+--liquibase formatted sql
+
+-- ============================================================================
+--changeset timotheus.pokorra:hs-global-integration-znuny endDelimiter:--//
+-- TODO.impl: also select column debitorNumber and do not filter anymore for '00'
+CREATE OR REPLACE VIEW hs_integration.contact AS
+  SELECT DISTINCT ON (uuid)
+    partner.partnernumber as partnernumber,
+    debitor.defaultprefix as defaultprefix,
+    c.uuid as uuid,
+    (CASE WHEN per.salutation <> '' THEN per.salutation ELSE NULL END) as salutation,
+    (CASE WHEN per.givenname <> '' THEN per.givenname ELSE NULL END) as givenname,
+    (CASE WHEN per.familyname <> '' THEN per.familyname ELSE NULL END) as familyname,
+    (CASE WHEN per.title <> '' THEN per.title ELSE NULL END) as title,
+    (CASE WHEN per.tradename <> '' THEN per.tradename ELSE NULL END) as tradename,
+    (CASE WHEN c.postaladdress->>'co' <> '' THEN c.postaladdress->>'co' ELSE NULL END) as co,
+    c.postaladdress->>'street' as street,
+    c.postaladdress->>'zipcode' as zipcode,
+    c.postaladdress->>'city' as city,
+    c.postaladdress->>'country' as country,
+    c.phonenumbers->>'phone_private' as phone_private,
+    c.phonenumbers->>'phone_office' as phone_office,
+    c.phonenumbers->>'phone_mobile' as phone_mobile,
+    c.phonenumbers->>'fax' as fax,
+    c.emailaddresses->>'main' as email
+  FROM hs_office.partner AS partner
+  JOIN hs_office.partner_legacy_id AS partner_lid ON partner_lid.uuid = partner.uuid
+  JOIN hs_office.relation AS pRel
+        ON pRel.type = 'PARTNER'
+        AND pRel.uuid = partner.partnerRelUuid
+  JOIN hs_office.relation AS dRel
+        ON dRel.type = 'DEBITOR'
+        AND dRel.anchorUuid = pRel.holderUuid
+  JOIN hs_office.debitor AS debitor
+        ON debitor.debitorreluuid = dRel.uuid
+        AND debitor.debitornumbersuffix = '00'
+  JOIN hs_office.contact AS c ON c.uuid = pRel.contactuuid
+  JOIN hs_office.person AS per ON per.uuid = pRel.holderuuid
+  UNION
+  SELECT DISTINCT ON (uuid)
+    partner.partnernumber as partnernumber,
+    debitor.defaultprefix as defaultprefix,
+    c.uuid as uuid,
+    (CASE WHEN per.salutation <> '' THEN per.salutation ELSE NULL END) as salutation,
+    (CASE WHEN per.givenname <> '' THEN per.givenname ELSE NULL END) as givenname,
+    (CASE WHEN per.familyname <> '' THEN per.familyname ELSE NULL END) as familyname,
+    (CASE WHEN per.title <> '' THEN per.title ELSE NULL END) as title,
+    (CASE WHEN per.tradename <> '' THEN per.tradename ELSE NULL END) as tradename,
+    (CASE WHEN c.postaladdress->>'co' <> '' THEN c.postaladdress->>'co' ELSE NULL END) as co,
+    c.postaladdress->>'street' as street,
+    c.postaladdress->>'zipcode' as zipcode,
+    c.postaladdress->>'city' as city,
+    c.postaladdress->>'country' as country,
+    c.phonenumbers->>'phone_private' as phone_private,
+    c.phonenumbers->>'phone_office' as phone_office,
+    c.phonenumbers->>'phone_mobile' as phone_mobile,
+    c.phonenumbers->>'fax' as fax,
+    c.emailaddresses->>'main' as email
+  FROM hs_office.partner AS partner
+  JOIN hs_office.relation AS pRel
+        ON pRel.type = 'PARTNER'
+        AND pRel.uuid = partner.partnerRelUuid
+  JOIN hs_office.relation AS dRel
+        ON dRel.type = 'DEBITOR'
+        AND dRel.anchorUuid = pRel.holderUuid
+  JOIN hs_office.debitor AS debitor
+        ON debitor.debitorreluuid = dRel.uuid
+        AND debitor.debitornumbersuffix = '00'
+  JOIN hs_office.relation AS rs1 ON rs1.uuid = partner.partnerreluuid AND rs1.type = 'PARTNER'
+  JOIN hs_office.relation AS relation ON relation.anchoruuid = rs1.holderuuid
+  JOIN hs_office.contact AS c ON c.uuid = relation.contactuuid
+  JOIN hs_office.person AS per ON per.uuid = relation.holderuuid;
+
+CREATE OR REPLACE VIEW hs_integration.ticket_customer_user AS
+  SELECT c.uuid,
+    max(c.partnernumber)::text as number,
+    max(c.defaultprefix) as code,
+    max(c.email) as login,
+    max(c.salutation) as salut,
+    max(c.givenname) as firstname,
+    max(c.familyname) as lastname,
+    max(c.title) as title,
+    max(c.tradename) as firma,
+    max(c.co) as co,
+    max(c.street) as street,
+    max(c.zipcode) as zipcode,
+    max(c.city) as city,
+    max(c.country) as country,
+    max(concat_ws(', '::text, c.phone_office, c.phone_private)) AS phone,
+    max(c.phone_private) as phone_private,
+    max(c.phone_office) as phone_office,
+    max(c.phone_mobile) as mobile,
+    max(c.fax) as fax,
+    max(c.email) as email,
+    string_agg(CASE WHEN relation.mark IS NULL THEN relation.type::text ELSE CONCAT(relation.type::text, ':', relation.mark::text) END, '/'::text) AS comment,
+    1 AS valid
+  FROM hs_integration.contact AS c
+  JOIN hs_office.relation AS relation ON c.uuid = relation.contactuuid
+  WHERE (c.defaultprefix != 'hsh' OR (c.partnernumber = 10000 AND c.email = 'hostmaster@hostsharing.net'))
+  GROUP BY c.uuid;
+--//

src/main/resources/db/changelog/db.changelog-master.yaml

@@ -171,3 +171,9 @@ databaseChangeLog:
         file: db/changelog/7-hs-hosting/701-hosting-asset/7018-hs-hosting-asset-test-data.sql
     - include:
         file: db/changelog/9-hs-global/9000-statistics.sql
+    - include:
+        file: db/changelog/9-hs-global/9100-hs-integration-schema.sql
+    - include:
+        file: db/changelog/9-hs-global/9110-integration-kimai.sql
+    - include:
+        file: db/changelog/9-hs-global/9120-integration-znuny.sql

src/test/java/net/hostsharing/hsadminng/hs/migration/BaseOfficeDataImport.java

@@ -54,7 +54,7 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
             "subscriber:customers-announce"
     };
     private static final String[] KNOWN_ROLES = ArrayUtils.addAll(
-            new String[] { "partner", "vip-contact", "ex-partner", "billing", "contractual", "operation" },
+            new String[] { "partner", "vip-contact", "ex-partner", "billing", "contractual", "operation", "silent" },
             SUBSCRIBER_ROLES);
 
     // at least as the number of lines in business_partners.csv from test-data, but less than real data partner count
@@ -65,18 +65,21 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
     static int relationId = INITIAL_RELATION_ID;
 
     private static final List<Integer> IGNORE_BUSINESS_PARTNERS = Arrays.asList(
-            512167, // 11139, partner without contractual contact
-            512170, // 11142, partner without contractual contact
-            511725, // 10764, partner without contractual contact
-            // 512171, // 11143, partner without partner contact -- exception
             -1
     );
 
     private static final List<Integer> IGNORE_CONTACTS = Arrays.asList(
-            90547, // Kontakt hat keine Rolle
             -1
     );
 
+    private static final Map<Integer, HsOfficePersonType> PERSON_TYPES_BY_CONTACT = Map.of(
+        90072, HsOfficePersonType.NATURAL_PERSON,
+        90641, HsOfficePersonType.LEGAL_PERSON,
+        90368, HsOfficePersonType.LEGAL_PERSON,
+        90564, HsOfficePersonType.NATURAL_PERSON,
+        -1, HsOfficePersonType.LEGAL_PERSON
+    );
+
     static Map<Integer, HsOfficeContactRealEntity> contacts = new WriteOnceMap<>();
     static Map<Integer, HsOfficePersonEntity> persons = new WriteOnceMap<>();
     static Map<Integer, HsOfficePartnerEntity> partners = new WriteOnceMap<>();
@@ -192,56 +195,56 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
 
         assertThat(toJsonFormattedString(partners)).isEqualToIgnoringWhitespace("""
                 {
-                   100=partner(P-10003: ?? Michael Mellis, Herr Michael Mellis , Michael Mellis),
+                   100=partner(P-10003: ?? Michael Mellis, Herr Michael Mellis, Michael Mellis),
-                   120=partner(P-10020: LP JM GmbH, Herr Philip Meyer-Contract , JM GmbH),
+                   120=partner(P-10020: LP JM GmbH, Herr Philip Meyer-Contract, JM GmbH),
-                   122=partner(P-11022: ?? Test PS, Petra Schmidt , Test PS),
+                   122=partner(P-11022: ?? Test PS, Petra Schmidt, Test PS),
-                   132=partner(P-10152: ?? Ragnar IT-Beratung, Herr Ragnar Richter , Ragnar IT-Beratung),
+                   132=partner(P-10152: ?? Ragnar IT-Beratung, Herr Ragnar Richter, Ragnar IT-Beratung),
-                   190=partner(P-19090: NP Camus, Cecilia, Frau Cecilia Camus ),
+                   190=partner(P-19090: NP Camus, Cecilia, Frau Cecilia Camus),
                    199=partner(P-19999: null null, null),
-                   213=partner(P-10000: LP Hostsharing e.G., Firma Hostmaster Hostsharing , Hostsharing e.G.),
+                   213=partner(P-10000: LP Hostsharing e.G., Firma Hostmaster Hostsharing, Hostsharing e.G.),
-                   541=partner(P-11018: ?? Wasserwerk Südholstein, Frau Christiane Milberg , Wasserwerk Südholstein),
+                   541=partner(P-11018: ?? Wasserwerk Südholstein, Frau Christiane Milberg, Wasserwerk Südholstein),
-                   542=partner(P-11019: ?? Das Perfekte Haus, Herr Richard Wiese , Das Perfekte Haus)
+                   542=partner(P-11019: ?? Das Perfekte Haus, Herr Richard Wiese, Das Perfekte Haus)
                 }
                 """);
         assertThat(toJsonFormattedString(contacts)).isEqualToIgnoringWhitespace("""
                 {
-                   100=contact(caption='Herr Michael Mellis , Michael Mellis', emailAddresses='{ "main": "michael@Mellis.example.org"}'),
+                   100=contact(caption='Herr Michael Mellis, Michael Mellis', emailAddresses='{ "main": "michael@Mellis.example.org"}'),
                    1200=contact(caption='JM e.K.', emailAddresses='{ "main": "jm-ex-partner@example.org"}'),
-                   1201=contact(caption='Frau Dr. Jenny Meyer-Billing , JM GmbH', emailAddresses='{ "main": "jm-billing@example.org"}'),
+                   1201=contact(caption='Frau Dr. Jenny Meyer-Billing, JM GmbH', emailAddresses='{ "main": "jm-billing@example.org"}'),
-                   1202=contact(caption='Herr Andrew Meyer-Operation , JM GmbH', emailAddresses='{ "main": "am-operation@example.org"}'),
+                   1202=contact(caption='Herr Andrew Meyer-Operation, JM GmbH', emailAddresses='{ "main": "am-operation@example.org"}'),
-                   1203=contact(caption='Herr Philip Meyer-Contract , JM GmbH', emailAddresses='{ "main": "pm-partner@example.org"}'),
+                   1203=contact(caption='Herr Philip Meyer-Contract, JM GmbH', emailAddresses='{ "main": "pm-partner@example.org"}'),
-                   1204=contact(caption='Frau Tammy Meyer-VIP , JM GmbH', emailAddresses='{ "main": "tm-vip@example.org"}'),
+                   1204=contact(caption='Frau Tammy Meyer-VIP, JM GmbH', emailAddresses='{ "main": "tm-vip@example.org"}'),
-                   1301=contact(caption='Petra Schmidt , Test PS', emailAddresses='{ "main": "ps@example.com"}'),
+                   1301=contact(caption='Petra Schmidt, Test PS', emailAddresses='{ "main": "ps@example.com"}'),
-                   132=contact(caption='Herr Ragnar Richter , Ragnar IT-Beratung', emailAddresses='{ "main": "hostsharing@ragnar-richter.de"}'),
+                   132=contact(caption='Herr Ragnar Richter, Ragnar IT-Beratung', emailAddresses='{ "main": "hostsharing@ragnar-richter.de"}'),
-                   1401=contact(caption='Frau Frauke Fanninga ', emailAddresses='{ "main": "ff@example.org"}'),
+                   1401=contact(caption='Frau Frauke Fanninga', emailAddresses='{ "main": "ff@example.org"}'),
-                   1501=contact(caption='Frau Cecilia Camus ', emailAddresses='{ "main": "cc@example.org"}'),
+                   1501=contact(caption='Frau Cecilia Camus', emailAddresses='{ "main": "cc@example.org"}'),
-                   212=contact(caption='Firma Hostmaster Hostsharing , Hostsharing e.G.', emailAddresses='{ "main": "hostmaster@hostsharing.net"}'),
+                   212=contact(caption='Firma Hostmaster Hostsharing, Hostsharing e.G.', emailAddresses='{ "main": "hostmaster@hostsharing.net"}'),
-                   90436=contact(caption='Frau Christiane Milberg , Wasserwerk Südholstein', emailAddresses='{ "main": "rechnung@ww-sholst.example.org"}'),
+                   90436=contact(caption='Frau Christiane Milberg, Wasserwerk Südholstein', emailAddresses='{ "main": "rechnung@ww-sholst.example.org"}'),
-                   90437=contact(caption='Herr Richard Wiese , Das Perfekte Haus', emailAddresses='{ "main": "admin@das-perfekte-haus.example.org"}'),
+                   90437=contact(caption='Herr Richard Wiese, Das Perfekte Haus', emailAddresses='{ "main": "admin@das-perfekte-haus.example.org"}'),
-                   90438=contact(caption='Herr Karim Metzger , Wasswerwerk Südholstein', emailAddresses='{ "main": "karim.metzger@ww-sholst.example.org"}'),
+                   90438=contact(caption='Herr Karim Metzger, Wasswerwerk Südholstein', emailAddresses='{ "main": "karim.metzger@ww-sholst.example.org"}'),
-                   90590=contact(caption='Herr Inhaber R. Wiese , Das Perfekte Haus', emailAddresses='{ "main": "515217@kkemail.example.org"}'),
+                   90590=contact(caption='Herr Inhaber R. Wiese, Das Perfekte Haus', emailAddresses='{ "main": "515217@kkemail.example.org"}'),
-                   90629=contact(caption='Ragnar Richter ', emailAddresses='{ "main": "mail@ragnar-richter..example.org"}'),
+                   90629=contact(caption='Ragnar Richter', emailAddresses='{ "main": "mail@ragnar-richter..example.org"}'),
-                   90677=contact(caption='Eike Henning ', emailAddresses='{ "main": "hostsharing@eike-henning..example.org"}'),
+                   90677=contact(caption='Eike Henning', emailAddresses='{ "main": "hostsharing@eike-henning..example.org"}'),
-                   90698=contact(caption='Jan Henning ', emailAddresses='{ "main": "mail@jan-henning.example.org"}')
+                   90698=contact(caption='Jan Henning', emailAddresses='{ "main": "mail@jan-henning.example.org"}')
                 }
                 """);
         assertThat(toJsonFormattedString(persons)).isEqualToIgnoringWhitespace("""
                 {
-                   100=person(personType='??', tradeName='Michael Mellis', familyName='Mellis', givenName='Michael'),
+                   100=person(personType='??', tradeName='Michael Mellis', salutation='Herr', familyName='Mellis', givenName='Michael'),
                    1200=person(personType='LP', tradeName='JM e.K.'),
-                   1201=person(personType='LP', tradeName='JM GmbH', familyName='Meyer-Billing', givenName='Jenny'),
+                   1201=person(personType='LP', tradeName='JM GmbH', salutation='Frau', title='Dr.', familyName='Meyer-Billing', givenName='Jenny'),
-                   1202=person(personType='LP', tradeName='JM GmbH', familyName='Meyer-Operation', givenName='Andrew'),
+                   1202=person(personType='LP', tradeName='JM GmbH', salutation='Herr', familyName='Meyer-Operation', givenName='Andrew'),
-                   1203=person(personType='LP', tradeName='JM GmbH', familyName='Meyer-Contract', givenName='Philip'),
+                   1203=person(personType='LP', tradeName='JM GmbH', salutation='Herr', familyName='Meyer-Contract', givenName='Philip'),
-                   1204=person(personType='LP', tradeName='JM GmbH', familyName='Meyer-VIP', givenName='Tammy'),
+                   1204=person(personType='LP', tradeName='JM GmbH', salutation='Frau', familyName='Meyer-VIP', givenName='Tammy'),
                    1301=person(personType='??', tradeName='Test PS', familyName='Schmidt', givenName='Petra'),
-                   132=person(personType='??', tradeName='Ragnar IT-Beratung', familyName='Richter', givenName='Ragnar'),
+                   132=person(personType='??', tradeName='Ragnar IT-Beratung', salutation='Herr', familyName='Richter', givenName='Ragnar'),
-                   1401=person(personType='NP', familyName='Fanninga', givenName='Frauke'),
+                   1401=person(personType='NP', salutation='Frau', familyName='Fanninga', givenName='Frauke'),
-                   1501=person(personType='NP', familyName='Camus', givenName='Cecilia'),
+                   1501=person(personType='NP', salutation='Frau', familyName='Camus', givenName='Cecilia'),
-                   212=person(personType='LP', tradeName='Hostsharing e.G.', familyName='Hostsharing', givenName='Hostmaster'),
+                   212=person(personType='LP', tradeName='Hostsharing e.G.', salutation='Firma', familyName='Hostsharing', givenName='Hostmaster'),
-                   90436=person(personType='??', tradeName='Wasserwerk Südholstein', familyName='Milberg', givenName='Christiane'),
+                   90436=person(personType='??', tradeName='Wasserwerk Südholstein', salutation='Frau', familyName='Milberg', givenName='Christiane'),
-                   90437=person(personType='??', tradeName='Das Perfekte Haus', familyName='Wiese', givenName='Richard'),
+                   90437=person(personType='??', tradeName='Das Perfekte Haus', salutation='Herr', familyName='Wiese', givenName='Richard'),
-                   90438=person(personType='??', tradeName='Wasswerwerk Südholstein', familyName='Metzger', givenName='Karim'),
+                   90438=person(personType='??', tradeName='Wasswerwerk Südholstein', salutation='Herr', familyName='Metzger', givenName='Karim'),
-                   90590=person(personType='??', tradeName='Das Perfekte Haus', familyName='Wiese', givenName='Inhaber R.'),
+                   90590=person(personType='??', tradeName='Das Perfekte Haus', salutation='Herr', familyName='Wiese', givenName='Inhaber R.'),
                    90629=person(personType='NP', familyName='Richter', givenName='Ragnar'),
                    90677=person(personType='NP', familyName='Henning', givenName='Eike'),
                    90698=person(personType='NP', familyName='Henning', givenName='Jan')
@@ -272,71 +275,81 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
                 """);
         assertThat(toJsonFormattedString(relations)).isEqualToIgnoringWhitespace("""
                 {
-                   2000000=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000000=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000001=rel(anchor='?? Michael Mellis', type='DEBITOR', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000001=rel(anchor='?? Michael Mellis', type='DEBITOR', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000002=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter , Ragnar IT-Beratung'),
+                   2000002=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000003=rel(anchor='?? Ragnar IT-Beratung', type='DEBITOR', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter , Ragnar IT-Beratung'),
+                   2000003=rel(anchor='?? Ragnar IT-Beratung', type='DEBITOR', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000004=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing , Hostsharing e.G.'),
+                   2000004=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing, Hostsharing e.G.'),
-                   2000005=rel(anchor='LP Hostsharing e.G.', type='DEBITOR', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing , Hostsharing e.G.'),
+                   2000005=rel(anchor='LP Hostsharing e.G.', type='DEBITOR', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing, Hostsharing e.G.'),
-                   2000006=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000006=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000007=rel(anchor='?? Wasserwerk Südholstein', type='DEBITOR', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000007=rel(anchor='?? Wasserwerk Südholstein', type='DEBITOR', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000008=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000008=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000009=rel(anchor='?? Das Perfekte Haus', type='DEBITOR', holder='?? Das Perfekte Haus', contact='Herr Inhaber R. Wiese , Das Perfekte Haus'),
+                   2000009=rel(anchor='?? Das Perfekte Haus', type='DEBITOR', holder='?? Das Perfekte Haus', contact='Herr Inhaber R. Wiese, Das Perfekte Haus'),
-                   2000010=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract , JM GmbH'),
+                   2000010=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract, JM GmbH'),
-                   2000011=rel(anchor='LP JM GmbH', type='DEBITOR', holder='LP JM GmbH', contact='Frau Dr. Jenny Meyer-Billing , JM GmbH'),
+                   2000011=rel(anchor='LP JM GmbH', type='DEBITOR', holder='LP JM GmbH', contact='Frau Dr. Jenny Meyer-Billing, JM GmbH'),
-                   2000012=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Test PS', contact='Petra Schmidt , Test PS'),
+                   2000012=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='?? Test PS', contact='Petra Schmidt, Test PS'),
-                   2000013=rel(anchor='?? Test PS', type='DEBITOR', holder='?? Test PS', contact='Petra Schmidt , Test PS'),
+                   2000013=rel(anchor='?? Test PS', type='DEBITOR', holder='?? Test PS', contact='Petra Schmidt, Test PS'),
-                   2000014=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus '),
+                   2000014=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus'),
-                   2000015=rel(anchor='NP Camus, Cecilia', type='DEBITOR', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus '),
+                   2000015=rel(anchor='NP Camus, Cecilia', type='DEBITOR', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus'),
                    2000016=rel(anchor='LP Hostsharing e.G.', type='PARTNER', holder='null null, null'),
                    2000017=rel(anchor='null null, null', type='DEBITOR'),
-                   2000018=rel(anchor='LP Hostsharing e.G.', type='OPERATIONS', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing , Hostsharing e.G.'),
+                   2000018=rel(anchor='LP Hostsharing e.G.', type='OPERATIONS_ALERT', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing, Hostsharing e.G.'),
-                   2000019=rel(anchor='LP Hostsharing e.G.', type='REPRESENTATIVE', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing , Hostsharing e.G.'),
+                   2000019=rel(anchor='LP Hostsharing e.G.', type='OPERATIONS', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing, Hostsharing e.G.'),
-                   2000020=rel(anchor='?? Michael Mellis', type='OPERATIONS', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000020=rel(anchor='LP Hostsharing e.G.', type='REPRESENTATIVE', holder='LP Hostsharing e.G.', contact='Firma Hostmaster Hostsharing, Hostsharing e.G.'),
-                   2000021=rel(anchor='?? Michael Mellis', type='REPRESENTATIVE', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000021=rel(anchor='?? Michael Mellis', type='OPERATIONS_ALERT', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000022=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='operations-discussion', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000022=rel(anchor='?? Michael Mellis', type='OPERATIONS', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000023=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='operations-announce', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000023=rel(anchor='?? Michael Mellis', type='REPRESENTATIVE', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000024=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='generalversammlung', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000024=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='operations-discussion', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000025=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='members-announce', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000025=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='operations-announce', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000026=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='members-discussion', holder='?? Michael Mellis', contact='Herr Michael Mellis , Michael Mellis'),
+                   2000026=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='generalversammlung', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000027=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter , Ragnar IT-Beratung'),
+                   2000027=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='members-announce', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000028=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-discussion', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter , Ragnar IT-Beratung'),
+                   2000028=rel(anchor='?? Michael Mellis', type='SUBSCRIBER', mark='members-discussion', holder='?? Michael Mellis', contact='Herr Michael Mellis, Michael Mellis'),
-                   2000029=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-announce', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter , Ragnar IT-Beratung'),
+                   2000029=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS_ALERT', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000030=rel(anchor='LP JM GmbH', type='EX_PARTNER', holder='LP JM e.K.', contact='JM e.K.'),
+                   2000030=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000031=rel(anchor='LP JM GmbH', type='OPERATIONS', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation , JM GmbH'),
+                   2000031=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-discussion', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000032=rel(anchor='LP JM GmbH', type='VIP_CONTACT', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation , JM GmbH'),
+                   2000032=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-announce', holder='?? Ragnar IT-Beratung', contact='Herr Ragnar Richter, Ragnar IT-Beratung'),
-                   2000033=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='operations-announce', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation , JM GmbH'),
+                   2000033=rel(anchor='LP JM GmbH', type='EX_PARTNER', holder='LP JM e.K.', contact='JM e.K.'),
-                   2000034=rel(anchor='LP JM GmbH', type='REPRESENTATIVE', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract , JM GmbH'),
+                   2000034=rel(anchor='LP JM GmbH', type='OPERATIONS_ALERT', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation, JM GmbH'),
-                   2000035=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='members-announce', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract , JM GmbH'),
+                   2000035=rel(anchor='LP JM GmbH', type='OPERATIONS', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation, JM GmbH'),
-                   2000036=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='customers-announce', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract , JM GmbH'),
+                   2000036=rel(anchor='LP JM GmbH', type='VIP_CONTACT', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation, JM GmbH'),
-                   2000037=rel(anchor='LP JM GmbH', type='VIP_CONTACT', holder='LP JM GmbH', contact='Frau Tammy Meyer-VIP , JM GmbH'),
+                   2000037=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='operations-announce', holder='LP JM GmbH', contact='Herr Andrew Meyer-Operation, JM GmbH'),
-                   2000038=rel(anchor='?? Test PS', type='OPERATIONS', holder='?? Test PS', contact='Petra Schmidt , Test PS'),
+                   2000038=rel(anchor='LP JM GmbH', type='REPRESENTATIVE', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract, JM GmbH'),
-                   2000039=rel(anchor='?? Test PS', type='REPRESENTATIVE', holder='?? Test PS', contact='Petra Schmidt , Test PS'),
+                   2000039=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='members-announce', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract, JM GmbH'),
-                   2000040=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='operations-announce', holder='NP Fanninga, Frauke', contact='Frau Frauke Fanninga '),
+                   2000040=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='customers-announce', holder='LP JM GmbH', contact='Herr Philip Meyer-Contract, JM GmbH'),
-                   2000041=rel(anchor='NP Camus, Cecilia', type='OPERATIONS', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus '),
+                   2000041=rel(anchor='LP JM GmbH', type='VIP_CONTACT', holder='LP JM GmbH', contact='Frau Tammy Meyer-VIP, JM GmbH'),
-                   2000042=rel(anchor='NP Camus, Cecilia', type='REPRESENTATIVE', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus '),
+                   2000042=rel(anchor='?? Test PS', type='OPERATIONS_ALERT', holder='?? Test PS', contact='Petra Schmidt, Test PS'),
-                   2000043=rel(anchor='?? Wasserwerk Südholstein', type='REPRESENTATIVE', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000043=rel(anchor='?? Test PS', type='OPERATIONS', holder='?? Test PS', contact='Petra Schmidt, Test PS'),
-                   2000044=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='generalversammlung', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000044=rel(anchor='?? Test PS', type='REPRESENTATIVE', holder='?? Test PS', contact='Petra Schmidt, Test PS'),
-                   2000045=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='members-announce', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000045=rel(anchor='LP JM GmbH', type='SUBSCRIBER', mark='operations-announce', holder='NP Fanninga, Frauke', contact='Frau Frauke Fanninga'),
-                   2000046=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='members-discussion', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg , Wasserwerk Südholstein'),
+                   2000046=rel(anchor='NP Camus, Cecilia', type='OPERATIONS_ALERT', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus'),
-                   2000047=rel(anchor='?? Das Perfekte Haus', type='OPERATIONS', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000047=rel(anchor='NP Camus, Cecilia', type='OPERATIONS', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus'),
-                   2000048=rel(anchor='?? Das Perfekte Haus', type='REPRESENTATIVE', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000048=rel(anchor='NP Camus, Cecilia', type='REPRESENTATIVE', holder='NP Camus, Cecilia', contact='Frau Cecilia Camus'),
-                   2000049=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='operations-discussion', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000049=rel(anchor='?? Wasserwerk Südholstein', type='REPRESENTATIVE', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000050=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='operations-announce', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000050=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='generalversammlung', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000051=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='generalversammlung', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000051=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='members-announce', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000052=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='members-announce', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000052=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='members-discussion', holder='?? Wasserwerk Südholstein', contact='Frau Christiane Milberg, Wasserwerk Südholstein'),
-                   2000053=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='members-discussion', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese , Das Perfekte Haus'),
+                   2000053=rel(anchor='?? Das Perfekte Haus', type='OPERATIONS_ALERT', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000054=rel(anchor='?? Wasserwerk Südholstein', type='OPERATIONS', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger , Wasswerwerk Südholstein'),
+                   2000054=rel(anchor='?? Das Perfekte Haus', type='OPERATIONS', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000055=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='operations-discussion', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger , Wasswerwerk Südholstein'),
+                   2000055=rel(anchor='?? Das Perfekte Haus', type='REPRESENTATIVE', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000056=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='operations-announce', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger , Wasswerwerk Südholstein'),
+                   2000056=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='operations-discussion', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000057=rel(anchor='?? Ragnar IT-Beratung', type='REPRESENTATIVE', holder='NP Richter, Ragnar', contact='Ragnar Richter '),
+                   2000057=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='operations-announce', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000058=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='generalversammlung', holder='NP Richter, Ragnar', contact='Ragnar Richter '),
+                   2000058=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='generalversammlung', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000059=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='members-announce', holder='NP Richter, Ragnar', contact='Ragnar Richter '),
+                   2000059=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='members-announce', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000060=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='members-discussion', holder='NP Richter, Ragnar', contact='Ragnar Richter '),
+                   2000060=rel(anchor='?? Das Perfekte Haus', type='SUBSCRIBER', mark='members-discussion', holder='?? Das Perfekte Haus', contact='Herr Richard Wiese, Das Perfekte Haus'),
-                   2000061=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='NP Henning, Eike', contact='Eike Henning '),
+                   2000061=rel(anchor='?? Wasserwerk Südholstein', type='OPERATIONS_ALERT', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger, Wasswerwerk Südholstein'),
-                   2000062=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-discussion', holder='NP Henning, Eike', contact='Eike Henning '),
+                   2000062=rel(anchor='?? Wasserwerk Südholstein', type='OPERATIONS', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger, Wasswerwerk Südholstein'),
-                   2000063=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-announce', holder='NP Henning, Eike', contact='Eike Henning '),
+                   2000063=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='operations-discussion', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger, Wasswerwerk Südholstein'),
-                   2000064=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='NP Henning, Jan', contact='Jan Henning ')
+                   2000064=rel(anchor='?? Wasserwerk Südholstein', type='SUBSCRIBER', mark='operations-announce', holder='?? Wasswerwerk Südholstein', contact='Herr Karim Metzger, Wasswerwerk Südholstein'),
+                   2000065=rel(anchor='?? Ragnar IT-Beratung', type='REPRESENTATIVE', holder='NP Richter, Ragnar', contact='Ragnar Richter'),
+                   2000066=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='generalversammlung', holder='NP Richter, Ragnar', contact='Ragnar Richter'),
+                   2000067=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='members-announce', holder='NP Richter, Ragnar', contact='Ragnar Richter'),
+                   2000068=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='members-discussion', holder='NP Richter, Ragnar', contact='Ragnar Richter'),
+                   2000069=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS_ALERT', holder='NP Henning, Eike', contact='Eike Henning'),
+                   2000070=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='NP Henning, Eike', contact='Eike Henning'),
+                   2000071=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-discussion', holder='NP Henning, Eike', contact='Eike Henning'),
+                   2000072=rel(anchor='?? Ragnar IT-Beratung', type='SUBSCRIBER', mark='operations-announce', holder='NP Henning, Eike', contact='Eike Henning'),
+                   2000073=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS_ALERT', holder='NP Henning, Jan', contact='Jan Henning'),
+                   2000074=rel(anchor='?? Ragnar IT-Beratung', type='OPERATIONS', holder='NP Henning, Jan', contact='Jan Henning')
                 }
                 """);
     }
@@ -502,7 +515,7 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
         // this happens if a natural person is marked as 'contractual' for itself
         final var idsToRemove = new HashSet<Integer>();
         relations.forEach((id, r) -> {
-            if (r.getHolder() == r.getAnchor()) {
+            if (r.getType() == HsOfficeRelationType.REPRESENTATIVE && r.getHolder() == r.getAnchor()) {
                 idsToRemove.add(id);
             }
         });
@@ -670,7 +683,7 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
         jpaAttempt.transacted(() -> {
             context(rbacSuperuser);
             coopShares.forEach(this::persist);
-            updateLegacyIds(coopShares, "hs_office.coopsharestransaction_legacy_id", "member_share_id");
+            updateLegacyIds(coopShares, "hs_office.coopsharetx_legacy_id", "member_share_id");
 
         }).assertSuccessful();
 
@@ -958,6 +971,9 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
 
                     HsOfficePersonEntity contactPerson = partnerPerson;
                     if (!StringUtils.equals(rec.getString("firma"), partnerPerson.getTradeName()) ||
+                            partnerPerson.getPersonType() != determinePersonType(rec) ||
+                            !StringUtils.equals(rec.getString("title"), partnerPerson.getTitle()) ||
+                            !StringUtils.equals(rec.getString("salut"), partnerPerson.getSalutation()) ||
                             !StringUtils.equals(rec.getString("first_name"), partnerPerson.getGivenName()) ||
                             !StringUtils.equals(rec.getString("last_name"), partnerPerson.getFamilyName())) {
                         contactPerson = addPerson(HsOfficePersonEntity.builder().build(), rec);
@@ -976,6 +992,10 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
                         debitor.getDebitorRel().setContact(contact);
                     }
                     if (containsRole(rec, "operation")) {
+                        addRelation(HsOfficeRelationType.OPERATIONS_ALERT, partnerPerson, contactPerson, contact);
+                        addRelation(HsOfficeRelationType.OPERATIONS, partnerPerson, contactPerson, contact);
+                    }
+                    if (containsRole(rec, "silent")) {
                         addRelation(HsOfficeRelationType.OPERATIONS, partnerPerson, contactPerson, contact);
                     }
                     if (containsRole(rec, "contractual")) {
@@ -1053,34 +1073,60 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
     }
 
     private HsOfficePersonEntity addPerson(final HsOfficePersonEntity person, final Record contactRecord) {
-        // TODO: title+salutation: add to person
+        person.setSalutation(contactRecord.getString("salut"));
+        person.setTitle(contactRecord.getString("title"));
         person.setGivenName(contactRecord.getString("first_name"));
         person.setFamilyName(contactRecord.getString("last_name"));
         person.setTradeName(contactRecord.getString("firma"));
-        determinePersonType(person, contactRecord.getString("roles"));
+        person.setPersonType(determinePersonType(contactRecord));
 
         persons.put(contactRecord.getInteger("contact_id"), person);
         return person;
     }
 
-    private static void determinePersonType(final HsOfficePersonEntity person, final String roles) {
+    private static HsOfficePersonType determinePersonType(final Record contactRecord) {
-        if (person.getTradeName().isBlank()) {
+        String roles = contactRecord.getString("roles");
-            person.setPersonType(HsOfficePersonType.NATURAL_PERSON);
+        String country = contactRecord.getString("country");
+        String familyName = contactRecord.getString("last_name");
+        String givenName = contactRecord.getString("first_name");
+        String tradeName = contactRecord.getString("firma");
+
+        if (PERSON_TYPES_BY_CONTACT.containsKey(contactRecord.getInteger("contact_id"))) {
+            return PERSON_TYPES_BY_CONTACT.get(contactRecord.getInteger("contact_id"));
+        }
+
+        if (tradeName.isBlank() || tradeName.startsWith("verstorben")) {
+            return HsOfficePersonType.NATURAL_PERSON;
         } else
             // contractual && !partner with a firm and a natural person name
             // should actually be split up into two persons
             // but the legacy database consists such records
-            if (roles.contains("contractual") && !roles.contains("partner") &&
+
-                    !person.getFamilyName().isBlank() && !person.getGivenName().isBlank()) {
+            if (endsWithWord(tradeName, "OHG", "GbR", "KG", "UG", "PartGmbB", "mbB")) {
-                person.setPersonType(HsOfficePersonType.NATURAL_PERSON);
+                return HsOfficePersonType.INCORPORATED_FIRM; // Personengesellschaft. Gesellschafter haften persönlich.
-            } else if (endsWithWord(person.getTradeName(), "e.K.", "e.G.", "eG", "GmbH", "AG", "KG")) {
+            } else if (containsWord(tradeName, "e.K.", "e.G.", "eG", "gGmbH", "GmbH", "mbH", "AG", "e.V.", "eV", "e.V")
-                person.setPersonType(HsOfficePersonType.LEGAL_PERSON);
+                || tradeName.toLowerCase().contains("haftungsbeschränkt")
-            } else if (endsWithWord(person.getTradeName(), "OHG")) {
+                || tradeName.toLowerCase().contains("stiftung")
-                person.setPersonType(HsOfficePersonType.INCORPORATED_FIRM);
+                || tradeName.toLowerCase().contains("stichting")
-            } else if (endsWithWord(person.getTradeName(), "GbR")) {
+                || tradeName.toLowerCase().contains("foundation")
-                person.setPersonType(HsOfficePersonType.INCORPORATED_FIRM);
+                || tradeName.toLowerCase().contains("schule")
+                || tradeName.toLowerCase().contains("verein")
+                || tradeName.toLowerCase().contains("gewerkschaft")
+                || tradeName.toLowerCase().contains("gesellschaft")
+                || tradeName.toLowerCase().contains("kirche")
+                || tradeName.toLowerCase().contains("fraktion")
+                || tradeName.toLowerCase().contains("landkreis")
+                || tradeName.toLowerCase().contains("behörde")
+                || tradeName.toLowerCase().contains("bundesamt")
+                || tradeName.toLowerCase().contains("bezirksamt")
+                ) {
+                return HsOfficePersonType.LEGAL_PERSON; // Haftungsbeschränkt
+            } else if (roles.contains("contractual") && !roles.contains("partner") &&
+                   !familyName.isBlank() && !givenName.isBlank()) {
+                // REPRESENTATIVES are always natural persons
+                return HsOfficePersonType.NATURAL_PERSON;
             } else {
-                person.setPersonType(HsOfficePersonType.UNKNOWN_PERSON_TYPE);
+                return HsOfficePersonType.UNKNOWN_PERSON_TYPE;
             }
     }
 
@@ -1094,6 +1140,19 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
         return false;
     }
 
+    private static boolean containsWord(final String value, final String... endings) {
+        final var lowerCaseValue = value.toLowerCase();
+        for (String ending : endings) {
+            if (lowerCaseValue.equals(ending.toLowerCase()) ||
+                lowerCaseValue.startsWith(ending.toLowerCase() + " ") ||
+                lowerCaseValue.contains(" " + ending.toLowerCase() + " ") ||
+                lowerCaseValue.endsWith(" " + ending.toLowerCase())) {
+                return true;
+            }
+        }
+        return false;
+    }
+
     private void verifyContainsOnlyKnownRoles(final String roles) {
         final var allowedRolesSet = stream(KNOWN_ROLES).collect(Collectors.toSet());
         final var givenRolesSet = stream(roles.replace(" ", "").split(",")).collect(Collectors.toSet());
@@ -1158,13 +1217,13 @@ public abstract class BaseOfficeDataImport extends CsvDataImport {
             final String firm) {
         final var result = new StringBuilder();
         if (isNotBlank(salut))
-            result.append(salut + " ");
+            result.append((isBlank(result) ? "" : " ") + salut);
         if (isNotBlank(title))
-            result.append(title + " ");
+            result.append((isBlank(result) ? "" : " ") + title);
         if (isNotBlank(firstname))
-            result.append(firstname + " ");
+            result.append((isBlank(result) ? "" : " ") + firstname);
         if (isNotBlank(lastname))
-            result.append(lastname + " ");
+            result.append((isBlank(result) ? "" : " ") + lastname);
         if (isNotBlank(firm)) {
             result.append((isBlank(result) ? "" : ", ") + firm);
         }

src/test/java/net/hostsharing/hsadminng/hs/migration/CsvDataImport.java

@@ -255,7 +255,7 @@ public class CsvDataImport extends ContextBasedTest {
             em.createNativeQuery("delete from hs_office.coopassettx where true").executeUpdate();
             em.createNativeQuery("delete from hs_office.coopassettx_legacy_id where true").executeUpdate();
             em.createNativeQuery("delete from hs_office.coopsharetx where true").executeUpdate();
-            em.createNativeQuery("delete from hs_office.coopsharestransaction_legacy_id where true").executeUpdate();
+            em.createNativeQuery("delete from hs_office.coopsharetx_legacy_id where true").executeUpdate();
             em.createNativeQuery("delete from hs_office.membership where true").executeUpdate();
             em.createNativeQuery("delete from hs_office.sepamandate where true").executeUpdate();
             em.createNativeQuery("delete from hs_office.sepamandate_legacy_id where true").executeUpdate();
@@ -275,7 +275,7 @@ public class CsvDataImport extends ContextBasedTest {
             em.createNativeQuery("alter sequence hs_office.contact_legacy_id_seq restart with 1000000000;").executeUpdate();
             em.createNativeQuery("alter sequence hs_office.coopassettx_legacy_id_seq restart with 1000000000;")
                     .executeUpdate();
-            em.createNativeQuery("alter sequence public.hs_office.coopsharestransaction_legacy_id_seq restart with 1000000000;")
+            em.createNativeQuery("alter sequence public.hs_office.coopsharetx_legacy_id_seq restart with 1000000000;")
                     .executeUpdate();
             em.createNativeQuery("alter sequence public.hs_office.partner_legacy_id_seq restart with 1000000000;")
                     .executeUpdate();

src/test/java/net/hostsharing/hsadminng/hs/migration/ImportOfficeData.java

@@ -8,8 +8,6 @@ import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
 import org.springframework.context.annotation.Import;
 import org.springframework.test.annotation.DirtiesContext;
 
-import static org.assertj.core.api.Assertions.assertThat;
-
 /*
  * This 'test' includes the complete legacy 'office' data import.
  *
@@ -58,9 +56,4 @@ import static org.assertj.core.api.Assertions.assertThat;
 @TestMethodOrder(MethodOrderer.OrderAnnotation.class)
 @ExtendWith(OrderedDependedTestsExtension.class)
 public class ImportOfficeData extends BaseOfficeDataImport {
-
-    @BeforeEach
-    void check() {
-        assertThat(jdbcUrl).isEqualTo("jdbc:tc:postgresql:15.5-bookworm:///importOfficeDataTC");
-    }
 }

src/test/resources/migration/dump.sh

@@ -36,13 +36,11 @@ dump "select sepa_mandat_id, bp_id, bank_customer, bank_name, bank_iban, bank_bi
 
 dump "select member_asset_id, bp_id, date, action, amount, comment
      from member_asset
-     WHERE bp_id NOT IN (511912)
      order by member_asset_id" \
      "office/asset_transactions.csv"
 
 dump "select member_share_id, bp_id, date, action, quantity, comment
       from member_share
-     WHERE bp_id NOT IN (511912)
       order by member_share_id" \
      "office/share_transactions.csv"
 
@@ -85,7 +83,7 @@ dump "select domain_id, domain_name, domain_since, domain_dns_master, domain_own
 dump "select emailaddr_id, domain_id, localpart, subdomain, target
       from emailaddr
       order by emailaddr_id" \
-     "emailaddr.csv"
+     "hosting/emailaddr.csv"
 
 dump "select emailalias_id, pac_id, name, target
       from emailalias