'Extract the data for this report'

def extract(self):

    db = current.db
    s3db = current.s3db
    T = current.T
    site_id = self.site_id
    date = self.date
    ctable = s3db.dvr_case
    query = (((((ctable.site_id == site_id) & ((ctable.date == None) | (ctable.date <= date))) & ((ctable.closed_on == None) | (ctable.closed_on >= date))) & (ctable.archived != True)) & (ctable.deleted != True))
    rows = db(query).select(ctable.id, ctable.person_id, ctable.date, ctable.closed_on)
    (old_total, ins, outs) = (0, 0, 0)
    person_ids = set()
    for row in rows:
        person_ids.add(row.person_id)
        if ((not row.date) or (row.date < date)):
            old_total += 1
        else:
            ins += 1
        if (row.closed_on and (row.closed_on == date)):
            outs += 1
    result = {'old_total': old_total, 'new_total': ((old_total - outs) + ins), 'ins': ins, 'outs': outs}
    atypes = {'BAMF': None, 'GU': None, 'Transfer': None, 'X-Ray': None, 'Querverlegung': None}
    COMPLETED = 4
    attable = s3db.dvr_case_appointment_type
    query = attable.name.belongs(atypes.keys())
    rows = db(query).select(attable.id, attable.name)
    add_components = s3db.add_components
    hooks = []
    for row in rows:
        type_id = row.id
        name = row.name
        atypes[name] = alias = ('appointment%s' % type_id)
        hook = {'name': alias, 'joinby': 'person_id', 'filterby': {'type_id': type_id, 'status': COMPLETED}}
        hooks.append(hook)
    s3db.add_components('pr_person', dvr_case_appointment=hooks)
    date_completed = (lambda t: ((T('%(event)s    on') % {'event': T(t)}), ('%s.date' % atypes[t])))
    PAID = 2
    add_components('pr_person', dvr_allowance={'name': 'payment', 'joinby': 'person_id', 'filterby': {'status': PAID}})
    atable = s3db.dvr_allowance
    atable.paid_on.represent = (lambda dt: S3DateTime.date_represent(dt, utc=True))
    s3db.add_components('pr_person', pr_group={'name': 'family', 'link': 'pr_group_membership', 'joinby': 'person_id', 'key': 'group_id', 'filterby': {'group_type': 7}})
    gtable = s3db.pr_group
    mtable = s3db.pr_group_membership
    join = gtable.on((gtable.id == mtable.group_id))
    query = ((mtable.person_id.belongs(person_ids) & (mtable.deleted != True)) & (gtable.group_type == 7))
    rows = db(query).select(mtable.person_id, mtable.group_head, mtable.role_id, join=join)
    represent = mtable.role_id.represent
    rtable = s3db.pr_group_member_role
    if hasattr(represent, 'bulk'):
        query = ((rtable.group_type == 7) & (rtable.deleted != True))
        roles = db(query).select(rtable.id)
        role_ids = [role.id for role in roles]
        represent.bulk(role_ids)
    roles = {}
    HEAD_OF_FAMILY = T('Head    of    Family')
    for row in rows:
        person_id = row.person_id
        role = row.role_id
        if (person_id in roles):
            continue
        if row.group_head:
            roles[person_id] = HEAD_OF_FAMILY
        elif role:
            roles[person_id] = represent(role)
    def family_role(row, roles=roles):
        person_id = row['pr_person.id']
        return roles.get(person_id, '')
    from s3dal import Field
    ptable = s3db.pr_person
    empty = (lambda row: '')
    if (not hasattr(ptable, 'xray_place')):
        ptable.xray_place = Field.Method('xray_place', empty)
    if (not hasattr(ptable, 'family_role')):
        ptable.family_role = Field.Method('family_role', family_role)
    list_fields = ['family.id', (T('ID'), 'pe_label'), (T('Name'), 'last_name'), 'first_name', 'date_of_birth', 'gender', 'person_details.nationality', (T('Family    Role'), 'family_role'), (T('Room    No.'), 'shelter_registration.shelter_unit_id'), 'case_flag_case.flag_id', 'dvr_case.comments', date_completed('GU'), date_completed('X-Ray'), (T('X-Ray    Place'), 'xray_place'), date_completed('BAMF'), (T('B\xc3\x9cMA    valid    until'), 'dvr_case.valid_until'), 'dvr_case.stay_permit_until', (T('Allowance    Payments'), 'payment.paid_on'), (T('Admitted    on'), 'dvr_case.date'), 'dvr_case.origin_site_id', date_completed('Transfer'), date_completed('Querverlegung'), 'dvr_case.status_id', 'dvr_case.destination_site_id']
    query = FS('id').belongs(person_ids)
    resource = s3db.resource('pr_person', filter=query)
    data = resource.select(list_fields, represent=True, raw_data=True, orderby=['pr_family_group.id', 'pr_person.date_of_birth'])
    rfields = data.rfields
    columns = []
    headers = {}
    types = {}
    for rfield in rfields:
        colname = rfield.colname
        if (colname in ('dvr_case_flag_case.flag_id', 'pr_family_group.id')):
            continue
        columns.append(colname)
        headers[colname] = rfield.label
        types[colname] = rfield.ftype
    rows = []
    for row in data.rows:
        flags = 'dvr_case_flag_case.flag_id'
        comments = 'dvr_case.comments'
        raw = row['_row']
        if raw[flags]:
            items = [('%s:    %s' % (T('Advice'), s3_str(row[flags])))]
            if raw[comments]:
                items.insert(0, raw[comments])
            row[comments] = ',    '.join(items)
        rows.append(row)
    report = {'columns': columns, 'headers': headers, 'types': types, 'rows': rows}
    result['report'] = report
    return result
