inventory/backend/models/AccountTransaction.php

<?php
class AccountTransaction {
    private $conn;
    private $table_name = "account_transactions";

    public $id;
    public $journal_entry_id;
    public $account_id;
    public $debit_amount;
    public $credit_amount;
    public $description;
    public $created_at;
    public $updated_at;

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

    public function create() {
        $query = "INSERT INTO " . $this->table_name . " SET journal_entry_id=:journal_entry_id, account_id=:account_id, debit_amount=:debit_amount, credit_amount=:credit_amount, description=:description, created_at=:created_at, updated_at=:updated_at";

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

        $this->journal_entry_id = htmlspecialchars(strip_tags($this->journal_entry_id));
        $this->account_id = htmlspecialchars(strip_tags($this->account_id));
        $this->debit_amount = htmlspecialchars(strip_tags($this->debit_amount));
        $this->credit_amount = htmlspecialchars(strip_tags($this->credit_amount));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->created_at = date('Y-m-d H:i:s');
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":journal_entry_id", $this->journal_entry_id);
        $stmt->bindParam(":account_id", $this->account_id);
        $stmt->bindParam(":debit_amount", $this->debit_amount);
        $stmt->bindParam(":credit_amount", $this->credit_amount);
        $stmt->bindParam(":description", $this->description);
        $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 at.*, coa.account_name, coa.account_type, je.entry_number, je.entry_date FROM " . $this->table_name . " at LEFT JOIN chart_of_accounts coa ON at.account_id = coa.id LEFT JOIN journal_entries je ON at.journal_entry_id = je.id ORDER BY je.entry_date DESC, je.created_at DESC, at.id DESC";

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

        return $stmt;
    }

    public function readOne() {
        $query = "SELECT at.*, coa.account_name, coa.account_type, je.entry_number, je.entry_date FROM " . $this->table_name . " at LEFT JOIN chart_of_accounts coa ON at.account_id = coa.id LEFT JOIN journal_entries je ON at.journal_entry_id = je.id WHERE at.id = ? LIMIT 0,1";

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

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

        $this->journal_entry_id = $row['journal_entry_id'];
        $this->account_id = $row['account_id'];
        $this->debit_amount = $row['debit_amount'];
        $this->credit_amount = $row['credit_amount'];
        $this->description = $row['description'];
        $this->created_at = $row['created_at'];
        $this->updated_at = $row['updated_at'];
    }

    public function update() {
        $query = "UPDATE " . $this->table_name . " SET journal_entry_id=:journal_entry_id, account_id=:account_id, debit_amount=:debit_amount, credit_amount=:credit_amount, description=:description, updated_at=:updated_at WHERE id=:id";

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

        $this->journal_entry_id = htmlspecialchars(strip_tags($this->journal_entry_id));
        $this->account_id = htmlspecialchars(strip_tags($this->account_id));
        $this->debit_amount = htmlspecialchars(strip_tags($this->debit_amount));
        $this->credit_amount = htmlspecialchars(strip_tags($this->credit_amount));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":journal_entry_id", $this->journal_entry_id);
        $stmt->bindParam(":account_id", $this->account_id);
        $stmt->bindParam(":debit_amount", $this->debit_amount);
        $stmt->bindParam(":credit_amount", $this->credit_amount);
        $stmt->bindParam(":description", $this->description);
        $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 at.*, coa.account_name, coa.account_type, je.entry_number, je.entry_date FROM " . $this->table_name . " at LEFT JOIN chart_of_accounts coa ON at.account_id = coa.id LEFT JOIN journal_entries je ON at.journal_entry_id = je.id WHERE 
                  at.description LIKE ? OR 
                  coa.account_name LIKE ? OR 
                  je.entry_number LIKE ? OR 
                  je.entry_date LIKE ?
                  ORDER BY je.entry_date DESC, je.created_at DESC, at.id 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->execute();

        return $stmt;
    }

    public function getBalance($account_id) {
        $query = "SELECT SUM(debit_amount) - SUM(credit_amount) as balance FROM " . $this->table_name . " WHERE account_id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $account_id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row['balance'] ?? 0;
    }

    public function getTransactionType() {
        if ($this->debit_amount > 0 && $this->credit_amount == 0) {
            return '<span style="background-color: #dc3545; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Debit</span>';
        } elseif ($this->debit_amount == 0 && $this->credit_amount > 0) {
            return '<span style="background-color: #28a745; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Credit</span>';
        } else {
            return '<span style="background-color: #ffc107; color: black; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Mixed</span>';
        }
    }

    public function getAmount() {
        if ($this->debit_amount > 0) {
            return $this->debit_amount;
        } else {
            return $this->credit_amount;
        }
    }
}
?>