import sqlite3 import pandas as pd from datetime import datetime from contextlib import contextmanager class DBManager: """Manages SQLite database for BTA Furnace monitoring system""" def __init__(self, db_path='bta_furnace.db'): self.db_path = db_path self.init_schema() @contextmanager def get_connection(self): """Context manager untuk database connection""" conn = sqlite3.connect(self.db_path) conn.row_factory = sqlite3.Row # Enable column access by name try: yield conn finally: conn.close() def init_schema(self): """Initialize database schema jika belum ada""" with self.get_connection() as conn: cursor = conn.cursor() # Tabel data measurement cursor.execute(''' CREATE TABLE IF NOT EXISTS data_measurement ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, cone_depan REAL, bodi_tengah REAL, cone_belakang REAL, suhu_avg REAL, ketebalan_actual REAL, ketebalan_prediksi REAL, status TEXT, is_flagged INTEGER DEFAULT 0 ) ''') # Tabel maintenance log cursor.execute(''' CREATE TABLE IF NOT EXISTS maintenance_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, maintenance_date DATETIME DEFAULT CURRENT_TIMESTAMP, previous_thickness REAL, reset_to_thickness REAL, notes TEXT ) ''') # Tabel app config cursor.execute(''' CREATE TABLE IF NOT EXISTS app_config ( key TEXT PRIMARY KEY, value TEXT ) ''') # Insert default config jika belum ada cursor.execute("SELECT COUNT(*) FROM app_config") if cursor.fetchone()[0] == 0: defaults = [ ('min_safe_thickness', '115'), ('threshold_temp', '400'), ('slaging_interval', '300'), ('reset_thickness', '230'), ('laju_penipisan', '0.35') ] cursor.executemany("INSERT INTO app_config (key, value) VALUES (?, ?)", defaults) conn.commit() def insert_measurement(self, cone_depan, bodi_tengah, cone_belakang, ketebalan_actual, ketebalan_prediksi, status='AMAN', is_flagged=False): """Insert measurement data ke database""" suhu_avg = (cone_depan + bodi_tengah + cone_belakang) / 3 with self.get_connection() as conn: cursor = conn.cursor() cursor.execute(''' INSERT INTO data_measurement (cone_depan, bodi_tengah, cone_belakang, suhu_avg, ketebalan_actual, ketebalan_prediksi, status, is_flagged) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', (cone_depan, bodi_tengah, cone_belakang, suhu_avg, ketebalan_actual, ketebalan_prediksi, status, int(is_flagged))) conn.commit() return cursor.lastrowid def get_latest_measurement(self): """Get latest measurement""" with self.get_connection() as conn: cursor = conn.cursor() cursor.execute(''' SELECT * FROM data_measurement ORDER BY timestamp DESC LIMIT 1 ''') row = cursor.fetchone() if row: result = dict(row) # Ensure numeric columns are float for col in ['cone_depan', 'bodi_tengah', 'cone_belakang', 'suhu_avg', 'ketebalan_actual', 'ketebalan_prediksi']: if col in result and result[col] is not None: result[col] = float(result[col]) return result return None def get_historis(self, limit=100, flagged_only=False): """Get historical measurements""" with self.get_connection() as conn: cursor = conn.cursor() if flagged_only: query = ''' SELECT * FROM data_measurement WHERE is_flagged = 1 ORDER BY timestamp DESC LIMIT ? ''' else: query = ''' SELECT * FROM data_measurement ORDER BY timestamp DESC LIMIT ? ''' cursor.execute(query, (limit,)) rows = cursor.fetchall() results = [] for row in rows: result = dict(row) # Ensure numeric columns are float for col in ['cone_depan', 'bodi_tengah', 'cone_belakang', 'suhu_avg', 'ketebalan_actual', 'ketebalan_prediksi']: if col in result and result[col] is not None: result[col] = float(result[col]) results.append(result) return results def get_all_data_as_dataframe(self): """Get all measurement data sebagai Pandas DataFrame""" with self.get_connection() as conn: df = pd.read_sql_query( "SELECT * FROM data_measurement ORDER BY timestamp ASC", conn ) return df def log_maintenance(self, previous_thickness, reset_to_thickness=230, notes=''): """Log maintenance event""" with self.get_connection() as conn: cursor = conn.cursor() cursor.execute(''' INSERT INTO maintenance_log (previous_thickness, reset_to_thickness, notes) VALUES (?, ?, ?) ''', (previous_thickness, reset_to_thickness, notes)) conn.commit() return cursor.lastrowid def get_maintenance_history(self, limit=50): """Get maintenance history""" with self.get_connection() as conn: cursor = conn.cursor() cursor.execute(''' SELECT * FROM maintenance_log ORDER BY maintenance_date DESC LIMIT ? ''', (limit,)) rows = cursor.fetchall() return [dict(row) for row in rows] def get_config(self, key): """Get config value""" with self.get_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT value FROM app_config WHERE key = ?", (key,)) row = cursor.fetchone() value = row[0] if row else None # Ensure string type if value is not None and isinstance(value, bytes): value = value.decode('utf-8') return value def set_config(self, key, value): """Set config value""" with self.get_connection() as conn: cursor = conn.cursor() cursor.execute( "INSERT OR REPLACE INTO app_config (key, value) VALUES (?, ?)", (key, str(value)) ) conn.commit() def get_stats(self): """Get statistics untuk dashboard summary""" with self.get_connection() as conn: cursor = conn.cursor() # Total measurements cursor.execute("SELECT COUNT(*) FROM data_measurement") total_measurements = cursor.fetchone()[0] # Total flagged (slaging) cursor.execute("SELECT COUNT(*) FROM data_measurement WHERE is_flagged = 1") total_flagged = cursor.fetchone()[0] # Latest measurement cursor.execute(''' SELECT ketebalan_prediksi, timestamp FROM data_measurement ORDER BY timestamp DESC LIMIT 1 ''') latest = cursor.fetchone() current_thickness = float(latest[0]) if latest else 230.0 # Total maintenance events cursor.execute("SELECT COUNT(*) FROM maintenance_log") total_maintenance = cursor.fetchone()[0] return { 'total_measurements': total_measurements, 'total_flagged': total_flagged, 'current_thickness': current_thickness, 'total_maintenance': total_maintenance }