Inserting Product sales data with with a Bill number to generate Invoice- Restaurant Management

Restaurant Management V-2 with invoice generation

Tkinter Storing product sale with details in table and generate Invoice Restaurant Management system

The Part -3 of the script should have these added features

Once the details of selection of product with total and tax details are shown ( Check the Part II of Restaurant Management ) the following features are to be added.

On click of the Reset / confirmation button the display part will reset to blank ( listing of sold products ) and all the spinboxes will reset to 0.
As the sale is confirmed, one unique bill number is to be generated by inserting data , total price and tax price to the plus2_bill table.
Using the unique bill number , all sold product details like product id, quantity , price with bill number and data are to be inserted to plus2_sell table.
One button will be displayed saying Generate bill and on click of it the function my_invoice() will be triggered.
The function my_invoice will receive the bill number and using this it will generate the Invoice.
The function my_invoice will collect all the names of the product sold, with price, quantity and generate the full invoice.

main-db-3.py

import tkinter as tk
from tkinter import *
from tkinter import ttk
from my_connect import my_conn
from  datetime import date
from my_invoice import my_invoice
from config import *
sb=[]
my_w = tk.Tk()
my_w.geometry("1000x800") 
my_w.columnconfigure(0,weight=8)
my_w.columnconfigure(1,weight=2)
my_w.rowconfigure(0, weight=1) 
my_w.rowconfigure(1, weight=14) # change weight to 4
my_w.rowconfigure(2, weight=2)

frame_top=tk.Frame(my_w,bg='white')
frame_bottom=tk.Frame(my_w,bg='white')

frame_m_right=tk.Frame(my_w,bg='#f8fab4')
frame_m_left=tk.Frame(my_w,bg='#284474')

#placing in grid
frame_top.grid(row=0,column=0,sticky='WENS',columnspan=2)
frame_m_left.grid(row=1,column=0,sticky='WENS')
frame_m_right.grid(row=1,column=1,sticky='WENS')
frame_bottom.grid(row=2,column=0,sticky='WENS',columnspan=2)
trv = ttk.Treeview(frame_m_right, selectmode ='browse')
trv.grid(row=0,column=0,columnspan=2,padx=3,pady=2)

# column identifiers 
trv["columns"] = ("1", "2","3")
trv.column("#0", width = 80, anchor ='w')
trv.column("1", width = 60, anchor ='w')
trv.column("2", width =50 , anchor ='c')
trv.column("3", width = 50, anchor ='c')
  
# Headings  
# respective columns
trv.heading("#0", text ="Item",anchor='w')
trv.heading("1", text ="Price",anchor='w')
trv.heading("2", text ="qty",anchor='c')
trv.heading("3", text ="Total",anchor='c')
def my_reset():
    global total, tax
    for item in trv.get_children():
        trv.delete(item)
    l1=[]
    for i in range(8):
        l1.append(tk.IntVar(value=0))
    for i in range(len(sb)):
        sb[i].config(textvariable=l1[i])

for w in frame_m_right.grid_slaves(1):
        w.grid_remove()
    for w in frame_m_right.grid_slaves(2):
        w.grid_remove()    
    for w in frame_m_right.grid_slaves(3):
        w.grid_remove()
    dt=date.today().strftime('%Y-%m-%d') # today's date
    query="INSERT INTO  plus2_bill (total,tax,`bill_date`) \
                  VALUES (%s,%s,%s)"
    data=[total,tax,dt]
    id=my_conn.execute(query,data)
    #print(total, tax)
    bill_no=id.lastrowid
    for i in dl:
        i.insert(3,bill_no)
        i.insert(4,dt)
    query="INSERT INTO plus2_sell(p_id,price,quantity,bill_no,bill_date)\
                  VALUES(%s,%s,%s,%s,%s)"
    id=my_conn.execute(query,dl)
    #print("Rows Added  = ",id.rowcount)  
    lr1=tk.Button(frame_m_right,text='Bill',font=font1,command=lambda:my_invoice(my_w,bill_no,img_top))
    lr1.grid(row=1,column=0,sticky='nw')                
