<?php
// CLASSE QUI GERE LES DOCUMENTS EXTERNES
// AUTEUR :  ALEXIS BADIA
// TEL : 79839653
// EMAIL : barekensabealexiss@gmail.com
// DATE : 01/02/2023
ini_set('max_execution_time', 0);
ini_set('memory_limit','15048M');
class Demande_Document extends CI_Controller{
    function __construct() {
        parent::__construct();
        $this->Is_Connected();
        //$this->cart->destroy();
      }

  //FONCTION QUI TEST SI L'UTILISATEUR EST CONNECTE
    public function Is_Connected()
       {
       if (empty($this->session->userdata('SOCAR_ID_USER')))
        {
         redirect(base_url('Login/'));
        }
       }

//FUNCTION POUR AJOUTER DANS LE PANNIER
function addcart()
{
  $FICHIER_ID=$this->input->post('FICHIER_ID');
  $IMAGENAME=$this->upload_file('file','document_rapport');
  $CODE=$this->Model->getRequeteOne('SELECT CODE FROM `doc_type_etape` WHERE  `FICHIER_ID`='.$FICHIER_ID);
  if(!empty($CODE['CODE']))
  {
 // Conversion en PDF

 $url = base_url();
 $elements = explode("/", $url);
 //$nouveau_url = $url ;
 $nouveau_url = null ;
 $indice = sizeof($elements);
 for ($i = 0; $i < ($indice - 2); $i++) {
     $nouveau_url .=$elements[$i] . '/';
 }

 $mimetype = mime_content_type(FCPATH.'/uploads/document_rapport/'.$IMAGENAME); //application/pdf

 if(in_array($mimetype, array('application/pdf'))) {

   $UNIQID=uniqid();
   $path = $nouveau_url.'uploads/document_rapport/';
   $NEWIMAGES = $nouveau_url.'/uploads/document_rapport/'.$IMAGENAME;
   $imagick = new Imagick();
   $imagick->readImage($NEWIMAGES);
   $imagick->writeImages($UNIQID.'_doc.jpeg', false);

   rename(FCPATH.''.$UNIQID.'_doc.jpeg', FCPATH."uploads/document_rapport/".$UNIQID."_doc.jpeg");
   $IMAGENAME = $UNIQID."_doc.jpeg";

 } 

   

  }


  $id_cart=$FICHIER_ID;
  $datass = array(
    'id'      => $id_cart,
    'qty'     => 1,
    'price'   => 1,
    'name'    => 'fich',
    'fichier'=>$id_cart,
    'FILE_NAME'=>$IMAGENAME,
  );
  $this->cart->insert($datass);

  $table='<div class="table-responsive"><table class="table table-bordered table-striped table-hover table-condensed">
  <tr>
  
  <td>#</td>
  <td>NOM</td>
  <td>ACFICHIERTION</td>
  <td>SUPPRIMER</td>
  </tr>
  ';
  $i=0;
  $j=1;

  foreach ($this->cart->contents() as $items) {
   $i++;
   $j++;
   $fichier=$this->Model->getRequeteOne('SELECT * FROM `doc_type_etape` WHERE  `FICHIER_ID`='.$items['fichier']);
   $table.='<tr>
   
   <td>'.$i.'</td>
   <td>'.$fichier["DESCRIPTION"].'</td>
   <td>'.$items["FILE_NAME"].'</td>
   <td><input type="hidden" id="rowid'.$j.'" value='.$items['rowid'].'>
   <button class="btn btn-danger btn-xs" type="button" onclick="remove_ct('.$j.')">x</button>
   </td>
   </tr>';
 }

 $table.='</table> <div>

 </div></div>';

 echo $table;
}


function remove_cart()

{

  $rowid = $this->input->post('rowid');
  $this->cart->remove($rowid);
  $table = null;
  $i = 0;
  $j=1;
  if ($this->cart->contents()) {
    # code...
  $table='<div class="table-responsive"><table class="table table-bordered table-striped table-hover table-condensed">
  <tr>
  
  <td>#</td>
  <td>NOM</td>
  <td>ACFICHIERTION</td>
  <td>SUPPRIMER</td>
  </tr>
  ';
  $i=0;
  $j=1;

  foreach ($this->cart->contents() as $items) {
   $i++;
   $j++;
   $fichier=$this->Model->getRequeteOne('SELECT * FROM `doc_type_etape` WHERE  `FICHIER_ID`='.$items['fichier']);
   $table.='<tr>
   
   <td>'.$i.'</td>
   <td>'.$fichier["DESCRIPTION"].'</td>
   <td>'.$items["FILE_NAME"].'</td>
   <td><input type="hidden" id="rowid'.$j.'" value='.$items['rowid'].'>
   <button class="btn btn-danger btn-xs" type="button" onclick="remove_ct('.$j.')">x</button>
   </td>
   </tr>';
 }

 $table.='</table> <div>

 </div></div>';

 echo $table;
}
}

//FONCTION POUR UPLOADER UN FICHIER
public function upload_file($input_name=array(),$dossier)
  {
    $nom_file=$_FILES[$input_name]['tmp_name'];
    $nom_champ=$_FILES[$input_name]['name']; 
    $repertoire_fichier =FCPATH.'uploads/'.$dossier.'/';
    $code=uniqid();
    $date="".date('YmdHi');
    $nom_champ=str_replace(" ","",$nom_champ);
    $nomfichier=$code.'-'.$date.'_'.$nom_champ;
    $fichier=basename($nomfichier);
    if(!is_dir($repertoire_fichier)) //create the folder if it does not already exists   
    {
      mkdir($repertoire_fichier,0777,TRUE);                                                         
    }
    move_uploaded_file($nom_file, $repertoire_fichier.$fichier);
    return $nomfichier;
  }

  

  //FONCTION QUI AFFICHE LES INPUTS POUR INITIER UNE DEMANDE
  public function index()
    {
      $data['title']='Gestion de documents';
      $data['doc_statut']=$this->Model->getRequete('SELECT * FROM `doc_statut` WHERE `IS_ACTIF`=1 ORDER BY `DESC_STATUT` ASC');
      $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
      $data['nivea']=$this->Model->getRequete('SELECT * FROM `organigramme_niveau` WHERE `ID_NIVEAUX_ORGANIGRAMME` NOT IN (0,2) ORDER BY `DESCRIPTION` ASC');
      //print_r($data['niveau']);
      $data['doc_type']=$this->Model->getRequete('SELECT * FROM `doc_type` WHERE `DOC_TYPE_ID` IN (SELECT `DOC_TYPE_ID` FROM config_doc_profil_etape) AND `STATUT`=1 order by DESC_TYPE');
      $data['ID_NIVEAUX_ORGANIGRAMME']=0;
      $this->load->view('Demande_Document_Add_View',$data);

    }

    
  public function get_services()
  {

  $ID_NIVEAUX_ORGANIGRAMME=$this->input->post('ID_NIVEAUX_ORGANIGRAMME');
  if ($ID_NIVEAUX_ORGANIGRAMME==1)
   {
   $service= $this->Model->getRequete("SELECT ID_SERVICE AS SERVICE,DESCRIPTION FROM `organigramme_service` WHERE `STATUS`=1 ORDER BY `DESCRIPTION` ASC");
   }

  if ($ID_NIVEAUX_ORGANIGRAMME==3)
   {
   $service= $this->Model->getRequete("SELECT ID_DIRECTION AS SERVICE,DESCRIPTION FROM `organigramme_direction` WHERE `STATUS`=1 ORDER BY `DESCRIPTION` ASC");
   }

    if ($ID_NIVEAUX_ORGANIGRAMME==4)
   {
   $service= $this->Model->getRequete("SELECT ID_SECTION AS SERVICE,DESCRIPTION FROM `organigramme_section` WHERE `STATUS`=1 ORDER BY `DESCRIPTION` ASC");
   }


  $datas= '<option value="">-- Sélectionner --</option>';
  foreach($service as $nom){
  $datas.= '<option value="'.$nom["SERVICE"].'">'.$nom["DESCRIPTION"].'</option>';
  }
  $datas.= '';
  echo $datas;
  }


