def get_customer_orders(customer_num) -> list:
"""
Get all orders for an customer given a customer number
:params customer_num: int
:returns json list of dicts
"""
sql = """
SELECT orders.*
FROM orders
LEFT JOIN customers ON customers.customerNumber = orders.customerNumber
WHERE customers.customerNumber = %s"""
db.execute(sql, customer_num)
customers_orders = list(db.fetchall())
description = db.description
header_data = []
for item in description:
header_data.append(item[0])
new_data = []
for order_list in customers_orders:
order_list: list = list(order_list)
for index, item in enumerate(order_list):
if isinstance(item, decimal.Decimal):
order_list[index]: str = str(item)
if isinstance(item, datetime.date):
order_list[index]: str = item.strftime("%d/%m/%Y")
new_data.append(dict(zip(header_data, order_list)))
return jsonify(new_data)
def get_employee_customers(emp_num) -> list:
"""
Get all Customers for an Employee
@param:
1) Employee Number - should be unique
@returns:
1) List of Customers details for the employee
"""
sql = """
SELECT customers.*
FROM customers
LEFT JOIN employees ON salesRepEmployeeNumber = employees.employeeNumber
WHERE employeeNumber = %s"""
db.execute(sql, emp_num)
customers_data = list(db.fetchall())
description = db.description
header_data = []
for item in description:
header_data.append(item[0])
new_data = []
for customer_list in customers_data:
customer_list = list(customer_list)
for index, item in enumerate(customer_list):
if isinstance(item, decimal.Decimal):
customer_list[index] = str(item)
new_data.append(dict(zip(header_data, customer_list)))
return jsonify(new_data)
def decorated_function(*args, **kwargs):
if 'x-api-key' not in request.headers:
abort(401)
# NOTE: everytime this is called update the api key table to set the keys to invalid which pass the expiry_date
# NOTE: also set up commit and rollback
try:
# Fix this query some problem here
expiry_api_sql = """
UPDATE apikeys set AKvalid = %s WHERE AKexpiryDate < NOW()
"""
db.execute(expiry_api_sql, 'No')
except Exception as err:
app.logger.exception(err)
return "An error occured"
api_sql = """
SELECT AKapi,
AKvalid,
AKsuspended,
AKexpiryDate
FROM apikeys
WHERE AKapi = %s
AND AKlastAction != %s
"""
# get the key from the database if exists ( using the value passed from the headers 'x-api-key' )
req_api_key = request.headers.get('x-api-key')
db.execute(api_sql, (req_api_key, "Deleted"))
api_key_details = db.fetchone()
# No data exists in the database for this key
if api_key_details is None:
return redirect(url_for('api_key'))
header_data = []
for header in db.description:
header_data.append(header[0])
api_data = dict(zip(tuple(header_data), api_key_details))
# Suspended api_key (New email address is needed)
if api_data['AKsuspended'] == "Yes" or api_data['AKsuspended'] is None:
abort(401)
# Invalid api_key (Expired etc.)
if api_data['AKvalid'] == "No" or api_data['AKvalid'] is None:
return redirect(url_for('api_key'))
if request.headers.get('x-api-key') and request.headers.get(
'x-api-key') == api_data['AKapi']:
return view_function(*args, **kwargs)
else:
# return redirect('/api/api_key') or
# return with args passed such as the key is not right in the db
return redirect(url_for('api_key'))
def get_employee_det(emp_num) -> dict:
if request.method == 'GET':
"""
returns json object of employee details
:param emp_num: int
:return: json object of employee details
"""
sql = "SELECT * FROM employees WHERE employeeNumber = %s"
db.execute(sql, emp_num)
employee_details: tuple = db.fetchone()
header_data = []
for header in db.description:
header_data.append(header[0])
final_data = dict(zip(tuple(header_data), employee_details))
return jsonify(final_data)
if request.method == 'POST':
# get the action type to make the right choice
action_type = request.get_json().get('action_type')
# check if the employee is a valid employee
if action_type == 'update':
sql_update = ""
employee_update = request.get_json()
for key, value in employee_update.get('content').items():
sql_update = sql_update + f" {key} = '{value}',"
update_employee_query = f"UPDATE employees SET{sql_update}"[:-1] + " WHERE employeeNumber = %s"
try:
db.execute(update_employee_query,
employee_update.get('employee_id'))
except Exception as err:
raise err
return redirect(
url_for('get_employee_det',
emp_num=employee_update.get('employee_id'),
_method="GET"))
if action_type == 'delete':
# just change last action to delete
return jsonify("deleted")
if action_type == 'undelete':
# just update the last action to undelete
return jsonify("undeleted")
if action_type == 'erase':
# audit the deletion before actually removing
return jsonify("erased")
return jsonify(f"Unknown action type {action_type}")
def orders() -> list:
sql = "SELECT * FROM orders"
db.execute(sql)
orders_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_orders_data = []
for order_item in orders_list:
all_orders_data.append(dict(zip(header_data, order_item)))
return jsonify(all_orders_data)
def customers() -> list:
sql = "SELECT * FROM customers"
db.execute(sql)
customers_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_customers_data = []
for customer in customers_list:
all_customers_data.append(dict(zip(header_data, customer)))
return jsonify(all_customers_data)
def products() -> list:
sql = "SELECT * FROM products"
db.execute(sql)
products_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_products_data = []
for product in products_list:
all_products_data.append(dict(zip(header_data, product)))
return jsonify(all_products_data)
def offices() -> list:
sql = "SELECT * FROM offices"
db.execute(sql)
offices_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_offices_data = []
for office in offices_list:
all_offices_data.append(dict(zip(header_data, office)))
return jsonify(all_offices_data)
def order_det(order_num) -> dict:
sql = """
SELECT * FROM orderdetails WHERE orderNumber = %s
"""
db.execute(sql, order_num)
orders_det: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
order_details_list = []
for order_item in orders_det:
order_details_list.append(dict(zip(header_data, order_item)))
return jsonify(order_details_list)
def order(order_num) -> dict:
sql = "SELECT * FROM orders WHERE orderNumber = %s"
db.execute(sql, order_num)
order_data: list = list(db.fetchone())
# basically convert datetime.date to human readable form
for index, item in enumerate(order_data):
if isinstance(item, datetime.date):
order_data[index]: str = item.strftime("%d/%m/%Y")
header_data = []
for header in db.description:
header_data.append(header[0])
final_data = dict(zip(header_data, order_data))
return jsonify(final_data)
def employees() -> list:
"""Getting all the employees details
:return: json object of employees
"""
sql = "SELECT * FROM employees"
db.execute(sql)
employees_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_employees_data = []
for employee in employees_list:
all_employees_data.append(dict(zip(header_data, employee)))
return jsonify(all_employees_data)
def product_lines() -> list:
"""Getting all the product lines details
:return: json object of product lines
"""
sql = "SELECT * FROM productlines"
db.execute(sql)
product_lines_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_product_line_data = []
for product_line in product_lines_list:
all_product_line_data.append(dict(zip(header_data, product_line)))
return jsonify(all_product_line_data)
def payments() -> list:
"""Getting all the payments details
:return: json object of payments
"""
sql = "SELECT * FROM payments"
db.execute(sql)
payments_list: list = list(db.fetchall())
header_data = []
for header in db.description:
header_data.append(header[0])
all_payments_data = []
for payment in payments_list:
payment_list: list = list(payment)
for index, item in enumerate(payment_list):
if isinstance(item, decimal.Decimal):
payment_list[index]: str = str(item)
if isinstance(item, datetime.date):
payment_list[index]: str = item.strftime("%d/%m/%Y")
all_payments_data.append(dict(zip(header_data, payment_list)))
return jsonify(all_payments_data)
