public function exportToExcel() { // $path = storage_path('app/DTRTemplates/DTRSummary.xlsx'); //Path of the excel template to be loaded $path = storage_path('app/DTRTemplates/DTRSummary.xlsx'); Excel::load($path, function ($reader) { //load the excel file $raw_sheet = $reader->sheet('raw'); //select the raw sheet of the excel file $summary_sheet = $reader->sheet('summary'); //select the summary sheet of the excel file $rawSheetIndex = 1; $summarySheetIndex = 3; $employees = Employee::has('employee_dtrs')->with('employee_dtrs', 'shifts')->orderBy('last_name')->get(); foreach ($employees as $employee) { //get the employee logs within the provided days $rawSheetIndex = $rawSheetIndex + 2; $staffcode = $employee->employee_id; $staffname = strtoupper($employee->last_name) . ', ' . $employee->first_name; $computations = value(function () use($employee) { $late = new DateTime('00:00:00'); $undertime = new DateTime('00:00:00'); $overbreak = new DateTime('00:00:00'); $hrs_worked = new DateTime('00:00:00'); foreach ($employee->employee_dtrs as $dtr) { $late->add(computeTimeInterval($dtr->late, '00:00:00')); $undertime->add(computeTimeInterval($dtr->undertime, '00:00:00')); $overbreak->add(computeTimeInterval($dtr->overbreak, '00:00:00')); $hrs_worked->add(computeTimeInterval($dtr->end_of_duty, $dtr->start_of_duty)); } //return an array with employee late, undertime, overbreak and hrs_worked value return ['late' => toMinutes(date_diff($late, new DateTime('00:00:00'))), 'undertime' => toMinutes(date_diff($undertime, new DateTime('00:00:00'))), 'overbreak' => toMinutes(date_diff($overbreak, new DateTime('00:00:00'))), 'hrs_worked' => toHours(date_diff($hrs_worked, new DateTime('00:00:00')))]; }); //add a new row and put all the gathered datas $row = $summary_sheet->appendRow($summarySheetIndex, [$staffcode, $staffname, $computations['late'], $computations['undertime'], $computations['overbreak'], '', $computations['hrs_worked']]); ++$summarySheetIndex; foreach ($employee->employee_dtrs as $employee_dtr) { // dd($employee_dtr); //assign the data to the variables $date = date('Y-m-d', strtotime($employee_dtr->start_of_duty)); $login = date('H:i:s', strtotime($employee_dtr->start_of_duty)); $logout = date('H:i:s', strtotime($employee_dtr->end_of_duty)); $late = $employee_dtr->late; $undertime = $employee_dtr->undertime; $lateToMinutes = stringToMinutes($late); $undertimeToMinutes = stringToMinutes($undertime); $shift_from = $employee_dtr->shift->shift_from; $shift_to = $employee_dtr->shift->shift_to; if ($lateToMinutes > 200 && $lateToMinutes < 240) { $lateToMinutes = 240; } if ($undertimeToMinutes > 200 && $undertimeToMinutes < 240) { $undertimeToMinutes = 240; } //add a new row and put all the gathered datas if ($employee_dtr->remarks == 'ABSENT') { $login = '******'; $logout = 'ABSENT'; $lateToMinutes = 480; } $row = $raw_sheet->appendRow($rawSheetIndex, [$staffname, $date, $login, $logout, $shift_from, $shift_to, $late, $lateToMinutes, $undertime, $undertimeToMinutes, null, $employee_dtr->remarks]); $staffname = null; ++$rawSheetIndex; //increment the index to know what row are we } } })->download('xlsx'); //download the excel file }
public function getEmployees() { return response()->json(Employee::has('company')->get()); }