Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import sqlite3
- import string
- import pandas as pd
- import seaborn as sns
- from matplotlib import lines
- import matplotlib.pyplot as plt
- from email_validator import validate_email, EmailNotValidError
- SalesDataFull = pd.ExcelFile("SalesDataFull.xlsx")
- conn = sqlite3.connect('OS_employee.db')
- OrdersOnlyData = SalesDataFull.parse("Orders")
- def checkChoice():
- print("Welcome to Office Solutions")
- choice = input("Existing user?")
- while choice != 'no' and choice != 'yes' and choice != "x":
- choice = input("Error. Please enter yes/no or x to Exit: ")
- if choice == "yes":
- login()
- elif choice == "no":
- register()
- elif choice == "x":
- print("Thank you for visiting Office Solutions.")
- exit()
- def login():
- with conn:
- cur = conn.cursor()
- try:
- loginTest = False # main condition to loop if email and password not met
- while not loginTest: # wrong email loopy
- userEmail = input("Email please: ")
- userEmail = userEmail.lower().replace(" ", "")
- userPassword = input("Password: ").strip()
- cur.execute(
- "SELECT COUNT (*) FROM Employee WHERE(Email= '" + userEmail.lower() + "' AND Password= '" + userPassword + "')")
- results = cur.fetchone() # return very first thing it finds that matches
- print(results[0]) # print first thing
- if results[0] == 1:
- print("Login successful")
- loginTest = True
- else:
- print("Login Unsuccessful")
- existingUser = input("Existing user?[yes/no]")
- if existingUser == "no":
- register()
- except:
- print("connection failed")
- def emailCheck():
- Email = input("Email: ")
- while Email:
- try:
- v = validate_email(Email)
- break
- except EmailNotValidError:
- print("Error")
- Email = input("Email: ")
- #check email duplication
- with conn:
- cur = conn.cursor()
- try:
- cur.execute("SELECT COUNT (*) FROM Employee WHERE(Email = '" + Email + "')")
- results = cur.fetchone()
- while results[0] == 1:
- Email = input("Email existed. Please enter again: ")
- cur.execute("SELECT COUNT (*) FROM Employee WHERE(Email = '" + Email + "')")
- results = cur.fetchone()
- return Email
- except:
- print("Connected Failed")
- def EmployeeID_DuplicationCheck():
- with conn:
- cur = conn.cursor()
- try:
- EmployeeID = input("Enter New Employee ID: ")
- while EmployeeID == "":
- EmployeeID = input("What's your Employee ID:")
- # check EmployeeID duplication
- cur.execute("SELECT COUNT (*) FROM Employee WHERE(EmployeeID = '" + EmployeeID + "')")
- results = cur.fetchone()
- while results[0] == 1:
- EmployeeID = input("EmployeeID existed. Please enter again: ")
- cur.execute("SELECT COUNT (*) FROM Employee WHERE(EmployeeID = '" + EmployeeID + "')")
- results = cur.fetchone()
- return EmployeeID
- except sqlite3.Error as e:
- print(e)
- def register():
- with conn:
- cur = conn.cursor()
- try:
- EmployeeID = EmployeeID_DuplicationCheck()
- FirstName = input("What's your first name:")
- while FirstName == "":
- FirstName = input("What's your first name:")
- LastName = input("What's your last name: ")
- while LastName == "":
- LastName = input("What's your last name:")
- email = emailCheck()
- password = input("Enter a password: ")
- while password == "":
- password = input("Enter a password: ")
- cur.execute("""
- INSERT INTO Employee (EmployeeID, FirstName, LastName,Email, Password) VALUES (?, ?, ?, ?, ?)""",
- (EmployeeID, FirstName, LastName, email, password))
- cur.execute('select * from employee where EmployeeId=?', (EmployeeID,))
- results = cur.fetchall()
- print(results)
- # After registering, user is sent back to the main page.
- print("You have been successfully registered!\n")
- except:
- print("Connected Failed")
- def totalProfit_month():
- #total profit by month
- ProfitDataMonth = OrdersOnlyData
- ProfitDataMonth["Profit"] = ProfitDataMonth["Profit"].sum()
- ProfitDataMonth["Month"] = ProfitDataMonth["Order Date"].dt.month
- MonthlyProfit = ProfitDataMonth[["Month", "Profit"]]
- MonthlyProfitSum = MonthlyProfit.groupby(by="Month").sum()
- MonthlyProfitSum['Profit'] = MonthlyProfitSum['Profit'].map("{:.2f}".format)
- MonthlyProfitSum['Profit'] = pd.np.where(MonthlyProfitSum['Profit'].astype(float)
- < 0, '-$' + MonthlyProfitSum['Profit'].astype(str).str[1:],
- '$' + MonthlyProfitSum['Profit'].astype(str))
- print(MonthlyProfitSum)
- MonthlyProfitSum = MonthlyProfitSum.reset_index()
- #barchart
- barchart_mostProfitable_month = sns.barplot(x="Month", y="Profit", data=MonthlyProfit[["Month", "Profit"]])
- barchart_mostProfitable_month.set_title("Sales by Profit")
- #piechart
- # labels = ProfitDataMonth
- # sizes = [ProfitDataMonth[["Month", "Profit"]]]
- #
- # fig1, ax1 = plt.subplots()
- # ax1.pie(sizes, labels=labels,
- # shadow=True, startangle=90)
- # ax1.axis('equal')
- plt.show()
- def Overallprofit_region():
- OrdersOnlyData = SalesDataFull.parse("Orders")
- profit_region = OrdersOnlyData[["Region", "Profit"]]
- overall_prof = profit_region.groupby(by="Region").sum().sort_values(by="Profit", ascending=False)
- print("\nThese are the total profits for each region: ")
- print(overall_prof.head(4))
- prof_cols_sub = OrdersOnlyData[["Region", "Profit"]]
- prof_cols_sub.groupby("Region").sum().plot(kind="bar")
- plt.show()
- #Data display part
- #montly most profitable product
- def mostProfitable_month():
- OrdersOnlyData = SalesDataFull.parse("Orders")
- df_month = OrdersOnlyData["Order Date"].dt.month
- OrdersOnlyData["Month"] = df_month
- month = int(input("Enter month (1-12): "))
- while not (1 <= month <= 12):
- print("Invalid Month")
- month = int(input("Enter month (1-12): "))
- month_profit_headers = OrdersOnlyData[['Month', 'Product Name', 'Profit']]
- month_profit_sum = month_profit_headers.groupby(['Product Name']).sum().sort_values(by='Profit', ascending=True).reset_index()
- print("The 10 least profitable products ")
- month_profit_sum['Profit'] = month_profit_sum['Profit'].map("{:.2f}".format)
- month_profit_sum['Profit'] = pd.np.where(month_profit_sum['Profit'].astype(float)
- < 0, '-$' + month_profit_sum['Profit'].astype(str).str[1:],
- '$' + month_profit_sum['Profit'].astype(str))
- print("10 least profitable product by month")
- print(month_profit_sum.tail(10).round(2))
- print(lines)
- # OrdersOnlyData = ProfitableData
- # OrdersOnlyData["Month"] = OrdersOnlyData["Order Date"].dt.month
- # month_profit_headers = OrdersOnlyData[["Product Name", "Profit"]]
- # month_profit_sum = month_profit_headers.groupby(by="Profit").sum().sort_values(by="Profit", ascending=False).head(10).round(2)
- # month_profit_sum = month_profit_sum.reset_index()
- # print("10 most profitable product by month")
- # print(month_profit_sum)
- # print("--------------")
- #10 least profitable product
- def leastProfitable_month():
- OrdersOnlyData = SalesDataFull.parse("Orders")
- df_month = OrdersOnlyData["Order Date"].dt.month
- OrdersOnlyData["Month"] = df_month
- month = int(input("Enter month (1-12): "))
- while not (1 <= month <= 12):
- print("Invalid Month")
- month = int(input("Enter month (1-12): "))
- month_profit_headers = OrdersOnlyData[['Month', 'Product Name', 'Profit']]
- month_profit_sum = month_profit_headers.groupby(['Product Name']).sum().sort_values(by='Profit', ascending=False).reset_index()
- print("The 10 least profitable products ")
- month_profit_sum['Profit'] = month_profit_sum['Profit'].map("{:.2f}".format)
- month_profit_sum['Profit'] = pd.np.where(month_profit_sum['Profit'].astype(float)
- < 0, '-$' + month_profit_sum['Profit'].astype(str).str[1:], '$' + month_profit_sum['Profit'].astype(str))
- print("10 least profitable product by month")
- print(month_profit_sum.tail(10).round(2))
- print(lines)
- #def state():
- def stateTopProfit():
- states = OrdersOnlyData.State.unique()
- for pos_state in states:
- state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == pos_state]
- state_prof = state_select[["State", "Product Name", "Profit"]]
- best_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit", ascending=True).reset_index()
- print("\nBest Product(s) in {} ".format(pos_state))
- print("--------------------------------------------")
- print(best_state.head(5).round(2))
- def stateLeastProfit():
- states = OrdersOnlyData.State.unique()
- for neg_state in states:
- state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == neg_state]
- state_prof = state_select[["State", "Product Name", "Profit"]]
- worse_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit").reset_index()
- print("\nWorst Product(s) in {} ".format(neg_state))
- print("--------------------------------------------")
- print(worse_state.tail(5).round(2))
- #def quarter():
- # def mostProfitable_quarter():
- # df_quarter = OrdersOnlyData["Order Date"].dt.quarter
- # OrdersOnlyData["Quarter"] = df_quarter
- # month_profit_headers = OrdersOnlyData[['Quarter', 'Product Name', 'Profit']]
- # month_profit_sum = month_profit_headers.groupby(by=['Quarter']).sum().sort_values(by='Profit', ascending=False).reset_index()
- # quarter = int(input("Enter quarter (1, 2, 3, 4): "))
- #
- # while not (1 <= quarter <= 4):
- # print("Invalid entry, please try again")
- # quarter = int(input("Enter quarter: (1, 2, 3, 4"))
- # print([month_profit_sum['Quarter'] == quarter])
- #
- # if quarter == 1:
- # print("The 10 most profitable products ")
- # quarter1Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2)
- # print(quarter1Profit_most)
- #
- # elif quarter == 2:
- # print("The 10 most profitable products ")
- # quarter2Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2)
- # print(quarter2Profit_most)
- #
- # elif quarter == 3:
- # print("The 10 most profitable products ")
- # quarter3Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2)
- # print(quarter3Profit_most)
- #
- # elif quarter == 4:
- # print("The 10 most profitable products ")
- # quarter4Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2)
- # print(quarter4Profit_most)
- #
- #
- # def leastProfitable_quarter():
- # df_quarter = OrdersOnlyData["Order Date"].dt.quarter
- # OrdersOnlyData["Quarter"] = df_quarter
- # month_profit_headers = OrdersOnlyData[['Quarter', 'Product Name', 'Profit']]
- # month_profit_sum = month_profit_headers.groupby(by=['Quarter']).sum().sort_values(by='Profit', ascending=False).reset_index()
- #
- # quarter = int(input("Enter quarter (1, 2, 3, 4): "))
- #
- # while not (1 <= quarter <= 4):
- # print("Invalid entry, please try again")
- # quarter = int(input("Enter quarter: (1, 2, 3, 4"))
- # print([month_profit_sum['Quarter'] == quarter])
- #
- # if quarter == 1:
- # print("The 10 least profitable products ")
- # quarter1Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2)
- # print(quarter1Profit_most)
- #
- # elif quarter == 2:
- # print("The 10 least profitable products ")
- # quarter2Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2)
- # print(quarter2Profit_most)
- #
- # elif quarter == 3:
- # print("The 10 least profitable products ")
- # quarter3Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2)
- # print(quarter3Profit_most)
- #
- # elif quarter == 4:
- # print("The 10 least profitable products ")
- # quarter4Profit_most = df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2)
- # print(quarter4Profit_most)
- #
- #
- #def city():
- totalProfit_month()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement