prepare("SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'accounts' AND COLUMN_NAME = 'routing_number'"); $q->execute(); $row = $q->fetch(); if ($row && (int)$row['cnt'] === 0) { $pdo->exec("ALTER TABLE accounts ADD COLUMN routing_number VARCHAR(20) NULL AFTER account_number"); } } catch (PDOException $e) { error_log("ensureRoutingColumn error: " . $e->getMessage()); } } // 💡 NEW HELPER: Ensure a routing number column on the users table function ensureUserRoutingColumn(PDO $pdo) { try { $q = $pdo->prepare("SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'bank_routing_number'"); $q->execute(); $row = $q->fetch(); if ($row && (int)$row['cnt'] === 0) { // Note: I'm adding a unique index for routing number to ensure uniqueness. $pdo->exec("ALTER TABLE users ADD COLUMN bank_routing_number VARCHAR(20) NULL UNIQUE AFTER email"); } } catch (PDOException $e) { error_log("ensureUserRoutingColumn error: " . $e->getMessage()); } } function e($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); } // ================================================================= // CORE LOGIC (Account fetching/creation) // ================================================================= ensureUserRoutingColumn($pdo); // Check/create the new user column first // Fetch user basic info, currency settings, AND PERMANENT ROUTING NUMBER try { $stmt = $pdo->prepare("SELECT user_id, first_name, last_name, email, currency_symbol, currency_country, bank_routing_number FROM users WHERE user_id = :uid LIMIT 1"); $stmt->execute(['uid' => $user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); if (!$user) { header('Location: login.php'); exit(); } // Set currency variables from the fetched user data, with fallbacks $currency_symbol = $user['currency_symbol'] ?? '$'; $country_name = $user['currency_country'] ?? 'United States'; $user_routing_number = $user['bank_routing_number']; // Get the user's permanent routing number // 💡 NEW LOGIC: Generate and set the permanent routing number if null if (empty($user_routing_number)) { $attempts = 0; $rt = null; while ($attempts++ < 50) { $candidate = generateRouting9(); // Check uniqueness across the USERS table now $q = $pdo->prepare("SELECT COUNT(*) AS cnt FROM users WHERE bank_routing_number = :rt"); $q->execute(['rt' => $candidate]); if ($q->fetchColumn() == 0) { $rt = $candidate; break; } } if ($rt === null) $rt = generateRouting9(); // Fallback if 50 attempts fail (unlikely) $user_routing_number = $rt; // Update the user's record with the new permanent routing number $upd = $pdo->prepare("UPDATE users SET bank_routing_number = :rt WHERE user_id = :uid"); $upd->execute(['rt' => $user_routing_number, 'uid' => $user_id]); } } catch (PDOException $e) { error_log("DB error fetching user and currency/routing: " . $e->getMessage()); // Use hardcoded fallback if DB query fails $currency_symbol = '$'; $country_name = 'United States (Error Fallback)'; $user_routing_number = '000000000'; } // ⚠️ Note: The original ensureRoutingColumn for the 'accounts' table is now effectively deprecated // in favor of the new 'bank_routing_number' on the 'users' table, but kept for strict code evolution. // ensureRoutingColumn($pdo); // Fetch accounts for user try { $stmt = $pdo->prepare("SELECT * FROM accounts WHERE user_id = :uid"); $stmt->execute(['uid' => $user_id]); $accounts = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { error_log("DB error fetching accounts: " . $e->getMessage()); $accounts = []; } // Auto-create initial Checking account if none exists if (empty($accounts)) { // Logic for generating unique account number (only account number is needed now) $attempts = 0; $acct = null; while ($attempts++ < 50) { $candidate = generateAccountNumber10(); $q = $pdo->prepare("SELECT COUNT(*) AS cnt FROM accounts WHERE account_number = :acct"); $q->execute(['acct' => $candidate]); if ($q->fetchColumn() == 0) { $acct = $candidate; break; } } if ($acct === null) $acct = generateAccountNumber10(); try { // The accounts table may still have a routing_number column from the previous version, // so we'll use the user's new permanent routing number for consistency. $ins = $pdo->prepare("INSERT INTO accounts (user_id, account_type, account_number, routing_number, balance, created_at, updated_at) VALUES (:uid, :atype, :acnum, :rtnum, :bal, NOW(), NOW())"); $ins->execute([ 'uid' => $user_id, 'atype' => 'Checking', 'acnum' => $acct, // Use the permanent user routing number here for consistency 'rtnum' => $user_routing_number, 'bal' => 0.00 ]); // re-fetch accounts $stmt = $pdo->prepare("SELECT * FROM accounts WHERE user_id = :uid"); $stmt->execute(['uid' => $user_id]); $accounts = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { error_log("DB error creating account: " . $e->getMessage()); } } // pick primary account (first) $primaryAccount = $accounts[0] ?? null; $accountNumber = $primaryAccount['account_number'] ?? '—'; // 💡 Use the permanent user routing number instead of the (possibly missing/deprecated) account routing number $routingNumber = $user_routing_number ?? '—'; $balanceRaw = (float)($primaryAccount['balance'] ?? 0.00); $balance = $currency_symbol . number_format($balanceRaw, 2); $accountType = $primaryAccount['account_type'] ?? 'Checking'; $account_id = $primaryAccount['account_id'] ?? null; // Fetch recent transactions $transactions = []; if ($account_id) { try { $tx = $pdo->prepare(" SELECT t.*, a.account_number FROM transactions t JOIN accounts a ON t.account_id = a.account_id WHERE t.account_id = :aid ORDER BY t.created_at DESC LIMIT 10 "); $tx->execute(['aid' => $account_id]); $transactions = $tx->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { error_log("DB error fetching transactions: " . $e->getMessage()); } } ?> ダッシュボード — アメリカン銀行