Tema d’esame: Consorzio Formaggi

CREATE DATABASE consorzio_formaggi;

USE consorzio_formaggi;

CREATE TABLE caseificio (
    id VARCHAR(4) PRIMARY KEY,           -- id è una stringa di 4 caratteri alfanumerici
    nome VARCHAR(255) NOT NULL,          -- nome del caseificio
    indirizzo VARCHAR(255) NOT NULL,     -- indirizzo
    citta VARCHAR(100) NOT NULL,         -- città
    provincia CHAR(2) NOT NULL,          -- provincia è una stringa di 2 caratteri
    latitudine DECIMAL(9,6),             -- latitudine con 6 decimali
    longitudine DECIMAL(9,6),            -- longitudine con 6 decimali
    titolare VARCHAR(255) NOT NULL       -- nome del titolare
);

INSERT INTO caseificio (id, nome, indirizzo, citta, provincia, latitudine, longitudine, titolare)
VALUES
('A123', 'Caseificio Bella Vita', 'Via delle Rose 12', 'Milano', 'MI', 45.4642, 9.1900, 'Giovanni Rossi'),
('B234', 'Caseificio La Fattoria', 'Via Roma 45', 'Torino', 'TO', 45.0703, 7.6869, 'Marco Bianchi'),
('C345', 'Caseificio del Sole', 'Via del Mare 8', 'Napoli', 'NA', 40.8522, 14.2681, 'Antonio Verdi'),
('D456', 'Caseificio Latteria 9000', 'Piazza Vittoria 22', 'Bologna', 'BO', 44.4949, 11.3426, 'Lucia Neri'),
('E567', 'Caseificio Fattoria di Montagna', 'Via delle Alpi 3', 'Trento', 'TN', 46.0660, 11.1216, 'Paolo Galli'),
('F678', 'Caseificio Casale dei Sogni', 'Via S. Maria 10', 'Firenze', 'FI', 43.7697, 11.2550, 'Francesca Colombo'),
('G789', 'Caseificio Il Girasole', 'Via del Bosco 18', 'Venezia', 'VE', 45.4408, 12.3155, 'Giuseppe Russo'),
('H890', 'Caseificio Verde Prato', 'Strada Nazionale 27', 'Verona', 'VR', 45.4384, 10.9916, 'Anna Ferri'),
('I901', 'Caseificio La Buona Tavola', 'Via Pio XII 56', 'Pescara', 'PE', 42.4611, 14.2130, 'Giorgio Di Stefano'),
('J012', 'Caseificio Il Cielo', 'Viale Europa 11', 'Perugia', 'PG', 43.1107, 12.3908, 'Claudia Ricci'),
('K123', 'Caseificio Sole di Toscana', 'Via Fiume 30', 'Livorno', 'LI', 43.5480, 10.3106, 'Francesco Rossi'),
('L234', 'Caseificio Monti Azzurri', 'Piazza Grande 14', 'Ancona', 'AN', 43.6158, 13.5185, 'Matteo Gatti'),
('M345', 'Caseificio La Stella', 'Strada della Valle 9', 'Roma', 'RM', 41.9028, 12.4964, 'Valeria Bianchi'),
('N456', 'Caseificio Fattoria Verde', 'Via Tiberina 4', 'Cagliari', 'CA', 39.2238, 9.1217, 'Luigi Amato'),
('O567', 'Caseificio La Riva', 'Via del Porto 7', 'Genova', 'GE', 44.4056, 8.9463, 'Silvia Moretti'),
('P678', 'Caseificio Il Sorriso', 'Corso Italia 19', 'Bari', 'BA', 41.1175, 16.8719, 'Antonio Fiore'),
('Q789', 'Caseificio La Nuova Era', 'Via del Vento 12', 'Reggio Calabria', 'RC', 38.1100, 15.6613, 'Francesco Perri'),
('R890', 'Caseificio Il Campanile', 'Via della Chiesa 3', 'Catania', 'CT', 37.5079, 15.0830, 'Salvatore Licata'),
('S901', 'Caseificio Il Castello', 'Strada Provinciale 8', 'Messina', 'ME', 38.1938, 15.5540, 'Carmela Marino'),
('T012', 'Caseificio La Via Lattea', 'Piazza del Duomo 20', 'Lecce', 'LE', 40.3520, 18.1752, 'Marco Gentile');

