<?session_start();

//RESTRICCIÓN DE ACCESO A TRABAJADOR (ACCESO INDIVIDUAL) 
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");
$enlace=cargar_conexion();
$consulta=mysqli_query($enlace,"select * from periodo where id='".$_SESSION['id_validar']."' order by nomina,periodo desc limit 1");
if (mysqli_num_rows($consulta)!=0){
	$row=mysqli_fetch_array($consulta);
	$per=mostrar_fecha($row['desde']).' al '.mostrar_fecha($row['hasta'])." (Nom:".$row['nomina'].")";
} else $per=''; 

//***************

require('fpdf.php');
class PDF extends FPDF {
	function Header()
	{
		$this->Image($_SESSION['logo_validar'],15,6,40,30);
		
		$this->Ln(20);
		$this->SetFont('arial','B',12);
		$this->Cell(8);
		$this->Cell(15,5,$_SESSION['empresa_validar'],0,0,'L');
		$this->SetFont('arial','',8);
		$this->Cell(0,5,'Fecha: '.hoy(),0,1,'R');
		$this->Cell(8);
		$this->Cell(15,5,'Rif: J-41318346-3',0,0,'L'); 
		$this->Cell(0,5,'Hora: '.hora(),0,1,'R');
		$this->Ln(2);$this->Cell(8);
		$this->Cell(0,5,"Periodo del: ".substr($_POST['per1'],0,10)." al ".substr($_POST['per2'],0,10),0,1,'R');
		$this->Ln(3);

	}
	function Footer()
	{
		$this->SetY(-20);
		$this->Cell(0,0,'',1,1,'C');
		$this->SetFont('Times','I',8);
		$this->Cell(0,0,$_POST['pie'],0,1,'C');
		$this->Cell(0,10,utf8_decode(' Página ').$this->PageNo().'/{nb} ',0,0,'C');
	}
	function titulos($header,$w,$tittle,$space){
	
		$this->SetFont('arial','B',12);		
		$this->Cell(0,5,$tittle,0,0,'C');
		$this->Ln(5);
		$this->SetFillColor(65, 105, 225);
		$this->SetTextColor(255);
		$this->SetDrawColor(65, 105, 225);
		$this->SetLineWidth(.3);
		$this->SetFont('','B',10);	
		// Header
		$this->Cell($space);
		for($i=0;$i<count($header);$i++)
			$this->Cell($w[$i],7,utf8_decode($header[$i]),0,0,'C',true);
		$this->Ln();
		$this->SetFillColor(224,235,255);
		$this->SetTextColor(0);
		$this->SetFont('','',8);

    }
	function Tabla($header, $data, $w, $tittle,$space,$totales)	{
	
			$fill = false;
			$this->titulos($header,$w,$tittle,$space);
			$rowCount=0;
			while ($row=mysqli_fetch_row($data)) {				
				if ($rowCount%20==0 && $rowCount){
					$this->AddPage();	
					$this->titulos($header,$w,$tittle,$space);			
				}				
				$this->Cell($space);
				for($i=0;$i<count($header);$i++){
					$aligment = 'C';
					if ($i<2 || $i ==5)	$aligment = 'L';
					if($i==6) $aligment = 'R';
					$this->Cell($w[$i],6,$row[$i],0,0,$aligment,$fill);
				}
				$this->Ln();
				$fill = !$fill;
				$rowCount++;
			}

			$this->titulos($totales,$w,'',$space);
	}
}
//************************/


