content: import itertools
  import operator
  
  from sqlalchemy_searchable import search
  
  from scoda.app import app
  from flask import request, url_for, redirect, flash, make_response, session, render_template, jsonify, Response, \
      send_file
  from flask_security import current_user
  from itertools import zip_longest
  from sqlalchemy.sql import select
  from sqlalchemy import func, extract, desc
  from .models import db
  from .models import *
  from .models.user import UserAnalysis
  from .models.datasets import ExploreForm
  from .models.maps import MapForm, NightFormETH, NightFormJHB
  from pandas import read_sql_query
  import gviz_api
  import geojson, json
  import pandas as pd
  from .app import csrf
  from werkzeug.datastructures import MultiDict
  from urllib.parse import urlencode, urlparse, parse_qsl, urlsplit, parse_qs
  
  def grouper(iterable, n, fillvalue=None):
      "Collect data into fixed-length chunks or blocks"
      # grouper('ABCDEFG', 3, 'x') --> ABC DEF Gxx"
      args = [iter(iterable)] * n
      return zip_longest(*args, fillvalue=fillvalue)
  
  @app.route('/help')
  def help():
      return render_template('help/help.html')
  
  @app.route('/api/indicators-list/', defaults={'check': ''})
  @app.route('/api/indicators-list/<check>', methods=['GET', 'POST'])
  def api_indicators_list(check):
      remove_list = ['Poverty rate', 'Gini Coefficient', 'Gross Value Add', 'Exports', 'Multiple deprivation index',
                     'Human Development Index']
      if check == "codebook":
          indicators_list = [[str(c.id), c.name] for c in CbIndicator.query.join(CbDataPoint,CbDataPoint.indicator_id == CbIndicator.id).all() if c.name not in remove_list]
      else:
          indicators_list = [[str(c.id), c.in_name] for c in Indicator.all() if c.in_name not in remove_list]
      # print(indicators_list)
      # payload = {"indicators_list": indicators_list}
      return jsonify(indicators_list)
  
  @app.route('/api/explore/', defaults={'check': ''})
  @app.route('/api/explore/<check>', methods=['GET', 'POST'])
  def api_explore(check):
      form = ExploreForm()
      status = 200
      plot = 0
      tour = 1
      #ind = 76
      #Note: Riaan Snyders: 10 June 2020 - Removed for now. Only functions on GET at the moment.
      #if request.method == 'POST':
          #if form.validate():
              #data_json = request.get_json()
              #ind = data_json["indicator_id"]
      if request.args.get('indicator_id'):
          ind = request.args.get('indicator_id')
      else:
          ind = 76
      print(ind)
      plot = 1
      tour = 2
      # codebook query
      if check == "codebook":
          query = db.session.query(CbRegion.name.label('re_name'), CbDataPoint.start_dt, CbIndicator.name.label('ds_name'), CbDataPoint.value). \
              filter(CbDataPoint.indicator_id == ind).filter(CbDataPoint.indicator_id == CbIndicator.id). \
              filter(CbDataPoint.region_id == CbRegion.id)
          df = read_sql_query(query.statement, query.session.bind)
          df = df.rename(columns={'name': 're_name', 'name.1': 'ds_name'})
          df["year"] = df["start_dt"].apply(lambda x: int(x.strftime('%Y')))
          df["start_dt"] = df["year"]
      else:
          query = db.session.query(Region.re_name, DataPoint.year, DataSet.ds_name, DataPoint.value). \
              filter(DataPoint.indicator_id == ind).filter(DataPoint.dataset_id == DataSet.id). \
              filter(DataPoint.region_id == Region.id)
          df = read_sql_query(query.statement, query.session.bind)
      df.to_csv('%s/data/%s' % (app.root_path, "data_test.csv"), index=False)
      table = []
      table_plot = []
      years, cities, datasets = [list(df.year.unique()), list(df.re_name.unique()), list(df.ds_name.unique())]
      cities = [c for c in cities]
  
      options_list = [{'optid': i, 'optname': d} for i, d in enumerate(datasets, start=1)]
      years_list = [{'optid': i, 'optname': 'Year: %s' % d} for i, d in enumerate(sorted(years), start=1)]
  
      plot_type = 1
      print(len(years))
      if (len(datasets) > 1) or (len(years) == 1):
          plot_type = 2
  
      colours = ['#f44336', '#03a9f4', '#4caf50', '#ffc107', '#03a9f4', '#ff5722', '#9c27b0', '#8bc34a',
                 '#ffeb3b', '#9e9e9e', '#3f51b5', '#e91e63']
      series = {i: {'color': colours[i]} for i in range(len(datasets))}
      view = list(range(2, len(datasets) + 2))
      view.insert(0, 0)
  
      minVal = min(map(float, list(df.value.unique())))
      maxVal = max(map(float, list(df.value.unique()))) * 1.1
  
      head = ['City', 'Year']
      for i in datasets:
          head.append(str(i))
      table.append(head)
      table_plot.append(head)
  
      # df.re_name = df.re_name.str.encode('utf-8')
      if plot_type == 1:
          df_i = df.iloc[:, [0, 1, 3]]
  
          schema = [('City', 'string'), ('Year', 'string'), ('%s' % datasets[0], 'number')]
  
          data_table = gviz_api.DataTable(schema)
          data_table.LoadData(df_i.values)
          table_plot = data_table.ToJSon(columns_order=('City', '%s' % datasets[0], 'Year'))
  
          for c in cities:
              for y in years:
                  row = [str(c), str(y)]
                  for d in datasets:
                      datapoint = df.loc[(df["re_name"] == c) & (df["year"] == y) & (df["ds_name"] == d), "value"]
                      if len(datapoint) == 0:
                          row.append(None)
                      else:
                          row.append(
                              float(df.loc[(df["re_name"] == c) & (df["year"] == y) & (
                              df["ds_name"] == d), "value"]))
                  table.append(row)
      else:
          for c in cities:
              for y in years:
                  row = [str(c), str(y)]
                  for d in datasets:
                      datapoint = df.loc[(df["re_name"] == c) & (df["year"] == y) & (df["ds_name"] == d), "value"]
                      if len(datapoint) == 0:
                          row.append(None)
                      else:
                          row.append(
                              float(df.loc[(df["re_name"] == c) & (df["year"] == y) & (
                              df["ds_name"] == d), "value"]))
                  table.append(row)
      yrs = ['Year'] + [str(y) for y in years[::-1]]
      payload = {"plot":plot, "table":table, "table_plot":table_plot,"colours":colours,"year":str(max(years)), "series":series,
               "view":view, "plot_type":plot_type,"min":minVal,"max":maxVal, "cities":cities, "options_list":options_list,
               "years_list":years_list,"tour":tour, "years":yrs}
      return jsonify(payload)
          # else:
          #     form_errors = form.errors
          #     return {"form_errors":form_errors}
  
  @app.route('/explore', methods=['GET', 'POST'])
  def explore():
      analyses = []
  
      if current_user.is_authenticated:
          query = db.session.query(UserAnalysis.id, UserAnalysis.ds_name, UserAnalysis.description) \
              .filter(UserAnalysis.user_id == current_user.id).order_by(UserAnalysis.id.desc())
  
          analyses = []
  
          for i in grouper(query, 4):
              analyses.append(i)
  
      session['explore'] = []
      form = ExploreForm()
      status = 200
      plot = 0
      tour = 1
      if request.method == 'POST':
          if form.validate():
              plot = 1
              tour = 2
  
              ind = form.indicator_id.data
  
              query = db.session.query(Region.re_name, DataPoint.year, DataSet.ds_name, DataPoint.value). \
                  filter(DataPoint.indicator_id == ind).filter(DataPoint.dataset_id == DataSet.id). \
                  filter(DataPoint.region_id == Region.id)
              print(query.all())
              indicator = Indicator.query.get(ind)
  
              df = read_sql_query(query.statement, query.session.bind)
              # df.to_csv('%s/data/%s' % (app.root_path, "data_test.csv"), index=False)
              table = []
              years, cities, datasets = [list(df.year.unique()), list(df.re_name.unique()), list(df.ds_name.unique())]
              cities = [c for c in cities]
  
              options_list = [{'optid': i, 'optname': d} for i, d in enumerate(datasets, start=1)]
              years_list = [{'optid': i, 'optname': 'Year: %s' % d} for i, d in enumerate(sorted(years), start=1)]
  
              plot_type = 1
              if (len(datasets) > 1) or (len(years) == 1):
                  plot_type = 2
  
              colours = ['#f44336', '#03a9f4', '#4caf50', '#ffc107', '#03a9f4', '#ff5722', '#9c27b0', '#8bc34a',
                         '#ffeb3b', '#9e9e9e', '#3f51b5', '#e91e63']
              series = {i: {'color': colours[i]} for i in range(len(datasets))}
              view = list(range(2, len(datasets) + 2))
              view.insert(0, 0)
  
              minVal = min(map(float, list(df.value.unique())))
              maxVal = max(map(float, list(df.value.unique()))) * 1.1
  
              head = ['City', 'Year']
              for i in datasets:
                  head.append(str(i))
              table.append(head)
              print(df)
              # df.re_name = df.re_name.str.encode('utf-8')
              if plot_type == 1:
                  df = df.iloc[:, [0, 1, 3]]
  
                  schema = [('City', 'string'), ('Year', 'string'), ('%s' % datasets[0], 'number')]
  
                  data_table = gviz_api.DataTable(schema)
                  data_table.LoadData(df.values)
                  table = data_table.ToJSon(columns_order=('City', '%s' % datasets[0], 'Year'))
  
              else:
                  for c in cities:
                      for y in years:
                          row = [str(c), str(y)]
                          for d in datasets:
                              datapoint = df.loc[(df["re_name"] == c) & (df["year"] == y) & (df["ds_name"] == d), "value"]
                              if len(datapoint) == 0:
                                  row.append(None)
                              else:
                                  row.append(
                                      float(df.loc[(df["re_name"] == c) & (df["year"] == y) & (
                                      df["ds_name"] == d), "value"]))
                          table.append(row)
              yrs = ['Year'] + [str(y) for y in years[::-1]]
              return render_template('explore/explore.html', form=form, plot=plot, table=table, colours=colours,
                                     year=str(max(years)), series=series, view=view, plot_type=plot_type, min=minVal,
                                     max=maxVal, cities=cities, options_list=options_list, years_list=years_list,
                                     tour=tour, indicator=indicator, analyses=analyses, years=yrs)
          else:
              if request.is_xhr:
                  status = 412
              else:
                  flash('Please correct the problems below and try again.', 'warning')
  
      else:
          return render_template('explore/explore.html', form=form, tour=tour)
  
      if not request.is_xhr:
          resp = make_response(
              render_template('explore/explore.html', form=form, plot=plot, tour=tour, analyses=analyses))
  
      else:
          resp = ''
  
      return (resp, status,
              # ensure the browser refreshes the page when Back is pressed
              {'Cache-Control': 'no-cache, no-store, must-revalidate'})
  
  
  @app.route('/demographics/<region_id>/<city_ward_code>/download', methods=['GET'])
  def demographics_download(region_id, city_ward_code):
      region = Region.query.get(region_id).re_name
  
      if city_ward_code == 'None':
          query = db.session.query(Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == region_id).all()
  
          df = pd.DataFrame()
          df['Year'] = range(1996, 2031)
          for g in query:
              df['%s - Ward %s' % (region, g[1])] = list(g[0])
  
      else:
          query = db.session.query(Ward.data, Ward.city_ward_code) \
              .filter(Ward.city_ward_code == city_ward_code) \
              .filter(Ward.region_id == region_id).all()
  
          df = pd.DataFrame()
          df['Year'] = range(1996, 2031)
          for g in query:
              df['%s - Ward %s' % (region, g[1])] = list(g[0])
  
      return Response(df.to_csv(index=False), mimetype="text/csv",
                      headers={"Content-disposition": "attachment; filename=demographics.csv"})
  
  
  @app.route('/demographics', methods=['GET', 'POST'])
  def demographics():
      analyses = []
  
      if current_user.is_authenticated:
          query = db.session.query(UserAnalysis.id, UserAnalysis.ds_name, UserAnalysis.description) \
              .filter(UserAnalysis.user_id == current_user.id).order_by(UserAnalysis.id.desc())
  
          analyses = []
  
          for i in grouper(query, 4):
              analyses.append(i)
  
      session['demo'] = []
  
      if 'maps' not in session.keys():
          session['maps'] = {0: {}, 1: {}}
  
      form1 = MapForm(prefix='form1', region_id='1', year=1)
      print(form1.city_ward_code.choices)
      status = 200
      tour = 1
      geometries1 = {}
  
      forms = [form1]
      if request.method == 'POST':
  
          if all(f.validate() for f in forms):
  
              for f, F in enumerate(forms):
                  for field in F:
                      if str(field.data) == 'None':
                          field.data = session['maps'][str(f)][field.name[6:]]
                      else:
                          session['maps'][str(f)][field.name[6:]] = field.data
  
              tour = 0
              # query = db.session.query(Area.geom.ST_AsGeoJSON(), Area.data)
              year1 = int(form1.year.data)
              year_ind1 = range(1996, 2031)
  
              if form1.city_ward_code.data == '':
  
                  query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
                      filter(Ward.region_id == form1.region_id.data)
  
                  geometries1 = {"type": "FeatureCollection",
                                 "features": []}
                  for g in query:
                      d = json.loads(g[0])
  
                      if year1 == 0:
                          flow = 0
                      else:
                          flow = round(g[1][year1] - g[1][year1 - 1])
  
                      geometries1['features'].append({"type": "Feature", "properties": {"density": round(g[1][year1]),
                                                                                        "flow": flow,
                                                                                        "name": 'Ward %s' % g[2],
                                                                                        "year": year_ind1[year1]},
                                                      "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.data).filter(Ward.region_id == form1.region_id.data).all()
                  region = db.session.query(Region.re_name).filter(Region.id == form1.region_id.data).first()
  
                  results = []
  
                  for r in query:
                      row = [val for val in list(r)[0]]
                      results.append(row)
  
                  df = pd.DataFrame(results).fillna(value=0)
  
                  table1 = [['Year', '%s' % str(region[0])]]
  
                  for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
                      table1.append([str(y), val])
  
                  m1 = 1.05 * max(df.sum(axis=0).tolist())
  
              else:
                  query = db.session.query(Area.geom.ST_AsGeoJSON(), Area.data, Area.city_ward_code) \
                      .filter(Area.city_ward_code == form1.city_ward_code.data) \
                      .filter(Area.region_id == form1.region_id.data)
  
                  geometries1 = {"type": "FeatureCollection",
                                 "features": []}
  
                  for g in query:
                      d = json.loads(g[0])
  
                      if year1 == 0:
                          flow = 0
                      else:
                          flow = round(g[1][year1] - g[1][year1 - 1])
  
                      geometries1['features'].append(
                          {"type": "Feature", "properties": {"density": round(g[1][year1]),
                                                             "flow": flow,
                                                             "name": 'Area %s' % g[2],
                                                             "year": year_ind1[year1]},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.data).filter(Ward.city_ward_code == form1.city_ward_code.data). \
                      filter(Ward.region_id == form1.region_id.data).first()
  
                  region = db.session.query(Region.re_name).filter(Region.id == form1.region_id.data).first()
                  region2 = db.session.query(Ward.city_ward_code).filter(Ward.city_ward_code == form1.city_ward_code.data) \
                      .first()
  
                  results = []
  
                  for r in query:
                      row = [val for val in list(r)]
                      results.append(row)
  
                  df = pd.DataFrame(results).fillna(value=0)
  
                  table1 = [['Year', '%s - Ward %s' % (str(region[0]), str(region2[0]))]]
  
                  for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
                      table1.append([str(y), val])
  
                  m1 = 1.05 * max(df.sum(axis=0).tolist())
  
              query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == form1.region_id.data).order_by(
                  Ward.city_ward_code).distinct()
  
              form1.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in enumerate(query.all()
                                                                                                               , start=1)]
              form1.city_ward_code.choices.insert(0, ('', 'View All'))
  
              return render_template('demographics/demographics.html', form1=form1, geometries1=geometries1,
                                     table1=table1, tour=tour, max1=m1, region1=form1.region_id.data,
                                     ward1=form1.city_ward_code.data, analyses=analyses)
  
          else:
              if request.is_xhr:
                  status = 412
              else:
                  flash('Please correct the problems below and try again.', 'warning')
  
      else:
  
          session['maps'][0] = {'city_ward_code': '', 'region_id': 1, 'year': 1}
          session['maps'][1] = {'city_ward_code': '', 'region_id': 4, 'year': 1}
  
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 1)
  
          geometries1 = {"type": "FeatureCollection",
                         "features": []}
  
          geometries2 = {"type": "FeatureCollection",
                         "features": []}
  
          for g in query:
              d = json.loads(g[0])
              geometries1['features'].append({"type": "Feature", "properties": {"density": round(g[1][1]),
                                                                                "flow": round(g[1][1] - g[1][0]),
                                                                                "name": 'Ward %s' % g[2],
                                                                                "year": 1997},
                                              "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 1).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table1 = [['Year', 'Johannesburg']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table1.append([str(y), val])
  
          m = 1.05 * max(df.sum(axis=0).tolist())
  
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 4)
  
          for g in query:
              d = json.loads(g[0])
              geometries2['features'].append({"type": "Feature", "properties": {"density": round(g[1][1]),
                                                                                "flow": round(g[1][1] - g[1][0]),
                                                                                "name": 'Ward %s' % g[2],
                                                                                "year": 1997},
                                              "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 4).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table2 = [['Year', 'EThekwini']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table2.append([str(y), val])
  
          m2 = 1.05 * max(df.sum(axis=0).tolist())
  
          return render_template('demographics/demographics.html', form1=form1, geometries1=geometries1,
                                 tour=tour, table1=table1, max1=m, region1=1, ward1=None, ward2=None, analyses=analyses
                                 )
  
      if not request.is_xhr:
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 1)
          geometries1 = {"type": "FeatureCollection",
                         "features": []}
  
          geometries2 = {"type": "FeatureCollection",
                         "features": []}
  
          for g in query:
              d = json.loads(g[0])
              geometries1['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
              geometries2['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 1).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table1 = [['Year', 'Johannesburg']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table1.append([str(y), val])
  
          m = 1.05 * max(df.sum(axis=0).tolist())
  
          resp = make_response(render_template('demographics/demographics.html', form1=form1,
                                               geometries1=geometries1, table1=table1,
                                               tour=tour, max1=m, region1=1,
                                               ward1=None, analyses=analyses))
  
      else:
          resp = ''
  
      return (resp, status,
              # ensure the browser refreshes the page when Back is pressed
              {'Cache-Control': 'no-cache, no-store, must-revalidate'})
  
  @app.route('/api/demographics', methods=['GET', 'POST'])
  @csrf.exempt
  def api_demographics():
      analyses = []
  
      session['demo'] = []
  
      if 'maps' not in session.keys():
          session['maps'] = {0: {}, 1: {}}
  
      form1 = MapForm(prefix='form1', region_id='1', year=1)
      geometries1 = {}
  
      if request.method == 'POST':
          data = request.get_json()
          print(data)
          #data = request.data.decode('utf-8')
          #object = parse_qs(urlsplit('?' + data).query)
          #object = {key: str(value[0]) for key, value in object.items()}
          
          #if 'csrf_token' in object: del object['csrf_token']
          #form1 = MapForm(MultiDict(object))
          form1 = data
  
          print(form1['year'])
  
          #if form1.validate():
          if form1:
              tour = 0
              # query = db.session.query(Area.geom.ST_AsGeoJSON(), Area.data)
              #year1 = int(form1.year)
              year1 = int(form1['year'])
              year_ind1 = range(1996, 2031)
  
              #if form1.city_ward_code.data == '':
              if form1['city_ward_code'] == '':
                  query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
                      filter(Ward.region_id == form1['region_id'])
  
                  geometries1 = {"type": "FeatureCollection",
                                 "features": []}
                  for g in query:
                      d = json.loads(g[0])
  
                      if year1 == 0:
                          flow = 0
                      else:
                          flow = round(g[1][year1] - g[1][year1 - 1])
  
                      geometries1['features'].append({"type": "Feature", "properties": {"density": round(g[1][year1]),
                                                                                        "flow": flow,
                                                                                        "name": 'Ward %s' % g[2],
                                                                                        "year": year_ind1[year1]},
                                                      "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.data).filter(Ward.region_id == form1['region_id']).all()
                  region = db.session.query(Region.re_name).filter(Region.id == form1['region_id']).first()
  
                  results = []
  
                  for r in query:
                      row = [val for val in list(r)[0]]
                      results.append(row)
  
                  df = pd.DataFrame(results).fillna(value=0)
  
                  table1 = [['Year', '%s' % str(region[0])]]
  
                  for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
                      table1.append([str(y), val])
  
                  m1 = 1.05 * max(df.sum(axis=0).tolist())
  
              else:
                  query = db.session.query(Area.geom.ST_AsGeoJSON(), Area.data, Area.city_ward_code) \
                      .filter(Area.city_ward_code == int(form1['city_ward_code'])) \
                      .filter(Area.region_id == int(form1['region_id']))
  
                  geometries1 = {"type": "FeatureCollection",
                                 "features": []}
  
                  for g in query:
                      d = json.loads(g[0])
  
                      if year1 == 0:
                          flow = 0
                      else:
                          flow = round(g[1][year1] - g[1][year1 - 1])
  
                      geometries1['features'].append(
                          {"type": "Feature", "properties": {"density": round(g[1][year1]),
                                                             "flow": flow,
                                                             "name": 'Area %s' % g[2],
                                                             "year": year_ind1[year1]},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.data).filter(Ward.city_ward_code == int(form1['city_ward_code'])). \
                      filter(Ward.region_id == int(form1['region_id'])).first()
  
                  region = db.session.query(Region.re_name).filter(Region.id == int(form1['region_id'])).first()
                  region2 = db.session.query(Ward.city_ward_code).filter(Ward.city_ward_code == int(form1['city_ward_code'])) \
                      .first()
  
                  results = []
  
                  for r in query:
                      row = [val for val in list(r)]
                      results.append(row)
  
                  df = pd.DataFrame(results).fillna(value=0)
  
                  table1 = [['Year', '%s - Ward %s' % (str(region[0]), str(region2[0]))]]
  
                  for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
                      table1.append([str(y), val])
  
                  m1 = 1.05 * max(df.sum(axis=0).tolist())
  
              query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == int(form1['region_id'])).order_by(
                  Ward.city_ward_code).distinct()
  
              #form1.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in enumerate(query.all()
                                                                                                               #, start=1)]
              #form1.city_ward_code.choices.insert(0, ('', 'View All'))
  
  
              resp = jsonify({'success': True, 'geometries1': geometries1,'table1':table1,
                              'tour':tour, 'max1':m1, 'region1':form1['region_id'],'ward1':form1['city_ward_code']})
              resp.status_code = 200
              return resp
          else:
              message = 'Please correct the problems below and try again.'
              resp = jsonify(message=message)
              resp.status_code = 500
              return resp
  
      else:
          session['maps'][0] = {'city_ward_code': '', 'region_id': 1, 'year': 1}
          session['maps'][1] = {'city_ward_code': '', 'region_id': 4, 'year': 1}
  
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 1)
  
          geometries1 = {"type": "FeatureCollection",
                         "features": []}
  
          for g in query:
              d = json.loads(g[0])
              geometries1['features'].append({"type": "Feature", "properties": {"density": round(g[1][1]),
                                                                                "flow": round(g[1][1] - g[1][0]),
                                                                                "name": 'Ward %s' % g[2],
                                                                                "year": 1997},
                                              "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 1).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table1 = [['Year', 'Johannesburg']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table1.append([str(y), val])
  
          m = 1.05 * max(df.sum(axis=0).tolist())
  
          resp = jsonify({'success': True, 'table1': table1,
                           'max1': m, 'region1': 1, 'ward1': None,'ward2':None, 'geometries1': geometries1,
                          'form_year':form1.year.choices,'form_ward':form1.city_ward_code.choices,'form_city':form1.region_id.choices})
          resp.status_code = 200
          return resp
  
  @app.route('/nightlights_jhb', methods=['GET', 'POST'])
  def demographics_night_jhb():
      analyses = []
  
      if current_user.is_authenticated:
          query = db.session.query(UserAnalysis.id, UserAnalysis.ds_name, UserAnalysis.description) \
              .filter(UserAnalysis.user_id == current_user.id).order_by(UserAnalysis.id.desc())
  
          analyses = []
  
          for i in grouper(query, 4):
              analyses.append(i)
  
      session['night'] = []
  
      form = NightFormJHB()
      status = 200
      tour = 1
  
      if request.method == 'POST':
  
          if form.validate():
  
              tour = 0
  
              if form.city_ward_code.data == '':
  
                  query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference). \
                      filter(Grid.region_id == 1)
  
                  geometries = {"type": "FeatureCollection",
                                "features": []}
  
                  bias_ind = [x / 10.0 for x in range(5, 21, 1)].index(float(form.grid_bias.data))
  
                  for g in query:
  
                      d = json.loads(g[0])
  
                      geometries['features'].append({"type": "Feature", "properties": {"density": round(g[1][bias_ind] - g[3]),
                                                                                       "name": 'Grid %s' % g[2],
                                                                                       "year": 2016},
                                                     "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == 1).order_by(
                      Ward.city_ward_code).distinct()
  
                  form.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in
                                                 enumerate(query.all()
                                                           , start=1)]
                  form.city_ward_code.choices.insert(0, ('', 'View All'))
  
                  return render_template('demographics/demographics_night.html', form=form, geometries=geometries,
                                         bias_val=form.grid_bias.data)
  
              else:
  
                  w = db.session.query(Ward.id).filter(Ward.city_ward_code == form.city_ward_code.data)\
                      .filter(Ward.region_id == 1).first()
  
                  w = Ward.query.get(w[0])
  
                  query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference) \
                      .filter(Grid.geom.intersects(w.geom))
  
                  geometries = {"type": "FeatureCollection",
                                "features": []}
  
                  bias_ind = [x / 10.0 for x in range(5, 21, 1)].index(float(form.grid_bias.data))
  
                  for g in query:
                      d = json.loads(g[0])
  
                      geometries['features'].append(
                          {"type": "Feature", "properties": {"density": round(g[1][bias_ind] - g[3]),
                                                             "name": 'Grid %s' % g[2],
                                                             "year": 2016},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code)\
                      .filter(Ward.city_ward_code == form.city_ward_code.data).filter(Ward.region_id == 1)
  
                  geometries2 = {"type": "FeatureCollection",
                                 "features": []}
  
                  for g in query:
                      d = json.loads(g[0])
  
                      geometries2['features'].append(
                          {"type": "Feature", "properties": {"density": 0,
                                                             "name": 'Ward %s' % form.city_ward_code.data,
                                                             "year": 2016},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == 1).order_by(
                      Ward.city_ward_code).distinct()
  
                  form.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in enumerate(query.all()
                                                                                                                  , start=1)]
                  form.city_ward_code.choices.insert(0, ('', 'View All'))
  
                  return render_template('demographics/demographics_night.html', form=form, geometries=geometries,
                                     bias_val=form.grid_bias.data, geometries2=geometries2, ward=form.city_ward_code.data)
  
          else:
              if request.is_xhr:
                  status = 412
              else:
                  flash('Please correct the problems below and try again.', 'warning')
  
      else:
  
          query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference). \
              filter(Grid.region_id == 1)
  
          geometries = {"type": "FeatureCollection",
                        "features": []}
  
          for g in query:
              d = json.loads(g[0])
  
              geometries['features'].append({"type": "Feature", "properties": {"density": g[1][0] - g[3],
                                                                               "name": 'Grid %s' % g[2],
                                                                               "year": 2016},
                                             "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          return render_template('demographics/demographics_night.html', form=form, bias_val=0.5, geometries=geometries,
                                 analyses=analyses)
  
      if not request.is_xhr:
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 1)
          geometries1 = {"type": "FeatureCollection",
                         "features": []}
  
          geometries2 = {"type": "FeatureCollection",
                         "features": []}
  
          for g in query:
              d = json.loads(g[0])
              geometries1['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
              geometries2['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 1).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table1 = [['Year', 'Johannesburg']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table1.append([str(y), val])
  
          m = 1.05 * max(df.sum(axis=0).tolist())
  
          resp = make_response(render_template('demographics/demographics.html', form1=form1, form2=form2,
                                               geometries1=geometries1, geometries2=geometries2, table1=table1,
                                               table2=table1, tour=tour, max1=m, max2=m, region1=1, region2=1,
                                               ward1=None, ward2=None, analyses=analyses))
  
      else:
          resp = ''
  
      return (resp, status,
              # ensure the browser refreshes the page when Back is pressed
              {'Cache-Control': 'no-cache, no-store, must-revalidate'})
  
  
  @app.route('/nightlights_eth', methods=['GET', 'POST'])
  def demographics_night_eth():
      analyses = []
  
      if current_user.is_authenticated:
          query = db.session.query(UserAnalysis.id, UserAnalysis.ds_name, UserAnalysis.description) \
              .filter(UserAnalysis.user_id == current_user.id).order_by(UserAnalysis.id.desc())
  
          analyses = []
  
          for i in grouper(query, 4):
              analyses.append(i)
  
      session['night'] = []
  
      form = NightFormETH()
      status = 200
      tour = 1
  
      if request.method == 'POST':
  
          if form.validate():
  
              tour = 0
  
              if form.city_ward_code.data == '':
  
                  query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference). \
                      filter(Grid.region_id == 4)
  
                  geometries = {"type": "FeatureCollection",
                                "features": []}
  
                  for g in query:
  
                      d = json.loads(g[0])
  
                      geometries['features'].append({"type": "Feature", "properties": {"density": round(g[1][0]-g[3]),
                                                                                       "name": 'Grid %s' % g[2],
                                                                                       "year": 2016},
                                                     "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == 4).order_by(
                      Ward.city_ward_code).distinct()
  
                  form.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in
                                                 enumerate(query.all()
                                                           , start=1)]
                  form.city_ward_code.choices.insert(0, ('', 'View All'))
  
                  return render_template('demographics/demographics_night_ETH.html', form=form, geometries=geometries)
  
              else:
  
                  w = db.session.query(Ward.id).filter(Ward.city_ward_code == form.city_ward_code.data)\
                      .filter(Ward.region_id == 4).first()
  
                  w = Ward.query.get(w[0])
  
                  query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference) \
                      .filter(Grid.geom.intersects(w.geom)).filter(Grid.region_id == 4)
  
                  geometries = {"type": "FeatureCollection",
                                "features": []}
  
                  for g in query:
                      d = json.loads(g[0])
  
                      geometries['features'].append(
                          {"type": "Feature", "properties": {"density": round(g[1][0]-g[3]),
                                                             "name": 'Grid %s' % g[2],
                                                             "year": 2016},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code)\
                      .filter(Ward.city_ward_code == form.city_ward_code.data).filter(Ward.region_id == 4)
  
                  geometries2 = {"type": "FeatureCollection",
                                 "features": []}
  
                  for g in query:
                      d = json.loads(g[0])
  
                      geometries2['features'].append(
                          {"type": "Feature", "properties": {"density": 0,
                                                             "name": 'Ward %s' % form.city_ward_code.data,
                                                             "year": 2016},
                           "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
                  query = db.session.query(Ward.city_ward_code).filter(Ward.region_id == 4).order_by(
                      Ward.city_ward_code).distinct()
  
                  form.city_ward_code.choices = [[str(i), 'Ward %s' % row.city_ward_code] for i, row in enumerate(query.all()
                                                                                                                  , start=1)]
                  form.city_ward_code.choices.insert(0, ('', 'View All'))
  
                  return render_template('demographics/demographics_night_ETH.html', form=form, geometries=geometries,
                                     geometries2=geometries2, ward=form.city_ward_code.data)
  
          else:
              if request.is_xhr:
                  status = 412
              else:
                  flash('Please correct the problems below and try again.', 'warning')
  
      else:
  
          query = db.session.query(Grid.geom.ST_AsGeoJSON(), Grid.data, Grid.city_grid_id, Grid.reference). \
              filter(Grid.region_id == 4)
  
          geometries = {"type": "FeatureCollection",
                        "features": []}
  
          for g in query:
              d = json.loads(g[0])
  
              geometries['features'].append({"type": "Feature", "properties": {"density": round(g[1][0]-g[3]),
                                                                               "name": 'Grid %s' % g[2],
                                                                               "year": 2016},
                                             "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          return render_template('demographics/demographics_night_ETH.html', form=form, geometries=geometries,
                                 analyses=analyses)
  
      if not request.is_xhr:
          query = db.session.query(Ward.geom.ST_AsGeoJSON(), Ward.data, Ward.city_ward_code). \
              filter(Ward.region_id == 1)
          geometries1 = {"type": "FeatureCollection",
                         "features": []}
  
          geometries2 = {"type": "FeatureCollection",
                         "features": []}
  
          for g in query:
              d = json.loads(g[0])
              geometries1['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
              geometries2['features'].append(
                  {"type": "Feature", "properties": {"density": round(g[1][0]), "flow": 0, "name": g[2],
                                                     "year": 1996},
                   "geometry": {"type": "Polygon", "coordinates": d['coordinates']}})
  
          query = db.session.query(Ward.data).filter(Ward.region_id == 1).all()
  
          results = []
  
          for r in query:
              row = [val for val in list(r)[0]]
              results.append(row)
  
          df = pd.DataFrame(results).fillna(value=0)
  
          table1 = [['Year', 'Johannesburg']]
  
          for y, val in zip(range(1996, 2031), df.sum(axis=0).tolist()):
              table1.append([str(y), val])
  
          m = 1.05 * max(df.sum(axis=0).tolist())
  
          resp = make_response(render_template('demographics/demographics.html', form1=form1, form2=form2,
                                               geometries1=geometries1, geometries2=geometries2, table1=table1,
                                               table2=table1, tour=tour, max1=m, max2=m, region1=1, region2=1,
                                               ward1=None, ward2=None, analyses=analyses))
  
      else:
          resp = ''
  
      return (resp, status,
              # ensure the browser refreshes the page when Back is pressed
              {'Cache-Control': 'no-cache, no-store, must-revalidate'})
  
  
  @app.route('/return-land/')
  def land_gen():
  
      return send_file('data/Ethekwini_Region_Data.xlsx', as_attachment=True)
  
  
  @app.route('/_parse_data', methods=['GET'])
  def parse_data():
      kwargs = {}
      for i in ['dataset_id', 'indicator_id', 'region_id', 'type_id', 'theme_id', 'year']:
          param = request.args.get(i)
          if (i == 'year'):
              if (str(param) != 'Empty') and (param is not None) and (str(param) != ''):
                  kwargs[i] = int(param)
              else:
                  pass
  
          elif (param is not None) and (str(param) != ''):
              kwargs[i] = param
  
      session['explore'] = [i for i in kwargs]
  
      datasets = db.session.query(DataPoint.dataset_id).filter_by(**kwargs).distinct()
      indicators = db.session.query(DataPoint.indicator_id).filter_by(**kwargs).distinct()
      regions = db.session.query(DataPoint.region_id).filter_by(**kwargs).distinct()
      types = db.session.query(DataPoint.type_id).filter_by(**kwargs).distinct()
      themes = db.session.query(DataPoint.theme_id).filter_by(**kwargs).distinct()
      years = db.session.query(DataPoint.year).filter_by(**kwargs).distinct()
  
      response = {}
  
      remove_list = ['Poverty rate', 'Gini Coefficient', 'Gross Value Add', 'Exports', 'Multiple deprivation index',
                     'Human Development Index']
  
      dataset_list = [(i[0], str(DataSet.query.filter_by(id=i).first().ds_name)) for i in datasets if
                      str(DataSet.query.filter_by(id=i).first().ds_name) not in remove_list]
      if 'dataset_id' not in session['explore']:
          dataset_list.insert(0, ('', 'Empty'))
      else:
          dataset_list.insert(1, ('', 'Empty'))
  
      response['dataset'] = dataset_list
  
      indicator_list = [[i[0], str(Indicator.query.filter_by(id=i).first().in_name)] for i in indicators if
                        str(Indicator.query.filter_by(id=i).first().in_name) not in remove_list]
  
      if 'indicator_id' not in session['explore']:
          indicator_list.insert(0, ('', 'Empty'))
          response['ind_ready'] = 0
      else:
          indicator_list.insert(1, ('', 'Empty'))
          response['ind_ready'] = 1
  
      response['indicator'] = indicator_list
      region_list = [(i[0], str(Region.query.filter_by(id=i).first().re_name)) for i in regions]
  
      if 'region_id' not in session['explore']:
          region_list.insert(0, ('', 'Empty'))
      else:
          region_list.insert(1, ('', 'Empty'))
  
      response['region'] = region_list
  
      type_list = [(i[0], str(Type.query.filter_by(id=i).first().ty_name)) for i in types]
  
      if 'type_id' not in session['explore']:
          type_list.insert(0, ('', 'Empty'))
      else:
          type_list.insert(1, ('', 'Empty'))
  
      response['type'] = type_list
  
      theme_list = [(i[0], str(Theme.query.filter_by(id=i).first().th_name)) for i in themes]
  
      if 'theme_id' not in session['explore']:
          theme_list.insert(0, ('', 'Empty'))
      else:
          theme_list.insert(1, ('', 'Empty'))
  
      response['theme'] = theme_list
  
      year_list = [(str(i), str(y[0])) for i, y in enumerate(sorted(years))]
  
      if 'year' not in session['explore']:
          year_list.insert(0, ('', 'Empty'))
      else:
          year_list.insert(1, ('', 'Empty'))
  
      response['year'] = year_list
  
      return jsonify(response)
  
  
  @app.route('/_parse_demo', methods=['GET'])
  def parse_demo():
      kwargs = {}
      for i in ['region_id', 'ward_id']:
          param = request.args.get(i)
          if (param is not None) and (str(param) != ''):
              kwargs[i] = param
  
      session['demo'] = [i for i in kwargs]
  
      wards = db.session.query(Ward.city_ward_code).filter_by(**kwargs).distinct().order_by(Ward.city_ward_code)
  
      response = {}
  
      ward_list = [(str(i[0]), 'Ward %s' % Ward.query.filter_by(id=i).first().city_ward_code) for i in wards]
  
      if 'ward_id' not in session['demo']:
          ward_list.insert(0, ('', 'View All'))
      else:
          ward_list.insert(1, ('', 'View All'))
  
      response['wards'] = ward_list
  
      return jsonify(response)
  
  @app.route('/api/codebook', methods=['GET', 'POST'])
  @app.route('/api/codebook/<int:page>', methods=['GET', 'POST'])
  @csrf.exempt
  def api_codebook(page=1):
      query = db.session.query(CbIndicator). \
          outerjoin(CbTheme, CbTheme.id == CbIndicator.theme_id). \
          outerjoin(CbSource, CbSource.id == CbIndicator.source_id). \
          outerjoin(CbUnit, CbUnit.id == CbIndicator.unit_id)
  
      if request.method == 'POST':
          data = request.get_json()
  
          if data['c88']:
              query = query.filter(CbIndicator.c88_theme.in_(data['c88']))
  
          if data['socr']:
              query = query.filter(CbIndicator.socr_theme.in_(data['socr']))
  
          if data['sdg']:
              query = query.filter(CbIndicator.sdg_theme.in_(data['sdg']))
  
          if data['search']:
              query = search(query, data['search'], sort=True)
  
      else:
          query = query.limit(150).offset((page - 1) * 20)
  
      row_count = query.count()
      query = query.all()
      # query.sort(key=lambda x: x.code)
  
      result_list = [row_count]
      for day, dicts_for_group_code in itertools.groupby(query, key=lambda x:x.group_code):
          dicts_for_group_code = list(dicts_for_group_code)
          day_dict = {
              "id": str(dicts_for_group_code[0].id),
              "varCode": dicts_for_group_code[0].code,
              "groupCode": dicts_for_group_code[0].group_code,
              "indicator": dicts_for_group_code[0].name,
              "c88": dicts_for_group_code[0].c88_theme,
              "socr": dicts_for_group_code[0].socr_theme,
              "sdg": dicts_for_group_code[0].sdg_theme,
              "definition": dicts_for_group_code[0].definition,
              "source": dicts_for_group_code[0].source.name if dicts_for_group_code[0].source else None,
              "reportingResponsibility": dicts_for_group_code[0].reporting_responsibility,
              "notesOnCalculation": dicts_for_group_code[0].notes_on_calculation,
              "variableType": dicts_for_group_code[0].unit.name,
              "frequencyOfCollection": dicts_for_group_code[0].frequency_of_collection,
              "automatibility": dicts_for_group_code[0].automatable,
              "granulity": dicts_for_group_code[0].granularity,
              "gathering_method": dicts_for_group_code[0].gathering_method,
              "expandability": dicts_for_group_code[0].expandable,
              "period": dicts_for_group_code[0].period,
              "unit_of_measurement": dicts_for_group_code[0].unit.name,
              "source_link": dicts_for_group_code[0].url_link,
              "data_check":True if dicts_for_group_code[0].indicator_data else False
          }
          children = []
          dicts_for_group_code.pop(0)
          for d in dicts_for_group_code:
              child = {
                  "id": str(d.id),
                  "varCode": d.code,
                  "groupCode": d.group_code,
                  "indicator": d.name,
                  "c88": d.c88_theme,
                  "socr": d.socr_theme,
                  "sdg": d.sdg_theme,
                  "definition": d.definition,
                  "source": d.source.name if d.source else None,
                  "reportingResponsibility": d.reporting_responsibility,
                  "notesOnCalculation": d.notes_on_calculation,
                  "variableType": d.unit.name,
                  "frequencyOfCollection": d.frequency_of_collection,
                  "automatibility": d.automatable,
                  "granulity": d.granularity,
                  "gathering_method": d.gathering_method,
                  "expandability": d.expandable,
                  "period": d.period,
                  "unit_of_measurement": d.unit.name,
                  "source_link": d.url_link,
                  "data_check": bool(d.indicator_data),
              }
  
              children.append(child)
          day_dict.update({"children": children})
          result_list.append(day_dict)
  
      return jsonify(result_list)
  