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

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

Subscribe