0x1998 - MANAGER

Edit File: export_activite.php

<?php
session_cache_limiter('private');

include "config/auth-config.php";
include "config/authcheck.php";
include "config/garde.php";

// VERIFICATION
	 if (!verif_autor($_SESSION['user'],5))
	{
	$msg="Vos autorisations sont insuffisantes pour cette action";
	echo "<meta http-equiv=\"refresh\" content=\"0;url=accueil.php?msg=$msg\"> ";
	die();
	}

require_once('config/excel/Worksheet.php');
  require_once('config/excel/Workbook.php');

$liste=autor_liste($_SESSION['user']);
$current_year=$_POST['current_year'];
$current_month=$_POST['current_month'];
$categorie=$_POST['categorie'];

$next_month=$current_month+1;

$firstday_nextmonth=mktime(0,0,0,$next_month,1,$current_year);
$firstday_currentmonth=mktime(0,0,0,$current_month,1,$current_year);
$lastday_currentmonth=mktime(0,0,0,$next_month,0,$current_year);
//$lastday_currentmonth=$firstday_nextmonth - 86400;
//$get_lastday_previousmonth=getdate($lastday_previousmonth);
$get_lastday_currentmonth=getdate($lastday_currentmonth);
$get_firstday_nextmonth=getdate($firstday_nextmonth);
$get_firstday_currentmonth=getdate($firstday_currentmonth);

$currentmonth_lastday=$get_lastday_currentmonth['mday'];

function HeaderingExcel($filename) {
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=$filename" );
      header("Expires: 0");
      header("Cache-Control: no-cache, post-check=0,pre-check=0");
      header("Pragma: public");
      }

// HTTP headers
  //HeaderingExcel('activite.xls');

// Creating a workbook
  $workbook = new Workbook("out/activite.xls");
  // Creating the first worksheet
  
  
// FORMAT DES COLONNES
 $format7 =& $workbook->add_format(); 
 $format13 =& $workbook->add_format(); 
 $format9 =& $workbook->add_format(); 
 $format0 =& $workbook->add_format(); 
 $format1 =& $workbook->add_format(); 
 $format2 =& $workbook->add_format(); 
 $format3 =& $workbook->add_format(); 
 $format4 =& $workbook->add_format(); 
 $format8 =& $workbook->add_format(); 
 $format5 =& $workbook->add_format(); 
 $format6 =& $workbook->add_format(); 
 $format11 =& $workbook->add_format(); 
 $format12 =& $workbook->add_format(); 
 $format14 =& $workbook->add_format(); 
 $format15 =& $workbook->add_format(); 
 
$format0->set_align('center');
$format0->set_bold('1'); 
$format0->set_border('1');

$format1->set_pattern();
$format1->set_fg_color('42'); 
$format1->set_color('black'); 
$format1->set_left('1');
$format1->set_top('1');
$format1->set_bottom('1');
$format1->set_align('center');

$format2->set_pattern();
$format2->set_fg_color('29'); 
$format2->set_color('black'); 
$format2->set_bold('1');

$format3->set_pattern();
$format3->set_fg_color('47'); 
$format3->set_color('black'); 
$format3->set_bold('1');

$format4->set_pattern();
$format4->set_fg_color('black'); 
$format4->set_color('white'); 
$format4->set_bold('1'); 
$format4->set_align('center');
$format4->set_border('1');

$format5->set_pattern();
$format5->set_fg_color('42'); 
$format5->set_color('black'); 
$format5->set_bold('1');

$format6->set_top('1');
$format6->set_align('center');

$format7->set_border('1');
$format7->set_align('vcenter');

$format8->set_pattern();
$format8->set_border('1');
$format8->set_align('center');
$format8->set_fg_color('orange');

$format9->set_left('1');
$format9->set_top('1');
$format9->set_bottom('1');
$format9->set_right('1');
$format9->set_align('center');

$format11->set_align('center');

$format12->set_align('center');
$format12->set_bold('1'); 
$format12->set_right('1');
$format12->set_top('1');
$format12->set_left('1');

$format13->set_pattern();
$format13->set_border('1');
$format13->set_fg_color('42'); 
$format13->set_color('black'); 
$format13->set_align('center');

