Reduce Time Complexity

Posted 5 months ago by kshitizmittal

Punchreport model :-

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Spatie\Activitylog\Traits\LogsActivity;
use Auth;

class Punchreport extends Model
{
    use LogsActivity;
    protected $fillable = ['emp_id','punch_in','punch_out','date'];

    protected static $logAttributes = ['emp_id','punch_in','punch_out','date'];
    protected static $recordEvents = ['updated','created'];

    public function rosterData(){
        $roster = Roster::where(['emp_id'=>$this->emp_id,'month'=>date('F',strtotime($this->date))])->first(); //query1
        if($roster){
            return $roster; 
        } else {
            return $default_roster = ['week_off'=>"Saturday,Sunday,Default","roster_time_in"=>"09:30","roster_time_off"=>"18:30"];
        }           
    }
    public function leaveData(){
        $leaves = Applyleave::where('emp_id','=',$this->emp_id)
                            ->where('start_date','<=',$this->date)
                            ->where('end_date','>=',$this->date)
                            ->where('final_approval','=','Approve')
                            ->with(['leavetypes'])
                            ->first(); //query 2
        if($leaves){
            return $leaves;
        } else {
            return null;
        }
    }
    public function holidayData(){
        $holidays = Assigntanda::where(['emp_id'=>$this->emp_id])->with(['holidayCalendar.holidays'])->first(); //query 3
        if($holidays){
            return $holidays->holidayCalendar->holidays->pluck('holidaydate')->toArray();
        } else {
            return null;
        }
    }
    public function getStatusAttribute(){
        $week_off = $this->rosterData()["week_off"];
        $leaves = $this->leaveData();
        $holidays = $this->holidayData();

        $is_week_off = false;
        $on_leave = false;
        $is_holiday = false;
        
        if(in_array($this->date,$holidays)){
            return "H";
        }
        if(in_array(date('l',strtotime($this->date)),explode(',',$week_off))){
            $is_week_off = true;
            return "WO";
        }
        if($leaves){
            $on_leave = true;
            return $leaves->leavetypes->type_code;  
        }
        
        if($this->punch_in && $this->punch_out) {
            $timediff = (strtotime($this->punch_out) - strtotime($this->punch_in))/3600;
            if($timediff < 0) {
                $timediff = 24+$timediff;
            }

            if($timediff >= 8.0){
                return "P";
            }
            elseif($timediff >= 4.0 && $timediff <= 8.0) {
                return "1/2P";
            }
            else {
                return "A";
            }
        } else {
            return "A";
        }       

    }

    /* === EmployeeDetail Relation === */
    public function employeedetails()
    {
        return $this->belongsTo('App\Employeedetail','emp_id', 'emp_id');
    }
}

I this Punch report table structure is

  1. emp_id - employee Id of the employee
  2. punch_in - punch in time
  3. punch_out - punchout time
  4. date - date of punch.

This table contains multiple record of employee, lets say in January there will be 31 records of a employee ID for ex:- "123456".

Now I want to add custom attribute "status" in which I want to validate the punch record as well as the week off, leaves and holiday on that day which are coming from different tables.

I am facing time Complexity issue here coz, the query is running 31 * 3 times and if the number of record are incresing it is taking more time.

Please sign in or create an account to participate in this conversation.