def index_old(self):
        from gviz_data_table import Table
        from rockpack.mainsite.services.user.models import User, UserActivity
        user_count = readonly_session.query(func.count(User.id)).\
            filter(User.refresh_token != '').scalar()
        header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
                  'max active days', 'avg active days', 'stddev active days')
        lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
                                  func.min(UserActivity.date_actioned)).label('lifetime')
        active_days = func.count(func.distinct(func.date(
            UserActivity.date_actioned))).label('active_days')
        activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
            group_by(UserActivity.user)
        ctx = {}
        for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
            data = activity.having(having_expr).from_self(
                func.count('*'),
                func.max(lifetime),
                func.avg(lifetime),
                func.stddev_samp(lifetime),
                func.max(active_days),
                func.avg(active_days),
                func.stddev_samp(active_days)
            ).one()
            table = Table([
                dict(id='metric', type=str),
                dict(id='value', type=float),
                dict(id='%', type=str),
            ])
            pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
            table.extend(zip(*(header, map(float, data), pdata)))
            ctx['ret_%s_data' % key] = table.encode()

        return self.render('admin/retention_stats_old.html', **ctx)

  def _compute_ratios(stats_clause):
    tc_session.expunge_all()
    avg_from_rate = select([alias(
        select([func.avg(RouterStats.circ_from_rate)],
                           stats_clause)
        )]).as_scalar()
    avg_to_rate = select([alias(
        select([func.avg(RouterStats.circ_to_rate)],
                           stats_clause)
        )]).as_scalar()
    avg_bi_rate = select([alias(
        select([func.avg(RouterStats.circ_bi_rate)],
                           stats_clause)
        )]).as_scalar()
    avg_ext = select([alias(
        select([func.avg(RouterStats.avg_first_ext)],
                           stats_clause)
        )]).as_scalar()
    avg_sbw = select([alias(
        select([func.avg(RouterStats.sbw)],
                           stats_clause)
        )]).as_scalar()

    RouterStats.table.update(stats_clause, values=
       {RouterStats.table.c.circ_from_ratio:
         (1-RouterStats.table.c.circ_from_rate)/(1-avg_from_rate),
        RouterStats.table.c.circ_to_ratio:
         (1-RouterStats.table.c.circ_to_rate)/(1-avg_to_rate),
        RouterStats.table.c.circ_bi_ratio:
         (1-RouterStats.table.c.circ_bi_rate)/(1-avg_bi_rate),
        RouterStats.table.c.ext_ratio:
         avg_ext/RouterStats.table.c.avg_first_ext,
        RouterStats.table.c.sbw_ratio:
         RouterStats.table.c.sbw/avg_sbw}).execute()
    tc_session.commit()

  def _compute_ranks():
    tc_session.clear()
    min_r = select([func.min(BwHistory.rank)],
        BwHistory.table.c.router_idhex
            == RouterStats.table.c.router_idhex).as_scalar()
    avg_r = select([func.avg(BwHistory.rank)],
        BwHistory.table.c.router_idhex
            == RouterStats.table.c.router_idhex).as_scalar()
    max_r = select([func.max(BwHistory.rank)],
        BwHistory.table.c.router_idhex
            == RouterStats.table.c.router_idhex).as_scalar()
    avg_bw = select([func.avg(BwHistory.bw)],
        BwHistory.table.c.router_idhex
            == RouterStats.table.c.router_idhex).as_scalar()
    avg_desc_bw = select([func.avg(BwHistory.desc_bw)],
        BwHistory.table.c.router_idhex
            == RouterStats.table.c.router_idhex).as_scalar()

    RouterStats.table.update(values=
       {RouterStats.table.c.min_rank:min_r,
        RouterStats.table.c.avg_rank:avg_r,
        RouterStats.table.c.max_rank:max_r,
        RouterStats.table.c.avg_bw:avg_bw,
        RouterStats.table.c.avg_desc_bw:avg_desc_bw}).execute()

    #min_avg_rank = select([func.min(RouterStats.avg_rank)]).as_scalar()
    max_avg_rank = select([func.max(RouterStats.avg_rank)]).as_scalar()

    RouterStats.table.update(values=
       {RouterStats.table.c.percentile:
            (100.0*RouterStats.table.c.avg_rank)/max_avg_rank}).execute()
    tc_session.commit()

  def superblock_stats(self, sample_id):
    """Calculate superblock level metrics to annotate genes.

    Requires all related blocks to already be properly annotated.

    What's happening is that we are simply taking the average of the
    metrics on the transcript level and applying that as gene metrics.
    This gives a decent, albeit not perfect, represenation of gene
    level metrics.

    .. note::

      Annotation of transcripts needs to be acomplished before
      annotating genes!

    Args:
      sample_id (str): sample Id to match with coverage annotations

    Returns:
      list: list of tuples: ``(<gene_id>, <coverage>, <completeness>)``
    """
    return self.query(
      Block.superblock_id,
      func.avg(BlockData.coverage),
      func.avg(BlockData.completeness)
    ).join(BlockData, Block.id==BlockData.parent_id)\
     .filter(BlockData.sample_id == sample_id)\
     .group_by(Block.superblock_id)

  def write_bws(f, pct_low=0, pct_high=100, order_by=None, recompute=False, stat_clause=None, filter_clause=None, disp_clause=None):
    if not order_by:
      order_by=RouterStats.avg_first_ext

    if recompute:
      RouterStats.compute(pct_low, pct_high, stat_clause, filter_clause)

    pct_clause = and_(RouterStats.percentile >= pct_low, 
                         RouterStats.percentile < pct_high)

    # This is Fail City and sqlalchemy is running for mayor.
    if sqlalchemy.__version__ < "0.5.0":
      sbw = RouterStats.query.filter(pct_clause).filter(stat_clause).avg(RouterStats.sbw)
      filt_sbw = RouterStats.query.filter(pct_clause).filter(stat_clause).avg(RouterStats.filt_sbw)
    else:
      sbw = tc_session.query(func.avg(RouterStats.sbw)).filter(pct_clause).filter(stat_clause).scalar()
      filt_sbw = tc_session.query(func.avg(RouterStats.filt_sbw)).filter(pct_clause).filter(stat_clause).scalar()

    f.write(str(int(time.time()))+"\n")

    def cvt(a,b,c=1):
      if type(a) == float: return int(round(a/c,b))
      elif type(a) == int: return a
      elif type(a) == type(None): return "None"
      else: return type(a)

    for s in RouterStats.query.filter(pct_clause).filter(stat_clause).\
           filter(disp_clause).order_by(order_by).all():
      f.write("node_id=$"+s.router.idhex+" nick="+s.router.nickname)
      f.write(" strm_bw="+str(cvt(s.sbw,0)))
      f.write(" filt_bw="+str(cvt(s.filt_sbw,0)))
      f.write(" desc_bw="+str(int(cvt(s.avg_desc_bw,0))))
      f.write(" ns_bw="+str(int(cvt(s.avg_bw,0)))+"\n")

    f.flush()

