def party_counts(self):
        # if the bill cosponsorships have party_ids, then use it.
        party_counts = db_session.query(func.count(distinct(Cosponsorship.person_id)))\
                                 .join(Cosponsorship.bill)\
                                 .filter(Bill.id == self.id)\
                                 .outerjoin(Cosponsorship.party)\
                                 .add_columns(Party.name, Party.color)\
                                 .group_by(Party.id)

        # Otherwise, use the most recent party affiliation of candidacy info.
        if any(party is None for _, party, _ in party_counts):
            party_counts = db_session.query(Party.name,
                                            func.count(distinct(Person.id)),
                                            Party.color)\
                                     .join(Candidacy)\
                                     .join(Election)\
                                     .filter(Election.assembly_id == self.assembly_id)\
                                     .join(Person)\
                                     .join(Cosponsorship)\
                                     .join(Bill)\
                                     .filter(Bill.id == self.id)\
                                     .group_by(Party.id)
        else:
            party_counts = ((party, count, color)
                            for count, party, color in party_counts)

        return list(party_counts)

def components_list(db, opsysrelease_ids, associate_ids=None):
    '''
    Returns a list of tuples consisting from component's id
    and component's name
    '''
    sub = db.session.query(distinct(Report.component_id)).subquery()
    components_query = (db.session.query(OpSysComponent.id,
                        OpSysComponent.name)
                    .filter(OpSysComponent.id.in_(sub))
                    .order_by(OpSysComponent.name))

    if opsysrelease_ids:
        fsub = (db.session.query(
                distinct(OpSysReleaseComponent.components_id))
                .filter(OpSysReleaseComponent.opsysreleases_id.in_(
                    opsysrelease_ids)))

        components_query = (components_query
            .filter(OpSysComponent.id.in_(fsub)))

    if associate_ids:
        fsub = (db.session.query(
                distinct(OpSysReleaseComponent.components_id))
                .filter(OpSysReleaseComponent.id.in_(
                    db.session.query(OpSysReleaseComponentAssociate.opsysreleasecompoents_id)
                    .filter(OpSysReleaseComponentAssociate.associatepeople_id.in_(associate_ids)))))

        components_query = (components_query
            .filter(OpSysComponent.id.in_(fsub)))

    return components_query.all()

def get_allowed_hosts(session):
    result = (session
              .query(distinct(AccessWebService.host))
              .filter(and_(AccessWebService.host != None,
                           AccessWebService.disable == 0)).all())
    result = [item[0].encode('utf-8', 'ignore') for item in result]
    return result

    def get_recipient_users(self):
        groups = []

        if self.daily_schedule_subscribers.data:
            log.info("Email recipients includes daily schedule subscribers")
            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.subscribe_daily_schedule == True)
                    .filter(User.confirmed_at != None)
                    .all())

        if self.committee_ids.data:
            log.info("Email recipients includes subscribers for these committees: %s" % self.committee_ids.data)
            user_ids = db.session\
                    .query(distinct(user_committee_alerts.c.user_id))\
                    .filter(user_committee_alerts.c.committee_id.in_(self.committee_ids.data))\
                    .all()
            user_ids = [u[0] for u in user_ids]

            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.id.in_(user_ids))
                    .filter(User.confirmed_at != None)
                    .all())

        return set(u for u in chain(*groups))

def data(request):

    dbsession = DBSession()

    geom = request.params['geom'] if 'geom' in request.params else 'polygon'
    srs = int(request.params['srs']) if 'srs' in request.params else 4326
    root = int(request.params['root']) if 'root' in request.params else None
    depth = int(request.params['depth']) if 'depth' in request.params else 1
    level = int(request.params['level']) if 'level' in request.params else None

    filter = []

    # отбор по иерархии
    p_coalesce = []
    t_child = []
    for l in range(depth):
        t_tab = aliased(Unit)
        p_coalesce.insert(0, t_tab.id)
        t_child.append(t_tab)

    q_child = dbsession.query(distinct(func.coalesce(*p_coalesce)).label('child_id')) \
        .filter(t_child[0].parent_id == root)

    for l in range(depth):
        if l == 0: pass
        else: q_child = q_child.join((t_child[l], t_child[l - 1].child))

    child_id = [r.child_id for r in q_child.all()] 

    # геометрия
    if geom == 'polygon':
        t_geom = UnitPolygon
    elif geom == 'point':
        t_geom = UnitPoint
    
    e_geom = func.st_astext(func.st_transform(func.st_setsrid(t_geom.geom, 4326), srs))

    q_unit = dbsession.query(Unit, e_geom.label('geom')).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i)).filter(Unit.id.in_(child_id)) \
        .outerjoin((t_geom, t_geom.unit_id == Unit.id))

    if level:
        q_unit = q_unit.filter(Unit.level == level)

    features = []

    for record in q_unit.all():
        properties = dict(protocol_o=record.Unit.protocol_o.as_dict() if record.Unit.protocol_o else None,
                          protocol_i=record.Unit.protocol_i.as_dict() if record.Unit.protocol_i else None,
                          unit=record.Unit.as_dict())
        features.append(Feature(id=record.Unit.id, geometry=loads_wkt(record.geom) if record.geom else None, properties=properties))

    fcoll = dumps_geojson(FeatureCollection(features))
    if 'callback' in request.params:
        return Response('%s(%s)' % (request.params['callback'], fcoll), content_type="text/javascript")
    else:
        return Response(fcoll, content_type="application/json")

