예제 #1
0
파일: email.php 프로젝트: bgreiner/orsee
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>&nbsp;&nbsp;&nbsp;</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 .= '&nbsp;&nbsp;&nbsp;';
        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) . ' &lt;' . $email['from_address'] . '&gt;';
        } elseif ($email['message_type'] == 'note') {
            echo experiment__list_experimenters($email['admin_id'], false, true);
        } else {
            if ($email['from_name']) {
                echo $email['from_name'] . ' &lt;' . $email['from_address'] . '&gt;';
            } 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>';
}
예제 #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;
}