<?php

 
class Exports extends CI_Controller {

    public function __construct() {
        parent::__construct();
        // $this->make_bread->add('Mode de paiement', "assurance/Mode_paiement", 0);
        // $this->breadcrumb = $this->make_bread->output();
            // $this->is_Oauth();
  
    }



    


    public function entre_details() {
       
      $PERIODE = $this->input->post('PERIODE');

      if ($PERIODE != null) {
      $str = $PERIODE;
      $dat=explode(" - ",$str);
     $date1 = DateTime::createFromFormat('d/m/Y', $dat[0]);
     $debut = $date1->format('Y-m-d');
     $date2 = DateTime::createFromFormat('d/m/Y', $dat[1]);
     $fin = $date2->format('Y-m-d');
     $conditions = ' AND DATE_BON_COMMANDE BETWEEN "'.$debut.'" AND "'.$fin.'"';
     $selectdates = 'Entre '.$debut.' et '.$fin;
     $data['selectdate'] = $PERIODE;
      }
      else{
        $thismonth = date("Y-m");
        $selectdates = date("Y-m");
        $conditions = ' AND DATE_BON_COMMANDE LIKE "'.$thismonth.'%"';
        $data['selectdate'] = '';
      }
      // echo $conditions;
      // exit();

    $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'),
    );