dl=[]    
total,tax,final=0,0,0
def my_bill():
    global dl,total,tax,final 
    total,tax,final=0,0,0
    dl.clear()
    for item in trv.get_children():
        trv.delete(item)
    
    for i in range(len(sb)):
        if(int(sb[i].get())>0): 
            price=int(sb[i].get())*my_menu[i][2]
            total=round(total+price,2)
            my_str1=(str(my_menu[i][2]), str(sb[i].get()), str(price))
            trv.insert("",'end',iid=i,text=my_menu[i][1],values=my_str1)
            dl.append([my_menu[i][0],my_menu[i][2],int(sb[i].get())])
    lr1=tk.Label(frame_m_right,text='Total',font=font1)
    lr1.grid(row=1,column=0,sticky='nw')
    lr2=tk.Label(frame_m_right,text=str(total),font=font1)
    lr2.grid(row=1,column=1,sticky='nw')
    lr21=tk.Label(frame_m_right,text='Tax 10%',font=font1)
    lr21.grid(row=2,column=0,sticky='nw')
    tax=round(0.1*total,2)
    lr22=tk.Label(frame_m_right,text=str(tax),font=font1)
    lr22.grid(row=2,column=1,sticky='nw')
    lr31=tk.Label(frame_m_right,text='Total',font=font2)
    lr31.grid(row=3,column=0,sticky='nw')
    final=round(total+tax,2)
    lr32=tk.Label(frame_m_right,text=str(final),font=font2)
    lr32.grid(row=3,column=1,sticky='nw')
    # add data to to database table plus2_bill 
    
    #print(dt)
    
        
# Layout is over , start placing buttons 
#path_image="G:\\My Drive\\testing\\plus2_restaurant_v1\\images\\"
font1=('Times',14,'normal')
font2=('Times',32,'bold')
pdx,pdy=1,5
img_top = tk.PhotoImage(file = path_image+"restaurant-3.png")
bg=tk.PhotoImage(file=path_image+'bg2.png')

c1 = tk.Canvas(frame_m_left,width=1000,height=500)
c1.grid(row=0,column=0,columnspan=4,rowspan=4,sticky='nw',padx=0)
c1.create_image(0,0,image=bg,anchor='nw')

img_l1 = tk.Label(frame_top,  image=img_top)
img_l1.grid(row=0,column=0,sticky='nw',pady=1)
img_l1.image=img_top
my_menu={} # Dictionary to store items with price
sb=[]
r,c,i=0,0,0
def show_items(cat):
    global r,c,i,my_menu
    my_menu.clear() # remove all items
    sb.clear() 
    r,c,i=0,0,0
    r_set=my_conn.execute("SELECT * FROM plus2_products WHERE \
       available=1 and  p_cat="+str(cat))
    
    for item in r_set: 
        menu=tk.Label(frame_m_left,text=item[1]+'('+str(item[3])+')',font=font1)
        menu.grid(row=r,column=c,padx=pdx,pady=0)    
        r1=r+1
        sbox = tk.Spinbox(frame_m_left,from_=0,to_=5,font=font2,width=1)
        sbox.grid(row=r1,column=c,padx=pdx,pady=0)
        sb.append(sbox)  
        my_menu[i]=[item[0],item[1],item[3]]
        i=i+1
        if(c>2): # add one more row 
            c=0
            r=r+2
        else:
            c=c+1

show_items(1)
r=r+1
r1_v = tk.IntVar(value=1) # We used integer variable here

r1 = tk.Radiobutton(frame_bottom, text='Breakfast', variable=r1_v, value=1,command=lambda:show_items(1))
r1.grid(row=r,column=0)

r2 = tk.Radiobutton(frame_bottom, text='Lunch', variable=r1_v, value=0,command=lambda:show_items(2))
r2.grid(row=r,column=1)

r3 = tk.Radiobutton(frame_bottom, text='Dinner', variable=r1_v, value=5,command=lambda:show_items(3))
r3.grid(row=r,column=2)

b1=tk.Button(frame_bottom,text='Get Bill',command=my_bill)
b1.grid(row=r,column=3,padx=10)
b2=tk.Button(frame_bottom,text='Confirm ( Reset)',command=my_reset)
b2.grid(row=r,column=4,padx=10)
my_w.mainloop()

my_connect.py

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:pw@localhost/db_name")

MySQL Installing
MySQL Connecting string
MySQL connection using SQLAlchemy

my_invoice.py

import tkinter as tk
from my_connect import my_conn

