def test_task_browse_gold_task_filters(self):
filters = dict(task_id=1,
hide_completed=True,
gold_task='1',
order_by='task_id')
expected_filter_query = " AND task.id = :task_id AND task.state='ongoing' AND task.calibration = :calibration"
expected_params = {'task_id': 1, 'calibration': '1'}
filters, params = get_task_filters(filters)
assert filters == expected_filter_query, filters
assert params == expected_params, params
filters = dict(task_id=1,
hide_completed=True,
gold_task='0',
order_by='task_id')
expected_params = {'task_id': 1, 'calibration': '0'}
filters, params = get_task_filters(filters)
assert filters == expected_filter_query, filters
assert params == expected_params, params
args = dict(task_id=12345, gold_task='1')
valid_args = dict(task_id=12345,
gold_task='1',
order_by_dict={},
display_columns=[
u'task_id', u'priority', u'pcomplete',
u'created', u'finish_time', u'gold_task',
u'actions'
])
pargs = parse_tasks_browse_args(args)
assert pargs == valid_args, pargs
args = dict(task_id=12345, gold_task='0')
valid_args = dict(task_id=12345,
gold_task='0',
order_by_dict={},
display_columns=[
u'task_id', u'priority', u'pcomplete',
u'created', u'finish_time', u'gold_task',
u'actions'
])
pargs = parse_tasks_browse_args(args)
assert pargs == valid_args, pargs
args = dict(task_id=12345, gold_task='All')
valid_args = dict(task_id=12345,
order_by_dict={},
display_columns=[
u'task_id', u'priority', u'pcomplete',
u'created', u'finish_time', u'gold_task',
u'actions'
])
pargs = parse_tasks_browse_args(args)
assert pargs == valid_args, pargs
args = dict(task_id=12345, gold_task='7')
assert_raises(ValueError, parse_tasks_browse_args, args)
def update_priority(self, project_id, priority, filters):
priority = min(1.0, priority)
priority = max(0.0, priority)
conditions, params = get_task_filters(filters)
sql = text('''
WITH to_update AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
)
UPDATE task
SET priority_0=:priority
WHERE project_id=:project_id AND task.id in (
SELECT id FROM to_update);
'''.format(conditions))
self.db.session.execute(sql, dict(priority=priority,
project_id=project_id,
**params))
self.db.session.commit()
cached_projects.clean_project(project_id)
def test_task_browse_get_task_filters(self):
filters = dict(task_id=1,
hide_completed=True,
pcomplete_from='0.5',
pcomplete_to='0.7',
priority_from=0.0,
priority_to=0.5,
created_from='2018-01-01T00:00:00.0001',
created_to='2018-12-12T00:00:00.0001',
ftime_from='2018-01-01T00:00:00.0001',
ftime_to='2018-12-12T00:00:00.0001',
order_by='task_id',
filter_by_field=[(u'CompanyName', u'starts with',
u'abc')],
filter_by_upref=dict(languages=['en'],
locations=['us']),
state='ongoing')
expected_filter_query = ''' AND task.id = :task_id AND task.state=\'ongoing\' AND (coalesce(ct, 0)/task.n_answers) >= :pcomplete_from AND LEAST(coalesce(ct, 0)/task.n_answers, 1.0) <= :pcomplete_to AND priority_0 >= :priority_from AND priority_0 <= :priority_to AND task.created >= :created_from AND task.created <= :created_to AND ft >= :ftime_from AND ft <= :ftime_to AND state = :state AND (COALESCE(task.info->>\'CompanyName\', \'\') ilike :filter_by_field_0 escape \'\\\') AND ( ( (task.user_pref-> \'locations\' IS NULL AND task.user_pref-> \'languages\' IS NULL) OR (task.user_pref @> \'{"languages": ["en"]}\' OR task.user_pref @> \'{"locations": ["us"]}\') ) )'''
expected_params = {
'task_id': 1,
'pcomplete_from': '0.5',
'pcomplete_to': '0.7',
'ftime_to': '2018-12-12T05:00:00.000100+00:00',
'created_from': '2018-01-01T05:00:00.000100+00:00',
'ftime_from': '2018-01-01T05:00:00.000100+00:00',
'state': 'ongoing',
'priority_to': 0.5,
'priority_from': 0.0,
'filter_by_field_0': 'abc%',
'created_to': '2018-12-12T05:00:00.000100+00:00'
}
filters, params = get_task_filters(filters)
assert filters == expected_filter_query, filters
assert params == expected_params, params
def update_tasks_redundancy(self, project, n_answers, filters=None):
"""
Update the n_answer of every task from a project and their state.
Use raw SQL for performance. Mark tasks as exported = False for
tasks with curr redundancy < new redundancy, with state as completed
and were marked as exported = True
"""
from pybossa.jobs import check_and_send_task_notifications
if n_answers < self.MIN_REDUNDANCY or n_answers > self.MAX_REDUNDANCY:
raise ValueError("Invalid redundancy value: {}".format(n_answers))
filters = filters or {}
task_expiration = '{} day'.format(self.rdancy_upd_exp)
conditions, params = get_task_filters(filters)
tasks_not_updated = self._get_redundancy_update_msg(
project, n_answers, conditions, params, task_expiration)
self.update_task_exported_status(project.id, n_answers, conditions, params, task_expiration)
sql = text('''
WITH all_tasks_with_orig_filter AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
),
tasks_with_file_urls AS (
SELECT t.id as id FROM task t
WHERE t.id IN (SELECT id from all_tasks_with_orig_filter)
AND jsonb_typeof(t.info) = 'object'
AND EXISTS(SELECT TRUE FROM jsonb_object_keys(t.info) AS key
WHERE key ILIKE '%\_\_upload\_url%')
),
tasks_excl_file_urls AS (
SELECT id FROM all_tasks_with_orig_filter
WHERE id NOT IN (SELECT id FROM tasks_with_file_urls)
)
UPDATE task SET n_answers=:n_answers,
state='ongoing' WHERE project_id=:project_id AND
((id IN (SELECT id from tasks_excl_file_urls)) OR
(id IN (SELECT id from tasks_with_file_urls) AND state='ongoing'
AND TO_DATE(created, 'YYYY-MM-DD\THH24:MI:SS.US') >= NOW() - :task_expiration ::INTERVAL));'''
.format(conditions))
self.db.session.execute(sql, dict(n_answers=n_answers,
project_id=project.id,
task_expiration=task_expiration,
**params))
self.update_task_state(project.id)
self.db.session.commit()
cached_projects.clean_project(project.id)
check_and_send_task_notifications(project.id)
return tasks_not_updated
def delete_valid_from_project(self,
project,
force_reset=False,
filters=None):
if not force_reset:
"""Delete only tasks that have no results associated."""
params = {}
sql = text('''
DELETE FROM task WHERE task.project_id=:project_id
AND task.id NOT IN
(SELECT task_id FROM result
WHERE result.project_id=:project_id GROUP BY result.task_id);
''')
else:
"""force reset, remove all results."""
filters = filters or {}
conditions, params = get_task_filters(filters)
# bulkdel db conn is with db user having session_replication_role
# when bulkdel is not configured, make explict sql query to set
# session replication role to replica
sql_session_repl = ''
if not 'bulkdel' in current_app.config.get('SQLALCHEMY_BINDS'):
sql_session_repl = 'SET session_replication_role TO replica;'
sql = text('''
BEGIN;
{}
CREATE TEMP TABLE to_delete ON COMMIT DROP AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
);
DELETE FROM result WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task_run WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task WHERE task.project_id=:project_id
AND id in (SELECT id FROM to_delete);
COMMIT;
'''.format(sql_session_repl, conditions))
self.db.bulkdel_session.execute(sql,
dict(project_id=project.id, **params))
self.db.bulkdel_session.commit()
cached_projects.clean_project(project.id)
self._delete_zip_files_from_store(project)
def browse_tasks(project_id, args):
"""Cache browse tasks view for a project."""
tasks = []
total_count = task_count(project_id, args)
if not total_count:
return total_count, tasks
filters, filter_params = get_task_filters(args)
sql = text('''
SELECT task.id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created, task.calibration
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id''' + filters +
" ORDER BY %s" % (args.get('order_by') or 'id ASC') +
" LIMIT :limit OFFSET :offset")
limit = args.get('records_per_page') or 10
offset = args.get('offset') or 0
results = session.execute(
sql,
dict(project_id=project_id,
limit=limit,
offset=offset,
**filter_params))
for row in results:
# TODO: use Jinja filters to format date
def format_date(date):
if date is not None:
return convert_utc_to_est(date).strftime('%m-%d-%y %H:%M')
finish_time = format_date(row.ft)
created = format_date(row.created)
task = dict(id=row.id,
n_task_runs=row.n_task_runs,
n_answers=row.n_answers,
priority_0=row.priority_0,
finish_time=finish_time,
created=created,
calibration=row.calibration)
task['pct_status'] = _pct_status(row.n_task_runs, row.n_answers)
tasks.append(task)
return total_count, tasks
def get_consensus_data_metadata(project_id, filters):
conditions, filter_params = get_task_filters(filters)
query = text('''
SELECT
task.id as task_id,
task.project_id as project_id,
task.calibration as gold,
task.info as task_info,
task.user_pref as user_pref,
r.info as consensus,
taskruns.task_run__id as task_run__id,
taskruns.task_run__created as task_run__created,
taskruns.task_run__finish_time as task_run__finish_time,
taskruns.task_run__user_id as task_run__user_id,
taskruns.task_run__info as task_run__info,
taskruns.email_addr as email_addr,
taskruns.fullname as fullname
FROM task LEFT JOIN (
SELECT task_id,
CAST(COUNT(tr.id) AS FLOAT) AS ct,
MAX(tr.finish_time) as ft,
array_agg(tr.id) as task_run__id,
array_agg(tr.created) as task_run__created,
array_agg(tr.finish_time) as task_run__finish_time,
json_object_agg(u.name, tr.info) as task_run__info,
array_agg(tr.user_id) as task_run__user_id,
array_agg(u.email_addr) as email_addr,
array_agg(u.fullname) as fullname
FROM task_run tr
JOIN "user" u
ON tr.user_id = u.id
WHERE project_id = :project_id
GROUP BY task_id
) AS taskruns
ON task.id = taskruns.task_id
LEFT JOIN result r
ON task.id = r.task_id
AND r.last_version = True
WHERE (task.state = 'completed' OR (task.calibration = 1 AND taskruns.ct > 0))
AND task.project_id=:project_id
{};
'''.format(conditions))
params = dict(project_id=project_id, **filter_params)
rows = db.slave_session.execute(query, params).fetchall()
return format_consensus(rows)
def browse_tasks_export_count(obj, project_id, expanded, filters):
"""Returns the count of the tasks from the browse tasks view
for a project using the same filters that are selected by
the user in the UI.
"""
conditions, filter_params = get_task_filters(filters)
if obj == 'task':
sql = text('''
SELECT COUNT(task.id)
FROM task
LEFT OUTER JOIN (
SELECT task_id
, CAST(COUNT(id) AS FLOAT) AS ct
, MAX(finish_time) as ft
FROM task_run
WHERE project_id = :project_id
GROUP BY task_id
) AS log_counts
ON task.id = log_counts.task_id
WHERE project_id = :project_id
{0}
'''.format(conditions))
elif obj == 'task_run':
sql = text('''
SELECT COUNT(task_run.id)
FROM task_run
LEFT JOIN task
ON task_run.task_id = task.id
LEFT OUTER JOIN (
SELECT task_id
, CAST(COUNT(id) AS FLOAT) AS ct
, MAX(finish_time) as ft
FROM task_run
WHERE project_id = :project_id
GROUP BY task_id
) AS log_counts
ON task.id = log_counts.task_id
WHERE task_run.project_id = :project_id
{0}
'''.format(conditions))
else:
return
return session.execute(sql, dict(project_id=project_id,
**filter_params)).scalar()
def task_count(project_id, filters):
"""Return the count of tasks in a project matching the given filters."""
conditions, filter_params = get_task_filters(filters)
sql = text('''
SELECT COUNT(*) OVER() as total_count, task.id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {} LIMIT 1'''
.format(conditions))
results = session.execute(sql, dict(project_id=project_id,
**filter_params))
row = results.first()
return row.total_count if row else 0
def delete_valid_from_project(self, project, force_reset=False, filters=None):
if not force_reset:
"""Delete only tasks that have no results associated."""
params = {}
sql = text('''
DELETE FROM task WHERE task.project_id=:project_id
AND task.id NOT IN
(SELECT task_id FROM result
WHERE result.project_id=:project_id GROUP BY result.task_id);
''')
else:
"""force reset, remove all results."""
filters = filters or {}
conditions, params = get_task_filters(filters)
sql = text('''
BEGIN;
CREATE TEMP TABLE to_delete ON COMMIT DROP AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
);
DELETE FROM result WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task_run WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task WHERE task.project_id=:project_id
AND id in (SELECT id FROM to_delete);
COMMIT;
'''.format(conditions))
self.db.session.execute(sql, dict(project_id=project.id, **params))
self.db.session.commit()
cached_projects.clean_project(project.id)
self._delete_zip_files_from_store(project)
def task_count(project_id, args):
"""Return the count of tasks in a project matching the given filters."""
filters, filter_params = get_task_filters(args)
sql = text('''
SELECT COUNT(*) AS total_count
FROM task WHERE task.id IN
(
SELECT task.id FROM task LEFT OUTER JOIN
(
SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id
) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
)
'''.format(filters))
results = session.execute(sql, dict(project_id=project_id,
**filter_params))
row = results.first()
return row.total_count if row else 0
def update_tasks_redundancy(self, project, n_answers, filters=None):
"""
Update the n_answer of every task from a project and their state.
Use raw SQL for performance. Mark tasks as exported = False for
tasks with curr redundancy < new redundancy, with state as completed
and were marked as exported = True
"""
filters = filters or {}
conditions, params = get_task_filters(filters)
if n_answers < self.MIN_REDUNDANCY or n_answers > self.MAX_REDUNDANCY:
raise ValueError("Invalid redundancy value: {}".format(n_answers))
self.update_task_exported_status(project.id, n_answers, conditions, params)
sql = text('''
WITH to_update AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
)
UPDATE task SET n_answers=:n_answers,
state='ongoing' WHERE project_id=:project_id
AND task.id in (SELECT id from to_update);'''
.format(conditions))
self.db.session.execute(sql, dict(n_answers=n_answers,
project_id=project.id,
**params))
self.update_task_state(project.id, n_answers)
self.db.session.commit()
cached_projects.clean_project(project.id)
def delete_bulk_tasks(data):
"""Delete tasks in bulk from project."""
from sqlalchemy.sql import text
from pybossa.core import db
import pybossa.cache.projects as cached_projects
from pybossa.cache.task_browse_helpers import get_task_filters
project_id = data['project_id']
project_name = data['project_name']
curr_user = data['curr_user']
coowners = data['coowners']
current_user_fullname = data['current_user_fullname']
force_reset = data['force_reset']
params = {}
# lock tasks for given project with SELECT FOR UPDATE
# create temp table with all tasks to be deleted
# during transaction, disable constraints check with session_replication_role
# delete rows from child talbes first and then from parent
if not force_reset:
"""Delete only tasks that have no results associated."""
sql = text('''
BEGIN;
SELECT task_id FROM counter WHERE project_id=:project_id FOR UPDATE;
SELECT task_id FROM task_run WHERE project_id=:project_id FOR UPDATE;
SELECT id FROM task WHERE project_id=:project_id FOR UPDATE;
SET session_replication_role TO replica;
CREATE TEMP TABLE to_delete ON COMMIT DROP AS (
SELECT task.id as id FROM task WHERE project_id=:project_id
AND task.id NOT IN
(SELECT task_id FROM result
WHERE result.project_id=:project_id
GROUP BY result.task_id)
);
DELETE FROM counter WHERE project_id=:project_id
AND task_id IN (SELECT id FROM to_delete);
DELETE FROM task_run WHERE project_id=:project_id
AND task_id IN (SELECT id FROM to_delete);
DELETE FROM task WHERE project_id=:project_id
AND id IN (SELECT id FROM to_delete);
COMMIT;
''')
msg = ("Tasks and taskruns with no associated results have been "
"deleted from project {0} by {1}"
.format(project_name, current_user_fullname))
else:
args = data.get('filters', {})
conditions, params = get_task_filters(args)
sql = text('''
BEGIN;
SELECT task_id FROM counter WHERE project_id=:project_id FOR UPDATE;
SELECT task_id FROM result WHERE project_id=:project_id FOR UPDATE;
SELECT task_id FROM task_run WHERE project_id=:project_id FOR UPDATE;
SELECT id FROM task WHERE project_id=:project_id FOR UPDATE;
SET session_replication_role TO replica;
CREATE TEMP TABLE to_delete ON COMMIT DROP AS (
SELECT task.id as id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id {}
);
DELETE FROM counter WHERE project_id=:project_id
AND task_id IN (SELECT id FROM to_delete);
DELETE FROM result WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task_run WHERE project_id=:project_id
AND task_id in (SELECT id FROM to_delete);
DELETE FROM task WHERE task.project_id=:project_id
AND id in (SELECT id FROM to_delete);
COMMIT;
'''.format(conditions))
msg = ("Tasks, taskruns and results associated have been "
"deleted from project {0} as requested by {1}"
.format(project_name, current_user_fullname))
db.session.execute(sql, dict(project_id=project_id, **params))
cached_projects.clean_project(project_id)
subject = 'Tasks deletion from %s' % project_name
body = 'Hello,\n\n' + msg + '\n\nThe %s team.'\
% current_app.config.get('BRAND')
recipients = [curr_user]
for user in coowners:
recipients.append(user.email_addr)
mail_dict = dict(recipients=recipients, subject=subject, body=body)
send_mail(mail_dict)
def browse_tasks_export(obj, project_id, expanded, filters, disclose_gold):
"""Export tasks from the browse tasks view for a project
using the same filters that are selected by the user
in the UI.
"""
TASK_FIELDS, TASK_GOLD_FIELD, TASKRUN_FIELDS = (
(TASK_FIELDS_WITH_GOLD, TASK_GOLD_FIELD_WITH_GOLD,
TASKRUN_FIELDS_WITH_GOLD) if disclose_gold else
(TASK_FIELDS_WITHOUT_GOLD, TASK_GOLD_FIELD_WITHOUT_GOLD,
TASKRUN_FIELDS_WITHOUT_GOLD))
conditions, filter_params = get_task_filters(filters)
if obj == 'task':
sql = text('''
SELECT {0}
FROM task
LEFT OUTER JOIN (
SELECT task_id
, CAST(COUNT(id) AS FLOAT) AS ct
, MAX(finish_time) as ft
FROM task_run
WHERE project_id = :project_id
GROUP BY task_id
) AS log_counts
ON task.id = log_counts.task_id
WHERE project_id = :project_id
{1}
'''.format(
_field_mapreducer((TASK_FIELDS, ), (TASK_GOLD_FIELD, )),
conditions))
elif obj == 'task_run':
if expanded:
sql = text('''
SELECT {0}
FROM task_run
LEFT JOIN task
ON task_run.task_id = task.id
LEFT OUTER JOIN (
SELECT task_id
, CAST(COUNT(id) AS FLOAT) AS ct
, MAX(finish_time) as ft
FROM task_run
WHERE project_id = :project_id
GROUP BY task_id
) AS log_counts
ON task.id = log_counts.task_id
LEFT JOIN "user"
ON task_run.user_id = "user".id
WHERE task_run.project_id = :project_id
{1}
'''.format(
_field_mapreducer(
(TASKRUN_FIELDS, ''), (TASK_FIELDS, 'task__'),
(USER_FIELDS, 'user__'), (TASK_GOLD_FIELD, 'task__')),
conditions))
else:
sql = text('''
SELECT {0}
FROM task_run
LEFT JOIN task
ON task_run.task_id = task.id
LEFT OUTER JOIN (
SELECT task_id
, CAST(COUNT(id) AS FLOAT) AS ct
, MAX(finish_time) as ft
FROM task_run
WHERE project_id = :project_id
GROUP BY task_id
) AS log_counts
ON task_run.task_id = log_counts.task_id
WHERE task_run.project_id = :project_id
{1}
'''.format(
_field_mapreducer((TASKRUN_FIELDS, ''),
(TASK_GOLD_FIELD, 'task__')), conditions))
else:
return
return session.execute(sql, dict(project_id=project_id, **filter_params))
def browse_tasks(project_id,
args,
filter_user_prefs=False,
user_id=None,
**kwargs):
"""Cache browse tasks view for a project."""
# TODO: use Jinja filters to format date
def format_date(date):
if date is not None:
return convert_utc_to_est(date).strftime('%m-%d-%y %H:%M')
def format_task(row, lock_users=[]):
"""convert database record to task dictionary and format data."""
finish_time = format_date(row.ft)
created = format_date(row.created)
user_pref = row.user_pref or {}
task = dict(id=row.id,
n_task_runs=row.n_task_runs,
n_answers=row.n_answers,
priority_0=row.priority_0,
finish_time=finish_time,
created=created,
calibration=row.calibration,
userPrefLang=", ".join(user_pref.get("languages", [])),
userPrefLoc=", ".join(user_pref.get("locations", [])),
lock_users=lock_users)
task['pct_status'] = _pct_status(row.n_task_runs, row.n_answers)
return task
order_by = args.get('order_by') or ""
tasks = []
total_count = task_count(project_id, args)
if not total_count:
return total_count, tasks
locked_tasks_in_project = {}
for lock in get_locked_tasks_project(project_id):
locked_tasks_in_project.setdefault(int(lock["task_id"]),
[]).append(lock["user_id"])
args['user_id'] = user_id
filters, filter_params = get_task_filters(args)
sql = """
SELECT task.id,
coalesce(ct, 0) as n_task_runs, task.n_answers, ft,
priority_0, task.created, task.calibration,
task.user_pref, task.worker_filter, task.worker_pref
FROM task LEFT OUTER JOIN
(SELECT task_id, CAST(COUNT(id) AS FLOAT) AS ct,
MAX(finish_time) as ft FROM task_run
WHERE project_id=:project_id GROUP BY task_id) AS log_counts
ON task.id=log_counts.task_id
WHERE task.project_id=:project_id""" + filters + \
" ORDER BY %s" % (args.get('order_by') or 'id ASC')
params = dict(project_id=project_id, **filter_params)
limit = args.get('records_per_page') or 10
offset = args.get('offset') or 0
if filter_user_prefs:
# construct task list for worker view
params["assign_user"] = args["sql_params"]["assign_user"]
results = session.execute(text(sql), params)
task_rank_info = []
user_profile = args.get("filter_by_wfilter_upref",
{}).get("current_user_profile", {})
for row in results:
score = 0
w_pref = row.worker_pref or {}
w_filter = row.worker_filter or {}
user_pref = row.user_pref or {}
# validate worker_filter and compute preference score
if not user_meet_task_requirement(row.id, w_filter, user_profile):
continue
if not args.get('order_by'):
# if there is no sort defined, sort task by preference scores
score = get_task_preference_score(w_pref, user_profile)
task = format_task(row)
task_rank_info.append((task, score))
# get a list of available tasks for current worker
total_count = len(task_rank_info)
tasks = select_available_tasks(task_rank_info, locked_tasks_in_project,
project_id, user_id, offset + limit,
args.get("order_by"))
tasks = tasks[offset:offset + limit]
else:
# construct task browse page for owners/admins
if not "lock_status" in order_by:
sql += " LIMIT :limit OFFSET :offset"
params["limit"] = limit
params["offset"] = offset
results = session.execute(text(sql), params)
task_rank_info = []
for row in results:
task = format_task(row, locked_tasks_in_project.get(row.id, []))
lock_score = 0
if row.id in locked_tasks_in_project:
lock_score = -1
else:
lock_score = task['pct_status']
task_rank_info.append((task, lock_score))
if order_by == "lock_status asc":
task_rank_info = heapq.nlargest(offset + limit,
task_rank_info,
key=lambda tup: tup[1])
tasks = task_rank_info[offset:offset + limit]
elif order_by == "lock_status desc":
task_rank_info = heapq.nsmallest(offset + limit,
task_rank_info,
key=lambda tup: tup[1])
tasks = task_rank_info[offset:offset + limit]
else:
tasks = task_rank_info
return total_count, [t[0] for t in tasks]
