def get_suppliers_query(business_id, items_ids=None):
"""create an SQL query that will return all of the suppliers that sell to a business and the items that they supply.
provided item id list it will return the items requested"""
conditions = []
if business_id:
conditions.append(
["AND", "supplier.business_id", "=",
int(business_id)])
else:
raise HTTPException(status_code=400, detail="no business id sent")
if items_ids:
and_or = "AND"
for item in items_ids:
conditions.append([and_or, "supplier.item_id", "=", int(item)])
and_or = "OR"
conditions.append(
["AND", "supplier.supplier_id", "=", "users.user_id"])
final_query, res_code = DbQueries.select_query(["supplier", "users"],
None, conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_item_from_order(item_id, order_id):
"""creates an sql query that gets a specific item from an order"""
conditions = []
if order_id:
conditions.append(
["AND", "order_content.order_id", "=",
int(order_id)])
else:
raise HTTPException(status_code=400, detail="No order_id id sent")
if item_id:
conditions.append(
["AND", "order_content.item_id", "=",
int(item_id)])
else:
raise HTTPException(status_code=400, detail="No item id sent")
conditions.append(
["AND", "order_content.order_id", "=", "orders.order_id"])
final_query, res_code = DbQueries.select_query(
[["orders"], ["order_content"]],
[[],
[["item_id"], ["order_id"], ["price_per_unit"], ["amount"],
["unit"]]], conditions)
if res_code != 200:
raise HTTPException(status_code=400, detail=final_query)
return final_query, 200
def get_open_orders_query(business_id, order_ids=None):
"""create an SQL query that will get all opened orders information for a specific business.
provided order id list it will return only the open orders requested"""
conditions = []
if business_id:
conditions.append(
["AND", "orders.business_id", "=",
int(business_id)])
else:
return error_message(400, "Bad request",
"no business id sent"), 400
if order_ids:
and_or = "AND"
for order in order_ids:
conditions.append([and_or, "orders.order_id", "=", int(order)])
and_or = "OR"
conditions.append(["AND", "orders.order_id", "=", "content.order_id"])
conditions.append(["AND", "orders.order_date", "is", "null"])
final_query, res_code = DbQueries.select_query(
["orders", ["order_content", "content"]], None, conditions)
if res_code != 200:
return error_message(res_code, final_query), 400
return final_query, 200
def get_weight_history(item_id, business_id, min_date, max_date):
"""creates an items weight history query
input: business_id, item_id - ids
max,min dates - Unix Timestamp - represents the time window for the history"""
conditions = []
if business_id:
conditions.append(
["AND", "weights.business_id", "=",
int(business_id)])
else:
raise HTTPException(status_code=400, detail="No business id sent")
if item_id:
conditions.append(["AND", "weights.item_id", "=", int(item_id)])
else:
raise HTTPException(status_code=400, detail="No item id sent")
if min_date is not None:
conditions.append([
"AND", "weights.weighing_date", ">=",
"to_timestamp(" + str(min_date) + ")"
])
if max_date is not None:
conditions.append([
"AND", "weights.weighing_date", "<=",
"to_timestamp(" + str(max_date) + ")"
])
cols_to_bring = [[["weight_value", "weight", "SUM"],
["weighing_date", "date"], ["item_id"]]]
query, res_code = DbQueries.select_query(["weights"], cols_to_bring,
conditions)
if res_code != 200:
raise HTTPException(status_code=500,
detail="Error creating weight query")
return query, 200
def get_userid_by_email(self, user_email):
column_list = [[["user_id"]]]
conditions = []
if user_email:
if ";" in user_email:
raise HTTPException(status_code=400,
detail="request not allowed")
email = user_email.split("@")
if len(email) != 2:
raise HTTPException(status_code=400, detail="Invalid email")
conditions.append(
["AND", "users.email_user_name", "=", "'" + email[0] + "'"])
conditions.append(
["AND", "users.email_domain_name", "=", "'" + email[1] + "'"])
else:
raise HTTPException(status_code=400, detail=" user email not sent")
final_query, res_code = DbQueries.select_query([["users"]],
column_list, conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
res = self.connection.get_result(final_query)
try:
return res[0]["user_id"]
except (KeyError, IndexError):
raise HTTPException(status_code=404, detail="User not found")
def get_container_item_id_query(business_id,
container_id,
using_end_date=None):
"""creates an SQL query that will return a containers item id"""
conditions = []
if business_id:
conditions.append(
["AND", "containers.business_id", "=",
int(business_id)])
else:
raise HTTPException(status_code=400, detail="No business id sent")
if business_id:
conditions.append(
["AND", "containers.container_id", "=",
int(container_id)])
else:
raise HTTPException(status_code=400, detail="No business id sent")
if using_end_date:
conditions.append([
"AND", "containers.using_end_date", "=",
"to timestamp(" + using_end_date + ")"
])
else:
conditions.append(
["AND", "containers.using_end_date", "is", "null"])
final_query, res_code = DbQueries.select_query([["containers"]],
[[["item_id"]]],
conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_order_by_supplier(business_id, supplier_id, open_orders=False):
"""creates an sql query that gets a specific order by supplier.
open orders is in case the request is only open orders(orders with no date)"""
conditions = []
if supplier_id:
conditions.append(
["AND", "orders.supplier_id", "=",
int(supplier_id)])
else:
return error_message(400, "Bad request",
"no supplier id sent"), 400
if business_id:
conditions.append(
["AND", "orders.business_id", "=",
int(business_id)])
else:
return error_message(400, "Bad request",
"no business id sent"), 400
if open_orders:
conditions.append(["AND", "orders.order_date", " is ", 'null'])
final_query, res_code = DbQueries.select_query([["orders"]], [],
conditions)
if res_code != 200:
return error_message(res_code, final_query), 400
return final_query, 200
def get_user_preferences_query(user_id):
"""creates an SQL query that will return the user preferences"""
column_list = [["user_id"], ["lang"], ["minimum_reach_alerts"],
["freshness_alerts"]]
conditions = [["user_id", "=", int(user_id)]]
final_query, res_code = DbQueries.select_query(["user_preference"],
column_list, conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_new_container():
"""gets the last added container (container ids are auto incremented)"""
conditions = [["AND", "item_id", "is", "null"]]
cols_to_bring = [[["container_id", "container_id", "MAX"]]]
query, res_code = DbQueries.select_query(["containers"], cols_to_bring,
conditions)
if res_code != 200:
raise HTTPException(status_code=500,
detail="Error getting new item")
return query
def get_order(order_id):
"""creates an SQL query that will return a specific order based on the id"""
conditions = []
if order_id:
conditions.append(["AND", "orders.order_id", "=", int(order_id)])
else:
raise HTTPException(status_code=400, detail="No order id sent")
final_query, res_code = DbQueries.select_query([["orders"]], [],
conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query, 200
def get_users_query(business_id):
"""this function creates a"""
conditions = []
if business_id:
conditions.append(
["AND", "users.business_id", "=",
int(business_id)])
final_query, res_code = DbQueries.select_query([["users"]], [],
conditions)
if res_code != 200:
raise HTTPException(status_code=res_code,
detail="unable to get users")
return final_query
def remove_row(table_name, conditions):
"""creates a delete SQL query using the parameters
input: table name: string with table name
conditions: list of lists containing the conditions to remove rows
"""
if not table_name:
return None
query = "DELETE FROM " + table_name
where = DbQueries.condition_creator(conditions)
if where is not None:
query += " WHERE " + where
query + "; "
return query
def get_items(item_id=None, business_id=None):
"""creates an SQL query that will return all items.
If provided business_id and item_id can focus the query"""
conditions = []
if item_id:
conditions.append(["AND", "item_id", "=", int(item_id)])
if business_id is not None:
conditions.append(["AND", "business_id", "=", int(business_id)])
final_query, res_code = DbQueries.select_query([["food_items"]], [],
conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_business_query(business_id):
"""method for getting business information based on business id"""
conditions = []
if business_id:
conditions.append(
["AND", "business.business_id", "=",
int(business_id)])
final_query, res_code = DbQueries.select_query(["business"], [],
conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_container_by_id_query(container_id):
"""get a query with container information """
conditions = []
if container_id:
conditions.append(["AND", "container_id", "=", int(container_id)])
else:
raise HTTPException(status_code=400, detail="No container id sent")
cols_to_bring = []
query, res_code = DbQueries.select_query(["containers"], cols_to_bring,
conditions)
if res_code != 200:
raise HTTPException(status_code=500,
detail="Error creating container query")
return query
def get_rules_query(business_id=None):
"""method for getting the rules by business id
parameters received: required: business_id, optional: active, rule_id
output:[["rule_id", "item_id", "content_minimum_per_day", "content_maximum_per_day",
"content_total_minimum", "content_total_maximum", "active"]....]"""
conditions = []
if business_id:
conditions.append(
["AND", "food_items.business_id", "=",
int(business_id)])
final_query, res_code = DbQueries.select_query(
["food_items"],
[[["item_id"], ["business_id"], ["content_minimum_per_day"],
["content_maximum_per_day"], ["content_total_minimum"],
["content_total_maximum"], ["item_average_weight"], ["barcode"]]
], conditions)
if res_code != 200:
raise HTTPException(status_code=res_code, detail=final_query)
return final_query
def get_notifications(business_id=None, active=True, notification_id=None):
"""method for getting the active notifications by business id
output:[["code", "message", "item_id", "active", "closed_by_user"]]"""
conditions = []
if business_id:
conditions.append(
["AND", "notifications.business_id", "=",
int(business_id)])
if active:
conditions.append(["AND", "notifications.active", "=", active])
if notification_id:
conditions.append([
"AND", "notifications.notification_id", "=",
int(notification_id)
])
final_query, res_code = DbQueries.select_query(
[["notifications"]],
[[["code"], ["business_id"], ["message"],
["food_item_id", "item_id"], ["active"], ["closed_by_user"]]],
conditions)
return final_query
def get_notifications_with_info(business_id=None,
active=True,
notification_id=None):
"""this function creates a full notification information kit query
a notification kit includes the active notifications and the
parameters received: required: business_id,
optional: active, notification_id
output:[["code", "message", "item_id", "active", "closed_by_user"]]"""
conditions = []
if business_id:
conditions.append(
["AND", "notifications.business_id", "=",
int(business_id)])
if active:
conditions.append(["AND", "notifications.active", "=", active])
if notification_id:
conditions.append([
"AND", "notifications.notification_id", "=",
int(notification_id)
])
conditions.append(
["AND", "notifications.food_item_id", "=", "food.item_id"])
conditions.append(["AND", "food.category_id", "=", "cat.category_id"])
sub_table = ReadQueries.get_current_weight_query(business_id)
sub_table = sub_table[:-1]
conditions.append(["AND", "sub.item_id", "=", "food.item_id"])
final_query, res_code = DbQueries.select_query(
[["notifications"], ["food_items", "food"], ["categories", "cat"],
["(" + sub_table + ")", "sub"]],
[[["code"], ["message"], ["food_item_id", "item_id"], ["active"],
["closed_by_user"]], [["item_name"], ["unit"], ["barcode"]],
[["category_id"], ["category_name"]],
[["date"], ["item_name"], ["weight"]]], conditions)
if res_code != 200:
return error_message(res_code, final_query), 400
return final_query, 200
def get_supplier(business_id, supplier_id, item_id):
"""Create a supplier sql query based on the item_id, business_id and supplier_id"""
conditions = []
if item_id:
conditions.append(["AND", "supplier.item_id", "=", int(item_id)])
else:
return "No order id sent", 400
if supplier_id:
conditions.append(
["AND", "supplier.supplier_id", "=",
int(supplier_id)])
else:
return "No supplier id sent", 400
if business_id:
conditions.append(
["AND", "supplier.business_id", "=",
int(business_id)])
else:
return "No business id sent", 400
final_query, res_code = DbQueries.select_query([["supplier"]], [],
conditions)
if res_code != 200:
return "Unable to create query", 400
return final_query, 200
def get_current_weight_query(business_id=None,
container_ids=None,
item_ids=None,
get_by_container=False):
"""gets an SQL query of the last weight of all items.
optional parameters:
business_id: will specify which business you want to request
container_ids: int or list of ints specifying which container we want
item_ids: int specifying which item we want
get by container: boolean determines if to give the results by container or by item """
conditions = []
if business_id:
conditions.append(
["AND", "containers.business_id", "=",
int(business_id)])
if container_ids:
and_or = "AND"
if type(container_ids) == list:
for container_id in container_ids:
conditions.append([
and_or, "containers.container_id", "=",
int(container_id)
])
and_or = "OR"
else:
conditions.append([
"AND", "containers.container_id", "=",
int(container_ids)
])
if item_ids:
and_or = "AND"
for item in item_ids:
conditions.append(
[and_or, "containers.item_id", "=",
int(item)])
and_or = "OR"
conditions.append(["AND", "containers.using_end_date", "is", "null"])
conditions.append(
["AND", "weights.container_id", "=", "containers.container_id"])
max_table, res_code = DbQueries.select_query(
["containers", "weights"],
[[["container_id"]], [["weighing_date", "date", "MAX"]]],
conditions)
max_table = max_table[:-1]
conditions.append(
["AND", "food_items.item_id", "=", "containers.item_id"])
conditions.append(["AND", "t1.date", "=", "weights.weighing_date"])
conditions.append(
["AND", "t1.container_id", "=", "containers.container_id"])
conditions.append(
["AND", "food_items.category_id", "=", "categories.category_id"])
if get_by_container:
cols_to_bring = [[["container_id"], ["business_id"]],
[["weight_value", "weight"],
["weighing_date", "date"]],
[["item_name"], ["item_id"],
["unit"], ["barcode"]],
[["category_name"], ["category_id"]], []]
else:
cols_to_bring = [[[]],
[["weight_value", "weight", "SUM"],
["weighing_date", "date", "MAX"]],
[["item_name"], ["item_id"],
["unit"], ["barcode"]],
[["category_name"], ["category_id"]], []]
final_query, res_code = DbQueries.select_query([
"containers", "weights", "food_items", "categories",
["(" + max_table + ")", "t1"]
], cols_to_bring, conditions)
if res_code != 200:
raise HTTPException(status_code=400, detail=final_query)
return final_query