def politicoultimarodada(rodada_id=None,candidato_id=None):
    enviar = request.args.get('enviar')
    inicio = request.args.get('inicio')
    fim = request.args.get('fim')
    if enviar is None:
        enviar='True'
    rodada=politicaServices.getRodada(rodada_id)
    if rodada is None:
        return
    rodada_id=rodada.id
    total=0
      
    dominio=""
    #dominio="http://localhost:8084"
    politicos_pontos = politicaServices.topPoliticosRodada(tamanho=5)
    politicos_presenca = politicaServices.topPoliticosPresenca(rodada_id, tamanho=5)
    politicos_votacao = politicaServices.topPoliticosVotacoes(rodada_id, tamanho=5)
    politicos_proposicao = politicaServices.topPoliticosProposicao(rodada_id, tamanho=5)
    politicos_despesa = politicaServices.topPoliticosDespesa(rodada_id, tamanho=5)
    
    media_pontos = Candidatura.query.with_entities(func.avg(Candidatura.pontuacao_ultima).label("media"))
    media_presenca= Pontuacao.query.with_entities(func.avg(Pontuacao.presenca)).filter(Pontuacao.rodada==rodada_id)[0][0]
    media_votacao= Pontuacao.query.with_entities(func.avg(Pontuacao.votacao)).filter(Pontuacao.rodada==rodada_id)[0][0]
    media_proposicao= Pontuacao.query.with_entities(func.avg(Pontuacao.proposicao)).filter(Pontuacao.rodada==rodada_id)[0][0]
    media_despesa= Pontuacao.query.with_entities(func.avg(Pontuacao.despesa)).filter(Pontuacao.rodada==rodada_id)[0][0]
    
    rodada_atual = db.session.merge(session['rodada_atual'])
    titulo="Veja sua pontuacao da rodada"
    log=""
    if candidato_id is not None and candidato_id<>"all":
        candidato = politicaServices.pontuacaoByCandidato(candidato_id, rodada_id)
        nome_politico = Candidatura.query.filter_by(id=candidato_id).first()
        #log = log + enviaPoliticoUltimaRodada(candidato,rodada_id,titulo,rodada,rodada_atual,enviar, nome_politico) #politicos,
        enviaPoliticoUltimaRodada(candidato,rodada_id,titulo,rodada,rodada_atual,enviar, nome_politico, politicos_pontos,
                                  politicos_presenca, politicos_votacao, politicos_proposicao, politicos_despesa,
                                  media_presenca, media_votacao, media_proposicao, media_despesa)
        total = 0
        total = total +1
        log = log + ", " + str(nome_politico.email) #+ str(p[0]) 
        #return render_template("comunicado/politicoultimarodada.html",candidato, dominio=dominio,rodada=rodada,
         #                      rodada_atual=rodada_atual, nome_politico=nome_politico) #,politicos=politicos rodadaPontos=rodadaPontos,
    else:
        candidatos = Candidatura.query.all()
        total=0
        for politico in candidatos:
            if total<inicio or total>fim:
                continue
                
            candidato = politicaServices.pontuacaoByCandidato(candidato=politico.id, rodada_numero=rodada_id)
            nome_politico = Candidatura.query.filter_by(id=politico.id).first()
            total=total+1
            #log = log + enviaPoliticoUltimaRodada(candidato,rodada_id,titulo,rodada,politicos,rodada_atual,enviar)
            try:                
                enviaPoliticoUltimaRodada(candidato,rodada_id,titulo,rodada,rodada_atual,enviar, nome_politico, politicos_pontos,
                                  politicos_presenca, politicos_votacao, politicos_proposicao, politicos_despesa,
                                  media_presenca, media_votacao, media_proposicao, media_despesa)
            except:
                print "Email Error"
            log = log + ", " + str(nome_politico.email)
    return render_template("comunicado/statuscomunicado.html",dominio="",titulo=titulo,total=total,log=log) 