CREATE TABLE foto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    caseificio_id VARCHAR(4),
    path VARCHAR(255) NOT NULL,
    FOREIGN KEY (caseificio_id) REFERENCES caseificio(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE produzione (
    id INT AUTO_INCREMENT PRIMARY KEY,
    caseificio_id VARCHAR(4),
    data DATE,
    latte_prodotto DECIMAL(10,2),
    latte_lavorato DECIMAL(10,2),
    forme_prodotte INT,
    FOREIGN KEY (caseificio_id) REFERENCES caseificio(id)
);

CREATE TABLE vendite (
    id INT AUTO_INCREMENT PRIMARY KEY,
    caseificio_id VARCHAR(4),  
    data_vendita DATE,
    nome_acquirente VARCHAR(100),
    tipo_acquirente VARCHAR(50),
    stagionatura ENUM('12', '24', '30', '36'),
    scelta ENUM('prima', 'seconda'),
    forme_vendute INT,
    FOREIGN KEY (caseificio_id) REFERENCES caseificio(id)   
);

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password VARCHAR(255),
    ruolo ENUM('user', 'admin')
);

INSERT INTO produzione (caseificio_id, data, latte_prodotto, latte_lavorato, forme_prodotte) VALUES
('A123', '2025-04-01', 1200, 1000, 80),
('A123', '2025-04-02', 1300, 1100, 85),
('B234', '2025-04-01', 1000, 900, 70),
('B234', '2025-04-02', 1100, 950, 72),
('C345', '2025-04-01', 1400, 1200, 90),
('C345', '2025-04-02', 1350, 1150, 88),
('D456', '2025-04-01', 900, 800, 60),
('D456', '2025-04-02', 950, 850, 62),
('E567', '2025-04-01', 1250, 1050, 82),
('E567', '2025-04-02', 1280, 1080, 83),
('F678', '2025-04-01', 1000, 850, 65),
('F678', '2025-04-02', 1100, 880, 68),
('G789', '2025-04-01', 1150, 950, 75),
('G789', '2025-04-02', 1180, 970, 76),
('H890', '2025-04-01', 1050, 900, 70),
('H890', '2025-04-02', 1070, 920, 72),
('I901', '2025-04-01', 1220, 1020, 81),
('I901', '2025-04-02', 1240, 1040, 82),
('J012', '2025-04-01', 1300, 1100, 86),
('J012', '2025-04-02', 1320, 1120, 87),
('K123', '2025-04-01', 980, 820, 60),
('K123', '2025-04-02', 990, 840, 62),
('L234', '2025-04-01', 1230, 1030, 80),
('L234', '2025-04-02', 1250, 1050, 81),
('M345', '2025-04-01', 1400, 1200, 90),
('M345', '2025-04-02', 1420, 1220, 92),
('N456', '2025-04-01', 1100, 950, 70),
('N456', '2025-04-02', 1120, 960, 72),
('O567', '2025-04-01', 1350, 1150, 88),
('O567', '2025-04-02', 1370, 1170, 89),
('P678', '2025-04-01', 1200, 1000, 78),
('P678', '2025-04-02', 1220, 1020, 79),
('Q789', '2025-04-01', 1150, 950, 74),
('Q789', '2025-04-02', 1170, 970, 75),
('R890', '2025-04-01', 1260, 1060, 83),
('R890', '2025-04-02', 1280, 1080, 84),
('S901', '2025-04-01', 980, 850, 63),
('S901', '2025-04-02', 1000, 870, 65),
('T012', '2025-04-01', 1050, 900, 70),
('T012', '2025-04-02', 1080, 920, 72);

