protected function writePersonalDataSheet()
{
$path = storage_path('app/templates/PersonalDataSheet.xlsx');
if (!null === $this->employee->file && file_exists('files/PersonalDataSheets/' . $this->employee->file)) {
unlink('files/PersonalDataSheets/' . $this->employee->file);
}
$pds_file = Excel::load($path, function ($reader) {
$reader->file = studly_case($this->employee->fullName()) . '.xlsx';
$this->employee->file = $reader->file;
$this->employee->save();
$sheet = $reader->sheet('C1');
$sheet->protect('password');
//Personal Information
$sheet->setCellValue('C7', strtoupper($this->employee->surname));
$sheet->setCellValue('C8', strtoupper($this->employee->first_name));
$sheet->setCellValue('C9', strtoupper($this->employee->middle_name));
$sheet->setCellValue('N9', strtoupper($this->employee->name_extension));
$sheet->setCellValue('E10', date('M d Y', strtotime($this->employee->birthday)));
$sheet->setCellValue('C11', strtoupper($this->employee->birthplace));
$sheet->setCellValue('D13', strtoupper($this->employee->civil_status));
$sheet->setCellValue('C16', strtoupper($this->employee->citizenship));
$sheet->setCellValue('C17', strtoupper($this->employee->height));
$sheet->setCellValue('C18', strtoupper($this->employee->weight));
$sheet->setCellValue('C19', strtoupper($this->employee->blood_type));
$sheet->setCellValue('C20', $this->employee->gsis_id);
$sheet->setCellValue('C21', $this->employee->pagibig_id);
$sheet->setCellValue('C22', $this->employee->philhealth_id);
$sheet->setCellValue('C23', $this->employee->sss_id);
$sheet->setCellValue('I10', strtoupper($this->employee->employee_address()->residential()->address));
$sheet->setCellValue('I13', $this->employee->employee_address()->residential()->zip_code);
$sheet->setCellValue('I14', $this->employee->employee_address()->residential()->telephone_number);
$sheet->setCellValue('I15', strtoupper($this->employee->employee_address()->permanent()->address));
$sheet->setCellValue('I18', $this->employee->employee_address()->permanent()->zip_code);
$sheet->setCellValue('I19', $this->employee->employee_address()->permanent()->telephone_number);
$sheet->setCellValue('I20', $this->employee->user->email);
$sheet->setCellValue('I21', $this->employee->cellphone_number);
$sheet->setCellValue('I22', $this->employee->agency_employee_number);
$sheet->setCellValue('I23', $this->employee->tin);
//Family Background
if ($this->employee->employee_spouse) {
$sheet->setCellValue('C25', strtoupper($this->employee->employee_spouse->last_name));
$sheet->setCellValue('C26', strtoupper($this->employee->employee_spouse->first_name));
$sheet->setCellValue('C27', strtoupper($this->employee->employee_spouse->middle_name));
$sheet->setCellValue('C28', strtoupper($this->employee->employee_spouse->occupation));
$sheet->setCellValue('C29', strtoupper($this->employee->employee_spouse->employer_business_name));
$sheet->setCellValue('C30', strtoupper($this->employee->employee_spouse->business_address));
$sheet->setCellValue('C31', $this->employee->employee_spouse->telephone_number);
}
if ($this->employee->employee_child) {
$row_num = 26;
foreach ($this->employee->employee_child as $child_name) {
$sheet->setCellValue('H' . $row_num, strtoupper($child_name->name));
$sheet->setCellValue('M' . $row_num, strtoupper(date('m/d/Y', strtotime($child_name->birthday))));
++$row_num;
}
}
$sheet->setCellValue('D33', strtoupper($this->employee->employee_parents()->father()->last_name));
$sheet->setCellValue('D34', strtoupper($this->employee->employee_parents()->father()->first_name));
$sheet->setCellValue('D35', strtoupper($this->employee->employee_parents()->father()->middle_name));
$sheet->setCellValue('D37', strtoupper($this->employee->employee_parents()->mother()->last_name));
$sheet->setCellValue('D38', strtoupper($this->employee->employee_parents()->mother()->first_name));
$sheet->setCellValue('D39', strtoupper($this->employee->employee_parents()->mother()->middle_name));
$employeeEducations = $this->employee->employee_educations;
if ($employeeEducations) {
$educations = collect();
$educations->push($this->employee->employee_educations()->elementary()->first());
$educations->push($this->employee->employee_educations()->secondary()->first());
$educations->push($this->employee->employee_educations()->vocational()->first());
foreach ($this->employee->employee_educations()->college()->get() as $college) {
$educations->push($college);
}
foreach ($this->employee->employee_educations()->graduateStudies()->get() as $college) {
$educations->push($college);
}
$this->writeEducation($sheet, $educations);
}
$sheet = $reader->sheet('C2');
$sheet->protect('password');
if ($this->employee->employee_civil_services) {
$row = 5;
foreach ($this->employee->employee_civil_services->take(5) as $civil_service) {
$sheet->setCellValue('A' . $row, strtoupper($civil_service->career_service));
$sheet->setCellValue('F' . $row, strtoupper($civil_service->rating));
$sheet->setCellValue('G' . $row, date('M d Y', strtotime($civil_service->examination_date)));
$sheet->setCellValue('I' . $row, strtoupper($civil_service->examination_place));
$sheet->setCellValue('L' . $row, $civil_service->license_number);
$sheet->setCellValue('M' . $row, date('M d Y', strtotime($civil_service->release_date)));
++$row;
}
}
if ($this->employee->employee_work_experiences) {
$row = 17;
foreach ($this->employee->employee_work_experiences->take(20) as $work_experience) {
$sheet->setCellValue('A' . $row, date('m/d/Y', strtotime($work_experience->date_from)));
$sheet->setCellValue('C' . $row, date('m/d/Y', strtotime($work_experience->date_to)));
$sheet->setCellValue('D' . $row, strtoupper($work_experience->position_title));
$sheet->setCellValue('G' . $row, strtoupper($work_experience->company));
$sheet->setCellValue('J' . $row, strtoupper($work_experience->monthly_salary));
$sheet->setCellValue('K' . $row, strtoupper($work_experience->salary_grade));
$sheet->setCellValue('L' . $row, strtoupper($work_experience->status_of_appointment));
$sheet->setCellValue('M' . $row, strtoupper($work_experience->government_service));
++$row;
}
}
$sheet = $reader->sheet('C3');
$sheet->protect('password');
if ($this->employee->employee_voluntary_work) {
$row = 6;
foreach ($this->employee->employee_voluntary_work->take(5) as $voluntary_work) {
$sheet->setCellValue('A' . $row, $voluntary_work->organization_name . '/ ' . $voluntary_work->organization_address);
$sheet->setCellValue('E' . $row, date('m/d/Y', strtotime($voluntary_work->date_from)));
$sheet->setCellValue('F' . $row, date('m/d/Y', strtotime($voluntary_work->date_to)));
$sheet->setCellValue('G' . $row, $voluntary_work->number_of_hours);
$sheet->setCellValue('H' . $row, $voluntary_work->position);
++$row;
}
}
if ($this->employee->trainings) {
$row = 16;
$trainings = $this->employee->trainings()->finishedTrainings()->includedInPds()->get()->sortByDesc('start')->take(15);
foreach ($trainings as $training) {
$sheet->setCellValue('A' . $row, strtoupper($training->title));
$sheet->setCellValue('E' . $row, strtoupper(date('m/d/Y', strtotime($training->start))));
$sheet->setCellValue('F' . $row, strtoupper(date('m/d/Y', strtotime($training->end))));
$sheet->setCellValue('G' . $row, strtoupper($training->number_of_hours));
$sheet->setCellValue('H' . $row, strtoupper($training->sponsored_by));
++$row;
}
}
if ($this->employee->employee_skills) {
$row = 34;
foreach ($this->employee->employee_skills->take(5) as $skill) {
$sheet->setCellValue('A' . $row, strtoupper($skill->skill));
++$row;
}
}
if ($this->employee->employee_non_academics) {
$row = 34;
foreach ($this->employee->employee_non_academics->take(5) as $non_academic) {
$sheet->setCellValue('C' . $row, strtoupper($non_academic->name));
++$row;
}
}
if ($this->employee->employee_organizations) {
$row = 34;
foreach ($this->employee->employee_organizations->take(5) as $organization) {
$sheet->setCellValue('H' . $row, strtoupper($organization->name));
++$row;
}
}
$sheet = $reader->sheet('C4');
$sheet->protect('password');
$sheet->setCellValue('D54', date('M d, Y', strtotime($this->employee->created_at)));
excel_attach_image(['path' => public_path('img/signature.png'), 'coordinates' => 'D48', 'worksheet' => $reader->sheet('C4')]);
if (!$this->employee->noPhoto()) {
excel_attach_image(['path' => public_path('uploads/' . $this->employee->photo), 'coordinates' => 'G40', 'worksheet' => $reader->sheet('C4'), 'width' => 160]);
}
});
$pds_file->store('xlsx', 'files/PersonalDataSheets', true);
// If error occurs, refer to Writer\Excel2007.php line 235 or later
}
public function attachSignature($reader)
{
if ($this->leave->isStatus('certified')) {
if ($this->leave->recommending_approval && $this->leave->recommending_approval->user->signature_path) {
excel_attach_image(['name' => $this->leave->recommending_approval->full_name, 'path' => $this->leave->recommending_approval->user->signature_path, 'coordinates' => 'J25', 'worksheet' => $reader->sheet('Sheet1')]);
}
if ($this->leave->approved_by && $this->leave->approved_by->user->signature_path) {
excel_attach_image(['name' => $this->leave->approved_by->full_name, 'path' => $this->leave->approved_by->user->signature_path, 'coordinates' => 'J33', 'worksheet' => $reader->sheet('Sheet1')]);
}
if ($this->leave->certified_by && $this->leave->certified_by->user->signature_path) {
excel_attach_image(['name' => $this->leave->certified_by->full_name, 'path' => $this->leave->certified_by->user->signature_path, 'coordinates' => 'A33', 'worksheet' => $reader->sheet('Sheet1')]);
}
return true;
}
return false;
}
protected function writeForm($leave)
{
$employee = $leave->employee;
$not_found = false;
$path = 'files/SpecialLeaveForms/' . $leave->file;
if (!file_exists($path) || null === $leave->file) {
$path = storage_path('app/templates/SpecialLeaveForm.xlsx');
$not_found = true;
}
$leave_form = Excel::load($path, function ($reader) use($leave, $employee, $not_found) {
$sheet = $reader->sheet('Sheet1');
$sheet->protect('password');
$sheet->setCellValue('A6', $employee->first_name);
$sheet->setCellValue('E6', $employee->middle_name);
$sheet->setCellValue('I6', $employee->surname);
$sheet->setCellValue('A8', $employee->user->department->name);
// Staff / Division
$sheet->setCellValue('E8', $employee->user->positions->first()->name);
// Position
if (!$leave->file_path) {
$reader->file = $leave->fileName() . '.xlsx';
$leave->file = $reader->file;
$leave->save();
$sheet->setCellValue('I8', date('M d Y', strtotime($leave->created_at)));
// Type of leave
$sheet->setCellValue('B13', $leave->type == 'personal milestone' ? utf8_encode('P') : null);
$sheet->setCellValue('F13', $leave->type == 'filial obligation' ? utf8_encode('P') : null);
$sheet->setCellValue('J13', $leave->type == 'personal transaction' ? utf8_encode('P') : null);
$sheet->setCellValue('B17', $leave->type == 'parental obligations' ? utf8_encode('P') : null);
$sheet->setCellValue('F17', $leave->type == 'domestic emergencies' ? utf8_encode('P') : null);
$sheet->setCellValue('J17', $leave->type == 'calamity' ? utf8_encode('P') : null);
$sheet->setCellValue('A21', $leave->number_of_days);
$sheet->setCellValue('E21', date('M d Y', strtotime($leave->date_from)) . ' - ' . date('M d Y', strtotime($leave->date_to)));
// Action on application
$sheet->setCellValue('A24', $employee->leave_credit->special_leave == 3 ? utf8_encode('P') : null);
$sheet->setCellValue('A25', $employee->leave_credit->special_leave == 2 ? utf8_encode('P') : null);
$sheet->setCellValue('A26', $employee->leave_credit->special_leave == 1 ? utf8_encode('P') : null);
// Recommending Approval
if ($leave->recommending_approval) {
$sheet->setCellValue('I28', strtoupper($leave->recommending_approval->full_name));
$sheet->setCellValue('I31', $leave->recommending_approval->user->position_title);
}
// Approved by
$sheet->setCellValue('A37', strtoupper($leave->approved_by->full_name));
// Certified by
$sheet->setCellValue('F28', strtoupper($leave->certified_by->full_name));
} else {
switch ($leave->status) {
case 'disapproved':
case 'rejected':
$sheet->setCellValue('I34', $leave->remarks);
}
if ($leave->isStatus('certified')) {
excel_attach_image(['name' => $leave->employee->full_name, 'path' => public_path('img/signature.png'), 'coordinates' => 'I21', 'worksheet' => $reader->sheet('Sheet1')]);
if ($leave->recommending_approval) {
excel_attach_image(['name' => $leave->recommending_approval->full_name, 'path' => public_path('img/signature.png'), 'coordinates' => 'I25', 'worksheet' => $reader->sheet('Sheet1')]);
}
if ($leave->approved_by) {
excel_attach_image(['name' => $leave->approved_by->full_name, 'path' => public_path('img/signature.png'), 'coordinates' => 'E35', 'worksheet' => $reader->sheet('Sheet1')]);
}
if ($leave->certified_by) {
excel_attach_image(['name' => $leave->certified_by->full_name, 'path' => public_path('img/signature.png'), 'coordinates' => 'F25', 'worksheet' => $reader->sheet('Sheet1')]);
}
}
}
});
return $leave_form->store('xlsx', 'files/SpecialLeaveForms', true);
}
protected function writeRegularLeaveForm()
{
$leave = $this->leave;
$employee = $this->leave->employee;
$path = $leave->file_path;
if (!$path) {
$path = storage_path('app/templates/LeaveRequestForm.xlsx');
}
$form = Excel::load($path, function ($reader) use($employee, $path) {
$sheet = $reader->sheet('Sheet1');
$sheet->protect('password');
if (!$this->leave->file_path) {
$reader->file = $this->leave->fileName() . '.xlsx';
$this->leave->file = $reader->file;
$this->leave->save();
$sheet->setCellValue('D5', $employee->fullName());
$sheet->setCellValue('D7', $employee->user->positions->first()->name);
$sheet->setCellValue('D11', $employee->user->department->name);
$sheet->setCellValue('D13', date('M d, Y', strtotime($this->leave->created_at)));
$sheet->setCellValue('E15', $this->leave->working_days_applied . ' days');
$sheet->setCellValue('D17', date('M d, Y', strtotime($this->leave->start_date)) . ' - ' . date('M d, Y', strtotime($this->leave->end_date)));
//Commutation
$sheet->setCellValue('B21', $this->leave->commutation == 'requested' ? utf8_encode('P') : null);
$sheet->setCellValue('E21', $this->leave->commutation == 'not requested' ? utf8_encode('P') : null);
//Type of leave
$sheet->setCellValue('L3', $this->leave->leave_type == 'vacation' ? utf8_encode('P') : null);
$sheet->setCellValue('L7', $this->leave->leave_type == 'sick' ? utf8_encode('P') : null);
$sheet->setCellValue('L11', $this->leave->leave_type == 'maternity' ? utf8_encode('P') : null);
//Additional info
$sheet->setCellValue('O3', $this->leave->additional_info == 'within the philippines' ? utf8_encode('P') : null);
$sheet->setCellValue('O5', $this->leave->additional_info == 'abroad' ? utf8_encode('P') : null);
$sheet->setCellValue('O7', $this->leave->additional_info == 'out patient' ? utf8_encode('P') : null);
$sheet->setCellValue('O9', $this->leave->additional_info == 'in hospital' ? utf8_encode('P') : null);
$sheet->setCellValue('J27', strtoupper($this->leave->recommending_approval ? $this->leave->recommending_approval->full_name : null));
$sheet->setCellValue('J35', strtoupper($this->leave->approved_by ? $this->leave->approved_by->full_name : null));
$sheet->setCellValue('A35', strtoupper($this->leave->certified_by ? $this->leave->certified_by->full_name : null));
} else {
//Action on application
switch ($this->leave->status) {
case 'recommended':
$sheet->setCellValue('L21', utf8_encode('P'));
excel_attach_image(['path' => public_path('img/signature.png'), 'coordinates' => 'J25', 'worksheet' => $reader->sheet('Sheet1')]);
break;
case 'unrecommended':
$sheet->setCellValue('L23', utf8_encode('P'));
// $sheet->setCellValue('J25', $this->leave->remarks);
break;
case 'approved':
$sheet->setCellValue('L29', utf8_encode('P'));
excel_attach_image(['path' => public_path('img/signature.png'), 'coordinates' => 'J33', 'worksheet' => $reader->sheet('Sheet1')]);
break;
case 'disapproved':
$sheet->setCellValue('Q29', utf8_encode('P'));
// $sheet->setCellValue('J25', $this->leave->remarks);
break;
case 'certified':
excel_attach_image(['path' => public_path('img/signature.png'), 'coordinates' => 'A33', 'worksheet' => $reader->sheet('Sheet1')]);
break;
}
}
// Leave Credits
$sheet->setCellValue('A26', date('M d, Y', strtotime(Carbon::now())));
$sheet->setCellValue('D26', $employee->leave_credit->vacation_leave);
$sheet->setCellValue('E26', $employee->leave_credit->sick_leave);
$sheet->setCellValue('G26', $employee->leave_credit->totalLeaveCredit());
// Less
if ($this->leave->isLeaveType('vacation')) {
$sheet->setCellValue('D27', $this->leave->working_days_applied);
$sheet->setCellValue('D28', $employee->leave_credit->vacation_leave - (double) $this->leave->working_days_applied);
} else {
$sheet->setCellValue('E27', $this->leave->working_days_applied);
$sheet->setCellValue('E28', $employee->leave_credit->sick_leave - (double) $this->leave->working_days_applied);
}
$sheet->setCellValue('G27', $this->leave->working_days_applied);
$sheet->setCellValue('G28', $employee->leave_credit->totalLeaveCredit() - $this->leave->working_days_applied);
});
return $form->store('xlsx', public_path('files/RegularLeaveForms'), true);
}