  //FONCTION POUR INITIER UNE DEMANDE
  public function add()
    {
      $this->Is_Connected();
      $NOM=$this->input->post('NOM');
      $PRENOM=$this->input->post('PRENOM');
      $TEL=$this->input->post('TEL');
      $NUM_POLICE=$this->input->post('NUM_POLICE');
      $EMAIL=$this->input->post('EMAIL');
      $NUMERO_ADHERANT=$this->input->post('NUMERO_ADHERANT');
      //$SERVICE=$this->input->post('SERVICE');
      $DOC_TYPE_ID=$this->input->post('DOC_TYPE_ID');
      $DATE_DEMANDE=$this->input->post('DATE_DEMANDE');
      $COMMENTAIRE=$this->input->post('COMMENTAIRE');
      $STATUT_DOC_IDSS=$this->Model->getRequeteOne('SELECT * FROM `config_doc_profil_etape` WHERE `DOC_TYPE_ID`="'.$DOC_TYPE_ID.'" AND `NUM`=2');
      $STATUT_DOC_ID=$STATUT_DOC_IDSS['STATUT_DOC_ID'];
      $MATRICULE=$this->input->post('MATRICULE');
      $ID_NIVEAUX_ORGANIGRAMME=$this->input->post('ID_NIVEAUX_ORGANIGRAMME');
      $PATH_DOC=$this->input->post('PATH_DOC');
      $STATUT_DOC_ID_AFFECTATION_FORCEE=$this->input->post('STATUT_DOC_ID');
      $NUM_INTERMEDIAIRE=$this->input->post('NUM_INTERMEDIAIRE');
      
      print_r($NUM_INTERMEDIAIRE);
      exit();


      $this->form_validation->set_rules('DOC_TYPE_ID', '', 'required',array('required'=>'Le type de document obligatoire'));
      
      $this->form_validation->set_rules('NOM', '', 'required',array('required'=>'Le nom est obligatoire'));
      $this->form_validation->set_rules('PRENOM', '', 'required',array('required'=>'Le prénom est obligatoire'));
      $this->form_validation->set_rules('TEL', '', 'required',array('required'=>'Le numéro de tél est obligatoire'));
      $this->form_validation->set_rules('NUM_POLICE', '', 'required',array('required'=>'Le numéro police est obligatoire'));
     
      $this->form_validation->set_rules('MATRICULE', '', 'required',array('required'=>'Le matricule est obligatoire'));
      $this->form_validation->set_rules('NUMERO_ADHERANT', '', 'required',array('required'=>'Le champ est obligatoire'));
      $this->form_validation->set_rules('NUM_INTERMEDIAIRE', '', 'required',array('required'=>'Le champ est obligatoire'));

      $this->form_validation->set_rules('DATE_DEMANDE', '', 'required',array('required'=>'La date de demande est obligatoire'));
      $this->form_validation->set_rules('COMMENTAIRE', '', 'required',array('required'=>'Un commentaire est obligatoire'));
      

   if ($this->form_validation->run() == FALSE){
       
     echo $this->input->post('COMMENTAIRE');
      exit();
    $message = "<div class='alert alert-danger'>Demande non enregistr&eacute;<button type='button' class='close' data-dismiss='alert'>&times;</button></div>";
     $this->session->set_flashdata(array('message'=>$message));
      $data['title']='Gestion de documents';
      $data['doc_statut']=$this->Model->getRequete('SELECT * FROM `doc_statut` WHERE `IS_ACTIF`=1 ORDER BY `DESC_STATUT` ASC');
      $data['direction']=$this->Model->getRequete('SELECT * FROM `organigramme_direction` order by DESCRIPTION');
      $data['nivea']=$this->Model->getRequete('SELECT * FROM `organigramme_niveau` WHERE `ID_NIVEAUX_ORGANIGRAMME` NOT IN (0,2) ORDER BY `DESCRIPTION` ASC');
      $data['doc_type']=$this->Model->getRequete('SELECT * FROM `doc_type` WHERE `DOC_TYPE_ID` IN (SELECT `DOC_TYPE_ID` FROM config_doc_profil_etape) AND `STATUT`=1 order by DESC_TYPE');
      $data['ID_NIVEAUX_ORGANIGRAMME']=$this->input->post('ID_NIVEAUX_ORGANIGRAMME');
      $this->load->view('Demande_Document_Add_View',$data);
   }
   else{
    $DATE_DEMANDE=date_create($DATE_DEMANDE);
    $DATE_DEMANDE = date_format($DATE_DEMANDE,"Y-m-d"); 

    //DEBUT UPLOAD 1
    $repPhotoo =FCPATH.'/uploads/document_demandes';
    $documents=uniqid();
    $document_demandes = $_FILES['PATH_DOC']['name'];
    $config['upload_path'] ='./uploads/document_demandes/';
    $config['allowed_types'] = '*';
    $test = explode('.', $document_demandes);
    $ext = end($test);
    $name = $documents.'_doc.' . $ext;
    $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('PATH_DOC');
    $image_name_main=$config['file_name'];
    $data_image=$this->upload->data();
    //FIN UPLOAD 1

    //DEBUT UPLOAD 2
    $PATH =FCPATH.'/uploads/document_rapport/';
    $UNIQID=uniqid();
    $DOCRAP = $_FILES['PATH_DOC']['name'];
    $CONFIG2['upload_path'] ='./uploads/document_rapport/';
    $CONFIG2['allowed_types'] = '*';
    $EXTENSION = explode('.', $DOCRAP);
    $EXTE = end($EXTENSION);
    $NAMES = $UNIQID.'_doc.' . $EXTE;
    $CONFIG2['file_name'] =$NAMES;



    if(!is_dir($PATH)) //create the folder if it does not already exists   
      {
        mkdir($PATH,0777,TRUE);                                                        
      } 

    $this->upload->initialize($CONFIG2);
    $this->upload->do_upload('PATH_DOC');
    $IMAGENAME=$CONFIG2['file_name'];
    $data_image=$this->upload->data();




    //FIN UPLOAD 1data_image

    // Conversion en PDF

        // $url = base_url();
        // $elements = explode("/", $url);
        // // $nouveau_url = $url ;
        // $nouveau_url = null ;
        // $indice = sizeof($elements);
        // for ($i = 0; $i < ($indice - 2); $i++) {
        //     $nouveau_url .=$elements[$i] . '/';
        // }

        // $mimetype = mime_content_type(FCPATH.'/uploads/document_rapport/'.$IMAGENAME); //application/pdf

        // if(in_array($mimetype, array('application/pdf'))) {
        //   $UNIQID=uniqid();
        //   $path = $nouveau_url.'uploads/document_rapport/';
        //   $NEWIMAGES = $nouveau_url.'/uploads/document_rapport/'.$IMAGENAME;
        //   $imagick = new Imagick();
        //   $imagick->readImage($NEWIMAGES);
        //   $imagick->writeImages($UNIQID.'_doc.jpeg', false);

        //   rename(FCPATH.''.$UNIQID.'_doc.jpeg', FCPATH."uploads/document_rapport/".$UNIQID."_doc.jpeg");
        //   $PATH_DOC_HISTO = $UNIQID."_doc.jpeg";

        // } else {
        //     $PATH_DOC_HISTO = $IMAGENAME;
        // }


    $PATH_DOC = $image_name_main;

    $data_requerant=array(
      'NOM'=>$NOM,
      'PRENOM'=>$PRENOM,
      'TEL'=>$TEL,
      'NUM_POLICE'=>$NUM_POLICE,
      'EMAIL'=>$EMAIL,
      'MATRICULE'=>$MATRICULE,
      'NUMERO_ADHERANT'=>$NUMERO_ADHERANT,
      'NUM_INTERMEDIAIRE'=>$NUM_INTERMEDIAIRE
    );

    $checkrequerant=$this->Model->getRequeteOne('SELECT `REQUERANT_ID` FROM `doc_requerant` WHERE `NUM_POLICE` LIKE "'.$NUM_POLICE.'" AND `NUMERO_ADHERANT` LIKE "'.$NUMERO_ADHERANT.'" AND `NUM_INTERMEDIAIRE` LIKE "'.$NUM_INTERMEDIAIRE.'"');
    if($checkrequerant) 
    {
    $REQUERANT_ID=$checkrequerant['REQUERANT_ID'];
    }
    else
    {
    $REQUERANT_ID=$this->Model->insert_last_id('doc_requerant',$data_requerant);
    }


    if (!empty($ID_NIVEAUX_ORGANIGRAMME) && !empty($STATUT_DOC_ID_AFFECTATION_FORCEE)) 
    {

      $NEWDATE=$DATE_DEMANDE.' '.date('H:i:s');
      // print_r($NEWDATE);
      // exit();
      $data_demande=array(
      'REQUERANT_ID'=>$REQUERANT_ID,
      'DOC_TYPE_ID'=>$DOC_TYPE_ID,
      'DATE_DEMANDE'=>$NEWDATE,
      'PATH_DOC'=>$PATH_DOC,
      'USER_ID'=>$this->session->userdata('SOCAR_ID_USER'),
      'COMMENTAIRE'=>$COMMENTAIRE,
      'IS_NEW'=>1,
      'STATUT_DOC_ID'=>$STATUT_DOC_ID_AFFECTATION_FORCEE
    );
    }

    else
    {

     $NEWDATE=$DATE_DEMANDE.' '.date('H:i:s');
      // print_r($NEWDATE);
      // exit();
      $data_demande=array(
      'REQUERANT_ID'=>$REQUERANT_ID,
      'DOC_TYPE_ID'=>$DOC_TYPE_ID,
      'DATE_DEMANDE'=>$NEWDATE,
      'PATH_DOC'=>$PATH_DOC,
      'USER_ID'=>$this->session->userdata('SOCAR_ID_USER'),
      'COMMENTAIRE'=>$COMMENTAIRE,
      'IS_NEW'=>1,
      'STATUT_DOC_ID'=>$STATUT_DOC_ID
      // 'ID_NIVEAUX_ORGANIGRAMME'=>$ID_NIVEAUX_ORGANIGRAMME,
      // 'ID_DIRECTION'=>$ID_DIRECTION,
      // 'ID_SERVICE'=>$ID_SERVICE,
      // 'ID_SECTION'=>$ID_SECTION,
    );
    }

    $DEMANDE_ID=$this->Model->insert_last_id('doc_demande',$data_demande);
    if ($DEMANDE_ID)
    {
      $data=array('DEMANDE_ID'=>$DEMANDE_ID,'USER_ID'=>$this->session->userdata('SOCAR_ID_USER'),'COMMENTAIRE'=>$COMMENTAIRE,'DATE_TRAITEMENT'=>date('Y-m-d H:i'),'STATUT_DOC_ID'=>1);
        // print_r($data);
        // exit();
        $DEMANDE_HISTO_ID=$this->Model->insert_last_id('doc_demande_historique',$data);

         $data_files=array(
                      'FICHIER_ID'=>1,
                      'FILE_PATH'=>$PATH_DOC,
                      'DEMANDE_ID'=>$DEMANDE_ID,
                      'STATUT_DOC_ID'=>$STATUT_DOC_ID,
                      'USER_ID'=>$this->session->userdata('SOCAR_ID_USER'),
                      'DEMANDE_HISTO_ID'=>$DEMANDE_HISTO_ID
                    ); 

       $save=$this->Model->insert_last_id('doc_demande_historique_fichier', $data_files);

      $message = "<div class='alert alert-success' id='msgs'>
                            Demande de document 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('gestion_document/Demande_Document/listejson'));  
   }

    }

  }


