Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
8.7 kB
2
Indexable
Never
# 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()