def update_customer(): sql_connection = connect_to_database() if request.method == 'POST': data = request.get_json() # Receives javascript xhr request and updates information for customers if data['pageType'] == 'Customers': cust_id = data['id'] firstName = data['firstName'] lastName = data['lastName'] street = data['street'] city = data['city'] state = data['state'] zip_code = data['zip'] phone = data['phone'] email = data['email'] cust_query = f"UPDATE customers SET firstName='{firstName}', lastName='{lastName}', street='{street}', city='{city}', state='{state}', zip='{zip_code}', phone='{phone}', email='{email}' WHERE customerID={cust_id}" execute_query(sql_connection, cust_query) return redirect('/customers') # Receives javascript xhr request to update distributor information if data['pageType'] == 'Distributors': dist_id = data['id'] name = data['name'] street = data['street'] city = data['city'] state = data['state'] zip_code = data['zip'] phone = data['phone'] dist_query = f"UPDATE distributors SET name='{name}', street='{street}', city='{city}', state='{state}', zip='{zip_code}', phone='{phone}' WHERE distributorID={dist_id}" execute_query(sql_connection, dist_query) return redirect('/distributors')
def confirm_order(): """Order confirmation""" sql_connection = connect_to_database() # form data to add order if request.method == 'POST': orderData = request.get_json() dist_id = orderData['dist_id'] filled = orderData['filled'] total = orderData['total'] order_query = f"INSERT INTO orders(distributorID, orderDate, orderFilled, orderTotal) VALUES ({dist_id}, curdate(), {filled}, {total})" execute_query(sql_connection, order_query) order_q = "SELECT last_insert_id()" order_id = execute_query(sql_connection, order_q).fetchall() order_id = order_id[0]['last_insert_id()'] items = orderData['items'] for i in range(len(items)): # adds items to orderedItems relationship table tied to last_insert_id() ordered_items_query = f"INSERT INTO orderedItems(orderID, inventoryID, quantity) VALUES ({order_id}, {items[i]['id']}, {items[i]['quantity']})" execute_query(sql_connection, ordered_items_query) # subtracts from distInventory update_dist_query = f"UPDATE distInventory SET quantity=quantity-{items[i]['quantity']} WHERE inventoryID={items[i]['id']}" execute_query(sql_connection, update_dist_query) return redirect('/orders')
def fill_orders(): """Fills an order and adds ordered items to records in shop""" sql_connection = connect_to_database() data = request.get_json() if request.method == 'POST': orders = data['orders'] records = [] # loop through orders and gather information for i in range(len(orders)): order_info_query = f"SELECT d.inventoryID, d.distributorID, d.name, d.artist, d.price, d.year, d.img, (SELECT name from distributors WHERE distributorID={orders[i]['distributor_id']}) AS distributor, o.quantity FROM distInventory d \ INNER JOIN orderedItems o ON d.inventoryID=o.inventoryID WHERE o.orderID={orders[i]['order_id']} " # set order filled to True order_filled = f"UPDATE orders SET orderFilled=True WHERE orderID={orders[i]['order_id']}" execute_query(sql_connection, order_filled) record_info = execute_query(sql_connection, order_info_query).fetchall() records.append(record_info) # for every record on the order insert it into database, if record already exists in database, update quantity for i in records: for j in i: add_records_query = f"INSERT INTO records (productID, price, name, artist, year, distributorID, img, quantity)\ VALUES ({j['inventoryID']}, {j['price']}, '{j['name']}', '{j['artist']}', '{j['year']}', '{j['distributorID']}', '{j['img']}', {j['quantity']}) \ ON DUPLICATE KEY UPDATE quantity=quantity+{j['quantity']}" execute_query(sql_connection, add_records_query) return redirect('/records')
def create_inventory(): """Creates inventory for a newly added distributor""" name = request.args['name'] sql_connection = connect_to_database() # get distributorID from newly created distributor get_dist = f"SELECT distributorID from distributors WHERE name='{name}'" dist = execute_query(sql_connection, get_dist).fetchall() dist_id = dist[0]['distributorID'] payload = {} # initialize genres genres = ['country', 'blues', 'rock', 'pop', 'funk', 'hip-hop'] # pick a random genre for i in range(1, 10): genre = random.choice(genres) # create API request url based on genre and page number url = f"https://api.discogs.com/database/search?genre='{genre}'&page={i}&key=yAkaOKZIxeKFgRSyIXEY&secret=hbaRZHEfvIVguItMHliaGvliDAJVbBCw" response = requests.request("GET", url, data=payload) response = response.json() # use results to insert into distInventory table for j in range(0, 20): key = 'year' # make sure each album has a year and is from US (to avoid foreign chars) if key in response['results'][j] and response['results'][j][ 'country'] == 'US': title = response['results'][j]['title'] # ensures title can be inserted in MySQL table title = title.replace('"', '') title = title.replace("'", "") title = title.replace("*", "") title = title.encode('ascii', 'ignore').decode('ascii') # split title by delimeter to get artist and album title title = title.split('-') artist = title[0] name = title[1][1:] year = response['results'][j]['year'] price = random.randint(350, 5000) / 100 quantity = randint(0, 50) img = response['results'][j]['cover_image'] insert_inventory = f'INSERT INTO distInventory(distributorID, artist, name, year, price, quantity, img) \ VALUES ({dist_id}, "{artist}", "{name}", "{year}", {price}, {quantity}, "{img}")' execute_insert = execute_query(sql_connection, insert_inventory) # after all records have been inserted, redirect to distributors page return redirect('/distributors')
def add_purchase(cust): """Begins new purchase for a customer""" sql_connection = connect_to_database() records = 'SELECT r.productID, r.name, r.artist, r.year, r.price, r.img, r.quantity, d.name AS distributor FROM records r INNER JOIN distributors d ON r.distributorID=d.distributorID' records_query = execute_query(sql_connection, records).fetchall() return render_template('views.html', recordsPurch=records_query, title='recordsPurch', id=cust)
def view_orders(): """Displays all orders""" sql_connection = connect_to_database() if request.method == 'GET': orders_query = "SELECT o.orderID, o.orderDate, o.distributorID, o.orderFilled, o.orderTotal, d.name AS distributor FROM orders o \ INNER JOIN distributors d ON o.distributorID=d.distributorID ORDER BY orderID desc" orders = execute_query(sql_connection, orders_query).fetchall() return render_template('views.html', orders=orders, title='Orders')
def select_dist(): """Select a distributor to order from""" sql_connection = connect_to_database() if request.method == 'GET': distributors = 'SELECT distributorID, name, street, city, state, zip, phone FROM distributors' distributors_query = execute_query(sql_connection, distributors).fetchall() return render_template('forms.html', title='Create Order', distributor_order=distributors_query)
def show_records(): """Displays all records in the database""" sql_connection = connect_to_database() if request.method == 'GET': records = 'SELECT r.name, r.artist, r.year, r.price, r.img, r.quantity, d.name AS distributor FROM records r LEFT JOIN distributors d ON r.distributorID=d.distributorID' records_query = execute_query(sql_connection, records).fetchall() return render_template('views.html', records=records_query, title='Records')
def show_items(): """Displays items on a customer purchase""" sql_connection = connect_to_database() # form data to retrieve purchaseID purch_id = int(request.form['purchaseID']) get_items = f"SELECT name, artist, year, price, img FROM records INNER JOIN \ purchasedItems ON purchasedItems.productID = records.productID INNER JOIN purchases ON purchases.purchaseID = purchasedItems.purchaseID \ WHERE purchases.purchaseID = {purch_id}" items_query = execute_query(sql_connection, get_items).fetchall() return render_template('views.html', purchaseItems=items_query)
def create_order(): """Displays inventory for particular distributor to select items from""" sql_connection = connect_to_database() if request.method == 'POST': if request.form['distributor'] != None: dist_name = request.form['distributor'] dist_inventory_query = f"SELECT inventoryID, distributorID, name, artist, year, price, quantity, img FROM distInventory WHERE distributorID=(SELECT distributorID FROM distributors WHERE name='{dist_name}')" dist_inventory = execute_query(sql_connection, dist_inventory_query).fetchall() return render_template('forms.html', dist_inventory=dist_inventory, dist_name=dist_name)
def view_purchases(): """Displays all customer purchases, query retrieves purchase info related to customerID""" sql_connection = connect_to_database() if request.method == 'GET': purchases = 'SELECT purchases.purchaseID, purchases.purchaseDate, purchases.paymentMethod, purchases.totalPrice, \ customers.firstName, customers.lastName FROM purchases LEFT JOIN customers ON customers.customerID=purchases.customerID' purchases_query = execute_query(sql_connection, purchases).fetchall() return render_template('views.html', purchases=purchases_query, title='Purchases')
def view_order(): """Displays items on a particular order""" sql_connection = connect_to_database() # uses orderID to retrieve information from relationship table if request.method == 'POST': order_id = request.form['order_id'] items_query = f"SELECT d.name, d.artist, d.price, d.img, o.quantity, o.orderID FROM distInventory d INNER JOIN orderedItems o ON d.inventoryID=o.inventoryID WHERE o.orderID={order_id}" ordered_items = execute_query(sql_connection, items_query).fetchall() orderID = ordered_items[0]['orderID'] return render_template('views.html', ordered_items=ordered_items, orderID=orderID)
def view_dist_inventory(): """Displays inventory for specified distributor""" # form data to get distributor name and ID if request.method == 'POST': dist_name = request.form['dist_name'] dist_id = request.form['dist_id'] sql_connection = connect_to_database() inventory_query = f"SELECT name, artist, year, price, quantity, img FROM distInventory WHERE distributorID={dist_id}" inventory = execute_query(sql_connection, inventory_query).fetchall() return render_template('views.html', inventory=inventory, dist_name=dist_name)
def add_record(): """Adds a record to the database""" sql_connection = connect_to_database() # form data for record info if request.method == 'POST': name = request.form['name'] artist = request.form['artist'] price = request.form['price'] quantity = request.form['quantity'] year = request.form['year'] add_record = f"INSERT INTO records(name, artist, year, price, quantity) VALUES ('{name}', '{artist}', '{year}', '{price}', '{quantity}')" record_query = execute_query(sql_connection, add_record).fetchall() # redirect to records, after adding return redirect('/records') return render_template('forms.html', title='Add Record')
def add_distributor(): """Adds a new distributor to the database""" sql_connection = connect_to_database() # form data if request.method == 'POST': name = request.form['distName'] street = request.form['street'] city = request.form['city'] state = request.form['state'] phone = request.form['phone'] zip_code = request.form['zip'] add_dist = f"INSERT INTO distributors(name, street, city, state, zip, phone) VALUES ('{name}', '{street}', '{city}', '{state}', '{zip_code}', '{phone}')" dist_query = execute_query(sql_connection, add_dist).fetchall() return redirect(url_for('create_inventory', name=name)) return render_template('forms.html', title='Add Distributor')
def view_cust_purchases(var): """Displays purchases for specific customer""" # customerID id = var sql_connection = connect_to_database() purchases = 'SELECT purchaseID, purchaseDate, paymentMethod, totalPrice FROM purchases INNER JOIN customers ON customers.customerID = ' + id + ' AND purchases.customerID = ' + id name = 'SELECT firstName, lastName FROM customers WHERE customers.customerID = ' + id purchases_query = execute_query(sql_connection, purchases).fetchall() name_query = execute_query(sql_connection, name).fetchall() # if no purchase data if not purchases_query: purchases_query = ["noData"] firstName = name_query[0]['firstName'] lastName = name_query[0]['lastName'] return render_template('views.html', custpurchase=purchases_query, title='Purchases for ' + firstName + " " + lastName)
def add_customer(): """Adds a new customer to the database""" sql_connection = connect_to_database() # form data if request.method == 'POST': firstName = request.form['first-name'] lastName = request.form['last-name'] street = request.form['street'] city = request.form['city'] state = request.form['state'] zip_code = request.form['zip'] email = request.form['email'] phone = request.form['phone'] add_customer = f"INSERT INTO customers(firstName, lastName, street, city, state, zip, email, phone) VALUES ('{firstName}', \ '{lastName}', '{street}', '{city}', '{state}', {zip_code}, '{email}', '{phone}')" customer_query = execute_query(sql_connection, add_customer).fetchall() return redirect('/customers') return render_template('forms.html', title='Add Customer')
def show_customer(): """Displays all customers""" sql_connection = connect_to_database() # Navigation to customers page, retrieves latest customer information from database if request.method == 'GET': customers = 'SELECT customerID, firstName, lastName, street, city, state, zip, phone, email FROM customers' customers_query = execute_query(sql_connection, customers).fetchall() return render_template('views.html', customers=customers_query, title='Customers') # Post request handles delete button, delete query deletes customer by ID and redirects to the get request route to reload the page without deleted customer if request.method == 'POST': if request.form['submit_btn'] == 'Delete': customerID = request.form['customerID'] delete_query = f"DELETE FROM customers WHERE customerID={customerID}" execute_query(sql_connection, delete_query) return redirect('/customers')
def show_distributor(city_info, name_info): """Displays all distributors""" sql_connection = connect_to_database() if request.method == 'GET': distributors_query = 'SELECT distributorID, name, street, city, state, zip, phone FROM distributors' distributors = execute_query(sql_connection, distributors_query).fetchall() # post to handle search/filtering if request.method == 'POST': # search distributor by name if request.form.get('option') == 'name': name = request.form.get('search') name_query = f"SELECT name, street, city, state, zip, phone from distributors WHERE name='{name}'" name_info = execute_query(sql_connection, name_query).fetchall() # if no search results if not name_info: return render_template('views.html', title='No Search Results') return render_template('views.html', distributors=name_info) # search distributor by city if request.form.get('option') == 'city': city = request.form.get('search') city_query = f"SELECT name, street, city, state, zip, phone from distributors WHERE city='{city}'" city_info = execute_query(sql_connection, city_query).fetchall() # if no search results if not city_info: return render_template('views.html', title='No Search Results') return render_template('views.html', distributors=city_info) return render_template('views.html', distributors=distributors, title='Distributors')
def add_purchase_final(cust): """Creates a purchase""" sql_connection = connect_to_database() customerID = None if request.method == 'ADD': recordsPurchased.clear() data = request.get_json() # records selected for purchase recordArray = data['recordIDs'] if 'id' in data: customerID = int(data['id']) recordArray = [int(i) for i in recordArray] if recordArray: for i in recordArray: recordsPurchased.append(i) if customerID is None: # return for javascript function return render_template('forms.html', title='Add Purchase') else: return render_template('forms.html', title='Add Purchase') if request.method == 'GET': if recordsPurchased: if cust is None: customers = "SELECT customerID, firstName, lastName FROM customers" customers_query = execute_query(sql_connection, customers).fetchall() return render_template('forms.html', title='Add Purchase', customers=customers_query) else: customer = f"SELECT customerID, firstName, lastName FROM customers WHERE \ customerID = {int(cust)}" customer_query = execute_query(sql_connection, customer).fetchall() return render_template('forms.html', title='Add Purchase', customer=customer_query) else: return redirect('/purchases/add-purchase') if request.method == 'POST': if recordsPurchased: custID = int(request.form['customer']) purchaseDate = request.form['purchasedate'] data = request.form['method'] total = 0 # gets record information and inserts into purchase for i in recordsPurchased: price_query = f"SELECT price FROM records WHERE records.productID = {i}" price_query_ex = execute_query(sql_connection, price_query).fetchall() total += price_query_ex[0]['price'] insert_purchases = f"INSERT INTO purchases(customerID, purchaseDate, paymentMethod, totalPrice) VALUES ({custID}, \ '{purchaseDate}', '{data}', {total})" purchases_query = execute_query(sql_connection, insert_purchases).fetchall() last_purchase_id = f"SELECT LAST_INSERT_ID()" last_purch_id_query = execute_query(sql_connection, last_purchase_id).fetchall() last_id = last_purch_id_query[0]['LAST_INSERT_ID()'] for i in recordsPurchased: insert_purchasedItems = f"INSERT INTO purchasedItems(purchaseID, productID) VALUES ({last_id}, {i})" exe_purchasedItems = execute_query( sql_connection, insert_purchasedItems).fetchall() update_quant = f"UPDATE records SET quantity = quantity - 1 WHERE records.productID = {i}" update_quant_query = execute_query(sql_connection, update_quant).fetchall() recordsPurchased.clear() return redirect('/purchases')