How to Generate Leave Balance Table for a particular employee

In my Laravel-5.8 project, when an employee logs in I want to display a table as shown below that shows his Leave Balance

https://i.stack.imgur.com/KNuin.png

I have 3 tables that are applicable

class LeaveCategory extends Model
{
   protected $table = 'leave_categories';

   protected $fillable = [
              'leave_category_name',
          ];

   public function leavecategorydetail()
   {
       return $this->hasMany('App\Models\LeaveCategoryDetail');
   }  
}

class LeaveCategoryDetail extends Model
{
   protected $table = 'leave_category_details';
   protected $fillable = [
              'id',
              'leave_category_id',
              'employment_type_id',
              'no_of_days',
          ];

   public function leavecategory()
   {
       return $this->belongsTo('App\Models\LeaveCategory', 'leave_category_id', 'id');
   }

   public function employmenttype()
   {
       return $this->belongsTo('App\Models\EmploymentType', 'employment_type_id', 'id' );
   }    
}

class LeaveRequest extends Model
{
   protected $table = 'leave_requests';
   protected $fillable = [
              'id',
              'employee_id',
              'leave_category_id',
              'leave_status',
              'approved_days',
          ];


   public function employee()
   {
       return $this->belongsTo('App\Models\Employee','employee_id');
   }    

   public function leavetype()
   {
       return $this->belongsTo('App\Models\LeaveCategory','leave_category_id');
   }
}

As earlier said, the expected result is to have 4 columns (Leave Category , Applicable Leave, Approved Leave , Available )

Controller

public function leave_balance()
{
    $userId = Auth::user()->id;
$userCompany = Auth::user()->company_id;
$employmentcategory = Employee::select('employeement_category_id')->where('employee_id', $userId)->where('is_active', 1)->first();

//Leave Category
$leavecategories = LeaveCategory::select('leave_category_name')->where('company_id', $userCompany)->get();

//Applicable Leave
    $applicableleaves = DB::table('leave_categories')
             ->join('leave_category_details', 'leave_category_details.leave_category_id', '=', 'leave_categories.id')
             ->select('leave_category_details.no_of_days')
             ->where('leave_categories.company_id', $userCompany)
             ->where('leave_categories.employment_category_id',$employmentcategory)
             ->get(); 

//Approved Leave
    $approvedleaves = DB::table('leave_requests')
             ->select('employee_id','leave_category_id', DB::raw('SUM(approved_days) AS approvedLeave'))
             ->where('employee_id', $userId)
	 ->where('leave_category_id', $employmentcategory)
             ->where('leave_status',4)
             ->groupBy('employee_id', 'leave_category_id')
             ->get(); 

//Available
$availableleaves = $applicableleaves - $approvedleaves

   $leavebalances = ...

return view('leave-balances')
	->with('leavebalances', $leavebalances)
}

How do I combine the four queries in my controller ($leavecategories, $applicableleaves, $approvedleaves, $availableleaves) into $leavebalances and also get a view like

https://i.stack.imgur.com/KNuin.png

            <thead>
                <tr>
                    <th width="55%">
                        Leave Category
                    </th>
                    <th width="15%">
                        Applicable Leave
                    </th>
                    <th width="15%">
                        Approved Leave
                    </th>
                    <th width="15%">
                        leavebalances
                    </th>                        
                </tr>
            </thead>
            <tbody>      
                <!--start foreach-->
                    <td>

                    </td> 
                    <td>

                    </td>      
                    <td>

                    </td> 
                    <td>

                    </td>                           
                <!--end foreach-->

If there is no field/value for $approvedleaves, it should initialize with 0

Thank you.

flightsimmer668

6 years ago

Level 11

Hello @noblemfd

In the leave_balance() controller method, I think the way the data is fetched (applicable leave, approved leave, etc) makes it difficult for you to show it in the table within your view, because you are essentially gathering columns, not rows of data, for the table.

Perhaps you could try something like the following:

public function leave_balance()
{
    ...

    // Leave Category
    $leaveCategories = LeaveCategory::select('leave_category_name')->where('company_id', $userCompany)->get();

    // for each leave category, add keys for applicable leave, approved and available
    foreach ($leaveCategories as $i) {
        $i['applicable_leaves'] = // applicable leaves for this category
	$i['approved_leaves'] = // approved leaves for this category
	$i['available_leaves'] = // available leaves for this category
    }

    return view('leave-balances')->with('leaveCategories', $leaveCategories);

Then in your view's table you could


@foreach ($leaveCategories as $i)
<tr>
    <td>{{ $i->leave_category_name }}</td> 
    <td>{{ $i['applicable_leaves'] }}</td>      
    <td>{{ $i['approved_leaves'] }}</td> 
    <td>{{ $i['available_leaves'] }}</td>                           
</tr>
@endforeach

I hope this helps you. It does feel like there's way too much code in the leave_balance() controller method. You might want to consider refactoring the leave balance calculations to their own class.

If there is no field/value for $approvedleaves, it should initialize with 0

It's cleaner to do this in the code where you figure out the value for $i['available_leaves'] and not in the view

noblemfd

6 years ago

Level 9

@flightsimmer668 - Please I don't understand this.

    $i['applicable_leaves'] = // applicable leaves for this category
$i['approved_leaves'] = // approved leaves for this category
$i['available_leaves'] = // available leaves for this category

Can you explain a bit, maybe with example.

Thank you

flightsimmer668

6 years ago

Level 11

$i['applicable_leaves'] = // applicable leaves for this category
$i['approved_leaves'] = // approved leaves for this category
$i['available_leaves'] = // available leaves for this category

Here you are assigning an array key to each $i object so that you can access it later. For each array key, you'll need to run some code in order to calculate applicable_leaves, approved_leaves, and available_leaves. For example:

foreach ($leaveCategories as $i) {
        $i['applicable_leaves'] = $this->applicableLeaves($i);
	$i['approved_leaves'] = // approved leaves for this category
	$i['available_leaves'] = // available leaves for this category
}

private function applicableLeaves(LeaveCategory $leaveCategory)
{
	// use whatever code you need here in order to figure out the corresponding number
	// of applicable leaves for this leave category.

	// then return that value.
}

Please or to participate in this conversation.