113 lines
3.3 KiB
Python
Executable File
113 lines
3.3 KiB
Python
Executable File
#!/usr/bin/python2.4
|
|
#!/opt/local/bin/python3.0
|
|
|
|
#import sqlite3 as sqlite
|
|
from pysqlite2 import dbapi2 as sqlite
|
|
from sys import exit
|
|
|
|
from locations import *
|
|
|
|
def connectdb (dbfile):
|
|
"""
|
|
Create connection to database
|
|
return DB Object
|
|
user gets information if failure
|
|
"""
|
|
print ("Verbindung zu %s herstellen" %dbfile)
|
|
try:
|
|
db = sqlite.connect (dbfile)
|
|
return db
|
|
except:
|
|
print ("Konnte DB Verbindung nicht herstellen")
|
|
exit (1)
|
|
|
|
|
|
class DB:
|
|
# Database Stuff
|
|
# --------------
|
|
def __init__ (self, dbfile):
|
|
self.db = connectdb (dbfile)
|
|
|
|
def executesql (self, dbstring):
|
|
"""
|
|
Fuehre den uebergebenen SQL-Befehl aus und liefere ein Cursor-Objekt
|
|
zurueck.
|
|
"""
|
|
try:
|
|
cursor = self.db.cursor ()
|
|
cursor.execute (dbstring)
|
|
except OperationalError: #, msg:
|
|
print ("Datenbankausfuehrung fehlgeschlagen. Ausgeloest durch: " + dbstring)
|
|
raise Warning
|
|
except IntegrityError: #, msg:
|
|
print ("Doppelter Eintrag. Ausgeloest durch: " + dbstring)
|
|
raise Warning
|
|
except ProgrammingError: #, msg:
|
|
print ("Fehlerhaftes SQL. Ausgeloest durch: " + dbstring)
|
|
raise Warning
|
|
except Warning: #, msg:
|
|
pass
|
|
|
|
self.db.commit ()
|
|
return cursor
|
|
|
|
# Trace
|
|
#------------------
|
|
def ghosts (self):
|
|
"""
|
|
Suche als Geisterkisten entfernte Kisten
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("SELECT * FROM Trace WHERE action='as Ghost removed'")
|
|
except:
|
|
print ("Konnte Kisten nicht finden")
|
|
raise Warning
|
|
|
|
boxes = cursor.fetchall ()
|
|
|
|
return boxes
|
|
|
|
# Dayly insertions
|
|
#-----------------
|
|
def daylyinsertions (self):
|
|
"""
|
|
Liefert eine Auflistung aller Einlagerungen der letzten 24h
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("SELECT kistennummer,rfzts FROM Trace WHERE (action='in Puffer inserted') AND ((julianday(current_timestamp) - julianday(rfzts)) * 24) <= 24 ORDER BY kistennummer;")
|
|
cursor2 = self.executesql ("SELECT kistennummer,artikelnummer,charge,gewicht FROM Trace WHERE (action='in Puffer reserved') AND ((julianday(current_timestamp) - julianday(rfzts)) * 24) <= 24 ORDER BY kistennummer;")
|
|
except:
|
|
print ("Konnte Daten nicht ermitteln")
|
|
|
|
reservations = cursor2.fetchall ()
|
|
reservationdict = {}
|
|
for knr, anr, lot, weight in reservations:
|
|
reservationdict [knr] = (anr, lot, int (weight))
|
|
sumary = {}
|
|
artboxes = {}
|
|
insertions = cursor.fetchall ()
|
|
for knr, rfzts in insertions:
|
|
key = (reservationdict [knr][0], reservationdict [knr][1])
|
|
curval = sumary.get (key, (0,0))
|
|
sumary [key] = (curval[0] + 1, curval[1] + reservationdict [knr][2])
|
|
curval = artboxes.get (key)
|
|
if not curval: artboxes[key] = []
|
|
artboxes [key].append ((knr, reservationdict [knr][2], rfzts))
|
|
|
|
return sumary, artboxes
|
|
|
|
|
|
if __name__ == "__main__":
|
|
#conn = DB ("/opt/data/storage.db3")
|
|
conn = DB ("/home/michi/storage.db3.1")
|
|
insertions, artboxes = conn.daylyinsertions ()
|
|
print 'Artikel;Charge;Kisten;Gewicht'
|
|
for key in insertions:
|
|
print ('%s;%s;%d;%0.2f' %(key[0], key[1], insertions[key][0], insertions[key][1]/100.0))
|
|
|
|
print ('\nArtikel;Charge;Kiste;Gewicht;Timestamp')
|
|
for key in artboxes:
|
|
for knr, weight, rfzts in artboxes [key]:
|
|
print ('%s;%s;%s;%0.2f;%s' % (key[0], key[1], knr, weight / 100.0, rfzts))
|
|
|