Working with Invoices

Working with Invoices

Create Invoice

$db = Database::getInstance();

// Create invoice
$stmt = $db->prepare("
    INSERT INTO invoices (
        client_id, invoice_number, status, total, currency,
        due_date, created_at
    ) VALUES (?, ?, 'pending', ?, ?, DATE_ADD(NOW(), INTERVAL 14 DAY), NOW())
");
$invoiceNumber = 'INV-' . date('Y') . '-' . str_pad($nextNumber, 6, '0', STR_PAD_LEFT);
$stmt->bind_param("issd", $clientId, $invoiceNumber, $total, $currency);
$stmt->execute();
$invoiceId = $db->insert_id;

// Add invoice items
$stmt = $db->prepare("
    INSERT INTO invoice_items (
        invoice_id, description, quantity, price, total
    ) VALUES (?, ?, ?, ?, ?)
");
foreach ($items as $item) {
    $total = $item['quantity'] * $item['price'];
    $stmt->bind_param("isidd", 
        $invoiceId,
        $item['description'],
        $item['quantity'],
        $item['price'],
        $total
    );
    $stmt->execute();
}

Get Invoice with Items

$stmt = $db->prepare("
    SELECT i.*, 
           SUM(ii.total) as item_total,
           COUNT(ii.id) as item_count
    FROM invoices i
    LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
    WHERE i.id = ?
    GROUP BY i.id
");
$stmt->bind_param("i", $invoiceId);
$stmt->execute();
$invoice = $stmt->get_result()->fetch_assoc();

// Get items separately
$stmt = $db->prepare("SELECT * FROM invoice_items WHERE invoice_id = ? ORDER BY id");
$stmt->bind_param("i", $invoiceId);
$stmt->execute();
$items = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

Mark Invoice as Paid

$db->begin_transaction();
try {
    // Update invoice
    $stmt = $db->prepare("
        UPDATE invoices 
        SET status = 'paid', paid_at = NOW() 
        WHERE id = ?
    ");
    $stmt->bind_param("i", $invoiceId);
    $stmt->execute();
    
    // Create transaction record
    $stmt = $db->prepare("
        INSERT INTO transactions (
            invoice_id, client_id, gateway, amount, currency,
            status, transaction_id, created_at
        ) VALUES (?, ?, ?, ?, ?, 'completed', ?, NOW())
    ");
    $stmt->bind_param("iissds", 
        $invoiceId, $clientId, $gateway, $amount, $currency, $transactionId
    );
    $stmt->execute();
    
    // Activate services if needed
    activateOrderServices($orderId);
    
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

Apply Credit to Invoice

require_once ROOT_PATH . '/core/CreditManager.php';

$creditManager = new CreditManager();
$result = $creditManager->applyCreditToInvoice($clientId, $invoiceId, $amount);

if ($result['success']) {
    // Credit applied successfully
}