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 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 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 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)