def get_grouped_production(self, criterion, group_type):
        activity = []
        if group_type in ['state', 'county']:
            values = []
            names = []
            group = []

            values.append(Activity.state)
            group.append(Activity.state)
            names.append("state")

            if group_type == 'county':
                values.append(Activity.county)
                group.append(Activity.county)
                names.append("county")

            values.append(sqlfunc.sum(Activity.production))
            names.append("production")
            values.append(sqlfunc.sum(Activity.average_employees))
            names.append("average_employees")
            values.append(sqlfunc.sum(Activity.labor_hours))
            names.append("labor_hours")

            results = DBSession.query(*values).filter(*criterion).group_by(*group).all()
            for row in results:
                d = {}
                for name, value in zip(names, row):
                    d[name] = value
                activity.append(d)
        else:
            raise Exception("Invalid group type: %s" % group_type)

        return activity

def get_top_players_by_time(server_id):
    try:
        leaderboard_lifetime = int(request.registry.settings['xonstat.leaderboard_lifetime'])
    except:
        leaderboard_lifetime = 30

    leaderboard_count = 10
    recent_games_count = 20

    try:
        top_players = DBSession.query(PlayerGameStat.player_id, func.sum(PlayerGameStat.alivetime)).\
                filter(Game.server_id == server_id).\
                filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
                filter(PlayerGameStat.game_id == Game.game_id).\
                filter(PlayerGameStat.player_id > 2).\
                order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
                group_by(PlayerGameStat.player_id).\
                limit(leaderboard_count).all()

        player_ids = []
        player_total = {}
        for (player_id, total) in top_players:
                player_ids.append(player_id)
                player_total[player_id] = total

        top_players = []
        players = DBSession.query(Player.player_id, Player.nick).filter(Player.player_id.in_(player_ids)).all()
        for (player_id, nick) in players:
                top_players.append( (player_id, nick, player_total[player_id]) )
        top_players.sort(key=lambda tup: -tup[2])

    except Exception as e:
        top_players = []
        raise e
    return top_players

    def top_players(self):
        """Top players on this server by total playing time."""
        try:
            top_players_q = DBSession.query(
                fg.row_number().over(
                    order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
                Player.player_id, Player.nick,
                func.sum(PlayerGameStat.alivetime).label("alivetime"))\
                .filter(Player.player_id == PlayerGameStat.player_id)\
                .filter(Game.game_id == PlayerGameStat.game_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Player.player_id > 2)\
                .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
                .group_by(Player.nick)\
                .group_by(Player.player_id)

            if self.last:
                top_players_q = top_players_q.offset(self.last)

            if self.limit:
                top_players_q = top_players_q.limit(self.limit)

            top_players = top_players_q.all()

        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_players

 def execute(self, message, user, params):
     
     alliance = Alliance.load(params.group(1))
     if alliance is None:
         message.reply("No alliance matching '%s' found"%(params.group(1),))
         return
     
     Q = session.query(sum(Planet.value), sum(Planet.score),
                       sum(Planet.size), sum(Planet.xp),
                       count(), Planet.race)
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.group_by(Intel.alliance_id, Planet.race)
     Q = Q.order_by(asc(Planet.race))
     result = Q.all()
     if len(result) < 1:
         message.reply("No planets in intel match alliance %s"%(alliance.name,))
         return
     prev=[]
     for value, score, size, xp, members, race in result:
         reply="%s %s Val(%s)" % (members,race,self.num2short(value/members),)
         reply+=" Score(%s)" % (self.num2short(score/members),)
         reply+=" Size(%s) XP(%s)" % (size/members,self.num2short(xp/members),)
         prev.append(reply)
     reply="Demographics for %s: "%(alliance.name,)+ ' | '.join(prev)
     message.reply(reply)

    def fleet_overview(self):
        if self.scantype not in ("J",):
            return

        from sqlalchemy.sql.functions import min, sum

        f = aliased(FleetScan)
        a = aliased(FleetScan)
        d = aliased(FleetScan)

        Q = session.query(
            f.landing_tick,
            f.landing_tick - min(Scan.tick),
            count(a.id),
            coalesce(sum(a.fleet_size), 0),
            count(d.id),
            coalesce(sum(d.fleet_size), 0),
        )
        Q = Q.join(f.scan)
        Q = Q.filter(f.scan == self)

        Q = Q.outerjoin((a, and_(a.id == f.id, a.mission.ilike("Attack"))))
        Q = Q.outerjoin((d, and_(d.id == f.id, d.mission.ilike("Defend"))))

        Q = Q.group_by(f.landing_tick)
        Q = Q.order_by(asc(f.landing_tick))
        return Q.all()

def _story_build_summary_query():
    # first create a subquery for task statuses
    select_items = []
    select_items.append(Story)
    select_items.append(
        expr.case(
            [(func.sum(Task.status.in_(
                ['todo', 'inprogress', 'review'])) > 0,
              'active'),
             ((func.sum(Task.status == 'merged')) > 0, 'merged')],
            else_='invalid'
        ).label('status')
    )
    for task_status in Task.TASK_STATUSES:
        select_items.append(expr.cast(
            func.sum(Task.status == task_status), Integer
        ).label(task_status))
    select_items.append(expr.null().label('task_statuses'))

    result = select(select_items, None,
                    expr.Join(Story, Task, onclause=Story.id == Task.story_id,
                              isouter=True)) \
        .group_by(Story.id) \
        .alias('story_summary')

    return result

    def get(self, database, query):
        bs = block_size.c.block_size
        stmt = powa_getstatdata_detailed_db()
        stmt = stmt.where(
            (column("datname") == bindparam("database")) &
            (column("queryid") == bindparam("query")))
        stmt = stmt.alias()
        from_clause = outerjoin(powa_statements, stmt,
                           and_(powa_statements.c.queryid == stmt.c.queryid, powa_statements.c.dbid == stmt.c.dbid))
        c = stmt.c
        rblk = mulblock(sum(c.shared_blks_read).label("shared_blks_read"))
        wblk = mulblock(sum(c.shared_blks_hit).label("shared_blks_hit"))
        stmt = (select([
            column("query"),
            sum(c.calls).label("calls"),
            sum(c.runtime).label("runtime"),
            rblk,
            wblk,
            (rblk + wblk).label("total_blks")])
            .select_from(from_clause)
            .where(powa_statements.c.queryid == bindparam("query"))
            .group_by(column("query"), bs))

        value = self.execute(stmt, params={
            "query": query,
            "database": database,
            "from": self.get_argument("from"),
            "to": self.get_argument("to")
        })
        if value.rowcount < 1:
            self.render("xhr.html", content="No data")
            return
        self.render("database/query/detail.html", stats=value.first())

    def get_top_scorers(self):
        """Top players by score. Shared by all renderers."""
        cutoff = self.now - timedelta(days=self.lifetime)
        cutoff = self.now - timedelta(days=120)

        top_scorers_q = DBSession.query(
            fg.row_number().over(order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
            Player.player_id, Player.nick, func.sum(PlayerGameStat.score).label("total_score"))\
            .filter(Player.player_id == PlayerGameStat.player_id)\
            .filter(Game.game_id == PlayerGameStat.game_id)\
            .filter(Game.map_id == self.map_id)\
            .filter(Player.player_id > 2)\
            .filter(PlayerGameStat.create_dt > cutoff)\
            .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
            .group_by(Player.nick)\
            .group_by(Player.player_id)

        if self.last:
            top_scorers_q = top_scorers_q.offset(self.last)

        if self.limit:
            top_scorers_q = top_scorers_q.limit(self.limit)

        top_scorers = top_scorers_q.all()

        return top_scorers

def main_index(request):
    leaderboard_count = 10
    recent_games_count = 32

    # top players by score
    top_players = DBSession.query(Player.player_id, Player.nick, 
            func.sum(PlayerGameStat.score)).\
            filter(Player.player_id == PlayerGameStat.player_id).\
            filter(Player.player_id > 2).\
            order_by(expr.desc(func.sum(PlayerGameStat.score))).\
            group_by(Player.nick).\
            group_by(Player.player_id).all()[0:10]

    top_players = [(player_id, html_colors(nick), score) \
            for (player_id, nick, score) in top_players]

    for i in range(leaderboard_count-len(top_players)):
        top_players.append(('-', '-', '-'))

    # top servers by number of total players played
    top_servers = DBSession.query(Server.server_id, Server.name, 
            func.count()).\
            filter(Game.server_id==Server.server_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Server.server_id).\
            group_by(Server.name).all()[0:10]

    for i in range(leaderboard_count-len(top_servers)):
        top_servers.append(('-', '-', '-'))

    # top maps by total times played
    top_maps = DBSession.query(Map.map_id, Map.name, 
            func.count(Game.game_id)).\
            filter(Map.map_id==Game.game_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Map.map_id).\
            group_by(Map.name).all()[0:10]

    for i in range(leaderboard_count-len(top_maps)):
        top_maps.append(('-', '-', '-'))

    recent_games = DBSession.query(Game, Server, Map).\
            filter(Game.server_id==Server.server_id).\
            filter(Game.map_id==Map.map_id).\
            order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count]

    for i in range(recent_games_count-len(recent_games)):
        recent_games.append(('-', '-', '-'))

    return {'top_players':top_players,
            'top_servers':top_servers,
            'top_maps':top_maps,
            'recent_games':recent_games,
            }

 def query(self):
     bs = block_size.c.block_size
     query = powa_getstatdata_sample("db")
     query = query.alias()
     c = query.c
     return (select([
             extract("epoch", c.ts).label("ts"),
             (sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime"),
             total_read(c),
             total_hit(c)])
         .where(c.calls != None)
         .group_by(c.ts, bs)
         .order_by(c.ts)
         .params(samples=100))

def leaderboard_query(session, start_date, until_date):
    """
    This is, admittedly, a really ugly sql query. Query optimization has not
    been performed, but it shouldn't be anything more complicated than a few
    indices. Good luck.
    """
    #start_date = datetime.strptime(start_date, '%Y-%m-%d')
    #until_date = datetime.strptime(until_date_str, '%Y-%m-%d')
    subq = session\
        .query(
            Instance,
            InstanceType,
            User,
            case([(Instance.end_date != None, Instance.end_date)], else_=now()).label('stop_date'))\
        .join(Instance.user)\
        .join(Instance.type)\
        .subquery()

    uptime_column = case(
        [
            (subq.c.created_date > until_date, 0),
            (subq.c.stop_date < start_date, 0)
        ],
        else_=extract('epoch',
            func.LEAST(subq.c.stop_date, cast(until_date, DateTime)) -
            func.GREATEST(subq.c.created_date, cast(start_date, DateTime))
        )
    )

    print subq.c
    subq2 = session.query(
        subq.c.user_id,
        sum(case([(uptime_column == 0, 0)], else_=1)).label('instance_count'),
        #func.count(subq.c.instance_id).label('instance_count'),
        sum(uptime_column).label('uptime'),
        sum(uptime_column * subq.c.cpu).label('cpu_seconds')
    ).group_by(subq.c.user_id).order_by(desc('cpu_seconds')).subquery()

    q = session.query(
        subq2.c.user_id,
        subq2.c.uptime,
        subq2.c.cpu_seconds,
        subq2.c.instance_count,
        User.username,
        User.is_staff,
        User.name
    ).join(User)

    return q

 def get_ten_biggest_mums(self, rec):
     Q = session.query(User.name, sum(Cookie.howmany).label("gac"))
     Q = Q.join(Cookie.giver)
     Q = Q.filter(Cookie.receiver == rec)
     Q = Q.group_by(User.name)
     Q = Q.order_by(desc("gac"))
     return Q[:10]

def testing_function_2(query_module):
    models_module = sqlalchemy_models
    query = query_module.get_query(sum(models_module.Author.id))

    rows = query.all()
    result = map(extract_row, rows)
    return str(result)

    def get_list(cls, **kw):
        # SELECT client.clientid, job_bytes, max_job FROM client
        # LEFT JOIN (SELECT job.clientid, SUM(job.jobbytes) AS job_bytes FROM job
        # GROUP BY job.clientid) AS vsota ON vsota.clientid = client.clientid
        # LEFT JOIN (SELECT job.clientid, MAX(job.schedtime) AS max_job FROM job
        # GROUP BY job.clientid) AS last_job ON last_job.clientid = client.clientid;
        sum_stmt = Job.query\
            .with_entities(Job.clientid, func.sum(Job.jobbytes).label('job_sumvolbytes'))\
            .group_by(Job.clientid)\
            .subquery('stmt_sub')
        last_stmt = Job.query\
            .with_entities(Job.clientid, func.max(Job.starttime).label('job_maxschedtime')).filter(Job.jobstatus == 'T')\
            .group_by(Job.clientid)\
            .subquery('stmt_max')
        objects = cls.query.with_entities(Client, 'job_sumvolbytes', 'job_maxschedtime', func.count(Job.jobid).label('num_jobs'))\
            .outerjoin(Job, Client.clientid == Job.clientid)\
            .outerjoin(sum_stmt, sum_stmt.c.clientid == Client.clientid)\
            .outerjoin(last_stmt, last_stmt.c.clientid == Client.clientid)\
            .group_by(cls, 'job_sumvolbytes', 'job_maxschedtime')\
            .all()

        # ugly hack since sqlite returns strings for job_maxschedtime
        # TODO: report upstream to sqlalchemy
        if DBSession.bind.dialect.name == 'sqlite':
            def convert_datetime(l):
                if l.job_maxschedtime:
                    l.job_maxschedtime = datetime.datetime.strptime(l.job_maxschedtime, '%Y-%m-%d %H:%M:%S')
                return l
            objects = map(convert_datetime, objects)
        return objects

 def galaxy(self, message, user, params):
     galaxy = Galaxy.load(*params.group(1,3))
     if galaxy is None:
         message.alert("No galaxy with coords %s:%s" % params.group(1,3))
         return
     
     Q = session.query(sum(Planet.value), sum(Planet.score),
                       sum(Planet.size), sum(Planet.xp),
                       count(), Planet.race)
     Q = Q.filter(Planet.galaxy == galaxy)
     Q = Q.filter(Planet.active == True)
     Q = Q.group_by(Planet.race)
     Q = Q.order_by(asc(Planet.race))
     result = Q.all()
     
     self.execute(message, "%s:%s" % (galaxy.x, galaxy.y,), result)

 def query(self):
     # Fetch the base query for sample, and filter them on the database
     bs = block_size.c.block_size
     subquery = powa_getstatdata_sample("db")
     # Put the where clause inside the subquery
     subquery = subquery.where(column("datname") == bindparam("database"))
     query = subquery.alias()
     c = query.c
     return (select([
             to_epoch(c.ts),
             (sum(c.runtime) / greatest(sum(c.calls), 1.)).label("avg_runtime"),
             total_read(c),
             total_hit(c)])
         .where(c.calls != None)
         .group_by(c.ts, bs)
         .order_by(c.ts)
         .params(samples=100))

 def _get_qty(self, itemID, type, date):
     if type == 'received':
         return DBSession.query(sum(ReceiveItem.qty)).filter(and_(
             ReceiveItem.active == 0, ReceiveItem.itemID == itemID,
             ReceiveItem.createTime >= date,
             ReceiveItem.createTime < date + timedelta(days=1))).first()[0] or 0
     elif type == 'shipped':
         return DBSession.query(sum(ShipItem.qty)).filter(and_(
             ShipItem.active == 0, ShipItem.itemID == itemID,
             ShipItem.createTime >= date,
             ShipItem.createTime < date + timedelta(days=1))).first()[0] or 0
     elif type == 'reserved':
         return DBSession.query(sum(ReserveItem.qty)).filter(and_(
             ReserveItem.active == 0, ReserveItem.itemID == itemID,
             ReserveItem.createTime >= date,
             ReserveItem.createTime < date + timedelta(days=1))).first()[0] or 0
     else:
         return 0

def total_by_codename(cls, codenames):
    """Return total lifetime pageviews (or clicks) for given codename(s)"""
    codenames = tup(codenames)
    # uses hour totals to get the most up-to-date count
    q = (Session.query(cls.codename, sum(cls.pageview_count))
                       .filter(cls.interval == "hour")
                       .filter(cls.codename.in_(codenames))
                       .group_by(cls.codename))
    return list(q)

def team_score_list():
    teams = Team.query.filter(Team.role.in_([Team.BLUE, Team.RED]))
    scoring_teams = []
    for team in teams:
        temp = db.session.query(
                functions.sum(CheckResult.success * ServiceCheck.value),
                functions.sum(ServiceCheck.value)) \
            .select_from(
                join(CheckResult,
                     join(ServiceCheck, Service, ServiceCheck.service_id == Service.id),
                     CheckResult.check_id == ServiceCheck.id))

        services = temp.filter(Service.team_id == team.id).first()

        earned = 0
        maximum = 0
        if services[0]:
            earned = services[0]
            maximum = services[1]

        flag_subquery = db.session.\
            query(functions.count(FlagDiscovery.id).label('solve_count'), Flag.value).\
            select_from(join(Flag, FlagDiscovery, Flag.id == FlagDiscovery.flag_id)).\
            filter(Flag.team_id == team.id).\
            group_by(Flag.id).\
            subquery('flag_subquery')
        flags = db.session \
            .query(functions.sum(flag_subquery.c.solve_count * flag_subquery.c.value)).\
            first()

        flags = flags[0] if flags[0] else 0

        injects = score_injects(team)

        team.scores = {
            'services_earned': earned,
            'services_maximum': maximum,
            'injects_earned': injects,
            'flags_lost': flags
        }

        scoring_teams.append(team)

    return render_scoring_page('scoring/index.html', teams=scoring_teams)

    def eta(self):
        store = inspect(self).session
        attr = functions.sum(Video.rendering_eta())

        if self.donator:
            filter = Video.donator & (Video.created_on <= self.created_on)
        else:
            filter = Video.donator | (Video.created_on <= self.created_on)
        query = store.query(attr).filter(Video.rendered_on == None)
        return query.filter(filter).one()[0] or 0