ventas_php/export/ventas-proveedor.php

<?php

	include_once('../init.php');
	include_once('../config.php');
	include_once(DOC_ROOT.'/libraries.php'); 
		
	$Usr = $user->Info();
	
	if($Usr['type'] != 'admin' && $Usr['type'] != 'gerente' && $Usr['type'] != 'centralizador' && $Usr['type'] != 'almacen' && $Usr['type'] != 'facturacion' && $Usr['type'] != 'direccion' && $Usr['type'] != 'supervisor'){
		header('Location: '.WEB_ROOT);
		exit;
	}
	
	$name = "R_ventas_por_Proveedor";	
	
	extract($_POST);
	
	$idSucursal = $_POST['idSucursal'];
	$idProveedor = $_POST['idProveedor'];
	$fechaIni = date('Y-m-d',strtotime($_POST['fechaI']));
	$fechaFin = date('Y-m-d',strtotime($_POST['fechaF']));
	
	$reportes->setIdSuc($idSucursal);
	$resSuc=$reportes->EnumSucursales($Usr['type'],$Usr['usuarioId']);
									
	if($idProveedor)
		$sqlFilter = ' AND proveedorId = '.$idProveedor;
					
	$sql = 'SELECT proveedorId, nombre FROM proveedor 
			WHERE baja = "0" '.$sqlFilter.' 
			ORDER BY proveedorId ASC';
	$util->DBSelect($_SESSION['empresaId'])->setQuery($sql);
	$resProv = $util->DBSelect($_SESSION['empresaId'])->GetResult();
	
	$totales['inv'] = 0;
	$totales['prods'] = 0;
	$totales['vtas'] = 0;
	$sucursales = array();
	foreach($resSuc as $res){
		
		$sucursalId = $res['sucursalId'];
		
		$totalInvG = 0;
		$totalProdsG = 0;
		$totalVtasG = 0;					
		$proveedores = array();
		foreach($resProv as $val){
			
			$sql = 'SELECT SUM(vp.cantidad) AS totalProds , SUM(vp.total) AS totalVtas
					FROM venta AS v, ventaProducto AS vp, producto AS p
					WHERE v.ventaId = vp.ventaId
					AND vp.productoId = p.productoId
					AND ((v.status = "Cancelado" AND v.cancelDev = "1") OR v.status <> "Cancelado")
					AND v.status <> "Descuento"
					AND v.fecha >= "'.$fechaIni.' 00:00:00" 
					AND v.fecha <= "'.$fechaFin.' 23:59:59"
					AND v.sucursalId = "'.$sucursalId.'"
					AND p.proveedorId = '.$val['proveedorId'];
			$util->DBSelect($_SESSION['empresaId'])->setQuery($sql);
			$row = $util->DBSelect($_SESSION['empresaId'])->GetRow();
			
			$totalProds = $row['totalProds'];
			$totalVtas = $row['totalVtas'];
			
			$sql = 'SELECT SUM(dp.cantidad) AS totalProdsDev, SUM(dp.total) AS totalDevs
					FROM venta AS v, devolucion AS d, devolucionProducto AS dp, producto AS p
					WHERE v.ventaId = d.ventaId
					AND d.devolucionId = dp.devolucionId
					AND dp.productoId = p.productoId
					AND ((v.status = "Cancelado" AND v.cancelDev = "1") OR v.status <> "Cancelado")
					AND v.status <> "Descuento"
					AND v.fecha >= "'.$fechaIni.' 00:00:00" 
					AND v.fecha <= "'.$fechaFin.' 23:59:59"
					AND v.sucursalId = "'.$sucursalId.'"
					AND p.proveedorId = '.$val['proveedorId'];
			$util->DBSelect($_SESSION['empresaId'])->setQuery($sql);
			$row = $util->DBSelect($_SESSION['empresaId'])->GetRow();
			
			$totalProds -= $row['totalProdsDev'];
			$totalVtas -= $row['totalDevs'];
			
			if($totalProds){
				$a = 1;
			}else{
				continue;
			}
					
			//Inventario Disponible
									
			$sql = 'SELECT SUM(inv.cantidad)
					FROM inventario AS inv, producto AS prod, proveedor AS prov
					WHERE inv.productoId = prod.productoId
					AND prod.proveedorId = prov.proveedorId
					AND inv.status = "Disponible"
					AND prod.proveedorId = "'.$val['proveedorId'].'"
					AND inv.sucursalId = "'.$sucursalId.'"';		
			$util->DBSelect($_SESSION['empresaId'])->setQuery($sql);
			$totalInv = $util->DBSelect($_SESSION['empresaId'])->GetSingle();
			
			//Eliminamos las ventas no descontadas
			
			$sql = 'SELECT SUM(cantidad) 
					FROM inventario
					LEFT JOIN pedido ON pedido.pedidoId = inventario.pedidoId
					WHERE sucursalId = "'.$sucursalId.'"
					AND pedido.proveedorId = "'.$val['proveedorId'].'"
					AND pedido.ventaDesc = "Si"';
			$util->DBSelect($_SESSION['empresaId'])->setQuery($sql);
			$totalInvDesc = $util->DBSelect($_SESSION['empresaId'])->GetSingle();		
									
			$val['nombre'] = utf8_encode($val['nombre']);
			$val['totalInv'] = $totalInv - $totalInvDesc;
			$val['totalProds'] = $totalProds;
			$val['totalVtas'] = $totalVtas;
									
			$totalInvG += $totalInv;
			$totalProdsG += $totalProds;
			$totalVtasG += $totalVtas;
			
			$proveedores[] = $val;						
									
		}//foreach
							
		$res['proveedores'] = $proveedores;
		$res['totalInv'] = $totalInvG;
		$res['totalProds'] = $totalProdsG;
		$res['totalVtas'] = $totalVtasG;
		
		$totales['inv'] += $totalInvG;
		$totales['prods'] += $totalProdsG;
		$totales['vtas'] += $totalVtasG;
		
		$sucursales[] = $res;
		
	}//foreach

	$x .= "<table border=\"1\">";
	
	foreach($sucursales as $res){
			
		$x.="<thead> 
		<tr>
			<th style=\"background:#E0E5E7;text-align:center\" colspan=\"5\"><b>".utf8_encode(urldecode($res['nombre']))."</b></th>
		</tr>
		</thead><tbody>";
		
		$x .= "
			<tr>
			<td style=\"background:#E0E5E7;text-align:center\">No. Prov.</td>
			<td style=\"background:#E0E5E7;text-align:center\">Proveedor</td>
			<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">Inventario Actual</td>
			<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">Productos Vendidos</td>
			<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">Total de Venta</td>
			</tr>";
		
		foreach($res['proveedores'] as $val){
			
			$x .= "
				<tr>
				<td style='text-align:center;'>".$val['proveedorId']."</td>
				<td>".$val['nombre']."</td>
				<td style=\"text-align:center; padding-right:20px\">".$val['totalInv']."</td>
				<td style=\"text-align:center; padding-right:20px\">".$val['totalProds']."</td>
				<td style=\"text-align:right; padding-right:20px\">$".$val['totalVtas']."</td>
				</tr>";
							
		}
		
		$x .= "
		<tr>
		<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\"></td>
		<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">Total</td>
		<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">".$res['totalInv']."</td>
		<td style=\"background:#E0E5E7;text-align:center\" padding-right:20px\">".$res['totalProds']."</td>
		<td style=\"background:#E0E5E7;text-align:right\" padding-right:20px\">$".$res['totalVtas']."</td>
		</tr>";
		
	}
	
	$x .= "
		<tr>
		<td style=\"text-align:center\" padding-right:20px\"></td>
		<td style=\"text-align:center\" padding-right:20px\">TOTAL GENERAL</td>
		<td style=\"text-align:center\" padding-right:20px\">".$totales['inv']."</td>
		<td style=\"text-align:center\" padding-right:20px\">".$totales['prods']."</td>
		<td style=\"text-align:right\" padding-right:20px\">$".$totales['vtas']."</td>
		</tr>";
	
	$x .= "</tbody>";
	$x .= "</table>";
		
	header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
	header("Content-type:   application/x-msexcel; charset=utf-8");
	header("Content-Disposition: attachment; filename=".$name.".xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	
	echo $x;
		
?>