def get_user_languages(user_id):
    return ([
        p for p, in (
            db.session.query(distinct(Phrase.language))
            .filter(
                Phrase.user_id == user_id,
                Phrase.status == Phrase.Status.visible.value,
            )
        )
    ])

    def getActionsRoot(self, identifier, **options):
        '''
        @see: IUserRbacService.getActions
        '''
        rbacId = self.findRbacId(identifier)
        if rbacId is None: return emptyCollection(**options)

        sql = self.session().query(distinct(RightAction.actionPath))
        sql = sql.filter(RightAction.categoryId.in_(self.sqlRights(rbacId)))  # @UndefinedVariable

        return processCollection(listRootPaths(path for path, in sql.all()), **options)

    def recent_codenames(cls, fullname):
        """Get a list of recent codenames used for 300x100 ads.

        The 300x100 ads get a codename that looks like "fullname_campaign".
        This function gets a list of recent campaigns.
        """
        time_points = get_time_points('day')
        query = (Session.query(distinct(cls.codename).label("codename"))
                        .filter(cls.date.in_(time_points))
                        .filter(cls.codename.startswith(fullname)))
        return [row.codename for row in query]

 def getEntriesFiltered(self, identifier, accessId, **options):
     '''
     @see: IACLPrototype.getEntriesFiltered
     '''
     assert isinstance(accessId, int), 'Invalid access id %s' % accessId
     
     sql = self.session().query(distinct(EntryMapped.Position))
     sql = sql.join(self.EntryFilter).join(self.AclAccess).join(self.Acl)
     sql = sql.filter(self.AclAccess.accessId == accessId).filter(self.AclIdentifier == identifier)
     sql = sql.order_by(EntryMapped.Position)
     return iterateCollection(sql, **options)

    def autocomplete_tags(self, all=False):
        text = request.GET.get('val', None)

        # filter warnings
        import warnings
        warnings.filterwarnings('ignore', module='pylons.decorators')

        if text:
            if all:
                query = meta.Session.query(expression.distinct(expression.func.lower(Tag.title)))
            else:
                query = meta.Session.query(expression.distinct(expression.func.lower(SimpleTag.title)))

            query = query.filter(or_(Tag.title_short.op('ILIKE')('%s%%' % text),
                                     Tag.title.op('ILIKE')('%s%%' % text)))

            results = [title for title in query.all()]
            return dict(values = results)

        return None

