# -*- coding: utf-8 -*-

"""
    Sahana Eden Vulnerability Controller
"""

module = request.controller
resourcename = request.function

if not settings.has_module(module):
    raise HTTP(404, body="Module disabled: %s" % module)

# @ToDo: deployment_setting
#countries = ["TL", "VN"]
countries = ["VN"]

# -----------------------------------------------------------------------------
def index():
    """ Module Home Page: Map """

    # This module uses it's own Theme
    settings.base.theme = "Vulnerability"

    # Additional scripts
    append = s3.scripts.append
    append("/%s/static/scripts/yepnope.1.5.4-min.js" % appname)
    append("/%s/static/scripts/jit/jit-yc.js" % appname)
    if s3.debug:
        append("/%s/static/scripts/jquery.ui.selectmenu.js" % appname)
        append("/%s/static/scripts/jquery.ui.progressbar.js" % appname)
        append("/%s/static/scripts/TypeHelpers.js" % appname)
        append("/%s/static/scripts/S3/s3.vulnerability.js" % appname)
        append("/%s/static/scripts/S3/s3.dataTables.js" % appname)
        append("/%s/static/scripts/jquery.dataTables.js" % appname)
        append("/%s/static/scripts/jquery.dataTables.fnSetFilteringDelay.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.fillbetween.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.crosshair.js" % appname)
    else:
        append("/%s/static/scripts/S3/s3.vulnerability.min.js" % appname)
        append("/%s/static/scripts/S3/s3.dataTables.min.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.min.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.crosshair.min.js" % appname)

    js_global = []
    append = js_global.append

    # i18n
    i18n = "\n".join((
        "i18n.gis_requires_login='%s'" % T("Requires Login"),
        "i18n.no_matching_result='%s'" % T("No matching result"),
        "i18n.no_entries_found='%s'" % T("No Entries Found"),
        "i18n.loading_report_details='%s'" % T("Loading report details"),
        "i18n.choose='%s'" % T("Choose"),
        "i18n.population='%s'" % T("Population"),
        "i18n.reported='%s'" % T("Reported"),
        "i18n.country='%s'" % COUNTRY,
        "i18n.country_in='%s'" % T("Country in"),
        "i18n.select_country='%s'" % T("Select a Country"),
        "i18n.show_more='%s'" % T("Show more"),
        "i18n.show_less='%s'" % T("Show less"),
        "i18n.submit_data='%s'" % T("Submit Data"),
        "i18n.analysis='%s'" % T("Analysis"),
        "i18n.reports='%s'" % T("Reports"),
        "i18n.all_reports='%s'" % T("All reports"),
        "i18n.my_reports='%s'" % T("My reports"),
        "i18n.approval_request_submitted='%s'" % T("Approval request submitted"),
        "i18n.thankyou_for_your_approval='%s'" % T("Thank you for your approval"),
        "i18n.reject_request_submitted='%s'" % T("Reject request submitted"),
        "i18n.submission_has_been_declined='%s'" % T("Thank you, the submission%(br)shas been declined") % dict(br="<br />"),
        "i18n.last_data_collected_on='%s'" % T("Last Data Collected on"),
        "i18n.by='%s'" % T("by"),
        "i18n.in_='%s'" % T("in"),
        "i18n.in_this='%s'" % T("in this"),
        "i18n.of='%s'" % T("of"),
        "i18n.out_of='%s'" % T("out of"),
        "i18n.review='%s'" % T("Review"),
        "i18n.submitted_by='%s'" % T("submitted by"),
        "i18n.go_to_the='%s'" % T("Go to the"),
        "i18n.select_data_type='%s'" % T("Select data type"),
        "i18n.about_to_submit_indicator_ratings='%s'" % T("You are about to submit indicator ratings for"),
        "i18n.poor='%s'" % T("poor"),
        "i18n.fair='%s'" % T("fair"),
        "i18n.moderate='%s'" % T("moderate"),
        "i18n.strong='%s'" % T("strong"),
        "i18n.data_quality='%s'" % T("Data Quality"),
        "i18n.of_total_data_reported='%s'" % T("of total data reported"),
        "i18n.uploading_report_details='%s'" % T("Uploading report details"),
        "i18n.upload_successful='%s'" % T("Upload successful"),
        ))
    append(i18n)

    # Get the L0 hdata & summary vdata
    hdata, vdata = l0()

    # Get the default location to open the map
    bounds = None
    root_org = auth.root_org()
    start = False
    if root_org:
        otable = s3db.org_organisation
        ttable = s3db.gis_location_tag
        gtable = s3db.gis_location
        query = (otable.id == root_org) & \
                (ttable.tag == "ISO2") & \
                (ttable.value == otable.country)
        r = db(query).select(ttable.location_id,
                             limitby=(0, 1)).first()
        if r and r.location_id in countries:
            start = True
            append('''\nstart=%s''' % r.location_id)
            # Add the child L1 summary vdata
            l1(r.location_id, vdata)
    if not start:
        append('''\nstart=""''')

    dumps = json.dumps
    script = '''
hdata=%s
vdata=%s
''' % (dumps(hdata), dumps(vdata))
    append(script)

    # Get the list of indicators
    itable = s3db.vulnerability_indicator
    query = (itable.deleted == False)
    rows = db(query).select(itable.name,
                            itable.description,
                            itable.parameter_id,
                            orderby=itable.posn)
    indicators = OrderedDict()
    count = 1
    for row in rows:
        indicators[count] = dict(i=row.parameter_id,
                                 n=row.name,
                                 d=row.description)
        count += 1

    append('''idata=%s''' % json.dumps(indicators))

    s3.js_global.append("".join(js_global))

    # Reports
    from s3.s3utils import S3DataTable
    resource = s3db.resource("stats_group")
    list_fields = ["id",
                   "date",
                   "location_id",
                   "location_id$L2",
                   "group",
                   "group_type_id",
                   "created_by",
                   "approved_by",
                   ]
    rfields = resource.resolve_selectors(list_fields)[0]
    filteredrows = resource.count()
    dt = S3DataTable(rfields, [], orderby=~s3db.stats_group.date)
    level_1_titles = [["Approval pending", T("Approval pending")],
                      ["VCA Report",T("VCA Report")],
                      ["Report",T("Report")],
                      ]
    report = dt.html(filteredrows,
                     filteredrows,
                     "report",
                     dt_pagination = "false",
                     dt_bFilter = "false",
                     dt_sDom = "t",
                     dt_group = [4, 3],
                     dt_group_totals = [level_1_titles],
                     dt_ajax_url = URL(c="vulnerability",
                                       f="report",
                                       extension="aadata",
                                       vars={"id": "report"},
                                       ),
                     dt_action_col = -1,
                     dt_group_space = "true",
                     dt_shrink_groups = "accordion",
                     dt_group_types = ["text", "none"],
                     )
    s3.report = report

    user = auth.user
    if user:
        user_name = "%s %s" % (user.first_name, user.last_name)
    else:
        user_name = ""
    today = request.utcnow.strftime("%d-%b-%y")

    s3.stylesheets.append("jit/base.css")

    response.view = "vulnerability/map.html"
    return dict(indicators=indicators,
                user_name = user_name,
                today = today,
                COUNTRY = COUNTRY.upper(),
                CHOOSE_COUNTRY = T("Choose Country"))

