def get(self, cursor=None, venue_id=None, **kwargs): nameqry = {'select': ('CONCAT(forename, \' \', surname)',), 'table': 'users'} fbidqry = {'select': ('facebook_id',), 'table': 'users'} nameqry['where'] = ('users.id = venue_staff.user_id',) fbidqry['where'] = nameqry['where'] qry = {'select': ('id', 'user_id', 'promo_perm', 'time', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_staff', 'where': 'venue_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (venue_id,)) staff = [util.row_to_dict(cursor, row) for row in cursor] nameqry['where'] = ('users.id = venue_managers.user_id',) fbidqry['where'] = nameqry['where'] qry = {'select': ('id', 'user_id', 'time', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_managers', 'where': 'venue_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (venue_id,)) managers = [util.row_to_dict(cursor, row) for row in cursor] return {'staff': staff, 'managers': managers}
def get(self, cursor=None, **kwargs): qry = {'select': ('id', 'type'), 'table': 'venue_categories', 'where': '', 'order_by': 'type ASC'} cursor.execute(util.query(**qry)) return [util.row_to_dict(cursor, row) for row in cursor]
def get(self, cursor=None, user_id=None, **kwargs): qry = {'select': ('id', 'term', 'time'), 'table': 'user_searches', 'where': 'user_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def get(self, cursor=None, user_id=None, venue_id=None, getall=None, level=None, from_time=None, until_time=None, **kwargs): red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': 'promotion_id = promotions.id'} promo_qry = {'select': ['id', 'title', 'description', 'passcode', 'start', '[end]', 'maximum', 'creator', 'level', '(' + util.query(**red) + ') AS redemptions'], 'table': 'promotions', 'where': ['venue_id = ?', 'hidden != 1'], 'order_by': 'id DESC'} if from_time and until_time: own_red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': ('promotion_id = promotions.id', 'time >= ' + from_time, 'time < ' + until_time, 'user_id = ' + str(user_id))} promo_qry['select'].append('(' + util.query(**own_red) + ') AS own_redemptions') if not util.to_bool(getall): promo_qry['limit'] = 1 promo_qry['where'].append(str(util.now()) + ' >= start') promo_qry['where'].append('([end] = 0 OR [end] > ' + str(util.now()) + ')') promo_qry['where'].append('(maximum = 0 OR (' + util.query(**red) + ') < maximum)') promo_qry['where'].append(level + ' >= level') promo_qry['order_by'] = 'level DESC, id DESC' cursor.execute(util.query(**promo_qry), (venue_id,)) row = cursor.fetchone() if row: return {t[0]: val for t, val in zip(cursor.description, row)} else: return None cursor.execute(util.query(**promo_qry), (venue_id,)) return [util.row_to_dict(cursor, row) for row in cursor.fetchall()]
def get(self, cursor=None, user_id=None, venue_id=None, **kwargs): if (venue_id): subqry = {'select': 'COUNT(id)', 'table': 'post_reports', 'where': ('post_id = posts.id')} qry = {'select': ('posts.id', 'user_id', 'posts.venue_id', 'caption', 'time', 'hidden', 'users.forename', 'users.surname'), 'left_join': 'users', 'on': 'posts.user_id = users.id', 'table': 'posts', 'where': ('posts.venue_id = ?', 'hidden = 0', '(' + util.query(**subqry) + ') < 3' #, #'time > ' + str(util.now() - 691200)), ), 'order_by': 'time DESC', 'limit': 50} cursor.execute(util.query(**qry), (venue_id,)) else: qry = {'select': ('posts.id', 'venues.name', 'posts.time'), 'left_join': 'venues', 'on': 'posts.venue_id = venues.id', 'table': 'posts', 'where': ('posts.user_id = ?'), 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def retrieve(self, cursor=None, user_id=None, term=None): if util.to_bool(term): qry = {'select': ['id', 'facebook_id', 'forename', 'surname' ], 'table': 'users', 'where': ("CONCAT(forename, \' \', surname) LIKE ?",), 'order_by': 'surname ASC, forename ASC'} cursor.execute(util.query(**qry), ("%" + term.replace(' ', "%") + "%",)) return [util.row_to_dict(cursor, row) for row in cursor] else: qry = {'select': ['id', 'facebook', 'twitter', 'forename', 'surname', 'age', 'birth_day', 'birth_month', 'birth_year', 'gender', 'employee', 'joined', 'country', 'language', 'email', 'top5', 'save_locally', 'last_login', 'last_facebook', 'last_twitter' ], 'table': 'users', 'order_by': 'id'} qry['select'].append('twitter_id') qry['select'].append('twitter_token') qry['select'].append('twitter_secret') qry.update({'where': 'id = ?', 'limit': 1}) cursor.execute(util.query(**qry), (user_id,)) res = cursor.fetchone() return util.row_to_dict(cursor, res)
def get(self, cursor=None, user_id=None, **kwargs): qry = {'select': ('venues.name', 'promotion_redemptions.time', 'promotions.passcode', 'promotions.description'), 'left_join': ('promotions', 'venues'), 'on': ('promotion_redemptions.promotion_id = promotions.id', 'venues.id = promotions.venue_id'), 'table': 'promotion_redemptions', 'where': ('promotion_redemptions.user_id = ?'), 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def get(self, cursor=None, venue_id=None, **kwargs): nameqry = {'select': ('CONCAT(forename, \' \', SUBSTRING(surname, 1, 1))',), 'table': 'users', 'where': ('users.id = venue_comments.user_id',)} fbidqry = {'select': ('facebook_id',), 'table': 'users', 'where': ('users.id = venue_comments.user_id',)} qry = {'select': ('id', 'user_id', 'venue_id', 'time', 'comment', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_comments', 'where': ('venue_id = ?',), 'order_by': 'time DESC', 'limit': 10} cursor.execute(util.query(**qry), (venue_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def get(self, cursor=None, user_id=None, term=None, following_only=None, my_lat=None, my_lon=None, distance=None, own=None, quiet=None, trending=None, from_time=None, until_time=None, promotions=None, level=None, around_me=None, **kwargs): subqry = {'select': 'COUNT(id)', 'table': 'venue_followers', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': 'promotion_id = promotions.id'} promoqry = {'select': 'COUNT(id)', 'table': 'promotions', 'where': ('venue_id = venues.id', str(util.now()) + ' >= start', '([end] = 0 OR [end] > ' + str(util.now()) + ')', '(maximum = 0 OR (' + util.query(**red) + ') < maximum)', level + ' >= level', 'hidden != 1')} managerqry = {'select': 'COUNT(id)', 'table': 'venue_managers', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} staffqry = {'select': 'COUNT(id)', 'table': 'venue_staff', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} staffppqry = {'select': 'SUM(promo_perm)', 'table': 'venue_staff', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} fields = ['id', 'name', 'address', 'country', 'phone', 'email', 'email_verified', 'category_id', 'headline', 'tonight', 'website', 'facebook', 'twitter', 'facebook_id', 'twitter_id', 'twitter_token', 'twitter_secret', 'lat', 'lon', 'official', 'verified', 'customer_spend', 'authenticated', 'creator', '(' + util.query(**managerqry) + ') AS manager', '(' + util.query(**staffqry) + ') AS staff', '(' + util.query(**staffppqry) + ') AS promo_perm', "(" + util.query(**subqry) + ") AS following", '(' + util.query(**promoqry) + ') AS promotions'] order_by = ('name ASC',) if term: where = ("name LIKE ?",) elif util.to_bool(following_only): where = ("(" + util.query(**subqry) + ") > 0") elif own: where = ('(' + util.query(**managerqry) + ') = 1 OR (' + util.query(**staffqry) + ') = 1') elif my_lat and my_lon and distance: maybe = {'select': 'COUNT(id)', 'table': 'venue_rsvps', 'where': ('maybe = 1', 'venue_id = venues.id', 'going = 0', 'time >= ?', 'time < ?')} going = {'select': 'COUNT(id)', 'table': 'venue_rsvps', 'where': ('going = 1', 'venue_id = venues.id', 'time >= ?', 'time < ?')} if util.to_bool(quiet): order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 ASC',) elif util.to_bool(trending): order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 DESC',) else: order_by = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) ASC',) where = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) <= ? * ?',) if util.to_bool(promotions): where += ('(' + util.query(**promoqry) + ') > 0',) elif util.to_bool(quiet) or util.to_bool(trending): fields[0] = 'TOP(12) id' elif util.to_bool(around_me): psubqry = {'select': 'COUNT(id)', 'table': 'post_reports', 'where': ('post_id = posts.id')} post_count = {'select': 'CASE WHEN COUNT(id) > 0 THEN 1 ELSE 0 END', 'table': 'posts', 'where': ('posts.venue_id = venues.id', 'hidden = 0', '(' + util.query(**psubqry) + ') < 3', 'time > ' + str(util.now() - 691200))} order_by = ('(' + util.query(**post_count) + ') DESC',) + order_by else: where = '' qry = {'select': fields, 'table': 'venues', 'where': where, 'order_by': order_by} if term: cursor.execute(util.query(**qry), ("%" + term + "%",)) return [util.row_to_dict(cursor, row) for row in cursor] else: values = tuple() if my_lat and my_lon and distance: values += (float(my_lat), float(my_lat), float(my_lon), float(my_lon), float(distance), float(distance)) if util.to_bool(quiet) is None and util.to_bool(trending) is None: values += (float(my_lat), float(my_lat), float(my_lon), float(my_lon)) else: values += (from_time, until_time, from_time, until_time) cursor.execute(util.query(**qry), values) return [util.row_to_dict(cursor, row) for row in cursor]