importing business-partners + contacts from CSV

Michael Hoennig

2022-10-29 23a81a9a07ad727575d20dc9b0bf33a17e03fdf3

importing business-partners + contacts from CSV

 src/main/java/net/hostsharing/hsadminng/hs/office/membership/HsOfficeReasonForTermination.java

@@ -1,5 +1,5 @@
package net.hostsharing.hsadminng.hs.office.membership;

public enum HsOfficeReasonForTermination {
    NONE, CANCELLATION, TRANSFER, DEATH, LIQUIDATION, EXPULSION;
    NONE, CANCELLATION, TRANSFER, DEATH, LIQUIDATION, EXPULSION, UNKNOWN;
}

 src/main/java/net/hostsharing/hsadminng/hs/office/person/HsOfficePersonType.java

@@ -1,6 +1,7 @@
package net.hostsharing.hsadminng.hs.office.person;

public enum HsOfficePersonType {
    UNKNOWN,
    NATURAL,
    LEGAL,
    SOLE_REPRESENTATION,

 src/main/java/net/hostsharing/hsadminng/hs/office/sepamandate/HsOfficeSepaMandateEntity.java

@@ -54,6 +54,7 @@

    @Column(name = "validity", columnDefinition = "daterange")
    @Type(PostgreSQLRangeType.class)
    @Builder.Default
    private Range<LocalDate> validity = Range.infinite(LocalDate.class);

