def _column_helper(self, thr, gender_or_migrant, other=None):
        columns = self.table.c
        column = (columns.thr_eligible == 1)
        column &= {
            'rations': columns.num_rations_distributed >= 21,
            'absent': columns.num_rations_distributed.in_((0, None)),
            'partial': columns.num_rations_distributed.between(1, 20)
        }[thr]
        column &= {
            'male': columns.sex == 'M',
            'female': columns.sex == 'F',
            'migrant': columns.resident == 'no',
        }[gender_or_migrant]
        if other is None:
            return func.count(self.table.c.case_id).filter(column).label(
                "thr_rations_{}_{}".format(thr, gender_or_migrant))

        column &= {
            'st': columns.caste == 'st',
            'sc': columns.caste == 'sc',
            'others': columns.caste.notin_(('st', 'sc')),
            'disabled': columns.disabled == '1',
            'minority': columns.minority == '1',
            'male': columns.sex == 'M',
            'female': columns.sex == 'F',
        }[other]
        return func.count(self.table.c.case_id).filter(column).label(
            "thr_rations_{}_{}".format(gender_or_migrant, other))

    def statistics(self):
        c.locations = meta.Session.query(Region, func.count(User.id)).filter(LocationTag.region_id == Region.id).filter(User.location_id == LocationTag.id).group_by(Region).all()

        c.geo_locations = meta.Session.query(User.location_city, func.count(User.id)).group_by(User.location_city).order_by(desc(func.count(User.id))).all()

        # Getting last week date range
        locale = c.locale
        from_time_str = format_date(date.today() - timedelta(7),
                                    format="short",
                                    locale=locale)
        to_time_str = format_date(date.today() + timedelta(1),
                                    format="short",
                                    locale=locale)
        from_time = parse_date(from_time_str, locale=locale)
        to_time = parse_date(to_time_str, locale=locale)

        uploads_stmt = meta.Session.query(
            Event.author_id,
            func.count(Event.created).label('uploads_count'))\
            .filter(Event.event_type == 'file_uploaded')\
            .filter(Event.created < to_time)\
            .filter(Event.created >= from_time)\
            .group_by(Event.author_id).order_by(desc('uploads_count')).limit(10).subquery()
        c.active_users = meta.Session.query(User,
                                            uploads_stmt.c.uploads_count.label('uploads'))\
                                            .join((uploads_stmt, uploads_stmt.c.author_id == User.id)).all()

        return render('/statistics.mako')

