<?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();

  
  $cedula=$_REQUEST["cedula"];
  $periodo=$_REQUEST["periodo"];
  $fdesde=$_REQUEST["fdesde"];

  $consulta = "SELECT pe.cargo, pe.departamento, pe.nomina, pe.unidad, vu.* FROM personal pe INNER JOIN vacacion_usd vu 
  ON pe.codigo = vu.cedula WHERE vu.cedula = '$cedula' AND vu.periodo = '$periodo' AND vu.fdesde = '$fdesde'";
  $resultado = mysqli_query($enlace,$consulta) or die ("no se pudo realizar la consulta...");
  $n_filas = mysqli_num_rows($resultado);
  $row=[];
  $diario='';
  
  if ($n_filas > 0){
    $row=mysqli_fetch_array($resultado);
    $diario=round($row["monto"]/30, 2);
  } else {
    die ("Sin resultados...");
  }

  $objPHPExcel = new PHPExcel();

  $subject = "VACACIONES DE ".$row["nombres"]. " ".$row["apellidos"]." GENERADAS EL".$row["fecha_elab"];

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

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

  $objPHPExcel->getActiveSheet()->setCellValue("A1","MERSAN CA");
  $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold( true );
  
  $objPHPExcel->getActiveSheet()->setCellValue("C2","VACACIONES DE NOMINA MENSUAL");
  $objPHPExcel->getActiveSheet()->getStyle("C2")->getFont()->setBold( true );

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

  $objPHPExcel->getActiveSheet()->setCellValue("A3","Periodo: ".$periodo);
  $objPHPExcel->getActiveSheet()->setCellValue("A4","Antiguedad: ".$row["antiguedad"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A5","Fecha desde: ".$row["fdesde"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A6","Fecha Hasta: ".$row["fhasta"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A7","Fecha de Reincorporacion: ".$row["reintegro"]);

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

  $objPHPExcel->getActiveSheet()->setCellValue("A9","DATOS DEL TRABAJADOR:");
  $objPHPExcel->getActiveSheet()->getStyle("A9")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("A10","Nombre:");
  $objPHPExcel->getActiveSheet()->setCellValue("B10", $row["nombres"]. " ".$row["apellidos"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A11","Cedula:");
  $objPHPExcel->getActiveSheet()->setCellValue("B11",$row["cedula"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A12","Fecha de Ingreso:");
  $objPHPExcel->getActiveSheet()->setCellValue("B12",$row["fecha_ingreso"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A13","Departamento:");
  $objPHPExcel->getActiveSheet()->setCellValue("B13",$row["departamento"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A14","Unidad:");
  $objPHPExcel->getActiveSheet()->setCellValue("B14",$row["unidad"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A15","Cargo:");
  $objPHPExcel->getActiveSheet()->setCellValue("B15",$row["cargo"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A16","Fecha de procesado:");
  $objPHPExcel->getActiveSheet()->setCellValue("B16",$row["fecha_elab"]);
  $objPHPExcel->getActiveSheet()->setCellValue("A17","Nomina:");
  $objPHPExcel->getActiveSheet()->setCellValue("B17",$row["nomina"]);

  $objPHPExcel->getActiveSheet()->getStyle('A9:A17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('B9:B17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

  // ----------------------------------------------------------------------
  
  $objPHPExcel->getActiveSheet()->setCellValue("D9","DATOS DEL PAQUETE:");
  $objPHPExcel->getActiveSheet()->getStyle("D9")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("D10","Paquete mens (USD):");
  $objPHPExcel->getActiveSheet()->setCellValue("E10","$".$row["monto"]);
  $objPHPExcel->getActiveSheet()->setCellValue("D11","Salario diario en (USD):");
  $objPHPExcel->getActiveSheet()->setCellValue("E11","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D12","Tasa Bs/$ para cálculos:");
  $objPHPExcel->getActiveSheet()->setCellValue("E12","Bs".$row["tasa"]);
  
  $objPHPExcel->getActiveSheet()->getStyle('D9:D17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('E9:E17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

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

  $objPHPExcel->getActiveSheet()->setCellValue("B19","CALCULO DE VACACIONES EN USD ($)");
  $objPHPExcel->getActiveSheet()->getStyle("B19")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("B20","Dias");
  $objPHPExcel->getActiveSheet()->setCellValue("C20","Salario Diario");

  $objPHPExcel->getActiveSheet()->setCellValue("A21","VACACIONES");
  $objPHPExcel->getActiveSheet()->setCellValue("B21",$row["dvac"]);
  $objPHPExcel->getActiveSheet()->setCellValue("C21","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D21","$".$row["valor_dvac"]);
  $objPHPExcel->getActiveSheet()->getStyle('A21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('B21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('C21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('D21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("B21")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("A22","DIAS ADIC");
  $objPHPExcel->getActiveSheet()->setCellValue("B22",$row["dias_ext"]);
  $objPHPExcel->getActiveSheet()->setCellValue("C22","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D22","$".$row["valor_dias_ext"]);
  $objPHPExcel->getActiveSheet()->getStyle('A22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('B22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('C22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('D22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("B22")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("A23","DIAS BON VACAC");
  $objPHPExcel->getActiveSheet()->setCellValue("B23",$row["dbon"]);
  $objPHPExcel->getActiveSheet()->setCellValue("C23","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D23","$".$row["valor_dbon"]);
  $objPHPExcel->getActiveSheet()->getStyle('A23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('B23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('C23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('D23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("B23")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("A24","DIAS DESCANSO");
  $objPHPExcel->getActiveSheet()->setCellValue("B24",$row["otros"]);
  $objPHPExcel->getActiveSheet()->setCellValue("C24","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D24","$".$row["valor_otros"]);
  $objPHPExcel->getActiveSheet()->getStyle('A24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('B24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('C24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('D24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("B24")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->setCellValue("A25","BONO POST VACIONAL");
  $objPHPExcel->getActiveSheet()->setCellValue("B25",$row["dias_post"]);
  $objPHPExcel->getActiveSheet()->setCellValue("C25","$".$diario);
  $objPHPExcel->getActiveSheet()->setCellValue("D25","$".$row["valor_dias_post"]);
  $objPHPExcel->getActiveSheet()->getStyle('A25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('B25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('C25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('D25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("B25")->getFont()->setBold( true );

  $total = $row["valor_dvac"] + $row["valor_dias_ext"] + $row["valor_dbon"] + $row["valor_otros"] + $row["valor_dias_post"];
  $objPHPExcel->getActiveSheet()->setCellValue("C26","Total vacaciones calculadas en $");
  $objPHPExcel->getActiveSheet()->setCellValue("D26","$".$total);
  $objPHPExcel->getActiveSheet()->getStyle('C26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle('D26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  $objPHPExcel->getActiveSheet()->getStyle("D26")->getFont()->setBold( true );

  $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
  $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setAutoSize(true);

  
  $objPHPExcel->getActiveSheet()->getStyle('A19:D19')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('D19:D26')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('A19:A26')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
  $objPHPExcel->getActiveSheet()->getStyle('A26:D26')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);

  // ----------------------------------------------------------------------
  
  $objPHPExcel->getActiveSheet()->setCellValue("A28","Monto cancelado");
  $objPHPExcel->getActiveSheet()->setCellValue("B28","$".($total-$row["total_pago_vac"]));

  $objPHPExcel->getActiveSheet()->setCellValue("A29","Total real a pagar en $ (en efectivo)");
  $objPHPExcel->getActiveSheet()->setCellValue("B29","$".$row["total_pago_vac"]);

  $objPHPExcel->getActiveSheet()->setCellValue("A30","Porcion 52%");
  $objPHPExcel->getActiveSheet()->setCellValue("B30","$".$row["valor_mayor"]);

  $objPHPExcel->getActiveSheet()->setCellValue("A31","Porcion 48%");
  $objPHPExcel->getActiveSheet()->setCellValue("B31","$".$row["valor_diferencia"]);

  $objPHPExcel->getActiveSheet()->getStyle('A28:A31')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()->getStyle('B28:B31')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

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


  $objPHPExcel->getActiveSheet()->setTitle('VACACIONES');
  $objPHPExcel->setActiveSheetIndex(0);

  $sheet = $objPHPExcel->getActiveSheet();
  $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(true);
  
  foreach ($cellIterator as $cell) {
      $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
  }

  $fileName = "vacaciones-".$row["nombres"]. " ".$row["apellidos"].$row["fecha_elab"].".xlsx";


  $nombrearch="Asientos_Utilidades";
  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;

?>