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 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 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 FinishDelivering(): deliveryid = request.json['DELIVERYID'] orderid = request.json['ORDERID'] orderprice = request.json['price'] Query = {'O.SHIPMENT_STATUS': 'DELIVERED', 'D.AVAILABLE': 1} condition = "O.ORDER_ID = " + orderid + " and D.delivery_id = " + deliveryid connector = SQL(host=server_name, user=server_admin) connector.exectute_query( "UPDATE ORDERS as O, DELIVERY as D SET O.SHIPMENT_STATUS = 'DELIVERED', D.AVAILABLE = '1' WHERE 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) AddedMoney = int(2 / 100 * float(orderprice)) AddedMoney = int(AddedMoney) Query = "SET @Prev_Credit = (" + str( AddedMoney ) + " + (SELECT CREDIT FROM VIRTUAL_BANK WHERE CARD_NUMBER = (SELECT DELIVERY_CREDIT_CARD_NUMBER FROM DELIVERY WHERE DELIVERY_ID = " + deliveryid + ") ) );\n" + "UPDATE VIRTUAL_BANK SET CREDIT = @Prev_Credit WHERE CARD_NUMBER = (SELECT DELIVERY_CREDIT_CARD_NUMBER FROM DELIVERY WHERE DELIVERY_ID = " + deliveryid + ");" connector.exectute_query(Query) connector.close_connection() return "1"
def ScheduleOrder(): DentistID = request.json['DentistID'] ProductID = request.json['ProductID'] duration = request.json['duration'] NoUnits = request.json['NoUnits'] sql = SQL(server_name, server_admin) Query = f"INSERT INTO SCEDULE_PRODUCT(DENTIST_ID, PRODUCT_ID, DURATION, NoUnits) VALUES ({DentistID}, {ProductID}, {duration}, {NoUnits});" sql.exectute_query(Query) Query = f''' CREATE EVENT SCEDULED_{DentistID}_{ProductID}_{duration} ON SCHEDULE EVERY {duration} DAY DO INSERT INTO ORDERS(DENTIST_ID, TOTAL_COST, SHIPMENT_STATUS) VALUES({DentistID}, (SELECT SELLING_PRICE FROM PRODUCT WHERE PRODUCT_ID = {ProductID}) * {NoUnits} , 'Checking'); ''' sql.exectute_query(Query) sql.close_connection() return '1'
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'
def GetTotalPrice(): sql = SQL(server_name, server_admin) dentist_id = request.json['DentistID'] #Query = f"SELECT SUM(P.SELLING_PRICE * DC.Number_Units) as TOTAL_PRICE FROM PRODUCT as P, DENTISTCART as DC WHERE DC.DENTIST_ID = {dentist_id};" Query = f"SELECT SUM(P.SELLING_PRICE * DC.Number_Units) FROM PRODUCT AS P, DENTISTCART AS DC WHERE DC.PRODUCT_ID = P.PRODUCT_ID AND DC.DENTIST_ID = {dentist_id};" result = sql.exectute_query(Query) total_price = result[0][0] print("total price = " + str(total_price)) Query = f"SELECT VB.CREDIT FROM VIRTUAL_BANK AS VB, DENTIST AS D WHERE VB.CARD_NUMBER = (SELECT DENTIST_CREDIT_CARD_NUMBER FROM DENTIST WHERE DENTIST_ID = {dentist_id});" result = sql.exectute_query(Query) Credit = result[0][0] try: enough_credit = 1 if Credit > total_price else 0 Output = { "total_price": int(total_price), "enough_credit": enough_credit } except: enough_credit = 1 Output = {"total_price": 0, "enough_credit": enough_credit} return json.dumps(Output)
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 Review(): sql = SQL(server_name, server_admin) productid = request.json['product_id'] review = request.json['review'] Query = f"SET @NOREVIEWS = (SELECT NO_OF_REVIEWERS FROM PRODUCT WHERE PRODUCT_ID = {productid});" sql.exectute_query(Query) Query = f"SET @RATING = (SELECT RATE FROM PRODUCT WHERE PRODUCT_ID = {productid});" sql.exectute_query(Query) Query = f"SET @NEW_RATE = (@RATING * @NOREVIEWS + {review}) / (@NOREVIEWS + 1); " sql.exectute_query(Query) Query = f"UPDATE PRODUCT SET RATE = @NEW_RATE, NO_OF_REVIEWERS = @NOREVIEWS + 1 WHERE PRODUCT_ID = {productid};" sql.exectute_query(Query) sql.close_connection() return '1'
def ShipCart(): dentist_id = request.json['DentistID'] sql = SQL(server_name, server_admin) Query = f"SELECT SUM(P.SELLING_PRICE * DC.Number_Units) as TOTAL_PRICE FROM PRODUCT as P, DENTISTCART as DC WHERE DC.DENTIST_ID = {dentist_id};" result = sql.exectute_query(Query) total_price = result[0][0] # Get No. Orders Query = "SELECT COUNT(*) FROM ORDERS;" result = sql.exectute_query(Query) ORDER_ID = result[0][0] + 1 # Create Order Query = f"INSERT INTO ORDERS(ORDER_ID, DENTIST_ID, TOTAL_COST, SHIPMENT_STATUS) VALUES ({ORDER_ID}, {dentist_id}, {total_price}, 'Checking');" sql.exectute_query(Query) Query = f"SELECT VB.CREDIT FROM VIRTUAL_BANK AS VB, DENTIST AS D WHERE VB.CARD_NUMBER = (SELECT DENTIST_CREDIT_CARD_NUMBER FROM DENTIST WHERE DENTIST_ID = {dentist_id});" result = sql.exectute_query(Query) Credit = result[0][0] print(Credit) print("total price = " + str(total_price)) remaining_Credit = int(Credit) - int(total_price) # Get From Credit Query = f"UPDATE VIRTUAL_BANK SET CREDIT = {remaining_Credit} WHERE CARD_NUMBER = (SELECT DENTIST_CREDIT_CARD_NUMBER FROM DENTIST WHERE DENTIST_ID = {dentist_id});" sql.exectute_query(Query) result = sql.select_query('DentistCart', ['PRODUCT_ID', 'Number_Units'], f" DENTIST_ID = {dentist_id}") Products = result['PRODUCT_ID'] Units = result['Number_Units'] for i in range(len(Products)): Query = f"INSERT INTO ORDER_PRODUCT VALUES({Units[i]}, {ORDER_ID}, {Products[i]});" sql.exectute_query(Query) sql.close_connection() return "1"
def ViewCart(): sql = SQL(server_name, server_admin) dentist_id = request.json['DentistID'] Query = f"SELECT COUNT(*) FROM DentistCart WHERE DENTIST_ID = {dentist_id};" result = sql.exectute_query(Query) total_cart = result[0][0] result = sql.select_query('DentistCart', ['PRODUCT_ID', 'Number_Units'], f"DENTIST_ID = {dentist_id}") Output = {} Output['Number_Units'] = result['Number_Units'] Output['ProductID'] = [] Output['ProductName'] = [] Output['Price'] = [] Output['Category'] = [] Output['ImageURL'] = [] Output['Rate'] = [] Output['Description'] = [] Output['Brand'] = [] Output['NoOfReviews'] = [] Output['total_cart'] = int(total_cart) for prod_id in result['PRODUCT_ID']: Prod_Query = sql.select_query('PRODUCT', [ 'PRODUCT_ID', 'PRODUCT_NAME', 'SELLING_PRICE', 'IMAGE_URL', 'RATE', 'DESCRIPTION', 'Brand', 'NO_OF_REVIEWERS', 'CATEGORY' ], f" PRODUCT_ID = {prod_id}") Output['ProductID'].append(Prod_Query['PRODUCT_ID'][0]) Output['ProductName'].append(Prod_Query['PRODUCT_NAME'][0]) Output['Price'].append(Prod_Query['SELLING_PRICE'][0]) Output['Category'].append(Prod_Query['CATEGORY'][0]) Output['ImageURL'].append(Prod_Query['IMAGE_URL'][0]) Output['Rate'].append(Prod_Query['RATE'][0]) Output['Description'].append(Prod_Query['DESCRIPTION'][0]) Output['Brand'].append(Prod_Query['Brand'][0]) Output['NoOfReviews'].append(Prod_Query['NO_OF_REVIEWERS'][0]) sql.close_connection() return json.dumps(Output)
database = "DENTISTA" connection_details = [server_name, server_admin, server_password, database] ''' sql = SQL(server_name, server_admin) Query = "SELECT VB.CREDIT FROM VIRTUAL_BANK AS VB, DENTIST AS D WHERE VB.CARD_NUMBER = (SELECT DENTIST_CREDIT_CARD_NUMBER FROM DENTIST WHERE DENTIST_ID = 1);" Query = "SELECT COUNT(*) FROM ORDERS;" Query = "SELECT * FROM PRODUCT WHERE MATCH (PRODUCT_NAME) AGAINST (LIKE'%cha%' IN NATURAL LANGUAGE MODE);" Query = ''' SET @NOREVIEWS = (SELECT NO_OF_REVIEWERS FROM PRODUCT WHERE PRODUCT_ID = 1); SET @RATING = (SELECT RATE FROM PRODUCT WHERE PRODUCT_ID = 1); SET @NEW_RATE = (@RATING * @NOREVIEWS + 0) / (@NOREVIEWS + 1); UPDATE PRODUCT SET RATE = @NEW_RATE, NO_OF_REVIEWERS = @NOREVIEWS + 1 WHERE PRODUCT_ID = 1; SELECT * FROM PRODUCT; ''' Query = "SELECT * FROM LOGIN_DATA;" result = sql.exectute_query(Query) condition = "email = '" + '*****@*****.**' + "' and Password = '******'y2o9u5s2e0f00' + "'" print("condition " + condition) result = sql.select_query(table='LOGIN_DATA', columns=['AccountType'], sql_condition=condition) print(result) sql.close_connection()
class Validator: def __init__(self, connection_details, table): #Constructor # connection_details: is a python list with the following connection string for the database [host, user, password, database] # table: the name of the table that the validation need to be done to # Notice: ANY additional validation needed for a certain user or action and not added in this API should be added here with some level of abstraction or as inherited class self.table = table self.connector = None # The instance of the SQL API if len( connection_details ) == 2: # Check wether the user entered the password and database name or not [By default password = @dentist1, Database = DENTISTA] self.connector = SQL( host=connection_details[0], user=connection_details[1]) # Use the default arguments elif len( connection_details ) == 4: # add the full arguments to the constructor of the SQL API self.connector = SQL(host=connection_details[0], user=connection_details[1], password=connection_details[2], database=connection_details[3]) def email_validation(self, email_attribute): # email_attribute: is the attribute name of the email in the database table email = request.json['email'] # Getting the email from flutter app condition = email_attribute + " = '" + email + "'" # the condtion of the SQL Query # This is done to check that this email doesn't exsists in the database query_result = self.connector.select_query( table=self.table, columns=[email_attribute], sql_condition=condition) # getting the result of the query if query_result[email_attribute] == [] and validate_email( email ): # Checking for the format of the email, and that it isn't exist in the database return '1' return '0' def Product_validation(self, Product_attribute): # email_attribute: is the attribute name of the email in the database table name = request.json['name'] # Getting the email from flutter app ID = request.json['ID'] # Getting the email from flutter app condition = Product_attribute + " = '" + name + "' and STORE_ID = '" + ID + "'" # the condtion of the SQL Query # This is done to check that this email doesn't exsists in the database query_result = self.connector.select_query( table=self.table, columns=[Product_attribute], sql_condition=condition) # getting the result of the query if query_result[ Product_attribute] != []: # Checking for the format of the email, and that it isn't exist in the database return '0' return '1' def phone_validation(self, phone_attribute): # phone_attribute: is the attribute name of the phone_number in the database table phone = request.json[ 'phone'] # Getting the phone_number from flutter app condition = phone_attribute + " = '" + phone + "'" # the condtion of the SQL Query # This is done to check that this phone doesn't exsists in the database query_result = self.connector.select_query( table=self.table, columns=[phone_attribute], sql_condition=condition) # getting the result of the query self.connector.close_connection() if query_result[ phone_attribute] == []: # Checking that phone_number isn't exist in the database return '1' # this phone is valid return '0' def VehicleLicense_validation(self, license_attribute): # license_attribute: is the attribute name of the VECHILE_LICENCE in the database table license = request.json[ 'license'] # Getting the License from flutter app condition = license_attribute + " = '" + license + "'" # the condtion of the SQL Query # This is done to check that this license doesn't exsists in the database query_result = self.connector.select_query(table=self.table, columns=[license_attribute], sql_condition=condition) if query_result[ license_attribute] == []: # Checking that Vehicle license isn't exist in the database return '1' # this license is valid return '0' def CreditCard_validation(self, CreditCard_attribute): # CreditCard_attribute: is the attribute name of the CreditCard in the database table CardNumber = request.json['CardNumber'] CardEMonth = request.json['CardEMonth'] CardEYear = request.json['CardEYear'] CardCVV = request.json['CardCVV'] Query = "SELECT CARD_NUMBER FROM VIRTUAL_BANK WHERE CARD_NUMBER = '{}' and EXPIRATION_MONTH = {} and EXPIRATION_YEAR = {} and SECURITY_CODE = {} ;".format( CardNumber, CardEMonth, CardEYear, CardCVV) query_result = self.connector.exectute_query(Query) self.connector.close_connection() results = [] if query_result == None: return '0' for row in query_result: results.append(row[0]) if results == []: return '0' # this data doesn't exsists return '1'