Пример #1
0
$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;
}
Пример #2
0
 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
 }