def my_invoice(my_w,bill_no,img_top2):
    import tkinter as tk
    #global path_image,img_top2
    my_w_child=tk.Toplevel(my_w,background='#FFFFFF')
    my_w_child.geometry("800x600")
    my_w_child.title('www.plus2net.com')
    font1=('Times',32,'bold')
    font2=('Times',20,'normal')
    font3=('Times',16,'normal')
    my_line='--'
    for i in range(13):
        my_line=my_line+'------'
    query='SELECT * FROM plus2_bill WHERE bill_no=%s'
    my_cursor=my_conn.execute(query,bill_no)
    my_result = my_cursor.fetchone()
    dt=my_result[1].strftime('%d-%B-%Y')
    #print(my_result)
    query='SELECT b.p_name,a.p_id,a.price,a.quantity  FROM plus2_sell a, plus2_products b WHERE a.p_id=b.p_id and a.bill_no=%s '
    my_cursor2=my_conn.execute(query,bill_no)
    my_result2 = my_cursor2.fetchall()
    
    #img_top2 = tk.PhotoImage(file = path_image+"restaurant-4.png")
    #img_top2 = ImageTk.PhotoImage(Image.open("G:\\My Drive\\testing\\plus2_restaurant_v1\\images\\restaurant-4.jpg"))
    
    img_l_c = tk.Label(my_w_child,  image=img_top2)
    img_l_c.grid(row=0,column=0)
    #img_l_c.draw()
    l1=tk.Label(my_w_child,text='Invoice',font=font1,fg='blue',bg='#ffffff')
    l1.grid(row=0,column=1)
    l2=tk.Label(my_w_child,text='Bill No :#'+str(bill_no),font=font2,bg='#ffffff')
    l2.grid(row=0,column=2,padx=10)
    l3=tk.Label(my_w_child,text='Date:'+dt,font=font2,bg='#ffffff')
    l3.grid(row=0,column=3,padx=10)
    l3=tk.Label(my_w_child,text=my_line,font=font2,bg='#ffffff')
    l3.grid(row=1,column=0,padx=10,columnspan=4,sticky='w')
    r=2
    #print(my_result2)
    total=0
    for i in my_result2:
        l_item=tk.Label(my_w_child,text=i[0],bg='#ffffff',font=font3)
        l_item.grid(row=r,column=0)
        l_item1=tk.Label(my_w_child,text=i[2],font=font3)
        l_item1.grid(row=r,column=1)
        l_item1=tk.Label(my_w_child,text=i[3],bg='#ffffff',font=font3,fg='blue')
        l_item1.grid(row=r,column=2)
        price=round(i[2]*i[3],2)
        l_item1=tk.Label(my_w_child,text=str(price),bg='#ffffff',font=font2)
        l_item1.grid(row=r,column=3,sticky='ne')
        total=round(total+price,2)
        r=r+1
    l4=tk.Label(my_w_child,text=my_line,font=font2,bg='#ffffff')
    l4.grid(row=r,column=0,padx=10,columnspan=4,sticky='w')
    r=r+1
    lr1=tk.Label(my_w_child,text='Total',font=font2)
    lr1.grid(row=r,column=0,sticky='ne')
    lr2=tk.Label(my_w_child,text=str(total),font=font2)
    lr2.grid(row=r,column=1,sticky='ne',columnspan=3)
    r=r+1
    lr21=tk.Label(my_w_child,text='Tax 10%',font=font2)
    lr21.grid(row=r,column=0,sticky='ne')
    tax=round(0.1*total,2)
    lr22=tk.Label(my_w_child,text=str(tax),font=font2)
    lr22.grid(row=r,column=1,sticky='ne',columnspan=3)
    r=r+1
    lr31=tk.Label(my_w_child,text='Total',font=font1,bg='#ffffff')
    lr31.grid(row=r,column=0,sticky='ne')
    final=round(total+tax,2)
    lr32=tk.Label(my_w_child,text=str(final),font=font1,bg='#ffffff',fg='red')
    lr32.grid(row=r,column=1,sticky='ne',columnspan=3)

Create table plus2_bill, plus2_sell and to create product table plus2_products and to insert sample data, use this sql dump.

-- Database: `plus2_restaurant`
--

-- --------------------------------------------------------

--
-- Table structure for table `plus2_bill`
--