# -----------------------------------------------------------------------------
def tmdata():
    """ Controller to extract tree map data """

    MAX_LEVEL = 3 # the lowest level for child lookups

    # Requested locations
    if not len(request.args):
        return '''s={}'''
    else:
        locations = list(set([int(a) for a in request.args if a.isdigit()]))

    sdata = Storage()

    # Vulnerability Indicators
    indicator_pids = s3db.vulnerability_ids()
    idefaults = [(i, 0) for i in indicator_pids]

    # Locations Hierarchy
    ltable = s3db.gis_location
    parents = list(locations)
    children = list(locations)
    while parents or children:
        query = None
        if children:
            query = (ltable.id.belongs(children))
        if parents:
            q = (ltable.parent.belongs(parents))
            if query is None:
                query = q
            else:
                query |= q
        if query is None:
            break
        rows = db(query).select(ltable.id,
                                ltable.name,
                                ltable.level,
                                ltable.parent)
        next_parents = []
        next_children = []
        for row in rows:

            this = row.id
            level = int(row.level[1])
            parent = row.parent

            if this not in sdata:
                sdata[this] = {}
            data = sdata[this]
            data["n"] = row.name
            data["l"] = level
            data["f"] = parent
            data["p"] = 100
            data["i"] = dict(idefaults)
            data["x"] = this not in locations

            if level > 0 and parent:
                if parent in parents and \
                   level < MAX_LEVEL and \
                   parent in locations:
                    next_parents.append(this)
                elif this in children and parent not in sdata:
                    next_children.append(parent)
        parents = next_parents
        children = next_children

    # Population
    # @todo
    #dtable = s3db.stats_demographic
    #ptable = s3db.stats_demographic_data
    ##ptable = s3db.stats_aggregate
    #query = (dtable.name == "Population") & \
            #(ptable.parameter_id == dtable.parameter_id) & \
            #(ptable.location_id.belongs(sdata.keys()))
    #rows = db(query).select(ptable.location_id,
                            #ptable.value,
                            ##ptable.sum,
                            #orderby=~ptable.date)
    #location_ids = []
    #seen = location_ids.append
    #for row in rows:
        #location_id = row.location_id
        #if location_id not in location_ids:
            #seen(location_id)
            #sdata[location_id]["p"] = row.value
            ##sdata[location_id]["p"] = row.sum

    # Indicators
    stable = s3db.stats_aggregate

    query = (stable.location_id.belongs(sdata.keys())) & \
            (stable.parameter_id.belongs(indicator_pids))
    rows = db(query).select(stable.location_id,
                            stable.parameter_id,
                            stable.median)
    for row in rows:
        location_id = row.location_id
        location_data = sdata[location_id]
        if "i" not in location_data:
            location_data["i"] = dict(idefaults)
        location_data["i"][row.parameter_id] = row.median

    # Return as script
    script = '''sdata=%s\n''' % json.dumps(sdata)
    response.headers["Content-Type"] = "application/json"
    return script

# -----------------------------------------------------------------------------
def init():
    """
        Create the static GeoJSONs that the app needs
    """

    gis.export_admin_areas(countries)
    return "complete"

# -----------------------------------------------------------------------------
def l0():
    """
        Return hdata (Hierarchy Labels) & summary vdata (Resilience) for all Countries
        - used only by the initial map load
    """

    gtable = s3db.gis_location
    ttable = s3db.gis_location_tag
    htable = s3db.gis_hierarchy
    query = (gtable.id == ttable.location_id) & \
            (ttable.tag == "ISO2") & \
            (ttable.value.belongs(countries)) & \
            (gtable.id == htable.location_id)
    stable = s3db.stats_aggregate
    lquery = (stable.parameter_id == s3db.vulnerability_resilience_id()) & \
             (stable.agg_type == 4) & \
             (stable.location_id == gtable.id)
    left = stable.on(lquery)
    hdata = {}
    vdata = {}

    ids = []
    append = ids.append
    rows = db(query).select(gtable.id,
                            gtable.name,
                            htable.L1,
                            htable.L2,
                            htable.L3,
                            #htable.L4,
                            stable.date,
                            stable.mean,
                            orderby=~stable.date,
                            left=left)
    for row in rows:
        id = row[gtable].id
        if id in ids:
            # We're only interested in the most recent data per location
            continue
        append(id)
        _grow = row[gtable]
        _hrow = row[htable]
        hdata[id] = dict(l1 = _hrow.L1,
                         l2 = _hrow.L2,
                         l3 = _hrow.L3,
                         #l4 = _hrow.L4,
                         )
        mean = row[stable].mean
        if mean is None:
            resilience = 0
        else:
            resilience = int(round(mean, 0))
        vdata[id] = dict(r = resilience,
                         n = _grow.name,
                         l = 0,
                         )

    return hdata, vdata

# -----------------------------------------------------------------------------
def l1(id, vdata):
    """
        Update summary vdata (Resilience) for all child L1s of the start country
        - used only by the initial map load
    """

    gtable = db.gis_location
    query = (gtable.parent == id) & \
            (gtable.level == "L1")
    atable = db.vulnerability_aggregated_indicator
    stable = db.stats_aggregate
    rquery = (atable.name == "Resilience") & \
             (stable.parameter_id == atable.parameter_id) & \
             (stable.agg_type == 4)
    rows = db(query).select(gtable.id,
                            gtable.name,
                            )
    for row in rows:
        query = rquery & (stable.location_id == row.id)
        _row = db(query).select(stable.date,
                                stable.mean,
                                orderby=~stable.date).first()
        resilience = None
        if _row and _row.mean is not None:
            resilience = int(round(_row.mean, 0))
        vdata[row.id] = dict(r = resilience,
                             n = row.name,
                             l = 1,
                             f = id,
                             )

    return