def read_rst(rst_id):
    # 店の詳細情報取得
    session = Session()
    s = (
        session.query(
            Tabelog.Rcd.label("rst_id"),
            Tabelog.RestaurantName,
            Tabelog.TabelogMobileUrl,
            Tabelog.TotalScore,
            Tabelog.Situation,
            Tabelog.DinnerPrice,
            Tabelog.LunchPrice,
            Tabelog.Category,
            Tabelog.Station,
            Tabelog.Address,
            Tabelog.Tel,
            Tabelog.BusinessHours,
            Tabelog.Holiday,
            gf.x(Tabelog.LatLng).label("lat"),
            gf.y(Tabelog.LatLng).label("lng"),
            func.round(func.avg(UserPost.difficulty)).label("difficulty"),
            func.avg(UserPost.difficulty).label("raw_difficulty"),
        )
        .filter(Tabelog.Rcd == rst_id)
        .filter(UserPost.rst_id == Tabelog.Rcd)
        .group_by(UserPost.id)
        .first()
    )
    # gf.wkt(Tabelog.LatLng).label('Point'))\
    #         .filter('Rcd = :rcd').params(rcd=rst_id).first()
    session.commit()
    session.close()
    return s

def index(page = 1):
	form = DataForm()
	user_data = Data.query.filter_by(user_id = g.user.id)
	#ms = user_data.order_by(Data.systolic_pressure.desc()).first()
	four_weeks_ago = datetime.datetime.now() - datetime.timedelta(weeks=4)

	maxs = db.session.query(func.max(Data.systolic_pressure).label('max_systolic')).filter_by(user_id = g.user.id).one()
	max_systolic = maxs.max_systolic
	mins = db.session.query(func.min(Data.systolic_pressure).label('min_systolic')).filter_by(user_id = g.user.id).one()
	min_systolic = mins.min_systolic
	avgs = db.session.query(func.avg(Data.systolic_pressure).label('avg_systolic')).filter_by(user_id = g.user.id).\
			filter(Data.timestamp > four_weeks_ago).one()
	avg_systolic = avgs.avg_systolic

	maxd = db.session.query(func.max(Data.diastolic_pressure).label('max_diastolic')).filter_by(user_id = g.user.id).one()
	max_diastolic = maxd.max_diastolic
	mind = db.session.query(func.min(Data.diastolic_pressure).label('min_diastolic')).filter_by(user_id = g.user.id).one()
	min_diastolic = mind.min_diastolic
	avgd = db.session.query(func.avg(Data.diastolic_pressure).label('avg_diastolic')).filter_by(user_id = g.user.id).\
			filter(Data.timestamp > four_weeks_ago).one()
	avg_diastolic = avgd.avg_diastolic

	maxc = db.session.query(func.max(Data.cardiac_rate).label('max_rate')).filter_by(user_id = g.user.id).one()
	max_rate = maxc.max_rate
	minc = db.session.query(func.min(Data.cardiac_rate).label('min_rate')).filter_by(user_id = g.user.id).one()
	min_rate = minc.min_rate
	avgc = db.session.query(func.avg(Data.cardiac_rate).label('avg_rate')).filter_by(user_id = g.user.id).\
			filter(Data.timestamp > four_weeks_ago).one()
	avg_rate = avgc.avg_rate

	if form.validate_on_submit():
		data = Data(systolic_pressure = form.systolic_pressure.data,
					diastolic_pressure = form.diastolic_pressure.data,
					cardiac_rate = form.cardiac_rate.data,
					timestamp = datetime.datetime.now(),
					body = form.note.data,
					user = g.user)
		db.session.add(data)
		db.session.commit()
		db.session.close()
		flash('Added successfully')
		return redirect(url_for('index'))

	datas = user_data.order_by(Data.timestamp.desc()).paginate(page, DATAS_PER_PAGE, False)

	return render_template('index.html',
		title = 'Home',
		form = form,
		max_systolic = max_systolic,
		min_systolic = min_systolic,
		avg_systolic = avg_systolic,
		max_diastolic = max_diastolic,
		min_diastolic = min_diastolic,
		avg_diastolic = avg_diastolic,
		max_rate = max_rate,
		min_rate = min_rate,
		avg_rate = avg_rate,
		datas = datas)