def most_interacting_domains_from_3DID(session_3DID):
    """SQLAlchemy query returns an ordered list of all interacting pairs of domains where minimum number of interactions is 100.
    
    * **session_3DID** is SQLAlchemy session that this function should use.
    
    SQL equivalent:

    .. code-block:: sql

        SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
        FROM PDB AS p1, Interacting_PDBs AS i1, PDB AS p2, Interacting_PDBs AS i2
        WHERE p1.id = i1.PDB_first_id
        AND p2.id = i2.PDB_second_id
        AND i1.id = i2.id
        GROUP BY p1.domain_id, p2.domain_id
        HAVING d1 > 100 AND d2 > 100
        ORDER BY d1, d2;
    """
    p1 = aliased(PDB, name='p1')
    p2 = aliased(PDB, name='p2')
    i1 = aliased(Interacting_PDBs, name='i1')
    i2 = aliased(Interacting_PDBs, name='i2')
    d1 = func.count(p1.domain_id).label('d1')
    d2 = func.count(p2.domain_id).label('d2')

    most_interacting = session_3DID.query(p1.domain_id, p2.domain_id, d1, d2).filter(p1.id==i1.PDB_first_id).filter(p2.id== i2.PDB_second_id).filter(i1.id==i2.id).group_by(p1.domain_id, p2.domain_id).having(d1 > 100).having(d2 > 100).order_by(d1, d2).all()
    
    return most_interacting

    def db_calculate_filedistribution(resource_id):
        log.msg("[%s] Calculating file distributions" % resource_id)

        file_distribution = {}

        query = (select([func.count()]).select_from(Files).where(Files.c.resource_id == resource_id))
        total_file_count = yield tx_pool.runQuery(query)
        total_file_count = int(total_file_count[0].count_1)

        for k, v in FileCategories().data.iteritems():
            query = (select([func.count()]).select_from(Files).where(Files.c.file_format == v).where(Files.c.resource_id == resource_id))
            count = yield tx_pool.runQuery(query)

            if count:
                count = int(count[0].count_1)

                pct = 100 * float(count)/float(total_file_count)
                file_distribution[k] = "%.1f" % pct
            else:
                file_distribution[k] = 0

        query = (ResourceMeta.update().where(ResourceMeta.c.id == resource_id).values(file_distribution=json.dumps(file_distribution)))
        yield tx_pool.runOperation(query)

        log.msg("[%s] Calculating file distributions DONE" % resource_id)

    def __query_database(self, search=None, page=0, page_size=0, order_by=None, order_dir=None, host_filter={}):
        host_bundle = Bundle('host', Host.id, Host.name, Host.os, Host.description, Host.owned,\
            Host.default_gateway_ip, Host.default_gateway_mac, EntityMetadata.couchdb_id,\
            EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
            EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
            EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,\
            func.group_concat(distinct(Interface.id)).label('interfaces'),\
            func.count(distinct(Vulnerability.id)).label('vuln_count'),\
            func.count(distinct(Service.id)).label('open_services_count'))

        query = self._session.query(host_bundle)\
                             .outerjoin(EntityMetadata, EntityMetadata.id == Host.entity_metadata_id)\
                             .outerjoin(Interface, Host.id == Interface.host_id)\
                             .outerjoin(Vulnerability, Host.id == Vulnerability.host_id)\
                             .outerjoin(Service, (Host.id == Service.host_id) & (Service.status.in_(('open', 'running', 'opened'))))\
                             .group_by(Host.id)

        # Apply pagination, sorting and filtering options to the query
        query = sort_results(query, self.COLUMNS_MAP, order_by, order_dir, default=Host.id)
        query = apply_search_filter(query, self.COLUMNS_MAP, search, host_filter, self.STRICT_FILTERING)
        count = get_count(query, count_col=Host.id)

        if page_size:
            query = paginate(query, page, page_size)

        results = query.all()

        return results, count

    def totals(self, row):
        """ Counts of articles and sources """
        self.scores_ws.write(row, 0, 'Articles')
        rows = self.filter(
            db.session.query(
                Medium.name,
                func.count(1).label('freq'))
            .join(Document)
            .group_by(Medium.name)
        ).all()
        self.write_simple_score_row('Total articles', rows, row)

        row += 2

        self.scores_ws.write(row, 0, 'Sources')
        rows = self.filter(
            db.session.query(
                Medium.name,
                func.count(1).label('freq'))
            .join(Document)
            .join(DocumentSource)
            .group_by(Medium.name)
        ).all()
        self.write_simple_score_row('Total sources', rows, row)

        return row

    def stats(self):
        query_directors = select(['persons.id', func.count('roles.person_id').label('count')],
                       from_obj=['persons', 'roles'],
                       whereclause="roles.person_id = persons.id AND roles.role_type = 'director'",
                       group_by=['persons.id'], order_by='count desc', limit=10)
        query_actors = select(['persons.id', func.count('roles.person_id').label('count')],
                       from_obj=['persons', 'roles'],
                       whereclause="roles.person_id = persons.id AND roles.role_type = 'cast'",
                       group_by=['persons.id'], order_by='count desc', limit=10)                       
        
        top_directors = DBSession.query(Person, 'count').from_statement(query_directors).all()
        top_actors = DBSession.query(Person, 'count').from_statement(query_actors).all()        
    
        ia = IMDb()

        top250_ids = [x.movieID for x in ia.get_top250_movies()]
        bottom100_ids = [x.movieID for x in ia.get_bottom100_movies()]
        
        top250_count = DBSession.query(Movie).filter(Movie.id.in_(top250_ids)).count()
        bottom100_count = DBSession.query(Movie).filter(Movie.id.in_(bottom100_ids)).count()
        total_count = DBSession.query(Movie).count()
        
        total_runtime = 1
        
        return {'top250_count': top250_count,
                'bottom100_count': bottom100_count,
                'total_count': total_count,
                'total_runtime' : total_runtime,
                'top_directors': top_directors,
                'top_actors': top_actors}

    def get(self):
        args = self.reqparse.parse_args()
        get_current = args.current or 1
        get_limit = args.limit or 10
        get_skip = args.skip or 0
        sort = args.sort or 'name'
        order = args.order or 'asc'
        if order != 'asc':
            order = "-"
        else:
            order = ""
        if get_current:
            get_skip = (get_current * get_limit) - get_limit
        if args.filter:
            total_records = self.dbSession.query(func.count(Genre.id)).\
                filter(Genre.name.like("%" + args.filter + "%")).scalar()
            genres = self.dbSession.query(Genre).filter(Genre.name.like("%" + args.filter + "%")) \
                .order_by(order + sort).slice(get_skip, get_limit)
        else:
            total_records = self.dbSession.query(func.count(Genre.id)).scalar()
            genres = self.dbSession.query(Genre).order_by(order + sort).limit(get_limit)

        rows = []
        if genres:
            for genre in genres:
                rows.append({
                    "genreId": genre.roadieId,
                    "name": genre.name,
                    "createdDate": genre.createdDate.isoformat(),
                    "lastUpdated": "" if not genre.lastUpdated else genre.lastUpdated.isoformat()
                })

        return jsonify(rows=rows, current=args.current or 1, rowCount=len(rows), total=total_records, message="OK")

