Integrating Product table to Invoice Generation system

";require "templates/body_start.php";?>

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 tk_invoice_products.py file.
Here is the code for the file tk_invoice_products.py

from sqlalchemy import create_enginemy_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 setmy_dict = {}  # Create an empty dictionarymy_list = []  # Create an empty listfor 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 manage 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) 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;

Full code with Product table : Copy to clipboard

from tkinter import ttkimport tkinter as tkfrom datetime import datefrom sqlalchemy import create_enginefrom 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_wmy_w = tk.Tk()my_w.geometry("730x500")my_w.title("www.plus2net.com")font1 = ["Times", 16, "normal"]  # font size and stylefont2 = ["Times", 22, "normal"]my_img = tk.PhotoImage(file="H:\\top2.png")  # Use your pathl_img = tk.Label(my_w, image=my_img)  # show imagel_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 theme to clamstyle.configure(    "Treeview",    background="azure2",    fieldbackground="lightyellow",    foreground="black",    font=font1,)style.configure("Treeview.Heading", background="PowderBlue")# Using treeview widgettrv = ttk.Treeview(my_w, selectmode="browse")trv.grid(row=2, column=0, columnspan=7, rowspan=2, padx=10, pady=2)# number of columnstrv["columns"] = ("1", "2", "3", "4", "5", "6")trv["show"] = "headings"trv.column("1", width=40, anchor="c")  # width & alignmenttrv.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 texttrv.heading("2", text="p_id")  # Heading texttrv.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, 0def 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 collecteddef 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.0def my_select(self):    b2.config(state="active")  # Delete button is active nowdef 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 totaldef 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 totaldef 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 functiontrv.bind("<<TreeviewSelect>>", my_select)  # User selection of rowproduct.trace("w", my_price)  # Call the function on changemy_w.mainloop()

Query to get products based on stock position

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 tk_invoice_products.py 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  \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 "

← Invoice Generation
Display MySQL records in Treeview » Pagination of MySQL records in Treeview »
← Displaying MySQL records using Entry or Label ← Delete MySQL record
TreeviewTreeview insert Treeview parent child nodeSelect -Edit-update MySQL Product table using Treeview » Query window & displaying records in Treeview »