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

//delegati al recupero
$result = $mysqli->query("SELECT anagr_id, CONCAT_WS(' ',deleg_cognome,deleg_nome) AS anagrafica FROM servizi_delegati WHERE serv_cod = 'bus'");
while($row=$result->fetch_assoc()){
	$delegati[$row['anagr_id']][] = entities_xls(welltxt($row['anagrafica']));
}

//bus di appartenenza (o dirottato)
if(empty($_GET['grp']))
	$grp_id = "IS NOT NULL";
else if(intval($_GET['grp']) > 0)
	$grp_id = "=".intval($_GET['grp'])." AND servbus_dirotta = 0";
else
	$grp_id = "IS NOT NULL AND servbus_dirotta = 1";


//estraggo alunni iscritti al servizio bus
$result = $mysqli->query("
SELECT 
CONCAT_WS(' ',anagrafiche.anagr_cognome,anagrafiche.anagr_nome) AS anagrafica,
CONCAT_WS(' ',serv_bus.servbus_ferm_and_ind,serv_bus.servbus_ferm_and_n) AS fermata_and,
CONCAT_WS(' ',serv_bus.servbus_ferm_rit_ind,serv_bus.servbus_ferm_rit_n) AS fermata_rit,
serv_bus.servbus_congedo,
serv_bus.servbus_dirotta,
serv_bus.servbus_rata_imp,
serv_bus.servbus_saldo_imp,
users.user_cell,
grp.grp_nome AS gruppo,
anagrafiche.anagr_id

FROM serv_bus

LEFT JOIN anagrafiche ON serv_bus.anagr_id = anagrafiche.anagr_id
LEFT JOIN users ON anagrafiche.user_id = users.user_id
LEFT JOIN gruppi grp ON serv_bus.scuola_id = grp.grp_id 

WHERE 

anagrafiche.ente_id = ".$_SESSION['user']['ente_id']." AND grp.grp_id ".$grp_id." AND anagrafiche.anno = ".$_SESSION['user']['ente_anno']."

ORDER BY serv_bus.servbus_dirotta, grp.grp_nome, anagrafiche.anagr_cognome, anagrafiche.anagr_nome
");

if($result->num_rows > 0){
	while($row=$result->fetch_assoc()){
		
		$grp = $row['servbus_dirotta'] == 0 ? $row['gruppo'] : 'Altro BUS';
		
		$deleg = $delegati[$row['anagr_id']] ? implode(chr(10),$delegati[$row['anagr_id']]) : '';
		
		
		$anagrafiche[$grp][$row['anagr_id']] = array(
			entities_xls(welltxt($row['anagrafica'])),
			$row['servbus_rata_imp'],
			$row['servbus_saldo_imp'],			
			entities_xls(welltxt($row['fermata_and'])),
			entities_xls(welltxt($row['fermata_rit'])),
			$deleg,
			($row['servbus_congedo'] == 1 ? 'SI' : ''),
			$row['user_cell']
		);
	}

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


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

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

$n_sheet = 0;
foreach($anagrafiche as $grp_name => $v){
	
	$objPHPExcel->setActiveSheetIndex($n_sheet);
	$sheet = $objPHPExcel->getActiveSheet();
	$sheet->setTitle(substr($grp_name,0,31));
	
	
	//header	
	$sheet->fromArray(array('Utente','Acconto','Saldo','Fermata ANDATA','Fermata RITORNO','Accompagnatori','Discesa'.chr(10).'autonoma','Telefono','Note'));
	//body
	$sheet->fromArray($v,'NULL','A2');
	
	//style
	$sheet->getStyle('B1:C1000')->getNumberFormat()->setFormatCode('#,##0.00');
	$sheet->getStyle('F2:F100')->getAlignment()->setWrapText(true);
	$sheet->getStyle('G1')->getAlignment()->setWrapText(true);

	
	$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->applyFromArray($header_style);

	$objPHPExcel->getActiveSheet()->getStyle('A1:X100')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	
	//cols width
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);

	
	
	foreach($sheet->getRowDimensions() as $rd) { 
		$rd->setRowHeight(-1); 
	}	
	
	$objPHPExcel->createSheet();
	$n_sheet++;
}

header('Content-Type: application/vnd.ms-excel; charset=utf-16');
header('Content-Disposition: attachment;filename="bus_elenco.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');

?>