<?php
////Alexis 
class Dashboard_Conge extends CI_Controller
{
  
function index(){

 $DATE1=$this->input->post('DATE1');
 $DATE2=$this->input->post('DATE2');
 $STATUS_CONGE=$this->input->post('STATUS_CONGE');

 if (empty($DATE1) && empty($DATE2))
 {
 $data['date_rapport']=" Rapport  du ".strftime('%d-%m-%Y',strtotime(date('Y-m-d')));
 $cond = '';
 }
 if (!empty($DATE1))
 {
  $data['date_rapport']=' Rapport du  '.strftime("%d-%m-%Y",strtotime($DATE1));
  $cond = ' AND ("'.$DATE1.'%" BETWEEN conge_demande_conge.DATE_DEBUT_CONGE_ACCORDE AND conge_demande_conge.DATE_FIN_CONGE_ACCORDE)';
 }


if (!empty($STATUS_CONGE))
{
$data['date_rapport']=' Rapport  du '.strftime("%d-%m-%Y",strtotime(date('Y-m-d')));
$cond = ' AND conge_demande_conge.STATUS_CONGE='.$STATUS_CONGE;
}

            $categories = '[';
            $data_direction = "[";
            $tot_rec_direct = 0;
            $data_service = "[";
            $tot_rec_servic = 0;
            $data_depart = "[";
            $tot_rec_depart = 0;

            /*EST_GENERE*/

            $categorie = $this->Model->getRequete('SELECT * FROM `config_type_conge` WHERE `STATUS`=1');

            foreach ($categorie as $key)
               {

                 $categories .= '"'.$key['NOM_CONGE'].'",';


                 //NIVEAU SERVICE
                  $getcongeparservice=$this->Model->getRequeteOne("SELECT COUNT(`ID_DEMANDE_CONGE`) Nbr FROM `conge_demande_conge` JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE=conge_demande_conge.STATUS_CONGE JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=conge_status_conge.ID_NIVEAUX WHERE organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=1 AND conge_demande_conge.ID_TYPE_CONGE=".$key['ID_TYPE_CONGE']." ".$cond."");
                  $nbrcongservic = !empty($getcongeparservice['Nbr']) ? $getcongeparservice['Nbr'] : 0 ;
                  $data_service .= "{y: ".$nbrcongservic.",key: ".$key['ID_TYPE_CONGE'].", color: 'green'},";
                  $tot_rec_servic += $nbrcongservic;


                  //NIVEAU DEPARTEMENT
                  $getcongepardepart=$this->Model->getRequeteOne("SELECT COUNT(`ID_DEMANDE_CONGE`) Nbr FROM `conge_demande_conge` JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE=conge_demande_conge.STATUS_CONGE JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=conge_status_conge.ID_NIVEAUX WHERE organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=2 AND conge_demande_conge.ID_TYPE_CONGE=".$key['ID_TYPE_CONGE']." ".$cond."");

                  $nbrcongedepart = !empty($getcongepardepart['Nbr']) ? $getcongepardepart['Nbr'] : 0 ;
                  $data_depart .= "{y: ".$nbrcongedepart.",key: ".$key['ID_TYPE_CONGE'].", color: 'orange'},";
                  $tot_rec_depart += $nbrcongedepart;


                  //NIVEAU DIRECTION
                  $getcongedirection=$this->Model->getRequeteOne("SELECT COUNT(`ID_DEMANDE_CONGE`) Nbr FROM `conge_demande_conge` JOIN conge_status_conge ON conge_status_conge.STATUS_CONGE=conge_demande_conge.STATUS_CONGE JOIN organigramme_niveau ON organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=conge_status_conge.ID_NIVEAUX WHERE organigramme_niveau.ID_NIVEAUX_ORGANIGRAMME=3 AND conge_demande_conge.ID_TYPE_CONGE=".$key['ID_TYPE_CONGE']." ".$cond."");

                  $nbrcongedirect = !empty($getcongedirection['Nbr']) ? $getcongedirection['Nbr'] : 0 ;
                  $data_direction .= "{y: ".$nbrcongedirect.",key: ".$key['ID_TYPE_CONGE'].", color: 'blue'},";
                  $tot_rec_direct += $nbrcongedirect;


            }


         /////////////  DIRECTION //////

  $totcat=0;
  $nbcatego="";
  $directionlist=$this->Model->getRequete('SELECT * FROM `organigramme_direction` WHERE `STATUS`=1');
  foreach ($directionlist as $direct )
   {
    $categoconge='';
    $tot_S=0;
    $donnees_catego='';
    foreach ($categorie as $categ ) {

      $categoconge.="'".$categ['NOM_CONGE']."',";
      $donne=$this->Model->getRequeteOne('SELECT COUNT(`ID_DEMANDE_CONGE`) AS nbre_fonct FROM `conge_demande_conge` 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 WHERE 1 AND `ID_TYPE_CONGE`='.$categ['ID_TYPE_CONGE'] .' AND conge_demande_conge.ID_DIRECTION='.$direct['ID_DIRECTION'] .' '.$cond.'');

      if (empty($donne['nbre_fonct']))
       {

        $donne['nbre_fonct']=0;
      }
      $tot_S+=$donne['nbre_fonct'];

      $donnees_catego.="{y:".$donne['nbre_fonct'].",key:".$categ['ID_TYPE_CONGE'].",key1:".$direct['ID_DIRECTION']."},";
      }
       // print_r($donne_sex);
    $nbr_categories="{
      name: '".$direct['DESCRIPTION']." (".$tot_S.")',
      data: [";
      $donnees_catego=str_replace(',@', "", $donnees_catego.'@');
      $nbr_categories.=$donnees_catego."] },";
       //print_r($nbr_sex);

      $nbcatego.=$nbr_categories;
      $totcat+=$tot_S;
    }



     
   /////////////  DEPARTEMENT //////

  $TOTALDEPART=0;
  $nbr_departem="";
  $departlist=$this->Model->getRequete('SELECT * FROM `organigramme_service` WHERE `STATUS`=1');
  foreach ($departlist as $depart )
   {
    $congedepartement='';
    $totaldep=0;
    $donnes_depart='';
    foreach ($categorie as $categ ) {

      $congedepartement.="'".$categ['NOM_CONGE']."',";
      $donne=$this->Model->getRequeteOne('SELECT COUNT(`ID_DEMANDE_CONGE`) AS nbre_fonct FROM `conge_demande_conge` 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 WHERE 1 AND `ID_TYPE_CONGE`='.$categ['ID_TYPE_CONGE'] .' AND conge_demande_conge.ID_SERVICE='.$depart['ID_SERVICE'] .' '.$cond.'');

      if (empty($donne['nbre_fonct']))
       {

        $donne['nbre_fonct']=0;
      }
      $totaldep+=$donne['nbre_fonct'];

      $donnes_depart.="{y:".$donne['nbre_fonct'].",key:".$categ['ID_TYPE_CONGE'].",key1:".$depart['ID_SERVICE']."},";
      }
       // print_r($donne_sex);
      $nbr_departements="{
      name: '".$depart['DESCRIPTION']." (".$totaldep.")',
      data: [";
      $donnes_depart=str_replace(',@', "", $donnes_depart.'@');
      $nbr_departements.=$donnes_depart."] },";
      $nbr_departem.=$nbr_departements;
      $TOTALDEPART+=$totaldep;
    }





/////////////  SERVICE //////

  $totalserv=0;
  $nbr_service="";
  $listeservice=$this->Model->getRequete('SELECT * FROM `organigramme_section` WHERE `STATUS`=1');
  foreach ($listeservice as $servic )
   {
    $conge_service='';
    $totalservices=0;
    $donnees_service='';
    foreach ($categorie as $categ ) {

      $conge_service.="'".$categ['NOM_CONGE']."',";
      $donne=$this->Model->getRequeteOne('SELECT COUNT(`ID_DEMANDE_CONGE`) AS nbre_fonct FROM `conge_demande_conge` 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 WHERE 1 AND `ID_TYPE_CONGE`='.$categ['ID_TYPE_CONGE'] .' AND conge_demande_conge.ID_SECTION='.$servic['ID_SECTION'] .' '.$cond.'');

      if (empty($donne['nbre_fonct']))
       {

        $donne['nbre_fonct']=0;
      }
      $totalservices+=$donne['nbre_fonct'];

      $donnees_service.="{y:".$donne['nbre_fonct'].",key:".$categ['ID_TYPE_CONGE'].",key1:".$servic['ID_SECTION']."},";
      }
       // print_r($donne_sex);
      $nbr_services="{
      name: '".$servic['DESCRIPTION']." (".$totalservices.")',
      data: [";
      $donnees_service=str_replace(',@', "", $donnees_service.'@');
      $nbr_services.=$donnees_service."] },";
      $nbr_service.=$nbr_services;
      $totalserv+=$totalservices;
    }


        //SERVICE
    $titleservice='Nombre de congé par service';
    $nbr_services=str_replace(",@", "", $nbr_services.'@');
    $conge_service=str_replace(",@", "", $conge_service.'@');

    ### envoi des donnees dans la vue
    $data['nbr_service']=$nbr_service;
    $data['titleservice']=$titleservice;
    $data['conge_service']=$conge_service;
    $data['totalserv']=$totalserv;



    //DEPARTEMENT
    $titledepart='Nombre de congé par département';
    $nbr_departements=str_replace(",@", "", $nbr_departements.'@');
    $congedepartement=str_replace(",@", "", $congedepartement.'@');

    ### envoi des donnees dans la vue
    $data['nbr_departem']=$nbr_departem;
    $data['titledepart']=$titledepart;
    $data['congedepartement']=$congedepartement;
    $data['TOTALDEPART']=$TOTALDEPART;


   //DIRECTION
    $title='Nombre de congé par direction';
    $nbr_categories=str_replace(",@", "", $nbr_categories.'@');
    $categoconge=str_replace(",@", "", $categoconge.'@');

    ### envoi des donnees dans la vue
    $data['nbr_categories']=$nbcatego;
    $data['title']=$title;
    $data['categoconge']=$categoconge;
    $data['totcat']=$totcat;

    //RAPPORT PAR NIVEAU (1ER RAPPORT)
    $data['categories'] = $categories."]"; 
    $data['data_direction'] = $data_direction."]"; 
    $data['data_service'] = $data_service."]"; 
          
    $data['tot_rec_direct'] = $tot_rec_direct; 
    $data['tot_rec_servic'] = $tot_rec_servic; 
    $data['data_depart'] = $data_depart."]"; 
    $data['tot_rec_depart'] = $tot_rec_depart; 

    $data['title'] = "Rapport des congés par niveau ";
    $data['typeconge'] = $this->Model->getRequete('SELECT * FROM `conge_status_conge` WHERE 1');
    $data['STATUS_CONGE'] = -10;
    $this->load->view('Dashboard_Conge_View',$data);
}



}
?>
