<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class DemandeController extends Controller
{
    public function demande_en_cours(){
        $ncc = session('ncc');
        $demandes = DB::select("SELECT demande.*, type_attestation.libelle,type_certificat.libelle as certif FROM demande,type_attestation,type_certificat WHERE demande.type_attestation=type_attestation.num_type AND demande.certificat=type_certificat.code_type AND sta in (0,1)");
        $attestation = DB::select("SELECT * FROM type_attestation");
        $certificat = DB::select("SELECT certificat.*,type_certificat.libelle FROM certificat,type_certificat WHERE certificat.type_certificat=type_certificat.code_type AND ncc='$ncc' AND sta=0");
        return view("demandesencours")->with("liste_demande",$demandes)->with("liste_type",$attestation)->with("liste_certificat",$certificat);
    }

    public function demande_terminees(){
        $demandes = DB::select("SELECT demande.*, type_attestation.libelle,type_certificat.libelle as certif FROM demande,type_attestation,type_certificat WHERE demande.type_attestation=type_attestation.num_type AND demande.certificat=type_certificat.code_type AND sta=2");
        return view("demandesterminee")->with("liste_demande",$demandes);
    }

    public function demandes(){
        $demandes = DB::select("SELECT demande.*, type_attestation.libelle,type_certificat.libelle as certif FROM demande,type_attestation,type_certificat WHERE demande.type_attestation=type_attestation.num_type AND demande.certificat=type_certificat.code_type AND sta=3");
        return view("demandes")->with("liste_demande",$demandes);
    }

    public function ajout_demande(Request $request){
        $date_dem = date("Y-m-d");
        $y = date("Y");
        $m = date("m");
        $type_att = $request->type_att;
        $certificat = $request->certificat;
        $demandeur = $request->nom_complet;
        $nb_fact = $request->d;
        $sta = 0;

        $get_id = DB::select("SELECT count(id_demande) as id FROM demande")[0];
        $id = $get_id->id;
        $new = $id+1;
        $id_demande = $y.$m.'_'.sprintf('%03d',$new);

        $ajout = DB::select("INSERT INTO demande VALUES(?,?,?,?,?,?,?)",array($id_demande,$date_dem,$type_att,$certificat,$demandeur,$nb_fact,$sta));

        $verif = DB::select("SELECT * FROM demande WHERE id_demande='$id_demande'");

        if(count($verif)>0){
            for ($i=0; $i<$nb_fact; $i++){
                $NFactureArr=$request->n_fact[$i];
                $DateArr=$request->date_fact[$i];
                $FourArr=$request->ncc_fact[$i];
                $ObjetArr = $request->objet_fact[$i];
                $MTHArr = $request->montht_fact[$i];
                $TVAArr = $request->monttva_fact[$i];
                $FactureArr = $request->facturename[$i];

                $n_fact=$NFactureArr;
                $date=$DateArr;
                $fournisseur=$FourArr;
                $objet = $ObjetArr;
                $mont_ht = $MTHArr;
                $mont_tva = $TVAArr;
                $facture = $FactureArr;                      
                $id_facture = $id_demande.'-'.$n_fact;
                $preuve = $id_facture.'.pdf';
                $request->facture[$i]->move('pdf',$preuve);

                $aj = DB::insert("INSERT INTO facture VALUES(?, ?, ?, ?, ?, ?, ?, ?)", array($id_facture,$n_fact,$date,$fournisseur,$objet,$mont_ht,$mont_tva,$id_demande));
            }
            return response()->json([
                array('success' => true), 
                'status'=>200,
            ]);
        }
    }

    public function get_demande(Request $request){
        $id_demande = $request->id;
        $ncc = session('ncc');
        $output = '';
        $get_demande = DB::select("SELECT * FROM demande WHERE id_demande='$id_demande'");
        $get_facture = DB::select("SELECT * FROM facture WHERE id_demande='$id_demande'");

        foreach($get_demande as $row){
            $type_attestation = $row->type_attestation;
            $certificat = $row->certificat;
            $nb_facture = $row->nb_facture;
        }

        $attestation = DB::select("SELECT * FROM type_attestation WHERE num_type!='$type_attestation'");

        $type = DB::select("SELECT libelle FROM type_attestation WHERE num_type='$type_attestation'")[0];
        $lib_att = $type->libelle;

        $type_cert = DB::select("SELECT libelle FROM type_certificat WHERE code_type='$certificat'")[0];
        $lib_cert = $type_cert->libelle;
        
        $liste_certificat = DB::select("SELECT certificat.*,type_certificat.libelle FROM certificat,type_certificat WHERE certificat.type_certificat=type_certificat.code_type AND ncc='$ncc' AND code_type != '$certificat' AND sta=0");

        $output .= '    
                        <div class="form-groups" id="exportateur">

                            <span><label for="exampleInputEmail3">Informations du demandeur</label></span>

                            <div class="form-group">
                                <label>Nom complet</label>
                                <input type="text" name="nom_complet1" id="nom_complet1" class="form-control" value="'.session('utilisateur').'" readonly>
                            </div>
                            
                            <div class="form-group" style="columns: 2;">
                                <label>N°cc</label>
                                <input type="text" name="ncc1" id="ncc1" class="form-control" value="'.session('ncc').'" readonly>

                                <label>Nom contribuable</label>
                                <input type="text" name="contribuable1" id="contribuable1" class="form-control" value="'.session('nc').'" readonly>
                            </div>
                                    
                        </div>

                        <div class="form-groups" id="exportateur">

                            <span><label for="exampleInputEmail3">Informations de la demande</label></span>

                            <div class="form-group">
                                <label for="exampleInputEmail1">Type d\'attestation</label>
                                <select class="form-control select2" style="width: 100%;" name="type_att1" id="type_att1">
                                    <option value="'.$type_attestation.'">'.$lib_att.'</option>';
                                    foreach($attestation as $type){
        $output .= '                   <option value="'.$type->num_type.'">'.$type->libelle.'</option>';
                                    }
        $output .= '            </select>
                            </div>

                            <div class="form-group">
                                <label for="exampleInputEmail1">Certificat</label>
                                <select class="form-control select2" style="width: 100%;" name="certificat1" id="certificat1">
                                    <option value="'.$certificat.'">'.$lib_cert.'</option>';
                                    foreach($liste_certificat as $type){
        $output .= '                   <option value="'.$type->type_certificat.'">'.$type->libelle.'</option>';
                                    }
        $output .= '            </select>
                            </div>

                        </div>
                        <div class="form-groups" id="liste_facture1">
                            <span><label for="exampleInputEmail3">Factures proforma</label></span>';
                    foreach($get_facture as $facture){
        $output .=      '
                                        <div class="form-group" id="box'.$facture->id_facture.'"><hr>
                                        <input type="hidden" name="id_facture[]" id="id_facture" class="form-control" value="'.$facture->id_facture.'" required>
                                        <div class="form-group" style="columns: 2;">
                                            <label>N° Facture</label>
                                            <input type="text" name="n_fact1[]" id="n_fact1" class="form-control" value="'.$facture->num_facture.'" required>     
                                            
                                            <label>Date facture</label>
                                            <input type="date" name="date_fact1[]" id="date_fact1" class="form-control" value="'.$facture->dates.'" required>                                                
                                        </div>
                                        
                                        <div class="form-group">
                                            <label>ncc Fournisseur</label>
                                            <input type="text" name="ncc_fact1[]" id="ncc_fact1" class="form-control" value="'.$facture->ncc_fournisseur.'" required>                                           
                                        </div>

                                        <div class="form-group" >
                                            <label>Objet</label>
                                            <input type="text" name="objet_fact1[]" id="objet_fact1" class="form-control" value="'.$facture->objet.'" required> 
                                        </div>

                                        <div class="form-group" style="columns: 2;">
                                            <label>Montant HT</label>
                                            <input type="number" name="montht_fact1[]" id="montht_fact1" class="form-control" value="'.$facture->mont_ht.'" required>  
                                            
                                            <label>Montant TVA</label>
                                            <input type="number" name="monttva_fact1[]" id="monttva_fact1" class="form-control" value="'.$facture->mont_tva.'" required>                                           
                                        </div>
                                                
                                        <div class="form-group">
                                            <label for="exampleInputFile">Joindre une facture proforma</label>
                                            <div class="input-group">                                                 
                                                <input type="text" name="facturename1[]" id="facturename1" value="'.$facture->id_facture.'.pdf" class="form-control" placeholder="Aucun fichier choisi" required>
                                                <div class="custom-file">
                                                    <input type="file" name="facture1[]" class="custom-file-input" id="InputFile1" >
                                                    <label class="custom-file-label" id="nomfacture1" for="exampleInputFile">'.$facture->id_facture.'.pdf</label>
                                                </div>
                                                <div class="input-group-append">
                                                    <span id="btn-eye1" class="input-group-text" style="background-color: #16B84E; border-color: #16B84E; border: 1px;"><i class="fas fa-eye" aria-hidden="true" style="color: #ffffff;"></i></span>
                                                </div>
                                            </div>
                                            <div class="form-group">
                                                <button type="button" class="btn badge-danger mr-2" onclick=remove_more1("'.$facture->id_facture.'")><i class="bi-trash h4" style="color: #fff;"></i></button>
                                            </div>
                                        </div>
                                    </div>
                                    ';
                    }
            $output .= '<input type="hidden" name="d1" id="d1" value="'.$nb_facture.'" /></div>';
        return response()->json($output);
    }

    public function get_facture(Request $request){
        $id_demande = $request->id;
        $output = '';
        $get_facture = DB::select("SELECT * FROM facture WHERE id_demande='$id_demande'");

        $nb_facture = count($get_facture);

        $output .= '<div class="form-groups" id="liste_facture1">
                            <span><label for="exampleInputEmail3">Liste des factures à joindre</label></span>';
                    foreach($get_facture as $facture){
        $output .=      '
                                        <div class="form-group" id="box'.$facture->id_facture.'"><hr>
                                        <input type="hidden" name="id_facture1[]" id="id_facture1" class="form-control" value="'.$facture->id_facture.'" required>
                                        <div class="form-group">
                                            <label>N° Facture</label>
                                            <input type="text" name="n_fact2[]" id="n_fact2" class="form-control" value="'.$facture->num_facture.'" readonly>                                                  
                                        </div>
                                                
                                        <div class="form-group">
                                            <label for="exampleInputFile">Joindre une facture</label>
                                            <div class="input-group">                                                 
                                                <input type="text" name="facturename2[]" id="facturename2'.$facture->id_facture.'" class="form-control" placeholder="Aucun fichier choisi" required>
                                                <div class="custom-file">
                                                    <input type="file" name="facture2[]" class="custom-file-input" id="facture2'.$facture->id_facture.'" onchange=printer3("'.$facture->id_facture.'")>
                                                    <label class="custom-file-label" id="nomfacture2'.$facture->id_facture.'" for="exampleInputFile">Aucun fichier choisi</label>
                                                </div>
                                                <div class="input-group-append">
                                                    <span id="btn-eye1" class="input-group-text" style="background-color: #16B84E; border-color: #16B84E; border: 1px;"><i class="fas fa-eye" aria-hidden="true" style="color: #ffffff;"></i></span>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    ';
                    }
            $output .= '<input type="hidden" name="d2" id="d2" value="'.$nb_facture.'" /></div>';
        return response()->json($output);
    }

    public function delete_facture(Request $request){
        $id = $request->id;
        $delete = DB::select("DELETE FROM facture WHERE id_facture='$id'");

        $deletefile = public_path('pdf/').$id.'.pdf';

        if(unlink($deletefile)){
            $verif = DB::select("SELECT * FROM facture WHERE id_facture='$id'");

            if(count($verif)>0){
                return response()->json([
                    array('success' => true), 
                    'status'=>100,
                ]);
            }else{
                return response()->json([
                    array('success' => true), 
                    'status'=>200,
                ]);
            }
        }


    }

    public function modificationDemande(Request $request){
        $id= $request->id;
        $type_att= $request->type_att1;
        $certif = $request->certificat1;
        $nmbre=$request->d1;
        $a=0;
        $b=0;

        $trans = DB::update("UPDATE demande SET type_attestation='$type_att', certificat='$certif', nb_facture='$nmbre' WHERE id_demande='$id'");


        $NFactureArr=$request->n_fact1;
        $DateArr=$request->date_fact1;
        $FourArr=$request->ncc_fact1;
        $ObjetArr = $request->objet_fact1;
        $MTHArr = $request->montht_fact1;
        $TVAArr = $request->monttva_fact1;
        $FactureArr = $request->facturename1;
        $FactureFile = $request->facture1;
        $id_fichArr=$request->id_facture;
        for ($i=0; $i<$nmbre; $i++){
                $n_fact=$NFactureArr[$i];
                $date=$DateArr[$i];
                $fournisseur=$FourArr[$i];
                $objet = $ObjetArr[$i];
                $mont_ht = $MTHArr[$i];
                $mont_tva = $TVAArr[$i];
                $facture = $FactureArr[$i];                      
                //$id_facture = $id_fichArr[$i];
            if ($FactureFile[$i] = 'undefined'){

                $id_facture = $id_fichArr[$i];
                $id_demande = $id;
                $verif = DB::select("SELECT * FROM facture WHERE id_facture='$id_facture'"); 
                if(count($verif)>0){
                    $fich = DB::update("UPDATE facture SET num_facture='$n_fact', dates='$date', ncc_fournisseur='$fournisseur', objet='$objet', mont_ht='$mont_ht', mont_tva='$mont_tva' WHERE id_facture='$id_facture'");
                    if($fich){
                        $b++;
                    }
                }else{
                    $id_facture = $id.'-'.$n_fact;
                    $aj = DB::insert("INSERT INTO facture VALUES(?, ?, ?, ?, ?, ?, ?, ?)", array($id_facture,$n_fact,$date,$fournisseur,$objet,$mont_ht,$mont_tva,$id));
                    if($aj){
                        $a++;
                    }
                }
            }else{

                $id_facture = $id.'-'.$n_fact;
                $preuve = $id_facture.'.pdf';
                $request->facture1[$i]->move('pdf',$preuve);
                $id_fich = $id_fichArr[$i];
                $id_cess = $id;
                $verif = DB::select("SELECT * FROM facture WHERE id_facture='$id_fich'"); 
                if(count($verif)>0){
                    $fich = DB::update("UPDATE facture SET num_facture='$n_fact', `date`='$date', ncc_fournisseur='$fournisseur', objet='$objet', mont_ht='$mont_ht', mont_tva='$mont_tva' WHERE id_facture='$id_facture'");
                    if($fich){
                        $b++;
                    }
                }else{
                    $id_facture = $id.'-'.$n_fact;
                    $aj = DB::insert("INSERT INTO facture VALUES(?, ?, ?, ?, ?, ?, ?, ?)", array($id_facture,$n_fact,$date,$fournisseur,$objet,$mont_ht,$mont_tva,$id));
                    if($aj){
                        $a++;
                    }
                }
            }



         }
        

        if ($trans || $a>0 || $b>0){
            return response()->json([
                'status' => 200,
                'b' => $b
            ]);
        }else{
            return response()->json([
                'status' => 100
            ]);
        }
    }

    public function supprimeDemande(Request $request){
        $id = $request->id;
        $sup = DB::delete("DELETE FROM demande WHERE id_demande='$id'");
        if ($sup){
            $supfich = DB::delete("DELETE FROM facture WHERE id_demande='$id'");
            if ($supfich){
                return response()->json([
                    array('success' => true), 
                    'status' => 200
                ]); 
            }
        }
    }


    public function motif_facture(Request $request){

        $id_demande = $request->id;

        $up = DB::select("UPDATE demande SET sta=3 WHERE id_demande = '$id_demande'"); 

        $nmbre=$request->d2;
        $id_fichArr=$request->id_facture1;
        for ($i=0; $i<$nmbre; $i++){                   

            $id_facture = $id_fichArr[$i];
            $preuve = $id_facture.'.pdf';
            $request->facture2[$i]->move('pdf',$preuve);

        }
        
        return response()->json([
            'status' => 200,
        ]);
    }

    public function soumettreDemande(Request $request){
        $id = $request->id;
        $sup = DB::delete("UPDATE demande SET sta=1 WHERE id_demande='$id'");
        if ($sup){
            return response()->json([
                array('success' => true), 
                'status' => 200
            ]); 
        }
    }

        public function show_demande(Request $request){
        $id_demande = $request->id;
        $ncc = session('ncc');
        $output = '';
        $get_demande = DB::select("SELECT * FROM demande WHERE id_demande='$id_demande'");
        $get_facture = DB::select("SELECT * FROM facture WHERE id_demande='$id_demande'");

        foreach($get_demande as $row){
            $type_attestation = $row->type_attestation;
            $certificat = $row->certificat;
            $nb_facture = $row->nb_facture;
        }

        $type = DB::select("SELECT libelle FROM type_attestation WHERE num_type='$type_attestation'")[0];
        $lib_att = $type->libelle;

        $type_cert = DB::select("SELECT libelle FROM type_certificat WHERE code_type='$certificat'")[0];
        $lib_cert = $type_cert->libelle;

        $output .= '    
                        <div class="form-groups" id="exportateur">

                            <span><label for="exampleInputEmail3">Informations du demandeur</label></span>

                            <div class="form-group">
                                <label>Nom complet : <span style="text-align: right; font-weight: lighter;">'.session('utilisateur').'</span></label>
                            </div>
                            
                            <div class="form-group">
                                <label>N°cc: <span style="text-align: right; font-weight: lighter;">'.session('ncc').'</span></label>

                                <label STYLE="padding:0 0 0 40px;">Nom contribuable : <span style="text-align: right; font-weight: lighter;">'.session('nc').'</span></label>
                            </div>
                                    
                        </div>

                        <div class="form-groups" id="exportateur">

                            <span><label for="exampleInputEmail3">Informations de la demande</label></span>

                            <div class="form-group">
                                <label for="exampleInputEmail1">Type d\'attestation : <span style="text-align: right; font-weight: lighter;">'.$lib_att.'</span></label>
                            </div>

                            <div class="form-group">
                                <label for="exampleInputEmail1">Certificat : <span style="text-align: right; font-weight: lighter;">'.$lib_cert.'</span></label>
                            </div>

                        </div>
                        <div class="form-groups" id="liste_facture1">
                            <span><label for="exampleInputEmail3">Factures proforma</label></span>';
                    foreach($get_facture as $facture){
        $output .=      '
                                        <hr>
                                        <div class="form-group">
                                            <label>N° Facture : <span style="text-align: right; font-weight: lighter;">'.$facture->num_facture.'</span></label>
                                            
                                            <label STYLE="padding:0 0 0 60px;">Date facture : <span style="text-align: right; font-weight: lighter;">'.$facture->dates.'</span></label>                                               
                                        </div>
                                        
                                        <div class="form-group">
                                            <label>ncc Fournisseur : <span style="text-align: right; font-weight: lighter;">'.$facture->ncc_fournisseur.'</span></label>                                          
                                        </div>

                                        <div class="form-group" >
                                            <label>Objet : <span style="text-align: right; font-weight: lighter;">'.$facture->objet.'</span></label>
                                        </div>

                                        <div class="form-group">
                                            <label>Montant HT : <span style="text-align: right; font-weight: lighter;">'.$facture->mont_ht.' FCFA</span></label>  
                                            
                                            <label STYLE="padding:0 0 0 40px;">Montant TVA : <span style="text-align: right; font-weight: lighter;">'.$facture->mont_tva.' FCFA</span></label>                                          
                                        </div>
                                                
                                    
                                    ';
                    }
        $output .= '</div>';
        return response()->json($output);
    }
}