def get_line_sum():
	line = int(request.form['line'])
	chosen_direction = request.form['direction']
	if chosen_direction in ['Inbound','Outbound']:
		for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction):
			length = result[0]
			zones, fare, cost = initialize_variables(length)
		i = 0
		while (i < length):
			zones[i] =  db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction)[i][0]
			for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction):
				fare[i] = fare_sum[0]
			for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction):
				cost[i] = cost_sum[0]
			i += 1
		return zones, fare, cost
	else:
		for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line):
			length = result[0]
			zones, fare, cost = initialize_variables(length)
		i = 0
		while (i < length):
			zones[i] =  db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line)[i][0]
			for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]):
				fare[i] = fare_sum[0]
			for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]):
				cost[i] = cost_sum[0]
			i += 1
		return zones, fare, cost

def mention_stats():
    check_valid_arguments(request.args, ["to", "from", "count"])
    time_from, time_to = validate_and_set_interval(request.args)

    if 'count' in request.args:
        validate_count(request.args['count'])
        mentions = User\
            .query\
            .join(tweet_mentions_user)\
            .join(TweetWish)\
            .filter(func.unix_timestamp(TweetWish.created_at) < time_to)\
            .filter(func.unix_timestamp(TweetWish.created_at) >= time_from)\
            .add_columns(func.count(User.id))\
            .group_by(User.id)\
            .order_by(desc(func.count(User.id)))\
            .limit(request.args['count'])\
            .all()
    else:
        mentions = User\
            .query\
            .join(tweet_mentions_user)\
            .join(TweetWish)\
            .filter(func.unix_timestamp(TweetWish.created_at) < time_to)\
            .filter(func.unix_timestamp(TweetWish.created_at) >= time_from)\
            .add_columns(func.count(User.id))\
            .group_by(User.id)\
            .order_by(desc(func.count(User.id)))\

    serialized = []
    for result in mentions:
        serialized.append({'user': result[0].json_dump(),
                           'mention_count': result[1]})
    return jsonify(popular_users=serialized)

    def _get_result_counts(self, cutoff):
        build_stats = dict(
            db.session.query(Build.result, func.count())
            .filter(Build.date_created >= cutoff, Build.status == Status.finished, Build.result != Result.unknown)
            .group_by(Build.result)
        )

        job_stats = dict(
            db.session.query(Job.result, func.count())
            .filter(Job.date_created >= cutoff, Job.status == Status.finished, Job.result != Result.unknown)
            .group_by(Job.result)
        )

        jobstep_stats = dict(
            db.session.query(JobStep.result, func.count())
            .filter(JobStep.date_created >= cutoff, JobStep.status == Status.finished, JobStep.result != Result.unknown)
            .group_by(JobStep.result)
        )

        context = []
        for result in Result.__members__.values():
            if result in (Result.unknown, Result.skipped):
                continue

            context.append(
                {
                    "name": unicode(result),
                    "numBuilds": build_stats.get(result, 0),
                    "numJobs": job_stats.get(result, 0),
                    "numJobSteps": jobstep_stats.get(result, 0),
                }
            )

        return context