def home():
	highestRated = session.query(Product.id, Product.name, Product.price, 
		Product.imageName, func.avg(Review.rating).label('averageRating')) \
		.join(Review, Product.id == Review.product_id).group_by(Product.id) \
		.order_by(desc(func.avg(Review.rating))).limit(5).all()
	newlyAdded = session.query(Product).order_by(desc(Product.dateAdded)) \
		.limit(5).all()
	currentUser = getCurrentUser()
	return render_template('home.html', highestRated = highestRated, 
		newlyAdded = newlyAdded)

 def fetch_top_rated_packages():
     package = table('package')
     rating = table('rating')
     sql = select([package.c.id, func.avg(rating.c.rating), func.count(rating.c.rating)],
                  from_obj=[package.join(rating)]). \
         where(package.c.private == 'f'). \
         group_by(package.c.id). \
         order_by(func.avg(rating.c.rating).desc(), func.count(rating.c.rating).desc()). \
         limit(limit)
     res_ids = model.Session.execute(sql).fetchall()
     return [(model.Session.query(model.Package).get(unicode(pkg_id)), avg, num) for pkg_id, avg, num in res_ids]

def get_query(db, model_map):
    Group = model_map['Group']
    User = model_map['User']
    return db.session.query(Group.id.label(u'国家编号'),
                            Group.name.label(u'国家'),
                            func.sum(User.impact).label(u'总影响力'),
                            func.avg(User.impact).label(u'平均影响力'),
                            func.sum(User.intelligience).label(u'总智力'),
                            func.avg(User.intelligience).label(u'平均智力'),
                            func.sum(User.strength).label(u'总武力'),
                            func.avg(User.strength).label(u'平均武力')).group_by(User.group_id).join(User)

 def top_rated_packages(cls, limit=10):
     # NB Not using sqlalchemy as sqla 0.4 doesn't work using both group_by
     # and apply_avg
     package = table('package')
     rating = table('rating')
     sql = select([package.c.id, func.avg(rating.c.rating), func.count(rating.c.rating)], from_obj=[package.join(rating)]).\
           group_by(package.c.id).\
           order_by(func.avg(rating.c.rating).desc(), func.count(rating.c.rating).desc()).\
           limit(limit)
     res_ids = model.Session.execute(sql).fetchall()
     res_pkgs = [(model.Session.query(model.Package).get(unicode(pkg_id)), avg, num) for pkg_id, avg, num in res_ids]
     return res_pkgs

