<?php
    $startDate = date('Y-m-d 00:00');
    $endDate = date('Y-m-d 23:59');
    $currMonth = date('m');
    $currYear = date('Y');

    $status_id = App\Models\Status::COMPLETED;
    $user_id = Auth::user()->id;

    $locations_user = App\Models\Location::whereHas('users', function ($query) use ($user_id) {
                $query->where('users.id', $user_id);
            })->pluck('id')->toArray();

    $question_set_jobs_completeds  = App\Models\QuestionSetJob::where('status_id',$status_id)->whereIn('location_id',$locations_user);

    if(!empty($request->get('filter_periode'))){
        $tab = explode(' - ',$request->get('filter_periode'));
        $datedeb = date('Y-m-d 00:00',strtotime(str_replace('/','-',$tab[0])));
        $datefin = date('Y-m-d 23:59',strtotime(str_replace('/','-',$tab[1])));

        $question_set_jobs_completeds->where('start_at','>=', $datedeb);
        $question_set_jobs_completeds->where('end_at','<=', $datefin);
    }

    $locations_ids = array();
    if(!empty($request->get('location_id'))){
        $locations_ids = $request->get('location_id');
        $question_set_jobs_completeds = $question_set_jobs_completeds->whereIn('location_id',$locations_ids);
    }

    //dd($question_set_jobs_completeds);
    $question_set_jobs_completeds2= clone $question_set_jobs_completeds;

    $question_set_jobs_completeds_by_locations = $question_set_jobs_completeds2->join('locations', 'question_set_jobs.location_id', '=', 'locations.id')
        ->select('question_set_jobs.location_id', 'locations.name', DB::raw('count(*) as total'))
        ->groupBy('question_set_jobs.location_id', 'locations.name')
        ->get();

?>


<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>checklists by locations</title>

    <style>
        table {
            width: 100%;
            border-collapse: collapse;
        }
        th, td {
            border: 1px solid black;
            text-align: center;
            font-size: 13px;
            height: 20px;
        }
        thead th {
            background-color: black;
            color: white;
        }
        tbody tr {
            background-color: #AEB6BF;
        }
    </style>

</head>
<body>



<table style="width: 100%; border-collapse: collapse;" >
    <thead>
    <tr>
        <th colspan="7" style="height: 30px; font-size: 20px; font-weight: bold">
           Details of  checklists by locations ({{date('d-m-Y', strtotime($datedeb))}} to {{date('d-m-Y', strtotime($datefin))}})
        </th>
    </tr>
    </thead>
</table>

<table>
<thead style="background-color: black">
    <tr>
        <th style="background-color: black; width: 20px; height: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Locations</th>
        <th style="background-color: black; width: 20px; height: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Checklist</th>
        <th style="background-color: black; width: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Submitted</th>
        <th style="background-color: black; width: 60px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Total Issues</th>
        <th style="background-color: black; width: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Critical Issues</th>
        <th style="background-color: black; width: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Average Score</th>
        <th style="background-color: black; width: 20px; border: 1px solid white;  font-size: 13px; font-weight: bold; text-align: center; color: white">Decision</th>
    </tr>