//FUNCTION POUR RETOURNER LES INFOS D' UN REQUERANT AFIN DE LES MODIFIER
   public function getOne($REQUERANT_ID)
    {

      $data['requerant']=$this->Model->getRequeteOne('SELECT * FROM `doc_requerant` WHERE md5(`REQUERANT_ID`)="'.$REQUERANT_ID.'"');
      $data['title']="Modification d'un réquerant";
      $this->load->view('Requerant_Add_View',$data);

    }

    //FUNCTION POUR DESACTIVER/ACTIVER UN REQUERANT
    public function desactivation($REQUERANT_ID)
    {
    $requerant=$this->Model->getRequeteOne('SELECT * FROM `doc_requerant` WHERE `REQUERANT_ID`="'.$REQUERANT_ID.'"');
    if ($requerant['STATUT']==1) 
    {
    $this->Model->update('doc_requerant',array('REQUERANT_ID'=>$REQUERANT_ID),array('STATUT'=>0));
    $message = "<div class='alert alert-success' id='message'>Désactivation faite avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>"; 
    }
    else
    {
     $this->Model->update('doc_requerant',array('REQUERANT_ID'=>$REQUERANT_ID),array('STATUT'=>1)); 
     $message = "<div class='alert alert-success' id='message'>Activation faite avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    }


    $this->session->set_flashdata(array('message'=>$message));
    redirect(base_url('gestion_document/Demande_Document/req_list'));

    }

  //FUNCTION POUR MODIFIER UN REQUERANT
   public function update()
   {

      $NOM=$this->input->post('NOM');
      $PRENOM=$this->input->post('PRENOM');
      $TEL=$this->input->post('TEL');
      $NUM_POLICE=$this->input->post('NUM_POLICE');
      $EMAIL=$this->input->post('EMAIL');
      $MATRICULE=$this->input->post('MATRICULE');
      $NUMERO_ADHERANT=$this->input->post('NUMERO_ADHERANT');
      $NUM_INTERMEDIAIRE=$this->input->post('NUM_INTERMEDIAIRE');

      $REQUERANT_ID=$this->input->post('REQUERANT_ID');

      $this->form_validation->set_rules('NOM', '', 'required',array('required'=>'Le nom est obligatoire'));
      $this->form_validation->set_rules('PRENOM', '', 'required',array('required'=>'Le prénom est obligatoire'));
      $this->form_validation->set_rules('TEL', '', 'required',array('required'=>'Le Téléphone est obligatoire'));
      $this->form_validation->set_rules('MATRICULE', '', 'required',array('required'=>'Le matricule est obligatoire'));
      $this->form_validation->set_rules('NUMERO_ADHERANT', '', 'required',array('required'=>'Le champ est obligatoire'));
      $this->form_validation->set_rules('NUM_INTERMEDIAIRE', '', 'required',array('required'=>'Le mail est obligatoire'));
      $this->form_validation->set_rules('NUM_POLICE', '', 'required',array('required'=>'Le numéro de police est obligatoire'));

   if ($this->form_validation->run() == FALSE)
   {

      $data['requerant']=$this->Model->getRequeteOne('SELECT * FROM `doc_requerant` WHERE `REQUERANT_ID`="'.$REQUERANT_ID.'"');
      $data['title']="Modification d'un réquerant";
      $this->load->view('Requerant_Add_View',$data);

   }
   else
  {
    $datas=array(
      'NOM'=>$NOM,
      'PRENOM'=>$PRENOM,
      'TEL'=>$TEL,
      'NUM_POLICE'=>$NUM_POLICE,
      'MATRICULE'=>$MATRICULE,
      'EMAIL'=>$EMAIL,
      'NUM_INTERMEDIAIRE'=>$NUM_INTERMEDIAIRE,
      'NUMERO_ADHERANT'=>$NUMERO_ADHERANT);
    $this->Model->update('doc_requerant',array('REQUERANT_ID'=>$REQUERANT_ID),$datas);
    $message = "<div class='alert alert-success' id='message'>Modification faite avec succés
                            <button type='button' class='close' data-dismiss='alert'>&times;</button>
                      </div>";
    $this->session->set_flashdata(array('message'=>$message));
    redirect(base_url('gestion_document/Demande_Document/req_list'));
  }

   }

//LISTE DES DEMANDES AVEC DATATABLE SIMPLE 
public function demande_list()
    {

  $result=$this->Model->getRequete("SELECT PROFIL_ID_DESIGNE,doc_demande.DOC_TYPE_ID,doc_demande.STATUT_DOC_ID,doc_demande.DEMANDE_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,MATRICULE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,doc_type.DESC_TYPE,doc_statut.DESC_STATUT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION,doc_demande.DATE_DEMANDE,doc_demande.PATH_DOC,doc_demande.COMMENTAIRE, CONCAT(config_user.NOM,' ',config_user.PRENOM) USERS FROM `doc_demande` JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN config_user ON config_user.ID_USER=doc_demande.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE doc_demande.DOC_TYPE_ID IN (SELECT config_doc_profil_etape.DOC_TYPE_ID FROM config_doc_profil_etape WHERE config_doc_profil_etape.PROFIL_ID=".$this->session->userdata('SOCAR_PROFIL_ID').") ORDER BY doc_demande.DATE_DEMANDE DESC");
         $tabledata=array();
   
   foreach ($result as $key)
   {

   if ($key['PROFIL_ID_DESIGNE']) {
     $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$key['PROFIL_ID_DESIGNE']."");
   }
   else
   {
    $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$key['STATUT_DOC_ID']." AND DOC_TYPE_ID=".$key['DOC_TYPE_ID']."");
   }
    
    $demande=array();
    //$demande[]=date('Y-m-d H:i:s', strtotime($key['DATE_DEMANDE']. ' + 1 hours'));
    $demande[]=date('Y-m-d H:i:s', strtotime($key['DATE_DEMANDE']));
    $demande[]=$key['REQUERANT'].' ('.$key['NUM_POLICE'].')';
    $demande[]=$key['MATRICULE'];
    $demande[]=$key['TEL'].'<br>'.$key['EMAIL'];
    $demande[]=$key['DESC_TYPE'];
    $demande[]=$profiles['DESCRIPTION'];
    $demande[]=$key['DESC_STATUT'];
    
    //$demande[]=$key['USERS'];
    $demande['OPTIONS'] = '<div class="dropdown ">
    <a class="btn btn-primary btn-sm dropdown-toggle" data-toggle="dropdown">Action
    <span class="caret"></span></a>
    <ul class="dropdown-menu dropdown-menu-left">
    ';
    $demande['OPTIONS'] .= "<li><a href='".base_url("gestion_document/Demande_Document/get_info_traitement/".md5($key['DEMANDE_ID']))."'><i style='font-size: 20px' class='fa fa-eye' aria-hidden='true'></i> Détail</a></li>";
    $demande['OPTIONS'] .= "   </ul>";

    $tabledata[]=$demande;
  }

  $template = array(
    'table_open' => '<table id="demande_liste" class="table table-bordered table-striped table-hover table-condensed">',
    'table_close' => '</table>'
  );
  $this->table->set_template($template);
  $this->table->set_heading(array('Demandé','Requérant','Matricule','Contacts','Type','Niveau','Etape','Options'));
  $data['demandes']=$tabledata;
  // $data['title']='Demande de document';
  $data['title']='';
  $this->load->view('Demande_Document_List_View',$data);
            
}



