def index():
conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels')
cursor = data.create_cursor(conn)
cursor.execute("""
select * from `products`
""")
return render_template('index.template.html', cursor=cursor)
def process_create_product():
# extract out all the variables from the form
product_code = request.form.get('productCode')
product_name = request.form.get('productName')
product_line = request.form.get('productLine')
product_vendor = "default vendor"
product_scale = 100
product_description = "Desc"
quantity_in_stock = 10
buy_price = 19.99
MSRP = 25.00
# prepare the SQL
sql = f"""
insert into `products` (`productCode`, `productName`, `productLine`, `productScale`, `productVendor`, `productDescription`, `quantityInStock`, `buyPrice`, `MSRP`)
VALUES
('{product_code}', '{product_name}', '{product_line}',
'{product_scale}', '{product_vendor}','{product_description}', '{quantity_in_stock}', '{buy_price}', '{MSRP}' )
"""
conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels')
cursor = data.create_cursor(conn)
cursor.execute(sql)
conn.commit()
return redirect(url_for('search'))
def get_employees_ln(conn):
sql = " select `lastName` from `employees`"
cursor_ln = data.create_cursor(conn)
cursor_ln.execute(sql)
return cursor_ln
def search():
conn = data.get_connection('localhost', os.environ.get('C9_USER'), '',
'classicmodels')
product_line_cursor = data.get_product_lines(conn)
# create the query
sql = """select * from `products` where 1"""
# we use request.args to get whatever is in the query string
selected_product_line = request.args.get('selected_product_line')
if selected_product_line:
print("Filtering by", selected_product_line)
sql = sql + f" and `productLine` = '{selected_product_line}'"
search_terms = request.args.get('search-by')
# if search_terms is not None
if search_terms:
sql = sql + f" and `productName` like '%{search_terms}%'"
products_cursor = data.create_cursor(conn)
products_cursor.execute(sql)
return render_template('search.template.html',
product_lines=product_line_cursor,
products=products_cursor,
sql=sql)
def get_employees(conn, office_code):
sql =f""" select * from `employees` where 1 and `officeCode` = {office_code} """
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
def get_employees_filtered(conn, user_input_fn, user_input_ln, user_input_ct, user_input_jt):
sql = f"""select * from `employees`
join `offices` on `employees`.`officeCode` = `offices`.`officeCode`
where 1"""
cursor = data.create_cursor(conn)
print('user_input_fn : ', user_input_fn)
if user_input_fn != "":
sql = sql + f" and `firstName`='{user_input_fn}' "
print(sql)
print('user_input_ln : ', user_input_ln)
if user_input_ln != "":
sql = sql + f" and `lastName`='{user_input_ln}' "
print('user_input_ct :', user_input_ct)
if user_input_ct != "":
sql = sql + f" and `country`='{user_input_ct}' "
print('user_input_jt :', user_input_jt)
if user_input_jt !="":
sql = sql + f" and `jobTitle`='{user_input_jt}' "
print('-------sql---------')
print(sql)
cursor.execute(sql)
return cursor
def get_offices(conn):
sql = """select * from `offices` where 1"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
def new_employee(conn,employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitle ):
cursor=data.create_cursor(conn)
sql=f""" insert into `employees` (`employeeNumber`,`lastName`,`firstName`,`extension`,`email`,`officeCode`,`reportsTo`,`jobTitle`)
values('{employeeNumber}','{lastName}','{firstName}','{extension}','{email}','{officeCode}','{reportsTo}','{jobTitle}')
"""
cursor.execute(sql)
return cursor
# def get_next_employee_number(conn):
# cursor = data.create_cursor(conn)
# sql = "select max(employeeNumber) from `employees` "
# cursor.execute(sql)
# next_employee_number = cursor.fetchone()['max(employeeNumber)'] +1
# print('next_employee_number: ', next_employee_number)
# return next_employee_number
def get_employees_only(conn):
sql = "select * from `employees`"
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
def get_customer_by_customer_number(conn, customer_number):
sql = f"""select * from `customers` where `customerNumber` = '{customer_number}'"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
customer = cursor.fetchone(
) #only one customer is associated with a customer_number
return customer
def get_employees_jt(conn):
sql = " select `jobTitle` from `employees` "
cursor_jt = data.create_cursor(conn)
cursor_jt.execute(sql)
return cursor_jt
def get_offices(conn):
sql = "select * from `offices` ORDER BY `officeCode` asc"
cursor_offices= data.create_cursor(conn)
cursor_offices.execute(sql)
return cursor_offices
def get_reports_to(conn):
cursor =data.create_cursor(conn)
sql= "select `reportsTo` from `employees`"
cursor.execute(sql)
return cursor
def new_offices(conn, officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory):
sql = f"""insert into `offices` (`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`)
values('{officeCode}','{city}','{phone}','{addressLine1}','{addressLine2}','{state}','{country}','{postalCode}','{territory}')
"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
conn.commit()
def get_order_details(conn, order_number):
sql = f"""
select * from `orderdetails`
join `products` on `orderdetails`.`productCode` = `products`.`productCode`
where `orderNumber` = '{order_number}'
order by `orderLineNumber`
"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
def get_office_code_country(conn):
cursor = data.create_cursor(conn)
sql = f""" select `country`, `city` from `employees`
join `offices` on `employees`.`officeCode` = `offices`.`officeCode` """
cursor.execute(sql)
return cursor
def get_employees_ct(conn):
sql = f"""select `country` from `employees`
join `offices` on `employees`.`officeCode` =`offices`.`officeCode` """
cursor_ct = data.create_cursor(conn)
cursor_ct.execute(sql)
cursor_ct = cursor_ct.fetchall()
print(cursor_ct)
return cursor_ct
def get_customers(conn):
sql = """select * from `customers` where 1"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
import data
import os
conn = data.get_connection('localhost', os.environ.get('C9_USER'), '',
'classicmodels')
cursor = data.create_cursor(conn)
product_line = input("Please enter the product line: ")
text_description = input("Please enter the description: ")
sql = f"""
insert into `productlines`
(`productLine`, `textDescription`)
VALUES
('{product_line}', '{text_description}')
"""
# remember to execute the query
cursor.execute(sql)
#commit the changes (i.e confirm the changes)
conn.commit()
def get_orders_for_customer(conn, customer_number):
# there is only one customer associated with an order, so the query below should return only one result.
sql = f"""select * from `orders` where `customerNumber` = '{customer_number}'"""
cursor = data.create_cursor(conn)
cursor.execute(sql)
return cursor
