def test_validtable(self, table = None):
""" Tests that we're testing real, usable data """
if table is None: table = self.definition
conn = sql.connect(":memory:")
try: conn.execute(table)
except: self.fail("Table Definition is invalid.")
conn.close()
def startup(self):
""" Controller Startup
Sets title, pulls inventory for the month, populates treeview, updates total.
"""
p = self.pane
p.titlelabel.configure(text=f"Inventory For: {self.dt.strftime('%B %Y')}")
conn = sql.connect(str(self.dbfile))
conn.row_factory = sql.dict_factory
costs = conn.execute("""SELECT inventory.itemid, cost, price, vendor, inventory.*, items.description
FROM inventory
LEFT JOIN items ON inventory.itemid = items.itemid
LEFT JOIN (
SELECT itemid, cost, MAX(date) as latestdate, price, vendor
FROM costs
WHERE date <= :date2
GROUP BY itemid) AS latestcosts ON inventory.itemid = latestcosts.itemid
WHERE inventory.date = :date AND quantity > 0 AND quantity IS NOT NULL;
""",
dict(date = self.dt.strftime(constants.DATEFORMAT),date2 = self.dt)).fetchall()
conn.close()
for item in costs:
item['date'] = self.dt
item['newcost'] = item['cost']
self.items = {item['itemid']: item for item in costs}
for itemid in self.items:
self.setitem(itemid)
self.settotal()
return super().startup()
def submit(self):
## Only update in database costs that we explicitly changed
updated = [item for itemid,item in self.items.items() if item['newcost'] != item['cost']]
nextmonth = almethods.getfirstofnextmonthdatetime(self.dt)
for item in updated: item['nextmonth'] = nextmonth
conn = sql.connect(str(self.dbfile))
## Before updating, we need to make sure to preserve the cost on all other months
## This means making sure that each item has a "cost" value for next month that
## will supercede our arbitrary adjustment in the future
try:
import pprint
pprint.pprint(updated)
## Make sure all updated items have a cost in the next month
nextmonths = conn.executemany("""
INSERT OR REPLACE INTO costs (id, itemid, date, cost, price, vendor)
VALUES (
(SELECT id FROM costs WHERE itemid = :itemid AND date = :nextmonth),
:itemid,
:nextmonth,
:cost,
:price,
:vendor
);
""",updated)
## Add adjusted costs to database
conn.executemany("""
INSERT OR REPLACE INTO costs (id, itemid, date, cost, price, vendor)
VALUES (
(SELECT id FROM costs WHERE itemid = :itemid AND date = :date),
:itemid,
:date,
:newcost,
:price,
:vendor
);
""", updated)
except:
tkmessagebox.showerror("An Error Occurred", traceback.format_exc())
conn.rollback()
return
else:
conn.commit()
finally:
conn.close()
self.parent.cleanup()
def continuetoparent(self):
""" Checks the database, month, and year and then returns the selections to the parent controller """
p = self.pane
dbfile = self.checkdbfile()
if not dbfile: return
try:
dt = datetime.datetime(year = int(p.yearspinbox.get()), month = p.getmonth(), day = 1)
except:
traceback.print_exc()
tkmessagebox.showerror("Invalid Month/Year", "Invalid Month/Year Combination")
return
conn = None
conn = sql.connect(str(dbfile))
try:
items = conn.execute("""SELECT * FROM inventory WHERE date=:dt""",{'dt':dt.strftime(constants.DATEFORMAT)}).fetchall()
if not items:
tkmessagebox.showerror("No Inventory","Could not find inventory items for the given Month")
return
finally:
if conn:
conn.close()
self.parent.loadfudger(dbfile,dt)
def checkdbfile(self,dbfile = None):
""" Validates the DB file """
if dbfile is None:
dbfile = self.pane.databasefileentry.get()
dbfile = pathlib.Path(dbfile).resolve()
if not dbfile.exists():
tkmessagebox.showerror("Bad Database File","Selected Database does not exist")
return
conn = None
try:
conn = sql.connect(str(dbfile))
tables = sql.getalltables(conn)
if all(table in tables for table in ["inventory","items"]):
tkmessagebox.showerror("Invalid Database","Database missing required tables")
return
except:
traceback.print_exc()
tkmessagebox.showerror("Invalid Database","Could not open target Database")
return
finally:
if conn:
conn.close()
return dbfile
import csv
import pprint
import re
import traceback
from alcustoms import sql
db = sql.connect(
r"C:\Users\J-Moriarty\Dropbox\}Python\BRD Services\BRDWebApp\db.sqlite3")
try:
def getbaditems():
items = db.execute("""
SELECT itemid,itemindex FROM items;
""").fetchall()
baditems = list()
for itemid, itemindex in items:
try:
float(itemindex)
except:
baditems.append((itemid, itemindex))
return baditems
baditems = getbaditems()
pprint.pprint(baditems)
DIGITRE = re.compile("""(?P<index>^\d*)""")
def process(itemindex):
return float(DIGITRE.search(itemindex).group("index") + ".5")
gooditems = [{
def recreate(self):
self.database.close()
self.database = sql.connect(":memory:")
self.database.row_factory = sql.dict_factory
self.showoutput(">>>>>>>>>> Database Recreated <<<<<<<<<<", "system")
def setupdatabase(self):
self.database = sql.connect(MEMORY)
self.database.row_factory = sql.dict_factory
self.showoutput(">>>>>>>>>> In-Memory Database Created <<<<<<<<<<",
"system")