def listener_count(self):
"""Return listener count of radio
"""
from sqlalchemy.sql.functions import sum
from sqlalchemy.sql.expression import func
session = Session.object_session(self)
return session.query(func.ifnull(sum(ProxyConnection.listener), 0)) \
.filter_by(user_id=self.user_id) \
.one()
def get_space_usage(self, user_id):
"""Get space usage of user
"""
from sqlalchemy.sql.expression import func
# 100 MB basic space
basic = 100 * 1024 * 1024
# TODO: sum space of user here
used = self.session \
.query(func.ifnull(func.sum(tables.Audio.size), 0)) \
.filter_by(user_id=user_id) \
.scalar()
return dict(total=basic, used=used)
def get_country_stats(self):
"""Get country list order by online listener count
"""
from sqlalchemy import distinct
from sqlalchemy.sql.expression import func, desc
from sqlalchemy.sql.functions import sum
# table short cut
Site = tables.Site
OnAir = tables.OnAir
ProxyConnection = tables.ProxyConnection
listener_count = func.ifnull(sum(ProxyConnection.listener), 0) \
.label('listener_count')
radio_count = func.ifnull(func.count(distinct(Site.user_id)), 0) \
.label('radio_count')
locations = self.session \
.query(Site.location, listener_count, radio_count) \
.join((OnAir, OnAir.user_id == Site.user_id)) \
.outerjoin((ProxyConnection, ProxyConnection.user_id == Site.user_id)) \
.group_by(Site.location) \
.order_by(desc('listener_count'))
return locations.all()
def get_listener_count(self):
"""Get listener count for all radios
"""
from sqlalchemy.sql.expression import func
from sqlalchemy.sql.functions import sum
OnAir = tables.OnAir
User = tables.User
Conn = tables.ProxyConnection
query = self.session \
.query(User.user_name, func.ifnull(sum(Conn.listener), 0)) \
.join((OnAir, OnAir.user_id == User.user_id)) \
.outerjoin((Conn, Conn.user_id == User.user_id)) \
.group_by(User.user_id)
radios = {}
for name, count in query:
radios[str(name)] = int(count)
return radios
def get_country_list(self, limit=30):
"""Get country list order by online listener count
"""
from sqlalchemy.sql.expression import func, desc
from sqlalchemy.sql.functions import sum
# table short cut
Site = tables.Site
OnAir = tables.OnAir
ProxyConnection = tables.ProxyConnection
listener_count = func.ifnull(sum(ProxyConnection.listener), 0) \
.label('listener_count')
locations = self.session \
.query(Site.location, listener_count) \
.join((OnAir, OnAir.user_id == Site.user_id)) \
.filter((Site.public is True)) \
.outerjoin((ProxyConnection, ProxyConnection.user_id == Site.user_id)) \
.group_by(Site.location) \
.order_by(desc('listener_count')) \
.limit(limit)
return locations.all()
def totalkcaldieta(idmeta, data):
from sqlalchemy import and_
from sqlalchemy.sql.expression import func, cast
try:
sumdieta = Dieta.query.filter(and_(Dieta.idmetaatleta==idmeta,Dieta.data==data)).\
with_entities(cast(func.round(func.ifnull(func.sum(Dieta.totalcalorias),0)), sqlalchemy.Float).label('totalkcal'),
cast(func.round(func.ifnull(func.sum(Dieta.totalcarbo),0)), sqlalchemy.Float).label('totalcarbo'),
cast(func.round(func.ifnull(func.sum(Dieta.totalproteina),0)), sqlalchemy.Float).label('totalproteina'),
cast(func.round(func.ifnull(func.sum(Dieta.totalgordura),0)), sqlalchemy.Float).label('totalgordura'),
cast(func.round(func.ifnull(func.sum(Dieta.totalfibras),0)),sqlalchemy.Float).label('totalfibras'),
cast(func.round(func.ifnull(func.sum(Dieta.totalsodio),0)), sqlalchemy.Float).label('totalsodio')).first()
resultjson = {
'totalkcal': sumdieta[0],
'totalcarbo': sumdieta[1],
'totalproteina': sumdieta[2],
'totalgordura': sumdieta[3],
'totalfibras': sumdieta[4],
'totalsodio': sumdieta[5]
}
return resultjson
except:
return None
def sum_or_0(column):
return func.ifnull(sum(column), 0)
def get_sites(
self,
offset=None,
limit=None,
on_air=True,
keywords=None,
radio_names=None,
public=True,
locations=None,
exclude_locations=None,
tags=None,
ids=None,
order_by=None,
return_count=False,
load_user=True,
load_tags=True,
):
"""Get query of on air sites
if on_air is true, only on air radio sites will be returned, if
it's false, only offline radio sites will be return, otherwise,
all sites will be returned
order_by can be 'listener_count', 'online_time' or None
radio_names is list of user name of radio to filter
public is same as on_air but for filtering public and private sites
location is the location of site to filter
ids is a list of user_id
if return_count is Ture, the result will be count of data only
load_user indicate whether to load user eagerly
load_tags indicate whether to load tags eagerly
"""
from sqlalchemy.sql.expression import func, not_, desc, or_
from sqlalchemy.orm import joinedload
from sqlalchemy.sql.functions import sum
assert order_by in [None, 'listener_count', 'online_time']
# table short cut
User = tables.User
Site = tables.Site
OnAir = tables.OnAir
ProxyConnection = tables.ProxyConnection
Tag = tables.Tag
SiteTag = tables.SiteTag
listener_count = func.ifnull(sum(ProxyConnection.listener), 0) \
.label('listener_count')
sites = self.session \
.query(Site, listener_count) \
.outerjoin((OnAir, OnAir.user_id == Site.user_id)) \
.outerjoin((ProxyConnection, ProxyConnection.user_id == Site.user_id)) \
.group_by(Site.user_id)
# only on air sites
if on_air is True:
sites = sites.filter(OnAir.user_id is not None)
elif on_air is False:
sites = sites.filter(OnAir.user_id is None)
# conditions
if keywords is not None:
ors = []
for keyword in keywords:
ors.append(Site.title.like('%%%s%%' % keyword))
ors.append(Site.brief.like('%%%s%%' % keyword))
ors.append(Site.description.like('%%%s%%' % keyword))
user_ids = self.session.query(User.user_id) \
.filter(User.user_name.in_(keywords)) \
.subquery()
user_ids_by_tag = self.session \
.query(Site.user_id) \
.join((SiteTag,
SiteTag.user_id == Site.user_id)) \
.join((Tag, SiteTag.tag_id == Tag.id)) \
.filter(Tag.name.in_(keywords)) \
.subquery()
ors.append(Site.user_id.in_(user_ids))
ors.append(Site.user_id.in_(user_ids_by_tag))
sites = sites.filter(or_(*ors))
if ids is not None:
sites = sites.filter(Site.user_id.in_(ids))
if radio_names is not None:
user_ids = self.session.query(User.user_id) \
.filter(User.user_name.in_(radio_names)) \
.subquery()
sites = sites.filter(Site.user_id.in_(user_ids))
if public is True:
sites = sites.filter(Site.public is True)
elif public is False:
sites = sites.filter(Site.public is False)
if locations is not None:
locations = map(lambda x: x.upper(), locations)
sites = sites.filter(Site.location.in_(locations))
if exclude_locations is not None:
sites = sites.filter(not_(Site.location.in_(exclude_locations)))
if tags is not None:
ids = self.session \
.query(Site.user_id) \
.join((SiteTag,
SiteTag.user_id == Site.user_id)) \
.join((Tag, SiteTag.tag_id == Tag.id)) \
.filter(Tag.name.in_(tags)) \
.subquery()
sites = sites.filter(Site.user_id.in_(ids))
# all we need is count of data
if return_count:
return sites.count()
# set the order
if order_by is not None:
if order_by == 'listener_count':
sites = sites.order_by(desc('listener_count'))
elif order_by == 'online_time':
sites = sites.order_by(desc(OnAir.online_time))
# offset and limit
if offset is not None:
sites = sites.offset(offset)
if limit is not None:
sites = sites.limit(limit)
sites = sites.all()
# eager load
ids = [site.user_id for site, _ in sites]
query = self.session.query(Site)
if load_user:
query = query.options(joinedload('user'))
if load_tags:
query = query.options(joinedload('tags'))
query = query.filter(Site.user_id.in_(ids))
query.all()
return sites
def rank_query():
username = request.args.get('username')
uuid = request.args.get('uuid')
if uuid:
player = Player.query.options(
joinedload(Player.titles)
).filter_by(uuid=uuid).first()
else:
player = Player.query.options(
joinedload(Player.titles)
).filter(or_(Player.username.ilike('%%%s%%' % username),
Player.nickname.ilike('%%%s%%' % username)))\
.order_by(func.ifnull(Player.nickname, Player.username))\
.limit(1).first()
if not player:
return jsonify({
'err': 1
})
stats = PlayerStats.query.filter_by(server=g.server, player=player).first()
if not stats:
return jsonify({
'err': 1
})
time = h.elapsed_time_string(stats.time_spent)
veteran_statuses = VeteranStatus.query.filter_by(player=player)
for veteran_status in veteran_statuses:
server_id = veteran_status.server_id
rank = veteran_status.rank
server_name = {
1: 'SS I',
2: 'SS II',
4: 'SS III'
}[server_id]
if rank <= 10:
veteran_group = 'Top 10 Veteran'
elif rank <= 40:
veteran_group = 'Top 40 Veteran'
else:
veteran_group = 'Veteran'
title_name = '%s %s' % (server_name, veteran_group)
title = Title.query.filter_by(name=title_name).first()
if title and title not in player.titles:
player.titles.append(title)
player.save(commit=True)
titles = [{'name': x.name, 'broadcast': x.broadcast} for x in player.titles]
retval = {
'err': 0,
'rank': stats.rank,
'time': time,
'minutes': stats.time_spent,
'username': player.username,
'uuid': player.uuid,
'titles': titles
}
return jsonify(retval)