def get_columns():
    columns = []
    columns.append(ColumnDT('id',                       mData='id'))
    columns.append(ColumnDT('nop',                      mData='nop'))
    columns.append(ColumnDT('thn_pajak_sppt',           mData='thn_pajak_sppt'))
    columns.append(ColumnDT('pbb_yg_harus_dibayar_sppt',mData='pbb_yg_harus_dibayar_sppt', filter=_DTnumber))
    columns.append(ColumnDT('tgl_cetak_sppt',           mData='tgl_cetak_sppt',            filter=_DTdate))
    columns.append(ColumnDT('status_pembayaran_sppt',   mData='status_pembayaran_sppt'))
    
    query = PosPbbDBSession.query(func.concat(PosSppt.kd_propinsi,
                                              PosSppt.kd_dati2,
                                              PosSppt.kd_kecamatan,
                                              PosSppt.kd_kelurahan,
                                              PosSppt.kd_blok,
                                              PosSppt.no_urut, 
                                              PosSppt.kd_jns_op, 
                                              PosSppt.thn_pajak_sppt).label('id'),
                                  func.concat(PosSppt.kd_propinsi,
                                              PosSppt.kd_dati2,
                                              PosSppt.kd_kecamatan,
                                              PosSppt.kd_kelurahan,
                                              PosSppt.kd_blok,
                                              PosSppt.no_urut, 
                                              PosSppt.kd_jns_op).label('nop'),
                                  PosSppt.thn_pajak_sppt, 
                                  PosSppt.pbb_yg_harus_dibayar_sppt,
                                  PosSppt.tgl_cetak_sppt,
                                  PosSppt.status_pembayaran_sppt)    
    return columns, query

    def _order_by(self, query, joins, sort_joins, sort_field, sort_desc):
        query, joins = super(AffiliationView, self)._order_by(query, joins, sort_joins, sort_field, sort_desc)

        if sort_field.name == 'code':
            # sort by the code field, which has entries like:
            # 1
            # 1.1
            # 1.2
            # 1.10
            # 1.11
            # 10.1
            #
            # these are hard to sort, because they don't sort correctly
            # numerically or lexicographically. Instead, we treat them
            # as parts of dotted-quad IP addresses and use mysql's inet_aton
            # to sort them.

            sort_field = func.inet_aton(
                    func.if_(func.instr(sort_field, '.') > 0,
                        func.concat(sort_field, '.0.0'),     # eg. 10.2
                        func.concat(sort_field, '.0.0.0')))  # eg. 10

            if sort_desc:
                sort_field = desc(sort_field)
            query = query.order_by(None).order_by(sort_field)

        return query, joins

    def __init__(self, search_term):

        search_term = re.sub( and_regex, ' AND ',  search_term )
        search_term = re.sub( or_regex, ' OR ', search_term)

        parser = QueryParser("content", schema=None)
        q = parser.parse(search_term)
        invalid = self.validate_search_term(q)
        if invalid:
            raise ValueError(invalid + search_term)

        myapp.db_connector.connect()
        session = myapp.db_connector.get_session()

        subq = session.query( TourneyList.id.label("tourney_list_id"),
                              TourneyVenue.country.label("country_name"),
                              TourneyVenue.state.label("state_name"),
                              TourneyVenue.city.label("city_name"),
                              TourneyVenue.venue.label("venue_name"),
                              Tourney.tourney_type.label("tourney_type"),
                              func.group_concat( ShipPilot.ship_type.distinct()).label("ship_name" ),
                              func.group_concat( func.concat( Pilot.name, " ", Pilot.canon_name )).label("pilot_name"),
                              func.group_concat( func.concat( Upgrade.name, " ", Upgrade.canon_name ) ).label("upgrade_name") ). \
            join(Tourney).\
            join(TourneyVenue).\
            join(Ship). \
            join(ShipPilot). \
            join(Pilot). \
            outerjoin(ShipUpgrade). \
            outerjoin(Upgrade).\
            group_by( TourneyList.id).subquery()


        fn  = tree_to_expr(q, subq)
        self.query = session.query(subq.c.tourney_list_id).filter( fn )

 def get_info_op(cls, p_kode):
     pkey = FixLength(NOP)
     pkey.set_raw(p_kode)
     query = pbb_DBSession.query(
           func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
                concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
                concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
                concat(cls.kd_jns_op).label('nop'),
           cls.thn_pajak_sppt, cls.nm_wp_sppt.label('nm_wp'),
           func.concat(cls.jln_wp_sppt,', ').concat(cls.blok_kav_no_wp_sppt).label('alamat_wp'),
           func.concat(cls.rt_wp_sppt, ' / ').concat(cls.rw_wp_sppt).label('rt_rw_wp'),
           cls.kelurahan_wp_sppt.label('kelurahan_wp'), cls.kota_wp_sppt.label('kota_wp'), 
           cls.luas_bumi_sppt.label('luas_tanah'), cls.njop_bumi_sppt.label('njop_tanah'),
           cls.luas_bng_sppt.label('luas_bng'),cls.njop_bng_sppt.label('njop_bng'),
           cls.pbb_yg_harus_dibayar_sppt.label('ketetapan'), 
           cls.status_pembayaran_sppt.label('status_bayar'),
           func.concat(DatObjekPajak.jalan_op,', ').concat(DatObjekPajak.blok_kav_no_op).label('alamat_op'),
           func.concat(DatObjekPajak.rt_op,' / ').concat(DatObjekPajak.rw_op).label('rt_rw_op'),).\
           filter(cls.kd_propinsi == DatObjekPajak.kd_propinsi, 
                  cls.kd_dati2 == DatObjekPajak.kd_dati2, 
                  cls.kd_kecamatan == DatObjekPajak.kd_kecamatan, 
                  cls.kd_kelurahan == DatObjekPajak.kd_kelurahan, 
                  cls.kd_blok == DatObjekPajak.kd_blok, 
                  cls.no_urut == DatObjekPajak.no_urut, 
                  cls.kd_jns_op == DatObjekPajak.kd_jns_op)
     return query.filter(cls.kd_propinsi == pkey['kd_propinsi'], 
                         cls.kd_dati2 == pkey['kd_dati2'], 
                         cls.kd_kecamatan == pkey['kd_kecamatan'], 
                         cls.kd_kelurahan == pkey['kd_kelurahan'], 
                         cls.kd_blok == pkey['kd_blok'], 
                         cls.no_urut == pkey['no_urut'], 
                         cls.kd_jns_op == pkey['kd_jns_op'],)

