Invoice Generation system

";require "templates/body_start.php";?> Adding products to generate Invoice using Tkinter Treeview

User can select Product from dropdown combobx and then enter quantity and price per unit. On click of the Add button one row with row wise total price ( Unit price x Quantity ) will be inserted as a row in the treeview. At the same time sub-total price and tax will be updated and Sum will be reflected as total price.

Above step will be repeated for each addition or removal of product row from the bill ( Treeview )

my_add()

Increment the Serial number by one and calculate the total by multiplying quantity with unit price. Insert the details to Treeview

def my_add():    global iid    iid=iid+1  # Serial number to display     total=round(qty.get()*prc.get(),2) # row wise total     trv.insert("", 'end',iid=iid, values =(iid,product.get(),qty.get(),prc.get(),total))    my_upd(trv)

my_upd()

Loop through all rows and calculate the sub total price by adding row wise total. Shows to final price after adding tax of % . This function is called each time if new row ( product ) added or delated.

def my_upd(trv):    global total     total,sub_total=0,0    for child in trv.get_children():        sub_total=round(sub_total+float(trv.item(child)["values"][4]),2)    l6.config(text=str(sub_total)) # shows sub total     tax=round(0.1*sub_total,2)  # 10 % tax rate, update here    l8.config(text=str(tax))  # tax amount is displayed     total=round(sub_total+tax,2) # tax added to sub total     l10.config(text=str(total))  # Final price is displayed    product.set('') # reset the combobox     qty.set(1)  # reset quantity to 1    prc.set(0.0) # reset price to 0.0

my_select()

Once a row in Treeview is selected by user this function enables the delete button.

def my_select(self):    b2.config(state='active')

Data_delete()

Reads the user selected row id and removes the same from the Treeview and call my_upd() to re-calculate the totals.

def data_delete():    p_id = trv.selection()[0] # collect selected row id    trv.delete(p_id)    b2['state']='disabled'    my_upd(trv) # Update the total

my_reset()

Removes all the rows from Treeview, set all total and taxes to zero and update the displays to zero value.

def my_reset():    for item in trv.get_children():        trv.delete(item) # remove row     global total    total=0    product.set('') # reset combobox    qty.set(1) # Update quantity to 1     prc.set(0.0) # Update price to 0.0    l6.config(text='0')  # Update display sub total    l8.config(text='0')  # Update display for tax    l10.config(text='0') # Update display for total

Insert_data()

Insert the total and todays date to plus2_invoice table. Collects the unique invoice id from the mysql database table and then use the same to add all the products with details like quantity , unity price to plus2_invoice_dtl table.

This function only requires database connection and executes when Confirm button is clicked.
Copy the SQL dump to create the two tables ( plus2_invoice , plus2_invoice_dtl )

def insert_data():    global total     dt = date.today() # Today's date     data=(total,dt) # Data for parameterized query    query="INSERT INTO plus2_invoice ( total, dt) values(%s,%s)"    #print(query)    id=my_conn.execute(query,data)    inv_id=id.lastrowid # get the bill or invoice number after adding data        query="INSERT INTO  plus2_invoice_dtl (inv_id,p_id,product,qty,price) \	VALUES(%s,%s,%s,%s,%s)"    my_data=[] # list to store multiple rows of data    # In all rows inventory id is same    for line in trv.get_children():        my_list=trv.item(line)['values']        my_data.append([inv_id,my_list[0],my_list[1],my_list[2],my_list[3]])    id=my_conn.execute(query,my_data) # adding list of products to table    #print("Rows Added  = ",id.rowcount)    l_msg.config(text='Bill No:'+str(inv_id)+',Products:'+str(id.rowcount))    l_msg.after(3000, lambda: l_msg.config(text='') )    my_reset() # reset function

Generating Invoice by adding product, price and quantity using Treeview and storing data in database

SQL Dump of Sample tables

CREATE TABLE `plus2_invoice` (  `inv_id` int(4) NOT NULL,  `total` float NOT NULL,  `dt` date NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- ------------------------------------------------------------ Table structure for table `plus2_invoice_dtl`--CREATE TABLE `plus2_invoice_dtl` (  `dtl_id` int(11) NOT NULL,  `inv_id` int(6) NOT NULL,  `p_id` int(4) NOT NULL,  `product` varchar(50) NOT NULL,  `qty` int(3) NOT NULL,  `price` float NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Indexes for dumped tables------ Indexes for table `plus2_invoice`--ALTER TABLE `plus2_invoice`  ADD PRIMARY KEY (`inv_id`);---- Indexes for table `plus2_invoice_dtl`--ALTER TABLE `plus2_invoice_dtl`  ADD PRIMARY KEY (`dtl_id`);---- AUTO_INCREMENT for dumped tables------ AUTO_INCREMENT for table `plus2_invoice`--ALTER TABLE `plus2_invoice`  MODIFY `inv_id` int(4) NOT NULL AUTO_INCREMENT;---- AUTO_INCREMENT for table `plus2_invoice_dtl`--ALTER TABLE `plus2_invoice_dtl`  MODIFY `dtl_id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

from tkinter import ttkimport tkinter as tkfrom  datetime import datefrom sqlalchemy import create_enginemy_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")   # Creating tkinter my_wmy_w = tk.Tk()my_w.geometry("730x500") my_w.title("www.plus2net.com") font1=['Times',16,'normal'] # font size and style font2=['Times',22,'normal']my_img = tk.PhotoImage(file = "E:\\top2.png") # Use your pathl_img = tk.Label(my_w,  image=my_img ) # show image l_img.grid(row=0,column=0,padx=10,pady=5)l1=tk.Label(my_w,text='Product',font=font1)l1.grid(row=1,column=0,padx=10,pady=5)p_list=['Moniter','Mouse','Keyboard','Pen Drive','CPU','Power Unit'] # product listproduct=tk.StringVar(my_w)cb_product = ttk.Combobox(my_w, values=p_list,textvariable=product,width=10)cb_product.grid(row=1,column=1)##l2=tk.Label(my_w,text='Quantity',font=font1)l2.grid(row=1,column=2,padx=20,pady=10)qty=tk.IntVar(value=1)quantity = tk.Entry(my_w, textvariable=qty,width=5)quantity.grid(row=1,column=3)##l3=tk.Label(my_w,text='Price',font=font1)l3.grid(row=1,column=4,padx=20,pady=10)prc=tk.DoubleVar()price = tk.Entry(my_w, textvariable=prc,width=10)price.grid(row=1,column=5)##b1=tk.Button(my_w,text='Add',font=14,command=lambda:my_add())b1.grid(row=1,column=6)##style = ttk.Style(my_w) style.theme_use("clam") # set theam to clamstyle.configure("Treeview", background="azure2",                 fieldbackground="lightyellow", foreground="black",font=font1)style.configure('Treeview.Heading', background="PowderBlue") # Using treeview widgettrv = ttk.Treeview(my_w, selectmode ='browse')trv.grid(row=2,column=0,columnspan=7,rowspan=2,padx=10,pady=2)# number of columnstrv["columns"] = ("1", "2", "3","4","5")trv['show'] = 'headings'trv.column("1", width = 40, anchor ='c') # width & alignmenttrv.column("2", width = 250, anchor ='w')trv.column("3", width = 70, anchor ='c')trv.column("4", width = 90, anchor ='e')trv.column("5", width = 100, anchor ='e')trv.heading("1", text ="Sl No") # Heading text trv.heading("2", text ="Product")trv.heading("3", text ="Quantity")trv.heading("4", text ="Rate")  trv.heading("5", text ="Total")  l5=tk.Label(my_w,text='Total :',fg='blue',font=font1,anchor='e')l5.grid(row=4,column=4)l6=tk.Label(my_w,text='0',fg='blue',font=font1,anchor='e')l6.grid(row=4,column=5)l7=tk.Label(my_w,text='Tax 10 % :',fg='blue',font=font1,anchor='e')l7.grid(row=5,column=4)l8=tk.Label(my_w,text='0',fg='blue',font=font1,anchor='e')l8.grid(row=5,column=5)l9=tk.Label(my_w,text='Total :',fg='red',font=font2,anchor='e')l9.grid(row=6,column=4)l10=tk.Label(my_w,text='0',fg='red',font=font2,anchor='e')l10.grid(row=6,column=5,pady=20)    b2=tk.Button(my_w,text='Delete',state='disabled',command=lambda:data_delete())b2.grid(row=2,column=6)b3=tk.Button(my_w,text='Del All',command=lambda:my_reset())b3.grid(row=3,column=6,padx=1)b4=tk.Button(my_w,text='Confirm ',font=font2,bg='lightyellow',command=lambda:insert_data())b4.grid(row=6,column=2)l_msg=tk.Label(my_w,text='',fg='red',font=12)l_msg.grid(row=6,column=0,columnspan=2)total,iid=0,0def my_add():    global iid    iid=iid+1  # Serial number to display     total=round(qty.get()*prc.get(),2) # row wise total     trv.insert("", 'end',iid=iid, values =(iid,product.get(),qty.get(),prc.get(),total))    my_upd(trv)def my_upd(trv):    global total     total,sub_total=0,0    for child in trv.get_children():        sub_total=round(sub_total+float(trv.item(child)["values"][4]),2)    l6.config(text=str(sub_total)) # shows sub total     tax=round(0.1*sub_total,2)  # 10 % tax rate, update here    l8.config(text=str(tax))  # tax amount is displayed     total=round(sub_total+tax,2) # tax added to sub total     l10.config(text=str(total))  # Final price is displayed    product.set('') # reset the combobox     qty.set(1)  # reset quantity to 1    prc.set(0.0) # reset price to 0.0 def my_select(self):    b2.config(state='active') # Delete button is active now def data_delete():    p_id = trv.selection()[0] # collect selected row id    trv.delete(p_id)  # remove the selected row from Treeview    b2['state']='disabled' # disable the button     my_upd(trv) # Update the total def my_reset():    for item in trv.get_children():        trv.delete(item) # remove row     global total    total=0    product.set('') # reset combobox    qty.set(1) # Update quantity to 1     prc.set(0.0) # Update price to 0.0    l6.config(text='0')  # Update display sub total    l8.config(text='0')  # Update display for tax    l10.config(text='0') # Update display for totaldef insert_data():    global total     dt = date.today() # Today's date     data=(total,dt) # Data for parameterized query    query="INSERT INTO plus2_invoice ( total, dt) values(%s,%s)"    #print(query)    id=my_conn.execute(query,data)    inv_id=id.lastrowid # get the bill or invoice number after adding data        query="INSERT INTO  plus2_invoice_dtl (inv_id,p_id,product,qty,price) \         VALUES(%s,%s,%s,%s,%s)"    my_data=[] # list to store multiple rows of data    # In all rows inventory id is same    for line in trv.get_children():        my_list=trv.item(line)['values']        my_data.append([inv_id,my_list[0],my_list[1],my_list[2],my_list[3]])    id=my_conn.execute(query,my_data) # adding list of products to table    #print("Rows Added  = ",id.rowcount)    l_msg.config(text='Bill No:'+str(inv_id)+',Products:'+str(id.rowcount))    l_msg.after(3000, lambda: l_msg.config(text='') )    my_reset() # reset function trv.bind("<<TreeviewSelect>>", my_select)  # User selection of rowmy_w.mainloop()

Integration of product table

While selecting the options of the Combobox we can get data from a Product table. Once the user selects the product, the price and the product id ( p_id) is collected. User has to enter the quantity only.
Integration of MySQL Product table to Combobox of Invoice Generation Inserting product details to procurement table

Display MySQL records in Treeview Pagination of MySQL records in Treeview
Displaying MySQL records using Entry or Label Delete MySQL record
Treeview Treeview insert Treeview parent child node Select -Edit-update MySQL Product table using Treeview Query window & displaying records in Treeview

Python Tkinter Dictionary Python- Tutorials