490 lines
16 KiB
PHP
490 lines
16 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
require_once __DIR__ . '/bootstrap.php';
|
|
require_once __DIR__ . '/ledger.php';
|
|
|
|
/**
|
|
* Parses a decimal amount ("12,34" or "12.34") into cents. Returns null for
|
|
* anything that is not a plausible positive amount.
|
|
*/
|
|
function imports_normalize_amount(string $raw): ?int
|
|
{
|
|
$normalized = str_replace(',', '.', trim($raw));
|
|
if (!preg_match('/^-?[0-9]+(?:\.[0-9]{1,2})?$/', $normalized)) {
|
|
return null;
|
|
}
|
|
|
|
$cents = (int)round(((float)$normalized) * 100);
|
|
|
|
return $cents > 0 ? $cents : null;
|
|
}
|
|
|
|
/**
|
|
* Matches a CSV name against a participant's paypal_name or display_name
|
|
* (case-insensitive).
|
|
*
|
|
* @return array{participant_id: int, display_name: string}|null
|
|
*/
|
|
function imports_find_participant(PDO $pdo, int $tenantId, string $name): ?array
|
|
{
|
|
$name = trim($name);
|
|
if ($name === '') {
|
|
return null;
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
'SELECT id, display_name
|
|
FROM participants
|
|
WHERE tenant_id = ?
|
|
AND (LOWER(paypal_name) = LOWER(?) OR LOWER(display_name) = LOWER(?))
|
|
ORDER BY id
|
|
LIMIT 2'
|
|
);
|
|
$stmt->execute([$tenantId, $name, $name]);
|
|
$rows = $stmt->fetchAll();
|
|
|
|
if (count($rows) !== 1) {
|
|
return null;
|
|
}
|
|
$row = $rows[0];
|
|
|
|
return [
|
|
'participant_id' => (int)$row['id'],
|
|
'display_name' => (string)$row['display_name'],
|
|
];
|
|
}
|
|
|
|
function imports_normalize_match_text(string $value): string
|
|
{
|
|
$value = trim($value);
|
|
$value = preg_replace('/\s+/', ' ', $value) ?? $value;
|
|
|
|
return function_exists('mb_strtolower')
|
|
? mb_strtolower($value, 'UTF-8')
|
|
: strtolower($value);
|
|
}
|
|
|
|
/**
|
|
* Liefert wahrscheinliche Teilnehmer fuer eine manuelle Zuordnung. Die
|
|
* automatische Buchung bleibt strenger; diese Vorschlaege sind nur eine Hilfe
|
|
* fuer Admins/Kassenwarte in CSV- und PayPal-Warteschlangen.
|
|
*
|
|
* @return list<array{participant_id:int, display_name:string, email:?string, paypal_name:?string, score:int}>
|
|
*/
|
|
function imports_suggest_participants(PDO $pdo, int $tenantId, string $rawName, ?string $note = null, int $limit = 5): array
|
|
{
|
|
$haystack = imports_normalize_match_text(trim($rawName . ' ' . (string)$note));
|
|
if ($haystack === '') {
|
|
return [];
|
|
}
|
|
|
|
$tokens = array_values(array_unique(array_filter(
|
|
preg_split('/\s+/', $haystack) ?: [],
|
|
static fn(string $token): bool => strlen($token) >= 3
|
|
)));
|
|
|
|
$stmt = $pdo->prepare(
|
|
'SELECT id, display_name, email, paypal_name
|
|
FROM participants
|
|
WHERE tenant_id = ? AND active = 1
|
|
ORDER BY display_name'
|
|
);
|
|
$stmt->execute([$tenantId]);
|
|
|
|
$candidates = [];
|
|
foreach ($stmt->fetchAll() as $row) {
|
|
$displayName = (string)$row['display_name'];
|
|
$paypalName = (string)($row['paypal_name'] ?? '');
|
|
$email = (string)($row['email'] ?? '');
|
|
$displayNorm = imports_normalize_match_text($displayName);
|
|
$paypalNorm = imports_normalize_match_text($paypalName);
|
|
$emailNorm = imports_normalize_match_text($email);
|
|
$score = 0;
|
|
|
|
if ($displayNorm !== '' && $displayNorm === imports_normalize_match_text($rawName)) {
|
|
$score += 90;
|
|
}
|
|
if ($paypalNorm !== '' && $paypalNorm === imports_normalize_match_text($rawName)) {
|
|
$score += 100;
|
|
}
|
|
if ($displayNorm !== '' && str_contains($haystack, $displayNorm)) {
|
|
$score += 45;
|
|
}
|
|
if ($paypalNorm !== '' && str_contains($haystack, $paypalNorm)) {
|
|
$score += 55;
|
|
}
|
|
|
|
foreach ($tokens as $token) {
|
|
if ($displayNorm !== '' && str_contains($displayNorm, $token)) {
|
|
$score += 12;
|
|
}
|
|
if ($paypalNorm !== '' && str_contains($paypalNorm, $token)) {
|
|
$score += 16;
|
|
}
|
|
if ($emailNorm !== '' && str_contains($emailNorm, $token)) {
|
|
$score += 6;
|
|
}
|
|
}
|
|
|
|
if ($score <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$candidates[] = [
|
|
'participant_id' => (int)$row['id'],
|
|
'display_name' => $displayName,
|
|
'email' => $row['email'] !== null ? (string)$row['email'] : null,
|
|
'paypal_name' => $row['paypal_name'] !== null ? (string)$row['paypal_name'] : null,
|
|
'score' => $score,
|
|
];
|
|
}
|
|
|
|
usort($candidates, static function (array $a, array $b): int {
|
|
return $b['score'] <=> $a['score']
|
|
?: strcasecmp((string)$a['display_name'], (string)$b['display_name']);
|
|
});
|
|
|
|
return array_slice($candidates, 0, max(1, $limit));
|
|
}
|
|
|
|
/**
|
|
* A payment counts as a duplicate if the same participant already has a
|
|
* non-voided ledger payment of the same amount on the same day.
|
|
*/
|
|
function imports_is_duplicate(PDO $pdo, int $tenantId, int $participantId, int $amountCents, string $bookedAtDate): bool
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*)
|
|
FROM ledger_entries
|
|
WHERE tenant_id = ?
|
|
AND participant_id = ?
|
|
AND type = 'payment'
|
|
AND amount_cents = ?
|
|
AND DATE(booked_at) = ?
|
|
AND voided_at IS NULL"
|
|
);
|
|
$stmt->execute([$tenantId, $participantId, $amountCents, $bookedAtDate]);
|
|
|
|
return (int)$stmt->fetchColumn() > 0;
|
|
}
|
|
|
|
/**
|
|
* Parses a PayPal-style export CSV: column 0 is the booking date, column 3
|
|
* the payer name, column 7 the gross amount. The first line is a header and
|
|
* is skipped.
|
|
*
|
|
* @return list<array{row_number: int, raw_name: string, amount_raw: string, date_raw: string, raw: list<string>}>
|
|
*/
|
|
function imports_parse_csv(string $filePath): array
|
|
{
|
|
$handle = fopen($filePath, 'r');
|
|
if ($handle === false) {
|
|
throw new RuntimeException('Die CSV-Datei konnte nicht gelesen werden.');
|
|
}
|
|
|
|
$rows = [];
|
|
$rowNumber = 0;
|
|
fgetcsv($handle); // Header ueberspringen
|
|
|
|
while (($data = fgetcsv($handle, 1000, ',')) !== false) {
|
|
$rowNumber++;
|
|
$rows[] = [
|
|
'row_number' => $rowNumber,
|
|
'raw_name' => trim((string)($data[3] ?? '')),
|
|
'amount_raw' => trim((string)($data[7] ?? '')),
|
|
'date_raw' => trim((string)($data[0] ?? '')),
|
|
'raw' => $data,
|
|
];
|
|
}
|
|
|
|
fclose($handle);
|
|
|
|
return $rows;
|
|
}
|
|
|
|
function imports_create_batch(PDO $pdo, int $tenantId, string $originalFilename, string $checksum, ?int $createdByUserId): int
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
'INSERT INTO payment_import_batches (tenant_id, original_filename, checksum, status, created_by_user_id)
|
|
VALUES (?, ?, ?, ?, ?)'
|
|
);
|
|
$stmt->execute([$tenantId, $originalFilename, $checksum, 'previewed', $createdByUserId]);
|
|
|
|
return (int)$pdo->lastInsertId();
|
|
}
|
|
|
|
function imports_store_row(
|
|
PDO $pdo,
|
|
int $batchId,
|
|
int $rowNumber,
|
|
?int $participantId,
|
|
string $rawName,
|
|
int $amountCents,
|
|
string $bookedAt,
|
|
string $status,
|
|
array $rawRow
|
|
): int {
|
|
$stmt = $pdo->prepare(
|
|
'INSERT INTO payment_import_rows
|
|
(batch_id, row_num, participant_id, raw_name, amount_cents, booked_at, status, raw_json)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
|
|
);
|
|
$stmt->execute([
|
|
$batchId,
|
|
$rowNumber,
|
|
$participantId,
|
|
$rawName,
|
|
$amountCents,
|
|
$bookedAt,
|
|
$status,
|
|
json_encode($rawRow, JSON_UNESCAPED_UNICODE),
|
|
]);
|
|
|
|
return (int)$pdo->lastInsertId();
|
|
}
|
|
|
|
/**
|
|
* @return array{batch: array{id: int, original_filename: string, status: string, created_at: string}, rows: list<array>}|null
|
|
*/
|
|
function imports_fetch_batch(PDO $pdo, int $tenantId, int $batchId): ?array
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
'SELECT id, original_filename, status, created_at
|
|
FROM payment_import_batches
|
|
WHERE id = ? AND tenant_id = ?'
|
|
);
|
|
$stmt->execute([$batchId, $tenantId]);
|
|
$batch = $stmt->fetch();
|
|
if ($batch === false) {
|
|
return null;
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
'SELECT r.id, r.row_num, r.participant_id, r.raw_name, r.amount_cents, r.booked_at, r.status, p.display_name
|
|
FROM payment_import_rows r
|
|
LEFT JOIN participants p ON p.id = r.participant_id
|
|
WHERE r.batch_id = ?
|
|
ORDER BY r.row_num'
|
|
);
|
|
$stmt->execute([$batchId]);
|
|
|
|
return [
|
|
'batch' => [
|
|
'id' => (int)$batch['id'],
|
|
'original_filename' => (string)$batch['original_filename'],
|
|
'status' => (string)$batch['status'],
|
|
'created_at' => (string)$batch['created_at'],
|
|
],
|
|
'rows' => $stmt->fetchAll(),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return list<array{id:int, original_filename:string, status:string, created_at:string, committed_at:?string, total_rows:int, matched_rows:int, unresolved_rows:int, imported_rows:int, ignored_rows:int}>
|
|
*/
|
|
function imports_fetch_recent_batches(PDO $pdo, int $tenantId, int $limit = 10): array
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"SELECT b.id, b.original_filename, b.status, b.created_at, b.committed_at,
|
|
COUNT(r.id) AS total_rows,
|
|
SUM(CASE WHEN r.status = 'matched' THEN 1 ELSE 0 END) AS matched_rows,
|
|
SUM(CASE WHEN r.status IN ('unmatched', 'duplicate', 'invalid') THEN 1 ELSE 0 END) AS unresolved_rows,
|
|
SUM(CASE WHEN r.status = 'imported' THEN 1 ELSE 0 END) AS imported_rows,
|
|
SUM(CASE WHEN r.status = 'ignored' THEN 1 ELSE 0 END) AS ignored_rows
|
|
FROM payment_import_batches b
|
|
LEFT JOIN payment_import_rows r ON r.batch_id = b.id
|
|
WHERE b.tenant_id = ?
|
|
GROUP BY b.id, b.original_filename, b.status, b.created_at, b.committed_at
|
|
ORDER BY b.created_at DESC, b.id DESC
|
|
LIMIT " . max(1, min(50, $limit))
|
|
);
|
|
$stmt->execute([$tenantId]);
|
|
|
|
return array_map(static function (array $row): array {
|
|
return [
|
|
'id' => (int)$row['id'],
|
|
'original_filename' => (string)$row['original_filename'],
|
|
'status' => (string)$row['status'],
|
|
'created_at' => (string)$row['created_at'],
|
|
'committed_at' => $row['committed_at'] !== null ? (string)$row['committed_at'] : null,
|
|
'total_rows' => (int)$row['total_rows'],
|
|
'matched_rows' => (int)$row['matched_rows'],
|
|
'unresolved_rows' => (int)$row['unresolved_rows'],
|
|
'imported_rows' => (int)$row['imported_rows'],
|
|
'ignored_rows' => (int)$row['ignored_rows'],
|
|
];
|
|
}, $stmt->fetchAll());
|
|
}
|
|
|
|
/**
|
|
* @return array{ok:bool, error?:string}
|
|
*/
|
|
function imports_assign_row(PDO $pdo, int $tenantId, int $batchId, int $rowId, int $participantId): array
|
|
{
|
|
$batch = imports_fetch_batch($pdo, $tenantId, $batchId);
|
|
if ($batch === null || $batch['batch']['status'] !== 'previewed') {
|
|
return ['ok' => false, 'error' => 'Dieser Import kann nicht mehr bearbeitet werden.'];
|
|
}
|
|
|
|
$summaries = ledger_fetch_participant_summaries($pdo, $tenantId, ['participant_ids' => [$participantId]]);
|
|
$participant = $summaries[0] ?? null;
|
|
if ($participant === null || empty($participant['active'])) {
|
|
return ['ok' => false, 'error' => 'Das gewählte Mitglied wurde nicht gefunden.'];
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
"UPDATE payment_import_rows r
|
|
JOIN payment_import_batches b ON b.id = r.batch_id
|
|
SET r.participant_id = ?, r.status = 'matched'
|
|
WHERE r.id = ?
|
|
AND r.batch_id = ?
|
|
AND b.tenant_id = ?
|
|
AND b.status = 'previewed'
|
|
AND r.status IN ('unmatched', 'duplicate')"
|
|
);
|
|
$stmt->execute([$participantId, $rowId, $batchId, $tenantId]);
|
|
|
|
if ($stmt->rowCount() !== 1) {
|
|
return ['ok' => false, 'error' => 'Diese Zeile kann nicht zugeordnet werden.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* @return array{ok:bool, error?:string}
|
|
*/
|
|
function imports_ignore_row(PDO $pdo, int $tenantId, int $batchId, int $rowId): array
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"UPDATE payment_import_rows r
|
|
JOIN payment_import_batches b ON b.id = r.batch_id
|
|
SET r.participant_id = NULL, r.status = 'ignored'
|
|
WHERE r.id = ?
|
|
AND r.batch_id = ?
|
|
AND b.tenant_id = ?
|
|
AND b.status = 'previewed'
|
|
AND r.status IN ('unmatched', 'duplicate', 'invalid')"
|
|
);
|
|
$stmt->execute([$rowId, $batchId, $tenantId]);
|
|
|
|
if ($stmt->rowCount() !== 1) {
|
|
return ['ok' => false, 'error' => 'Diese Zeile kann nicht ignoriert werden.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* Commits every 'matched' row of a previewed batch straight into the ledger.
|
|
* Already-committed batches are rejected.
|
|
*
|
|
* @return array{imported: int}
|
|
*/
|
|
function imports_commit_batch(PDO $pdo, int $tenantId, int $batchId): array
|
|
{
|
|
$stmt = $pdo->prepare('SELECT status FROM payment_import_batches WHERE id = ? AND tenant_id = ?');
|
|
$stmt->execute([$batchId, $tenantId]);
|
|
$status = $stmt->fetchColumn();
|
|
if ($status === false) {
|
|
throw new RuntimeException('Der Import wurde nicht gefunden.');
|
|
}
|
|
if ($status !== 'previewed') {
|
|
throw new RuntimeException('Dieser Import wurde bereits verarbeitet.');
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
"SELECT r.id, r.participant_id, r.amount_cents, r.booked_at
|
|
FROM payment_import_rows r
|
|
JOIN participants p ON p.id = r.participant_id
|
|
WHERE r.batch_id = ? AND r.status = 'matched'"
|
|
);
|
|
$stmt->execute([$batchId]);
|
|
$rows = $stmt->fetchAll();
|
|
|
|
$imported = 0;
|
|
|
|
$pdo->beginTransaction();
|
|
try {
|
|
$updateRow = $pdo->prepare(
|
|
"UPDATE payment_import_rows SET status = 'imported', ledger_entry_id = ? WHERE id = ?"
|
|
);
|
|
|
|
foreach ($rows as $row) {
|
|
$ledgerEntryId = ledger_record_payment(
|
|
$pdo,
|
|
$tenantId,
|
|
(int)$row['participant_id'],
|
|
(int)$row['amount_cents'],
|
|
'csv_import'
|
|
);
|
|
|
|
$updateRow->execute([$ledgerEntryId, $row['id']]);
|
|
$imported++;
|
|
}
|
|
|
|
$pdo->prepare("UPDATE payment_import_batches SET status = 'committed', committed_at = NOW() WHERE id = ?")
|
|
->execute([$batchId]);
|
|
|
|
$pdo->commit();
|
|
} catch (Throwable $e) {
|
|
if ($pdo->inTransaction()) {
|
|
$pdo->rollBack();
|
|
}
|
|
|
|
throw $e;
|
|
}
|
|
|
|
return ['imported' => $imported];
|
|
}
|
|
|
|
/**
|
|
* Parst eine Mitglieder-CSV (Vorlage: Name, E-Mail, PayPal-Name, Aktiv). Erste
|
|
* Zeile ist die Kopfzeile und wird uebersprungen. Trennzeichen wird anhand der
|
|
* Kopfzeile automatisch erkannt (Komma oder Semikolon - Excel im deutschen
|
|
* Gebietsschema exportiert Semikolon).
|
|
*
|
|
* @return list<array{row_number:int, name:string, email:string, paypal_name:string, active:bool}>
|
|
*/
|
|
function imports_parse_member_csv(string $filePath): array
|
|
{
|
|
$handle = fopen($filePath, 'r');
|
|
if ($handle === false) {
|
|
throw new RuntimeException('Die CSV-Datei konnte nicht gelesen werden.');
|
|
}
|
|
|
|
$firstLine = fgets($handle);
|
|
if ($firstLine === false) {
|
|
fclose($handle);
|
|
return [];
|
|
}
|
|
// BOM entfernen, Trennzeichen erkennen.
|
|
$firstLine = preg_replace('/^\xEF\xBB\xBF/', '', $firstLine);
|
|
$delimiter = (substr_count($firstLine, ';') > substr_count($firstLine, ',')) ? ';' : ',';
|
|
|
|
$rows = [];
|
|
$rowNumber = 0;
|
|
while (($data = fgetcsv($handle, 2000, $delimiter)) !== false) {
|
|
// Komplett leere Zeilen ueberspringen.
|
|
if ($data === [null] || (count($data) === 1 && trim((string)$data[0]) === '')) {
|
|
continue;
|
|
}
|
|
$rowNumber++;
|
|
$aktivRaw = strtolower(trim((string)($data[3] ?? 'ja')));
|
|
$active = !in_array($aktivRaw, ['nein', 'no', '0', 'false', 'inaktiv'], true);
|
|
$rows[] = [
|
|
'row_number' => $rowNumber,
|
|
'name' => trim((string)($data[0] ?? '')),
|
|
'email' => trim((string)($data[1] ?? '')),
|
|
'paypal_name' => trim((string)($data[2] ?? '')),
|
|
'active' => $active,
|
|
];
|
|
}
|
|
|
|
fclose($handle);
|
|
|
|
return $rows;
|
|
}
|