//CHARGEMENT VIEW LISTE JSON DES DEMANDES

function listejson()
{
  $this->Is_Connected();
// $data['title']='Demande de document';
   $data['title']='';
$this->load->view('Liste_Json_Demande_Document_List_View',$data);
}


//LISTE JSON DES TOUTES LES  DEMANDES
public function listing()
{
$critere = '';
$between_data = '';
$query_principal="SELECT PROFIL_ID_DESIGNE,doc_demande.DOC_TYPE_ID,doc_demande.STATUT_DOC_ID,doc_demande.DEMANDE_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,MATRICULE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,doc_type.DESC_TYPE,doc_statut.DESC_STATUT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION,doc_demande.DATE_DEMANDE,doc_demande.PATH_DOC,doc_demande.COMMENTAIRE, CONCAT(config_user.NOM,' ',config_user.PRENOM) USERS FROM `doc_demande` JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN config_user ON config_user.ID_USER=doc_demande.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE doc_demande.DOC_TYPE_ID IN (SELECT config_doc_profil_etape.DOC_TYPE_ID FROM config_doc_profil_etape WHERE config_doc_profil_etape.PROFIL_ID=".$this->session->userdata('SOCAR_PROFIL_ID').")";

$var_search= !empty($_POST['search']['value']) ? $_POST['search']['value'] : null;

 $limit='LIMIT 0,10';

 if($_POST['length'] != -1){
  $limit='LIMIT '.$_POST["start"].','.$_POST["length"];
}
$order_by='';

$order_column=array('DATE_DEMANDE','DEMANDE_ID','doc_requerant.NOM','doc_requerant.PRENOM','MATRICULE','TEL','EMAIL','DESC_TYPE','DESC_STATUT');

$order_by = isset($_POST['order']) ? ' ORDER BY '.$order_column[$_POST['order']['0']['column']] .'  '.$_POST['order']['0']['dir'] : ' ORDER BY DEMANDE_ID ASC';

$search = !empty($_POST['search']['value']) ? (" AND (doc_requerant.NOM LIKE '%$var_search%' OR doc_requerant.PRENOM LIKE '%$var_search%' OR TEL LIKE '%$var_search%' OR EMAIL LIKE '%$var_search%'  OR DATE_DEMANDE LIKE '%$var_search%' OR DESC_STATUT LIKE '%$var_search%' OR  CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) LIKE '%$var_search%')") : NULL;

$critere = '';

$query_secondaire=$query_principal.' '.$critere.' '.$search.' '.$order_by.'   '.$limit;
$query_filter = $query_principal.' '.$critere.' '.$search;
$fetch_op = $this->Model->datatable($query_secondaire);

$data = array();
$i=0;
foreach ($fetch_op as $row) {
 $i++;
 $sub_array = array();

if ($row->PROFIL_ID_DESIGNE) 
{
$profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$row->PROFIL_ID_DESIGNE."");
}
else
{
 $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$row->STATUT_DOC_ID." AND DOC_TYPE_ID=".$row->DOC_TYPE_ID."");
}

$DATE_DEMANDE=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE)); 
//$sub_array[]=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE. ' + 1 hours'));
$sub_array[]=$DATE_DEMANDE;
$sub_array[]=$row->REQUERANT.' ('.$row->NUM_POLICE.')';
// $sub_array[]=$row->MATRICULE;
// $sub_array[]=$row->TEL.'<br>'.$row->EMAIL;
$sub_array[]=$row->DESC_TYPE;
$sub_array[]=$profiles['DESCRIPTION'];
$sub_array[]=$row->DESC_STATUT;
// $options= '<div class="dropdown ">
// <a class="btn btn-primary btn-sm dropdown-toggle" data-toggle="dropdown">Action
// <span class="caret"></span></a>
// <ul class="dropdown-menu dropdown-menu-left">
// ';
$options= "<a href='".base_url("gestion_document/Demande_Document/get_info_traitement/".md5($row->DEMANDE_ID))."'><i style='font-size: 20px' class='fa fa-eye' aria-hidden='true'></i> Détail</a>";
// $options .= "   </ul>";
$sub_array[]=$options;
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST['draw']),
"recordsTotal" =>$this->Model->all_data($query_principal),
"recordsFiltered" => $this->Model->filtrer($query_filter),
"data" => $data
);
echo json_encode($output);
}


//LISTE JSON DES DEMANDES A TRAITER

function listejson_atraiter()
{
  $this->Is_Connected();
// $data['title']='Demande de document';
$data['title']='';
$this->load->view('Liste_Json_Demande_Document_List_Atraiter_View',$data);
}


