<?php
	
 /*Database Connection*/ 

 include 'dbconnection.php';

$Email = $_GET["Email"]; 
$DB = mysqli_query($mysql,"SELECT dbname,Condominio,repmant FROM tb_users WHERE email ='$Email'");
while($row = mysqli_fetch_array($DB)){

$dbname = $row ['dbname'];
$Condominio = $row ['Condominio'];
$Repmant = $row ['repmant'];
}


$hostname2 = "localhost";
$username2 = "tempdpe5_nicolas";
$password2= "(Made7575)";
$conn = mysqli_connect($hostname2,$username2,$password2,$dbname);
if (mysqli_connect_errno()){
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
} 
mysqli_set_charset($conn ,"utf8"); 

$mask ='Trabajos-Pendientes-por-estatus-'.$Condominio.'*.*';
array_map('unlink', glob('/home/tempdpe5/public_html/intelpropertyrd/'.$Repmant.$mask));


//mysqli_query($con,"UPDATE ordendetrabajo SET Fecha2 = Fecha");

//mysqli_query($con,"UPDATE ordendetrabajo SET Mes = Fecha");

//mysqli_query($con,"UPDATE ordendetrabajo SET Ano = Fecha");

//mysqli_query($con,"UPDATE ordendetrabajo SET Mes = DATE_FORMAT(Mes, '%M')");

//mysqli_query($con,"UPDATE ordendetrabajo SET Ano = DATE_FORMAT(Ano, '%Y')");

mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Enero' WHERE Mes = 'January'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Febrero' WHERE Mes = 'February'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Marzo' WHERE Mes = 'March'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Abril' WHERE Mes = 'April'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Mayo' WHERE Mes = 'May'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Junio' WHERE Mes = 'June'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Julio' WHERE Mes = 'July'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Agosto' WHERE Mes = 'August'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Septiembre' WHERE Mes = 'September'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Octubre' WHERE Mes = 'October'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Noviembre' WHERE Mes = 'November'");
mysqli_query($conn,"UPDATE ordendetrabajo SET Mes = 'Diciembre' WHERE Mes = 'December'");