# -----------------------------------------------------------------------------
def vdata():
    """
        Return JSON of the Vulnerability data for a location
        - for display in Map Popups and the Drawer

        vdata = { id : {
                        'n' : name,
                        'l' : level,
                        'f' : parent,
                        'r' : resilience,
                        'i' : indicator data,
                        'c' : count (how many L3s reported in this region),
                        't' : count (how many L3s total in this region),
                        'q' : quality,
                        'p' : population,
                        's' : source (for population),
                        'b' : population breakdown (for L3s),
                        'd' : date last collected (for L3s),
                        'w' : collected by (for L3s),
                        'm' : images (for L3s),
                        }
                 }
    """

    try:
        id = request.args[0]
    except:
        raise HTTP(400)

    gtable = s3db.gis_location
    query = (gtable.id == id)
    row = db(query).select(gtable.name,
                           gtable.level,
                           gtable.parent,
                           gtable.L0,
                           gtable.L1,
                           gtable.L2,
                           #gtable.L3,
                           limitby=(0, 1)).first()
    if not row or not row.level:
        return ""
    script = ""
    data = dict(
            n = row.name,
            l = int(row.level[1]),
            f = row.parent,
            )
    vdata = {}
    level = row.level
    l0_name = row.L0
    l1_name = row.L1
    l2_name = row.L2
    #l3_name = row.L3
    stable = s3db.stats_aggregate
    resilience_id = s3db.vulnerability_resilience_id()
    if level != "L3":
        # We need to read the names & resilience of the next level down for the popup dropdown selector
        _level = int(level[1]) + 1
        query = (gtable.parent == id) & \
                (gtable.level == "L%s" % _level) & \
                (gtable.deleted == False)
        lquery = (stable.parameter_id == resilience_id) & \
                 (stable.agg_type == 4) & \
                 (stable.end_date == None) & \
                 (stable.location_id == gtable.id)
        left = stable.on(lquery)
        rows = db(query).select(gtable.id,
                                gtable.name,
                                stable.date,
                                stable.mean,
                                stable.ward_count,
                                stable.reported_count,
                                left=left)
        for row in rows:
            grow = row[gtable] 
            mean = row[stable].mean
            if mean is None:
                resilience = 0
            else:
                resilience = int(round(mean, 0))
            vdata[grow.id] = dict(r = resilience,
                                  n = grow.name,
                                  l = _level,
                                  f = id,
                                  )

    else:
        # We are an L3 already
        # Last Data Collected on t by c
        utable = auth.settings.table_user
        vtable = s3db.vulnerability_data
        query = (vtable.location_id == id)
        left = utable.on(utable.id == vtable.created_by)
        row = db(query).select(vtable.date,
                               utable.first_name,
                               utable.last_name,
                               orderby=~vtable.date,
                               left=left,
                               limitby=(0, 1)).first()
        if row:
            data["d"] = row[vtable].date.isoformat()
            user = row[utable]
            data["w"] = "%s %s" % (user.first_name, user.last_name)
        else:
            data["d"] = None
            data["w"] = None

    # Get the Resilience
    query = (stable.parameter_id == resilience_id) & \
            (stable.agg_type == 4) & \
            (stable.end_date == None) & \
            (stable.location_id == id) & \
            (stable.deleted == False)
    r = db(query).select(stable.date,
                         stable.mean,
                         stable.ward_count,
                         stable.reported_count,
                         orderby=~stable.date,
                         limitby=(0, 1)).first()

    if not r or r.mean is None:
        data["r"] = 0
        if level != "L3":
            data["c"] = 0
            data["q"] = "p"
            # Total number of L3s in this region
            # @ToDo: Below L0 we cannot guarantee uniqueness of Lx names
            query = (gtable.level == "L3") & \
                    (gtable.deleted == False) & \
                    (gtable.L0 == l0_name)
            if level == "L1":
                query = query & (gtable.L1 == l1_name)
            elif level == "L2":
                query = query & (gtable.L1 == l1_name) & \
                                (gtable.L2 == l2_name)
            #elif level == "L3":
            #    query = query & (gtable.L1 == l1_name) & \
            #                    (gtable.L2 == l2_name) & \
            #                    (gtable.L3 == l3_name)
            ward_count = db(query).count()
            data["t"] = ward_count
    else:
        data["r"] = int(round(r.mean, 0))
        # How many L3s have reported?
        reported_count = r.reported_count
        data["c"] = reported_count
        # Total number of L3s in this region
        ward_count = r.ward_count
        data["t"] = ward_count
        if level != "L3":
            # Calculate Quality
            if reported_count == 0 or ward_count == 0:
                q = "p"
            else:
                q = reported_count / ward_count
                if q < 0.25:
                    q = "p"
                elif q < 0.50:
                    q = "f"
                elif q < 0.75:
                    q = "m"
                else:
                    q = "s"
            data["q"] = q

    # Get the list of indicators
    #indicator_pids = s3db.vulnerability_ids()
    #(stable.parameter_id.belongs(indicator_pids))
    itable = s3db.vulnerability_indicator
    # Get the aggregated data for this location for all indicators
    query = (stable.location_id == id) & \
            (stable.parameter_id == itable.parameter_id) & \
            (itable.deleted == False)
    rows = db(query).select(stable.parameter_id,
                            stable.min,
                            stable.max,
                            stable.median)
    indicator_data = {}
    for row in rows:
        indicator_data[row.parameter_id] = dict(
                        min = row.min,
                        max = row.max,
                        med = row.median,
                       )
    data["i"] = indicator_data

    # Get the Demographic data for the location
    dtable = s3db.stats_demographic
    if level != "L3":
        # Just Population
        query = (dtable.name == "Population") & \
                (stable.location_id == id) & \
                (stable.parameter_id == dtable.parameter_id)
        row = db(query).select(stable.sum,
                               orderby=~stable.date,
                               limitby=(0, 1)).first()
        if row:
            p = row.sum
            data["p"] = int(p) if p else ""
        else:
            data["p"] = ""
    else:
        # L3: Population, Breakdowns & Source
        ddtable = s3db.stats_demographic_data
        doctable = s3db.doc_document
        srctable = s3db.stats_group
        query = (ddtable.location_id == id) & \
                (ddtable.parameter_id == dtable.parameter_id) & \
                (ddtable.group_id == srctable.id) & \
                (doctable.source_id == srctable.source_id)
        rows = db(query).select(dtable.id,
                                dtable.name,
                                ddtable.value,
                                doctable.name,
                                #ddtable.date,
                                orderby=~ddtable.date
                                )
        b = {}
        ids = []
        append = ids.append
        for row in rows:
            _id = row[dtable].id
            if _id in ids:
                # We're only interested in the most recent data per demographic
                continue
            append(_id)
            param = row[dtable].name
            if param == "Population":
                data["p"] = row[ddtable].value
                data["s"] = row[doctable].name
            else:
                # Breakdown
                b[id] = dict(
                        n = str(T(param)),
                        v = row[ddtable].value,
                        s = row[doctable].name,
                    )
        data["b"] = b

        # Images
        itable = s3db.doc_image
        ttable = s3db.pr_image_library
        gttable = s3db.stats_group_type
        query = (itable.source_id == srctable.source_id) & \
                (srctable.group_type_id == gttable.id) & \
                (gttable.name == "stats_image") & \
                (ttable.original_name == itable.file) & \
                (itable.location_id == id)
        left = utable.on(utable.id == itable.created_by)
        images = db(query).select(itable.file,
                                  itable.comments,
                                  ttable.new_name,
                                  utable.first_name,
                                  utable.last_name,
                                  left=left,
                                  orderby=~itable.date)
        m = []
        mappend = m.append
        for image in images:
            i = image[itable]
            user = image[utable]
            mappend([image[ttable].new_name, i.file, i.comments,
                     "%s %s" % (user.first_name, user.last_name)])
        data["m"] = m

    vdata[id] = data
    script = '''n=%s\n''' % json.dumps(vdata)
    response.headers["Content-Type"] = "application/json"
    return script

