<?php
checkLogin('adm');
checkEnteService('men');
spl_autoload_unregister('my_autoloader');
include(ROOT."/includes/classes/phpexcel/PHPExcel.php");


$anno = intval($_GET['yyyy']);

if($_GET['solare'] == 1){
	$solare = true;
	$w = "DATE_FORMAT(serv_men_presenze.date,'%Y') = ".$anno;
}else{
	$w = "(DATE_FORMAT(serv_men_presenze.date,'%Y%m') >= ".$anno."09 AND DATE_FORMAT(serv_men_presenze.date,'%Y%m') <= ".($anno+1)."06)";
}

//estraggo alunni iscritti al servizio bus
$result = $mysqli->query($q="
SELECT 
COUNT(*) AS t, CONCAT_WS(' ',anagrafiche.anagr_cognome,anagrafiche.anagr_nome) AS anagrafica,
serv_men_presenze.anagr_id,
grp.grp_nome AS gruppo,
comuni.comune,
DATE_FORMAT(serv_men_presenze.date,'%Y-%m') AS data,
serv_men_presenze.date


FROM serv_men_presenze
LEFT JOIN anagrafiche ON serv_men_presenze.anagr_id = anagrafiche.anagr_id
LEFT JOIN serv_men ON serv_men_presenze.anagr_id = serv_men.anagr_id
LEFT JOIN gruppi grp ON serv_men.scuola_id = grp.grp_id 
LEFT JOIN comuni ON serv_men_presenze.comune_id = comuni.comune_id

WHERE 
anagrafiche.ente_id = ".$_SESSION['user']['ente_id']." AND 
grp.grp_id ". (!empty($_GET['grp']) ? "=".intval($_GET['grp']) : "IS NOT NULL") ." AND 
(serv_men_presenze.status = 'P' OR serv_men_presenze.status='S') AND ".$w."
GROUP BY serv_men_presenze.comune_id, DATE_FORMAT(serv_men_presenze.date,'%Y%m'), serv_men_presenze.anagr_id
ORDER BY comuni.comune, data, anagrafiche.anagr_cognome, anagrafiche.anagr_nome
");

if($result->num_rows > 0){
	while($row=$result->fetch_assoc()){
		
		$scuola = $row['gruppo'];
		
		$anagrafiche[$row['comune']][$row['anagr_id']][$row['data']] = array(
			'anagr'=>entities_xls(welltxt($row['anagrafica'])),
			'tot'=>$row['t']
		);
	}

}else{
	header("Location: ../adm/?noprintresult");	
	die();
}



//larghezza e numero colonne
if($solare){ //12 mesi
	$n_months = 12;
	$start_date = new DateTime($anno.'-01-01');
}else{ //10 mesi
	$n_months = 10;
	$start_date = new DateTime($anno.'-09-01');	
}

$last_col = chr(65 + $n_months + 1);
$last_calc_col = chr(65 + $n_months);

//preparo colonne con nomi dei mesi
for($i=0;$i<$n_months;$i++){
	$cols_header[] = mese_ita($start_date->format('m'),true).' '. $start_date->format('Y');
	$months[] = $start_date->format('Y-m');
	$start_date->modify('+ 1 month');
}
array_unshift($cols_header,'Alunno');
array_push($cols_header,'TOT');




$objPHPExcel = new PHPExcel();
PHPExcel_Settings::setLocale('it');

$scuola_name_style = array(
	'font' => array(
		'bold' => true
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'startcolor' => array(
			'argb' => 'FFffbc02'
		)
	)
);

$header_style = array(
	'borders' => array(
		'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
		),
	),
	'font' => array(
		'bold' => true
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'startcolor' => array(
			'argb' => 'FFdbfa00'
		)
	),
	'alignment' => array(
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
	)
);
$body_style = array(
	'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
		)
);
$footer_style = array(
	'borders' => array(
		'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
		),
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'startcolor' => array(
			'argb' => 'FFdbfa00'
		)
	),
	'font' => array(
		'bold' => true
	),
	
);


//header
if($_GET['solare'] == 1)
	$w = "SOLARE ".$anno;
else
	$w = "SCOLASTICO ".$anno."/".($anno+1);

$header_title = 'CONTEGGIO PASTI - ANNO '.$w;

if(intval($_GET['grp']) > 0)
	$header_title = $scuola.' - '.$header_title;
	




