inwo_vgl/diwo_code_snippets/model_abfrage_db/VergleichsansichtHhmEinkommenToJsonDbFunctions.java

package com.tsystems.diwo.business.queries;

import com.fasterxml.jackson.core.JsonProcessingException;

import com.tsystems.common.InwoLogger;
import com.tsystems.common.model.db.interfaces.IInwoDBCall;

import java.math.BigDecimal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class VergleichsansichtHhmEinkommenToJsonDbFunctions extends VergleichsansichtBaseDbFunctions {

    private static final InwoLogger LOG =
        InwoLogger.createInwoLogger( VergleichsansichtHhmEinkommenToJsonDbFunctions.class );

    /* ============================================================
       ÖFFENTLICHE EINSTIEGE (Root: "stammakte" -> vorgaenge[])
       ============================================================ */

    public static String buildJsonAktuellerVorgang ( IInwoDBCall dbCall, Integer sakId, Integer lfdnr,
                                                     Integer konnr ) throws SQLException, JsonProcessingException {
        Map<String, Object> stammakte = fetchStammakte( dbCall, sakId );

        Map<String, Object> stammvorgang = fetchStammvorgang( dbCall, sakId, lfdnr );

        // Hole bez und wognr aus t_akten
        Map<String, Integer> aktenData = fetchBezWognrFromAkten( dbCall, sakId );

        // Hole Eingangsdatum für zeitabhängige Lookups
        Date eingangsdatum = fetchEingangsdatum( dbCall, sakId, lfdnr );

        List<Map<String, Object>> zeitraeume =
            fetchZeitraeumeUndDetails( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), lfdnr, eingangsdatum,
                                       /*onlyKonnr*/konnr );

        Map<String, Object> vorgangObj = new LinkedHashMap<>();
        vorgangObj.put( "stammvorgang", stammvorgang );
        vorgangObj.put( "zeitraeume", zeitraeume );

        List<Map<String, Object>> vorgaenge = new ArrayList<>();
        vorgaenge.add( vorgangObj );
        stammakte.put( "vorgaenge", vorgaenge );

        String result = om.writeValueAsString( Collections.singletonMap( "stammakte", stammakte ) );

        LOG.finest( "Resulting JSON: " + result );

        // Close database call at the very end after all queries are completed
        dbCall.closeQuery();

        return result;
    }

    /** Alle anderen Vorgänge (≠ lfdnr) + alle deren Zeiträume -> {"stammakte":{..., "vorgaenge":[{...}, ...]}} */
    public static String buildJsonAndereVorgaenge ( IInwoDBCall dbCall, Integer sakId,
                                                    Integer lfdnr ) throws SQLException, JsonProcessingException {
        Map<String, Object> stammakte = fetchStammakte( dbCall, sakId );

        List<Integer> andere = fetchAndereLfdnr( dbCall, sakId, lfdnr );
        List<Map<String, Object>> vorgaenge = new ArrayList<>();

        // Hole bez und wognr aus t_akten
        Map<String, Integer> aktenData = fetchBezWognrFromAkten( dbCall, sakId );

        for ( Integer otherLfdnr : andere ) {
            Map<String, Object> stammvorgang = fetchStammvorgang( dbCall, sakId, otherLfdnr );

            // Hole Eingangsdatum für zeitabhängige Lookups
            Date eingangsdatum = fetchEingangsdatum( dbCall, sakId, otherLfdnr );

            List<Map<String, Object>> zeitraeume =
                fetchZeitraeumeUndDetails( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), otherLfdnr,
                                           eingangsdatum, /*onlyKonnr*/null );

            Map<String, Object> vorgangObj = new LinkedHashMap<>();
            vorgangObj.put( "stammvorgang", stammvorgang );
            vorgangObj.put( "zeitraeume", zeitraeume );
            vorgaenge.add( vorgangObj );
        }

        stammakte.put( "vorgaenge", vorgaenge );

        String result = om.writeValueAsString( Collections.singletonMap( "stammakte", stammakte ) );

        LOG.finest( "Resulting JSON: " + result );

        // Close database call at the very end after all queries are completed
        dbCall.closeQuery();

        return result;
    }

    /* ===========================
       Eingangsdatum
       =========================== */

    private static Date fetchEingangsdatum ( IInwoDBCall dbCall, Integer sakId, Integer lfdnr ) throws SQLException {
        String q = "SELECT EING_DAT FROM T_STAMMVORGAENGE WHERE SAK_ID = :sak_id AND LFDNR = :lfdnr";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() )
            return rs.getDate( "EING_DAT" );

        return null;
    }

    /* ===========================
       Zeiträume + Details
       =========================== */

    /**
     * Lädt Zeiträume für einen Vorgang.
     * - onlyKonnr != null  -> nur dieser KONNR (aktueller Vorgang)
     * - onlyKonnr == null  -> alle Zeiträume (andere Vorgänge)
     */
    private static List<Map<String, Object>> fetchZeitraeumeUndDetails ( IInwoDBCall dbCall, Integer bez, Integer wognr,
                                                                         Integer lfdnr, Date eingangsdatum,
                                                                         Integer onlyKonnr ) throws SQLException {
        String base =
            "SELECT TO_CHAR(k.KONNR) AS zeitraum_id, " + "       TO_CHAR(k.BEGINN_DAT, 'YYYY-MM-DD') AS beginn, " +
            "       TO_CHAR(k.ENDE_DAT,   'YYYY-MM-DD') AS ende " + "FROM T_KONSTELLATIONEN k " +
            "WHERE k.AN_WOGNR = :wognr AND k.AN_BEZ = :bez AND k.AN_LFDNR = :lfdnr ";
        String filter = ( onlyKonnr != null ) ? "AND k.KONNR = :konnr " : "";
        String order = "ORDER BY k.BEGINN_DAT NULLS FIRST, k.KONNR";
        String q = base + filter + order;

        dbCall.query( q );
        dbCall.in( "wognr", wognr, Types.INTEGER );
        dbCall.in( "bez", bez, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );
        if ( onlyKonnr != null )
            dbCall.in( "konnr", onlyKonnr, Types.INTEGER );

        List<Map<String, Object>> out = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            String zeitraumId = rs.getString( "zeitraum_id" );
            String beginn = rs.getString( "beginn" );
            String ende = rs.getString( "ende" );

            Map<String, Object> zr = new LinkedHashMap<>();
            zr.put( "id", zeitraumId );
            if ( beginn != null )
                zr.put( "beginn", beginn );
            if ( ende != null )
                zr.put( "ende", ende );

            // Personen für diesen Zeitraum laden
            List<Map<String, Object>> personen =
                fetchPersonenImZeitraum( dbCall, bez, wognr, lfdnr, Integer.parseInt( zeitraumId ), eingangsdatum );
            zr.put( "personen", personen );

            out.add( zr );
        }

        return out;
    }

    /* ===========================
       Personen im Zeitraum
       =========================== */

    private static List<Map<String, Object>> fetchPersonenImZeitraum ( IInwoDBCall dbCall, Integer bez, Integer wognr,
                                                                       Integer lfdnr, Integer konnr,
                                                                       Date eingangsdatum ) throws SQLException {
        // Hole alle BIK_IDs für diesen Zeitraum
        String q =
            "SELECT kb.BIK_ID " + "FROM T_KON_BIK kb " + "WHERE kb.KON_AN_WOGNR = :wognr AND kb.KON_AN_BEZ = :bez " +
            "AND kb.KON_AN_LFDNR = :lfdnr AND kb.KON_KONNR = :konnr";

        dbCall.query( q );
        dbCall.in( "wognr", wognr, Types.INTEGER );
        dbCall.in( "bez", bez, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );
        dbCall.in( "konnr", konnr, Types.INTEGER );

        List<Integer> bikIds = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            bikIds.add( ( ( BigDecimal ) rs.getObject( "BIK_ID" ) ).intValue() );
        }

        List<Map<String, Object>> personen = new ArrayList<>();
        for ( Integer bikId : bikIds ) {
            Map<String, Object> personImZeitraum = fetchPersonImZeitraum( dbCall, bikId, eingangsdatum );
            if ( personImZeitraum != null ) {
                personen.add( personImZeitraum );
            }
        }

        return personen;
    }

    private static Map<String, Object> fetchPersonImZeitraum ( IInwoDBCall dbCall, Integer bikId,
                                                               Date eingangsdatum ) throws SQLException {
        // Hole Bewohner-ID aus T_BEWOHNER_IN_KONST
        Integer bewId = fetchBewohnerIdFromBik( dbCall, bikId );
        if ( bewId == null )
            return null;

        Map<String, Object> person = fetchPersonBasis( dbCall, bewId, bikId, eingangsdatum );
        if ( person == null )
            return null;

        Map<String, Object> personImZeitraum = new LinkedHashMap<>();
        personImZeitraum.put( "person", person );

        // Einkommen laden
        List<Map<String, Object>> einkommen = fetchEinkommen( dbCall, bikId );
        if ( !einkommen.isEmpty() ) {
            personImZeitraum.put( "einkommen", einkommen );
        }

        // Freibeträge laden
        List<Map<String, Object>> freibetraege = fetchFreibetraege( dbCall, bikId );
        if ( !freibetraege.isEmpty() ) {
            personImZeitraum.put( "freibetraege", freibetraege );
        }

        // Kinderbetreuung laden
        List<Map<String, Object>> kinderbetreuung = fetchKinderbetreuung( dbCall, bikId );
        if ( !kinderbetreuung.isEmpty() ) {
            personImZeitraum.put( "kinderbetreuung", kinderbetreuung );
        }

        return personImZeitraum;
    }

    private static Integer fetchBewohnerIdFromBik ( IInwoDBCall dbCall, Integer bikId ) throws SQLException {
        String q = "SELECT BEW_ID FROM T_BEWOHNER_IN_KONST WHERE ID = :bik_id";
        dbCall.query( q );
        dbCall.in( "bik_id", bikId, Types.INTEGER );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            return ( ( BigDecimal ) rs.getObject( "BEW_ID" ) ).intValue();
        }

        return null;
    }

    /* ===========================
       PersonBasis
       =========================== */

    private static Map<String, Object> fetchPersonBasis ( IInwoDBCall dbCall, Integer bewId, Integer bikId,
                                                          Date eingangsdatum ) throws SQLException {
        java.sql.Date eingangDat =
            ( eingangsdatum != null ) ? new java.sql.Date( eingangsdatum.getTime() ) :
            java.sql
                                                                                                                  .Date
                                                                                                                  .valueOf( "2099-12-31" );

        String q =
            "SELECT " + "    TO_CHAR(b.ID) AS id, " + "    b.NAME AS nachname, " + "    b.VORNAME AS vorname, " +
            "    b.GEBURTSNAME AS geburtsname, " + "    b.NAME_ALT AS alterName, " + "    b.TITEL AS titel, " +
            "    b.ANREDE AS anrede_raw, " + "    COALESCE(anr.BESCHREIBUNG, b.ANREDE) AS anrede, " +
            "    TO_CHAR(b.GEB_DAT, 'YYYY-MM-DD') AS geburtsdatum, " +
            "    TO_CHAR(b.STERBE_DAT, 'YYYY-MM-DD') AS sterbedatum, " + "    b.GEBURTSORT AS geburtsort, " +
            "    b.GESCHLECHT AS geschlecht_raw, " + "    COALESCE(gsch.RV_MEANING, b.GESCHLECHT) AS geschlecht, " +
            "    b.TELEFON AS telefon, " + "    b.EMAIL AS email, " + "    b.BLOCKNAME_JN AS blockname_raw, " +
            "    b.VSNR AS postabrutionsnummer " + "FROM T_BEWOHNER b " + "LEFT JOIN T_SANREDEN anr " +
            "       ON anr.WERT = b.ANREDE " + "LEFT JOIN DIWO_REF_CODES gsch " +
            "       ON gsch.RV_LOW_VALUE = b.GESCHLECHT " + "      AND gsch.RV_DOMAIN = 'DV_GESCHLECHT' " +
            "WHERE b.ID = :bew_id";

        dbCall.query( q );
        dbCall.in( "bew_id", bewId, Types.INTEGER );

        Map<String, Object> person = new LinkedHashMap<>();
        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            person.put( "id", rs.getString( "id" ) );
            person.put( "nachname", rs.getString( "nachname" ) );
            person.put( "vorname", rs.getString( "vorname" ) );
            putStringIfNotNull( person, "geburtsname", rs.getString( "geburtsname" ) );
            putStringIfNotNull( person, "alterName", rs.getString( "alterName" ) );
            putStringIfNotNull( person, "titel", rs.getString( "titel" ) );
            putStringIfNotNull( person, "anrede", rs.getString( "anrede" ) );
            putStringIfNotNull( person, "geburtsdatum", rs.getString( "geburtsdatum" ) );
            putStringIfNotNull( person, "sterbedatum", rs.getString( "sterbedatum" ) );
            putStringIfNotNull( person, "geburtsort", rs.getString( "geburtsort" ) );
            putStringIfNotNull( person, "geschlecht", rs.getString( "geschlecht" ) );
            putStringIfNotNull( person, "telefon", rs.getString( "telefon" ) );
            putStringIfNotNull( person, "email", rs.getString( "email" ) );
            putStringIfNotNull( person, "postabrutionsnummer", rs.getString( "postabrutionsnummer" ) );
            person.put( "blockname", "J".equalsIgnoreCase( rs.getString( "blockname_raw" ) ) );
        }

        // Zusätzliche Daten aus T_BEWOHNER_IN_KONST
        fetchPersonBasisZusatz( dbCall, person, bikId, eingangDat );

        return person.isEmpty() ? null : person;
    }

    private static void fetchPersonBasisZusatz ( IInwoDBCall dbCall, Map<String, Object> person, Integer bikId,
                                                 java.sql.Date eingangDat ) throws SQLException {
        String q =
            "SELECT " + "    bik.AZ_SOZ AS aktenzeichenSozial, " + "    bik.VERWANDT AS verwandt_raw, " +
            "    COALESCE(verw.BESCHREIBUNG, bik.VERWANDT) AS verwandtschaftsverhaeltnis, " +
            "    bik.SOZ_STELLUNG AS sozstellung_raw, " +
            "    COALESCE(sozst.BESCHREIBUNG, bik.SOZ_STELLUNG) AS sozialstellung, " +
            "    bik.FAMILIENSTAND AS familienstand_raw, " +
            "    COALESCE(fam.BESCHREIBUNG, bik.FAMILIENSTAND) AS familienstand, " +
            "    TO_CHAR(bik.ENDE_AUFENTHALTSTITEL, 'YYYY-MM-DD') AS aufenthaltstitelEnde " +
            "FROM T_BEWOHNER_IN_KONST bik " + "LEFT JOIN T_SVERWANDTSCHAFT verw " +
            "       ON verw.WERT = bik.VERWANDT " + "      AND :eingang_dat >= verw.AB_DAT " +
            "      AND :eingang_dat <= NVL(verw.BIS_DAT, DATE '9999-12-31') " + "LEFT JOIN T_SSOZIALSTELLUNGEN sozst " +
            "       ON sozst.WERT = bik.SOZ_STELLUNG " + "      AND :eingang_dat >= sozst.AB_DAT " +
            "      AND :eingang_dat <= NVL(sozst.BIS_DAT, DATE '9999-12-31') " + "LEFT JOIN T_SFAMILIENSTAND fam " +
            "       ON fam.WERT = bik.FAMILIENSTAND " + "      AND :eingang_dat >= fam.AB_DAT " +
            "      AND :eingang_dat <= NVL(fam.BIS_DAT, DATE '9999-12-31') " + "WHERE bik.ID = :bik_id";

        dbCall.query( q );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "eingang_dat", eingangDat, Types.DATE );
        dbCall.in( "bik_id", bikId, Types.INTEGER );


        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            putStringIfNotNull( person, "aktenzeichenSozial", rs.getString( "aktenzeichenSozial" ) );
            putStringIfNotNull( person, "verwandtschaftsverhaeltnis", rs.getString( "verwandtschaftsverhaeltnis" ) );
            putStringIfNotNull( person, "sozialstellung", rs.getString( "sozialstellung" ) );
            putStringIfNotNull( person, "familienstand", rs.getString( "familienstand" ) );
            putStringIfNotNull( person, "aufenthaltstitelEnde", rs.getString( "aufenthaltstitelEnde" ) );
        }
    }

    /* ===========================
       Einkommen
       =========================== */

    private static List<Map<String, Object>> fetchEinkommen ( IInwoDBCall dbCall, Integer bikId ) throws SQLException {
        String q =
            "SELECT " + "    TO_CHAR(e.LFDNR) AS id, " + "    e.ART AS art, " + "    e.ART_ZUSATZ AS artZusatz, " +
            "    e.FREQUENZ AS faktor, " + "    e.BETRAG AS betrag, " + "    e.WERB_KOST AS werbungskosten, " +
            "    e.ZUSCHLAG AS zuschlag, " + "    e.ABSCHLAG AS abschlag " + "FROM T_EINKOMMEN e " +
            "WHERE e.BIK_ID = :bik_id " + "ORDER BY e.LFDNR";

        dbCall.query( q );
        dbCall.in( "bik_id", bikId, Types.INTEGER );

        List<Map<String, Object>> einkommen = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            Map<String, Object> e = new LinkedHashMap<>();
            e.put( "id", rs.getString( "id" ) );

            String art = rs.getString( "art" );
            String artBeschreibung = lookupEinkommensartBeschreibung( dbCall, art );
            e.put( "art", artBeschreibung );

            putStringIfNotNull( e, "artZusatz", rs.getString( "artZusatz" ) );
            putNumIfNotNull( e, "faktor", rs, "faktor" );
            putNumIfNotNull( e, "betrag", rs, "betrag" );
            putNumIfNotNull( e, "werbungskosten", rs, "werbungskosten" );
            putNumIfNotNull( e, "zuschlag", rs, "zuschlag" );
            putNumIfNotNull( e, "abschlag", rs, "abschlag" );
            einkommen.add( e );
        }

        return einkommen;
    }

    private static String lookupEinkommensartBeschreibung ( IInwoDBCall dbCall, String art ) throws SQLException {
        String q = "SELECT BESCHREIBUNG FROM T_SEINKOMMENSARTEN WHERE WERT = :art";
        dbCall.query( q );
        dbCall.in( "art", art, Types.VARCHAR );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            return rs.getString( "BESCHREIBUNG" );
        }

        return art; // Fallback auf ursprünglichen Wert
    }

    /* ===========================
       Freibeträge
       =========================== */

    private static List<Map<String, Object>> fetchFreibetraege ( IInwoDBCall dbCall,
                                                                 Integer bikId ) throws SQLException {
        String q =
            "SELECT " + "    TO_CHAR(f.LFDNR) AS id, " + "    f.ART AS art, " + "    f.FREQUENZ AS faktor, " +
            "    f.BETRAG AS betrag, " + "    TO_CHAR(f.FRIST_DAT, 'YYYY-MM-DD') AS fristDatum " +
            "FROM T_FREIBETRAEGE f " + "WHERE f.BIK_ID = :bik_id " + "ORDER BY f.LFDNR";

        dbCall.query( q );
        dbCall.in( "bik_id", bikId, Types.INTEGER );

        List<Map<String, Object>> freibetraege = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            Map<String, Object> f = new LinkedHashMap<>();
            f.put( "id", rs.getString( "id" ) );
            String art = rs.getString( "art" );
            f.put( "art", lookupFreibetragBeschreibung( dbCall, art ) );

            // Typ aus T_SFREIB_DETAILS ermitteln
            String typ = lookupFreibetragTyp( dbCall, art );
            if ( typ != null ) {
                f.put( "typ", typ );
            }

            putNumIfNotNull( f, "faktor", rs, "faktor" );
            putNumIfNotNull( f, "betrag", rs, "betrag" );
            putStringIfNotNull( f, "fristDatum", rs.getString( "fristDatum" ) );
            freibetraege.add( f );
        }

        return freibetraege;
    }

    private static String lookupFreibetragBeschreibung ( IInwoDBCall dbCall, String art ) throws SQLException {
        String q = "SELECT BESCHREIBUNG FROM T_SFREIBETRAGSARTEN WHERE WERT = :art";
        dbCall.query( q );
        dbCall.in( "art", art, Types.VARCHAR );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            return rs.getString( "BESCHREIBUNG" );
        }

        return art; // Fallback auf ursprünglichen Wert
    }

    private static String lookupFreibetragTyp ( IInwoDBCall dbCall, String art ) throws SQLException {
        String q = "SELECT KENNUNG FROM T_SFREIB_DETAILS WHERE FREIB_ART = :art";
        dbCall.query( q );
        dbCall.in( "art", art, Types.VARCHAR );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            String kennung = rs.getString( "KENNUNG" );
            // Mapping: S = F, A = P
            if ( "S".equals( kennung ) ) {
                return "F";
            } else if ( "A".equals( kennung ) ) {
                return "P";
            }
            return kennung;
        }

        return null;
    }

    /* ===========================
       Kinderbetreuung
       =========================== */

    private static List<Map<String, Object>> fetchKinderbetreuung ( IInwoDBCall dbCall,
                                                                    Integer bikId ) throws SQLException {
        String q =
            "SELECT " + "    TO_CHAR(ROWNUM) AS id, " + "    kb.BIK_ID_KIND AS kindBikId, " +
            "    kb.BETRAG AS betrag " + "FROM T_KINDERBETREUUNG kb " + "WHERE kb.BIK_ID = :bik_id " +
            "ORDER BY kb.BIK_ID_KIND";

        dbCall.query( q );
        dbCall.in( "bik_id", bikId, Types.INTEGER );

        List<Map<String, Object>> kinderbetreuung = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            Map<String, Object> kb = new LinkedHashMap<>();
            kb.put( "id", rs.getString( "id" ) );

            // Get the original Bewohner ID from the KIND_BEW_ID (which is a BIK_ID)
            Integer kindBikId = ( ( BigDecimal ) rs.getObject( "kindBikId" ) ).intValue();
            Integer kindBewId = null;
            if ( kindBikId != null ) {
                kindBewId = fetchBewohnerIdFromBik( dbCall, kindBikId );
            }

            kb.put( "kindRef", kindBewId != null ? String.valueOf( kindBewId ) : null );
            putNumIfNotNull( kb, "betrag", rs, "betrag" );
            kinderbetreuung.add( kb );
        }

        return kinderbetreuung;
    }

}