<?php include "../index/conexion.inc" ?>
<?php include "../index/validacion.php" ?>
<?php
	$cV01 = $_POST['v1'];	/* DESDE */
	$cV02 = $_POST['v2'];	/* HASTA */
	$cV03 = $_POST['v3'];	/* BODEGA */
	$cV04 = $_POST['v4'];	/* PROVEEDOR */
	$cV05 = $_POST['v5'];	/* DEPARTAMENTO */
?>
<?php
	// conexión
	$servername = $svr_servidor;
	$conectioninfo = array("Database"=>$svr_base, "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
	{
		if ($cV04 == '')
		{
			$cV06 = "";
		}
		else
		{
			$cV06 = " AND (P.CODPROVEEDOR = ".$cV04.")";
		}
		if ($cV05 == '')
		{
			$cV07 = "";
		}
		else
		{
			$cV07 = " AND (A.DPTO = ".$cV05.")";
		}
		// CONSULTA SQL
		$sql = "SELECT A.CODARTICULO, A.DPTO, A.REFPROVEEDOR, P.CODPROVEEDOR, P.NOMPROVEEDOR, A.DESCRIPCION AS NOMBRE, AL.TALLA, AL.COLOR,  \n"
			  ."AL.CODBARRAS, STOCK.BODEGA, STOCK.NOMBREALMACEN, ISNULL(STOCK.TOMA,0) AS UNIDADES, ISNULL(S.STOCK,0) AS STOCK_ACTUAL,  \n"
			  ."ISNULL(S.STOCK,0) - ISNULL(STOCK.TOMA,0) AS DIFERENCIA  \n"
			  ."FROM ARTICULOS AS A  \n"
			  ."INNER JOIN ARTICULOSLIN AS AL ON (A.CODARTICULO = AL.CODARTICULO) \n"
			  ."LEFT JOIN ( \n"
			  ."			SELECT CODARTICULO, TALLA, COLOR, STOCK  \n"
			  ."			FROM STOCKS \n"
			  ."			WHERE CODALMACEN = '".$cV03."' \n"
			  ."		   ) AS S ON (AL.CODARTICULO = S.CODARTICULO) AND (AL.TALLA = S.TALLA) AND (AL.COLOR = S.COLOR) \n"
			  ."LEFT JOIN ( \n"
			  ."			SELECT TF.CODARTICULO, TF.TALLA, TF.COLOR, TF.CODBARRAS, TF.BODEGA, TF.NOMBREALMACEN, SUM(UNIDADES) AS TOMA  \n"
			  ."			FROM  \n"
			  ."			( \n"
			  ."				SELECT A.IDLINEA, AR.CODARTICULO,  ISNULL(AL1.TALLA, ISNULL(AL2.TALLA, AL3.TALLA)) AS TALLA,  \n"
			  ."				ISNULL(AL1.COLOR, ISNULL(AL2.COLOR, AL3.COLOR)) AS COLOR, A.CODBARRAS, A.BODEGA, B.NOMBREALMACEN, A.UNIDADES, A.FECHAALTA, \n"
			  ."				ISNULL(V.CODVENDEDOR, 0) AS CODVENDEDOR, V.NOMVENDEDOR AS VENDEDOR, A.CAJAMASTER, A.UBICACION, UB.IDZONA, UB.PASILLO, UB.LADO, \n"
			  ."				P.CODPROVEEDOR, A.GONDOLA, A.DOCUMENTO, 1 AS ORD \n"
			  ."				FROM JOIN_INVENTARIO_FISICO AS A \n"
			  ."				LEFT JOIN ALMACEN AS B ON (A.BODEGA = B.CODALMACEN COLLATE Latin1_General_CS_AI) \n"
			  ."				LEFT JOIN ARTICULOSLIN AS AL1 ON (A.CODBARRAS = AL1.CODBARRAS COLLATE Latin1_General_CS_AI) \n"
			  ."				LEFT JOIN ARTICULOSLIN AS AL2 ON (A.CODBARRAS = AL2.CODBARRAS2 COLLATE Latin1_General_CS_AI) \n"
			  ."				LEFT JOIN ARTICULOSLIN AS AL3 ON (A.CODBARRAS = AL3.CODBARRAS3 COLLATE Latin1_General_CS_AI) \n"
			  ."				LEFT JOIN ARTICULOS AS AR ON (AR.CODARTICULO = AL1.CODARTICULO OR AR.CODARTICULO = AL2.CODARTICULO OR AR.CODARTICULO = AL3.CODARTICULO) \n"
			  ."				LEFT JOIN JOIN_UBICACIONES AS UB ON (CONVERT(VARCHAR, A.UBICACION) = CONVERT(VARCHAR, UB.IDUBICACION)) \n"
			  ."				LEFT JOIN VENDEDORES AS V ON (A.USUARIO = V.CODVENDEDOR) \n"
			  ."				LEFT JOIN REFERENCIASPROV AS P ON (AR.REFPROVEEDOR = P.REFPROVEEDOR) \n"
			  ."				WHERE (CONVERT(DATE, A.FECHAALTA) >= '".$cV01."') AND (CONVERT(DATE, A.FECHAALTA) <= '".$cV02."') AND (A.BODEGA = '".$cV03."') \n"
			  ."			) TF  \n"
			  ."			GROUP BY TF.CODARTICULO, TF.TALLA, TF.COLOR, TF.BODEGA, TF.NOMBREALMACEN, TF.CODBARRAS \n"
			  ."			) AS STOCK ON (AL.CODARTICULO = STOCK.CODARTICULO) AND (AL.TALLA = STOCK.TALLA) AND (AL.COLOR = STOCK.COLOR) \n"
			  ."LEFT JOIN REFERENCIASPROV AS REF ON (A.CODARTICULO = REF.CODARTICULO) \n"
			  ."LEFT JOIN PROVEEDORES AS P ON (REF.CODPROVEEDOR = P.CODPROVEEDOR) \n"
			  ."WHERE (AL.TALLA <> '.') AND (AL.COLOR <>'.')  ".$cV06." ".$cV07."";
		$pa1 = array();
		$op1 = array("scrollable" => SQLSRV_CURSOR_KEYSET);
		// Lanzar la consulta
		$re1 = sqlsrv_query($conexion, $sql, $pa1, $op1);
		if (sqlsrv_num_rows($re1) != 0)
		{
			echo "
			<div class='x_panel'>
				<div class='x_title'>
					<h3>Comparativo</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>COD.ARTICULO</th>
								<th>COD.DEPARTAMENTO</th>
								<th>REF.PROVEEDOR</th>
								<th>COD.PROVEEDOR</th>
								<th>PROVEEDOR</th>
								<th>NOMBRE</th>
								<th>TALLA</th>
								<th>COLOR</th>
								<th>COD.BARRAS</th>
								<th>BODEGA</th>
								<th>ALMACEN</th>
								<th>UNIDADES</th>
								<th>STOCK ACTUAL</th>
								<th>DIFERENCIA</th>
							</tr>
						</thead>
						<tbody>";
						while($fila = sqlsrv_fetch_array($re1))
						{
							echo "
							<tr>
								<td>".$fila['CODARTICULO']."</td>
								<td>".$fila['DPTO']."</td>
								<td>".$fila['REFPROVEEDOR']."</td>
								<td>".$fila['CODPROVEEDOR']."</td>
								<td>".$fila['NOMPROVEEDOR']."</td>
								<td>".$fila['NOMBRE']."</td>
								<td>".$fila['TALLA']."</td>
								<td>".$fila['COLOR']."</td>
								<td>".$fila['CODBARRAS']."</td>
								<td>".$fila['BODEGA']."</td>
								<td>".$fila['NOMBREALMACEN']."</td>
								<td>".$fila['UNIDADES']."</td>
								<td>".$fila['STOCK_ACTUAL']."</td>
								<td>".$fila['DIFERENCIA']."</td>
							</tr>								
							";
						}
						echo "
						</tbody>
					</table>
				</div>
			</div>
			";
		}
		else
		{
			echo "
			<div class='alert alert-warning alert-dismissible fade in' role='alert'>
			<button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='true'>×</span>
			</button>
				Registros <strong>NO</strong> localizados.
			</div>
			";
		}
		sqlsrv_free_stmt($re1);
	}
	sqlsrv_close($conexion);
?>
<script>
	$(document).ready(function() {
		$('#example').DataTable();
	} );
</script>
