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.

Not real code, but how about: `if($value->checkOutTime between ('00:00' and 'work start time next day')) { $this->addToPreviousDay($workLength);}` <<<=== if checkout time between midnight and (whenever everyone starts their next day), then add that time to the previous day. There should be a Carbon function for that — UnderDog, Sep 02 '23 at 04:54
I think that there are many ways to achieve this. You can use `checktype` to get the `in-out` pairing or you can define a time when it's considered as `in` like maybe any first checkin time beyond 7 a.m is considered as `in` regardless if there's any `out` for the previous `in` or not. — FanoFN, Sep 02 '23 at 04:59
I don't recommend storing your date in a broken down way like that. You can always use functions like `DAY(...)` , `MONTH(..)` and `YEAR(...)` to deconstruct the date if you need to. If you leverage this then you can simplify your code to look for the last inserted `presence_record_table` when you encounter an `out` record so you also have a factual data entry rather than one that implies that 00:12 belongs to the previous day — apokryfos, Sep 02 '23 at 04:59
What are the rules for handling anomalies? They will happen (power/network failure, employee leaving on stretcher/gurney, etc)! Multiple `in`s and/or `out`s in a row. Can employees check in/out multiple times per day? How frequently are you planning to run your cron (hourly, daily, weekly, monthly)? How many employees are you tracking (10s, 100s, 1000s)? Your current script gets all `checkinout` rows (`CheckInOut::all()`) on each run. This process can be implemented more efficiently in SQL than Laravel (less toing and froing between MySQL and PHP). [Consider this](https://dbfiddle.uk/DL5AuY0c) — user1191247, Sep 02 '23 at 13:24

score 0 · Answer 1 · answered Sep 02 '23 at 07:04

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.

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

1 Answers1