<?php
namespace App\Models;
use App\Core\Database;
use PDO;
class LeaveBalance
{
private Database $db;
private string $table_name = "leave_balances";
// Properties
public ?int $id = null;
public int $employee_id;
public int $leave_type_id;
public float $balance_days; // Using float for days like 8.5
public int $year; // The year this balance applies to
public ?string $last_updated_at = null;
// Optional: For holding related objects if populated
public ?Employee $employee = null;
public ?LeaveType $leaveType = 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 float $balance_days
* @return mixed
*/
public function setBalanceDays(float $balance_days): self
{
$this->balance_days = $balance_days;
return $this;
}
/**
* @param int $year
* @return mixed
*/
public function setYear(int $year): self
{
$this->year = $year;
return $this;
}
/**
* Calculate the duration in days between two dates, inclusive.
* Basic calculation, does not account for weekends or holidays yet.
* @param string $startDate YYYY-MM-DD
* @param string $endDate YYYY-MM-DD
* @return int Number of days. Returns 0 if dates are invalid or end_date is before start_date.
*/
public static function calculateLeaveDurationInDays(string $startDate, string $endDate): int
{
try {
// Ensure DateTime class is available or use \DateTime if not in global namespace
$start = new \DateTime($startDate);
$end = new \DateTime($endDate);
if ($start > $end) {
return 0;
}
$diff = $start->diff($end);
return $diff->days + 1;
} catch (\Exception $e) {
// It's better to use a proper logging mechanism if available
// For now, using error_log if log_message function is not defined in this scope
if (function_exists('log_message')) {
log_message("Error calculating date duration: {$startDate} to {$endDate}. Error: ".$e->getMessage());
} else {
error_log("LeaveBalance Model: Error calculating date duration: {$startDate} to {$endDate}. Error: ".$e->getMessage());
}
return 0;
}
}
/**
* Deducts a specific number of days from an employee's leave balance for a given leave type and year.
* If no balance record exists, it attempts to create one with a negative balance.
*
* @param int $employeeId
* @param int $leaveTypeId
* @param int $year
* @param float $daysToDeduct
* @return bool True on successful deduction, false otherwise.
*/
public function deductFromBalance(int $employeeId, int $leaveTypeId, int $year, float $daysToDeduct): bool
{
if ($daysToDeduct <= 0) {
if (function_exists('log_message')) {
log_message("Days to deduct must be positive. Value: {$daysToDeduct}");
} else {
error_log("LeaveBalance Model: Days to deduct must be positive. Value: {$daysToDeduct}");
}
return false;
}
$this->db->beginTransaction();
try {
$query = "SELECT * FROM {$this->table_name}
WHERE employee_id = :employee_id
AND leave_type_id = :leave_type_id
AND year = :year
LIMIT 1 FOR UPDATE";
$this->db->query($query);
$this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
$this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
$this->db->bind(':year', $year, PDO::PARAM_INT);
$currentBalanceRow = $this->db->single();
$newBalance = 0; // Initialize to ensure it's defined for logging
if ($currentBalanceRow) {
$newBalance = (float) $currentBalanceRow['balance_days'] - $daysToDeduct;
$updateQuery = "UPDATE {$this->table_name} SET balance_days = :new_balance
WHERE id = :id";
$this->db->query($updateQuery);
$this->db->bind(':new_balance', $newBalance);
$this->db->bind(':id', (int) $currentBalanceRow['id'], PDO::PARAM_INT);
$this->db->execute();
if (function_exists('log_message')) {
log_message("Updated balance for employee {$employeeId}, type {$leaveTypeId}, year {$year}. Old: {$currentBalanceRow['balance_days']}, Deducted: {$daysToDeduct}, New: {$newBalance}");
}
} else {
$newBalance = 0 - $daysToDeduct;
$insertQuery = "INSERT INTO {$this->table_name} (employee_id, leave_type_id, year, balance_days)
VALUES (:employee_id, :leave_type_id, :year, :balance_days)";
$this->db->query($insertQuery);
$this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
$this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
$this->db->bind(':year', $year, PDO::PARAM_INT);
$this->db->bind(':balance_days', $newBalance);
$this->db->execute();
if (function_exists('log_message')) {
log_message("Created new balance entry for employee {$employeeId}, type {$leaveTypeId}, year {$year} with balance {$newBalance}");
}
}
$this->db->commit();
return true;
} catch (\Exception $e) {
$this->db->rollBack();
if (function_exists('log_message')) {
log_message("Error deducting leave balance for employee {$employeeId}, type {$leaveTypeId}: ".$e->getMessage());
} else {
error_log("LeaveBalance Model: Error deducting leave balance for employee {$employeeId}, type {$leaveTypeId}: ".$e->getMessage());
}
return false;
}
}
/**
* Create or update a leave balance record.
* This method uses an "upsert" like logic: if a balance for the employee, leave type, and year exists, it updates it.
* Otherwise, it creates a new one.
*
* @return bool True on success, false on failure.
*/
public function save(): bool
{
// Check if a balance record already exists for this employee, leave type, and year
$existing = $this->findByEmployeeLeaveTypeYear($this->employee_id, $this->leave_type_id, $this->year);
if ($existing && $existing->id) {
// Update existing record
$this->id = $existing->id; // Ensure ID is set for update
$query = "UPDATE {$this->table_name} SET
balance_days = :balance_days
/* last_updated_at is usually handled by DB trigger or DEFAULT CURRENT_TIMESTAMP ON UPDATE */
WHERE id = :id";
$this->db->query($query);
$this->db->bind(':balance_days', $this->balance_days);
$this->db->bind(':id', $this->id, PDO::PARAM_INT);
} else {
// Create new record
$query = "INSERT INTO {$this->table_name} (employee_id, leave_type_id, balance_days, year)
VALUES (:employee_id, :leave_type_id, :balance_days, :year)";
$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(':balance_days', $this->balance_days); // PDO should handle float correctly
$this->db->bind(':year', $this->year, PDO::PARAM_INT);
}
if ($this->db->execute()) {
if (!$existing || !$existing->id) {
// If it was an insert
$this->id = (int) $this->db->lastInsertId();
}
return true;
}
return false;
}
/**
* Read leave balances.
* @param ?int $id If null, fetches all. Otherwise, fetches by ID.
* @return mixed Array of balances, a single balance object, or false.
*/
public function read(?int $id = null)
{
if ($id !== null) {
$query = "SELECT * FROM {$this->table_name} WHERE 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 balances
$query = "SELECT * FROM {$this->table_name} ORDER BY year DESC, employee_id ASC";
$this->db->query($query);
$results = $this->db->resultSet();
$balances = [];
foreach ($results as $row) {
$balance = new self($this->db);
$balances[] = $balance->populateFromRow($row);
}
return $balances;
}
}
/**
* Find a specific leave balance for an employee, leave type, and year.
* @param int $employeeId
* @param int $leaveTypeId
* @param int $year
* @return self|false The LeaveBalance object if found, otherwise false.
*/
public function findByEmployeeLeaveTypeYear(int $employeeId, int $leaveTypeId, int $year)
{
$query = "SELECT * FROM {$this->table_name}
WHERE employee_id = :employee_id
AND leave_type_id = :leave_type_id
AND year = :year
LIMIT 1";
$this->db->query($query);
$this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
$this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
$this->db->bind(':year', $year, PDO::PARAM_INT);
$row = $this->db->single();
if ($row) {
return $this->populateFromRow($row);
}
return false;
}
/**
* Get all leave balances for a specific employee for a given year.
* @param int $employeeId
* @param int $year
* @return array Array of LeaveBalance objects.
*/
public function getBalancesForEmployeeByYear(int $employeeId, int $year): array
{
$query = "SELECT lb.*, lt.name as leave_type_name, lt.alias as leave_type_alias
FROM {$this->table_name} lb
JOIN leave_types lt ON lb.leave_type_id = lt.id
WHERE lb.employee_id = :employee_id AND lb.year = :year
ORDER BY lt.name ASC";
$this->db->query($query);
$this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
$this->db->bind(':year', $year, PDO::PARAM_INT);
$results = $this->db->resultSet();
$balances = [];
foreach ($results as $row) {
$balance = $this->populateFromRow($row, true); // Pass true to populate related LeaveType
$balances[] = $balance;
}
return $balances;
}
/**
* Helper method to populate object properties from a database row.
* @param array $row Associative array from database fetch.
* @param bool $populateRelations Whether to try to populate related objects (LeaveType in this case)
* @return self
*/
private function populateFromRow(array $row, bool $populateRelations = false): self
{
$this->id = (int) $row['id'];
$this->employee_id = (int) $row['employee_id'];
$this->leave_type_id = (int) $row['leave_type_id'];
$this->balance_days = (float) $row['balance_days'];
$this->year = (int) $row['year'];
$this->last_updated_at = $row['last_updated_at'];
if ($populateRelations) {
if (isset($row['leave_type_name'])) {
$this->leaveType = new LeaveType($this->db);
$this->leaveType->id = $this->leave_type_id;
$this->leaveType->name = $row['leave_type_name'];
if (isset($row['leave_type_alias'])) {
$this->leaveType->alias = $row['leave_type_alias'];
}
}
// Could also populate Employee if needed and joined
}
return $this;
}
/**
* Deduct days from a specific leave balance.
* @param float $daysToDeduct Number of days to deduct.
* @return bool True on success, false on failure (e.g., insufficient balance).
*/
public function deductDays(float $daysToDeduct): bool
{
if ($this->id === null) {
return false;
}
// Must have a balance record loaded
if ($this->balance_days < $daysToDeduct) {
return false;
}
// Insufficient balance
$this->balance_days -= $daysToDeduct;
return $this->save(); // Use save to update the balance
}
/**
* Add days to a specific leave balance (e.g., correcting a deduction or cancelling a leave).
* @param float $daysToAdd Number of days to add.
* @return bool True on success, false on failure.
*/
public function addDays(float $daysToAdd): bool
{
if ($this->id === null) {
return false;
}
// Must have a balance record loaded
$this->balance_days += $daysToAdd;
return $this->save(); // Use save to update the balance
}
/**
* Delete a leave balance record.
* @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();
}
}