rktbds/upload.php

<?php
$dbPath = __DIR__ . '/_private/pl.db';
$message = '';
$messageType = '';

function parseMonthLabel(string $label): ?array {
    $label = trim($label);
    $months = [
        'Jan'=>1,'Feb'=>2,'Mar'=>3,'Apr'=>4,'May'=>5,'Jun'=>6,
        'Jul'=>7,'Aug'=>8,'Sep'=>9,'Oct'=>10,'Nov'=>11,'Dec'=>12
    ];
    if (strtoupper($label) === 'TOTAL') {
        return ['year'=>null,'month_num'=>null,'is_total'=>1,'is_partial'=>0];
    }
    // Partial month: "May 1 - 11, 26"
    if (preg_match('/^(\w{3})\s+\d+\s*-\s*\d+,\s*(\d{2})$/', $label, $m)) {
        $mn = $months[$m[1]] ?? null;
        if (!$mn) return null;
        $yy = (int)$m[2];
        return ['year'=>$yy>=90?1900+$yy:2000+$yy,'month_num'=>$mn,'is_total'=>0,'is_partial'=>1];
    }
    // Standard: "May 90"
    if (preg_match('/^(\w{3})\s+(\d{2})$/', $label, $m)) {
        $mn = $months[$m[1]] ?? null;
        if (!$mn) return null;
        $yy = (int)$m[2];
        return ['year'=>$yy>=90?1900+$yy:2000+$yy,'month_num'=>$mn,'is_total'=>0,'is_partial'=>0];
    }
    return null;
}

