def Delivery_insertion():
connector = SQL(host=server_name, user=server_admin)
columns = [
'DELIVERY_Fname', 'DELIVERY_Lname', 'DELIVERY_EMAIL',
'DELIVERY_PASSWORD', 'DELIVERY_CREDIT_CARD_NUMBER', 'AREA',
'VECHILE_LICENCE', 'VECHILE_MODEL', 'Delivery_PHONE_NUMBER'
]
values = []
for key in columns:
values.append(request.json[key])
columns.append('AVAILABLE')
values.append(1)
columns.append('RATE')
values.append(0)
columns.append('NUMBER_OF_DORDERS')
values.append(0)
connector.insert_query(table='DELIVERY', attributes=columns, values=values)
condition = "DELIVERY_EMAIL = '" + values[2] + "'"
DeliveryID = connector.select_query(table='DELIVERY',
columns=['delivery_ID'],
sql_condition=condition)
connector.insert_query(table='DELIVERY_VERIFICATION',
attributes=['DELIVERY_ID'],
values=DeliveryID['delivery_ID'])
connector.close_connection()
return "1"
def AddtoCart():
sql = SQL(server_name, server_admin)
product_id = request.json['product_id']
dentist_email = request.json['dentist_email']
for_id = sql.select_query('DENTIST', ['DENTIST_ID'],
f"DENTIST_EMAIL = '{dentist_email}'")
dentist_id = for_id['DENTIST_ID'][0]
check_exsist = sql.select_query(
'DentistCart', ['DENTIST_ID', 'Number_Units'],
f"DENTIST_ID = {dentist_id} and PRODUCT_ID = {product_id}")
try:
if len(check_exsist['DENTIST_ID']) > 0:
units = check_exsist['Number_Units'][0] + 1
#sql.update_query('DentistCart', {'Number_Units' : units}, f" DENTIST_ID = {dentist_id} and PRODUCT_ID = {product_id}")
sql.exectute_query(
f"UPDATE DentistCart SET Number_Units = {units} WHERE DENTIST_ID = {dentist_id} and PRODUCT_ID = {product_id};"
)
else:
sql.insert_query('DentistCart',
['DENTIST_ID', 'PRODUCT_ID', 'Number_Units'],
[dentist_id, product_id, 1])
except:
sql.insert_query('DentistCart',
['DENTIST_ID', 'PRODUCT_ID', 'Number_Units'],
[dentist_id, product_id, 1])
sql.close_connection()
return '1'
def dentist_insertion():
columns = ['DENTIST_Fname', 'DENTIST_LNAME', 'DENTIST_EMAIL', 'DENTIST_PASSWORD', 'DENTIST_PHONE_NUMBER', 'DENTIST_ADDRESS', 'DENTIST_ZIP_CODE', 'DENTIST_REGION', 'DENTIST_CITY', 'DENTIST_CREDIT_CARD_NUMBER']
values = []
for key in columns:
values.append(request.json[key])
connector = SQL(host=server_name, user=server_admin)
connector.insert_query(table = 'DENTIST', attributes=columns, values=values)
connector.close_connection()
return "1"
def Store_insertion():
columns = [
'STORE_NAME', 'EMAIL', 'PASSWORD', 'PHONE_NUMBER', 'CREDIT_CARD_NUMBER'
]
values = []
for key in columns:
values.append(request.json[key])
connector = SQL(host=server_name, user=server_admin)
connector.insert_query(table='STORE', attributes=columns, values=values)
connector.close_connection()
return "1"
def Manager_Insertion():
cloumns = [
'MANAGER_Fname', 'MANAGER_Lname', 'MANAGER_EMAIL', 'MANAGER_PASSWORD',
'MANAGEMENT_TYPE', 'AREA_OF_MANAGEMENT'
]
values = []
for key in cloumns:
values.append(request.json[key])
connector = SQL(server_name, server_admin, server_password)
connector.insert_query(table='Manager', attributes=cloumns, values=values)
connector.close_connection()
return "1"
def Product_Insertion():
columns = ['NUMBER_OF_UNITS','STORE_ID','PRODUCT_ID','PRICE','SELLING_PRICE','Brand','DESCRIPTION' ,'Category']
values =[]
for key in columns:
values.append(request.json[key])
connector = SQL(host=server_name, user=server_admin)
PRODUCT_IDColumn = ['PRODUCT_ID']
condition = "PRODUCT_NAME = '" + values[2]+"' and STORE_ID = '" +values[1]+ "'"
ProductID= connector.select_query(table='product',columns=PRODUCT_IDColumn,sql_condition=condition)
if ProductID['PRODUCT_ID']== []:
columns[2]='PRODUCT_NAME'
print(columns)
print(values)
connector.insert_query(table='product' ,attributes=columns, values=values)
connector.close_connection()
return "1"
values[2]=(",".join(repr(e) for e in ProductID['PRODUCT_ID']))
Query="UPDATE product SET NUMBER_OF_UNITS ='"+ values[0] +"', PRICE ='"+ values[3] +"', SELLING_PRICE ='"+ values[4] +"'WHERE STORE_ID ='"+values[1]+"' and PRODUCT_ID = '"+values[2] +"'"
connector.exectute_query(Query)
connector.close_connection()
return "1"
def AddComment():
# Adding a comment
sql = SQL(server_name, server_admin)
dentist_email = request.json['dentist_email']
for_id = sql.select_query('DENTIST', ['DENTIST_ID'], f"DENTIST_EMAIL = '{dentist_email}'")
dentist_id = for_id['DENTIST_ID'][0]
product_id = request.json['product_id']
comment = request.json['comment']
# datetime object containing current date and time
now = datetime.now()
# dd/mm/YY H:M:S
dt_string = now.strftime("%Y-%m-%d %H:%M:%S")
sql.insert_query('DENTIST_COMMENT', ['DENTIST_ID', 'PRODUCT_ID', 'COMMENT_CONTEXT', 'LIKES', 'COMMENT_TIME'], [dentist_id, product_id, comment, 0, dt_string])
sql.close_connection()
return '1'
def Store2_insertion():
columns = ['STORE_ID', 'ADDRESS', 'REGION', 'CITY', 'ZIP_CODE']
values = []
for key in columns:
values.append(request.json[key])
connector = SQL(host=server_name, user=server_admin)
StoreIDColumn = ['STORE_ID']
condition = "STORE_NAME = '" + values[0] + "'"
StoreID = connector.select_query(table='STORE',
columns=StoreIDColumn,
sql_condition=condition)
values[0] = (",".join(repr(e) for e in StoreID['STORE_ID']))
condition = "STORE_ID = '" + values[0] + "' + REGION = '" + values[0] + "'"
connector.insert_query(table='STORE_BRANCH',
attributes=columns,
values=values)
store_bransh_id = connector.select_query(table='STORE_BRANCH',
columns=['Store_branch_id'],
sql_condition=condition)
connector.insert_query(table='store_verification',
attributes=['Store_branch_id'],
values=store_bransh_id)
connector.close_connection()
return "1"
def InsertChatRoom():
useroneType = request.json['useronetype']
usertwoType = request.json['usertwotype']
useroneID = request.json['useroneid']
usertwoID = request.json['usertwoid']
message = request.json['message']
senderID = request.json['senderid']
senerType = request.json['sendertype']
connector = SQL(host=server_name,
user=server_admin,
password=server_password)
if (useroneType == 'Manager' and usertwoType == 'Store'):
columns = [
'MESSAGES', 'MANAGER_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, useroneID, usertwoID, senderID, senerType]
connector.insert_query(table='MANAGER_STORE_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (usertwoType == 'Manager' and useroneType == 'Store'):
columns = [
'MESSAGES', 'MANAGER_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, usertwoID, useroneID, senderID, senerType]
connector.insert_query(table='MANAGER_STORE_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (useroneType == 'Manager' and usertwoType == 'Delivery'):
columns = [
'MESSAGES', 'MANAGER_ID', 'DELIVERY_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, useroneID, usertwoID, senderID, senerType]
connector.insert_query(table='MANAGER_DELIVERY_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (usertwoType == 'Manager' and useroneType == 'Delivery'):
columns = [
'MESSAGES', 'MANAGER_ID', 'DELIVERY_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, usertwoID, useroneID, senderID, senerType]
connector.insert_query(table='MANAGER_DELIVERY_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (useroneType == 'Dentist' and usertwoType == 'Delivery'):
columns = [
'MESSAGES', 'DENTIST_ID', 'DELIVERY_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, useroneID, usertwoID, senderID, senerType]
connector.insert_query(table='DENTIST_DELIVERY_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (usertwoType == 'Dentist' and useroneType == 'Delivery'):
columns = [
'MESSAGES', 'DENTIST_ID', 'DELIVERY_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, usertwoID, useroneID, senderID, senerType]
connector.insert_query(table='DENTIST_DELIVERY_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (useroneType == 'Dentist' and usertwoType == 'Store'):
columns = [
'MESSAGES', 'DENTIST_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, useroneID, usertwoID, senderID, senerType]
connector.insert_query(table='DENTIST_STORE_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (usertwoType == 'Dentist' and useroneType == 'Store'):
columns = [
'MESSAGES', 'DENTIST_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, usertwoID, useroneID, senderID, senerType]
connector.insert_query(table='DENTIST_STORE_CHAT',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (useroneType == 'Delivery' and usertwoType == 'Store'):
columns = [
'MESSAGES', 'Delivery_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, useroneID, usertwoID, senderID, senerType]
connector.insert_query(table='store_delivery_chat',
attributes=columns,
values=values)
connector.close_connection()
return "1"
elif (usertwoType == 'Delivery' and useroneType == 'Store'):
columns = [
'MESSAGES', 'Delivery_ID', 'STORE_ID', 'SENDER_ID', 'SENDER_TYPE'
]
values = [message, usertwoID, useroneID, senderID, senerType]
connector.insert_query(table='store_delivery_chat',
attributes=columns,
values=values)
connector.close_connection()
return "1"
