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; }