<?php
	require_once("../../includes/bootstrap.php");
	$sesion=new sesion();
	$academia=bootstrap::initBd($sesion->get('anio'));
	/** Error reporting */
//	error_reporting(E_ALL);
//	ini_set('display_errors', TRUE);
//	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/London');
	
	if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');
	
	/** Include PHPExcel */
	require_once("../../includes/phpExcel/PHPExcel.php");
	$nombre_grupo=$sesion->getVar('configuracion.nombres_grupos');
	
	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();
	
	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Academia Web")
								 ->setLastModifiedBy("Maarten Balliauw")
								 ->setTitle("Office 2007 XLSX Test Document")
								 ->setSubject("Office 2007 XLSX Test Document")
								 ->setDescription("Carga Academica Por Cada Docente")
								 ->setKeywords("Academia 2013")
								 ->setCategory("Reportes");
	// Add some data

	$i=0;
	$cmps="";
	$idSede=$_GET['id'];
	$wh = (empty($idSede) || $idSede=="0x") ? "" : " and view_datos_grupos.cod_sede=".$idSede;
	$sql="SELECT DISTINCT
			aca_asignaturasxgrupos.cod_asigxgrupo,
			pro_profesores.cod_profesor,
			pro_profesores.ide_profesor,
			LCASE(aca_asignaturas.nom_asi) AS nom_asi,
			view_datos_grupos.nom_jornada,
			LCASE(nom_sede) AS nom_sede,
			CONCAT_WS(' ',view_datos_grupos.nom_grado,view_datos_grupos.`nom_grupo_let`) AS nom_grup,
			aca_asignaturasxgrupos.ihs,
			LCASE(CONCAT_WS(' ',pro_profesores.ap1_pro,pro_profesores.ap2_pro,pro_profesores.no1_pro,pro_profesores.no2_pro)) AS nom_ape_pro
		FROM
			aca_asignaturasxgrupos
			INNER JOIN pro_profesores 
				ON (aca_asignaturasxgrupos.cod_profesor = pro_profesores.cod_profesor)
			INNER JOIN view_datos_grupos 
				ON (aca_asignaturasxgrupos.cod_grupo = view_datos_grupos.cod_grupo)
			INNER JOIN aca_asignaturas 
				ON (aca_asignaturasxgrupos.cod_asi = aca_asignaturas.cod_asi) where 1=1 $wh ORDER BY ide_profesor,orden,$nombre_grupo asc";
	$academia->instruccion($sql);
	
	$hojaActiva=$objPHPExcel->getActiveSheet();
	$dtsColegio=$sesion->get('info_colegio');
	$hojaActiva->setCellValue('A1', $dtsColegio['nombre']);
	$hojaActiva->setCellValue('A2', "NIT. ".  $dtsColegio['nit']);
	$hojaActiva->setCellValue('A3', "NIT. ".  empty($dtsColegio['resolucion']) ? $dtsColegio['decreto'] : $dtsColegio['resolucion'] );
	
	$hojaActiva->mergeCells('A1:G1');
	$hojaActiva->mergeCells('A2:G2');
	$hojaActiva->mergeCells('A3:G3');
	$hojaActiva->getStyle('A1:G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$hojaActiva->getStyle('A1:G3')->getFont()->setBold(true);
	$fil=6;
	$auxFil=$fil;
	
	
	$objDrawing = new PHPExcel_Worksheet_Drawing();
	$objDrawing->setName('Logo');
	$objDrawing->setDescription('Logo');
	$objDrawing->setPath('../../images/logo_colegio.png');
	$objDrawing->setHeight(50);
	$objDrawing->setWidth(36);
	$objDrawing->setWorksheet($hojaActiva);
	unset($objDrawing);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$fil.":G".$fil)->getFont()->setBold(true);
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$fil, 'N° Identificación');
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$fil, 'Nombres y Apellidos');
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$fil, 'Sede');
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$fil, 'Curso Grupo');
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$fil, 'Jornada');
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$fil, 'Asignatura');
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$fil, 'I.H.S');
	
	$styleArray = array(
					'borders' => array(
						'outline' => array(
							'style' => PHPExcel_Style_Border::BORDER_THICK,
							'color' => array('argb' => '0000'),
						),
					),
				);	
	$fil++;
	$cntAnt=$fil;
	$reg=$academia->extraer_registro();
	$aux=$reg['ide_profesor'];
	$sede=$reg['nom_sede'];
	do{
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$fil, $reg['ide_profesor']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$fil, ucwords($reg['nom_ape_pro']));
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$fil, ucwords($reg['nom_sede']));
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$fil, $reg['nom_grup']);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$fil, $reg['nom_jornada']);
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$fil, ucfirst($reg['nom_asi']));
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$fil, $reg['ihs']);		
		
		if($aux!=$reg['ide_profesor']){
			$objPHPExcel->getActiveSheet()->mergeCells('A'.$cntAnt.':A'.($fil-1));
			$objPHPExcel->getActiveSheet()->mergeCells('B'.$cntAnt.':B'.($fil-1));
			$objPHPExcel->getActiveSheet()->getStyle('A'.$cntAnt.':G'.($fil-1))->applyFromArray($styleArray);
			$aux=$reg['ide_profesor'];
			$cntAnt=$fil;
		}
		$fil++;
	}while($reg=$academia->extraer_registro());
	
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$cntAnt.':A'.($fil-1));
	$objPHPExcel->getActiveSheet()->mergeCells('B'.$cntAnt.':B'.($fil-1));
	$objPHPExcel->getActiveSheet()->getStyle('A'.$cntAnt.':G'.($fil-1))->applyFromArray($styleArray);
	
	$objPHPExcel->getActiveSheet()->getStyle('A'.$auxFil.":B".$fil)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
//	$objPHPExcel->getActiveSheet()->getStyle('c5')->getAlignment()->setShrinkToFit(true);
	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Carga Académica');

	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);
	
	$nomdoc=(empty($idSede) || $idSede=="0x") ? "Carga Academica Total.xls" : "Carga Academica - ".$sede.".xls";
	// Redirect output to a client’s web browser (Excel5)
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="'.$nomdoc.'"');
	header('Cache-Control: max-age=0');
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	unset($objPHPExcel);
	unset($objWriter);
	exit;
?>