<?php 
use Illuminate\Support\Facades\DB;

$results = DB::table('weld_logs')
    ->select([
        // Alt yüklenici ismi
        'ste_subcontractor',

        // Toplam satır sayısı (tüm eklemelerin toplamı)
        DB::raw('NULLIF(
            SUM(
                CASE 
                    WHEN (ut_result != "" 
                              OR rt_result != "") 
                    THEN 1 
                    ELSE 0 
                END
            ), 
            0
        ) AS examination_joints_quantity'),

        // Kaynak tarihi boş olmayanların `nps_1` toplamı
        DB::raw(
            'SUM(
                CASE 
                    WHEN welding_date IS NOT NULL 
                    THEN nps_1 
                    ELSE 0 
                END
            ) AS welded_joints_wdi'
        ),

        // Tüm satırların `nps_1` toplamı
        DB::raw('NULLIF(
            SUM(
                CASE 
                    WHEN (ut_result != "" 
                              OR rt_result != "") 
                    THEN nps_1
                    ELSE 0 
                END
            ), 
            0
        ) AS examination_joints_wdi'),

        // Atölye kaynaklarının (Shop) onarım oranı
        DB::raw(
            'ROUND(
                100.0 * 
                SUM(
                    CASE 
                        WHEN type_of_joint = "S" 
                             AND (ut_result NOT IN ("", "Accept / Годен") 
                                  OR rt_result NOT IN ("", "Accept / Годен")) 
                        THEN 1 
                        ELSE 0 
                    END
                ) / 
                NULLIF(
                    SUM(
                        CASE 
                            WHEN type_of_joint = "S" 
                                 AND (ut_result NOT IN ("") 
                                      OR rt_result NOT IN ("")) 
                            THEN 1 
                            ELSE 0 
                        END
                    ), 
                    0
                ), 
                2
            ) AS shop_repair_rate'
        ),

        // Saha kaynaklarının (Field) onarım oranı
        DB::raw(
            'ROUND(
                100.0 * 
                SUM(
                    CASE 
                        WHEN type_of_joint = "F" 
                             AND (ut_result NOT IN ("", "Accept / Годен") 
                                  OR rt_result NOT IN ("", "Accept / Годен")) 
                        THEN 1 
                        ELSE 0 
                    END
                ) / 
                NULLIF(
                    SUM(
                        CASE 
                            WHEN type_of_joint = "F" 
                                 AND (ut_result NOT IN ("") 
                                      OR rt_result NOT IN ("")) 
                            THEN 1 
                            ELSE 0 
                        END
                    ), 
                    0
                ), 
                2
            ) AS field_repair_rate'
        ),

        // Atölye kaynaklarının (Shop) toplam eklem sayısı
        DB::raw(
            'NULLIF(
                SUM(
                    CASE 
                        WHEN type_of_joint = "S" 
                             AND (ut_result != "" 
                                  OR rt_result != "") 
                        THEN 1 
                        ELSE 0 
                    END
                ), 
                0
            ) AS shop_all_total'
        ),

        // Saha kaynaklarının (Field) toplam eklem sayısı
        DB::raw(
            'NULLIF(
                SUM(
                    CASE 
                        WHEN type_of_joint = "F" 
                             AND (ut_result != "" 
                                  OR rt_result != "") 
                        THEN 1 
                        ELSE 0 
                    END
                ), 
                0
            ) AS field_all_total'
        ),

        // Atölye kaynaklarının (Shop) onarılan toplam eklem sayısı
        DB::raw(
            'NULLIF(
                SUM(
                    CASE 
                        WHEN type_of_joint = "S" 
                             AND (ut_result NOT IN ("", "Accept / Годен") 
                                  OR rt_result NOT IN ("", "Accept / Годен")) 
                        THEN 1 
                        ELSE 0 
                    END
                ), 
                0
            ) AS shop_repair_total'
        ),

        // Saha kaynaklarının (Field) onarılan toplam eklem sayısı
        DB::raw(
            'NULLIF(
                SUM(
                    CASE 
                        WHEN type_of_joint = "F" 
                             AND (ut_result NOT IN ("", "Accept / Годен") 
                                  OR rt_result NOT IN ("", "Accept / Годен")) 
                        THEN 1 
                        ELSE 0 
                    END
                ), 
                0
            ) AS field_repair_total'
        ),
    ])
    // Alt yükleniciye göre gruplama
    ->groupBy('ste_subcontractor')
    // Veriyi çekme
    ->get();


?>
{{col("col-12","Subcontractor Repair Analysis")}} 
<table class="table table-bordered text-center">
            <thead class="table-light">
                <tr>
                    <th rowspan="2">Subcontractor</th>
                    <th rowspan="2">Examination Joints Quantity</th>
                    <th rowspan="2">Welded Joints WDI</th>
                    <th rowspan="2" class="table-warning">Examination Joints WDI</th>
                    <th colspan="3">Repair Rate %</th>
                </tr>
                <tr>
                    <th>Shop</th>
                    <th>Field</th>
                    <th>General</th>
                </tr>
            </thead>
            <tbody>
                @foreach($results AS $r)
                <?php 
                try {
                    $generalRate = round(100 * ($r->field_repair_total + $r->shop_repair_total)/($r->shop_all_total + $r->field_all_total), 2);
                } catch (\Throwable $th) {
                    //throw $th;
                    $generalRate = 0;
                }
                ?>
                <tr>
                    <td>{{$r->ste_subcontractor}}</td>
                    <td>{{$r->examination_joints_quantity}}</td> 
                    <td>{{$r->welded_joints_wdi}}</td>  
                    <td>{{@$r->examination_joints_wdi}}</td> 
                    <td>{{$r->shop_repair_rate}}%</td> 
                    <td>{{$r->field_repair_rate}}%</td> 
                    <td>{{$generalRate}}%</td> 
                </tr>
                @endforeach
              
            </tbody>
        </table>             
{{_col()}}