function email__list_emails($mode = 'inbox', $id = '', $rmode = 'assigned', $url_string = '', $show_refresh = true)
{
global $color, $lang, $settings;
if (substr($url_string, 0, 1) == '?') {
$url_string = substr($url_string, 1);
}
$conditions = array();
$pars = array();
if ($mode == 'trash') {
$conditions[] = ' flag_deleted=1 ';
} else {
$conditions[] = ' flag_deleted=0 ';
}
if ($mode == 'inbox') {
$conditions[] = ' flag_processed=0 ';
} elseif ($mode == 'mailbox') {
$conditions[] = ' mailbox=:mailbox ';
$pars[':mailbox'] = $id;
} elseif ($mode == 'experiment') {
$conditions[] = ' experiment_id=:experiment_id ';
$pars[':experiment_id'] = $id;
} elseif ($mode == 'session') {
$conditions[] = ' session_id=:session_id ';
$pars[':session_id'] = $id;
} elseif ($mode == 'participant') {
$conditions[] = ' participant_id=:participant_id ';
$pars[':participant_id'] = $id;
}
if ($rmode == 'assigned') {
global $expadmindata;
$ass_clause = query__get_experimenter_or_clause(array($expadmindata['admin_id']), 'emails', 'assigned_to');
$conditions[] = $ass_clause['clause'];
foreach ($ass_clause['pars'] as $k => $v) {
$pars[$k] = $v;
}
} elseif ($rmode == 'experiments') {
global $expadmindata;
$likelist = query__make_like_list($expadmindata['admin_id'], 'assigned_to');
$conditions[] = " experiment_id IN (SELECT experiment_id as id\n FROM " . table('experiments') . " WHERE (" . $likelist['par_names'] . ") ) ";
foreach ($likelist['pars'] as $k => $v) {
$pars[$k] = $v;
}
}
$query = "SELECT * FROM " . table('emails') . "\n WHERE " . implode(" AND ", $conditions) . "\n ORDER BY thread_time DESC, thread_id, if (thread_id=message_id,0,1), timestamp";
$result = or_query($query, $pars);
$emails = array();
$experiment_ids = array();
$session_ids = array();
while ($email = pdo_fetch_assoc($result)) {
$emails[] = $email;
if ($mode != 'experiment' && $email['experiment_id']) {
$experiment_ids[] = $email['experiment_id'];
}
if ($mode != 'session' && $email['session_id']) {
$session_ids[] = $email['session_id'];
}
}
$mailboxes = email__load_mailboxes();
$shade = false;
$related_experiments = experiment__load_experiments_for_ids($experiment_ids);
$related_sessions = sessions__load_sessions_for_ids($session_ids);
echo '<table style="max-width: 90%;">';
if ($show_refresh) {
echo '
<tr><td align="right">
' . icon('refresh', thisdoc() . '?' . $url_string, 'fa-2x', 'color: green;', 'refresh list'), '
</td></tr>';
}
echo ' <tr><td>
<table class="or_listtable"><thead>
<tr style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">';
echo '<td> </td>';
// is thread head
echo '<td>' . lang('email_subject') . '</td>';
// type: incoming, note, reply && subject
echo '<td>' . lang('email_from') . '</td>';
// from
echo '<td>' . lang('date') . '</td>';
// date
echo '<td></td>';
// read // assigned_to_read
echo '<td></td>';
// processed - check and background of row
echo '<td></td>';
// view email button
echo '</tr>
</thead><tbody>';
$cols = 7;
$shade = false;
$style_unprocessed = ' style="font-weight: bold;"';
foreach ($emails as $email) {
$second_row = '';
if ($email['thread_id'] == $email['message_id']) {
if ($shade) {
$shade = false;
} else {
$shade = true;
}
$second_row = "";
// experiment or mailbox - not if experiment or session or mailbox
if (!in_array($mode, array('experiment', 'session', 'mailbox'))) {
if ($email['experiment_id']) {
if (isset($related_experiments[$email['experiment_id']])) {
$second_row .= $related_experiments[$email['experiment_id']]['experiment_name'];
}
} elseif ($email['mailbox']) {
$second_row .= '<b>' . lang('email_mailbox') . ':</b> ' . $mailboxes[$email['mailbox']];
}
}
// session - not if session or mailbox
if (!in_array($mode, array('session', 'mailbox'))) {
if ($email['session_id']) {
if ($second_row) {
$second_row .= ', ';
}
$second_row .= session__build_name($related_sessions[$email['session_id']]);
}
}
// assigned to
if ($settings['email_module_allow_assign_emails'] == 'y' && $email['assigned_to']) {
if ($second_row) {
$second_row .= ', ';
}
$second_row .= experiment__list_experimenters($email['assigned_to'], false, true);
}
}
echo '<tr';
if ($shade) {
echo ' bgcolor="' . $color['list_shade1'] . '"';
} else {
echo ' bgcolor="' . $color['list_shade2'] . '"';
}
if (!$email['flag_processed'] && $mode != 'inbox') {
echo $style_unprocessed;
}
echo '>';
// thread head and subject
if ($email['message_id'] == $email['thread_id']) {
echo '<TD colspan=2>';
} else {
echo '<TD></TD><TD>';
}
echo '<A name="' . $email['message_id'] . '"></A>';
$linktext = '';
if ($email['message_type'] == 'reply') {
$linktext .= icon('reply', '', '', ' color: #666666;', 'reply');
} elseif ($email['message_type'] == 'note') {
$linktext .= icon('file-text-o', '', '', ' color: #666666;', 'internal note');
} elseif ($email['message_type'] == 'incoming') {
$linktext .= icon('envelope-square', '', '', ' color: #666666;', 'incoming');
}
$linktext .= ' ';
if ($email['message_type'] == 'note') {
$linktext .= lang('email_internal_note');
} else {
$linktext .= $email['subject'];
}
echo $linktext;
if ($email['has_attachments']) {
echo icon('paperclip');
}
echo '</TD>';
// from
echo '<td>';
if ($email['message_type'] == 'reply') {
echo experiment__list_experimenters($email['admin_id'], false, true) . ' <' . $email['from_address'] . '>';
} elseif ($email['message_type'] == 'note') {
echo experiment__list_experimenters($email['admin_id'], false, true);
} else {
if ($email['from_name']) {
echo $email['from_name'] . ' <' . $email['from_address'] . '>';
} else {
echo $email['from_address'];
}
}
if ($email['message_type'] == 'incoming' && $email['participant_id']) {
echo icon('check-circle-o', '', '', ' font-size: 8pt; color: #666666;', 'checked');
}
echo '</td>';
// date
echo '<td>' . ortime__format($email['timestamp']) . '</td>';
if ($email['thread_id'] == $email['message_id']) {
// read // assigned_to_read
echo '<td align=center valign=middle>';
echo '<A HREF="' . thisdoc() . '?' . $url_string . '&switch_read=true&message_id=' . urlencode($email['message_id']) . '">';
if ($email['flag_read']) {
echo icon('circle-o', '', '', ' color: #666666;');
} else {
echo icon('dot-circle-o', '', '', ' color: #008000;');
}
echo '</A>';
if ($settings['email_module_allow_assign_emails'] == 'y' && $email['assigned_to']) {
echo '<A HREF="' . thisdoc() . '?' . $url_string . '&switch_assigned_to_read=true&message_id=' . urlencode($email['message_id']) . '">';
if ($email['flag_assigned_to_read']) {
echo icon('circle-o', '', '', ' color: #666666;');
} else {
echo icon('dot-circle-o', '', '', ' color: #000080;');
}
echo '</A>';
}
echo '</td>';
// processed - check and background of row
echo '<td>';
if ($email['flag_processed']) {
echo icon('check', '', '', ' color: #008000;');
}
echo '</td>';
// view email button
echo '<td valign="top"';
if ($second_row) {
echo ' rowspan="2"';
}
echo '>';
echo javascript__email_popup_button_link($email['message_id']);
echo '</td>';
} else {
echo '<td colspan="3"></td>';
}
echo '</tr>';
if ($second_row) {
echo '<tr';
if ($shade) {
echo ' bgcolor="' . $color['list_shade1'] . '"';
} else {
echo ' bgcolor="' . $color['list_shade2'] . '"';
}
if (!$email['flag_processed'] && $mode != 'inbox') {
echo $style_unprocessed;
}
echo '>';
echo '<TD></TD>';
echo '<TD colspan="' . ($cols - 2) . '">';
echo '<i>' . $second_row . '</i>';
echo '</TD>';
echo '</TR>';
}
}
echo '</tbody></table>
</td></tr>
</table>';
}
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;
}