· 7 years ago · Apr 24, 2019, 01:40 AM
1# -*- coding: utf-8 -*-
2"""
3Created on Wed Dec 5 19:25:08 2018
4
5@author: shrim
6"""
7
8# -*- coding: utf-8 -*-
9"""
10Created on Wed Nov 7 12:34:11 2018
11
12@author: alvin
13"""
14
15
16import sqlite3
17import getpass
18import time
19import pandas as pd
20import scipy.stats
21import seaborn as sns
22import matplotlib.pyplot as plt
23import numpy as np
24
25
26xl = pd.ExcelFile(r"C:\users\shrim\Downloads\SalesDataFull.xlsx")
27OrdersOnlyData = xl.parse("Orders")
28CopyOrdersData = OrdersOnlyData.copy()
29lines = "="*60
30connection = sqlite3.connect(r'C:\users\shrim\Downloads\OS_Employee.db',timeout=5)
31c= connection.cursor()
32connection2= sqlite3.connect('Orders.db',timeout=5)
33c2= connection2.cursor()
34
35
36def login():
37 print("Login")
38 print()
39 print("Enter 1 to Login")
40 print("Enter 2 to Create an Account")
41 print("Enter 3 to Exit")
42
43def get_login():
44 accepted = False
45 while not accepted:
46 choice = int(input("Enter 1, 2, or 3: "))
47 if 1 <= choice <= 5:
48 accepted = True
49 else:
50 print()
51 print("Please enter a valid value:")
52 return choice
53
54def main_menu():
55 time.sleep(1)
56 print("")
57 print("Main Menu")
58 print()
59 print("1. Top 10 Most Profitable products by Year + Quarters")
60 print("2. Top 10 Least Profitable by Year + Quarters")
61 print("3. Log out")
62 print("4. Top 10 Most profitable states and region")
63 print("5. Top 10 Least Profitable States and region")
64 print("6. See Plot for Associate between Discounts and Quantities Sold in South")
65 print("7. See Plot for Associate between Discounts and Quantities Sold in Central")
66 print("8. See Plot for Associate between Discounts and Quantities Sold in East")
67 print("9. See Plot for Associate between Discounts and Quantities Sold in West")
68
69def get_main_menu():
70 accepted = False
71 while not accepted:
72 choice = int(input("Please select an option: "))
73 if 0 <= choice <= 10:
74 accepted = True
75 else:
76 print()
77 print("Please enter a valid value: (1)")
78 return choice
79
80'''def plot(region, num):
81 word = ""
82 if num == 6:
83 word = "Central"
84 elif num == 2:
85 word = "South"
86 elif num == 3:
87 word = "West"
88 elif num == 4:
89 word = "East"
90 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == word, :]
91 ActiveDataFrame = RegionalSales
92 x = "Discount"
93 y = "Quantity"
94 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
95 correlation = str(getCorr[0])
96 pValue = str(getCorr[1])
97 print("The correlation between " + x + " and " + y + " in the " + word + " Region is: " + correlation)
98 print("With p value of: " + pValue)
99 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
100 plt.xlabel(x)
101 plt.ylabel(y)
102 plt.title("Correlation of " + x + " and " + y + " in region: " + word)
103 plt.show()
104'''
105def validate_data(year, num):
106
107 sort = True
108 word = ""
109 if num == 1:
110 sort = False
111 word = "Most"
112 else:
113 sort = True
114 word = "Least"
115
116 prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
117 prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
118 months = (pd.DatetimeIndex(prod_select["Order Date"]).month.unique()).sort_values()
119 print("Year: " + str(year) + ".")
120
121 for month in months:
122
123 months_list = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]
124 prod_select_month = prod_select.loc[prod_select["Order Date"].dt.month == month]
125 best_select = prod_select_month.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
126 print("\n" + word + " profitable products in " + months_list[month-1] + "\n")
127 print("$" + (best_select.head(10))
128
129def validate_quarter(year, num):
130
131 sort = True
132 word = ""
133 if num == 1:
134 sort = False
135 word = "Most"
136 else:
137 sort = True
138 word = "Least"
139
140 prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
141 prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
142 quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
143 print("Year: " + str(year) + ".")
144
145 for quarter in quarters:
146
147 quarters_list = ["1st Quarter", "2nd Quarter", "3rd Quarter", "4th Quarter"]
148 prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
149 best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
150 print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
151 print(best_select.head(10))
152 #after year choose whether they want a specific month or every month, or by quarter = 3/12 Jan,Feb,Mar
153
154
155'''def product_by_quarter(year, num):
156 lines = "="*25
157 sort = True
158 word = ""
159 if num == 1:
160 sort = False
161 word = "Most"
162 else:
163 sort = True
164 word = "Least"
165
166 prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
167 prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
168 quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
169 print("\n" + lines + lines + "\n")
170 print("Year: " + str(year) + ".")
171
172 for quarter in quarters:
173
174 quarters_list = ["Quarter I", "Quarter II", "Quarter III", "Quarter IV"]
175 prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
176 best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
177 print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
178 print("\n" + lines + lines + "\n")
179 print(best_select.head(10))
180 '''
181def main():
182 finished = False
183 while not finished:
184 login()
185 choice = get_login()
186 if choice == 1:
187 with sqlite3.connect(r'C:\users\shrim\Downloads\OS_Employee.db'):
188 email = input('Please enter your email address: ')
189 password = getpass.getpass('Please enter your password: ')
190 try:
191 c.execute("SELECT COUNT (*) FROM Employee WHERE (Email = '" + email +"' AND Password = '" + password + "')")
192 connection.commit()
193 result= c.fetchone()
194 if result[0]==1:
195 print('')
196 print('Processing...')
197 time.sleep(1.5)
198 print("...")
199 print("Login successful.")
200 time.sleep(1)
201 print("Welcome to Office Solutions!")
202 connection.close()
203 finished = False
204 while not finished:
205 main_menu()
206 choice = get_main_menu()
207 if choice == 1:
208 num = 1
209 while True:
210 print("\nYears available: 2014, 2015, 2016, 2017")
211 choice = input("Please choose a year or press (0) to go back to previous menu: ")
212 if choice == "2014":
213 print("...")
214 time.sleep(1)
215 print("Processing...")
216 time.sleep(1)
217 validate_data(2014, num)
218 validate_quarter(2014, num)
219 elif choice == "2015":
220 print("...")
221 time.sleep(1)
222 print("Processing...")
223 time.sleep(1)
224 validate_data(2015, num)
225 validate_quarter(2015, num)
226 elif choice == "2016":
227 print("...")
228 time.sleep(1)
229 print("Processing...")
230 time.sleep(1)
231 validate_data(2016, num)
232 validate_quarter(2016, num)
233 elif choice == "2017":
234 print("...")
235 time.sleep(1)
236 print("Processing...")
237 time.sleep(1)
238 validate_data(2017, num)
239 validate_quarter(2017, num)
240 elif choice == "0":
241 break
242 else:
243 print("Year not found")
244 elif choice == 2:
245 num = 2
246 while True:
247 print("\nYears available: 2014, 2015, 2016, 2017")
248 choice = input("Please choose a year or press (0) to go back to previous menu: ")
249 print("------")
250 if choice == "2014":
251 print("...")
252 time.sleep(1)
253 print("Processing...")
254 time.sleep(1)
255 validate_data(2014, num)
256 validate_quarter(2014, num)
257 elif choice == "2015":
258 print("...")
259 time.sleep(1)
260 print("Processing...")
261 time.sleep(1)
262 validate_data(2015, num)
263 validate_quarter(2015, num)
264 elif choice == "2016":
265 print("...")
266 time.sleep(1)
267 print("Processing...")
268 time.sleep(1)
269 validate_data(2016, num)
270 validate_quarter(2016, num)
271 elif choice == "2017":
272 print("...")
273 time.sleep(1)
274 print("Processing...")
275 time.sleep(1)
276 validate_data(2017, num)
277 validate_quarter(2017, num)
278 elif choice == "0":
279 break
280 else:
281 print("Year not found")
282 elif choice ==4:
283 state_prof_cols=OrdersOnlyData[["State", "Profit"]]
284 state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending=False)
285 print("\nTop 10 states with the highest profit.")
286 print(state_purchases.head(10))
287 print(lines)
288
289 reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
290 region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = False)
291 print("\nRegion with the highest profit.")
292 print(region_profits.head(4))
293 print(lines)
294 elif choice ==5:
295
296 state_prof_cols=OrdersOnlyData[["State", "Profit"]]
297 state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending = True)
298 print("\nTop 10 states with most Revenue Loss")
299 print(state_purchases.head(10))
300 reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
301 region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = True)
302 print("\nRegion with the lowest profit.")
303 print(region_profits.tail(4))
304 print(lines)
305 elif choice == 6:
306 Reg = "South"
307 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
308 ActiveDataFrame = RegionalSales
309 x = "Discount"
310 y = "Quantity"
311 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
312 correlation = str(getCorr[0])
313 pValue = str(getCorr[1])
314 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
315 print("With p value of: " + pValue)
316 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
317 plt.xlabel(x)
318 plt.ylabel(y)
319 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
320 plt.show()
321
322 elif choice == 7:
323 Reg = "Central"
324 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
325 ActiveDataFrame = RegionalSales
326 x = "Discount"
327 y = "Quantity"
328 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
329 correlation = str(getCorr[0])
330 pValue = str(getCorr[1])
331 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
332 print("With p value of: " + pValue)
333 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
334 plt.xlabel(x)
335 plt.ylabel(y)
336 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
337 plt.show()
338
339 elif choice == 8:
340 Reg = "East"
341 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
342 ActiveDataFrame = RegionalSales
343 x = "Discount"
344 y = "Quantity"
345 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
346 correlation = str(getCorr[0])
347 pValue = str(getCorr[1])
348 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
349 print("With p value of: " + pValue)
350 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
351 plt.xlabel(x)
352 plt.ylabel(y)
353 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
354 plt.show()
355
356 elif choice == 9:
357 Reg = "West"
358 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
359 ActiveDataFrame = RegionalSales
360 x = "Discount"
361 y = "Quantity"
362 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
363 correlation = str(getCorr[0])
364 pValue = str(getCorr[1])
365 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
366 print("With p value of: " + pValue)
367 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
368 plt.xlabel(x)
369 plt.ylabel(y)
370 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
371 plt.show()
372 '''num == 6
373 while True:
374 print("\nChoose Region: 1. Central, 2. South, 3. West, 4. East: ")
375 choice = input("Please choose a region or press (0) to go back to previous menu: ")
376 print("------")
377 if choice == "1":
378 print("...")
379 time.sleep(1)
380 print("Processing...")
381 time.sleep(1)
382 plot(1, num)
383 elif choice == "2":
384 print("...")
385 time.sleep(1)
386 print("Processing...")
387 time.sleep(1)
388 plot(2, num)
389 elif choice == "3":
390 print("...")
391 time.sleep(1)
392 print("Processing...")
393 time.sleep(1)
394 plot(3, num)
395 elif choice == "4":
396 print("...")
397 time.sleep(1)
398 print("Processing...")
399 time.sleep(1)
400 plot(4, num)
401 '''
402 elif choice == 0:
403 print('')
404 time.sleep(1)
405 print("Thank you!")
406 finished = True
407 else:
408 finished = True
409 else:
410 print()
411 print('Processing...')
412 time.sleep(1.5)
413 print()
414 print("Login failed")
415 except:
416 print("Connection failed")
417 elif choice == 2:
418 with sqlite3.connect(r'C:\users\shrim\Downloads\OS_Employee.db') as db:
419 employee_ID =input("Please enter your Employee ID: ")
420 while employee_ID.isnumeric() == False:
421 employee_ID = input("Sorry, try again\nPlease enter your Employee ID:")
422
423 first_name = input("Please enter your first name: ")
424 while first_name.isnumeric() == True:
425 first_name = input("Sorry, try again\nPlease enter your first name: ")
426
427 last_name = input("Please enter your last name: ")
428 while last_name.isnumeric() == True:
429 last_name = input("Sorry, try again\nPlease enter your last name: ")
430
431 domainlist= ["@gmail.com", "@yahoo.com","@outlook.com", "@hotmail.com", "@ymail.com"]
432 domaintuple= tuple(domainlist)
433 while True:
434 email = input('Please enter your working email: ')
435 email= email.strip()
436 if email.endswith(domaintuple) ==True:
437 c.execute("SELECT COUNT (*) FROM Employee WHERE (Email ='" +email+ "')")
438 emailresult= c.fetchone()
439 if emailresult[0]!=1:
440 print("Email is available")
441 break
442 else:
443 print("Email is taken. Please enter another email")
444 email=''
445 continue
446 else:
447 print("Invalid email. Please enter another email")
448 email=''
449 continue
450
451 password = input("Create a password for your log-in: ")
452 while len(password) > 24 or len(password)<3:
453 password= input("Sorry, try again\nPlease enter a password for your log-in: ")
454
455 user_account =(employee_ID, first_name.title(), last_name.title(), email, password)
456 try:
457 c.execute('INSERT INTO Employee (EmployeeID,FirstName,LastName,Email,Password) VALUES (?,?,?,?,?)', user_account)
458 db.commit()
459 print("Processing...")
460 time.sleep(1.5)
461 print("Registration successful! Please log in.")
462 except:
463 print("Connection failed.")
464 elif choice == 0:
465 print('')
466 time.sleep(1)
467 print("Thank you!")
468 finished = True
469 else:
470 finished = True
471
472if __name__ == "__main__":
473 DATABASE = r'C:\users\shrim\Downloads\OS_Employee.db'
474
475main()