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 ttk
import tkinter as tk
from datetime import date
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
# Creating tkinter my_w
my_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 path
l_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 list
product=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 clam
style.configure("Treeview", background="azure2",
fieldbackground="lightyellow", foreground="black",font=font1)
style.configure('Treeview.Heading', background="PowderBlue")
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=2,column=0,columnspan=7,rowspan=2,padx=10,pady=2)
# number of columns
trv["columns"] = ("1", "2", "3","4","5")
trv['show'] = 'headings'
trv.column("1", width = 40, anchor ='c') # width & alignment
trv.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,0
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)
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 total
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
trv.bind("<<TreeviewSelect>>", my_select) # User selection of row
my_w.mainloop()