def associate_interface(conn, user):
cursor = conn.cursor()
bar = str("\n" + ("-" * 25) + "\n")
command = ""
command_list = str(
"select an option from the commands below:\n" +
"\t(commands are case sensitive)\n" + (Fore.GREEN if cm else "") +
"exit" + (Fore.RESET if cm else "") + ": exit the program\n" +
(Fore.GREEN if cm else "") + "help" + (Fore.RESET if cm else "") +
": display commands list\n" + (Fore.GREEN if cm else "") + "checkout" +
(Fore.RESET if cm else "") + ": checkout a customer\n" +
(Fore.GREEN if cm else "") + "order" + (Fore.RESET if cm else "") +
": view orders and order new inventory items\n" +
(Fore.GREEN if cm else "") + "stock" + (Fore.RESET if cm else "") +
": add received items to the inventory\n" + "")
print(command_list)
while True:
command = get_cmd()
if (engine.quit(command)):
break
#
elif command == "help":
print(command_list)
continue
#
elif command == "checkout":
order.checkout(conn, user)
print(command_list)
continue
#end command == checkout
elif command == "order":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM inventory WHERE store_id='" +
str(user.store_id) +
"' ORDER BY stock ASC").fetchall(),
cursor.description)
order.reorder(conn, user)
print(command_list)
continue
#
elif command == "stock":
order.restock(conn, user)
print(command_list)
continue
#
else:
print("Invalid command entered.")
print(command_list)
def customer_interface(conn, user):
cursor = conn.cursor()
bar = str("\n" + ("-" * 25) + "\n")
command = ""
command_list = str("select an option from the commands below:\n" +
"\t(commands are case sensitive)\n" +
(Fore.GREEN if cm else "") + "exit" + (Fore.RESET if cm else "") + ": exit the program\n" +
(Fore.GREEN if cm else "") + "help" + (Fore.RESET if cm else "") + ": display commands list\n" +
(Fore.GREEN if cm else "") + "checkout" + (Fore.RESET if cm else "") + ": purchase items\n" +
(Fore.GREEN if cm else "") + "inventory" + (Fore.RESET if cm else "") + ": view items available for purchase\n" +
"")
print(command_list)
while True:
command = get_cmd()
if (engine.quit(command)):
break
#
elif command == "help":
print(command_list)
continue
#
elif command == "checkout":
order.checkout(conn, user)
print(command_list)
continue
#end command == checkout
elif command == "inventory":
engine.print_cursor_fetch(cursor.execute("SELECT * FROM inventory WHERE store_id='" + str(user.store_id) +
"' ORDER BY sold_last_month DESC").fetchall(), cursor.description)
continue
#
else:
print("Invalid command entered.")
print(command_list)
#End while command != exit
#end customer_interface()
def checkout(conn, user):
cursor = conn.cursor()
cart = []
grand_total = 0.00
while True:
#get inventory id for items to order
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "Inventory ID" + (Fore.RESET if cm else "") +
" of the item you'd add.\n" +
"Enter " +
(Fore.CYAN if cm else "") + "cancel" + (Fore.RESET if cm else "") +
" to exit.\n" +
"Enter " +
(Fore.CYAN if cm else "") + "done" + (Fore.RESET if cm else "") +
" when complete.")
if (engine.quit(input, "Exiting checkout mode.")):
input = "_BREAKLOOP_"
break
elif input == "done":
break
elif int(input) not in [i[0] for i in cursor.execute("SELECT id FROM inventory WHERE " +
"id = '" + input + "';").fetchall()]:
print((Fore.RED if cm else "") +
"Error: inventory item " + str(input) + " not found." +
(Fore.RESET if cm else ""))
continue #got to top of input loop
else: #not done, not exit, and inventory item is found; add to list
cart.append(input)
print("Item " +
(Fore.GREEN if cm else "") + input + (Fore.RESET if cm else "") +
" added to purchase!")
if input == "_BREAKLOOP_": #if canceling purchase
return #break out of checkout mode
#end while True
#get customer info
input = get_cmd("Would the customer like to use their membership ID? Enter the " +
(Fore.CYAN if cm else "") + "Customer ID" + (Fore.RESET if cm else "") +
" or enter " +
(Fore.CYAN if cm else "") + "no" + (Fore.RESET if cm else "") + ".")
if (engine.quit(input, "Exiting checkout mode.")):
return
elif (input.lower() == "no"):
customer_id = "NULL"
else:
customer_id = input
while int(input) not in [i[0] for i in cursor.execute("SELECT id FROM customer WHERE " +
"id = '" + input + "';").fetchall()]:
input = get_cmd("Customer ID not found, please re-enter Customer ID, or type "+
(Fore.GREEN if cm else "") + "cancel" + (Fore.RESET if cm else "") +
" to cancel.")
customer_id = input
if (engine.quit(input), "Not using Customer ID, get CC info."):
customer_id = "NULL"
break
#end get customer id
#get cc info
if customer_id != "NULL":
input = get_cmd("Would the customer like to use new CC or charge or their card on file? Enter the " +
(Fore.CYAN if cm else "") + "card" + (Fore.RESET if cm else "") +
" or enter " +
(Fore.CYAN if cm else "") + "account" + (Fore.RESET if cm else "") + ".")
if (engine.quit(input, "Exiting checkout mode.")):
return
if ((customer_id == "NULL") or (input == "card")):
input = get_cmd("Enter the customer's " +
(Fore.CYAN if cm else "") + "CC number" + (Fore.RESET if cm else "") + ".")
if (engine.quit(input, "Exiting checkout mode.")):
return
customer_cc = str(input)
input = get_cmd("Enter the customer's " +
(Fore.CYAN if cm else "") + "CC expiration date" + (Fore.RESET if cm else "") + ".")
if (engine.quit(input, "Exiting checkout mode.")):
return
customer_cc_exp = str(input)
elif input == "account":
customer_cc = str(engine.get_cell(conn, "card_number", "customer", "id", customer_id))
customer_cc_exp = str(engine.get_cell(conn, "card_exp", "customer", "id", customer_id))
else:
print((Fore.RED if cm else "") +
"Error inputing CC information. CC set to NULL, contact manager." +
(Fore.RESET if cm else "") )
customer_cc = str("NULL")
customer_cc_exp = str("NULL")
#end get CC info
#generate itemization id
#find id that is unique
untrimmed_itemization_list = glob.glob(str(".\\itemization\\*.csv"))
itemization_list = []
for each_item in untrimmed_itemization_list:
itemization_list.append(str(each_item.replace(".\\itemization\\", "").replace(".csv", "")))
while True:
item_id = random.randint(11111111, 99999999)
item_id = str("i" + str(item_id))
if item_id in itemization_list:
continue #if exists, try again
else:
break #if unique, move on
#
#create itemization table
try:
query = str(engine.get_itemization_query(item_id))
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error building itemization table for " + str(item_id) + ":" +
(Fore.RESET if cm else ""))
print(query)
print(error)
else:
#add each item in cart to itemized table
for each_item_id in cart:
try:
this_row_id = str(random.randint(11111111, 99999999)) #get random id for item row
this_row_category = str(engine.get_cell(conn, "category", "inventory", "id", each_item_id)) #get category from inventory table
this_row_item_id = str(engine.get_cell(conn, "item_id", "inventory", "id", each_item_id)) #get item_id from inventory table
this_row_price = str(engine.get_cell(conn, "price", this_row_category, "id", this_row_item_id)) #get quantity to be ordered
query = str("INSERT INTO " + item_id + " VALUES ('401" +
str(this_row_id) + "', '" +
str(this_row_category) + "', '" +
str(this_row_item_id) + "', '" +
str("1") + "', '" +
str(this_row_price) +
"');")
print(query) #debugging
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error populating itemization table for " + str(item_id) + ":" +
(Fore.RESET if cm else ""))
print(query)
print(error)
else:
grand_total = float(float(grand_total) + float(this_row_price))
#end adding to table
#add purchase to purchases table
try:
#get unique order id
while True:
this_purchase_id = random.randint(11111111, 99999999)
if this_purchase_id in [i[0] for i in cursor.execute("SELECT id FROM purchases;")]:
continue #if exists, try again
else:
break #if unique, move on
#
"""
From pruchases schema:
id;customer_id;store_id;cc_number;cc_expiration;itemization_id;grand_total;date
int;int;int;int;date;varchar(255);float(12,2);date
"""
query = str("INSERT INTO purchases VALUES ('" +
str(this_purchase_id) + "', '" +
str(customer_id) + "', '" +
str(user.store_id) + "', '" +
str(customer_cc) + "', '" +
str(customer_cc_exp) + "', '" +
str(item_id) + "', '" +
str(grand_total) + "', '" +
str(date.today()) +
"');")
for each_item in cart:
print("Buying item " +
str(engine.get_cell(conn, "name", "inventory", "id", each_item)) +
"...")
print(query) #debugging
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error populating puchases table for " + str(this_purchase_id) + ":" +
(Fore.RESET if cm else ""))
print(error)
print("\nGrand total for the purchase is:\n" + (Fore.GREEN if cm else "") +
"$" + str(round(grand_total,2)) + (Fore.RESET if cm else "") + "\n")
#end checkout()
def reorder(conn, user):
cursor = conn.cursor()
#get inventory id for items to order
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "Inventory ID" + (Fore.RESET if cm else "") +
" of the item you'd like to order.")
if (engine.quit(input, "Exiting order mode.")):
return
while int(input) not in [i[0] for i in cursor.execute("SELECT id FROM inventory WHERE " +
"id = '" + input + "';").fetchall()]:
input = get_cmd("Inventory ID not found, please re-enter Inventory ID, or type "+
(Fore.GREEN if cm else "") + "cancel" + (Fore.RESET if cm else "") +
" to cancel.")
if (engine.quit(input), "Exiting order mode."):
return
#end while id not found
#once id is found
reorder_id = int(input)
#get quantity
while True:
try:
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "quantity" + (Fore.RESET if cm else "") +
" of the item you'd like to order.")
if (engine.quit(input, "Exiting order mode.")):
return
#
input = int(input)
except ValueError as error:
print("Error, please enter an integer.")
continue
else:
reorder_quantity = int(input)
break
#end get quantity
#output suppliers for user reference
print("Available Suppliers:")
engine.print_cursor_fetch(cursor.execute("SELECT * FROM supplier;").fetchall(), cursor.description)
print()
#get supplier id for items to order
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "Supplier ID" + (Fore.RESET if cm else "") +
" you would like to order from.")
if (engine.quit(input, "Exiting order mode.")):
return
while int(input) not in [i[0] for i in cursor.execute("SELECT id FROM supplier WHERE " +
"id = '" + input + "';").fetchall()]:
input = get_cmd("Supplier ID not found, please re-enter Supplier ID, or type "+
(Fore.GREEN if cm else "") + "cancel" + (Fore.RESET if cm else "") +
" to cancel.")
if (engine.quit(input), "Exiting order mode."):
return
#end while supplier id not found
supplier_id = int(input)
#generate itemization id
#find id that is unique
untrimmed_itemization_list = glob.glob(str(".\\itemization\\*.csv"))
itemization_list = []
for each_item in untrimmed_itemization_list:
itemization_list.append(str(each_item.replace(".\\itemization\\", "").replace(".csv", "")))
while True:
item_id = random.randint(11111111, 99999999)
item_id = str("i" + str(item_id))
if item_id in itemization_list:
continue #if exists, try again
else:
break #if unique, move on
#
#create itemization table
try:
query = str(engine.get_itemization_query(item_id))
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error building itemization table for " + str(item_id) + ":" +
(Fore.RESET if cm else ""))
print(query)
print(error)
else:
try:
this_row_id = str(random.randint(11111111, 99999999)) #get random id for item row
this_row_category = str(engine.get_cell(conn, "category", "inventory", "id", reorder_id)) #get category from inventory table
this_row_item_id = str(engine.get_cell(conn, "item_id", "inventory", "id", reorder_id)) #get item_id from inventory table
this_row_price = str(engine.get_cell(conn, "price", this_row_category, "id", this_row_item_id)) #get quantity to be ordered
query = str("INSERT INTO " + item_id + " VALUES ('401" +
str(this_row_id) + "', '" +
str(this_row_category) + "', '" +
str(this_row_item_id) + "', '" +
str(reorder_quantity) + "', '" +
str(this_row_price) +
"');")
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error populating itemization table for " + str(item_id) + ":" +
(Fore.RESET if cm else ""))
print(query)
print(error)
else:
#add order to orders table
try:
#get unique order id
while True:
this_order_id = random.randint(11111111, 99999999)
if this_order_id in [i[0] for i in cursor.execute("SELECT id FROM orders;")]:
continue #if exists, try again
else:
break #if unique, move on
#
grand_total = float(float(reorder_quantity) * float(this_row_price))
query = str("INSERT INTO orders VALUES ('" +
str(this_order_id) + "', '" +
str(date.today()) + "', '" +
str(user.store_id) + "', '" +
str(supplier_id) + "', '" +
str(user.id) + "', '" +
str(item_id) + "', '" +
str(grand_total) +
"');")
print("Ordering " + str(reorder_quantity) + " of item " +
str(engine.get_cell(conn, "name", "inventory", "id", reorder_id)) +
"...")
print(query)
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error populating order table for " + str(this_order_id) + ":" +
(Fore.RESET if cm else ""))
print(error)
def restock(conn, user):
"""restock items in inventory"""
cursor = conn.cursor()
engine.print_cursor_fetch(cursor.execute("SELECT * FROM inventory WHERE store_id='" + str(user.store_id) +
"' ORDER BY stock ASC").fetchall(), cursor.description)
#get inventory id for items to add
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "Inventory ID" + (Fore.RESET if cm else "") +
" of the item you'd like to restock.")
if (engine.quit(input, "Exiting order mode.")):
return
while int(input) not in [i[0] for i in cursor.execute("SELECT id FROM inventory WHERE " +
"id = '" + input + "';").fetchall()]:
input = get_cmd("Inventory ID not found, please re-enter Inventory ID, or type "+
(Fore.GREEN if cm else "") + "cancel" + (Fore.RESET if cm else "") +
" to cancel.")
if (engine.quit(input), "Exiting order mode."):
return
#end while id not found
#once id is found
restock_id = int(input)
#get quantity
while True:
try:
input = get_cmd("Enter the " +
(Fore.CYAN if cm else "") + "quantity" + (Fore.RESET if cm else "") +
" of the item you'd like to restock.")
if (engine.quit(input, "Exiting order mode.")):
return
#
input = int(input)
except ValueError as error:
print("Error, please enter an integer.")
continue
else:
restock_quantity = int(input)
break
#end get quantity
restock_quantity = int( int(restock_quantity) + int(engine.get_cell(conn, "stock", "inventory", "id", restock_id)) )
try:
query = str("UPDATE inventory SET stock = '" + str(restock_quantity) +
"' WHERE id = '" + str(restock_id) + "';")
cursor.execute(query)
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error restocking inventory item " + str(restock_id) + ":" +
(Fore.RESET if cm else ""))
print(query)
print(error)
else:
print("Successfully added stock.")
engine.print_cursor_fetch(cursor.execute("SELECT * FROM inventory WHERE store_id='" + str(user.store_id) +
"' ORDER BY stock ASC").fetchall(), cursor.description)
def edit_suppliers(conn, user):
cursor = conn.cursor()
command_list = str("select an option from the commands below:\n" +
"\t(commands are case sensitive)\n" +
(Fore.GREEN if cm else "") + "supp" +
(Fore.RESET if cm else "") + ": view suppliers\n" +
(Fore.GREEN if cm else "") + "add_supp" +
(Fore.RESET if cm else "") + ": add a new supplier\n" +
(Fore.GREEN if cm else "") + "remove_supp" +
(Fore.RESET if cm else "") + ": remove a supplier\n" +
(Fore.GREEN if cm else "") + "ship" +
(Fore.RESET if cm else "") + ": view shippers\n" +
(Fore.GREEN if cm else "") + "add_ship" +
(Fore.RESET if cm else "") + ": add a new shipper\n" +
(Fore.GREEN if cm else "") + "remove_ship" +
(Fore.RESET if cm else "") + ": remove a shipper\n" +
"")
command = ""
while (command != "exit"):
print(command_list)
command = get_cmd()
if (engine.quit(command)):
continue
elif command == "help":
print(command_list)
print()
continue
elif command == "supp":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM supplier").fetchall(),
cursor.description)
print()
continue
#
elif command == "add_supp":
new_values = ""
#get supplier ID
id_found = False
while not id_found:
print("You may type " + (Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " at any time.\n" +
"Enter new " + (Fore.CYAN if cm else "") +
"supplier ID" + (Fore.RESET if cm else "") +
" or enter " + (Fore.GREEN if cm else "") + "random" +
(Fore.RESET if cm else "") +
" to generate a new unique id.")
new_id = get_cmd()
if engine.quit(new_id, "Exiting suppliers mode.\n"):
return
elif new_id == "random":
while not id_found:
new_id = random.randint(10001, 99998)
if int(new_id) in [
i[0] for i in cursor.execute(
"SELECT id FROM supplier")
]:
continue
else:
id_found = True
else:
try:
new_id = int(new_id)
except:
print("ID must be an integer")
continue
else:
if int(new_id) in [
i[0] for i in cursor.execute(
"SELECT id FROM supplier")
]:
print("ALERT: this ID already exists.\n")
else:
id_found = True
#end get supplier id
new_values = new_values + "'" + str(new_id) + "', "
"""from /schemas/supplier.csv:
id;name;address_number;address_street;address_city;address_zip;email;phone_number
int;varchar(255);int;varchar(255);varchar(255);int;varchar(255);int
"""
next_attributes = [
"Name", "Street Number", "Street", "City", "Zip",
"Contact Email", "Phone number"
]
for each_attribute in next_attributes:
input = get_cmd("Enter " + (Fore.CYAN if cm else "") +
each_attribute + (Fore.RESET if cm else "") +
" or " + (Fore.GREEN if cm else "") + "NULL" +
(Fore.RESET if cm else "") +
" if unknown, or enter " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else ""))
if engine.quit(input, "Exiting suppliers mode."):
return
else:
new_values = new_values + "'" + str(input) + "', "
#
#remove last comma
new_values = new_values[:-2]
#add to database
print((Back.CYAN if cm else "") +
"Adding new supplier to database..." +
(Back.RESET if cm else ""))
try:
cursor.execute("INSERT INTO supplier VALUES (" + new_values +
");")
except sqlite3.Error as error:
print((Fore.RED if cm else "") + "ERROR: " +
(Fore.RESET if cm else "") +
"SQL error found in default.py > hire_mode():\n" +
str(error))
else:
print("New supplier added!")
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM supplier WHERE id='" +
str(new_id) + "'").fetchall(),
cursor.description)
continue
#end command == add_supp
elif command == "remove_supp":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM supplier").fetchall(),
cursor.description)
print()
removed_id = get_cmd("Enter the " + (Fore.RED if cm else "") +
"Supplier ID " + (Fore.RESET if cm else "") +
" of the supplier to remove")
if engine.quit(removed_id, "Exiting suppliers mode."):
return
else:
if int(removed_id) in [
i[0] for i in cursor.execute("SELECT id FROM supplier")
]:
print(
(Fore.RED if cm else "") + "ATTENTION! " +
(Fore.RESET if cm else "") +
"You about to remove the following supplier from the database:"
)
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM supplier WHERE id='" +
str(removed_id) + "'").fetchall(),
cursor.description)
print()
confirm = get_cmd(
"Enter YES to continue, any other input will cancel.")
if (confirm != "YES"):
print("Exiting suppliers mode.\n")
return
try:
cursor.execute("DELETE FROM supplier WHERE id='" +
str(removed_id) + "'")
except sqlite3.Error as error:
print(
"SQL Error found in admin.py > edit_suppliers()\n"
+ error)
else:
print("Supplier removed from database.\n")
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM supplier").fetchall(),
cursor.description)
print()
else:
print("Supplier ID not found.\n")
continue
elif command == "ship":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM shippers").fetchall(),
cursor.description)
print()
continue
elif command == "add_ship":
new_values = ""
#get supplier ID
id_found = False
while not id_found:
print("You may type " + (Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " at any time.\n" +
"Enter new " + (Fore.CYAN if cm else "") + "shipper ID" +
(Fore.RESET if cm else "") + " or enter " +
(Fore.GREEN if cm else "") + "random" +
(Fore.RESET if cm else "") +
" to generate a new unique id.")
new_id = get_cmd()
if engine.quit(new_id, "Exiting suppliers mode.\n"):
return
elif new_id == "random":
while not id_found:
new_id = random.randint(10001, 99998)
if int(new_id) in [
i[0] for i in cursor.execute(
"SELECT id FROM shippers")
]:
continue
else:
id_found = True
else:
try:
new_id = int(new_id)
except:
print("ID must be an integer")
continue
else:
if int(new_id) in [
i[0] for i in cursor.execute(
"SELECT id FROM shippers")
]:
print("ALERT: this ID already exists.\n")
else:
id_found = True
#end get employee id
new_values = new_values + "'" + str(new_id) + "', "
"""from /schemas/supplier.csv:
id;shipper_name;shipper_account_number;phone_number;email;address_number;address_street_name;address_city;address_zip_code
int;varchar(256);int;int;varchar(256);int;varchar(256);varchar(256);int;int
"""
next_attributes = [
"Name", "Account Number", "Phone number", "Contact Email",
"Street Number", "Street", "City", "Zip"
]
for each_attribute in next_attributes:
input = get_cmd("Enter " + (Fore.CYAN if cm else "") +
each_attribute + (Fore.RESET if cm else "") +
" or " + (Fore.GREEN if cm else "") + "NULL" +
(Fore.RESET if cm else "") +
" if unknown, or enter " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else ""))
if engine.quit(input, "Exiting suppliers mode."):
return
else:
new_values = new_values + "'" + str(input) + "', "
#
#remove last comma
new_values = new_values[:-2]
#add to database
print((Back.CYAN if cm else "") +
"Adding new shipper to database..." +
(Back.RESET if cm else ""))
try:
cursor.execute("INSERT INTO shippers VALUES (" + new_values +
");")
except sqlite3.Error as error:
print((Fore.RED if cm else "") + "ERROR: " +
(Fore.RESET if cm else "") +
"SQL error found in default.py > hire_mode():\n" +
str(error))
else:
print("New shipper added!")
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM shippers WHERE id='" +
str(new_id) + "'").fetchall(),
cursor.description)
continue
#end command == add_ship
elif command == "remove_ship":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM shippers").fetchall(),
cursor.description)
print()
removed_id = get_cmd("Enter the " + (Fore.RED if cm else "") +
"Shipper ID " + (Fore.RESET if cm else "") +
" of the shipper to remove")
if engine.quit(removed_id, "Exiting suppliers mode."):
return
else:
if int(removed_id) in [
i[0] for i in cursor.execute("SELECT id FROM shippers")
]:
print(
(Fore.RED if cm else "") + "ATTENTION! " +
(Fore.RESET if cm else "") +
"You about to remove the following shipper from the database:"
)
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM shippers WHERE id='" +
str(removed_id) + "'").fetchall(),
cursor.description)
print()
confirm = get_cmd(
"Enter YES to continue, any other input will cancel.")
if (confirm != "YES"):
print("Exiting suppliers mode.\n")
return
try:
cursor.execute("DELETE FROM shippers WHERE id='" +
str(removed_id) + "'")
except sqlite3.Error as error:
print(
"SQL Error found in admin.py > edit_suppliers()\n"
+ error)
else:
print("Shipper removed from database.\n")
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM shippers").fetchall(),
cursor.description)
print()
else:
print("Shipper ID not found.\n")
continue
def hire_mode(conn, user=None):
"""guides an admin to adding a new user to the database"""
print(
"You are entering hire mode, this will add employees to the database.\n"
)
cursor = conn.cursor()
new_values = ""
"""from /schemas/employee.csv:
id;first_name;last_name;store_id;
ssn;phone_number;address_number;address_street;address_city;address_zip;
username;password;job_title;db_permission_level;
start_date;end_date;vacation_days_remaining;contract_id
"""
#get employee ID
id_found = False
while not id_found:
print("You may type " + (Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " at any time.\n" + "Enter new " +
(Fore.CYAN if cm else "") + "employee ID" +
(Fore.RESET if cm else "") + " or enter " +
(Fore.GREEN if cm else "") + "random" +
(Fore.RESET if cm else "") + " to generate a new unique id.")
new_id = get_cmd()
if engine.quit(new_id, "Exiting hire mode.\n"):
return
elif new_id == "random":
while not id_found:
new_id = random.randint(10001, 99998)
if int(new_id) in [
i[0] for i in cursor.execute("SELECT id FROM employee")
]:
continue
else:
id_found = True
else:
try:
new_id = int(new_id)
except:
print("ID must be an integer")
continue
else:
if int(new_id) in [
i[0] for i in cursor.execute("SELECT id FROM employee")
]:
print("ALERT: this ID already exists.\n")
else:
id_found = True
#end get employee id
new_values = new_values + "'" + str(new_id) + "', "
"""from /schemas/employee.csv:
id;first_name;last_name;store_id;
ssn;phone_number;address_number;address_street;address_city;address_zip;
username;password;job_title;db_permission_level;
start_date;end_date;vacation_days_remaining;contract_id
"""
next_attributes = [
"first name", "last name", "store ID", "ssn", "phone number",
"street number", "street name", "city", "zip"
]
for each_attribute in next_attributes:
input = get_cmd("Enter " + (Fore.CYAN if cm else "") + each_attribute +
(Fore.RESET if cm else "") + " or " +
(Fore.GREEN if cm else "") + "NULL" +
(Fore.RESET if cm else "") + " if unknown, or enter " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else ""))
if engine.quit(input, "Exiting hire mode."):
return
else:
new_values = new_values + "'" + str(input) + "', "
#
#get employee username
username_found = False
while not username_found:
print("You may type " + (Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " at any time.\n" +
"Enter new employee " + (Fore.CYAN if cm else "") + "username" +
(Fore.RESET if cm else "") + " or enter " +
(Fore.GREEN if cm else "") + "random" +
(Fore.RESET if cm else "") +
" to generate a new unique username.")
new_username = get_cmd()
if engine.quit(new_username, "Exiting hire mode."):
return
elif new_username == "random":
while not username_found:
new_username = random.randint(10001, 99998)
if str(new_username) in [
i[0] for i in cursor.execute(
"SELECT username FROM employee")
]:
continue
else:
username_found = True
else:
if str(new_username) in [
i[0]
for i in cursor.execute("SELECT username FROM employee")
]:
print("ALERT: this username already exists.\n")
else:
username_found = True
#end get employee username
new_values = new_values + "'" + str(new_username) + "', "
"""from /schemas/employee.csv:
id;first_name;last_name;store_id;
ssn;phone_number;address_number;address_street;address_city;address_zip;
username;password;job_title;db_permission_level;
start_date;end_date;vacation_days_remaining;contract_id
"""
next_attributes = ["password", "job title"]
for each_attribute in next_attributes:
input = get_cmd("Enter " + (Fore.CYAN if cm else "") + each_attribute +
(Fore.RESET if cm else "") + " or " +
(Fore.GREEN if cm else "") + "NULL" +
(Fore.RESET if cm else "") + " if unknown, or enter " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else ""))
if engine.quit(input, "Exiting hire mode."):
return
else:
new_values = new_values + "'" + str(input) + "', "
#
#get employee database access level
while input not in ["admin", "manager", "associate"]:
input = get_cmd("Enter database permission level from:\n" +
(Fore.GREEN if cm else "") + "admin" +
(Fore.RESET if cm else "") + "\n" +
(Fore.GREEN if cm else "") + "manager" +
(Fore.RESET if cm else "") + "\n" +
(Fore.GREEN if cm else "") + "associate" +
(Fore.RESET if cm else "") + "\n")
if engine.quit(input, "Exiting hire mode."):
return
#
new_values = new_values + "'" + str(input) + "', "
next_attributes = [
"start date", "end date", "vacation days", "contract ID"
]
for each_attribute in next_attributes:
input = get_cmd("Enter " + (Fore.CYAN if cm else "") + each_attribute +
(Fore.RESET if cm else "") + " or " +
(Fore.GREEN if cm else "") + "NULL" +
(Fore.RESET if cm else "") + " if unknown, or enter " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else ""))
if engine.quit(input, "Exiting hire mode."):
return
else:
new_values = new_values + "'" + str(input) + "', "
#
#remove last comma
new_values = new_values[:-2]
print((Back.CYAN if cm else "") + "Adding new employee to database..." +
(Back.RESET if cm else ""))
try:
cursor.execute("INSERT INTO employee VALUES (" + new_values + ");")
except sqlite3.Error as error:
print((Fore.RED if cm else "") + "ERROR: " +
(Fore.RESET if cm else "") +
"SQL error found in default.py > hire_mode():\n" + str(error))
else:
print("New employee added!")
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM employee WHERE id='" + str(new_id) +
"'").fetchall(), cursor.description)
print("Exiting hire mode.\n")
def admin_interface(conn, user):
cursor = conn.cursor()
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM inventory").fetchall(),
cursor.description) #debugging
print()
bar = str("\n" + ("-" * 25) + "\n")
command = ""
command_list = str(
"select an option from the commands below:\n" +
"\t(commands are case sensitive)\n" + (Fore.GREEN if cm else "") +
"exit" + (Fore.RESET if cm else "") + ": exit the program\n" +
(Fore.GREEN if cm else "") + "help" + (Fore.RESET if cm else "") +
": display commands list\n" + (Fore.GREEN if cm else "") + "orders" +
(Fore.RESET if cm else "") +
": view all orders made and amounts sold\n" +
(Fore.GREEN if cm else "") + "bestsellers" +
(Fore.RESET if cm else "") +
": view best selling items by location\n" +
(Fore.GREEN if cm else "") + "employ" + (Fore.RESET if cm else "") +
": hire or fire a manager or associate\n" +
#(Fore.GREEN if cm else "") + "pay" + (Fore.RESET if cm else "") + ": issue paychecks\n" +
(Fore.GREEN if cm else "") + "losses" + (Fore.RESET if cm else "") +
": check for lost or broken items\n" + (Fore.GREEN if cm else "") +
"suppliers" + (Fore.RESET if cm else "") +
": alter suppliers and shippers\n" + (Fore.GREEN if cm else "") +
"SQL" + (Fore.RESET if cm else "") + ": enter SQL query mode\n" + "")
print(command_list)
while True:
command = get_cmd()
if (engine.quit(command)):
break
elif command == "help":
print(command_list)
continue
#SQL MODE
elif command == "SQL":
print(bar)
print(
"Now entering SQL mode, all other commands are now invalid.\n"
+ "enter 'exit' to leave SQL mode.\n")
query = ""
while not engine.quit(query):
print((Fore.YELLOW if cm else ""))
query = input("Enter a SQL Query:\n")
print((Fore.RESET if cm else ""))
if engine.quit(query, "Now leaving SQL mode."):
print(command_list)
break
try:
cursor.execute(query)
except sqlite3.Error as error:
print("Error executive SQL query:\n" + str(error) + "\n" +
"Use the command 'exit' to leave SQL mode.\n")
else:
engine.print_cursor_fetch(cursor.fetchall(),
cursor.description)
#end while query != exit
print(bar)
continue
#End SQL mode
#hire/fire mode
elif command == "employ":
print(bar)
mode = ""
while ((mode != "fire") or (mode != "hire")):
print("Select from the following commmands:\n" +
(Fore.GREEN if cm else "") + "exit" +
(Fore.RESET if cm else "") + ": return to main menu\n" +
(Fore.GREEN if cm else "") + "hire" +
(Fore.RESET if cm else "") + ": add a new employee\n" +
(Fore.GREEN if cm else "") + "fire" +
(Fore.RESET if cm else "") + ": remove an employee\n")
mode = get_cmd()
if engine.quit(
mode,
"Exiting employ mode, type help to see commands."):
break
elif mode == "fire":
fire_mode(conn, user)
elif mode == "hire":
hire_mode(conn, user)
else:
print("Invalid command entered.")
print(bar)
continue
#end employ mode
#view all orders
elif command == "orders":
print(bar)
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM orders ORDER BY date DESC").fetchall(),
cursor.description)
print(bar)
continue
#
#view besetsellers
elif command == "bestsellers":
print(bar)
store_ids = [
i[0] for i in cursor.execute("SELECT id FROM stores;")
]
for each_store in store_ids:
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM inventory WHERE store_id='" +
str(each_store) +
"' ORDER BY sold_last_month DESC LIMIT 10").fetchall(),
cursor.description)
print()
print(bar)
continue
#
#view losses
elif command == "losses":
print(bar)
store_ids = [
i[0] for i in cursor.execute("SELECT id FROM stores;")
]
for each_store in store_ids:
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM inventory WHERE store_id='" +
str(each_store) +
"' ORDER BY damaged_lost DESC LIMIT 10").fetchall(),
cursor.description)
print()
print(bar)
continue
#
elif command == "suppliers":
print(bar)
edit_suppliers(conn, user)
print(bar)
continue
else:
print("Invalid command entered.")
print(command_list)
def fire_mode(conn, user=None):
"""fire mode to remove an employee"""
print(
(Fore.RED if cm else "") + "ATTENTION! " + (Fore.RESET if cm else "") +
"You are entering fire mode, this will PERMANENTLY remove employees from the database.\n"
)
cursor = conn.cursor()
confirm = get_cmd("Enter YES to continue, any other input will cancel.")
if (confirm != "YES"):
print("Exiting fire mode.\n")
return
attributes = str(
"id, first_name, last_name, store_id, phone_number, username, job_title, contract_id"
)
engine.print_cursor_fetch(
cursor.execute("SELECT " + attributes + " FROM employee").fetchall(),
cursor.description)
print()
fired_id = get_cmd(
"Enter the employee ID to fire employee, or type cancel..")
if engine.quit(fired_id, "Exiting fire mode."):
return
else:
if int(fired_id) in [
i[0] for i in cursor.execute("SELECT id FROM employee")
]:
print(
(Fore.RED if cm else "") + "ATTENTION! " +
(Fore.RESET if cm else "") +
"You about to remove the following employee from the database:"
)
engine.print_cursor_fetch(
cursor.execute("SELECT " + attributes +
" FROM employee WHERE id='" + str(fired_id) +
"'").fetchall(), cursor.description)
print()
confirm = get_cmd(
"Enter YES to continue, any other input will cancel.")
if (confirm != "YES"):
print("Exiting fire mode.\n")
return
try:
cursor.execute("DELETE FROM employee WHERE id='" +
str(fired_id) + "'")
except sqlite3.Error as error:
print("SQL Error found in admin.py > fire_mode()\n" + error)
else:
print("Employee removed from database.\n")
engine.print_cursor_fetch(
cursor.execute("SELECT " + attributes +
" FROM employee").fetchall(),
cursor.description)
print()
else:
print("Employee ID not found.\n")
print("Exiting fire mode.\n")
print((Back.MAGENTA) if cm else "", end="")
print("Welcome to Bryan Electronics Database Management System!")
print((Back.RESET) if cm else "", end="")
#find username
user_found = False
user_type = ""
if debugging:
username = debugging_username
user_type = debugging_usertype
user_found = True
#
while not user_found:
username = get_cmd("Please enter your username:\n")
#search default users
if username in user.default_users.keys():
user_found = True
user_type = "default"
continue
#search employees
#search python list of usernames from employee, generated by SQL query
if username in [
i[0] for i in cursor.execute(
"SELECT username FROM employee;").fetchall()
]:
user_found = True
user_type = str(
def manager_interface(conn, user):
cursor = conn.cursor()
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM inventory WHERE store_id = '" +
str(user.store_id) + "';").fetchall(),
cursor.description)
print()
bar = str("\n" + ("-" * 25) + "\n")
command = ""
command_list = str(
"select an option from the commands below:\n" +
"\t(commands are case sensitive)\n" + (Fore.GREEN if cm else "") +
"exit" + (Fore.RESET if cm else "") + ": exit the program\n" +
(Fore.GREEN if cm else "") + "help" + (Fore.RESET if cm else "") +
": display commands list\n" + (Fore.GREEN if cm else "") +
"bestsellers" + (Fore.RESET if cm else "") +
": view best selling items at your location\n" +
(Fore.GREEN if cm else "") + "employ" + (Fore.RESET if cm else "") +
": hire or fire an associate\n" + (Fore.GREEN if cm else "") +
"order" + (Fore.RESET if cm else "") +
": view orders and order new inventory items\n" +
(Fore.GREEN if cm else "") + "stock" + (Fore.RESET if cm else "") +
": add received items to the inventory\n" + "")
print(command_list)
while True:
command = get_cmd()
if (engine.quit(command)):
break
#
elif command == "help":
print(command_list)
continue
#
elif command == "bestsellers":
print(bar)
engine.print_cursor_fetch(
cursor.execute(
"SELECT * FROM inventory WHERE store_id='" +
str(user.store_id) +
"' ORDER BY sold_last_month DESC LIMIT 10").fetchall(),
cursor.description)
print(bar)
continue
#
#hire/fire mode
elif command == "employ":
print(bar)
mode = ""
while ((mode != "fire") or (mode != "hire")):
print("Select from the following commmands:\n" +
(Fore.GREEN if cm else "") + "exit" +
(Fore.RESET if cm else "") + ": return to main menu\n" +
(Fore.GREEN if cm else "") + "hire" +
(Fore.RESET if cm else "") + ": add a new employee\n" +
(Fore.GREEN if cm else "") + "fire" +
(Fore.RESET if cm else "") + ": remove an employee\n")
mode = get_cmd()
if engine.quit(
mode,
"Exiting employ mode, type help to see commands."):
break
elif mode == "fire":
fire_mode(conn, user)
elif mode == "hire":
hire_mode(conn, user)
else:
print("Invalid command entered.")
print(bar)
continue
#end employ mode
elif command == "order":
print(bar)
order_mode(conn, user)
print(bar)
continue
#
else:
print("Invalid command entered.")
print(command_list)
def order_mode(conn, user):
cursor = conn.cursor()
command = ""
while not engine.quit(command, "Exiting order mode."):
command = get_cmd("\nSelect from the following commands:\n" +
(Fore.GREEN if cm else "") + "orders" +
(Fore.RESET if cm else "") +
": view recent orders\n" +
(Fore.GREEN if cm else "") + "details" +
(Fore.RESET if cm else "") +
": view a detailed order report\n" +
(Fore.GREEN if cm else "") + "inventory" +
(Fore.RESET if cm else "") +
": view current inventory by lowest stock\n" +
(Fore.GREEN if cm else "") + "reorder" +
(Fore.RESET if cm else "") + ": order more items\n" +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " exit order mode\n" +
"")
if engine.quit(command, "Exiting order mode."):
return
elif command == "orders":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM orders WHERE store_id='" +
str(user.store_id) +
"' ORDER BY date DESC").fetchall(),
cursor.description)
continue
elif command == "details":
input = get_cmd("Enter the " + (Fore.CYAN if cm else "") +
"Order ID" + (Fore.RESET if cm else "") +
" of the order you'd like to view.")
if (engine.quit(input, "Exiting order mode.")):
return
while int(input) not in [
i[0]
for i in cursor.execute("SELECT id FROM orders WHERE " +
"id = '" + str(input) +
"';").fetchall()
]:
input = get_cmd(
"Order ID not found, please re-enter Order ID, or type " +
(Fore.GREEN if cm else "") + "cancel" +
(Fore.RESET if cm else "") + " to cancel.")
if (engine.quit(input), "Exiting order mode."):
return
#end while id not found
#once id is found
order_id = int(input)
#get itemization id
try:
itemization_id = str(
engine.get_cell(conn, "itemization_id", "orders", "id",
str(order_id)))
print("Loading itemized order list: " + itemization_id + "...")
except sqlite3.Error as error:
print((Fore.RED if cm else "") +
"Error collecting Itemization ID from order table:" +
(Fore.RESET if cm else ""))
print(error)
print("Exiting order mode.")
return
else:
print("Displaying itemization details for Order " +
str(order_id) + ":")
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM " + str(itemization_id)),
cursor.description)
#end command = details
elif command == "inventory":
engine.print_cursor_fetch(
cursor.execute("SELECT * FROM inventory WHERE store_id='" +
str(user.store_id) +
"' ORDER BY stock ASC").fetchall(),
cursor.description)
continue
elif command == "reorder":
order.reorder(conn, user)
continue
#end command = reorder
else:
print("Error, invalid command entered.")
continue