citrus-cms/resources/views/admin-ajax/welder-performance.blade.php

<?php 
/**
 * @api-readonly
 * Returns welder performance statistics
 */
$welderLocations = welder_locations();
$query = apply_welded_filter(db("weld_logs"));

$query = $query
    ->select(
        "mechanic_supervisor", "welder_1", "ste_subcontractor",
        DB::raw('MONTH(welding_date) as month'), DB::raw('YEAR(welding_date) as year'), DB::raw('COUNT(*) as count'))
    ->selectRaw('ROUND(SUM(CASE WHEN no_of_the_joint_as_per_as_built_survey NOT LIKE "%R%" THEN nps_1 ELSE 0 END), 2) as welder_wdi_1')
    ->selectRaw('ROUND(SUM(CASE WHEN no_of_the_joint_as_per_as_built_survey LIKE "%R%" THEN nps_1 ELSE 0 END), 2) as repair_total_1')
    ->selectRaw('ROUND(SUM(CASE WHEN no_of_the_joint_as_per_as_built_survey LIKE "%R%" THEN nps_1 ELSE 0 END) * 100 / SUM(CASE WHEN no_of_the_joint_as_per_as_built_survey NOT LIKE "%R%" THEN nps_1 ELSE 0 END), 2) AS repair_rate_1')

    ->whereNotNull("welder_1")
    ->where("welder_1", "<>", "")
    ->whereRaw("YEAR(welding_date) = {$_GET['year']}")

    ->orderBy("welding_date", "ASC")
    ->groupBy(DB::raw('MONTH(welding_date)'), DB::raw('YEAR(welding_date)'))
    
    ->groupBy("welder_1");

$query = $query->get();

$crewQuery = apply_welded_filter(db("weld_logs"));



$crewQuery = $crewQuery
                ->select(
                        "mechanic_supervisor", 
                        "ste_subcontractor", 
                        "mechanic_supervisor_control_date", 
                        DB::raw('COUNT(*) as total'),
                        DB::raw('MONTH(welding_date) as month'),
                        DB::raw('YEAR(welding_date) as year')
                )
                ->whereNotNull("mechanic_supervisor")
                ->groupBy("mechanic_supervisor", DB::raw('MONTH(welding_date)'), DB::raw('YEAR(welding_date)'))
               ;

$crewQuery = $crewQuery->get();



$zoneQuery = apply_welded_filter(db("weld_logs"))
    ->select([
        "project",
        DB::raw('MAX(design_area) as design_area'),
        DB::raw('ROUND(SUM(nps_1), 2) as total'),
        DB::raw('SUM(CASE WHEN type_of_joint = "S" AND welding_date IS NOT NULL THEN nps_1 ELSE 0 END) AS welded_shop_wdi'),
        DB::raw('SUM(CASE WHEN type_of_joint = "F" AND welding_date IS NOT NULL THEN nps_1 ELSE 0 END) AS welded_field_wdi'),
        DB::raw('SUM(CASE WHEN type_of_joint = "S" AND welding_date IS NOT NULL THEN 1 ELSE 0 END) AS welded_shop_count'),
        DB::raw('SUM(CASE WHEN type_of_joint = "F" AND welding_date IS NOT NULL THEN 1 ELSE 0 END) AS welded_field_count'),
        DB::raw('SUM(CASE WHEN type_of_joint = "S" AND welding_date IS NULL THEN nps_1 ELSE 0 END) AS backlog_shop_wdi'),
        DB::raw('SUM(CASE WHEN type_of_joint = "F" AND welding_date IS NULL THEN nps_1 ELSE 0 END) AS backlog_field_wdi'),
        DB::raw('SUM(CASE WHEN type_of_joint = "S" AND welding_date IS NULL THEN 1 ELSE 0 END) AS backlog_shop_count'),
        DB::raw('SUM(CASE WHEN type_of_joint = "F" AND welding_date IS NULL THEN 1 ELSE 0 END) AS backlog_field_count'),
        DB::raw('ROUND(SUM(CASE WHEN welding_date IS NOT NULL THEN nps_1 ELSE 0 END) * 100 / SUM(nps_1), 2) AS proses_wdi')
    ])
    ->groupBy("project")
    ->get();



$refactor = [];

$crew = [];