# -----------------------------------------------------------------------------
def rdata():
    """
        Controller to extract data for resilience analysis line graph

        returns a JavaScript like:

        r={"location_id":
                {"year":
                    {"indicator_index": [value, deviation]}
                }
          }

        where indicator_index is 0 for the overall resilience (mean), or
        1-10 for the individual indicators (=index in the list + 1).

        Any data which are not available from the db will be omitted (to
        save bandwidth) - the client-side script must detect any missing
        keys itself.

        @todo: this controller must make sure that there is always a mean
               (overall resilience) in each set => calculate if not present.
    """

    response.headers["Content-Type"] = "application/json"

    if not len(request.args):
        return '''n={}'''
    else:
        locations = list(set([a for a in request.args if a.isdigit()]))

    vars = request.get_vars
    fyear = None
    lyear = None
    if "after" in vars:
        try:
            fyear = int(vars["after"])
        except ValueError:
            pass
    if "before" in vars:
        try:
            lyear = int(vars["before"])
        except ValueError:
            pass

    if lyear and fyear and lyear > fyear:
        lyear, fyear = fyear, lyear
    if fyear:
        fdate = datetime.datetime(fyear, 1, 1)
    else:
        fdate = None
    if lyear:
        ldate = datetime.datetime(lyear+1, 1, 1)
    else:
        ldate = request.utcnow

    resilience_id = s3db.vulnerability_resilience_id()
    indicator_pids = s3db.vulnerability_ids()
    pos = Storage([(indicator_pids[i], i+1)
                   for i in xrange(len(indicator_pids))])
    pos[resilience_id] = 0

    stable = s3db.stats_aggregate
    query = (stable.deleted != True) & \
            (((stable.parameter_id == resilience_id) & \
              (stable.agg_type == 4)) |
             (stable.parameter_id.belongs(indicator_pids)))

    if len(locations) == 1:
        query &= (stable.location_id == locations[0])
    else:
        query &= (stable.location_id.belongs(locations))
    if fyear:
        query &= (stable.date >= fdate)
    if lyear is None or lyear == request.utcnow.year:
        query &= ((stable.end_date < ldate) | (stable.end_date == None))
    else:
        query &= (stable.end_date < ldate)

    rows = db(query).select(stable.location_id,
                            stable.parameter_id,
                            stable.date,
                            stable.mean,
                            stable.median,
                            stable.mad,
                            orderby=~stable.date)

    keys = []
    seen = keys.append
    data = dict()
    for row in rows:
        l = row.location_id
        y = row.date.year
        p = pos[row.parameter_id]
        if (l, y, p) in keys:
            continue
        seen((l, y, p))

        if p == pos[resilience_id]:
            val = int(round(row.mean, 0))
        else:
            val = row.median
        dev = row.mad

        if l not in data:
            ldata = data[l] = dict()
        else:
            ldata = data[l]
        if y not in ldata:
            ydata = ldata[y] = dict()
        else:
            ydata = ldata[y]
        ydata[p] = (val, dev)

    script = '''r=%s\n''' % json.dumps(data)
    return script

# -----------------------------------------------------------------------------
def reportFilter(filter_request, loc_id):
    """
        Helper function to extract the selections from the side panel
        and generate a resource filter
    """

    sgtable = s3db.stats_group
    sgtype = s3db.stats_group_type
    gistable = s3db.gis_location
    prtable = s3db.pr_person
    query = (sgtable.deleted != True) &\
            (sgtable.group_type_id == sgtype.id)
    if loc_id != -1:
        child_locations = gis.get_children(loc_id)
        if len(child_locations) == 0:
            query &= (sgtable.location_id == loc_id)
        else:
            child_ids = [row.id for row in child_locations]
            query &= (sgtable.location_id.belongs(child_ids))
    if filter_request["from_date"]:
        query &= (sgtable.date >= filter_request["from_date"])
    if filter_request["to_date"]:
        query &= (sgtable.date <= filter_request["to_date"])
    indicator = (sgtype.name == "stats_vca")
    if "indicator" in filter_request:
        indicator |= (sgtype.name == "vulnerability_indicator")
    if "demographics" in filter_request:
        indicator |= (sgtype.name == "stats_demographic")
    if "map" in filter_request:
        indicator |= (sgtype.name == "stats_map")
    if "images" in filter_request:
        indicator |= (sgtype.name == "stats_image")
    if "reports" in filter_request:
        indicator |= (sgtype.name == "stats_other")
    query &= indicator
    if "myReports" in filter_request:
        user = auth.s3_logged_in_person()
        query &= ((sgtable.approved_by == user) | (sgtable.created_by == user))
    if "text" in filter_request and filter_request["text"] != "":
        text = "%%%s%%" % filter_request["text"].lower()
        query &= (sgtable.location_id == gistable.id)
        query &= (sgtable.created_by == prtable.id)
        query &= ((gistable.name.lower().like(text))
                  | (prtable.first_name.lower().like(text))
                  | (prtable.last_name.lower().like(text)))
    return query

