Integrating Product table to Invoice Generation system
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_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 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;
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()
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 theme 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
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.
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 "