def FetchProducts():
index = request.json['index']
sql = SQL(server_name, server_admin)
Prod_Query = sql.select_general('PRODUCT', ['PRODUCT_ID', 'PRODUCT_NAME', 'SELLING_PRICE', 'IMAGE_URL', 'RATE', 'DESCRIPTION', 'Brand', 'NO_OF_REVIEWERS', 'CATEGORY'], f" Limit {index}, 1")
Output = {}
Output['ProductID'] = Prod_Query['PRODUCT_ID'][0]
Output['ProductName'] = Prod_Query['PRODUCT_NAME'][0]
Output['Price'] = Prod_Query['SELLING_PRICE'][0]
Output['Category'] = Prod_Query['CATEGORY'][0]
Output['ImageURL'] = Prod_Query['IMAGE_URL'][0]
Output['Rate'] = Prod_Query['RATE'][0]
Output['Description'] = Prod_Query['DESCRIPTION'][0]
Output['Brand'] = Prod_Query['Brand'][0]
Output['NoOfReviews'] = Prod_Query['NO_OF_REVIEWERS'][0]
# Offer is remaining
offer_query = sql.select_query('OFFERS', ['DISCOUNT'], f"PRODUCT_ID = {Output['ProductID']}")
try:
Output["discount"] = offer_query['DISCOUNT'][0]
except:
Output["discount"] = 0
print(Output)
return json.dumps(Output)
def DeliveryStatus():
deliveryemail = request.json['email']
condition = "DELIVERY_EMAIL = '" + deliveryemail + "'"
connector = SQL(host=server_name,
user=server_admin,
password=server_password)
#if manager ID has value -> Delivery is accepted
IDS = connector.select_query(table='DELIVERY',
columns=['MANAGER_ID', 'DELIVERY_ID'],
sql_condition=condition)
if IDS['MANAGER_ID'] != [None]:
connector.close_connection()
return "Accepted"
#else there are 2 state waiting or rejected
else:
condition = "DELIVERY_ID = " + str(IDS['DELIVERY_ID'][0])
#if delivery verification id has value(existed) means delivery in pending
verificationid = connector.select_query(
table='delivery_verification',
columns=['DELIVERY_VERIFICATION_ID'],
sql_condition=condition)
if verificationid['DELIVERY_VERIFICATION_ID'] != []:
connector.close_connection()
return "Pending"
#else delivery is rejected
else:
connector.close_connection()
return "Rejected"
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 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 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 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 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 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 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 NoProducts():
prod = request.json['product']
sql = SQL(server_name, server_admin)
result = sql.select_query("PRODUCT", ['PRODUCT_ID'])
print(result)
try:
noprod = result['PRODUCT_ID']
print(noprod)
return str(len(noprod))
except:
return '0'
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]
def Get_All_Stores():
ManagerArea = request.json['MArea']
ManagerId = request.json['MID']
Condition = "REGION = '" + ManagerArea + "'" + "AND MANAGER_ID = '" + str(
ManagerId) + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table='(STORE S JOIN store_branch SB ON SB.STORE_ID = S.STORE_ID)',
columns=['S.STORE_ID', 'STORE_NAME'],
sql_condition=Condition)
result = {'SID': result['S.STORE_ID'], 'SNAME': result['STORE_NAME']}
connector.close_connection()
return json.dumps(result)
def GetManager():
DeliveryArea = request.json['area']
Condition = "AREA_OF_MANAGEMENT = '" + DeliveryArea + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table='Manager',
columns=['Manager_ID', 'Manager_Fname', 'Manager_Lname'],
sql_condition=Condition)
result = {
'MID': result['Manager_ID'],
'MFname': result['Manager_Fname'],
'MLname': result['Manager_Lname']
}
connector.close_connection()
return json.dumps(result)
def Get_Products_Count():
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='(PRODUCT P JOIN STORE S ON S.STORE_ID= P.STORE_ID)',
columns=['PRODUCT_ID'],
sql_condition=Conditon)
total_products = 0
try:
total_products = len(result['PRODUCT_ID'])
except:
pass
result = {'Products_Count': total_products}
connector.close_connection()
return json.dumps(result)
def email_validate():
email = request.json['email']
connector = SQL(host=server_name,
user=server_admin,
password=server_password)
condition = "DENTIST_EMAIL = '" + email + "'"
output = connector.select_query(table='DENTIST',
columns=['DENTIST_ID'],
sql_condition=condition)
connector.close_connection()
valid_state = '0'
if output['DENTIST_ID'] == [] and validate_email(email):
valid_state = '1'
return valid_state
def sql_testcase_1():
# This is a valid testcase of Insertion
connector = SQL(host=server_name, user=server_admin)
attributes = [
'DENTIST_ID', 'DENTIST_Fname', 'DENTIST_LNAME', 'DENTIST_EMAIL',
'DENTIST_PASSWORD', 'DENTIST_PHONE_NUMBER', 'DENTIST_ADDRESS',
'DENTIST_ZIP_CODE', 'DENTIST_REGION', 'DENTIST_CITY',
'DENTIST_CREDIT_CARD_NUMBER', 'DENTIST_IMAGE_URL'
]
values = [
2, 'Mohammed', 'Ahmed', '*****@*****.**', 'y2o2u5s2e0f00',
'01555266212', 'dar el salam', 123456, 'Maadi', 'Cairo',
'4152-2222-3333-1111', 'IMAGE'
]
connector.insert_query(table='DENTIST',
attributes=attributes,
values=values)
out = connector.select_query(
table='DENTIST',
columns=['DENTIST_Fname', 'DENTIST_LNAME'],
sql_condition=" DENTIST_EMAIL = '*****@*****.**'")
print(out)
connector.close_connection()
def Get_Request_Info_Store():
Store_ID = request.json['SID']
Condition = "STORE_ID = '" + str(Store_ID) + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table='STORE',
columns=['STORE_NAME', 'EMAIL', 'PHONE_NUMBER', 'CREDIT_CARD_NUMBER'],
sql_condition=Condition)
result = {
'Sname': result['STORE_NAME'][0],
'Email': result['EMAIL'][0],
'CCN': result['CREDIT_CARD_NUMBER'][0],
'Phone': result['PHONE_NUMBER'][0]
}
connector.close_connection()
return json.dumps(result)
def Get_All_Delivery():
ManagerArea = request.json['MArea']
ManagerId = request.json['MID']
Condition = "AREA = '" + ManagerArea + "'" + "AND MANAGER_ID = '" + str(
ManagerId) + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table='DELIVERY',
columns=['DELIVERY_ID', 'DELIVERY_Fname', 'DELIVERY_Lname'],
sql_condition=Condition)
result = {
'DID': result['DELIVERY_ID'],
'DFname': result['DELIVERY_Fname'],
'DLname': result['DELIVERY_Lname']
}
connector.close_connection()
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 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 phone_validate():
phone = request.json['phone']
connector = SQL(host=server_name,
user=server_admin,
password=server_password)
condition = "DENTIST_PHONE_NUMBER = '" + phone + "'"
output = connector.select_query(table='DENTIST',
columns=['DENTIST_ID'],
sql_condition=condition)
connector.close_connection()
valid_state = '0'
if output['DENTIST_ID'] == []:
valid_state = '1'
return valid_state
def ViewLikes():
comment_id = request.json['comment_id']
sql = SQL(server_name, server_admin)
dentist_result = sql.select_query('DENTIST_LIKES', ['DENTIST_ID'], f"COMMENT_ID = {comment_id}")
dentist_ids = dentist_result['DENTIST_ID']
Dentist_Names = []
Dentist_Images = []
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])
Outputs = {}
Outputs['Names'] = Dentist_Names
Outputs['Images'] = Dentist_Images
return json.dumps(Outputs)
def testcase3():
connector = SQL(host=server_name, user=server_admin)
out = connector.select_query(
table='DENTIST',
columns='*',
sql_condition="DENTIST_EMAIL = '*****@*****.**'")
print(out)
connector.close_connection()
return out
def Get_Pending_Requests_Stores():
ManagerArea = request.json['MArea']
Condition = "REGION LIKE '" + ManagerArea + "'"
print(Condition)
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table=
'(STORE S JOIN store_branch SB ON S.STORE_ID = SB.STORE_ID JOIN store_verification SV ON SV.Store_branch_id = SB.Store_branch_id)',
columns=['STORE_NAME', 'S.STORE_ID', 'SV.STORE_BRANCH_ID'],
sql_condition=Condition)
print(result)
result = {
'SID': result['S.STORE_ID'],
'Sname': result['STORE_NAME'],
'BID': result['SV.STORE_BRANCH_ID']
}
connector.close_connection()
return json.dumps(result)
def bank_testcase():
connector = SQL(host=server_name, user=server_admin)
df = pd.read_csv('males_en.csv')
Names = np.array(df['Name'])
Year = [2020, 2021, 2022, 2023, 2024]
Month = ["May", "April", "June", "July"]
Nums = [1, 2, 3, 4, 5, 6, 7, 8, 9]
for j in range(20):
fname = np.random.choice(Names, 1)[0]
lname = np.random.choice(Names, 1)[0]
email = fname + "." + lname + "@gmail.com"
PassWord = fname + lname + '1'
year = np.random.choice(Year, 1)[0]
month = np.random.choice(Month, 1)[0]
Card_Num = ""
for i in range(4):
n = np.random.choice(Nums, 1)
Card_Num = Card_Num + str(n[0])
Card_Num = Card_Num + "-"
for i in range(4):
n = np.random.choice(Nums, 1)
Card_Num = Card_Num + str(n[0])
Card_Num = Card_Num + "-"
for i in range(4):
n = np.random.choice(Nums, 1)
Card_Num = Card_Num + str(n[0])
Card_Num = Card_Num + "-"
for i in range(4):
n = np.random.choice(Nums, 1)
Card_Num = Card_Num + str(n[0])
NID = ""
for i in range(14):
n = np.random.choice(Nums, 1)
NID = NID + str(n[0])
SCode = ""
for i in range(3):
n = np.random.choice(Nums, 1)
SCode = SCode + str(n[0])
CREDIT = 0
attributes = [
'CARD_NUMBER', 'FIRST_NAME', 'LAST_NAME', 'EMAIL', 'PASSWORD',
'NATIONAL_ID', 'SECURITY_CODE', 'EXPIRATION_MONTH',
'EXPIRATION_YEAR', 'CREDIT'
]
values = [
Card_Num, fname, lname, email, PassWord, NID, SCode, month, year,
CREDIT
]
connector.insert_query(table='VIRTUAL_BANK',
attributes=attributes,
values=values)
out = connector.select_query(table='VIRTUAL_BANK', columns='*')
print(out)
connector.close_connection()
def SearchProduct():
search_context = request.json['SearchContext']
sql = SQL(server_name, server_admin)
columns = ['PRODUCT_ID', 'PRODUCT_NAME', 'SELLING_PRICE', 'IMAGE_URL', 'RATE', 'DESCRIPTION', 'Brand', 'NO_OF_REVIEWERS', 'CATEGORY']
condition = f" MATCH (PRODUCT_NAME) AGAINST ('{search_context}' IN NATURAL LANGUAGE MODE);"
Prod_Query = sql.select_query('PRODUCT', columns, condition)
Output = {}
Output['ProductID'] = Prod_Query['PRODUCT_ID']
Output['ProductName'] = Prod_Query['PRODUCT_NAME']
Output['Price'] = Prod_Query['SELLING_PRICE']
Output['Category'] = Prod_Query['CATEGORY']
Output['ImageURL'] = Prod_Query['IMAGE_URL']
Output['Rate'] = Prod_Query['RATE']
Output['Description'] = Prod_Query['DESCRIPTION']
Output['Brand'] = Prod_Query['Brand']
Output['NoOfReviews'] = Prod_Query['NO_OF_REVIEWERS']
Output["discount"] = 0
return json.dumps(Output)
def Get_Pending_Requests_Del():
ManagerArea = request.json['MArea']
Condition = "AREA LIKE '" + ManagerArea + "'"
connector = SQL(server_name, server_admin, server_password)
result = connector.select_query(
table=
'(delivery D JOIN delivery_verification DF ON DF.DELIVERY_ID = D.DELIVERY_ID)',
columns=[
'DISTINCT( D.DELIVERY_ID)', 'DELIVERY_Fname', 'DELIVERY_Lname'
],
sql_condition=Condition)
result = {
'DID': result['DISTINCT( D.DELIVERY_ID)'],
'fname': result['DELIVERY_Fname'],
'lname': result['DELIVERY_Lname']
}
print(result)
connector.close_connection()
return json.dumps(result)
def GetDentist():
email = request.json['email']
sql = SQL(host=server_name, user=server_admin)
results = sql.select_query('DENTIST', ['DENTIST_ID', 'DENTIST_Fname', 'DENTIST_LNAME', 'DENTIST_PASSWORD', 'DENTIST_PHONE_NUMBER', 'DENTIST_ADDRESS', 'DENTIST_ZIP_CODE', 'DENTIST_REGION', 'DENTIST_CITY', 'DENTIST_CREDIT_CARD_NUMBER', 'DENTIST_IMAGE_URL'], f"DENTIST_EMAIL = '{email}'")
Outputs = {}
Outputs['DentistID'] = results['DENTIST_ID'][0]
Outputs['DentistFname'] = results['DENTIST_Fname'][0]
Outputs['DentistLname'] = results['DENTIST_LNAME'][0]
Outputs['DentistAddress'] = results['DENTIST_ADDRESS'][0]
Outputs['DentistCity'] = results['DENTIST_CITY'][0]
Outputs['DentistRegion'] = results['DENTIST_REGION'][0]
Outputs['DentistImageURL'] = results['DENTIST_IMAGE_URL'][0]
Outputs['DentistCreditCardNumber'] = results['DENTIST_CREDIT_CARD_NUMBER'][0]
Outputs['DentistPassword'] = results['DENTIST_PASSWORD'][0]
Outputs['DentistPhoneNumber'] = results['DENTIST_PHONE_NUMBER'][0]
Outputs['DentistZipCode'] = results['DENTIST_ZIP_CODE'][0]
sql.close_connection()
return json.dumps(Outputs)
def RetriveSchedule():
DentistID = request.json['DentistID']
condition = f" PRODUCT_ID = (SELECT PRODUCT_ID FROM SCEDULE_PRODUCT WHERE DENTIST_ID = {DentistID})"
columns = ['PRODUCT_ID', 'PRODUCT_NAME', 'SELLING_PRICE', 'IMAGE_URL', 'RATE', 'DESCRIPTION', 'Brand', 'NO_OF_REVIEWERS', 'CATEGORY']
sql = SQL(server_name, server_admin)
Prod_Query = sql.select_query('PRODUCT', columns, condition)
Output = {}
Output['ProductID'] = Prod_Query['PRODUCT_ID']
Output['ProductName'] = Prod_Query['PRODUCT_NAME']
Output['Price'] = Prod_Query['SELLING_PRICE']
Output['Category'] = Prod_Query['CATEGORY']
Output['ImageURL'] = Prod_Query['IMAGE_URL']
Output['Rate'] = Prod_Query['RATE']
Output['Description'] = Prod_Query['DESCRIPTION']
Output['Brand'] = Prod_Query['Brand']
Output['NoOfReviews'] = Prod_Query['NO_OF_REVIEWERS']
Output["discount"] = 0
return json.dumps(Output)
def LikeComment():
# Add the like on a certian 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]
comment_id = request.json['comment_id']
# First increment likes of the comment by 1
comments_result = sql.select_query('DENTIST_COMMENT', ['LIKES'], f" COMMENT_ID = {comment_id}")
num_comments = comments_result['LIKES'][0] + 1
sql.exectute_query(f"UPDATE DENTIST_COMMENT SET LIKES = {num_comments};")
# Second add the like to the relationship
sql.exectute_query(f"INSERT INTO DENTIST_LIKES(DENTIST_ID, COMMENT_ID) VALUES ({dentist_id, comment_id});")
sql.close_connection()
return '1'
