<?php
class Demande_Conge  extends CI_Controller{
    function __construct() {
        parent::__construct();
        $this->Is_Connected();

        $this->load->library('Mylibrary');
        $this->ci = & get_instance();
        $this->ci->load->library("user_agent");

      }

      public function Is_Connected()
       {
       if (empty($this->session->userdata('SOCAR_ID_USER')))
        {
         redirect(base_url('Login/'));
        }
       }

        


  
    public function index()
    {

      $data['title']='Demande Conge';
      $data['responsabilite']=$this->Model->getRequete('SELECT * FROM `rh_responsabilite` order by DESCRIPTION');
      $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
      $data['departement']=$this->Model->getRequete('SELECT * FROM `organigramme_section` order by DESCRIPTION');
      $data['type_conge']=$this->Model->getRequete('SELECT * FROM `config_type_conge` order by NOM_CONGE');
      $data['rh']=$this->Model->getRequete('SELECT * FROM `rh_employe` order by NOM');
      
      $this->load->view('Demande_Conge_Add_View',$data);

    }

    
    public function get_departement()
  {
  $noms= $this->Model->getRequete("SELECT rh_employe.ID_EMPLOYE, rh_employe.NOM, rh_employe.PRENOM FROM `rh_employe_affectation` JOIN rh_employe ON rh_employe.ID_EMPLOYE = rh_employe_affectation.ID_EMPLOYE WHERE rh_employe_affectation.STATUS_AFFECTATION = 1 AND rh_employe_affectation.ID_DIRECTION = ".$this->input->post('ID_DIRECTION')." ");
  $datas= '<option value="">-- Sélectionner --</option>';
  foreach($noms as $nom){
  $datas.= '<option value="'.$nom["ID_EMPLOYE"].'">'.$nom["NOM"].' '.$nom["PRENOM"].'</option>';
  }
  $datas.= '';
  echo $datas;
  }

  

  public function get_service()
  {
  $noms= $this->Model->getRequete("SELECT rh_employe.ID_EMPLOYE, rh_employe.NOM, rh_employe.PRENOM FROM `rh_employe_affectation` JOIN rh_employe ON rh_employe.ID_EMPLOYE = rh_employe_affectation.ID_EMPLOYE WHERE rh_employe_affectation.STATUS_AFFECTATION = 1 AND rh_employe_affectation.ID_SERVICE = ".$this->input->post('ID_SERVICE')." ");
  $datas= '<option value="">-- Sélectionner --</option>';
  foreach($noms as $nom){
  $datas.= '<option value="'.$nom["ID_EMPLOYE"].'">'.$nom["NOM"].' '.$nom["PRENOM"].'</option>';
  }
  $datas.= '';
  echo $datas;
  }

  public function get_employe()
  {
  $noms= $this->Model->getRequete("SELECT rh_employe.ID_EMPLOYE, rh_employe.NOM, rh_employe.PRENOM FROM `rh_employe_affectation` JOIN rh_employe ON rh_employe.ID_EMPLOYE = rh_employe_affectation.ID_EMPLOYE WHERE rh_employe_affectation.STATUS_AFFECTATION = 1 AND rh_employe_affectation.ID_SECTION = ".$this->input->post('ID_SECTION')." ");
  $datas= '<option value="">-- Sélectionner --</option>';
  foreach($noms as $nom){
  $datas.= '<option value="'.$nom["ID_EMPLOYE"].'">'.$nom["NOM"].' '.$nom["PRENOM"].'</option>';
  }
  $datas.= '';
  echo $datas;
  }



  public function get_credit_conge()
  {

    $type_conge= $this->Model->getRequeteOne("SELECT * FROM `config_type_conge` WHERE `ID_TYPE_CONGE` = ".$this->input->post('ID_TYPE_CONGE')." ");

    $datas= '';

    if ($type_conge['ACCORD_A_POSTERIORIE'] == 1 ) {

      $noms= $this->Model->getRequeteOne("SELECT SUM(NB_JOURS_RESTANT) AS NB_JOURS_RESTANT FROM conge_credit WHERE STATUS = 1 AND ID_TYPE_CONGE = ".$this->input->post('ID_TYPE_CONGE')." AND ID_EMPLOYE = ".$this->input->post('ID_EMPLOYE')." ");
      $resulat = $noms['NB_JOURS_RESTANT'];
      $datas.='<label for="exampleInputEmail1">Nb de jours Restant</label> <input type="number" class="form-control" id="SOLDE_NB_JOURS" name="SOLDE_NB_JOURS" value="'.$resulat.'" readonly="readonly">';
    
    }
    elseif ($type_conge['BESOIN_DOCUMENT'] == 1 ) {
          $datas.='<label for="exampleInputEmail1">Documents preuves (PDF)</label> <div class="custom-file"> <input type="file" class="custom-file-input" id="customFile" name="DOCUMENT_PREUVE" accept="application/pdf">  <label class="custom-file-label" for="customFile">Chercher fichier</label>
                    </div>';
        }
    // else{
        

    // }
  

  echo $datas;
  }


