def registerAdmin(name, username, password, contact_no, access_token):
try:
flag = 0
db, cursor = connection()
insert_sql = f"insert into admin(name,username,password,contact_no,access_token) values('{name}','{username}','{password}','{contact_no}','{access_token}')"
search_sql = f"select * from admin where username like '{username}' and password like '{password}' and contact_no like '{contact_no}'"
cursor.execute(search_sql)
rs = cursor.fetchall()
if (len(rs) > 0):
flag = 1
else:
cursor.execute(insert_sql)
db.commit()
except Exception as e:
db.rollback()
raise e
else:
if (flag == 0):
print("inserted successfully")
else:
print("user already exist")
finally:
db.close()
def registerCustomer(name, contact_no, address, c_type, customer_email):
try:
flag = 0
db, cursor = connection()
insert_sql = f"insert into customer(customer_name,customer_contact,customer_address,customer_type,customer_email) values('{name}','{contact_no}','{address}','{c_type}','{customer_email}')"
search_sql = f"select * from customer where customer_name like '{name}' and customer_contact like '{contact_no}'"
cursor.execute(search_sql)
rs = cursor.fetchall()
if (len(rs) > 0):
flag = 1
else:
cursor.execute(insert_sql)
db.commit()
except Exception as e:
db.rollback()
raise e
else:
if (flag == 0):
print("inserted successfully")
else:
print("customer already exist")
finally:
db.close()
def getAllProduct():
db, cursor = connection()
try:
sql = f"select * from product"
cursor.execute(sql)
resu = cursor.fetchall()
except Exception as e:
print("Something went wrong")
raise e
else:
print(list(resu))
finally:
db.close()
def buyProduct(customer_id, product_id, buying_quantity, buying_total_bill):
db, cursor = connection()
check_quantity = f"select product_quantity from product where product_id like '{product_id}'"
cursor.execute(check_quantity)
quantity = cursor.fetchall()
#checking the quantity.
if (int(quantity[0][0]) < 1):
print("Sorry the product is sold out.")
else:
try:
flag = 0
db, cursor = connection()
insert_sql = f"insert into buying(customer_id,product_id,buying_quantity,buying_total_bill) values('{customer_id}','{product_id}','{buying_quantity}','{buying_total_bill}')"
search_sql = f"select * from buying where product_id like '{product_id}'and customer_id like '{customer_id}'"
update_sql = f"update product set product_quantity = product_quantity-'{buying_quantity}' where product_id like '{product_id}'"
cursor.execute(search_sql)
rs = cursor.fetchall()
if (len(rs) > 0):
flag = 1
else:
cursor.execute(insert_sql)
cursor.execute(search_sql)
cursor.execute(update_sql)
db.commit()
except Exception as e:
db.rollback()
raise e
else:
if (flag == 0):
print("bought successfully")
return (True)
else:
print("Already purchased")
return (False)
finally:
db.close()
def getProductDetail(product_name):
try:
flag = 0
db, cursor = connection()
sql = f"select * from product where product_name like '{product_name}'"
cursor.execute(sql)
res = cursor.fetchall()
if len(res) > 0:
flag = 1
except Exception as e:
print("Something went wrong")
raise e
else:
print(list(res))
finally:
db.close()
def getTotalBill(product_id, product_quantity):
try:
flag = 1
db, cursor = connection()
sql_price = f"select product_price from product where product_id like '{product_id}'"
cursor.execute(sql_price)
res = cursor.fetchall()
except Exception as e:
print("Something went wrong!!")
raise e
else:
res = int(res[0][0]) * (product_quantity)
return res
finally:
db.close()
#print(getTotalBill(1,2))
def getTotalBuyDetail(customer_id):
try:
flag = 0
db, cursor = connection()
sql = f"select * from buying where customer_id like'{customer_id}'"
cursor.execute(sql)
res = cursor.fetchall()
if (len(res) > 0):
flag = 1
except Exception as e:
print("Something went wrong!!")
raise e
else:
if (flag == 1):
print(list(res))
else:
print(f"No record found for customer id {customer_id}")
finally:
db.close()
def getProductId(product_name):
try:
flag = 0
db, cursor = connection()
sql = f"select product_id from product where product_name like '{product_name}'"
cursor.execute(sql)
result = cursor.fetchall()
if len(result) > 0:
flag = 1
except Exception as e:
print("Something went wrong")
raise e
else:
#print(list(result))
return (result[0][0])
#print(result[0][0])
finally:
db.close()
#getProductId('java')
def getCustomer(username):
try:
flag = 0
db, cursor = connection()
sql = f"select * from customer where customer_name like '{username}'"
cursor.execute(sql)
result = cursor.fetchall()
if (len(result) > 0):
flag = 1
except Exception as e:
print("Something went wrong!!")
raise e
else:
if (flag == 0):
print(f"No records found")
else:
print(list(result))
#print(result[0][1]) this can be used to print values in the tuple
#print(type(result))
finally:
db.close()
def checkLogin(username, contact_no):
try:
flag = 0
db, cursor = connection()
sql = f"select * from customer where customer_name like '{username}' and customer_contact like '{contact_no}'"
cursor.execute(sql)
res = cursor.fetchall()
if len(res) > 0:
flag = 1
except Exception as e:
print("Something went wrong")
raise e
else:
if (flag):
print(f"Welcome! {username}")
return (True)
else:
print("No records available")
#return(False)
finally:
db.close()
def checkAdminLogin(username, password):
try:
flag = 0
db, cursor = connection()
sql = f"select * from admin where username like '{username}' and password like '{password}'"
cursor.execute(sql)
res = cursor.fetchall()
if len(res) > 0:
flag = 1
except Exception as e:
print("Something went wrong")
raise e
else:
if (flag):
print(f"Welcome admin '{username}'")
return (True)
else:
return (False)
finally:
db.close()
def registerProduct(product_name, product_price, product_quantity):
try:
flag = 0
db, cursor = connection()
insert_sql = f"insert into product(product_name,product_price,product_quantity) values ('{product_name}','{product_price}','{product_quantity}')"
search_sql = f"select * from product where product_name like '{product_name}' and product_price like {product_price}"
cursor.execute(search_sql)
rs = cursor.fetchall()
if (len(rs) > 0):
flag = 1
else:
cursor.execute(insert_sql)
db.commit()
except Exception as e:
db.rollback()
raise e
else:
if (flag == 0):
print("inserted successfully")
else:
print("produt already exist")
finally:
db.close()
def getCustomerId(customer_contact):
try:
flag = 0
db, cursor = connection()
sql = f"select customer_id from customer where customer_contact like '{customer_contact}'"
cursor.execute(sql)
resul = cursor.fetchall()
if (len(resul) > 0):
flag = 1
except Exception as e:
print("Something went wrong!!")
raise e
else:
if (flag == 0):
print(f"No records found")
else:
#return resul
return (resul[0][0])
finally:
db.close()
#print(getCustomerId('9930'))
def generateCustomerCSV():
db,cursor = connection()
dataframe = pd.read_sql('select * from customer',db)
dataframe.to_csv("customer.csv")
#generateCustomerCSV()