$format14->set_align('center');
$format14->set_left('1');

$format15->set_align('center');
$format15->set_right('1');

// CREATION DES WORKSHEET

$name2="Activite ". $current_month."-".$current_year;

$worksheet1 =& $workbook->add_worksheet($name2);
$worksheet1->set_margins_TB('0.5');
$worksheet1->hide_gridlines();

for ($i=1;$i<=$currentmonth_lastday;$i++)
{
$jour_date=mktime(0,0,0,$current_month,$i,$current_year);
$jour_sem=getdate($jour_date);
$jour=$jour_sem['wday'];
$js[$i]=$jour;
}

$worksheet1->set_column(0, 0 , 25);

//ENTETES
$message="";

// PREMIERE LIGNE : ENTETE
$sem=1;
$tab_jour = array(0 =>'dim', 'lun', 'mar', 'mer', 'jeu', 'ven', 'sam' );
//$j=2;
$k=2;
for ($i=1;$i<=$currentmonth_lastday;$i++)
{
$jour_date=mktime(0,0,0,$current_month,$i,$current_year);
$jour_sem=getdate($jour_date);
$jour=$jour_sem['wday'];
$js[$i]=$jour;

$page="worksheet".$sem;

$jour=$tab_jour[$jour];
$format_jour=$format12;
$format_jour2=$format14;
if ($jour=='sam' || $jour=='dim' || EstFerie($current_year,$current_month,$i)=='O') { $format_jour=$format4; $format_jour2=$format4;}
$$page->write_string(0,$k,$jour,$format_jour);
$$page->write_string(1,$k,$i,$format_jour2);
$k++;
}
$$page->write_string(0,$k,'Volume',$format0);
$$page->merge_cells(0, $k, 1, $k);
$k++;
$$page->write_string(0,$k,'Remarque',$format0);
$$page->merge_cells(0, $k, 1, $k);

// LIGNES SUIVANTES

$sql="select *, convert(if(ordre=0,'99',ordre), SIGNED INTEGER) as tri from ".PREF."users 
		inner join user_type on user_type.id=".PREF."users.id 
		WHERE ordre >0 AND niveau>=2  order by tri, `nom` ";
$result2 = mysqli_query($link,$sql) or die("requete impossible 1.. $sql");

$numligne=2;
$compteurOrdre=0;
while ($info=mysqli_fetch_array($result2))
{
// On cherche si on doit insérer des lignes vides ou pas 
$decallage=$info['ordre']-$compteurOrdre;
if ($decallage>1)
{
	for ($i=1;$i<$decallage;$i++)
	{
		$numligne=$numligne+4;
	}
}
	
	$ligne1=$numligne;
	$ligne2=$numligne+1;
	$ligne3=$numligne+2;
	$ligne4=$numligne+3;
	
	// ON ECRIT LES ENTETES DE LIGNE COLONNE 2
	$$page->write($ligne1,1,'M',$format9);
	$$page->write($ligne2,1,'AM',$format9);
	$$page->write($ligne3,1,'S',$format9);
	$$page->write($ligne4,1,'N',$format9);	
	
	$$page->merge_cells($ligne1, 0, $ligne4, 0);

// CHARGEMENT DES VALEURS -------------------
//
// JOUR
unset($jour);
$sql="select day(activite_date) as jour, activite_type, if (activite_type='M' or activite_type='AM',list_activite_medtimeJ, list_activite_medtimeN) from activite
		inner join list_activite on activite_activite=list_activite_id
		where `activite_user`='".$info[0]."' 
		and month(activite_date)='".$current_month."'
		and year(activite_date)='".$current_year."'
		order by jour";
		
$result=mysqli_query($link,$sql) or die("requete impossible 1.. $sql");
while ($a_row=mysqli_fetch_array($result))
{
if ($info[0]==91 && $a_row[2]=='NC') $a_row[2]='CS';	
$index=$a_row[0];
$type=$a_row[1];
$jour[$type][$index]=$a_row[2];
}
	
	
$message="";
$message=$info['nom'];

for ($ss=1;$ss<=$sem;$ss++)
{
$page="worksheet$ss";
$$page->write($numligne,0,$message,$format7);
}

$j=2;
$sem=1;

for ($i=1;$i<=$currentmonth_lastday;$i++)
	{
	
		$page="worksheet".$sem;

// MATIN
	if (isset($jour['M'][$i]))
		{
		// TRANSFORMATION
		if ($jour['M'][$i]=='A' && $js[$i]==6) $jour['M'][$i]='P5';
		elseif ($jour['M'][$i]=='P5' && ($js[$i]==0 || EstFerie($current_year,$current_month,$i)=='O')) $jour['M'][$i]='G5';
	
				$$page->write($ligne1,$j,$jour['M'][$i],$format6);
		}
	else
		{
				$$page->write($ligne1,$j,'',$format6);
		}
	// APRES MIDI
	if (isset($jour['AM'][$i]))
		{
		// TRANSFORMATION
		if ($jour['AM'][$i]=='P5' && ($js[$i]==6 || $js[$i]==0 || EstFerie($current_year,$current_month,$i)=='O')) $jour['AM'][$i]='G5';
	
				$$page->write($ligne2,$j,$jour['AM'][$i],$format6);
		}
	else
		{
				$$page->write($ligne2,$j,'',$format6);
		}
	// SOIREE
	if (isset($jour['S'][$i]))
		{
		// TRANSFORMATION
		//if ($jour['S'][$i]=='P') $jour['S'][$i]='G';
	
				$$page->write($ligne3,$j,$jour['S'][$i],$format6);
		}
	else
		{
				$$page->write($ligne3,$j,'',$format6);
		}
	// NUIT
	if (isset($jour['N'][$i]))
		{
		// TRANSFORMATION
		//if ($jour['N'][$i]=='P') $jour['N'][$i]='G';
	
				$$page->write($ligne4,$j,$jour['N'][$i],$format6);
		}
	else
		{
				$$page->write($ligne4,$j,'',$format6);
		}

//------------------------------
	$j++; // ON SE DECALLE D'UNE COLONNE
	}
	
	// ON NOTE LE VOLUME EN AVANT DERNIERE COLONNE
  $req="select volume from solde_activite where `activite_user`='".$info[0]."' and `month`='".$current_month."' AND `year`='".$current_year."'";
  $result3 = mysqli_query($link,$req) or die("requete impossible 2 . $req");
  $info3=mysqli_fetch_array($result3);
	
	if ($info3['volume']<0) $$page->write($ligne1,$j,$info3['volume'],$format8);
	else $$page->write($ligne1,$j,$info3['volume'],$format7);
	$$page->merge_cells($ligne1, $j, $ligne4, $j);	
	
	$j++;
	
	// ON NOTE LA REMARQUE EN DERNIERE COLONNE
  $req="select du_rem from dispo_user where `du_user`='".$info[0]."' and `du_month`='".$current_month."' AND `du_year`='".$current_year."' and `du_type`='activite' ";
  $result4 = mysqli_query($link,$req) or die("requete impossible 2 . $req");
  $info4=mysqli_fetch_array($result4);
	
	$$page->write($ligne1,$j,$info4['du_rem'],$format7);
	$$page->merge_cells($ligne1, $j, $ligne4, $j);

$numligne=$numligne+4;
$compteurOrdre=$info['ordre'];
}

// ON FAIT LE RECAP DE TOUT EN BAS
$sql="select day(activite_date) as jour,activite_type, list_activite_denom, count(activite_id)  from activite
inner join list_activite on list_activite_id=activite_activite 
where month(activite_date)=".$current_month." 
and year(activite_date)=".$current_year."

group by
activite_type, activite_activite, jour";

$result=mysqli_query($link,$sql);
while ($a_row=mysqli_fetch_array($result))
{
$jour=$a_row[0];
$type=$a_row[1];
$activite=$a_row[2];
$stats[$type][$activite][$jour]=$a_row[3];

}
// ON A LE GROS ARRAY > ON EST PARTI !!!
	$ligne1=$numligne;
	$ligne2=$numligne+1;
	$ligne3=$numligne+2;
	$ligne4=$numligne+3;
	$ligne5=$numligne+4;
	$ligne6=$numligne+5;
	$ligne7=$numligne+6;
	$ligne8=$numligne+7;
	$j=0;