def get_columns():
    columns = []
    columns.append(ColumnDT('id',                 mData='id'))
    columns.append(ColumnDT('nop',                mData='nop'))
    columns.append(ColumnDT('thn_pajak_sppt',     mData='thn_pajak_sppt'))
    columns.append(ColumnDT('pembayaran_sppt_ke', mData='pembayaran_sppt_ke'))
    columns.append(ColumnDT('denda_sppt',         mData='denda_sppt',          filter=_DTnumber))
    columns.append(ColumnDT('jml_sppt_yg_dibayar',mData='jml_sppt_yg_dibayar', filter=_DTnumber))
    columns.append(ColumnDT('tgl_pembayaran_sppt',mData='tgl_pembayaran_sppt', filter=_DTdate))
    
    query = PosPbbDBSession.query(func.concat(PosPembayaranSppt.kd_propinsi,
                                              PosPembayaranSppt.kd_dati2,
                                              PosPembayaranSppt.kd_kecamatan,
                                              PosPembayaranSppt.kd_kelurahan,
                                              PosPembayaranSppt.kd_blok,
                                              PosPembayaranSppt.no_urut, 
                                              PosPembayaranSppt.kd_jns_op, 
                                              PosPembayaranSppt.thn_pajak_sppt, 
                                              PosPembayaranSppt.pembayaran_sppt_ke).label('id'),
                                  func.concat(PosPembayaranSppt.kd_propinsi,
                                              PosPembayaranSppt.kd_dati2,
                                              PosPembayaranSppt.kd_kecamatan,
                                              PosPembayaranSppt.kd_kelurahan,
                                              PosPembayaranSppt.kd_blok,
                                              PosPembayaranSppt.no_urut, 
                                              PosPembayaranSppt.kd_jns_op).label('nop'),
                                  PosPembayaranSppt.thn_pajak_sppt, 
                                  PosPembayaranSppt.pembayaran_sppt_ke, 
                                  PosPembayaranSppt.denda_sppt, 
                                  PosPembayaranSppt.jml_sppt_yg_dibayar,
                                  PosPembayaranSppt.tgl_pembayaran_sppt)    
    return columns, query

 def id_string(cls):
     """ IN SQL:
     SELECT CONCAT(CONCAT(CONCAT(LEFT((SELECT card_types.type_name FROM card_types WHERE card_types.id = cards.card_type_id),1),letter),RIGHT(CONCAT('000000',cards.id),6)),"C") as nid FROM cards;
     """
     return func.concat(func.concat(
         func.concat(func.left(
                 db.select([Card_Type.type_name]).where(Card_Type.id == cls.card_type_id).limit(1).as_scalar(), 1),
             cls.letter),
         func.right(func.concat('000000', cls.id), 6)), "C")

