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)