For those of you who have followed the record sales and save data into the database project, this will be the final chapter of this project. In the future, you can follow the project updates on the Github page.
In this chapter, I have included the combo box which allows the user to select the month he or she wishes to see the graph of sale data!
There is no need to create another combo box to let the user select the month before he submits the earning into the database. We just need to extract the data from the earning table based on the date which has been automatically inserted during the data submission project.
Below is the user interface part of the program where only a combo box has been added beside the plot buttons to let the user selects the month he wishes to view the graph of total earning.
import tkinter as tk
from tkinter import ttk
from Input import Input
win = tk.Tk()
win.title("Earn Great")
def submit(cc): # commit the data into earning table
if(cc=="Shoe"):
sub_mit.submit(shoe_type.get(), earning.get(), location.get(), cc)
elif(cc=='Shirt'):
sub_mit.submit(shirt_type.get(), earning.get(), location.get(), cc)
else:
print("You need to enter a value!")
#create label frame for the shoe ui
shoe_frame= ttk.Labelframe(win, text ="Shoe Sale")
shoe_frame.grid(column=0, row=0, padx=4, pady=4, sticky='w')
# create combo box for the shoe type
shoe_type = tk.StringVar()
shoe_combo = ttk.Combobox(shoe_frame, width=9, textvariable = shoe_type)
shoe_combo['values'] = ('Baby Girl', 'Baby Boy', 'Boy', 'Girl', 'Man', 'Woman')
shoe_combo.current(0)
shoe_combo.grid(column=0, row=0)
# create the submit button for shoe type
action_shoe = ttk.Button(shoe_frame, text="submit", command= lambda: submit("Shoe"))
action_shoe.grid(column=1, row=0)
#create label frame for the shirt ui
shirt_frame= ttk.Labelframe(win, text ="Shirt Sale")
shirt_frame.grid(column=0, row=1, padx=4, pady=4, sticky='w')
# create combo box for the shirt type
shirt_type = tk.StringVar()
shirt_combo = ttk.Combobox(shirt_frame, width=16, textvariable = shirt_type)
shirt_combo['values'] = ('T-Shirt', 'School Uniform', 'Baby Cloth', 'Jacket', 'Blouse', 'Pajamas')
shirt_combo.current(0)
shirt_combo.grid(column=0, row=0)
# create the submit button for shirt type
action_shirt = ttk.Button(shirt_frame, text="submit", command= lambda: submit("Shirt"))
action_shirt.grid(column=1, row=0)
#create label frame for the earning ui
earning_frame= ttk.Labelframe(win, text ="Earning")
earning_frame.grid(column=1, row=0, padx=4, pady=4, sticky='w')
# create combo box for the shoe earning
earning = tk.StringVar()
earn_combo = ttk.Combobox(earning_frame, width=9, textvariable = earning)
earn_combo['values'] = ('1.00', '2.00', '3.00', '4.00', '5.00', '6.00', '7.00', '8.00', '9.00', '10.00')
earn_combo.current(0)
earn_combo.grid(column=0, row=0)
#create label frame for the location ui
location_frame= ttk.Labelframe(win, text ="Location")
location_frame.grid(column=1, row=1, padx=4, pady=4, sticky='w')
# create combo box for the sale location
location = tk.StringVar()
location_combo = ttk.Combobox(location_frame, width=13, textvariable = location)
location_combo['values'] = ('Down Town', 'Market', 'Bus Station', 'Beach', 'Tea House')
location_combo.current(0)
location_combo.grid(column=0, row=0)
def plot(cc): # plotting the bar chart of total sales
sub_mit.plot(location.get(), cc, month.get())
#create label frame for the plot graph ui
plot_frame= ttk.Labelframe(win, text ="Plotting Graph Select Date")
plot_frame.grid(column=0, row=2, padx=4, pady=4, sticky='w')
# create the plot button for shoe type
action_pshoe = ttk.Button(plot_frame, text="Shoe", command= lambda: plot("Shoe"))
action_pshoe.grid(column=1, row=0)
# create the plot button for shirt type
action_pshirt = ttk.Button(plot_frame, text="Shirt", command= lambda: plot("Shirt"))
action_pshirt.grid(column=2, row=0)
# create the plot button for all items
action_p_loc = ttk.Button(plot_frame, text="All Items", command= lambda: plot("All Items"))
action_p_loc.grid(column=3, row=0)
# create combo box for the sale's month
month = tk.StringVar()
month_combo = ttk.Combobox(plot_frame, width=3, textvariable = month)
month_combo['values'] = ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
month_combo.current(0)
month_combo.grid(column=4, row=0)
win.resizable(0,0)
sub_mit = Input()
sub_mit.setting()
win.mainloop()
Here is the program to plot the graph and to save the data.
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
class Input:
def __init__(self):
pass
def setting(self):
conn = sqlite3.connect('daily_earning.db')
print("Opened database successfully")
try:
conn.execute('''CREATE TABLE DAILY_EARNING_CHART
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
DESCRIPTION TEXT (50) NOT NULL,
EARNING TEXT NOT NULL,
TYPE TEXT NOT NULL,
LOCATION TEXT NOT NULL,
TIME TEXT NOT NULL);''')
except:
pass
conn.close()
def submit(self,description, earning, location, cc): # Insert values into earning table
self.description = description
self.earning = earning
self.location = location
self.cc = cc
try:
sqliteConnection = sqlite3.connect('daily_earning.db')
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
sqlite_insert_query = "INSERT INTO DAILY_EARNING_CHART (DESCRIPTION,EARNING,TYPE, LOCATION, TIME) VALUES ('" + self.description + "','"+ self.earning + "','" + self.cc + "','" + self.location + "',datetime('now', 'localtime'))"
count = cursor.execute(sqlite_insert_query)
sqliteConnection.commit()
print("Record inserted successfully into DAILY_EARNING_CHART table", cursor.rowcount)
cursor.close()
except sqlite3.Error as error:
print("Failed to insert earning data into sqlite table", error)
finally:
if (sqliteConnection):
sqliteConnection.close()
def plot(self, location, cc, month): # plotting the bar chart
plt.clf() #this is uses to clear the previous graph plot
# dictionary uses to print out the month within header of the graph
monthdict = {'01':'January', '02':'Febuary', '03':'March', '04':'April', '05':'May', '06' : 'June', '07':'July', '08':'August', '09':'September', '10':'October', '11':'November', '12':'December'}
try:
shoe_dict = {'Baby Girl' : 0.00, 'Baby Boy' : 0.00, 'Boy':0.00, 'Girl':0.00, 'Man':0.00, 'Woman':0.00}
shirt_dict = {'T-Shirt':0.00, 'School Uniform':0.00, 'Baby Cloth':0.00, 'Jacket':0.00, 'Blouse':0.00, 'Pajamas':0.00}
sqliteConnection = sqlite3.connect('daily_earning.db')
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
if cc=='All Items':
cursor.execute("SELECT * FROM DAILY_EARNING_CHART WHERE LOCATION=?", (location,))
else:
cursor.execute("SELECT * FROM DAILY_EARNING_CHART WHERE TYPE=? AND LOCATION=?", (cc, location))
rows = cursor.fetchall()
for row in rows:
if(row[5].split('-')[1]) == month:
if cc=="Shoe":
shoe_dict[row[1]] += float(row[2])
elif cc=="Shirt":
shirt_dict[row[1]] += float(row[2])
elif cc=="All Items":
if row[1] in shoe_dict:
shoe_dict[row[1]] += float(row[2])
else:
shirt_dict[row[1]] += float(row[2])
# dictionary for the graph axis
label_x = []
label_y = []
if cc=="Shoe":
for key, value in shoe_dict.items():
label_x.append(key)
label_y.append(value)
elif cc=="Shirt":
for key, value in shirt_dict.items():
label_x.append(key)
label_y.append(value)
else:
for key, value in shirt_dict.items():
label_x.append(key)
label_y.append(value)
for key, value in shoe_dict.items():
label_x.append(key)
label_y.append(value)
# begin plotting the bar chart
s = pd.Series(index=label_x, data=label_y)
s.plot(color="green", kind="bar", title = cc + " Sales for " + monthdict[month] + " at " + location)
plt.show()
except sqlite3.Error as error:
print("Failed to plot earning data", error)
finally:
if (sqliteConnection):
sqliteConnection.close()
Take note that we need to clear the cache of the graph so those bar charts on the graph will not overlap after the program has plotted the bar chart for the first time and wishes to plot another graph.
Select a month before plotting the graph
The earning chart for the month of FebruaryThat is about it, in the future, any latest project update will only appear on the Github page as I have mentioned before.
from Planet Python
via read more
No comments:
Post a Comment