def ViewComments():
# Retrive Comments of a certian product
sql = SQL(server_name, server_admin)
product_id = request.json['product_id']
comment_result = sql.select_query('DENTIST_COMMENT', ['DENTIST_ID', 'COMMENT_ID', 'COMMENT_CONTEXT', 'COMMENT_TIME', 'LIKES'], f" PRODUCT_ID = {product_id}")
Output = {}
Output['comment_id'] = comment_result['COMMENT_ID']
Output['comment'] = comment_result['COMMENT_CONTEXT']
Output['likes'] = comment_result['LIKES']
Output['DentistID'] = comment_result['DENTIST_ID']
Dentist_Names = []
Dentist_Images = []
dentist_ids = comment_result['DENTIST_ID']
for dentist_id in dentist_ids:
dentist_query = sql.select_query('DENTIST', ['DENTIST_Fname', 'DENTIST_LNAME', 'DENTIST_IMAGE_URL'], f" DENTIST_ID = {dentist_id}")
name = dentist_query['DENTIST_Fname'][0] + " " + dentist_query["DENTIST_LNAME"][0]
Dentist_Names.append(name)
Dentist_Images.append(dentist_query['DENTIST_IMAGE_URL'][0])
Output['DentistName'] = Dentist_Names
Output['Dentist_Images'] = Dentist_Images
#Date
Dates = []
for date in comment_result['COMMENT_TIME']:
Dates.append(CheckDate(date))
Output['CommentDate'] = Dates
sql.close_connection()
return json.dumps(Output)
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 Accept_Request():
reqType = request.json['type']
ManagerID = request.json['MID']
connector = SQL(server_name, server_admin, server_password)
if reqType == 'Delivery':
Deliver_id = request.json['DID']
Condition = "DELIVERY_ID = '" + str(Deliver_id) + "'"
connector.delete_query(table='delivery_verification',
sql_condition=Condition)
connector.update_query(
table='DELIVERY',
columns_values_dict={'MANAGER_ID': str(ManagerID)},
sql_condition=Condition)
elif reqType == 'Store':
Store_id = request.json['SID']
store_branch_id = request.json['BID']
Condition = "STORE_branch_ID = '" + str(store_branch_id) + "'"
connector.delete_query(table='store_verification',
sql_condition=Condition)
connector.update_query(
table='Store_branch',
columns_values_dict={'MANAGER_ID': str(ManagerID)},
sql_condition=Condition)
connector.close_connection()
return "1"
def DeliveredOrders():
deliverid = request.json['DELIVERYID']
today = datetime.date.today().strftime("%Y-%m-%d")
columns = [
'O.ORDER_ID',
'D.DENTIST_FNAME',
'D.DENTIST_LNAME',
'O.SHIPMENT_STATUS',
'O.TOTAL_COST',
]
condition = "O.DELIVERY_ID = " + deliverid + " and O.ORDER_DATE= '" + today + "' and O.DENTIST_ID=D.DENTIST_ID and O.SHIPMENT_STATUS ='DELIVERED' order by O.SHIPMENT_STATUS"
connector = SQL(host=server_name, user=server_admin)
result = connector.select_query(table='ORDERS as O , DENTIST as D',
columns=columns,
sql_condition=condition,
DISTINCTdetector=True)
connector.close_connection()
result = {
'cost': result['O.TOTAL_COST'],
'orderids': result['O.ORDER_ID'],
'number': len(result['O.ORDER_ID']),
'dentistfname': result['D.DENTIST_FNAME'],
'dentistlname': result['D.DENTIST_LNAME'],
'status': result['O.SHIPMENT_STATUS']
}
return json.dumps(result)
def DeliverOrder():
deliveryid = request.json['DELIVERYID']
orderid = request.json['ORDERID']
numberofDorders = request.json['no.Dorders']
connector = SQL(host=server_name, user=server_admin)
result = connector.select_query(table='ORDERS',
columns=['SHIPMENT_STATUS'],
sql_condition="ORDER_ID= " + orderid)
today = datetime.date.today().strftime("%Y-%m-%d")
if result['SHIPMENT_STATUS'][0] == 'ASSIGNED':
return "0"
else:
Query = {
'O.DELIVERY_ID': deliveryid,
'O.SHIPMENT_STATUS': 'ASSIGNED',
'D.AVAILABLE': "0",
'D.NUMBER_OF_DORDERS': numberofDorders,
'O.ORDER_DATE': today
}
condition = "O.ORDER_ID = " + orderid + " and D.delivery_id = " + deliveryid
connector.update_query(table='ORDERS as O, DELIVERY as D',
columns_values_dict=Query,
sql_condition=condition)
connector.close_connection()
return "1"
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 OrdersToBeDelivered():
columns = [
'o.ORDER_ID', 'o.TOTAL_COST', 'd.DENTIST_Fname', 'd.DENTIST_LNAME',
'd.DENTIST_ADDRESS', 'd.DENTIST_PHONE_NUMBER', 'd.DENTIST_EMAIL',
'd.DENTIST_ID', 'O.SHIPMENT_STATUS'
]
area = request.json['area']
connector = SQL(host=server_name, user=server_admin)
condition = " (SHIPMENT_STATUS = 'Not Delivered' or SHIPMENT_STATUS = 'ASSIGNED')and d.DENTIST_ID=o.DENTIST_ID and d.DENTIST_CITY='" + area + "' order by O.SHIPMENT_STATUS"
availableordersnumber = connector.select_query(
table='orders as O, dentist as d ',
columns=['count(distinct O.ORDER_ID)'],
sql_condition=condition)
result = connector.select_query(table='orders as O, dentist as d ',
columns=columns,
sql_condition=condition,
DISTINCTdetector=True)
result = {
'status': result['O.SHIPMENT_STATUS'],
'orderid': result['o.ORDER_ID'],
'ordertotal': result['o.TOTAL_COST'],
'dentistfname': result['d.DENTIST_Fname'],
'dentistlname': result['d.DENTIST_LNAME'],
'no.orders': availableordersnumber['count(distinct O.ORDER_ID)'],
'address': result['d.DENTIST_ADDRESS'],
'phone': result['d.DENTIST_PHONE_NUMBER'],
'email': result['d.DENTIST_EMAIL'],
'DID': result['d.DENTIST_ID']
}
connector.close_connection()
return json.dumps(result)
def ProductsofOrder():
orderid = request.json['orderid']
connector = SQL(host=server_name, user=server_admin)
condition = " op.PRODUCT_ID = p.PRODUCT_ID and op.ORDER_ID = '" + orderid + "'"
columns = ['count(*)']
numberofproducts = connector.select_query(
table='order_product as op, product as p ',
columns=columns,
sql_condition=condition)
columns = [
'op.PRODUCT_ID', 'p.PRODUCT_NAME', 'p.SELLING_PRICE',
'op.NUMBER_OF_UNITS'
]
result = connector.select_query(table='order_product as op, product as p ',
columns=columns,
sql_condition=condition)
result = {
'productid': result['op.PRODUCT_ID'],
'productname': result['p.PRODUCT_NAME'],
'productprice': result['p.SELLING_PRICE'],
'no.units': result['op.NUMBER_OF_UNITS'],
'no.products': numberofproducts['count(*)']
}
connector.close_connection()
return json.dumps(result)
def Store_ManagerChat():
ID = request.json['ID']
connector = SQL(host=server_name, user=server_admin)
condition = " STORE_ID ='" + ID + "'"
Count_Region = connector.select_query(
table='store_branch ',
columns=['count(DISTINCT (MANAGER_ID))'],
sql_condition=condition)
condition = "MANAGER_ID in (select MANAGER_ID from store_branch where STORE_ID ='" + ID + "' and MANAGER_ID )"
columns = ['MANAGER_Fname', 'MANAGER_Lname', 'MANAGER_ID']
result = connector.select_query(table='manager',
columns=columns,
sql_condition=condition)
result = {
'MANAGER_Fname':
result['MANAGER_Fname'],
'MANAGER_ID':
result['MANAGER_ID'],
'MANAGER_Lname':
result['MANAGER_Lname'],
'count(DISTINCT (MANAGER_ID))':
Count_Region['count(DISTINCT (MANAGER_ID))']
}
connector.close_connection()
return json.dumps(result)
def Store_Information():
columns = ['STORE_NAME', 'EMAIL', 'PHONE_NUMBER', 'CREDIT_CARD_NUMBER']
ID = request.json['ID']
connector = SQL(host=server_name, user=server_admin)
condition = " STORE_ID ='" + ID + "' and MANAGER_ID "
Count_Region = connector.select_query(table='store_branch ',
columns=['count(STORE_ID)'],
sql_condition=condition)
Region = connector.select_query(table='store_branch ',
columns=['REGION'],
sql_condition=condition)
condition = " STORE_ID ='" + ID + "' "
result = connector.select_query(table='STORE ',
columns=columns,
sql_condition=condition)
result = {
'STORE_NAME': result['STORE_NAME'],
'EMAIL': result['EMAIL'],
'PHONE_NUMBER': result['PHONE_NUMBER'],
'CREDIT_CARD_NUMBER': result['CREDIT_CARD_NUMBER'],
'Count_Branches': Count_Region['count(STORE_ID)'],
'BRANCHES': Region['REGION']
}
connector.close_connection()
return json.dumps(result)
def Delete_Delivery():
Delivery_ID = request.json['DID']
Conditon = "DELIVERY_ID = '" + str(Delivery_ID) + "'"
connector = SQL(server_name, server_admin, server_password)
connector.delete_query(table='DELIVERY', sql_condition=Conditon)
connector.close_connection()
return "1"
def Avaliable_total_Products():
columns = ['NUMBER_OF_UNITS','SELLING_PRICE','PRICE' ,'IMAGE_URL', 'PRODUCT_NAME','PRODUCT_ID']
connector = SQL(host=server_name, user=server_admin)
Count_Product = connector.select_query(table='product',columns= ['count(distinct PRODUCT_ID)'])
result = connector.select_query(table='product',columns=columns)
result = {'SELLING_PRICE': result['SELLING_PRICE'],'PRICE': result['PRICE'],'PRODUCT_ID': result['PRODUCT_ID'], 'NUMBER_OF_UNITS': result['NUMBER_OF_UNITS'], 'IMAGE_URL': result['IMAGE_URL'], 'PRODUCT_NAME': result['PRODUCT_NAME'], 'Count': Count_Product['count(distinct PRODUCT_ID)']}
connector.close_connection()
return json.dumps(result)
def UpdateDentistImage():
ImageURL = request.json['ImageURL']
DentistID = request.json['DentistID']
Query = f"Update DENTIST SET DENTIST_IMAGE_URL = '{ImageURL}' WHERE DENTIST_ID = {DentistID};"
sql = SQL(host=server_name, user=server_admin)
sql.exectute_query(Query)
sql.close_connection()
return '1'
def Update_Item_table():
columns_dic = request.json['dic']
ID = request.json["ID"]
PRODUCT_NAME =request.json["PRODUCT_NAME"]
condition = "STORE_ID = '" +str(ID) + "' and PRODUCT_NAME = '" +PRODUCT_NAME+"'"
connector = SQL(host=server_name, user=server_admin)
connector.update_query(table='product' ,columns_values_dict= columns_dic,sql_condition=condition)
connector.close_connection()
return "1"
def TotalDeliveredOrders():
deliverid = request.json['DELIVERYID']
condition = "DELIVERY_ID = " + deliverid + " and SHIPMENT_STATUS = 'DELIVERED'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(table='ORDERS',
columns=['count(*)'],
sql_condition=condition)
connector.close_connection()
return json.dumps(result)
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 RemoveFromCart():
sql = SQL(server_name, server_admin)
dentist_id = request.json['DentistID']
product_id = request.json['ProductID']
sql.delete_query(
'DentistCart',
f" DENTIST_ID = {dentist_id} and PRODUCT_ID = {product_id}")
sql.close_connection()
return "1"
def UpdateData():
columns_dic = request.json['dic']
DeliveryID = request.json["MID"]
condition = "DELIVERY_ID = '" + str(DeliveryID) + "'"
connector = SQL(server_name, server_admin, server_password)
connector.update_query(table='DELIVERY',
columns_values_dict=columns_dic,
sql_condition=condition)
connector.close_connection()
return "1"
def Update_Store_table():
columns_dic = request.json['dic']
ID = request.json["ID"]
condition = "STORE_ID = '" + str(ID) + "'"
connector = SQL(host=server_name, user=server_admin)
connector.update_query(table='STORE',
columns_values_dict=columns_dic,
sql_condition=condition)
connector.close_connection()
return "1"
def NoComments():
# Retrive number of comments
product_id = request.json['product_id']
sql = SQL(server_name, server_admin)
result = sql.select_query('DENTIST_COMMENT', ['PRODUCT_ID'], f"PRODUCT_ID = {product_id}")
sql.close_connection()
try:
return str(len(result['PRODUCT_ID']))
except:
return '0'
def UpdateDentistTable():
columns_dic = request.json['dic']
DentistID = request.json["DID"]
condition = "DENTIST_ID = " +str(DentistID)
connector = SQL(server_name,server_admin)
connector.update_query(table='Dentist' ,columns_values_dict= columns_dic,sql_condition=condition)
connector.close_connection()
return "1"
def GetAreaofManager():
email = request.json['Email']
Condition = "MANAGER_EMAIL = '" + email + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(table="MANAGER",
columns=['AREA_OF_MANAGEMENT'],
sql_condition=Condition)
result = {'Area': result['AREA_OF_MANAGEMENT'][0]}
connector.close_connection()
return json.dumps(result)
def Total_Delivered_orders():
Delivery_ID = request.json['DID']
Conditon = "DELIVERY_ID = '" + str(Delivery_ID) + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(table='Delivery',
columns=['NUMBER_OF_DORDERS'],
sql_condition=Conditon)
result = {"noofOrders": result['NUMBER_OF_DORDERS'][0]}
connector.close_connection()
return json.dumps(result)
def Delete_Store():
Store_ID = request.json['SID']
Mid = request.json['MID']
Conditon = "STORE_ID = '" + str(
Store_ID) + "'" + " AND MANAGER_ID = '" + str(Mid) + "'"
print(Conditon)
connector = SQL(server_name, server_admin, server_password)
connector.delete_query(table='STORE_branch', sql_condition=Conditon)
connector.close_connection()
return "1"
def Avaliable_Products():
columns = ['NUMBER_OF_UNITS','SELLING_PRICE','RATE','Brand','PRICE' ,'Category','DESCRIPTION','IMAGE_URL', 'PRODUCT_NAME','PRODUCT_ID']
ID = request.json['ID']
connector = SQL(host=server_name, user=server_admin)
condition = " STORE_ID ='" + ID+ "' "
Count_Product = connector.select_query(table='product ',columns= ['count(distinct PRODUCT_ID)'],sql_condition=condition)
result = connector.select_query(table='product ',columns=columns,sql_condition=condition)
result = {'SELLING_PRICE': result['SELLING_PRICE'],'PRICE': result['PRICE'],'Brand': result['Brand'],'RATE': result['RATE'],'PRODUCT_ID': result['PRODUCT_ID'], 'NUMBER_OF_UNITS': result['NUMBER_OF_UNITS'],'Category': result['Category'],'DESCRIPTION': result['DESCRIPTION'], 'IMAGE_URL': result['IMAGE_URL'], 'PRODUCT_NAME': result['PRODUCT_NAME'], 'Count': Count_Product['count(distinct PRODUCT_ID)']}
connector.close_connection()
return json.dumps(result)
def Remove_Schedule():
DentistID = request.json['DentistID']
ProductID = request.json['ProductID']
duration = request.json['duration']
sql = SQL(server_name, server_admin)
Query = f"DROP EVENT SCEDULED_{DentistID}_{ProductID}_{duration};"
sql.exectute_query(Query)
sql.close_connection()
return '1'
def Get_Store_branches():
Store_ID = request.json['SID']
Conditon = "S.STORE_ID = '" + str(Store_ID) + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table='(store_branch SB JOIN STORE S ON SB.STORE_ID = S.STORE_ID)',
columns=['REGION'],
sql_condition=Conditon)
result = {'branches': result['REGION']}
connector.close_connection()
return json.dumps(result)
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 Update_Manager_table():
columns_dic = request.json['dic']
ManagerID = request.json["MID"]
condition = "MANAGER_ID = '" + str(ManagerID) + "'"
connector = SQL(server_name, server_admin, server_password)
connector.update_query(table='Manager',
columns_values_dict=columns_dic,
sql_condition=condition)
connector.close_connection()
return "1"
def LogIn():
email = request.json['email']
password = request.json['password']
sql = SQL(host=server_name, user=server_admin, password=server_password)
condition = "email = '" + email + "' and Password = '******'"
result = sql.select_query(table='LOGIN_DATA',
columns=['AccountType'],
sql_condition=condition)
sql.close_connection()
if result['AccountType'] == {}:
return 'None'
return result['AccountType'][0]