    $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,
        ),
    );

    
      // if ($TYPEASS == NULL) {
      //    $SQLTYPEASS = ' ';

      //   $resultat=$this->Model->getList('asurance_type',array());
      // }
      // else{
      //   $SQLTYPEASS = ' AND TYPE_ASSURANCE_ID = "'.$TYPEASS.'"';
        $resultat=$this->Model->getRequete('SELECT DISTINCT YEAR(stock_approvisionnement.DATE_BON_COMMANDE) AS "Year", MONTH(stock_approvisionnement.DATE_BON_COMMANDE) AS "Month" FROM stock_approvisionnement WHERE 1 '.$conditions.' ORDER by Year ASC, Month ASC');
      // }
      // echo $SQLTYPEASS;
    $i = 0;
     foreach ($resultat as $key) {
    
    $objPHPExcel->setActiveSheetIndex($i)->mergeCells('A1:G1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $key['Year'].' - '.$key['Month']);

    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Designation');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Quantite');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Prix Unitaire');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Prix Total');
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Fournisseur');
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Observation');
  
    $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()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
  

    $row = 3;

    $nbchar =  strlen($key['Month']);
    if ($nbchar == 1) {
      $newmonth = '0'.$key['Month'];
    }
    else{
      $newmonth = $key['Month'];
    }
      $custDtaData  = $this->Model->getRequete('SELECT stock_approvisionnement.DATE_BON_COMMANDE, masque_piece.PIECE_NOM, stock_approvisionnement_detail.QUANTITE, stock_approvisionnement_detail.PRIX_UNITAIRE, stock_fournisseurs.FOURNISSEUR_NOM, stock_approvisionnement_detail.OBSERVATION FROM stock_approvisionnement JOIN stock_approvisionnement_detail on stock_approvisionnement.APPROVISIONNEMENT_ID = stock_approvisionnement_detail.APPROVISIONNEMENT_ID JOIN stock_fournisseurs on stock_fournisseurs.FOURNISSEUR_ID = stock_approvisionnement.FOURNISSEUR_ID JOIN masque_piece on masque_piece.PIECE_ID = stock_approvisionnement_detail.PIECE_ID WHERE stock_approvisionnement.DATE_BON_COMMANDE like "%'.$key['Year'].'-'.$newmonth.'%" '.$conditions.' ');
    $tot= 0;
    foreach ($custDtaData as $value)
    {
        $PRIX_TOTAL = $value['QUANTITE'] * $value['PRIX_UNITAIRE'];    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $value['DATE_BON_COMMANDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value['PIECE_NOM']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $value['QUANTITE']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $value['PRIX_UNITAIRE']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $PRIX_TOTAL);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $value['FOURNISSEUR_NOM']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $value['OBSERVATION']);
        $row++;
        $tot+= $PRIX_TOTAL;
    }

    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $tot);
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '');
  
    // $objPHPExcel->getActiveSheet()->getStyle('A'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('C'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('G'.$row)->applyFromArray($style_header);
  
    $objPHPExcel->getActiveSheet()->setTitle($key['Year'].' - '.$key['Month']);

    $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');


    }
 

 public function sortie_stock() {

    $PERIODE = $this->input->post('PERIODE');
    if ($PERIODE != null) {
      $str = $PERIODE;
      $dat=explode(" - ",$str);
     $date1 = DateTime::createFromFormat('d/m/Y', $dat[0]);
     $debut = $date1->format('Y-m-d');
     $date2 = DateTime::createFromFormat('d/m/Y', $dat[1]);
     $fin = $date2->format('Y-m-d');
     $data['selectdates'] = 'Entre '.$debut.' et '.$fin;
     $data['selectdate'] = $PERIODE;
     if ($debut != $fin) {
       $thismonth = date("Y-m");
        $conditions = ' AND stock_consommation.DATE_MONTAGE BETWEEN "'.$debut.'" AND "'.$fin.'"';
        $selectdates = 'Entre '.$debut.' et '.$fin;
     }
     else{
      $thismonth = date("Y-m");
        $selectdates = date("Y-m");
        $conditions = ' AND stock_consommation.DATE_MONTAGE LIKE "'.$debut.'"';
     }
      }
      else{
        $thismonth = date("Y-m");
        $data['selectdates'] = 'mois de '.$thismonth;
        $conditions = ' AND stock_consommation.DATE_MONTAGE LIKE "'.$thismonth.'%"';
        $data['selectdate'] = '';
      }





      $PIECE_CATEGORIE_ID = $this->input->post('PIECE_CATEGORIE_ID');
    if ($PIECE_CATEGORIE_ID == null) {
      $condcat = '';
      // $data['tcondit']='';
      // $data['selectcat']='';
    }
    else{
      // $catss= $this->Model->getRequeteOne('select * from masque_piece_categorie WHERE PIECE_CATEGORIE_ID = '.$PIECE_CATEGORIE_ID.' ');
      $condcat = ' AND masque_piece.PIECE_CATEGORIE_ID = '.$PIECE_CATEGORIE_ID.' ';
      // $data['tcondit']=' - Categorie:'.$catss['DESCRIPTION'].' ';
      // $data['selectcat']=$PIECE_CATEGORIE_ID;
    }

    $VEHICULE_ID = $this->input->post('VEHICULE_ID');
    if ($VEHICULE_ID == null) {
      $condveh = '';
      // $data['vcondit']='';
      // $data['selectveh']='';
    }
    else{
      // $vehsel= $this->Model->getRequeteOne('select * from masque_vehicules WHERE VEHICULE_ID = '.$VEHICULE_ID.' ');
      $condveh = ' AND voiture_vehicules.VEHICULE_ID = '.$VEHICULE_ID.' ';
      // $data['vcondit']=' - Plaque:'.$vehsel['PLAQUE_VEHICULE'].' ';
      // $data['selectveh']=$VEHICULE_ID;
    }


    $COLLABORATEUR_ID = $this->input->post('COLLABORATEUR_ID');
    if ($COLLABORATEUR_ID == null) {
      $condcolab = '';
      // $data['ccondit']='';
      // $data['selectcola']='';
    }
    else{
      // $colabo= $this->Model->getRequeteOne('select * from admin_collaborateurs WHERE COLLABORATEUR_ID = '.$COLLABORATEUR_ID.' ');
      $condcolab = ' AND stock_consommation.COLLABORATEUR_ID = '.$COLLABORATEUR_ID.' ';
      // $data['ccondit']=' - Collaborateur:'.$colabo['COLLABORATEUR_NOM'].' '.$colabo['COLLABORATEUR_PRENOM'].' ';
      // $data['selectcola']=$COLLABORATEUR_ID;
    }



    $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'),
    );

    $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,
        ),
    );