$n_sheet = 0;
foreach($anagrafiche as $comune => $anagrafica){


	$objPHPExcel->setActiveSheetIndex($n_sheet);
	$sheet = $objPHPExcel->getActiveSheet();
	$sheet->setTitle(substr(entities_xls(welltxt($comune)),0,31));
	
	//header	
	$sheet->SetCellValue('A1', $header_title);
	
	$rows[] = $cols_header;
	
	foreach($anagrafica as $anagr_id=>$v){
				
		$anagr = current($v);
		$row[] = $anagr['anagr'];
		
		foreach($months as $k=>$mese){
			//preso
			if($v[$mese]){
				$cell = $v[$mese]['tot'];
			}else{
				$cell = '';
			}
			
			$row[] = $cell;
		}
				
		$rows[] = $row;
		$rows_riepilogo[] = $row;
		unset($row);
	}
	$rows[] = array('TOTALE');

	$nrows = count($rows)+1;
	$sheet->getStyle('A'.$nrows.':'.$last_col.$nrows)->applyFromArray($footer_style);

	
	$sheet->fromArray($rows,'NULL','A2');

	//somma colonne
	for($col = 'B'; $col !== $last_col; $col++) {
		$sheet->setCellValue($col.$nrows, "=SUM(".$col."3:".$col.($nrows-1).")");
	}
	
	//somma righe
	for($i=3;$i<$nrows;$i++)
		$sheet->setCellValue($last_col.$i, "=SUM(B".$i.":".$last_calc_col.$i.")");
		
	$sheet->setCellValue($last_col.$nrows, "=SUM(".$last_col."3:".$last_col.($nrows-1).")");
	
	
	unset($rows);
	$objPHPExcel->createSheet();
	$n_sheet++;

}



$result = $mysqli->query("SELECT date,DATE_FORMAT(date,'%Y-%m') AS mese FROM serv_men_presenze GROUP BY date ORDER BY date");
while($row2 = $result->fetch_assoc())
	$giorni_mensa[$row2['mese']]++;



################### PAGINA MAESTRE ###################
$objPHPExcel->setActiveSheetIndex($n_sheet);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle("Insegnanti");
$cols_header[0] = '';
$rows[] = $cols_header;

$n_insegnanti = intval($_GET['insegnanti']);

//header	
$sheet->SetCellValue('A1', $header_title);

$row[] = 'Insegnanti';
foreach($months as $k=>$mese){
	if($giorni_mensa[$mese]){
		$npasti = $giorni_mensa[$mese] * $n_insegnanti;
	}else{
		$npasti = '';
	}
	$row[] = $npasti;
}


$rows[] = $row;
$rows[] = array('TOTALE');

$sheet->fromArray($rows,'NULL','A2');

$nrows = count($rows)+1;
$sheet->getStyle('A'.$nrows.':'.$last_col.$nrows)->applyFromArray($footer_style);

//somma colonne
for($col = 'B'; $col !== $last_col; $col++) {
	$sheet->setCellValue($col.$nrows, "=SUM(".$col."3:".$col.($nrows-1).")");

}
//somma righe
for($i=3;$i<$nrows;$i++)
	$sheet->setCellValue($last_col.$i, "=SUM(B".$i.":".$last_calc_col.$i.")");

$sheet->setCellValue($last_col.$nrows, "=SUM(".$last_col."3:".$last_col.($nrows-1).")");


$objPHPExcel->createSheet();















################### RIEPILOGO TOTALE ###################
$n_sheet++;
$objPHPExcel->setActiveSheetIndex($n_sheet);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle("Riepilogo");
$cols_header[0] = 'Alunno';
array_unshift($rows_riepilogo, $cols_header);

//header	
$sheet->SetCellValue('A1', $header_title);

$rows_riepilogo[] = array('TOTALE');

$sheet->fromArray($rows_riepilogo,'NULL','A2');

$nrows = count($rows_riepilogo)+1;
$sheet->getStyle('A'.$nrows.':'.$last_col.$nrows)->applyFromArray($footer_style);

//somma colonne
for($col = 'B'; $col !== $last_col; $col++) {
	$sheet->setCellValue($col.$nrows, "=SUM(".$col."3:".$col.($nrows-1).")");

}
//somma righe
for($i=3;$i<$nrows;$i++)
	$sheet->setCellValue($last_col.$i, "=SUM(B".$i.":".$last_calc_col.$i.")");

$sheet->setCellValue($last_col.$nrows, "=SUM(".$last_col."3:".$last_col.($nrows-1).")");


$objPHPExcel->createSheet();

















foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
	//applico stile
	$sheet->getStyle('A1:'.$last_col.'1')->applyFromArray($scuola_name_style);
	$sheet->getStyle('A2:'.$last_col.'2')->applyFromArray($header_style);
	$sheet->getStyle('B2:'.$last_col.'100')->applyFromArray($body_style);
	$sheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
	
	$sheet->getColumnDimension('A')->setWidth(30);


	for($col = 'B'; $col !== $last_col; $col++) {
		$sheet->getColumnDimension($col)->setWidth(10);			
	}
	$sheet->setSelectedCell('A2');
}






header('Content-Type: application/vnd.ms-excel; charset=utf-16');
header('Content-Disposition: attachment;filename="report_pasti.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

?>