<?php include "../index/conexion.inc" ?>
<?php include "../index/validacion.php" ?>
<?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
	{
		// VARIABLES
		$cV01 = $_POST['desde'];	/* DESDE */
		$cV02 = $_POST['hasta'];	/* HASTA */
		// PARAMETROS PARA EXPORTAR
		header("Pragma: public");
		header("Expires: 0");
		$filename = "LOG Join Pagos.xls";
		header("Content-type: application/x-msdownload");
		header("Content-Disposition: attachment; filename=$filename");
		header("Pragma: no-cache");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		echo "
			<table>
				<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>
		";
	}
	// Desconexion
	sqlsrv_close($conexion);
?>