Monitoraggio HACCP con ESP32, Raspberry Pi e il Timer RTC-DS3231

all:
	pio -f -c vim run

upload:
	pio -f -c vim run --target upload

clean:
	pio -f -c vim run --target clean

program:
	pio -f -c vim run --target program

uploadfs:
	pio -f -c vim run --target uploadfs

update:
	pio -f -c vim update

; PlatformIO Project Configuration File
;
;   Build options: build flags, source filter
;   Upload options: custom upload port, speed and extra flags
;   Library options: dependencies, extra library storages
;   Advanced options: extra scripting
;
; Please visit documentation for the other options and examples
; https://docs.platformio.org/page/projectconf.html

[env:esp32dev]
platform = espressif32
board = esp32dev
framework = arduino
monitor_speed = 115200

lib_deps =
    adafruit/RTClib @ ^2.1.3
    adafruit/Adafruit BusIO @ ^1.14.5
    bblanchon/ArduinoJson @ ^6.21.3
    knolleary/PubSubClient @ ^2.8
    SPI
    Wire    

#include <WiFi.h>
#include <HTTPClient.h>
#include <Wire.h>
#include "RTClib.h"

const char* ssid = "SSID";
const char* password = "PASSWORD_WIFI";
const char* serverUrl = "http://192.168.1.153:5040/ingest";

RTC_DS3231 rtc;
unsigned long lastMillis = 0;
const long interval = 15000; 

void setup() {
    Serial.begin(115200);
    Wire.begin(21, 22);
    if (!rtc.begin()) {
        Serial.println("[CRITICAL] RTC_NOT_FOUND");
        while (1);
    }
    WiFi.begin(ssid, password);
    Serial.println("[SYSTEM] START_MONITORING");
}

void loop() {
    if (millis() - lastMillis >= interval) {
        lastMillis = millis();

        // 1. LETTURA DATI DAL TIMER E SENSORI SIMULATI
        DateTime now = rtc.now();
        float t1 = -18.0 + (random(-100, 100) / 100.0);
        float t2 = 14.0 + (random(-100, 100) / 100.0);
        float hum = 80.0 + (random(-50, 50) / 10.0);
        float pres = 1013.25 + (random(-100, 100) / 100.0);

        // 2. LOG SERIALE BRUTALE (Sempre attivo)
        char timestamp[20];
        sprintf(timestamp, "%04d-%02d-%02d %02d:%02d:%02d", now.year(), now.month(), now.day(), now.hour(), now.minute(), now.second());
        
        Serial.print("> DATA_LOG: ");
        Serial.print(timestamp);
        Serial.print(" | T1: "); Serial.print(t1);
        Serial.print(" | T2: "); Serial.print(t2);
        Serial.print(" | HUM: "); Serial.print(hum);
        Serial.print(" | PRES: "); Serial.println(pres);

        // 3. INVIO A FLASK (Se WiFi disponibile)
        if (WiFi.status() == WL_CONNECTED) {
            HTTPClient http;
            http.begin(serverUrl);
            http.addHeader("Content-Type", "application/json");

            String json = "{\"timestamp\":\"" + String(timestamp) + "\",";
            json += "\"t1\":" + String(t1, 2) + ",";
            json += "\"t2\":" + String(t2, 2) + ",";
            json += "\"hum\":" + String(hum, 2) + ",";
            json += "\"pres\":" + String(pres, 2) + "}";

            int code = http.POST(json);
            Serial.printf("[NETWORK] SEND_STATUS: HTTP_%d\n", code);
            http.end();
        } else {
            Serial.println("[NETWORK] OFFLINE - DATA_NOT_SENT");
        }
        Serial.println("------------------------------------------------------------------");
    }
}

CREATE TABLE IF NOT EXISTS haccp_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
temp_cella_1 REAL,
temp_cella_2 REAL,
umidita_relativa REAL,
pressione_pa REAL
);

import sqlite3
import csv
import io
from flask import Flask, request, jsonify, Response, render_template_string
from datetime import datetime, timedelta

app = Flask(__name__)
DB_FILE = "haccp_monitor.db"

def query_db(query, args=(), one=False):
    with sqlite3.connect(DB_FILE) as conn:
        conn.row_factory = sqlite3.Row
        cur = conn.execute(query, args)
        rv = cur.fetchall()
        return (rv[0] if rv else None) if one else rv

@app.route('/ingest', methods=['POST'])
def ingest():
    data = request.json
    try:
        ts = data.get('timestamp')
        with sqlite3.connect(DB_FILE) as conn:
            if ts:
                conn.execute(
                    "INSERT INTO haccp_log (timestamp, temp_cella_1, temp_cella_2, umidita_relativa, pressione_pa) VALUES (?, ?, ?, ?, ?)",
                    (ts, data['t1'], data['t2'], data['hum'], data['pres'])
                )
            else:
                conn.execute(
                    "INSERT INTO haccp_log (temp_cella_1, temp_cella_2, umidita_relativa, pressione_pa) VALUES (?, ?, ?, ?)",
                    (data['t1'], data['t2'], data['hum'], data['pres'])
                )
        return jsonify({"status": "OK"}), 201
    except Exception as e:
        return jsonify({"status": "ERROR", "msg": str(e)}), 500

