@php use Carbon\Carbon; // Get data for workers grouped by recommended grade for the last month $last_year_grade_list_with_worker = DB::table('worker_entries') ->select('recomanded_grade', DB::raw('COUNT(id_card_no) as total_workers')) ->whereMonth('created_at', Carbon::now()->subMonth()->month) ->whereYear('created_at', Carbon::now()->year) ->groupBy('recomanded_grade') ->get(); $last_month_total = $last_year_grade_list_with_worker->sum('total_workers'); // Get data for workers grouped by recommended grade for the current month $this_year_grade_list_with_worker = DB::table('worker_entries') ->select('recomanded_grade', DB::raw('COUNT(id_card_no) as total_workers')) ->whereMonth('created_at', Carbon::now()->month) ->whereYear('created_at', Carbon::now()->year) ->groupBy('recomanded_grade') ->get(); $this_month_total = $this_year_grade_list_with_worker->sum('total_workers'); // Calculate service length groups (2-year intervals) $distinct_service_length_groups = DB::table('worker_entries') ->select( DB::raw('FLOOR(DATEDIFF(DAY, joining_date, GETDATE()) / (365 * 2)) as service_length_group'), // Groups every 2 years DB::raw('COUNT(DISTINCT id_card_no) as total_workers'), // Count distinct workers ) ->whereNull('old_matrix_Data_status') ->groupBy(DB::raw('FLOOR(DATEDIFF(DAY, joining_date, GETDATE()) / (365 * 2))')) ->get(); $total_workers = $distinct_service_length_groups->sum('total_workers'); $all_time_total_recomanded_grade = DB::table('worker_entries') ->select('recomanded_grade', DB::raw('COUNT(DISTINCT id_card_no) as total_workers')) ->whereNull('old_matrix_Data_status') // ->whereMonth('created_at', Carbon::now()->month) ->whereYear('created_at', Carbon::now()->year) ->groupBy('recomanded_grade') ->get(); //count total worker except old matrix data and null recomanded grade $all_time_total_workers = DB::table('worker_entries') ->whereNull('old_matrix_Data_status') ->whereNotNull('recomanded_grade') ->count(); // Uncomment to debug // dd($service_length_groups); @endphp

Last Month

{{-- --}}
Total Workers: {{ $last_month_total }}

This Month

{{-- --}}
Total Workers: {{ $this_month_total }}

ALL Time Total Evalueted Record

@foreach ($all_time_total_recomanded_grade as $row) @if ($row->recomanded_grade == '') @else @endif @if ($row->recomanded_grade == '') @else @endif @endforeach
Grade Total Workers
Not Update Yet Unfinished list {{ $row->recomanded_grade }} @php $rowtotal_workers = $row->total_workers - $all_time_total_workers; @endphp {{ $rowtotal_workers }} {{ $row->total_workers }}
Total {{ $all_time_total_workers }}

Service Length

{{-- --}}
Total Workers: {{ $total_workers }}
@php // Initialize an empty array to store the data for the pie chart $last_year_chart_data = []; // Loop through the database results and prepare the data for Highcharts foreach ($last_year_grade_list_with_worker as $row) { $last_year_chart_data[] = [ 'name' => $row->recomanded_grade, 'y' => (int) $row->total_workers, // Convert to integer ]; } // Convert the data array to JSON format for Highcharts $last_year_chart__json_data = json_encode($last_year_chart_data); // Initialize an empty array to store the data for the pie chart $this_year_chart_data = []; // Loop through the database results and prepare the data for Highcharts foreach ($this_year_grade_list_with_worker as $row) { $this_year_chart_data[] = [ 'name' => $row->recomanded_grade, 'y' => (int) $row->total_workers, // Convert to integer ]; } // Convert the data array to JSON format for Highcharts $this_year_chart__json_data = json_encode($this_year_chart_data); // Initialize arrays to store data for the chart $serviceLengthGroups = []; $totalWorkers = []; $totalWorkersPercentage = []; // Loop through the database results to calculate the total number of workers $totalWorkersCount = 0; foreach ($distinct_service_length_groups as $row) { $totalWorkersCount += (int) $row->total_workers; } // echo $totalWorkersCount; // Check if totalWorkersCount is zero to avoid division by zero error if ($totalWorkersCount != 0) { // Loop through the database results and prepare the data for Highcharts foreach ($distinct_service_length_groups as $row) { // Calculate the lower and upper bounds of the service length group $lowerBound = (int) $row->service_length_group * 2; $upperBound = $lowerBound + 2; $serviceLengthGroup = $lowerBound . '-' . $upperBound; // Store data in arrays $serviceLengthGroups[] = $serviceLengthGroup; $totalWorkers[] = (int) $row->total_workers; $totalWorkersPercentage[] = round(((int) $row->total_workers / $totalWorkersCount) * 100, 2); // Calculate percentage } } // dd($serviceLengthGroups, $totalWorkers, $totalWorkersPercentage); //convert all data according to serviceLengthGroups value assending order // Extract the starting value from each range $startingValues = array_map(function ($group) { return (int) explode('-', $group)[0]; // Get the first part of the range }, $serviceLengthGroups); // Use array_multisort to sort by the extracted starting values array_multisort($startingValues, SORT_ASC, $serviceLengthGroups, $totalWorkers, $totalWorkersPercentage); // Convert arrays to JSON format for Highcharts $serviceLengthGroupsJson = json_encode($serviceLengthGroups); $totalWorkersJson = json_encode($totalWorkers); $totalWorkersPercentageJson = json_encode($totalWorkersPercentage); @endphp