def hashtag_stats():
    check_valid_arguments(request.args, ["to", "from", "count"])
    time_from, time_to = validate_and_set_interval(request.args)

    if 'count' in request.args:
        validate_count(request.args['count'])
        hashtags = TweetWish\
            .query\
            .filter(func.unix_timestamp(TweetWish.created_at) < time_to)\
            .filter(func.unix_timestamp(TweetWish.created_at) >= time_from)\
            .join(tweet_contains_hashtag)\
            .join(Hashtag)\
            .with_entities(Hashtag.hashtag)\
            .add_columns(func.count(Hashtag.hashtag))\
            .group_by(Hashtag.hashtag)\
            .order_by(desc(func.count(Hashtag.hashtag)))\
            .limit(request.args['count'])\
            .all()
    else:
        hashtags = TweetWish\
            .query\
            .filter(func.unix_timestamp(TweetWish.created_at) < time_to)\
            .filter(func.unix_timestamp(TweetWish.created_at) >= time_from)\
            .join(tweet_contains_hashtag)\
            .join(Hashtag)\
            .with_entities(Hashtag.hashtag)\
            .add_columns(func.count(Hashtag.hashtag))\
            .group_by(Hashtag.hashtag)\
            .order_by(desc(func.count(Hashtag.hashtag)))\
            .all()

    return jsonify(
        popular_hashtags=[{'hashtag': key, 'count': value} for key, value in hashtags])

    def get_top_participants(self, list_name, start, end, limit=None):
        """ Return all the participants between two given dates.

        :param list_name: The name of the mailing list in which this email
            should be searched.
        :param start: A datetime object representing the starting date of
            the interval to query.
        :param end: A datetime object representing the ending date of
            the interval to query.
        :param limit: Limit the number of participants to return. If None or
            not supplied, return them all.
        :returns: The list of thread-starting messages.
        """
        part = self.db.query(Sender.name, Email.sender_email,
                             func.count(Email.sender_email)
                ).join(Email
                ).filter(and_(
                    Email.list_name == list_name,
                    Email.date >= start,
                    Email.date < end,
                )).group_by(Email.sender_email, Sender.name
                ).order_by(desc(func.count(Email.sender_email)))
        if limit is not None:
            part = part.limit(limit)
        return part.all()

    def _columns(self, total_row=False):
        columns = (
            self._column_helper("rations", "male", "st"),
            self._column_helper("rations", "female", "st"),
            self._column_helper("rations", "male", "sc"),
            self._column_helper("rations", "female", "sc"),
            self._column_helper("rations", "male", "others"),
            self._column_helper("rations", "female", "others"),
            self._column_helper("rations", "male", "disabled"),
            self._column_helper("rations", "female", "disabled"),
            self._column_helper("rations", "male", "minority"),
            self._column_helper("rations", "female", "minority"),
            self._column_helper("absent", "male"),
            self._column_helper("absent", "female"),
            self._column_helper("partial", "male"),
            self._column_helper("partial", "female"),
            self._column_helper("rations", "migrant", "male"),
            self._column_helper("rations", "migrant", "female"),
            func.count(self.table.c.case_id).filter(
                self.table.c.sex == 'M').label("child_count_male"),
            func.count(self.table.c.case_id).filter(
                self.table.c.sex == 'F').label("child_count_female"),
            func.sum(self.table.c.num_rations_distributed).filter(
                self.table.c.sex == 'M').label("thr_total_rations_male"),
            func.sum(self.table.c.num_rations_distributed).filter(
                self.table.c.sex == 'F').label("thr_total_rations_female"),
        )

        if not total_row:
            return (self.table.c.awc_id.label("owner_id"),) + columns

        return columns

    def _load_records(session):
        results = []

        qry = session.query(models.ImageFile.filehash,
            func.count('*').label('hash_count'))\
            .group_by(models.ImageFile.filehash).having(func.count('*') > 1)

        for filehash, count in session.query(models.ImageFile.filehash,
                func.count('*').label('hash_count'))\
                .group_by(models.ImageFile.filehash).having(func.count('*')
                    > 1).order_by('hash_count desc'):
            qry = session.query(models.ImageFile.id, models.ImageFile.name,
                models.ImageFile.fullpath,
                func.char_length(models.ImageFile.name).label('namelen'))\
                .filter(models.ImageFile.filehash == filehash)
            assert qry.count() == count

            files = []

            for result in qry:
                files.append(dict(name=result.name, fullpath=result.fullpath,
                    id=result.id))
            results.append(dict(hash=filehash, count=count, files=files))

        return results

    def list(self, service_filter={}):
        service_bundle = Bundle('service',
                Service.id, Service.name, Service.description, Service.protocol,
                Service.status, Service.ports, Service.version, Service.owned,
                Service.interface_id,
                func.count(distinct(Vulnerability.id)).label('vuln_count'), EntityMetadata.couchdb_id,\
                EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
                EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
                EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,
                func.count(distinct(Credential.id)).label("credentials_count"))

        query = self._session.query(service_bundle).\
                group_by(Service.id).\
                outerjoin(EntityMetadata, EntityMetadata.id == Service.entity_metadata_id).\
                outerjoin(Vulnerability, Service.id == Vulnerability.service_id).group_by(Service.id).\
                outerjoin(Interface, Interface.id == Service.interface_id).\
                outerjoin(Credential, (Credential.service_id == Service.id) and (Credential.host_id == None)).\
                outerjoin(Host, Host.id == Interface.host_id)

        query = apply_search_filter(query, self.COLUMNS_MAP, None, service_filter, self.STRICT_FILTERING)

        # 'LIKE' for search services started by hostId.%.%
        if service_filter.get('hostIdCouchdb') is not None:
            query = query.filter(
                EntityMetadata.couchdb_id.like(service_filter.get('hostIdCouchdb') + ".%.%"))

        raw_services = query.all()
        services = [self.__get_service_data(r.service) for r in raw_services]
        result = {'services': services}
        return result

    def index(self):
        c.name = config['app_conf']['site_name']
        c.title = 'Welcome'
        c.messages = []

        c.session = Session()

        file_q = c.session.query(File)

        first_file = file_q.first()

        if first_file is None:
            return render('/derived/show/please_scan.html')

        c.repo_url = first_file.root
        c.total_size = c.session.query(func.sum(File.size)).one()[0]
        c.file_count = file_q.count()
        c.avg_size = c.session.query(func.avg(File.size)).one()[0]

        res = c.session.execute(select([File.ext,
            func.count(File.ext)]).group_by(File.ext).order_by(func.count(File.ext).desc())).fetchone()

        c.ext_string = res[0]
        c.ext_count = res[1]

        c.revision = c.session.query(func.max(Revision.id)).one()[0]

        c.asset_count = c.session.query(Asset).count()
        c.collection_count = c.session.query(Collection).count()

        return render('/derived/show/index.html')

    def _get_tag_suggestions(self, _tag_list):
        # pylint: disable=singleton-comparison
        # Cannot use `is` in SQLAlchemy filters

        include = [
            'exhibitor',
            'delegate',
            'market',
            'activity',
        ]

        results = []
        for path_short in include:
            q = self.orm.query(
                Orgtag.base_short,
                func.count(Org.org_id).label("freq")
            ) \
                .join(org_orgtag, org_orgtag.c.orgtag_id == Orgtag.orgtag_id) \
                .join(Org, Org.org_id == org_orgtag.c.org_id) \
                .filter(
                    Orgtag.public == True,
                    Orgtag.is_virtual == None,
                    Orgtag.path_short == path_short,
                    Org.public == True
                ) \
                .group_by(Orgtag.orgtag_id) \
                .order_by(func.count(Org.org_id).desc()) \
                .limit(10)

            results += list(q.all())

        return self._get_random_suggestions(results, 2)

