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)