inventory/backend/models/ChartOfAccounts.php

<?php
class ChartOfAccounts {
    private $conn;
    private $table_name = "chart_of_accounts";

    public $id;
    public $account_number;
    public $account_name;
    public $account_type;
    public $parent_id;
    public $description;
    public $opening_balance;
    public $current_balance;
    public $vat_percentage;
    public $is_active;
    public $created_at;
    public $updated_at;

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

    public function create() {
        $query = "INSERT INTO " . $this->table_name . " SET account_number=:account_number, account_name=:account_name, account_type=:account_type, parent_id=:parent_id, description=:description, opening_balance=:opening_balance, current_balance=:current_balance, vat_percentage=:vat_percentage, is_active=:is_active, created_at=:created_at, updated_at=:updated_at";

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

        $this->account_number = htmlspecialchars(strip_tags($this->account_number));
        $this->account_name = htmlspecialchars(strip_tags($this->account_name));
        $this->account_type = htmlspecialchars(strip_tags($this->account_type));
        $this->parent_id = htmlspecialchars(strip_tags($this->parent_id));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->opening_balance = htmlspecialchars(strip_tags($this->opening_balance));
        $this->current_balance = htmlspecialchars(strip_tags($this->current_balance));
        $this->vat_percentage = htmlspecialchars(strip_tags($this->vat_percentage));
        $this->is_active = $this->is_active ? 1 : 0;
        $this->created_at = date('Y-m-d H:i:s');
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":account_number", $this->account_number);
        $stmt->bindParam(":account_name", $this->account_name);
        $stmt->bindParam(":account_type", $this->account_type);
        $stmt->bindParam(":parent_id", $this->parent_id);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":opening_balance", $this->opening_balance);
        $stmt->bindParam(":current_balance", $this->current_balance);
        $stmt->bindParam(":vat_percentage", $this->vat_percentage);
        $stmt->bindParam(":is_active", $this->is_active);
        $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 . " WHERE is_active = TRUE ORDER BY account_type, account_number";

        $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->account_number = $row['account_number'];
        $this->account_name = $row['account_name'];
        $this->account_type = $row['account_type'];
        $this->parent_id = $row['parent_id'];
        $this->description = $row['description'];
        $this->opening_balance = $row['opening_balance'];
        $this->current_balance = $row['current_balance'];
        $this->vat_percentage = $row['vat_percentage'];
        $this->is_active = $row['is_active'];
        $this->created_at = $row['created_at'];
        $this->updated_at = $row['updated_at'];
    }

    public function update() {
        $query = "UPDATE " . $this->table_name . " SET account_number=:account_number, account_name=:account_name, account_type=:account_type, parent_id=:parent_id, description=:description, opening_balance=:opening_balance, current_balance=:current_balance, vat_percentage=:vat_percentage, is_active=:is_active, updated_at=:updated_at WHERE id=:id";

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

        $this->account_number = htmlspecialchars(strip_tags($this->account_number));
        $this->account_name = htmlspecialchars(strip_tags($this->account_name));
        $this->account_type = htmlspecialchars(strip_tags($this->account_type));
        $this->parent_id = htmlspecialchars(strip_tags($this->parent_id));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->opening_balance = htmlspecialchars(strip_tags($this->opening_balance));
        $this->current_balance = htmlspecialchars(strip_tags($this->current_balance));
        $this->vat_percentage = htmlspecialchars(strip_tags($this->vat_percentage));
        $this->is_active = $this->is_active ? 1 : 0;
        $this->updated_at = date('Y-m-d H:i:s');

        $stmt->bindParam(":account_number", $this->account_number);
        $stmt->bindParam(":account_name", $this->account_name);
        $stmt->bindParam(":account_type", $this->account_type);
        $stmt->bindParam(":parent_id", $this->parent_id);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":opening_balance", $this->opening_balance);
        $stmt->bindParam(":current_balance", $this->current_balance);
        $stmt->bindParam(":vat_percentage", $this->vat_percentage);
        $stmt->bindParam(":is_active", $this->is_active);
        $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 
                  account_number LIKE ? OR 
                  account_name LIKE ? OR 
                  description LIKE ?
                  ORDER BY account_type, account_number";

        $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 getAccountTypeBadge() {
        $badges = [
            'asset' => '<span style="background-color: #007bff; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Vasta-omaisuus</span>',
            'liability' => '<span style="background-color: #dc3545; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Velat</span>',
            'equity' => '<span style="background-color: #28a745; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Oma pääoma</span>',
            'revenue' => '<span style="background-color: #17a2b8; color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Tuotot</span>',
            'expense' => '<span style="background-color: #ffc107; color: black; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Kulut</span>'
        ];
        
        return $badges[$this->account_type] ?? $this->account_type;
    }

    public function getAccountTypeName() {
        $types = [
            'asset' => 'Vasta-omaisuus',
            'liability' => 'Velat',
            'equity' => 'Oma pääoma',
            'revenue' => 'Tuotot',
            'expense' => 'Kulut'
        ];
        
        return $types[$this->account_type] ?? $this->account_type;
    }

    public function getAccountCategory() {
        $categories = [
            'asset' => '1000-1999',
            'liability' => '2000-2999',
            'equity' => '3000-3999',
            'revenue' => '4000-4999',
            'expense' => '5000-5999'
        ];
        
        return $categories[$this->account_type] ?? $this->account_type;
    }
}
?>