# -----------------------------------------------------------------------------
def reportDataTable(request):
    """
        Helper function to return the dataTable that uses the selected
        filter options
    """

    from s3.s3utils import S3DataTable
    sgtable = s3db.stats_group
    gtable = s3db.gis_location

    # -------------------------------------------------------------------------
    # Set up custom represents
    # -------------------------------------------------------------------------
    def location_repr(id):
        """
            Return the location name (commune) wrapped in a span
        """
        if not id:
            repr_text = messages.NONE
        else:
            table = db.gis_location
            row = db(table.id == id).select(table.name,
                                            table.level,
                                            table.L1,
                                            table.L2,
                                            limitby=(0, 1)).first()
            if not row:
                repr_text = messages.UNKNOWN_OPT
            if row.level == "L3":
                repr_text = "%s (%s)" % (row.L2, row.name)
            elif row.level == "L2":
                repr_text = "%s (%s)" % (row.L1, row.name)
            elif row.level == "L1":
                repr_text = "%s (%s)" % (row.L0, row.name)
            else:
                repr_text = row.name
        return SPAN(repr_text, _class="communeCell")

    # -------------------------------------------------------------------------
    def submitted_repr(id):
        """
            Return the initial of the first name and the complete last name
        """
        if not id:
            repr_text = T("Imported data")
        else:
            table = db.pr_person
            row = db(table.id == id).select(table.first_name,
                                            table.last_name,
                                            limitby=(0, 1)).first()
            if row:
                repr_text = "%s. %s" % (row.first_name[0], row.last_name)
            else:
                repr_text = messages.UNKNOWN_OPT
        return repr_text

    # -------------------------------------------------------------------------
    def approved_repr(id):
        """
            Return the initials of the first and the last name
        """

        if id is None:
            repr_text = T("Approval pending")
        elif id == 0:
            repr_text = T("Approved")
        else:
            table = db.pr_person
            row = db(table.id == id).select(table.first_name,
                                            table.last_name,
                                            limitby=(0, 1)).first()
            if row:
                repr_text = T("Approved by %(first_initial)s.%(last_initial)s") % \
                    dict(first_initial = row.first_name[0],
                         last_initial = row.last_name[0])
            else:
                repr_text = messages.UNKNOWN_OPT
        return repr_text

    # -------------------------------------------------------------------------
    def action_repr(id):
        """
            Return the action button for this row
        """

        if id is None:
            repr_text = messages.NONE
        else:
            row = s3db.stats_group[id]
            if row.approved_by != None:
                repr_text = A(T("View"),
                              _id = id,
                              _class = "viewButton",
                              _href = "javascript:viewReportDetails(%s);" % id
                              )
            else:
                repr_text = A(T("Review"),
                              _id = id,
                              _class = "reviewButton",
                              _href = "javascript:showReportDetails(%s);" % id
                              )

            repr_text.append(A(T("Close"),
                               _class = "closeReviewButton",
                               _href = "javascript:hideReportDetails(%s);" % id
                               ))
        return repr_text

    sgtable.location_id.represent = location_repr
    sgtable.created_by.represent = submitted_repr
    sgtable.approved_by.represent = approved_repr
    sgtable.id.represent = action_repr

    filter_request = request.post_vars
    loc_id = -1
    loc_level = -1
    if filter_request:
        if "location_id" in filter_request:
            loc_id = int(filter_request["location_id"])
            query = (gtable.id == loc_id)
            row = db(query).select(gtable.level,
                                   limitby=(0, 1)).first()
            if not row or not row.level:
                loc_level = -1
            else:
                loc_level = row.level
        filter = reportFilter(filter_request, loc_id)
    #############################################################
    # Note if list_fields are changed here then they also need
    # to be changed in index, where the table is initialised
    #############################################################
    if loc_level == -1:
        list_fields = [(T("Action"), "id"),
                       (T("Date"), "date"),
                       (T("Commune Name"), "location_id"),
                       "location_id$L0",
                       "group",
                       (T("Type"), "group_type_id"),
                       (T("Submitted by"), "created_by"),
                       (T("Status"), "approved_by"),
                       ]
        group_field = "gis_location.L0"
    elif loc_level == "L0":
        list_fields = [(T("Action"), "id"),
                       (T("Date"), "date"),
                       (T("Commune Name"), "location_id"),
                       "location_id$L1",
                       "group",
                       (T("Type"), "group_type_id"),
                       (T("Submitted by"), "created_by"),
                       (T("Status"), "approved_by"),
                       ]
        group_field = "gis_location.L1"
    elif loc_level == "L1":
        list_fields = [(T("Action"), "id"),
                       (T("Date"), "date"),
                       (T("Commune Name"), "location_id"),
                       "location_id$L2",
                       "group",
                       (T("Type"), "group_type_id"),
                       (T("Submitted by"), "created_by"),
                       (T("Status"), "approved_by"),
                       ]
        group_field = "gis_location.L2"
    elif loc_level == "L2":
        list_fields = [(T("Action"), "id"),
                       (T("Date"), "date"),
                       (T("Commune Name"), "location_id"),
                       "location_id$L3",
                       "group",
                       (T("Type"), "group_type_id"),
                       (T("Submitted by"), "created_by"),
                       (T("Status"), "approved_by"),
                       ]
        group_field = "gis_location.L3"
    elif loc_level == "L3":
        list_fields = [(T("Action"), "id"),
                       (T("Date"), "date"),
                       (T("Commune Name"), "location_id"),
                       "location_id$L3",
                       "group",
                       (T("Type"), "group_type_id"),
                       (T("Submitted by"), "created_by"),
                       (T("Status"), "approved_by"),
                       ]
        group_field = "gis_location.L3"

    # Ensure that we also get the records awaiting for approval
    resource = s3db.resource("stats_group", unapproved=True)
    filter_request = request.post_vars
    if filter_request:
        resource.add_filter(filter)
    filteredrows = resource.count()
    if filteredrows > 0:
        rows = resource.select(list_fields,
                               orderby=~sgtable.date,
                               start=0,
                               limit=filteredrows,
                               )
        data = resource.extract(rows,
                                list_fields,
                                represent=True,
                                )
        # The types are fixed and will always be displayed (even if empty)
        type_totals = {"Approval pending" : 0,
                       "VCA Report" : 0,
                       "Report" : 0
                       }
        # Calculate the report group totals
        location_totals = {}
        for item in data:
            # Collect the type totals
            group = item["stats_group.group"]
            if not group:
                group = "Report"
            type_totals[group] += 1
            # Collect the L2 sub totals
            loc_code = "%s_%s" % (group, item[group_field])
            if loc_code in location_totals:
                location_totals[loc_code] += 1
            else:
                location_totals[loc_code] = 1
        rfields = resource.resolve_selectors(list_fields)[0]
        dt = S3DataTable(rfields, data, orderby=~sgtable.date)
        dt.defaultActionButtons(resource)
        if request.extension == "html":
            level_1_titles = [["Approval pending", T("Approval pending")],
                              ["VCA Report", T("VCA Report")],
                              ["Report", T("Report")],
                              ]
        if request.extension == "html":
            report = dt.html(filteredrows,
                             filteredrows,
                             "report",
                             dt_displayLength = filteredrows,
                             dt_pagination = "false",
                             dt_bFilter = "false",
                             dt_sDom = "t",
                             dt_group = [3, 4],
                             dt_group_totals = [type_totals, location_totals],
                             dt_group_titles = [level_1_titles],
                             dt_ajax_url = URL(c="vulnerability",
                                               f="report",
                                               extension="aadata",
                                               vars={"id": "report"},
                                               ),
                             dt_action_col = -1,
                             dt_group_space = "true",
                             dt_shrink_groups = "accordion",
                             dt_group_types = ["text", "none"],
                             )
        reportCount = T("%(count)s Entries Found") % dict(count=filteredrows)
        report.append(INPUT(_type="hidden",
                            _id="reportCount",
                            _name="config",
                            _value=reportCount))
    else:
        report = ""
        if filteredrows > 0:
            report = dt.json("report",
                             int(request.vars.sEcho),
                             filteredrows,
                             filteredrows,
                             dt_group_totals=[type_totals],
                             )
    return str(report)

# -----------------------------------------------------------------------------
def getReportDetails(id, buttonsRequired):
    """
        Method to get the details of a report from the stats_group id

        It will build the custom display, which is essentially a form
        wrapped around a table, if buttons are required then they will be added
        allowing for the report to be approved or rejected.
    """

    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    ss_table = s3db.stats_source
    query = (sgtable.id == id) & \
            (sgtable.group_type_id == sgt_table.id)
    rows = db(query).select(sgt_table.name,
                            sgtable.source_id,
                            limitby=(0, 1)).first()
    reportType = rows.stats_group_type.name
    reportSource_id = rows.stats_group.source_id
    valid = True
    if reportType == "vulnerability_indicator":
        # Get the data for this report
        vdtable = s3db.vulnerability_data
        vitable = s3db.vulnerability_indicator
        query = (vdtable.deleted == False) & \
                (vdtable.group_id == id) & \
                (vitable.parameter_id == vdtable.parameter_id)
        rows = db(query).select(vdtable.value,
                                vitable.name,
                                orderby=vitable.posn)
        # Build the custom table
        table = TABLE(TR(TH(_class="indicatorLabels"),
                         TH(DIV(1), _class="indicator1"),
                         TH(DIV(2), _class="indicator2"),
                         TH(DIV(3), _class="indicator3"),
                         TH(DIV(4), _class="indicator4"),
                         TH(DIV(5), _class="indicator5"),
                         ),
                      TR(TH(),
                         TH(SPAN(XML("&larr;"), _class="arrow"),
                            " %s" % T("LOW RESILIENCE"),
                            _colspan=2),
                         TH(" %s" % T("HIGH RESILIENCE"),
                            SPAN(XML("&rarr;"), _class="arrow"),
                            _class="highResilienceLabel",
                            _colspan=3)
                        ),
                      _class="indicatorsTable")

        mark = XML("<mark>*</mark>")
        tr_class = "white"
        for row in rows:
            tr_class = "gray" if tr_class == "white" else "white"
            tr = TR(_class=tr_class)
            name = row.vulnerability_indicator.name
            td = TD(mark, _class="indicatorLabels")
            td.append(name)
            tr.append(td)
            value = int(row.vulnerability_data.value)
            for i in range(5):
                option = INPUT(_type = "radio",
                               _class = "indicator%d" % (i + 1),
                               _name = name,
                               _value = i + 1,
                               value = value,
                               _disabled = "disabled",
                               )
                tr.append(option)
            table.append(tr)
    elif reportType == "stats_demographic":
        # Get the data for this report
        ddtable = s3db.stats_demographic_data
        sdtable = s3db.stats_demographic
        query = (ddtable.deleted == False) & \
                (ddtable.group_id == id) & \
                (sdtable.parameter_id == ddtable.parameter_id)
        rows = db(query).select(ddtable.value,
                                ddtable.location_id,
                                sdtable.name,
                                orderby = sdtable.name)
        reportRow = ss_table[reportSource_id]
        if reportRow:
            reportSource = reportRow.name
        else:
            reportSource = ""
        # Build the custom table
        table = TABLE(_class = "demographicsTable")
        table.append(TR(TD(reportSource, _colspan=3)))

        tr_class = "grey"
        for row in rows:
            tr_class = "grey" if tr_class == "white" else "white"
            tr = TR(_class = tr_class)
            name = row.stats_demographic.name
            tr.append(TD(name, _class = "demoLabel"))
            value = IS_INT_AMOUNT().represent(row.stats_demographic_data.value)
            tr.append(TD(value, _class = "demoStatistic"))
            location = s3db.gis_location_represent(row.stats_demographic_data.location_id,
                                                   show_link = False)
            tr.append(TD(location, _class = "demoSource"))
            table.append(tr)
    elif reportType == "stats_map" or reportType == "stats_image":
        ditable = s3db.doc_image
        query = (ditable.source_id == reportSource_id)
        record = db(query).select(limitby=(0, 1)).first()
        if record:
            size = (250, 250)
            image = s3db.pr_image_represent(record.file, size=size)
            size = s3db.pr_image_size(image, size)
            desc = DIV(record.comments, _class="imageDesc")
            filename = record.name
            url_small = URL(c="default", f="download", args=image)
            alt = record.comments if record.comments else filename
            thumb = IMG(_src=url_small,
                        _alt=alt,
                        _width=size[0],
                        _height=size[1]
                        )
            url_full = URL(c="default", f="download", args=record.file)
            download = A(T("Download"), _class="download", _href=url_full)
            view = A(T("View full size"),
                     _class="download",
                     _href=URL(c="vulnerability", f="view_image", args=record.id),
                     _target="blank")
            table = TABLE(_class = "imageTable")
            table.append(TR(TD(thumb, _colspan=4)))
            table.append(TR(TD(desc),
                            TD(download),
                            TD(DIV(" | ", _class="divider")),
                            TD(view),
                            _class="mapRow"))
        else:
            valid = False
    elif reportType == "stats_other" or reportType == "stats_vca":
        doctable = s3db.doc_document
        query = (doctable.source_id == reportSource_id)
        record = db(query).select(limitby=(0, 1)).first()
        if record:
            desc = DIV(record.name, _class="imageDesc")
            url = URL(c="default", f="download", args=record.file)
            download = A(T("Download"), _class="download", _href=url)
            table = TABLE(_class="imageTable")
            table.append(TR(TD(desc),
                            TD(download),
                            _class="mapRow"))
        else:
            valid = False
    else:
        valid = False
    # Place the table in a form and attach the buttons (if required)
    form = FORM(_id="form%s" % id)
    if valid:
        form.append(table)
    else:
        form.append(DIV(T("No data available"), _class="mapRow"))
    if buttonsRequired:
        if valid:
            form.append(INPUT(_type="button", _name="Approve%s" % id,
                              _value="Approve", _class="approveButton"))
        form.append(INPUT(_type="button", _name="Decline%s" % id,
                          _value="Decline", _class="declineButton"))
    return str(form)