public function listing_a_traiter()
{
$critere = '';
$between_data = '';
$query_principal="SELECT PROFIL_ID_DESIGNE,doc_demande.DOC_TYPE_ID,doc_demande.STATUT_DOC_ID,doc_demande.DEMANDE_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,MATRICULE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,doc_type.DESC_TYPE,doc_statut.DESC_STATUT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION,doc_demande.DATE_DEMANDE,doc_demande.PATH_DOC,doc_demande.COMMENTAIRE, CONCAT(config_user.NOM,' ',config_user.PRENOM) USERS FROM `doc_demande` LEFT JOIN config_doc_profil_etape ON config_doc_profil_etape.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN config_user ON config_user.ID_USER=doc_demande.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE config_doc_profil_etape.PROFIL_ID=".$this->session->userdata('SOCAR_PROFIL_ID')." AND (doc_demande.PROFIL_ID_DESIGNE IS NULL OR doc_demande.PROFIL_ID_DESIGNE=".$this->session->userdata('SOCAR_PROFIL_ID').")";

$var_search= !empty($_POST['search']['value']) ? $_POST['search']['value'] : null;

 $limit='LIMIT 0,10';

 if($_POST['length'] != -1){
  $limit='LIMIT '.$_POST["start"].','.$_POST["length"];
}
$order_by='';

$order_column=array('DATE_DEMANDE','DEMANDE_ID','doc_requerant.NOM','doc_requerant.PRENOM','MATRICULE','TEL','EMAIL','DESC_TYPE','DESC_STATUT');

$order_by = isset($_POST['order']) ? ' ORDER BY '.$order_column[$_POST['order']['0']['column']] .'  '.$_POST['order']['0']['dir'] : ' ORDER BY DEMANDE_ID ASC';

$search = !empty($_POST['search']['value']) ? (" AND (doc_requerant.NOM LIKE '%$var_search%' OR doc_requerant.PRENOM LIKE '%$var_search%' OR TEL LIKE '%$var_search%' OR EMAIL LIKE '%$var_search%'  OR DATE_DEMANDE LIKE '%$var_search%' OR DESC_STATUT LIKE '%$var_search%' OR  CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) LIKE '%$var_search%')") : NULL;

$critere = '';

$query_secondaire=$query_principal.' '.$critere.' '.$search.' '.$order_by.'   '.$limit;
$query_filter = $query_principal.' '.$critere.' '.$search;
$fetch_op = $this->Model->datatable($query_secondaire);

$data = array();
$i=0;
foreach ($fetch_op as $row) {
 $i++;
 $sub_array = array();

if ($row->PROFIL_ID_DESIGNE) 
{
$profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$row->PROFIL_ID_DESIGNE."");
}
else
{
 $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$row->STATUT_DOC_ID." AND DOC_TYPE_ID=".$row->DOC_TYPE_ID."");
}

$DATE_DEMANDE=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE)); 
$sub_array[]=$DATE_DEMANDE;
$sub_array[]=$row->REQUERANT.' ('.$row->NUM_POLICE.')';
$sub_array[]=$row->DESC_TYPE;
$sub_array[]=$profiles['DESCRIPTION'];
$sub_array[]=$row->DESC_STATUT;
$options= "<a href='".base_url("gestion_document/Demande_Document/get_info_traitement/".md5($row->DEMANDE_ID))."'><i style='font-size: 20px' class='fa fa-eye' aria-hidden='true'></i> Détail</a>";
$sub_array[]=$options;
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST['draw']),
"recordsTotal" =>$this->Model->all_data($query_principal),
"recordsFiltered" => $this->Model->filtrer($query_filter),
"data" => $data
);
echo json_encode($output);
}


//LISTE JSON DES DEMANDES TRAITEES

function listejson_traitees()
{
  $this->Is_Connected();
// $data['title']='Demande de document';
$data['title']='';
$this->load->view('Liste_Json_Demande_Document_List_Traitees_View',$data);
}

public function listing_traitees()
{
$critere = '';
$between_data = '';
$query_principal="SELECT PROFIL_ID_DESIGNE,doc_demande.DOC_TYPE_ID,doc_demande.STATUT_DOC_ID,doc_demande.DEMANDE_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,MATRICULE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,doc_type.DESC_TYPE,doc_statut.DESC_STATUT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION,doc_demande.DATE_DEMANDE,doc_demande.PATH_DOC,doc_demande.COMMENTAIRE, CONCAT(config_user.NOM,' ',config_user.PRENOM) USERS FROM `doc_demande` JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN config_user ON config_user.ID_USER=doc_demande.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE doc_demande.DEMANDE_ID IN (SELECT doc_demande_historique.DEMANDE_ID FROM doc_demande_historique WHERE doc_demande_historique.USER_ID=".$this->session->userdata('SOCAR_ID_USER').")";

$var_search= !empty($_POST['search']['value']) ? $_POST['search']['value'] : null;

 $limit='LIMIT 0,10';

 if($_POST['length'] != -1){
  $limit='LIMIT '.$_POST["start"].','.$_POST["length"];
}
$order_by='';

$order_column=array('DATE_DEMANDE','DEMANDE_ID','doc_requerant.NOM','doc_requerant.PRENOM','MATRICULE','TEL','EMAIL','DESC_TYPE','DESC_STATUT');

$order_by = isset($_POST['order']) ? ' ORDER BY '.$order_column[$_POST['order']['0']['column']] .'  '.$_POST['order']['0']['dir'] : ' ORDER BY DEMANDE_ID ASC';

$search = !empty($_POST['search']['value']) ? (" AND (doc_requerant.NOM LIKE '%$var_search%' OR doc_requerant.PRENOM LIKE '%$var_search%' OR TEL LIKE '%$var_search%' OR EMAIL LIKE '%$var_search%'  OR DATE_DEMANDE LIKE '%$var_search%' OR DESC_STATUT LIKE '%$var_search%' OR  CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) LIKE '%$var_search%')") : NULL;

$critere = '';

$query_secondaire=$query_principal.' '.$critere.' '.$search.' '.$order_by.'   '.$limit;
$query_filter = $query_principal.' '.$critere.' '.$search;
$fetch_op = $this->Model->datatable($query_secondaire);

$data = array();
$i=0;
foreach ($fetch_op as $row) {
 $i++;
 $sub_array = array();

if ($row->PROFIL_ID_DESIGNE) 
{
$profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$row->PROFIL_ID_DESIGNE."");
}
else
{
 $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$row->STATUT_DOC_ID." AND DOC_TYPE_ID=".$row->DOC_TYPE_ID."");
}

$DATE_DEMANDE=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE)); 
$sub_array[]=$DATE_DEMANDE;
$sub_array[]=$row->REQUERANT.' ('.$row->NUM_POLICE.')';
$sub_array[]=$row->DESC_TYPE;
$sub_array[]=$profiles['DESCRIPTION'];
$sub_array[]=$row->DESC_STATUT;
$options= "<a href='".base_url("gestion_document/Demande_Document/get_info_traitement/".md5($row->DEMANDE_ID))."'><i style='font-size: 20px' class='fa fa-eye' aria-hidden='true'></i> Détail</a>";
$sub_array[]=$options;
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST['draw']),
"recordsTotal" =>$this->Model->all_data($query_principal),
"recordsFiltered" => $this->Model->filtrer($query_filter),
"data" => $data
);
echo json_encode($output);
}


//LISTE JSON DES TOUTES LES  DEMANDES EN ATTENTE
function listejson_atente()
{
  $this->Is_Connected();
// $data['title']='Demande de document';
$data['title']='';
$this->load->view('Liste_Json_Demande_Document_List_Enattente_View',$data);
}

