def get_task_filters(args):
"""
build the WHERE part of the query using the filter parameters
return the part of the WHERE clause and the dictionary of bound parameters
"""
filters = ''
params = {}
if args.get('task_id'):
params['task_id'] = args['task_id']
filters += ' AND task.id = :task_id'
if args.get('hide_completed') and args.get('hide_completed') is True:
filters += " AND task.state='ongoing'"
if args.get('pcomplete_from') is not None:
params['pcomplete_from'] = args['pcomplete_from']
filters += " AND (coalesce(ct, 0)/task.n_answers) >= :pcomplete_from"
if args.get('pcomplete_to') is not None:
params['pcomplete_to'] = args['pcomplete_to']
filters += " AND (coalesce(ct, 0)/task.n_answers) <= :pcomplete_to"
if args.get('priority_from') is not None:
params['priority_from'] = args['priority_from']
filters += " AND priority_0 >= :priority_from"
if args.get('priority_to') is not None:
params['priority_to'] = args['priority_to']
filters += " AND priority_0 <= :priority_to"
if args.get('created_from'):
datestring = convert_est_to_utc(args['created_from']).isoformat()
params['created_from'] = datestring
filters += " AND task.created >= :created_from"
if args.get('created_to'):
datestring = convert_est_to_utc(args['created_to']).isoformat()
params['created_to'] = datestring
filters += " AND task.created <= :created_to"
if args.get('ftime_from'):
datestring = convert_est_to_utc(args['ftime_from']).isoformat()
params['ftime_from'] = datestring
filters += " AND ft >= :ftime_from"
if args.get('ftime_to'):
datestring = convert_est_to_utc(args['ftime_to']).isoformat()
params['ftime_to'] = datestring
filters += " AND ft <= :ftime_to"
if args.get('state'):
params['state'] = args['state']
filters += " AND state = :state"
if args.get('order_by'):
args['order_by'].replace('pcomplete',
'(coalesce(ct, 0)/task.n_answers)')
if args.get('filter_by_field'):
filter_query, filter_params = _get_task_info_filters(
args['filter_by_field'])
filters += filter_query
params.update(**filter_params)
if args.get('filter_by_upref'):
user_pref = args['filter_by_upref']
if user_pref['languages'] or user_pref['locations']:
user_pref_db_clause = get_user_pref_db_clause(user_pref)
filters += " AND ( {} )".format(user_pref_db_clause)
return filters, params
def get_task_filters(args):
"""
build the WHERE part of the query using the filter parameters
return the part of the WHERE clause and the dictionary of bound parameters
"""
filters = ''
params = {}
args = args or {}
if args.get('task_id'):
params['task_id'] = args['task_id']
filters += ' AND task.id = :task_id'
if args.get('hide_completed') and args.get('hide_completed') is True:
filters += " AND task.state='ongoing'"
if args.get('pcomplete_from') is not None:
params['pcomplete_from'] = args['pcomplete_from']
filters += " AND (coalesce(ct, 0)/task.n_answers) >= :pcomplete_from"
if args.get('pcomplete_to') is not None:
params['pcomplete_to'] = args['pcomplete_to']
filters += " AND LEAST(coalesce(ct, 0)/task.n_answers, 1.0) <= :pcomplete_to"
if args.get('priority_from') is not None:
params['priority_from'] = args['priority_from']
filters += " AND priority_0 >= :priority_from"
if args.get('priority_to') is not None:
params['priority_to'] = args['priority_to']
filters += " AND priority_0 <= :priority_to"
if args.get('created_from'):
datestring = convert_est_to_utc(args['created_from']).isoformat()
params['created_from'] = datestring
filters += " AND task.created >= :created_from"
if args.get('created_to'):
datestring = convert_est_to_utc(args['created_to']).isoformat()
params['created_to'] = datestring
filters += " AND task.created <= :created_to"
if args.get('ftime_from'):
datestring = convert_est_to_utc(args['ftime_from']).isoformat()
params['ftime_from'] = datestring
filters += " AND ft >= :ftime_from"
if args.get('ftime_to'):
datestring = convert_est_to_utc(args['ftime_to']).isoformat()
params['ftime_to'] = datestring
filters += " AND ft <= :ftime_to"
if args.get('state'):
params['state'] = args['state']
filters += " AND state = :state"
if 'gold_task' in args:
params['calibration'] = args['gold_task']
filters += " AND task.calibration = :calibration"
if args.get('order_by'):
args["order_by"] = args['order_by'].replace(
'lock_status', '(coalesce(ct, 0)/task.n_answers)')
if args.get('filter_by_field'):
filter_query, filter_params = _get_task_info_filters(
args['filter_by_field'])
filters += filter_query
params.update(**filter_params)
if args.get('filter_by_upref'):
user_pref = args['filter_by_upref']
if user_pref['languages'] or user_pref['locations']:
user_pref_db_clause = get_user_pref_db_clause(user_pref)
filters += " AND ( {} )".format(user_pref_db_clause)
# for task queue
if args.get("filter_by_wfilter_upref"):
# task queue exclude completed tasks
filters += " AND state!='completed'"
# exclude tasks that the current worker has worked on before
filters += ''' AND NOT EXISTS
(SELECT 1 FROM task_run WHERE project_id=:project_id AND
user_id=:user_id AND task_id=task.id)'''
params["user_id"] = args.get('user_id')
# include additional filters
user_pref = args["filter_by_wfilter_upref"]["current_user_pref"]
user_email = args["filter_by_wfilter_upref"]["current_user_email"]
user_pref_db_clause = get_user_pref_db_clause(user_pref, user_email)
filters += " AND ( {} )".format(user_pref_db_clause)
params["assign_user"] = args["sql_params"]["assign_user"]
user_profile = args["filter_by_wfilter_upref"]["current_user_profile"]
user_filter_db_clause = get_user_filter_db_clause(user_profile)
filters += " AND ( {} )".format(user_filter_db_clause)
return filters, params
def get_user_preferences(user_id):
assert user_id is not None or user_id > 0
user_pref = User.query.get(user_id).user_pref or {}
return get_user_pref_db_clause(user_pref)
def get_user_preferences(user_id):
user = get_user_by_id(user_id)
user_pref = user.user_pref or {} if user else {}
user_email = user.email_addr if user else None
return get_user_pref_db_clause(user_pref, user_email)
