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'