def show_delete_order_menu() -> None:
data = get_order_data()
current_ids = [item['id'] for item in data]
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
id = get_validated_input('Please Enter An ID To Delete: ',
int,
fg='Blue',
min_length=1,
is_present=current_ids,
cancel_on='0')
if not id:
return
DbController.instance().delete('orders', id)
data = get_order_data()
clear()
dicts_to_table(data)
if input(
fmt_string(
'Item SuccessFully Updated. Would You Like To Update Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def print_data_view(key: str) -> None:
data = get_cats(DbController.instance().get_all_rows(key, '*'))
is_looping = True
while is_looping:
clear()
dicts_to_table(data, paginate=True)
index = get_validated_input('Please Select 1 To Sort: ',
int,
fg='Blue',
cancel_on='0',
is_present=[1])
if index == False:
is_looping = False
continue
if index == 1:
clear()
# Get a list of the current column names to be used in a sort order
sort_on_keys = [key for key in data[0]]
list_to_table(sort_on_keys, 'Available Sorts', enumerate=True)
sort_key = get_validated_input(
'How Would You Like To Sort By [id]? ',
int,
fg='Blue',
min_value=1,
max_value=len(sort_on_keys),
cancel_on=0)
# Refresh data with selected sort order
data = get_cats(DbController.instance().get_all_rows(
key, '*', order=f'ORDER BY {sort_key}'))
continue
def show_delete_item_menu(get_key: str) -> None:
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
current_ids = [item['id'] for item in data]
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
item_id = get_validated_input(f'Please Enter An ID To Delete: ',
int,
fg='Blue',
min_length=1,
is_present=current_ids,
cancel_on='0')
if not item_id:
return
DbController.instance().delete(get_key, item_id)
clear()
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
dicts_to_table(data)
if input(
fmt_string(
'Item SuccessFully Added. Would You Like To Add Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def show_update_status_menu() -> None:
data = get_order_data()
current_ids = [order['id'] for order in data]
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
index = get_validated_input('Please Select An Id To Update: ',
int,
fg='Blue',
cancel_on='0',
is_present=current_ids)
if not index:
is_looping = False
continue
status_list = DbController.instance().get_all_rows(
'status', 'id, code')
current_row = DbController.instance().get_rows_where(
'orders', '*', 'id', index)[0]
clear()
dicts_to_table(data)
print(fmt_string(f'\nUpdating Order {index}...', fg='Cyan'))
dicts_to_table(status_list)
status_index = get_validated_input(f'Please Select A New Status: ',
int,
fg='Blue',
min_length=0,
max_value=len(status_list),
min_value=1,
cancel_on='0')
if not status_index:
continue
current_row['status'] = status_index
DbController.instance().update('orders', index, current_row)
data = get_order_data()
clear()
dicts_to_table(data)
if input(
fmt_string(
'Status SuccessFully Updated. Would You Like To Update Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
continue
def get_unassigned_orders():
clear()
print(fmt_string('Viewing: UNASSIGNED ORDERS', fg='Cyan'))
data = DbController.instance().call_proc('get_unassigned_orders')
if len(data) > 0:
dicts_to_table(data)
input(fmt_string('Press ENTER To Continue', fg='Green'))
def get_free_couriers():
clear()
print(fmt_string('Viewing: FREE COURIERS', fg='Cyan'))
data = DbController.instance().call_proc('get_unassigned_couriers')
if len(data) > 0:
dicts_to_table(data)
input(fmt_string('Press ENTER To Continue', fg='Green'))
def get_table(table):
query_res = DbController.instance().get_all_rows(table, '*')
res = app.response_class(response=json.dumps(query_res),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
def view_procs():
clear()
views = DbController.instance().get_available_procs()
list_to_table(views, 'Views', enumerate=True)
action = get_validated_input('Select A Item To Preview: ',
int,
fg='Green',
min_value=1,
max_value=len(views),
cancel_on='0')
if not action:
return
dicts_to_table(DbController.instance().call_proc(views[action - 1].replace(
' ', '_')))
input(fmt_string('Press Enter To Continue', fg='Green'))
def get_item(table: str, id: int):
if request.method == 'GET':
query_res = DbController.instance().get_rows_where(
table, '*', 'id', str(id))
res = app.response_class(response=json.dumps(query_res),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
else:
query = DbController.instance().update(
table, id, ast.literal_eval(request.data.decode('UTF-8')))
res = app.response_class(response=json.dumps(query),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
def get_order_data(sort: str = 'o.status') -> list[dict[Any, Any]]:
return DbController.instance().get_joins(
fields=[
'o.id', 'o.name', 'o.address', 'o.area', 'o.phone',
'courier.name AS Courier', 's.code AS status'
],
source='orders o',
targets=['couriers courier', 'status s'],
conditions=['courier.id = o.courier', 's.id = o.status'],
order=f'ORDER BY {sort}')
def show_add_item_menu(get_key: str) -> None:
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
current_names = [item['name'] for item in data]
schema = {k.lower(): type(v) for (k, v) in data[0].items()}
validation = {
'all': {
'min_length': 1,
'cancel_on': '0',
'fg': 'Blue'
},
'name': {
'unique': current_names
}
}
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
new_dict = dict_builder(schema, ['id', 'basket'],
validation=validation)
if not new_dict:
return
DbController.instance().insert(get_key, new_dict)
clear()
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
dicts_to_table(data)
if input(
fmt_string(
'Item SuccessFully Added. Would You Like To Add Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def get_cats(data: list[dict[Any, Any]],
action: str = 'join') -> list[dict[Any, Any]]:
cats = DbController.instance().get_all_rows('catagories', '*')
for row in data:
if 'catagory' in row:
if action == 'join':
for cat in cats:
if row['catagory'] == cat['id']:
row['catagory'] = cat['name']
elif action == 'delete':
del row['catagory']
return data
def show_update_item_menu(get_key: str) -> None:
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
current_ids = [item['id'] for item in data]
schema = {k.lower(): type(v) for (k, v) in data[0].items()}
validation = {'all': {'min_length': 1, 'cancel_on': '0', 'fg': 'Blue'}}
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
id = get_validated_input('Please Enter An ID To Edit: ',
int,
fg='Blue',
min_length=1,
is_present=current_ids,
cancel_on='0')
if not id:
return
new_dict = dict_builder(schema, ['id', 'items'], validation=validation)
if not new_dict:
return
DbController.instance().update(get_key, id, new_dict)
clear()
data = get_cats(DbController.instance().get_all_rows(get_key, '*'),
action='delete')
dicts_to_table(data)
if input(
fmt_string(
'Item SuccessFully Added. Would You Like To Add Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def search_table(table: str):
clear()
term = get_validated_input('Please Enter A Search Term: ', fg='Blue')
if table == 'orders':
data = DbController.instance().search_joined_table(
table='orders o',
term=term,
fields=[
'o.id', 'o.name', 'o.address', 'o.area', 'o.phone',
'courier.name AS Courier', 's.code AS status'
],
targets=['couriers courier', 'status s'],
conditions=['courier.id = o.courier', 's.id = o.status'])
else:
data = get_cats(DbController.instance().search_table(table, term),
action='join')
if len(data) > 0:
dicts_to_table(data)
else:
print(fmt_string('No Results Found', fg='White', bg='Red'))
input(fmt_string('Press Enter To Continue', fg='Green'))
def show_order_detail_menu(order) -> None:
# Use the data recived in view_order to print -> use the `status` tables style value to colour by status
for k, v in order.items():
if k == 'status':
col = DbController.instance().get_rows_where(
'status', '*', 'code', v)[0]['style']
print(fmt_string(f'{str(k).title()}: ', fg='Blue'),
fmt_string(f'{str(v).title()}\n', fg=col))
else:
print(fmt_string(f'{str(k).title()}: ', fg='Blue'),
f'{str(v).title()}\n')
# Get the orders current basket from the `basket` table and add a total price per item on
items = DbController.instance().get_joins_where(
fields=[
'b.quantity AS "#x"', 'p.name',
'b.quantity * p.price AS "Sub Total"'
],
source='orders o',
targets=['basket b', 'products p'],
conditions=[f'b.order_id = {order["id"]}', 'b.item = p.id'],
where=f'o.id = {order["id"]}',
type='INNER')
sub = 0
for item in items:
sub += item['Sub Total']
if len(items) > 0:
items.append({'#x': '', 'name': '', 'Sub Total': '----------'})
items.append({'#x': '', 'name': '', 'Sub Total': sub})
dicts_to_table(items)
else:
print(fmt_string('Order: ', fg='Blue'),
fmt_string('Basket Is Empty', fg='Red'))
input(fmt_string('\nPress ENTER To Continue', fg='Green'))
def show_add_order_menu() -> None:
display_data = get_order_data()
schema = {k.lower(): type(v) for (k, v) in display_data[0].items()}
validation = {'all': {'min_length': 1, 'cancel_on': '0', 'fg': 'Blue'}}
is_looping = True
while is_looping:
clear()
dicts_to_table(display_data)
new_dict = dict_builder(schema, ['id', 'courier', 'status'],
validation)
if not new_dict:
return
clear()
dicts_to_table(display_data)
print(fmt_string('\nOrder Complete!', fg='Green'))
dicts_to_table([new_dict], headers=list(display_data[0].keys())[1:-2])
if input(fmt_string('Does This Look Correct?[y/n]\n',
fg='Green')).lower() == 'n':
continue
DbController.instance().insert('orders', new_dict)
clear()
display_data = get_order_data()
dicts_to_table(display_data)
if input(
fmt_string(
'Item SuccessFully Added. Would You Like To Add Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def get_order_view():
query_res = DbController.instance().get_joins(
fields=[
'o.id', 'o.name', 'o.address', 'o.area', 'o.phone',
'courier.name AS Courier', 's.code AS status'
],
source='orders o',
targets=['couriers courier', 'status s'],
conditions=['courier.id = o.courier', 's.id = o.status'],
order=f'ORDER BY o.status')
res = app.response_class(response=json.dumps(query_res),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
def get_summary_by_status():
sum = DbController.instance().call_proc('get_order_status_summary')
labels = [item['status'] for item in sum]
total = 0
for item in sum:
total += item['count']
sizes = [item['count'] / total for item in sum]
_, ax1 = plt.subplots()
ax1.pie(
sizes,
labels=labels,
autopct='%1.1f%%', # type: ignore
shadow=True,
startangle=90)
ax1.axis('equal') # type: ignore
plt.show()
def get_order(id: int):
query_res = DbController.instance().get_joins_where(
fields=[
'b.quantity AS "#x"', 'p.name',
'b.quantity * p.price AS "Sub Total"'
],
source='orders o',
targets=['basket b', 'products p'],
conditions=[f'b.order_id = {id}', 'b.item = p.id'],
where=f'o.id = {id}',
type='INNER')
res = app.response_class(response=json.dumps(query_res),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
def print_order_view():
is_looping = True
data = get_order_data()
# Get ID's of current orders to ensure that a valid one is selected
current_ids = [
item['id']
for item in DbController.instance().get_column('orders', 'id')
]
while is_looping:
clear()
dicts_to_table(data, paginate=True)
index = get_validated_input(
'Please Select An Id To View (-1 To Sort): ',
int,
fg='Blue',
cancel_on='0',
is_present=current_ids + [-1])
if index == False:
is_looping = False
continue
if index == -1:
clear()
dicts_to_table(data, paginate=False)
# Get a list of the current coloum names to be used in a sort order
sort_on_keys = list(data[0].keys())
list_to_table(sort_on_keys, 'Available Sorts', enumerate=True)
sort_key = get_validated_input(
'What Would You Like To Sort By [id]? ',
int,
fg='Blue',
min_value=1,
max_value=len(sort_on_keys),
cancel_on=0)
# Status sorts on id not named string so set this if status is selected
data = get_order_data(sort_key if sort_key != 7 else 'o.status')
continue
for order in data:
if order['id'] == index:
clear()
show_order_detail_menu(order)
import unittest
from unittest.mock import patch
from pymysql import NULL
from src.DbController import DbController
from src.app import get_cats, show_add_item_menu, show_add_order_menu, show_delete_item_menu, show_update_item_menu, show_update_order_menu, show_update_status_menu
DbController('', '', '', '')
class TestDeleteItemMenu(unittest.TestCase):
@patch('src.app.DbController.get_all_rows')
@patch('src.app.get_validated_input')
@patch('src.app.DbController.delete')
@patch('src.app.dicts_to_table', lambda x: None)
@patch('src.app.input', lambda x: 'n')
@patch('src.app.clear', lambda: None)
def test_should_call_correct_delete(self, mock_delete, mock_valid_input,
mock_rows):
#Given
mock_valid_input.return_value = 1
mock_rows.return_value = [{'id': 1, 'name': 'gary', 'phone': '999'}]
get_key = 'test'
#When
show_delete_item_menu(get_key)
#Then
mock_delete.assert_called_with(get_key, 1)
def get_order_totals():
data = DbController.instance().call_proc('get_order_totals')
clear()
print(fmt_string('Viewing: TOTALS BY ORDER', fg='Cyan'))
dicts_to_table(data)
input(fmt_string('Press ENTER To Continue', fg='Green'))
lambda: show_menu('main_menu'), get_summary_by_status,
get_order_totals, get_unassigned_orders, get_free_couriers
]
}
}
# Start Loop
menu_state = 'main_menu'
if __name__ == '__main__':
host = os.environ.get("mysql_host")
user = os.environ.get("mysql_user")
password = os.environ.get("mysql_pass")
database = os.environ.get("mysql_db")
DbController(host, user, password, database) # type: ignore
# If you can't connect to the DB, log, inform user and close the app
if not DbController.instance().connection:
log('critical', 'No Connection To Source Could Be Established')
print(host, user, password, database)
print(
f'\u001b[37;1m\u001b[41;1mNo Connection To Source Could Be Established. Please Review Config\u001b[0m'
)
input()
while menu_state:
show_menu(menu_state)
DbController.instance().close()
# endregion :=Setup
def update_catagory_mapping():
is_in_cat = True
while is_in_cat:
cats = DbController.instance().get_all_rows('catagories', '*')
cat_ids = [item['id'] for item in cats]
clear()
dicts_to_table(cats)
selected_catagory = get_validated_input(
'Please Select A Catagory To Update: ',
int,
fg='Blue',
is_present=cat_ids,
cancel_on='0')
if not selected_catagory:
is_in_cat = False
continue
is_editing = True
while is_editing:
data = DbController.instance().get_rows_where(
'products', 'name, id', 'catagory', selected_catagory)
product_ids = [
item['id'] for item in DbController.instance().get_all_rows(
'products', 'id')
]
list_names = ['*' + item['name'] for item in data]
list_ids = [item['id'] for item in data]
clear()
list_to_table(list_names, "Current Menu", max_length=3)
dicts_to_table(get_cats(DbController.instance().get_all_rows(
'products', '*')),
paginate=True,
page_length=10,
on_clear=lambda: list_to_table(
list_names, "Current Menu", max_length=3))
selected_item = get_validated_input(
'Please Select An Item To Add: ',
int,
fg='Blue',
is_present=product_ids,
cancel_on='0',
cancel_text='GO BACK')
if not selected_item:
is_editing = False
continue
if selected_catagory == 9 and selected_item in list_ids:
input(
fmt_string(
'That Item Is Already In This Menu, But You Can Not Remove From The Default Menu',
fg='White',
bg='Red'))
continue
elif selected_item in list_ids:
if input(
fmt_string(
'That Item Is Already In This Menu. Would You Like To Remove It [y/n]? \n',
fg='White',
bg='Red')) == 'y':
DbController.instance().update('products', selected_item,
{'catagory': 9})
else:
DbController.instance().update('products', selected_item,
{'catagory': selected_catagory})
def select_order_items(order_id) -> None:
current_basket = list(DbController.instance().get_joins_where(
source='basket b',
fields=['p.id', 'p.name', 'b.quantity'],
targets=['products p'],
conditions=['b.item = p.id'],
where=f'b.order_id = {order_id}'))
current_rows = DbController.instance().get_rows_where(
'basket', '*', 'order_id', order_id)
current_ids = [item['item'] for item in current_rows]
catagories = DbController.instance().get_all_rows('catagories', '*')
catagory_ids = [cat['id'] for cat in catagories]
to_update = []
to_insert = []
to_delete = []
is_in_cat = True
while is_in_cat:
clear()
if len(current_basket) > 0:
dicts_to_table(current_basket)
print(fmt_string(f'Updating Basket For Order {order_id}...',
fg='Cyan'))
dicts_to_table(catagories)
catagory = get_validated_input('Please Select A Catagory: ',
int,
fg='Blue',
is_present=catagory_ids,
cancel_on='0',
cancel_text='SKIP')
if not catagory:
is_in_cat = False
continue
is_in_product = True
while (is_in_product):
clear()
# Reconcile duplicate records -> only affects locally
if len(current_basket) > 0:
for i in range(len(current_basket) - 1):
for j in range(i + 1, len(current_basket)):
if current_basket[i]['id'] == current_basket[j]['id']:
current_basket[i]['quantity'] += current_basket[j][
'quantity']
del current_basket[j]
if current_basket[i]['quantity'] <= 0:
del current_basket[i]
dicts_to_table(current_basket)
products = DbController.instance().get_rows_where(
'products', 'id, name', 'catagory', catagory)
product_ids = [item['id'] for item in products]
print(
fmt_string(f'Updating Basket For Order {order_id}...',
fg='Cyan'))
dicts_to_table(products)
product = get_validated_input('Please Select An Item: ',
int,
fg='Blue',
is_present=product_ids,
cancel_on='0',
cancel_text='GO BACK')
if not product:
is_in_product = False
continue
quantity = get_validated_input('Please Enter A Quantity: ',
int,
fg='Blue',
cancel_on='0',
cancel_text='GO BACK')
if product in current_ids:
for item in current_rows:
if item['item'] == product:
if item['quantity'] + quantity <= 0:
to_delete.append(item)
current_rows.remove(item)
for row in current_basket:
print(row['id'], product)
if row['id'] == product:
current_basket.remove(row)
else:
item['quantity'] += quantity
to_update.append(item)
for row in current_basket:
if row['id'] == product:
row['quantity'] += quantity
else:
to_insert.append({
'order_id': order_id,
'item': product,
'quantity': quantity
})
for row in products:
if row['id'] == product:
current_basket.append({
'id': product,
'name': row['name'],
'quantity': quantity
})
# reconcile updates to additions -> would affect table
for i in range(len(to_insert) - 1):
for j in range(i + 1, len(to_insert)):
if to_insert[i]['item'] == to_insert[j]['item']:
to_insert[i]['quantity'] += to_insert[j]['quantity']
del to_insert[j]
if to_insert[i]['quantity'] <= 0:
del to_insert[i]
for record in to_update:
DbController.instance().update_where('basket', ['order_id', 'item'],
[order_id, record['item']],
record)
for record in to_delete:
DbController.instance().delete_where('basket', ['order_id', 'item'],
[order_id, record['item']])
for record in to_insert:
DbController.instance().insert('basket', record)
import unittest
from unittest.mock import patch
from src.DbController import DbController
DbController('', '', '', '')
instance = DbController.instance()
class TestSingleton(unittest.TestCase):
def test_instance_is_same(self):
#Given
x = DbController('1', '2', '3', '4')
#When / Then
self.assertIs(instance, x)
@patch('src.DbController.DbController.execute')
class TestGetFieldNames(unittest.TestCase):
def test_should_get_correct_names(self, mock_execute):
#Given
mock_execute.return_value = [{'Field': 'id'}, {'Field': 'name'}]
expected = ['name']
table = 'test'
#When
actual = instance.get_field_names(table)
#Then
self.assertEqual(expected, actual)
def show_update_order_menu() -> None:
data = get_order_data()
current_ids = [item['id'] for item in data]
status_list = DbController.instance().get_all_rows('status', '*')
status_ids = [status['id'] for status in status_list]
courier_list = DbController.instance().get_all_rows('couriers', '*')
courier_ids = [courier['id'] for courier in courier_list]
is_looping = True
while is_looping:
clear()
dicts_to_table(data)
id = get_validated_input('Please Enter An ID To Edit: ',
int,
fg='Blue',
min_length=1,
is_present=current_ids,
cancel_on='0')
if not id:
return
order = DbController.instance().get_joins_where(
fields=[
'o.id', 'o.name', 'o.address', 'o.area', 'o.phone',
'courier.name AS courier', 's.code AS status'
],
source='orders o',
targets=['couriers courier', 'status s'],
conditions=['courier.id = o.courier', 's.id = o.status'],
where=f'o.id = {id}',
order='ORDER BY o.status')
clear()
dicts_to_table(order)
schema = {k.lower(): type(v) for (k, v) in data[0].items()}
schema['courier'] = int
schema['status'] = int
validation = {
'all': {
'min_length': 0,
'cancel_on': '',
'cancel_text': 'SKIP',
'fg': 'Blue'
},
'status': {
'is_present': status_ids,
'cancel_on': '0'
},
'courier': {
'is_present': courier_ids,
'cancel_on': '0'
}
}
on_key = {
'status': [
clear, lambda: dicts_to_table(order),
lambda: dicts_to_table(status_list)
],
'courier': [
clear, lambda: dicts_to_table(order),
lambda: dicts_to_table(courier_list)
]
}
new_dict = dict_builder(schema, ['id'],
validation,
on_key=on_key,
on_cancel='skip')
if new_dict:
DbController.instance().update('orders', id, new_dict)
select_order_items(id)
clear()
data = get_order_data()
dicts_to_table(data)
if input(
fmt_string(
'Item SuccessFully Updated. Would You Like To Update Another?[y/n]\n',
fg='Green')) == 'n':
is_looping = False
def test_instance_is_same(self):
#Given
x = DbController('1', '2', '3', '4')
#When / Then
self.assertIs(instance, x)
def get_order(id: int):
query_res = DbController.instance().get_joins_where(
fields=[
'b.quantity AS "#x"', 'p.name',
'b.quantity * p.price AS "Sub Total"'
],
source='orders o',
targets=['basket b', 'products p'],
conditions=[f'b.order_id = {id}', 'b.item = p.id'],
where=f'o.id = {id}',
type='INNER')
res = app.response_class(response=json.dumps(query_res),
status=200,
mimetype='application/json')
res.headers["Access-Control-Allow-Origin"] = '*'
return res
@app.errorhandler(404)
def not_found(error):
return error
host = os.environ.get("mysql_host")
user = os.environ.get("mysql_user")
password = os.environ.get("mysql_pass")
database = os.environ.get("mysql_db")
DbController(host, user, password, database) #type: ignore
