function query__strip_ws_subqueries_recursively($subqueries)
{
foreach ($subqueries as $k => $subquery) {
$subqueries[$k]['clause']['query'] = trim(preg_replace('/\\s+/', ' ', $subqueries[$k]['clause']['query']));
if (isset($subqueries[$k]['subqueries'])) {
$subqueries[$k]['subqueries'] = query__strip_ws_subqueries_recursively($subqueries[$k]['subqueries']);
}
}
return $subqueries;
}
function query__get_query_array($posted_array, $experiment_id = "")
{
global $lang;
$formfields = participantform__load();
$participated_clause = expregister__get_pstatus_query_snippet("participated");
$allowed_signs = array('<=', '=', '>');
$query_array = array();
$query_array['clauses'] = array();
foreach ($posted_array as $num => $entry) {
$temp_keys = array_keys($entry);
$module_string = $temp_keys[0];
$module_string_array = explode("_", $module_string);
$module = $module_string_array[0];
$type = $module_string_array[1];
if ($module == 'pform') {
unset($module_string_array[0]);
unset($module_string_array[1]);
if ($module_string_array[2] == 'ms') {
unset($module_string_array[2]);
}
$pform_formfield = implode("_", $module_string_array);
} else {
$pform_formfield = "";
}
$params = $entry[$module_string];
$op = '';
$ctype = '';
$pars = array();
$clause = '';
$subqueries = array();
$add = true;
if (isset($params['logical_op']) && $params['logical_op']) {
$op = strtoupper($params['logical_op']);
}
switch ($module) {
case "bracket":
if ($type == 'open') {
$ctype = 'bracket_open';
$clause = '(';
} else {
$ctype = 'bracket_close';
$clause = ')';
}
break;
case "experimentclasses":
$ctype = 'subquery';
// clause
$clause = 'participant_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= 'IN (#subquery0#) ';
$pars = array();
// subquery
$subqueries[0]['clause']['query'] = "SELECT participant_id as id\n\t\t\t\t\t\t\tFROM " . table('participate_at') . "\n\t\t\t\t\t\t\tWHERE experiment_id IN (#subquery0#) \n\t\t\t\t\t\t\tAND " . $participated_clause;
$subqueries[0]['clause']['pars'] = array();
$likelist = query__make_like_list($params['ms_classes'], 'experiment_class');
$subqueries[0]['subqueries'][0]['clause']['query'] = "\n\t\t\t\t\t\tSELECT experiment_id as id \n\t\t\t\t\t\tFROM " . table('experiments') . "\n\t\t\t\t\t\tWHERE (" . $likelist['par_names'] . ") ";
$subqueries[0]['subqueries'][0]['clause']['pars'] = $likelist['pars'];
break;
case "experimenters":
$ctype = 'subquery';
// clause
$clause = 'participant_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= 'IN (#subquery0#) ';
$pars = array();
// subquery
$subqueries[0]['clause']['query'] = "SELECT participant_id as id \n\t\t\t\t\t\t\tFROM " . table('participate_at') . "\n\t\t\t\t\t\t\tWHERE experiment_id IN (#subquery0#) \n\t\t\t\t\t\t\tAND " . $participated_clause;
$subqueries[0]['clause']['pars'] = array();
$likelist = query__make_like_list($params['ms_experimenters'], 'experimenter');
$subqueries[0]['subqueries'][0]['clause']['query'] = "\n\t\t\t\t\t\tSELECT experiment_id as id\n\t\t\t\t\t\tFROM " . table('experiments') . "\n\t\t\t\t\t\tWHERE (" . $likelist['par_names'] . ") ";
$subqueries[0]['subqueries'][0]['clause']['pars'] = $likelist['pars'];
break;
case "experimentsassigned":
$ctype = 'subquery';
// clause
$clause = 'participant_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= 'IN (#subquery0#) ';
$pars = array();
// subquery
$list = query__make_enquoted_list($params['ms_experiments'], 'experiment_id');
$subqueries[0]['clause']['query'] = "SELECT participant_id as id\n\t\t\t\t\t\t\tFROM " . table('participate_at') . "\n\t\t\t\t\t\t\tWHERE experiment_id IN (" . $list['par_names'] . ")";
$subqueries[0]['clause']['pars'] = $list['pars'];
break;
case "experimentsparticipated":
$ctype = 'subquery';
// clause
$clause = 'participant_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= 'IN (#subquery0#) ';
$pars = array();
// subquery
$list = query__make_enquoted_list($params['ms_experiments'], 'experiment_id');
$subqueries[0]['clause']['query'] = "SELECT participant_id as id \n\t\t\t\t\t\t\tFROM " . table('participate_at') . "\n\t\t\t\t\t\t\tWHERE experiment_id IN (" . $list['par_names'] . ")\n\t\t\t\t\t\t\tAND " . $participated_clause;
$subqueries[0]['clause']['pars'] = $list['pars'];
break;
case "statusids":
$ctype = 'part';
$list = query__make_enquoted_list($params['ms_status'], 'status_id');
$clause = 'status_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= "IN (" . $list['par_names'] . ")";
$pars = $list['pars'];
break;
case "pformtextfields":
$ctype = 'part';
$clause = "";
if ($params['not']) {
$clause .= 'NOT ';
}
$form_query_fields = array();
foreach ($formfields as $f) {
// whitelist by loop
if (preg_match("/(textline|textarea)/i", $f['type']) && (!$experiment_id && $f['search_include_in_participant_query'] == 'y' || $experiment_id && $f['search_include_in_experiment_assign_query'] == 'y')) {
if ($params['search_field'] == 'all') {
$form_query_fields[] = $f['mysql_column_name'];
} elseif ($params['search_field'] == $f['mysql_column_name']) {
$form_query_fields[] = $f['mysql_column_name'];
}
}
}
$like_array = array();
$pars = array();
$i = 0;
foreach ($form_query_fields as $field) {
$like_array[] = $field . " LIKE :search_string" . $i . " ";
$pars[':search_string' . $i] = '%' . $params['search_string'] . '%';
$i++;
}
$clause .= ' (' . implode(" OR ", $like_array) . ') ';
break;
case "pform":
$ctype = 'part';
$clause = "";
$f = array();
foreach ($formfields as $p) {
if ($p['mysql_column_name'] == $pform_formfield) {
$f = $p;
}
}
if (isset($f['mysql_column_name'])) {
$clause .= $f['mysql_column_name'] . ' ';
if ($type == 'numberselect') {
if (in_array($params['sign'], $allowed_signs)) {
$clause .= $params['sign'];
} else {
$clause .= $allowed_signs[0];
}
$clause .= ' :number';
$pars = array(':number' => $params['fieldvalue']);
} elseif ($type == 'simpleselect') {
if ($params['not']) {
$clause .= "!= ";
} else {
$clause .= "= ";
}
$clause .= " :fieldvalue";
$pars = array(':fieldvalue' => trim($params['fieldvalue']));
} else {
if ($params['not']) {
$clause .= "NOT ";
}
$list = query__make_enquoted_list($params['ms_' . $pform_formfield], 'fieldvalue');
$clause .= "IN (" . $list['par_names'] . ")";
$pars = $list['pars'];
}
} else {
$add = false;
}
break;
case "noshows":
$ctype = 'part';
if ($params['count'] == 0) {
$params['count'] = 0;
}
$clause = 'number_noshowup ';
if (in_array($params['sign'], $allowed_signs)) {
$clause .= $params['sign'];
} else {
$clause .= $allowed_signs[0];
}
$clause .= ' :noshowcount';
$pars = array(':noshowcount' => $params['count']);
break;
case "participations":
$ctype = 'part';
if ($params['count'] == 0) {
$params['count'] = 0;
}
$clause = 'number_reg ';
if (in_array($params['sign'], $allowed_signs)) {
$clause .= $params['sign'];
} else {
$clause .= $allowed_signs[0];
}
$clause .= ' :partcount';
$pars = array(':partcount' => $params['count']);
break;
case "updaterequest":
$ctype = 'part';
if ($params['update_request_status'] == 'y') {
$params['update_request_status'] = 'y';
} else {
$params['update_request_status'] = 'n';
}
$clause = 'pending_profile_update_request = :pending_profile_update_request';
$pars = array(':pending_profile_update_request' => $params['update_request_status']);
break;
case "randsubset":
$add = false;
if ($params['limit'] == 0) {
$params['limit'] = 0;
}
$query_array['limit'] = $params['limit'];
break;
case "activity":
$activities = array('last_activity', 'last_enrolment', 'last_profile_update', 'creation_time', 'deletion_time');
if (in_array($params['activity_type'], $activities)) {
$ctype = 'part';
$clause = "";
if ($params['not']) {
$clause .= 'NOT ';
}
$sesstime_act = ortime__array_to_sesstime($params, 'dt_activity_');
$pars = array(':activity_time' => ortime__sesstime_to_unixtime($sesstime_act));
$clause .= ' (' . $params['activity_type'] . ' < :activity_time) ';
} else {
$add = false;
}
break;
case "subsubjectpool":
$ctype = 'part';
$list = query__make_enquoted_list($params['ms_subpool'], 'subpool_id');
$clause = 'subpool_id ';
if ($params['not']) {
$clause .= 'NOT ';
}
$clause .= "IN (" . $list['par_names'] . ")";
$pars = $list['pars'];
break;
default:
$add = false;
break;
}
if ($add) {
$query_array['clauses'][] = array('op' => $op, 'ctype' => $ctype, 'clause' => array('query' => $clause, 'pars' => $pars), 'subqueries' => $subqueries);
}
}
// remove unnecessary whitespace from any queries
foreach ($query_array['clauses'] as $k => $q) {
$query_array['clauses'][$k]['clause']['query'] = trim(preg_replace('/\\s+/', ' ', $query_array['clauses'][$k]['clause']['query']));
if (isset($query_array['clauses'][$k]['subqueries'])) {
$query_array['clauses'][$k]['subqueries'] = query__strip_ws_subqueries_recursively($query_array['clauses'][$k]['subqueries']);
}
}
// unset empty brackets, recursively if needed
$ok = false;
while (!$ok) {
$ok = true;
foreach ($query_array['clauses'] as $k => $q) {
if ($ok && $q['ctype'] == 'bracket_close' && $query_array['clauses'][$k - 1]['ctype'] == 'bracket_open') {
unset($query_array['clauses'][$k]);
unset($query_array['clauses'][$k - 1]);
$ok = false;
if (isset($query_array['clauses'][$k - 2]) && $query_array['clauses'][$k - 2]['ctype'] == 'bracket_open' && isset($query_array['clauses'][$k + 1]['op'])) {
$query_array['clauses'][$k + 1]['op'] = '';
}
}
}
$new_clauses = array();
foreach ($query_array['clauses'] as $k => $q) {
$new_clauses[] = $q;
}
$query_array['clauses'] = $new_clauses;
}
return $query_array;
}