def organizations_and_counters():
    '''Query organizations with their counters'''
    query = DB.query(Group,
        func.count(distinct(Package.id)).label('nb_datasets'),
        func.count(distinct(Member.id)).label('nb_members')
    )
    query = query.outerjoin(CertifiedPublicService)
    query = query.outerjoin(Package, and_(
        Group.id == Package.owner_org,
        ~Package.private,
        Package.state == 'active',
    ))
    query = query.outerjoin(Member, and_(
        Member.group_id == Group.id,
        Member.state == 'active',
        Member.table_name == 'user'
    ))
    query = query.filter(Group.state == 'active')
    query = query.filter(Group.approval_status == 'approved')
    query = query.filter(Group.is_organization == True)
    query = query.group_by(Group.id, CertifiedPublicService.organization_id)
    query = query.order_by(
        CertifiedPublicService.organization_id == null(),
        desc('nb_datasets'),
        desc('nb_members'),
        Group.title
    )
    query = query.options(orm.joinedload(Group.certified_public_service))
    return query

    def _get_name_suggestion(self, has_name):
        # pylint: disable=singleton-comparison
        # Cannot use `is` in SQLAlchemy filters

        suggestions = []
        if has_name:
            return suggestions

        q = self.orm.query(
            Org.name,
            func.count(Orgtag.orgtag_id).label("freq")
        ) \
            .join(org_orgtag, org_orgtag.c.org_id == Org.org_id) \
            .join(Orgtag, Orgtag.orgtag_id == org_orgtag.c.orgtag_id) \
            .filter(
                Org.public == True,
                Orgtag.public == True,
                Orgtag.is_virtual == None,
            ) \
            .group_by(Org.org_id) \
            .order_by(func.count(Orgtag.orgtag_id).desc()) \
            .limit(30)

        results = q.all()

        return self._get_random_suggestions(results, 2)