inventory/backend/models/JournalEntry.php

<?php
class JournalEntry {
    private $conn;
    private $table_name = "journal_entries";

    public $id;
    public $entry_number;
    public $entry_date;
    public $description;
    public $reference_number;
    public $created_at;
    public $updated_at;

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

    public function create() {
        $query = "INSERT INTO " . $this->table_name . " SET entry_number=:entry_number, entry_date=:entry_date, description=:description, reference_number=:reference_number, created_at=:created_at, updated_at=:updated_at";

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

        $this->entry_number = htmlspecialchars(strip_tags($this->entry_number));
        $this->entry_date = htmlspecialchars(strip_tags($this->entry_date));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->reference_number = htmlspecialchars(strip_tags($this->reference_number));
        $this->created_at = date('Y-m-d H:i:s');
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":entry_number", $this->entry_number);
        $stmt->bindParam(":entry_date", $this->entry_date);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":reference_number", $this->reference_number);
        $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 * FROM " . $this->table_name . " ORDER BY entry_date DESC, created_at DESC";

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

        return $stmt;
    }

    public function readOne() {
        $query = "SELECT * FROM " . $this->table_name . " WHERE id = ? LIMIT 0,1";

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

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

        $this->entry_number = $row['entry_number'];
        $this->entry_date = $row['entry_date'];
        $this->description = $row['description'];
        $this->reference_number = $row['reference_number'];
        $this->created_at = $row['created_at'];
        $this->updated_at = $row['updated_at'];
    }

    public function update() {
        $query = "UPDATE " . $this->table_name . " SET entry_number=:entry_number, entry_date=:entry_date, description=:description, reference_number=:reference_number, updated_at=:updated_at WHERE id=:id";

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

        $this->entry_number = htmlspecialchars(strip_tags($this->entry_number));
        $this->entry_date = htmlspecialchars(strip_tags($this->entry_date));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->reference_number = htmlspecialchars(strip_tags($this->reference_number));
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":entry_number", $this->entry_number);
        $stmt->bindParam(":entry_date", $this->entry_date);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":reference_number", $this->reference_number);
        $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 * FROM " . $this->table_name . " WHERE 
                  entry_number LIKE ? OR 
                  description LIKE ? OR 
                  reference_number LIKE ?
                  ORDER BY entry_date DESC, 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->execute();

        return $stmt;
    }

    public function getTransactions($entry_id) {
        $query = "SELECT at.*, coa.account_name, coa.account_type FROM account_transactions at LEFT JOIN chart_of_accounts coa ON at.account_id = coa.id WHERE at.journal_entry_id = ? ORDER BY at.id";

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

        return $stmt;
    }

    public function generateEntryNumber() {
        $query = "SELECT COUNT(*) as count FROM " . $this->table_name . " WHERE YEAR(entry_date) = YEAR(CURRENT_DATE)";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $count = $row['count'] + 1;
        
        return date('Y') . '-' . str_pad($count, 4, '0', STR_PAD_LEFT);
    }

    public function validateEntry() {
        $query = "SELECT SUM(debit_amount) as total_debit, SUM(credit_amount) as total_credit FROM account_transactions WHERE journal_entry_id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $total_debit = $row['total_debit'];
        $total_credit = $row['total_credit'];
        
        return abs($total_debit - $total_credit) < 0.01; // Allow for small rounding differences
    }
}
?>