</thead>

    <tbody>

    <?php

        $dico = [];
        $results = [];

        foreach ($question_set_jobs_completeds_by_locations as $q) {
            $question_set_jobs_completeds2 = $question_set_jobs_completeds;
            $location= DB::table('locations')->where('name', $q->name)->first('id');

            if ($location) {
                $filtered_jobs = $question_set_jobs_completeds2->where('location_id',$location->id);
                $result =  App\Models\QuestionSetJob::computeIssues($filtered_jobs->get());
                $total_issues = $result['total_issues'];
                $critical_issues = $result['critical_issues'];

                $results[] = [
                    'question_set_jobs' => $filtered_jobs,
                    'name' => $q->name,
                    'question_set_jobs_count' => count($filtered_jobs->get()),
                    'total_issues' => $total_issues,
                    'critical_issues' => $critical_issues,
                ];
                //$question_set_jobs_completeds = $question_set_jobs_completeds_copy;
            } else {
                // Handle error
            }

            // TODO :  A retiré , un probleme survient après le changemet de contexte de $question_set_jobs_completeds !!
            $startDate = date('Y-m-d 00:00');
            $endDate = date('Y-m-d 23:59');

            $status_id = App\Models\Status::COMPLETED;
            $question_set_jobs_completeds  = App\Models\QuestionSetJob::where('status_id',$status_id);

            if(!empty($request->get('filter_periode'))){
                $tab = explode(' - ',$request->get('filter_periode'));
                $datedeb = date('Y-m-d 00:00',strtotime(str_replace('/','-',$tab[0])));
                $datefin = date('Y-m-d 23:59',strtotime(str_replace('/','-',$tab[1])));

                //dd($datedeb,$datefin);
                $question_set_jobs_completeds->where('start_at','>=', $datedeb);
                $question_set_jobs_completeds->where('end_at','<=', $datefin);
            }

            $locations_ids = array();
            if(!empty($request->get('location_id'))){
                $locations_ids = $request->get('location_id');
                $question_set_jobs_completeds = $question_set_jobs_completeds->whereIn('location_id',$locations_ids);
            }
            //$question_set_jobs_completeds2 = $question_set_jobs_completeds;
            // TODO :  A retiré , un probleme survient après le changemet de contexte de $question_set_jobs_completeds !!
        }

    ?>
    @foreach($results as $result)
        <?php
            $question_set_jobs = $result['question_set_jobs']->get();
            $cpt = 0;
            $avg_score = 0;
            foreach ($question_set_jobs as $question_set_job) {
                $cpt++;
                $possible_score = $question_set_job->answers->sum('possible_score');
                if($possible_score>0){
                    //$score  =  number_format($question_set_job->getTotalScore()*100/$possible_score, 2,',', ' ');
                    $avg_score +=  $question_set_job->getTotalScore()*100/$possible_score;
                }
            }
            //$avg_score  =  number_format($avg_score/$cpt, 2,',', ' ').'%';
            $avg_score  =  $cpt!=0?number_format($avg_score/$cpt, 2,',', ' ').'%':'0%';
        ?>
        <tr style="background-color: #AEB6BF">
            <td style="background-color: #AEB6BF;height: 20px; border: 1px solid #000000;font-size: 13px;">{{ $result['name'] }}</td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right"></td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right">{{ $result['question_set_jobs_count'] }}</td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right">{{ $result['total_issues'] }}</td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right">{{ $result['critical_issues'] }}</td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right">{{$avg_score}}</td>
            <td style="background-color: #AEB6BF;border: 1px solid #000000;font-size: 13px; text-align: right"></td>
        </tr>
        @foreach($question_set_jobs as $question_set_job)
            <?php
                $question_set = $question_set_job->question_set;

                $question_set_jobs_ids = [$question_set_job->id];

                $result =  App\Models\QuestionSetJob::computeIssues($question_set_jobs,$question_set_jobs_ids);
                $total_issues = $result['total_issues'];
                $critical_issues = $result['critical_issues'];

            ?>
            <tr>
                <th scope="row"></th>
                <td style="height: 20px; border: 1px solid #000000;font-size: 13px;">
                    {{ $question_set->title }}
                </td>
                <td style="border: 1px solid #000000;font-size: 13px; text-align: right">{{count($question_set_jobs_ids) }}</td>
                <td style="border: 1px solid #000000;font-size: 13px; text-align: right">{{ $total_issues }}</td>
                <td style="border: 1px solid #000000;font-size: 13px; text-align: right">{{ $critical_issues }}</td>
                <td style="border: 1px solid #000000;font-size: 13px;text-align: right">{{$question_set_job->getScorePercentText()}}</td>
                <td style="border: 1px solid #000000;font-size: 13px; text-align: right">{{$question_set_job->getDecision()}}</td>
            </tr>
        @endforeach
    @endforeach
    </tbody>

    <tfoot>
    <tr style="background-color: #cccccc">

    </tr>
    </tfoot>
</table>

</body>
</html>
