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]
