def remove_stock(items: list, database: str = utils.get_database_path()):
# Open the database
con = sqlite3.connect(database)
# Update the quantity
new_quantity = []
for item in items:
new_quantity.append([item[1], item[0]])
old_quantity = []
for q in new_quantity.copy():
new_total = con.execute("SELECT quantity FROM stock WHERE name = (?)", (q[1],)).fetchall()[0]
old_quantity.append([new_total, q[1]])
quantity = []
for q in old_quantity:
new_quan = float(q[0][0] - new_quantity[old_quantity.index(q)][0])
quantity.append([new_quan, q[1]])
con.executemany("UPDATE stock SET quantity = ? WHERE name = ?", quantity)
# Don't need to update the unit or cost
# Save changes to the database and close
con.commit()
con.close()
def load_items(database: str = utils.get_database_path()) -> list:
con = sqlite3.connect(database)
items_tuple = con.execute("SELECT * FROM items;").fetchall()
# No changes made, don't need to save the database
con.close()
items = []
for item in items_tuple:
pictogram_list = []
for pictogram_path in item[-1].split(","):
pictogram_list.append(pictogram_path)
pictogram_list.pop(-1)
pictogram_model = PictogramModel(pictograms=pictogram_list)
item_dict = {
"Name": item[0],
"Chemical Formula": item[1],
"Warning Label": item[2],
"Danger Level": item[3],
"Notes": item[4],
"Pictograms": pictogram_model
}
items.append(item_dict)
return items
def edit_stock(items: list, database: str = utils.get_database_path()):
# Open the database
con = sqlite3.connect(database)
# Update the quantity
quantity = []
for item in items:
quantity.append([item[1], item[0]])
con.executemany("UPDATE stock SET quantity = ? WHERE name = ?", quantity)
# Update the units
units = []
for item in items:
units.append((item[2], item[0]))
con.executemany("UPDATE stock SET unit = ? WHERE name = ?", units)
# Update the quantity
cost = []
for item in items:
cost.append([item[3], item[0]])
con.executemany("UPDATE stock SET cost = ? WHERE name = ?", cost)
# Save changes to the database and close
con.commit()
con.close()
def load_stock(self):
stock = []
for s in load_stock(database=utils.get_database_path()):
stock.append(list(s.values()))
self.stock_model.stock = stock
self.stock_model.update()
self.table_model.layoutAboutToBeChanged.emit()
self.table_model.mirror_model(self.stock_model)
def load_items(self):
items = []
for i in load_items(database=utils.get_database_path()):
items.append(list(i.values())[:-2])
self.items_model.items = items
self.items_model.update()
self.table_model.layoutAboutToBeChanged.emit()
self.table_model.mirror_model(self.items_model)
def edit_items(items: list, database: str = utils.get_database_path()):
con = sqlite3.connect(database)
# Update the names
# item[0] = Old Name, item[1] = New Name
names = []
for item in items:
names.append([item[1], item[0]])
con.executemany("UPDATE items SET name = ? WHERE name = ?", names)
con.executemany("UPDATE stock SET name = ? WHERE name = ?", names)
# Update the chemical formulas
chem_formulas = []
for item in items:
chem_formulas.append([item[2], item[1]])
con.executemany("UPDATE items SET chemical_formula = ? WHERE name = ?",
chem_formulas)
# Update the warning labels
warning_labels = []
for item in items:
warning_labels.append([item[3], item[1]])
con.executemany("UPDATE items SET warning_label = ? WHERE name = ?",
warning_labels)
# Update the danger levels
danger_levels = []
for item in items:
danger_levels.append([item[4], item[1]])
con.executemany("UPDATE items SET danger_level = ? WHERE name = ?",
danger_levels)
# Update the notes
notes = []
for item in items:
notes.append([item[5], item[1]])
con.executemany("UPDATE items SET notes = ? WHERE name = ?", notes)
# Update the notes
pictograms = []
for item in items:
pictograms.append([item[6], item[1]])
con.executemany("UPDATE items SET pictograms = ? WHERE name = ?",
pictograms)
con.commit()
con.close()
def add_stock(items: list, database: str = utils.get_database_path()):
# Open the database
con = sqlite3.connect(database)
# Update the quantity
new_quantity = []
for item in items:
new_quantity.append([item[1], item[0]])
old_quantity = []
for q in new_quantity.copy():
new_total = con.execute("SELECT quantity FROM stock WHERE name = (?)", (q[1],)).fetchall()[0]
old_quantity.append([new_total, q[1]])
quantity = []
for q in old_quantity:
new_quan = float(q[0][0] + new_quantity[old_quantity.index(q)][0])
quantity.append([new_quan, q[1]])
con.executemany("UPDATE stock SET quantity = ? WHERE name = ?", quantity)
# Update the units
units = []
for item in items:
units.append((item[2], item[0]))
con.executemany("UPDATE stock SET unit = ? WHERE name = ?", units)
# Update the cost
new_cost = []
for item in items:
new_cost.append([item[3], item[0]])
old_cost = []
for c in new_cost.copy():
new_total = con.execute("SELECT cost FROM stock WHERE name = (?)", (c[1],)).fetchall()[0]
old_cost.append([new_total, c[1]])
cost = []
for c in old_cost:
new_c = float(c[0][0] + new_cost[old_cost.index(c)][0])
cost.append([new_c, c[1]])
con.executemany("UPDATE stock SET cost = ? WHERE name = ?", cost)
# Save changes to the database and close
con.commit()
con.close()
def add_items(items: list, database: str = utils.get_database_path()):
con = sqlite3.connect(database)
con.executemany(
"INSERT INTO items(name, chemical_formula, warning_label, danger_level, notes, pictograms) values (?, ?, ?, ?, ?, ?)",
items)
item_names = []
for item in items:
item_names.append([item[0]])
con.executemany(
"INSERT INTO stock(name, quantity, unit, cost) values (?, 0, 'None', 0)",
item_names)
con.commit()
con.close()
def load_stock(database: str = utils.get_database_path()) -> list:
con = sqlite3.connect(database)
items_tuple = con.execute("SELECT * FROM stock;").fetchall()
# No changes made, don't need to save the database
con.close()
items = []
for item in items_tuple:
item_dict = {
"Name": item[0],
"Quantity": item[1],
"Unit": item[2],
"Cost": item[3],
}
items.append(item_dict)
return items
def remove_items(items: list, database: str = utils.get_database_path()):
"""
:param items: The items to remove from the database
:type items: list
:param database: The database to remove the items from
:type database: str
"""
con = sqlite3.connect(database)
item_names = []
for item_name in items:
item_names.append([item_name])
con.executemany("DELETE FROM items WHERE name = ?;", item_names)
con.executemany("DELETE FROM stock WHERE name = ?;", item_names)
con.commit()
con.close()