From 657b0312711ab55e141a7965b05f660d07745619 Mon Sep 17 00:00:00 2001
From: Ekaitz Zarraga <ekaitz@elenq.tech>
Date: Thu, 6 Aug 2020 00:32:47 +0200
Subject: Correct invoice count:
- Separate books for types (sent/received)
- Separate count for each series
- Remove id from products too -> use description instead
---
fracture/db.py | 43 +++++++++++++++++++++++------------
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"),
--
cgit v1.2.3