public function listing_attente()
{
$critere = '';
$between_data = '';
$query_principal="SELECT PROFIL_ID_DESIGNE,doc_demande.DOC_TYPE_ID,doc_demande.STATUT_DOC_ID,doc_demande.DEMANDE_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,MATRICULE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,doc_type.DESC_TYPE,doc_statut.DESC_STATUT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION,doc_demande.DATE_DEMANDE,doc_demande.PATH_DOC,doc_demande.COMMENTAIRE, CONCAT(config_user.NOM,' ',config_user.PRENOM) USERS FROM `doc_demande` JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN config_user ON config_user.ID_USER=doc_demande.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID LEFT JOIN config_doc_profil_etape ON config_doc_profil_etape.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE config_doc_profil_etape.PROFIL_ID!=".$this->session->userdata('SOCAR_PROFIL_ID')." AND doc_demande.DEMANDE_ID NOT IN (SELECT doc_demande_historique.DEMANDE_ID FROM doc_demande_historique WHERE doc_demande_historique.USER_ID=".$this->session->userdata('SOCAR_ID_USER').")";

$var_search= !empty($_POST['search']['value']) ? $_POST['search']['value'] : null;

 $limit='LIMIT 0,10';

 if($_POST['length'] != -1){
  $limit='LIMIT '.$_POST["start"].','.$_POST["length"];
}
$order_by='';

$order_column=array('DATE_DEMANDE','DEMANDE_ID','doc_requerant.NOM','doc_requerant.PRENOM','MATRICULE','TEL','EMAIL','DESC_TYPE','DESC_STATUT');

$order_by = isset($_POST['order']) ? ' ORDER BY '.$order_column[$_POST['order']['0']['column']] .'  '.$_POST['order']['0']['dir'] : ' ORDER BY DEMANDE_ID ASC';

$search = !empty($_POST['search']['value']) ? (" AND (doc_requerant.NOM LIKE '%$var_search%' OR doc_requerant.PRENOM LIKE '%$var_search%' OR TEL LIKE '%$var_search%' OR EMAIL LIKE '%$var_search%'  OR DATE_DEMANDE LIKE '%$var_search%' OR DESC_STATUT LIKE '%$var_search%' OR  CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) LIKE '%$var_search%')") : NULL;

$critere = '';

$query_secondaire=$query_principal.' '.$critere.' '.$search.' '.$order_by.'   '.$limit;
$query_filter = $query_principal.' '.$critere.' '.$search;
$fetch_op = $this->Model->datatable($query_secondaire);

$data = array();
$i=0;
foreach ($fetch_op as $row) {
 $i++;
 $sub_array = array();

if ($row->PROFIL_ID_DESIGNE) 
{
$profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$row->PROFIL_ID_DESIGNE."");
}
else
{
 $profiles=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$row->STATUT_DOC_ID." AND DOC_TYPE_ID=".$row->DOC_TYPE_ID."");
}

$DATE_DEMANDE=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE)); 
//$sub_array[]=date('Y-m-d H:i:s', strtotime($row->DATE_DEMANDE. ' + 1 hours'));
$sub_array[]=$DATE_DEMANDE;
$sub_array[]=$row->REQUERANT.' ('.$row->NUM_POLICE.')';
// $sub_array[]=$row->MATRICULE;
// $sub_array[]=$row->TEL.'<br>'.$row->EMAIL;
$sub_array[]=$row->DESC_TYPE;
$sub_array[]=$profiles['DESCRIPTION'];
$sub_array[]=$row->DESC_STATUT;
// $options= '<div class="dropdown ">
// <a class="btn btn-primary btn-sm dropdown-toggle" data-toggle="dropdown">Action
// <span class="caret"></span></a>
// <ul class="dropdown-menu dropdown-menu-left">
// ';
$options= "<a href='".base_url("gestion_document/Demande_Document/get_info_traitement/".md5($row->DEMANDE_ID))."'><i style='font-size: 20px' class='fa fa-eye' aria-hidden='true'></i> Détail</a>";
// $options .= "   </ul>";
$sub_array[]=$options;
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST['draw']),
"recordsTotal" =>$this->Model->all_data($query_principal),
"recordsFiltered" => $this->Model->filtrer($query_filter),
"data" => $data
);
echo json_encode($output);
}




//LA LISTE DES REQUERANTS
public function req_list()
    {
  $requerant=$this->Model->getRequete("SELECT STATUT,REQUERANT_ID,CONCAT(doc_requerant.NOM,' ',doc_requerant.PRENOM) REQUERANT,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NUM_POLICE,NUMERO_ADHERANT,DATE_INSERTION,MATRICULE,NUM_INTERMEDIAIRE FROM doc_requerant ORDER BY doc_requerant.NOM ASC");
         $tabledata=array();
   
   foreach ($requerant as $key)
   {

    $req=array();
    $req[]=$key['REQUERANT'];
    $req[]=$key['NUM_POLICE'];
    $req[]=$key['NUMERO_ADHERANT'];
    $req[]=$key['MATRICULE'];
    $req[]=$key['NUM_INTERMEDIAIRE'];
    $req[]=$key['TEL'].'<br>'.$key['EMAIL'];
    if ($key['STATUT']==1) 
    {
     $statut='Désactiver';
     $mess='Voulez-vous désactiver';
     $req[]='<i class="fa fa-check-square" aria-hidden="true"></i>';

    }
    else
    {
     $statut='Activer';
     $mess='Voulez-vous activer';
     $req[]='<i class="fa fa-minus-circle" aria-hidden="true"></i>'; 
    }
    $req[]=$key['DATE_INSERTION'];


    $req['OPTIONS'] = '<div class="dropdown ">
    <a class="btn btn-primary btn-sm dropdown-toggle" data-toggle="dropdown">Action
    <span class="caret"></span></a>
    <ul class="dropdown-menu dropdown-menu-right">
    ';

    $req['OPTIONS'] .= "<li><a href='".base_url("gestion_document/Demande_Document/getOne/".md5($key['REQUERANT_ID']))."'>Modifier</a></li>";
    $req['OPTIONS'] .= "<li><a hre='#' data-toggle='modal' 
                                  data-target='#desactivation" . $key['REQUERANT_ID'] . "'><font color='red'>".$statut."</font></a></li>";
    $req['OPTIONS'] .= "   </ul>
                              </div>
                                    <div class='modal fade' id='desactivation" . $key['REQUERANT_ID'] . "'>
                                        <div class='modal-dialog'>
                                            <div class='modal-content'>

                                                <div class='modal-body'>
                                                    <h5>".$mess."  :<b>" . $key['REQUERANT'] . "</b>?</h5>
                                                </div>

                                               <div class='modal-footer'>
                                                    <a class='btn btn-danger btn-md' href='" . base_url('gestion_document/Demande_Document/desactivation/'.$key['REQUERANT_ID']). "'>".$statut."</a>
                                                    <button class='btn btn-primary btn-md' class='close' data-dismiss='modal'>Annuler</button>
                                                </div>

                                            </div>
                                        </div>
                                    </div>";

    $req['OPTIONS'] .= "   </ul>";

    $tabledata[]=$req;
  }

  $template = array(
    'table_open' => '<table id="requerant_liste" class="table table-bordered table-striped table-hover table-condensed">',
    'table_close' => '</table>'
  );
  $this->table->set_template($template);
  $this->table->set_heading(array('Requérant','Num Police','Num Adhérant','Matricule','Num intermédiaire','Contacts','Statut','Enregistré','Options'));
  $data['requerants']=$tabledata;
  $data['title']='Liste des Requérants';
  $this->load->view('Requerant_List_View',$data);
            
    }

