inventory/backend/models/WorkHour.php

<?php

class WorkHour {
    private $conn;
    private $table_name = 'work_hours';
    
    public $id;
    public $task_id;
    public $user_id;
    public $date;
    public $hours;
    public $description;
    public $rate;
    public $total_amount;
    public $created_at;
    public $updated_at;
    
    public function __construct($db) {
        $this->conn = $db;
    }
    
    public function create() {
        // Auto-fetch client hour price if rate is not provided or empty
        if (!$this->rate || $this->rate === '') {
            $this->rate = $this->getClientHourPrice();
        }
        
        // Calculate total amount if rate is provided and greater than 0
        if ($this->rate && $this->rate > 0) {
            $this->total_amount = $this->hours * $this->rate;
        } else {
            $this->rate = null;
            $this->total_amount = null;
        }
        
        $query = "INSERT INTO " . $this->table_name . " 
                  SET task_id=:task_id, user_id=:user_id, date=:date, hours=:hours, 
                      description=:description, rate=:rate, total_amount=:total_amount, 
                      created_at=:created_at, updated_at=:updated_at";
        
        $stmt = $this->conn->prepare($query);
        
        $this->task_id = htmlspecialchars(strip_tags($this->task_id));
        $this->user_id = htmlspecialchars(strip_tags($this->user_id));
        $this->date = htmlspecialchars(strip_tags($this->date));
        $this->hours = htmlspecialchars(strip_tags($this->hours));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->rate = $this->rate ? htmlspecialchars(strip_tags($this->rate)) : null;
        $this->total_amount = $this->total_amount ? htmlspecialchars(strip_tags($this->total_amount)) : null;
        $this->created_at = date('Y-m-d H:i:s');
        $this->updated_at = date('Y-m-d H:i:s');
        
        $stmt->bindParam(":task_id", $this->task_id);
        $stmt->bindParam(":user_id", $this->user_id);
        $stmt->bindParam(":date", $this->date);
        $stmt->bindParam(":hours", $this->hours);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":rate", $this->rate);
        $stmt->bindParam(":total_amount", $this->total_amount);
        $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 wh.*, t.title as task_title, u.first_name, u.last_name 
                  FROM " . $this->table_name . " wh 
                  LEFT JOIN tasks t ON wh.task_id = t.id 
                  LEFT JOIN users u ON wh.user_id = u.id 
                  ORDER BY wh.date DESC, wh.created_at DESC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        
        return $stmt;
    }
    
    public function readByTask($task_id) {
        $query = "SELECT wh.*, u.first_name, u.last_name, c.hour_price as client_hour_price,
                  c.company_name as client_name, c.first_name as client_first_name, c.last_name as client_last_name
                  FROM " . $this->table_name . " wh 
                  LEFT JOIN users u ON wh.user_id = u.id 
                  LEFT JOIN tasks t ON wh.task_id = t.id 
                  LEFT JOIN projects p ON t.project_id = p.id 
                  LEFT JOIN clients c ON p.customer_id = c.id 
                  WHERE wh.task_id = ? 
                  ORDER BY wh.date DESC, wh.created_at DESC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $task_id);
        $stmt->execute();
        
        return $stmt;
    }
    
    public function readOne() {
        $query = "SELECT wh.*, t.title as task_title, u.first_name, u.last_name 
                  FROM " . $this->table_name . " wh 
                  LEFT JOIN tasks t ON wh.task_id = t.id 
                  LEFT JOIN users u ON wh.user_id = u.id 
                  WHERE wh.id = ? LIMIT 0,1";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $this->task_id = $row['task_id'];
        $this->user_id = $row['user_id'];
        $this->date = $row['date'];
        $this->hours = $row['hours'];
        $this->description = $row['description'];
        $this->rate = $row['rate'];
        $this->total_amount = $row['total_amount'];
        $this->created_at = $row['created_at'];
        $this->updated_at = $row['updated_at'];
    }
    
    public function update() {
        $query = "UPDATE " . $this->table_name . " 
                  SET task_id=:task_id, user_id=:user_id, date=:date, hours=:hours, 
                      description=:description, rate=:rate, total_amount=:total_amount, 
                      updated_at=:updated_at 
                  WHERE id=:id";
        
        $stmt = $this->conn->prepare($query);
        
        $this->task_id = htmlspecialchars(strip_tags($this->task_id));
        $this->user_id = htmlspecialchars(strip_tags($this->user_id));
        $this->date = htmlspecialchars(strip_tags($this->date));
        $this->hours = htmlspecialchars(strip_tags($this->hours));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->rate = htmlspecialchars(strip_tags($this->rate));
        $this->total_amount = htmlspecialchars(strip_tags($this->total_amount));
        $this->updated_at = date('Y-m-d H:i:s');
        
        $stmt->bindParam(":task_id", $this->task_id);
        $stmt->bindParam(":user_id", $this->user_id);
        $stmt->bindParam(":date", $this->date);
        $stmt->bindParam(":hours", $this->hours);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":rate", $this->rate);
        $stmt->bindParam(":total_amount", $this->total_amount);
        $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 getTotalHoursByTask($task_id) {
        $query = "SELECT SUM(hours) as total_hours, COUNT(*) as entries 
                  FROM " . $this->table_name . " 
                  WHERE task_id = ?";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $task_id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return [
            'total_hours' => $row['total_hours'] || 0,
            'entries' => $row['entries'] || 0
        ];
    }
    
    public function getTotalHoursByUser($user_id, $start_date = null, $end_date = null) {
        $query = "SELECT SUM(wh.hours) as total_hours, COUNT(*) as entries 
                  FROM " . $this->table_name . " wh 
                  WHERE wh.user_id = ?";
        
        if ($start_date && $end_date) {
            $query .= " AND wh.date BETWEEN ? AND ?";
        }
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $user_id);
        
        if ($start_date && $end_date) {
            $stmt->bindParam(2, $start_date);
            $stmt->bindParam(3, $end_date);
        }
        
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return [
            'total_hours' => $row['total_hours'] || 0,
            'entries' => $row['entries'] || 0
        ];
    }
    
    public function getClientHourPrice() {
        $query = "SELECT c.hour_price 
                  FROM " . $this->table_name . " wh 
                  LEFT JOIN tasks t ON wh.task_id = t.id 
                  LEFT JOIN projects p ON t.project_id = p.id 
                  LEFT JOIN clients c ON p.customer_id = c.id 
                  WHERE wh.task_id = ? AND c.hour_price IS NOT NULL AND c.hour_price > 0 
                  LIMIT 1";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->task_id);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? $row['hour_price'] : 0;
    }
}
?>