def query(context='', query=None, offset=0, limit=50, order='creation_ts desc', user_id=None):
where = get_where(context, query, user_id)
what = '''
a.id, a.first_name, a.last_name, a.email, a.website, a.affiliation, a.affiliated, a.department, a.interests, a.degree, a.occupation, a.secret_md5, a.referee_name, a.referee_email, a.referee_affiliation, a.referee_rating, a.creation_ts, a.update_ts, a.status, a.decided_by_user_id, a.calculated_vote, a.nationality, a.country, a.pascal_member, a.travel_support, a.travel_support_budget, a.gender, a.grant_amount,
(select group_concat(u.nickname separator ', ') from votes as v, users as u
where v.user_id = u.id and v.applicant_id = a.id) as voters,
(select count(*) from votes as v, users as u
where v.user_id = u.id and v.applicant_id = a.id) as number_votes,
(select group_concat(distinct u.nickname separator ', ') from comments as c, users as u
where c.user_id = u.id and c.applicant_id = a.id) as commenters,
(select count(*) from comments as c, users as u
where c.user_id = u.id and c.applicant_id = a.id) as number_comments,
(select nickname from users as u
where u.id = a.decided_by_user_id) as decided_by_nickname'''
table = 'applicants as a'
if user_id and context == 'reviewed':
table = 'applicants as a, votes as v, comments as c'
results = db.select(table,
what = what,
where = where,
offset = offset,
limit = limit,
order = order)
count = int(db.select(table, what='count(distinct a.id) as c', where=where)[0].c)
return (results, count)
def query(query=None, offset=8, limit=8, order='id desc', user_id=None):
table = 'users'
results = db.select(table, offset=offset, limit=limit, order=order)
count = int(db.select(table, what='count(distinct id) as c')[0].c)
return (results, count)
def get_stats():
stats = web.storage(total=0,
undecided=0,
admitted=0,
rejected=0,
attendee=0,
scholarships=0,
allocated_amount=0)
results = db.select('applicants',
what='status, count(id) as c',
group='status')
for r in results:
if r.status is None: r.status = 'undecided'
stats[r.status] = r.c
stats.allocated_amount = db.select(
'applicants', what='sum(grant_amount) as amount')[0].amount
stats.total = int(
db.select('applicants', what='count(distinct id) as c')[0].c)
stats.scholarships = int(
db.select('applicants',
what='count(distinct id) as c',
where=get_where('scholarship_recipient'))[0].c)
stats.admitted = int(
db.select('applicants',
what='count(distinct id) as c',
where=get_where('admitted'))[0].c)
return stats
def get_pages(drafts=False):
order = "position ASC"
if drafts:
return [dict(title = x.title, name= x.name) for x in list(db.select('page',
what = "title, name", order=order))]
else:
return [dict(title = x.title, name= x.name) for x in list(db.select('page',
what = "title, name", where="draft = $drafts", vars = dict(drafts = int(drafts)), order=order))]
def get_special_question(ID_special_question):
special_question_row = web.listget(list(db.select('special_questions',dict(ID_special_question=ID_special_question),where='ID_special_question=$ID_special_question')), 0, default=None)
if special_question_row is None:
return None
else:
ID_special_question=special_question_row['ID_special_question']
description_special_question=special_question_row['Description']
question_list = [Question(row['Description'],row['Answer']) for row in list(db.select('questions',dict(ID_special_question=ID_special_question),where='ID_special_question=$ID_special_question'))]
return SpecialQuestion(description_special_question,question_list)
def query(query=None, offset=8, limit=8, order='id desc', user_id=None):
table = 'users'
results = db.select(table,
offset = offset,
limit = limit,
order = order)
count = int(db.select(table, what='count(distinct id) as c')[0].c)
return (results, count)
def query(query=None, offset=10, limit=10, order='id desc'):
table = '_feedback'
results = db.select(table,
offset = offset,
limit = limit,
order = order)
count = int(db.select(table, what='count(distinct id) as c')[0].c)
return (results, count)
def get_issues(project_id, milestone_list=None, category_list=None, status=-1, offset=0, limit=1000):
where = 'project_id=$project_id'
if milestone_list is not None:
where += ' and milestone_id in (%s)' % milestone_list
if category_list is not None:
where += ' and category_id in (%s)' % category_list
if status != -1:
where += ' and status = %d' % int(status)
count = db.select('issue_view', where=where, vars=locals(), what='COUNT(*) AS count')[0].count
issues = db.select('issue_view', where=where, vars=locals(), order='status, priority, milestone_id, id DESC', offset=offset, limit=limit).list()
return issues, count
def query(userID):
results = db.select('image',
order='id DESC',
vars=dict(userID=userID),
where='userID = $userID')
count = int(
db.select('image',
what='count(distinct id) as c',
vars=dict(userID=userID),
where='userID = $userID')[0].c)
return (results, count)
def get_unread_metion_notifition(uid):
getwhere = 'mid = $uid and isread = 0'
notification_mention_results = db.select('_notification_mention',
vars=dict(uid=uid),
where=getwhere,
order='id DESC')
count_mention = int(
db.select('_notification_mention',
what='count(distinct id) as c',
where=getwhere,
vars=dict(uid=uid))[0].c)
return (notification_mention_results, count_mention)
def list_messages(start_index, page_size, order_by):
message_list = db.select('messages',
what='id, title, message, message_group, creator, created, lastupdated, lastupdatedby, is_active',
order=order_by,
limit=page_size,
offset=start_index).list()
# get total users
message_count = db.select('messages', what='count(0) as c')[0].c
# initiate dictionary
records = {}
# initiate list
resposta = []
if len(message_list) > 0:
# set response message to OK
records['Result'] = 'OK'
# set per user informations
for item in range(len(message_list)):
# get 'creator' id from user
u = get_user_by_id(message_list[item].get('creator'))
# convert 'creator' id to 'creator' real name
message_list[item].update(creator=u[0].get('name'))
# get 'lastupdatedby' id from message
u = get_user_by_id(message_list[item].get('lastupdatedby'))
# convert 'lastupdatedby' id to 'lastupdatedby' real name
if u:
message_list[item].update(lastupdatedby=u[0].get('name'))
# convert 'created' and 'lastupdated' from epoch to gmtime
if message_list[item].get('created'):
message_list[item].update(created=time.strftime('%d/%m/%Y %H:%M:%S', time.localtime(message_list[item].get('created'))))
if message_list[item].get('lastupdated'):
message_list[item].update(lastupdated=time.strftime('%d/%m/%Y %H:%M:%S', time.localtime(message_list[item].get('lastupdated'))))
resposta.append(message_list[item])
records['Records'] = resposta
else:
# no items retrieved from table
records['Result'] = 'ERROR'
records['Message'] = 'Nenhuma mensagem encontrada'
# total records from table
records['TotalRecordCount'] = message_count
return json.dumps(records)
def is_unique(field, value, where=None, vars=None):
if not where is None:
where = where + ' AND ' + field + '=$' + field
if vars is None:
vars = {}
vars.update({field: value})
else:
where = field + '=$' + field
vars = {field: value}
try:
db.select(table, what='id', where=where, vars=vars, limit=1)[0]
return False
except IndexError:
return True
def get_stats():
stats = web.storage(undecided=0, admitted=0, rejected=0, allocated_amount=0)
results = db.select('applicants',
what = 'status, count(id) as c',
group = 'status')
for r in results:
if not r.status: r.status = 'undecided'
stats[r.status] = r.c
stats.allocated_amount = db.select('applicants',
what = 'sum(grant_amount) as amount')[0].amount
return stats
def get_users(page=0, what='*', where=None, vars=None, order=None, total=True, limit=None, offset=None):
if page > 0:
limit = limit or 20
offset = (page-1)*limit
if order is None:
order = 'created DESC'
users = db.select('users', what=what, where=where, vars=vars, order=order, limit=limit, offset=offset)
if total is True:
total = db.select('users', what='COUNT(*) AS total', where=where, vars=vars)[0]['total']
else:
total = len(users)
return users, total
def GET(self):
data=web.input()
order=self.setOrder(data)
for cmd,line in data.items():
self.handler(cmd,line)
bugslist=db.select('bugs',where="IsOver <> 1",order="Pid %s"%order)
return render.nolist(bugslist,order)
def get_question(question):
"""Get a specific question from the cache."""
return web.listget(
db.select('wikitrivia_cache',
vars=dict(url=question.wiki_url),
where='wiki_url = $url',
limit=1), 0, False)
def GET(self):
hosts = db.select('HOSTS', what='COUNT(UUID) as COUNT')
count = hosts[0]['COUNT']
form = search_form()
return render.index(count, form)
def save_category_relationships(object_id, category_ids, new=False):
if not isinstance(category_ids, list):
return False
category_ids = web.uniq(category_ids)
if not category_ids:
return False
t = db.transaction()
try:
if new:
add_category_ids = category_ids
else:
old_category_relationships = db.select('object_category_relationships', what='category_id', where='object_id=$object_id', vars={'object_id': object_id})
old_category_ids = [category['category_id'] for category in old_category_relationships]
del_category_ids = set(old_category_ids) - set(category_ids)
add_category_ids = set(category_ids) - set(old_category_ids)
if del_category_ids:
db.delete('object_category_relationships', where='object_id=$object_id AND category_id IN $del_category_ids', vars={'object_id': object_id, 'del_category_ids': list(del_category_ids)})
if add_category_ids:
db.multiple_insert('object_category_relationships', [{'category_id': category_id, 'object_id': object_id} for category_id in add_category_ids])
except:
t.rollback()
return False
else:
t.commit()
return True
def check(username, password):
r = db.select('users',
vars=dict(username=username, password=password),
what='*',
where='username=$username AND password=PASSWORD($password)',
limit=1)
return len(r) == 1
def email_exist_p(email):
email = email.lower()
return db.select(
'users',
vars = dict(email=email),
what = 'count(uid) as c',
where = 'email = $email')[0].c
def get_random():
"""Get random modules."""
return db.select('modules',
what = 'id, title',
where = 'calculated_vote >= -1',
order = 'rand()',
limit = 3)
def IsFavorite(u_id, i_id):
user_id = u_id
img_id = i_id
return db.select('ImageFavorite',
vars=dict(img_id=img_id, user_id=user_id),
what='count(id) as c',
where='img_id = $img_id and user_id = $user_id')[0].c
def get_data_by_object_id(object_id):
'''Returns an IterBetter of all weights for a particular object_id, where each
row is a Storage object.'''
try:
return db.select('data', vars=locals(), where='object_id=$object_id')
except IndexError:
return None
def get_directories(user_id, parent_id=None, limit = 50):
return db.select('directories',
vars=dict(user_id=user_id, parent_id=parent_id),
what='id, user_id, name, parent_id, created_date, updated_date',
where='user_id=$user_id AND parent_id %s $parent_id' % ('=','is')[parent_id is None],
order='name',
limit = limit)
def get_file(file_id):
r = db.select('files',
vars=dict(file_id=file_id),
what='id, user_id, name, body, parent_id, created_date, updated_date',
where='id=$file_id',
limit = 1)
return (None, r[0])[len(r)==1]
def getLikedPostsByUserId(uid, offset, limit):
return db.select('_post_vote_user',
order='creation_ts DESC',
offset=offset,
limit=limit,
vars=dict(uid=uid),
where='uid = $uid')
def getCreatedPostsByUserId(postAuthor, offset, limit):
return db.select('_post',
order='id DESC',
offset=offset,
limit=limit,
vars=dict(postAuthor=postAuthor),
where='postAuthor = $postAuthor')
def getRecentOnePostsInNode(nodeId):
return web.listget(
db.select('_post',
limit=1,
order='id DESC',
vars=dict(nodeId=nodeId),
where='nodeId = $nodeId'), 0, {})
def get_just_added_comment(uid, pid):
return web.listget(
db.select('_post_comments',
vars=dict(pid=pid, uid=uid),
where='pid = $pid and uid = $uid',
order='id DESC',
limit=1), 0, {})
def get_popular():
"""Get the most popular modules."""
return db.select('modules',
what = 'id, title',
where = 'calculated_vote >= -1',
order = 'calculated_vote desc, datetime_created desc',
limit = 10)
def getPostListByNodeIdSortByScore(NodeId, offset, perpage):
return db.select('_post',
order='score DESC, magnitude DESC, id DESC',
offset=offset,
limit=perpage,
vars=dict(nodeId=NodeId),
where='nodeId = $nodeId')
def filter_by_tag(tag):
return db.select('modules as m, tags as t',
vars = dict(tag=tag, _tag=tag+'s'),
what = 'distinct m.id, title, url, description, author, screenshot',
where = 'm.id = module_id and (tag = $tag or tag = $_tag) and calculated_vote >= -1',
order = 'calculated_vote desc, datetime_created desc',
limit = 20)
def GET(self):
date = datetime.datetime.today().strftime(
"%a, %d %b %Y %H:%M:%S +0200")
rec_posts = list(
db.select('_admin_rec_home', where='pid', order="pid_sort"))
postList = []
for i in xrange(len(rec_posts)):
postList += postModel.getPostsByPostId(rec_posts[i].pid)
authors = []
for i in xrange(len(postList)):
authors += users.get_users_by_id(postList[i].postAuthor)
nodes = []
for i in xrange(len(postList)):
nodes += nodeModel.getNodesByNodeId(postList[i].nodeId)
feed_url = "http://www.biubiubiubiu.com/feed"
ishome = True
node = None
web.header('Content-Type', 'application/xml')
return view.feed_index(node, postList, date, authors, nodes, feed_url,
ishome)
def get_roles(user_id):
r = db.select('roles r, users_roles ur',
vars=dict(user_id=user_id),
what='r.id id, r.name name',
where='r.id=ur.role_id AND ur.user_id=$user_id',
limit=100)
return r
def get_board(board_id):
b = db.select('boards',
vars=dict(board_id=board_id),
what='*',
where='id=$board_id',
limit=1)
return (None, b[0])[len(b)==1]
def getCreatedNodesByUserId(node_author, offset, limit):
return db.select('_node',
order='id DESC',
offset=offset,
limit=limit,
vars=dict(node_author=node_author),
where='node_author = $node_author')
def get_board_threads(board_id, limit = 10):
return db.select('threads',
vars=dict(board_id=board_id),
what='id, image, text, title, created_at',
order='updated_at DESC',
where='board_id=$board_id',
limit = limit)
def get_data_by_object_id(object_id):
'''Returns an IterBetter of all weights for a particular object_id, where each
row is a Storage object.'''
try:
return db.select('data', vars=locals(), where='object_id=$object_id')
except IndexError:
return None
def get_latest_for_author(author):
return db.select('modules as m, comments as c',
vars = dict(author=author),
what = 'm.title as module_title, c.author, content, module_id',
where = 'm.id = module_id and m.author = $author and calculated_vote >= -1',
order = 'c.datetime_created desc',
limit = 3)
def save_tag_relationships(object_id, model, tags=None, new=False):
if isinstance(tags, (unicode, str)):
tags = tags.split(',')
if not isinstance(tags, list):
return False
tags = [tag.strip() for tag in tags if tag]
tags = web.uniq(tags)
if not tags:
return False
t = db.transaction()
try:
new_tag_ids = new_tags(tags)
if new:
add_tag_ids = new_tag_ids
else:
old_tag_relationships = db.select('object_tag_relationships', what='tag_id', where='object_id=$object_id AND model=$model', vars={'object_id': object_id, 'model': model})
old_tag_ids = [tag['tag_id'] for tag in old_tag_relationships]
del_tag_ids = set(old_tag_ids) - set(new_tag_ids)
add_tag_ids = set(new_tag_ids) - set(old_tag_ids)
if del_tag_ids:
db.delete('object_tag_relationships', where='object_id=$object_id AND model=$model AND tag_id IN $del_tag_ids', vars={'object_id': object_id, 'model': model, 'del_tag_ids': list(del_tag_ids)})
if add_tag_ids:
db.multiple_insert('object_tag_relationships', [{'tag_id': tag_id, 'object_id': object_id, 'model': model} for tag_id in add_tag_ids])
except:
t.rollback()
return False
else:
t.commit()
return True
def user_email_matches(user_email):
count = db.select('users',
what = 'count(*) as count',
where = 'user_email = $user_email',
vars = {'user_email': user_email}
)[0].count
return count
def GET(self):
data=web.input()
order=self.setOrder(data)
if data.has_key("del"):self.do_del(data["del"])
bugslist=db.select('bugs',where="IsOver = 1",order="YesTime %s"%order)
return render.yeslist(bugslist,order,self.uid)
def GET(self, client_id, invoice_id):
i = get_or_404(
db.select("invoices",
where="id=$invoice_id and client_id=$client_id",
limit=1,
vars=locals()))
return str(i.receipt)
def get_counts():
counts = web.storage(new=0, pending=0, all=0, admitted=0, rejected=0, reviewed=0)
for context in counts.keys():
counts[context] = db.select('applicants',
what = 'count(*) as c',
where = get_where(context))[0].c
return counts
def getPostListByNodeIdSortByID(NodeId, offset, perpage):
return db.select('_post',
order='id DESC',
offset=offset,
limit=perpage,
vars=dict(nodeId=NodeId),
where='nodeId = $nodeId')
def GET(self, nodeId):
date = datetime.datetime.today().strftime(
"%a, %d %b %Y %H:%M:%S +0200")
node = nodeModel.getNodeByNodeId(nodeId)
posts = list(
db.select("_post",
order="id DESC",
limit=10,
vars=dict(nodeId=nodeId),
where='nodeId = $nodeId'))
authors = []
for i in xrange(len(posts)):
authors += users.get_users_by_id(posts[i].postAuthor)
nodes = []
for i in xrange(len(posts)):
nodes += nodeModel.getNodesByNodeId(posts[i].nodeId)
feed_url = "http://www.biubiubiubiu.com/'+ nodeId +'feed"
ishome = False
web.header('Content-Type', 'application/xml')
return view.feed_index(node, posts, date, authors, nodes, feed_url,
ishome)
def find_feed(url, selector):
return get_one(
db.select('feed', {
'url': url,
'selector': '\n'.join(selector)
},
where='url=$url AND selector=$selector'))
def get_board_byshortname(shortname):
b = db.select('boards',
vars=dict(shortname=shortname),
what='*',
where='shortname=$shortname',
limit=1)
return (b[0], None)[len(b)!=1]
def GET(self):
data = web.input()
url = data['url']
ret = {}
if url:
results = db.select('product', what='name,url', where='url=$url', vars={'url':url})
if len(results):
result = results[0]
ret['name'], ret['url'] = result['name'], result['url']
query = "select * from contentanalysis.analyze where text='%s'" % ret['name']
anal = yqlquery(query)
try:
entities = anal['query']['results']['entities']['entity']
for entity in entities:
if entity['types']['type'].get('region'):
ret['region'] = entity['types']['type']['region']
break
except (KeyError, TypeError, ValueError):
ret['region'] = 'unknown'
f = flipkart()
past_prices = fetch_prices(url)
cur_price = f.get_price(url)
ret['price'] = {'past':past_prices, 'present':cur_price}
prices = map(lambda x:x[1],past_prices)
price_trend = order(prices)
variances = monthly_variance(past_prices)
variance_trend = order(variances)
ret['trend'] = {'price':[price_trend,prices],'variance':[variance_trend,variances]}
return serialize(ret)
def get_random():
"""Get random modules."""
return db.select('modules',
what='id, title',
where='calculated_vote >= -1',
order='rand()',
limit=3)
def already_voted(module_id, user_ip):
return web.listget(
db.select('votes',
vars = dict(ip=user_ip, id=module_id),
what = 'count(vote)',
where = 'ip = $ip and module_id = $id',
group = 'module_id having count(module_id) > 0'), 0, False)