def get_average_rates():
    """Gets average rates for all period."""
    sub = Rate.query\
        .group_by('bank_id')\
        .order_by('update_time desc')\
        .subquery()

    return db.session.query(sub.c.update_time,
                            func.avg(sub.c.usd_buying),
                            func.avg(sub.c.usd_selling),
                            func.avg(sub.c.eur_buying),
                            func.avg(sub.c.eur_selling)).one()

def get_average_rates_from_date(date_from):
    """Gets average rates counted from selected date."""
    sub = Rate.query\
        .group_by('bank_id')\
        .filter(Rate.update_time >= date_from)\
        .order_by('update_time desc')\
        .subquery()

    return db.session.query(sub.c.update_time,
                            func.avg(sub.c.usd_buying),
                            func.avg(sub.c.usd_selling),
                            func.avg(sub.c.eur_buying),
                            func.avg(sub.c.eur_selling)).one()

def sample_queries():
    print('\nWhat is average rating by age group, gender, and occupation of the reviewers?')
    q = session.query(User.age, User.gender, User.occupation, func.avg(Rating.rating)).join(Rating)\
        .group_by(User.age, User.gender, User.occupation)
    print('{:^5s}|{:^9s}|{:^15s}|{:^8s}'.format('Age', 'Gender', 'Occupation', 'Rating'))
    print('-'*37)
    for li in q:
        print('{:^5d} {:^9s} {:^15s} {:^8.3f}'.format(li[0], li[1], li[2], li[3]))
    print('\nWhat is average rating by movie genre?')
    print('{:^15s}|{:^8s}'.format('Genre', 'Rating'))
    print('-'*24)
    for r in session.query(Genre.genre, func.avg(Rating.rating)).join(Rating).group_by(Genre.genre).all():
        print('{:^15s} {:^8.3f}'.format(r[0], r[1]))

def query_average_by_group():
    print "test query_average_by_group"
    avg_by_name = session.query(Person.name, func.avg(Person.score))\
            .group_by(Person.name).all()
    print avg_by_name

    avg_by_name = session.query(Person.name, func.avg(Person.score))\
            .filter_by(is_man=False).group_by(Person.name).all()
    print avg_by_name

    avg_by_name = session.query(Person.name, func.sum(Person.score), func.count(Person.score))\
            .group_by(Person.name).all()
    print avg_by_name

