<?php

namespace App\Http\Controllers;

use App\Exports\ExportDossier;
use App\Models\Brand;
use App\Models\City;
use App\Models\Company;
use App\Models\Document;
use App\Models\Dossier;
use App\Models\DossierStatus;
use App\Models\Garage;
use App\Models\Guarantee;
use App\Models\Intermidiaire;
use App\Models\Mode;
use App\Models\Payment;
use App\Models\PieceType;
use App\Models\Role;
use App\Models\TypeExpertise;
use App\Models\TypeReparation;
use App\Models\User;
use App\Models\VehiculeModel;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ChartController extends Controller
{
    public function index()
    {
        $anomalie = Dossier::where('dossier_status_id', 1)->count();
        $encours = Dossier::where('dossier_status_id', 2)->count();
        $terminer = Dossier::where('dossier_status_id', 3)->count();
        // dd($anomalie,$encours,$terminer);
        $companies = Company::select('company')->whereHas('dossier')->withCount('dossier')->get();
        $gestionnaires = User::select('name')->with('step')->get();
        $chiffreur = User::select('name')->where('role_id', 2)->with('step')->get();
        $companie = array();
        $nbr = array();
        foreach ($companies as $key) {
            array_push($companie, $key->company);
            array_push($nbr, $key->dossier_count);
        }
        $user = User::with('step')->withCount('step')->get();
        $roles = Role::all();
        $step = DB::select(DB::raw('select distinct u.name,count(distinct s.dossier_id) as nbr from  step_dossier s join users u on s.user_id = u.id
            where role_id = 1 group by user_id'));
        $users = array();
        $nbrD = array();
        foreach ($step as $key) {
            array_push($users, $key->name);
            array_push($nbrD, $key->nbr);
        }
        $types = DB::select(DB::raw('select distinct u.type,count( s.type_expertise_id) as nbr from  dossiers s join type_expertises u on s.type_expertise_id = u.id
        group by type_expertise_id'));
        // dd($type);
        $type = array();
        $nbrt = array();
        foreach ($types as $key) {
            array_push($type, $key->type);
            array_push($nbrt, $key->nbr);
        }
        // $payer = Payment::with('dossier')->where('note_honoraire_status_id', 1)->count();
        // $total = Payment::count();
        // $percent = $payer / $total * 100;


        $result = DB::select(DB::raw(" SELECT  type_reparations.type AS r  ,COUNT(DISTINCT pieces.id) AS piece FROM rapport_piece
        INNER JOIN type_reparations ON type_reparations.id = rapport_piece.type_reparation_id
        INNER JOIN pieces ON pieces.id = rapport_piece.piece_id
        GROUP BY r

         "));
        $r = array();
        $piece = array();
        foreach ($result as $key) {
            array_push($piece, $key->piece);
            array_push($r, $key->r);
        }

        // $query = DB::select(DB::raw(" SELECT  note_honoraire_statuses.status AS note  ,COUNT(DISTINCT payments.id) AS payments FROM payments
        //     INNER JOIN note_honoraire_statuses ON payments.note_honoraire_status_id = note_honoraire_statuses.id
        //     GROUP BY note
        //      "));
        // $note = array();
        // $payments = array();
        // foreach ($query as $key) {
        //     array_push($payments, $key->payments);
        //     array_push($note, $key->note);
        // }

        $query = DB::select(DB::raw(" SELECT  intermidiaires.company AS inter  ,COUNT(DISTINCT dossiers.id) AS dossier FROM dossiers
                INNER JOIN intermidiaires ON intermidiaires.id = dossiers.intermidiaire_id
                GROUP BY inter
                 "));
        $inter = array();
        $dossier = array();
        foreach ($query as $key) {
            array_push($dossier, $key->dossier);
            array_push($inter, $key->inter);
        }

        return view('configuration.chart.index', compact('anomalie', 'dossier', 'inter', 'r', 'piece', 'terminer', 'encours', 'companie', 'nbr', 'roles', 'users', 'nbrD', 'type', 'nbrt'));
    }


    public function range(Request $request)
    {
        $anomalie = Dossier::where('dossier_status_id', 1)->whereBetween('created_at', [$request->from, $request->to])->count();
        $encours = Dossier::where('dossier_status_id', 2)->whereBetween('created_at', [$request->from, $request->to])->count();
        $terminer = Dossier::where('dossier_status_id', 3)->whereBetween('created_at', [$request->from, $request->to])->count();
        // dd($anomalie,$encours,$terminer);
        $companies = Company::select('company')->whereHas('dossier')->withCount('dossier')->whereBetween('created_at', [$request->from, $request->to])->get();
        $gestionnaires = User::select('name')->with('step')->whereBetween('created_at', [$request->from, $request->to])->get();
        $chiffreur = User::select('name')->where('role_id', 2)->whereBetween('created_at', [$request->from, $request->to])->with('step')->get();
        $companie = array();
        $nbr = array();
        foreach ($companies as $key) {
            array_push($companie, $key->company);
            array_push($nbr, $key->dossier_count);
        }
        $user = User::with('step')->withCount('step')->get();
        $roles = Role::all();
        $step = DB::select(DB::raw('select distinct u.name,count(distinct s.dossier_id) as nbr from  step_dossier s join users u on s.user_id = u.id
            where role_id = 1  group by user_id'));
        $users = array();
        $nbrD = array();
        foreach ($step as $key) {
            array_push($users, $key->name);
            array_push($nbrD, $key->nbr);
        }
        $types = DB::select(DB::raw('select distinct u.type,count( s.type_expertise_id) as nbr from  dossiers s join type_expertises u on s.type_expertise_id = u.id
        group by type_expertise_id'));
        // dd($type);
        $type = array();
        $nbrt = array();
        foreach ($types as $key) {
            array_push($type, $key->type);
            array_push($nbrt, $key->nbr);
        }
        // $payer = Payment::with('dossier')->where('note_honoraire_status_id', 1)->count();
        // $total = Payment::count();
        // $percent = $payer / $total * 100;
        return view('configuration.chart.index', compact('anomalie', 'terminer', 'encours', 'companie', 'nbr', 'roles', 'users', 'nbrD', 'type', 'nbrt'));
    }

    public function userdossier($id)
    {
        $step = DB::select(DB::raw('select distinct u.name,count(distinct s.dossier_id) as nbr from  step_dossier s join users u on s.user_id = u.id
            where role_id = ' . $id . ' group by user_id'));
        // dd($step);
        return $step;
    }

    public function export(Request $request)
    {
        $excel = Excel::download(new ExportDossier($request), 'dossiers.xlsx');
        return response()->download($excel, 'dossiers.xlsx');
    }

    public function charts()
    {
        $companies = Company::all();
        $guarantees = Guarantee::all();
        $modes = Mode::all();
        $typeexpertises = TypeExpertise::all();
        $intermidiaires = Intermidiaire::all();
        $brands = Brand::all();
        $garages = Garage::all();
        $models = VehiculeModel::where('brand_id', 1)->get();
        $cities = City::all();
        $users = User::all();
        $modes = Mode::all();
        $typepieces = TypeReparation::all();
        $cities = City::all();
        $statuses = DossierStatus::all();

        return view('configuration.chart.bar', compact('companies', 'cities', 'statuses', 'guarantees', 'modes', 'typeexpertises', 'intermidiaires', 'brands', 'garages', 'models', 'cities', 'users', 'typepieces', 'modes'));
    }



    public function stats(Request $request)
    {
        // $request->validate([
        //     'from' => 'required|date',
        //     'to' => 'required|date',
        //     'formule' => 'required'
        // ]);
        $company = $request->company ?? '';
        $intermidiaire = $request->intermidiaire ?? '';
        $typeexpertise_ = $request->typeexpertise ?? '';
        $user = $request->user ?? '';
        $mode = $request->mode ?? '';
        $garage = $request->garage ?? '';
        $guarantee_id = $request->guarantee ?? '';
        $vehicule_model_id = $request->vehicule_model_id ?? '';
        $company_op = $request->company_op ?? '';
        $city = $request->city_id ?? '';
        $status = $request->statut ?? '';
        $update = $request->update ?? '';
       

        $from = $request->from;
        $to = $request->to;

        if ($request->submitindex == 1) {
            return Excel::download(new ExportDossier($request), 'dossiers.xlsx');
        } else {
            if ($request->formule) {

                if ($request->formule == "d1") {

                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(  DATEDIFF( dossiers.created_at,dossiers.date_reception  )) AS TIMEDIFF FROM dossiers
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$update' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                 GROUP BY m
                 Order By m
                 "));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de création du dossier Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d2") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( DATEDIFF(documents.created_at, dossiers.created_at)) AS TIMEDIFF FROM dossiers
                inner join documents on documents.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE documents.type_document_id = 5
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m"));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de réception des photos avant réparation Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d3") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( DATEDIFF(devis.date_reception, dossiers.date_reception)) AS TIMEDIFF FROM dossiers
                inner join devis on devis.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE devis.devis = 1
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m"));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de réception du devis Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d4") {
                    $result = DB::select(DB::raw("SELECT  MONTH(meetings.created_at) AS m ,AVG( ABS( DATEDIFF(devis.date_reception, meetings.created_at))) AS TIMEDIFF FROM dossiers
                inner join devis on devis.dossier_id = dossiers.id
                inner join meetings on meetings.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND meetings.type = 2
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m"));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de réception du devis (Mission avec meeting) Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d5") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( ABS(DATEDIFF(meetings.created_at, dossiers.date_reception))) AS TIMEDIFF FROM dossiers
                inner join meetings on meetings.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND meetings.type = 2
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m"));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai d envoi du meeting avant réparation Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d6") {
                    $result = DB::select(DB::raw("SELECT DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,AVG( DATEDIFF(meetings.date_fin, meetings.created_at)) AS TIMEDIFF FROM dossiers inner join meetings on meetings.dossier_id = dossiers.id INNER JOIN opponents ON opponents.id=dossiers.opponent_id INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND meetings.meeting_status_id = 3 and meetings.type=2
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m
                "));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai d expertise avant réparation  Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }

                if ($request->formule == "d7") {
                    $result = DB::select(DB::raw("SELECT DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m, AVG(TIMESTAMPDIFF(HOUR, d2.created_at, d1.created_at)) AS TIMEDIFF FROM dossiers INNER JOIN devis ON devis.dossier_id = dossiers.id 
                    INNER JOIN devis_status AS d1 ON d1.devis_id = devis.id AND d1.devis_status_id = 2 
                    INNER JOIN devis_status AS d2 ON d2.devis_id = devis.id AND d2.devis_status_id = 4 
                    INNER JOIN opponents ON opponents.id = dossiers.opponent_id 
                    INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id 
                    WHERE (devis.devis_status_id = 2 OR devis.devis_status_id = 7)
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                GROUP BY m
                 Order By m
                "));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de traitement accord par Heure';
                    return response()->json([$avg, $month, $d, $type]);
                }
                // if ($request->formule == "d8") {
                //     $result = DB::select(DB::raw('SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DATEDIFF(devis_status.created_at, devis.created_at)) AS TIMEDIFF FROM dossiers
                //     inner join devis on devis.dossier_id = dossiers.id
                //     INNER JOIN devis_status ON devis_status.devis_id =devis.id
                //      WHERE devis.devis_status_id = 2 OR devis.devis_status_id = 7
                //      AND devis_status.devis_status_id = 2
                //     AND YEAR(dossiers.created_at) = YEAR(CURDATE())
                //     GROUP BY m
                //Order By m
                //     '));
                //     $month = array();
                //     $avg = array();
                //     foreach ($result as $key) {
                //         array_push($avg, $key->TIMEDIFF);
                //         array_push($month, $key->m);
                //     }
                //     $d = "d6";
                //     return view('configuration.chart.result', compact('month', 'avg', 'd'));
                // }
                if ($request->formule == "d9") {
                    $result = DB::select(DB::raw(" SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( DATEDIFF(documents.created_at,devis_status.created_at)) AS TIMEDIFF
                FROM dossiers
                INNER JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN documents ON dossiers.id = documents.dossier_id
                INNER JOIN devis_status ON devis.id = devis_status.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE devis.devis_status_id =7
                AND documents.type_document_id=6
                AND devis_status.devis_status_id = 7
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;

                    $d = 'Délai de réparation Par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d10") {
                    $result = DB::select(DB::raw(" SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( DATEDIFF(factures.created_at,devis_status.created_at)) AS TIMEDIFF
                FROM dossiers
                INNER JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN factures ON dossiers.id = factures.dossier_id
                INNER JOIN devis_status ON devis.id = devis_status.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE (devis.devis_status_id = 7 OR devis.devis_status_id = 2)
                AND devis_status.devis_status_id = 7
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));
                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai de réception de la facture par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d11") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(ABS( DATEDIFF(documents.created_at,facture_status.created_at))) AS TIMEDIFF
                FROM dossiers
                INNER JOIN factures ON dossiers.id = factures.dossier_id
                INNER JOIN facture_status ON factures.id = facture_status.facture_id
                INNER JOIN documents ON dossiers.id = documents.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE  factures.devis_status_id = 2
                AND documents.type_document_id = 6
                AND facture_status.devis_status_id = 2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai de réception photos après réparation par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }

                if ($request->formule == "d12") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( TIMESTAMPDIFF(HOUR,meetings.created_at,meetings.date_fin)) AS TIMEDIFF
                FROM dossiers
                INNER JOIN meetings ON dossiers.id = meetings.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE meetings.type = '10'
                AND meetings.meeting_status_id = 3
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai d envoi d expertise après réparation par Heure';
                    return response()->json([$avg, $month, $d, $type]);
                }

                if ($request->formule == "d13") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( DATEDIFF(documents.created_at,devis_status.created_at)) AS TIMEDIFF
                FROM dossiers
                INNER JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN documents ON dossiers.id = documents.dossier_id
                INNER JOIN devis_status ON devis.id = devis_status.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE documents.type_document_id = 19
                AND devis_status.devis_status_id = 7
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai de réception des Arréts de valeur par Jour';
                    return response()->json([$avg, $month, $d, $type]);
                }

                if ($request->formule == "d14") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(ABS( TIMESTAMPDIFF(HOUR,rapports.created_at,facture_status.created_at))) AS TIMEDIFF
                FROM dossiers
                INNER JOIN factures ON dossiers.id = factures.dossier_id
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN facture_status ON factures.id = facture_status.facture_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE (factures.devis_status_id=2 or factures.devis_status_id=7)
                AND facture_status.devis_status_id = 2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai d etablissement rapport Par Heure';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d15") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( ABS(TIMESTAMPDIFF(HOUR,r2.created_at , r1.created_at)))  AS TIMEDIFF
                FROM dossiers
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN rapport_status r1 ON rapports.id = r1.rapport_id
                INNER JOIN rapport_status r2 ON rapports.id = r2.rapport_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE r1.rapport_status_id = 4 AND r2.rapport_status_id =2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai de validation rapport par Heure';
                    return response()->json([$avg, $month, $d, $type]);
                }
                if ($request->formule == "d16") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( ABS(TIMESTAMPDIFF(HOUR,r2.created_at , r1.created_at))) AS TIMEDIFF
                FROM dossiers
                INNER JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN devis_status r1 ON devis.id = r1.devis_id
                INNER JOIN devis_status r2 ON devis.id = r2.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE r1.devis_status_id = 2 AND r2.devis_status_id = 7
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $avg = array();
                    foreach ($result as $key) {
                        array_push($avg, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $type = 1;
                    $d = 'Délai d envoi d accord/avis dommage par Heure';
                    return response()->json([$avg, $month, $d, $type]);
                }
                //end delai

                //start Ecart
                if ($request->formule == "d17") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( devis.montant_devis) AS Mdevis ,AVG( montants.montant_accord) AS Accord
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                INNER JOIN devis ON devis.id = montants.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE (devis.devis_status_id =7 OR devis.devis_status_id =2)
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->Accord);
                        array_push($devis, $key->Mdevis);
                        array_push($month, $key->m);
                    }
                    $label1 = 'montant devis';
                    $label2 = 'montant accord';
                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }
                if ($request->formule == "d18") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_accord) AS Mdevis ,AVG(DISTINCT montants.montant_rapport) AS Rapport
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $rapport = array();

                    foreach ($result as $key) {
                        array_push($rapport, $key->Rapport);
                        array_push($devis, $key->Mdevis);
                        array_push($month, $key->m);
                    }
                    $type = 2;
                    $label1 = 'montant devis';
                    $label2 = 'montant rapport';
                    return response()->json([$devis, $rapport, $month, $type, $label1, $label2]);
                }


                if ($request->formule == "d19") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_accord) AS Mdevis ,AVG(DISTINCT factures.montant_facture) AS facture
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                 INNER JOIN factures ON factures.id = montants.facture_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE (factures.devis_status_id = 7 OR factures.devis_status_id = 2)
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->facture);
                        array_push($devis, $key->Mdevis);
                        array_push($month, $key->m);
                    }
                    $label1 = 'montant devis accord';
                    $label2 = 'montant facture ';
                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }

                if ($request->formule == "d20") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT devis.montant_devis) AS Mdevis ,AVG(DISTINCT factures.montant_facture) AS facture
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                 INNER JOIN factures ON factures.id = montants.facture_id
                  INNER JOIN devis ON devis.id = montants.devis_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->facture);
                        array_push($devis, $key->Mdevis);
                        array_push($month, $key->m);
                    }
                    $label1 = 'montant devis ';
                    $label2 = 'montant facture ';
                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }
                if ($request->formule == "d21") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_rapport) AS rapport
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                 INNER JOIN rapports ON rapports.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE (rapports.rapport_status_id = 2 OR  rapports.rapport_status_id = 3)
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen sinistre';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d22") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT devis.montant_devis) AS rapport
                FROM dossiers
                INNER JOIN devis ON devis.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen devis';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d23") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_accord) AS rapport
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen accord';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d24") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_rapport) AS rapport ,AVG(DISTINCT contradictoires.adverse_montant_accord) AS adverse
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                INNER JOIN contradictoires ON dossiers.id = contradictoires.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                        array_push($devis, $key->adverse);
                    }
                    $label1 = 'montant adverse';
                    $label2 = 'montant rapport';
                    $d = 0;
                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }

                if ($request->formule == "d25") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT montants.montant_rapport) AS rapport ,AVG(DISTINCT contradictoires.arbitre_montant_accord) AS arbitre
                FROM dossiers
                INNER JOIN montants ON dossiers.id = montants.dossier_id
                INNER JOIN contradictoires ON dossiers.id = contradictoires.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                        array_push($devis, $key->arbitre);
                    }
                    $label1 = 'montant arbitre';
                    $label2 = 'montant rapport';

                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }

                if ($request->formule == "d26") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT contradictoires.adverse_montant_accord) AS adverse
                FROM dossiers
                INNER JOIN contradictoires ON dossiers.id = contradictoires.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->adverse);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen expert adverse';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d27") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT contradictoires.arbitre_montant_accord) AS adverse
                FROM dossiers
                INNER JOIN contradictoires ON dossiers.id = contradictoires.dossier_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen expert arbitre';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d28") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT factures.montant_facture) AS facture
                FROM dossiers
                INNER JOIN factures ON factures.id = factures.dossier_id
                INNER JOIN garages ON garages.id = dossiers.garage_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->facture);
                        array_push($month, $key->m);
                    }
                    $d = "Coût moyen réparateur";
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d29") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( rapport_piece.prix_unitaire) AS rapport
                FROM dossiers
                INNER JOIN garages ON garages.id = dossiers.garage_id
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN rapport_piece ON rapports.id = rapport_piece.rapport_id
                INNER JOIN pieces ON pieces.id = rapport_piece.piece_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE rapport_piece.type_reparation_id = 1
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen pièce RECUP';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d30") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( rapport_piece.prix_unitaire) AS rapport
                FROM dossiers
                INNER JOIN garages ON garages.id = dossiers.garage_id
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN rapport_piece ON rapports.id = rapport_piece.rapport_id
                INNER JOIN pieces ON pieces.id = rapport_piece.piece_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE rapport_piece.type_reparation_id = 2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = "Coût moyen pièce Adaptable";
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d31") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG( rapport_piece.prix_unitaire) AS rapport
                FROM dossiers
                INNER JOIN garages ON garages.id = dossiers.garage_id
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN rapport_piece ON rapports.id = rapport_piece.rapport_id
                INNER JOIN pieces ON pieces.id = rapport_piece.piece_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE rapport_piece.type_reparation_id = 3
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();
                    foreach ($result as $key) {
                        array_push($accord, $key->rapport);
                        array_push($month, $key->m);
                    }
                    $d = 'Coût moyen pièce Origine';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d32") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT DATEDIFF(rapport_status.created_at, dossiers.date_reception)) AS TIMEDIFF
                FROM dossiers
                INNER JOIN garages ON garages.id = dossiers.garage_id
                INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN rapport_status ON rapports.id = rapport_status.rapport_id
                INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE rapport_status.rapport_status_id = 2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = "Delai Traitement Dossier Par Jour";
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d33") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,COUNT(*) AS TIMEDIFF FROM dossiers
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
               WHERE dossiers.sinistre_douteux = 1
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre de dossiers douteux';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                // yassine chof hna
                if ($request->formule == "d34") {
                    $result =  DB::table('dossiers')
                    ->select(DB::raw("DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m"), DB::raw("COUNT(*) AS TIMEDIFF"))
                    ->whereBetween('dossiers.created_at', [$from, $to])
                    ->where(function ($query) use ($company, $vehicule_model_id, $guarantee_id, $typeexpertise_, $mode, $intermidiaire, $user, $garage, $company_op, $city, $status) {
                        if ($company !== '') $query->where('dossiers.company_id', $company);
                        if ($vehicule_model_id !== '') $query->where('dossiers.vehicule_model_id', $vehicule_model_id);
                        if ($guarantee_id !== '') $query->where('dossiers.guarantee_id', $guarantee_id);
                        if ($typeexpertise_ !== '') $query->where('dossiers.type_expertise_id', $typeexpertise_);
                        if ($mode !== '') $query->where('dossiers.mode_id', $mode);
                        if ($intermidiaire !== '') $query->where('dossiers.intermidiaire_id', $intermidiaire);
                        if ($user !== '') $query->where('step_dossier.user_id', $user);
                        if ($garage !== '') $query->where('dossiers.garage_id', $garage);
                        if ($company_op !== '') $query->where('opponents.company_id', $company_op);
                        if ($city !== '') $query->where('dossiers.city_id', $city);
                        if ($status !== '') $query->where('dossiers.dossier_status_id', $status);
                        if ($update !== '') $query->where('dossiers.update', $update);
                    })
                    ->groupBy('m')
                    ->orderBy('m')
                    ->get();

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre de dossiers';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d35") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,COUNT(DISTINCT devis.id) AS TIMEDIFF FROM dossiers
                INNER JOIN devis ON devis.dossier_id = dossiers.id
                 INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                 INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre de devis /2eme devis / 3eme devis';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d36") {
                    $result = DB::select(DB::raw("SELECT DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,COUNT(DISTINCT devis.id) AS TIMEDIFF FROM dossiers 
                    INNER JOIN devis ON devis.dossier_id = dossiers.id inner join devis_status on devis_status.devis_id = devis.id 
                    INNER JOIN opponents ON opponents.id = dossiers.opponent_id 
                    INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id 
                    WHERE (devis.devis_status_id = 7 OR devis.devis_status_id = 2) 
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, devis_status.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre d accord/2eme accord / 3eme accord';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d37") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,COUNT(DISTINCT rapports.id) AS TIMEDIFF FROM dossiers

                INNER JOIN rapports ON rapports.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre rapport';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d38") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y') AS m ,COUNT(DISTINCT factures.id) AS TIMEDIFF FROM dossiers

                INNER JOIN factures ON factures.dossier_id = dossiers.id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre de facture';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d39") {
                    $result = DB::select(DB::raw("SELECT  dossiers.ref AS m ,COUNT(DISTINCT documents.id) AS TIMEDIFF FROM dossiers

                INNER JOIN documents ON documents.dossier_id = dossiers.id
                INNER JOIN type_documents ON type_documents.id = documents.type_document_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre de photos par dossier';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d40") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS count , AVG(DATEDIFF(dossiers.date_fin , dossiers.created_at)) AS avg FROM dossiers
                  INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.dossier_status_id = 3
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($devis, $key->count);
                        array_push($accord, $key->avg);
                        array_push($month, $key->m);
                    }
                    $label1 = 'Nombre dossier';
                    $label2 = 'Delai /j';


                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }

                if ($request->formule == "d41") {

                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS count , AVG(DATEDIFF(NOW() , dossiers.created_at)) AS avg FROM dossiers
                  INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE dossiers.dossier_status_id = 2
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($devis, $key->count);
                        array_push($accord, $key->avg);
                        array_push($month, $key->m);
                    }
                    $label1 = 'Nombre dossier';
                    $label2 = 'Delai /j';


                    $type = 2;
                    return response()->json([$devis, $accord, $month, $type, $label1, $label2]);
                }

                if ($request->formule == "d42") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                INNER JOIN documents ON dossiers.id = documents.dossier_id

                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE documents.type_document_id != 1 AND documents.type_document_id != 2  AND documents.type_document_id != 3
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre mission en attente docs de base';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }
                if ($request->formule == "d43") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                INNER JOIN documents ON dossiers.id = documents.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE documents.type_document_id != 5
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre mission en attente expertise';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d44") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                LEFT JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE devis.dossier_id IS NULL
                AND dossiers.created_at between '$from' and '$to'
                AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                    AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre mission en attente devis';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d45") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT devis.id) AS TIMEDIFF FROM dossiers
                LEFT JOIN devis ON dossiers.id = devis.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE devis.devis_status_id != 7 AND devis.devis_status_id != 2
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = "Nombre mission en attente accord";
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d46") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                INNER JOIN documents ON dossiers.id = documents.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE documents.type_document_id != 6
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = "Nombre mission en attente expertise après réparation";
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d47") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                LEFT JOIN factures ON dossiers.id = factures.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE factures.dossier_id IS NULL
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre mission en attente facture ';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d48") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m , COUNT(DISTINCT dossiers.id) AS TIMEDIFF FROM dossiers
                LEFT JOIN rapports ON dossiers.id = rapports.dossier_id
                INNER JOIN opponents ON opponents.id = dossiers.opponent_id
                INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                WHERE rapports.dossier_id IS NULL
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Nombre mission en attente rapport';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d49") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT DATEDIFF(d2.created_at, ds1.created_at)) AS TIMEDIFF FROM dossiers
                    inner join devis d1 on d1.dossier_id = dossiers.id
                    inner join devis d2 on d2.dossier_id = dossiers.id
                    inner join devis_status ds1 on ds1.devis_id = d1.id
                    INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                    INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                 WHERE d1.devis  = 1 AND d2.devis = 2
                 AND ds1.devis_status_id = 7
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Delai de reception du 2éme devis Par Jour';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d50") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT DATEDIFF( d1.created_at,d2.created_at)) AS TIMEDIFF FROM dossiers
                    inner join devis  on devis.dossier_id = dossiers.id
                    INNER JOIN devis_status AS d1 ON d1.devis_id = devis.id AND d1.devis_status_id = 2 INNER JOIN devis_status AS d2 ON d2.devis_id = devis.id AND d2.devis_status_id = 4
                    INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                    INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                    WHERE  devis.devis = 2
                    AND (devis.devis_status_id = 2 or devis.devis_status_id = 7)
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Delai de traitement du 2éme accord';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d51") {
                    $result = DB::select(DB::raw("SELECT  DATE_FORMAT(dossiers.created_at, '%m-%Y')  AS m ,AVG(DISTINCT DATEDIFF( d2.created_at,d1.created_at)) AS TIMEDIFF FROM dossiers
                    inner join devis  on devis.dossier_id = dossiers.id
                    inner join devis_status d1  on d1.devis_id = devis.id AND d1.devis_status_id = 2
                    inner join devis_status d2  on d2.devis_id = devis.id AND d2.devis_status_id = 7
                    INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                    INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                    WHERE  devis.devis = 2
                    AND d1.devis_status_id = 2  AND d2.devis_status_id = 7
                 AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')
                Group BY m
                 Order By m
                "));

                    $month = array();
                    $devis = array();
                    $accord = array();

                    foreach ($result as $key) {
                        array_push($accord, $key->TIMEDIFF);
                        array_push($month, $key->m);
                    }
                    $d = 'Delai d envoi du 2éme devis';
                    $type = 1;
                    return response()->json([$accord, $month, $d, $type]);
                }

                if ($request->formule == "d52") {
                    $result = DB::select(DB::raw("SELECT  SUM(DISTINCT rapport_piece.montant_ht) AS pieces
                    ,SUM(DISTINCT COALESCE(tauxes.taux_1*tauxes.t1 ,0) + COALESCE(tauxes.taux_2 * tauxes.t2 ,0) + COALESCE(tauxes.taux_3 *t3 ,0) ) AS mo
                    ,SUM( DISTINCT COALESCE(tauxes.taux_ing_opaque ,0) + COALESCE(tauxes.taux_ing_opaque_vernis ,0) + COALESCE(tauxes.taux_ing_metal ,0)
                    + COALESCE(tauxes.taux_ing_metal_vernis ,0) + COALESCE(tauxes.taux_ing_nacre_vernis ,0)
                    + COALESCE(tauxes.forfait_mo_1,0)+ COALESCE(tauxes.forfait_mo_2,0)+ COALESCE(tauxes.forfait_mo_3,0)
                    +  COALESCE(tauxes.forfait_ing_pieces,0) ) AS ing , SUM( DISTINCT COALESCE(tauxes.taux_mo_peinture*tp,0)) AS p
                      FROM dossiers
                            INNER JOIN rapports ON dossiers.id = rapports.dossier_id
                            INNER JOIN montants ON montants.dossier_id = dossiers.id
                            INNER JOIN tauxes ON rapports.taux_id = tauxes.id
                            INNER JOIN rapport_piece ON rapport_piece.rapport_id = rapports.id
                            INNER JOIN opponents ON opponents.id=dossiers.opponent_id
                            INNER JOIN step_dossier ON step_dossier.dossier_id = dossiers.id
                            WHERE (rapports.rapport_status_id = 2 OR rapports.rapport_status_id = 3)
                            AND dossiers.created_at between '$from' and '$to'
                 AND IF('$company' = '', 1, dossiers.company_id = '$company')
                 AND IF('$vehicule_model_id' = '', 1, dossiers.vehicule_model_id = '$vehicule_model_id')
                 AND IF('$guarantee_id' = '', 1, dossiers.guarantee_id = '$guarantee_id')
                 AND IF('$typeexpertise_' = '', 1, dossiers.type_expertise_id = '$typeexpertise_')
                 AND IF('$mode' = '', 1, dossiers.mode_id = '$mode')
                 AND IF('$intermidiaire' = '', 1, dossiers.intermidiaire_id = '$intermidiaire')
                 AND IF('$user' = '', 1, step_dossier.user_id = '$user')
                 AND IF('$garage' = '', 1, dossiers.garage_id = '$garage')
                 AND IF('$company_op' = '', 1, opponents.company_id  = '$company_op')
                 AND IF('$city' = '', 1, dossiers.city_id = '$city')
                 AND IF('$status' = '', 1, dossiers.dossier_status_id = '$status')
                 AND IF('$update' = '', 1, dossiers.update = '$update')

                "));

                    $fourniture = 0;
                    $peinture = 0;
                    $total = 0;
                    $mo = 0;

                    foreach ($result as $key) {
                        $fourniture = $key->pieces + $key->ing;
                        $peinture = $key->p;
                        $mo = $key->mo;
                        $total = $mo + $fourniture  + $peinture;
                    }
                    $mo = ($mo / $total) * 100;
                    $peinture = ($peinture / $total) * 100;
                    $fourniture = ($fourniture / $total) * 100;

                    $d = 'Cout de Reparation';
                    $type = 5;
                    return response()->json([$mo, $peinture, $fourniture, $type, $d]);
                }
            }
        }
    }
}
