time attendance is incorrect if checkout time is more than 00:00

Question

I want to make an attendance report of employees.

If there any employees who scan checkout after 00.00, the data will go to the next date, I don't want it like that, because it will be difficult to report the number of hours worked for each employee.

so employees always take attendance at the fingerprint scanner every time they come or go home. Data from the fingerprint scan will enter to checkinout table.

checkinout table:

id	userid	checktime	checktype
1	34	2023-08-01 07:52:23	in
2	27	2023-08-01 10:57:18	in
3	34	2023-08-01 19:57:27	out
4	27	2023-08-02 00:12:06	out

After the data enters the checkinout, I use cron jobs to process the raw data from the checkinout table and insert it into the presence_record table.

presence_record_table:

id	id_employee	period_month	period_year	date	time_in	time_out
2362	34	8	2023	1	07:52:23	19:57:27
2363	27	8	2023	1	10:57:18	00:00:00
2364	27	8	2023	2	00:00:00	00:12:06

This is the script for my cron job:

$dataFingerprint = CheckInOut::all();

foreach ($dataFingerprint as $value) {
    $id_employee = $value->userid;
    $date = Carbon::parse($value->checktime)->day;
    $month = Carbon::parse($value->checktime)->month;
    $year = Carbon::parse($value->checktime)->year;


    $cekData = PresenceRecord::where('id_employee', $id_employee)
              ->where('date', $date)
              ->where('period_month', $month)
              ->where('period_year', $year)
              ->first();

    if($cekData){
        $result = $cekData;
    else {
        $result = new PresenceRecord();
    }

    $result->id_employee = $id_employee;
    $result->date = $date;
    $result->period_month = $month;
    $result->period_year = $year;
    if($value->checktype == "in"){
        $result->time_in = date("H:i:s",strtotime($value->checktime));
    } else if($value->checktype == "out") {
        $result->time_out = date("H:i:s",strtotime($value->checktime));
    }
    $result->save();
}

As you can see from the example table I gave, I want id_employee 27 the checkout time is fixed on August 1st and the time is 00:12 This makes it easier for me to make report employee working hours later.

Answer 1

From MySQL query perspective, you could do something like this:

/*defining a first check in time*/

SET @firstCheckIn := '07:00:00';

SELECT userid,
       CASE WHEN TIME(checktime) <= @firstCheckIn 
             AND checktype='Out' THEN DATE(checktime) - INTERVAL 1 DAY
        ELSE DATE(checktime) END AS checkindate,
       MAX(CASE WHEN checktype='In' THEN checktime END) AS time_in,
       MAX(CASE WHEN checktype='Out' THEN checktime END) AS time_out
  FROM checkinout
GROUP BY userid, checkindate;

First is to define an "acceptable" first check in time. In the example above, I use SET to assign @firstCheckIn variable with '07:00:00'. After that, you can use CASE expression to check these conditions:

• if checkintime is less than @firstCheckIn and checktype='Out', get the row's date and subtract 1 day from it DATE(checktime) - INTERVAL 1 DAY.
• if conditions above is not met, just get DATE(checktime).

As for your presence_record_table, you may not need to create the table at all but I can see the reason in having it. However, I would suggest that you retain the date time value as it is and do the separation of day, month, year in query only. The main reason is simply because with DATETIME data, you can easily use date/time related functions like TIMEDIFF() to get the duration between time_out and time_in.

Here's a fiddle with further tests: https://dbfiddle.uk/xQVxu61k

Note: I'm not familiar with ORM so I'm not sure how to translate this query into ORM.