0x1998 - MANAGER

Edit File: export_liste.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');

$current_year=$_POST['current_year'];
$current_month=$_POST['current_month'];
$categorie=$_POST['categorie']; // en fait c'est la skill !

// ON RAPATRIE LES CARACTERISTIQUES DE LA SKILL
$sql="select * from list_skill where list_skill_id=".$categorie;
$result=mysqli_query($link,$sql);
$val=mysqli_fetch_array($result);
$splitSem=$val['list_skill_splitSem'];

$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_currentmonth=getdate($lastday_currentmonth);
$get_firstday_currentmonth=getdate($firstday_currentmonth);
$currentmonth_lastday=$get_lastday_currentmonth['mday'];

// ---- MATRICE DES JOURS
	$result=mysqli_query($link,"select list_matrice_ferie, list_matrice_lundi, list_matrice_mardi, list_matrice_mercredi,
						list_matrice_jeudi, list_matrice_vendredi, list_matrice_samedi, list_matrice_dimanche
						from list_matrice_jours where list_matrice_skill=".$categorie);
	
	$row=mysqli_fetch_array($result);
	for ($i=0;$i<=7;$i++)
		{
			$matrice[$i]=$row[$i];
		}
	mysqli_free_result($result);
$libelle_value=array("J"=>"Jour","N"=>"Nuit","W"=>"WE","M"=>"Matin","AM"=>"AM", "AM1"=>"AM1", "AM2"=>"AM2", "AM3"=>"AM3","Soir3"=>"Soir3" ,"Soir"=>"Soir");	
//--------------------------------------

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('dispo.xls');

// Creating a workbook
  $workbook = new Workbook("out/dispo.xls");
  // Creating the first worksheet

// FORMAT DES COLONNES

$format1 =& $workbook->add_format(); // FOND VERT POUR ++ et +
 $format2 =& $workbook->add_format(); // FOND ROUGE POUR TOTAUX
 $format3 =& $workbook->add_format(); // FOND ORANGE POUR TOTAUX
 $format4 =& $workbook->add_format(); // ENTETE BLANC SUR FOND NOIR
 $format5 =& $workbook->add_format(); // FOND VERT POUR TOTAUX
 $format11 =& $workbook->add_format();

$formatG_V=& $workbook->add_format();
 $formatM_V=& $workbook->add_format();
 $formatD_V=& $workbook->add_format(); 
 $formatG_=& $workbook->add_format();
 $formatM_=& $workbook->add_format();
 $formatD_=& $workbook->add_format();
 
 
$formatG_V->set_pattern();
$formatG_V->set_fg_color('42'); 
$formatG_V->set_color('black'); 
$formatG_V->set_left('1');
$formatG_V->set_top('1');
$formatG_V->set_bottom('1');
$formatG_V->set_align('center');

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

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

$formatM_->set_top('1');
$formatM_->set_bottom('1');
$formatM_->set_align('center');

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

$formatD_->set_top('1');
$formatD_->set_bottom('1');
$formatD_->set_right('1');
$formatD_->set_align('center');
 
$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_right('1');
$format1->set_align('center');

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

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

$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'); 
$format5->set_align('center');

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

// CREATION DES WORKSHEET

if ($splitSem==1)
{
$sem=1;
$name2="Dispo ". $current_month."-".$current_year."-".$sem;
}
else
{
$name2="Dispo ". $current_month."-".$current_year;
}

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

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;
 if (($i>1 && $js[$i]==1) && $splitSem==1)
    {
     $sem++;
     $name2="Dispo ". $current_month."-".$current_year."-".$sem;
     $page="worksheet$sem";
     $$page =& $workbook->add_worksheet($name2);
	 $$page->set_margins_TB('0.5');
	 $$page->hide_gridlines();
     $$page->set_column(0, 0 , 25);
    }
}
//---------------- FIN CREATION WORKSHEET
//----PREMIERE LIGNE : ENTETES
$message="";

$sem=1;
$tab_jour = array(1=>'lun', 2=>'mar', 3=>'mer', 4=>'jeu', 5=>'ven', 6=>'sam',7 =>'dim');
$col=2;
for ($i=1;$i<=$currentmonth_lastday;$i++)
{
// on détermine le js	
$jour_date=mktime(0,0,0,$current_month,$i,$current_year);
$jour_sem=getdate($jour_date);
$jour=$jour_sem['wday'];
$jour=($jour==0)? 7:$jour; //--> si jour=0 (dimanche) alors jour=7
$js[$i]=$jour;
$js_matrice[$i]= (EstFerie($current_year,$current_month,$i)=='O')? 0 : $jour; // Si ferie on met 0

if (($i>1 && $js[$i]==1)&& $splitSem==1)
{
$sem++;
$col=2;
}

$page="worksheet".$sem;

$jour=$tab_jour[$jour]; // $jour = le jour en texte

// en fonction du js_matrice je récup la matrice pour savoir quoi mettre
$jour_matrice=explode(";",$matrice[$js_matrice[$i]]);
$number[$i]=count($jour_matrice);

// FORMAT
$format_jour=rech_format('entete',$js_matrice[$i]);
//-------------
// PREMIERE LIGNE : Le texte du jour
$$page->write_string(0,$col,$jour,$$format_jour);
// SECONDE LIGNE : le numéro du jour
$$page->write_string(1,$col,$i,$$format_jour);
// TROISIEME LIGNE : Autant de col que de type dans la matrice
$passage=1; // pour compter la position de chaque foreach ci-dessous
foreach ($jour_matrice as $key => $value)
	{
		$$page->write_string(2,$col,$libelle_value[$value],$$format_jour);
		$$page->set_column($col, $col , 4); // LARGEUR=4
		$memoire_col[$i][$value]['col']=$col;
		$memoire_col[$i][$value]['sem']=$sem;
		// je cherche et stocke le format de cette colonne (fonction du nombre[i]);
			$format_col[$i][$value]=rech_format_col($number[$i],$passage);

// si j'ai plus d'une colonne il faut réécrire les 2 premières lignes
			if ($number[$i]>1 && $passage>1)
			{
				$$page->write_string(0,$col,'',$$format_jour);
				$$page->write_string(1,$col,'',$$format_jour);

}
		$passage++;
		$col++;
	}
// on merge si number>1
if ($number[$i]>1)
	{
	$debut=$col-$number[$i];	
	$fin=$col-1;
	$$page->merge_cells(0, $debut, 0, $fin);
	$$page->merge_cells(1, $debut, 1, $fin);
	}
}

