<?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['bodega'];	/* BODEGA */
		// PARAMETROS PARA EXPORTAR
		header("Pragma: public");
		header("Expires: 0");
		$filename = "StockBodegas.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>ARTICULO</th>
						<th>DESCRIPCION</th>
						<th>TALLA</th>
						<th>COLOR</th>
						<th>MODELO</th>
						<th>STOCK</th>
						<th>CAJA MASTER</th>
						<th>ID UBICACION</th>
						<th>UBICACION</th>
					</tr>
				</thead>
				<tbody>";
					// CONSULTA SQL
					$sql = "DECLARE @BODEGA NVARCHAR(4) = '".$cV01."'  EXECUTE JOIN_REPORTE_STOCK_POR_BODEGA @BODEGA  \n"
			  ."SELECT  A.BODEGA, A.CAJAMASTER,A.UBICACION,A.MODELO, A.DESCRIPCION,  A.CODARTICULO, A.TALLA, A.COLOR, A.CODBARRAS, \n"
			 ." UNIDADES - ISNULL(SALIDAS,0) STOCK, A.CODUBICACION \n"
			  ."FROM  \n"
			  ."( SELECT  B.BODEGA,  A.CODIGOBARRAS CAJAMASTER, A.CODUBICACION UBICACION, A.CODARTICULO, AR.REFPROVEEDOR MODELO, AR.DESCRIPCION, \n"
			  ."	A.TALLA, A.COLOR, ARL.CODBARRAS, A.CANTIDADARTICULOS AS UNIDADES, B.CODUBICACION \n"
			  ."	FROM JOIN_CAJAMASTER_2 AS A  \n"
			  ."	LEFT JOIN JOIN_UBICACIONES AS B ON CONVERT(VARCHAR, A.CODUBICACION) = CONVERT(VARCHAR, B.IDUBICACION) \n"
			  ."	LEFT JOIN ARTICULOS AS AR ON (A.CODARTICULO = AR.CODARTICULO) \n"
			 ." 	LEFT JOIN ARTICULOSLIN AS ARL ON (A.CODARTICULO = ARL.CODARTICULO) AND (A.TALLA = ARL.TALLA COLLATE Latin1_General_CS_AI) AND (A.COLOR = ARl.COLOR COLLATE Latin1_General_CS_AI) \n"
			  ."	WHERE (B.BODEGA = @BODEGA) AND A.CODIGOBARRAS <>'0000'\n"
			 ." ) AS A LEFT JOIN \n"
			 ." ( SELECT CAJAMASTER, CODIGOARTICULO, TALLA,COLOR, SUM(SALIDAS) SALIDAS \n"
			 ." 	FROM JOIN_REPORTE_STOCK_BOG_TB AS AA WHERE BODEGA = @BODEGA \n"
			."	AND CAJAMASTER <>'0000'\n"
				."GROUP BY CAJAMASTER, CODIGOARTICULO, TALLA,COLOR\n"
			  .") AS B ON (A.CODARTICULO = B.CODIGOARTICULO) AND (A.TALLA = B.TALLA) \n"
			 ." AND (A.COLOR = B.COLOR) AND (A.CAJAMASTER = B.CAJAMASTER) WHERE \n"
			  ."(UNIDADES - ISNULL(SALIDAS,0) <>0) \n"
		."		UNION ALL\n"
			."   SELECT  A.BODEGA, A.CAJAMASTER,A.UBICACION,A.MODELO, A.DESCRIPCION,  A.CODARTICULO, A.TALLA, A.COLOR, A.CODBARRAS, \n"
			 ." UNIDADES - ISNULL(SALIDAS,0) STOCK, A.CODUBICACION \n"
			 ." FROM \n" 
			 ." ( \n"
			 ." 	SELECT  B.BODEGA,  A.CODIGOBARRAS CAJAMASTER, A.CODUBICACION UBICACION, A.CODARTICULO, AR.REFPROVEEDOR MODELO, AR.DESCRIPCION, \n"
			  ."	A.TALLA, A.COLOR, ARL.CODBARRAS, A.CANTIDADARTICULOS AS UNIDADES, B.CODUBICACION \n"
			  	."FROM JOIN_CAJAMASTER_2 AS A  \n"
			  ."	LEFT JOIN JOIN_UBICACIONES AS B ON CONVERT(VARCHAR, A.CODUBICACION) = CONVERT(VARCHAR, B.IDUBICACION) \n"
			  ."	LEFT JOIN ARTICULOS AS AR ON (A.CODARTICULO = AR.CODARTICULO) \n"
			  ."	LEFT JOIN ARTICULOSLIN AS ARL ON (A.CODARTICULO = ARL.CODARTICULO) AND (A.TALLA = ARL.TALLA COLLATE Latin1_General_CS_AI) AND (A.COLOR = ARl.COLOR COLLATE Latin1_General_CS_AI) \n"
			  ."	WHERE (B.BODEGA = @BODEGA) AND A.CODIGOBARRAS ='0000'\n"
			  .") AS A LEFT JOIN \n"
			  ."( SELECT CAJAMASTER,UBICACION, CODIGOARTICULO, TALLA,COLOR, SUM(SALIDAS) SALIDAS \n"
			  	."FROM JOIN_REPORTE_STOCK_BOG_UB AS AA WHERE BODEGA = @BODEGA \n"
				."GROUP BY CAJAMASTER,UBICACION, CODIGOARTICULO, TALLA,COLOR\n"
			 ." ) AS B ON (A.CODARTICULO = B.CODIGOARTICULO) AND (A.TALLA = B.TALLA) \n"
			 ." AND (A.COLOR = B.COLOR) AND (A.CAJAMASTER = B.CAJAMASTER)\n"
			 ." AND  A.UBICACION = B.UBICACION\n"
			."  WHERE \n"
			 ." (UNIDADES - ISNULL(SALIDAS,0) <>0)  ";
					$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['DESCRIPCION']."</td>
								<td>".$fila['TALLA']."</td>
								<td>".$fila['COLOR']."</td>
								<td>".$fila['MODELO']."</td>
								<td>".$fila['STOCK']."</td>
								<td>".$fila['CAJAMASTER']."</td>
								<td>".$fila['UBICACION']."</td>
								<td>".$fila['CODUBICACION']."</td>
							</tr>
						";
					}
				echo "
				</tbody>
			</table>
		";
	}
	// Desconexion
	sqlsrv_close($conexion);
?>