# -----------------------------------------------------------------------------
def approveReport(id):
    """
        Function to approve a report
    """

    # Approve the doc source entity record
    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    resource = s3db.resource("stats_group", id=id, unapproved=True)
    resource.approve()
    # find the type of report that we have
    query = (sgtable.id == id) & \
            (sgtable.group_type_id == sgt_table.id)
    record = db(query).select(sgt_table.name,
                              sgt_table.stats_group_instance,
                              limitby=(0, 1)).first()
    rec_type = record.name
    if rec_type == "vulnerability_indicator" or rec_type == "stats_demographic":
        # Find the type of stats source record that we have
        if rec_type == "vulnerability_indicator":
            query = (s3db.vulnerability_data.group_id == id)
            resource = s3db.resource("vulnerability_data", filter=query, unapproved=True)
            resource.approve()
        if rec_type == "stats_demographic":
            query = (s3db.stats_demographic_data.group_id == id)
            resource = s3db.resource("stats_demographic_data", filter=query, unapproved=True)
            resource.approve()
        # Approve the stats_data records
        query = (s3db.stats_data.group_id == id)
        resource = s3db.resource("stats_data", filter=query, unapproved=True)
        resource.approve()
        s3task.async("stats_group_clean")
        return True
    rec_instance = record.stats_group_instance
    if rec_instance == "doc_image":
        query = (sgtable.id == id) &\
                (s3db.doc_image.source_id == sgtable.source_id)
        resource = s3db.resource("doc_image", filter=query, unapproved=True)
        resource.approve()
        return True
    elif rec_instance == "doc_document":
        query = (sgtable.id == id) &\
                (s3db.doc_document.source_id == sgtable.source_id)
        resource = s3db.resource("doc_document", filter=query, unapproved=True)
        resource.approve()
        return True
    return False

# -----------------------------------------------------------------------------
def declineReport(id):
    """
        Function to decline to approve a report
    """

    # Decline the doc source entity record
    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    # Find the type of report that we have
    query = (sgtable.id == id) & \
            (sgtable.group_type_id == sgt_table.id)
    record = db(query).select(sgt_table.name,
                              sgt_table.stats_group_instance,
                              limitby=(0, 1)).first()
    rec_type = record.name
    # Now that we have all the data reject the report
    resource = s3db.resource("stats_group", id=id, unapproved=True)
    resource.reject()
    if rec_type == "vulnerability_indicator" or rec_type == "stats_demographic":
        # Approve the stats_data records
        query = (s3db.stats_data.group_id == id)
        resource = s3db.resource("stats_data", filter=query, unapproved=True)
        resource.reject()
        # Find the type of stats source record that we have
        if rec_type == "vulnerability_indicator":
            query = (s3db.vulnerability_data.group_id == id)
            resource = s3db.resource("vulnerability_data", filter=query, unapproved=True)
            resource.reject()
        if rec_type == "stats_demographic":
            query = (s3db.stats_demographic_data.group_id == id)
            resource = s3db.resource("stats_demographic_data", filter=query, unapproved=True)
            resource.reject()
        return True
    rec_instance = record.stats_group_instance
    if rec_instance == "doc_image":
        query = (sgtable.id == id) &\
                (s3db.doc_image.source_id == sgtable.source_id)
        resource = s3db.resource("doc_image", filter=query, unapproved=True)
        resource.reject()
        return True
    elif rec_instance == "doc_document":
        query = (sgtable.id == id) &\
                (s3db.doc_document.source_id == sgtable.source_id)
        resource = s3db.resource("doc_document", filter=query, unapproved=True)
        resource.reject()
        return True
    return False

# -----------------------------------------------------------------------------
def report():
    """ Not a REST Controller """

    s3.no_formats = True
    if request.args(0) == "filter":
        report = reportDataTable(request)
        data = json.dumps(report)
    elif request.args(0) == "review" or request.args(0) == "view":
        id = request.get_vars.id
        buttonsRequired = request.args(0) == "review"
        reportDetails = getReportDetails(id, buttonsRequired=buttonsRequired)
        data = json.dumps(reportDetails)
    elif request.args(0) == "approve":
        # Check authorization
        permitted = auth.s3_has_permission
        authorised = permitted("approve", "stats_group")
        if not authorised:
            data = json.dumps(str(T("You are not permitted to approve documents")))
        else:
            id = request.post_vars.id
            if approveReport(id):
                report = reportDataTable(request)
                data = json.dumps(report)
            else:
                data = json.dumps(str(T("Failed to approve")))
    elif request.args(0) == "decline":
        id = request.post_vars.id
        if declineReport(id):
            report = reportDataTable(request)
            data = json.dumps(report)
        else:
            data = json.dumps(str(T("Decline failed")))
    else:
        filter = {}
        date_widget = S3DateWidget(format="yy-mm-dd", future=0)
        to_date = Field("to_date")
        to_date._tablename = ""
        from_date = Field("from_date")
        from_date._tablename = ""
        filter["to_date"] = str(date_widget(to_date, None))
        filter["from_date"] = str(date_widget(from_date, None))
        report = reportDataTable(request)
        data_dict = {"filter" : filter,
                     "report" : report
                     }
        data = json.dumps(data_dict)

    response.headers["Content-Type"] = "application/json"
    return data