CREATE TABLE IF NOT EXISTS `plus2_bill` (
  `bill_no` int(11) NOT NULL AUTO_INCREMENT,
  `bill_date` date NOT NULL,
  `total` float(8,2) NOT NULL,
  `tax` float(8,2) NOT NULL,
  PRIMARY KEY (`bill_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

-- --------------------------------------------------------

--
-- Table structure for table `plus2_products`
--

CREATE TABLE IF NOT EXISTS `plus2_products` (
  `p_id` int(3) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(100) NOT NULL,
  `unit` varchar(20) NOT NULL,
  `price` float(8,2) NOT NULL,
  `p_cat` int(1) NOT NULL,
  `available` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `plus2_products`
--

INSERT INTO `plus2_products` (`p_id`, `p_name`, `unit`, `price`, `p_cat`, `available`) VALUES
(1, 'Item-1-BF', 'Number', 20.00, 1, 1),
(2, 'Item-2-BF', 'Number', 30.00, 1, 1),
(3, 'Item-3-BF', 'Number', 30.40, 1, 1),
(4, 'Item-4-BF', 'Number', 45.80, 1, 1),
(5, 'Item-5-BF', 'Number', 15.07, 1, 1),
(6, 'Item-6-BF', 'Number', 45.70, 1, 1),
(7, 'Item-7-BF', 'Number', 78.00, 1, 1),
(8, 'Item-8-BF', 'Number', 87.00, 1, 1),
(9, 'Item-9-Lunch', 'Packet', 150.00, 2, 1),
(10, 'Item-10-Lunch', 'One Plate', 180.00, 2, 1),
(11, 'Item-11-Lunch', 'Plate', 50.00, 2, 1),
(12, 'Item-12-Lunch', 'Plate', 90.00, 2, 1),
(13, 'Item-13-Lunch', 'Packet', 90.00, 2, 1),
(14, 'Item-14-Lunch', 'Plate', 110.00, 2, 1),
(15, 'Item-15-Lunch', 'Plate', 120.00, 2, 1),
(16, 'Item-16-Lunch', 'Packet', 200.00, 2, 1),
(17, 'Item-17-Dinner', 'Plate', 545.00, 3, 1),
(18, 'Item-18-Dinner', 'Packet', 500.00, 3, 1),
(19, 'Item-19-Dinner', 'Number', 80.00, 3, 1),
(20, 'Item-20-Dinner', 'Number', 110.00, 3, 1),
(21, 'Item-21-Dinner', 'Plate', 200.00, 3, 1),
(22, 'Item-22-Dinner', 'Packet', 500.00, 3, 1),
(23, 'Item-23-Dinner', 'Plate', 120.00, 3, 1),
(24, 'Item-24-Dinner', 'Plate', 180.00, 3, 1);

-- --------------------------------------------------------

--
-- Table structure for table `plus2_sell`
--

CREATE TABLE IF NOT EXISTS `plus2_sell` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(3) NOT NULL,
  `price` float NOT NULL,
  `quantity` int(11) NOT NULL,
  `bill_no` int(4) NOT NULL,
  `bill_date` date NOT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

config.py

path_image="G:\\My Drive\\testing\\plus2_restaurant_v1\\images\\"
font1=('Times',14,'normal')
font2=('Times',32,'bold')

Adding sample data to tables

Here is the script to first remove all records from plus2_bill and plus2_sell, then insert records for previous n ( check the for loop at the end ) days. No data is added to plus2_products table.

my_insert.py

import random
from datetime import timedelta,date
from my_connect import my_conn # database connection. 
#query="TRUNCATE plus2_sell"# remove all data from table 
#my_conn.execute(query) # remove all data from table 
#query="TRUNCATE plus2_bill"# remove all data from table 
#my_conn.execute(query)# remove all data from table

d=date.today() # today's date 
def my_insert(td):
    dt=d+ timedelta(days=-td) # date object created 
    dt=dt.strftime('%Y-%m-%d') # date string 
    dl=[] # list to store data
    total=0 
    no_of_sell=random.randint(3,6) # number of sale in a day
    for i in range(no_of_sell):
        dl_item=[]
        p_id=random.randint(1,8)
        quantity=random.randint(1,5)
        query="SELECT price FROM plus2_products WHERE p_id = %s"
        my_cursor=my_conn.execute(query,p_id)
        my_result = my_cursor.fetchone()
        #print(my_result[0])
        dl_item.append(p_id)
        dl_item.append(my_result[0])
        dl_item.append(quantity)
        price=my_result[0]*quantity
        total=round(total+price,2)
        dl.append(dl_item)
    tax=round(0.1*total,2)
    
    query="INSERT INTO  plus2_bill (total,tax,`bill_date`) \
                  VALUES (%s,%s,%s)"
    data=[total,tax,dt]
    id=my_conn.execute(query,data)
    bill_no=id.lastrowid
    for i in dl:
        i.insert(3,bill_no)
        i.insert(4,dt)
    query="INSERT INTO plus2_sell(p_id,price,quantity,bill_no,bill_date)\
                  VALUES(%s,%s,%s,%s,%s)"
    id=my_conn.execute(query,dl)
for i in range(0,5): # increase the range to store more days
    my_insert(i)

Restaurant Management V-1 Restaurant Management V-2 Creating Tables
More Projects using Tkinter

Python Tkinter Dictionary Python- Tutorials

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

Restaurant Management System V-3 ( Invoice Generation )

The Part -3 of the script should have these added features

Adding sample data to tables

Subhendu Mohapatra