[FIXED] model and controller query to merge timein and timeout with single row in codeigniter

Issue

Please teach me how can I achieve this(Codeigniter framework) Please see images that I want to achieve thank you. and I'm also new to codeigniter and want to understand the answer.

enter image description here

Here is my database: enter image description here

model public function get_attendance_employees() {

    $sql = 'SELECT * FROM tbl_employee';
    //$binds = array(1);
    $query = $this->db->query($sql);
    
    return $query;
}
public function attendance_first_in_check($userid,$attendance_date) {

    $sql = "SELECT * FROM tbl_employee_time_log as time_log WHERE userid = ? and substring(time_log.time,1,10) = ? and type = 'timein' limit 1";
    $binds = array($userid,$attendance_date);
    $query = $this->db->query($sql,$binds);

    return $query;
}
public function attendance_first_in($userid,$attendance_date) {

    $sql = "SELECT * FROM tbl_employee_time_log as time_log WHERE userid = ? and substring(time_log.time,1,10) = ? and type = 'timein'";
    $binds = array($userid,$attendance_date);
    $query = $this->db->query($sql, $binds);
    
    return $query->result();
}
public function attendance_first_out_check($userid,$attendance_date) {

    $sql = "SELECT * FROM tbl_employee_time_log as time_log WHERE userid = ? and substring(time_log.time,1,10) = ? and type = 'timeout' order by id desc limit 1";
    $binds = array($userid,$attendance_date);
    $query = $this->db->query($sql, $binds);
    
    return $query;
}
public function attendance_first_out($userid,$attendance_date) {

    $sql = "SELECT * FROM tbl_employee_time_log as time_log WHERE userid = ? and substring(time_log.time,1,10) = ? and type = 'timeout' order by id desc limit 1";
    $binds = array($userid,$attendance_date);
    $query = $this->db->query($sql, $binds);
    
    return $query->result();
}

controller

public function attendance_bio() {

    $session = $this->session->userdata('username');
    if(!empty($session)){ 
        $this->load->view("admin/timesheet/attendance_bio", $data);
    } else {
        redirect('admin/');
    }
    // Datatables Variables
    $draw = intval($this->input->get("draw"));
    $start = intval($this->input->get("start"));
    $length = intval($this->input->get("length"));
    
    $attendance_date2 = $this->input->get("attendance_date"); //string
    //$ref_location_id = $this->input->get("location_id");
    //$convert_atten = strtotime($attendance_date2); //int value
    //$attendance_date = $convert_atten->format('m-d-Y');
    $attendance_date = date("m-d-Y",strtotime($attendance_date2));
    //$timein = $this->Bio_model->get_attendance_date();
    //$timein = $this->Bio_model->get_attendance_timein($get_day);
    //$timein = $this->Bio_model->get_all();
    $employee = $this->Bio_model->get_attendance_employees();
    
    $data = array();
    //var_dump($attendance_date);
    foreach($employee->result() as $r){
            $check = $this->Bio_model->attendance_first_in_check($r->userid,$attendance_date);
            if($check->num_rows() > 0){
                    // check clock in time
                    $first_in = $this->Bio_model->attendance_first_in($r->userid,$attendance_date);
                    //$first_in2 = $first_in[0]->time->format('m-d-Y');
                    // clock in
                    $clock_in = new DateTime(strtotime($first_in[0]->time));
                    //$clock_in2 = $clock_in->format('H:i');
                    $clock_in2 = date_format($clock_in->time, 'H:i');
                    //$clock_in2 = var_dump($clock_in); 
                } else {
                    $clock_in2 = '-';
                    $clock_in = '-';
            }
            $check_out = $this->Bio_model->attendance_first_out_check($r->userid,$attendance_date);     
                if($check_out->num_rows() == 1){
                    
                    $first_out = $this->Bio_model->attendance_first_out($r->userid,$attendance_date);
                    //$first_out2 = $firs
                    $clock_out = new DateTime(strtotime($first_out[0]->time));
                    
                                if ($first_out[0]->time!='') {
                                    //$clock_out2 = $clock_out->format('H:i');
                                    //$clock_out2 = date('H:i',$clock_out);
                                    $clock_out2 = date_format($clock_out->time, 'H:i');
                                    } else {
                                        $clock_out2 =  '-';
                                    }
                }else {
                    $clock_out2 =  '-';
                    $clock_out = '-';
                }
                $data[] = array(
            $r->userid,
            $attendance_date,
            $clock_in2,
            $clock_out2,
        
        );
            
    }
  $output = array(
       "draw" => $draw,
         "recordsTotal" => $employee->num_rows(),
         "recordsFiltered" => $employee->num_rows(),
         "data" => $data
    );
  echo json_encode($output);
  exit();
 }

