98 lines
2.6 KiB
Python
98 lines
2.6 KiB
Python
import sqlite3
|
|
|
|
DB_NAME = "ats_doku.db"
|
|
|
|
def initialize_db():
|
|
connection = sqlite3.connect(DB_NAME)
|
|
cursor = connection.cursor()
|
|
|
|
# --- Tabelle 1: Die Liste für dein Dropdown ---
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS ats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE,
|
|
code TEXT UNIQUE
|
|
)
|
|
''')
|
|
|
|
# --- Tabelle 2: Das Logbuch (jetzt mit Ort) ---
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS einsaetze (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
location TEXT,
|
|
name TEXT,
|
|
date TEXT,
|
|
time INTEGER,
|
|
etype TEXT,
|
|
device TEXT
|
|
)
|
|
''')
|
|
|
|
# Optional: Ein paar Test-ats anlegen, falls die Tabelle leer ist
|
|
# (Damit du direkt was im Dropdown siehst)
|
|
cursor.execute("SELECT count(*) FROM ats")
|
|
if cursor.fetchone()[0] == 0:
|
|
ats_liste = [("Tim Grubmüller",), ("Phil Langer",), ("Max Hämmerle",)]
|
|
cursor.executemany("INSERT INTO ats (name) VALUES (?)", ats_liste)
|
|
|
|
connection.commit()
|
|
connection.close()
|
|
|
|
|
|
def add_data_to_einsaetze(name, location, date, time, etype, device):
|
|
connection = sqlite3.connect(DB_NAME)
|
|
cursor = connection.cursor()
|
|
|
|
sql_query = "INSERT INTO einsaetze (name, location, date, time, etype, device) VALUES (?, ?, ?, ?, ?, ?)"
|
|
data = (name, location, date, time, etype, device)
|
|
|
|
cursor.execute(sql_query, data)
|
|
print("DB geschrieben")
|
|
|
|
connection.commit()
|
|
connection.close()
|
|
|
|
def get_ats_names():
|
|
connection = sqlite3.connect(DB_NAME)
|
|
cursor = connection.cursor()
|
|
|
|
# nur die Spalte 'name' aus der Tabelle 'ats'
|
|
cursor.execute("SELECT name FROM ats ORDER BY name ASC")
|
|
names = cursor.fetchall()
|
|
connection.close()
|
|
return [row[0] for row in names]
|
|
|
|
def get_einsaetze():
|
|
connection = sqlite3.connect(DB_NAME)
|
|
|
|
#Get Data an Row Names
|
|
connection.row_factory = sqlite3.Row
|
|
cursor = connection.cursor()
|
|
cursor.execute("SELECT * FROM einsaetze ORDER BY id DESC")
|
|
data = cursor.fetchall()
|
|
connection.close()
|
|
return [dict(row) for row in data]
|
|
|
|
|
|
# ATS Träger aus DB Löschen ODER einfügen.abs
|
|
def delete_ats_traeger(id):
|
|
connection = sqlite3.connect(DB_NAME)
|
|
|
|
cursor = connection.cursor()
|
|
|
|
def get_ats_with_id():
|
|
#Verbindung Aufbauen
|
|
connection = sqlite3.connect(DB_NAME)
|
|
connection.row_factory = sqlite3.Row
|
|
|
|
#Cursor in die DB Schicken und Daten holen.
|
|
cursor = connection.cursor()
|
|
cursor.execute("SELECT id, name FROM ats ORDER BY name ASC")
|
|
|
|
#
|
|
data = cursor.fetchall()
|
|
return [dict(row) for row in data]
|
|
|
|
|
|
|
|
|