def rename_directory(db, user_id, old_api_path, new_api_path):
    """
    Rename a directory.
    """
    old_db_path = from_api_dirname(old_api_path)
    new_db_path = from_api_dirname(new_api_path)

    if old_db_path == '/':
        raise RenameRoot('Renaming the root directory is not permitted.')

    # Overwriting existing directories is disallowed.
    if _dir_exists(db, user_id, new_db_path):
        raise DirectoryExists(new_api_path)

    # Set this foreign key constraint to deferred so it's not violated
    # when we run the first statement to update the name of the directory.
    db.execute('SET CONSTRAINTS '
               'pgcontents.directories_parent_user_id_fkey DEFERRED')

    # Update name column for the directory that's being renamed
    db.execute(
        directories.update().where(
            and_(
                directories.c.user_id == user_id,
                directories.c.name == old_db_path,
            )
        ).values(
            name=new_db_path,
        )
    )

    # Update the name and parent_name of any descendant directories.  Do
    # this in a single statement so the non-deferrable check constraint
    # is satisfied.
    db.execute(
        directories.update().where(
            and_(
                directories.c.user_id == user_id,
                directories.c.name.startswith(old_db_path),
                directories.c.parent_name.startswith(old_db_path),
            )
        ).values(
            name=func.concat(
                new_db_path,
                func.right(directories.c.name, -func.length(old_db_path))
            ),
            parent_name=func.concat(
                new_db_path,
                func.right(
                    directories.c.parent_name,
                    -func.length(old_db_path)
                )
            ),
        )
    )

def selectTableRowsUsingFormForDataAnalysis(form):
    """
    Builds an sql query from form parameters, executes it, and returns the results as a list.
    Used for Data Analysis Module.
    :param form: Form containing user selected dimensions to aggregate data by.
    :return: List of rows returned by the query. First row is table header.
    """
    selectFields = []

    # Populate selectFields from form
    # Patient and Test Type
    if form.patient.data:
        selectFields.append(models.Record.patient_id.label(form.patient.label.text))
    if form.test_type.data:
        selectFields.append(models.Record.test_type.label(form.test_type.label.text))

    # Test Date Period
    # Hierarchy is Year, Year > Month, Year > Week
    if form.test_date.data != form.NONE_LABEL:
        # just year
        if form.test_date.data == form.YEAR_LABEL:
            selectFields.append(func.year(models.Record.test_date).label(form.YEAR_LABEL))
        # month
        if form.test_date.data == form.MONTH_LABEL:
            selectFields.append(
                func.concat(func.monthname(models.Record.test_date), ', ', func.year(models.Record.test_date)).label(
                    form.MONTH_LABEL))
        # week
        if form.test_date.data == form.WEEK_LABEL:
            selectFields.append(
                func.concat(func.weekofyear(models.Record.test_date), ', ', func.year(models.Record.test_date)).label(
                    form.WEEK_LABEL))

    # construct query
    query = db.session.query(models.Record).join(models.Image).group_by(*["`" + c.name + "`" for c in selectFields])

    # Count results as number of images
    selectFields.append(func.count('*').label("# of Images"))

    # Execute the query with the selected fields
    results = query.values(*selectFields)

    # Reformat as list of rows with a header
    # Construct header
    resultsList = [[c.name for c in selectFields]]

    # Add results from query
    resultsList += list(results)

    return resultsList

    def admin_search_candidates(self):
        params = self.request.params
        status = params.get('status')
        order = params.get('order')
        q = params.get('q')
        tags = split_strip(params.get('tags'))

        basequery = DBSession.query(SearchResultCandidate) \
            .options(joinedload_all('languages.language'), joinedload_all('languages.proficiency'),
                     joinedload_all('skills.skill'), joinedload_all('skills.level'),
                     joinedload('preferred_locations'),
                     joinedload('target_position'))
        if status:
            status = get_by_name_or_raise(CandidateStatus, status)
            basequery = basequery.filter(Candidate.status == status)
        if q:
            q = q.lower()
            basequery = basequery.filter(
                or_(func.lower(Candidate.first_name).startswith(q),
                    func.lower(Candidate.last_name).startswith(q),
                    func.lower(func.concat(Candidate.first_name, " ", Candidate.last_name)).startswith(q),
                    func.lower(Candidate.email).startswith(q)))
        if tags:
            basequery = basequery.outerjoin(CandidateSkill).join(Skill).filter(Skill.name.in_(tags))
        if order:
            basequery = add_sorting(basequery, order, CANDIDATE_SORTABLES)
        return run_paginated_query(self.request, basequery, counter=distinct_counter(SearchResultCandidate.id))