Solution

Thank you for providing more information on the topic and the code you have attempted so far. I have supplied an algorithm which I believe will work for you.

Contoller:

<?php namespace App\Controllers;

use App\Models\EmployeeTimeLog;

class Home extends BaseController
{
    public function index($employeeId)
    {
        // get all between dates
        $emplyeeModel = new EmployeeTimeLog();

        // get one week before today - for example
        $startDate = date("Y-m-d", strtotime("-1 week"));
        $endDate = date("Y-m-d");

        // get clock-in and clock-out for employee between dates
        $timeIn = $emplyeeModel->getTimeIn($employeeId, $startDate, $endDate)->getResult();
        $timeOut = $emplyeeModel->getTimeOut($employeeId, $startDate, $endDate)->getResult();

        $timeInIndex = 0;
        $timeOutIndex = 0;
        $output = [];
        $nextDate = $startDate;
        // loop through all the days between start and end date
        while($nextDate < $endDate){
            // get the lowest "timein" datetime which is the same date as the search date $nextDate
            $nextTimeIn = NULL;
            while($timeInIndex < count($timeIn) && date("Y-m-d", strtotime($timeIn[$timeInIndex]->Time)) == $nextDate){
                if(is_null($nextTimeIn)){
                    $nextTimeIn = $timeIn[$timeInIndex];
                }
                $timeInIndex ++;
            }

            // get the highest "timeout" datetime which is the same day as the search date $nextDate
            $nextTimeOut = NULL;
            while($timeOutIndex < count($timeOut) && date("Y-m-d", strtotime($timeOut[$timeOutIndex]->Time)) == $nextDate){
                $nextTimeOut = $timeOut[$timeOutIndex];
                $timeOutIndex ++;
            }

            // enter into a 2 dimentional array with
            // index 0 = timein
            // index 1 = timeout
            $output[] = [$nextTimeIn, $nextTimeOut];

            // get the next date
            $nextDate = date('Y-m-d', strtotime($nextDate. ' + 1 days'));
        }

        // return view with data
        return view('welcome_message', ["output" => $output]);
    }
}

Model:

<?php
namespace App\Models;

use CodeIgniter\Model;

class EmployeeTimeLog extends Model{

    private $tableName = "tbl_employee_time_log";

    public function __construct(){
        $db = \Config\Database::connect();
        $this->builder = $db->table($this->tableName);
    }

    private function timeData($employeeId, $startDate, $endDate){
        // builder function DRY
        $this->builder->where("EmployeeId", $employeeId);
        $this->builder->where("Time >", $startDate);
        $this->builder->where("Time <", $endDate);
        $this->builder->orderBy("Time", "ASC");
    }

    public function getTimeIn($employeeId, $startDate, $endDate){
        // call builder function and add timeIn to only get clock in times
        $this->timeData($employeeId, $startDate, $endDate);
        $this->builder->where("Type", "timein");
        return $this->builder->get();
    }

    public function getTimeOut($employeeId, $startDate, $endDate){
        // call builder function and add timeout to only get clock out times
        $this->timeData($employeeId, $startDate, $endDate);
        $this->builder->where("Type", "timeout");
        return $this->builder->get();
    }
}

?>

View:

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Timesheets</title>
    </head>
    <body>
        <h1>Timesheets</h1>

        <?php
        echo "<h1>Times</h1>";
        foreach ($output as $times) {
            echo "Time in: ";
            if(!is_null($times[0])){
                echo $times[0]->Time;
            }else{
                echo "-";
            }

            echo " Time out: ";
            if(!is_null($times[1])){
                echo $times[1]->Time;
            }else{
                echo "-";
            }
            echo "<br>";
        }
         ?>
    </body>
</html>

Output:

Database:

The whole algorithm works by searching through the date between the start and end date of the dates provided. You can then check any timein and timeout values which have the same date as the loop date. If a date cannot be found, the default is NULL which is replaced by "-" in the view.

The algorithm specifically gets the earliest timein and the latest timeout value for the employee. This means that if the employee clocks-in twice in one day the earliest date will be used and the opposite for clock-out.

This algorithm specifically works for one employee, but just use your select * employees and loop through instead of the employeeId on the controller. You may want to look in the documentation for the Query Builder in CodeIgniter it makes querying the database much easier, and will escape data to protect from SQL Injections etc.

It could be better to redesign your database table and check on insert rather than manipulate on select. Where you have one row for each day and the timein and timeout which represent the time. Something like this:

You can check to see if the date already exists, if so override the current value if needed. If the current date doesn't exist for that employee create a new record.

If you need any clarification, let me know,

Thanks,

Answered By - Jack Dane