function cron__update_participants_history()
{
$logm = "";
// initialize with zero
$query = "UPDATE " . table('participants') . " \n \t\tSET number_reg = 0, number_noshowup = 0";
$done = or_query($query);
$query = "SELECT " . table('participate_at') . ".participant_id, \n\t\t\tcount(*) as number_reg\n \t\tFROM " . table('participate_at') . ", " . table('sessions') . ", " . table('experiments') . " \n \t\tWHERE " . table('sessions') . ".session_id = " . table('participate_at') . ".session_id\n \t\tAND " . table('participate_at') . ".experiment_id = " . table('experiments') . ".experiment_id\n \t\tAND hide_in_stats = 'n' \n \t\tAND (session_status='completed' OR session_status='balanced')\n \t\tAND " . table('participate_at') . ".session_id != 0 \n \t\tGROUP BY participant_id";
$result = or_query($query);
$n = 0;
while ($line = pdo_fetch_assoc($result)) {
$done = cron__participants_update_history_participant($line, 'number_reg');
$n++;
}
$logm .= "updated participant's number_reg: " . $n . "\n";
$noshow_clause = expregister__get_pstatus_query_snippet("noshow");
$query = "SELECT " . table('participate_at') . ".participant_id,\n count(*) as number_noshowup\n\t\t\tFROM " . table('participate_at') . ", " . table('sessions') . ", " . table('experiments') . "\n\t\t\tWHERE " . table('sessions') . ".session_id = " . table('participate_at') . ".session_id\n\t\t\tAND " . table('participate_at') . ".experiment_id = " . table('experiments') . ".experiment_id\n\t\t\tAND hide_in_stats = 'n'\n\t\t\tAND (session_status='completed' OR session_status='balanced')\n\t\t\tAND " . table('participate_at') . ".session_id != 0 \n\t\t\tAND " . $noshow_clause . " \n\t\t\tGROUP BY participant_id";
$result = or_query($query);
$n = 0;
while ($line = pdo_fetch_assoc($result)) {
$done = cron__participants_update_history_participant($line, 'number_noshowup');
$n++;
}
$logm .= "updated participant's number_noshowup: " . $n;
return $logm;
}
function stats__get_data($condition = array(), $type = 'stats', $restrict = array(), $options = array())
{
global $settings;
$conditions = array();
$pars = array();
if (is_array($condition) && count($condition) > 0) {
$conditions[] = $condition['clause'];
if (isset($condition['pars']) && is_array($condition['pars'])) {
foreach ($condition['pars'] as $k => $v) {
$pars[$k] = $v;
}
}
}
$formfields = participantform__load();
$pform_fields = array();
$pform_types = array();
foreach ($formfields as $f) {
if (!preg_match("/(textline|textarea)/i", $f['type']) && isset($f['include_in_statistics']) && ($f['include_in_statistics'] == 'pie' || $f['include_in_statistics'] == 'bars')) {
$pform_fields[] = $f['mysql_column_name'];
$pform_types[$f['mysql_column_name']] = $f;
}
}
$statfields = array('subpool_id', 'subscriptions', 'status_id');
foreach ($pform_fields as $field) {
if (!in_array($field, $statfields)) {
$statfields[] = $field;
}
}
if ($type == 'stats' && $settings['enable_rules_signed_tracking'] == 'y') {
if (!in_array('rules_signed', $statfields)) {
$statfields[] = 'rules_signed';
}
} else {
$exists = array_search('rules_signed', $statfields);
if ($exists) {
unset($statfields[$exists]);
}
}
if ($type == 'stats') {
foreach (array('number_reg', 'number_noshowup', 'last_enrolment', 'last_profile_update', 'last_activity') as $field) {
if (!in_array($field, $statfields)) {
$statfields[] = $field;
}
}
}
$query = "SELECT * from " . table('participants');
$query_conditions = "";
if (count($conditions) > 0) {
$query_conditions .= " WHERE";
$cond_strings = array();
foreach ($conditions as $cond) {
$cond_strings[] = "(" . $cond . ")";
}
$query_conditions .= " " . implode(" AND ", $cond_strings);
}
$query .= $query_conditions;
$result = or_query($query, $pars);
$counts = array();
$pids = array();
while ($line = pdo_fetch_assoc($result)) {
// check whether we should count this participant and (for monthly stats) use this value
$p_restrict = false;
foreach ($statfields as $c) {
$value = $line[$c];
if (in_array($c, array('last_enrolment', 'last_profile_update', 'last_activity'))) {
if (!$value) {
$value = 0;
}
if ($value == 0) {
$value = '-';
} else {
$value = date('Ym', $value);
}
}
$value = db_string_to_id_array($value);
if (count($value) > 0) {
$this_restrict = true;
foreach ($value as $v) {
if (!isset($restrict[$c][$v])) {
$this_restrict = false;
}
}
if ($this_restrict) {
$p_restrict = true;
}
} else {
$value = '-';
if (isset($restrict[$c][$value])) {
$p_restrict = true;
}
}
}
if (!$p_restrict) {
$pids[] = $line['participant_id'];
foreach ($statfields as $c) {
$value = $line[$c];
$continue = true;
if (in_array($c, array('last_enrolment', 'last_profile_update', 'last_activity'))) {
if (!$value) {
$value = 0;
}
if (date('Ym', $value) < date('Ym', strtotime("-" . $settings['stats_months_backward'] . " month", time()))) {
$continue = false;
}
if ($value == 0) {
$value = '-';
} else {
$value = date('Ym', $value);
}
}
if ($continue) {
$value = db_string_to_id_array($value);
if (count($value) > 0) {
foreach ($value as $v) {
if (!isset($counts[$c][$v])) {
$counts[$c][$v] = 0;
}
$counts[$c][$v]++;
}
} else {
$value = '-';
if (!isset($counts[$c][$value])) {
$counts[$c][$value] = 0;
}
$counts[$c][$value]++;
}
}
}
}
}
$count_pids = count($pids);
$pid_condition = "";
if (count($restrict) > 0) {
$pid_condition = " AND participant_id IN (" . implode(", ", $pids) . ") ";
} elseif (count($conditions) == 1 && isset($options['condition_only_on_pid']) && $options['condition_only_on_pid']) {
$pid_condition = " AND " . $condition['clause'] . " ";
} elseif ($query_conditions) {
$pid_condition = " AND participant_id IN (SELECT participant_id FROM " . table('participants') . " " . $query_conditions . ") ";
}
// avg. experience (participation in experiments of class x, y, z at time of experiment)
$statfields[] = 'experience_avg_experimentclass';
if ($count_pids > 0) {
$participated_clause = expregister__get_pstatus_query_snippet("participated");
$query = "SELECT count(*) as num, experiment_class\n FROM " . table('participate_at') . " as p, " . table('experiments') . " as e\n WHERE p.session_id>0 " . $pid_condition . "\n AND e.experiment_id=p.experiment_id\n AND e.experiment_class !=''\n AND " . $participated_clause . " ";
if (is_array($options) && isset($options['upper_experience_limit'])) {
$query .= " AND session_id IN (SELECT session_id FROM " . table('sessions') . "\n WHERE session_start < " . ortime__unixtime_to_sesstime($options['upper_experience_limit']) . "\n AND session_status IN ('completed','balanced') ) ";
}
$query .= "GROUP BY experiment_class";
$result = or_query($query, $pars);
while ($line = pdo_fetch_assoc($result)) {
$line['experiment_class'] = db_string_to_id_array($line['experiment_class']);
foreach ($line['experiment_class'] as $v) {
if ($v > 0) {
if (!isset($counts['experience_avg_experimentclass'][$v])) {
$counts['experience_avg_experimentclass'][$v] = 0;
}
$counts['experience_avg_experimentclass'][$v] += round($line['num'] / $count_pids, 2);
}
}
}
}
if ($type == 'report') {
// avg. count pstatus at time of experiment)
$statfields[] = 'experience_avg_pstatus';
}
if ($count_pids > 0 && $type == 'report') {
$query = "SELECT count(*) as num, pstatus_id\n FROM " . table('participate_at') . "\n WHERE session_id>0 " . $pid_condition;
if (is_array($options) && isset($options['upper_experience_limit'])) {
$query .= " AND session_id IN (SELECT session_id FROM " . table('sessions') . "\n WHERE session_start < " . ortime__unixtime_to_sesstime($options['upper_experience_limit']) . "\n AND session_status IN ('completed','balanced') ) ";
}
$query .= "GROUP BY pstatus_id";
$result = or_query($query, $pars);
while ($line = pdo_fetch_assoc($result)) {
$counts['experience_avg_pstatus'][$line['pstatus_id']] = round($line['num'] / $count_pids, 2);
}
}
if ($type == 'stats') {
// by pstatus: pstatus count // really needed? we have no_noshows, num_reg ...
// by month: pstatus
$statfields[] = 'bymonth_pstatus';
$statfields[] = 'bymonth_noshowrate';
}
if ($count_pids > 0 && $type == 'stats') {
$first_month = date('Ym000000', strtotime("-" . $settings['stats_months_backward'] . " month", time()));
$noshow_statuses = expregister__get_specific_pstatuses("noshow");
$query = "SELECT date_format(s.session_start*100,'%Y%m') as sessionmonth,\n pstatus_id, count(p.participate_id) as num\n FROM " . table('participate_at') . " as p, " . table('sessions') . " as s\n WHERE p.session_id>0\n AND p.session_id=s.session_id\n AND s.session_status IN ('completed','balanced')\n AND s.session_start>" . $first_month . " " . $pid_condition . "\n GROUP BY sessionmonth, pstatus_id\n ORDER BY sessionmonth, pstatus_id ";
$result = or_query($query, $pars);
$noshowperc_data = array();
while ($line = pdo_fetch_assoc($result)) {
$counts['bymonth_pstatus_' . $line['pstatus_id']][$line['sessionmonth']] = $line['num'];
if (!isset($noshowperc_data[$line['sessionmonth']]['n'])) {
$noshowperc_data[$line['sessionmonth']]['n'] = 0;
}
if (!isset($noshowperc_data[$line['sessionmonth']]['y'])) {
$noshowperc_data[$line['sessionmonth']]['y'] = 0;
}
if (in_array($line['pstatus_id'], $noshow_statuses)) {
$noshowperc_data[$line['sessionmonth']]['n'] += $line['num'];
} else {
$noshowperc_data[$line['sessionmonth']]['y'] += $line['num'];
}
}
// get the noshow percentages as well
foreach ($noshowperc_data as $month => $shownup) {
$counts['bymonth_noshowrate'][$month] = round($shownup['n'] / ($shownup['n'] + $shownup['y']) * 100, 1);
}
}
$xnames = array('number_reg' => lang('experience'), 'number_noshowup' => lang('noshowup'), 'pstatus' => lang('month'), 'experience_avg_experimentclass' => lang('experiment_classes'), 'experience_avg_pstatus' => lang('participation_status'));
$titles = array('number_reg' => lang('experience'), 'number_noshowup' => lang('noshows_by_count'), 'last_enrolment' => lang('last_enrolment'), 'last_profile_update' => lang('last_profile_update'), 'last_activity' => lang('last_activity'), 'bymonth_noshowrate' => lang('noshows_by_month'), 'experience_avg_experimentclass' => lang('experience_in_experiment_classes'), 'experience_avg_pstatus' => lang('average_participation_experience'));
// prepare all-containing arrray to return
$data_temparray = array();
foreach ($counts as $c => $nums) {
$d = array();
$d['N'] = $count_pids;
if (isset($pform_types[$c])) {
$d['browsable'] = true;
$d['charttype'] = $pform_types[$c]['include_in_statistics'];
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang($pform_types[$c]['name_lang']);
$d['title'] = lang($pform_types[$c]['name_lang']);
if (preg_match("/(select_lang|radioline_lang)/", $pform_types[$c]['type'])) {
$d['value_names'] = lang__load_lang_cat($c, lang('lang'));
} elseif (preg_match("/(radioline|select_list)/", $pform_types[$c]['type'])) {
$optionvalues = explode(",", $pform_types[$c]['option_values']);
$optionnames = explode(",", $pform_types[$c]['option_values_lang']);
$d['value_names'] = array();
foreach ($optionvalues as $k => $v) {
if (isset($optionnames[$k])) {
$d['value_names'][$v] = lang($optionnames[$k]);
}
}
} else {
$d['value_names'] = array();
}
// select numbers?
if ($pform_types[$c]['type'] == 'select_numbers') {
krsort($nums);
} else {
arsort($nums);
}
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif (in_array($c, array('number_reg', 'number_noshowup'))) {
$d['browsable'] = true;
$d['charttype'] = 'bars';
if ($c == 'number_reg') {
$d['wide'] = true;
}
$d['limit_not_apply'] = true;
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = $xnames[$c];
$d['title'] = $titles[$c];
$d['value_names'] = array();
krsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif (in_array($c, array('experience_avg_experimentclass', 'experience_avg_pstatus'))) {
$d['charttype'] = 'pie';
$d['type_of_data'] = 'calc';
$d['yname'] = lang('average_count');
$d['xname'] = $xnames[$c];
$d['title'] = $titles[$c];
$d['value_names'] = array();
if ($c == 'experience_avg_experimentclass') {
$d['value_names'] = experiment__load_experimentclassnames();
} else {
$pstatuses = expregister__get_participation_statuses();
foreach ($pstatuses as $k => $s) {
$d['value_names'][$k] = $s['internal_name'];
}
}
arsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif ($c == 'rules_signed') {
$d['browsable'] = true;
$d['charttype'] = 'pie';
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang('rules_signed');
$d['title'] = lang('rules_signed');
$d['value_names'] = array('n' => lang('n'), 'y' => lang('y'));
arsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif ($c == 'subpool_id') {
$d['browsable'] = true;
$d['charttype'] = 'pie';
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang('subpool');
$d['title'] = lang('subpool');
$subpools = subpools__get_subpools();
$d['value_names'] = array();
foreach ($subpools as $k => $p) {
$d['value_names'][$k] = $p['subpool_name'];
}
arsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif ($c == 'status_id') {
$d['browsable'] = true;
$d['charttype'] = 'pie';
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang('participant_status');
$d['title'] = lang('participant_statuses');
$statuses = participant_status__get_statuses();
$d['value_names'] = array();
foreach ($statuses as $k => $p) {
$d['value_names'][$k] = $p['name'];
}
krsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif ($c == 'subscriptions') {
$d['browsable'] = true;
$d['charttype'] = 'none';
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang('subscriptions');
$d['title'] = lang('subscriptions');
$exptypes = load_external_experiment_types("", false);
$d['value_names'] = array();
foreach ($exptypes as $k => $p) {
$d['value_names'][$k] = $p[lang('lang')];
}
krsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif (in_array($c, array('last_enrolment', 'last_profile_update', 'last_activity', 'bymonth_noshowrate'))) {
$d['charttype'] = 'bars';
$d['wide'] = true;
if ($c == 'bymonth_noshowrate') {
$d['yname'] = lang('share_in_percent');
$d['type_of_data'] = 'calc';
} else {
$d['yname'] = lang('count');
$d['type_of_data'] = 'count';
}
$d['xname'] = lang('month');
$d['title'] = $titles[$c];
foreach ($nums as $k => $number) {
if ($k == '-') {
$d['value_names'][$k] = $k;
} else {
$d['value_names'][$k] = substr($k, 4, 2) . '/' . substr($k, 2, 2);
}
}
krsort($nums);
$d['data'] = $nums;
$data_temparray[$c] = $d;
} elseif (preg_match('/^bymonth_pstatus/', $c)) {
$tarr = explode("_", $c);
$status = $tarr[2];
if (!isset($data_temparray['bymonth_pstatus'])) {
$pstatuses = expregister__get_participation_statuses();
$d['charttype'] = 'multibars';
$d['wide'] = true;
$d['type_of_data'] = 'count';
$d['yname'] = lang('count');
$d['xname'] = lang('month');
$d['title'] = lang('participation_status_count_by_month');
$d['value_names'] = array();
$d['column_names'] = array();
foreach ($pstatuses as $k => $s) {
$d['column_names'][$k] = $s['internal_name'];
}
$data_temparray['bymonth_pstatus'] = $d;
}
krsort($nums);
$data_temparray['bymonth_pstatus']['data'][$status] = $nums;
$d['value_names'] = array();
foreach ($data_temparray['bymonth_pstatus']['data'] as $status => $months) {
foreach ($months as $month => $count) {
$d['value_names'][$month] = $month;
}
}
krsort($d['value_names']);
foreach ($d['value_names'] as $k => $v) {
$d['value_names'][$k] = substr($v, 4, 2) . '/' . substr($v, 2, 2);
}
$data_temparray['bymonth_pstatus']['value_names'] = $d['value_names'];
}
}
// prepare all-containing array to return
$data_array = array();
foreach ($statfields as $c) {
if (isset($data_temparray[$c]['data'])) {
$data_temparray[$c]['name'] = $c;
$data_array[$c] = $data_temparray[$c];
} else {
$data_array[$c] = array('name' => $c, 'N' => 0, 'data' => array());
}
}
return $data_array;
}
$query = "SELECT max(session_start) as max_time, min(session_start) as min_time\n\t\t\tFROM " . table('sessions') . " \n\t\t\tWHERE experiment_id = :experiment_id";
$line = orsee_query($query, $pars);
$min_session_time = ortime__sesstime_to_unixtime($line['min_time']);
$max_session_time = ortime__sesstime_to_unixtime($line['max_time']);
$total_data = array();
// pool
$options = array('upper_experience_limit' => $min_session_time);
$condition = array('clause' => " status_id > 0 AND creation_time < '" . $max_session_time . "' AND \n\t\t\t\t (deletion_time=0 OR deletion_time > '" . $min_session_time . "') ", 'pars' => array());
$total_data['pool'] = stats__get_data($condition, 'report', array(), $options);
// experiment eligible
$options = array('upper_experience_limit' => $min_session_time, 'condition_only_on_pid' => true);
$condition = array('clause' => "participant_id IN (SELECT participant_id FROM " . table('participate_at') . " \n\t\t\t\t\t\t\t\tWHERE experiment_id= :experiment_id )", 'pars' => array(':experiment_id' => $experiment_id));
$total_data['exp'] = stats__get_data($condition, 'report', array(), $options);
// participated
$options = array('upper_experience_limit' => $min_session_time, 'condition_only_on_pid' => true);
$participated_clause = expregister__get_pstatus_query_snippet("participated");
$condition = array('clause' => "participant_id IN (SELECT participant_id FROM " . table('participate_at') . " \n\t\t\t\t\t\t\t\tWHERE experiment_id= :experiment_id AND session_id > 0 AND " . $participated_clause . ")", 'pars' => array(':experiment_id' => $experiment_id));
$total_data['part'] = stats__get_data($condition, 'report', array(), $options);
echo '<TABLE class="or_orr_section_content">';
$i = 0;
$cols = 2;
$out = array();
foreach ($total_data['pool'] as $k => $table1) {
if (isset($table1['data']) && is_array($table1['data']) && count($table1['data']) > 0) {
$show = true;
} else {
$show = false;
}
if ($show) {
$out[] = stats__report_display_table($table1, lang('stats_report__pool'), $total_data['exp'][$k], lang('stats_report__assigned'), $total_data['part'][$k], lang('stats_report__participated'));
if (count($out) == $cols) {
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;
}
function experimentmail__send_noshow_warnings_to_queue($session)
{
$noshow_clause = expregister__get_pstatus_query_snippet("noshow");
$pars = array(':experiment_id' => $session['experiment_id'], ':session_id' => $session['session_id']);
$query = "INSERT INTO " . table('mail_queue') . " (timestamp,mail_type,mail_recipient,experiment_id,session_id)\n SELECT UNIX_TIMESTAMP(),'noshow_warning', participant_id, experiment_id, session_id\n FROM " . table('participate_at') . "\n WHERE experiment_id= :experiment_id\n AND session_id= :session_id\n AND " . $noshow_clause;
$done = or_query($query, $pars);
$count = pdo_num_rows($done);
return $count;
}
function experiment__preload_experiments()
{
global $lang, $preloaded_experiments;
if (isset($preloaded_experiments) && is_array($preloaded_experiments) && count($preloaded_experiments) > 0) {
return $preloaded_experiments;
} else {
$experiments = array();
$query = "SELECT experiment_id, experiment_name, experimenter from " . table('experiments');
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']] = array('time' => '', 'start_date' => '', 'end_date' => '', 'assigned' => 'n', 'participated' => 'n');
$experiments[$line['experiment_id']]['experiment_id'] = $line['experiment_id'];
$experiments[$line['experiment_id']]['experiment_name'] = $line['experiment_name'];
$experiments[$line['experiment_id']]['experimenter'] = $line['experimenter'];
}
$query = "SELECT experiment_id, \n\t\t\t\tmin(session_start) as start_date,\n\t\t\t\tmax(session_start) as end_date \n\t\t\t\tFROM " . table('sessions') . " \n\t\t\t\tWHERE session_id>0\n\t\t\t\tGROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['time'] = 1000000000000 - $line['end_date'];
$experiments[$line['experiment_id']]['start_date'] = $line['start_date'];
$experiments[$line['experiment_id']]['end_date'] = $line['end_date'];
}
$query = "SELECT experiment_id FROM " . table('participate_at') . " GROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['assigned'] = 'y';
}
$participated_clause = expregister__get_pstatus_query_snippet("participated");
$query = "SELECT experiment_id FROM " . table('participate_at') . " WHERE " . $participated_clause . " GROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['participated'] = 'y';
}
$sort_order = "time,experiment_name";
multi_array_sort($experiments, $sort_order);
$preloaded_experiments = $experiments;
return $experiments;
}
}