def get_all_customer_rating(event):
data = json.loads(event['body']) if event.get('body', 0) else event
if data.get('restaurant_rating_id'):
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT customer_customer.first_name, customer_customer.last_name, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| customer_customer.customer_profile_pic AS customer_profile_pic, restaurant_restaurantratings.customer_id, restaurant_restaurantratings.restaurant_id, restaurant_restaurantratings.customer_rating, to_char(restaurant_restaurantratings.created_at, 'MON-DD-YYYY HH12:MIPM') AS created_at, restaurant_restaurantratings.customer_comment from restaurant_restaurantratings INNER JOIN customer_customer ON restaurant_restaurantratings.customer_id = customer_customer.id where restaurant_restaurantratings.restaurant_id={}".format(
data['restaurant_rating_id']))
row = curr.fetchall()
if row:
return row
else:
return []
elif data.get('coffee_house_rating_id'):
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT customer_customer.first_name, customer_customer.last_name, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| customer_customer.customer_profile_pic AS customer_profile_pic, coffeehouse_coffeehouseratings.customer_id, coffeehouse_coffeehouseratings.coffee_house_id, coffeehouse_coffeehouseratings.customer_rating, to_char(coffeehouse_coffeehouseratings.created_at, 'MON-DD-YYYY HH12:MIPM') AS created_at, coffeehouse_coffeehouseratings.customer_comment from coffeehouse_coffeehouseratings INNER JOIN customer_customer ON coffeehouse_coffeehouseratings.customer_id = customer_customer.id where coffeehouse_coffeehouseratings.coffee_house_id={}".format(
data['coffee_house_rating_id']))
row = curr.fetchall()
if row:
return row
else:
return []
def profile_picture(event):
s3 = boto3.client("s3")
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
conn.autocommit = True
sql = """ UPDATE waiter_waiter
SET profile_picture = %s
WHERE id = %s"""
conn = None
updated_rows = 0
try:
name = data['profile_picture']
customer_id = data['id']
image_id = str(id(customer_id))
image_name = str(image_id + '_waiter_id_{}.png'.format(customer_id))
decode_content = base64.b64decode(name)
s3.put_object(Bucket='mapplate-public',
Key='media/waiter_profile_picture/' + image_name,
ContentType="image/png",
Body=decode_content)
s3_bucket_url = 'waiter_profile_picture/' + image_name
conn = get_db()
cur = conn.cursor()
cur.execute(sql, (s3_bucket_url, data['id']))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def coffee_offer(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT id, end_date_time, start_date_time from cms_coffeeoffer")
obj = curr.fetchall()
datetimeFormat = '%Y-%m-%d %H:%M:%S.%f'
current_time = str(datetime.datetime.now())
coffee_offer_list = []
for date_time_from_db in obj:
end_time_db = str(date_time_from_db['end_date_time'])
end_times = end_time_db[:-6] + '.0'
time_diff = datetime.datetime.strptime(
end_times, datetimeFormat) - datetime.datetime.strptime(
current_time, datetimeFormat)
diff = time_diff.days
if diff >= 0:
curr = conn.cursor()
curr.execute(
"SELECT cms_coffeeoffer.id, coffeehouse_coffee.coffee_name, coffeehouse_coffee.coffee_price, cms_coffeeoffer.discount_price, cms_coffeeoffer.coffee_offer, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| cms_coffeeoffer.slider_image AS slider_image, cms_coffeeoffer.coffee_id, cms_coffeeoffer.coffee_house_id, cms_coffeeoffer.after_discount_price from cms_coffeeoffer INNER JOIN coffeehouse_coffee ON cms_coffeeoffer.coffee_id = coffeehouse_coffee.id where cms_coffeeoffer.id= {} "
.format(date_time_from_db['id']))
coffee_offer_list.append(curr.fetchone())
# from food_food INNER JOIN food_variant ON food_food.id = food_variant.food_id where food_food.restaurant_id= {}".format(data['restaurant_id']))
else:
print("Slider not found")
return coffee_offer_list
def update_profile_info(event):
data = json.loads(event['body']) if event.get('body', 0) else event
sql = """ UPDATE waiter_waiter
SET first_name = %s, last_name = %s, date_of_birth = %s, address_line_1 = %s, address_line_2 = %s, mobile = %s, city = %s, state = %s, country = %s, email = %s, zip_code = %s
WHERE id = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
cur = conn.cursor()
cur.execute(
sql, (data['first_name'], data['last_name'], data['to_char'],
data['address_line_1'], data['address_line_2'],
data['mobile'], data['city'], data['state'], data['country'],
data['email'], data['zip_code'], data['id']))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def insert_data(event):
conn = get_db()
data = json.loads(event['body']) if event.get('body', 0) else event
password = data['password']
password_hash = hashlib.sha256(password.encode()).hexdigest()
curr = conn.cursor()
curr.execute(
"SELECT username from customer_customer where email= '{}'".format(
data['email']))
row = curr.fetchone()
if data['email'] != row:
cur = conn.cursor()
cur.execute(
"insert into customer_customer(first_name ,last_name, date_of_birth, address_line_1, address_line_2, mobile, city, state ,country ,zip_code ,signup_location, signup_ip, email, password, username, geolocation_long, geolocation_lat) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(data['first_name'], data['last_name'], data['date_of_birth'],
data['address_line_1'], data['address_line_2'], data['mobile'],
data['city'], data['state'], data['country'], data['zip_code'],
data['signup_location'], data['signup_ip'], data['email'],
password_hash, data['username'], data['geolocation_long'],
data['geolocation_lat']))
conn.commit()
cur.close()
else:
return 'This email address already exists'
def get_restaurants_rating(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
cur = conn.cursor()
if data.get('restaurant_id') or data.get('restaurant_rating_id'):
if data.get('restaurant_id'):
restaurant_id = data.get('restaurant_id')
elif data.get('restaurant_rating_id'):
restaurant_id = data.get('restaurant_rating_id')
cur.execute(
"SELECT customer_rating from restaurant_restaurantratings where restaurant_id= {}".format(restaurant_id))
row = cur.fetchall()
items_len = len(row)
result = dict(functools.reduce(operator.add, map(collections.Counter, row)))
rating = result['customer_rating'] / items_len
update_calculate_rating(event, rating, items_len)
return rating
elif data.get('coffee_house_id') or data.get('coffee_house_rating_id'):
if data.get('coffee_house_id'):
coffee_house_id = data.get('coffee_house_id')
else:
coffee_house_id = data.get('coffee_house_rating_id')
cur.execute("SELECT customer_rating from coffeehouse_coffeehouseratings where coffee_house_id= {}".format(
coffee_house_id))
row = cur.fetchall()
items_len = len(row)
result = dict(functools.reduce(operator.add, map(collections.Counter, row)))
rating = result['customer_rating'] / items_len
update_calculate_rating(event, rating, items_len)
return rating
def update_vendor(event):
data = json.loads(event['body']) if event.get('body', 0) else event
sql = """ UPDATE customer_customer
SET first_name = %s, last_name = %s, date_of_birth = %s, address_line_1 = %s, address_line_2 = %s, mobile = %s, city = %s, state = %s, country = %s, zip_code = %s, signup_ip = %s, geoLocation_long = %s, geoLocation_lat = %s
WHERE id = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
# create a new cursor
cur = conn.cursor()
# execute the UPDATE statement
cur.execute(
sql,
(data['first_name'], data['last_name'], data['date_of_birth'],
data['address_line_1'], data['address_line_2'], data['mobile'],
data['city'], data['state'], data['country'], data['zip_code'],
data['signup_ip'], data['geoLocation_long'],
data['geoLocation_lat'], data['id']))
# get the number of updated rows
updated_rows = cur.rowcount
# Commit the changes to the database
conn.commit()
# Close communication with the PostgreSQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def latlong(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT latitude,longitude, status from restaurant_restaurants ")
row = curr.fetchall()
if len(row) >= 2:
restaurantsDataList = row
customer = {
'latitude': data['latitude'],
'longitude': data['longitude']
}
result = closest(restaurantsDataList, customer)
response = result[:10]
rs = []
for items in response:
restaurants_lat_long = (items['latitude'], items['longitude']
) # (lat, lon)
customers_lat_logn = (data['latitude'], data['longitude'])
distance_li = haversine(restaurants_lat_long, customers_lat_logn)
if distance_li <= 5:
# if items.get('status') == "Open":
rs.append(items['latitude'])
find_lat = tuple(rs)
return find_lat
else:
return []
def address_update(event):
data = json.loads(event['body']) if event.get('body', 0) else event
sql = """ UPDATE order_order
SET delivery_address = %s, postcode = %s, city = %s
WHERE id = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
# create a new cursor
cur = conn.cursor()
# execute the UPDATE statement
cur.execute(sql, (data['update_delivery_address'], data['postcode'],
data['city'], data['order_id_num']))
# get the number of updated rows
updated_rows = cur.rowcount
# Commit the changes to the database
conn.commit()
# Close communication with the PostgreSQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def get_coffee_list(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
coffee_object = []
curr = conn.cursor()
curr.execute(
"SELECT id, coffee_name, coffee_availability, coffee_price, discount_type, discount_price, after_discount_price, type_of_coffee, coffee_review, coffee_components, quantity,'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_picture AS coffee_picture, coffee_offer, coffee_details, coffee_active from coffeehouse_coffee where coffee_house_id= {}"
.format(data['coffee_house_id']))
coffee_list = curr.fetchall()
if coffee_list:
for coffee_item in coffee_list:
coffee_availability = int(coffee_item['coffee_availability'])
if coffee_list and coffee_availability == 1:
# for coffee in coffee_list:
# Get coffee id
coffee_id = coffee_item['id']
# get food addon list
curr.execute(
"SELECT id, coffee_addon_name, coffee_addon_price from coffeehouse_coffeeaddons where coffee_id = {}"
.format(coffee_id))
coffee_addon_item = curr.fetchall()
coffee_item['coffee_addon'] = coffee_addon_item
# append coffee item
coffee_object.append(coffee_item)
return coffee_object
def table_qr(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT table_name,number_of_seat,about_table,id, restaurant_id, coffee_house_id from waiter_diningtable where qr_code= '{}'"
.format(data['qr_code']))
obj = curr.fetchall()
if obj:
for property_list in obj:
restaurant_id = property_list['restaurant_id']
coffee_house_id = property_list['coffee_house_id']
if restaurant_id:
curr.execute(
"SELECT restaurant_restaurants.restaurant_name, restaurant_restaurants.restaurant_phone, restaurant_restaurants.restaurant_about, restaurant_restaurants.restaurant_address_line_1, restaurant_restaurants.restaurant_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_restaurants.restaurant_profile_picture AS restaurant_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_restaurants.restaurant_thumbnail AS restaurant_thumbnail , restaurant_restaurants.country, restaurant_restaurants.zip_code,restaurant_restaurants.city, restaurant_restaurants.state, restaurant_restaurants.latitude, restaurant_restaurants.longitude, restaurant_restaurants.restaurant_old_new_status, restaurant_restaurants.type_of_restaurant, restaurant_restaurants.food_prepare_avg_time, restaurant_restaurants.special_foods, restaurant_restaurants.food_price_icon, restaurant_restaurants.restaurants_rating, restaurant_restaurants.total_customer_review, restaurant_restaurants.pickup_delivery_status, restaurant_restaurants.free_delivery, restaurant_restaurants.offer, restaurant_restaurants.status, restaurant_restaurants.restaurant_active, restaurant_restaurants.restaurant_email, waiter_diningtable.id, waiter_diningtable.table_name, waiter_diningtable.number_of_seat, waiter_diningtable.about_table, waiter_diningtable.coffee_house_id, waiter_diningtable.qr_code, waiter_diningtable.table_icon, waiter_diningtable.table_status, waiter_diningtable.is_active, waiter_diningtable.restaurant_id from waiter_diningtable INNER JOIN restaurant_restaurants ON waiter_diningtable.restaurant_id = restaurant_restaurants.id where waiter_diningtable.restaurant_id = {} "
.format(restaurant_id))
obj = curr.fetchone()
return obj
elif coffee_house_id:
curr.execute(
"SELECT coffeehouse_coffeehouse.coffee_house_name, coffeehouse_coffeehouse.coffee_house_email,coffeehouse_coffeehouse.coffee_house_phone, coffeehouse_coffeehouse.coffee_house_about, coffeehouse_coffeehouse.coffee_house_address_line_1, coffeehouse_coffeehouse.coffee_house_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffeehouse_coffeehouse.coffee_house_profile_picture AS coffee_house_profile_picture,'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffeehouse_coffeehouse.coffee_house_thumbnail AS coffee_house_thumbnail, coffeehouse_coffeehouse.coffee_house_website, coffeehouse_coffeehouse.coffee_house_country, coffeehouse_coffeehouse.coffee_house_zip_code, coffeehouse_coffeehouse.coffee_house_city, coffeehouse_coffeehouse.coffee_house_state, coffeehouse_coffeehouse.latitude, coffeehouse_coffeehouse.longitude,coffeehouse_coffeehouse.coffee_house_old_new_status, coffeehouse_coffeehouse.type_of_coffee, coffeehouse_coffeehouse.coffee_prepare_avg_time, coffeehouse_coffeehouse.special_coffee, coffeehouse_coffeehouse.coffee_house_price_icon, coffeehouse_coffeehouse.coffee_house_rating, coffeehouse_coffeehouse.total_customer_review, coffeehouse_coffeehouse.coffee_house_offer, coffeehouse_coffeehouse.coffee_house_status, coffeehouse_coffeehouse.coffee_house_active, waiter_diningtable.id, waiter_diningtable.table_name, waiter_diningtable.number_of_seat, waiter_diningtable.about_table, waiter_diningtable.qr_code, waiter_diningtable.table_icon, waiter_diningtable.table_status, waiter_diningtable.is_active, waiter_diningtable.coffee_house_id, waiter_diningtable.restaurant_id from waiter_diningtable INNER JOIN coffeehouse_coffeehouse ON waiter_diningtable.coffee_house_id = coffeehouse_coffeehouse.id where waiter_diningtable.coffee_house_id= {} "
.format(coffee_house_id))
obj = curr.fetchone()
return obj
else:
return []
def update_retaurant_review(event):
data = json.loads(event['body']) if event.get('body', 0) else event
if data.get('restaurant_id'):
sql = """ UPDATE restaurant_restaurantratings
SET restaurant_id = %s, customer_rating = %s, customer_comment = %s
WHERE customer_id = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
cur = conn.cursor()
rep = '5'
cur.execute(sql,
(data['restaurant_id'], data['customer_rating'], data['customer_comment'], data['customer_id']))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
elif data.get('coffee_house_id'):
sql = """ UPDATE coffeehouse_coffeehouseratings
SET coffee_house_id = %s, customer_rating = %s, customer_comment = %s
WHERE customer_id = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
cur = conn.cursor()
rep = '5'
cur.execute(sql,
(data['coffee_house_id'], data['customer_rating'], data['customer_comment'],
data['customer_id']))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def get_data():
conn = get_db()
curr = conn.cursor()
curr.execute("SELECT latitude, longitude from restaurant_restaurants")
# curr.execute("select * from (SELECT *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( latitude ) ) ) ) AS distance FROM restaurant_restaurants) al where distance < 5 ORDER BY distance LIMIT 1100;")
row = curr.fetchall()
return row
def lambda_handler(event, context):
client = boto3.client('cognito-idp')
data = json.loads(event['body']) if event.get('body', 0) else event
# elif(re.search(regex,data['username'])):
if data['username']:
conn = get_db()
cur = conn.cursor()
cur.execute(
"SELECT email FROM customer_customer WHERE email LIKE '{}'".format(
data['username']))
row = cur.fetchone()
cur.close()
if row is None:
return {"status": "success", "message": "Email is not valid"}
try:
username = event['username']
response = client.forgot_password(
ClientId=CLIENT_ID,
SecretHash=get_secret_hash(username),
Username=username,
)
except client.exceptions.UserNotFoundException:
return {"status": "error", "message": "Username doesnt exists"}
except client.exceptions.InvalidParameterException:
return {
"status": "error",
"message": f"User <{username}> is not confirmed yet"
}
except client.exceptions.CodeMismatchException:
return {"status": "error", "message": "Invalid Verification code"}
except client.exceptions.NotAuthorizedException:
return {"status": "error", "message": "User is already confirmed"}
except client.exceptions.LimitExceededException:
return {"status": "error", "message": "Limit Exceeded Exception"}
except Exception as e:
return {
"status": "error",
"message": f"Uknown error {e.__str__()} "
}
return {
"status": "success",
"message":
f"Please check your Registered email id for validation code"
}
else:
return {"status": "error"}
def get_foodaddons(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT id,food_name_id, addon_name, price from food_foodaddons where food_name_id= {} ORDER BY id ASC".format(
data['food_name_id']))
row = curr.fetchall()
return row
def get_rating(event):
data = json.loads(event['body']) if event.get('body', 0) else event
if data.get('restaurant_id'):
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT customer_id, restaurant_id, customer_rating, customer_comment from restaurant_restaurantratings where customer_id={}".format(
data['customer_id']))
row = curr.fetchone()
return row
elif data.get('coffee_house_id'):
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT customer_id, coffee_house_id, customer_rating, customer_comment from coffeehouse_coffeehouseratings where customer_id={}".format(
data['customer_id']))
row = curr.fetchone()
return row
def get_coffee_addons(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
try:
curr = conn.cursor()
curr.execute(
"SELECT id, coffee_addon_name, coffee_addon_price, coffee_id from coffeehouse_coffeeaddons where coffee_id= {} ORDER BY id ASC"
.format(data['coffee_id']))
coffee_addon_list = curr.fetchall()
return coffee_addon_list
except Exception as e:
return e
def get_data(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT id,username,first_name,last_name, date_of_birth, address_line_1,address_line_2,mobile,city,state,country,zip_code, notification_token, signup_ip,geolocation_long,geolocation_lat, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| customer_profile_pic AS customer_profile_pic from customer_customer where email= '{}'"
.format(data['username']))
row = curr.fetchone()
# datai = []
# while row is not None:
# row = curr.fetchone()
# datai.append(row)
return row
def get_data(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute(
"SELECT username from customer_customer where email= '{}'".format(
data['email']))
row = curr.fetchone()
# datai = []
# while row is not None:
# row = curr.fetchone()
# datai.append(row)
return row
def get_food_offer(event):
data = json.loads(event['body']) if event.get('body', 0) else event
lat_long = latlong(event)
coffee_house_lat_long = coffee_house_latlong(event)
conn = get_db()
curr = conn.cursor()
if latlong:
curr.execute(
"SELECT id, restaurant_name, restaurant_email, restaurant_phone, restaurant_about, restaurant_address_line_1, restaurant_address_line_2,'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_profile_picture AS restaurant_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_thumbnail AS restaurant_thumbnail, country, zip_code, city, state, restaurant_linkedin, restaurant_website, restaurant_facebook, restaurant_instagram, restaurant_twitter, latitude, longitude, restaurant_old_new_status, type_of_restaurant, food_prepare_avg_time, special_foods, food_price_icon, restaurants_rating, total_customer_review, pickup_delivery_status, free_delivery, offer, status, restaurant_active from restaurant_restaurants where latitude IN {} ORDER BY id ASC"
.format(lat_long))
restaurants_id = curr.fetchall()
offer_list = []
for rest in restaurants_id:
curr = conn.cursor()
curr.execute(
"SELECT id, food_name, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| food_picture AS food_picture, food_price, discount_type, discount_price, after_discount_price, food_stock, food_availability, food_active, type_of_food, food_components, food_review, food_offer, food_details, category_id, food_time_id, restaurant_id from food_food where restaurant_id= {} ORDER BY id ASC"
.format(rest['id']))
food_item = curr.fetchall()
variant_list = []
food_addon_list = []
food_list_obj = []
for food in food_item:
food_id = food['id']
discount_price = food['discount_price']
if discount_price:
curr.execute(
"SELECT id, addon_name, price from food_foodaddons where food_name_id = {}"
.format(food_id))
food_addon_item = curr.fetchall()
food["addon"] = food_addon_item
curr.execute(
"SELECT id, variant_title, variant_price, food_id from food_variant where food_id = {}"
.format(food_id))
variant_item = curr.fetchall()
food['variant_list'] = variant_item
food_list_obj.append(food)
rest["food"] = food_list_obj
offer_list.append(rest)
return offer_list
else:
return "Restaurant latitude and longitude not found"
def get_food_menu():
conn = get_db()
curr = conn.cursor()
# curr.execute("SELECT food_id, food_name, food_uom,food_active, food_price, food_details, food_discounts, food_ads, restaurant_id, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| food_picture AS food_picture from food_items")
# curr.execute("SELECT variant_name, food_name from food_items INNER JOIN food_variants ON (food_variants.variant_name = food_items.food_name)")
# curr.execute("SELECT food_food.id, food_food_restaurant.restaurants_id, food_food.food_name, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| food_picture AS food_picture, food_food.food_active, food_variant.id, food_variant.food_title, food_variant.food_uom,food_variant.food_details, food_variant.food_price, food_variant.food_discounts, food_variant.variant_active, food_variant.food_id, food_variant.type_of_food from food_food INNER JOIN food_variant ON food_food.id = food_variant.food_id INNER JOIN food_food_restaurant ON food_food.id = food_food_restaurant.food_id ")
# curr.execute("SELECT food_food.id, food_food.restaurant_id, food_food.food_name, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| food_picture AS food_picture, food_food.food_active, food_variant.id, food_variant.food_title, food_variant.food_uom,food_variant.food_details, food_variant.food_price, food_variant.food_discounts, food_variant.variant_active, food_variant.food_id, food_variant.type_of_food from food_food INNER JOIN food_variant ON food_food.id = food_variant.food_id ")
curr.execute(
"SELECT food_foodmenu.id, food_foodmenu_food_variants.variant_id, food_foodmenu.food_menu_name, food_foodmenu.food_menu_details,food_foodmenu.food_menu_price, food_foodmenu.food_menu_discounts, food_foodmenu.menu_active, food_foodmenu.type_of_food_menu, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| food_menu_image AS food_menu_image from food_foodmenu INNER JOIN food_foodmenu_food_variants ON food_foodmenu.id = food_foodmenu_food_variants.foodmenu_id "
)
row = curr.fetchall()
return row
def table_info(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
if event.get("restaurant_id"):
curr.execute(
"SELECT restaurant_restaurants.restaurant_name, restaurant_restaurants.restaurant_phone, restaurant_restaurants.restaurant_about, restaurant_restaurants.restaurant_address_line_1, restaurant_restaurants.restaurant_address_line_2, restaurant_restaurants.restaurant_profile_picture, restaurant_restaurants.restaurant_thumbnail, restaurant_restaurants.country, restaurant_restaurants.zip_code,restaurant_restaurants.city, restaurant_restaurants.state, restaurant_restaurants.latitude, restaurant_restaurants.longitude, restaurant_restaurants.restaurant_old_new_status, restaurant_restaurants.type_of_restaurant, restaurant_restaurants.food_prepare_avg_time, restaurant_restaurants.special_foods, restaurant_restaurants.food_price_icon, restaurant_restaurants.restaurants_rating, restaurant_restaurants.total_customer_review, restaurant_restaurants.pickup_delivery_status, restaurant_restaurants.free_delivery, restaurant_restaurants.offer, restaurant_restaurants.status, restaurant_restaurants.restaurant_active, restaurant_restaurants.restaurant_email, waiter_diningtable.id, waiter_diningtable.table_name, waiter_diningtable.number_of_seat, waiter_diningtable.about_table, waiter_diningtable.qr_code, waiter_diningtable.table_icon, waiter_diningtable.table_status, waiter_diningtable.is_active, waiter_diningtable.restaurant_id from waiter_diningtable INNER JOIN restaurant_restaurants ON waiter_diningtable.restaurant_id = restaurant_restaurants.id where waiter_diningtable.restaurant_id = {} "
.format(data['restaurant_id']))
obj = curr.fetchall()
return obj
elif event.get("coffee_house_id"):
curr.execute(
"SELECT coffeehouse_coffeehouse.coffee_house_name, coffeehouse_coffeehouse.coffee_house_email,coffeehouse_coffeehouse.coffee_house_phone, coffeehouse_coffeehouse.coffee_house_about, coffeehouse_coffeehouse.coffee_house_address_line_1, coffeehouse_coffeehouse.coffee_house_address_line_2,coffeehouse_coffeehouse.coffee_house_profile_picture, coffeehouse_coffeehouse.coffee_house_thumbnail, coffeehouse_coffeehouse.coffee_house_website, coffeehouse_coffeehouse.coffee_house_country, coffeehouse_coffeehouse.coffee_house_zip_code, coffeehouse_coffeehouse.coffee_house_city, coffeehouse_coffeehouse.coffee_house_state, coffeehouse_coffeehouse.latitude, coffeehouse_coffeehouse.longitude,coffeehouse_coffeehouse.coffee_house_old_new_status, coffeehouse_coffeehouse.type_of_coffee, coffeehouse_coffeehouse.coffee_prepare_avg_time, coffeehouse_coffeehouse.special_coffee, coffeehouse_coffeehouse.coffee_house_price_icon, coffeehouse_coffeehouse.coffee_house_rating, coffeehouse_coffeehouse.total_customer_review, coffeehouse_coffeehouse.coffee_house_offer, coffeehouse_coffeehouse.coffee_house_status, coffeehouse_coffeehouse.coffee_house_active, waiter_diningtable.id, waiter_diningtable.table_name, waiter_diningtable.number_of_seat, waiter_diningtable.about_table, waiter_diningtable.qr_code, waiter_diningtable.table_icon, waiter_diningtable.table_status, waiter_diningtable.is_active, waiter_diningtable.coffee_house_id from waiter_diningtable INNER JOIN coffeehouse_coffeehouse ON waiter_diningtable.coffee_house_id = coffeehouse_coffeehouse.id where waiter_diningtable.coffee_house_id= {} "
.format(data['coffee_house_id']))
obj = curr.fetchall()
return obj
def get_login_info(self, event):
data = json.loads(event['body']) if event.get('body', 0) else event
try:
conn = get_db()
cur = conn.cursor()
cur.execute(
"SELECT id, first_name, last_name, to_char(date_of_birth, 'YYY-MM-DD'), 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| profile_picture AS profile_picture, address_line_1, address_line_2, mobile, city, state, country, zip_code, is_active, email, password, restaurant_id, coffee_house_id, user_id, table_id from waiter_waiter where email= '{}' "
.format(data['email']))
info = cur.fetchone()
self.email = info['email']
self.password = info['password']
info.pop('password')
self.user_info = info
return self.user_info
except Exception as e:
return e
def get_order_history(event):
data = json.loads(event['body']) if event.get('body', 0) else event
try:
conn = get_db()
curr = conn.cursor()
if data.get('customer_id'):
curr.execute(
"SELECT id, order_id, order_amount, order_status, payment_status, delivery_address, postcode, city, waiter_tips, order_type, to_char(delivery_time, 'MON-DD-YYYY HH12:MIPM') AS delivery_time, to_char(date_time, 'MON-DD-YYYY HH12:MIPM') AS date_time, customer_id, coffee_house_id, restaurant_id from order_order where customer_id = {} ORDER BY id DESC ".format(
data['customer_id']))
obj = curr.fetchall()
return obj
else:
return []
except Exception as e:
return e
def coffee_house_latlong(event):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
curr = conn.cursor()
curr.execute("SELECT latitude,longitude from coffeehouse_coffeehouse ")
row = curr.fetchall()
coffee_house = row
customer = {'latitude': data['latitude'], 'longitude': data['longitude']}
result = closest_coffee_house(coffee_house, customer)
response = result[:10]
rs = []
for items in response:
rs.append(items['latitude'])
find_lat = tuple(rs)
return find_lat
def lambda_handler(event, context):
data = json.loads(event['body']) if event.get('body', 0) else event
conn = get_db()
cur = conn.cursor()
if data.get('restaurant_id'):
cur.execute(
"SELECT id, restaurant_name, restaurant_email, restaurant_phone, restaurant_about, restaurant_address_line_1, restaurant_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_profile_picture AS restaurant_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_thumbnail AS restaurant_thumbnail, restaurant_linkedin, restaurant_website, restaurant_facebook, restaurant_instagram, restaurant_twitter, country, zip_code, city, state, restaurant_active, user_id, latitude, longitude, status, type_of_restaurant, food_prepare_avg_time, food_price_icon, special_foods, restaurant_old_new_status, restaurants_rating, pickup_delivery_status, total_customer_review, free_delivery, offer from restaurant_restaurants where id={}"
.format(data['restaurant_id']))
info = cur.fetchall()
return {'body': info}
elif data.get('coffee_house_id'):
cur.execute(
"SELECT id, coffee_house_name, coffee_house_email, coffee_house_phone, coffee_house_about, coffee_house_address_line_1, coffee_house_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_profile_picture AS coffee_house_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_thumbnail AS coffee_house_thumbnail, coffee_house_linkedin, coffee_house_website, coffee_house_facebook, coffee_house_instagram, coffee_house_twitter, coffee_house_country, coffee_house_zip_code, coffee_house_city, coffee_house_state, latitude, longitude, coffee_house_old_new_status, type_of_coffee, coffee_prepare_avg_time, special_coffee, coffee_house_price_icon, coffee_house_rating, total_customer_review, coffee_house_offer, coffee_house_status, coffee_house_active, user_id from coffeehouse_coffeehouse where id={}"
.format(data['coffee_house_id']))
info = cur.fetchall()
return {'body': info}
def get_restaurants(event):
lat_long = latlong(event)
conn = get_db()
curr = conn.cursor()
if len(lat_long) >= 2:
curr.execute(
"SELECT id, restaurant_name, restaurant_email, restaurant_phone, restaurant_about,restaurant_address_line_1,restaurant_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_profile_picture AS restaurant_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_thumbnail AS restaurant_thumbnail,country,zip_code,city,state, latitude,longitude, restaurant_old_new_status, type_of_restaurant,food_prepare_avg_time,special_foods,food_price_icon, status, restaurant_active, restaurants_rating, total_customer_review, pickup_delivery_status, free_delivery, offer from restaurant_restaurants where latitude IN {} ORDER BY id ASC "
.format(lat_long))
row = curr.fetchall()
return row
elif len(lat_long) >= 1:
curr.execute(
"SELECT id, restaurant_name, restaurant_email, restaurant_phone, restaurant_about,restaurant_address_line_1,restaurant_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_profile_picture AS restaurant_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| restaurant_thumbnail AS restaurant_thumbnail,country,zip_code,city,state, latitude,longitude, restaurant_old_new_status, type_of_restaurant,food_prepare_avg_time,special_foods,food_price_icon, status, restaurant_active, restaurants_rating, total_customer_review, pickup_delivery_status, free_delivery, offer from restaurant_restaurants where latitude = {} "
.format(lat_long[0]))
row = curr.fetchall()
return row
else:
return []
def get_coffee_house(event):
data = json.loads(event['body']) if event.get('body', 0) else event
lat_long = latlong(event)
conn = get_db()
curr = conn.cursor()
if len(lat_long) >= 2:
curr.execute(
"SELECT id, coffee_house_name, coffee_house_email, coffee_house_phone, coffee_house_about,coffee_house_address_line_1,coffee_house_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_profile_picture AS coffee_house_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_thumbnail AS coffee_house_thumbnail,coffee_house_country,coffee_house_zip_code,coffee_house_city, coffee_house_state,latitude,longitude, coffee_house_old_new_status, type_of_coffee,coffee_prepare_avg_time,special_coffee,coffee_house_price_icon, coffee_house_status, coffee_house_active, coffee_house_rating, total_customer_review, coffee_house_offer from coffeehouse_coffeehouse where latitude IN {} ORDER BY id ASC"
.format(lat_long))
coffee_house_list = curr.fetchall()
return coffee_house_list
elif len(lat_long) >= 1:
curr.execute(
"SELECT id, coffee_house_name, coffee_house_email, coffee_house_phone, coffee_house_about,coffee_house_address_line_1,coffee_house_address_line_2, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_profile_picture AS coffee_house_profile_picture, 'https://mapplate-public.s3.eu-central-1.amazonaws.com/media/'|| coffee_house_thumbnail AS coffee_house_thumbnail,coffee_house_country,coffee_house_zip_code,coffee_house_city, coffee_house_state,latitude,longitude, coffee_house_old_new_status, type_of_coffee,coffee_prepare_avg_time,special_coffee,coffee_house_price_icon, coffee_house_status, coffee_house_active, coffee_house_rating, total_customer_review, coffee_house_offer from coffeehouse_coffeehouse where latitude = {} "
.format(lat_long[0]))
coffee_house_list = curr.fetchall()
return coffee_house_list
else:
return []
def device_token_update(device_token, email):
sql = """ UPDATE waiter_waiter
SET device_token = %s
WHERE email = %s"""
conn = None
updated_rows = 0
try:
conn = get_db()
cur = conn.cursor()
cur.execute(sql, (device_token, email))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def insert_data(event):
conn = get_db()
data = json.loads(event['body']) if event.get('body', 0) else event
if data.get('restaurant_id'):
conn.autocommit = True
curr = conn.cursor()
curr.execute(
"SELECT customer_id from restaurant_restaurantratings where customer_id= {}".format(data['customer_id']))
row = curr.fetchone()
if row == None:
cur = conn.cursor()
cur.execute(
"insert into restaurant_restaurantratings(customer_rating, customer_id,customer_comment,restaurant_id ) values(%s,%s,%s,%s)",
(data['customer_rating'],
data['customer_id'],
data['customer_comment'],
data['restaurant_id']
))
else:
update_retaurant_review(event)
elif data.get('coffee_house_id'):
conn.autocommit = True
curr = conn.cursor()
curr.execute(
"SELECT customer_id from coffeehouse_coffeehouseratings where customer_id= {}".format(data['customer_id']))
row = curr.fetchone()
if row == None:
cur = conn.cursor()
cur.execute(
"insert into coffeehouse_coffeehouseratings(customer_rating, customer_id,customer_comment,coffee_house_id ) values(%s,%s,%s,%s)",
(data['customer_rating'],
data['customer_id'],
data['customer_comment'],
data['coffee_house_id']
))
else:
update_retaurant_review(event)