/**
* Get leave balance as a LeaveBalance object with the following components
* * entitlements
* * used (taken)
* * scheduled
* * pending approval
* * leave without entitlements
*
* @param int $empNumber Employee Number
* @param int $leaveTypeId Leave Type ID
* @param date $asAtDate Balance as at given date
* @return LeaveBalance Returns leave balance object
*/
public function getLeaveBalance($empNumber, $leaveTypeId, $asAtDate, $date = NULL)
{
$conn = Doctrine_Manager::connection()->getDbh();
$pendingIds = $this->getPendingStatusIds();
$pendingIdList = is_array($pendingIds) ? implode(',', $pendingIds) : $pendingIds;
$sql = 'SELECT le.no_of_days AS entitled, ' . 'le.days_used AS used, ' . 'sum(IF(l.status = 2, lle.length_days, 0)) AS scheduled, ' . 'sum(IF(l.status IN (' . $pendingIdList . '), lle.length_days, 0)) AS pending, ' . 'sum(IF(l.status = 3, l.length_days, 0)) AS taken, 0 as notLinked ' . 'FROM ohrm_leave_entitlement le LEFT JOIN ' . 'ohrm_leave_leave_entitlement lle ON le.id = lle.entitlement_id LEFT JOIN ' . 'ohrm_leave l ON l.id = lle.leave_id ' . 'WHERE le.deleted = 0 AND le.emp_number = ? AND le.leave_type_id = ? ' . ' AND le.to_date >= ?';
$parameters = array($empNumber, $leaveTypeId, $asAtDate);
if (!empty($date)) {
$sql .= ' AND ? BETWEEN le.from_date AND le.to_date ';
$parameters[] = $date;
}
$sql .= ' GROUP BY le.id';
$dateLimits = $this->getLeaveEntitlementStrategy()->getLeaveWithoutEntitlementDateLimitsForLeaveBalance($asAtDate, $date);
if (is_array($dateLimits) && count($dateLimits) > 0) {
$sql .= ' UNION ALL ' . 'SELECT ' . '0 AS entitled, ' . 'SUM(l.length_days) AS used, ' . 'sum(IF(l.status = 2, l.length_days, 0)) AS scheduled, ' . 'sum(IF(l.status IN (' . $pendingIdList . '), l.length_days, 0)) AS pending, ' . 'sum(IF(l.status = 3, l.length_days, 0)) AS taken, ' . 'sum(l.length_days) AS notLinked ' . 'FROM ohrm_leave l ' . 'LEFT JOIN ohrm_leave_leave_entitlement lle ON (lle.leave_id = l.id) ' . 'WHERE (lle.leave_id IS NULL) AND l.emp_number = ? AND l.leave_type_id = ? AND l.status NOT IN (-1, 0) ' . ' AND l.date BETWEEN ? AND ? ';
$parameters[] = $empNumber;
$parameters[] = $leaveTypeId;
$parameters[] = $dateLimits[0];
$parameters[] = $dateLimits[1];
//$parameters[] = $asAtDate;
$sql .= 'GROUP BY l.leave_type_id';
}
$sql = 'SELECT sum(a.entitled) as entitled, sum(a.used) as used, sum(a.scheduled) as scheduled, ' . 'sum(a.pending) as pending, sum(a.taken) as taken, sum(a.notLinked) as notLinked ' . ' FROM (' . $sql . ') as a';
$statement = $conn->prepare($sql);
$result = $statement->execute($parameters);
$adjustmentSql = " SELECT sum(no_of_days) adjustment FROM ohrm_leave_adjustment la" . " WHERE la.emp_number = ? AND la.leave_type_id= ? AND ? BETWEEN la.from_date AND la.to_date";
$adjustmentParams = array($empNumber, $leaveTypeId, $asAtDate);
$statementAdjustment = $conn->prepare($adjustmentSql);
$resultAdjustment = $statementAdjustment->execute($adjustmentParams);
$balance = new LeaveBalance();
if ($result) {
if ($statement->rowCount() > 0) {
$result = $statement->fetch(PDO::FETCH_ASSOC);
if (!empty($result['entitled'])) {
$balance->setEntitled($result['entitled']);
}
if (!empty($result['used'])) {
$balance->setUsed($result['used']);
}
if (!empty($result['scheduled'])) {
$balance->setScheduled($result['scheduled']);
}
if (!empty($result['pending'])) {
$balance->setPending($result['pending']);
}
if (!empty($result['taken'])) {
$balance->setTaken($result['taken']);
}
if (!empty($result['notLinked'])) {
$balance->setNotLinked($result['notLinked']);
}
}
}
if ($resultAdjustment) {
$resultAdjustment = $statementAdjustment->fetch(PDO::FETCH_ASSOC);
if (!empty($resultAdjustment['adjustment'])) {
$balance->setAdjustment($resultAdjustment['adjustment']);
}
}
$balance->updateBalance();
return $balance;
}
