Integrating Product table to Invoice Generation system

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 tk_invoice_products import my_dict,my_list

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

product.trace("w", my_price)  # Call the function on change

CREATE TABLE `plus2_product` (
  `p_id` int(11) NOT NULL,
  `p_name` varchar(25) NOT NULL,
  `price` float(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 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`
  MODIFY `p_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

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()
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="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",
    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", "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
    trv.insert(
        "",
        "end",
        iid=iid,
        values=(iid, p_id, 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"][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 = 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[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
my_w.mainloop()

Query to get products based on 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  \
LEFT JOIN \
(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  \
LEFT JOIN  \
plus2_product as t2 on t1.p_id=t2.p_id  \
WHERE stock > 2 "