From 5e37e877c1d4181a1ed54552875cec6af6abaa91 Mon Sep 17 00:00:00 2001 From: Ekaitz Zarraga Date: Mon, 27 Jul 2020 17:01:43 +0200 Subject: More work on load and print: - Load by year and quarter or year only - output as a dict --- fracture/__main__.py | 10 ++-- fracture/invoice.py | 158 +++++++++++++++++++++++++++++++++++---------------- 2 files changed, 114 insertions(+), 54 deletions(-) diff --git a/fracture/__main__.py b/fracture/__main__.py index 4805306..822cf96 100644 --- a/fracture/__main__.py +++ b/fracture/__main__.py @@ -109,8 +109,10 @@ def new_invoice(): # edit(Invoice.load(num).to_config()) @command -def summarize(xlsx=False, quarter=None, year=None): - print(xlsx) +def summarize(xlsx=False, year=None, quarter=None): + invoices = Invoice.load_by_date(year, quarter) + rows = map(lambda x: x.to_row(), invoices) + if __name__ == "__main__": load_config() @@ -135,9 +137,9 @@ if __name__ == "__main__": help="Display summary for tax declarations") summary_parser.add_argument("--xlsx", action="store_true", help="Output as xlsx") - summary_parser.add_argument("--quarter", type=int, nargs=1, + summary_parser.add_argument("--quarter", type=int, help="Obtain the summary of the quarter") - summary_parser.add_argument("--year", type=int, nargs=1, + summary_parser.add_argument("--year", type=int, help="Obtain the summary of the year") summary_parser.set_defaults(func=summarize) diff --git a/fracture/invoice.py b/fracture/invoice.py index 4ea9d71..02762f0 100644 --- a/fracture/invoice.py +++ b/fracture/invoice.py @@ -1,5 +1,5 @@ import sqlite3 -from datetime import date, datetime +from datetime import date, datetime, timedelta from configparser import ConfigParser import io @@ -43,17 +43,18 @@ class Invoice: idate = None, notes= None, products = None, - customers = None, - taxes = None): + customer = None, + taxes = None, + id = None): # Initializes to empty state if not set self.set_type(type or tuple(Invoice.TYPES)[0]) - self.id = None + self.id = id self.set_series(series or tuple(Invoice.SERIES.keys())[0]) self.date = idate or date.today() self.notes = notes or "" self.products = products or (Product(Invoice.DEFAULT_PRODUCT_DESC), Product(Invoice.DEFAULT_PRODUCT_DESC)) - self.customer = customers or Customer(Invoice.DEFAULT_CUSTOMER_DESC) + self.customer = customer or Customer(Invoice.DEFAULT_CUSTOMER_DESC) self.taxes = taxes or Invoice.DEFAULT_TAXES def set_series(self, series): @@ -69,12 +70,13 @@ class Invoice: % Invoice.TYPES) self.type = type - def dump(self): - print(self.format_id()) - def format_id(self): return Invoice.ID_FORMAT(self.series, self.date, self.id) + def __repr__(self): + return "\n\t"+\ + self.to_config().replace("\n", "\n\t") + \ + "\n" def to_config(self): strf = io.StringIO() cfg = ConfigParser() @@ -150,6 +152,82 @@ class Invoice: customer, taxes) + @classmethod + def load(cls, id): + with sqlite3.connect(cls.DB_FILE) as conn: + conn.row_factory = sqlite3.Row + + c = conn.cursor() + + # PRODUCTS + products = () + c.execute("SELECT * FROM products WHERE invoice_id = ?", (id,)) + res = c.fetchone() + while res is not None: + desc = res["description"] + un = res["units"] + pu = res["price_unit"] + vat = res["vat"] + products += (Product(desc,un,pu,vat),) + res = c.fetchone() + + # TAXES + taxes = () + c.execute("SELECT * FROM taxes WHERE invoice_id = ?", (id,)) + 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,)) + res = c.fetchone() + return cls( + id = id, + type = res["type"], + series = res["series"], + idate = datetime.strptime(res["date"],"%Y-%m-%d").date(), + notes = res["notes"], + products = products, + customer = Customer( res["customer_name"], + res["customer_id"], + res["customer_address"] ), + taxes = taxes) + + @classmethod + def load_many(cls, cond="", vals=(), 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 " + cond), vals) + return tuple( cls.load( int(r["id"]) ) for r in c.fetchall() ) + + @classmethod + def load_by_date(cls, year, quarter=None): + dbegin = date(year, 1, 1) + dend = date(year+1, 1, 1) + if quarter is not None: + month = (quarter - 1) * 3 + 1 + dbegin = date(year, month, 1) + endyear = year + endmonth = month + 4 + if endmonth > 12: + endyear = year+1 + endmonth = month + dend = date(endyear, endmonth, 1) + return cls.load_many( + """ WHERE + strftime('%s',?) <= strftime('%s',date) + AND + strftime('%s',date) < strftime('%s',?) + """, + (dbegin, dend)) + + def persist(self): conn = sqlite3.connect(Invoice.DB_FILE) c = conn.cursor() @@ -195,51 +273,31 @@ class Invoice: conn.commit() conn.close() return self.id - - @classmethod - def load(cls, id): - with sqlite3.connect(cls.DB_FILE) as conn: - conn.row_factory = sqlite3.Row - - c = conn.cursor() - - # PRODUCTS - products = () - c.execute("SELECT * FROM products WHERE invoice_id = ?", (id,)) - res = c.fetchone() - while res is not None: - desc = res["description"] - un = res["units"] - pu = res["price_unit"] - vat = res["vat"] - products += (Product(desc,un,pu,vat),) - res = c.fetchone() - - # TAXES - taxes = () - c.execute("SELECT * FROM taxes WHERE invoice_id = ?", (id,)) - 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,)) - res = c.fetchone() - return cls( - res["type"], - res["series"], - res["date"], - res["notes"], - products, - Customer( res["customer_name"], - res["customer_id"], - res["customer_address"] ), - taxes) - def format(self): # TODO # https://bugs.python.org/issue35111 pass + def to_row(self): + psum = ((i.price_unit*i.units, + i.price_unit*i.units*i.vat) for i in self.products) + base = 0 + vat = 0 + for i in psum: + base += i[0] + vat += i[1] + + row = { + "series": self.series, + "id": self.format_id(), + "date": self.date.strftime("%Y-%m-%d"), + "customer_id": self.customer.id, + "customer_name": self.customer.name, + "base": round(base, self.CURRENCY_DECIMAL), + "vat": round(vat, self.CURRENCY_DECIMAL) + } + + for i in self.taxes: + row["tax-" + i.name] = round(i.ratio * base, self.CURRENCY_DECIMAL) + return row -- cgit v1.2.3