%%file invoice-generator.py
import typer
import csv
import datetime
import os
import re
import xlsxwriter
import string
alicante_data = {
"address" : "Address: C402,Purvi Lotus Apartments;HSR 2nd sector Extn,Near SS Palya Lake,;Hosapaly,Bangalore 560068",
"GSTIN" :"27ASBLL55591Z0",
"CIN" : "U74900KA2015PTC078397",
"Vendor No":"97330282",
"HSN": "998314"
}
def generate_invoice_count(folder=None):
year = datetime.datetime.now().year
pattern = r"^AAPL-[A-Z0-9\-]+-(?P<count>\d\d\d)-" + f"{year}.xlsx$"
p = re.compile(pattern)
def get_count(text):
m = p.match(text)
return int(m.groupdict()['count'])
if not folder:
folder = os.getcwd()
files = [f for f in os.listdir(folder) if p.match(f)]
if files:
last = max(files, key=get_count)
return f"{get_count(last)+1}".zfill(3)
else:
return "001"
def invoice_number(customer_id):
year = datetime.datetime.now().year
count= generate_invoice_count()
return f"AAPL-{customer_id}-{count}-{year}"
def read_po(pofile):
with open(pofile) as f:
d_po = csv.DictReader(f)
return [line for line in d_po]
def read_customer_details(customerfile):
with open(customerfile) as f:
d_po = csv.DictReader(f)
return next(d_po)
def create_invoice_excel(invoice, document_num, customer_info, po_info):
def convert_cell_to_row_col(cell):
col = string.ascii_uppercase.index(cell[0])
row = int(cell[1:])-1
return row, col
def add_entry(cell, key, value, format_=None):
row, col = convert_cell_to_row_col(cell)
if format_:
worksheet.write(row, col, key, format_)
worksheet.write(row, col+1, value, format_)
else:
worksheet.write(row, col, key)
worksheet.write(row, col+1, value)
def add_details_from_dictionary(details, startline):
"""Write data from dictionary , handle address separately
and return next line number
"""
n = startline
address_lines = details['address'].split(";")
for i,line in enumerate(address_lines, start=n):
worksheet.write(f"A{i}", line)
n = n + len(address_lines)
without_address = {key:value for key,value in details.items() if key!="address"}
for key, value in without_address.items():
add_entry(f"A{n}", key, value, bold)
n = n + 1
return n
def alicante_details(worksheet, startline = 11):
"""write data from alicante and return next line number
"""
n = add_details_from_dictionary(alicante_data, startline=startline)
add_entry(f"A{n}", "Document No", document_num, bold)
return n+1
def customer_details(customer_info, startline):
return add_details_from_dictionary(customer_info, startline=startline)
def add_service_details(worksheet, po_info, hsn, startline):
for s, service in enumerate(po_info):
sitems = list(service.items())
sitems.insert(1, ('HSN/SAC', hsn))
service = dict(sitems)
row, col = convert_cell_to_row_col(f"A{startline}")
for c, key in enumerate(service.keys()):
worksheet.write(row, c, key, bold)
for c, value in enumerate(service.values()):
worksheet.write(row+s+1,c, value )
excelname = f"{invoice}.xlsx"
print("generating invoice", excelname)
workbook = xlsxwriter.Workbook(excelname)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
boldbig = workbook.add_format({'bold': True})
boldbig.set_font_size(18)
logo = '/home/vikrant/Pictures/alicante-logo.png'
worksheet.insert_image('A1',
logo,
{"x_scale": 0.54, "y_scale": 0.54}
)
merge_format = workbook.add_format({'align': 'center',
'bold':True})
merge_format.set_font_size(15)
worksheet.merge_range("A8:H8", f"INVOICE - {invoice}", merge_format)
worksheet.write("G10", datetime.datetime.now().strftime("%d %b %Y"))
worksheet.set_column('A:A', 12)
n = alicante_details(worksheet)
n = n + 1
worksheet.write(f"A{n}", "To:", bold)
n = n +1
n = customer_details(customer_info, startline=n)
add_service_details(worksheet, po_info, alicante_data['HSN'], startline=n)
workbook.close()
def generate_invoice(document_num:str, pofile:str, customer:str):
po_info = read_po(pofile)
customer_info = read_customer_details(customer)
invoice = invoice_number(customer_info['customer_id'])
print(po_info)
create_invoice_excel(invoice, document_num, customer_info, po_info)
if __name__ == "__main__":
typer.run(generate_invoice)