function importCSV(string $filepath, string $dbPath): array {
    if (!file_exists($filepath) || !is_readable($filepath)) {
        return ['ok'=>false,'msg'=>'File not found or not readable: '.$filepath];
    }

    $dir = dirname($dbPath);
    if (!is_dir($dir) && !mkdir($dir, 0775, true)) {
        return ['ok'=>false,'msg'=>'Cannot create database directory.'];
    }

    try {
        $db = new PDO('sqlite:'.$dbPath);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (Exception $e) {
        return ['ok'=>false,'msg'=>'Cannot open database: '.$e->getMessage()];
    }

    $db->exec('PRAGMA journal_mode=WAL');
    $db->exec('DROP TABLE IF EXISTS pl_values');
    $db->exec('DROP TABLE IF EXISTS accounts');
    $db->exec('DROP TABLE IF EXISTS months');

    $db->exec('CREATE TABLE months (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        label TEXT NOT NULL,
        year INTEGER,
        month_num INTEGER,
        sort_order INTEGER NOT NULL,
        is_total INTEGER NOT NULL DEFAULT 0,
        is_partial INTEGER NOT NULL DEFAULT 0
    )');
    $db->exec('CREATE TABLE accounts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        row_type TEXT NOT NULL,
        indent_level INTEGER NOT NULL DEFAULT 0,
        sort_order INTEGER NOT NULL
    )');
    $db->exec('CREATE TABLE pl_values (
        account_id INTEGER NOT NULL,
        month_id INTEGER NOT NULL,
        value REAL NOT NULL DEFAULT 0,
        PRIMARY KEY (account_id, month_id)
    )');

    $handle = fopen($filepath, 'r');
    if (!$handle) return ['ok'=>false,'msg'=>'Cannot open file.'];

    // Strip UTF-8 BOM if present
    $bom = fread($handle, 3);
    if ($bom !== "\xEF\xBB\xBF") rewind($handle);

    $header = fgetcsv($handle);
    if (!$header) { fclose($handle); return ['ok'=>false,'msg'=>'Empty or invalid CSV.']; }

    $db->beginTransaction();

    // Insert months from header (col 0 is the row-label column, skip it)
    $monthStmt = $db->prepare(
        'INSERT INTO months (label, year, month_num, sort_order, is_total, is_partial) VALUES (?,?,?,?,?,?)'
    );
    $monthIds = []; // col_index => month.id

    for ($i = 1; $i < count($header); $i++) {
        $label = trim($header[$i]);
        if ($label === '') continue;
        $parsed = parseMonthLabel($label);
        if (!$parsed || $parsed['is_partial']) continue;
        if ($parsed['year'] !== null && $parsed['year'] < 1996) continue;
        $monthStmt->execute([
            $label, $parsed['year'], $parsed['month_num'],
            $i, $parsed['is_total'], $parsed['is_partial']
        ]);
        $monthIds[$i] = (int)$db->lastInsertId();
    }

    // Known row classifications
    $level0Headers  = ['Ordinary Income/Expense','Other Income/Expense'];
    $level1Headers  = ['Income','Cost of Goods Sold','Expense','Other Income','Other Expense'];
    $level0Subtotals = ['Gross Profit','Net Ordinary Income','Net Other Income'];
    $level1Subtotals = ['Total Income','Total COGS','Total Expense','Total Other Income','Total Other Expense'];
    $totals          = ['Net Income'];

    $accountStmt = $db->prepare(
        'INSERT INTO accounts (name, row_type, indent_level, sort_order) VALUES (?,?,?,?)'
    );
    $valueStmt = $db->prepare(
        'INSERT OR REPLACE INTO pl_values (account_id, month_id, value) VALUES (?,?,?)'
    );

    $sortOrder    = 0;
    $accountCount = 0;
    $valueCount   = 0;

    while (($row = fgetcsv($handle)) !== false) {
        if (empty($row)) continue;
        $name = trim($row[0] ?? '');
        if ($name === '') continue;

        // Detect whether any value columns are non-empty
        $hasValues = false;
        for ($i = 1; $i < count($row); $i++) {
            if (isset($row[$i]) && trim($row[$i]) !== '') { $hasValues = true; break; }
        }

        if (!$hasValues) {
            $rowType = 'header';
            $indent  = in_array($name, $level0Headers) ? 0 : 1;
        } elseif (in_array($name, $totals)) {
            $rowType = 'total';
            $indent  = 0;
        } elseif (in_array($name, $level0Subtotals)) {
            $rowType = 'subtotal';
            $indent  = 0;
        } elseif (in_array($name, $level1Subtotals)) {
            $rowType = 'subtotal';
            $indent  = 1;
        } else {
            $rowType = 'item';
            $indent  = 2;
        }

        $accountStmt->execute([$name, $rowType, $indent, $sortOrder++]);
        $accountId = (int)$db->lastInsertId();
        $accountCount++;

        if ($hasValues) {
            for ($i = 1; $i < count($row); $i++) {
                if (!isset($monthIds[$i])) continue;
                $raw = trim($row[$i] ?? '');
                if ($raw === '') continue;
                $val = (float)str_replace([',', ' '], '', $raw);
                $valueStmt->execute([$accountId, $monthIds[$i], $val]);
                $valueCount++;
            }
        }
    }

    $db->commit();
    fclose($handle);

    $numMonths = count($monthIds);
    return [
        'ok'  => true,
        'msg' => "Successfully imported $accountCount accounts across $numMonths months ($valueCount data points)."
    ];
}

// Handle POST
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['import_existing'])) {
        $csvPath = __DIR__ . '/May2026PL.csv';
        $result  = importCSV($csvPath, $dbPath);
    } elseif (isset($_FILES['csvfile']) && $_FILES['csvfile']['error'] === UPLOAD_ERR_OK) {
        $result = importCSV($_FILES['csvfile']['tmp_name'], $dbPath);
    } else {
        $uploadErr = $_FILES['csvfile']['error'] ?? -1;
        $result    = ['ok'=>false,'msg'=>'Upload error (code '.$uploadErr.'). Check php.ini upload_max_filesize.'];
    }
    $message     = $result['msg'];
    $messageType = $result['ok'] ? 'success' : 'error';
}

