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