import sqlite3
from tkinter import *



def dummy():
    connection = sqlite3.connect("phase2.db")

    cursor = connection.cursor()

    cursor.execute("SELECT * FROM Supplier")
    print("fetchall:")
    result = cursor.fetchall()
    for r in result:
    	print(r)

def fetch(entries):
   for entry in entries:
      field = entry[0]
      text  = entry[1].get()
      print('%s: "%s"' % (field, text))

def makeform(root, fields):
   entries = []
   for field in fields:
      row = Frame(root)
      lab = Label(row, width=15, text=field, anchor='w')
      ent = Entry(row)
      row.pack(side=TOP, fill=X, padx=5, pady=5)
      lab.pack(side=LEFT)
      ent.pack(side=LEFT, expand=NO, fill=X)
      entries.append((field, ent))
   return entries

def querycontroller(buttonName):
	if (buttonName == "b1"):
		query1()
	if (buttonName == "b2"):
		query2()
	if (buttonName == "b3"):
		query3()
	if (buttonName == "b4"):
		query4()
	if (buttonName == "b5"):
		query5()
	if (buttonName == "b6"):
		query6()
	if (buttonName == "b7"):
		query7()
	if (buttonName == "b8"):
		query8()
	if (buttonName == "b9"):
		query9()
	if (buttonName == "b10"):
		query10()
	if (buttonName == "b11"):
		query11()
	if (buttonName == "b12"):
		query12()
	if (buttonName == "b13"):
		query13()
	if (buttonName == "b14"):
		query14()
	if (buttonName == "b15"):
		query15()
	if (buttonName == "b16"):
		query16()
	if (buttonName == "b17"):
		query17()
	if (buttonName == "b18"):
		query18()
	if (buttonName == "b19"):
		query19()
	if (buttonName == "b20"):
		query20()

def query1():
	root = Tk()
	root.title("Query 1")
	customer_fields = ['Customer ID', 'E-Mail', 'Full Name','Billing Address','Shipping Address','Phone Number', 'Total Orders']
	ents = makeform(root, customer_fields)
	b1 = Button(root, text='Insert Customer',command=(lambda e=ents: q1p(e)))
	b1.place(x=0 , y=300)
	b2 = Button(root, text='Quit', command=root.quit)
	b2.place(x=140, y=300)
	root.geometry("400x500")
	root.mainloop()

def q1p(entries):
   id_array = entries[0]
   email_array = entries[1]
   name_array = entries[2]
   bill_array = entries[3]
   ship_array = entries[4]
   phone_array = entries[5]
   orders_array = entries[6]

   id_value = id_array[1].get()
   email_value = email_array[1].get()
   name_value = name_array[1].get()
   bill_value = bill_array[1].get()
   ship_value = ship_array[1].get()
   phone_value = phone_array[1].get()
   orders_value = orders_array[1].get()


   connection = sqlite3.connect("phase2.db")
   cursor = connection.cursor()

   format_str = """INSERT INTO Customer (Customer_Id, Email, Name, BillingAddress, ShippingAddress, PhoneNumber, TotalOrders)
    VALUES ("{id}", "{email}", "{name}", "{bill}", "{ship}", "{phone}", "{orders}");"""

   sql_command = format_str.format(id=id_value, email=email_value, name=name_value, bill=bill_value, ship=ship_value, phone=phone_value, orders=orders_value)
   cursor.execute(sql_command)
   connection.commit()


def query2():
	root = Tk()
	root.title("Query 2")
	cart_fields = ['Cart ID', 'Total Price' , 'Customer ID']
	ents = makeform(root, cart_fields)
	b1 = Button(root, text='Insert Cart',command=(lambda e=ents: q2p(e)))
	b1.place(x=0 , y=300)
	b2 = Button(root, text='Quit', command=root.quit)
	b2.place(x=140, y=300)
	root.geometry("400x500")
	root.mainloop()

def q2p(entries):
   id_array = entries[0]
   price_array = entries[1]
   cid_array = entries[2]


   id_value = id_array[1].get()
   price_value = price_array[1].get()
   cid_value = cid_array[1].get()
   connection = sqlite3.connect("phase2.db")
   cursor = connection.cursor()

   format_str = """INSERT INTO Cart (Cart_Id, TotalPrice, Customer_Id)
    VALUES ("{id}", "{price}", "{cid}");"""

   sql_command = format_str.format(id=id_value, price=price_value, cid=cid_value)
   cursor.execute(sql_command)
   connection.commit()

def query3():
	root = Tk()
	root.title("Query 3")
	supp_fields = ['Supplier ID', 'Supplier Name' , 'Address', 'Phone Number', 'Total Products', ]
	ents = makeform(root, supp_fields)
	b1 = Button(root, text='Insert Supplier',command=(lambda e=ents: q3p(e)))
	b1.place(x=0 , y=300)
	b2 = Button(root, text='Quit', command=root.quit)
	b2.place(x=140, y=300)
	root.geometry("400x500")
	root.mainloop()