// COLONNE 1
$$page->write($ligne1,$j,'Total Regul Jour',$format6);
$$page->write($ligne2,$j,'Total Regul Nuit',$format6);
$$page->write($ligne3,$j,'Total Trans Jour',$format6);
$$page->write($ligne4,$j,'Total Trans Nuit',$format6);
$$page->write($ligne5,$j,'Total Astr Jour',$format6);
$$page->write($ligne6,$j,'Total Astr Nuit',$format6);
$$page->write($ligne7,$j,'Total Super Jour',$format6);
$$page->write($ligne8,$j,'Total Super Nuit',$format6);

// ON MERGE les 2 PREMIERES COL	
	$$page->merge_cells($ligne1, 0, $ligne1, 1);
	$$page->merge_cells($ligne2, 0, $ligne2, 1);
	$$page->merge_cells($ligne3, 0, $ligne3, 1);
	$$page->merge_cells($ligne4, 0, $ligne4, 1);

$j=2;
	for ($i=1;$i<=$currentmonth_lastday;$i++)
	{
	// REGUL JOUR >
	$stats['M']['Régulation'][$i]=isset($stats['M']['Régulation'][$i]) ? $stats['M']['Régulation'][$i] :0;
	$stats['AM']['Régulation'][$i]=isset($stats['AM']['Régulation'][$i]) ? $stats['AM']['Régulation'][$i] :0;
	if ($stats['M']['Régulation'][$i]==$stats['AM']['Régulation'][$i]) $message=$stats['AM']['Régulation'][$i];
	else $message=$stats['M']['Régulation'][$i]."|".$stats['AM']['Régulation'][$i];
	$format=findFormat('Regul','Jour',$stats['M']['Régulation'][$i],$stats['AM']['Régulation'][$i],$js[$i],$i);
	$$page->write($ligne1,$j,$message,$$format);
	// REGUL NUIT >
	$stats['S']['Régulation'][$i]=isset($stats['S']['Régulation'][$i]) ? $stats['S']['Régulation'][$i] :0;
	$stats['N']['Régulation'][$i]=isset($stats['N']['Régulation'][$i]) ? $stats['N']['Régulation'][$i] :0;	
	if ($stats['S']['Régulation'][$i]==$stats['N']['Régulation'][$i]) $message=$stats['N']['Régulation'][$i];
	else $message=$stats['S']['Régulation'][$i]."|".$stats['N']['Régulation'][$i];
	$format=findFormat('Regul','Nuit',$stats['S']['Régulation'][$i],$stats['N']['Régulation'][$i],$js[$i],$i);
	$$page->write($ligne2,$j,$message,$$format);
	// TRANSPORT JOUR >
	$stats['M']['Transport'][$i]=isset($stats['M']['Transport'][$i]) ? $stats['M']['Transport'][$i] :0;
	$stats['AM']['Transport'][$i]=isset($stats['AM']['Transport'][$i]) ? $stats['AM']['Transport'][$i] :0;
	$stats['M']['Renfort'][$i]=isset($stats['M']['Renfort'][$i]) ? $stats['M']['Renfort'][$i] :0;
	$stats['AM']['Renfort'][$i]=isset($stats['AM']['Renfort'][$i]) ? $stats['AM']['Renfort'][$i] :0;	
	if (($stats['M']['Transport'][$i]+$stats['M']['Renfort'][$i])==($stats['AM']['Transport'][$i]+$stats['AM']['Renfort'][$i])) $message=($stats['AM']['Transport'][$i]+$stats['M']['Renfort'][$i]);
	else $message=($stats['M']['Transport'][$i]+$stats['M']['Renfort'][$i])."|".($stats['AM']['Transport'][$i]+$stats['AM']['Renfort'][$i]);
	$format=findFormat('Transport','Jour',($stats['M']['Transport'][$i]+$stats['M']['Renfort'][$i]),($stats['AM']['Transport'][$i]+$stats['AM']['Renfort'][$i]),$js[$i],$i);
	$$page->write($ligne3,$j,$message,$$format);
	// TRANSPORT NUIT >
	$stats['S']['Transport'][$i]=isset($stats['S']['Transport'][$i]) ? $stats['S']['Transport'][$i] :0;
	$stats['N']['Transport'][$i]=isset($stats['N']['Transport'][$i]) ? $stats['N']['Transport'][$i] :0;	
	if ($stats['S']['Transport'][$i]==$stats['N']['Transport'][$i]) $message=$stats['N']['Transport'][$i];
	else $message=$stats['S']['Transport'][$i]."|".$stats['N']['Transport'][$i];
	$format=findFormat('Transport','Nuit',$stats['S']['Transport'][$i],$stats['N']['Transport'][$i],$js[$i],$i);
	$$page->write($ligne4,$j,$message,$$format);
	// ASTREINTE JOUR
	$stats['M']['Astreinte'][$i]=isset($stats['M']['Astreinte'][$i]) ? $stats['M']['Astreinte'][$i] :0;
	$stats['AM']['Astreinte'][$i]=isset($stats['AM']['Astreinte'][$i]) ? $stats['AM']['Astreinte'][$i] :0;	
	if ($stats['M']['Astreinte'][$i]==$stats['AM']['Astreinte'][$i]) $message=$stats['M']['Astreinte'][$i];
	else $message=$stats['M']['Astreinte'][$i]."|".$stats['AM']['Astreinte'][$i];
	$format=findFormat('Astreinte','Jour',$stats['M']['Astreinte'][$i],$stats['AM']['Astreinte'][$i],$js[$i],$i);
	$$page->write($ligne5,$j,$message,$$format);	
	// ASTREINTE NUIT
	$stats['S']['Astreinte'][$i]=isset($stats['S']['Astreinte'][$i]) ? $stats['S']['Astreinte'][$i] :0;
	$stats['N']['Astreinte'][$i]=isset($stats['N']['Astreinte'][$i]) ? $stats['N']['Astreinte'][$i] :0;	
	if ($stats['S']['Astreinte'][$i]==$stats['N']['Astreinte'][$i]) $message=$stats['S']['Astreinte'][$i];
	else $message=$stats['S']['Astreinte'][$i]."|".$stats['N']['Astreinte'][$i];
	$format=findFormat('Astreinte','Nuit',$stats['S']['Astreinte'][$i],$stats['N']['Astreinte'][$i],$js[$i],$i);
	$$page->write($ligne6,$j,$message,$$format);
	// SUPERVISION JOUR >
	$stats['M']['Supervision'][$i]=isset($stats['M']['Supervision'][$i]) ? $stats['M']['Supervision'][$i] :0;
	$stats['AM']['Supervision'][$i]=isset($stats['AM']['Supervision'][$i]) ? $stats['AM']['Supervision'][$i] :0;
	if ($stats['M']['Supervision'][$i]==$stats['AM']['Supervision'][$i]) $message=$stats['AM']['Supervision'][$i];
	else $message=$stats['M']['Supervision'][$i]."|".$stats['AM']['Supervision'][$i];
	$format=findFormat('Supervision','Jour',$stats['M']['Supervision'][$i],$stats['AM']['Supervision'][$i],$js[$i],$i);
	$$page->write($ligne7,$j,$message,$$format);
	// SUPERVISION NUIT >
	$stats['S']['Supervision'][$i]=isset($stats['S']['Supervision'][$i]) ? $stats['S']['Supervision'][$i] :0;
	$stats['N']['Supervision'][$i]=isset($stats['N']['Supervision'][$i]) ? $stats['N']['Supervision'][$i] :0;	
	if ($stats['S']['Supervision'][$i]==$stats['N']['Supervision'][$i]) $message=$stats['N']['Supervision'][$i];
	else $message=$stats['S']['Supervision'][$i]."|".$stats['N']['Supervision'][$i];
	$format=findFormat('Supervision','Nuit',$stats['S']['Supervision'][$i],$stats['N']['Supervision'][$i],$js[$i],$i);
	$$page->write($ligne8,$j,$message,$$format);
	
	
	$j++;
	}

$panes[0]=2;
	$panes[1]=2;
	$panes[2]=2;
	$panes[3]=2;
	$worksheet1->freeze_panes($panes);
  $workbook->close();