diff options
| -rw-r--r-- | fracture/db.py | 43 | ||||
| -rw-r--r-- | fracture/invoice.py | 65 |
2 files changed, 84 insertions, 24 deletions
diff --git a/fracture/db.py b/fracture/db.py index 2611ee9..56a268d 100644 --- a/fracture/db.py +++ b/fracture/db.py @@ -5,18 +5,25 @@ def create(dbname): c = conn.cursor() c.execute('''CREATE TABLE products - ( id INTEGER PRIMARY KEY, - invoice_id INTEGER, - description TEXT NOT NULL, - units REAL NOT NULL, - price_unit REAL NOT NULL, - vat REAL, - FOREIGN KEY(invoice_id) REFERENCES invoices(id) + ( invoice_id INTEGER, + invoice_type TEXT, + invoice_series INTEGER, + description TEXT NOT NULL, + units REAL NOT NULL, + price_unit REAL NOT NULL, + vat REAL, + PRIMARY KEY(description, + invoice_id, + invoice_type, + invoice_series) + FOREIGN KEY(invoice_id) REFERENCES invoices(id) + FOREIGN KEY(invoice_type) REFERENCES invoices(type) + FOREIGN KEY(invoice_series) REFERENCES invoices(series) ON DELETE CASCADE )''') # date is %Y-%m-%d c.execute('''CREATE TABLE invoices - ( id INTEGER PRIMARY KEY, + ( id INTEGER NOT NULL, type TEXT NOT NULL, series INTEGER NOT NULL, date TEXT NOT NULL, @@ -24,15 +31,23 @@ def create(dbname): customer_id TEXT, customer_name TEXT, - customer_address TEXT + customer_address TEXT, + PRIMARY KEY(type, series, id) )''') c.execute('''CREATE TABLE taxes - ( name TEXT NOT NULL, - invoice_id INTEGER, - ratio REAL, - PRIMARY KEY(name, invoice_id) - FOREIGN KEY(invoice_id) REFERENCES invoices(id) + ( name TEXT NOT NULL, + invoice_id INTEGER, + invoice_type TEXT, + invoice_series INTEGER, + ratio REAL, + PRIMARY KEY(name, + invoice_id, + invoice_type, + invoice_series) + FOREIGN KEY(invoice_id) REFERENCES invoices(id) + FOREIGN KEY(invoice_type) REFERENCES invoices(type) + FOREIGN KEY(invoice_series) REFERENCES invoices(series) ON DELETE CASCADE )''') conn.commit() diff --git a/fracture/invoice.py b/fracture/invoice.py index 02762f0..754af02 100644 --- a/fracture/invoice.py +++ b/fracture/invoice.py @@ -152,8 +152,9 @@ class Invoice: customer, taxes) + @classmethod - def load(cls, id): + def load(cls, id, series, type): with sqlite3.connect(cls.DB_FILE) as conn: conn.row_factory = sqlite3.Row @@ -161,7 +162,9 @@ class Invoice: # PRODUCTS products = () - c.execute("SELECT * FROM products WHERE invoice_id = ?", (id,)) + c.execute("""SELECT * FROM products WHERE invoice_id = ? + AND invoice_series = ? + AND invoice_type = ?""", (id, series, type)) res = c.fetchone() while res is not None: desc = res["description"] @@ -173,14 +176,18 @@ class Invoice: # TAXES taxes = () - c.execute("SELECT * FROM taxes WHERE invoice_id = ?", (id,)) + c.execute("""SELECT * FROM taxes WHERE invoice_id = ? + AND invoice_series = ? + AND invoice_type = ?""", (id, series, type)) res = c.fetchone() while res is not None: taxes += (Tax(res["name"], res["ratio"]) ,) res = c.fetchone() # INVOICE - c.execute("SELECT * FROM invoices WHERE id = ?", (id,)) + c.execute("""SELECT * FROM invoices WHERE id = ? + AND series = ? + AND type = ?""", (id, series, type)) res = c.fetchone() return cls( id = id, @@ -203,8 +210,29 @@ class Invoice: conn.row_factory = sqlite3.Row c = conn.cursor() - c.execute(("SELECT id FROM invoices " + cond), vals) - return tuple( cls.load( int(r["id"]) ) for r in c.fetchall() ) + c.execute(("SELECT id, series, type FROM invoices " + cond), vals) + return tuple( cls.load( int(r["id"]), + int(r["series"]), + r["type"] ) for r in c.fetchall() ) + + @classmethod + def latest_id(cls, series, type, debug=False): + with sqlite3.connect(cls.DB_FILE) as conn: + if debug: + conn.set_trace_callback(print) + + conn.row_factory = sqlite3.Row + + c = conn.cursor() + c.execute("""SELECT id FROM invoices + WHERE series = ? AND type = ? + ORDER BY(id) DESC LIMIT 1""", (series, type)) + res = c.fetchall() + + latest_id = 0 + if len(res) != 0: + latest_id = int(res[0]["id"]) + return latest_id @classmethod def load_by_date(cls, year, quarter=None): @@ -230,34 +258,41 @@ class Invoice: def persist(self): conn = sqlite3.connect(Invoice.DB_FILE) + if self.id is None: + self.id = self.latest_id(self.series, self.type) + 1 c = conn.cursor() c.execute("""INSERT INTO invoices ( type, series, + id, date, notes, customer_id, customer_name, customer_address - ) VALUES (?,?,?,?,?,?,?)""", ( + ) VALUES (?,?,?,?,?,?,?,?)""", ( self.type, self.series, + self.id, self.date.strftime("%Y-%m-%d"), self.notes, self.customer.id, self.customer.name, self.customer.address )) - self.id = c.lastrowid for p in self.products: c.execute("""INSERT INTO products ( invoice_id, + invoice_series, + invoice_type, description, units, price_unit, vat - ) VALUES (?,?,?,?,?)""",( + ) VALUES (?,?,?,?,?,?,?)""",( self.id, + self.series, + self.type, p.description, p.units, p.price_unit, @@ -266,13 +301,22 @@ class Invoice: for x in self.taxes: c.execute("""INSERT INTO taxes ( invoice_id, + invoice_series, + invoice_type, name, ratio - ) VALUES (?,?,?)""", (self.id, x.name, x.ratio)) + ) VALUES (?,?,?,?,?)""", ( + self.id, + self.series, + self.type, + x.name, + x.ratio)) conn.commit() conn.close() return self.id + + def format(self): # TODO # https://bugs.python.org/issue35111 @@ -288,6 +332,7 @@ class Invoice: vat += i[1] row = { + "type": self.type, "series": self.series, "id": self.format_id(), "date": self.date.strftime("%Y-%m-%d"), |