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