<?php
// Conexi�n a la base de datos
$servername = "localhost";
$username = "u628200248_terrapro";
$password = "Terrapro2025";
$dbname = "u628200248_terrapro";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Conexi�n fallida: " . $conn->connect_error);
}

// Consulta SQL para sumar los valores agrupados por item_id
//$sql = "SELECT item_id, item_name ,SUM(total_sell) AS total_valor, SUM(item_quantity) AS total_cantidad FROM purchase_item GROUP BY item_id";
//$result = $conn->query($sql);
$sql = "SELECT pi.item_id, pi.item_name, SUM(pi.total_sell) AS total_valor, SUM(pi.item_quantity) AS total_cantidad,  pts.quantity_in_stock FROM purchase_item pi
        LEFT JOIN product_to_store pts ON pi.item_id = pts.product_id
        GROUP BY pi.item_id";
$result = $conn->query($sql);

?>

<!DOCTYPE html>
<html>
<head>
    <title>Suma de Valores por Item</title>
</head>
<body>

<h2>Tabla de Suma de Valores por Item</h2>

<table border="1">
    <tr>
        <th>Item ID</th>
		<th>productos</th>
        <th>Compras</th>
        <th>Ventas</th>
		<th>saldo</th>
		<th>stock</th>
		<th>Error</th>
    </tr>

    <?php
    // Mostrar los resultados en la tabla HTML
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . $row["item_id"] . "</td>";
			echo "<td>" . $row["item_name"] . "</td>";
			echo "<td>" . $row["total_cantidad"] . "</td>";
            echo "<td>" . $row["total_valor"] . "</td>";
            
						
			$diferenciaCV = $row["total_cantidad"] - $row["total_valor"];
            echo "<td>" . $diferenciaCV . "</td>";
			echo "<td>" . $row["quantity_in_stock"] . "</td>";
			$stock = $diferenciaCV - $row["quantity_in_stock"];
            echo "<td>" . $stock . "</td>";
            echo "</tr>";
        }
    } else {
        echo "<tr><td colspan='3'>No se encontraron resultados.</td></tr>";
    }
    ?>

</table>

</body>
</html>

<?php
// Cerrar la conexi�n a la base de datos
$conn->close();
?>
