1 files deleted
8 files modified
2 files added
382 ■■■■■ changed files
src/main/java/net/hostsharing/hsadminng/hs/office/membership/HsOfficeReasonForTermination.java 2 ●●● patch | view | raw | blame | history
src/main/java/net/hostsharing/hsadminng/hs/office/person/HsOfficePersonType.java 1 ●●●● patch | view | raw | blame | history
src/main/java/net/hostsharing/hsadminng/hs/office/sepamandate/HsOfficeSepaMandateEntity.java 1 ●●●● patch | view | raw | blame | history
src/main/resources/db/changelog/210-hs-office-person.sql 2 ●●● patch | view | raw | blame | history
src/main/resources/db/changelog/300-hs-office-membership.sql 2 ●●● patch | view | raw | blame | history
src/test/java/net/hostsharing/hsadminng/hs/office/migration/ImportBusinessPartners.java 350 ●●●●● patch | view | raw | blame | history
src/test/resources/application.yml 4 ●●●● patch | view | raw | blame | history
src/test/resources/migration/business-partners.csv 4 ●●●● patch | view | raw | blame | history
src/test/resources/migration/business_partners.csv 4 ●●●● patch | view | raw | blame | history
src/test/resources/migration/contacts.csv 10 ●●●● patch | view | raw | blame | history
src/test/resources/migration/sepa-mandates.csv 2 ●●● patch | view | raw | blame | history
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
New file
@@ -0,0 +1,350 @@
package net.hostsharing.hsadminng.hs.office.migration;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import net.hostsharing.hsadminng.context.Context;
import net.hostsharing.hsadminng.context.ContextBasedTest;
import net.hostsharing.hsadminng.hs.office.bankaccount.HsOfficeBankAccountEntity;
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 net.hostsharing.hsadminng.hs.office.person.HsOfficePersonType;
import net.hostsharing.hsadminng.hs.office.sepamandate.HsOfficeSepaMandateEntity;
import net.hostsharing.test.JpaAttempt;
import org.junit.jupiter.api.MethodOrderer;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestMethodOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.mock.mockito.MockBean;
import org.springframework.context.annotation.Import;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.validation.constraints.NotNull;
import java.io.IOException;
import java.io.InputStreamReader;
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 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 extends ContextBasedTest {
    private static Map<Integer, HsOfficeContactEntity> contacts = new HashMap<>();
    private static Map<Integer, HsOfficePersonEntity> persons = 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<>();
    private static Map<Integer, HsOfficeSepaMandateEntity> sepaMandates = new HashMap<>();
    private static Map<Integer, HsOfficeBankAccountEntity> bankAccounts = new HashMap<>();
    @PersistenceContext
    EntityManager em;
    @Autowired
    JpaAttempt jpaAttempt;
    @MockBean
    HttpServletRequest request;
    @Test
    @Order(1)
    void importBusinessPartners() {
        try (Reader reader = resourceReader("migration/business-partners.csv")) {
            final var records = readAllLines(reader);
            importBusinessPartners(records);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    @Order(2)
    void importContacts() {
        try (Reader reader = resourceReader("migration/contacts.csv")) {
            final var records = readAllLines(reader);
            importContacts(records);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    @Order(3)
    void importSepaMandates() {
        try (Reader reader = resourceReader("migration/sepa-mandates.csv")) {
            final var records = readAllLines(reader);
            importSepaMandates(records);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    @Order(10)
    void persistEntities() {
        jpaAttempt.transacted(() -> {
            context("superuser-alex@hostsharing.net"); // TODO: use real user
            contacts.forEach((id, contact) -> em.persist(contact));
            persons.forEach((id, person) -> em.persist(person));
            partners.forEach((id, partner) -> em.persist(partner));
            debitors.forEach((id, debitor) -> em.persist(debitor));
            memberships.forEach((id, membership) -> em.persist(membership));
            bankAccounts.forEach((id, account) -> em.persist(account));
            sepaMandates.forEach((id, mandate) -> em.persist(mandate));
        }).assertSuccessful();
    }
    public List<String[]> readAllLines(Reader reader) throws Exception {
        final var parser = new CSVParserBuilder()
                .withSeparator(';')
                .withQuoteChar('"')
                .build();
        try (CSVReader csvReader = new CSVReaderBuilder(reader)
                .withSkipLines(1)
                .withCSVParser(parser)
                .build()) {
            return csvReader.readAll();
        }
    }
    private void importBusinessPartners(final List<String[]> records) {
        records.stream()
                .map(this::trimAll)
                .forEach(record -> {
                    final var person = HsOfficePersonEntity.builder()
                            .personType(HsOfficePersonType.UNKNOWN) // TODO
                            .build();
                    persons.put(toInt(record[0]), person);
                    final var partner = HsOfficePartnerEntity.builder()
                            .details(HsOfficePartnerDetailsEntity.builder().build())
                            .contact(HsOfficeContactEntity.builder().build())
                            .person(person)
                            .build();
                    partners.put(toInt(record[0]), partner);
                    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();
                    debitors.put(toInt(record[0]), debitor);
                    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 importSepaMandates(final List<String[]> records) {
        records.stream()
                .map(this::trimAll)
                .forEach(record -> {
                    final var debitor = debitors.get(toInt(record[1]));
                    final var sepaMandate = HsOfficeSepaMandateEntity.builder()
                            .debitor(debitor)
                            .bankAccount(HsOfficeBankAccountEntity.builder()
                                    .holder(record[2])
                                    // .bankName(record[3]) // TODO
                                    .iban(record[4])
                                    .bic(record[5])
                                    .build())
                            .reference(record[6])
                            .agreement(LocalDate.parse(record[7]))
                            .validity(toPostgresDateRange(
                                    toLocalDate(record[8]),
                                    toLocalDate(record[9])))
                            .build();
                    sepaMandates.put(toInt(record[0]), sepaMandate);
                    bankAccounts.put(toInt(record[0]), sepaMandate.getBankAccount());
                });
    }
    private void importContacts(final List<String[]> records) {
        records.stream()
                .map(this::trimAll)
                .forEach(record -> {
                    if (isNotBlank(record[17]) && record[17].contains("billing")) {
                        final var partner = partners.get(toInt(record[1]));
                        final var person = partner.getPerson();
                        person.setTradeName(record[6]);
                        // TODO: title+salutation
                        person.setFamilyName(record[3]);
                        person.setGivenName(record[4]);
                        initContact(partner.getContact(), record);
                    } else {
                        initContact(new HsOfficeContactEntity(), record);
                        // TODO: create relationship
                    }
                });
    }
    private void initContact(final HsOfficeContactEntity contact, final String[] record) {
        contacts.put(toInt(record[0]), contact);
        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));
    }
    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 LocalDate toLocalDate(final String dateString) {
        if (isNotBlank(dateString)) {
            return LocalDate.parse(dateString);
        }
        return 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) {
        return new InputStreamReader(getClass().getClassLoader().getResourceAsStream(resourcePath));
    }
    private Reader fileReader(@NotNull final Path filePath) throws IOException {
        //        Path path = Paths.get(
        //                ClassLoader.getSystemResource("csv/twoColumn.csv").toURI())
        //    );
        return Files.newBufferedReader(filePath);
    }
}
src/test/resources/application.yml
@@ -4,8 +4,8 @@
            platform: postgres
    datasource:
        url: jdbc:tc:postgresql:13.7-bullseye:///spring_boot_testcontainers
        url-local: jdbc:postgresql://localhost:5432/postgres
        url-tc: jdbc:tc:postgresql:13.7-bullseye:///spring_boot_testcontainers
        url: jdbc:postgresql://localhost:5432/postgres
        username: postgres
        password: password
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
src/test/resources/migration/contacts.csv
@@ -1,5 +1,5 @@
contact_id;    bp_id;    salut;    first_name;    last_name;    title;    firma;    co;    street;            zipcode;city;    country;    phone_private;        phone_office;        phone_mobile;    fax;        email
71;        7;    Herr;    Michael;    Mellies;        ;    ;    ;    Kleine Freiheit 50;    26524;    Hage;    DE;        ;            +49 4931 123456;    +49 1522 123456;;        mih@example.org
101;        10;    Frau;    Jenny;        Meyer;        Dr.;    JM e.K.;;    Waldweg 5;        11001;    Berlin; DE;        +49 30 7777777;        +49 30 8888888;        ;        +49 30 9999999; jm@example.org
102;        10;    Herr;    Andrew;        Meyer;        ;    JM e.K.;;    Waldweg 5;        11001;    Berlin; DE;        +49 30 6666666;        +49 30 5555555;        ;        +49 30 9999999; am@example.org
121;        12;    ;    Paule;        Schmidt;    ;    Test PS;;    ;            ;    ;    ;        ;            ;            ;        ;        ps@example.com
contact_id;    bp_id;    salut;    first_name;    last_name;    title;    firma;    co;    street;            zipcode;city;    country;    phone_private;        phone_office;        phone_mobile;    fax;        email; roles
71;        7;    Herr;    Michael;    Mellies;        ;    ;    ;    Kleine Freiheit 50;    26524;    Hage;    DE;        ;            +49 4931 123456;    +49 1522 123456;;        mih@example.org; billing,operation
101;        10;    Frau;    Jenny;        Meyer;        Dr.;    JM e.K.;;    Waldweg 5;        11001;    Berlin; DE;        +49 30 7777777;        +49 30 8888888;        ;        +49 30 9999999; jm@example.org; billing
102;        10;    Herr;    Andrew;        Meyer;        ;    JM e.K.;;    Waldweg 5;        11001;    Berlin; DE;        +49 30 6666666;        +49 30 5555555;        ;        +49 30 9999999; am@example.org; operation
121;        12;    ;    Paule;        Schmidt;    ;    Test PS;;    ;            ;    ;    ;        ;            ;            ;        ;        ps@example.com; billing,operation
src/test/resources/migration/sepa-mandates.csv
@@ -1,3 +1,3 @@
sepa_mandat_id;    bp_id;    bank_customer;    bank_name;    bank_iban;        bank_bic;    mandat_ref;    mandat_signed;    mandat_since;    mandat_until;    mandat_used
234234;        7;    Michael Mellies;    ING Bank AG;    DE37500105177419788228;    INGDDEFFXXX;    MH12345;    2004-06-12;    2004-06-15;    ;        2022-10-20
235662;        10;    JM e.K.;    ING Bank AG;    DE49500105174516484892;    INGDDEFFXXX;    JM33344;    2005-06-282;    2005-07-01;    ;        2016-01-18
235662;        10;    JM e.K.;    ING Bank AG;    DE49500105174516484892;    INGDDEFFXXX;    JM33344;    2005-06-28;    2005-07-01;    ;        2016-01-18