Example #1
0
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)
Example #2
0
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
Example #4
0
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))]
Example #5
0
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)
Example #6
0
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)
Example #7
0
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)
Example #8
0
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
Example #9
0
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)
Example #10
0
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)
Example #11
0
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)
Example #12
0
    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
Example #13
0
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
Example #14
0
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
Example #15
0
 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)
Example #16
0
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)
Example #17
0
    def GET(self):
        hosts = db.select('HOSTS', what='COUNT(UUID) as COUNT')
        count = hosts[0]['COUNT']

        form = search_form()

        return render.index(count, form)
Example #18
0
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
Example #19
0
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
Example #20
0
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
Example #21
0
def get_random():
    """Get random modules."""
    return db.select('modules', 
        what  = 'id, title',
        where = 'calculated_vote >= -1',
        order = 'rand()', 
        limit = 3)
Example #22
0
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
Example #23
0
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
Example #24
0
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)
Example #25
0
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]
Example #26
0
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')
Example #27
0
def getCreatedPostsByUserId(postAuthor, offset, limit):
    return db.select('_post',
                     order='id DESC',
                     offset=offset,
                     limit=limit,
                     vars=dict(postAuthor=postAuthor),
                     where='postAuthor = $postAuthor')
Example #28
0
def getRecentOnePostsInNode(nodeId):
    return web.listget(
        db.select('_post',
                  limit=1,
                  order='id DESC',
                  vars=dict(nodeId=nodeId),
                  where='nodeId = $nodeId'), 0, {})
Example #29
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, {})
Example #30
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)
Example #31
0
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')
Example #32
0
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)
Example #33
0
    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)
Example #34
0
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
Example #35
0
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]
Example #36
0
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')
Example #37
0
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)
Example #38
0
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
Example #39
0
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)
Example #40
0
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
Example #41
0
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
Example #42
0
    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)
Example #43
0
 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)
Example #44
0
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
Example #45
0
def getPostListByNodeIdSortByID(NodeId, offset, perpage):
    return db.select('_post',
                     order='id DESC',
                     offset=offset,
                     limit=perpage,
                     vars=dict(nodeId=NodeId),
                     where='nodeId = $nodeId')
Example #46
0
    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)
Example #47
0
def find_feed(url, selector):
    return get_one(
        db.select('feed', {
            'url': url,
            'selector': '\n'.join(selector)
        },
                  where='url=$url AND selector=$selector'))
Example #48
0
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]
Example #49
0
    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)
Example #50
0
def get_random():
    """Get random modules."""
    return db.select('modules',
                     what='id, title',
                     where='calculated_vote >= -1',
                     order='rand()',
                     limit=3)
Example #51
0
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)