import.php

<?php

function convif($do, $data)
{
    if (!$do) {
        return $data;
    }
    return iconv('ISO-8859-15', 'UTF-8', $data ?? '');
}
// Reports a line that cannot be imported; the import stops there.
// With $lineno the line is skipped (its number is part of the report).
function import_invalid_line($i, $rawcsvline, $csvline, $csvformat, $handkassacsvline, $lineno = null)
{
    echo "Ungültige Buchungszeile in Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . ")" . ($lineno === null ? '' : ", Zeile $lineno") . " <br />\n";
    echo "<pre>" . html_escape($rawcsvline) . "</pre><br />\n";
    echo "<pre>" . html_escape($csvline) . "</pre><br />\n";
    echo "Das erkannte CSV Format ist $csvformat.<br />\n";
    echo "Falls du eine Handkassaeingabe tätigen möchtest geht das mit folgendem Format:<br />\n";
    echo "<pre>" . $handkassacsvline . "</pre><br />\n";
}
// Whether format_date() produced a valid date (it passes unrecognized text
// through).
function import_date_valid($date)
{
    if (!is_string($date) || preg_match('/^(\d{4})-(\d{1,2})-(\d{1,2})$/D', $date, $parts) != 1) {
        return false;
    }
    return checkdate(intval($parts[2]), intval($parts[3]), intval($parts[1]));
}
function page_import($rights)
{
    global $dbconn;
    // The member list of this request (a temporary table, getusers()); a
    // failed refresh stops the page with its message.
    $members_error = getusers();
    if ($members_error !== null) {
        die($members_error);
    }
    block_start();
    echo "<table>\n";
    $csv_mimetypes = [
        'text/csv',
        'text/plain',
        'application/csv',
        'text/comma-separated-values',
        'application/excel',
        'application/vnd.ms-excel',
        'application/vnd.msexcel',
        'text/anytext',
        'application/octet-stream',
        'application/txt',
        'application/x-csv',
    ];

    for ($i = 0; isset($_FILES['uploadcsv']['name']) && $i < count($_FILES['uploadcsv']['name']); $i++) {
        if (strlen($_FILES['uploadcsv']['name'][$i]) <= 0) {
            continue;
        }
        if (strlen($_FILES['uploadcsv']['type'][$i]) <= 0 || (!in_array($_FILES['uploadcsv']['type'][$i], $csv_mimetypes))) {
            echo "Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . ") hat ein ungültiges Dateiformat (" . html_escape($_FILES['uploadcsv']['type'][$i]) . ") und konnte daher nicht importiert werden.<br />";
            continue;
        }
        if ($_FILES['uploadcsv']['error'][$i] != 0) {
            echo "Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . ") wurde nicht erfolgreich hochgeladen und konnte daher nicht importiert werden.<br />";
            continue;
        }
        if ($_FILES['uploadcsv']['size'][$i] < 10) {
            echo "Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . ") ist zu klein und konnte daher nicht importiert werden.<br />";
            continue;
        }
        $csvformat = 'BAWAG';
        if (($handle = fopen($_FILES['uploadcsv']['tmp_name'][$i], "r")) !== false) {
            echo "<h2>Importiere " . html_escape($_FILES['uploadcsv']['name'][$i]) . "</h2>\n";
            $voucher_number = 0;
            $lineno = 0;
            $skipped = [];
            while (($data = fgetcsv($handle, 0, ";", '"', "\\")) !== false) {
                $lineno++;
                $voucher2 = [];
                $dovoucher2 = false;
                $csvline = implode(";", $data);
                $convert = false;
                if (mb_detect_encoding($csvline, 'UTF-8', true) === false) {
                    $convert = true;
                }
                $rawcsvline = $csvline;
                $csvline = pg_escape_string($dbconn, str_replace(['|',' ',"\n","\t","\r"], ['','','','',''], convif($convert, $csvline)));
                $sparkassecsvline = 'Bezeichnung;Valutadatum;Betrag;Währung;Buchungsdatum;Umsatzzeile1;Zusatztext;Auftraggeber;Kundendaten/Zahlungsreferenz;Umsatzzeile2;Ersterfassungsreferenz;AuftraggeberKontonr./IBAN;AuftraggeberBLZ/BIC;Partnername;PartnerKontonr./IBAN;PartnerBLZ/BIC';
                if ($csvline == $sparkassecsvline) {
                    $csvformat = 'Sparkasse';
                }
                $paypalcsvline = 'Datum,Zeit,Zeitzone,Name,Art,Status,Währung,Brutto,Gebühr,Netto,VonE-Mail-Adresse,AnE-Mail-Adresse,Transaktionscode,StatusderGegenpartei,Adressstatus,Verwendungszweck,Artikelnummer,BetragfürVersandkosten,Versicherungsbetrag,Umsatzsteuer,Option1-Name,Option1-Wert,Option2-Name,Option2-Wert,Auktions-Site,Käufer-ID,Artikel-URL,Angebotsende,Vorgangs-Nr.,Rechnungs-Nr.,Txn-Referenzkennung,Rechnungsnummer,IndividuelleNummer,Bestätigungsnummer,Guthaben,Adresse,ZusätzlicheAngaben,Ort,Staat/Provinz/Region/Landkreis/Territorium/Präfektur/Republik,PLZ,Land,TelefonnummerderKontaktperson,';
                if ($csvline == $paypalcsvline) {
                    $csvformat = 'Paypal';
                }
                $handkassacsvline = 'Datum;Betrag;Konto;Text;Kommentar;Fremdkonto;Zweckwidmung;Kostenrückerstattung;Name;Mitgliedsnummer;Lo;Adresse;PLZ;Stadt';
                if ($csvline == $handkassacsvline) {
                    $csvformat = 'Handkassa';
                }
                // Header lines and blank lines (empty fields only) are not
                // imported (before, their amount counted as 0, and in BAWAG
                // files a blank line stopped the import as an invalid line).
                if (in_array($csvline, [$sparkassecsvline, $paypalcsvline, $handkassacsvline], true)) {
                    continue;
                }
                if (trim($csvline, ';,') === '') {
                    continue;
                }
                $query = "SELECT 1 FROM import WHERE line = '{$csvline}';";
                $result = db_query($dbconn, $query);
                if (pg_fetch_array($result, null, PGSQL_ASSOC)) {
                    continue;
                }
                pg_free_result($result);
                //echo str_replace("\n","<br />",print_r($data,1));
                if ($csvformat == 'BAWAG') {
                    $voucher['konto'] = $data[0];
                    switch ($voucher['konto']) {
                        case 'AT606000050110117300':
                            $voucher['lo'] = 2;
                            break;
                        case 'AT596000050110117318':
                            $voucher['lo'] = 3;
                            break;
                        case 'AT376000050110117326':
                            $voucher['lo'] = 4;
                            break;
                        case 'AT686000050110117350':
                            $voucher['lo'] = 5;
                            break;
                        case 'AT406000050110117369':
                        case 'AT716000020810070316':
                        case 'AT496000020810070324':
                            $voucher['lo'] = 6;
                            break;
                        case 'AT716000050110117393':
                            $voucher['lo'] = 8;
                            break;
                        case 'AT856000010110123642':
                            $voucher['lo'] = 9;
                            break;
                        case 'AT916000050110110437':
                            $voucher['lo'] = 10;
                            break;
                        default:
                            import_invalid_line($i, $rawcsvline, $csvline, $csvformat, $handkassacsvline);
                            return -1;
                    }
                    $voucher['comment'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[1])));
                    $voucher['date'] = format_date($data[2]);
                    $voucher['amount'] = amount_cents($data[4], ',', '.', true);
                    $voucher['gegenkonto'] = '';
                    $voucher['name'] = '';
                } elseif ($csvformat == 'Handkassa') {
                    // Whole euros, or the last "," or "." before one or two
                    // decimals, the other one grouping thousands ("7", "-3.5",
                    // "12,50", "1.234,56", "1,234.56"; not "1.234").
                    $amount = trim($data[1] ?? '', " \t\r\n");
                    if (preg_match('/^[+-]?\d+$/D', $amount) == 1) {
                        $amount .= ',00';
                    } elseif (preg_match('/[.,]\d$/D', $amount) == 1) {
                        $amount .= '0';
                    }
                    $voucher['amount'] = amount_cents($amount, ',.', ',.', true);
                    if ($voucher['amount'] === 0) {
                        continue;
                    }
                    $voucher['date'] = format_date($data[0]);
                    $voucher['konto'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[2])));
                    $voucher['comment'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[3])));
                    $voucher['commentgf'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[4])));
                    $voucher['gegenkonto'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[5])));
                    $voucher['committed'] = ($data[6] == 'Ja' || $data[6] == 'Wahr' || $data[6] == '1') ? 'true' : 'false';
                    $voucher['refund'] = ($data[7] == 'Ja' || $data[7] == 'Wahr' || $data[7] == '1') ? 'true' : 'false';
                    $voucher['name'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[8])));
                    $voucher['mitgliedsnummer'] = intval($data[9]);
                    if ($voucher['mitgliedsnummer'] != 0) {
                        $voucher['mitglied'] = 'true';
                        $voucher['person_type'] = 2;
                    }
                    $voucher['lo'] = intval($data[10]) == 0 ? 10 : intval($data[10]);
                    $voucher['street'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[11])));
                    $voucher['city'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[13])));
                    $voucher['plz'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[12])));
                } elseif ($csvformat == 'Sparkasse') {
                    $voucher['amount'] = amount_cents($data[2], ',.', ',.', true);
                    if ($voucher['amount'] === 0) {
                        continue;
                    }
                    $voucher['lo'] = 6;
                    $voucher['konto'] = '20815 6659403';
                    $voucher['comment'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[5] . "; " . $data[6] . "; " . $data[7])));
                    $voucher['date'] = format_date($data[4]);
                    $voucher['gegenkonto'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[14] . " " . $data[15])));
                    $voucher['name'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[13])));
                } elseif ($csvformat == 'Paypal') {
                    $data = str_getcsv($data[0], ",", '"', "\\");
                    $voucher['amount'] = amount_cents($data[7], ',.', ',.', true);
                    if ($voucher['amount'] === 0) {
                        continue;
                    }
                    $voucher['lo'] = 10;
                    $voucher['konto'] = 'spende@piratenpartei.at';
                    $cmt = "";
                    if ($data[15] != '') {
                        $cmt .= convif($convert, $data[15]) . "; ";
                    }
                    $cmt .= "Transaktion: " . convif($convert, $data[12]) . "; ";
                    $cmt .= "Brutto: " . convif($convert, $data[7]) . "€; ";
                    $cmt .= "Gebühr: " . convif($convert, $data[8]) . "€; ";
                    $cmt .= convif($convert, $data[3]);
                    $voucher['comment'] = pg_escape_string($dbconn, $cmt);
                    $voucher['date'] = format_date($data[0]);
                    $voucher['gegenkonto'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, ($data[10] == 'spende@piratenpartei.at') ? $data[11] : $data[10])));
                    $voucher['name'] = pg_escape_string($dbconn, str_replace("|", "\n", convif($convert, $data[3])));
                    $voucher2['lo'] = 10;
                    $voucher2['konto'] = 'spende@piratenpartei.at';
                    $voucher2['comment'] = pg_escape_string($dbconn, $cmt);
                    $voucher2['date'] = format_date($data[0]);
                    $voucher2['amount'] = amount_cents($data[8], ',.', ',.', true);
                    $voucher2['gegenkonto'] = "";
                    $voucher2['name'] = 'Paypal';
                    $voucher2['person_type'] = 1;
                    $voucher2['member'] = 'false';
                    $voucher2['mitgliedsnummer'] = 0;
                    $voucher2['type'] = 20;
                    $voucher2['street'] = '';
                    $voucher2['plz'] = '';
                    $voucher2['city'] = '';
                    $voucher2['purpose'] = 'false';
                    $voucher2['receipt'] = 'false';
                    $dovoucher2 = true;
                }
                // Only dates, organizations the user may use (organization_allowed())
                // and valid amounts: other lines are reported and skipped.
                if (!import_date_valid($voucher['date']) || !organization_allowed($voucher['lo'], $rights) || $voucher['amount'] === null || ($dovoucher2 && $voucher2['amount'] === null)) {
                    import_invalid_line($i, $rawcsvline, $csvline, $csvformat, $handkassacsvline, $lineno);
                    $skipped[] = $lineno;
                    continue;
                }
                $voucher_number = db_next_value($dbconn, 'voucher_number');
                $voucher['person_type'] = 1;
                $voucher['member'] = 'false';
                $voucher['mitgliedsnummer'] = 0;
                $voucher['street'] = '';
                $voucher['plz'] = '';
                $voucher['city'] = '';
                $voucher['purpose'] = 'false';
                $voucher['receipt'] = 'false';
                if ($voucher['gegenkonto'] == '' && $voucher['name'] == '') {
                    $c = explode("\n", $voucher['comment']);
                    if (count($c) >= 2 && preg_match('/^([A-Z0-9]+ [A-Z][A-Z][0-9]+) (.*[A-Z]+.*)$/i', $c[1], $matches) == 1) {
                        $voucher['gegenkonto'] = $matches[1];
                        $voucher['name'] = $matches[2];
                    } elseif (count($c) >= 2 && preg_match('/^(.+ .+) ([^\d+] [^\d]+)$/', $c[1], $matches) == 1) {
                        $voucher['gegenkonto'] = $matches[2];
                        $voucher['name'] = $matches[1];
                    } elseif (count($c) >= 2 && preg_match('/^(\d+ \d+) ([^\d]+ [^\d]+)$/', $c[1], $matches) == 1) {
                        $voucher['gegenkonto'] = $matches[1];
                        $voucher['name'] = $matches[2];
                    }
                }
                $voucher['type'] = 14;
                if (strlen($voucher['name']) == 0) {
                    // $csvline is escaped already. Member names are matched literally:
                    // their LIKE wildcards and the escape character are escaped (E''
                    // strings, independent of standard_conforming_strings), and an empty
                    // name matches nothing.
                    $query = "SELECT * FROM ppmembers WHERE name <> '' AND '$csvline' ILIKE '%' || replace(replace(replace(name, E'\\\\', E'\\\\\\\\'), '%', E'\\\\%'), '_', E'\\\\_') || '%' ESCAPE E'\\\\'";
                    if ($result = db_query($dbconn, $query)) {
                        if (pg_num_rows($result) == 1) {
                            $line = pg_fetch_array($result, null, PGSQL_ASSOC);
                            $voucher['member'] = 'true';
                            $voucher['mitgliedsnummer'] = intval($line['id']);
                            $voucher['person_type'] = 2;
                            $voucher['type'] = 1;
                            $voucher['name'] = pg_escape_string($dbconn, $line['name']);
                            pg_free_result($result);
                            $found = true;
                        }
                    }
                }
                if (str_contains($voucher['comment'], 'MB')
                    || str_contains($voucher['comment'], 'Mitgliedsbeitrag')) {
                    $voucher['type'] = 1;
                    $voucher['member'] = 'true';
                    $voucher['person_type'] = 2;
                }
                if ($voucher['mitgliedsnummer'] == 0 && strlen($voucher['name']) > 0) {
                    $n = $voucher['name'];
                    $n2 = trim(str_replace(['Mag.','DI (FH)','iur.','Dipl.-Ing.','Dr.','Ing.'], ['','','','','',''], $n));
                    $nt = explode(" ", $n, 2);
                    $nt2 = explode(" ", $n2, 2);
                    $queries = [];
                    $queries[] = "SELECT * FROM ppmembers WHERE lower(name) = lower('$n');";
                    if (count($nt) == 2) {
                        $queries[] = "SELECT * FROM ppmembers WHERE lower(name) = lower('{$nt[1]} {$nt[0]}');";
                    }
                    if ($n != $n2) {
                        $queries[] = "SELECT * FROM ppmembers WHERE lower(name) = lower('$n2');";
                        if (count($nt2) == 2) {
                            $queries[] = "SELECT * FROM ppmembers WHERE lower(name) = lower('{$nt2[1]} {$nt2[0]}');";
                        }
                    }
                    $found = false;
                    foreach ($queries as $query) {
                        if ($result = db_query($dbconn, $query)) {
                            if (pg_num_rows($result) == 1) {
                                $line = pg_fetch_array($result, null, PGSQL_ASSOC);
                                $voucher['member'] = 'true';
                                $voucher['mitgliedsnummer'] = intval($line['id']);
                                $voucher['person_type'] = 2;
                                $voucher['type'] = 1;
                                pg_free_result($result);
                                $found = true;
                                break;
                            }
                            pg_free_result($result);
                        }
                    }
                    if (!$found) {
                        // no member!
                        $voucher['type'] = 8;
                    }
                }
                if ($voucher['amount'] < 0) {
                    $voucher['type'] = 28;
                }

                echo "Buchung $voucher_number erstellt!<br />\n";
                //print_r($voucher);

                $query = "INSERT INTO vouchers (voucher_id, date, type, orga, member, member_id, contra_account, name, street, plz, city, amount, account, comment, committed, receipt_received) VALUES ($voucher_number, '{$voucher['date']}', {$voucher['type']},{$voucher['lo']},{$voucher['member']},{$voucher['mitgliedsnummer']},'{$voucher['gegenkonto']}','{$voucher['name']}','{$voucher['street']}','{$voucher['plz']}','{$voucher['city']}',{$voucher['amount']},'{$voucher['konto']}','{$voucher['comment']}',{$voucher['purpose']},{$voucher['receipt']})";
                db_exec($dbconn, $query, show_sql: true);

                if ($dovoucher2) {
                    $query = "INSERT INTO vouchers (voucher_id, date, type, orga, member, member_id, contra_account, name, street, plz, city, amount, account, comment, committed, receipt_received) VALUES ($voucher_number, '{$voucher2['date']}', {$voucher2['type']},{$voucher2['lo']},{$voucher2['member']},{$voucher2['mitgliedsnummer']},'{$voucher2['gegenkonto']}','{$voucher2['name']}','{$voucher2['street']}','{$voucher2['plz']}','{$voucher2['city']}',{$voucher2['amount']},'{$voucher2['konto']}','{$voucher2['comment']}',{$voucher2['purpose']},{$voucher2['receipt']})";
                    db_exec($dbconn, $query, show_sql: true);
                }

                $query = "INSERT INTO import (line) VALUES ('{$csvline}');";
                $result = pg_query($dbconn, $query);
                pg_free_result($result);
            }
            fclose($handle);
            if (count($skipped) > 0) {
                echo "Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . "): " . count($skipped) . " Buchungszeilen übersprungen (Zeilen " . implode(", ", $skipped) . ")<br />\n";
            }
            echo "Datei " . ($i + 1) . " (" . html_escape($_FILES['uploadcsv']['name'][$i]) . ") wurde erfolgreich importiert<br />\n";
        }
    }
    echo "</table>\n";
    echo 'Bitte wähle die CSV Dateien für den Import:
<form enctype="multipart/form-data" action="index.php?action=import" method="POST">
' . csrf_field() . '
<input type="hidden" name="MAX_FILE_SIZE" value="1000000" />
<input name="uploadcsv[]" type="file" multiple="multiple" /><br />
<br />';
    echo '
<input type="submit" value="Importieren" />
</form>';
    block_end();
}