@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();
// 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();
// 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();
// Uncomment to debug
// dd($service_length_groups);
@endphp
@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