def genotypes_for_records(vcf_id, query):
    """Return all genotypes which would appear on a row in a VCF (determined by
    CHROM/POS/REF/ALT) if just one genotype on that row passes the selections in
    `query'.

    This is used to generate the list of genotypes to be transformed into
    vcf.model._Records and then written to a VCF file.
    """
    query = _annotate_query_with_types(query, spec(vcf_id))
    with tables(db.engine, 'genotypes') as (con, gt):
        keyfunc = func.concat(
            gt.c.contig, ':', cast(gt.c.position, types.Unicode), '::',
            gt.c.reference, '->', gt.c.alternates)
        filtered_gts_q = select([keyfunc]).where(gt.c.vcf_id == vcf_id)
        filtered_gts_q = _add_filters(filtered_gts_q, gt, query.get('filters'))
        filtered_gts_q = _add_range(filtered_gts_q, gt, query.get('range'))
        filtered_gts_q = filtered_gts_q.cte('filtered_gts')

        records_q = select([gt]).where(
            keyfunc.in_(select([filtered_gts_q]))).where(gt.c.vcf_id == vcf_id)
        records_q = records_q.order_by(asc(func.length(gt.c.contig)),
                                       asc(gt.c.contig),
                                       asc(gt.c.position),
                                       asc(gt.c.reference),
                                       asc(gt.c.alternates),
                                       asc(gt.c.sample_name))
        genotypes = [dict(g) for g in con.execute(records_q).fetchall()]
    return genotypes

