<?php include "../index/conexion.inc" ?>
<?php include "../index/validacion.php" ?>
<?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
	{
		// VARIABLES
		$cV01 = $_POST['desde'];	/* DESDE */
		$cV02 = $_POST['hasta'];	/* HASTA */
		$cV03 = $_POST['bodega'];	/* BODEGA */
		$cV04 = $_POST['proveedor'];	/* PROVEEDOR */
		$cV05 = $_POST['departamento'];	/* DEPARTAMENTO */
		// PARAMETROS PARA EXPORTAR
		header("Pragma: public");
		header("Expires: 0");
		$filename = "Comparativo.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>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>";
		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 <>'.') AND (STOCK.BODEGA = '".$cV03."') ".$cV06." ".$cV07."";
					$pa1 = array();
					$op1 = array("scrollable" => SQLSRV_CURSOR_KEYSET);
					// Lanzar la consulta
					$re1 = sqlsrv_query($conexion, $sql, $pa1, $op1);
					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>
		";
	}
	// Desconexion
	sqlsrv_close($conexion);
?>