Untitled
unknown
plain_text
3 years ago
8.7 kB
11
Indexable
# connect database
import mysql.connector as a
password = str(input("Database Password : "))
con = a.connect(host = "localhost", user = 'root', passwd = password)
#select or create database
c = con.cursor()
c.execute("show databases")
dl = c.fetchall()
dl2 = []
for i in dl:
dl2.append(i[0])
if 'rest1' in dl2:
sql = 'use rest1'
c.execute(sql)
else:
sql1 = 'create database rest1'
c.execute(sql1)
sql2 = 'use rest1'
c.execute(sql2)
sql3 = """CREATE TABLE Dish (Dish varchar(20), Cost integer, Cook varchar(50), DishID varchar(20))"""
c.execute(sql3)
sql4 = """CREATE TABLE Orders (DishIDs varchar(100), Cost integer, Date varchar(20), Customer varchar(50), Aadhar varchar(20))"""
c.execute(sql4)
sql5 = """CREATE TABLE Cook (Name varchar(100), Aadhar varchar(20), Dishes varchar(100), Salary integer, DOJ varchar(20))"""
c.execute(sql5)
sql6 = """CREATE TABLE Salary (Name varchar(100), Aadhar varchar(20), Bank varchar(20), Month varchar(20), Salary integer, Days integer, net integer)"""
c.execute(sql6)
sql7 = """CREATE TABLE Expenditure (Type varchar(100), Cost integer, Date varchar(20))"""
c.execute(sql7)
con.commit()
# System Password Login
def signin():
print("\n")
print("Welcome To Mariott! ")
print("\n")
p = input("System Password : ")
if p == "admin123":
options()
else:
print("Please retry \n")
signin()
# Display options
def options():
print("1. Dishes \n2. Cooks \n3. Salary \n4. Order \n5. Income \n6. Bills")
choice = input('Select Option : ')
while True:
if(choice == "1"):
Dish()
elif(choice == "2"):
Cook()
elif(choice == "3"):
PaySalary()
elif(choice == "4"):
NewOrder()
elif(choice == "5"):
NetIncome()
elif(choice == "6"):
Expenditure()
else:
print("Wrong Choice, please input correct value")
def Dish():
choice = input("1. Add \n 2. Remove \n 3. Display \n 4. Main Menu \ninput Choice")
if choice == "1":
dn = input("Dish Name : ")
dc = input("Dish Cost : ")
Cname()
cb = "Cooked by : "
did = str(DishID())
data = (dn, dc, cb, did)
sql = 'insert into Dish values (%s, %s, %s, %s)'
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Data Entered Successfully!")
elif choice == "2":
did = input("DishID : ")
data = (did, )
sql = 'delete from Dish where DishID = %s'
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Data updated successfully!")
elif choice == '3':
print("\n")
sql = "select * from Dish"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
for i in d:
print(i[0], "-", i[1], "-", i[2], '-', i[3])
print("\n")
else:
options()
def DishID():
sql = "select count(*), max(dishid) from dish"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
for i in d:
if i[0] == 0:
return(1)
else:
return(int(i[1])+1)
def Cname():
sql = "select Name, Dishes from Cook"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
print("Avaliable cooks are : \n")
for i in d:
print(i[0], "---", i[1])
return
# add or remove cook
def Cook():
choice = input("1. Add \n 2. Remove \n 3. Display \n 4. Main Menu \ninput Choice")
if choice == "1":
cn = input("Cook Name : ")
ca = input('Aadhar : ')
d = input("Dishes")
s = int(input("Salary : "))
doj = input("Date of Joining(Y/M/D) : ")
data = (cn, ca, d, s, doj)
sql = 'insert into Cook values (%s, %s, %s, %s, %s)'
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Data Entered Successfully!")
elif choice == "2":
cn = input("Cook Name : ")
ca = input("Aadhar : ")
data = (cn, ca)
sql = "delete from Cook where Name = %s and Aadhar = %s"
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Data updated successfully!")
elif choice == "3":
sql = "select * from cook"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
for i in d:
print(i[0], "-", i[1], "-", i[2], '-', i[3], "-", i[4])
print("\n")
else:
options()
# pay cook's salary
def PaySalary():
sql8 = "select * from cook"
c = con.cursor()
c.execute(sql8)
d = c.fetchall()
for i in d:
print(i[0], "-", i[1], "-", i[2], '-', i[3], "-", i[4])
print("--------------------------")
cn = input("Cook's Name : ")
ca = input("Aadhar : ")
ba = input("Bank Account Number : ")
mn = input("Date(Y/M/D) : ")
s = int(input("Salary : "))
d = int(input("Working days : "))
if mn[5:7] in ["01", '03', '05', '07', '08', '10', '12']:
ns = (s/31)*d
elif mn[5:7] in ['04', '06', '09', '11']:
ns = (s/30)*d
else:
ns = (s/28)*d
data = (cn, ca, ba, mn, s, d, ns)
sql = "insert into Salary values(%s, %s, %s, %s, %s, %s)"
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Net Salary Paid : ", ns, " Rs")
print("-----------------------------------------")
choice = input("1. Salary menu \n2. Main Menu : ")
print("-----------------------------------------")
if choice == '1':
PaySalary()
else:
options()
# make new order
def NewOrder():
sql = "select * from Dish"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
print("NAME-----COST-----COOK-----DISH ID")
for i in d:
print(i[0], "-", i[1], "-", i[2], '-', i[3])
print("\n")
dil = []
while True:
di = input("select dish id (0 when done) : ")
if di == "0":
break
else:
dil.append(di)
sql = "select DishID, Cost from Dish"
c = con.cursor()
c.execute(sql)
d = c.fetchall()
dicl = {}
for i in d:
dicl[i[0]] = i[1]
tc = 0
for i in dil:
dc = dicl[i]
tc = tc + dc
dt = input("Date(Y/M/D) : ")
cn = input("Customer Name : ")
ca = input("Aadhar : ")
lis = input("Enter Dish IDs : ")
data = (lis, tc, dt, cn, ca)
sql = "insert into Salary values(%s, %s, %s, %s, %s)"
c = con.cursor()
c.execute(sql, data)
con.commit()
print("Total Amount : ", tc, " Rs")
print("-----------------------------------------")
choice = input("1. Order menu \n2. Main Menu \nInput Choice")
print("-----------------------------------------")
if choice == '1':
NewOrder()
else:
options()
# monthly net income
def NetIncome():
c = con.cursor()
t = input("1.All 2.Year 3.Month 4.Date 5.Main Menu: ")
if t == '1':
sql = 'select Cost from Orders'
c.execute(sql)
d = c.fetchall()
oi = 0
for i in d:
oi = oi + i[0]
print("Total income from Orders: ",oi,"Rs")
elif t == '2':
y = input("Enter Year: ")
sql = 'select Cost. Date from Orders'
c.execute(sql)
d = c.fetchall()
oi = 0
for i in d:
if y in i[1]:
oi = oi + i[0]
print("Total income from Orders :", oi, "Rs")
elif t == '4':
y = input("Enter Y/M/D :")
sql = 'select Cost from Orders where Date like %s'
data = (y,)
c.execute(sql,data)
d = c.fetchall()
oi = 0
for i in d:
oi = oi + i[0]
print("Total income from orders: ",oi,"Rs")
else:
options()
#make new expenditure
def Expenditure():
choice = input("1. BILL ENTRY 2.SHOW BILLS 3.MAIN MENU :")
if choice == '1':
t = input("Type: ")
c = int(input("Cost: "))
d = input("Date: Y/M/D :")
data = (t,c,d)
sql = 'insert into Expenditure values(%s,%s,%s)'
c = con.cursor()
c.execute(sql,data)
con.commit()
print("Data Entered Successfully")
options()
elif choice == '2':
c = con.cursor()
t = input("1. ALL 2. YEAR 3.MONTH 4.DATE: ")
if t == '1':
sql = 'select * from expenditure'
c.execute(sql)
d = c.fetchall()
for i in d:
if y in i[2]:
print(i)
else:
options()
signin()Editor is loading...