$new_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_RIGHT,
        ),
    );
    
      // if ($TYPEASS == NULL) {
      //    $SQLTYPEASS = ' ';

      //   $resultat=$this->Model->getList('asurance_type',array());
      // }
      // else{
      //   $SQLTYPEASS = ' AND TYPE_ASSURANCE_ID = "'.$TYPEASS.'"';
        $resultat=$this->Model->getRequete('SELECT DISTINCT YEAR(stock_consommation.DATE_MONTAGE) AS "Year", MONTH(stock_consommation.DATE_MONTAGE) AS "Month" FROM stock_consommation WHERE 1 '.$conditions.' ORDER by Year ASC, Month ASC');
        // echo'SELECT DISTINCT YEAR(stock_consommation.DATE_MONTAGE) AS "Year", MONTH(stock_consommation.DATE_MONTAGE) AS "Month" FROM stock_consommation WHERE 1 '.$conditions.' ORDER by Year ASC, Month ASC';
        // exit();
      // }
      // echo $SQLTYPEASS;
    $i = 0;
     foreach ($resultat as $key) {
    
    $objPHPExcel->setActiveSheetIndex($i)->mergeCells('A1:G1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $key['Year'].' - '.$key['Month']);

    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Vehicule');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Piece');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Nb Piece');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'PU');
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'PT');
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'KM');
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Chauffeur');
  
    $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()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
  

    $row = 3;

    $nbchar =  strlen($key['Month']);
    if ($nbchar == 1) {
      $newmonth = '0'.$key['Month'];
    }
    else{
      $newmonth = $key['Month'];
    }
      // $custDtaData  = $this->Model->getRequete('SELECT masque_vehicules.PLAQUE_VEHICULE, admin_collaborateurs.COLLABORATEUR_NOM, admin_collaborateurs.COLLABORATEUR_PRENOM, masque_piece.PIECE_NOM, stock_consommation.NOMBRE_PIECE, masque_piece_position.POSITION_DESCR, stock_consommation.DATE_MONTAGE, stock_consommation.KM_MONTAGE FROM `stock_consommation` LEFT JOIN admin_collaborateurs ON admin_collaborateurs.COLLABORATEUR_ID = stock_consommation.COLLABORATEUR_ID LEFT JOIN masque_vehicules ON masque_vehicules.VEHICULE_ID = stock_consommation.VEHICULE_ID LEFT JOIN masque_piece ON masque_piece.PIECE_ID = stock_consommation.PIECE_ID LEFT JOIN masque_piece_position ON masque_piece_position.POSITION_ID = stock_consommation.POSITION_ID WHERE stock_consommation.DATE_MONTAGE like "%'.$key['Year'].'-'.$newmonth.'%"  '.$conditions.' ');
    // $custDtaData = $this->Model->getRequete('SELECT stock_consommation.CONSOMMATION_ID, masque_vehicules.PLAQUE_VEHICULE, masque_piece.UNITE_ID, masque_piece.NOMBRE_PAR_UNITE, admin_collaborateurs.COLLABORATEUR_NOM, admin_collaborateurs.COLLABORATEUR_PRENOM, admin_profils.PROFIL_DESCR, masque_piece.PIECE_NOM, stock_consommation.NOMBRE_PIECE, masque_piece_position.POSITION_DESCR, stock_consommation.DATE_MONTAGE, stock_consommation.KM_MONTAGE, stock_approvisionnement.APPROVISIONNEMENT_ID, stock_approvisionnement_detail.PRIX_UNITAIRE FROM `stock_consommation` LEFT JOIN admin_collaborateurs ON admin_collaborateurs.COLLABORATEUR_ID = stock_consommation.COLLABORATEUR_ID LEFT JOIN masque_vehicules ON masque_vehicules.VEHICULE_ID = stock_consommation.VEHICULE_ID LEFT JOIN masque_piece ON masque_piece.PIECE_ID = stock_consommation.PIECE_ID LEFT JOIN masque_piece_position ON masque_piece_position.POSITION_ID = stock_consommation.POSITION_ID LEFT JOIN stock_consommation_detail on stock_consommation_detail.CONSOMMATION_ID = stock_consommation.CONSOMMATION_ID LEFT JOIN admin_profils ON admin_profils.PROFIL_ID = admin_collaborateurs.HIERARCHIE_ID LEFT JOIN stock_approvisionnement on stock_approvisionnement.APPROVISIONNEMENT_ID = stock_consommation_detail.APPROVISIONNEMENT_ID LEFT JOIN stock_approvisionnement_detail on stock_approvisionnement_detail.APPROVISIONNEMENT_ID = stock_approvisionnement.APPROVISIONNEMENT_ID  WHERE 1 and stock_consommation.DATE_MONTAGE like "%'.$key['Year'].'-'.$newmonth.'%"  '.$conditions.' '.$condcat.' '.$condveh.' '.$condcolab.' ');

    $custDtaData = $this->Model->getRequete('SELECT stock_consommation.CONSOMMATION_ID, voiture_vehicules.PLAQUE_VEHICULE, masque_piece.PIECE_NOM, masque_piece.UNITE_ID, masque_piece.NOMBRE_PAR_UNITE, stock_consommation.KM_MONTAGE, admin_collaborateurs.NOM, admin_collaborateurs.PRENOM, stock_consommation.NOMBRE_PIECE, stock_consommation_detail.CONSOM_DETAIL_ID, stock_approvisionnement.APPROVISIONNEMENT_ID, stock_consommation_detail.DETAIL_ID,  stock_approvisionnement_detail.MODE_PAIEMENT, stock_approvisionnement_detail.QUANTITE,  stock_approvisionnement_detail.QUANTITE_LIVRE, stock_approvisionnement_detail.PRIX_UNITAIRE,  stock_consommation.DATE_MONTAGE FROM `stock_consommation` LEFT JOIN masque_piece ON masque_piece.PIECE_ID = stock_consommation.PIECE_ID LEFT JOIN stock_consommation_detail ON stock_consommation_detail.CONSOMMATION_ID = stock_consommation.CONSOMMATION_ID LEFT JOIN stock_approvisionnement ON stock_approvisionnement.APPROVISIONNEMENT_ID = stock_consommation_detail.APPROVISIONNEMENT_ID LEFT JOIN stock_approvisionnement_detail ON stock_approvisionnement_detail.DETAIL_ID = stock_consommation_detail.DETAIL_ID LEFT JOIN voiture_vehicules ON voiture_vehicules.VEHICULE_ID = stock_consommation.VEHICULE_ID  LEFT JOIN admin_collaborateurs on admin_collaborateurs.COLLABORATEUR_ID = stock_consommation.COLLABORATEUR_ID WHERE 1 and stock_consommation.DATE_MONTAGE like "%'.$key['Year'].'-'.$newmonth.'%"  '.$conditions.' '.$condcat.' '.$condveh.' '.$condcolab.'');
    $tot= 0;
    foreach ($custDtaData as $value)
    {

      if ($value['UNITE_ID'] == 3) {
        $pu = $value['PRIX_UNITAIRE'] / $value['NOMBRE_PAR_UNITE'];
        $pt = $pu * $value['NOMBRE_PIECE'];
      }
      else{
        $pu = $value['PRIX_UNITAIRE'];
        $pt = $pu * $value['NOMBRE_PIECE'];
      }
      $tot += $pt;

        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $value['DATE_MONTAGE']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value['PLAQUE_VEHICULE']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $value['PIECE_NOM']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $value['NOMBRE_PIECE']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, number_format($pu,0,'',' '));
        $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->applyFromArray($new_style);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, number_format($pt,0,'',' '));
        $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($new_style);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $value['KM_MONTAGE']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $value['NOM'].' '.$value['PRENOM']);
        $row++;
        // $tot+= $PRIX_TOTAL;
    }

    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, number_format($tot,0,'',' '));
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '',' ');
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, '');
  
    $objPHPExcel->getActiveSheet()->getStyle('A'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($new_style);
    $objPHPExcel->getActiveSheet()->getStyle('G'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('H'.$row)->applyFromArray($style_header);

  
    $objPHPExcel->getActiveSheet()->setTitle($key['Year'].' - '.$key['Month']);

    $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');


    }
 


  public function export_carburant_ravi() {

      $PERIODE = $this->input->post('PERIODE');
      if ($PERIODE != null) {
      $str = $PERIODE;
      $dat=explode(" - ",$str);

     $date1 = DateTime::createFromFormat('d/m/Y', $dat[0]);
     $debut = $date1->format('Y-m-d');
     $date2 = DateTime::createFromFormat('d/m/Y', $dat[1]);
     $fin = $date2->format('Y-m-d');
     // $data['conditions'] = ' AND CONSOMATION_DATE BETWEEN "'.$debut.'" AND "'.$fin.'"';

     if ($debut == $fin) {
       $thismonth = date("Y-m");
        // $selectdates = date("Y-m");
        // $conditions = ' AND CONSOMATION_DATE LIKE "'.$thismonth.'%"';
       $selectdates = date("Y-m");
        $conditions = ' AND CONSOMATION_DATE LIKE "'.$thismonth.'%"';
        
     }
     else{
     // $conditions = ' AND CONSOMATION_DATE BETWEEN "'.$debut.'" AND "'.$fin.'"';
     // $selectdates = 'Entre '.$debut.' et '.$fin;
        $thismonth = date("Y-m");
        
        $conditions = ' AND CONSOMATION_DATE BETWEEN "'.$debut.'" AND "'.$fin.'"';
        $selectdates = 'Entre '.$debut.' et '.$fin;
     }
     
     // $data['selectdate'] = $PERIODE;
      }
      else{
        $thismonth = date("Y-m");
        $selectdates = date("Y-m");
        $conditions = ' AND CONSOMATION_DATE LIKE "'.$thismonth.'%"';
        // $data['selectdate'] = '';
      }

      // $data['options'] = $this->input->post('options');
      $options = $this->input->post('options');
      if ($options == NULL) {
          $condition='car.IS_PAYE = 0';
          $conditiond='voiture_carburant_consomation.IS_PAYE = 0';
      }
      else{
        // echo "Quelque chose";
        $data['selection'] = $options;
        // echo $options;
        // echo "<br>";
        if ($options == 0) {
          $condition=' 1';
          $conditiond=' 1';
        }
        elseif ($options == 1) {
          $condition='car.IS_PAYE = 0';  
          $conditiond=' voiture_carburant_consomation.IS_PAYE = 0';      
        }
        elseif ($options == 2) {
          $condition='car.IS_PAYE = 1';
          $conditiond='voiture_carburant_consomation.IS_PAYE = 1';
        }
        elseif ($options == 3) {
          $condition='car.ID_STATUS = 2';
          $conditiond='voiture_carburant_consomation.ID_STATUS = 2';
        }
        elseif ($options == 4) {
          $condition='car.ID_STATUS = 4 or car.ID_STATUS = 3';
          $conditiond='voiture_carburant_consomation.ID_STATUS = 4 or voiture_carburant_consomation.ID_STATUS = 3';
        }


      }

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

      if ($VEHICULE_ID !=NULL) {
        $condvehicle='AND car.VEHICULE_ID ='.$VEHICULE_ID.' ';
        $condvehicled='AND voiture_carburant_consomation.VEHICULE_ID ='.$VEHICULE_ID.' ';
      }
      else{
        $condvehicle='';
        $condvehicled='';
      }

      
      $STATION_ID = $this->input->post('STATION_ID');
      if ($STATION_ID != NULL) {
        $condstation = 'AND car.STATION_ID ='.$STATION_ID.' ';
        $condstationd = 'AND voiture_carburant_consomation.STATION_ID ='.$STATION_ID.' ';
      }
      else{
        $condstation = '';
        $condstationd = '';
      }
     
     // exit();
     

    $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'),
    );

    $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,
        ),
    );

    $mois=$this->Model->getRequete('SELECT DISTINCT YEAR(voiture_carburant_consomation.CONSOMATION_DATE) AS "Year", MONTH(voiture_carburant_consomation.CONSOMATION_DATE) AS "Month" FROM voiture_carburant_consomation WHERE '.$conditiond.' '.$conditions.' '.$condvehicled.' '.$condstationd.' ORDER by Year ASC, Month ASC');
    // echo'SELECT DISTINCT YEAR(carburant_consomation.CONSOMATION_DATE) AS "Year", MONTH(carburant_consomation.CONSOMATION_DATE) AS "Month" FROM carburant_consomation WHERE '.$condition.' '.$conditions.' '.$condvehicle.' '.$condstation.' ORDER by Year ASC, Month ASC';
    // exit();
  
    $i = 0;
     foreach ($mois as $key) {
    
    $objPHPExcel->setActiveSheetIndex($i)->mergeCells('A1:G1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $key['Year'].' - '.$key['Month']);

    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($top_header_style);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Vehicule');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Station');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Provenance');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Quantite');
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Montant');
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'KM');
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Chauffeur');
    
  
    $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()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
  

    $row = 3;

    $nbchar =  strlen($key['Month']);
    if ($nbchar == 1) {
      $newmonth = '0'.$key['Month'];
    }
    else{
      $newmonth = $key['Month'];
    }

    $distinctday=$this->Model->getRequete('SELECT DISTINCT YEAR(voiture_carburant_consomation.CONSOMATION_DATE) AS "Year", MONTH(voiture_carburant_consomation.CONSOMATION_DATE) AS "Month", DAY(voiture_carburant_consomation.CONSOMATION_DATE) AS "Day" FROM voiture_carburant_consomation WHERE '.$conditiond.' AND voiture_carburant_consomation.CONSOMATION_DATE like "%'.$key['Year'].'-'.$newmonth.'%"   '.$conditions.' '.$condvehicled.' '.$condstationd.' ORDER by Year ASC, Month ASC');
    foreach ($distinctday as $keys) {

      $nbcharday =  strlen($keys['Day']);
    if ($nbcharday == 1) {
      $newdate = '0'.$keys['Day'];
    }
    else{
      $newdate = $keys['Day'];
    }

      $custDtaData  = $this->Model->getRequete('SELECT car.CONSOMATION_ID, car.IS_PAYE, car.DATE_INSERSION, veh.PLAQUE_VEHICULE, car.CONSOMATION_DATE, car.KM_A_APROVISIONNEMENT, sta.STATION_NOM, car.QUANTITE_RECU, CASE WHEN car.PROVENANCEOUDESTINATION = 1 THEN "en provenance de" WHEN car.PROVENANCEOUDESTINATION = 2 THEN "à destination de " END as PROVENANCE, car.MONTANT_CONSOMATION, car.PRIX_UNITAIRE, car.NUM_RECU, car.NUM_GENERATED, car.PATH_FACTURE, uses.NOM as USNOM, uses.PRENOM as USPRENOM, chau.NOM as CHNOM, chau.PRENOM as CHPRENOM FROM voiture_carburant_consomation as car JOIN voiture_vehicules as veh ON veh.VEHICULE_ID = car.VEHICULE_ID JOIN voiture_station as sta on sta.STATION_ID = car.STATION_ID LEFT JOIN rh_employe as us ON us.ID_EMPLOYE = car.USERSAVER LEFT JOIN rh_employe as uses on uses.ID_EMPLOYE = us.ID_EMPLOYE LEFT JOIN rh_employe as chau on chau.ID_EMPLOYE = car.CHAUFFEUR_ID WHERE car.ID_STATUS = 1 AND '.$condition.' AND car.CONSOMATION_DATE like  "'.$key['Year'].'-'.$newmonth.'-'.$newdate.'" '.$conditions.' '.$condvehicle.' '.$condstation.' ');
      $custDtotal  = $this->Model->getRequeteOne('SELECT SUM(car.QUANTITE_RECU) as QUANTITE_RECU, SUM(car.MONTANT_CONSOMATION) as MONTANT_CONSOMATION FROM voiture_carburant_consomation as car JOIN voiture_vehicules as veh ON veh.VEHICULE_ID = car.VEHICULE_ID JOIN voiture_station as sta on sta.STATION_ID = car.STATION_ID  LEFT JOIN rh_employe as us ON us.ID_EMPLOYE = car.USERSAVER LEFT JOIN rh_employe as uses on uses.ID_EMPLOYE = us.ID_EMPLOYE LEFT JOIN rh_employe as chau on chau.ID_EMPLOYE = car.CHAUFFEUR_ID WHERE car.ID_STATUS = 1 AND '.$condition.' AND car.CONSOMATION_DATE like  "'.$key['Year'].'-'.$newmonth.'-'.$newdate.'" '.$conditions.' '.$condvehicle.' '.$condstation.' ');
     
    $tot= 0;
    foreach ($custDtaData as $value)
    {

        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $value['CONSOMATION_DATE']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value['PLAQUE_VEHICULE']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $value['STATION_NOM']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, '');
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, number_format($value['QUANTITE_RECU'], 2,',',' '));
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, number_format($value['MONTANT_CONSOMATION'], 2,',',' '));
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $value['KM_A_APROVISIONNEMENT']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $value['CHNOM'].' '.$value['CHPRENOM']);
        $row++;
        // $tot+= $PRIX_TOTAL;
    }  
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, number_format($custDtotal['QUANTITE_RECU'], 2,',',' '));
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, number_format($custDtotal['MONTANT_CONSOMATION'], 2,',',' '));
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, '');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('G'.$row)->applyFromArray($style_header);
    $objPHPExcel->getActiveSheet()->getStyle('H'.$row)->applyFromArray($style_header);
    $row++;
  
    

    }
    

    
  
    $objPHPExcel->getActiveSheet()->setTitle($key['Year'].' - '.$key['Month']);

    $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');


    }




   public function export_bon_de_commandes() {

      $PERIODE = $this->input->post('PERIODE');
      if ($PERIODE != null) {
      $str = $PERIODE;
      $dat=explode(" - ",$str);

     $date1 = DateTime::createFromFormat('d/m/Y', $dat[0]);
     $debut = $date1->format('Y-m-d');
     $date2 = DateTime::createFromFormat('d/m/Y', $dat[1]);
     $fin = $date2->format('Y-m-d');

     if ($debut == $fin) {
       $thismonth = date("Y-m");
       $conditions = ' AND app.DATE_BON_COMMANDE LIKE "'.$thismonth.'%"';
        $selectdates = date("Y-m");
     }
     else{
        $thismonth = date("Y-m");
        
        $conditions = '  AND app.DATE_BON_COMMANDE BETWEEN "'.$debut.'" AND "'.$fin.'"';
        $selectdates = 'Entre '.$debut.' et '.$fin;
     }
     
      }
      else{
        $thismonth = date("Y-m");
        $selectdates = date("Y-m");
        $conditions = ' AND app.DATE_BON_COMMANDE LIKE "'.$thismonth.'%"';
      }
      
      // $STATION_ID = $this->input->post('STATION_ID');
      // if ($STATION_ID != NULL) {
      //   $condstation = 'AND car.STATION_ID ='.$STATION_ID.' ';
      //   $condstationd = 'AND carburant_consomation.STATION_ID ='.$STATION_ID.' ';
      // }
      // else{
      //   $condstation = '';
      //   $condstationd = '';
      // }
     
     

    $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'),
    );

    $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,
        ),
    );

    // $mois=$this->Model->getRequete('SELECT DISTINCT YEAR(carburant_consomation.CONSOMATION_DATE) AS "Year", MONTH(carburant_consomation.CONSOMATION_DATE) AS "Month" FROM carburant_consomation WHERE '.$conditiond.' '.$conditions.' '.$condvehicled.' '.$condstationd.' ORDER by Year ASC, Month ASC');
    $mois=$this->Model->getRequete('SELECT DISTINCT YEAR(app.DATE_BON_COMMANDE) AS "Year", MONTH(app.DATE_BON_COMMANDE) AS "Month" FROM stock_approvisionnement as app WHERE app.IS_INTERNE = 0 '.$conditions.' ORDER by Year ASC, Month ASC');

    // echo 'SELECT DISTINCT YEAR(app.DATE_BON_COMMANDE) AS "Year", MONTH(app.DATE_BON_COMMANDE) AS "Month" FROM stock_approvisionnement as app WHERE app.IS_INTERNE = 0 '.$conditions.' ORDER by Year ASC, Month ASC';
    // exit();
  
    $i = 0;
     foreach ($mois as $key) {
    
    $objPHPExcel->setActiveSheetIndex($i)->mergeCells('A1:G1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $key['Year'].' - '.$key['Month']);

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

    #   DATE DU BON          


    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'FOURNISSEUR');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'PIECES');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'MONTANT PAYE');
    // $objPHPExcel->getActiveSheet()->setCellValue('E2', 'STATUT');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'ENREGISTRE');
    
  
    $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()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    // $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
  

    $row = 3;

    $nbchar =  strlen($key['Month']);
    if ($nbchar == 1) {
      $newmonth = '0'.$key['Month'];
    }
    else{
      $newmonth = $key['Month'];
    }

    $custDtaData  = $this->Model->getRequete('SELECT cll.NOM, cll.PRENOM, app.*, st.*, fn.*, COUNT(dtl.DETAIL_ID) as nbPiece, SUM(dtl.PRIX_UNITAIRE*dtl.QUANTITE) as montant FROM stock_approvisionnement as app JOIN rh_employe as usr on app.USER_ID = usr.ID_EMPLOYE LEFT JOIN stock_statut AS st on st.STATUT_ID = app.STATUT_ID LEFT JOIN stock_fournisseurs AS fn ON fn.FOURNISSEUR_ID = app.FOURNISSEUR_ID LEFT JOIN admin_collaborateurs AS cll on cll.COLLABORATEUR_ID = usr.COLLABORATEUR_ID LEFT JOIN stock_approvisionnement_detail AS dtl ON dtl.APPROVISIONNEMENT_ID=app.APPROVISIONNEMENT_ID WHERE app.IS_INTERNE = 0 '.$conditions.' AND app.DATE_BON_COMMANDE like  "'.$key['Year'].'-'.$newmonth.'%" GROUP BY app.APPROVISIONNEMENT_ID');

$custDtaDataTot  = $this->Model->getRequeteOne('SELECT SUM(dtl.PRIX_UNITAIRE*dtl.QUANTITE) as montant FROM stock_approvisionnement as app JOIN rh_employe as usr on app.USER_ID = usr.ID_EMPLOYE LEFT JOIN stock_statut AS st on st.STATUT_ID = app.STATUT_ID LEFT JOIN stock_fournisseurs AS fn ON fn.FOURNISSEUR_ID = app.FOURNISSEUR_ID LEFT JOIN admin_collaborateurs AS cll on cll.COLLABORATEUR_ID = usr.COLLABORATEUR_ID LEFT JOIN stock_approvisionnement_detail AS dtl ON dtl.APPROVISIONNEMENT_ID=app.APPROVISIONNEMENT_ID WHERE app.IS_INTERNE = 0 '.$conditions.' AND app.DATE_BON_COMMANDE like  "'.$key['Year'].'-'.$newmonth.'%"');

  
     
    $tot= 0;
    foreach ($custDtaData as $value)
    {
        $getpiece = $this->Model->getRequete('SELECT * FROM stock_approvisionnement_detail JOIN masque_piece on masque_piece.PIECE_ID = stock_approvisionnement_detail.PIECE_ID WHERE stock_approvisionnement_detail.APPROVISIONNEMENT_ID = '.$value['APPROVISIONNEMENT_ID'].'');
        // $getpiece = $this->Model->getRequete('SELECT * FROM stock_approvisionnement_detail JOIN masque_piece on masque_piece.PIECE_ID = stock_approvisionnement_detail.PIECE_ID WHERE stock_approvisionnement_detail.APPROVISIONNEMENT_ID = 79 ');
        // echo"<pre>";
        // print_r($getpiece);
        // echo"</pre>";
        $pie ='';
        foreach ($getpiece as $keys) {
           $pie .= $keys['PIECE_NOM']." (".$keys['QUANTITE']." a ".$keys['PRIX_UNITAIRE']." BIF) , ";
        }
        // $pie .='-';
        // $pie = str_replace('\n-'," ",$pie);
        // echo $pie;
        // exit();
        $date_insertion = new DateTime($value['DATE_INSERTION']);
        $date_bon = new DateTime($value['DATE_BON_COMMANDE']);
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $date_bon->format('d/m/Y'));
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value['FOURNISSEUR_NOM']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $pie);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $value['montant']);
        // $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '0');
        // $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $value['STATUT_DESCR']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $date_insertion->format('d/m/Y')." par ".$value['NOM']." ".$value['COLLABORATEUR_PRENOM']);
        // $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $value['CHNOM'].' '.$value['CHPRENOM']);
        $row++;
        // $tot+= $PRIX_TOTAL;
    }  
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, '');
    
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, number_format($custDtaDataTot['montant'], 2,',',' '));
    $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '');
    // $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, number_format($custDtotal['MONTANT_CONSOMATION'], 2,',',' '));
    // $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '');
    // $objPHPExcel->getActiveSheet()->setCellValue('H'.$row, '');
    // $objPHPExcel->getActiveSheet()->getStyle('A'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('B'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('C'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('D'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('E'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('F'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('G'.$row)->applyFromArray($style_header);
    // $objPHPExcel->getActiveSheet()->getStyle('H'.$row)->applyFromArray($style_header);
    // $row++;
  
    

    // }
    

    
  
    $objPHPExcel->getActiveSheet()->setTitle($key['Year'].' - '.$key['Month']);

    $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');


    }

    }

 

    

    