API tools faq
paste
Advertisement
Guest User

dfhdaslfda

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