@app.route('/data')
def get_data():
    # Endpoint tecnico per il refresh AJAX del grafico
    data = query_db("SELECT * FROM haccp_log ORDER BY timestamp DESC LIMIT 100")
    return jsonify([dict(row) for row in data])

@app.route('/export/<period>')
def export_csv(period):
    days = {"24h": 1, "week": 7, "month": 30}.get(period, 1)
    since = (datetime.now() - timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')
    rows = query_db("SELECT * FROM haccp_log WHERE timestamp > ? ORDER BY timestamp ASC", (since,))
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(['ID', 'TIMESTAMP', 'TEMP_CELLA_1', 'TEMP_CELLA_2', 'UMIDITA_REL', 'PRESSIONE_PA'])
    for row in rows:
        writer.writerow([row['id'], row['timestamp'], row['temp_cella_1'], row['temp_cella_2'], row['umidita_relativa'], row['pressione_pa']])
    return Response(output.getvalue(), mimetype="text/csv", headers={"Content-Disposition": f"attachment; filename=haccp_report_{period}.csv"})

@app.route('/')
def index():
    data = query_db("SELECT * FROM haccp_log ORDER BY timestamp DESC LIMIT 100")
    data_list = [dict(row) for row in data]
    return render_template_string(HTML_UI, data=data_list)

HTML_UI = """
<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="UTF-8">
    <title>HACCP_LIVE_MONITOR</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        body { background: #000; color: #0f0; font-family: 'Courier New', monospace; padding: 20px; }
        .container { max-width: 1200px; margin: 0 auto; }
        .header { border-bottom: 1px solid #0f0; padding-bottom: 10px; margin-bottom: 20px; }
        .btns { margin-bottom: 30px; display: flex; gap: 10px; }
        button { background: transparent; border: 1px solid #0f0; color: #0f0; padding: 10px 20px; cursor: pointer; font-weight: bold;}
        button:hover { background: #0f0; color: #000; }
        .chart-container { background: #050505; border: 1px solid #222; padding: 20px; height: 500px; position: relative; }
        .status-led { color: #0f0; font-size: 0.8em; float: right; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <span class="status-led" id="sync-status">SYNC: OK</span>
            <h2>[ HACCP_MONITORING_SYSTEM_v1.4 ]</h2>
            <p>SISTEMA: TEMPERATURE_CORE | REFRESH: AUTO (15s)</p>
        </div>
        <div class="btns">
            <button onclick="location.href='/export/24h'">LOG_24H</button>
            <button onclick="location.href='/export/week'">LOG_SETTIMANA</button>
            <button onclick="location.href='/export/month'">REPORT_MENSILE</button>
        </div>
        <div class="chart-container">
            <canvas id="haccpChart"></canvas>
        </div>
    </div>
    <script>
        let haccpChart;
        const ctx = document.getElementById('haccpChart').getContext('2d');

        function initChart(initialData) {
            const labels = initialData.map(r => r.timestamp).reverse();
            haccpChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: labels,
                    datasets: [
                        { 
                            label: 'Cella 1 (°C)', 
                            data: initialData.map(r => r.temp_cella_1).reverse(), 
                            borderColor: '#f00', 
                            borderWidth: 2,
                            tension: 0.3,
                            pointRadius: 2
                        },
                        { 
                            label: 'Cella 2 (°C)', 
                            data: initialData.map(r => r.temp_cella_2).reverse(), 
                            borderColor: '#ff0', 
                            borderWidth: 2,
                            tension: 0.3,
                            pointRadius: 2
                        }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    animation: false,
                    scales: {
                        x: { ticks: { color: '#0f0', maxRotation: 45 }, grid: { color: '#111' } },
                        y: { ticks: { color: '#0f0' }, grid: { color: '#222' } }
                    },
                    plugins: { legend: { labels: { color: '#0f0' } } }
                }
            });
        }

        async function refreshData() {
            try {
                const response = await fetch('/data');
                const newData = await response.json();
                const labels = newData.map(r => r.timestamp).reverse();
                
                haccpChart.data.labels = labels;
                haccpChart.data.datasets[0].data = newData.map(r => r.temp_cella_1).reverse();
                haccpChart.data.datasets[1].data = newData.map(r => r.temp_cella_2).reverse();
                haccpChart.update('none');
                
                document.getElementById('sync-status').innerText = "LAST_SYNC: " + new Date().toLocaleTimeString();
            } catch (e) {
                document.getElementById('sync-status').innerText = "SYNC: ERROR";
            }
        }

        // Avvio
        const initialData = {{ data|tojson }};
        initChart(initialData);
        
        // Refresh ogni 15 secondi (allineato al timer 0x68)
        setInterval(refreshData, 15000);
    </script>
</body>
</html>
"""

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5040, debug=False)