def get_period_field(period, model):
    """
    Returns the SQL Alchemy field to use base on the type
    aggregate it is (month vs. year)

    :param args: Dictionary
    :return: SQLAlchemy Field object
    """
    fields = []

    fields.append(func.date_part('year', model.date))

    if period == 'month':
        fields.append(
            func.lpad(
                expression.cast(
                    func.date_part('month', model.date), types.String
                ), 3, '-0'
            )
        )
    else:
        fields.append('-01')

    fields.append('-01')

    return func.concat(*fields).label('date')

 def get_dop(cls, p_kode, p_tahun):
     pkey = FixLength(NOP)
     pkey.set_raw(p_kode)
     query = pbb_DBSession.query( func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
                concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
                concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
                concat(cls.kd_jns_op).label('nop'),
           cls.thn_pajak_sppt, cls.luas_bumi_sppt, cls.njop_bumi_sppt, 
           cls.luas_bng_sppt, cls.njop_bng_sppt, cls.nm_wp_sppt,
           cls.pbb_yg_harus_dibayar_sppt, cls.status_pembayaran_sppt,
           DatObjekPajak.jalan_op, DatObjekPajak.blok_kav_no_op, 
           DatObjekPajak.rt_op, DatObjekPajak.rw_op,
           func.coalesce(SpptOpBersama.luas_bumi_beban_sppt,0).label('luas_bumi_beban'), 
           func.coalesce(SpptOpBersama.luas_bng_beban_sppt,0).label('luas_bng_beban'), 
           func.coalesce(SpptOpBersama.njop_bumi_beban_sppt,0).label('njop_bumi_beban'), 
           func.coalesce(SpptOpBersama.njop_bng_beban_sppt,0).label('njop_bng_beban'),
           Kelurahan.nm_kelurahan, Kecamatan.nm_kecamatan, Dati2.nm_dati2,
           func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_bayar'),
           func.sum(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)).label('jml_sppt_yg_dibayar'),
           func.sum(func.coalesce(PembayaranSppt.denda_sppt,0)).label('denda_sppt'),).\
           outerjoin(DatObjekPajak).\
           outerjoin(SpptOpBersama).\
           outerjoin(PembayaranSppt,and_(
               cls.kd_propinsi==PembayaranSppt.kd_propinsi,
               cls.kd_dati2==PembayaranSppt.kd_dati2,
               cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
               cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
               cls.kd_blok==PembayaranSppt.kd_blok,
               cls.no_urut==PembayaranSppt.no_urut,
               cls.kd_jns_op==PembayaranSppt.kd_jns_op,
               cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
               )).\
           filter(cls.kd_propinsi == Kelurahan.kd_propinsi, 
                 cls.kd_dati2 == Kelurahan.kd_dati2, 
                 cls.kd_kecamatan == Kelurahan.kd_kecamatan, 
                 cls.kd_kelurahan == Kelurahan.kd_kelurahan,).\
           filter(cls.kd_propinsi == Kecamatan.kd_propinsi, 
                 cls.kd_dati2 == Kecamatan.kd_dati2, 
                 cls.kd_kecamatan == Kecamatan.kd_kecamatan,).\
           filter(cls.kd_propinsi == Dati2.kd_propinsi, 
                 cls.kd_dati2 == Dati2.kd_dati2,).\
           group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan, cls.kd_blok,
                 cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt, cls.luas_bumi_sppt, cls.njop_bumi_sppt, 
                 cls.luas_bng_sppt, cls.njop_bng_sppt, cls.pbb_yg_harus_dibayar_sppt, 
                 cls.status_pembayaran_sppt, DatObjekPajak.jalan_op, DatObjekPajak.blok_kav_no_op, 
                 DatObjekPajak.rt_op, DatObjekPajak.rw_op,
                 SpptOpBersama.luas_bumi_beban_sppt, 
                 SpptOpBersama.luas_bng_beban_sppt, 
                 SpptOpBersama.njop_bumi_beban_sppt, 
                 SpptOpBersama.njop_bng_beban_sppt,
                 Kelurahan.nm_kelurahan, Kecamatan.nm_kecamatan, Dati2.nm_dati2,)
     return query.filter(
                         cls.kd_propinsi == pkey['kd_propinsi'], 
                         cls.kd_dati2 == pkey['kd_dati2'], 
                         cls.kd_kecamatan == pkey['kd_kecamatan'], 
                         cls.kd_kelurahan == pkey['kd_kelurahan'], 
                         cls.kd_blok == pkey['kd_blok'], 
                         cls.no_urut == pkey['no_urut'], 
                         cls.kd_jns_op == pkey['kd_jns_op'],
                         cls.thn_pajak_sppt==p_tahun)

    def get_hackathon_show_list(self, hackathon_id, show_type=None, limit=10):
        criterion = TeamShow.hackathon_id == hackathon_id
        if show_type:
            criterion = and_(criterion, TeamShow.type == show_type)
        # show_list = TeamShow.query.filter(criterion).order_by(TeamShow.create_time.desc()).limit(limit)

        show_list = (
            self.db.session()
            .query(
                TeamShow.id,
                TeamShow.note,
                TeamShow.team_id,
                TeamShow.hackathon_id,
                Team.name,
                Team.description,
                Team.logo,
                func.group_concat(func.concat(TeamShow.uri, ":::", TeamShow.type)).label("uri"),
            )
            .join(Team, Team.id == TeamShow.team_id)
            .filter(criterion)
            .group_by(TeamShow.team_id)
            .order_by(TeamShow.create_time.desc())
            .all()
        )

        return [s._asdict() for s in show_list]

    def test_return_type_detection(self):

        for fn in [func.coalesce, func.max, func.min, func.sum]:
            for args, type_ in [
                (
                    (datetime.date(2007, 10, 5), datetime.date(2005, 10, 15)),
                    sqltypes.Date,
                ),
                ((3, 5), sqltypes.Integer),
                ((decimal.Decimal(3), decimal.Decimal(5)), sqltypes.Numeric),
                (("foo", "bar"), sqltypes.String),
                (
                    (
                        datetime.datetime(2007, 10, 5, 8, 3, 34),
                        datetime.datetime(2005, 10, 15, 14, 45, 33),
                    ),
                    sqltypes.DateTime,
                ),
            ]:
                assert isinstance(fn(*args).type, type_), "%s / %r != %s" % (
                    fn(),
                    fn(*args).type,
                    type_,
                )

        assert isinstance(func.concat("foo", "bar").type, sqltypes.String)

