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]