public function run()
{
$faker = Faker\Factory::create();
// change this to auto create more leave balance records for each user
$user_count = 19;
// change this if you add or remove a leave type
$leavetype_count = 5;
foreach (range(1, $user_count) as $user_id) {
foreach (range(1, $leavetype_count) as $leavetype_id) {
if ($leavetype_id == 1) {
LeaveBalance::create(['user_id' => $user_id, 'leavetype_id' => $leavetype_id, 'balance' => $faker->randomElement(['1.25', '2.75', '5', '7.75', '10.25', '15'])]);
} elseif ($leavetype_id == 2) {
LeaveBalance::create(['user_id' => $user_id, 'leavetype_id' => $leavetype_id, 'balance' => $faker->randomElement(['1.25', '2.75', '5', '7.75', '10.25', '15'])]);
} else {
LeaveBalance::create(['user_id' => $user_id, 'leavetype_id' => $leavetype_id, 'balance' => 1.0]);
}
}
}
}
/**
* 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;
}
public function getLeavesListPdf($id)
{
$audit = AuditTrail::create(['user_id' => Auth::id(), 'role' => 'Employee Management Admin', 'action' => 'printed Leave balances of All employees.']);
if ($id == "*") {
$users = User::all();
} else {
$users = User::where('department_id', $id)->get();
}
Fpdf::AddPage();
Fpdf::Image('img/dap.jpg', 40, 0, 150);
Fpdf::Ln(35);
Fpdf::SetFont('Arial', 'B', 11);
Fpdf::SetTextColor(0, 0, 0);
Fpdf::Cell(300, 20, 'Date Generated: ' . date("F j, Y"), 0, 2, 'C', 0);
Fpdf::Cell(18, 7, 'Leave List', 0, 2, 'C', 0);
Fpdf::SetFont('Arial', 'B', 7);
Fpdf::SetTextColor(0, 0, 0);
Fpdf::Cell(18, 7, 'ID', 1);
Fpdf::Cell(24, 7, 'Name', 1);
Fpdf::Cell(30, 7, 'Department', 1);
Fpdf::Cell(9, 7, 'VL', 1);
Fpdf::Cell(9, 7, 'SL', 1);
Fpdf::Cell(9, 7, 'PL', 1);
Fpdf::Cell(9, 7, 'ML', 1);
Fpdf::Cell(9, 7, 'BL', 1);
Fpdf::Ln();
Fpdf::SetFont('Arial', '', 7);
foreach ($users as $user) {
Fpdf::Cell(18, 7, $user->id, 1);
Fpdf::Cell(24, 7, $user->firstname, 1);
Fpdf::Cell(30, 7, $user->department->name, 1);
$leaves = LeaveBalance::where('user_id', $user->id)->get();
foreach ($leaves as $leave) {
Fpdf::Cell(9, 7, $leave->balance, 1);
}
Fpdf::Ln();
}
Fpdf::SetY(-35);
Fpdf::AliasNbPages('{nb}');
Fpdf::SetFont('Arial', 'I', 10);
Fpdf::SetTextColor(0, 0, 0);
Fpdf::Cell(0, 10, 'Page ' . Fpdf::PageNo() . '/{nb}', 0, 0, 'C');
Fpdf::Output();
exit;
}