def get_average_rate_for_date_range(date_from, date_to):
    """Gets average rates for date range."""
    sub = Rate.query\
        .group_by('bank_id')\
        .filter(and_(Rate.update_time >= date_from, Rate.update_time <= date_to))\
        .order_by('update_time desc')\
        .subquery()

    return db.session.query(sub.c.update_time,
                            func.avg(sub.c.usd_buying),
                            func.avg(sub.c.usd_selling),
                            func.avg(sub.c.eur_buying),
                            func.avg(sub.c.eur_selling)).one()

    def _calculate_clone_stats(self, sample_id, min_cdr3, max_cdr3,
                               include_outliers, only_full_reads):
        clone_statistics = {}
        for name, stat in _clone_contexts.items():
            clone_statistics[name] = CloneContextStats(seqs=None, **stat)

        # TODO: This should be automatically generated from _dist_fields
        query = self._session.query(
            Sequence.clone_id,
            func.round(func.avg(Sequence.v_match)).label('v_match'),
            func.round(func.avg(Sequence.j_match)).label('j_match'),
            func.round(func.avg(Sequence.j_length)).label('j_length'),
            Sequence.v_gene,
            Sequence.j_gene,
            func.count(Sequence.seq_id).label('copy_number'),
            func.round(
                func.avg(Sequence.v_length + Sequence.num_gaps)
            ).label('v_length'),
            func.round(
                func.avg(100 * Sequence.v_match / Sequence.v_length)
            ).label('v_identity'),
            Sequence.cdr3_num_nts.label('cdr3_length'),
            SelectionPressure.sigma_fwr.label('sp_fwr'),
            SelectionPressure.sigma_cdr.label('sp_cdr'),
        ).join(
            SelectionPressure,
            and_(
                SelectionPressure.clone_id == Sequence.clone_id,
                SelectionPressure.sample_id == Sequence.sample_id
            ),
            isouter=True
        ).filter(
            Sequence.sample_id == sample_id,
            ~Sequence.clone_id.is_(None)
        )

        if only_full_reads:
            query = query.filter(Sequence.partial == 0)
        query = query.group_by(Sequence.clone_id)

        for clone in query:
            clone_info = self._session.query(Clone.cdr3_nt).filter(
                Clone.id == clone.clone_id).first()
            in_frame = len(clone_info.cdr3_nt) % 3 == 0
            stop = '*' in lookups.aas_from_nts(clone_info.cdr3_nt)
            functional = in_frame and not stop
            for name, stat in clone_statistics.items():
                stat.add_if_match(clone, in_frame, stop, functional)

        self._add_stat(clone_statistics, sample_id, include_outliers,
                       only_full_reads)

    def test_apply_avg(self):
        """Query.apply_avg(col)

        avg = session.query(Address).apply_avg(Address.bounces)

        """
        session = create_session()

        avgs = list(session.query(Address).values(func.avg(Address.bounces)))
        avg = avgs[0][0]
        assert avg > 0 and avg < 10

        avg = session.query(func.avg(Address.bounces)).one()[0]
        assert avg > 0 and avg < 10

def album_list():
	ltype, size, offset = map(request.args.get, [ 'type', 'size', 'offset' ])
	try:
		size = int(size) if size else 10
		offset = int(offset) if offset else 0
	except:
		return request.error_formatter(0, 'Invalid parameter format')

	query = session.query(Folder).filter(Folder.tracks.any())
	if ltype == 'random':
		albums = []
		count = query.count()

		if not count:
			return request.formatter({ 'albumList': {} })

		for _ in xrange(size):
			x = random.choice(xrange(count))
			albums.append(query.offset(x).limit(1).one())

		return request.formatter({
			'albumList': {
				'album': [ a.as_subsonic_child(request.user) for a in albums ]
			}
		})
	elif ltype == 'newest':
		query = query.order_by(desc(Folder.created))
	elif ltype == 'highest':
		query = query.join(RatingFolder).group_by(Folder.id).order_by(desc(func.avg(RatingFolder.rating)))
	elif ltype == 'frequent':
		query = query.join(Track, Folder.tracks).group_by(Folder.id).order_by(desc(func.avg(Track.play_count)))
	elif ltype == 'recent':
		query = query.join(Track, Folder.tracks).group_by(Folder.id).order_by(desc(func.max(Track.last_play)))
	elif ltype == 'starred':
		query = query.join(StarredFolder).join(User).filter(User.name == request.username)
	elif ltype == 'alphabeticalByName':
		query = query.order_by(Folder.name)
	elif ltype == 'alphabeticalByArtist':
		# this is a mess because who knows how your file structure is set up
		# with the database changes it's more difficult to get the parent of a dir
		parent = aliased(Folder)
		query = query.join(parent, Folder.parent).order_by(parent.name).order_by(Folder.name)
	else:
		return request.error_formatter(0, 'Unknown search type')

	return request.formatter({
		'albumList': {
			'album': [ f.as_subsonic_child(request.user) for f in query.limit(size).offset(offset) ]
		}
	})