INSERT INTO vendite (caseificio_id, data_vendita, nome_acquirente, tipo_acquirente, stagionatura, scelta, forme_vendute) VALUES
('A123', '2025-04-02', 'Mario Neri', 'Privato', '12', 'prima', 50),
('A123', '2025-04-02', 'Supermercati ABC', 'Azienda', '24', 'seconda', 3),
('B234', '2025-04-03', 'Luca Bianchi', 'Privato', '30', 'prima', 60),
('B234', '2025-04-03', 'Caseificio Romano', 'Azienda', '36', 'seconda', 2),
('C345', '2025-04-04', 'Giulia Verdi', 'Privato', '12', 'prima', 70),
('C345', '2025-04-04', 'Distribuzione Alimentare Srl', 'Azienda', '24', 'seconda', 150),
('D456', '2025-04-05', 'Antonio Rizzo', 'Privato', '30', 'prima', 80),
('D456', '2025-04-05', 'Alimentari del Centro', 'Azienda', '36', 'seconda', 1),
('E567', '2025-04-06', 'Claudia Rossi', 'Privato', '12', 'prima', 90),
('E567', '2025-04-06', 'Coop Supermercati', 'Azienda', '24', 'seconda', 140),
('F678', '2025-04-07', 'Michele Galli', 'Privato', '30', 'prima', 110),
('F678', '2025-04-07', 'Alimentari Freschi', 'Azienda', '36', 'seconda', 160),
('G789', '2025-04-08', 'Valentina Colombo', 'Privato', '12', 'prima', 95),
('G789', '2025-04-08', 'Supermercato Fresco', 'Azienda', '24', 'seconda', 125),
('H890', '2025-04-09', 'Francesca Gatti', 'Privato', '30', 'prima', 85),
('H890', '2025-04-09', 'Distribuzione Latte Srl', 'Azienda', '36', 'seconda', 145),
('I901', '2025-04-10', 'Roberta Verdi', 'Privato', '12', 'prima', 75),
('I901', '2025-04-10', 'Supermercato Biologico', 'Azienda', '24', 'seconda', 135),
('J012', '2025-04-11', 'Giorgio Marino', 'Privato', '30', 'prima', 65),
('J012', '2025-04-11', 'Alimentari del Borgo', 'Azienda', '36', 'seconda', 115);

<?php
// Avviamo una sessione per mantenere informazioni come il nome utente o il ruolo.
session_start(); 

// Includiamo il file per la connessione al database
require_once "connessione.php"; 

if ($_SERVER["REQUEST_METHOD"] == "POST") { // Verifichiamo che il metodo della richiesta è POST e acquisiamo i dati di login inseriti dall'utente.
    $username = $_POST["username"]; 
    $password = $_POST["password"];   

    // Controlliamo se la connessione al database è valida, altrimenti terminiamo il processo.
    if (!$conn) { 
        die("Errore di connessione: " . mysqli_connect_error()); 
    }     

    // Controlliamo l'utente: query SQL per verificare se esiste un utente con il nome utente fornito.
    $sql = "SELECT id, username, password, ruolo FROM utenti WHERE username = ?"; 

    // Prepariamo e associamo il parametro per evitare SQL injection.
    $stmt = mysqli_prepare($conn, $sql); 
    $stmt->bind_param( "s", $username); 

    // Eseguiamo la query e ottieniamo il risultato.
    $stmt->execute(); 
    $result = $stmt->get_result();
    

    if ($row = $result->fetch_assoc()) { 
        if (password_verify($password, $row["password"])) { 
            // Verifichiamo che la password inserita dall'utente corrisponda alla password memorizzata
            $_SESSION["username"] = $row["username"]; 
            $_SESSION["ruolo"] = $row["ruolo"]; 
            if ($_SESSION["ruolo"]== "admin"){
                // Autenticazione riuscita: salviamo le informazioni nella sessione e reindirizziamo alla pagina di destinazione
            header("Location: home_admin.php"); 
            exit();
            }else{
                // Messaggio di errore se l'utente non ha i privilegi di accesso
                $error = "Non sei amministratore!";
            }             
        } else { 
            // Messaggio di errore se la password non è corretta
            $error = "Password errata.";              
        } 
    } else { 
        // Messaggio di errore se il nome utente non esiste nel database
        $error = "Utente non trovato.";          
    }
    // Chiude lo statement e la connessione per evitare problemi di risorse.
    $stmt->close();
    $conn->close();     
}
?>
<!DOCTYPE html> 
<html lang="it"> 
<head> 
    <meta charset="UTF-8"> 
    <title>Login</title>     
    <!-- link al foglio di stile CSS per il design -->
    <link rel="stylesheet" href="stile.css"> 
</head> 
<body> 
    <div class="container"> 
        <h2>Login</h2> 
        <!-- Visualizza un messaggio di errore, se presente, in rosso -->
        <?php if (!empty($error)) echo "<p style='color: red;'>$error</p>"; ?> 
            <form method="POST" action=""> 
                <!-- Campo per inserire il nome utente -->
                <label for="username">Username:</label> 
                <input type="text" id="username" name="username" required>           

                <br> 
                <!-- Campo per inserire la password -->
                <label for="password">Password:</label> 
                <input type="password" id="password" name="password" required>            

                <br> 
                <!-- Pulsante per inviare il form -->
                <button type="submit">Accedi</button> 
                
            </form> 
        <!-- Link per la pagina di registrazione -->
        <a href="registrazione.php" class="btn">Registrati</a> 
        
    </div> 
</body> 
</html>

<?php
// Importiamo il file per la connessione al database.
require 'connessione.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {//Verifichiamo che il metodo della richiesta è POST e acquisiamo i dati di registrazione inseriti dall'utente.
    $username = $_POST["username"];
    $password = password_hash($_POST["password"], PASSWORD_DEFAULT);// Hash della password per garantire una maggiore sicurezza. PASSWORD_DEFAULT utilizza l'algoritmo bcrypt
    
    // Impostiamo il ruolo predefinito come "admin".
    $ruolo = 'admin';     

    // Controlliamo se lo username è già in uso
    $checkUser = $conn->prepare("SELECT id FROM utenti WHERE username = ?");

    // Prepariamo la query per evitare SQL injection 
    $checkUser->bind_param("s", $username);

    //Eseguiamo la query e memorizziamo i risultati
    $checkUser->execute();
    $checkUser->store_result(); 

    if ($checkUser->num_rows > 0) {//Se lo username è già in uso
        // Notifichiamo all'utente che il nome utente è già stato scelto da qualcun altro.
        echo "Errore: Username già in uso.";        
    } else {
        // Inserimento nuovo utente
        // Prepariamo una query per inserire l'utente nel database e leghiamo i parametri: username, password e ruolo.
        $stmt = $conn->prepare("INSERT INTO utenti (username, password, ruolo) VALUES (?, ?, ?)");
        $stmt->bind_param("sss", $username, $password, $ruolo);        

        if ($stmt->execute()) {// Se la query è stata eseguita con successo, confermiamolo all'utente e forniamo un link al login.
            echo "Registrazione completata con successo! <a href='login.php'>Vai al login</a>";            
        } else {// Mostriamo un messaggio di errore specifico
            echo "Errore: " . $stmt->error;            
        }
        $stmt->close();// Chiudiamo lo statement per liberare risorse.
    }
    // Chiudiamo sia lo statement di verifica esistenza username che la connessione al database.
    $checkUser->close();
    $conn->close();    
}
?>
<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="UTF-8">
    <title>Registrazione</title>
    <!-- colleghiamo il foglio di stile per il design -->
    <link rel="stylesheet" href="stile.css">    
</head>
<body>
    <div class="container">
        <h2>Registrazione</h2>
        <form method="POST">
            <!-- Campo obbligatorio per inserire lo username -->
            <label>Username:</label>
            <input type="text" name="username" required>        

            <br>
            <!-- Campo obbligatorio per inserire la password -->
            <label>Password:</label>
            <input type="password" name="password" required>        

            <br>
            <!-- Pulsante per inviare il modulo -->
            <button type="submit">Registrati</button>       
        </form>
    </div>
</body>
</html>

<?php
session_start();

//----------------------Sezione A1------------------------//
// Controlliamo se l'utente è loggato e ha il ruolo di admin
if (!isset($_SESSION["username"]) || $_SESSION["ruolo"] != 'admin') {
    header("Location: login.php");
    exit();
}

// Connessione al database
$conn = new mysqli("localhost", "root", "", "consorzio_formaggi");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//------------------------Sezione A2-------------------//

// Inizializzazione variabili
$queryResult = '';
$queryScelta = $_POST["query"] ?? '';
$dataInizio = $_POST["data_inizio"] ?? '';
$dataFine = $_POST["data_fine"] ?? '';
$anno = $_POST["anno"] ?? ''; 
$step = '';

//-------------Sezione A3-------------//
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    if ($queryScelta && !$dataInizio && !$dataFine && !$anno) {
        if (in_array($queryScelta, ['query1', 'query4'])) {
            $step = 'date_form';
        } elseif (in_array($queryScelta, ['query2', 'query3'])) {
            $step = 'year_form';
        } else {
            $step = 'execute_query';
        }
    } elseif ($queryScelta && $dataInizio && $dataFine) {
        $step = 'execute_query';
    } elseif ($queryScelta && $anno) {
        $step = 'execute_query';
    } 
}

//--------------A4----------------//
if ($step === 'execute_query') {
    switch ($queryScelta) {
        case 'query1':
            $querySQL = "SELECT c.nome, SUM(p.forme_prodotte) AS forme_prodotte
                         FROM caseificio c
                         JOIN produzione p ON c.id = p.caseificio_id
                         WHERE p.data BETWEEN '$dataInizio' AND '$dataFine'
                         GROUP BY c.id";
            break;
        case 'query2':
            $querySQL = "SELECT c.provincia, AVG(p.latte_lavorato) AS media_latte
                         FROM caseificio c
                         JOIN produzione p ON c.id = p.caseificio_id
                         WHERE YEAR(p.data) = '$anno'
                         GROUP BY c.provincia";
            break;
        case 'query3':
            $querySQL = "SELECT c.*, SUM(v.forme_vendute) AS totale_forme
                        FROM caseificio c
                        JOIN vendite v ON c.id = v.caseificio_id
                        WHERE v.scelta = 'prima' AND YEAR(v.data_vendita) = '$anno'
                        GROUP BY c.id
                        ORDER BY totale_forme DESC
                        LIMIT 1;
                        ";
            break;
        case 'query4':
            $querySQL = "SELECT c.*, SUM(v.forme_vendute) AS totale_forme_seconda_scelta
                        FROM caseificio c
                        JOIN vendite v ON c.id = v.caseificio_id
                        WHERE v.scelta = 'seconda' AND v.data_vendita BETWEEN '$dataInizio' AND '$dataFine'
                        GROUP BY c.id
                        HAVING totale_forme_seconda_scelta < 10";
            break;
    }

    //---------------A5----------------//
    if (isset($querySQL)) {
        $result = $conn->query($querySQL);
        if ($result->num_rows > 0) {
            $queryResult = '<table border="1"><tr>';
            while ($field = $result->fetch_field()) {
                $queryResult .= "<th>{$field->name}</th>";
            }
            $queryResult .= '</tr>';
            while ($row = $result->fetch_assoc()) {
                $queryResult .= '<tr>';
                foreach ($row as $column) {
                    $queryResult .= "<td>{$column}</td>";
                }
                $queryResult .= '</tr>';
            }
            $queryResult .= '</table>';
        } else {
            $queryResult = 'Nessun risultato trovato.';
        }
    }
}
?>

<!---------------------A6------------------>
<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Consorzio Formaggi</title>
    <link rel="stylesheet" href="stile2.css">
</head>
<body>
    <div class="sidebar">
        <form method="POST" action="home_admin.php">
            <button type="submit" name="query" value="query1">Query 1</button>
            <button type="submit" name="query" value="query2">Query 2</button>
            <button type="submit" name="query" value="query3">Query 3</button>
            <button type="submit" name="query" value="query4">Query 4</button>
        </form>

<!---------------A7----------------->      
        <?php if ($step === 'date_form'): ?>
            <h3>Seleziona un intervallo di date:</h3>
            <form method="POST" action="home_admin.php">
                <input type="hidden" name="query" value="<?php echo $queryScelta; ?>">
                <label for="data_inizio">Data Inizio:</label>
                <input type="date" id="data_inizio" name="data_inizio" required>
                <label for="data_fine">Data Fine:</label>
                <input type="date" id="data_fine" name="data_fine" required>
                <button type="submit">Esegui</button>
            </form>
        <?php elseif ($step === 'year_form'): ?>
            <h3>Seleziona l'anno:</h3>
            <form method="POST" action="home_admin.php">
                <input type="hidden" name="query" value="<?php echo $queryScelta; ?>">
                <label for="anno">Anno:</label>
                <input type="number" id="anno" name="anno" required>
                <button type="submit">Esegui</button>
            </form>
        <?php endif; ?>
    </div>

<!---------------A8-------------->
    <div class="content">
        <a href="logout.php" class="btn">LOGOUT</a>
        <h1>Benvenuto amministratore, <?php echo htmlspecialchars($_SESSION["username"]); ?> nella home page del Consorzio Formaggi!</h1>
        <h2>Seleziona una query per visualizzare i risultati</h2>
        <h3>Query 1: Visualizzare il numero di forme prodotte da ciascun caseificio tra due date fornite</h3>
        <h3>Query 2: Visualizzare la media del latte lavorato giornalmente nell’anno corrente dai caseifici provincia per provincia</h3>
        <h3>Query 3: Visualizzare i dati del caseificio che ha venduto il maggior numero di forme di prima scelta in un anno impostato dall’utente</h3>
        <h3>Query 4: Visualizzare l’elenco dei caseifici che, in un certo periodo individuato da due date fornite dall’utente, hanno venduto meno di 10 forme di seconda scelta</h3>

        <!-- Visualizziamo il risultato della query -->
        <div>
            <?php echo $queryResult; ?>
        </div>
    </div>
</body>
</html>

<?php
$conn->close();
?>