def get_all_orders(): context = DbContext() result = context.select(BASE_SEARCH_QUERY, ()) del context return result
def delete_order(order_id): context = DbContext() query = "DELETE FROM customer_order WHERE order_id = {order_id}".format(order_id=order_id) context.update(query, ()) del context
def sign_up(data): try: auth_token_payload = jwt.decode(data['auth_token'].encode('utf-8'), SECRET_KEY, algorithms=['HS256']) except Exception as e: print(e) return 2 if get_user(auth_token_payload['phone']) is not None: return 1 context = DbContext() password_hash = generate_password_hash(data['employee_password']) query = "INSERT INTO employee VALUES (DEFAULT, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" context.update( query, [ data['employee_full_name'], data['employee_itn'], data['employee_passport'], data['employee_address'], auth_token_payload['phone'], data['employee_sex'], data['employee_birthdate'], data['employee_salary'], auth_token_payload['position'], password_hash ] ) del context return 0
def update_order_and_return_updated(order): context = DbContext() # Check all books exist for i in range(len(order['books'])): query = "SELECT book_id FROM book WHERE book_title = %s" result = context.select(query, [order['books'][i]['title']]) if len(result) == 0: return order, 1 else: order['books'][i]['book_id'] = result[0][0] # Delete old order -> books relation and add new query = "DELETE FROM order_book WHERE order_id = {order_id}".format(order_id=order['order_id']) context.update(query, ()) for book in order['books']: context.update( "INSERT INTO order_book VALUES (%s, %s, %s, %s)", [order['order_id'], book['book_id'], book['books_amount'], book['book_price']] ) # Update order data query = "UPDATE customer_order SET receipt_date = %s, completion_date = %s WHERE order_id = {order_id}".format( order_id=order['order_id']) context.update(query, [order['receipt_date'], order['completion_date']]) query = "SELECT * FROM customer_order WHERE order_id = {order_id}".format(order_id=order['order_id']) updated = context.select(query, ()) del context return updated[0], 0
def get_order_by_id(order_id): context = DbContext() query = BASE_SEARCH_QUERY + "WHERE order_id = {order_id}".format(order_id=order_id) result = context.select(query, ()) del context return result[0]
def delete_contract(contract_id): context = DbContext() query = "DELETE FROM contract WHERE contract_id = {contract_id}".format( contract_id=contract_id) context.update(query, ()) del context
def get_my_contracts(manager_id): context = DbContext() query = BASE_SEARCH_QUERY + "WHERE employee_id = {manager_id}".format( manager_id=manager_id) result = context.select(query, ()) del context return result
def get_contract_by_id(contract_id): context = DbContext() query = BASE_SEARCH_QUERY + "WHERE contract_id = {contract_id}".format( contract_id=contract_id) result = context.select(query, ()) del context return result[0]
def get_books_in_order(order_id): context = DbContext() query = "SELECT DISTINCT ON (book.book_id) " \ "book.book_title, books_amount, book_price FROM " \ "order_book INNER JOIN book ON order_book.book_id = book.book_id " \ "WHERE order_id = {order_id}".format(order_id=order_id) result = context.select(query, ()) del context return result
def get_sales_report(): context = DbContext() query = "SELECT book_title, amount, revenue FROM " \ "(SELECT DISTINCT order_book.book_id, amount, book_price*amount AS revenue " \ "FROM (SELECT book_id, SUM(books_amount) AS amount FROM order_book GROUP BY book_id) t1 " \ "INNER JOIN order_book ON t1.book_id = order_book.book_id) t2 " \ "INNER JOIN book ON t2.book_id = book.book_id" result = context.select(query, ()) del context return result
def update_contract_and_return_updated(contract_id, contract): context = DbContext() query = "UPDATE contract SET sign_date = %s WHERE contract_id = {contract_id}".format( contract_id=contract_id) context.update(query, [contract['sign_date']]) updated_author = context.select( "SELECT * FROM contract WHERE contract_id = {contract_id}".format( contract_id=contract_id), ()) del context return updated_author[0]
def search_orders(customer_name, receipt_date): context = DbContext() query = BASE_SEARCH_QUERY + "WHERE " params = [] if customer_name is not None: query += "customer_full_name LIKE %s AND " params.append("%" + customer_name + "%") if receipt_date is not None: query += "receipt_date = %s AND " params.append(receipt_date) query += "true = true;" result = context.select(query, params) del context return result
def search_contracts(book_title, sign_date): context = DbContext() query = BASE_SEARCH_QUERY + "WHERE " params = [] if book_title is not None: query += "book_title LIKE %s AND " params.append("%" + book_title + "%") if sign_date is not None: query += "sign_date = %s AND " params.append("%" + sign_date + "%") query += "true = true;" result = context.select(query, params) del context return result
def add_contract(contract, manager_id): context = DbContext() # Check contract for this book doesn't already exist query = "SELECT * FROM contract WHERE book_id = (SELECT book_id FROM book WHERE book_title = %s)" existing_contract = context.select(query, [contract['book_title']]) if len(existing_contract) > 0: return 1 # Check specified book exists query = "SELECT * FROM book WHERE book_title = %s" book = context.select(query, [contract['book_title']]) if len(book) == 0: return 2 # Save contract query = "INSERT INTO contract VALUES (DEFAULT, {book_id}, {manager_id}, %s)".format( book_id=book[0][0], manager_id=manager_id) context.update(query, [contract['sign_date']]) del context return 0
def add_order(order): context = DbContext() # Check all books exist for i in range(len(order['books'])): query = "SELECT book_id FROM book WHERE book_title = %s" result = context.select(query, [order['books'][i]['title']]) if len(result) == 0: return order, 1 else: order['books'][i]['book_id'] = result[0][0] # Get customer id, add if not exists query = "SELECT customer_id FROM customer WHERE customer_full_name = %s AND customer_address = %s" customer = context.select(query, [order['customer_name'], order['customer_address']]) if len(customer) > 0: customer_id = customer[0][0] else: query = "INSERT INTO customer VALUES (DEFAULT, %s, %s)" context.update(query, [order['customer_name'], order['customer_address']]) customer_id = context.select("SELECT MAX(customer_id) AS last_customer_id FROM customer", ())[0][0] # Save order query = "INSERT INTO customer_order VALUES (DEFAULT, {customer_id}, %s, %s)".format(customer_id=customer_id) context.update(query, [order['receipt_date'], order['completion_date']]) order_id = context.select("SELECT MAX(order_id) AS last_order_id FROM customer_order", ())[0][0] # Save order -> books relation for order in order['books']: context.update( "INSERT INTO order_book VALUES (%s, %s, %s, %s)", [order_id, order['book_id'], order['books_amount'], order['book_price']] ) del context return order_id, 0