$hasExistingCsv = file_exists(__DIR__ . '/May2026PL.csv');
$hasDb          = file_exists($dbPath) && filesize($dbPath) > 0;
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Upload P&amp;L Data — ICG Magnetics</title>
<style>
*{box-sizing:border-box;margin:0;padding:0}
body{font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;background:#f1f5f9;color:#1e293b;min-height:100vh;display:flex;align-items:center;justify-content:center}
.card{background:white;border-radius:12px;box-shadow:0 4px 24px rgba(0,0,0,.08);width:100%;max-width:520px;padding:36px 40px}
.card h1{font-size:22px;font-weight:700;color:#0f172a;margin-bottom:4px}
.card .sub{font-size:13px;color:#64748b;margin-bottom:28px}
.section{margin-bottom:24px;padding:20px;border:1px solid #e2e8f0;border-radius:8px}
.section h2{font-size:13px;font-weight:600;color:#475569;text-transform:uppercase;letter-spacing:.5px;margin-bottom:14px}
.btn{display:inline-block;padding:10px 20px;border-radius:6px;font-size:14px;font-weight:600;cursor:pointer;border:none;transition:.15s}
.btn-primary{background:#3b82f6;color:white}
.btn-primary:hover{background:#2563eb}
.btn-secondary{background:#e2e8f0;color:#475569}
.btn-secondary:hover{background:#cbd5e1}
.btn-view{background:#10b981;color:white;text-decoration:none;display:inline-block;padding:10px 20px;border-radius:6px;font-size:14px;font-weight:600}
.btn-view:hover{background:#059669}
input[type=file]{display:block;width:100%;padding:10px;border:2px dashed #cbd5e1;border-radius:6px;margin-bottom:12px;font-size:13px;color:#475569;cursor:pointer;background:#f8fafc}
input[type=file]:hover{border-color:#94a3b8}
.alert{padding:14px 16px;border-radius:6px;font-size:13px;margin-bottom:20px;line-height:1.5}
.alert.success{background:#d1fae5;color:#065f46;border:1px solid #6ee7b7}
.alert.error{background:#fee2e2;color:#991b1b;border:1px solid #fca5a5}
.back{display:inline-block;margin-top:20px;color:#3b82f6;text-decoration:none;font-size:13px}
.back:hover{text-decoration:underline}
.note{font-size:11px;color:#94a3b8;margin-top:8px;line-height:1.5}
.existing-badge{display:inline-block;background:#d1fae5;color:#065f46;font-size:11px;padding:2px 7px;border-radius:10px;margin-left:6px;font-weight:500}
.overwrite-warning{font-size:12px;color:#b45309;margin-top:10px;padding:8px 12px;background:#fef3c7;border-radius:5px}
</style>
</head>
<body>
<div class="card">
  <h1>Upload P&amp;L Data</h1>
  <div class="sub">ICG Magnetics · QuickBooks CSV Import</div>

  <?php if ($message): ?>
  <div class="alert <?= htmlspecialchars($messageType) ?>">
    <?= htmlspecialchars($message) ?>
    <?php if ($messageType === 'success'): ?>
    <div style="margin-top:12px">
      <a href="index.php" class="btn-view">View P&amp;L →</a>
    </div>
    <?php endif; ?>
  </div>
  <?php endif; ?>

  <?php if ($hasExistingCsv): ?>
  <div class="section">
    <h2>Quick Import <span class="existing-badge">File Found</span></h2>
    <p style="font-size:13px;color:#475569;margin-bottom:12px">
      Found <strong>May2026PL.csv</strong> on this server. Click to import it directly.
    </p>
    <?php if ($hasDb): ?>
    <div class="overwrite-warning">⚠ This will replace all existing P&amp;L data.</div>
    <?php endif; ?>
    <form method="post" style="margin-top:12px">
      <button type="submit" name="import_existing" class="btn btn-primary">Import May2026PL.csv</button>
    </form>
  </div>
  <?php endif; ?>

  <div class="section">
    <h2>Upload New CSV</h2>
    <p style="font-size:13px;color:#475569;margin-bottom:12px">
      Upload a QuickBooks P&amp;L export (by month). All existing data will be replaced.
    </p>
    <?php if ($hasDb): ?>
    <div class="overwrite-warning">⚠ This will replace all existing P&amp;L data.</div>
    <?php endif; ?>
    <form method="post" enctype="multipart/form-data" style="margin-top:12px">
      <input type="file" name="csvfile" accept=".csv,text/csv">
      <div class="note">Export from QuickBooks: Reports → Profit &amp; Loss → Columns: Month → Export to CSV</div>
      <button type="submit" class="btn btn-primary" style="margin-top:12px">Upload &amp; Import</button>
    </form>
  </div>

  <?php if ($hasDb && !$message): ?>
  <a href="index.php" class="back">← Back to P&amp;L Viewer</a>
  <?php endif; ?>
</div>
</body>
</html>