# -----------------------------------------------------------------------------
def submitData():
    """ Controller to manage the ajax-import of vulnerability data """

    # Get the action to be performed
    action = request.vars.action
    if action == "vulnerability":
        return import_vul_create()
    elif action == "vulnerability_part1":
        return import_vul_part1()
    elif action == "vulnerability_part2":
        return import_vul_part2()
    elif action == "map":
        return import_image(action)
    elif action == "image":
        return import_image(action)
    elif action == "other":
        return import_image(action)
    elif action == "vca":
        return import_image(action)
    elif action == "demographics":
        return import_demo_create()
    elif action == "demographics_part1":
        return import_demo_part1()
    elif action == "demographics_part2":
        return import_demo_part2()

# -----------------------------------------------------------------------------
def import_vul_create():
    """ Controller to add a new set of vulnerability indicators """

    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    vd_table = s3db.vulnerability_data
    # first add the stats_group
    date = request.utcnow
    creator = auth.s3_logged_in_person()
    location_id = request.vars.location
    group_type = db(sgt_table.name == "vulnerability_indicator").select(sgt_table.id,
                                                                        limitby=(0, 1)
                                                                        ).first().id
    sg_id = sgtable.insert(date = date,
                           location_id = location_id,
                           group_type_id = group_type,
                           created_by = creator
                           )
    # Get the list of indicators
    itable = s3db.vulnerability_indicator
    rows = db(itable.deleted == False).select(itable.posn,
                                              itable.parameter_id,
                                              orderby=itable.posn)
    update_super = s3db.update_super
    for row in rows:
        vd_id = vd_table.insert(parameter_id = row.parameter_id,
                                location_id = location_id,
                                value = request.vars[str(row.posn)],
                                date = date,
                                group_id = sg_id,
                                created_by = creator
                                )
        update_super(vd_table, dict(id=vd_id))

# -----------------------------------------------------------------------------
def import_vul_part1():
    """
        Controller to manage the first phase of the import of vulnerability indicators
    """

    from gluon.serializers import json
    try:
        file = request.vars.file.file
    except:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(T("Error File missing"))})

    # Check authorization
    permitted = auth.s3_has_permission
    authorised = permitted("create", "vulnerability_data")
    if not authorised:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(T("You are not permitted to upload files"))})

    from lxml import etree
    from datetime import datetime

    creator = auth.s3_logged_in_person()
    output = s3_rest_controller("vulnerability", "data",
                                csv_stylesheet="data.xsl")
    if "Error" in output:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(output["Error"])})
    upload_id = output[0]
    item_ids = output[1]
    data = output[2]
    # This gets the data back from resource.extract() for all items
    ele_dict = {}
    # Collect all the data and group the vulnerability indicators
    for value in data:
        error = value["s3_import_item.error"]
        if error:
            response.headers["Content-Type"] = "application/json"
            return json({"Error": error})
        group_tuid = "Group"
        ele = value["s3_import_item.element"]
        ele = s3xml.xml_decode(ele)
        try:
            element = etree.fromstring(ele)
        except:
            return T("No valid data in the file")
        # Get all the components
        ctablename = element.get("name")
        data_dict = {}
        data = element.findall("data")
        for item in data:
            f = item.get("field", None)
            v = item.get("value", None)
            data_dict[f] = v
        references = element.findall("reference")
        for reference in references:
            f = reference.get("field", None)
            r = reference.get("resource", None)
            t = reference.get("tuid", None)
            data_dict[f] = (r, t)
            if f == "group_id":
                group_tuid = t
        if group_tuid in ele_dict:
            ele_dict[group_tuid].append(data_dict)
        else:
            ele_dict[group_tuid] = [data_dict]
    # Now condense the data down to just what is required
    # keyed on the group_tuid
    # date, created_by, location, and a dict of indicators [param and value]
    loc_label = gis.get_location_hierarchy("L4")
    data_list = []
    for (key,group) in ele_dict.items():
        row = group[0]
        group_dict = {}
        group_dict["group"] = key
        group_dict["date"] = datetime.strptime(row["date"], "%Y-%m-%d").strftime("%d-%b-%y")
        group_dict["created_by"] = creator
        loc = row["location_id"][1][12:] # strip location L#: from the tuid
        loc = "%s %s" % (loc, loc_label)
        group_dict["location"] = loc
        indicator_dict = {}
        param_len = len(row["parameter_id"][0]) +1 # include the separator
        for row in group:
            param = row["parameter_id"][1][param_len:]
            indicator_dict[param] = row["value"]
        group_dict["data"] = indicator_dict
        data_list.append(group_dict)
    response.headers["Content-Type"] = "application/json"
    return json({"upload_id" : upload_id,
                 "items" : item_ids,
                 "data" : data_list
                 })

# -----------------------------------------------------------------------------
def import_vul_part2():
    """
        Controller to manage the second phase of the import of vulnerability indicators
    """

    job_id = request.vars.job
    if not job_id:
        return T("Error No Job ID's provided")

    output = s3_rest_controller("vulnerability", "data",
                                csv_stylesheet="data.xsl")
    totalRecords = output[0]
    totalErrors = output[1]
    totalIgnored = output[2]

    from gluon.serializers import json

    response.headers["Content-Type"] = "application/json"
    return json({"totalRecords" : totalRecords,
                 "totalErrors" : totalErrors,
                 "totalIgnored" : totalIgnored
                 })

# -----------------------------------------------------------------------------
def import_image(action):
    """
        Controller to import a report
    """
    if action == "map" or action == "image":
        doc_table = s3db.doc_image
    else:
        doc_table = s3db.doc_document
    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    di_file = doc_table.file
    file = request.vars.file
    real_filename = file.filename
    new_filename = di_file.store(file, real_filename)
    date = request.utcnow
    creator = auth.s3_logged_in_person()
    location_id = request.vars.location
    desc = request.vars.desc
    doc_id = doc_table.insert(file = new_filename,
                              name = real_filename,
                              date = date,
                              comments = desc,
                              location_id = location_id,
                              created_by = creator
                              )
    s3db.update_super(doc_table, dict(id=doc_id))
    source_id = doc_table[doc_id].source_id
    if action == "map":
        group = "stats_map"
    elif action == "image":
        group = "stats_image"
    elif action == "other":
        group = "stats_other"
    elif action == "vca":
        group = "stats_vca"
    if action == "map" or action == "image":
        # Create a thumbnail of the image
        s3db.pr_image_resize(file.file,
                             new_filename,
                             real_filename,
                             (250, 250),
                             )
    group_type = db(sgt_table.name == group).select(sgt_table.id,
                              limitby=(0, 1)).first().id
    sgtable.insert(source_id = source_id,
                   group_type_id = group_type,
                   date = date,
                   location_id = location_id,
                   created_by = creator
                   )

