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'
