File "extraction.php"

Full Path: /home/asmplong/www/asm/extraction.php
File size: 10.25 KB
MIME-type: text/x-php
Charset: utf-8

<?php
session_start();
if(!isset($_SESSION['ident_membre']))
{
header('location:login.php');
}
elseif((!isset($_GET['date_debut'])) && (!isset($_GET['date_fin'])))
{
header('location:login.php');	
}
else
{
//Connexion à la base de données
include("inc.conec.php");
include("inc.fonctions.php"); 
include("inc.config.php"); 

$date_debut = $_GET['date_debut'].' 00:00:00';
$date_create_debut = date_create($date_debut);
$date_fin = $_GET['date_fin'].' 23:59:59';
$date_create_fin = date_create($date_fin);

/** Error reporting */
//error_reporting(E_ALL);

/** PHPExcel */
require_once 'Classes/PHPExcel.php';

/** PHPExcel_IOFactory */
require_once 'Classes/PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$getProp = $objPHPExcel->getProperties();

$getProp->setCreator("AMAR HASNI");
$getProp->setLastModifiedBy("AMAR HASNI");
$getProp->setTitle("EXTRACTION STATISTIQUES PLONGÉES ASM");
$getProp->setSubject("EXTRACTION STATISTIQUES PLONGÉES ASM");
$getProp->setDescription("EXTRACTION STATISTIQUES PLONGÉES ASM");
$getProp->setKeywords("EXTRACTION STATISTIQUES PLONGÉES ASM");
$getProp->setCategory("EXTRACTION STATISTIQUES PLONGÉES ASM");
		

$sharedStyle1 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(
	array('fill' 	=> array(
								'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
								'color'		=> array('argb' => 'FFCCFFCC')
							),
		  'borders' => array(	
								'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
							),
		  'alignment'=>array(
    							'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
		 ));
		 
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D4");


$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$sharedStyle2 = new PHPExcel_Style();

$sharedStyle2->applyFromArray(
	array('font'	=>array(
								'name'      =>  'Arial',
								'size'      =>  16,
								'bold'      => true
							)
		 ));



$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "A1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setIndent(1);

$sharedStyle3 = new PHPExcel_Style();

$sharedStyle3->applyFromArray(
	array('font'	=>array(
								'name'      =>  'Arial',
								'size'      =>  14,
								'bold'      => true
							)
		 ));


$objPHPExcel->getActiveSheet()->mergeCells('A3:E3');
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A3");
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setIndent(1);

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', 'STATISTIQUES PLONGÉES ASM DU '.date_format($date_create_debut, 'd-m-Y').' AU '.date_format($date_create_fin, 'd-m-Y').'')
            ->setCellValue('A3', 'NOMBRE DE PLONGÉES PAR MEMBRE (ARCHIVÉES)')
			->setCellValue('A4', 'NOM')
			->setCellValue('B4', 'PRENOM')
            ->setCellValue('C4', 'NIVEAU')
			->setCellValue('D4', 'PLONGÉES');                 




$i = 4;

		
			$req = mysql_query("SELECT * FROM ".$db_prefixe."membres WHERE membre_statut!='4' ORDER BY membre_id ASC");
			
			while($data = mysql_fetch_array($req))
			{
				

				$req_event = mysql_query("SELECT count(*) AS Total FROM ".$db_prefixe."events WHERE event_archives='1' AND event_type!='8' AND event_id IN (SELECT inscription_id_event FROM  ".$db_prefixe."inscriptions WHERE inscription_id_membre = '".$data['membre_id']."' AND inscription_present='1') AND event_date >= '".$date_debut."' AND event_date <= '".$date_fin."'");
				
				$data_event = mysql_fetch_array($req_event);
				
				if($data_event['Total'] != '0')
				{
				
				$i++;		
						$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$i, $data['membre_nom'])
						->setCellValue('B'.$i, $data['membre_prenom'])
						->setCellValue('C'.$i, $data['membre_niveau'])
						->setCellValue('D'.$i, $data_event['Total']);
				}
				
			}
			$i++;
			$i++;
			
			$objPHPExcel->getActiveSheet()->mergeCells('A'.$i.':E'.$i);
			$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A".$i);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setIndent(1);


// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A'.$i, 'NOMBRE DE PLONGÉES ORGANISÉES');

			$i++;


			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$i);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B".$i);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C".$i);	
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D".$i);
											