//FONCTION QUI RETOURNE LES INFOS DU DETAIL
  public function get_info_traitement($id)
    {
    $data['title']='Gestion de documents';
    $data['requerant']=$this->Model->getRequeteOne('SELECT PROFIL_ID_DESIGNE,doc_demande.`DEMANDE_ID`,doc_demande.DOC_TYPE_ID,doc_demande.DATE_DEMANDE,doc_requerant.TEL,doc_requerant.EMAIL,doc_requerant.NOM,doc_requerant.PRENOM,doc_requerant.NUM_POLICE,doc_demande.PATH_DOC,MATRICULE,doc_demande.STATUT_DOC_ID,doc_statut.DESC_STATUT,doc_demande.COMMENTAIRE,doc_type.DESC_TYPE FROM `doc_demande` JOIN doc_requerant ON doc_requerant.REQUERANT_ID=doc_demande.REQUERANT_ID LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID LEFT JOIN doc_type ON doc_type.DOC_TYPE_ID=doc_demande.DOC_TYPE_ID WHERE md5(`DEMANDE_ID`)="'.$id.'"');

    if ($data['requerant']['PROFIL_ID_DESIGNE']) 
    {
     $data['profiles']=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM config_profil WHERE config_profil.PROFIL_ID=".$data['requerant']['PROFIL_ID_DESIGNE']);
    }
    else
    {
    $data['profiles']=$this->Model->getRequeteOne("SELECT config_profil.DESCRIPTION FROM `config_doc_profil_etape` JOIN config_profil ON config_profil.PROFIL_ID=config_doc_profil_etape.PROFIL_ID WHERE STATUT_DOC_ID=".$data['requerant']['STATUT_DOC_ID']." AND DOC_TYPE_ID=".$data['requerant']['DOC_TYPE_ID'].""); 
    }

    if ($data['requerant']['PROFIL_ID_DESIGNE']) 
    {
    $data['statuts']=$this->Model->getRequeteOne("SELECT DEMANDE_ID FROM doc_demande WHERE doc_demande.PROFIL_ID_DESIGNE=".$this->session->userdata('SOCAR_PROFIL_ID')."");
    }
    else
    {
     $data['statuts']=$this->Model->getRequeteOne("SELECT DOC_PRO_ETAPE_ID FROM config_doc_profil_etape  WHERE config_doc_profil_etape.PROFIL_ID=".$this->session->userdata('SOCAR_PROFIL_ID')." AND STATUT_DOC_ID=".$data['requerant']['STATUT_DOC_ID']." AND DOC_TYPE_ID=".$data['requerant']['DOC_TYPE_ID'].""); 
    }
    $data['niveaus']=$this->Model->getRequete('SELECT * FROM `organigramme_niveau` WHERE `ID_NIVEAUX_ORGANIGRAMME` NOT IN (0,2) ORDER BY `DESCRIPTION` ASC');

    $data['actions']=$this->Model->getRequete("SELECT doc_action.ACTION_DOC_ID,doc_action.DESC_ACTION,doc_action.ACTION FROM `doc_statut_action` JOIN doc_action ON doc_action.ACTION_DOC_ID=doc_statut_action.ACTION_DOC_ID WHERE doc_statut_action.STATUT_DOC_ID=".$data['requerant']['STATUT_DOC_ID']." AND ".$data['requerant']['STATUT_DOC_ID']." IN (SELECT STATUT_DOC_ID FROM doc_statut_action)");

    $data['doc_type']=$this->Model->getRequete("SELECT doc_type_etape.FICHIER_ID,`DESCRIPTION` FROM `doc_type_etape` JOIN doc_type_document_etape ON doc_type_document_etape.FICHIER_ID=doc_type_etape.FICHIER_ID WHERE doc_type_etape.STATUT=1 AND doc_type_document_etape.STATUT_DOC_ID=".$data['requerant']['STATUT_DOC_ID']."");

    $data['check']=$this->Model->getRequeteOne("SELECT doc_demande.DEMANDE_ID,doc_demande.STATUT_DOC_ID FROM `doc_demande` JOIN doc_type_document_etape ON doc_type_document_etape.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE md5(`DEMANDE_ID`)='".$id."' LIMIT 1");

    $data['doc_statut']=$this->Model->getRequete('SELECT * FROM `doc_statut` WHERE `IS_ACTIF`=1 ORDER BY `DESC_STATUT` ASC');
    $data['derniertraitement']=$this->Model->getRequeteOne('SELECT `USER_ID` FROM `doc_demande_historique` WHERE md5(DEMANDE_ID)="'.$id.'" ORDER BY `DEMANDE_HISTO_ID` DESC LIMIT 1');
    $this->load->view('Demande_Detail_Views.php',$data);

    }


  //FONCTION QUI CHARGE LA LISTE HISTORIQUE DES DEMANDES
  function historique_list()
  {
    $ID_DEMANDE=$this->input->post('ID_DEMANDE');
    $query_principal='SELECT DESC_ACTION,DEMANDE_HISTO_ID,FILE_PATH,doc_statut.DESC_STATUT,CONCAT(config_user.NOM," ",config_user.PRENOM) AS USERS,doc_demande_historique.COMMENTAIRE,doc_demande_historique.DATE_TRAITEMENT,organigramme_direction.DESCRIPTION DIRECTION,organigramme_service.DESCRIPTION SERVICE,organigramme_section.DESCRIPTION SECTION FROM `doc_demande_historique` LEFT JOIN doc_demande ON doc_demande.DEMANDE_ID=doc_demande_historique.DEMANDE_ID JOIN config_user ON config_user.ID_USER=doc_demande_historique.USER_ID LEFT JOIN rh_employe_affectation ON rh_employe_affectation.ID_EMPLOYE=config_user.ID_EMPLOYE LEFT JOIN doc_statut ON doc_statut.STATUT_DOC_ID=doc_demande_historique.STATUT_DOC_ID LEFT JOIN organigramme_direction ON organigramme_direction.ID_DIRECTION=rh_employe_affectation.ID_DIRECTION LEFT JOIN organigramme_service ON organigramme_service.ID_SERVICE=rh_employe_affectation.ID_SERVICE LEFT JOIN organigramme_section ON organigramme_section.ID_SECTION=rh_employe_affectation.ID_SECTION LEFT JOIN doc_action ON doc_action.ACTION_DOC_ID=doc_demande_historique.ACTION_DOC_ID WHERE doc_demande_historique.DEMANDE_ID='.$ID_DEMANDE;

    $var_search = !empty($_POST['search']['value']) ? $_POST['search']['value'] : null;
    $limit = 'LIMIT 0,10';
    if($_POST['length'] != -1)
    {
      $limit = 'LIMIT ' . $_POST["start"] . ',' . $_POST["length"];
    }

    $order_by = '';
    $order_column = array('DESC_STATUT','doc_demande_historique.DATE_TRAITEMENT','DESC_ACTION','doc_demande_historique.COMMENTAIRE','doc_demande_historique.DATE_TRAITEMENT','CONCAT(config_user.NOM," ",config_user.PRENOM)');

    $order_by = isset($_POST['order']) ? ' ORDER BY ' . $order_column[$_POST['order']['0']['column']] . '  ' . $_POST['order']['0']['dir'] : ' ORDER BY doc_demande_historique.DATE_TRAITEMENT DESC';
    $search = !empty($_POST['search']['value']) ? (" AND (doc_statut.DESC_STATUT LIKE '%$var_search%' OR CONCAT(config_user.NOM,' ',config_user.PRENOM) LIKE '%$var_search%' OR DESC_ACTION LIKE '%$var_search%' OR doc_demande_historique.DATE_TRAITEMENT  LIKE '%$var_search%' OR doc_demande_historique.COMMENTAIRE LIKE '%$var_search%')") : '';
    $critaire = '';
    $query_secondaire = $query_principal . ' ' . $critaire . ' ' . $search . ' ' . $order_by . '   ' . $limit;
    $query_filter = $query_principal . ' ' . $critaire . ' ' . $search;
    $array_histo = $this->Model->datatable($query_secondaire);
    $array_demandes=array();
    $j=1;
    foreach($array_histo as $row)
    {
     if ($row->DIRECTION)
     {
     $SERVICE=$row->DIRECTION;
     }
     if ($row->SERVICE)
     {
     $SERVICE=$row->SERVICE;
     }
     if ($row->SECTION)
     {
     $SERVICE=$row->SECTION;
     }
      
      $sub_array=array();
      $sub_array[]=$j++;
      $sub_array[]=date('d-m-Y H:i',strtotime($row->DATE_TRAITEMENT));
      $sub_array[]=$row->DESC_ACTION;
      $sub_array[]=$row->COMMENTAIRE;
      
      $sub_array[]=$row->DESC_STATUT;
      $sub_array[]=$SERVICE;
      $sub_array[]=$row->USERS;

      $liste_des_fichies = " ";

      //FONCTION QUI RETOURNE LES FICHIERS UPLODES
      $files=$this->Model->getRequete('SELECT doc_type_etape.DESCRIPTION,doc_demande_historique_fichier.FILE_PATH FROM `doc_demande_historique_fichier` LEFT JOIN doc_type_etape ON doc_type_etape.FICHIER_ID=doc_demande_historique_fichier.FICHIER_ID WHERE doc_demande_historique_fichier.DEMANDE_HISTO_ID='.$row->DEMANDE_HISTO_ID.'');
      if($files)
       {
       foreach ($files as $file){
      $urlpass = base_url();
      $elementspas = explode("/", $urlpass);
      $nouveau_urlpass = null ;
        $indicepass = sizeof($elementspas);
        for ($i = 0; $i < ($indicepass - 2); $i++) {
            $nouveau_urlpass .=$elementspas[$i] . '/';
        }

        $liste_des_fichies.= "
        <table style='font-size: 12px; border-collapse: collapse;'>
        <tr><td>".$file['DESCRIPTION']."</td><td> <a href='".$nouveau_urlpass."uploads/document_rapport/".$file['FILE_PATH']."' target='_blank'><i style='font-size: 15px' class='fa fa-file' aria-hidden='true'></i></a></tr>
        </table>";
 
        }
        }
        else
        {
        $liste_des_fichies .= "N/A";  
        }

      $sub_array[]=$liste_des_fichies;

      $array_demandes[] = $sub_array;
    }
    $output = array(
      "draw" => intval($_POST['draw']),
      "recordsTotal" => $this->Model->all_data($query_principal),
      "recordsFiltered" => $this->Model->filtrer($query_filter),
      "data" => $array_demandes
    );
    echo json_encode($output);
  }