def tas_gtas_combo(session, period, year):
    """ Creates a combined list of TAS and GTAS data filtered by the given period/year

        Args:
            session: DB session
            period: The period for which to get GTAS data
            year: The year for which to get GTAS data

        Returns:
            A WITH clause to use with other queries
    """
    query = session.query(
            gtas_model.allocation_transfer_agency.label('allocation_transfer_agency'),
            gtas_model.agency_identifier.label('agency_identifier'),
            gtas_model.beginning_period_of_availa.label('beginning_period_of_availa'),
            gtas_model.ending_period_of_availabil.label('ending_period_of_availabil'),
            gtas_model.availability_type_code.label('availability_type_code'),
            gtas_model.main_account_code.label('main_account_code'),
            gtas_model.sub_account_code.label('sub_account_code'),
            gtas_model.amount.label('amount'),
            gtas_model.line.label('line'),
            tas_model.financial_indicator2.label('financial_indicator2'),
            tas_model.fr_entity_type.label('fr_entity_type')).\
        join(tas_model, gtas_model.tas == func.concat(func.coalesce(tas_model.allocation_transfer_agency, '000'),
                                                      func.coalesce(tas_model.agency_identifier, '000'),
                                                      func.coalesce(tas_model.beginning_period_of_availa, '0000'),
                                                      func.coalesce(tas_model.ending_period_of_availabil, '0000'),
                                                      func.coalesce(tas_model.availability_type_code, ' '),
                                                      func.coalesce(tas_model.main_account_code, '0000'),
                                                      func.coalesce(tas_model.sub_account_code, '000'))).\
        filter(gtas_model.period == period).\
        filter(gtas_model.fiscal_year == year)
    return query.cte('tas_gtas')

def find_members(s):

  found = []
  session= Session()

  if s.isdigit():
    # should be phone number
    if len(s) != 10:
      raise ValueError("Phone must be 10 digits")
    else:
      return _find_all_members_in_families(session, session.query(Family).filter(Family.id == int(s)).all())
  elif "@" in s:
    # should be email
    return _find_all_members_in_families(session, session.query(Family).filter(Family.email == s).all())
  else:
    # try first, last names
    from sqlalchemy import func
    return session.query(Member).filter(or_(func.concat(Member.first_name, Member.family_name).like("%%%s%%" % s), \
          func.concat(Member.family_name, Member.first_name).like("%%%s%%" % s))).all() \
        + session.query(Youth).filter(or_(func.concat(Youth.first_name, Youth.family_name).like("%%%s%%" % s), \
          func.concat(Youth.family_name, Youth.first_name).like("%%%s%%" % s))).all()

