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

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

 if (empty($DATE1) && empty($DATE2))
 {
 $data['titre_rapport']=" Rapport  du ".strftime('%d-%m-%Y',strtotime(date('Y-m-d')));
 $cond = '';
 }
 if (!empty($DATE1))
 {
  $data['titre_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($DATE1) && !empty($DATE2))
 {
 $data['titre_rapport']=' Rapport du  '.strftime("%d-%m-%Y",strtotime($DATE1)). ' au '.strftime("%d-%m-%Y",strtotime($DATE2));
 $cond = '';
 }


 // Rapport 1

            $categories1 = '[';
            $data_nombrevide1 = "[";
            $data_nombrevide2 = "[";
            $data_nombrevide3 = "[";
            $data_nombrevide4 = "[";

            $requet1 = $this->Model->getRequete('SELECT DATE_ENTREE_STOCK, syst_taille_bouteille.DESCRIPTION, SUM(QUANTITE) AS TOTAL_QUANTITE FROM stock_entre_stock JOIN syst_taille_bouteille ON syst_taille_bouteille.ID_TAILLE_BOUTEILLE = stock_entre_stock.ID_TAILLE_BOUTEILLE GROUP BY DATE_ENTREE_STOCK, syst_taille_bouteille.DESCRIPTION');

            foreach ($requet1 as $key)
               {

                $categories1 .= '"'.$key['DATE_ENTREE_STOCK'].'",';
                

                  $nombrevide1=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_entre_stock` WHERE DATE_ENTREE_STOCK LIKE '".$key['DATE_ENTREE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 1 AND ID_TYPE_BOUTEILLE = 1 ");
                  if ($nombrevide1['NUMB'] == NULL) {
                    $data_nombrevide1 .= '0,';
                  }
                  else{
                    $data_nombrevide1 .= $nombrevide1['NUMB'].',';
                  }
                  

                  $nombrevide2=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_entre_stock` WHERE DATE_ENTREE_STOCK LIKE '".$key['DATE_ENTREE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 2 AND ID_TYPE_BOUTEILLE = 1 ");
                  
                  if ($nombrevide2['NUMB'] == NULL) {
                    $data_nombrevide2 .= '0,';
                  }
                  else{
                    $data_nombrevide2 .= $nombrevide2['NUMB'].',';
                  }

                  $nombrevide3=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_entre_stock` WHERE DATE_ENTREE_STOCK LIKE '".$key['DATE_ENTREE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 3 AND ID_TYPE_BOUTEILLE = 1 ");
                  
                  if ($nombrevide3['NUMB'] == NULL) {
                    $data_nombrevide3 .= '0,';
                  }
                  else{
                    $data_nombrevide3 .= $nombrevide3['NUMB'].',';
                  }

                  $nombrevide4=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_entre_stock` WHERE DATE_ENTREE_STOCK LIKE '".$key['DATE_ENTREE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 4 AND ID_TYPE_BOUTEILLE = 1 ");
                  
                  if ($nombrevide4['NUMB'] == NULL) {
                    $data_nombrevide4 .= '0,';
                  }
                  else{
                    $data_nombrevide4 .= $nombrevide4['NUMB'].',';
                  }

            }

            $data['categories1'] = $categories1."]"; 
            $data['data_nombrevide1'] = $data_nombrevide1."]"; 
            $data['data_nombrevide2'] = $data_nombrevide2."]"; 
            $data['data_nombrevide3'] = $data_nombrevide3."]"; 
            $data['data_nombrevide4'] = $data_nombrevide4."]"; 




 // Rapport 2

 $categories2 = '[';
 $data_nombreplien1 = "[";
 $data_nombreplien2 = "[";

 $requet2 = $this->Model->getRequete('SELECT DATE_SORTIE_STOCK, syst_taille_bouteille.DESCRIPTION, SUM(QUANTITE) AS TOTAL_QUANTITE FROM stock_sortie_stock JOIN syst_taille_bouteille ON syst_taille_bouteille.ID_TAILLE_BOUTEILLE = stock_sortie_stock.ID_TAILLE_BOUTEILLE GROUP BY DATE_SORTIE_STOCK, syst_taille_bouteille.DESCRIPTION');

 foreach ($requet2 as $key)
    {

     $categories2 .= '"'.$key['DATE_SORTIE_STOCK'].'",';
     

       $nombreplien1=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_sortie_stock` WHERE DATE_SORTIE_STOCK LIKE '".$key['DATE_SORTIE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 1 AND ID_TYPE_BOUTEILLE = 2 ");
       if ($nombreplien1['NUMB'] == NULL) {
         $data_nombreplien1 .= '0,';
       }
       else{
         $data_nombreplien1 .= $nombreplien1['NUMB'].',';
       }
       

       $nombreplien2=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS NUMB FROM `stock_sortie_stock` WHERE DATE_SORTIE_STOCK LIKE '".$key['DATE_SORTIE_STOCK']."' AND ID_TAILLE_BOUTEILLE = 2 AND ID_TYPE_BOUTEILLE = 2 ");
       
       if ($nombreplien2['NUMB'] == NULL) {
         $data_nombreplien2 .= '0,';
       }
       else{
         $data_nombreplien2 .= $nombreplien2['NUMB'].',';
       }

       

 }

 $data['categories2'] = $categories2."]"; 
 $data['data_nombreplien1'] = $data_nombreplien1."]"; 
 $data['data_nombreplien2'] = $data_nombreplien2."]"; 
//  $data['data_nombrevide3'] = $data_nombrevide3."]"; 
//  $data['data_nombrevide4'] = $data_nombrevide4."]"; 



            ///// rapport 3

            
            $requet3 = $this->Model->getRequete('SELECT syst_taille_bouteille.DESCRIPTION, stock_stock.QUANTITE FROM `syst_taille_bouteille` JOIN stock_stock ON stock_stock.ID_TAILLE_BOUTEILLE = syst_taille_bouteille.ID_TAILLE_BOUTEILLE WHERE stock_stock.ID_TYPE_BOUTEILLE = 1');
            $datar3 = '[';
            foreach ($requet3 as $key) {
              
                $datar3 .= "{ name: '".$key['DESCRIPTION']."',
                y: ".$key['QUANTITE'].",
            },";
            }

            $data['datar3'] = $datar3.']';


            ///// rapport 4

            
            $requet4 = $this->Model->getRequete('SELECT syst_taille_bouteille.DESCRIPTION, stock_stock.QUANTITE FROM `syst_taille_bouteille` JOIN stock_stock ON stock_stock.ID_TAILLE_BOUTEILLE = syst_taille_bouteille.ID_TAILLE_BOUTEILLE WHERE stock_stock.ID_TYPE_BOUTEILLE = 2');
            $datar4 = '[';
            foreach ($requet4 as $key) {
              
                $datar4 .= "{ name: '".$key['DESCRIPTION']."',
                y: ".$key['QUANTITE'].",
            },";
            }
            
            $data['datar4'] = $datar4.']';

         /////////////  Rapport 5 //////

         
         $requet5 = $this->Model->getRequete('SELECT * FROM `stock_motif_retour`');

         $datar5 = '[';
         $data_vide1 = "[";
         $data_plein1 = "[";
            foreach ($requet5 as $key) {
              
                $datar5 .= "'".$key['DESCRIPTION']."',";
                
                $nombren1=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS QUANTITES FROM `stock_retour_stock` WHERE ID_MOTIF_RETOUR = ".$key['ID_MOTIF_RETOUR']." AND ID_TYPE_BOUTEILLE = 1");
                  if ($nombren1['QUANTITES'] == NULL) {
                    $data_vide1 .= '0,';
                  }
                  else{
                    $data_vide1 .= $nombren1['QUANTITES'].',';
                  }

                  $nombren2=$this->Model->getRequeteOne("SELECT SUM(QUANTITE) AS QUANTITES FROM `stock_retour_stock` WHERE ID_MOTIF_RETOUR = ".$key['ID_MOTIF_RETOUR']." AND ID_TYPE_BOUTEILLE = 2");
                  if ($nombren2['QUANTITES'] == NULL) {
                    $data_plein1 .= '0,';
                  }
                  else{
                    $data_plein1 .= $nombren2['QUANTITES'].',';
                  }


            }
            
            $data['datar5'] = $datar5.']';
            $data['data_vide1'] = $data_vide1."]"; 
            $data['data_plein1'] = $data_plein1."]"; 


            // requette 6
            $categories6 = '[';
            $data_prod1 = "[";
            $data_prod2 = "[";
            $data_prod3 = "[";
            $data_prod4 = "[";
            $requet6 = $this->Model->getRequete('SELECT DISTINCT(DATE_PRODUCTION) AS DATEPROD FROM `prod_production` WHERE 1');

            foreach ($requet6 as $key) {
              $categories6 .= '"'.$key['DATEPROD'].'",';

              $prod1=$this->Model->getRequeteOne("SELECT SUM(QUANTITE_PRODUIT) AS QPROD FROM `prod_production` WHERE ID_TAILLE_BOUTEILLE = 1 AND DATE_PRODUCTION LIKE '".$key['DATEPROD']."'");
                  if ($prod1['QPROD'] == NULL) {
                    $data_prod1 .= '0,';
                  }
                  else{
                    $data_prod1 .= $prod1['QPROD'].',';
                  }


              $prod2=$this->Model->getRequeteOne("SELECT SUM(QUANTITE_PRODUIT) AS QPROD FROM `prod_production` WHERE ID_TAILLE_BOUTEILLE = 2 AND DATE_PRODUCTION LIKE '".$key['DATEPROD']."'");
                  if ($prod2['QPROD'] == NULL) {
                    $data_prod2 .= '0,';
                  }
                  else{
                    $data_prod2 .= $prod2['QPROD'].',';
                  }

                  $prod3=$this->Model->getRequeteOne("SELECT SUM(QUANTITE_PRODUIT) AS QPROD FROM `prod_production` WHERE ID_TAILLE_BOUTEILLE = 3 AND DATE_PRODUCTION LIKE '".$key['DATEPROD']."'");
                  if ($prod3['QPROD'] == NULL) {
                    $data_prod3 .= '0,';
                  }
                  else{
                    $data_prod3 .= $prod3['QPROD'].',';
                  }


                  $prod4=$this->Model->getRequeteOne("SELECT SUM(QUANTITE_PRODUIT) AS QPROD FROM `prod_production` WHERE ID_TAILLE_BOUTEILLE = 4 AND DATE_PRODUCTION LIKE '".$key['DATEPROD']."'");
                  if ($prod4['QPROD'] == NULL) {
                    $data_prod4 .= '0,';
                  }
                  else{
                    $data_prod4 .= $prod4['QPROD'].',';
                  }

            }

            $data['categories6'] = $categories6."]"; 
            $data['data_prod1'] = $data_prod1."]"; 
            $data['data_prod2'] = $data_prod2."]"; 
            $data['data_prod3'] = $data_prod3."]"; 
            $data['data_prod4'] = $data_prod4."]"; 

  // $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['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_Stock_View',$data);
}



}
?>
