partsdb

electronic parts inventory
git clone https://git.e1e0.net/partsdb.git
Log | Files | Refs

commit ade9fb918b9093dfe6e6ac0abe00e4dab4067565
parent d3283490a5691aa37d7b747b70847152b018bcc4
Author: Paco Esteban <paco@e1e0.net>
Date:   Sun, 14 Mar 2021 11:09:34 +0100

implement stock modification

Diffstat:
Mdatabase.py | 26++++++++++++++++++++++++++
Mhelpers.py | 9++++++++-
MmyChips.py | 11+++++++----
Mschema.sql | 10++++++++++
4 files changed, 51 insertions(+), 5 deletions(-)

diff --git a/database.py b/database.py @@ -64,6 +64,14 @@ SEARCH_PARTS_QUERY = """ part_type LIKE '%'||?||'%' GROUP BY p.id """ +GET_HISTORY_QUERY = "SELECT * FROM parts_history WHERE part_id = ?" +INSERT_HISTORY_EVENT = """ + INSERT INTO parts_history + (part_id, movement, mcomment) + VALUES (?,?,?) +""" +UPDATE_PART_QTY_QUERY = "UPDATE parts SET quantity = ? WHERE id = ?" +GET_PART_QTY = "SELECT quantity FROM parts where id = ?" class PartsDB(): @@ -113,5 +121,23 @@ class PartsDB(): c.execute(SEARCH_PARTS_QUERY, (term, term, term, term, term)) return c.fetchall() + def get_part_history(self, part_id): + c = self.conn.cursor() + c.execute(GET_HISTORY_QUERY, (part_id,)) + return c.fetchall() + + def new_part_history_event(self, part_id, movement, comment): + with self.conn: + c = self.conn.cursor() + c.execute(INSERT_HISTORY_EVENT, (part_id, movement, comment)) + + def update_part_qty(self, part_id, movement): + with self.conn: + c = self.conn.cursor() + c.execute(GET_PART_QTY, (part_id,)) + qty = c.fetchone()['quantity'] + qty = qty + movement + c.execute(UPDATE_PART_QTY_QUERY, (qty, part_id)) + def close(self): self.conn.close() diff --git a/helpers.py b/helpers.py @@ -4,6 +4,7 @@ import tempfile + def print_parts_list(parts, short=False): if short: print(f"{'ID':4} | " @@ -57,7 +58,7 @@ def print_parts_list(parts, short=False): ) -def print_part(p, get_files=False): +def print_part(p, history, get_files=False): print(f"PN: {p['pn']}\tManufacturer: {p['manufacturer']}") print(f"Category: {p['cat']}\tType: {p['part_type']}" f"\tFootprint: {p['footprint']}") @@ -81,3 +82,9 @@ def print_part(p, get_files=False): print(f"Quantity: {p['quantity']}") # here should go the historical data. + if history: + print("History:") + for h in history: + print(f"{h['insert_date']} | " + f"{h['movement']} | " + f"{h['mcomment']}") diff --git a/myChips.py b/myChips.py @@ -116,15 +116,17 @@ def search_part(search_term): def get_part(part_id, files): part = db.get_part(part_id) - print_part(part, files) + history = db.get_part_history(part_id) + print_part(part, history, files) def delete_part(part_id): pass -def adjust_stock(part_id, stock_mod): - pass +def adjust_stock(part_id, stock_mod, comment): + db.new_part_history_event(part_id, stock_mod, comment) + db.update_part_qty(part_id, stock_mod) def export_db(dest_folder): @@ -173,6 +175,7 @@ if __name__ == '__main__': ap_stock.add_argument("part_id", help="Part Id", type=int) ap_stock.add_argument("stock_mod", help="Stock modifier (+ or -) int", type=int) + ap_stock.add_argument("comment", help="Reason for the stock mod") # export ap_export = asp.add_parser("export", help="Exports DB to HTML") ap_export.add_argument("dest_folder", help="Destination folder") @@ -193,7 +196,7 @@ if __name__ == '__main__': elif args.command == 'delete': delete_part(args.part_id) elif args.command == 'stock': - adjust_stock(args.part_id, args.stock_mod) + adjust_stock(args.part_id, args.stock_mod, args.comment) elif args.command == 'export': export_db(args.dest_folder) elif args.command == 'cat': diff --git a/schema.sql b/schema.sql @@ -29,3 +29,13 @@ CREATE TABLE IF NOT EXISTS parts ( FOREIGN KEY (category_id) REFERENCES categories (id), FOREIGN KEY (storage_id) REFERENCES storages (id) ); + +-- parts_histoy table +CREATE TABLE IF NOT EXISTS parts_history ( + id integer PRIMARY KEY, + part_id integer NOT NULL, + insert_date text NOT NULL DEFAULT CURRENT_TIMESTAMP, + movement integer NOT NULL, + mcomment text, + FOREIGN KEY (part_id) REFERENCES parts (id) +);