thetool/scripts/constructionconsent/wundschuh_import/import.php

<?php
require_once(__DIR__ . "/../../../config/config.php");
define('FRONKDB_SQLDEBUG', false);
error_reporting(E_ALL & ~(E_NOTICE | E_STRICT | E_DEPRECATED));

$dryRun = in_array('--dry-run', $argv);
$kgMap = ['Kasten' => '63241', 'Wundschuh' => '63293'];

$pdo = new PDO(
    "mysql:host=" . FRONKDB_DBHOST . ";dbname=" . FRONKDB_DBNAME . ";charset=utf8mb4",
    FRONKDB_DBUSER, FRONKDB_DBPASS,
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);

$csvFile = __DIR__ . '/wundschuh_consent_import.csv';
$handle = fopen($csvFile, 'r') or die("CSV not found\n");
$cols = array_flip(fgetcsv($handle));

$missingLog = fopen(__DIR__ . '/missing_consents.csv', 'w');
fputcsv($missingLog, ['Row', 'Name', 'Address', 'GST_Numbers']);

$stmt = $pdo->prepare("SELECT id, gst, kg FROM ConstructionConsent WHERE constructionconsentproject_id = 11 AND gst IS NOT NULL AND gst != ''");
$stmt->execute();
$consentIndex = [];
foreach ($stmt->fetchAll() as $c) {
    $consentIndex[$c['kg'] . '_' . $c['gst']][] = $c['id'];
}

echo "=== Wundschuh Import ===" . ($dryRun ? " [DRY RUN]" : "") . "\n";
echo count($consentIndex) . " consent GST entries loaded\n\n";

$stats = ['processed' => 0, 'created' => 0, 'skipped' => 0, 'missing' => 0];
$rowNum = 1;

while (($row = fgetcsv($handle)) !== false) {
    $rowNum++;
    $stats['processed']++;

    $get = fn($k) => trim($row[$cols[$k]] ?? '');
    $lastname = $get('Person_Name1');
    $firstname = $get('Person_Name2');
    $anrede = $get('Person_Anrede');
    $fullName = "$firstname $lastname";

    $gstStr = $get('Person_Grundstuecksnummern');
    $gstStr = preg_replace('/^Gst\.Nr\.\s*/i', '', $gstStr);
    preg_match_all('/([^()]+)\s*\(KG\s+([^)]+)\)/i', $gstStr, $m, PREG_SET_ORDER);

    $gstList = [];
    foreach ($m as $match) {
        $kg = $kgMap[trim($match[2])] ?? null;
        if (!$kg) continue;
        foreach (explode(',', $match[1]) as $g) {
            if ($g = trim($g)) $gstList[] = ['gst' => $g, 'kg' => $kg];
        }
    }

    $consentIds = [];
    foreach ($gstList as $g) {
        foreach ($consentIndex[$g['kg'] . '_' . $g['gst']] ?? [] as $id) {
            $consentIds[$id] = true;
        }
    }

    if (empty($consentIds)) {
        $addr = $get('Person_Anschrift_Straßenbezeichnung') . ' ' . $get('Person_Anschrift_Hausnummer_Von');
        fputcsv($missingLog, [$rowNum, $fullName, trim($addr), $get('Person_Grundstuecksnummern')]);
        $stats['missing']++;
        echo "[$rowNum] $fullName - NO CONSENT FOUND\n";
        continue;
    }

    $isCompany = in_array($anrede, ['Firma', 'Herrn/Frau/Firma']);
    $street = $get('Person_Anschrift_Straßenbezeichnung');
    $hn = $get('Person_Anschrift_Hausnummer_Von');
    $fl = $get('Person_Anschrift_Hausnummer_Stock');
    $dr = $get('Person_Anschrift_Hausnummer_Tuer');
    $fullStreet = $street . ($hn ? " $hn" : '') . ($fl ? "/$fl" : '') . ($dr ? "/$dr" : '');

    $bd = explode('/', $get('Person_Gebdat'));
    $birthdate = count($bd) === 3 ? sprintf('%04d-%02d-%02d', $bd[2], $bd[0], $bd[1]) : null;

    $title = $get('Person_Titelakad') ?: $get('Person_Titelakadn') ?: null;
    $country = ['A' => 'Österreich', 'I' => 'Italien'][$get('Person_Anschrift_Staat')] ?? $get('Person_Anschrift_Staat');

    $ownerData = [
        $title,
        $isCompany ? "$firstname $lastname" : null,
        $isCompany ? null : $firstname,
        $isCompany ? null : $lastname,
        $fullStreet,
        $get('Person_Anschrift_PLZ'),
        $get('Person_Anschrift_Ort'),
        $country,
        $birthdate
    ];

    echo "[$rowNum] $fullName - " . count($consentIds) . " consent(s)";

    foreach (array_keys($consentIds) as $cid) {
        $chk = $pdo->prepare("SELECT 1 FROM ConstructionConsentOwner WHERE constructionconsent_id = ? AND ((firstname = ? AND lastname = ?) OR (company = ? AND company IS NOT NULL AND company != ''))");
        $chk->execute([$cid, $ownerData[2], $ownerData[3], $ownerData[1]]);

        if ($chk->fetch()) {
            $stats['skipped']++;
            continue;
        }

        if (!$dryRun) {
            $ins = $pdo->prepare("INSERT INTO ConstructionConsentOwner (constructionconsent_id, title, company, firstname, lastname, street, zip, city, country, birthdate, status, result, create_by, edit_by, `create`, `edit`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'new', 'open', 118, 118, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())");
            $ins->execute([$cid, ...$ownerData]);
        }
        $stats['created']++;
    }
    echo "\n";
}

fclose($handle);
fclose($missingLog);

echo "\n=== Summary ===\n";
echo "Processed: {$stats['processed']} | Created: {$stats['created']} | Skipped: {$stats['skipped']} | Missing: {$stats['missing']}\n";
echo "Missing consents logged to: missing_consents.csv\n";
if ($dryRun) echo "[DRY RUN - no changes made]\n";