Vim Patior

View file

File name : _helpers.php
Content :
<?php
// Shared helper functions and data loading for page templates

function get_db_connection() {
    require 'db_config.php';
    try {
        $conn = new mysqli($servername, $username, $password, $dbname);
        if ($conn->connect_error) {
            throw new Exception("Connection failed: " . $conn->connect_error);
        }
        return $conn;
    } catch (Exception $e) {
        die("Database Connection Error: " . $e->getMessage());
    }
}

function get_initials($name) {
    if (empty($name)) return 'A';
    $words = explode(' ', $name);
    if (count($words) >= 2) {
        return strtoupper(substr($words[0], 0, 1) . substr(end($words), 0, 1));
    }
    return strtoupper(substr($name, 0, 2));
}

function format_currency($amount) {
    return 'BZ$' . number_format((float)$amount, 2);
}

function format_relative_date($date_string) {
    if (empty($date_string)) return 'N/A';
    try {
        $date = new DateTime($date_string);
        $now = new DateTime();
        $interval = $now->diff($date);

        if ($interval->invert === 0) { // Future date
            if ($interval->y > 0) return 'in ' . $interval->y . ' year' . ($interval->y > 1 ? 's' : '');
            if ($interval->m > 0) return 'in ' . $interval->m . ' month' . ($interval->m > 1 ? 's' : '');
            if ($interval->d >= 0) {
                if ($interval->d === 0) return 'today';
                if ($interval->d === 1) return 'tomorrow';
                return 'in ' . $interval->d . ' days';
            }
        } else { // Past date
            if ($interval->y > 0) return $interval->y . ' year' . ($interval->y > 1 ? 's' : '') . ' ago';
            if ($interval->m > 0) return $interval->m . ' month' . ($interval->m > 1 ? 's' : '') . ' ago';
            if ($interval->d > 0) return $interval->d . ' day' . ($interval->d > 1 ? 's' : '') . ' ago';
            return 'today';
        }
    } catch (Exception $e) {
        return 'Invalid date';
    }
}

/**
 * A compatible version of fetch_all_assoc that does not require the mysqlnd driver.
 * It uses bind_result and fetch to manually construct the associative array.
 */
function fetch_all_assoc($stmt) {
    $result = [];
    $stmt->store_result();
    if ($stmt->num_rows === 0) {
        $stmt->free_result();
        return [];
    }
    $meta = $stmt->result_metadata();
    $params = [];
    $row = [];
    while ($field = $meta->fetch_field()) {
        $params[] = &$row[$field->name];
    }
    call_user_func_array([$stmt, 'bind_result'], $params);
    while ($stmt->fetch()) {
        $c = [];
        foreach ($row as $key => $val) {
            $c[$key] = $val;
        }
        $result[] = $c;
    }
    $stmt->free_result();
    return $result;
}

function get_invoice_details($conn, $invoice_id) {
    $invoice_data = [];

    $stmt = $conn->prepare("SELECT i.*, c.name as customer_name, c.email as customer_email, c.address as customer_address, c.phone as customer_phone FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE i.id = ?");
    if (!$stmt) return null;
    
    $stmt->bind_param("s", $invoice_id);
    $stmt->execute();
    $invoices = fetch_all_assoc($stmt);
    $stmt->close();

    if (empty($invoices)) {
        return null;
    }
    $invoice_data['invoice'] = $invoices[0];
    
    $stmt = $conn->prepare("SELECT * FROM invoice_items WHERE invoice_id = ?");
    $stmt->bind_param("s", $invoice_id);
    $stmt->execute();
    $invoice_data['items'] = fetch_all_assoc($stmt);
    $stmt->close();
    
    return $invoice_data;
}

function get_customer_details($conn, $customer_id) {
    $data = ['customer' => null, 'invoices' => [], 'stats' => ['total_unpaid' => 0, 'paid_last_12_months' => 0]];

    // Get Customer
    $stmt = $conn->prepare("SELECT * FROM customers WHERE id = ?");
    $stmt->bind_param("s", $customer_id);
    $stmt->execute();
    $result = fetch_all_assoc($stmt);
    $stmt->close();
    if (empty($result)) return null;
    $data['customer'] = $result[0];

    // Get Invoices
    $stmt = $conn->prepare("SELECT * FROM invoices WHERE customer_id = ? ORDER BY invoice_date DESC");
    $stmt->bind_param("s", $customer_id);
    $stmt->execute();
    $data['invoices'] = fetch_all_assoc($stmt);
    $stmt->close();

    // Calculate Stats
    $one_year_ago = date('Y-m-d H:i:s', strtotime('-1 year'));
    $stmt_stats = $conn->prepare("
        SELECT 
            SUM(CASE WHEN status IN ('Due', 'Overdue') THEN total ELSE 0 END) as total_unpaid,
            SUM(CASE WHEN status = 'Paid' AND paid_at >= ? THEN total ELSE 0 END) as paid_last_12_months
        FROM invoices WHERE customer_id = ?
    ");
    $stmt_stats->bind_param("ss", $one_year_ago, $customer_id);
    $stmt_stats->execute();
    $stats_result = fetch_all_assoc($stmt_stats);
    $stmt_stats->close();
    if(!empty($stats_result)) {
        $data['stats']['total_unpaid'] = (float)$stats_result[0]['total_unpaid'];
        $data['stats']['paid_last_12_months'] = (float)$stats_result[0]['paid_last_12_months'];
    }

    return $data;
}
?>