////////////////////RESUMEN pagos LIQUIDACIONES
if ($_POST['treporte']==6) {
 $query='';
 if ($_POST['nomina']!='') $query .= " and personal.nomina = '".substr($_POST['per1'],13,3)."' ";
 		
		if ($_POST['salida']=='1') {
			$consulta = "SELECT  nomina, departamento, unidad, fecha_pago, 
			FORMAT(sum(valor_dias_pagar),2,'de_DE') as valor_dias_pagar,
			FORMAT(sum(valor_ct_total),2,'de_DE') as valor_ct_total,
			FORMAT(sum(valor_vaca_venc),2,'de_DE') as valor_vaca_venc,
			FORMAT(sum(valor_bonovac_venc),2,'de_DE') as valor_bonovac_venc,
			FORMAT(sum(bono_post_vaca),2,'de_DE') as bono_post_vaca,
			FORMAT(sum(valor_dias_desc_vacac),2,'de_DE') as valor_dias_desc_vacac,
			FORMAT(sum(valor_dias_vac_fraccion),2,'de_DE') as valor_dias_vac_fraccion,
			FORMAT(sum(valor_bonovac_fraccion),2,'de_DE') as valor_bonovac_fraccion,
			FORMAT(sum(ncon01),2,'de_DE') as ncon01,
			FORMAT(sum(valor_utilid_pagar),2,'de_DE') as valor_utilid_pagar,
			FORMAT(sum(prestaciones_pagar),2,'de_DE') as prestaciones_pagar,
			FORMAT(sum(valor_indem),2,'de_DE') as valor_indem,
			FORMAT(sum(intereses_prestac),2,'de_DE') as intereses_prestac,
			FORMAT(sum(valor_islr),2,'de_DE') as valor_islr,
			FORMAT(sum(valor_dpnlabor),2,'de_DE') as valor_dpnlabor,
			FORMAT(sum(inces),2,'de_DE') as inces,
			FORMAT(sum(anticipo_prestac),2,'de_DE') as anticipo_prestac,
			FORMAT(sum(saldo_prestamo),2,'de_DE') as saldo_prestamo,
			FORMAT(sum(adeuti),2,'de_DE') as adeuti,
			FORMAT(sum(ncon02),2,'de_DE') as ncon02,
			FORMAT(sum(total_asig),2,'de_DE') as total_asig,
			FORMAT(sum(total_deduccion),2,'de_DE') as total_deduccion,
			FORMAT(sum(neto_pagar_liq),2,'de_DE') as neto_pagar_liq, count(*) as numero
			FROM liquidaciones,personal
			WHERE  cedula=personal.codigo and liquidaciones.id=personal.id and liquidaciones.id=".$_SESSION['id_validar']." AND  liquidaciones.fecha_pago>='".substr($_POST['per1'],0,10)."' AND  liquidaciones.fecha_pago<= '".substr($_POST['per2'],0,10)."' AND estado_liquidacion='pagado' GROUP BY  fecha_pago,nomina, departamento, unidad;";
			$resultado = mysqli_query($enlace,$consulta) or die('verificaci&oacute; del c&oacute;digo fall&oacute;: ' . mysqli_error($enlace));
			$consulta = "SELECT  
			FORMAT(sum(valor_dias_pagar),2,'de_DE') as valor_dias_pagar,
			FORMAT(sum(valor_ct_total),2,'de_DE') as valor_ct_total,
			FORMAT(sum(valor_vaca_venc),2,'de_DE') as valor_vaca_venc,
			FORMAT(sum(valor_bonovac_venc),2,'de_DE') as valor_bonovac_venc,
			FORMAT(sum(bono_post_vaca),2,'de_DE') as bono_post_vaca,
			FORMAT(sum(valor_dias_desc_vacac),2,'de_DE') as valor_dias_desc_vacac,
			FORMAT(sum(valor_dias_vac_fraccion),2,'de_DE') as valor_dias_vac_fraccion,
			FORMAT(sum(valor_bonovac_fraccion),2,'de_DE') as valor_bonovac_fraccion,
			FORMAT(sum(ncon01),2,'de_DE') as ncon01,
			FORMAT(sum(valor_utilid_pagar),2,'de_DE') as valor_utilid_pagar,
			FORMAT(sum(prestaciones_pagar),2,'de_DE') as prestaciones_pagar,
			FORMAT(sum(valor_indem),2,'de_DE') as valor_indem,
			FORMAT(sum(intereses_prestac),2,'de_DE') as intereses_prestac,
			FORMAT(sum(valor_islr),2,'de_DE') as valor_islr,
			FORMAT(sum(valor_dpnlabor),2,'de_DE') as valor_dpnlabor,
			FORMAT(sum(inces),2,'de_DE') as inces,
			FORMAT(sum(anticipo_prestac),2,'de_DE') as anticipo_prestac,
			FORMAT(sum(saldo_prestamo),2,'de_DE') as saldo_prestamo,
			FORMAT(sum(adeuti),2,'de_DE') as adeuti,
			FORMAT(sum(ncon02),2,'de_DE') as ncon02,
			FORMAT(sum(total_asig),2,'de_DE') as total_asig,
			FORMAT(sum(total_deduccion),2,'de_DE') as total_deduccion,
			FORMAT(sum(neto_pagar_liq),2,'de_DE') as neto_pagar_liq, count(*) as numero
			FROM liquidaciones,personal
			WHERE  cedula=personal.codigo and liquidaciones.id=personal.id and liquidaciones.id=".$_SESSION['id_validar']." AND  liquidaciones.fecha_pago>='".substr($_POST['per1'],0,10)."' AND  liquidaciones.fecha_pago<= '".substr($_POST['per2'],0,10)."' AND estado_liquidacion='pagado';";
			$resultadoSum = mysqli_query($enlace,$consulta) or die('verificaci&oacute; del c&oacute;digo fall&oacute;: ' . mysqli_error($enlace));
			$sumMonto = mysqli_fetch_row($resultadoSum);
			$totales = array('Total',' ',' ',' ',$sumMonto[0],$sumMonto[1],$sumMonto[2],$sumMonto[3],$sumMonto[4],$sumMonto[5],$sumMonto[6],$sumMonto[7],$sumMonto8[8],$sumMonto[9],$sumMonto[10],$sumMonto[11],$sumMonto[12],$sumMonto[13],$sumMonto[14],$sumMonto[15],$sumMonto[16],$sumMonto[17],$sumMonto[18],$sumMonto[19],$sumMonto[20],$sumMonto[21],$sumMonto[22],$sumMonto[23]);
			$pdf=new PDF('L','mm',[216,875]);
			$pdf->AddPage();
			$pdf->AliasNbPages();
			$w=array(25,25,25,35,25,25,35,35,35,35,35,35,35,25,25,25,25,25,25,25,25,25,25,25,25,25,25,25,30);
			$space = (865 / 2) - (array_sum($w) / 2);
			$cabecera = array("Nómina","Departamento","Localidad","Fecha registro","Días Sueldo","Cestaticket","Vacaciones vencidas","Bono vacacional","Bono Post Vacac.","Dias descan vac","Vacaciones fracc.","Bono vacac fracc","Otras asignaciones","Utilidades","Prestaciones","Indemnización","Interéses","ISLR","Días No laborados","INCES","Antic. Prestaci.","Desc préstamo","Antic. Utilidad","Descuentos","Total Asig.","Total Deducc.","Neto pagar","Nro. Empleados");
			$pdf->SetFont('arial','B',12);
			$tittle = 'RESUMEN PAGOS LIQUIDACIONES';
			$pdf->Ln(5);
			$pdf->Tabla($cabecera,$resultado,$w,$tittle,$space,$totales);
			$pdf->Output('Resumen pagos Liquidaciones.pdf','D');	
			exit();
		}
		//$consulta = "select * from personal where id='".$_SESSION['id_validar']."' and status<>'I' $query order by codigo";
		$consulta = "SELECT cedula, CONCAT(personal.nombres,' ',personal.nombres1,' ',personal.apellidos,' ',personal.apellidos1), nomina, departamento, unidad, liquidaciones.fechai, liquidaciones.fecha_retiro, estado_liquidacion, fecha_pago, 
		REPLACE(REPLACE(FORMAT(valor_dias_pagar,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_ct_total,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_vaca_venc,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_bonovac_venc,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(bono_post_vaca,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_dias_desc_vacac,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_dias_vac_fraccion,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_bonovac_fraccion,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(ncon01,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_utilid_pagar,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(prestaciones_pagar,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_indem,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(intereses_prestac,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_islr,2),',',''),'.',','), 
		REPLACE(REPLACE(FORMAT(valor_dpnlabor,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(inces,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(anticipo_prestac,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(saldo_prestamo,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(adeuti,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(ncon02,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(total_asig,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(total_deduccion,2),',',''),'.',','),
		REPLACE(REPLACE(FORMAT(neto_pagar_liq,2),',',''),'.',',')
		
		FROM liquidaciones,personal
		WHERE  cedula=personal.codigo and liquidaciones.id=personal.id and liquidaciones.id=".$_SESSION['id_validar']." AND  liquidaciones.fecha_retiro>='".substr($_POST['per1'],0,10)."' AND  liquidaciones.fecha_retiro<= '".substr($_POST['per2'],0,10)."'  order by fecha_retiro ASC";
		$resultado = mysqli_query($enlace,$consulta) or die('verificaci&oacute; del c&oacute;digo fall&oacute;: ' . mysqli_error($enlace));
		$cont=0;
		$data=array();
		$cabecera=array("Cedula","Nombre y Apellidos Trabajador","Nomina","Departamento","Localidad","Fecha_ingreso","Fecha_retiro","Estatus","Fecha_pago","Dias_Sueldo","Cestaticket","Vacaciones_vencidas","Bono_vacacional","Bono_Post_Vacac","Dias_descan_vac","Vacaciones_fracc","Bono_vacac_fracc","Otras_asignaciones","Utilidades","Prestaciones","Indemnizacion","Intereses","ISLR","Dias_No_laborados","INCES","Antic_Prestaci","Desc_prestamo","Antic_Utilidad","Descuentos","Total_Asig","Total_Deducc","Neto_pagar");
		$cabecera=implode(";",$cabecera);
		while ($row=mysqli_fetch_row($resultado)) {
		     $cont++;
			 $data[]=implode(";",$row);
		}
		$data=implode("\n",$data);
	$arch="resumen_pagos_liquidaciones del ".substr($_POST['per1'],0,10)." al ".substr($_POST['per2'],0,10).".csv";		
	header("Content-type: text/csv");
	header("Content-Disposition: attachment; filename=".$arch);
	header("Pragma: no-cache");
	header("Expires: 0");
	echo "Empresa: ".$_SESSION['empresa_validar']."\n";
	echo " \n";
	echo "Resumen de pagos de Liquidaciones \n";
	echo " \n";
	echo "Periodo del: ".substr($_POST['per1'],0,10)." al ".substr($_POST['per2'],0,10)."\n";
	echo "\n";
	//echo substr($_POST['per1'],19,10)."\n";
	echo $cabecera."\n";
	echo utf8_decode($data);		
			
	exit();
}

?>
<html>
	<head><link rel='stylesheet' type='text/css' href='styles.css' />
	</head>
	<body>	
		<?
		menu();
		?>
		<br><table border=0 cellpadding=0 cellspacing=0 width='100%'>
			<tr>
				<td  width='100%' align='left'><font size=2 color='black' face='verdana'>Empresa: <b><?echo $_SESSION['empresa_validar'];?></b>  - Per&iacute;odo: <b><font color=red><?echo $per;?></font></b>  -  Usuario: <b><?echo $_SESSION['nombre_validar'];?></b></font></td> 
			</tr>
		</table><hr>	
<?	echo "<tr align='center'>";	
	echo "<h1><center><font face='Verdana, Arial, Helvetica, sans-serif'>Generar Reporte de Liquidaciones Procesadas</font></center></h1>";
	echo "</tr>";
	echo "<font face='verdana' size=2>";
	echo "
	<br><br><form name='entrada' action='".$_SERVER['PHP_SELF']."' target='blank' method='POST' enctype='multipart/form-data'>
	<table align='center'>";
			if ($per!='') {
					echo "<tr><td align='righta'>Per&iacute;odo Actual:</td>
						  <td align='left'><font color=red>".$per."</font></td>
						  </tr>";
			}			
			echo "
				<tr><td align='righta'>Tipo de Archivo:</td>
				<td align='left'>
					<select style='width:300px;' name='treporte'>
	
						<option value='6'>Reporte de  Liquidaciones Procesadas</option>
		
						

					</select>
				</td>
				</tr>
				
				<!--
				<tr>
				<td align='righta'>Salida del Reporte:</td>
				<td align='left'>
					<select style='width:300px;' name='salida'>
						<option value='1' selected>PDF
						<option value='2' selected>CSV (EXCEL)
					</select>
				</td>
				</tr>
				-->
				";
			
			if ($_SESSION['id_validar']=='104'){
			$consulta = mysqli_query($enlace,"select * from periodo where id='".$_SESSION['id_validar']."' AND nomina='200' order by nomina,periodo DESC") or die ("select * from periodo order by nomina,periodo DESC");
			}
			elseif($_SESSION['id_validar']=='105'){
				$consulta = mysqli_query($enlace,"select * from periodo where id='".$_SESSION['id_validar']."' order by nomina,periodo DESC") or die ("select * from periodo order by nomina,periodo DESC");
							}

			
			$numr = mysqli_num_rows($consulta);
			echo "<td align = ''> <font color=red>Per&iacute;odo Desde: </font></td>";
			echo "<td align = 'left'> <select style='width:300px;' name = 'per1'>";
			for ($x=1;$x<=$numr;$x++) {
				$row1 = mysqli_fetch_array($consulta); 
				if ($x!=1) {
					echo "<option>".$row1['desde'];
				}	
			}
			echo "</select>";
			echo "</td></tr>";
			
			if ($_SESSION['id_validar']=='104'){
			$consulta = mysqli_query($enlace,"select * from periodo where id='".$_SESSION['id_validar']."' AND nomina='200' order by nomina,periodo DESC") or die ("select * from periodo order by nomina,periodo DESC");
			}
			elseif($_SESSION['id_validar']=='105'){
				$consulta = mysqli_query($enlace,"select * from periodo where id='".$_SESSION['id_validar']."' order by nomina,periodo DESC") or die ("select * from periodo order by nomina,periodo DESC");
							}
			
			$numr = mysqli_num_rows($consulta);
			echo "<td align = ''> <font color=red>Per&iacute;odo Hasta: </font></td>";
			echo "<td align = 'left'> <select style='width:300px;' name = 'per2'>";
			for ($x=1;$x<=$numr;$x++) {
				$row1 = mysqli_fetch_array($consulta); 
				if ($x!=1) {
					echo "<option>".$row1['hasta'];
				}	
			}
			echo "</select>";
			echo "</td></tr>";
			
			
			/*
			
			echo "<tr>";
			$consulta="Select * from tablas where tabla=ucase('GERENCIA') and id='".$_SESSION['id_validar']."'  order by codigo,detalle";
			$consulta = mysqli_query($enlace,$consulta) or die ("no se pudo realizar la consulta...".$consulta.'='.mysqli_error($enlace));	
			$resultado = mysqli_num_rows($consulta);
			echo "<td align = 'righta'> Gerencia: </td>";
			echo "<td align = 'left'> <select style='width:300px;' name = 'gerencia'><option>";
			if ($resultado != 0) {
				for ($i=0;$i<$resultado;$i++) {
					$row1 = mysqli_fetch_array($consulta);
					echo "<option width = '30%'>".$row1['codigo']." - ".$row1['detalle'];
				}
			}	
			echo "</select>";
			echo "</td>";
			echo "</tr>";
								
			echo "<tr>";
			$consulta="Select * from tablas where tabla=ucase('DIVISION') and id='".$_SESSION['id_validar']."' order by codigo,detalle";
			$consulta = mysqli_query($enlace,$consulta) or die ("no se pudo realizar la consulta...".$consulta.'='.mysqli_error($enlace));	
			$resultado = mysqli_num_rows($consulta);
			echo "<td align = 'righta'> Divisi&oacute;n: </td>";
			echo "<td align = 'left'> <select style='width:300px;' name = 'division'><option>";
			if ($resultado != 0) {
				for ($i=0;$i<$resultado;$i++) {
					$row1 = mysqli_fetch_array($consulta);
					echo "<option width = '30%'>".$row1['codigo']." - ".$row1['detalle'];
				}
			}	
			echo "</select>";
			echo "</td>";
			echo "</tr>";
			
			echo "<tr>";
			$consulta="Select * from tablas where tabla=ucase('DEPARTAMENTO') and id='".$_SESSION['id_validar']."' order by codigo,detalle";
			$consulta = mysqli_query($enlace,$consulta) or die ("no se pudo realizar la consulta...".$consulta.'='.mysqli_error($enlace));	
			$resultado = mysqli_num_rows($consulta);
			echo "<td align = 'righta'> Departamento: </td>";
			echo "<td align = 'left'> <select style='width:300px;' name = 'departamento'><option>";
			if ($resultado != 0) {
				for ($i=0;$i<$resultado;$i++) {
					$row1 = mysqli_fetch_array($consulta);
					echo "<option width = '30%'>".$row1['codigo']." - ".$row1['detalle'];
				}
			}	
			echo "</select>";
			echo "</td>";
			*/
								
			echo "<tr><td colspan='4' align='center'>";
			echo "<input name='ver' type='submit' style='margin-left:5px; height:35px; width:200px' value='GENERAR ARCHIVO'>";
			echo "</td></tr>";	
			echo "</table></form></font>";
?>
		
	</body>
</html>