foreach($crewQuery AS $q) 
{
    if(!isset($crew[$q->ste_subcontractor][$q->mechanic_supervisor][$q->month]))
        $crew[$q->ste_subcontractor][$q->mechanic_supervisor][$q->month] = 0;

    $crew[$q->ste_subcontractor][$q->mechanic_supervisor][$q->month] += $q->total;
}

foreach($query AS $q) {
    $row = [];

    for($k = 1; $k<=12; $k++) {
        if(!isset($refactor[$q->welder_1]['welder_wdi_' . $k])) 
        {
            $refactor[$q->welder_1]['welder_wdi_' . $k] = 0;
            $refactor[$q->welder_1]['welder_wdi_' . $k . '_repair_total'] = 0;
            $refactor[$q->welder_1]['welder_repair_' . $k] = 0;
            $refactor[$q->welder_1]['welder_wdi_' . $k . '_change'] = "equal";
        }
    }

    

    $refactor[$q->welder_1]['welder_wdi_' . $q->month] = $q->welder_wdi_1;
    $refactor[$q->welder_1]['welder_wdi_' . $q->month . '_repair_total'] = $q->repair_total_1;
    $refactor[$q->welder_1]['welder_repair_' . $q->month] = $q->repair_rate_1;
    $refactor[$q->welder_1]['subcontractor'] = $q->ste_subcontractor;
    $refactor[$q->welder_1]['welder'] = $q->welder_1;
}

$previousRepairRate = 0;
$data = $refactor;
$rows = [];
$added = [];

function calculateAverage($welderData) {
    $sum = 0;
    $count = 0;
    for ($i = 1; $i <= 12; $i++) {
        $key = 'welder_wdi_' . $i;
        $value = $welderData[$key] ?? 0;
        if($value > 0) {
            $sum += $value;
            $count++;
        }
    }
    
    return $count > 0 ? round($sum / $count, 2) : 0;
}

foreach($refactor AS $welder => &$data) {
    $refactor[$welder]['location'] = @$welderLocations[$welder];
    $refactor[$welder]['avg'] = calculateAverage($refactor[$welder]);
    
    // Calculate year_total_wdi from monthly sums
    $yearTotal = 0;
    for($i = 1; $i <= 12; $i++) {
        $yearTotal += isset($refactor[$welder]['welder_wdi_' . $i]) ? $refactor[$welder]['welder_wdi_' . $i] : 0;
    }
    $refactor[$welder]['year_total_wdi'] = round($yearTotal, 2);
    
    // Calculate change indicators for each month
    for($month = 1; $month <= 12; $month++) {
        $currentWDI = $refactor[$welder]['welder_wdi_' . $month] ?? 0;
        $previousWDI = $refactor[$welder]['welder_wdi_' . ($month - 1)] ?? 0;
        
        // WDI change indicator (for W column)
        if($previousWDI < $currentWDI) {
            $wdiChange = "up";
        } elseif($previousWDI > $currentWDI) {
            $wdiChange = "down";
        } else {
            $wdiChange = "equal";
        }
        $refactor[$welder]['welder_wdi_' . $month . '_change'] = $wdiChange;
        
        // Repair rate change indicator (for RR column)
        $currentRR = $refactor[$welder]['welder_repair_' . $month] ?? 0;
        $previousRR = $refactor[$welder]['welder_repair_' . ($month - 1)] ?? 0;
        
        if($previousRR < $currentRR) {
            $rrChange = "up";    // Repair increased (bad)
        } elseif($previousRR > $currentRR) {
            $rrChange = "down";  // Repair decreased (good)
        } else {
            $rrChange = "equal";
        }
        $refactor[$welder]['welder_repair_' . $month . '_change'] = $rrChange;
    }
    
    if(!in_array($welder, $added)) {
        $rows[] = $refactor[$welder];
        $added[] = $welder;
    }
    
    
}

$crewPerformance = [];

foreach($crew AS $subcontractor => $data) {
    
    $row['subcontractor'] = $subcontractor;

    foreach($data AS $supervizor => $monthData) {

        $row['supervizor'] = $supervizor;

        foreach($monthData AS $monthName => $value) {
            $row["month_$monthName"] = $value;
        }
        
    }

    $crewPerformance[] = $row;
}

$data = [
    'welderPerformance' => $rows,
    'crewPerformance' => $crewQuery,
    'zonePerformance' => $zoneQuery
];

echo json_encode_tr($data);
?>