· 7 years ago · Dec 11, 2018, 09:20 PM
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 dns.resolver
21import scipy.stats
22import seaborn as sns
23import matplotlib.pyplot as plt
24
25
26xl = pd.ExcelFile(r"SalesDataFull.xlsx")
27OrdersOnlyData = xl.parse("Orders")
28CopyOrdersData = OrdersOnlyData.copy()
29lines = "="*60
30connection = sqlite3.connect('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 sort = True
131 word = ""
132 if num == 1:
133 sort = False
134 word = "Most"
135 else:
136 sort = True
137 word = "Least"
138
139 prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
140 prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
141 quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
142 print("Year: " + str(year) + ".")
143
144 for quarter in quarters:
145
146 quarters_list = ["1st Quarter", "2nd Quarter", "3rd Quarter", "4th Quarter"]
147 prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
148 best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
149 print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
150 print(best_select.head(10))
151 #after year choose whether they want a specific month or every month, or by quarter = 3/12 Jan,Feb,Mar
152
153
154'''def product_by_quarter(year, num):
155 lines = "="*25
156 sort = True
157 word = ""
158 if num == 1:
159 sort = False
160 word = "Most"
161 else:
162 sort = True
163 word = "Least"
164
165 prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
166 prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
167 quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
168 print("\n" + lines + lines + "\n")
169 print("Year: " + str(year) + ".")
170
171 for quarter in quarters:
172
173 quarters_list = ["Quarter I", "Quarter II", "Quarter III", "Quarter IV"]
174 prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
175 best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
176 print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
177 print("\n" + lines + lines + "\n")
178 print(best_select.head(10))
179 '''
180def main():
181 finished = False
182 while not finished:
183 login()
184 choice = get_login()
185 if choice == 1:
186 with sqlite3.connect('OS_Employee.db'):
187 email = input('Please enter your email address: ')
188 password = getpass.getpass('Please enter your password: ')
189 try:
190 c.execute("SELECT COUNT (*) FROM Employee WHERE (Email = '" + email +"' AND Password = '" + password + "')")
191 connection.commit()
192 result= c.fetchone()
193 if result[0]==1:
194 print('')
195 print('Processing...')
196 time.sleep(1.5)
197 print("...")
198 print("Login successful.")
199 time.sleep(1)
200 print("Welcome to Office Solutions!")
201 connection.close()
202 finished = False
203 while not finished:
204 main_menu()
205 choice = get_main_menu()
206 if choice == 1:
207 num = 1
208 while True:
209 print("\nYears available: 2014, 2015, 2016, 2017")
210 choice = input("Please choose a year or press (0) to go back to previous menu: ")
211 if choice == "2014":
212 print("...")
213 time.sleep(1)
214 print("Processing...")
215 time.sleep(1)
216 validate_data(2014, num)
217 validate_quarter(2014, num)
218 elif choice == "2015":
219 print("...")
220 time.sleep(1)
221 print("Processing...")
222 time.sleep(1)
223 validate_data(2015, num)
224 validate_quarter(2015, num)
225 elif choice == "2016":
226 print("...")
227 time.sleep(1)
228 print("Processing...")
229 time.sleep(1)
230 validate_data(2016, num)
231 validate_quarter(2016, num)
232 elif choice == "2017":
233 print("...")
234 time.sleep(1)
235 print("Processing...")
236 time.sleep(1)
237 validate_data(2017, num)
238 validate_quarter(2017, num)
239 elif choice == "0":
240 break
241 else:
242 print("Year not found")
243 elif choice == 2:
244 num = 2
245 while True:
246 print("\nYears available: 2014, 2015, 2016, 2017")
247 choice = input("Please choose a year or press (0) to go back to previous menu: ")
248 print("------")
249 if choice == "2014":
250 print("...")
251 time.sleep(1)
252 print("Processing...")
253 time.sleep(1)
254 validate_data(2014, num)
255 validate_quarter(2014, num)
256 elif choice == "2015":
257 print("...")
258 time.sleep(1)
259 print("Processing...")
260 time.sleep(1)
261 validate_data(2015, num)
262 validate_quarter(2015, num)
263 elif choice == "2016":
264 print("...")
265 time.sleep(1)
266 print("Processing...")
267 time.sleep(1)
268 validate_data(2016, num)
269 validate_quarter(2016, num)
270 elif choice == "2017":
271 print("...")
272 time.sleep(1)
273 print("Processing...")
274 time.sleep(1)
275 validate_data(2017, num)
276 validate_quarter(2017, num)
277 elif choice == "0":
278 break
279 else:
280 print("Year not found")
281 elif choice ==4:
282 state_prof_cols=OrdersOnlyData[["State", "Profit"]]
283 state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending=False)
284 print("\nTop 10 states with the highest profit.")
285 print(state_purchases.head(10))
286 print(lines)
287
288 reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
289 region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = False)
290 print("\nRegion with the highest profit.")
291 print(region_profits.head(4))
292 print(lines)
293 elif choice ==5:
294
295 state_prof_cols=OrdersOnlyData[["State", "Profit"]]
296 state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending = True)
297 print("\nTop 10 states with most Revenue Loss")
298 print(state_purchases.head(10))
299 reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
300 region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = True)
301 print("\nRegion with the lowest profit.")
302 print(region_profits.tail(4))
303 print(lines)
304 elif choice == 6:
305 Reg = "South"
306 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
307 ActiveDataFrame = RegionalSales
308 x = "Discount"
309 y = "Quantity"
310 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
311 correlation = str(getCorr[0])
312 pValue = str(getCorr[1])
313 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
314 print("With p value of: " + pValue)
315 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
316 plt.xlabel(x)
317 plt.ylabel(y)
318 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
319 plt.show()
320
321 elif choice == 7:
322 Reg = "Central"
323 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
324 ActiveDataFrame = RegionalSales
325 x = "Discount"
326 y = "Quantity"
327 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
328 correlation = str(getCorr[0])
329 pValue = str(getCorr[1])
330 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
331 print("With p value of: " + pValue)
332 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
333 plt.xlabel(x)
334 plt.ylabel(y)
335 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
336 plt.show()
337
338 elif choice == 8:
339 Reg = "East"
340 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
341 ActiveDataFrame = RegionalSales
342 x = "Discount"
343 y = "Quantity"
344 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
345 correlation = str(getCorr[0])
346 pValue = str(getCorr[1])
347 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
348 print("With p value of: " + pValue)
349 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
350 plt.xlabel(x)
351 plt.ylabel(y)
352 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
353 plt.show()
354
355 elif choice == 9:
356 Reg = "West"
357 RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
358 ActiveDataFrame = RegionalSales
359 x = "Discount"
360 y = "Quantity"
361 getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
362 correlation = str(getCorr[0])
363 pValue = str(getCorr[1])
364 print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
365 print("With p value of: " + pValue)
366 sns.lmplot(x=x, y=y, data=ActiveDataFrame)
367 plt.xlabel(x)
368 plt.ylabel(y)
369 plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
370 plt.show()
371 '''num == 6
372 while True:
373 print("\nChoose Region: 1. Central, 2. South, 3. West, 4. East: ")
374 choice = input("Please choose a region or press (0) to go back to previous menu: ")
375 print("------")
376 if choice == "1":
377 print("...")
378 time.sleep(1)
379 print("Processing...")
380 time.sleep(1)
381 plot(1, num)
382 elif choice == "2":
383 print("...")
384 time.sleep(1)
385 print("Processing...")
386 time.sleep(1)
387 plot(2, num)
388 elif choice == "3":
389 print("...")
390 time.sleep(1)
391 print("Processing...")
392 time.sleep(1)
393 plot(3, num)
394 elif choice == "4":
395 print("...")
396 time.sleep(1)
397 print("Processing...")
398 time.sleep(1)
399 plot(4, num)
400 '''
401 elif choice == 0:
402 print('')
403 time.sleep(1)
404 print("Thank you!")
405 finished = True
406 else:
407 finished = True
408 else:
409 print()
410 print('Processing...')
411 time.sleep(1.5)
412 print()
413 print("Login failed")
414 except:
415 print("Connection failed")
416 elif choice == 2:
417 with sqlite3.connect('OS_Employee.db') as db:
418 employee_ID =input("Please enter your Employee ID: ")
419 while employee_ID.isnumeric() == False:
420 employee_ID = input("Sorry, try again\nPlease enter your Employee ID:")
421
422 first_name = input("Please enter your first name: ")
423 while first_name.isnumeric() == True:
424 first_name = input("Sorry, try again\nPlease enter your first name: ")
425
426 last_name = input("Please enter your last name: ")
427 while last_name.isnumeric() == True:
428 last_name = input("Sorry, try again\nPlease enter your last name: ")
429
430 domainlist= ["@gmail.com", "@yahoo.com","@outlook.com", "@hotmail.com", "@ymail.com"]
431 domaintuple= tuple(domainlist)
432 while True:
433 email = input('Please enter your working email: ')
434 email= email.strip()
435 if email.endswith(domaintuple) ==True:
436 c.execute("SELECT COUNT (*) FROM Employee WHERE (Email ='" +email+ "')")
437 emailresult= c.fetchone()
438 if emailresult[0]!=1:
439 print("Email is available")
440 break
441 else:
442 print("Email is taken. Please enter another email")
443 email=''
444 continue
445 else:
446 print("Invalid email. Please enter another email")
447 email=''
448 continue
449
450 password = input("Create a password for your log-in: ")
451 while len(password) > 24 or len(password)<3:
452 password= input("Sorry, try again\nPlease enter a password for your log-in: ")
453
454 user_account =(employee_ID, first_name.title(), last_name.title(), email, password)
455 try:
456 c.execute('INSERT INTO Employee (EmployeeID,FirstName,LastName,Email,Password) VALUES (?,?,?,?,?)', user_account)
457 db.commit()
458 print("Processing...")
459 time.sleep(1.5)
460 print("Registration successful! Please log in.")
461 except:
462 print("Connection failed.")
463 elif choice == 0:
464 print('')
465 time.sleep(1)
466 print("Thank you!")
467 finished = True
468 else:
469 finished = True
470
471if __name__ == "__main__":
472 DATABASE = 'OS_Employee.db'
473
474main()