$objPHPExcel->setActiveSheetIndex(0)			
			->setCellValue('A'.$i, 'NOM')
			->setCellValue('B'.$i, 'PRENOM')
            ->setCellValue('C'.$i, 'NIVEAU')
			->setCellValue('D'.$i, 'NOMBRE'); 			
			

			$req2 = mysql_query("SELECT * FROM ".$db_prefixe."membres WHERE membre_statut='2' OR membre_statut='3' ORDER BY membre_id ASC");
			
			while($data2 = mysql_fetch_array($req2))
			{
				

				$req_event2 = mysql_query("SELECT count(*) AS Total FROM ".$db_prefixe."events WHERE event_id_dp  = '".$data2['membre_id']."' AND event_type!='8' AND event_archives='1' AND event_date >= '".$date_debut."' AND event_date <= '".$date_fin."'");
				
				$data_event2 = mysql_fetch_array($req_event2);
				
				if($data_event2['Total'] != '0')
				{
				
				$i++;		
						$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$i, $data2['membre_nom'])
						->setCellValue('B'.$i, $data2['membre_prenom'])
						->setCellValue('C'.$i, $data2['membre_niveau'])
						->setCellValue('D'.$i, $data_event2['Total']);
				}
				
			}
			$i++;
			$i++;


			
			$objPHPExcel->getActiveSheet()->mergeCells('A'.$i.':E'.$i);
			$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A".$i);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setIndent(1);
			


// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A'.$i, 'NOMBRE DE PLONGÉES PAR LIEU');

			$i++;


			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A".$i);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B".$i);
											
$objPHPExcel->setActiveSheetIndex(0)			
			->setCellValue('A'.$i, 'NOM DU LIEU')
			->setCellValue('B'.$i, 'NOMBRE'); 			
			

			foreach($tab_lieu as $lieu)
			{
				
				$req_event3 = mysql_query("SELECT count(*) AS Total_lieu FROM ".$db_prefixe."events WHERE event_archives ='1' AND event_type!='8' AND (event_lieu_1 = '".addslashes($lieu)."' OR event_lieu_2 = '".addslashes($lieu)."') AND event_date >= '".$date_debut."' AND event_date <= '".$date_fin."'");
				
				$data_event3 = mysql_fetch_array($req_event3);
				
				if(($data_event3['Total_lieu'] != '0') && ($lieu != ''))
				{
				
				$i++;		
						$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$i, $lieu)
						->setCellValue('B'.$i, $data_event3['Total_lieu']);
				}
				
			}
			
			$i++;
			$i++;
			
			$objPHPExcel->getActiveSheet()->mergeCells('A'.$i.':E'.$i);
			$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A".$i);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setIndent(1);

			$req4 = mysql_query("SELECT count(*) AS Total_annulees FROM ".$db_prefixe."events WHERE event_archives  = '2' AND event_type!='8' AND event_date >= '".$date_debut."' AND event_date <= '".$date_fin."'");
				
			$data4 = mysql_fetch_array($req4);

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A'.$i, 'NOMBRE DE PLONGÉES ANNULÉES : '.$data4['Total_annulees']);
			
			
			$i++;
			$i++;

			$objPHPExcel->getActiveSheet()->mergeCells('A'.$i.':E'.$i);
			$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
			$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A".$i);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setIndent(1);
		
			$req5 = mysql_query("SELECT * FROM ".$db_prefixe."membres WHERE membre_statut='4' ORDER BY membre_id ASC");
			
			$nb_baptemes = '0';
			
			while($data5 = mysql_fetch_array($req5))
			{			

				$req_event = mysql_query("SELECT count(*) AS Total_baptemes FROM ".$db_prefixe."events WHERE event_archives='1' AND event_type!='8' AND event_id IN (SELECT inscription_id_event FROM  ".$db_prefixe."inscriptions WHERE inscription_id_membre = '".$data5['membre_id']."') AND event_date >= '".$date_debut."' AND event_date <= '".$date_fin."'");
				
				$data_event = mysql_fetch_array($req_event);
				
				
				
				$nb_baptemes = $data_event['Total_baptemes'] + $nb_baptemes;
				
			}			
				
$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A'.$i, 'NOMBRE DE BAPTÈMES (INVITÉS) : '.$nb_baptemes);			
							

// Miscellaneous glyphs, UTF-8
//$objPHPExcel->setActiveSheetIndex(0)
  //          ->setCellValue('A4', 'Miscellaneous glyphs')
    //        ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Extraction ASM');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="extraction_ASM_du_'.changedateusfr($_GET['date_debut']).'_au_'.changedateusfr($_GET['date_fin']).'.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); 
exit;
}