//FONCTION QUI ENREGISTRE LES TRAIMENTS POUR CHAQUE ETAPE
  function addcommentaire()
  {
      $DEMANDE_ID=$this->input->post('ID_DEMANDE');
      $COMMENTAIRE=$this->input->post('COMMENTAIRE');
      $ACTION_DOC_ID=$this->input->post('url_data');
      $ID_NIVEAUX_ORGANIGRAMME=$this->input->post('ID_NIVEAUX_ORGANIGRAMME');
      $STATUT_DOC_ID_AFFECTATION_FORCEE=$this->input->post('STATUT_DOC_ID');
      $SERVICE=$this->input->post('SERVICE');
      $PROFIL_ID_DESIGNE=NULL;
      if($SERVICE==3 AND $ID_NIVEAUX_ORGANIGRAMME==3) {
      $PROFIL_ID_DESIGNE=3;
      }
      if($SERVICE==4 AND $ID_NIVEAUX_ORGANIGRAMME==3) {
      $PROFIL_ID_DESIGNE=4;
      }
      if($SERVICE==4 AND $ID_NIVEAUX_ORGANIGRAMME==3) {
      $PROFIL_ID_DESIGNE=4;
      }
      if($SERVICE==5 AND $ID_NIVEAUX_ORGANIGRAMME==3) {
      $PROFIL_ID_DESIGNE=2;
      }
      if($SERVICE==5 AND $ID_NIVEAUX_ORGANIGRAMME==1) {
        $PROFIL_ID_DESIGNE=9;
        }
      if($SERVICE==10 AND $ID_NIVEAUX_ORGANIGRAMME==1)
       {
        $PROFIL_ID_DESIGNE=12;
       }

      // print_r($ID_NIVEAUX_ORGANIGRAMME.' '.$SERVICE.' '.$PROFIL_ID_DESIGNE);
      // exit();

      $ACTIONS=$this->Model->getRequeteOne("SELECT ACTION_DOC_ID,`ACTION` FROM `doc_action` WHERE `ACTION_DOC_ID`=".$ACTION_DOC_ID);
      $ETAPE_ACTUEL=$this->Model->getRequeteOne("SELECT doc_demande.DEMANDE_ID,config_doc_profil_etape.STATUT_DOC_ID,config_doc_profil_etape.NUM FROM `doc_demande` JOIN config_doc_profil_etape ON config_doc_profil_etape.STATUT_DOC_ID=doc_demande.STATUT_DOC_ID WHERE doc_demande.DEMANDE_ID=".$DEMANDE_ID);
      //print_r($ACTIONS);
      //print_r($ETAPE_ACTUEL);
      //exit();
      if ($ACTIONS['ACTION']==1) //TEST POUR ALLER A L'ETAPE SUIVANTE
       {
      $ETAPE_SUIVANT=$ETAPE_ACTUEL['NUM']+1;
      $STATUT=$this->Model->getRequeteOne("SELECT config_doc_profil_etape.STATUT_DOC_ID,config_doc_profil_etape.NUM FROM config_doc_profil_etape  WHERE config_doc_profil_etape.NUM=".$ETAPE_SUIVANT);
       $STATUT_DOC_ID=$STATUT['STATUT_DOC_ID'];
       $STATUT_DOC_ID_HISTO=$ETAPE_ACTUEL['STATUT_DOC_ID'];
       }
      if ($ACTIONS['ACTION']==2) //TEST POUR ALLER A L'ETAPE PRECEDENTE
       {
      $ETAPE_SUIVANT=$ETAPE_ACTUEL['NUM']-1;
      $STATUT=$this->Model->getRequeteOne("SELECT config_doc_profil_etape.STATUT_DOC_ID,config_doc_profil_etape.NUM FROM config_doc_profil_etape  WHERE config_doc_profil_etape.NUM=".$ETAPE_SUIVANT);
       $STATUT_DOC_ID=$STATUT['STATUT_DOC_ID'];
       $STATUT_DOC_ID_HISTO=$ETAPE_ACTUEL['STATUT_DOC_ID'];
       }
       if($ACTIONS['ACTION_DOC_ID']==0) { //TEST POUR RESTER AU MEME ETAPE//REAFFECTATION
       $STATUT_DOC_ID=$ETAPE_ACTUEL['STATUT_DOC_ID'];
       $STATUT_DOC_ID_HISTO=$ETAPE_ACTUEL['STATUT_DOC_ID'];
       }

      $USER_ID=$this->session->userdata('SOCAR_ID_USER');

    if(!empty($DEMANDE_ID))
    {

        $data=array('DEMANDE_ID'=>$DEMANDE_ID,'USER_ID'=>$USER_ID,'COMMENTAIRE'=>$COMMENTAIRE,'DATE_TRAITEMENT'=>date('Y-m-d H:i'),'STATUT_DOC_ID'=>$STATUT_DOC_ID_HISTO,'ACTION_DOC_ID'=>$ACTION_DOC_ID);

        $DEMANDE_HISTO_ID=$this->Model->insert_last_id('doc_demande_historique',$data);


        if (!empty($ID_NIVEAUX_ORGANIGRAMME) && !empty($STATUT_DOC_ID_AFFECTATION_FORCEE)) 
        {

        $statut=$this->Model->update('doc_demande',array('DEMANDE_ID'=>$DEMANDE_ID),array('STATUT_DOC_ID'=>$STATUT_DOC_ID_AFFECTATION_FORCEE,'USER_ID'=>$USER_ID,'PROFIL_ID_DESIGNE'=>$PROFIL_ID_DESIGNE,'DATE_DEMANDE'=>date('Y-m-d H:i')));
        
        }

        else
        {
         $statut=$this->Model->update('doc_demande',array('DEMANDE_ID'=>$DEMANDE_ID),array('STATUT_DOC_ID'=>$STATUT_DOC_ID,'USER_ID'=>$USER_ID,'PROFIL_ID_DESIGNE'=>$PROFIL_ID_DESIGNE,'DATE_DEMANDE'=>date('Y-m-d H:i'))); 
        }
        
        
        //TEST SI LE CART D'UPLOAD N'EST PAS VIDE

      if ($this->cart->contents()) 
      {
        foreach ($this->cart->contents() as $items)

        {
          $array = array(
            'FICHIER_ID'=>$items['fichier'],
            'FILE_PATH'=>$items['FILE_NAME'],
            'DEMANDE_ID'=>$DEMANDE_ID,
            'STATUT_DOC_ID'=>$STATUT_DOC_ID,
            'USER_ID'=>$USER_ID,
            'DEMANDE_HISTO_ID'=>$DEMANDE_HISTO_ID
                      );
          $this->Model->delete('doc_demande_historique_fichier',array('FICHIER_ID'=>$items['fichier'],'DEMANDE_ID'=>$DEMANDE_ID));
          $FILESINSERT = $this->Model->insert_last_id('doc_demande_historique_fichier',$array);

          $CODE=$this->Model->getRequeteOne('SELECT CODE FROM `doc_type_etape` WHERE  `FICHIER_ID`='.$items['fichier']);
          if($CODE['CODE']=='ADE')
          {
          $this->Model->update('doc_demande',array('DEMANDE_ID'=>$DEMANDE_ID),array('PATH_DOC'=>$items['FILE_NAME']));
          }

        }
      }
      $this->cart->destroy();
      echo json_encode($statut);
      
    }
  }



}