<?php
	session_start();
	/* Connect To Database*/
	require_once ("../config/db.php");
	require_once ("../config/conexion.php");
	require_once ("../libraries/inventory.php");//Contiene funcion que controla stock en el inventario
	//Inicia Control de Permisos
	include("../config/permisos.php");
	$user_id = $_SESSION['user_id'];
	 

$action = (isset($_REQUEST['action'])&& $_REQUEST['action'] !=NULL)?$_REQUEST['action']:'';
if($action == 'ajax'){
	$daterange = mysqli_real_escape_string($con,(strip_tags($_REQUEST['range'], ENT_QUOTES)));
	$cat=intval($_REQUEST['cat']);
	//var_dump($cat);
	$tables="sale_product as ventas";
	$campos="products.product_name, categories.name, ventas.product_id, SUM(ventas.qty) AS TotalVentas, sales.sale_date ";
	
	$join=" INNER JOIN products on products.product_id = ventas.product_id";
	$join.=" INNER JOIN categories on products.category_id = categories.id";
	$join.=" INNER JOIN sales on ventas.sale_id = sales.sale_id";
	$where="products.status=1";
	$order=" GROUP BY ventas.product_id";
	$order.=" ORDER BY SUM(ventas.qty) DESC";
	
	if ($cat>0){
	 	$where.=" and products.category_id ='".$cat."'";
	}

	if (!empty($daterange)){
		list ($f_inicio,$f_final)=explode(" - ",$daterange);//Extrae la fecha inicial y la fecha final en formato espa?ol
		list ($dia_inicio,$mes_inicio,$anio_inicio)=explode("/",$f_inicio);//Extrae fecha inicial 
		$fecha_inicial="$anio_inicio-$mes_inicio-$dia_inicio 00:00:00";//Fecha inicial formato ingles
		list($dia_fin,$mes_fin,$anio_fin)=explode("/",$f_final);//Extrae la fecha final
		$fecha_final="$anio_fin-$mes_fin-$dia_fin 23:59:59";
		
		$where .= " and sales.sale_date between '$fecha_inicial' and '$fecha_final' ";
	}
	
	
	include 'pagination.php'; //include pagination file
	//pagination variables
	$page = (isset($_REQUEST['page']) && !empty($_REQUEST['page']))?$_REQUEST['page']:1;
	$per_page = 10000; //how much records you want to show
	$adjacents  = 4; //gap between pages after number of adjacents
	$offset = ($page - 1) * $per_page;
	//Count the total number of row in your table*/
	$count_query   = mysqli_query($con,"SELECT count(*) AS numrows FROM $tables $join");
	if ($row= mysqli_fetch_array($count_query)){$numrows = $row['numrows'];}
	else {echo mysqli_error($con);}
	$total_pages = ceil($numrows/$per_page);
	$reload = './sales_report.php';
	//main query to fetch the data
	$query = mysqli_query($con,"SELECT $campos FROM  $tables   $join  where $where  $order LIMIT  $offset,$per_page");
	//loop through fetched data
	

	
	if ($numrows>0){

	?>
	
	<div class="row">
		<div class="col-md-12">
			<div class="box">
				<div class="box-header with-border">
				<h3 class="box-title">Productos más vendidos.</h3>
				</div><!-- /.box-header -->
				<div class="box-body">
				<div class="table-responsive">
					<table class="table table-condensed table-hover table-striped ">
						<tr>						
							<th>Producto</th>
							<th >Categoría </th>
							<th>Ventas </th>							
						</tr>
						<?php 
						$finales=0;
						while($row = mysqli_fetch_array($query)){						
							$nombreProducto=$row['product_name'];
							$catProducto=$row['name'];							
							$totalVentas=$row['TotalVentas'];
												
							$finales++;
						?>	
						<tr>						
							<td><?php echo $nombreProducto;?></td>							
							<td><?php echo $catProducto;?></td>
							<td><?php echo $totalVentas;?></td>							
						</tr>
						<?php }?>		
					</table>
				</div>	
				</div><!-- /.box-body -->
				<div class="box-footer clearfix">
				
				<?php 
				$inicios=$offset+1;
				$finales+=$inicios -1;
				echo "Mostrando $inicios al $finales de $numrows registros";
				echo paginate($reload, $page, $total_pages, $adjacents);?>
				</div>
			</div><!-- /.box -->
		</div><!-- /.col -->
	</div><!-- /.row -->	
	<?php	
	}	
}
?>          
		  
