<?php
  session_start();

  if (isset($_SESSION['tipousuario'])){
    if ($_SESSION['tipousuario']=='1'){
      header ('location:menu_personal.php');
    }
  }

  if (trim($_SESSION['nombre_validar'])==''){
    header ('location:index.php');
  }
  
  include ("fphp.php");
  include ("calendario/calendario.php");
  require_once 'PHPExcel/PHPExcel.php';
  $enlace=cargar_conexion();
  
  $nomina=$_POST["nomina"];
  $periodoDesde=$_POST["periodoDesde"];
  $periodoHasta=$_POST["periodoHasta"];
  $idValidar=$_SESSION['id_validar'];

  $consulta = "SELECT SUM(h.cantidad) * c.reporte_horas AS cantidad, c.detalle AS decripcion, COUNT( distinct h.cedula) AS casos
    FROM historico AS h, concepto AS c WHERE h.concepto = c.codigo AND h.id = c.id AND h.id = '$idValidar' AND
    h.nomina = '$nomina' AND h.periodo >='$periodoDesde'AND h.periodo <='$periodoHasta' AND c.reporte_horas >0 GROUP BY c.detalle";

  $resultado = mysqli_query($enlace,$consulta) or die ("no se pudo realizar la consulta...");
  $n_filas = mysqli_num_rows($resultado);
  $row=[];

  $consultaDesNomina = "SELECT h.desnomina AS desnomina FROM historico AS h, concepto AS c WHERE h.concepto = c.codigo AND 
    h.id = c.id AND h.id = '$idValidar' AND h.nomina = '$nomina' AND h.periodo >='$periodoDesde'AND h.periodo <='$periodoHasta'
    AND c.reporte_horas >0 GROUP BY c.detalle LIMIT 1";
  $resultadoDesNomina = mysqli_query($enlace,$consultaDesNomina) or die ("no se pudo realizar la consulta...");
  $rowDesNomina=mysqli_fetch_array($resultadoDesNomina);
  $desNomina = $rowDesNomina["desnomina"];
  
  $consultaResumen = "SELECT count(p.sexo) AS total, SUM(IF(p.sexo='M',1,0)) AS masculino, SUM(IF(p.sexo='F',1,0)) AS femenino,
    SUM(IF(p.sexo='M',IF(nacionalidad='V',1,0),0)) AS masculinosV, SUM(IF(p.sexo='M',IF(nacionalidad='V',0,1),0)) AS masculinosE,
    SUM(IF(p.sexo='F',IF(nacionalidad='V',1,0),0)) AS femeninoV, SUM(IF(p.sexo='F',IF(nacionalidad='V',0,1),0)) AS femeninosE
    FROM personal p WHERE p.codigo IN (SELECT DISTINCT h.cedula FROM historico h where
    h.id = '100' AND h.nomina = '$nomina' AND h.periodo >='$periodoDesde' AND h.periodo <='$periodoHasta') AND p.id = '100'";

  $resultadoResumen = mysqli_query($enlace,$consultaResumen) or die ("no se pudo realizar la consulta...");
  $rowResumen=mysqli_fetch_array($resultadoResumen);

  $consultaNominaDesde = "SELECT * FROM periodo WHERE periodo = '$periodoDesde' AND nomina = '$nomina' AND id = '$idValidar'";
  $resultadosNominaDesde = mysqli_query($enlace,$consultaNominaDesde) or die ("Error al consultar las nominas1");
  $rowPeriodoDesde=mysqli_fetch_array($resultadosNominaDesde);
  $fechaDesde = $rowPeriodoDesde['desde'];

  $consultaNominaHasta = "SELECT * FROM periodo WHERE periodo = '$periodoHasta' AND nomina = '$nomina' AND id = '$idValidar'";
  $resultadosNominaHasta = mysqli_query($enlace,$consultaNominaHasta) or die ("Error al consultar las nominas2");
  $rowPeriodoHasta=mysqli_fetch_array($resultadosNominaHasta);
  $fechaHasta = $rowPeriodoHasta['hasta'];

  $fecha_procesado = date("d-m-Y");
  
  $objPHPExcel = new PHPExcel();

  $subject = "REPORTE DE HORAS TRABAJADAS DEL ".$fechaDesde." AL ".$fechaHasta;

  $objPHPExcel->getProperties()->setCreator("Saireh")
  ->setLastModifiedBy("Saireh")->setTitle($subject)
  ->setSubject($subject)->setDescription("Reporte")
  ->setKeywords("reporte")->setCategory("Reporte De Horas");
  
  PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

  // ----------------------------------------------------------------------

  $objPHPExcel->getActiveSheet()->setCellValue("A1","FORD MOTOR VENEZUELA S.A.");
  $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold( true );
  
  $objPHPExcel->getActiveSheet()->setCellValue("D1","FECHA DE PROCESO: ".$fecha_procesado);
  $objPHPExcel->getActiveSheet()->getStyle("D1")->getFont()->setBold( true );

  // ----------------------------------------------------------------------

  $objPHPExcel->getActiveSheet()->setCellValue("A3","ACUMULADO MINISTERIO DEL TRABAJO");

  // ----------------------------------------------------------------------

  $objPHPExcel->getActiveSheet()->setCellValue("A4","PERIODO: ".$fechaDesde." AL ".$fechaHasta);
  $objPHPExcel->getActiveSheet()->setCellValue("D4","NOMINA: ".$desNomina);

  // ----------------------------------------------------------------------

  $objPHPExcel->getActiveSheet()->setCellValue("A6","CONCEPTO");
  $objPHPExcel->getActiveSheet()->setCellValue("B6","CANTIDAD (HRS)");
  $objPHPExcel->getActiveSheet()->setCellValue("C6","CASOS");

  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFont()->setBold( true );
  
  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);

  // ----------------------------------------------------------------------

  $position = 8;
  while($rowPDesde=mysqli_fetch_array($resultado)){
    $decripcion = $rowPDesde['decripcion'];
    $cantidad = $rowPDesde['cantidad'];
    $casos = $rowPDesde['casos']; 
    
    $objPHPExcel->getActiveSheet()->setCellValue("A".$position,$decripcion);
    $objPHPExcel->getActiveSheet()->setCellValue("B".$position,$cantidad);
    $objPHPExcel->getActiveSheet()->setCellValue("C".$position,$casos);
    $objPHPExcel->getActiveSheet()->getStyle("A".$position)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $objPHPExcel->getActiveSheet()->getStyle("B".$position)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $objPHPExcel->getActiveSheet()->getStyle("C".$position)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $position++;
  }
  $position++;

  
  $mascTotal = $rowResumen['masculinosV'] + $rowResumen['masculinosE'];
    
  $objPHPExcel->getActiveSheet()->setCellValue("A".$position,"TRABAJADORES");
  $objPHPExcel->getActiveSheet()->setCellValue("B".$position,$rowResumen['total']);
  $objPHPExcel->getActiveSheet()->setCellValue("C".$position,"MASCULINO");
  $objPHPExcel->getActiveSheet()->setCellValue("D".$position,$mascTotal);
  $objPHPExcel->getActiveSheet()->setCellValue("E".$position,"VEN: ");
  $objPHPExcel->getActiveSheet()->setCellValue("F".$position,$rowResumen['masculinosV']);
  $objPHPExcel->getActiveSheet()->setCellValue("G".$position,"EXTR: ");
  $objPHPExcel->getActiveSheet()->setCellValue("H".$position,$rowResumen['masculinosE']);

  $objPHPExcel->getActiveSheet()->getStyle("A".$position)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $rango="B".$position.":H".$position;
  $objPHPExcel->getActiveSheet()->getStyle($rango)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $position++;
  
  $femTotal = $rowResumen['femeninoV'] + $rowResumen['femeninosE'];

  $objPHPExcel->getActiveSheet()->setCellValue("C".$position,"FEMENINO");
  $objPHPExcel->getActiveSheet()->setCellValue("D".$position,$rowResumen['femeninoV']);
  $objPHPExcel->getActiveSheet()->setCellValue("E".$position,"VEN: ");
  $objPHPExcel->getActiveSheet()->setCellValue("F".$position,$femTotal);
  $objPHPExcel->getActiveSheet()->setCellValue("G".$position,"EXTR: ");
  $objPHPExcel->getActiveSheet()->setCellValue("H".$position,$rowResumen['femeninosE']);

  $rango="B".$position.":H".$position;
  $objPHPExcel->getActiveSheet()->getStyle($rango)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  
  $objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
  
  $objPHPExcel->getActiveSheet()->setSelectedCells('A1');

  $objPHPExcel->getActiveSheet()->setTitle('REPORTE HORAS TRABAJADAS');
  $objPHPExcel->setActiveSheetIndex(0);

  $fileName = "reportes-horas-trabajadas".$fecha_procesado.".xlsx";

  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="'.$fileName.'"');
  header('Cache-Control: max-age=0');
  header('Cache-Control: max-age=1');
  header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
  header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
  header ('Cache-Control: cache, must-revalidate');
  header ('Pragma: public');
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save('php://output');
  exit;

?>
