<?php
namespace App\Models;
use App\Core\Database;
use PDO;
class LeaveRequest
{
private Database $db;
private string $table_name = "leave_requests";
// Properties
public ?int $id = null;
public int $employee_id;
public int $leave_type_id;
public string $start_date; // YYYY-MM-DD
public string $end_date; // YYYY-MM-DD
public ?string $reason = null;
public string $status = 'PENDING'; // PENDING, APPROVED, REJECTED, CANCELLED
public ?int $approved_by_id = null;
public ?string $approved_at = null;
public ?string $requested_at = null;
public ?string $updated_at = null;
// For joining with other tables (optional, can be populated by specific methods)
public ?Employee $employee = null;
public ?LeaveType $leaveType = null;
public ?Employee $approver = null;
/**
* @param Database $db
*/
public function __construct(?Database $db = null)
{
$this->db = $db ?: Database::getInstance();
}
// Setter methods
/**
* @param int $id
* @return mixed
*/
public function setId(int $id): self
{
$this->id = $id;
return $this;
}
/**
* @param int $employee_id
* @return mixed
*/
public function setEmployeeId(int $employee_id): self
{
$this->employee_id = $employee_id;
return $this;
}
/**
* @param int $leave_type_id
* @return mixed
*/
public function setLeaveTypeId(int $leave_type_id): self
{
$this->leave_type_id = $leave_type_id;
return $this;
}
/**
* @param string $start_date
* @return mixed
*/
public function setStartDate(string $start_date): self
{
$this->start_date = $start_date;
return $this;
}
/**
* @param string $end_date
* @return mixed
*/
public function setEndDate(string $end_date): self
{
$this->end_date = $end_date;
return $this;
}
/**
* @param string $reason
* @return mixed
*/
public function setReason(?string $reason): self
{
$this->reason = $reason;
return $this;
}
/**
* @param string $status
* @return mixed
*/
public function setStatus(string $status): self
{
// Basic validation for status
$allowed_statuses = ['PENDING', 'APPROVED', 'REJECTED', 'CANCELLED'];
if (in_array(strtoupper($status), $allowed_statuses)) {
$this->status = strtoupper($status);
} else {
// Handle invalid status, perhaps throw an exception or default
$this->status = 'PENDING';
}
return $this;
}
/**
* @param int $approved_by_id
* @return mixed
*/
public function setApprovedById(?int $approved_by_id): self
{
$this->approved_by_id = $approved_by_id;
return $this;
}
/**
* @param string $approved_at
* @return mixed
*/
public function setApprovedAt(?string $approved_at): self
{
$this->approved_at = $approved_at;
return $this;
}
/**
* Create a new leave request.
* @return bool True on success, false on failure.
*/
public function create(): bool
{
$query = "INSERT INTO {$this->table_name}
(employee_id, leave_type_id, start_date, end_date, reason, status, approved_by_id, approved_at)
VALUES
(:employee_id, :leave_type_id, :start_date, :end_date, :reason, :status, :approved_by_id, :approved_at)";
$this->db->query($query);
$this->db->bind(':employee_id', $this->employee_id, PDO::PARAM_INT);
$this->db->bind(':leave_type_id', $this->leave_type_id, PDO::PARAM_INT);
$this->db->bind(':start_date', $this->start_date);
$this->db->bind(':end_date', $this->end_date);
$this->db->bind(':reason', $this->reason, $this->reason === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
$this->db->bind(':status', $this->status);
$this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
$this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
if ($this->db->execute()) {
$this->id = (int) $this->db->lastInsertId();
return true;
}
return false;
}
/**
* Read leave requests.
* @param ?int $id If null, fetches all. Otherwise, fetches by ID.
* @return mixed Array of requests, a single request object, or false.
*/
public function read(?int $id = null)
{
$baseQuery = "SELECT lr.*,
e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
lt.name as leave_type_name,
approver.first_name as approver_first_name, approver.last_name as approver_last_name
FROM {$this->table_name} lr
JOIN employees e ON lr.employee_id = e.id
JOIN leave_types lt ON lr.leave_type_id = lt.id
LEFT JOIN employees approver ON lr.approved_by_id = approver.id";
if ($id !== null) {
$query = $baseQuery." WHERE lr.id = :id LIMIT 1";
$this->db->query($query);
$this->db->bind(':id', $id, PDO::PARAM_INT);
$row = $this->db->single();
if ($row) {
return $this->populateFromRow($row);
}
return false;
} else {
// Read all requests, potentially with filters or pagination in a real app
$query = $baseQuery." ORDER BY lr.requested_at DESC";
$this->db->query($query);
$results = $this->db->resultSet();
$requests = [];
foreach ($results as $row) {
$request = new self($this->db); // Create new instance for each row
$requests[] = $request->populateFromRow($row);
}
return $requests;
}
}
/**
* Helper method to populate object properties from a database row.
* @param array $row Associative array from database fetch.
* @return self
*/
private function populateFromRow(array $row): self
{
$this->id = (int) $row['id'];
$this->employee_id = (int) $row['employee_id'];
$this->leave_type_id = (int) $row['leave_type_id'];
$this->start_date = $row['start_date'];
$this->end_date = $row['end_date'];
$this->reason = $row['reason'];
$this->status = $row['status'];
$this->approved_by_id = $row['approved_by_id'] ? (int) $row['approved_by_id'] : null;
$this->approved_at = $row['approved_at'];
$this->requested_at = $row['requested_at'];
$this->updated_at = $row['updated_at'];
// Populate related objects if data is available from JOIN
// This is a simple way; more complex hydration might be needed for full ORM-like behavior
$this->employee = new Employee($this->db);
$this->employee->id = (int) $row['employee_id'];
$this->employee->first_name = $row['employee_first_name'] ?? 'N/A';
$this->employee->last_name = $row['employee_last_name'] ?? 'N/A';
$this->employee->email = $row['employee_email'] ?? 'N/A';
$this->leaveType = new LeaveType($this->db);
$this->leaveType->id = (int) $row['leave_type_id'];
$this->leaveType->name = $row['leave_type_name'] ?? 'N/A';
if ($this->approved_by_id) {
$this->approver = new Employee($this->db);
$this->approver->id = $this->approved_by_id;
$this->approver->first_name = $row['approver_first_name'] ?? 'N/A';
$this->approver->last_name = $row['approver_last_name'] ?? 'N/A';
}
return $this;
}
/**
* Get all pending leave requests for a specific employee.
* @param int $employeeId The ID of the employee.
* @return array Array of leave request objects (or arrays if populateFromRow creates arrays).
*/
public function getPendingRequestsByEmployee(int $employeeId): array
{
$query = "SELECT lr.*,
e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
lt.name as leave_type_name,
approver.first_name as approver_first_name, approver.last_name as approver_last_name
FROM {$this->table_name} lr
JOIN employees e ON lr.employee_id = e.id
JOIN leave_types lt ON lr.leave_type_id = lt.id
LEFT JOIN employees approver ON lr.approved_by_id = approver.id
WHERE lr.employee_id = :employee_id AND lr.status = 'PENDING'
ORDER BY lr.start_date ASC";
$this->db->query($query);
$this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
$results = $this->db->resultSet();
$requests = [];
if ($results) {
foreach ($results as $row) {
$request = new self($this->db);
$requests[] = $request->populateFromRow($row);
}
}
return $requests;
}
/**
* Update an existing leave request.
* Typically used to update status, approver_id, approved_at.
* @return bool True on success, false on failure.
*/
public function update(): bool
{
if ($this->id === null) {
return false;
}
$query = "UPDATE {$this->table_name} SET
employee_id = :employee_id,
leave_type_id = :leave_type_id,
start_date = :start_date,
end_date = :end_date,
reason = :reason,
status = :status,
approved_by_id = :approved_by_id,
approved_at = :approved_at
/* updated_at is usually handled by DB trigger or DEFAULT CURRENT_TIMESTAMP ON UPDATE */
WHERE id = :id";
$this->db->query($query);
$this->db->bind(':id', $this->id, PDO::PARAM_INT);
$this->db->bind(':employee_id', $this->employee_id, PDO::PARAM_INT);
$this->db->bind(':leave_type_id', $this->leave_type_id, PDO::PARAM_INT);
$this->db->bind(':start_date', $this->start_date);
$this->db->bind(':end_date', $this->end_date);
$this->db->bind(':reason', $this->reason, $this->reason === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
$this->db->bind(':status', $this->status);
$this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
$this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
return $this->db->execute();
}
/**
* Update the status of a leave request.
* @param string $status The new status.
* @param ?int $approvedById The ID of the employee who approved/rejected (if applicable).
* @return bool True on success, false on failure.
*/
public function updateStatus(string $status, ?int $approvedById = null): bool
{
if ($this->id === null) {
return false;
}
$this->setStatus($status); // Use setter for validation
$this->approved_by_id = $approvedById;
$this->approved_at = ($status === 'APPROVED' || $status === 'REJECTED') ? date('Y-m-d H:i:s') : null;
$query = "UPDATE {$this->table_name} SET
status = :status,
approved_by_id = :approved_by_id,
approved_at = :approved_at
WHERE id = :id";
$this->db->query($query);
$this->db->bind(':id', $this->id, PDO::PARAM_INT);
$this->db->bind(':status', $this->status);
$this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
$this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
return $this->db->execute();
}
/**
* Delete a leave request.
* @param ?int $id If null, uses $this->id.
* @return bool True on success, false on failure.
*/
public function delete(?int $id = null): bool
{
$id_to_delete = $id ?? $this->id;
if ($id_to_delete === null) {
return false;
}
$query = "DELETE FROM {$this->table_name} WHERE id = :id";
$this->db->query($query);
$this->db->bind(':id', $id_to_delete, PDO::PARAM_INT);
return $this->db->execute();
}
// Potential methods to add later:
// - findByEmployeeId(int $employeeId, ?string $status = null, ?string $dateRangeStart = null, ?string $dateRangeEnd = null)
// - findByManagerId(int $managerId, ?string $status = 'PENDING')
// - findPendingRequests()
// - getRequestsForHRSummary(string $date)
/**
* Get all approved leave/wfh requests that are active on a specific date.
* @param string $dateString The date to check in 'YYYY-MM-DD' format.
* @return array Array of leave request objects (or arrays, depending on populateFromRow).
*/
public function getAbsencesForDate(string $dateString): array
{
// Validate dateString format
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateString) || !\DateTime::createFromFormat('Y-m-d', $dateString)) {
// Use a globally available log_message if defined, otherwise error_log
if (function_exists('log_message')) {
log_message("Invalid date format for getAbsencesForDate: {$dateString}");
} else {
error_log("LeaveRequest Model: Invalid date format for getAbsencesForDate: {$dateString}");
}
return [];
}
$query = "SELECT lr.*,
e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
lt.name as leave_type_name, lt.alias as leave_type_alias
-- Add e.department if you have it and want to display it
FROM {$this->table_name} lr
JOIN employees e ON lr.employee_id = e.id
JOIN leave_types lt ON lr.leave_type_id = lt.id
WHERE lr.status = 'APPROVED'
AND :check_date BETWEEN lr.start_date AND lr.end_date
ORDER BY lt.name ASC, e.first_name ASC, e.last_name ASC";
$this->db->query($query);
$this->db->bind(':check_date', $dateString);
$results = $this->db->resultSet();
$absences = [];
if ($results) {
foreach ($results as $row) {
$request = new self($this->db);
$absences[] = $request->populateFromRow($row);
}
}
return $absences;
}
}