Ümit Tunç
298 lines
10 KiB
PHP
298 lines
10 KiB
PHP
<?php
|
||
use App\Models\PunchList;
|
||
use App\Services\Dashboards\TestPackageDashboardService;
|
||
use Illuminate\Support\Facades\DB;
|
||
|
||
$punchLists = PunchList::whereNotNull('status')->get();
|
||
$projectMap = TestPackageDashboardService::projectMap();
|
||
|
||
// Line/ISO number üzerinden project ve design_area mapping
|
||
$lineProjectMap = DB::table('weld_logs')
|
||
->select('line_number', DB::raw('MAX(project) as project'), DB::raw('MAX(design_area) as design_area'))
|
||
->whereNotNull('line_number')
|
||
->where('line_number', '!=', '')
|
||
->whereNotNull('project')
|
||
->groupBy('line_number')
|
||
->get()
|
||
->mapWithKeys(function($item) {
|
||
return [$item->line_number => ['project' => $item->project, 'design_area' => $item->design_area]];
|
||
})
|
||
->toArray();
|
||
|
||
$isoProjectMap = DB::table('weld_logs')
|
||
->select('iso_number', DB::raw('MAX(project) as project'), DB::raw('MAX(design_area) as design_area'))
|
||
->whereNotNull('iso_number')
|
||
->where('iso_number', '!=', '')
|
||
->whereNotNull('project')
|
||
->groupBy('iso_number')
|
||
->get()
|
||
->mapWithKeys(function($item) {
|
||
return [$item->iso_number => ['project' => $item->project, 'design_area' => $item->design_area]];
|
||
})
|
||
->toArray();
|
||
|
||
// test_package_no üzerinden project ve design_area mapping (tek sorguda tümünü yükle)
|
||
$testPackageProjectMap = [];
|
||
$testPackageNos = $punchLists->pluck('test_package')->filter()->unique()->toArray();
|
||
if (!empty($testPackageNos)) {
|
||
$testPackageProjectMap = DB::table('weld_logs')
|
||
->select('test_package_no', DB::raw('MAX(project) as project'), DB::raw('MAX(design_area) as design_area'))
|
||
->whereIn('test_package_no', $testPackageNos)
|
||
->whereNotNull('project')
|
||
->groupBy('test_package_no')
|
||
->get()
|
||
->mapWithKeys(function($item) {
|
||
return [$item->test_package_no => ['project' => $item->project, 'design_area' => $item->design_area]];
|
||
})
|
||
->toArray();
|
||
}
|
||
|
||
$statuses = ['Open', 'Closed', 'Canceled'];
|
||
$categories = ['A', 'B', 'C'];
|
||
|
||
$statusMatrix = [];
|
||
$overallCategories = [];
|
||
$projectRollups = [];
|
||
$monthlyRollups = [];
|
||
|
||
foreach ($statuses as $status) {
|
||
foreach ($categories as $category) {
|
||
$statusMatrix[$status]['punch_' . strtolower($category) . '_quantity'] = 0;
|
||
}
|
||
}
|
||
|
||
foreach ($punchLists as $punch) {
|
||
$status = ucfirst(strtolower($punch->status ?? 'Open'));
|
||
if (!in_array($status, $statuses)) {
|
||
$status = 'Open';
|
||
}
|
||
|
||
$category = strtoupper($punch->category ?? 'A');
|
||
if (!in_array($category, $categories)) {
|
||
continue;
|
||
}
|
||
|
||
// Project mapping: test_package -> line_isometric_no -> iso_number sırasıyla
|
||
$project = 'Unknown';
|
||
$designArea = null;
|
||
|
||
if (!empty($punch->test_package)) {
|
||
// Önce test_package_no mapping'den kontrol et
|
||
if (isset($testPackageProjectMap[$punch->test_package])) {
|
||
$project = trim($testPackageProjectMap[$punch->test_package]['project']);
|
||
$designArea = trim($testPackageProjectMap[$punch->test_package]['design_area'] ?? '');
|
||
} elseif (isset($projectMap[$punch->test_package])) {
|
||
// Fallback: TestPackageDashboardService'den gelen mapping
|
||
$project = trim($projectMap[$punch->test_package]);
|
||
}
|
||
}
|
||
|
||
// Eğer hala project bulunamadıysa, line_isometric_no veya iso_number'dan dene
|
||
if ($project === 'Unknown' && !empty($punch->line_isometric_no)) {
|
||
if (isset($lineProjectMap[$punch->line_isometric_no])) {
|
||
$project = trim($lineProjectMap[$punch->line_isometric_no]['project']);
|
||
$designArea = trim($lineProjectMap[$punch->line_isometric_no]['design_area'] ?? '');
|
||
} elseif (isset($isoProjectMap[$punch->line_isometric_no])) {
|
||
$project = trim($isoProjectMap[$punch->line_isometric_no]['project']);
|
||
$designArea = trim($isoProjectMap[$punch->line_isometric_no]['design_area'] ?? '');
|
||
}
|
||
}
|
||
|
||
$project = $project ?: 'Unknown';
|
||
$designArea = $designArea ?: 'Unknown';
|
||
|
||
$foundDate = $punch->found_date ?? $punch->created_at ?? now();
|
||
$monthKey = date('Y-m', strtotime($foundDate));
|
||
$monthLabel = date('M Y', strtotime($foundDate));
|
||
|
||
$statusMatrix[$status]['punch_' . strtolower($category) . '_quantity']++;
|
||
|
||
if (!isset($overallCategories[$category]['total'])) {
|
||
$overallCategories[$category] = [
|
||
'total' => 0,
|
||
'open' => 0,
|
||
'closed' => 0,
|
||
];
|
||
}
|
||
$overallCategories[$category]['total']++;
|
||
if ($status === 'Open') {
|
||
$overallCategories[$category]['open']++;
|
||
} elseif ($status === 'Closed') {
|
||
$overallCategories[$category]['closed']++;
|
||
}
|
||
|
||
$projectKey = strtolower($project);
|
||
if (!isset($projectRollups[$projectKey])) {
|
||
$projectRollups[$projectKey] = [
|
||
'project' => $project,
|
||
'categories' => []
|
||
];
|
||
}
|
||
if (!isset($projectRollups[$projectKey]['categories'][$category])) {
|
||
$projectRollups[$projectKey]['categories'][$category] = [
|
||
'open' => 0,
|
||
'closed' => 0,
|
||
'canceled' => 0,
|
||
];
|
||
}
|
||
$projectRollups[$projectKey]['categories'][$category][strtolower($status)]++;
|
||
|
||
// Area bazlı monthly rollup
|
||
if (!isset($monthlyRollups[$monthKey])) {
|
||
$monthlyRollups[$monthKey] = [
|
||
'label' => $monthLabel,
|
||
'areas' => []
|
||
];
|
||
}
|
||
|
||
$area = $designArea ?? 'Unknown';
|
||
$areaKey = strtolower($area);
|
||
if (!isset($monthlyRollups[$monthKey]['areas'][$areaKey])) {
|
||
$monthlyRollups[$monthKey]['areas'][$areaKey] = [
|
||
'area' => $area,
|
||
'open' => 0,
|
||
'closed' => 0,
|
||
'canceled' => 0,
|
||
];
|
||
}
|
||
$monthlyRollups[$monthKey]['areas'][$areaKey][strtolower($status)]++;
|
||
}
|
||
|
||
$totalConstructionPunchPointProgress = [];
|
||
$sumA = 0;
|
||
$sumB = 0;
|
||
$sumC = 0;
|
||
|
||
foreach ($statuses as $status) {
|
||
$row = [
|
||
'Status' => $status,
|
||
'Punch A' => $statusMatrix[$status]['punch_a_quantity'],
|
||
'Punch B' => $statusMatrix[$status]['punch_b_quantity'],
|
||
'Punch C' => $statusMatrix[$status]['punch_c_quantity'],
|
||
];
|
||
$totalConstructionPunchPointProgress[] = $row;
|
||
|
||
$sumA += $row['Punch A'];
|
||
$sumB += $row['Punch B'];
|
||
$sumC += $row['Punch C'];
|
||
}
|
||
|
||
$totalConstructionPunchPointProgress[] = [
|
||
'Status' => 'Total',
|
||
'Punch A' => $sumA,
|
||
'Punch B' => $sumB,
|
||
'Punch C' => $sumC,
|
||
];
|
||
|
||
$totalConstructionPunchPointProgress[] = [
|
||
'Status' => 'Progress',
|
||
'Punch A' => $sumA ? round(($statusMatrix['Closed']['punch_a_quantity'] / $sumA) * 100, 2) . '%' : '0%',
|
||
'Punch B' => $sumB ? round(($statusMatrix['Closed']['punch_b_quantity'] / $sumB) * 100, 2) . '%' : '0%',
|
||
'Punch C' => $sumC ? round(($statusMatrix['Closed']['punch_c_quantity'] / $sumC) * 100, 2) . '%' : '0%',
|
||
];
|
||
|
||
$categoryPie = [];
|
||
$totalCategoryCount = array_sum(array_column($overallCategories, 'total'));
|
||
|
||
foreach ($overallCategories as $category => $values) {
|
||
$categoryPie[] = [
|
||
'name' => "Punch {$category}",
|
||
'value' => $values['total'],
|
||
'share' => $totalCategoryCount ? round($values['total'] * 100 / $totalCategoryCount, 2) : 0,
|
||
];
|
||
}
|
||
|
||
$projectCategoryChart = [];
|
||
foreach ($projectRollups as $projectKey => $projectData) {
|
||
$project = $projectData['project'] ?? $projectKey;
|
||
$categoriesData = $projectData['categories'] ?? [];
|
||
$row = ['project' => $project];
|
||
foreach ($categories as $category) {
|
||
$stats = $categoriesData[$category] ?? ['open' => 0, 'closed' => 0, 'canceled' => 0];
|
||
$key = strtolower($category);
|
||
$row["{$key}_open"] = (int)($stats['open'] ?? 0);
|
||
$row["{$key}_closed"] = (int)($stats['closed'] ?? 0);
|
||
$row["{$key}_canceled"] = (int)($stats['canceled'] ?? 0);
|
||
$row["{$key}_total"] = (int)($stats['open'] ?? 0) + (int)($stats['closed'] ?? 0) + (int)($stats['canceled'] ?? 0);
|
||
}
|
||
$projectCategoryChart[] = $row;
|
||
}
|
||
|
||
// Eğer projectCategoryChart boşsa, en azından bir boş satır ekle
|
||
if (empty($projectCategoryChart)) {
|
||
$projectCategoryChart[] = [
|
||
'project' => 'No Data',
|
||
'a_open' => 0,
|
||
'a_closed' => 0,
|
||
'a_canceled' => 0,
|
||
'b_open' => 0,
|
||
'b_closed' => 0,
|
||
'b_canceled' => 0,
|
||
'c_open' => 0,
|
||
'c_closed' => 0,
|
||
'c_canceled' => 0
|
||
];
|
||
}
|
||
|
||
$monthlyStatusChart = [];
|
||
$requestedYear = get('year', date('Y'));
|
||
$filterYear = (int) $requestedYear;
|
||
// Seçilen yılın 12 ayını göster
|
||
$allowedMonths = [];
|
||
for ($i = 1; $i <= 12; $i++) {
|
||
$monthKey = sprintf('%04d-%02d', $filterYear, $i);
|
||
$allowedMonths[] = $monthKey;
|
||
}
|
||
|
||
ksort($monthlyRollups);
|
||
foreach ($monthlyRollups as $month => $monthData) {
|
||
// Sadece mevcut yılın aylarını işle
|
||
if (in_array($month, $allowedMonths)) {
|
||
// Her area için ayrı satır oluştur
|
||
if (isset($monthData['areas']) && is_array($monthData['areas'])) {
|
||
foreach ($monthData['areas'] as $areaKey => $areaData) {
|
||
$area = $areaData['area'] ?? $areaKey;
|
||
$stats = [
|
||
'open' => $areaData['open'] ?? 0,
|
||
'closed' => $areaData['closed'] ?? 0,
|
||
'canceled' => $areaData['canceled'] ?? 0,
|
||
];
|
||
$total = (int)($stats['open']) + (int)($stats['closed']) + (int)($stats['canceled']);
|
||
$monthlyStatusChart[] = [
|
||
'month' => $month,
|
||
'month_label' => $monthData['label'] ?? $month,
|
||
'area' => $area,
|
||
'design_area' => $area,
|
||
'open_count' => (int)($stats['open']),
|
||
'closed_count' => (int)($stats['closed']),
|
||
'canceled_count' => (int)($stats['canceled']),
|
||
'total_count' => $total,
|
||
];
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// Eğer monthlyStatusChart boşsa, en azından bir boş satır ekle
|
||
if (empty($monthlyStatusChart)) {
|
||
$monthlyStatusChart[] = [
|
||
'month' => date('Y-m'),
|
||
'month_label' => date('M Y'),
|
||
'area' => 'No Data',
|
||
'design_area' => 'No Data',
|
||
'open_count' => 0,
|
||
'closed_count' => 0,
|
||
'canceled_count' => 0,
|
||
'total_count' => 0,
|
||
];
|
||
}
|
||
|
||
$type = get('type', 'totalConstructionPunchPointProgress');
|
||
$payload = [
|
||
'totalConstructionPunchPointProgress' => $totalConstructionPunchPointProgress,
|
||
'openPunchPoint' => $categoryPie,
|
||
'projectCategory' => $projectCategoryChart,
|
||
'monthlyStatus' => $monthlyStatusChart,
|
||
];
|
||
|
||
echo json_encode_tr($payload[$type] ?? []); |