Integrating Product table to Invoice Generation system

Adding products to generate Invoice using Tkinter Treeview

While selecting the options of the Combobox we can get data from a Product table. Once the use selects the product, the price and the product id ( p_id) is collected. User has to enter the quantity only.
We get the dictionary my_dict with all product details and one list my_list from file.
Here is the code for the file
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
query = "SELECT *  FROM plus2_product"
my_data = list(my_conn.execute(query))  # SQLAlchem engine result set
my_dict = {}  # Create an empty dictionary
my_list = []  # Create an empty list
for row in my_data:
    my_dict[[row][0][0]] = row  # id as key
    my_list.append(row[1])  # name as list
From our main invoice generation script we will call the variables like this
from tk_invoice_products import my_dict,my_list
By using product table we can mange the stock position and control the product availability by adding business logics.
While selecting and adding the product to Treeview one more column for Product id is added. Here if we are adding any product outside of the available choices, then p_id value is set to 0.
def my_price(*args):  # *args is used to pass any number of arguments
    l1.config(text="")  # Clear the label
    global p_id
    p_id=0 # If product is not selected from the options then id is 0 
    for i, j in my_dict.items():  # Loop through the dictionary
        if j[1] == product.get():  # match the product name
            prc.set(j[2]) # price is collected. 
            p_id=j[0]  # Product id is collected
To trigger the price adding process we used trace method of stringVar() product connected to textvariable option of Combobox cb_product.
product.trace("w", my_price)  # Call the function on change
To calculate the Sub total price the column for row total is used but it is changed 4th column as one more column for p_id is included.
While storing the products in plus2_invoice_dtl the product id ( p_id ) is stored in place of serial number ( above code ) .
Here is the SQL dump to create your sample product table.
CREATE TABLE `plus2_product` (
  `p_id` int(11) NOT NULL,
  `p_name` varchar(25) NOT NULL,
  `price` float(8,2) NOT NULL

-- Dumping data for table `plus2_product`

INSERT INTO `plus2_product` (`p_id`, `p_name`, `price`) VALUES
(1, 'Mouse', 12.34),
(2, 'Key Board', 18.76),
(3, 'Moniter', 20.45),
(4, 'CPU', 20.35),
(5, 'Pen Drive', 8.50),
(6, 'Operating System', 10.23);

-- Indexes for dumped tables

-- Indexes for table `plus2_product`
ALTER TABLE `plus2_product`
  ADD PRIMARY KEY (`p_id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `plus2_product`
ALTER TABLE `plus2_product`
Integrating MySQL Product table with id and price to select by user in Invoice generation system
from tkinter import ttk
import tkinter as tk
from datetime import date
from sqlalchemy import create_engine
from tk_invoice_products import my_dict, my_list

# print(my_dict)
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
# Creating tkinter my_w
my_w = tk.Tk()
font1 = ["Times", 16, "normal"]  # font size and style
font2 = ["Times", 22, "normal"]
my_img = tk.PhotoImage(file="H:\\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)

product = tk.StringVar(my_w)
cb_product = ttk.Combobox(my_w, values=my_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.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", "6")
trv["show"] = "headings"
trv.column("1", width=40, anchor="c")  # width & alignment
trv.column("2", width=40, anchor="w")
trv.column("3", width=200, anchor="w")
trv.column("4", width=60, anchor="e")
trv.column("5", width=100, anchor="e")
trv.column("6", width=100, anchor="e")
trv.heading("1", text="Sl No")  # Heading text
trv.heading("2", text="p_id")  # Heading text
trv.heading("3", text="Product")
trv.heading("4", text="Quantity")
trv.heading("5", text="Rate")
trv.heading("6", 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, p_id = 0, 0, 0

def my_price(*args):  # *args is used to pass any number of arguments
    l1.config(text="")  # Clear the label
    global p_id
    p_id = 0  # If product is not selected from the options then id is 0
    for i, j in my_dict.items():  # Loop through the dictionary
        if j[1] == product.get():  # match the product name
            prc.set(j[2])  # price is collected.
            p_id = j[0]  # Product id is collected

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

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"][5]), 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 =  # 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) \
    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[1], my_list[2], my_list[3], my_list[4]])
    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(10000, lambda: l_msg.config(text=""))  # delay to remove message
    my_reset()  # reset function

trv.bind("<<TreeviewSelect>>", my_select)  # User selection of row
product.trace("w", my_price)  # Call the function on change

Query to get products based on stock position

Invoice generation by allowing products with minimum stock level based on procurement and sales data

We can use purchase table where we will store all procurement details. Here the quantity against each product is decided based on procurement quantity minus the sales quantity.

Details about the tables and SQL dump with sample data

In our page we will replace the query with our new query to take care about the stock position.
# query = "SELECT *  FROM plus2_product "
query = "SELECT t2.p_id,t2.p_name,t2.price FROM (  \
SELECT a.p_id,a.product,SUM(a.qty) AS receive , COALESCE(b.sold,0) AS sold, \
(SUM(a.qty) - COALESCE(b.sold,0)) AS stock FROM plus2_product_receive a  \
(SELECT p_id,product,SUM(qty) AS sold FROM `plus2_invoice_dtl` GROUP BY p_id) b  \
 ON a.p_id=b.p_id \
GROUP BY a.p_id) AS t1  \
plus2_product as t2 on t1.p_id=t2.p_id  \
WHERE stock > 2 "
Invoice Generation
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
Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2023 All rights reserved worldwide Privacy Policy Disclaimer