<?php include "../index/conexion.inc" ?>
<?php include "../index/validacion.php" ?>
<?php
	$cV01 = $_POST['v1'];	/* DESDE */
	$cV02 = $_POST['v2'];	/* HASTA */
?>
<?php
	// conexión
	$servername = $svr_servidor;
	$conectioninfo = array("Database"=>"JOININTEGRADOR", "UID"=>$svr_usuario, "PWD"=>$svr_contrasena, "CharacterSet"=>"UTF-8");
	$conexion = sqlsrv_connect($servername, $conectioninfo);
	if ($conexion == false)
	{
		$mensaje = "Existe un error al intentar conectarse con el servidor...";
	}
	else
	{
		echo "
		<div class='x_panel'>
			<div class='x_title'>
				<h3>LOG Pagos</h3>
				<div class='clearfix'></div>
			</div>
			<button type='submit' class='btn btn-success'>
				<span class='glyphicon glyphicon-export' aria-hidden='true'></span> Exportar a excel
			</button>
			<div class='table-responsive'>
				<table id='example' class='table table-striped table-bordered' style='width:100%'>
					<thead>
						<tr>
							<th>JSON ENVIO</th>
							<th>FECHA</th>
							<th>HORA</th>
							<th>COD.CLIENTE</th>
							<th>CLIENTE</th>
							<th>AGENTE</th>
							<th>ABONO</th>
							<th>CURRENCY</th>
							<th>DOCENTRY</th>
							<th>DOCENTRY PR</th>
							<th>CAJA</th>
							<th>DOCUMENTO</th>
							<th>MONTO</th>
							<th>METODO</th>
							<th>STATUS WS</th>
							<th>FECHA PROCESO</th>
						</tr>
					</thead>
					<tbody>";
						// CONSULTA SQL
						$sql = "DECLARE @DESDE DATE = '".$cV01."' \n"
							  ."DECLARE @HASTA DATE = '".$cV02."' \n"
							  ."SELECT P.LINEA, P.JSON, \n"
							  ."CASE \n"
							  ."	WHEN (P.JSON LIKE '%contado%') THEN 1 \n"
							  ."	WHEN (P.JSON LIKE '%Encabezado%') THEN 2 \n"
							  ."	WHEN (P.JSON LIKE '%credito%') THEN 3 \n"
							  ."	WHEN (P.JSON LIKE '%cheque%') THEN 4 \n"
							  ."	WHEN (P.JSON LIKE '%Reconciliacion%') THEN 5 \n"
							  ."END AS Tipo, P.FECHAACCION, P.STATUSTEXT_WS \n"
							  ."FROM ws_joinpagos AS P \n"
							  ."WHERE (CONVERT(DATE, P.FECHAACCION) >= @DESDE AND CONVERT(DATE, P.FECHAACCION) <= @HASTA) \n"
							  ."ORDER BY P.LINEA DESC";
						$pa1 = array();
						$op1 = array("scrollable" => SQLSRV_CURSOR_KEYSET);
						// Lanzar la consulta
						$re1 = sqlsrv_query($conexion, $sql, $pa1, $op1);
						while($fila = sqlsrv_fetch_array($re1))
						{
							$cV01 = $fila['LINEA'];
							$json = $fila['JSON'];
							$objt = json_decode($json,"true");
							echo "
							<tr>
								<td class='a-center'>
									<button class='btn btn-sm btn-warning' type='button' data-placement='top' onclick='popup02($cV01)' data-toggle='tooltip' data-original-title='Ver Trama'><i class='fa fa-edit'></i></button>
								</td>";
								if ($fila['Tipo']==5)
								{
									echo "
										<td>".substr($objt["Reconciliacion"]["0"]["DocDate"], 0, 10)."</td>
										<td>".substr($objt["Reconciliacion"]["0"]["DocDate"], 11, 8)."</td>
										<td>".$objt["Reconciliacion"]["0"]["CardCode"]."</td>
									";
								}
								else
								{
									echo "
										<td>".substr($objt["DocDate"], 0, 10)."</td>
										<td>".substr($objt["DocDate"], 11, 8)."</td>
										<td>".$objt["CardCode"]."</td>
									";
								}								
								// CONSULTA SQL CLIENTES Y AGENTE
								if ($fila['Tipo']==5)
								{
									if ($objt["Reconciliacion"]["0"]["CardCode"] == '')
									{
										echo "
											<td>".$objt["Reconciliacion"]["0"]["CardCode"]."</td>
											<td>".$objt["Reconciliacion"]["0"]["CardCode"]."</td>
										";
									}
									else
									{
										$sq2 = "SELECT C.NOMBRECLIENTE, UPPER(L.AGENTE_DE_VENTAS) AS AGENTE \n"
											  ."FROM AGORA_2020.dbo.CLIENTES AS C \n"
											  ."INNER JOIN AGORA_2020.dbo.CLIENTESCAMPOSLIBRES AS L ON (C.CODCLIENTE = L.CODCLIENTE) \n"
											  ."WHERE (C.CODCLIENTE = ".$objt["Reconciliacion"]["0"]["CardCode"].")";
										$pa2 = array();
										$op2 = array("scrollable" => SQLSRV_CURSOR_KEYSET);
										// Lanzar la consulta
										$re2 = sqlsrv_query($conexion, $sq2, $pa2, $op2);
										while($fila2 = sqlsrv_fetch_array($re2))
										{
											echo "
												<td>".$fila2['NOMBRECLIENTE']."</td>
												<td>".$fila2['AGENTE']."</td>
											";
										}
										sqlsrv_free_stmt($re2);
									}
								}
								else
								{
									if ($objt["CardCode"] == '')
									{
										echo "
											<td>".$objt["CardCode"]."</td>
											<td>".$objt["CardCode"]."</td>
										";
									}
									else
									{
										$sq2 = "SELECT C.NOMBRECLIENTE, UPPER(L.AGENTE_DE_VENTAS) AS AGENTE \n"
											  ."FROM AGORA_2020.dbo.CLIENTES AS C \n"
											  ."INNER JOIN AGORA_2020.dbo.CLIENTESCAMPOSLIBRES AS L ON (C.CODCLIENTE = L.CODCLIENTE) \n"
											  ."WHERE (C.CODCLIENTE = ".$objt["CardCode"].")";
										$pa2 = array();
										$op2 = array("scrollable" => SQLSRV_CURSOR_KEYSET);
										// Lanzar la consulta
										$re2 = sqlsrv_query($conexion, $sq2, $pa2, $op2);
										while($fila2 = sqlsrv_fetch_array($re2))
										{
											echo "
												<td>".$fila2['NOMBRECLIENTE']."</td>
												<td>".$fila2['AGENTE']."</td>
											";
										}
										sqlsrv_free_stmt($re2);
									}
								}
								if ($fila['Tipo']==5)
								{
									echo "								
									<td>".$objt["Reconciliacion"]["0"]["Abono"]."</td>
									<td>".$objt["Reconciliacion"]["0"]["Currency"]."</td>
									<td>".$objt["Reconciliacion"]["0"]["DocEntryFC"]."</td>
									<td>".$objt["Reconciliacion"]["0"]["DocEntryPR"]."</td>
									<td>".substr($objt["Reconciliacion"]["0"]["CajaICG"], 0, 4)."</td>
									<td>".substr($objt["Reconciliacion"]["0"]["PagoICG"]["0"]["ID"], 4, 10)."</td>";
								}
								else
								{
									echo "
									<td>".$objt["Abono"]."</td>
									<td>".$objt["Currency"]."</td>
									<td>".$objt["DocEntry"]."</td>
									<td></td>
									<td>".substr($objt["CajaICG"], 0, 4)."</td>
									<td>".substr($objt["CajaICG"], 4, 10)."</td>";
								}
								if ($fila['Tipo']==1)
								{
									echo "
										<td>".$objt["contado"]["0"]["Monto"]."</td>
										<td>CONTADO</td>
									";
								}
								if ($fila['Tipo']==2)
								{
									echo "
										<td>".$objt["Encabezado"]["0"]["Total"]."</td>
										<td>ENZABEZADO</td>
									";
								}
								if ($fila['Tipo']==3)
								{
									echo "
										<td>".$objt["credito"]["0"]["MontoTarjeta"]."</td>
										<td>TARJETA</td>
									";
								}
								if ($fila['Tipo']==4)
								{
									echo "
										<td>".$objt["cheque"]["0"]["MontoCheque"]."</td>
										<td>CHEQUE</td>
									";
								}
								if ($fila['Tipo']==5)
								{
									echo "
										<td>".$objt["Reconciliacion"]["0"]["Monto"]."</td>
										<td>RECONCILIACION</td>
									";
								}
							echo "
								<td class='a-center'>
									<button class='btn btn-sm btn-warning' type='button' data-placement='top' onclick='popup01($cV01)' data-toggle='tooltip' data-original-title='Ver Trama'>".$fila['STATUSTEXT_WS']."</button>
								</td>
								<td>".$fila['FECHAACCION']->format('Y-m-d h:m:s')."</td>
							</tr>";
						} echo "
					</tbody>
				</table>
			</div>
		</div>";
		sqlsrv_free_stmt($re1);
	}
	sqlsrv_close($conexion);
?>
<script>
	$(document).ready(function() {
		$('#example').DataTable();
	} );
</script>