def populations():
populationForm = PopulationForm()
population_sql = get_population_sql()
if not population_sql[0]:
flash(population_sql[1])
return render_template('populations.html', title='Populations', table='', unmanageList='', form=assignmentForm)
sql = population_sql[1]
ret = aux.get_table_rl(sql[0])
if not ret[0]:
flash(ret[1])
return render_template('populations.html', title='Populations', table='', unmanageList='', form=assignmentForm)
table = ret[1]
ret = aux.get_table_rl(sql[1])
if not ret[0]:
flash(ret[1])
return render_template('populations.html', title='Populations', table='', unmanageList='', form=assignmentForm)
cities = ret[1]
populationForm.cityDDL.choices = cities
if populationForm.validate_on_submit():
combined = populationForm.cityDDL.data.split('|')
city = combined[0]
state = combined[1]
flash("Updating population of %s, %s" % (city, state))
resultWrite = aux.writeQuery(sql[2], paramList=[populationForm.population.data, city, state])
if resultWrite[0]:
flash("Successfully updated population of %s, %s" % (city, state))
return redirect(url_for('populations'))
else:
flash(resultWrite[1])
return render_template('populations.html', form=populationForm, title='City Populations', table=table)
else:
return render_template('populations.html', form=populationForm, title='City Populations', table=table)
def state_volume(state, category, units, monthYear):
ret = aux.get_table_rl(MONTH_YEAR_DROPDOWN)
if not ret[0]:
flash(ret[1])
return render_template('state_volume.html', title='Store Revenue', monthsYears='')
monthsYears = ret[1]
ddlMonthsYears = [(m_y, m_y) for (m_y,) in monthsYears]
stateVolumeForm = StateVolumeForm()
stateVolumeForm.monthYearDDL.choices = ddlMonthsYears
# monthYearDropDownTable = list(aux.get_table2(MONTH_YEAR_DROPDOWN))
# monthYearDropDownResult = list(monthYearDropDownTable)
if request.method == 'POST':
# the Generate button was clicked
# app.logger.warning('[APP_LOG] Selected Month Year: %s', request.form.get("selected_month_year"))
# param = request.form.get("selected_month_year")
# param = param.split("-")
monthYear = stateVolumeForm.monthYearDDL.data
monthYearList = monthYear.split('-')
month = monthYearList[0]
year = monthYearList[1]
parameterList = [month, year, month, year]
ret = aux.get_table_rl(STATE_VOLUME_REPORT, parameterList)
if not ret[0]:
flash(ret[1])
return render_template('state_volume.html', title='State Volume', state_volume='')
stateVolReport = ret[1]
return render_template('state_volume.html',
title='State Volume Drill',
form=stateVolumeForm,
state_vol=stateVolReport,
monthYear=monthYear)
# stateVolReport = aux.get_table4(STATE_VOLUME_REPORT, param)
# stateVolReport = tuple(zip(stateVolReport.Category, stateVolReport.State, stateVolReport.Units))
# # converting into a tuple of tuples so jinja2 can iterate in the template
#
# return render_template('state_vol_drill.html',
# title='State Volume Drill',
# form=stateVolumeForm,
# state_vol=stateVolReport,
# month_years=monthYear)
else:
# either this is the initial display of the page, or we are drilling
if state == '':
# this is the initial display of the page
return render_template('state_volume.html', title='State Volume', form=stateVolumeForm)
else:
# we are drilling
table = aux.get_table1_5(STORE_DETAIL_REPORT, state)
return render_template('state_volume.html', title='State Volume', form=stateVolumeForm,
table=table, state=state, category=category, units=units, monthYear=monthYear)
def store_revenue():
# state_sql = '''SELECT DISTINCT state FROM City ORDER BY state;'''
# states = list(aux.get_table2(SQL_STATE))
ret = aux.get_table_rl(SQL_STATE)
if not ret[0]:
flash(ret[1])
return render_template('store_revenue.html',
title='Store Revenue',
states='')
states = ret[1]
ddlStates = [(st, st) for (st, ) in states]
storeRevenueForm = StoreRevenueForm()
storeRevenueForm.storeDDL.choices = ddlStates
if request.method == 'POST':
# param = request.form.get("selected_state")
# stores = aux.get_table2_5(SQL_STORE_REVENUE_BY_STATE,param)
stores = aux.get_table2_5(SQL_STORE_REVENUE_BY_STATE,
storeRevenueForm.storeDDL.data)
return render_template('store_revenue.html',
title='Store Revenue',
states=ddlStates,
table=stores,
state=storeRevenueForm.storeDDL.data,
form=storeRevenueForm)
return render_template('store_revenue.html',
title='Store Revenue',
states=ddlStates,
state=None,
table=None,
form=storeRevenueForm)
def gps():
ret = aux.getSql("09.2. Display Actual vs Predicted GPS.sql")
if ret[0]:
sql = ret[1]
ret = aux.get_table_rl(sql)
if ret[0]:
sqlOutput = ret[1]
return render_template('gps.html', title='GPS', table=sqlOutput)
flash(ret[1])
return render_template('gps.html', title='GPS', table='')
flash(ret[1])
return render_template('gps.html', title='GPS', table='')
def manufacturer_products(manufacturer):
if manufacturer == '':
# initial display
summaryTable = aux.get_table2(SQL_MANUFACTURER_SUMMARY)
return render_template('manufacturer_products.html',
title="Manufacturers' Products",
summary_table=summaryTable)
# drill-down display
ret = aux.get_table_rl(SQL_MANUFACTURER_HEADER, [manufacturer])
if not ret[0]:
flash(ret[1])
redirect(manufacturer_products)
header = ret[1]
manufacturer_details = aux.get_table1_5(SQL_MANUFACTURER_DTS, manufacturer)
return render_template('manufacturer_products.html',
manufacturer=manufacturer,
header=header,
manufacturer_details=manufacturer_details,
drillTitle='Manufacturer Drill Down')
def assignments():
assignmentForm = AssignmentForm(request.form)
assign_sql = get_assign_sql()
if not assign_sql[0]:
flash(assign_sql[1])
return render_template('assignments.html',
title='Assignments',
table='',
unmanageList='',
form=assignmentForm)
sql = assign_sql[1]
ret = aux.get_table_rl(sql[5])
if not ret[0]:
flash(ret[1])
return render_template('assignments.html',
title='Assignments',
table='',
unmanageList='',
form=assignmentForm)
table = ret[1]
unmanaged = aux.get_scalar(sql[4])
if not unmanaged[0]:
flash(unmanaged[1])
return render_template('assignments.html',
title='Assignments',
table='',
unmanageList='')
unmanagedList = unmanaged[1][0]
if unmanagedList is None:
unmanagedList = 'None found'
ret = aux.get_table_rl(sql[6])
if not ret[0]:
flash(ret[1])
return render_template('assignments.html',
title='Assignments',
table='',
unmanageList='',
form=assignmentForm)
stores = ret[1]
ret = aux.get_table_rl(sql[7])
if not ret:
flash(ret[1])
return render_template('assignments.html',
title='Assignments',
table='',
unmanageList='',
form=assignmentForm)
managers = ret[1]
assignmentForm.managerDDL.choices = managers
assignmentForm.storeDDL.choices = stores
if request.method == 'POST':
if assignmentForm.assignManager.data:
# assign manager to store
flash(
"Assigning manager with email %s to store number %s" %
(assignmentForm.managerDDL.data, assignmentForm.storeDDL.data))
ret = aux.writeQuery(
sql[2],
(assignmentForm.storeDDL.data, assignmentForm.managerDDL.data))
if not ret[0]:
flash(ret[1])
return render_template('assignments.html',
title='Assignments',
table=table,
unmanageList=unmanagedList,
form=assignmentForm)
flash(
"Manager with email %s successfully assigned to store number %s"
%
(assignmentForm.managerDDL.data, assignmentForm.storeDDL.data))
return redirect(url_for('assignments'))
if assignmentForm.unassignManager.data:
# unassign manager from store
flash(
"Unassigning manager with email %s from store number %s" %
(assignmentForm.managerDDL.data, assignmentForm.storeDDL.data))
ret = aux.writeQuery(
sql[3],
(assignmentForm.managerDDL.data, assignmentForm.storeDDL.data))
if not ret[0]:
flash(ret[1])
return render_template('assignments.html',
title='Assignments',
table=table,
unmanageList=unmanagedList,
orm=assignmentForm)
flash(
"Manager with email %s successfully unassigned from store number %s"
%
(assignmentForm.managerDDL.data, assignmentForm.storeDDL.data))
return redirect(url_for('assignments'))
return render_template('assignments.html',
title='Assignments',
table=table,
unmanagedList=unmanagedList,
form=assignmentForm)
def managers():
managerForm = ManagerForm(request.form)
ret = aux.getSqlSplit('03. Edit Manager Profile.sql')
if not ret[0]:
flash(ret[1])
return render_template('managers.html',
form=managerForm,
title='Managers',
table='')
manager_sql = ret[1]
SELECT_MANAGERS_SQL = manager_sql[0]
INSERT_NEW_MANAGER_SQL = manager_sql[1]
UPDATE_MANAGER_SQL = manager_sql[2]
DEACTIVATE_MANAGER_SQL = manager_sql[3]
FIND_MANAGER_ASSIGNMENTS_SQL = manager_sql[4]
REMOVE_MANAGER_ASSIGNMENTS_SQL = manager_sql[5]
COUNT_MANAGER_ASSIGNMENTS_SQL = manager_sql[6]
DELETE_MANAGER_SQL = manager_sql[7]
resultGet = aux.get_table_rl(SELECT_MANAGERS_SQL)
if resultGet[0]:
sqlOutput = resultGet[1]
managerForm.managersDDL.choices = [(email, "%s (%s)" % (name, email))
for (name, email) in sqlOutput]
if request.method == 'POST':
if managerForm.deactivateManager.data:
# deactivate manager
flash("Deactivating manager %s" %
dict(managerForm.managersDDL.choices).get(
managerForm.managersDDL.data))
resultWrite = aux.writeQuery(
DEACTIVATE_MANAGER_SQL,
paramList=[managerForm.managersDDL.data])
if resultWrite[0]:
flash("Successfully deactivated manager %s" %
dict(managerForm.managersDDL.choices).get(
managerForm.managersDDL.data))
return redirect(url_for('managers'))
else:
flash(resultWrite[1])
return redirect(url_for('managers'))
elif managerForm.deleteManager.data:
# delete manager
flash("Deleting manager %s" %
dict(managerForm.managersDDL.choices).get(
managerForm.managersDDL.data))
resultGet = aux.get_scalar(
COUNT_MANAGER_ASSIGNMENTS_SQL,
paramList=managerForm.managersDDL.data)
if resultGet[0]:
if resultGet[1][0] == 0:
resultWrite = aux.writeQuery(
DELETE_MANAGER_SQL,
paramList=managerForm.managersDDL.data)
if resultWrite[0]:
flash("Successfully deleted manager %s" %
dict(managerForm.managersDDL.choices).get(
managerForm.managersDDL.data))
return redirect(url_for('managers'))
else:
flash(resultWrite[1])
return redirect(url_for('managers'))
else:
flash(
"Manager %s, must be deactivated before being deleted."
% dict(managerForm.managersDDL.choices).get(
managerForm.managersDDL.data))
return redirect(url_for('managers'))
else:
flash(resultGet[1])
return redirect(url_for('managers'))
elif managerForm.addChange.data and \
managerForm.managerEmail.validate(managerForm, extra_validators=[DataRequired("Please enter manager's email address"), Email("Please enter a valid email address")]) and \
managerForm.managerName.validate(managerForm, extra_validators=[DataRequired("Please enter manager's name")]):
emails = [email for (name, email) in sqlOutput]
if (managerForm.managerEmail.data in emails):
# existing manager email
flash("Updating existing manager: %s (%s)" %
(managerForm.managerName.data,
managerForm.managerEmail.data))
resultWrite = aux.writeQuery(
UPDATE_MANAGER_SQL,
paramList=[
managerForm.managerName.data,
managerForm.managerEmail.data
])
if resultWrite[0]:
flash("Updated existing manager: %s (%s)" %
(managerForm.managerName.data,
managerForm.managerEmail.data))
return redirect(url_for('managers'))
else:
flash(resultWrite[1])
return redirect(url_for('managers'))
else:
# new manager
flash("Inserting new manager: %s (%s)" %
(managerForm.managerName.data,
managerForm.managerEmail.data))
resultWrite = aux.writeQuery(
INSERT_NEW_MANAGER_SQL,
paramList=[
managerForm.managerEmail.data,
managerForm.managerName.data
])
if resultWrite[0]:
flash("Successfully inserted new manager: %s (%s)" %
(managerForm.managerName.data,
managerForm.managerEmail.data))
return redirect(url_for('managers'))
else:
flash(resultWrite[1])
return redirect(url_for('managers'))
else:
return render_template('managers.html',
form=managerForm,
title='Managers',
table=sqlOutput)
else:
# todo: FLASH: if an existing one is added , flash a warning message within form.validate_on_submit as illustrated here: https://learning.oreilly.com/library/view/flask-web-development/9781491991725/ch04.html#ch_forms
# todo check manager email syntax to match email address if not, flash
# todo: add logic for deactivate manager
# todo: add logic for delete manager
return render_template('managers.html',
form=managerForm,
title='Managers',
table=sqlOutput)
else:
flash(resultGet[1])
# return render_template('managers.html', form=managerForm, title='Managers', table='')
return redirect(url_for('managers'))