vmario/kugelstossmeeting-ticketing
vmario/kugelstossmeeting-ticketing
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
kugelstossmeeting-ticketing/sqlite.py
Mario Voigt eb54a98fc0 Update sqlite.py
2025-01-06 20:27:37 +01:00

379 lines
16 KiB
Python
Executable File
Raw Permalink Blame History

#!/opt/kugelstossmeeting-ticketing/venv/bin/python3
'''
TODOS
- UI aufhübschen
- Suchfunktion einbauen
- usbscanner.py journal log automatisch öffnen per Button (soll separate Shell öffnen)
'''
import sqlite3
from sqlite3 import Error
from contextlib import closing
import os
import time
from datetime import datetime
import pytz
import tkinter as tk
#from tkinter import *
from tkinter import ttk, messagebox
import tkinter.font as font
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_file = os.path.join(BASE_DIR, "kugelstossmeeting-prod.db")
if os.path.isfile(db_file) is False:
error = "Error: Datenbankdatei '{}' konnte nicht gefunden werden!".format(db_file)
print(error)
messagebox.showerror("Error", error)
exit(1)
if os.path.getsize(db_file) == 0:
error = "Error: Datenbankdatei '{}' ist 0 Byte groß!".format(db_file)
print(error)
messagebox.showerror("Error", error)
exit(1)
title = "Kugelstoßmeeting Rochlitz"
timeoffset = 20 #seconds
autoscroll = True
lastSelectedTicket = None #das letzte manuell ausgewählte. Müssen wir merken, sonst wird es alle 2 Sekunden deselektiert
window = tk.Tk()
#window.geometry('800x600+0+0')
window.attributes('-zoomed', True)
window.title(title)
window.iconphoto(False, tk.PhotoImage(file=os.path.join(BASE_DIR, "icon.png")))
currentScan = None
def treeview_sort_column(tv, col, reverse):
l = [(tv.set(k, col), k) for k in tv.get_children('')]
l.sort(key=lambda t: int(t[0]), reverse=reverse)
for index, (val, k) in enumerate(l):
tv.move(k, '', index)
tv.heading(col, command=lambda: treeview_sort_column(tv, col, not reverse))
def insertManualSelectionAsScan(mb):
sql = "INSERT INTO scans(unixtimestamp_created,barcode,validated,hint,unixtimestamp_checked,skipped) VALUES(?,?,?,?,?,?)"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
ts = time.time_ns()
res = cur.execute(sql,(int(ts),mb,0,"Manuelle Auswahl",None,0))
print(res)
conn.commit()
def getLastScan():
#sql = "SELECT * FROM scans WHERE skipped = 0 AND validated = 0 ORDER BY unixtimestamp_created DESC LIMIT 1;"
sql = "SELECT * FROM scans WHERE (julianday('now') - julianday(CAST(unixtimestamp_created AS float) / 1e9,'unixepoch'))*24*60*60 < ? ORDER BY unixtimestamp_created DESC LIMIT 1;"
#sql = "SELECT * FROM scans ORDER BY unixtimestamp_created DESC LIMIT 1;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql, (timeoffset, ))
#res = cur.execute(sql)
rows = res.fetchall()
if len(rows) > 0:
return rows[-1]
else:
return []
def getValidatedCount():
sql = "SELECT COUNT(*) FROM scans WHERE validated = 1 and skipped = 0;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql)
rows = res.fetchall()
validated=rows[-1][0]
sql = "SELECT COUNT(*) FROM tickets;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql)
rows = res.fetchall()
total=rows[-1][0]
return "{}/{} eingecheckt".format(validated, total)
#Anzahl der Barcodes, die mehr als 1x eingescannt wurden (nur für Statistik)
def getScannedDuplicates():
sql = 'SELECT COUNT(*) FROM (SELECT COUNT(*) as "ct",* FROM scans GROUP BY barcode) WHERE ct > 1;'
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql)
rows = res.fetchall()
total=rows[-1][0]
return "{} Barcodes mehr als 1x gescannt".format(total)
def isAlreadyCheckedIn(barcode):
sql = "SELECT * FROM scans WHERE validated = 1 AND barcode = ? ORDER BY unixtimestamp_checked LIMIT 1;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql, (barcode, ))
rows = res.fetchall()
return True if len(rows) > 0 else False
def getScans(tv):
#sql = "SELECT * FROM scans WHERE skipped = 0 AND validated = 0;" # Scans, die nicht übersprungen und noch nicht validiert wurden
sql = "SELECT * FROM scans;" # alle Scans
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql)
rows = res.fetchall()
for i in tv.get_children():
tv.delete(i)
k=1
for i in rows:
unixtimestamp_created_c = datetime.fromtimestamp(int(str(i[0])[0:10]), pytz.timezone("Europe/Berlin")).strftime('%d.%m.%Y %H:%M:%S')
try:
unixtimestamp_checked_c = datetime.fromtimestamp(int(str(i[4])[0:10]), pytz.timezone("Europe/Berlin")).strftime('%d.%m.%Y %H:%M:%S')
except:
unixtimestamp_checked_c = ""
barcode = i[1]
validated = "Ja" if i[2] == 1 else "Nein"
hint = "" if i[3] == None else i[3]
#unixtimestamp_skipped = i[4]
skipped = "Ja" if i[5] == 1 else "Nein"
unixtimestamp_created = i[0]
if k % 2 == 0:
evenodd = "evenrow"
else:
evenodd = "oddrow"
tv.insert('', 1, text=k, values=(unixtimestamp_created_c, barcode, validated, hint, unixtimestamp_checked_c, skipped, unixtimestamp_created), tags=(evenodd, ))
k+=1
treeview_sort_column(tv, 6, False) #wir sortieren nach unixtimestamp_created, weil unixtimestamp_created_c einen Fehler bringt
global autoscroll
if autoscroll is True:
if len(tv.get_children()) > 0:
lastItem = tv.get_children()[-1]
tv.focus(lastItem)
tv.selection_set(lastItem)
tv.yview_moveto(1)
#Tickets
def getTickets(tv):
global lastSelectedTicket
lastSelectedTicket = tv.index(tv.focus())
#sql = "SELECT * FROM tickets;"
sql = "SELECT DISTINCT tickets.*, CASE WHEN RES.validated = 1 THEN 'Ja' ELSE 'Nein' END FROM tickets FULL JOIN (SELECT * FROM scans WHERE validated = 1 and skipped = 0) AS RES ON RES.barcode = tickets.BarcodeContent;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
res = cur.execute(sql)
rows = res.fetchall()
for i in tv.get_children():
tv.delete(i)
k=1
for i in rows:
if k % 2 == 0:
evenodd = "evenrow"
else:
evenodd = "oddrow"
tv.insert('', 1, text=k, values=(i), tags=(evenodd, ))
k+=1
treeview_sort_column(tv, 1, False)
if len(tvScans.get_children()) > 0:
global autoscroll
if autoscroll is True:
Barcode = tvScans.item(tvScans.focus())['values'][1] #get row von Scan, wo Barcode dem Barcode in Ticket entspr
col=0 #Barcode-Spalte
l = [(tvTickets.set(k, col), k) for k in tvTickets.get_children('')]
for index, (val, k) in enumerate(l):
if val == Barcode:
tv.focus(tv.get_children()[index])
tv.selection_set(tv.get_children()[index])
tv.yview_moveto(0)
tv.yview_scroll(index, "units")
else:
tv.focus(tv.get_children()[lastSelectedTicket])
tv.selection_set(tv.get_children()[lastSelectedTicket])
def validate():
if len(currentScan) > 0:
sql = "UPDATE scans set unixtimestamp_checked = ?, hint = ?, validated = 1 WHERE unixtimestamp_created = ? AND barcode = ? AND validated = 0 AND skipped = 0;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
ts = time.time_ns()
res = cur.execute(sql,(int(ts), hintTextInput.get(1.0, "end-1c"), currentScan[0],currentScan[1]))
print(res)
conn.commit()
hintTextInput.delete(1.0,tk.END) #reset hintTextInput
def skip():
if len(currentScan) > 0:
sql = "UPDATE scans set unixtimestamp_checked = ?, hint = ?, skipped = 1 WHERE unixtimestamp_created = ? AND barcode = ? AND validated = 0;"
with closing(sqlite3.connect(db_file)) as conn:
with closing(conn.cursor()) as cur:
ts = time.time_ns()
res = cur.execute(sql,(int(ts), hintTextInput.get(1.0, "end-1c"), currentScan[0],currentScan[1]))
print(res)
conn.commit()
hintTextInput.delete(1.0,tk.END) #reset hintTextInput
toggleScrollbarButton_t1 = "Umschalten zu: Ticket manuell auswählen"
toggleScrollbarButton_t2 = "Umschalten zu: Ticket automatisch per Scan auswählen (Standard)"
def manual():
if toggleScrollbarButton.config('text')[-1] == toggleScrollbarButton_t2:
global lastSelectedTicket
mBarcode = None
if len(tvScans.get_children()) > 0:
item = tvTickets.item(tvTickets.focus())
row = 0
for (val, k) in enumerate(item.values()):
row += 1
if row == 3:
mBarcode = k[0]
print(mBarcode)
insertManualSelectionAsScan(mBarcode) #insert a new "lastScan"
def ScrollToggle():
global autoscroll
if toggleScrollbarButton.config('text')[-1] == toggleScrollbarButton_t1:
toggleScrollbarButton.config(text=toggleScrollbarButton_t2)
autoscroll = False
tvTickets['selectmode'] = "browse"
else:
toggleScrollbarButton.config(text=toggleScrollbarButton_t1)
autoscroll = True
tvTickets['selectmode'] = "none"
toggleScrollbarButton = tk.Button(text=toggleScrollbarButton_t1, width=70, relief="raised", bg='pink', command=ScrollToggle)
toggleScrollbarButton.pack(padx=10, pady=5)
f1=tk.Frame(window)
f1.pack(expand=1)
tk.Label(f1, text="Die Anzeige des aktuellen Barcodes setzt sich automatisch nach {} Sekunden zurück".format(timeoffset), padx=10, pady=5).pack()
manualButton = tk.Button(f1, text="Manuelle Auswahl", command=manual)
manualButton.pack(side=tk.LEFT)
manualButton['font']=font.Font(size=35)
barcodeLabel = tk.Label(f1, text="<>", bg='#000000', fg='#00ff00', padx=10, pady=5)
barcodeLabel.config(font=(None, 35))
barcodeLabel.pack(side=tk.LEFT)
checkinButton = tk.Button(f1, text="Einchecken/entwerten", bg='red', fg='white', command=validate)
checkinButton.pack(side=tk.RIGHT)
checkinButton['font']=font.Font(size=35)
skipButton = tk.Button(f1, text="Überspringen", bg='red', fg='white', command=skip)
skipButton.pack(side=tk.RIGHT)
skipButton['font']=font.Font(size=35)
commentLabel = tk.Label(window, text="Kommentar", padx=10, pady=5)
commentLabel.config(font=(None, 15))
commentLabel.pack()
hintTextInput = tk.Text(window, height = 3, width = 100)
hintTextInput.pack()
totalValidatedLabel = tk.Label(window, text="/", fg='#008000', padx=10, pady=5, width=100)
totalValidatedLabel.config(font=(None, 35))
totalValidatedLabel.pack()
tabControl = ttk.Notebook(window)
tabScans = ttk.Frame(tabControl)
tabTickets = ttk.Frame(tabControl)
tabControl.add(tabTickets, text='Alle Tickets')
tabControl.add(tabScans, text='Alle Scans')
tabControl.pack(fill='both')
tk.Label(tabScans, text="Scans").pack()
totalDuplicateCodeScansLabel = tk.Label(tabScans, text="/", padx=10, pady=5)
totalDuplicateCodeScansLabel.pack()
tk.Label(tabTickets, text="Tickets").pack()
def updateBarcodeLabel(label, scan):
if len(scan) == 0:
labeltext="Bitte scannen!"
label.config(fg='#0000ff')
else:
barcode = scan[1]
alreadyCheckedIn = isAlreadyCheckedIn(barcode)
if alreadyCheckedIn is False:
if toggleScrollbarButton.config('text')[-1] == toggleScrollbarButton_t2:
ScrollToggle() #toggle zurück zu Standardmodus, wenn ein normaler Scan gekommen ist
labeltext="{0} ready!".format(barcode)
label.config(fg='#00ff00')
skip = scan[4]
if skip is not None:
labeltext="{0} übersprungen!".format(barcode)
label.config(fg='#ffffff')
else:
labeltext="{0} entwertet!".format(barcode)
label.config(fg='#ff0000')
label.config(text=labeltext)
tvScans = ttk.Treeview(tabScans, height=50, selectmode="browse")
tvScans["columns"] = ("c1", "c2", "c3", "c4", "c5", "c6", "c7") #eine Spalte mehr (unixtimestamp_created, welche aber ausgeblendet ist und nur zum Sortieren genutzt wird)
tvScans["displaycolumns"]=("c1", "c2", "c3", "c6", "c4", "c5")
tvScans.column("c1", width=100)
tvScans.column("c2", anchor='center', width=100)
tvScans.column("c3", anchor='center', width=100)
tvScans.column("c4", anchor='center', width=100)
tvScans.column("c5", anchor='center', width=100)
tvScans.column("c6", anchor='center', width=100)
tvScans.heading("#0", text="Nr.")
tvScans.heading("c1", text="TS Gescannt", anchor='w')
tvScans.heading("c2", text="Barcode")
tvScans.heading("c3", text="Entwertet")
tvScans.heading("c4", text="Anmerkung/Hinweise")
tvScans.heading("c5", text="TS Bearbeitet")
tvScans.heading("c6", text="Übersprungen")
tvScans.tag_configure('oddrow', background='white', font=("monospace", 10))
tvScans.tag_configure('evenrow', background='lightblue', font=("monospace", 10))
tvScansBar = ttk.Scrollbar(tabScans, orient="vertical", command=tvScans.yview)
tvScansBar.pack(side='right', fill='x')
tvScans.configure(yscrollcommand = tvScansBar.set)
tvScans.pack(fill='both')
tvTickets = ttk.Treeview(tabTickets, height=50, selectmode="none")
tvTickets["columns"] = ("c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10", "c11", "c12", "c13")
tvTickets["displaycolumns"]=("c1", "c3", "c6", "c7", "c8", "c9", "c10", "c11", "c12", "c13")
tvTickets.column("c1", width=100)
tvTickets.column("c2", anchor='center', width=100)
tvTickets.column("c3", anchor='center', width=100)
tvTickets.column("c4", anchor='center', width=100)
tvTickets.column("c5", anchor='center', width=100)
tvTickets.column("c6", anchor='center', width=100)
tvTickets.column("c7", anchor='center', width=100)
tvTickets.column("c8", anchor='center', width=100)
tvTickets.column("c9", anchor='center', width=100)
tvTickets.column("c10", anchor='center', width=100)
tvTickets.column("c11", anchor='center', width=100)
tvTickets.column("c12", anchor='center', width=100)
tvTickets.column("c13", anchor='center', width=100)
tvTickets.heading("#0", text="Nr.")
tvTickets.heading("c1", text="Barcode", anchor='w')
tvTickets.heading("c2", text="Nr")
tvTickets.heading("c3", text="VIP")
tvTickets.heading("c4", text="Ticketbeschriftung")
tvTickets.heading("c5", text="BarcodeFile")
tvTickets.heading("c6", text="Medium")
tvTickets.heading("c7", text="Aushändigung")
tvTickets.heading("c8", text="Bestellnummer/Verwendungszweck")
tvTickets.heading("c9", text="Bezahlt am")
tvTickets.heading("c10", text="Hinweise")
tvTickets.heading("c11", text="Besteller")
tvTickets.heading("c12", text="Typ")
tvTickets.heading("c13", text="Entwertet")
tvTickets.tag_configure('oddrow', background='white', font=("monospace", 10))
tvTickets.tag_configure('evenrow', background='lightblue', font=("monospace", 10))
#tvTicketsBar = ttk.Scrollbar(tvTickets, orient="vertical", command=tvTickets.yview)
#tvTicketsBar.pack(side='right', fill='x')
#tvTickets.configure(yscrollcommand = tvTicketsBar.set)
tvTickets.pack(fill='both')
def update():
getScans(tvScans)
getTickets(tvTickets)
global currentScan
currentScan=getLastScan() #bekomme den letzten, unbearbeiteten Scan
updateBarcodeLabel(barcodeLabel, currentScan)
totalValidatedLabel.config(text=getValidatedCount())
totalDuplicateCodeScansLabel.config(text=getScannedDuplicates())
window.after(2000, update)
update() # run first time
window.mainloop()
Reference in New Issue View Git Blame Copy Permalink