def last_activity(app_id):
sql = text('''SELECT finish_time FROM task_run WHERE app_id=:app_id
ORDER BY finish_time DESC LIMIT 1''')
results = db.engine.execute(sql, app_id=app_id)
for row in results:
if row is not None:
print pretty_date(row[0])
return pretty_date(row[0])
else:
return None
def last_activity(app_id):
sql = text('''SELECT finish_time FROM task_run WHERE app_id=:app_id
ORDER BY finish_time DESC LIMIT 1''')
results = db.engine.execute(sql, app_id=app_id)
for row in results:
if row is not None:
print pretty_date(row[0])
return pretty_date(row[0])
else:
return None
def get_all(category):
"""Return a list of published projects for a given category.
"""
sql = text('''SELECT project.id, project.name, project.short_name,
project.description, project.info, project.created, project.updated,
project.category_id, project.featured, "user".fullname AS owner
FROM "user", project
LEFT OUTER JOIN category ON project.category_id=category.id
WHERE
category.short_name=:category
AND "user".id=project.owner_id
AND project.published=true
AND (project.info->>'passwd_hash') IS NULL
GROUP BY project.id, "user".id ORDER BY project.name;''')
results = session.execute(sql, dict(category=category))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(project)
return projects
def manage_users(page):
"""
Admin page for all PyBossa registered users.
Returns a Jinja2 rendered template with the users.
Note ::
This would be an admin only page, hence, rendering cached data
not necessary. Instead the admin would rather want the most updated data
"""
per_page = 24
pagination = User.query.paginate(page, per_page, False)
accounts = pagination.items
count = pagination.total
"""
Normalize accounts for it to be rendered by the global helper functions we use in the theme
"""
for k in accounts:
k.n_task_runs = len(k.task_runs)
k.registered_ago = pretty_date(k.created)
if not accounts and page !=1 and not current_user.admin:
abort(404)
if current_user.is_authenticated():
user_id = current_user.id
else:
user_id = 'anonymous'
return render_template('geotagx/users/index.html', accounts = accounts,
total = count, pagination_page = str(page),
title = "Community", pagination = pagination)
def get_draft(category=None, page=1, per_page=5):
"""Return list of draft projects"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_draft(category, page=1, per_page=5):
"""Return list of draft applications"""
count = n_draft()
sql = text('''SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
results = db.engine.execute(sql, limit=per_page, offset=offset)
apps = []
for row in results:
app = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
def get_user_summary(name):
"""Return user summary."""
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info,
"user".email_addr, COUNT(task_run.user_id) AS n_answers,
"user".valid_email, "user".confirmation_email_sent
FROM "user"
LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
results = session.execute(sql, dict(name=name))
user = dict()
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
created=row.created, api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=dict(json.loads(row.info)),
email_addr=row.email_addr, n_answers=row.n_answers,
valid_email=row.valid_email,
confirmation_email_sent=row.confirmation_email_sent,
registered_ago=pretty_date(row.created))
if user:
rank_score = rank_and_score(user['id'])
user['rank'] = rank_score['rank']
user['score'] = rank_score['score']
user['total'] = get_total_users()
return user
else: # pragma: no cover
return None
def get_all(category):
"""Return a list of published projects for a given category.
"""
sql = text(
'''SELECT project.id, project.name, project.short_name,
project.description, project.info, project.created, project.updated,
project.category_id, project.featured, "user".fullname AS owner
FROM "user", project
LEFT OUTER JOIN category ON project.category_id=category.id
WHERE
category.short_name=:category
AND "user".id=project.owner_id
AND project.published=true
AND (project.info->>'passwd_hash') IS NULL
GROUP BY project.id, "user".id ORDER BY project.name;''')
results = session.execute(sql, dict(category=category))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(Project().to_public_json(project))
return projects
def get_all_draft(category=None):
"""Return list of all draft projects."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.created,
project.description, project.info, project.updated, "user".fullname as owner
FROM "user", project LEFT JOIN task ON project.id=task.project_id
WHERE task.project_id IS NULL
AND (project.info->>'task_presenter') IS NULL
AND project.hidden=0
AND project.owner_id="user".id;''')
results = session.execute(sql)
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(project)
return projects
def get_users_page(page, per_page=24):
"""Return users with a paginator."""
offset = (page - 1) * per_page
sql = text(
"""SELECT "user".id, "user".name,
"user".fullname, "user".email_addr,
"user".created, "user".info, COUNT(task_run.id) AS task_runs
FROM task_run, "user"
WHERE "user".id=task_run.user_id GROUP BY "user".id
ORDER BY "user".created DESC LIMIT :limit OFFSET :offset"""
)
results = session.execute(sql, dict(limit=per_page, offset=offset))
accounts = []
for row in results:
user = dict(
id=row.id,
name=row.name,
fullname=row.fullname,
email_addr=row.email_addr,
created=row.created,
task_runs=row.task_runs,
info=row.info,
registered_ago=pretty_date(row.created),
)
accounts.append(user)
return accounts
def get_draft(category, page=1, per_page=5):
"""Return list of draft projects"""
try:
count = n_draft()
sql = text('''SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_featured(category=None, page=1, per_page=5):
"""Return a list of featured projects with a pagination."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.info, project.created,
project.description,
"user".fullname AS owner FROM project, "user"
WHERE project.featured=true AND project.hidden=0
AND "user".id=project.owner_id GROUP BY project.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
results = session.execute(sql, dict(limit=per_page, offset=offset))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
projects.append(project)
return projects
def get_all_draft(category=None):
"""Return list of all draft projects."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.created,
project.description, project.info, project.updated,
"user".fullname AS owner
FROM "user", project
WHERE project.owner_id="user".id
AND project.published=false;''')
results = session.execute(sql)
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(project)
return projects
def get_all_featured(category=None):
"""Return a list of featured projects with a pagination."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.info,
project.created, project.updated, project.description,
"user".fullname AS owner
FROM project, "user"
WHERE project.featured=true
AND "user".id=project.owner_id
GROUP BY project.id, "user".id;''')
results = session.execute(sql)
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
updated=row.updated,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(project)
return projects
def get_all_draft(category=None):
"""Return list of all draft projects."""
sql = text(
"""SELECT project.id, project.name, project.short_name, project.created,
project.description, project.info, project.updated, "user".fullname as owner
FROM "user", project LEFT JOIN task ON project.id=task.project_id
WHERE task.project_id IS NULL
AND (project.info->>'task_presenter') IS NULL
AND project.hidden=0
AND project.owner_id="user".id;"""
)
results = session.execute(sql)
projects = []
for row in results:
project = dict(
id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info,
)
projects.append(project)
return projects
def get_all_featured(category=None):
"""Return a list of featured projects with a pagination."""
# sql = text(
# '''SELECT project.id, project.name, project.short_name, project.info,
# project.created, project.updated, project.description,
# "user".fullname AS owner
# FROM project, "user"
# WHERE project.featured=true
# AND "user".id=project.owner_id
# GROUP BY project.id, "user".id;''')
# results = session.execute(sql)
results = session.query(Project,
User).filter(User.id == Project.owner_id).filter(
Project.featured).group_by(
Project.id, User.id)
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
updated=row.updated,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(Project().to_public_json(project))
return projects
def get_all(category):
"""Return a list of projects with at least one task and a task_presenter.
"""
sql = text('''SELECT project.id, project.name, project.short_name,
project.description, project.info, project.created, project.updated,
project.category_id, project.featured, "user".fullname AS owner
FROM "user", task, project
LEFT OUTER JOIN category ON project.category_id=category.id
WHERE
category.short_name=:category
AND project.hidden=0
AND "user".id=project.owner_id
AND project.info LIKE('%task_presenter%')
AND task.project_id=project.id
GROUP BY project.id, "user".id ORDER BY project.name;''')
results = session.execute(sql, dict(category=category))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
projects.append(project)
return projects
def get_featured(category, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
count = n_featured()
sql = text(
'''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, featured, "user"
WHERE app.id=featured.app_id AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
results = db.engine.execute(sql, limit=per_page, offset=offset)
apps = []
for row in results:
app = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
overall_progress=overall_progress(row.id),
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
featured=row.id,
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
def get_featured(category, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
try:
count = n_featured()
sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, featured, "user"
WHERE app.id=featured.app_id AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
overall_progress=overall_progress(row.id),
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
featured=row.id,
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_users_page(page, per_page=24):
"""Return users with a paginator."""
offset = (page - 1) * per_page
sql = text('''SELECT "user".id, "user".name,
"user".fullname, "user".email_addr,
"user".created, "user".info, COUNT(task_run.id) AS task_runs
FROM task_run, "user"
WHERE "user".id=task_run.user_id GROUP BY "user".id
ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
results = session.execute(sql, dict(limit=per_page, offset=offset))
accounts = []
u = User()
for row in results:
user = dict(id=row.id,
name=row.name,
fullname=row.fullname,
email_addr=row.email_addr,
created=row.created,
task_runs=row.task_runs,
info=row.info,
registered_ago=pretty_date(row.created))
tmp = u.to_public_json(data=user)
accounts.append(tmp)
return accounts
def get_draft(category, page=1, per_page=5):
"""Return list of draft applications"""
count = n_draft()
sql = text('''
SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
results = db.engine.execute(sql, limit=per_page, offset=offset)
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
def get_draft(category=None, page=1, per_page=5):
"""Return list of draft projects."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.created,
project.description, project.info, "user".fullname as owner
FROM "user", project LEFT JOIN task ON project.id=task.project_id
WHERE task.project_id IS NULL
AND project.info NOT LIKE('%task_presenter%')
AND project.hidden=0
AND project.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
results = session.execute(sql, dict(limit=per_page, offset=offset))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
projects.append(project)
return projects
def get_all(category):
"""Return a list of projects with at least one task and a task_presenter.
"""
sql = text('''SELECT project.id, project.name, project.short_name,
project.description, project.info, project.created, project.updated,
project.category_id, project.featured, "user".fullname AS owner
FROM "user", task, project
LEFT OUTER JOIN category ON project.category_id=category.id
WHERE
category.short_name=:category
AND project.hidden=0
AND "user".id=project.owner_id
AND project.info LIKE('%task_presenter%')
AND task.project_id=project.id
GROUP BY project.id, "user".id ORDER BY project.name;''')
results = session.execute(sql, dict(category=category))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
projects.append(project)
return projects
def get_featured(category=None, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, "user"
WHERE app.featured=true AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
def get_all_featured(category=None):
"""Return a list of featured projects with a pagination."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.info,
project.created, project.updated, project.description,
"user".fullname AS owner
FROM project, "user"
WHERE project.featured=true
AND "user".id=project.owner_id
GROUP BY project.id, "user".id;''')
results = session.execute(sql)
projects = []
for row in results:
project = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
updated=row.updated,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(Project().to_public_json(project))
return projects
def get_user_summary(name):
"""Return user summary."""
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info,
"user".email_addr, COUNT(task_run.user_id) AS n_answers,
"user".valid_email, "user".confirmation_email_sent
FROM "user"
LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
results = session.execute(sql, dict(name=name))
user = dict()
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
created=row.created, api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=row.info,
email_addr=row.email_addr, n_answers=row.n_answers,
valid_email=row.valid_email,
confirmation_email_sent=row.confirmation_email_sent,
registered_ago=pretty_date(row.created))
if user:
rank_score = rank_and_score(user['id'])
user['rank'] = rank_score['rank']
user['score'] = rank_score['score']
user['total'] = get_total_users()
return user
else: # pragma: no cover
return None
def get_featured(category=None, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, "user"
WHERE app.featured=true AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_all_draft(category=None):
"""Return list of all draft projects."""
sql = text(
'''SELECT project.id, project.name, project.short_name, project.created,
project.description, project.info, project.updated,
"user".fullname AS owner
FROM "user", project
WHERE project.owner_id="user".id
AND project.published=false;''')
results = session.execute(sql)
projects = []
for row in results:
project = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(Project().to_public_json(project))
return projects
def get(category, page=1, per_page=5):
"""Return a list of apps with at least one task and a task_presenter
with a pagination for a given category"""
sql = text('''SELECT app.id, app.name, app.short_name, app.description,
app.info, app.created, app.category_id, app.featured, "user".fullname AS owner
FROM "user", task, app
LEFT OUTER JOIN category ON app.category_id=category.id
WHERE
category.short_name=:category
AND app.hidden=0
AND "user".id=app.owner_id
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id, "user".id ORDER BY app.name
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
def get_user_summary(name, current_user=None):
"""Return user summary."""
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info, "user".admin,
"user".locale,
"user".email_addr, COUNT(task_run.user_id) AS n_answers,
"user".valid_email, "user".confirmation_email_sent,
max(task_run.finish_time) AS last_task_submission_on,
"user".restrict
FROM "user"
LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
results = session.execute(sql, dict(name=name))
user = dict()
for row in results:
user = dict(id=row.id,
name=row.name,
fullname=row.fullname,
created=row.created,
api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=row.info,
admin=row.admin,
locale=row.locale,
email_addr=row.email_addr,
n_answers=row.n_answers,
valid_email=row.valid_email,
confirmation_email_sent=row.confirmation_email_sent,
registered_ago=pretty_date(row.created),
last_task_submission_on=row.last_task_submission_on,
restrict=row.restrict)
if user:
rank_score = rank_and_score(user['id'])
user['rank'] = rank_score['rank']
user['score'] = rank_score['score']
user['total'] = get_total_users()
if user['restrict']:
if (current_user and current_user.is_authenticated
and (current_user.id == user['id'])):
return user
else:
return None
else:
return user
else: # pragma: no cover
return None
def get_users_page(page, per_page=24):
offset = (page - 1) * per_page
sql = text('''SELECT "user".id, "user".name, "user".fullname, "user".email_addr,
"user".created, "user".info, COUNT(task_run.id) AS task_runs
FROM task_run, "user"
WHERE "user".id=task_run.user_id GROUP BY "user".id
ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
results = db.engine.execute(sql, limit=per_page, offset=offset)
accounts = []
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
email_addr=row.email_addr, created=row.created,
task_runs=row.task_runs, info=dict(json.loads(row.info)),
registered_ago=pretty_date(row.created))
accounts.append(user)
return accounts
def get(category, page=1, per_page=5):
"""Return a list of apps with at least one task and a task_presenter
with a pagination for a given category"""
try:
count = n_count(category)
sql = text('''SELECT app.id, app.name, app.short_name, app.description,
app.info, app.created, app.category_id, "user".fullname AS owner,
featured.app_id as featured
FROM "user", task, app
LEFT OUTER JOIN category ON app.category_id=category.id
LEFT OUTER JOIN featured ON app.id=featured.app_id
WHERE
category.short_name=:category
AND app.hidden=0
AND "user".id=app.owner_id
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id, "user".id, featured.app_id ORDER BY app.name
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get(category, page=1, per_page=5):
"""Return a list of apps with at least one task and a task_presenter
with a pagination for a given category"""
count = n_count(category)
sql = text('''
SELECT app.id, app.name, app.short_name, app.description,
app.info, app.created, app.category_id, "user".fullname AS owner,
featured.app_id as featured
FROM "user", task, app
LEFT OUTER JOIN category ON app.category_id=category.id
LEFT OUTER JOIN featured ON app.id=featured.app_id
WHERE
category.short_name=:category
AND app.hidden=0
AND "user".id=app.owner_id
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id, "user".id, featured.app_id ORDER BY app.name
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
results = db.engine.execute(sql,
category=category,
limit=per_page,
offset=offset)
apps = []
for row in results:
app = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
def get(category, page=1, per_page=5):
"""Return a list of apps with at least one task and a task_presenter
with a pagination for a given category"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.description,
app.info, app.created, app.category_id, app.featured, "user".fullname AS owner
FROM "user", task, app
LEFT OUTER JOIN category ON app.category_id=category.id
WHERE
category.short_name=:category
AND app.hidden=0
AND "user".id=app.owner_id
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id, "user".id ORDER BY app.name
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_users_page(page, per_page=24):
offset = (page - 1) * per_page
sql = text(
'''SELECT "user".id, "user".name, "user".fullname, "user".email_addr,
"user".created, "user".info, COUNT(task_run.id) AS task_runs
FROM task_run, "user"
WHERE "user".id=task_run.user_id GROUP BY "user".id
ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
results = session.execute(sql, dict(limit=per_page, offset=offset))
accounts = []
for row in results:
user = dict(id=row.id,
name=row.name,
fullname=row.fullname,
email_addr=row.email_addr,
created=row.created,
task_runs=row.task_runs,
info=dict(json.loads(row.info)),
registered_ago=pretty_date(row.created))
accounts.append(user)
return accounts
def get_users_page(page, per_page=24):
try:
offset = (page - 1) * per_page
sql = text('''SELECT "user".id, "user".name, "user".fullname, "user".email_addr,
"user".created, "user".info, COUNT(task_run.id) AS task_runs
FROM task_run, "user"
WHERE "user".id=task_run.user_id GROUP BY "user".id
ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
accounts = []
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
email_addr=row.email_addr, created=row.created,
task_runs=row.task_runs, info=dict(json.loads(row.info)),
registered_ago=pretty_date(row.created))
accounts.append(user)
return accounts
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def text_search(search_text, show_unpublished=True, show_hidden=True):
"""Return a list of published projects short_names.
"""
sql = text('''SELECT project.id, project.name, project.short_name,
project.description, project.info, project.created, project.updated,
project.category_id, project.featured, "user".fullname AS owner
FROM project
LEFT JOIN "user" ON project.owner_id="user".id
WHERE
(project.name ILIKE '%' || :search_text || '%'
OR "user".fullname ILIKE '%' || :search_text || '%'
OR project.description ILIKE '%' || :search_text || '%')
{}
{}
ORDER BY project.name;'''.format(
'AND project.published=true' if not show_unpublished else '',
'AND coalesce(project.hidden, false)=false'
if not show_hidden else ''))
results = session.execute(sql, dict(search_text=search_text))
projects = []
for row in results:
project = dict(id=row.id,
name=row.name,
short_name=row.short_name,
created=row.created,
updated=row.updated,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=row.info)
projects.append(Project().to_public_json(project))
return projects
def get_user_summary(name):
try:
# Get USER
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info,
"user".email_addr, COUNT(task_run.user_id) AS n_answers
FROM "user" LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(name=name))
user = dict()
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
created=row.created, api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=dict(json.loads(row.info)),
email_addr=row.email_addr, n_answers=row.n_answers,
registered_ago=pretty_date(row.created))
if user:
rank_score = rank_and_score(user['id'])
user['rank'] = rank_score['rank']
user['score'] = rank_score['score']
user['total'] = get_total_users()
return user
else: # pragma: no cover
return None
except: # pragma: no cover
session.rollback()
finally:
session.close()
def get_user_summary(name):
# Get USER
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info,
"user".email_addr, COUNT(task_run.user_id) AS n_answers
FROM "user" LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
results = db.engine.execute(sql, name=name)
user = dict()
for row in results:
user = dict(id=row.id, name=row.name, fullname=row.fullname,
created=row.created, api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=dict(json.loads(row.info)),
email_addr=row.email_addr, n_answers=row.n_answers,
registered_ago=pretty_date(row.created))
# Rank
# See: https://gist.github.com/tokumine/1583695
sql = text('''
WITH global_rank AS (
WITH scores AS (
SELECT user_id, COUNT(*) AS score FROM task_run
WHERE user_id IS NOT NULL GROUP BY user_id)
SELECT user_id, score, rank() OVER (ORDER BY score desc)
FROM scores)
SELECT * from global_rank WHERE user_id=:user_id;
''')
if user:
results = db.engine.execute(sql, user_id=user['id'])
for row in results:
user['rank'] = row.rank
user['score'] = row.score
# Get the APPs where the USER has participated
sql = text('''
SELECT app.id, app.name, app.short_name, app.info,
COUNT(task_run.app_id) AS n_answers FROM app, task_run
WHERE app.id=task_run.app_id AND
task_run.user_id=:user_id GROUP BY app.id
ORDER BY n_answers DESC;
''')
results = db.engine.execute(sql, user_id=user['id'])
apps_contributed = []
for row in results:
app = dict(id=row.id, name=row.name, info=dict(json.loads(row.info)),
short_name=row.short_name,
n_answers=row.n_answers)
apps_contributed.append(app)
# Get the CREATED APPS by the USER
sql = text('''
SELECT app.id, app.name, app.short_name, app.info, app.created
FROM app
WHERE app.owner_id=:user_id
ORDER BY app.created DESC;
''')
results = db.engine.execute(sql, user_id=user['id'])
apps_created = []
for row in results:
app = dict(id=row.id, name=row.name,
short_name=row.short_name,
info=dict(json.loads(row.info)))
apps_created.append(app)
return user, apps_contributed, apps_created
else: # pragma: no cover
return None, None, None
def get_user_summary(name):
# Get USER
sql = text('''
SELECT "user".id, "user".name, "user".fullname, "user".created,
"user".api_key, "user".twitter_user_id, "user".facebook_user_id,
"user".google_user_id, "user".info,
"user".email_addr, COUNT(task_run.user_id) AS n_answers
FROM "user" LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
WHERE "user".name=:name
GROUP BY "user".id;
''')
results = db.engine.execute(sql, name=name)
user = dict()
for row in results:
user = dict(id=row.id,
name=row.name,
fullname=row.fullname,
created=row.created,
api_key=row.api_key,
twitter_user_id=row.twitter_user_id,
google_user_id=row.google_user_id,
facebook_user_id=row.facebook_user_id,
info=dict(json.loads(row.info)),
email_addr=row.email_addr,
n_answers=row.n_answers,
registered_ago=pretty_date(row.created))
# Rank
# See: https://gist.github.com/tokumine/1583695
sql = text('''
WITH global_rank AS (
WITH scores AS (
SELECT user_id, COUNT(*) AS score FROM task_run
WHERE user_id IS NOT NULL GROUP BY user_id)
SELECT user_id, score, rank() OVER (ORDER BY score desc)
FROM scores)
SELECT * from global_rank WHERE user_id=:user_id;
''')
if user:
results = db.engine.execute(sql, user_id=user['id'])
for row in results:
user['rank'] = row.rank
user['score'] = row.score
# Get the APPs where the USER has participated
sql = text('''
SELECT app.id, app.name, app.short_name, app.info,
COUNT(task_run.app_id) AS n_answers FROM app, task_run
WHERE app.id=task_run.app_id AND
task_run.user_id=:user_id GROUP BY app.id
ORDER BY n_answers DESC;
''')
results = db.engine.execute(sql, user_id=user['id'])
apps_contributed = []
for row in results:
app = dict(id=row.id,
name=row.name,
info=dict(json.loads(row.info)),
short_name=row.short_name,
n_answers=row.n_answers)
apps_contributed.append(app)
# Get the CREATED APPS by the USER
sql = text('''
SELECT app.id, app.name, app.short_name, app.info, app.created
FROM app
WHERE app.owner_id=:user_id
ORDER BY app.created DESC;
''')
results = db.engine.execute(sql, user_id=user['id'])
apps_created = []
for row in results:
app = dict(id=row.id,
name=row.name,
short_name=row.short_name,
info=dict(json.loads(row.info)))
apps_created.append(app)
return user, apps_contributed, apps_created
else: # pragma: no cover
return None, None, None
def last_activity(app):
if (len(app.task_runs) >= 1):
return pretty_date(app.task_runs[0].finish_time)
else:
return "None"
def pretty_date_filter(s):
return pretty_date(s)
def _pretty_date_filter(s):
return pretty_date(s)
def last_activity(self):
if (len(self.task_runs) >= 1):
return pretty_date(self.task_runs[0].finish_time)
#return self.task_runs[0].finish_time
else:
return "None"
def test_pretty_date(self):
"""Test pretty_date works."""
now = datetime.now()
pd = util.pretty_date()
assert pd == "just now", pd
pd = util.pretty_date(now.isoformat())
assert pd == "just now", pd
pd = util.pretty_date(calendar.timegm(time.gmtime()))
assert pd == "just now", pd
d = now + timedelta(days=10)
pd = util.pretty_date(d.isoformat())
assert pd == '', pd
d = now - timedelta(seconds=10)
pd = util.pretty_date(d.isoformat())
assert pd == '10 seconds ago', pd
d = now - timedelta(minutes=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'a minute ago', pd
d = now - timedelta(minutes=2)
pd = util.pretty_date(d.isoformat())
assert pd == '2 minutes ago', pd
d = now - timedelta(hours=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'an hour ago', pd
d = now - timedelta(hours=5)
pd = util.pretty_date(d.isoformat())
assert pd == '5 hours ago', pd
d = now - timedelta(days=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'Yesterday', pd
d = now - timedelta(days=5)
pd = util.pretty_date(d.isoformat())
assert pd == '5 days ago', pd
d = now - timedelta(weeks=1)
pd = util.pretty_date(d.isoformat())
assert pd == '1 weeks ago', pd
d = now - timedelta(days=32)
pd = util.pretty_date(d.isoformat())
assert pd == '1 month ago', pd
d = now - timedelta(days=62)
pd = util.pretty_date(d.isoformat())
assert pd == '2 months ago', pd
d = now - timedelta(days=366)
pd = util.pretty_date(d.isoformat())
assert pd == '1 year ago', pd
d = now - timedelta(days=766)
pd = util.pretty_date(d.isoformat())
assert pd == '2 years ago', pd
def test_pretty_date(self):
"""Test pretty_date works."""
now = datetime.now()
pd = util.pretty_date()
assert pd == "just now", pd
pd = util.pretty_date(now.isoformat())
assert pd == "just now", pd
pd = util.pretty_date(calendar.timegm(time.gmtime()))
assert pd == "just now", pd
d = now + timedelta(days=10)
pd = util.pretty_date(d.isoformat())
assert pd == '', pd
d = now - timedelta(seconds=10)
pd = util.pretty_date(d.isoformat())
assert pd == '10 seconds ago', pd
d = now - timedelta(minutes=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'a minute ago', pd
d = now - timedelta(minutes=2)
pd = util.pretty_date(d.isoformat())
assert pd == '2 minutes ago', pd
d = now - timedelta(hours=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'an hour ago', pd
d = now - timedelta(hours=5)
pd = util.pretty_date(d.isoformat())
assert pd == '5 hours ago', pd
d = now - timedelta(days=1)
pd = util.pretty_date(d.isoformat())
assert pd == 'Yesterday', pd
d = now - timedelta(days=5)
pd = util.pretty_date(d.isoformat())
assert pd == '5 days ago', pd
d = now - timedelta(weeks=1)
pd = util.pretty_date(d.isoformat())
assert pd == '1 weeks ago', pd
d = now - timedelta(days=32)
pd = util.pretty_date(d.isoformat())
assert pd == '1 month ago', pd
d = now - timedelta(days=62)
pd = util.pretty_date(d.isoformat())
assert pd == '2 months ago', pd
d = now - timedelta(days=366)
pd = util.pretty_date(d.isoformat())
assert pd == '1 year ago', pd
d = now - timedelta(days=766)
pd = util.pretty_date(d.isoformat())
assert pd == '2 years ago', pd