    public void setValidFrom(final LocalDate validFrom) {

 src/main/resources/db/changelog/210-hs-office-person.sql

@@ -4,7 +4,7 @@
--changeset hs-office-person-MAIN-TABLE:1 endDelimiter:--//
-- ----------------------------------------------------------------------------

CREATE TYPE HsOfficePersonType AS ENUM ('NATURAL', 'LEGAL', 'SOLE_REPRESENTATION', 'JOINT_REPRESENTATION');
CREATE TYPE HsOfficePersonType AS ENUM ('UNKNOWN', 'NATURAL', 'LEGAL', 'SOLE_REPRESENTATION', 'JOINT_REPRESENTATION');

CREATE CAST (character varying as HsOfficePersonType) WITH INOUT AS IMPLICIT;


 src/main/resources/db/changelog/300-hs-office-membership.sql

@@ -4,7 +4,7 @@
--changeset hs-office-membership-MAIN-TABLE:1 endDelimiter:--//
-- ----------------------------------------------------------------------------

CREATE TYPE HsOfficeReasonForTermination AS ENUM ('NONE', 'CANCELLATION', 'TRANSFER', 'DEATH', 'LIQUIDATION', 'EXPULSION');
CREATE TYPE HsOfficeReasonForTermination AS ENUM ('NONE', 'CANCELLATION', 'TRANSFER', 'DEATH', 'LIQUIDATION', 'EXPULSION', 'UNKNOWN');

CREATE CAST (character varying as HsOfficeReasonForTermination) WITH INOUT AS IMPLICIT;


 src/test/java/net/hostsharing/hsadminng/hs/office/migration/ImportBusinessPartners.java

@@ -3,10 +3,19 @@
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import net.hostsharing.hsadminng.context.Context;
import net.hostsharing.hsadminng.hs.office.contact.HsOfficeContactEntity;
import net.hostsharing.hsadminng.hs.office.debitor.HsOfficeDebitorEntity;
import net.hostsharing.hsadminng.hs.office.membership.HsOfficeMembershipEntity;
import net.hostsharing.hsadminng.hs.office.membership.HsOfficeReasonForTermination;
import net.hostsharing.hsadminng.hs.office.partner.HsOfficePartnerDetailsEntity;
import net.hostsharing.hsadminng.hs.office.partner.HsOfficePartnerEntity;
import net.hostsharing.hsadminng.hs.office.person.HsOfficePersonEntity;
import org.junit.jupiter.api.Test;
import net.hostsharing.hsadminng.hs.office.person.HsOfficePersonType;
import net.hostsharing.test.JpaAttempt;
import org.junit.jupiter.api.*;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.context.annotation.Import;

import jakarta.validation.constraints.NotNull;
import java.io.IOException;
@@ -14,27 +23,53 @@
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.assertj.core.api.Assertions.assertThat;
import static net.hostsharing.hsadminng.mapper.PostgresDateRange.toPostgresDateRange;
import static org.apache.commons.lang3.StringUtils.isBlank;
import static org.apache.commons.lang3.StringUtils.isNotBlank;

@DataJpaTest
@Import({ Context.class, JpaAttempt.class })
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class ImportBusinessPartners {

    private Map<Integer, HsOfficePartnerEntity> partners = new HashMap<>();
    private Map<Integer, HsOfficeMembershipEntity> members = new HashMap<>();
    private static Map<Integer, HsOfficePartnerEntity> partners = new HashMap<>();
    private static Map<Integer, HsOfficeDebitorEntity> debitors = new HashMap<>();
    private static Map<Integer, HsOfficeMembershipEntity> memberships = new HashMap<>();

    @Test
    void importsBusinessPartners() throws Exception {
    @Order(1)
    void importsBusinessPartners() {

        try (Reader reader = resourceReader("migration/business_partners.csv")) {
        try (Reader reader = resourceReader("migration/business-partners.csv")) {
            final var records = readAllLines(reader);

            importsBusinessPartners(records);

            assertThat(records).hasSize(4);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    @Order(2)
    void importsContacts() {

        try (Reader reader = resourceReader("migration/contacts.csv")) {
            final var records = readAllLines(reader);
            importsContacts(records);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @AfterAll
    static void afterAll() {
        System.out.println(partners);
        System.out.println(debitors);
        System.out.println(memberships);

    }

@@ -54,14 +89,157 @@
    }

    private void importsBusinessPartners(final List<String[]> records) {
        records.forEach( record -> {
        records.stream()
                .map(this::trimAll)
                .forEach(record -> {

                    final var partner = HsOfficePartnerEntity.builder()
                            .details(HsOfficePartnerDetailsEntity.builder().build())
                            .contact(HsOfficeContactEntity.builder().build())
                            .person(HsOfficePersonEntity.builder()
                                    .personType(HsOfficePersonType.UNKNOWN) // TODO
                                    .build())
                            .build();
                    partners.put(toInt(record[0]), partner);

            HsOfficePersonEntity.builder()
                    .tradeName(record[])
                    final var debitor = HsOfficeDebitorEntity.builder()
                            .partner(partner)
                            .debitorNumber(toInt(record[1]))
                            // .memberCode(record[2]) TODO
                            .partner(partner)
                            .billingContact(partner.getContact())
                            // .free(toBool(record[8])) TODO
                            .vatBusiness("GROSS".equals(record[10]))
                            .vatId(record[11])
                            .build();

                    .build();
        });
                    partners.put(toInt(record[0]), partner);

                    if (isNotBlank(record[3])) {
                        final var membership = HsOfficeMembershipEntity.builder()
                                .partner(partner)
                                .memberNumber(toInt(record[1]))
                                .validity(toPostgresDateRange(localDate(record[3]), localDate(record[4])))
                                .reasonForTermination(
                                        isBlank(record[4])
                                                ? HsOfficeReasonForTermination.NONE
                                                : HsOfficeReasonForTermination.UNKNOWN) // TODO
                                .mainDebitor(debitor)
                                .build();
                        memberships.put(toInt(record[0]), membership);
                    }
                });
    }

    private void importsContacts(final List<String[]> records) {
        records.stream()
                .map(this::trimAll)
                .forEach(record -> {

                    final var partner = partners.get(toInt(record[1]));

                    final var contact = partner.getContact();
                    contact.setLabel(toLabel(record[2], record[5], record[3], record[4], record[6]));
                    contact.setEmailAddresses(record[16]);
                    contact.setPostalAddress(toAddress(record));
                    contact.setPhoneNumbers(toPhoneNumbers(record));

                    final var person = partner.getPerson();
                    person.setTradeName(record[6]);
                    // TODO: title+salutation
                    person.setFamilyName(record[3]);
                    person.setGivenName(record[4]);
                });
    }

    private String[] trimAll(final String[] record) {
        for (int i = 0; i < record.length; ++i) {
            if (record[i] != null) {
                record[i] = record[i].trim();
            }
        }
        return record;
    }

    private String toPhoneNumbers(final String[] record) {
        final var result = new StringBuilder("{\n");
        if (isNotBlank(record[12]))
            result.append("    \"private\": " + "\"" + record[12] + "\",\n");
        if (isNotBlank(record[13]))
            result.append("    \"office\": " + "\"" + record[13] + "\",\n");
        if (isNotBlank(record[14]))
            result.append("    \"mobile\": " + "\"" + record[14] + "\",\n");
        if (isNotBlank(record[15]))
            result.append("    \"fax\": " + "\"" + record[15] + "\",\n");
        return (result + "}").replace("\",\n}", "\"\n}");
    }

    private String toAddress(final String[] record) {
        final var result = new StringBuilder();
        final var name = toName(record[2], record[5], record[3], record[4]);
        if (isNotBlank(name))
            result.append(name + "\n");
        if (isNotBlank(record[6]))
            result.append(record[6] + "\n");
        if (isNotBlank(record[7]))
            result.append("c/o " + record[7] + "\n");
        if (isNotBlank(record[8]))
            result.append(record[8] + "\n");
        final var zipcodeAndCity = toZipcodeAndCity(record);
        if (isNotBlank(zipcodeAndCity))
            result.append(zipcodeAndCity + "\n");
        return result.toString();
    }

    private String toZipcodeAndCity(final String[] record) {
        final var result = new StringBuilder();
        if (isNotBlank(record[11]))
            result.append(record[11] + " ");
        if (isNotBlank(record[9]))
            result.append(record[9] + " ");
        if (isNotBlank(record[10]))
            result.append(record[10]);
        return result.toString();
    }

    private String toLabel(
            final String salut,
            final String title,
            final String firstname,
            final String lastname,
            final String firm) {
        final var result = new StringBuilder();
        if (isNotBlank(salut))
            result.append(salut + " ");
        if (isNotBlank(title))
            result.append(title + " ");
        if (isNotBlank(firstname))
            result.append(firstname + " ");
        if (isNotBlank(lastname))
            result.append(lastname + " ");
        if (result.length() > 0 && isNotBlank(firm)) {
            result.append(", " + firm);
        }
        return result.toString();
    }

    private String toName(final String salut, final String title, final String firstname, final String lastname) {
        return toLabel(salut, title, firstname, lastname, null);
    }

    private static Integer toInt(final String value) {
        return isNotBlank(value) ? Integer.parseInt(value.trim()) : 0;
    }

    private static Integer toInteger(final String value) {
        return isNotBlank(value) ? Integer.parseInt(value.trim()) : null;
    }

    private LocalDate localDate(final String dateStringNullOrBlank) {
        if (isNotBlank(dateStringNullOrBlank)) {
            return LocalDate.parse(dateStringNullOrBlank);
        }
        return null;
    }

    private Reader resourceReader(@NotNull final String resourcePath) {

 src/test/resources/migration/business-partners.csv

New file
@@ -0,0 +1,4 @@
bp_id;member_id;member_code;member_since;member_until;member_role;author_contract;nondisc_contract;free;exempt_vat;indicator_vat;uid_vat
7;10007;mih;2000-12-06;;Aufsichtsrat;2006-10-15;2001-10-15;false;false;NET;DE-VAT-007
10;10010;xyz;2000-12-06;2015-12-31;;;;false;false;GROSS;
12;11012;xxx;2021-04-01;;;;;true;true;GROSS;

 src/test/resources/migration/business_partners.csv

File was deleted