def update_recipients(connection, commentable_table):
  """Updates recipients field for commentable table."""
  # replace all None data with empty string for recipients field
  connection.execute(commentable_table.update()
                     .where(commentable_table.c.recipients.is_(None))
                     .values(recipients=''))
  # add Line of Defense One Contacts, Vice President to recipients list
  op.execute(commentable_table.update()
             .where(commentable_table.c.recipients != '')
             .values(recipients=func.concat(commentable_table.c.recipients,
                                            ",Line of Defense One Contacts,"
                                            "Vice Presidents")))

    def get_bayar(cls, p_kode):
        pkey = FixLength(NOP)
        pkey.set_raw(p_kode)    
        query = pbb_DBSession.query(
              func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
                   concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
                   concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
                   concat(cls.kd_jns_op).label('nop'), cls.thn_pajak_sppt,
			cls.nm_wp_sppt,	cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
			cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
            cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt, 
            cls.kd_kls_tanah, cls.kd_kls_bng, 
			cls.luas_bumi_sppt, cls.luas_bng_sppt, 
            cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,			
			cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
			cls.status_pembayaran_sppt, 
            cls.tgl_jatuh_tempo_sppt,
			cls.pbb_yg_harus_dibayar_sppt.label('pokok'),
            func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_pembayaran_sppt'),
            func.sum(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)).label('bayar'),
            func.sum(func.coalesce(PembayaranSppt.denda_sppt,0)).label('denda_sppt'),).\
			outerjoin(PembayaranSppt,and_(
                            cls.kd_propinsi==PembayaranSppt.kd_propinsi,
                            cls.kd_dati2==PembayaranSppt.kd_dati2,
                            cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
                            cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
                            cls.kd_blok==PembayaranSppt.kd_blok,
                            cls.no_urut==PembayaranSppt.no_urut,
                            cls.kd_jns_op==PembayaranSppt.kd_jns_op,
                            cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
                            )).\
            group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan, 
                    cls.kd_blok, cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt,
                    cls.nm_wp_sppt,	cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
                    cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
                    cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt, 
                    cls.kd_kls_tanah, cls.kd_kls_bng, 
                    cls.luas_bumi_sppt, cls.luas_bng_sppt, 
                    cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,			
                    cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
                    cls.status_pembayaran_sppt, 
                    cls.tgl_jatuh_tempo_sppt,
                    cls.pbb_yg_harus_dibayar_sppt.label('pokok'),)
            
        return query.filter(cls.kd_propinsi == pkey['kd_propinsi'], 
                            cls.kd_dati2 == pkey['kd_dati2'], 
                            cls.kd_kecamatan == pkey['kd_kecamatan'], 
                            cls.kd_kelurahan == pkey['kd_kelurahan'], 
                            cls.kd_blok == pkey['kd_blok'], 
                            cls.no_urut == pkey['no_urut'], 
                            cls.kd_jns_op == pkey['kd_jns_op'],)

def report_15(dataset_id, page):
    dataset = models.Dataset.query.get_or_404(dataset_id)
    species, habitat = get_report_data(dataset)

    species_data = species.with_entities(
        func.concat(models.DataSpeciesRegion.conclusion_assessment,
                    models.DataSpeciesRegion.conclusion_assessment_trend),
        func.count(models.DataSpeciesRegion.id)
    ).group_by(models.DataSpeciesRegion.conclusion_assessment,
               models.DataSpeciesRegion.conclusion_assessment_trend)

    species_count = species.filter(
        models.DataSpeciesRegion.conclusion_assessment.startswith('U')
    ).count() or 1
    species_data = dict(species_data) or {}
    for key, value in species_data.iteritems():
        species_data[key] = value * 100.0 / species_count

    habitat_data = habitat.with_entities(
        func.concat(models.DataHabitattypeRegion.conclusion_assessment,
                    models.DataHabitattypeRegion.conclusion_assessment_trend),
        func.count(models.DataHabitattypeRegion.id)
    ).group_by(models.DataHabitattypeRegion.conclusion_assessment,
               models.DataHabitattypeRegion.conclusion_assessment_trend)

    habitat_count = habitat.filter(
        models.DataHabitattypeRegion.conclusion_assessment.startswith('U')
    ).count() or 1
    habitat_data = dict(habitat_data) or {}
    for key, value in habitat_data.iteritems():
        habitat_data[key] = value * 100.0 / habitat_count

    return render_template(
        'aggregation/reports/15.html',
        page=page, dataset=dataset, species=species_data,
        habitats=habitat_data,
    )

def postgresql_non_ascii_and_lower(column, as_text=True):
    if hasattr(column, 'property'):
        columns = column.property.columns
        if len(columns) > 1:
            column = func.concat(*columns)
        else:
            column = column.property.columns[0]

    if isinstance(column.type, Enum):
        return column
    elif isinstance(column.type, String):
        return func.lower_and_clear(column)
    elif isinstance(column.type, Numeric):
        return column
    elif as_text:
        return func.text(column)
    else:
        return column