$$page->write_string(0,$col,'Remarque',$format11); // REMARQUE SUR LA DERNIERE COL DU DERNIER WORSHEET
$$page->set_column($col, $col , 70);
$col_remarque=$col;
$page_remarque=$page;

// ECRITURE DES PREMIERES COLONNES AVEC LES NOMS
$sql="select * from ".PREF."users inner join user_type on user_type.id=".PREF."users.id 
		inner join user_skill on user_skill.user_id=user_type.id
		WHERE block=0 and user_skill='".$categorie."' AND niveau>=3 order by `nom` ";
$result = mysqli_query($link,$sql) or die("requete selection users impossible.. $sql");

$ligne=3;
while ($info=mysqli_fetch_array($result))
{
$message="";
$message=utf8_decode($info['nom']);

for ($ss=1;$ss<=$sem;$ss++)
{
$page="worksheet$ss";
$$page->set_row($ligne, '15','');
$$page->write($ligne,0,$message,$format11);
$memoire_ligne[$info[0]]['ligne']=$ligne;
$memoire_ligne[$info[0]]['sem']=$ss;
}
$ligne++;
}
mysqli_free_result($result);

// LES REMARQUES !!!
  $req="select du_user, du_rem from dispo_user where `du_month`='".$current_month."' AND `du_year`='".$current_year."' and `du_type`='dispo'
			and du_user in (select ".PREF."users.id from ".PREF."users inner join user_type on user_type.id=".PREF."users.id 
		inner join user_skill on user_skill.user_id=user_type.id
		WHERE block=0 and du_skill='".$categorie."' AND niveau>=3 order by `nom`)";
  $result = mysqli_query($link,$req) or die("requete impossible 2 . $req");
  while ($info3=mysqli_fetch_array($result))
	{
		if ($info3[1]!="") 
		{
			$$page_remarque->write($memoire_ligne[$info3[0]]['ligne'],$col_remarque,utf8_decode($info3[1]),$format11);
			for ($ss=1;$ss<=$sem;$ss++)
				{
				$page="worksheet$ss";
				$$page->write($memoire_ligne[$info3[0]]['ligne'],1,"*",$format11);
				$$page->set_column(1, 1 , 4);
				}
		}
	}

// TOUT LE RESTE !!!!
$sql="select dispo_user, day(dispo_date), dispo_type, dispo_etat from dispo where month(dispo_date)=".$current_month." and year(dispo_date)=".$current_year." and dispo_skill=".$categorie;
$result = mysqli_query($link,$sql) or die("requete impossible 1.. $sql");
while ($row=mysqli_fetch_array($result))
	{
	$format_jour=rech_format_corps($row[3],$format_col[$row[1]][$row[2]]);
	$write_line=isset($memoire_ligne[$row[0]]['ligne']) ? $memoire_ligne[$row[0]]['ligne']:-1;
	$write_col=isset($memoire_col[$row[1]][$row[2]]['col'])? $memoire_col[$row[1]][$row[2]]['col'] : -1;
	$write_sem=isset($memoire_col[$row[1]][$row[2]]['sem'])? $memoire_col[$row[1]][$row[2]]['sem']: -1;
	$page="worksheet$write_sem";
	if ($write_col>0 && $write_line>0)
		{
		$$page->write($write_line,$write_col,$row[3],$$format_jour);
		if (!isset($array_compte[$write_sem][$write_col])) $array_compte[$write_sem][$write_col]=0;
		$array_compte[$write_sem][$write_col]=($row[3]=='++' OR $row[3]=='+') ? $array_compte[$write_sem][$write_col]+1:$array_compte[$write_sem][$write_col];
		}
	}

// ON ECRIT LES TOTAUX VIA array_compte

foreach ($array_compte as $key => $value) // SEMAINES
	{
		$page="worksheet$key";
			foreach($value as $key2 => $value2)
			{
				$format_jour=rech_format('totaux',$value2);
				$$page->write($ligne,$key2,$value2,$$format_jour);
			}
	}

$workbook->close();
  
 function rech_format($type,$value)
 {
	 if ($type=='entete')
		{
			$reponse=($value==0 OR $value>=6)? 'format4' :'format11';
		}

elseif ($type=='totaux')
		{
			if ($value==5 OR $value==6) $reponse='format3';
			elseif ($value>=7) $reponse='format5' ;
			else $reponse= 'format2';
		}			
 return $reponse;
 }

function rech_format_col($number,$passage)
 {
 if ($number==1) $reponse='C';
 elseif($number>1 && $passage==1) $reponse='G';
 elseif($number>1 && $passage==$number) $reponse='D';
 else $reponse='M';
 return $reponse;
 }

function rech_format_corps($value,$type) 
{
$couleur=($value=='++' OR $value=='+')? 'V':'';

if ($type=='C' && $couleur=='V') $reponse='format1';
elseif ($type=='C' && $couleur=='') $reponse='format11';
else $reponse='format'.$type."_".$couleur;
	
return $reponse;
}
?>