import mysql.connector
try:
    cnx = mysql.connector.connect(host='localhost',database='checklist',user='root',password='')
    cursor = cnx.cursor()
except:
     print("There is no connection with server")
     raise SystemExit()

print("          Analysis of checks")
print("          MENU")
print("1: Top 10 products by turnover, number of sales, the number of checks")
print("2: Number of checks in the shop")
print("3: Number of checks certain product")
print("4: The average check of certain products")
print("5: The turnover, number of checks, sales in shops")
print("6: Number of checks on certain product by day the of week")
print("7: Number of checks on certain product by dayS the of week")
print("0: Exit")

while (True):

  n = int(input("Input number of query\n"))
  if (n==1):
      query = ("SELECT product__name, total_price * COUNT( product_id)FROM products GROUP BY product_id ORDER BY (total_price * COUNT( product_id )) DESC LIMIT 0 , 10")
      cursor.execute(query)
      result = cursor.fetchall()
      print("Top 10 products by turnover")
      for i in result:
          print(i)
      query =("SELECT product__name, qty*COUNT( product__name) FROM products GROUP BY product_id ORDER BY (qty*COUNT( product__name )) DESC LIMIT 0 , 10 ")
      cursor.execute(query)
      result = cursor.fetchall()
      print("Top 10 products by number of sales")
      for i in result:
          print(i)
      query = ("SELECT product__name,COUNT( product__name ) FROM products GROUP BY product_id ORDER BY COUNT(product__name) DESC LIMIT 0,10 ")
      cursor.execute(query)
      result = cursor.fetchall()
      print("Top 10 products by number of checks")
      for i in result:
          print(i)

  elif n==2:
      shopname = input("Enter Shop Name, for example Shop №01\n")
      query = ("SELECT shop__name, COUNT( shop_id ) FROM checkinfo WHERE (shop__name='%s') GROUP BY shop_id ORDER BY (COUNT( shop_id )) DESC")%shopname
      print("Number of checks in the shop")
      cursor.execute(query)
      result = cursor.fetchall()
      print(result)

  elif n==3:
      productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
      query = ("SELECT product__name, COUNT( product__name )FROM products WHERE (product__name ='%s')ORDER BY COUNT( product_id ) DESC ")%productname
      print("Number of checks certain product")
      cursor.execute(query)
      result = cursor.fetchall()
      print(result)

  elif n==4:
      productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
      query = ("SELECT product__name, SUM( checkinfo.total_price )/COUNT( products.product__name ) FROM products, checkinfo WHERE (product__name ='%s' AND checkinfo.id = products.id)")%productname
      print("The average check of certain products")
      cursor.execute(query)
      result = cursor.fetchall()
      print(result)
  elif n==5:
      query = ("SELECT shop__name, SUM( total_price ) FROM checkinfo GROUP BY shop__name ")
      cursor.execute(query)
      result = cursor.fetchall()
      print("The turnover of the shops")
      for i in result:
          print(i)
      query =("SELECT shop__name, COUNT( shop_id ) FROM checkinfo GROUP BY shop_id ORDER BY ( COUNT( shop_id ) ) DESC ")
      cursor.execute(query)
      result = cursor.fetchall()
      print("Number of checks in the shops")
      for i in result:
          print(i)
      query = ("SELECT shop__name, SUM( items_qty ) FROM checkinfo GROUP BY shop_id ORDER BY ( COUNT( items_qty ) ) DESC  ")
      cursor.execute(query)
      result = cursor.fetchall()
      print("Sales in the shops")
      for i in result:
          print(i)
  elif n==6:
      day = input("Enter day of the week: 0:Monday .. 6:Sunday\n")
      productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
      query = ("SELECT product__name, COUNT(product__name)FROM products,checkinfo WHERE (product__name='%s' AND checkinfo.week_day= '%s' AND products.id = checkinfo.id)")%(productname,day)
      print("Number of checks on certain product by day the of week")
      cursor.execute(query)
      result = cursor.fetchall()
      print(result)
  elif n==7:
      productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
      query = ("SELECT week_day, COUNT( product__name ) FROM products, checkinfo WHERE (product__name =  '%s' AND products.id = checkinfo.id)GROUP BY (week_day)")%productname
      print("Number of checks on certain product by dayS the of week: 0:Monday .. 6:Sunday")
      cursor.execute(query)
      result = cursor.fetchall()
      for i in result:
          print(i)

  elif  n==0:
      print("Good bye!")
      break
      raise SystemExit()
      cursor.close()
      cnx.close()

  elif  n not in [0,1,2,3,4,5,6,7]:
      print("This command does not exist")