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'))