Ndtyjky

I have a problem about creating a record for attendance of an employee using DATE() and TIME() with NOW() in laravel. I have the table Attendance with column to store the record into database.

        $table->increments('id');

        $table->integer('user_id')->unsigned();

        $table->date('attendance_date')->nullable();

        $table->string('scan_in_location')->nullable();

        $table->string('scan_out_location')->nullable();

        $table->time('scan_in_time')->nullable();

        $table->time('scan_out_time')->nullable();

        $table->time('work_hour')->nullable();

        $table->time('work_hour_total')->nullable();

        $table->time('lunch_break')->default('01:00:00');

        $table->integer('scan_in_status')->nullable();

        $table->integer('scan_out_status')->nullable();

        $table->timestamps();

When the employee press CHECK IN on mobile, the record to will stored in column (user_id, scan_in_time, scan_in_location, scan_in_status) but when i only do

$attendance = new Attendance();



     if ($this->user->attendance()->save($attendance))

        return response()->json([

            'success' => true,

            'attendance' => $attendance

        ]);

    else

        return response()->json([

            'success' => false,

            'message' => 'Sorry, check in attendance could not be added'

        ], 500);

This code runs smooth with the account based on user_id. But when i press Create, i want to stored (user_id, scan_in_location, scan_in_time, scan_in_status) and this code return MariaDB version

//declare variable for taking current date - time

        $user_id = request('user_id');



        //check attendance using user_id

        $res = Attendance::select('id')

        ->where('user_id' , '=' , $user_id)

        ->where('attendance_date', '=' , DB::RAW('DATE(NOW())'))

        ->where('user_id' , '!=' , null)

        ->where('scan_in_time', '!=' , null)

        ->where('scan_out_time', '=', null)->get()->count();



        //set status 0 - Absent, 1 - On time, 2 - Late, 3 - very late, 4 - On leave

      $attendance_status = Attendance::where('user_id', '=' , $user_id)

      ->select(DB::RAW("CASE WHEN TIME(NOW()) >= '08:30:00' AND TIME(NOW()) < '09:00:00' THEN '2' WHEN TIME(NOW()) > '09:00:00' THEN '3' ELSE '1'))

      ->value('scan_in_status');



      if($res > 0){

         $res = DB::RAW("UPDATE attendances SET scan_out_time = NOW(),scan_out_location = ? ,work_hour = TIMESTAMPDIFF(MINUTE, scan_in_time, scan_out_time,lunch_break), work_hour_total = TIMESTAMPDIFF(MINUTE, scan_in_time, scan_out_time) WHERE `user_id` =  ? AND NOT scan_in_time IS NULL AND scan_out_time IS NULL", [$user_id, $user_id, $scan_out_location]);



         return response()->json(

            array(

               'success' => true,

               'user_id' => $user_id,

               'action' => 'SCAN_OUT',

               'scan_out_location' => $scan_out_location,

               'date' => date('Y/m/d'),

               'time' => date('H:i:s')

           )

        );

     }else{

         $res = DB::RAW("UPDATE attendances SET scan_in_time = NOW(), scan_in_location = ?, scan_in_status = ?

             WHERE attendance_date = DATE(NOW()) AND `user_id` = ?", [$scan_in_location, $scan_in_status, $user_id]);



         return response()->json(

           array(

              'success' => true,

              'user_id' => $user_id,

              'action' => 'SCAN_IN',

              'scan_in_location' => $scan_in_location,

              'date' => date('Y/m/d'),

              'time' => date('H:i:s')

          )

       );

     }

Can anyone give me some advises on this check in attendance using real time DATE(NOW()) and TIME(NOW)). Thank you.