def q3p(entries):
   id_array = entries[0]
   name_array = entries[1]
   add_array = entries[2]
   phone_array = entries[3]
   prod_array = entries[4]


   id_value = id_array[1].get()
   name_value = name_array[1].get()
   add_value = add_array[1].get()
   phone_value = phone_array[1].get()
   prod_value = prod_array[1].get()

   connection = sqlite3.connect("phase2.db")
   cursor = connection.cursor()

   format_str = """INSERT INTO Supplier (Supplier_Id, SupplierName, Address, PhoneNumber, TotalProducts)
    VALUES ("{id}", "{name}", "{address}", "{phone}", "{products}");"""

   sql_command = format_str.format(id=id_value, name=name_value, address=add_value, phone=phone_value, products=prod_value)
   cursor.execute(sql_command)
   connection.commit()

def query4():
	root = Tk()
	root.title("Query 4")
	root.mainloop()

def query5():
	root = Tk()
	root.title("Query 5")
	root.mainloop()

def query6():
	root = Tk()
	root.title("Query 6")
	root.mainloop()

def query7():
	root = Tk()
	root.title("Query 7")
	root.mainloop()

def query8():
	root = Tk()
	root.title("Query 8")
	root.mainloop()

def query9():
	root = Tk()
	root.title("Query 9")
	root.mainloop()

def query10():
	root = Tk()
	root.title("Query 10")
	root.mainloop()

def query11():
	root = Tk()
	root.title("Query 11")
	root.mainloop()

def query12():
	root = Tk()
	root.title("Query 12")
	root.mainloop()

def query13():
	root = Tk()
	root.title("Query 13")
	root.mainloop()

def query14():
	root = Tk()
	root.title("Query 14")
	root.mainloop()

def query15():
	root = Tk()
	root.title("Query 15")
	root.mainloop()

def query16():
	root = Tk()
	root.title("Query 16")
	root.mainloop()

def query17():
	root = Tk()
	root.title("Query 17")
	root.mainloop()

def query18():
	root = Tk()
	root.title("Query 18")
	root.mainloop()

def query19():
	root = Tk()
	root.title("Query 19")
	root.mainloop()

def query20():
	root = Tk()
	root.title("Query 20")
	root.mainloop()

if __name__ == '__main__':
   root = Tk()
   root.title("E-Commerce Game Shop")
   b1 = Button(root, text='Query 1',command=(lambda b="b1": querycontroller(b)))
   b1.place(x=0, y=50)

   b2 = Button(root, text='Query2', command=(lambda b="b2": querycontroller(b)))
   b2.place(x=80 , y=50)
   b3 = Button(root, text='Query3', command=(lambda b="b3": querycontroller(b)))
   b3.place(x=160 , y=50)

   b4 = Button(root, text='Query4', command=(lambda b="b4": querycontroller(b)))
   b4.place(x=240 , y=50)

   b5 = Button(root, text='Query5', command=(lambda b="b5": querycontroller(b)))
   b5.place(x=320 , y=50)


   b6 = Button(root, text='Query6',command=(lambda b="b6": querycontroller(b)))
   b6.place(x=0, y=100)

   b7 = Button(root, text='Query7', command=(lambda b="b7": querycontroller(b)))
   b7.place(x=80 , y=100)
   b8 = Button(root, text='Query8', command=(lambda b="b8": querycontroller(b)))
   b8.place(x=160 , y=100)

   b9 = Button(root, text='Query9', command=(lambda b="b9": querycontroller(b)))
   b9.place(x=240 , y=100)

   b10 = Button(root, text='Query10', command=(lambda b="b10": querycontroller(b)))
   b10.place(x=320 , y=100)

   b11 = Button(root, text='Query11',command=(lambda b="b11": querycontroller(b)))
   b11.place(x=0, y=150)

   b12 = Button(root, text='Query12', command=(lambda b="b12": querycontroller(b)))
   b12.place(x=80 , y=150)
   b13 = Button(root, text='Query13', command=(lambda b="b13": querycontroller(b)))
   b13.place(x=160 , y=150)

   b14 = Button(root, text='Query14', command=(lambda b="b14": querycontroller(b)))
   b14.place(x=240 , y=150)

   b15 = Button(root, text='Query15', command=(lambda b="b15": querycontroller(b)))
   b15.place(x=320 , y=150)

   b16 = Button(root, text='Query16',command=(lambda b="b16": querycontroller(b)))
   b16.place(x=0, y=200)

   b17 = Button(root, text='Query17', command=(lambda b="b17": querycontroller(b)))
   b17.place(x=80 , y=200)
   b18 = Button(root, text='Query18', command=(lambda b="b18": querycontroller(b)))
   b18.place(x=160 , y=200)

   b19 = Button(root, text='Query19', command=(lambda b="b19": querycontroller(b)))
   b19.place(x=240 , y=200)

   b20 = Button(root, text='Query20', command=(lambda b="b20": querycontroller(b)))
   b20.place(x=320 , y=200)

   root.geometry("600x400")
   dummy()
   root.mainloop()