    public function add()
    {
           
      $NAVIGATEUR=$this->ci->agent->browser.' version '.$this->ci->agent->version;
      $OS=$this->ci->agent->platform;
      $IP = $_SERVER["REMOTE_ADDR"];
      $dt = new DateTime("now", new DateTimeZone('Africa/Bujumbura'));
      $DATE_TRAITEMENT = $dt->format('Y-m-d H:i:s');
      
      $ID_DIRECTION=$this->input->post('ID_DIRECTION');
      $ID_SECTION=$this->input->post('ID_SECTION');
      $ID_SERVICE=$this->input->post('ID_SERVICE');
      $ID_EMPLOYE=$this->input->post('ID_EMPLOYE');
      $ID_TYPE_CONGE=$this->input->post('ID_TYPE_CONGE');
      $NB_JOURS_DEMANDE=$this->input->post('NB_JOURS_DEMANDE');
      $DATE_DEMANDE=$this->input->post('DATE_DEMANDE');

      // echo $ID_DIRECTION;
      

      $this->form_validation->set_rules('ID_DIRECTION', 'Direction', 'required');
      $this->form_validation->set_rules('ID_EMPLOYE', 'Employ&eacute;', 'required');
      $this->form_validation->set_rules('ID_TYPE_CONGE', 'Type de cong&eacute;', 'required');
      $this->form_validation->set_rules('NB_JOURS_DEMANDE', 'Nb Jours', 'required');
      $this->form_validation->set_rules('DATE_DEMANDE', 'Date', 'required');
      
  

   if ($this->form_validation->run() == FALSE){
    $message = "<div class='alert alert-danger'>
                              Congé non enregistr&eacute;
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
    $data['title']='Demande Conge';
    $data['responsabilite']=$this->Model->getRequete('SELECT * FROM `rh_responsabilite` order by DESCRIPTION');
    $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
    $data['departement']=$this->Model->getRequete('SELECT * FROM `organigramme_section` order by DESCRIPTION');
    $data['type_conge']=$this->Model->getRequete('SELECT * FROM `config_type_conge` order by NOM_CONGE');
    $data['rh']=$this->Model->getRequete('SELECT * FROM `rh_employe` order by NOM');
      
    $this->load->view('Demande_Conge_Add_View',$data);
   }
   else{
    
    // $DATE_ENTRE_SERVICE=date_create($DATE_ENTRE_SERVICE);
    // $DATE_ENTRE_SERVICE = date_format($DATE_ENTRE_SERVICE,"Y-m-d");
    $exercice = $this->Model->getRequeteOne('SELECT * FROM `config_exercice` WhERE STATUS_ACTIF = 1');
    $credit = $this->Model->getRequeteOne('SELECT * FROM `conge_credit` WHERE `ID_EXERCICE` = '.$exercice['ID_EXERCICE'].' AND `ID_EMPLOYE` = '.$ID_EMPLOYE.' ');
    $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$ID_EMPLOYE.' AND `STATUS_AFFECTATION` = 1');
    $type_conge = $this->Model->getRequeteOne('SELECT * FROM `config_type_conge` WHERE `ID_TYPE_CONGE` = '.$ID_TYPE_CONGE.' ');

    $rh=$this->Model->getRequeteOne('SELECT * FROM `rh_employe` WHERE `ID_EMPLOYE` = '.$ID_EMPLOYE.' ');

    if ($affectation['ID_SERVICE'] !=0) {
      

      // if ($affectation['ID_RESPONSABILITE'] ==1) {
      //   $STATUS_CONGE = 5;
      // }
      // else{
        $STATUS_CONGE = 4;
      // }
    }
    elseif ($affectation['ID_SECTION'] !=0) {
      // if ($affectation['ID_RESPONSABILITE'] ==1) {
      //   $STATUS_CONGE = 2;
      // }
      // else{
        $STATUS_CONGE = 1;
      // }
    }
    else{
      // if ($affectation['ID_RESPONSABILITE'] ==1) {
      //   $STATUS_CONGE = 8;
      // }
      // else{
        $STATUS_CONGE = 8;
      // }
    }

    // $DATE_FIN_CONGE_DEMANDE = strtotime( '+5 weekday' );
    // $DATE_FIN_CONGE_DEMANDE = date( 'Y-m-d', $DATE_FIN_CONGE_DEMANDE );


    // $startdate = '2022-05-12';
    $DATE_DEMANDE=date_create($DATE_DEMANDE);
    $DATE_DEMANDE = date_format($DATE_DEMANDE,"Y-m-d");

    $d = new DateTime($DATE_DEMANDE);
    $t = $d->getTimestamp();

    // loop for X days
    for($i=0; $i<$NB_JOURS_DEMANDE-1; $i++){
        // add 1 day to timestamp
        $addDay = 86400;
        // get what day it is next day
        $nextDay = date('w', ($t+$addDay));
        if ($type_conge['JOUR_CALENDAIRE'] == 0) {
          // if it's Saturday or Sunday get $i-1
          if($nextDay == 0 || $nextDay == 6) {
              $i--;
          }
          $ferrier = $this->Model->getRequeteOne('SELECT ID_JOURS_FERIER FROM `config_jours_ferrier` WHERE `DATE_FERIER` = "'.$d->format('Y-m-d').'" ');
          if(!empty($ferrier)) { 
            $i--;
          }
        }
        // modify timestamp, add 1 day
        $t = $t+$addDay;
        $d->setTimestamp($t);
        // echo $nextDay.'-'.$d->format('Y-m-d').'<br>';
    }

    $d->setTimestamp($t);


    $DATE_FIN_CONGE_DEMANDE = $d->format('Y-m-d');

    if ($type_conge['BESOIN_DOCUMENT'] == 1) {
      
    $repPhotoo =FCPATH.'/uploads/document_preuve';
    $documents=uniqid();
    $document_preuve = $_FILES['DOCUMENT_PREUVE']['name'];
    $config['upload_path'] ='./uploads/document_preuve/';
    $config['allowed_types'] = '*';
    // $test = explode('.', $document_preuve);
    // $ext = end($test);
    // $name = $documents.'_document_preuve.' . $ext;
    $name = $document_preuve;
    $config['file_name'] =$name;
    if(!is_dir($repPhotoo)) //create the folder if it does not already exists   
      {
        mkdir($repPhotoo,0777,TRUE);                                                        
      } 

    $this->upload->initialize($config);
    $this->upload->do_upload('DOCUMENT_PREUVE');
    $image_name_main=$config['file_name'];
    $data_image=$this->upload->data();
    if ($image_name_main != NULL) {
      $DOCUMENT_PREUVE = $image_name_main;
    }
    else{
      $DOCUMENT_PREUVE = NULL;
    }
    }
    else{
      $DOCUMENT_PREUVE = NULL;
    }

    




    $data_conge=array(
      'ID_EXERCICE'=>$exercice['ID_EXERCICE'],
      'ID_CREDIT'=>$credit['ID_CREDIT'],
      'ID_EMPLOYE'=>$ID_EMPLOYE,
      'DATE_DEMANDE'=>$DATE_DEMANDE,
      'DATE_DERNIER_REPOSE'=>$DATE_DEMANDE,
      'STATUS_CONGE'=>$STATUS_CONGE,
      'NB_JOURS_DEMANDE'=>$NB_JOURS_DEMANDE,
      'ID_TYPE_CONGE'=>$ID_TYPE_CONGE,
      'ID_DIRECTION'=>$ID_DIRECTION,
      'ID_SECTION'=>$ID_SECTION,
      'ID_SERVICE'=>$ID_SERVICE,
      'DATE_DEBUT_CONGE_DEMANDE'=>$DATE_DEMANDE,
      'DATE_FIN_CONGE_DEMANDE'=>$DATE_FIN_CONGE_DEMANDE,
      'DOCUMENT_PREUVE'=>$DOCUMENT_PREUVE,
      'DATE_SYSTEME_DEMANDE'=>$DATE_TRAITEMENT,
      'NAVIGATEUR'=>$NAVIGATEUR,
      'OS'=>$OS,
      'IP'=>$IP
    );
    // echo'<pre>';
    // print_r($data_conge);
    // exit();
    $ID_DEMANDE_CONGE = $this->Model->insert_last_id('conge_demande_conge',$data_conge);

    if ($this->session->userdata('SOCAR_PROFIL_ID') == 8) {
      
      $data_traitement = array(
        'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
        'DATE_TRAITEMENT' =>$DATE_TRAITEMENT,
        'ID_DIRECTION' =>$ID_DIRECTION,
        'ID_DEPARTEMENT' =>$ID_SERVICE,
        'ID_SERVICE' =>$ID_SECTION,
        'ID_EMPLOYE_TRAITANT' =>$ID_EMPLOYE,
        'STATUS_CONGE' =>13,
        'NB_JOURS_ACCORDE' => $NB_JOURS_DEMANDE,
        'COMMENTAIRE' => 'Demandé par les ressources humaines',
        'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,
        'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE,
        'NAVIGATEUR'=>$NAVIGATEUR,
        'OS'=>$OS,
        'IP'=>$IP,
       );

       $data_traitements = array(
        'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
        'DATE_TRAITEMENT' =>$DATE_TRAITEMENT,
        'ID_DIRECTION' =>$ID_DIRECTION,
        'ID_DEPARTEMENT' =>$ID_SERVICE,
        'ID_SERVICE' =>$ID_SECTION,
        'ID_EMPLOYE_TRAITANT' =>$ID_EMPLOYE,
        'STATUS_CONGE' =>5,
        'NB_JOURS_ACCORDE' => $NB_JOURS_DEMANDE,
        'COMMENTAIRE' => 'Demandé par les ressources humaines',
        'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,
        'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE,
        'NAVIGATEUR'=>$NAVIGATEUR,
        'OS'=>$OS,
        'IP'=>$IP,
       );

      //  print_r($data_traitement);
       
      $this->Model->insert_last_id('conge_traitement_conge',$data_traitement);
      $this->Model->insert_last_id('conge_traitement_conge',$data_traitements);
      
      $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),array('STATUS_CONGE'=>5,'NB_JOURS_ACCORDE'=>$NB_JOURS_ACCORDE,'DATE_DERNIER_REPOSE'=>$DATE_TRAITEMENT,'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE));

    }

    // exit();


          $direction=$this->Model->getRequeteOne('SELECT * FROM `organigramme_direction` WHERE ID_DIRECTION = '.$ID_DIRECTION.' ');

          $obj = 'Demande de congé';
          // if ($rh['ID_SEXE'] == 1) {
          //   $message = 'Monsieur '.$NOM.' '.$PRENOM.',<br>';
          // }
          // else{
          //  $message = 'Madame '.$NOM.' '.$PRENOM.',<br>';
          // }

          $message ='';

          







          $message.= 'Votre demande de congé: '.$type_conge['DESCRIPTION'].' débutant le '.$DATE_DEMANDE.' pour '.$NB_JOURS_DEMANDE.' jours vient d\'être bien enregistré.<br><br>  

          Pour suivre le traitement de votre congé, veuillez vous connecter au système suivant <a href="'.base_url().'" target="_blank">ce lien</a> et utiliser vos identifiants.<br><br>



           Système de gestion de congés. ';


      $this->notifications->newsend_mail($rh['EMAIL'],$obj,NULL,$message,NULL);



    $message = "<div class='alert alert-success' id='message'>
                            Demande de cong&eacute; enregistr&eacute; avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));  
   }

    }

    
    public function update()
    {
           
      
      $ID_EMPLOYE=$this->input->post('ID_EMPLOYE');
      $ID_DEMANDE_CONGE=$this->input->post('ID_DEMANDE_CONGE');
      $ID_TYPE_CONGE=$this->input->post('ID_TYPE_CONGE');
      $NB_JOURS_DEMANDE=$this->input->post('NB_JOURS_DEMANDE');
      $DATE_DEMANDE=$this->input->post('DATE_DEMANDE');

      $this->form_validation->set_rules('ID_EMPLOYE', 'Employ&eacute;', 'required');
      $this->form_validation->set_rules('ID_TYPE_CONGE', 'Type de cong&eacute;', 'required');
      $this->form_validation->set_rules('NB_JOURS_DEMANDE', 'Nb Jours', 'required');
      $this->form_validation->set_rules('DATE_DEMANDE', 'Date', 'required');
      
  

   if ($this->form_validation->run() == FALSE){
    $message = "<div class='alert alert-danger'>
                              Congé non enregistr&eacute;
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));

    $data['data']=$this->Model->getRequeteOne('SELECT * FROM conge_demande_conge WHERE ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.'');

      $data['title']='Demande Conge';
      $data['responsabilite']=$this->Model->getRequete('SELECT * FROM `rh_responsabilite` order by DESCRIPTION');
      $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
      $data['departement']=$this->Model->getRequete('SELECT * FROM `organigramme_departement` order by DESCRIPTION');
      $data['type_conge']=$this->Model->getRequete('SELECT * FROM `config_type_conge` order by NOM_CONGE');
      $data['rh']=$this->Model->getRequete('SELECT * FROM `rh_employe` order by NOM');
      
      $this->load->view('Demande_Conge_Update_View',$data);
   
   }
   else{
    
    $exercice = $this->Model->getRequeteOne('SELECT * FROM `config_exercice` WhERE STATUS_ACTIF = 1');
    $credit = $this->Model->getRequeteOne('SELECT * FROM `conge_credit` WHERE `ID_EXERCICE` = '.$exercice['ID_EXERCICE'].' AND `ID_EMPLOYE` = '.$ID_EMPLOYE.' ');
    $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$ID_EMPLOYE.' AND `STATUS_AFFECTATION` = 1');
    $type_conge = $this->Model->getRequeteOne('SELECT * FROM `config_type_conge` WHERE `ID_TYPE_CONGE` = '.$ID_TYPE_CONGE.' ');

    $rh=$this->Model->getRequeteOne('SELECT * FROM `rh_employe` WHERE `ID_EMPLOYE` = '.$ID_EMPLOYE.' ');

    // $startdate = '2022-05-12';
    $DATE_DEMANDE=date_create($DATE_DEMANDE);
    $DATE_DEMANDE = date_format($DATE_DEMANDE,"Y-m-d");

    $d = new DateTime($DATE_DEMANDE);
    $t = $d->getTimestamp();

    // loop for X days
    for($i=0; $i<$NB_JOURS_DEMANDE-1; $i++){
        // add 1 day to timestamp
        $addDay = 86400;
        // get what day it is next day
        $nextDay = date('w', ($t+$addDay));
        if ($type_conge['JOUR_CALENDAIRE'] == 0) {
          // if it's Saturday or Sunday get $i-1
          if($nextDay == 0 || $nextDay == 6) {
              $i--;
          }
          $ferrier = $this->Model->getRequeteOne('SELECT ID_JOURS_FERIER FROM `config_jours_ferrier` WHERE `DATE_FERIER` = "'.$d->format('Y-m-d').'" ');
          if(!empty($ferrier)) { 
            $i--;
          }
        }
        // modify timestamp, add 1 day
        $t = $t+$addDay;
        $d->setTimestamp($t);
        // echo $nextDay.'-'.$d->format('Y-m-d').'<br>';
    }

    $d->setTimestamp($t);


    $DATE_FIN_CONGE_DEMANDE = $d->format('Y-m-d');

    if ($type_conge['BESOIN_DOCUMENT'] == 1) {
      
    $repPhotoo =FCPATH.'/uploads/document_preuve';
    $documents=uniqid();
    $document_preuve = $_FILES['DOCUMENT_PREUVE']['name'];
    $config['upload_path'] ='./uploads/document_preuve/';
    $config['allowed_types'] = '*';
    // $test = explode('.', $document_preuve);
    // $ext = end($test);
    // $name = $documents.'_document_preuve.' . $ext;
    $name = $document_preuve;
    $config['file_name'] =$name;
    if(!is_dir($repPhotoo)) //create the folder if it does not already exists   
      {
        mkdir($repPhotoo,0777,TRUE);                                                        
      } 

    $this->upload->initialize($config);
    $this->upload->do_upload('DOCUMENT_PREUVE');
    $image_name_main=$config['file_name'];
    $data_image=$this->upload->data();
    if ($image_name_main != NULL) {
      $DOCUMENT_PREUVE = $image_name_main;
    }
    else{
      $DOCUMENT_PREUVE = NULL;
    }
    }
    else{
      $DOCUMENT_PREUVE = NULL;
    }


    $data_conge=array(
      'ID_EXERCICE'=>$exercice['ID_EXERCICE'],
      'ID_CREDIT'=>$credit['ID_CREDIT'],
      'DATE_DEMANDE'=>$DATE_DEMANDE,
      'DATE_DERNIER_REPOSE'=>$DATE_DEMANDE,
      'NB_JOURS_DEMANDE'=>$NB_JOURS_DEMANDE,
      'ID_TYPE_CONGE'=>$ID_TYPE_CONGE,
      'DATE_DEBUT_CONGE_DEMANDE'=>$DATE_DEMANDE,
      'DATE_FIN_CONGE_DEMANDE'=>$DATE_FIN_CONGE_DEMANDE,
      'DOCUMENT_PREUVE'=>$DOCUMENT_PREUVE
    );
    // echo'<pre>';
    // print_r($data_conge);

    // print_r(array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE));
    // exit();
    $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),$data_conge);


          $direction=$this->Model->getRequeteOne('SELECT * FROM `organigramme_direction` WHERE ID_DIRECTION = '.$ID_DIRECTION.' ');
          $obj = 'Demande de congé';
          if ($rh['ID_SEXE'] == 1) {
            $message = 'Monsieur '.$NOM.' '.$PRENOM.',<br>';
          }
          else{
           $message = 'Madame '.$NOM.' '.$PRENOM.',<br>';
          }
          $message.= 'Votre demande de congé: '.$type_conge['DESCRIPTION'].' débutant le '.$DATE_DEMANDE.' pour '.$NB_JOURS_DEMANDE.' jours vient d\'être bien enregistré.<br><br>  
          Pour suivre le traitement de votre congé, veuillez vous connecter au système suivant <a href="'.base_url().'" target="_blank">ce lien</a> et utiliser vos identifiants.<br><br>
           Système de gestion de congés. ';
      $this->notifications->newsend_mail($rh['EMAIL'],$obj,NULL,$message,NULL);



    $message = "<div class='alert alert-success' id='message'>
                            Demande de cong&eacute; modifi&eacute; avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));  
   }

    }
    public function listing()
    {

      if ($this->session->userdata('SOCAR_PROFIL_ID') == 8) {

        $niveau = ' ';
        $myconge = ' ';
        $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(1,4,7)';
        $newcont = ' ';
        // $newcont = ' OR conge_demande_conge.STATUS_CONGE IN(13) AND conge_demande_conge.ID_SERVICE = 4';
        

      }
      elseif ($this->session->userdata('SOCAR_PROFIL_ID') == 3) {

        $niveau = ' ';
        $myconge = ' ';
        // $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(8,11,12)';
        $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(8)';
        // $newcont = ' OR conge_demande_conge.STATUS_CONGE IN(5) OR conge_demande_conge.ID_DIRECTION = 3 ';
        $newcont = ' OR conge_demande_conge.STATUS_CONGE IN(5) AND conge_demande_conge.ID_DIRECTION = 3 ';

        
      }
      elseif ($this->session->userdata('SOCAR_PROFIL_ID') != 0) {
      $employe= $this->Model->getRequeteOne("SELECT * FROM rh_employe_affectation  WHERE ID_EMPLOYE = ".$this->session->userdata('ID_EMPLOYE')." AND STATUS_AFFECTATION =1 ");
      

            if ($employe['ID_RESPONSABILITE']==1) {
              
              if ($employe['ID_SERVICE'] > 0) {
                if ($employe['ID_SERVICE'] != 4) {
                  $niveau = ' AND conge_demande_conge.ID_SERVICE = '.$employe['ID_SERVICE'].' ';
                  $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(13)';
                }
                else{
                  $niveau = ' AND conge_demande_conge.ID_SERVICE = '.$employe['ID_SERVICE'].' ';
                  $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(13) AND ID_SERVICE = 4 ';
                }
                
              }
              elseif ($employe['ID_SECTION'] > 0) {
                
                $niveau = ' AND conge_demande_conge.ID_SECTION = '.$employe['ID_SECTION'].' ';
                $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(2)';
              }
              elseif ($employe['ID_DIRECTION'] > 0) {
                
                $niveau = ' AND conge_demande_conge.ID_DIRECTION = '.$employe['ID_DIRECTION'].' ';
                $statusc = ' AND conge_demande_conge.STATUS_CONGE IN(5)';
              }
              
              $myconge = ' ';
            }
            else{
              $niveau = ' ';
              if($employe['ID_EMPLOYE'] == NULL){
                $myconge = ' ';
              }
              else{
                $myconge = ' AND conge_demande_conge.ID_EMPLOYE = '.$employe['ID_EMPLOYE'].'';
              }
              
              $statusc = ' ';
            }
              
              $newcont = ' ';
              
      }
      
      else{
        $niveau = ' ';
        $myconge = ' ';
        $statusc = ' ';
        $newcont = ' ';

      }

  
      $data['resultat']=$this->Model->getRequete('SELECT ID_DEMANDE_CONGE, config_exercice.DESCRIPTION AS EXERCICE, conge_status_conge.REFUS, conge_credit.ID_CREDIT, rh_employe.NOM, rh_employe.PRENOM, conge_demande_conge.ID_EMPLOYE, DATE_DEMANDE, conge_status_conge.STATUS_CONGE AS STATUS_CONGES, conge_status_conge.DESCRIPTION AS STATUS_CONGE, organigramme_niveau.DESCRIPTION AS NIVEAU , NB_JOURS_DEMANDE, NB_JOURS_ACCORDE, config_type_conge.NOM_CONGE AS TYPE_CONGE, organigramme_direction.DESCRIPTION AS DIRECTION, conge_demande_conge.ID_DIRECTION, organigramme_section.DESCRIPTION AS DEPARTEMENT, conge_demande_conge.ID_SECTION, organigramme_service.DESCRIPTION AS SERVICE, conge_demande_conge.ID_SERVICE, conge_demande_conge.DATE_DERNIER_REPOSE, conge_demande_conge.DATE_DEBUT_CONGE_DEMANDE, conge_demande_conge.DATE_FIN_CONGE_DEMANDE FROM `conge_demande_conge` LEFT JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE LEFT JOIN conge_credit ON conge_credit.ID_CREDIT = conge_demande_conge.ID_CREDIT LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE LEFT JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_demande_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX LEFT JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE WHERE 1  '.$myconge.' '.$statusc.' '.$newcont.' '.$niveau.' ORDER BY conge_demande_conge.DATE_DERNIER_REPOSE DESC');

      // echo '<br>';
    // echo 'SELECT ID_DEMANDE_CONGE, config_exercice.DESCRIPTION AS EXERCICE, conge_status_conge.REFUS, conge_credit.ID_CREDIT, rh_employe.NOM, rh_employe.PRENOM, conge_demande_conge.ID_EMPLOYE, DATE_DEMANDE, conge_status_conge.STATUS_CONGE AS STATUS_CONGES, conge_status_conge.DESCRIPTION AS STATUS_CONGE, organigramme_niveau.DESCRIPTION AS NIVEAU , NB_JOURS_DEMANDE, NB_JOURS_ACCORDE, config_type_conge.NOM_CONGE AS TYPE_CONGE, organigramme_direction.DESCRIPTION AS DIRECTION, conge_demande_conge.ID_DIRECTION, organigramme_section.DESCRIPTION AS DEPARTEMENT, conge_demande_conge.ID_SECTION, organigramme_service.DESCRIPTION AS SERVICE, conge_demande_conge.ID_SERVICE, conge_demande_conge.DATE_DERNIER_REPOSE, conge_demande_conge.DATE_DEBUT_CONGE_DEMANDE, conge_demande_conge.DATE_FIN_CONGE_DEMANDE FROM `conge_demande_conge` LEFT JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE LEFT JOIN conge_credit ON conge_credit.ID_CREDIT = conge_demande_conge.ID_CREDIT LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE LEFT JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_demande_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX LEFT JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE WHERE 1  '.$myconge.' '.$statusc.' '.$newcont.' '.$niveau.' ORDER BY conge_demande_conge.DATE_DERNIER_REPOSE DESC';

    

 

      $data['title']='Demande de cong&eacute;';
      $data['typeconge'] = $this->Model->getRequete('SELECT * FROM `conge_status_conge` WHERE 1');
        $this->load->view('Demande_Conge_List_View',$data);
            

    }



    public function traiter()
    {
           
      
      $STATUS_CONGE=$this->input->post('STATUS_CONGE');
      $NB_JOURS_ACCORDE=$this->input->post('NB_JOURS_ACCORDE');
      // $NB_JOURS_ACCORDE=30;
      $DATE_DEBUT_CONGE_ACCORDE=$this->input->post('DATE_DEBUT_CONGE_ACCORDE');
      $COMMENTAIRE=$this->input->post('COMMENTAIRE');
      $ID_DEMANDE_CONGE=$this->input->post('ID_DEMANDE_CONGE');
      $ID_EMPLOYE=$this->input->post('ID_EMPLOYE');



      $this->form_validation->set_rules('STATUS_CONGE', 'Status', 'required', array('required' => 'Le champ Status est obligatoire.'));
      $this->form_validation->set_rules('NB_JOURS_ACCORDE', 'Nb Jours', 'required');
      $this->form_validation->set_rules('DATE_DEBUT_CONGE_ACCORDE', 'Date debut', 'required');
      
  

   if ($this->form_validation->run() == FALSE){
    $message = "<div class='alert alert-danger'>
                              Traitement du Congé non enregistr&eacute;
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
    
   
    
    $data['data']=$this->Model->getRequeteOne('SELECT ID_DEMANDE_CONGE, config_exercice.DESCRIPTION AS EXERCICE, conge_credit.ID_CREDIT, conge_credit.NB_JOURS_RESTANT, rh_employe.NOM, rh_employe.PRENOM, DATE_DEMANDE, conge_status_conge.DESCRIPTION AS STATUS_CONGE, conge_status_conge.STATUS_CONGE AS STATUS_CONGES, organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME, conge_demande_conge.ID_EMPLOYE, organigramme_niveau.DESCRIPTION AS NIVEAU, NB_JOURS_DEMANDE, NB_JOURS_ACCORDE, config_type_conge.NOM_CONGE AS TYPE_CONGE, organigramme_direction.DESCRIPTION AS DIRECTION, organigramme_service.DESCRIPTION AS DEPARTEMENT, organigramme_section.DESCRIPTION AS SERVICE, conge_demande_conge.DATE_DERNIER_REPOSE, conge_demande_conge.DATE_DEBUT_CONGE_DEMANDE, conge_demande_conge.DATE_FIN_CONGE_DEMANDE FROM `conge_demande_conge` LEFT JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE LEFT JOIN conge_credit ON conge_credit.ID_CREDIT = conge_demande_conge.ID_CREDIT LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_demande_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION WHERE conge_demande_conge.ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.' ');

    $data['historique']=$this->Model->getRequete('SELECT DATE_TRAITEMENT, organigramme_direction.DESCRIPTION AS DIRECTION,  organigramme_service.DESCRIPTION AS DEPARTEMENT , organigramme_section.DESCRIPTION AS SERVICE, rh_employe.NOM, rh_employe.PRENOM, conge_status_conge.DESCRIPTION AS STATUS_CONGE, COMMENTAIRE, DATE_DEBUT_CONGE_ACCORDE, DATE_FIN_CONGE_ACCORDE, organigramme_niveau.DESCRIPTION AS NIVEAU FROM `conge_traitement_conge` LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_traitement_conge.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=conge_traitement_conge.ID_DEPARTEMENT LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_traitement_conge.ID_SERVICE LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_traitement_conge.ID_EMPLOYE_TRAITANT LEFT JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_traitement_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX WHERE 1 AND ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.'');


    
    $data['employe']=$this->Model->getRequete('SELECT * FROM `rh_employe`');
    


    
    


    $data['title']='Demande de cong&eacute;';
    $this->load->view('Demande_Conge_Traitement_View',$data);
   }
   else{
  
    
    $affectation_employe = $this->Model->getRequeteOne('SELECT rh_employe_affectation.ID_SECTION, rh_employe_affectation.ID_SERVICE, rh_employe_affectation.ID_DIRECTION, rh_employe_affectation.ID_RESPONSABILITE FROM `conge_demande_conge` JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE = rh_employe.ID_EMPLOYE WHERE `ID_DEMANDE_CONGE` ='.$ID_DEMANDE_CONGE.' ');

   

    $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$ID_EMPLOYE.' AND `STATUS_AFFECTATION` = 1');
    $type_conge = $this->Model->getRequeteOne('SELECT JOUR_CALENDAIRE FROM conge_demande_conge JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE WHERE conge_demande_conge.ID_DEMANDE_CONGE =  '.$ID_DEMANDE_CONGE.' ');
    
    $DATE_DEMANDE=date_create($DATE_DEBUT_CONGE_ACCORDE);
    $DATE_DEMANDE = date_format($DATE_DEMANDE,"Y-m-d");


    

    $d = new DateTime($DATE_DEMANDE);
    $t = $d->getTimestamp();

    // loop for X days
    for($i=0; $i<$NB_JOURS_ACCORDE-1; $i++){

        // add 1 day to timestamp
        $addDay = 86400;
        // get what day it is next day
        $nextDay = date('w', ($t+$addDay));
        // if it's Saturday or Sunday get $i-1
        if ($type_conge['JOUR_CALENDAIRE'] == 0) {
        if($nextDay == 0 || $nextDay == 6) {
            $i--;
        }
        $ferrier = $this->Model->getRequeteOne('SELECT ID_JOURS_FERIER FROM `config_jours_ferrier` WHERE `DATE_FERIER` = "'.$d->format('Y-m-d').'" ');
        if(!empty($ferrier)) { 
          $i--;
        }
      }
        // modify timestamp, add 1 day
        $t = $t+$addDay;

        $d->setTimestamp($t);
  }

    $d->setTimestamp($t);

    $DATE_FIN_CONGE_DEMANDE = $d->format('Y-m-d');
    $DATE_TRAITEMENT_DERNIER = date('Y-m-d H:i');



    $NAVIGATEUR=$this->ci->agent->browser.' version '.$this->ci->agent->version;
      $OS=$this->ci->agent->platform;
      $IP = $_SERVER["REMOTE_ADDR"];
      $dt = new DateTime("now", new DateTimeZone('Africa/Bujumbura'));
      $DATE_TRAITEMENT = $dt->format('Y-m-d H:i:s');
    
      if($affectation_employe['ID_DIRECTION']==3){
        // echo'direction general<br>';
        if($this->session->userdata('SOCAR_PROFIL_ID') == 3){
          $NSTATUS = 11;
          // echo'DG connecte fin<br>';
        }
        else{
          $NSTATUS = $STATUS_CONGE;
          // echo'DG connecte process continue<br>';
        }
      }
      else{

        // echo'pas direction general<br>';
        if($this->session->userdata('SOCAR_PROFIL_ID') == 2 || $this->session->userdata('SOCAR_PROFIL_ID') == 4) {
          // echo 'directeur connecte<br>';
          if ($affectation_employe['ID_RESPONSABILITE'] ==1) {
            // echo 'Le demandeur est responsable le processus continue au DG<br>';
            $NSTATUS = $STATUS_CONGE;
          }
          else{
            // echo 'Le demandeur n\'est responsable le processus se termine par l\'accord du directeur<br>';
            $NSTATUS = 11;
          }
        }
        else if($this->session->userdata('SOCAR_PROFIL_ID') == 8) {
          echo 'chef rh connecte<br>';
          if ($affectation_employe['ID_RESPONSABILITE'] ==1) {
            echo 'une demande faite par un chef<br>';
            $data_traitement_rh = array(
              'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
              'DATE_TRAITEMENT' =>$DATE_TRAITEMENT,
              'ID_DIRECTION' =>$affectation['ID_DIRECTION'],
              'ID_DEPARTEMENT' =>$affectation['ID_SERVICE'],
              'ID_SERVICE' =>$affectation['ID_SECTION'],
              'ID_EMPLOYE_TRAITANT' =>$ID_EMPLOYE,
              'STATUS_CONGE' =>14,
              'NB_JOURS_ACCORDE' => $NB_JOURS_ACCORDE,
              'COMMENTAIRE' => $COMMENTAIRE,
              'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,
              'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE,
              'NAVIGATEUR'=>$NAVIGATEUR,
              'OS'=>$OS,
              'IP'=>$IP,
             );

            //  print_r($data_traitement_rh);
             $this->Model->insert_last_id('conge_traitement_conge',$data_traitement_rh);
            $NSTATUS = 5;
            // echo'Le process saute et continue<br>';

          }
          else{
            // echo 'demande faite pas par un chef<br>';
            $NSTATUS = $STATUS_CONGE;
            // echo'Le process continue normalement<br>';
          }
        
        }
        else{
          // echo 'directeur non connecte<br>';
          $NSTATUS = $STATUS_CONGE;
          // echo'Le process continue<br>';
        }


      }
      // exit();
      
    
    $data_traitement = array(
      'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
      'DATE_TRAITEMENT' =>$DATE_TRAITEMENT,
      'ID_DIRECTION' =>$affectation['ID_DIRECTION'],
      'ID_DEPARTEMENT' =>$affectation['ID_SERVICE'],
      'ID_SERVICE' =>$affectation['ID_SECTION'],
      'ID_EMPLOYE_TRAITANT' =>$ID_EMPLOYE,
      'STATUS_CONGE' =>$NSTATUS,
      'NB_JOURS_ACCORDE' => $NB_JOURS_ACCORDE,
      'COMMENTAIRE' => $COMMENTAIRE,
      'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,
      'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE,
      'NAVIGATEUR'=>$NAVIGATEUR,
      'OS'=>$OS,
      'IP'=>$IP,
     );
     
    $this->Model->insert_last_id('conge_traitement_conge',$data_traitement);
    
    $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),array('STATUS_CONGE'=>$NSTATUS,'NB_JOURS_ACCORDE'=>$NB_JOURS_ACCORDE,'DATE_DERNIER_REPOSE'=>$DATE_TRAITEMENT,'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE));
     
    
     


    if ($NSTATUS == 11) {
      echo 'fin du process<br>';
      $demandes = $this->Model->getRequeteOne('SELECT * FROM `conge_demande_conge` WHERE `ID_DEMANDE_CONGE` = '.$ID_DEMANDE_CONGE.'');
      $credit = $this->Model->getRequeteOne('SELECT * FROM `conge_credit` WHERE 1 AND ID_EMPLOYE = '.$demandes['ID_EMPLOYE'].' AND NB_JOURS_RESTANT > 0 ORDER BY FIN_CREDIT ASC limit 1');

      $ncredit = $this->Model->getRequeteOne('SELECT * FROM `conge_credit` WHERE 1 AND ID_EMPLOYE = '.$demandes['ID_EMPLOYE'].' AND NB_JOURS_RESTANT > 0 ORDER BY FIN_CREDIT ASC limit 1,1');

      // echo '<pre>';
      // print_r($demandes);
      // print_r($credit);
      // print_r($ncredit);
      
      if ($NB_JOURS_ACCORDE <= $credit['NB_JOURS_RESTANT']) {
        echo 'nombre de jours accorde inferieur au nombre de jours restant dans l exercice<br>';
        $NEW_NB_JOURS_ACCORDE = $credit['NB_JOURS_RESTANT']- $NB_JOURS_ACCORDE;

        $this->Model->update('conge_credit',array('ID_CREDIT'=>$credit['ID_CREDIT']),array('NB_JOURS_RESTANT'=>$NEW_NB_JOURS_ACCORDE));
        
        // // if($NEW_NB_JOURS_ACCORDE > 0){
          echo 'nb jour resant superieur a 0<br>';
          $this->Model->update(
            'conge_demande_conge',
            array(
              'ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE
            ),
            array(
              'ID_EXERCICE'=>$credit['ID_EXERCICE'],
              'ID_EXERCICE_SUIVANT'=>$ncredit['ID_EXERCICE'],
              'NB_JOURS_SUIVANT_PRECEDENT'=>$ncredit['NB_JOURS_RESTANT'],
              'NB_JOURS_RESTANT_SUIVANT_ACTUELLEMT'=>$ncredit['NB_JOURS_RESTANT'],
              'NB_JOURS_DUS_SUIVANT'=>$ncredit['NB_JOURS_CREDITE']
            )
          );
        // // }
        // // else{
        // //   echo 'nb jour resant egale a 0<br>';

        // // }
      
      }
      else{
        echo 'nombre de jours accorde superieur au nombre de jours restant dans l exercice<br>';
        
        $NEW_NB_JOURS_ACCORDE = $credit['NB_JOURS_RESTANT'];
       $this->Model->update('conge_credit',array('ID_CREDIT'=>$credit['ID_CREDIT']),array('NB_JOURS_RESTANT'=>0));
         $NOUVEAU_RETRAIT = $credit['NB_JOURS_RESTANT'] + $ncredit['NB_JOURS_RESTANT'] - $NB_JOURS_ACCORDE;
        //  echo $NOUVEAU_RETRAIT;
        
        $this->Model->update('conge_credit',array('ID_CREDIT'=>$ncredit['ID_CREDIT']),array('NB_JOURS_RESTANT'=>$NOUVEAU_RETRAIT));
        
        $this->Model->update(
          'conge_demande_conge',
          array(
            'ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE
          ),
          array(
            'ID_EXERCICE_PRECEDENT'=>$credit['ID_EXERCICE'],
            'NB_JOURS_RESTANT_PRECEDENT'=>$credit['NB_JOURS_RESTANT'],
            'NB_JOURS_RESTANT_PRECEDENT_ACTUELLEMT'=>0,
            'NB_JOURS_DUS_PRECEDEMENT'=>$credit['NB_JOURS_CREDITE']
          )
        );
      
        
      }
    
      $emp = $this->Model->getRequeteOne('SELECT rh_employe.EMAIL FROM conge_demande_conge JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE WHERE conge_demande_conge.ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.'');

    


          $obj = 'Reponse a votre demande de congé';
          $message = 'Cher (e) collaborateur (trice), <br>';
          $message.= 'Votre demande de congé est approuvée.<br>Il commence le '.$DATE_DEBUT_CONGE_ACCORDE.' et se termine le '.$DATE_FIN_CONGE_DEMANDE.'.<br>Merci';
          $message .='<a href="'.base_url('pdf/Pdf_Conge/index/'.$ID_DEMANDE_CONGE.'').'" target="_blank">Pour telecharger votre congé cliquer sur ce lien. </a> <br><br>';
       

    }


    if ($this->session->userdata('SOCAR_PROFIL_ID') == 8 && $NSTATUS == 13 && $affectation_employe['ID_SERVICE'] ==4) {
      
      $data_traitement = array(
        'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
        'DATE_TRAITEMENT' =>$DATE_TRAITEMENT,
        'ID_DIRECTION' =>$affectation['ID_DIRECTION'],
        'ID_DEPARTEMENT' =>$affectation['ID_SERVICE'],
        'ID_SERVICE' =>$affectation['ID_SECTION'],
        'ID_EMPLOYE_TRAITANT' =>$ID_EMPLOYE,
        'STATUS_CONGE' =>5,
        'NB_JOURS_ACCORDE' => $NB_JOURS_ACCORDE,
        'COMMENTAIRE' => 'Accordé par chef par les ressources humaines',
        'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,
        'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE,
        'NAVIGATEUR'=>$NAVIGATEUR,
        'OS'=>$OS,
        'IP'=>$IP,
       );
       
      

      $this->Model->insert_last_id('conge_traitement_conge',$data_traitement);
      
      $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),array('STATUS_CONGE'=>5,'NB_JOURS_ACCORDE'=>$NB_JOURS_ACCORDE,'DATE_DERNIER_REPOSE'=>$DATE_TRAITEMENT,'DATE_DEBUT_CONGE_ACCORDE' => $DATE_DEMANDE,'DATE_FIN_CONGE_ACCORDE' =>$DATE_FIN_CONGE_DEMANDE));

    }
    // exit();

    
    $message = "<div class='alert alert-success' id='message'>
                            Traitement de la demande de cong&eacute; enregistr&eacute; avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));  

   }

    }
    
    public function traitement($id)
    {

      $data['data']=$this->Model->getRequeteOne('SELECT ID_DEMANDE_CONGE, config_exercice.DESCRIPTION AS EXERCICE, conge_credit.ID_CREDIT, conge_credit.NB_JOURS_RESTANT, rh_employe.NOM, rh_employe.PRENOM, DATE_DEMANDE, conge_status_conge.DESCRIPTION AS STATUS_CONGE, conge_status_conge.STATUS_CONGE AS STATUS_CONGES, organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME, conge_demande_conge.ID_EMPLOYE, organigramme_niveau.DESCRIPTION AS NIVEAU, NB_JOURS_DEMANDE, NB_JOURS_ACCORDE, config_type_conge.NOM_CONGE AS TYPE_CONGE, organigramme_direction.DESCRIPTION AS DIRECTION, organigramme_service.DESCRIPTION AS DEPARTEMENT, organigramme_section.DESCRIPTION AS SERVICE, conge_demande_conge.DATE_DERNIER_REPOSE, conge_demande_conge.DATE_DEBUT_CONGE_DEMANDE, conge_demande_conge.DATE_FIN_CONGE_DEMANDE FROM `conge_demande_conge` LEFT JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE LEFT JOIN conge_credit ON conge_credit.ID_CREDIT = conge_demande_conge.ID_CREDIT LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_demande_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION WHERE conge_demande_conge.ID_DEMANDE_CONGE = '.$id.' ');

      $data['historique']=$this->Model->getRequete('SELECT DATE_TRAITEMENT, organigramme_direction.DESCRIPTION AS DIRECTION,  organigramme_service.DESCRIPTION AS DEPARTEMENT , organigramme_section.DESCRIPTION AS SERVICE, rh_employe.NOM, rh_employe.PRENOM, conge_status_conge.DESCRIPTION AS STATUS_CONGE, COMMENTAIRE, DATE_DEBUT_CONGE_ACCORDE, DATE_FIN_CONGE_ACCORDE, organigramme_niveau.DESCRIPTION AS NIVEAU FROM `conge_traitement_conge` LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_traitement_conge.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=conge_traitement_conge.ID_DEPARTEMENT LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_traitement_conge.ID_SERVICE LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_traitement_conge.ID_EMPLOYE_TRAITANT LEFT JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_traitement_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX WHERE 1 AND ID_DEMANDE_CONGE = '.$id.'');


      
      $data['employe']=$this->Model->getRequete('SELECT * FROM `rh_employe`');
      


      
      


      $data['title']='Demande de cong&eacute;';
      $this->load->view('Demande_Conge_Traitement_View',$data);

    }

    public function listing_sans()
    {

      $data['resultat']=$this->Model->getRequete('SELECT rh_employe.ID_EMPLOYE, NOM, PRENOM, rh_sexe.DESCRIPTION AS SEXE, EMAIL, TEL, DATE_ENTRE_SERVICE, rh_employe.STATUS, organigramme_direction.DESCRIPTION AS DIRECTION, organigramme_departement.DESCRIPTION AS DEPARTEMENT, organigramme_service.DESCRIPTION AS SERVICE, rh_employe_affectation.DATE_DEBUT, rh_responsabilite.DESCRIPTION AS RESPONSABILITE FROM `rh_employe` JOIN rh_sexe ON rh_sexe.ID_SEXE = rh_employe.ID_SEXE JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE = rh_employe.ID_EMPLOYE JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_departement ON organigramme_departement.ID_DEPARTEMENT = rh_employe_affectation.ID_DEPARTEMENT LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE =  rh_employe_affectation.ID_SERVICE JOIN rh_responsabilite ON rh_responsabilite.ID_RESPONSABILITE = rh_employe_affectation.ID_RESPONSABILITE WHERE 1 AND rh_employe_affectation.STATUS_AFFECTATION = 0');
      $tabledata=array();

      $data['title']='Employé';
        $this->load->view('Employe_List_Sans_View',$data);

    }


    

    public function listingall()
    {

     

      $data['resultat']=$this->Model->getRequete('SELECT ID_DEMANDE_CONGE, config_exercice.DESCRIPTION AS EXERCICE, conge_status_conge.REFUS, conge_credit.ID_CREDIT, rh_employe.NOM, rh_employe.PRENOM, conge_demande_conge.ID_EMPLOYE, DATE_DEMANDE, conge_status_conge.STATUS_CONGE AS STATUS_CONGES, conge_status_conge.DESCRIPTION AS STATUS_CONGE, organigramme_niveau.DESCRIPTION AS NIVEAU , NB_JOURS_DEMANDE, NB_JOURS_ACCORDE, config_type_conge.NOM_CONGE AS TYPE_CONGE, organigramme_direction.DESCRIPTION AS DIRECTION, conge_demande_conge.ID_DIRECTION, organigramme_section.DESCRIPTION AS DEPARTEMENT, conge_demande_conge.ID_SECTION, organigramme_service.DESCRIPTION AS SERVICE, conge_demande_conge.ID_SERVICE, conge_demande_conge.DATE_DERNIER_REPOSE, conge_demande_conge.DATE_DEBUT_CONGE_DEMANDE, conge_demande_conge.DATE_FIN_CONGE_DEMANDE FROM `conge_demande_conge` LEFT JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE LEFT JOIN conge_credit ON conge_credit.ID_CREDIT = conge_demande_conge.ID_CREDIT LEFT JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE LEFT JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE = conge_demande_conge.STATUS_CONGE LEFT JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME = conge_status_conge.ID_NIVEAUX LEFT JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE WHERE 1   ORDER BY conge_demande_conge.DATE_DERNIER_REPOSE DESC');

    

 

      $data['title']='Toute les Demande de cong&eacute;';
      $data['typeconge'] = $this->Model->getRequete('SELECT * FROM `conge_status_conge` WHERE 1');
        $this->load->view('Demande_Conge_List_All_View',$data);
            

    }


    public function index_update($id)
    {

      // $data['title']='Employe';
      $data['data']=$this->Model->getRequeteOne('SELECT * FROM conge_demande_conge WHERE ID_DEMANDE_CONGE = '.$id.'');

      $data['title']='Demande Conge';
      $data['responsabilite']=$this->Model->getRequete('SELECT * FROM `rh_responsabilite` order by DESCRIPTION');
      $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
      $data['service']=$this->Model->getRequete('SELECT * FROM `organigramme_service` order by DESCRIPTION');
      $data['type_conge']=$this->Model->getRequete('SELECT * FROM `config_type_conge` order by NOM_CONGE');
      $data['rh']=$this->Model->getRequete('SELECT * FROM `rh_employe` order by NOM');
      
      $this->load->view('Demande_Conge_Update_View',$data);

    }




    public function refuser_conge()
    {

      $COMMENTAIRE=$this->input->post('COMMENTAIRE');
      $ID_DEMANDE_CONGE=$this->input->post('ID_DEMANDE_CONGE');
      $historique = $this->Model->getRequeteOne('SELECT * FROM `conge_demande_conge` WHERE 1 AND ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.' ');
      $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$historique['ID_EMPLOYE'].' AND `STATUS_AFFECTATION` = 1');
      $DATE_TRAITEMENT_DERNIER = date('Y-m-d H:i');

      $data_traitement = array(
      'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
      'DATE_TRAITEMENT' =>$DATE_TRAITEMENT_DERNIER,
      'ID_DIRECTION' =>$affectation['ID_DIRECTION'],
      'ID_DEPARTEMENT' =>$affectation['ID_DEPARTEMENT'],
      'ID_SERVICE' =>$affectation['ID_SERVICE'],
      'ID_EMPLOYE_TRAITANT' =>0,
      'STATUS_CONGE' =>0,
      'NB_JOURS_ACCORDE' => $historique['NB_JOURS_ACCORDE'],
      'COMMENTAIRE' => $COMMENTAIRE,
      'DATE_DEBUT_CONGE_ACCORDE' =>$historique['DATE_DEBUT_CONGE_ACCORDE'],
      'DATE_FIN_CONGE_ACCORDE' =>$historique['DATE_FIN_CONGE_ACCORDE'],
     );

      // print_r($data_traitement);
      // exit();
    $this->Model->insert_last_id('conge_traitement_conge',$data_traitement);
    
    $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),array('STATUS_CONGE'=>0,'DATE_DERNIER_REPOSE'=>$DATE_TRAITEMENT_DERNIER));

      
      $message = "<div class='alert alert-danger' id='message'>
                            Congé refusé avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
      $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));  

    }

    
    public function check_refus()
  {
  $conge= $this->Model->getOne("conge_status_conge",array('STATUS_CONGE'=>$this->input->post('STATUS_CONGE')));
  
  echo $conge['REFUS'];
  }

  public function reaccepter_conge($id)
    {


      $COMMENTAIRE=$this->input->post('COMMENTAIRE');
      $ID_DEMANDE_CONGE=$this->input->post('ID_DEMANDE_CONGE');
      $historique = $this->Model->getRequeteOne('SELECT * FROM `conge_demande_conge` WHERE 1 AND ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.' ');
      $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$historique['ID_EMPLOYE'].' AND `STATUS_AFFECTATION` = 1');
      $DATE_TRAITEMENT_DERNIER = date('Y-m-d H:i');
      $derniertrait = $this->Model->getRequeteOne('SELECT * FROM `conge_traitement_conge` WHERE `ID_DEMANDE_CONGE` = '.$ID_DEMANDE_CONGE.'  AND STATUS_CONGE != 0 ORDER BY DATE_TRAITEMENT DESC LIMIT 1');

      $data_traitement = array(
      'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
      'DATE_TRAITEMENT' =>$DATE_TRAITEMENT_DERNIER,
      'ID_DIRECTION' =>$affectation['ID_DIRECTION'],
      'ID_DEPARTEMENT' =>$affectation['ID_DEPARTEMENT'],
      'ID_SERVICE' =>$affectation['ID_SERVICE'],
      'ID_EMPLOYE_TRAITANT' =>$derniertrait['ID_EMPLOYE_TRAITANT'],
      'STATUS_CONGE' =>$derniertrait['STATUS_CONGE'],
      'NB_JOURS_ACCORDE' => $historique['NB_JOURS_ACCORDE'],
      'COMMENTAIRE' => $COMMENTAIRE,
      'DATE_DEBUT_CONGE_ACCORDE' =>$historique['DATE_DEBUT_CONGE_ACCORDE'],
      'DATE_FIN_CONGE_ACCORDE' =>$historique['DATE_FIN_CONGE_ACCORDE'],
     );

    $this->Model->insert_last_id('conge_traitement_conge',$data_traitement);
    
    $this->Model->update('conge_demande_conge',array('ID_DEMANDE_CONGE'=>$ID_DEMANDE_CONGE),array('STATUS_CONGE'=>$derniertrait['STATUS_CONGE'],'DATE_DERNIER_REPOSE'=>$DATE_TRAITEMENT_DERNIER));

      
      $message = "<div class='alert alert-danger' id='message'>
                            Congé refusé avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
      $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));

    }



    public function message_conge()
    {


      $ID_DEMANDE_CONGE=$this->input->post('ID_DEMANDE_CONGE');
      $ID_TYPE_MESSAGE=$this->input->post('ID_TYPE_MESSAGE');
      $DATE_SYSTEME_DEMANDE = date('Y-m-d H:i');
      $CORPS_MESSAGE=$this->input->post('CORPS_MESSAGE');
      $ID_TYPE_DESTINATAIRE=$this->input->post('ID_TYPE_DESTINATAIRE');
      $ID_STATUS_MESSAGE=1;



      $demande = $this->Model->getRequeteOne('SELECT * FROM `conge_demande_conge` WHERE 1 AND ID_DEMANDE_CONGE = '.$ID_DEMANDE_CONGE.' ');
      $affectation = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE `ID_EMPLOYE` = '.$demande['ID_EMPLOYE'].' AND `STATUS_AFFECTATION` = 1');

      // print_r($affectation);



      $ID_EMPLOYE_EMETTEUR = $demande['ID_EMPLOYE'];
      
      $derniertrait = $this->Model->getRequeteOne('SELECT * FROM `conge_traitement_conge` WHERE `ID_DEMANDE_CONGE` = '.$ID_DEMANDE_CONGE.'  AND STATUS_CONGE != 0 ORDER BY DATE_TRAITEMENT DESC LIMIT 1');

      if ($ID_TYPE_DESTINATAIRE == 3) {
        $ID_EMPLOYE_DESTINATAIRE = $derniertrait['ID_EMPLOYE_TRAITANT'];
      }
      elseif ($ID_TYPE_DESTINATAIRE == 1) {
        $affectationsup = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE 1 AND `STATUS_AFFECTATION` = 1 AND ID_SECTION = '.$affectation['ID_SECTION'].' AND rh_employe_affectation.ID_SERVICE = '.$affectation['ID_SERVICE'].' AND ID_DIRECTION = '.$affectation['ID_DIRECTION'].' AND ID_RESPONSABILITE != 3');

        $ID_EMPLOYE_DESTINATAIRE = $affectationsup['ID_EMPLOYE'];
      }
      elseif ($ID_TYPE_DESTINATAIRE == 2) {
        if ($affectation['ID_SECTION']!= NULL || $affectation['ID_SECTION'] != 0) {
          $affectationsup = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE 1 AND `STATUS_AFFECTATION` = 1 AND rh_employe_affectation.ID_SERVICE = '.$affectation['ID_SERVICE'].' AND ID_DIRECTION = '.$affectation['ID_DIRECTION'].' AND ID_RESPONSABILITE != 3');
        $ID_EMPLOYE_DESTINATAIRE = $affectationsup['ID_EMPLOYE'];

        }

        elseif ($affectation['ID_SERVICE']!= NULL || $affectation['ID_SERVICE'] != 0) {
          $affectationsup = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE 1 AND `STATUS_AFFECTATION` = 1 AND ID_DIRECTION = '.$affectation['ID_DIRECTION'].' AND ID_RESPONSABILITE != 3');
        $ID_EMPLOYE_DESTINATAIRE = $affectationsup['ID_EMPLOYE'];

        }
        else{
          $affectationsup = $this->Model->getRequeteOne('SELECT * FROM `rh_employe_affectation` WHERE 1 AND '.$ID_EMPLOYE_EMETTEUR.' AND `STATUS_AFFECTATION` = 1 AND ID_DIRECTION = '.$affectation['ID_DIRECTION'].' AND ID_RESPONSABILITE != 3');
        $ID_EMPLOYE_DESTINATAIRE = $affectationsup['ID_EMPLOYE'];
        }
        
      }

      // exit();


      $data_traitement = array(
      'ID_DEMANDE_CONGE' =>$ID_DEMANDE_CONGE,
      'ID_TYPE_MESSAGE' =>$ID_TYPE_MESSAGE,
      'DATE_SYSTEME_DEMANDE' =>$DATE_SYSTEME_DEMANDE,
      'CORPS_MESSAGE' =>$CORPS_MESSAGE,
      'ID_TYPE_DESTINATAIRE' =>$ID_TYPE_DESTINATAIRE,
      'ID_EMPLOYE_EMETTEUR' =>$ID_EMPLOYE_EMETTEUR,
      'ID_STATUS_MESSAGE' => $ID_STATUS_MESSAGE,
      'ID_EMPLOYE_DESTINATAIRE' =>$ID_EMPLOYE_DESTINATAIRE,
      
     );

    $this->Model->insert_last_id('conge_message_conge',$data_traitement);
    


      
      $message = "<div class='alert alert-success' id='message'>
                            Message du congé enregistré avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
      $this->session->set_flashdata(array('message'=>$message));
      redirect(base_url('conge/Demande_Conge/listing'));

    }


    public function excels() {
       
      // $dateUne = $this->input->post('dateUne');
      // $dateDeux=$this->input->post('dateDeux');
      // $IS_SEND=$this->input->post('IS_SEND');
      // $dateUne=$this->uri->segment(3);
      // echo $dateUne."<br>-";
      // $dateDeux=$this->uri->segment(4);
      // echo $dateDeux."<br>-";
      // $IS_SEND=$this->uri->segment(5);
      // echo $IS_SEND."<br>-";
      
      $conduction = "";

        if (!empty($dateDeux) && !empty($dateUne)) {
        $conduction.= " and DATE_FORMAT(`DATE_INSERT`, '%Y-%m-%d')  BETWEEN '".$dateUne."' and '".$dateDeux."' ";
        }


        // $send = "";
        // if ($IS_SEND == 0) {
        // $send.= " and IS_SEND = 0";
        // }
        // elseif ($IS_SEND == 1) {
        //  $send.= " and IS_SEND = 1";
        // }
        // else{
        //     $send.= " ";
        // }



        $query_principal = $this->Model->getRequete("SELECT ID_DEMANDE_CONGE, CONCAT(rh_employe.NOM,' ', rh_employe.PRENOM) AS ID_EMPLOYE, DATE_SYSTEME_DEMANDE, NB_JOURS_ACCORDE, DATE_DEBUT_CONGE_ACCORDE, DATE_FIN_CONGE_ACCORDE, config_type_conge.NOM_CONGE AS ID_TYPE_CONGE, config_exercice.DESCRIPTION AS ID_EXERCICE, organigramme_direction.DESCRIPTION AS ID_DIRECTION, organigramme_service.DESCRIPTION AS ID_SERVICE, organigramme_section.DESCRIPTION AS ID_SECTION FROM `conge_demande_conge` JOIN rh_employe ON rh_employe.ID_EMPLOYE = conge_demande_conge.ID_EMPLOYE JOIN config_type_conge ON config_type_conge.ID_TYPE_CONGE = conge_demande_conge.ID_TYPE_CONGE JOIN config_exercice ON config_exercice.ID_EXERCICE = conge_demande_conge.ID_EXERCICE JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION = conge_demande_conge.ID_DIRECTION JOIN organigramme_service ON organigramme_service.ID_SERVICE = conge_demande_conge.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION = conge_demande_conge.ID_SECTION WHERE 1 AND STATUS_CONGE = 11 ORDER BY ID_DEMANDE_CONGE DESC");

        // $query_somme = $this->Model->getRequeteOne('SELECT SUM(MONTANT_TOTAL) AS MONTANT_TOTAL,SUM(MONTANT_TVA) AS MONTANT_TVA,SUM(MONTANT_NET) AS MONTANT_NET FROM `facture_club` WHERE `FACTURE_ID` IN(SELECT MAX(`FACTURE_ID`) as `FACTURE_ID` FROM facture_club WHERE 1 and ID_CLIENT = '.$this->session->userdata('ID_CLIENT').' '.$conduction.' '.$send.' GROUP BY `NUMERO_FATURE` )');
        
        $donnes = "";



      
     

    $this->load->library('excel');
    require_once './application/third_party/PHPExcel.php';
    require_once './application/third_party/PHPExcel/IOFactory.php';


    $objPHPExcel = new PHPExcel();

    $default_border = array(
        'style' => PHPExcel_Style_Border::BORDER_THIN,
        'color' => array('rgb' => '000000'),
    );


    // exit();
    $acc_default_border = array(
        'style' => PHPExcel_Style_Border::BORDER_THIN,
        'color' => array('rgb' => 'c7c7c7'),
    );
    $outlet_style_header = array(
        'font' => array(
            'color' => array('rgb' => '000000'),
            'size' => 10,
            'name' => 'Arial',
            'bold' => true,
        ),
    );
    $top_header_style = array(
        'borders' => array(
            'bottom' => $default_border,
            'left' => $default_border,
            'top' => $default_border,
            'right' => $default_border,
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            // 'color' => array('rgb' => 'fff'),
        ),
        'font' => array(
            // 'color' => array('rgb' => '000000'),
            'size' => 15,
            'name' => 'Arial',
            'bold' => true,
        ),
        'alignment' => array(
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
    );
    $style_header = array(
        'borders' => array(
            'bottom' => $default_border,
            'left' => $default_border,
            'top' => $default_border,
            'right' => $default_border,
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            // 'color' => array('rgb' => 'fff'),
        ),
        'font' => array(
            // 'color' => array('rgb' => '000000'),
            'size' => 12,
            'name' => 'Arial',
            'bold' => true,
        ),
        'alignment' => array(
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        ),
    );
    $account_value_style_header = array(
        'borders' => array(
            'bottom' => $default_border,
            'left' => $default_border,
            'top' => $default_border,
            'right' => $default_border,
        ),
        'font' => array(
            'color' => array('rgb' => '000000'),
            'size' => 12,
            'name' => 'Arial',
        ),
        'alignment' => array(
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        ),
    );
    $text_align_style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        ),
        'borders' => array(
            'bottom' => $default_border,
            'left' => $default_border,
            'top' => $default_border,
            'right' => $default_border,
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'fff'),
        ),
        'font' => array(
            'color' => array('rgb' => '000000'),
            'size' => 12,
            'name' => 'Arial',
            'bold' => true,
        ),
    );

    
      
    $i = 0;
     // foreach ($query_principal as $key) {
    
    $objPHPExcel->setActiveSheetIndex($i)->mergeCells('A1:K1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Liste des congés accordes');

    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($top_header_style);

    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Numéro');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Employé');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Date demande');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Nb jours');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Date debut congé');
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Date fin congé');
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Type congé');
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Exercice');
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Direction');
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Service');
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Section');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(27);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);

    $row = 3;
    // $custDtaData  = $this->Model->getList('assurance',array('TYPE_ASSURANCE_ID'=>$key['ASSURANCE_TYPE_ID']));
    
    $num= 1;
    foreach ($query_principal as $value)
    {
        // $subsc  = $this->Model->getOne('souscripteur',array('SOUSCRIPTEUR_ID'=>$value['SOUSCRIPTEUR_ID']));

        
        
            // $subsc  = $this->Model->getOne('souscripteur',array('SOUSCRIPTEUR_ID'=>$value['SOUSCRIPTEUR_ID']));
        // $benef  = $this->Model->getOne('assure',array('ASSURE_ID'=>$value['BENEFICIAIRE_ID']));
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $value['ID_DEMANDE_CONGE']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value['ID_EMPLOYE']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $value['DATE_SYSTEME_DEMANDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $value['NB_JOURS_ACCORDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $value['DATE_DEBUT_CONGE_ACCORDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $value['DATE_FIN_CONGE_ACCORDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $value['ID_TYPE_CONGE']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $value['ID_EXERCICE']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$row, $value['ID_DIRECTION']);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$row, $value['ID_SERVICE']);
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$row, $value['ID_SECTION']);
        
        $row++;
       
        $num++;
    }
    $objPHPExcel->getActiveSheet()->setTitle('Conge');
    $objPHPExcel->createSheet();
    $i++;
     // }
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Rapport du '.date('d m Y h:i:s').'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

// $data['ddebut']=$ddebut;
      // $data['dfin']=$dfin;
      // $data['CODE_SOCIETE']=$CODE_SOCIETE;
      // $data['TYPEASS']=$TYPEASS;
      // $data['pertoui']=$PERTEEXOUI;
      // $data['pertnom']=$PERTEEXNON;
    }
    



}