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