# -----------------------------------------------------------------------------
def import_demo_create():
    """
        Controller to import demographic data
    """

    sgtable = s3db.stats_group
    sgt_table = s3db.stats_group_type
    ss_table = s3db.stats_source
    sd_table = s3db.stats_demographic
    sdd_table = s3db.stats_demographic_data
    dd_table = s3db.doc_document
    update_super = s3db.update_super

    # first get the demographic data and source
    last_source = ""
    source_list = {} # the source_id for this source
    group_list = {} # the group_id for this source
    demo_string_list = ["Population",
                        "Male",
                        "Female",
                        "Over 60",
                        "Under 5",
                        "Households",
                        "Households below poverty line"
                        ]
    demographics_list = []
    data = []
    for x in range(7):
        source = request.vars["sourceField%s" % x]
        if source == "":
            source = last_source
        else:
            last_source = source
        data.append((request.vars["demoField%s" % x],
                     source))
        if source != "":
            if source not in source_list:
                record = db(ss_table.name == source).select(ss_table.id,
                                                            limitby=(0, 1)
                                                            ).first()
                if record == None:
                    # Save the source details & SE
                    doc_id = dd_table.insert(name = source)
                    update_super(dd_table, dict(id=doc_id))
                    source_id = dd_table[doc_id].source_id
                else:
                    source_id = record.source_id
                source_list[source] = source_id
        else:
            source_list[""] = None # added so that a group with no source will be created
    # Now get the parameter_id in demo_string_list order
    sd_rows = db().select(sd_table.name,
                          sd_table.parameter_id)
    demo_recs = {}
    for record in sd_rows:
        demo_recs[record.name] = record.parameter_id
    for demo_string in demo_string_list:
        if demo_string in demo_recs:
            demographics_list.append(demo_recs[demo_string])
        else:
            demographics_list.append(None) # Should never have this
    # Now get the stats_group
    date = request.vars.reportDate
    creator = auth.s3_logged_in_person()
    location_id = request.vars.location
    group_type = db(sgt_table.name == "stats_demographic").select(sgt_table.id,
                                                                  limitby=(0, 1)
                                                                  ).first().id
    for (source, id) in source_list.items():
        sg_id = sgtable.insert(date = date,
                               location_id = location_id,
                               group_type_id = group_type,
                               source_id = id,
                               created_by = creator
                               )
        group_list[source] = sg_id
    # Now save the demographic data
    for x in range(7):
        sdd_id = sdd_table.insert(parameter_id = demographics_list[x],
                                  location_id = location_id,
                                  value = data[x][0],
                                  date = date,
                                  group_id = group_list[data[x][1]],
                                  created_by = creator
                                  )
        update_super(sdd_table, dict(id=sdd_id))

# -----------------------------------------------------------------------------
def import_demo_part1():
    """
        Controller to manage the first phase of the import of demographic data
    """

    from gluon.serializers import json
    try:
        file = request.vars.file.file
    except:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(T("Error File missing"))})

    # Check authorization
    permitted = auth.s3_has_permission
    authorised = permitted("create", "stats_demographic_data")
    if not authorised:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(T("You are not permitted to upload files"))})

    from lxml import etree
    from datetime import datetime

    creator = auth.s3_logged_in_person()
    request.controller = "stats" # Need to set the controller to stats
    output = s3_rest_controller("stats", "demographic_data",
                                csv_stylesheet="demographic_data.xsl")
    if "Error" in output:
        response.headers["Content-Type"] = "application/json"
        return json({"Error": str(output["Error"])})
    upload_id = output[0]
    item_ids = output[1]
    data = output[2]
    # This gets the data back from resource.extract() for all items
    ele_dict = {}
    # Collect all the data and group the demographic data
    for value in data:
        error = value["s3_import_item.error"]
        if error:
            response.headers["Content-Type"] = "application/json"
            return json({"Error": error})
        group_tuid = "Group"
        ele = value["s3_import_item.element"]
        ele = s3xml.xml_decode(ele)
        try:
            element = etree.fromstring(ele)
        except:
            return T("No valid data in the file")
        # Get all the components
        ctablename = element.get("name")
        data_dict = {}
        data = element.findall("data")
        for item in data:
            f = item.get("field", None)
            v = item.get("value", None)
            data_dict[f] = v
        references = element.findall("reference")
        for reference in references:
            f = reference.get("field", None)
            r = reference.get("resource", None)
            t = reference.get("tuid", None)
            data_dict[f] = (r, t)
            if f == "group_id":
                group_tuid = t
        if group_tuid in ele_dict:
            ele_dict[group_tuid].append(data_dict)
        else:
            ele_dict[group_tuid] = [data_dict]
    # Now condense the data down to just what is required
    # keyed on the group_tuid
    # date, created_by, location, and a list of values and source
    loc_label = gis.get_location_hierarchy()
    data_list = []
    for (key, group) in ele_dict.items():
        row = group[0]
        group_dict = {}
        group_dict["group"] = key
        group_dict["source"] = key.split("/")[3]
        group_dict["date"] = datetime.strptime(row["date"], "%Y-%m-%d").strftime("%d-%b-%y")
        group_dict["created_by"] = creator
        loc = row["location_id"][1]
        loc_name = loc[12:] # strip location L#: from the tuid
        loc_level = loc[9:11]
        try:
            loc = "%s %s" % (loc_name, loc_label[loc_level])
        except:
            # Probably a country in which case try and get it from the db
            gis_table = s3db.gis_location
            rec = db(gis_table.uuid == loc).select(gis_table.name,
                                                   limitby=(0, 1)
                                                   ).first()
            if rec == None:
                loc = loc_name
            else:
                loc = rec.name
        group_dict["location"] = loc
        indicator_dict = {}
        param_len = len(row["parameter_id"][0]) + 1 # include the separator
        for row in group:
            param = row["parameter_id"][1][param_len:]
            indicator_dict[param] = row["value"]
        group_dict["data"] = indicator_dict
        data_list.append(group_dict)
    response.headers["Content-Type"] = "application/json"
    return json({"upload_id" : upload_id,
                 "items" : item_ids,
                 "data" : data_list
                 })

# -----------------------------------------------------------------------------
def import_demo_part2():
    """
        Controller to manage the second phase of the import of demographic data
    """

    job_id = request.vars.job
    if not job_id:
        return T("Error No Job ID's provided")

    request.controller = "stats" # Need to set the controller to stats
    output = s3_rest_controller("stats", "demographic_data",
                                csv_stylesheet="demographic_data.xsl")
    totalRecords = output[0]
    totalErrors = output[1]
    totalIgnored = output[2]

    from gluon.serializers import json

    response.headers["Content-Type"] = "application/json"
    return json({"totalRecords" : totalRecords,
                 "totalErrors" : totalErrors,
                 "totalIgnored" : totalIgnored
                 })

# -----------------------------------------------------------------------------
def indicator():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def aggregated_indicator():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def data():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def handdrawn():
    """ REST Controller for Hand-drawn Maps """

    gtable = s3db.stats_group
    gttable = s3db.stats_group_type
    s3.filter = (s3db.doc_image.source_id == gtable.source_id) & \
                (gtable.group_type_id == gttable.id) & \
                (gttable.name == "stats_map")

    return s3_rest_controller("doc", "image")

# -----------------------------------------------------------------------------
def view_image():
    """
        View a Fullscreen version of an Image - called from Reports
    """

    table = s3db.doc_image
    id = request.args[0]
    record = db(table.id == id).select(table.name,
                                       table.file,
                                       table.comments,
                                       limitby=(0, 1)).first()
    desc = DIV(record.comments, _class="imageDesc")
    filename = record.name
    url = URL(c="default", f="download", args=record.file)
    alt = record.comments if record.comments else filename
    image = IMG(_src=url, _alt=alt)
    output = Storage()
    output.image = image
    output.desc = desc
    return output

# END =========================================================================
