$q = "SELECT * FROM users"; $sql = $conn->prepare($q); if ($sql->execute()) { $results = $sql->fetchAll(PDO::FETCH_ASSOC); for ($i = 0; $i < count($results); $i++) { $q = "SELECT * FROM approved WHERE fromMember=:uid"; $uid = $results[$i]["uid"]; $sql = $conn->prepare($q); $sql->BindParam(":uid", $uid); if ($sql->execute()) { $r = $sql->fetchAll(); $totalMinutes = 0; for ($j = 0; $j < count($r); $j++) { $totalMinutes += intval($r[$j]["hours"]); } $totalTime = toHours($totalMinutes); $results[$i]["totalTime"] = $totalTime; } } print_r(json_encode($results)); } function toHours($min) { $hours = floor($min / 60); $min = floor($min % 60); if ($min <= 9) { $min = "0" . $min; } $time = $hours . ":" . $min; return $time; }
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 }