def GetSquads(run_id=None):
  conditions = (Team.table.c.present > 0)
  if run_id:
    run = Run.get_by(id=run_id)
    conditions = conditions & (Team.table.c.size==run.size) & (Team.table.c.present.op('&')(1 << (run.day - 1)))
  res = session.execute(select([distinct(Team.table.c.squad)], conditions)).fetchall()
  squads = [item for sublist in res for item in sublist]
  for s in squads[:]:
    if not s:
      squads.remove(s)
  return squads

 def getANDPosts(self, tags, limit=100, extra_items=None):
     q = self.DBsession().query(Post).join(Post.tags)
     if extra_items:
         q = self._dict2ToQuery(q, extra_items)
     if self.board:
         q = q.filter(Post.board == self.board)
     q = q.filter(Tag.name.in_(tags)).group_by(Post.image_id)
     q = q.having(func.count(distinct(Tag.name)) == len(tags))
     q = q.order_by(Post.post_id.desc())
     if limit:
         q = q.limit(limit)
     return q.all()

    def _setup_defaults(self):
        """setting up the defaults
        """
        # fill the asset_types_comboBox with all the asset types from the db
        all_types = map(lambda x: x[0], db.query(distinct(Asset.type)).all())

        if conf.default_asset_type_name not in all_types:
            all_types.append(conf.default_asset_type_name)
        
        logger.debug('all_types: %s' % all_types)
         
        self.asset_types_comboBox.addItems(all_types)

 def party_counts(self):
     party_counts = db_session.query(Party.name,
                                     func.count(distinct(Person.id)))\
                              .join(Candidacy)\
                              .join(Election)\
                              .filter(Election.assembly_id == self.assembly_id)\
                              .join(Person)\
                              .join(cosponsorship)\
                              .join(Bill)\
                              .filter(Bill.id == self.id)\
                              .group_by(Party.id)
     return [(party, int(count)) for party, count in party_counts]

 def org_roles(self, org_id):
     SchemaModel = get_model('rbacscheme')
     #----check if the organization is a global organization
     Org = self.model.get(org_id)
     RoleModel = get_model('role')
     RPRModel = get_model('Role_Perm_Rel')
     if Org.rbacscheme:
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.rbacscheme)
     else:
         #----global organization
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.id == SchemaModel.c.gorg)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.schema_id)
     #----need to filter the rols which belone to this schema
     roleList = do_(query)
     roleDict = {}
     for s in roleList:
         roleDict[s.id] = s.name
     return {'orgid':org_id, 'orgname':OrgObj.name, 'schemaname':OrgObj.schema_name, 'schema':OrgObj.rbacscheme , 'schema_id':OrgObj.schema_id, 'roleDict':roleDict}

def prepare_pre_cache(public_tenant):
    '''
    prepare which state and district are pre-cached

    :param string tenant:  name of the tenant
    :rType: list
    :return:  list of results containing state_code
    '''
    with EdMigratePublicConnection(public_tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
        fact_block_asmt_outcome = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
        query_fao = select([distinct(fact_asmt_outcome_vw.c.state_code).label(Constants.STATE_CODE),
                            fact_asmt_outcome_vw.c.district_id.label(Constants.DISTRICT_ID),
                            fact_asmt_outcome_vw.c.school_id.label(Constants.SCHOOL_ID)], from_obj=[fact_asmt_outcome_vw])
        query_fao = query_fao.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        query_fbao = select([distinct(fact_block_asmt_outcome.c.state_code).label(Constants.STATE_CODE),
                             fact_block_asmt_outcome.c.district_id.label(Constants.DISTRICT_ID),
                             fact_block_asmt_outcome.c.school_id.label(Constants.SCHOOL_ID)], from_obj=[fact_block_asmt_outcome])
        query_fbao = query_fbao.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
        query = query_fao.union(query_fbao).order_by(Constants.STATE_CODE, Constants.DISTRICT_ID, Constants.SCHOOL_ID)
        results = connector.get_result(query)
        return results

 def count_post_viewers(
         self, start_date=None, end_date=None, as_agent=True):
     from .post import Post
     from .action import ViewPost
     from sqlalchemy.sql.expression import distinct
     query = self.db.query(
         func.count(distinct(ViewPost.actor_id))).join(Post).filter(
             Post.discussion_id == self.id)
     if start_date:
         query = query.filter(ViewPost.creation_date >= start_date)
     if end_date:
         query = query.filter(ViewPost.creation_date < end_date)
     return query.first()[0]

def gold_buyers_on(date):
    start_date = datetime.datetime.combine(date, datetime.time.min)
    end_date = datetime.datetime.combine(date, datetime.time.max)

    NON_REVENUE_STATUSES = ("declined", "chargeback", "fudge")
    date_expr = func.timezone(TIMEZONE.zone, gold_table.c.date)
    query = (select([distinct(gold_table.c.account_id)])
                .where(~ gold_table.c.status.in_(NON_REVENUE_STATUSES))
                .where(date_expr >= start_date)
                .where(date_expr <= end_date)
                .where(gold_table.c.pennies > 0)
            )
    rows = ENGINE.execute(query)
    return [int(account_id) for (account_id,) in rows.fetchall() if account_id]

 def getSubActions(self, identifier, parentPath, **options):
     '''
     @see: IUserRbacService.getSubActions
     '''
     assert isinstance(parentPath, str), 'Invalid parent path %s' % parentPath
     
     rbacId = self.findRbacId(identifier)
     if rbacId is None: return emptyCollection(**options)
     
     sql = self.session().query(distinct(RightAction.actionPath))
     sql = sql.filter(RightAction.categoryId.in_(self.sqlRights(rbacId)))  # @UndefinedVariable
     sql = sql.filter(RightAction.actionPath.like('%s.%%' % parentPath))  # @UndefinedVariable
     
     return processCollection(listRootPaths((path for path, in sql.all()), len(parentPath) + 1), **options)