inventory/backend/models/Invoice.php

<?php
class Invoice {
    private $conn;
    private $table_name = "invoices";

    public $id;
    public $client_id;
    public $invoice_number;
    public $issue_date;
    public $due_date;
    public $status;
    public $subtotal;
    public $tax_amount;
    public $total_amount;
    public $notes;
    public $created_at;
    public $updated_at;

    public function __construct($db) {
        $this->conn = $db;
    }

    public function create() {
        $query = "INSERT INTO " . $this->table_name . " SET client_id=:client_id, invoice_number=:invoice_number, issue_date=:issue_date, due_date=:due_date, status=:status, subtotal=:subtotal, tax_amount=:tax_amount, total_amount=:total_amount, notes=:notes, created_at=:created_at, updated_at=:updated_at";

        $stmt = $this->conn->prepare($query);

        $this->client_id = htmlspecialchars(strip_tags($this->client_id));
        $this->invoice_number = htmlspecialchars(strip_tags($this->invoice_number));
        $this->issue_date = htmlspecialchars(strip_tags($this->issue_date));
        $this->due_date = htmlspecialchars(strip_tags($this->due_date));
        $this->status = htmlspecialchars(strip_tags($this->status));
        $this->subtotal = htmlspecialchars(strip_tags($this->subtotal));
        $this->tax_amount = htmlspecialchars(strip_tags($this->tax_amount));
        $this->total_amount = htmlspecialchars(strip_tags($this->total_amount));
        $this->notes = htmlspecialchars(strip_tags($this->notes));
        $this->created_at = date('Y-m-d H:i:s');
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":client_id", $this->client_id);
        $stmt->bindParam(":invoice_number", $this->invoice_number);
        $stmt->bindParam(":issue_date", $this->issue_date);
        $stmt->bindParam(":due_date", $this->due_date);
        $stmt->bindParam(":status", $this->status);
        $stmt->bindParam(":subtotal", $this->subtotal);
        $stmt->bindParam(":tax_amount", $this->tax_amount);
        $stmt->bindParam(":total_amount", $this->total_amount);
        $stmt->bindParam(":notes", $this->notes);
        $stmt->bindParam(":created_at", $this->created_at);
        $stmt->bindParam(":updated_at", $this->updated_at);

        if($stmt->execute()) {
            return true;
        }

        return false;
    }

    public function read() {
        $query = "SELECT i.*, c.first_name, c.last_name, c.company_name FROM " . $this->table_name . " i LEFT JOIN clients c ON i.client_id = c.id ORDER BY i.issue_date DESC, i.created_at DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt;
    }

    public function readOne() {
        $query = "SELECT i.*, c.first_name, c.last_name, c.company_name FROM " . $this->table_name . " i LEFT JOIN clients c ON i.client_id = c.id WHERE i.id = ? LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        $this->client_id = $row['client_id'];
        $this->invoice_number = $row['invoice_number'];
        $this->issue_date = $row['issue_date'];
        $this->due_date = $row['due_date'];
        $this->status = $row['status'];
        $this->subtotal = $row['subtotal'];
        $this->tax_amount = $row['tax_amount'];
        $this->total_amount = $row['total_amount'];
        $this->notes = $row['notes'];
        $this->created_at = $row['created_at'];
        $this->updated_at = $row['updated_at'];
    }

    public function update() {
        $query = "UPDATE " . $this->table_name . " SET client_id=:client_id, invoice_number=:invoice_number, issue_date=:issue_date, due_date=:due_date, status=:status, subtotal=:subtotal, tax_amount=:tax_amount, total_amount=:total_amount, notes=:notes, updated_at=:updated_at WHERE id=:id";

        $stmt = $this->conn->prepare($query);

        $this->client_id = htmlspecialchars(strip_tags($this->client_id));
        $this->invoice_number = htmlspecialchars(strip_tags($this->invoice_number));
        $this->issue_date = htmlspecialchars(strip_tags($this->issue_date));
        $this->due_date = htmlspecialchars(strip_tags($this->due_date));
        $this->status = htmlspecialchars(strip_tags($this->status));
        $this->subtotal = htmlspecialchars(strip_tags($this->subtotal));
        $this->tax_amount = htmlspecialchars(strip_tags($this->tax_amount));
        $this->total_amount = htmlspecialchars(strip_tags($this->total_amount));
        $this->notes = htmlspecialchars(strip_tags($this->notes));
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":client_id", $this->client_id);
        $stmt->bindParam(":invoice_number", $this->invoice_number);
        $stmt->bindParam(":issue_date", $this->issue_date);
        $stmt->bindParam(":due_date", $this->due_date);
        $stmt->bindParam(":status", $this->status);
        $stmt->bindParam(":subtotal", $this->subtotal);
        $stmt->bindParam(":tax_amount", $this->tax_amount);
        $stmt->bindParam(":total_amount", $this->total_amount);
        $stmt->bindParam(":notes", $this->notes);
        $stmt->bindParam(":updated_at", $this->updated_at);
        $stmt->bindParam(":id", $this->id);

        if($stmt->execute()) {
            return true;
        }

        return false;
    }

    public function delete() {
        $query = "DELETE FROM " . $this->table_name . " WHERE id = ?";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);

        if($stmt->execute()) {
            return true;
        }

        return false;
    }

    public function search($search_term) {
        $query = "SELECT i.*, c.first_name, c.last_name, c.company_name FROM " . $this->table_name . " i LEFT JOIN clients c ON i.client_id = c.id WHERE 
                  i.invoice_number LIKE ? OR 
                  c.first_name LIKE ? OR 
                  c.last_name LIKE ? OR 
                  c.company_name LIKE ? 
                  i.notes LIKE ?
                  ORDER BY i.issue_date DESC, i.created_at DESC";

        $stmt = $this->conn->prepare($query);
        $search_term = "%{$search_term}%";
        $stmt->bindParam(1, $search_term);
        $stmt->bindParam(2, $search_term);
        $stmt->bindParam(3, $search_term);
        $stmt->bindParam(4, $search_term);
        $stmt->bindParam(5, $search_term);
        $stmt->execute();

        return $stmt;
    }

    public function getInvoiceItems($invoice_id) {
        $query = "SELECT ii.*, i.name as item_name FROM invoice_items ii LEFT JOIN items i ON ii.item_id = i.id WHERE ii.invoice_id = ? ORDER BY ii.id";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $invoice_id);
        $stmt->execute();

        return $stmt;
    }

    public function getPayments($invoice_id) {
        $query = "SELECT * FROM payments WHERE invoice_id = ? ORDER BY payment_date DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $invoice_id);
        $stmt->execute();

        return $stmt;
    }

    public function getDisplayName() {
        return $this->invoice_number . ' - ' . $this->getClientName();
    }

    public function getClientName() {
        $query = "SELECT CONCAT(first_name, ' ', last_name) as client_name FROM clients WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->client_id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row['client_name'];
    }

    public function getStatusBadge() {
        $badges = [
            'draft' => '<span style="background-color: #6c757d; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Draft</span>',
            'sent' => '<span style="background-color: #17a2b8; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Sent</span>',
            'paid' => '<span style="background-color: #28a745; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Paid</span>',
            'overdue' => '<span style="background-color: #dc3545; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Overdue</span>',
            'cancelled' => '<span style="background-color: #6c757d; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Cancelled</span>'
        ];
        
        return $badges[$this->status] ?? $this->status;
    }

    public function calculateTotalFromItems($items) {
        $total = 0;
        foreach ($items as $item) {
            $total += $item['line_total'];
        }
        return $total;
    }
}
?>