Example #1
0
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;
}
Example #2
0
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;
}