Correct invoice count:

    - Separate books for types (sent/received)
    - Separate count for each series
    - Remove id from products too -> use description instead

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"),