mysqli_query($conn,"UPDATE ordendetrabajo SET mesano = CONCAT(Mes,' -- ',Ano)");

	/*include PhpSpreadsheet library*/
	require 'PhpSpreadsheet/vendor/autoload.php';

	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
	use PhpOffice\PhpSpreadsheet\Writer\Xls;	
	use PhpOffice\PhpSpreadsheet\Style\Alignment;
	use PhpOffice\PhpSpreadsheet\Style\Fill;
	use PhpOffice\PhpSpreadsheet\Style\Color;
	use PhpOffice\PhpSpreadsheet\Style\Border;
	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
	use PhpOffice\PhpSpreadsheet\Style\Protection;
	use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
	use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
	use PhpOffice\PhpSpreadsheet\Worksheet\Row;
	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
	use PhpOffice\PhpSpreadsheet\Cell\Cell;
	use PhpOffice\PhpSpreadsheet\Cell\DataValidation;	
	use PhpOffice\PhpSpreadsheet\IOFactory;	
	
	$spreadsheet = new Spreadsheet();
	
	/*Page Setup
	Page Orientation(ORIENTATION_LANDSCAPE/ORIENTATION_PORTRAIT), 
	Paper Size(PAPERSIZE_A3,PAPERSIZE_A4,PAPERSIZE_A5,PAPERSIZE_LETTER,PAPERSIZE_LEGAL etc)*/
	$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
	$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);

	/*Set Page Margins for a Worksheet*/
	$spreadsheet->getActiveSheet()->getPageMargins()->setTop(0.75);
	$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.25);
	$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.25);
	$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(0.75);

	/*Center a page horizontally/vertically*/
	$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
	$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

	/*Show/hide gridlines(true/false)*/
	$spreadsheet->getActiveSheet()->setShowGridlines(true);
	
	//Activate work sheet
    $spreadsheet->createSheet(0);
    $spreadsheet->setActiveSheetIndex(0);
	$spreadsheet->getActiveSheet(0);
	//work sheet name
    $spreadsheet->getActiveSheet()->setTitle('Reporte de Trabajos Pendientes');	
	/*Default Font Set*/
	$spreadsheet->getDefaultStyle()->getFont()->setName('Calibri');
	/*Default Font Size Set*/
	$spreadsheet->getDefaultStyle()->getFont()->setSize(11); 
	
	/*Border color*/
    $styleThinBlackBorderOutline = array('borders' => array('outline'=> array('borderStyle' => Border::BORDER_THIN, 'color' => array('argb' => '5a5a5a'))));
	$spreadsheet->getActiveSheet()->SetCellValue('A1', 'Reporte de Trabajos Pendientes Por Mes');
	$spreadsheet->getActiveSheet()->SetCellValue('A3', 'Condominio : '.$Condominio);
	$spreadsheet->getActiveSheet()->getStyle('A1')->getFont();
	$spreadsheet->getActiveSheet()->getStyle('A3')->getFont();

	
	/*Font Size for Cells*/
	$spreadsheet -> getActiveSheet()->getStyle('A1') -> applyFromArray(array('font' => array('size' => '25', 'bold' => true)), 'A1');
	$spreadsheet -> getActiveSheet()->getStyle('A3') -> applyFromArray(array('font' => array('size' => '20', 'bold' => true)), 'A3');
	
	/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
	$spreadsheet -> getActiveSheet()->getStyle('A1') -> getAlignment()->setHorizontal(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('A3') -> getAlignment()->setHorizontal(Alignment::VERTICAL_CENTER);
    $spreadsheet -> getActiveSheet()->getStyle('A4') -> getAlignment()->setHorizontal(Alignment::VERTICAL_CENTER);

	/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
	$spreadsheet -> getActiveSheet() -> getStyle('A1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('A3')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('A4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

	/*merge Cell*/
	$spreadsheet -> getActiveSheet() -> mergeCells('A1:F2');
	$spreadsheet -> getActiveSheet() -> mergeCells('A3:F3');
	

	/*Fill Color Change function for Cells*/
	cellColor('A1:F4', 'd9e1ec');
	cellColor('A5:F5', '9ab1d1');
	
	/*Value Set for Cells*/
    $spreadsheet -> getActiveSheet()				
				->SetCellValue('A5', 'Titulo')							
				->SetCellValue('B5', 'Fecha')
				->SetCellValue('C5', 'Descripcion')							
				->SetCellValue('D5', 'Tipo de Orden')							
				->SetCellValue('E5', 'Comentarios')
				->SetCellValue('F5', 'Tipo');	

	/*Font Size for Cells*/
	$spreadsheet -> getActiveSheet()->getStyle('A5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'A6');	
	$spreadsheet -> getActiveSheet()->getStyle('B5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'B6');
	$spreadsheet -> getActiveSheet()->getStyle('C5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'C6');
	$spreadsheet -> getActiveSheet()->getStyle('D5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'D6');
	$spreadsheet -> getActiveSheet()->getStyle('E5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'E6');
	$spreadsheet -> getActiveSheet()->getStyle('F5') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'F6');

	/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
	$spreadsheet -> getActiveSheet()->getStyle('A5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('B5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('C5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('D5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('E5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('F5') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	
	/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
	$spreadsheet -> getActiveSheet() -> getStyle('A5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('B5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('C5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('D5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('E5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('F5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	
	/*Width for Cells*/
	$spreadsheet -> getActiveSheet() -> getColumnDimension('A') -> setWidth(20);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('B') -> setWidth(15);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('C') -> setWidth(28);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('D') -> setWidth(18);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('E') -> setWidth(28);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('F') -> setWidth(18);
	
	/*Wrap text*/
	$spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
	$spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
	
	/*border color set for cells*/
	$spreadsheet -> getActiveSheet() -> getStyle('A5:A5') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('B5:B5') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('C5:C5') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('D5:D5') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('E5:E5') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('F5:F5') -> applyFromArray($styleThinBlackBorderOutline);


	/*sql*/

    $sql1 = mysqli_query($conn,"SELECT * FROM ordendetrabajo WHERE Status != 'Completada' GROUP BY Status");
    $cuenta = mysqli_num_rows($sql1);
    while($row2 = mysqli_fetch_array($sql1) ){
      
      $Status[] = $row2['Status'];
      }
    
    $j=6;
	$tempDate ='';

    for($i=0;$i<$cuenta;$i++){

	$sql = "SELECT Titulo,Fecha,Tipodeorden,Descripcion,Status,Mes,Ano,Fecha2,mesano,Comentarios FROM ordendetrabajo WHERE Condominio = 
	'$Condominio' AND Status = '".$Status[$i]."';";
	$result = mysqli_query($conn, $sql);

	//$i=1; 
	
	while ($aRow = mysqli_fetch_array($result)) {

		/*Group*/			
		if ($tempDate != $aRow['Status']) {
			$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':F' . $j) -> applyFromArray($styleThinBlackBorderOutline);
			$spreadsheet->getActiveSheet()->getStyle('A' . $j . ':F' . $j)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('bfbfbf');
            $spreadsheet->getActiveSheet()->getStyle('A' . $j . ':F' . $j)->getFont()->setSize(15)->setBold(true); 
			$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':F' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
			$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':F' . $j)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
			$spreadsheet -> getActiveSheet() -> mergeCells('A' . $j . ':F' . $j);
			$spreadsheet -> getActiveSheet() -> SetCellValue('A' . $j++, $aRow['Status']);
			$tempDate=$aRow['Status'];
		}	
	
		/*Value Set for Cells*/
		$spreadsheet->getActiveSheet()
					->SetCellValue('A'.$j, $aRow['Titulo'])							
					->SetCellValue('B'.$j, $aRow['Fecha2'])	
					->SetCellValue('C'.$j, $aRow['Descripcion'])																
					->SetCellValue('D'.$j, $aRow['Tipodeorden'])																
					->SetCellValue('E'.$j, $aRow['Comentarios'])
					->SetCellValue('F'.$j, $aRow['Tipo']);

        				
		/*border color set for cells*/	
		$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':A' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('B' . $j . ':B' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('C' . $j . ':C' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('D' . $j . ':D' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('E' . $j . ':E' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('F' . $j . ':F' . $j) -> applyFromArray($styleThinBlackBorderOutline);
	
		/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
		$spreadsheet -> getActiveSheet()->getStyle('A' . $j . ':A' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('B' . $j . ':B' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('C' . $j . ':C' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('D' . $j . ':D' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('E' . $j . ':E' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('F' . $j . ':F' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		
		/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
		$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':A' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('B' . $j . ':B' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('C' . $j . ':C' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('D' . $j . ':D' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('E' . $j . ':E' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('F' . $j . ':F' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

		/*Number format Cell D*/
		$spreadsheet->getActiveSheet()->getStyle('D'.$j)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
		$spreadsheet->getActiveSheet()->getStyle('D'.$j)->getNumberFormat()->setFormatCode('#,##0.00'); 
		
		/*Number format Cell E*/
		$spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
		$spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode('#,##0'); 

		if ($j % 2 == 0) {
			cellColor('A'.$j.':F'.$j, 'f4f8fb');
		}
		
		//$i++; 
		$j++;
	    }
	}
    

	if (function_exists('date_default_timezone_set')) {
			date_default_timezone_set('UTC');
	} else {
			putenv("AU=UTC");
	}
	
	$exportTime = date("Y-m-d-His", time());
	/* $writer = new Xls($spreadsheet);
	$file = 'row-grouping-'.$exportTime. '.xls'; //Save file name */
	
	$writer = new Xlsx($spreadsheet);
	$file = 'Trabajos-Pendientes-por-estatus-'.$Condominio.'-'.$exportTime.'.xlsx'; //Save file name
	$writer->save('/home/tempdpe5/public_html/intelpropertyrd/'.$Repmant.'Trabajos-Pendientes-por-estatus-'.$Condominio.'-'.$exportTime.'.xlsx');

	//mysqli_query($con,"ALTER IGNORE TABLE reportes  ADD UNIQUE INDEX (Reporte)");

	//mysqli_query($con,"INSERT IGNORE INTO reportes (Condominio,Descripcion,Tipo,Reporte,Actualizar) VALUES('".$Condominio."','Reporte de Trabajos Pendientes por Estatus','MANT','/mant/".$file."','trabajopendienteporestatusexcelactualizar.php?Condominio=".$Condominio."')");

	//mysqli_query($con,"ALTER TABLE reportes DROP INDEX Reporte");

	mysqli_query($conn,"UPDATE reportes SET Reporte = CONCAT('public/../reportes/mant/','$file') WHERE Condominio = '$Condominio' AND Descripcion = 'Reporte de Trabajos Pendientes por Estatus'"); 
	
	mysqli_query($conn,"UPDATE reportes SET file ='$file' WHERE Condominio = '$Condominio' AND Descripcion = 'Reporte de Trabajos Pendientes por Estatus'");

	//mysqli_query($con,"UPDATE reportes SET Actualizar = CONCAT('trabajopendienteporestatusexcelactualizar.php?Condominio=','".$Condominio."','&File=','".$file."') WHERE Condominio = '$Condominio' AND Descripcion = 'Reporte de Trabajos Pendientes por Estatus'");

	
	//header('Location:reportes/adm/'.$Condominio.'/'. $file); //File open location
    //header("Location:reportemantusuario");
	//header('Location:reportes/adm/'.$Condominio.'/'. $file); //File open location

	function cellColor($cells, $color) {
		global $spreadsheet;
		$spreadsheet->getActiveSheet()->getStyle($cells)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($color);
	}
	echo "<script>window.location.href='reportemantusuario';</script>";
?>