113 lines
2.8 KiB
Python
Executable File
113 lines
2.8 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 time import strftime
|
|
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_ERP:
|
|
# Database Stuff
|
|
# --------------
|
|
def __init__ (self, dbfile):
|
|
self.db = connectdb (dbfile)
|
|
|
|
|
|
def executesql (self, dbstring, arguments = ()):
|
|
"""
|
|
Fuehre den uebergebenen SQL-Befehl aus und liefere ein Cursor-Objekt
|
|
zurueck.
|
|
"""
|
|
try:
|
|
cursor = self.db.cursor ()
|
|
cursor.execute (dbstring, arguments)
|
|
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
|
|
|
|
|
|
def getinsertions (self):
|
|
"""
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("SELECT kistennummer, artikelnummer, charge, menge, einheit, rfzts from ERPTrace where action = 'In Puffer inserted'")
|
|
except:
|
|
return None
|
|
|
|
return cursor.fetchall ()
|
|
|
|
|
|
def getinsertionsgrouped (self):
|
|
"""
|
|
Get summarized information of insertions grouped by article / charge
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("SELECT artikelnummer, charge, sum(menge), einheit from ERPTrace where action = 'In Puffer inserted' GROUP BY artikelnummer, charge;")
|
|
except:
|
|
return None
|
|
|
|
return cursor.fetchall ()
|
|
|
|
|
|
|
|
def addinsertion (self, kistennummer, artikelnummer, charge, menge, einheit):
|
|
"""
|
|
log insertion
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("INSERT INTO ERPTrace (kistennummer, artikelnummer, charge, menge, einheit, rfzts, action) VALUES ('%s', '%s', '%s', '%s', '%s', current_timestamp, 'In Puffer inserted')"\
|
|
%(kistennummer, artikelnummer, charge, menge, einheit))
|
|
except:
|
|
print ("Couldn't add insertion to ERPTrace")
|
|
|
|
|
|
|
|
def markinsertionsasread (self):
|
|
"""
|
|
Mark all insertions as read
|
|
"""
|
|
try:
|
|
cursor = self.executesql ("UPDATE ERPTrace set action = 'Logged Puffer insertion' where action = 'In Puffer inserted';")
|
|
except:
|
|
return None
|
|
|
|
|
|
|
|
|
|
if __name__ == "__main__":
|
|
conn = DB_ERP ("/opt/data/storage_ERPTrace.db3")
|
|
conn.markinsertionsasread ()
|
|
conn.addinsertion ('123456', '1235','12','1234','KG')
|
|
print conn.getinsertions ()
|
|
|
|
|
|
|
|
|