Hello and welcome back, in this chapter we will continue to develop the previous earning application which shows the shoe and shirt sales figure from the input database.

If you want to understand what is going on, then do read the previous post about this topic. In this chapter, I am going to improve the previous application by including a combo box that allows the user to select the type of graph or chart he or she wants to view.

This is the updated version of the user interface program.

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(), plot_type.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 Goods", 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)

# create combo box for the plot type
plot_type = tk.StringVar()
month_combo = ttk.Combobox(plot_frame, width=7, textvariable = plot_type)
month_combo['values']  = ('line', 'bar', 'barh', 'hist', 'box', 'area', 'pie')
month_combo.current(0)
month_combo.grid(column=5, row=0)

win.resizable(0,0)

sub_mit = Input()
sub_mit.setting()

win.mainloop()

This is the updated part for the input class.

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, plot_type): # 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)
            if(plot_type!="pie"):
                s.plot(label="Goods Sale vs Month", use_index=True, color="green", legend=True, kind=plot_type, title = cc + " Sales for " + monthdict[month] +  " at " + location)
            else:
                s.plot(label="Goods Sale vs Month", use_index=True, legend=True,  kind=plot_type, 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()

Now we can select any chart or graph we wish to see from the month of sales database.

Select a plot type from the user interface above
The Pie Chart of the goods sale

More will come, as I am going to keep on working on the above program by including more features into it. One of the features I am working on is the feature that allows me to create a column from the database for future goods.

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *