<?php

namespace App\Http\Controllers\Admin;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use DB;
use Session;
use App\Jury;
use Illuminate\Http\Request;

class JuryController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\View\View
     */
    public function index(Request $request)
    {
        $keyword = $request->get('search');
        $perPage = 25;

        if (!empty($keyword)) {
            $jury = Jury::where(['annee_acad' => app('App\Http\Controllers\AuthController')->current_annee_academic()])
                ->orWhere('option', 'LIKE', "%$keyword%")
                ->orWhere('president', 'LIKE', "%$keyword%")
                ->orWhere('maitre_memoire', 'LIKE', "%$keyword%")
                ->orWhere('membre_1', 'LIKE', "%$keyword%")
                ->orWhere('membre_2', 'LIKE', "%$keyword%")
                ->orWhere('id_etudiant', 'LIKE', "%$keyword%")
                ->orWhere('sujet', 'LIKE', "%$keyword%")
                ->orWhere('id_salle', 'LIKE', "%$keyword%")
                ->orWhere('date', 'LIKE', "%$keyword%")
                ->orWhere('heure_debut', 'LIKE', "%$keyword%")
                ->latest()->paginate($perPage);
        } else {
            $jury = Jury::where(['annee_acad' => app('App\Http\Controllers\AuthController')->current_annee_academic()])
                ->latest()->paginate($perPage);
        }
        //dd($jury);
        foreach ($jury as $item) {
            // get president
            $president = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
                ->where(['id'=> $item->president])->get();
            if (sizeof($president) >0) {
                $item->president_nom = $president[0]->nom;
                $item->president_prenom = $president[0]->prenoms;
            }

            // get maitre_memoire
            $maitre_memoire = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
                ->where(['id'=> $item->maitre_memoire])->get();
            if (sizeof($president) >0) {
                $item->maitre_memoire_nom = $maitre_memoire[0]->nom;
                $item->maitre_memoire_prenom = $maitre_memoire[0]->prenoms;
            }

            // get members
            $item->membres = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')
                ->join('gestjury_membre_jury', ['gestjury_membre_jury.id'=> 'gestjury_jury_membre_jury.id_membre'] )
                ->select(['gestjury_membre_jury.nom', 'gestjury_membre_jury.prenoms', 'gestjury_membre_jury.id'])
                ->where(['gestjury_jury_membre_jury.id_jury'=> $item->id])->get();

            // get etudiants
            $item->etudiants = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')
                ->join('tabetudiant', ['tabetudiant.matricule'=> 'gestjury_jury_etudiant.id_etudiant'])
                //->join('tabinscription', ['tabinscription.matricule'=> 'gestjury_jury_etudiant.id_etudiant'])
                ->join('gestjury_jury', ['gestjury_jury.id'=> 'gestjury_jury_etudiant.id_jury'])
                //->join('filieres', ['tabinscription.idFiliere'=> 'filieres.idFiliere'])
                ->select([
                    'tabetudiant.nom',
                    'tabetudiant.prenom',
                    'tabetudiant.matricule',
                    //'filieres.idFiliere',
                    //'filieres.idDepartement'
                ])->where([
                    'gestjury_jury_etudiant.id_jury'=> $item->id,
                    'gestjury_jury.annee_acad' => app('App\Http\Controllers\AuthController')->current_annee_academic()//,
                    //'tabinscription.annee_acad' => app('App\Http\Controllers\AuthController')->current_annee_academic()
                ])->get();
            // get salle
            //dd($item);
            $salle = DB::connection(session::get('gestjury_database'))->table('gestjury_salle')
                ->where(['id'=> $item->id_salle])->get();
            if (sizeof($salle) >0) {
                $item->salle = $salle[0]->nom;
            }

            // get salle
            $filiere = DB::connection(session::get('gestjury_database'))->table('filieres')
                ->where(['idFiliere'=> $item->id_filiere])->get();
            if (sizeof($filiere) >0) {
                $item->filiere = $filiere[0]->intituleFilieres;
                $item->idDepartement = $filiere[0]->idDepartement;
            }

            // get signataire
            $signataire = DB::connection(session::get('gestjury_database'))
                ->table('gestjury_signataires')
                ->join('africawebstudent_universite.fonction as fonction', ['fonction.idFonction'=> 'gestjury_signataires.fonction'])
                ->where(['id'=> $item->signataire])->get();
            if (sizeof($signataire) > 0) {
                $item->signataire_grade = $signataire[0]->grade;
                $item->signataire_nom = $signataire[0]->nom;
                $item->signataire_prenoms = $signataire[0]->prenoms;
                $item->signataire_fonction = $signataire[0]->fonction;
            }
        }
        /*$filieres = DB::connection(session::get('gestjury_database'))->table('filieres')
            ->where(['annee_acad' => app('App\Http\Controllers\AuthController')->current_annee_academic()])
            ->get();*/
        $filieres = DB::connection(session::get('gestjury_database'))->table('filieres')
            ->get();
        $departements = DB::connection(session::get('gestjury_database'))->table('departement')->get();

        return view('admin.jury.index', compact('jury'))->with([
            'departements'=>$departements,
            'filieres' => $filieres
        ]);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\View\View
     */
    public function create()
    {
        $filieres = DB::connection(session::get('gestjury_database'))->table('filieres')->get();
        return view('admin.jury.create')->with([
            'filieres' => $filieres,
            'type' => 'select-filiere',
        ]);
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     *
     * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
     */
    public function store(Request $request)
    {
        $requestData = $request->all();
        $annee = 0;
        if($requestData['id_niveau'] == 2 || $requestData['id_niveau'] == 0) {
            $annee = 2;
        }else{
            $annee = 3;
        }
        if ($requestData['_type'] == "select-filiere") {
            $membres_jury = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')->get();
            $filieres = DB::connection(session::get('gestjury_database'))->table('filieres')->get();
            $signataires = DB::connection(session::get('gestjury_database'))->table('gestjury_signataires')->get();
            # select only sttudents who didn't have a jury for that year for the filiere
            $etudiants = DB::connection(session::get('gestjury_database'))->table('tabetudiant')
                ->join('tabinscription','tabinscription.matricule','=','tabetudiant.matricule')
                ->where([
                    'idFiliere'=> $requestData['id_filiere'],
                    'annee'=> $annee,
                    'annee_acad'=> app('App\Http\Controllers\AuthController')->current_annee_academic()
                    ])
                ->get();
            $etudiants_sans_jury = array();
            foreach ($etudiants as $etudiant) {
                $already_have_jury = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')
                                    ->where(['id_etudiant'=> $etudiant->matricule])->exists();
                if (!$already_have_jury) {
                    array_push($etudiants_sans_jury, $etudiant);
                }
            }
            $salles = DB::connection(session::get('gestjury_database'))->table('gestjury_salle')->get();
            return view('admin.jury.create')->with([
                'membres_jury'=>$membres_jury,
                'filieres' => $filieres,
                'etudiants' => $etudiants_sans_jury,
                'salles' => $salles,
                'signataires' => $signataires,
                'type' => 'submission',
                'niveau' => $requestData['id_niveau'],
                'selected_filiere' => $requestData['id_filiere']
            ]);
        }
        // Store request
        $members = array(
            $requestData['president'],
            $requestData['maitre_memoire'],
        );
        $members = array_merge($requestData['membres'], $members);
        $members_unique = array_unique($members);
        if (sizeof($members_unique) === sizeof($members)) {
            if ($requestData['verification_grade'] == 'oui') {
                # verification si le president est le plus grade
                $grades = array();
                $president_grade = 0;
                foreach ($members as $member_id) {
                    $niveauGrade = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
                    ->join('grade', ['grade.idGrade'=> 'gestjury_membre_jury.grade'] )
                    ->where(['gestjury_membre_jury.id'=> $member_id])->value('niveauGrade');
                    if($member_id == $requestData['president']) {
                        $president_grade = $niveauGrade;
                    }
                    array_push($grades, $niveauGrade);
                }
                //dd($grades);
                if ($president_grade < max($grades)) {
                    return response()->json(array('status'=>'warning', 'message'=>'
                        Attention! Le président n\'est pas le plus haut gradé. Si vous voulez toujours continuer, vous
                        pouvez sélectionner l\'option "non" dans le champ "Verification grade" en bas.'));
                }
            }
            //dd($requestData);
            if ($requestData['verification_conflicts'] == 'oui') {
                // Président
                $conflict_president1 = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
                    ->where(['date'=> $requestData['date']])
                    ->where(['president'=> $requestData['president']])
                    ->where(['heure_debut'=> $requestData['heure_debut']])
                    ->select(['gestjury_jury.id'])
                    ->get();
                    
                $conflict_president2 = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
                    ->join('gestjury_jury_membre_jury', ['gestjury_jury_membre_jury.id_jury'=> 'gestjury_jury.id'] )
                    ->where(['gestjury_jury.date'=> $requestData['date'], 'gestjury_jury_membre_jury.id_membre'=> $requestData['president'],'gestjury_jury.heure_debut'=> $requestData['heure_debut']  ])
                    ->select(['gestjury_jury.id'])
                    ->get();
                    
                    //dd($conflict_president2);
                if (sizeOf($conflict_president1) != 0) {
                    return response()->json(array('status'=>'warning', 'message'=>'
                        Attention! Le président est déja président dans un autre jury le même jour à la même heure.
                        Si vous voulez toujours continuer, vous
                        pouvez sélectionner l\'option "non" dans le champ "Verification conficts en bas."
                    ', 'id_jury_conflituel' => $conflict_president1[0]->id));
                }
                if (sizeOf($conflict_president2) != 0) {
                    return response()->json(array('status'=>'warning', 'message'=>'
                        Attention! Le président est déja membre de jury dans un autre jury le même jour à la même.
                        Si vous voulez toujours continuer, vous
                        pouvez sélectionner l\'option "non" dans le champ "Verification conflicts en bas.'
                        , 'id_jury_conflituel' => $conflict_president2[0]->id));
                }
                // Membres
                foreach ($members as $member_id) {
                    $member = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
                        ->where(['id'=> $member_id])->get();
                        if (sizeOf($member) != 0) {
                            $member = $member[0];
                            $conflict1 = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
                                ->where(['date'=> $requestData['date']])
                                ->where(['president'=> $member->id])
                                ->where(['heure_debut'=> $requestData['heure_debut']])
                                ->select(['gestjury_jury.id'])
                                ->get();
                            $conflict2 = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
                                ->join('gestjury_jury_membre_jury', ['gestjury_jury_membre_jury.id_jury'=> 'gestjury_jury.id'] )
                                ->where(['gestjury_jury.date'=> $requestData['date'], 'gestjury_jury_membre_jury.id_membre'=> $member->id,'gestjury_jury.heure_debut'=> $requestData['heure_debut'] ])
                                ->select(['gestjury_jury.id'])
                                ->get();
                            if (sizeOf($conflict1) != 0) {
                                return response()->json(array('status'=>'warning', 'message'=>'
                                    Attention! Le membre "'.$member->nom.' '.$member->prenoms.'" est déja président dans un autre jury le même jour à la même heure.
                                    Si vous voulez toujours continuer, vous
                                    pouvez sélectionner l\'option "non" dans le champ "Verification conflicts en bas."
                                ', 'id_jury_conflituel' => $conflict1[0]->id));
                            }
                            if (sizeOf($conflict2) != 0) {
                                return response()->json(array('status'=>'warning', 'message'=>'
                                    Attention! Le membre "'.$member->nom.' '.$member->prenoms.'"  est déja membre de jury dans un autre jury le même jour à la même heure.
                                    Si vous voulez toujours continuer, vous
                                    pouvez sélectionner l\'option "non" dans le champ "Verification conflicts en bas.'
                                    , 'id_jury_conflituel' => $conflict2[0]->id));
                            }
                        }
                }
                // Salle
                $conflict_salle = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
                    ->join('gestjury_salle', ['gestjury_salle.id'=> 'gestjury_jury.id_salle'] )
                    ->where(['gestjury_jury.date'=> $requestData['date'], 'gestjury_jury.id_salle'=> $requestData['id_salle'] ,'gestjury_jury.heure_debut'=> $requestData['heure_debut']])
                    ->select(['gestjury_jury.id'])
                    ->get();
                    //dd($conflict_salle);
                if (sizeOf($conflict_salle) != 0) {
                    return response()->json(array('status'=>'warning', 'message'=>'
                        Attention! Une autre soutenance se déroulera dans cette salle le même jour à la même heure.
                        Si vous voulez toujours continuer, vous
                        pouvez sélectionner l\'option "non" dans le champ "Verification conficts en bas."
                    ', 'id_jury_conflituel' => $conflict_salle[0]->id));
                }
            }
            //dd($requestData['id_niveau']);
            $id_jury = DB::connection(session::get('gestjury_database'))->table('gestjury_jury')->insertGetId([
                'id_filiere' => $requestData['id_filiere'], 
                'president'=> $requestData['president'],
                'maitre_memoire'=> $requestData['maitre_memoire'],
                'sujet'=> $requestData['sujet'],
                'id_salle'=> $requestData['id_salle'],
                'date'=> $requestData['date'],
                'signataire'=> $requestData['signataire'],
                'heure_debut'=> $requestData['heure_debut'],
                'type_numero'=> $requestData['type_numero'],
                'niveauSoutenance'=> $requestData['id_niveau'],
                'annee_acad'=> app('App\Http\Controllers\AuthController')->current_annee_academic()
            ]);
            # create etudiants
            foreach ($requestData['id_etudiants'] as $id_etudiant) {
                DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')->insert([
                    'id_jury' => $id_jury,
                    'id_etudiant' => $id_etudiant, 
                ]);
            }
            # create members
            foreach ($requestData['membres'] as $membre) {
                DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')->insert([
                    'id_jury' => $id_jury,
                    'id_membre' => $membre, 
                ]);
            }
            return response()->json(array('status'=>'success', 'message'=>'Jury bien créé.'));
        }
        return response()->json(array('status'=>'error', 'message'=>'Vous devez choisir differents membres du Jury.'));
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     *
     * @return \Illuminate\View\View
     */
    public function show($id)
    {
        $jury = $this->getJury($id);
        return view('admin.jury.show', compact('jury'));
    }

    public function getJury($id) {
        $jury = Jury::findOrFail($id);
        // get president
        $president = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
            ->where(['id'=> $jury->president])->get();
        if (sizeof($president) >0) {
            $president_g = DB::connection(session::get('gestjury_database'))->table('grade')
            ->where(['idGrade'=> $president[0]->grade])->get();
            $jury->president_nom = $president[0]->nom;
            $jury->president_prenom = $president[0]->prenoms;
            $jury->president_description = $president[0]->description;
            $jury->president_grade = $president_g[0]->sigleGrade;
        }

        // get maitre_memoire
        $maitre_memoire = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
            ->where(['id'=> $jury->maitre_memoire])->get();
        if (sizeof($president) >0) {
            $maitre_g = DB::connection(session::get('gestjury_database'))->table('grade')
            ->where(['idGrade'=> $maitre_memoire[0]->grade])->get();
            $jury->maitre_memoire_nom = $maitre_memoire[0]->nom;
            $jury->maitre_memoire_prenom = $maitre_memoire[0]->prenoms;
            $jury->maitre_memoire_description = $maitre_memoire[0]->description;
            $jury->maitre_memoire_grade = $maitre_g[0]->sigleGrade;
            
        }

        // get members
        $jury->membres = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')
            ->join('gestjury_membre_jury', ['gestjury_membre_jury.id'=> 'gestjury_jury_membre_jury.id_membre'] )
            ->select(['gestjury_membre_jury.nom', 'gestjury_membre_jury.prenoms', 'gestjury_membre_jury.id', 'gestjury_membre_jury.description', 'gestjury_membre_jury.grade'])
            ->where(['gestjury_jury_membre_jury.id_jury'=> $jury->id])->get();

        // get etudiants
        $jury->etudiants = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')
            ->join('tabetudiant', ['tabetudiant.matricule'=> 'gestjury_jury_etudiant.id_etudiant'] )
            ->select(['tabetudiant.nom', 'tabetudiant.prenom', 'tabetudiant.matricule', 'tabetudiant.sexe'])
            ->where(['gestjury_jury_etudiant.id_jury'=> $jury->id])->get();
        
        // get salle
        $salle = DB::connection(session::get('gestjury_database'))->table('gestjury_salle')
            ->where(['id'=> $jury->id_salle])->get();
        if (sizeof($salle) >0) {
            $jury->salle = $salle[0]->nom;
        }

        // get filiere
        $filiere = DB::connection(session::get('gestjury_database'))->table('filieres')
            ->where(['idFiliere'=> $jury->id_filiere])->get();
        if (sizeof($filiere) >0) {
            $jury->filiere = $filiere[0]->intituleFilieres;
        }

        // get signataire
        $signataire = DB::connection(session::get('gestjury_database'))
            ->table('gestjury_signataires')
            ->join('africawebstudent_universite.fonction as fonction', ['fonction.idFonction'=> 'gestjury_signataires.fonction'])
            ->where(['id'=> $jury->signataire])->get();
        if (sizeof($signataire) > 0) {
            $jury->signataire_grade = $signataire[0]->grade;
            $jury->signataire_nom = $signataire[0]->nom;
            $jury->signataire_prenoms = $signataire[0]->prenoms;
            $jury->signataire_fonction = $signataire[0]->nomFonction;
        }

        // get ville
        $etablissement = DB::connection(session::get('gestjury_database'))
            ->table('filieres')
            ->join('departement', ['departement.idDepartement'=> 'filieres.idDepartement'] )
            ->join('etablissement', ['departement.idEtablissement'=> 'etablissement.idEtablissement'] )
            ->join('universite', ['universite.idUniversite'=> 'etablissement.idUniversite'] )
            ->where(['idFiliere'=> $jury->id_filiere])
            ->select([
                'etablissement.ville',
                'etablissement.sigleeta',
                'etablissement.nameimgeta',
                'etablissement.adresse',
                'etablissement.descreta',
                'departement.desrdept',
                'departement.sigledept',
                'universite.sigleuni',
                'universite.nameimguni'
            ])
            ->get();
        if (sizeof($etablissement) >0) {
            $jury->ville = $etablissement[0]->ville;
            $jury->sigleeta = $etablissement[0]->sigleeta;
            $jury->descreta = $etablissement[0]->descreta;
            $jury->nameimgeta = $etablissement[0]->nameimgeta;
            $jury->desrdept = $etablissement[0]->desrdept;
            $jury->adresse = $etablissement[0]->adresse;
            $jury->sigledept = $etablissement[0]->sigledept;
            $jury->sigleuni = $etablissement[0]->sigleuni;
            $jury->nameimguni = $etablissement[0]->nameimguni;
        }

        // date 
        $date = date($jury->date); //date('Y-m-d'); // Date du jour
        $date = strftime("%A %d %B %G", strtotime($date));
        setlocale(LC_TIME, "fr_FR");
        $jury->date = $this->date_to_french($date);
        $jury->note_service_creation_date = $this->date_to_french(
            strftime("%A %d %B %G", strtotime($jury->created_at))
        );
        return $jury;
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     *
     * @return \Illuminate\View\View
     */
    public function edit($id)
    {
        $membres_jury = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')->get();
        $filieres = DB::connection(session::get('gestjury_database'))->table('filieres')->get();
        $etudiants = DB::connection(session::get('gestjury_database'))->table('tabetudiant')->get();
        $signataires = DB::connection(session::get('gestjury_database'))->table('gestjury_signataires')->get();
        $salles = DB::connection(session::get('gestjury_database'))->table('gestjury_salle')->get();

        $jury = Jury::findOrFail($id);
        //dd($jury);

        $jury->membres = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')
            ->join('gestjury_membre_jury', ['gestjury_membre_jury.id'=> 'gestjury_jury_membre_jury.id_membre'] )
            ->select(['gestjury_membre_jury.id'])
            ->where(['gestjury_jury_membre_jury.id_jury'=> $jury->id])->get();

        // get etudiants
        $jury->etudiants = DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')
            ->join('tabetudiant', ['tabetudiant.matricule'=> 'gestjury_jury_etudiant.id_etudiant'] )
            ->select(['tabetudiant.matricule'])
            ->where(['gestjury_jury_etudiant.id_jury'=> $jury->id])->get();

        $membres_ids = array();
        foreach ($jury->membres as $membre) {
            array_push($membres_ids, $membre->id);
        }
        $jury->membres_ids = $membres_ids;
        $etudiants_matricules = array();
        foreach ($jury->etudiants as $etudiant) {
            array_push($etudiants_matricules, $etudiant->matricule);
        }
        $jury->etudiants_matricules = $etudiants_matricules;
        return view('admin.jury.edit', compact('jury'))->with([
            'membres_jury'=>$membres_jury,
            'filieres' => $filieres,
            'etudiants' => $etudiants,
            'salles' => $salles,
            'membres_ids' => $membres_ids,
            'niveau' => $jury->niveauSoutenance,
            'etudiants_matricules' => $etudiants_matricules,
            'signataires' => $signataires,
        ]);
    }

    /**
     * Update the specified resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @param  int  $id
     *
     * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
     */
    public function update(Request $request, $id)
    {
        
        $requestData = $request->all();
        
        $jury = Jury::findOrFail($id);

        $members = array(
            $requestData['president'],
            $requestData['maitre_memoire'],
        );
        $members = array_merge($requestData['membres'], $members);
        $members_unique =  array_unique($members);
        if (sizeof($members_unique) === sizeof($members)) {
            
            if ($requestData['verification_grade'] == 'oui') {
                # verification si le president est le plus grade
                $grades = array();
                $president_grade = 0;
                foreach ($members as $member_id) {
                    $niveauGrade = DB::connection(session::get('gestjury_database'))->table('gestjury_membre_jury')
                    ->join('grade', ['grade.idGrade'=> 'gestjury_membre_jury.grade'] )
                    ->where(['gestjury_membre_jury.id'=> $member_id])->value('niveauGrade');
                    if($member_id == $requestData['president']) {
                        $president_grade = $niveauGrade;
                    }
                    array_push($grades, $niveauGrade);
                }
                //dd($grades);
                if ($president_grade < max($grades)) {
                    return response()->json(array('status'=>'warning', 'message'=>'
                        Attention! Le président n\'est pas le plus haut gradé. Si vous voulez toujours continuer, vous
                        pouvez sélectionner l\'option "non" dans le champ "Verification grade" en bas.'));
                }
            }

            
            DB::connection(session::get('gestjury_database'))->table('gestjury_jury')
            ->where(['id'=> $jury->id])
            ->update([
                'id_filiere' => $requestData['id_filiere'], 
                'president'=> $requestData['president'],
                'maitre_memoire'=> $requestData['maitre_memoire'],
                'sujet'=> $requestData['sujet'],
                'id_salle'=> $requestData['id_salle'],
                'date'=> $requestData['date'],
                'signataire'=> $requestData['signataire'],
                'heure_debut'=> $requestData['heure_debut'],
                'type_numero'=> $requestData['type_numero'],
                'niveauSoutenance'=> $requestData['id_niveau'],
                'annee_acad'=> app('App\Http\Controllers\AuthController')->current_annee_academic()
            ]);

            # delete all etudiants and jury
            DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')
                ->where(['gestjury_jury_membre_jury.id_jury'=> $jury->id])->delete();
            DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')
                ->where(['gestjury_jury_etudiant.id_jury'=> $jury->id])->delete();
            
            # create them now
            foreach ($requestData['id_etudiants'] as $id_etudiant) {
                DB::connection(session::get('gestjury_database'))->table('gestjury_jury_etudiant')->insert([
                    'id_jury' => $jury->id,
                    'id_etudiant' => $id_etudiant, 
                ]);
            }
            # create members
            foreach ($requestData['membres'] as $membre) {
                DB::connection(session::get('gestjury_database'))->table('gestjury_jury_membre_jury')->insert([
                    'id_jury' => $jury->id,
                    'id_membre' => $membre, 
                ]);
            }
            return response()->json(array('status'=>'success', 'message'=>'Jury modifié!'));
        }
        return response()->json(array('status'=>'error', 'message'=>'Vous devez choisir differents membres du Jury.'));
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     *
     * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
     */
    public function destroy($id)
    {
        Jury::destroy($id);

        return redirect('admin/jury')->with('success', 'Jury supprimé!');
    }

    public function date_to_french($date)
    {
        $translations_months = array(
            "January" => "Janvier",
            "February" => "Fevrier",
            "March" => "Mars",
            "April" => "Avril",
            "May" => "Mai",
            "June" => "Juin",
            "July" => "Juillet",
            "August " => "Aout",
            "September " => "Septembre",
            "October" => "Octobre",
            "November" => "Novembre",
            "December" => "Decembre",
        );
        foreach ($translations_months as $key => $month) {
            $pattern = '/'.$key.'/i';
            $date =  preg_replace($pattern, $month." ", $date);
        }
        $translations_days = array(
            "Monday" => "Lundi",
            "Tuesday" => "Mardi",
            "Wednesday" => "Mercredi",
            "Thursday" => "Jeudi",
            "Friday" => "Vendredi",
            "Saturday" => "Samedi",
            "Sunday" => "Dimanche",
        );
        foreach ($translations_days as $key => $day) {
            $pattern = '/'.$key.'/i';
            $date =  preg_replace($pattern, $day." ", $date);
        }
        return $date;
    }
}
