function orsee_session_gc($aMaxLifeTime)
{
site__database_config();
if (!isset($aMaxLifeTime) || !$aMaxLifeTime) {
$aMaxLifeTime = 60 * 60;
}
$pars = array(':aMaxLifeTime' => $aMaxLifeTime);
$query = "DELETE FROM " . table('http_sessions') . " WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(LastUpdated) > :aMaxLifeTime";
or_query($query, $pars);
return true;
}
function subpools__get_subpools()
{
global $preloaded_subpools;
if (is_array($preloaded_subpools) && count($preloaded_subpools) > 0) {
return $preloaded_subpools;
} else {
$subpools = array();
$query = "SELECT *\n \t\t\tFROM " . table('subpools') . " \n \t\t\tORDER BY subpool_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$subpools[$line['subpool_id']] = $line;
}
$preloaded_subpools = $subpools;
return $subpools;
}
}
function laboratories__get_laboratories($tlang = "")
{
if (!$tlang) {
global $lang;
$tlang = lang('lang');
}
$labs = array();
$query = "SELECT * FROM " . table('lang') . " WHERE content_type='laboratory'\n ORDER BY order_number ";
$result = or_query($query);
while ($lab = pdo_fetch_assoc($result)) {
$tlab = array();
$tlab['lab_name'] = laboratories__strip_lab_name(stripslashes($lab[lang('lang')]));
$tlab['lab_address'] = laboratories__strip_lab_address(stripslashes($lab[lang('lang')]));
$labs[$lab['content_name']] = $tlab;
}
return $labs;
}
function options__save_item_order($item_type, $order_array, $details = array())
{
$pars = array(':item_type' => $item_type);
$query = "DELETE FROM " . table('objects') . "\n WHERE item_type= :item_type";
$done = or_query($query, $pars);
$pars = array();
foreach ($order_array as $k => $v) {
if (isset($details[$v]) && is_array($details[$v])) {
$detstr = property_array_to_db_string($details[$v]);
} else {
$detstr = '';
}
$pars[] = array(':item_type' => $item_type, ':item_name' => $v, ':order_number' => $k, ':item_details' => $detstr);
}
$query = "INSERT INTO " . table('objects') . "\n SET order_number = :order_number,\n item_type = :item_type,\n item_name = :item_name,\n item_details = :item_details";
$done = or_query($query, $pars);
return $done;
}
$header = lang('delete_email_mailbox');
$headervar = lang('lang');
$reset_part_field = "";
$deletion_message = lang('email_mailbox_deleted');
break;
}
echo '<center>';
if ($reallydelete) {
$pars = array(':id' => $id);
$query = "DELETE FROM " . table('lang') . "\n WHERE lang_id= :id";
$result = or_query($query, $pars);
// there should be a miore sophisticarted way of doing this
if ($reset_part_field) {
$pars = array(':content_name' => $titem['content_name']);
$query = "UPDATE " . table('participants') . "\n SET " . $reset_part_field . "='0'\n WHERE " . $reset_part_field . "= :content_name";
$result = or_query($query, $pars);
}
message($deletion_message);
log__admin($item . "_delete", "lang_id:" . $titem['content_type'] . ',' . $titem['content_name']);
redirect('admin/lang_item_main.php?item=' . $item);
}
}
if ($proceed) {
// form
echo ' <CENTER>
<TABLE class="or_formtable">
<TR><TD colspan=2>
<TABLE width="100%" border=0 class="or_panel_title"><TR>
<TD style="background: ' . $color['panel_title_background'] . '; color: ' . $color['panel_title_textcolor'] . '" align="center">
' . $header . ' - ' . $titem[$headervar] . '
</TD>
function copy_table($table, $idvar, $cond = "", $delete = true)
{
global $do_delete, $do_insert, $new_db_name, $old_db_name;
$dquery = "DELETE FROM " . $new_db_name . "." . table($table) . "";
if ($do_delete && $delete) {
$done = or_query($dquery);
}
$squery = "SELECT * FROM " . $old_db_name . "." . table($table);
if ($cond) {
$squery .= ' ' . $cond;
}
$result = or_query($squery);
while ($line = pdo_fetch_assoc($result)) {
$line = convert_array_to_UTF8($line);
if ($do_insert) {
$done = orsee_db_save_array($line, $table, $line[$idvar], $idvar);
}
}
}
function lang__load_lang_cat($content_type, $language = "")
{
global $lang, $preloaded_lang_cats;
if (!$language) {
$language = lang('lang');
}
if (isset($preloaded_lang_cats[$content_type][$language]) && is_array($preloaded_lang_cats[$content_type][$language]) && count($preloaded_lang_cats[$content_type][$language]) > 0) {
return $preloaded_lang_cats[$content_type][$language];
} else {
$cat = array();
$pars = array(':content_type' => $content_type);
$query = "SELECT content_name, " . $language . " as content_value\n FROM " . table('lang') . " WHERE content_type= :content_type";
$result = or_query($query, $pars);
while ($line = pdo_fetch_assoc($result)) {
$cat[$line['content_name']] = stripslashes($line['content_value']);
}
$preloaded_lang_cats[$content_type][$language] = $cat;
return $cat;
}
}
function payments__get_default_budget($experiment = array(), $session = array())
{
$continue = true;
if ($continue) {
if (is_array($session) && isset($session['payment_budgets'])) {
$budgets = db_string_to_id_array($session['payment_budgets']);
if (count($budgets) > 0) {
$continue = false;
return $budgets[0];
}
}
}
if ($continue) {
if (is_array($experiment) && isset($experiment['payment_budgets'])) {
$budgets = db_string_to_id_array($experiment['payment_budgets']);
if (count($budgets) > 0) {
$continue = false;
return $budgets[0];
}
}
}
if ($continue) {
$budgets = payments__load_budgets(true);
ksort($budgets);
$first = true;
foreach ($budgets as $k => $budget) {
if ($first) {
return $k;
$first = false;
}
}
}
if ($continue) {
$query = "SELECT * FROM " . table('budgets') . " \n\t\t\t\tORDER BY budget_id\n\t\t\t\tLIMIT 1";
$result = or_query($query);
$line = pdo_fetch_assoc($result);
return $line['budget_id'];
}
}
function admin__select_admin_type($fieldname, $selected = "", $return_var = "type_name", $hide = array())
{
global $settings, $preloaded_admintypes;
$out = '';
if (!isset($preloaded_admintypes) || !is_array($preloaded_admintypes)) {
$preloaded_admintypes = array();
$query = "SELECT * from " . table('admin_types') . "\n\t\t\t\tORDER by type_name";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$preloaded_admintypes[$line['type_name']] = $line;
}
}
if (!isset($preloaded_admintypes[$selected])) {
$selected = $settings['default_admin_type'];
}
$out .= '<SELECT name="' . $fieldname . '">';
foreach ($preloaded_admintypes as $line) {
if (!in_array($line['type_id'], $hide)) {
$out .= '<OPTION value="' . $line[$return_var] . '"';
if ($line[$return_var] == $selected || $line['type_name'] == $selected) {
$out .= ' SELECTED';
}
$out .= '>' . $line['type_name'] . '</OPTION>';
}
}
$out .= '</SELECT>';
return $out;
}
function pdfoutput__make_part_list($experiment_id, $session_id = "", $pstatus = "", $focus = "", $sort = "", $file = false, $tlang = "")
{
global $settings;
if ($tlang == "") {
global $lang;
} else {
$lang = load_language($tlang);
}
$experiment = orsee_db_load_array("experiments", $experiment_id, "experiment_id");
$pstatuses = expregister__get_participation_statuses();
if ($session_id) {
$clause = "session_id = '" . $session_id . "'";
$title = lang('registered_subjects');
} elseif (isset($pstatuses[$pstatus])) {
$clause = "pstatus_id = '" . $pstatus . "'";
if ($pstatus == 0) {
$clause .= " AND session_id != 0";
}
$title = lang('subjects_in_participation_status') . ' "' . $pstatuses[$pstatus]['internal_name'] . '"';
} elseif ($focus == 'enroled') {
$clause = "session_id != 0";
$title = lang('registered_subjects');
}
$cols = participant__get_result_table_columns('session_participants_list_pdf');
if ($session_id) {
unset($cols['session_id']);
}
// load sessions of this experiment
$pars = array(':experiment_id' => $experiment_id);
$query = "SELECT *\n\t\t\tFROM " . table('sessions') . "\n\t\t\tWHERE experiment_id= :experiment_id\n\t\t\tORDER BY session_start";
$result = or_query($query, $pars);
global $thislist_sessions;
$thislist_sessions = array();
while ($line = pdo_fetch_assoc($result)) {
$thislist_sessions[$line['session_id']] = $line;
}
// load participant data for this session/experiment
$pars = array(':experiment_id' => $experiment_id);
$select_query = "SELECT * FROM " . table('participate_at') . ", " . table('participants') . " \n\t\t\t\t\tWHERE " . table('participate_at') . ".experiment_id= :experiment_id\n\t\t\t\t\tAND " . table('participate_at') . ".participant_id=" . table('participants') . ".participant_id\n\t\t\t\t\tAND (" . $clause . ")";
$order = query__get_sort('session_participants_list_pdf', $sort);
if (!$order) {
$order = table('participants') . ".participant_id";
}
$select_query .= " ORDER BY " . $order;
// get result
$result = or_query($select_query, $pars);
$participants = array();
while ($line = pdo_fetch_assoc($result)) {
$participants[] = $line;
}
$result_count = count($participants);
// load sessions of this experiment
$pars = array(':texperiment_id' => $experiment_id);
$squery = "SELECT *\n FROM " . table('sessions') . "\n\t\t\tWHERE experiment_id= :texperiment_id \n ORDER BY session_start";
$result = or_query($squery, $pars);
$thislist_sessions = array();
while ($line = pdo_fetch_assoc($result)) {
$thislist_sessions[$line['session_id']] = $line;
}
// reorder by session date if ordered by session id
if ($sort == "session_id") {
$temp_participants = $participants;
$participants = array();
foreach ($thislist_sessions as $sid => $s) {
foreach ($temp_participants as $p) {
if ($p['session_id'] == $sid) {
$participants[] = $p;
}
}
}
}
unset($temp_participants);
// determine table title
$table_title = $experiment['experiment_public_name'];
if ($session_id) {
$table_title .= ', ' . lang('session') . ' ' . str_replace(" ", " ", session__build_name($thislist_sessions[$session_id]));
}
$table_title .= ' - ' . $title;
// determine table headings
$table_headings = participant__get_result_table_headcells_pdf($cols);
$table_data = array();
$pnr = 0;
foreach ($participants as $p) {
$pnr++;
$p['order_number'] = $pnr;
$row = participant__get_result_table_row_pdf($cols, $p);
$table_data[] = $row;
}
// prepare pdf
include_once '../tagsets/class.ezpdf.php';
$pdf = new Cezpdf('a4', 'landscape');
$pdf->selectFont('../tagsets/fonts/Times-Roman.afm');
$fontsize = $settings['participant_list_pdf_table_fontsize'] ? $settings['participant_list_pdf_table_fontsize'] : 10;
$titlefontsize = $settings['participant_list_pdf_title_fontsize'] ? $settings['participant_list_pdf_title_fontsize'] : 12;
$y = $pdf->ezTable($table_data, $table_headings, $table_title, array('gridlines' => 31, 'showHeadings' => 1, 'shaded' => 2, 'shadeCol' => array(1, 1, 1), 'shadeCol2' => array(0.9, 0.9, 0.9), 'fontSize' => $fontsize, 'titleFontSize' => $titlefontsize, 'rowGap' => 1, 'colGap' => 3, 'innerLineThickness' => 0.5, 'outerLineThickness' => 1, 'maxWidth' => 800, 'width' => 800, 'protectRows' => 2));
if ($file) {
$pdffilecode = $pdf->output();
return $pdffilecode;
} else {
$pdf->ezStream(array('Content-Disposition' => 'participant_list.pdf', 'Accept-Ranges' => 0, 'compress' => 1));
}
}
function downloads__list_experiments($showsize = false, $showtype = false, $showdate = false)
{
global $lang, $color, $expadmindata;
$out = '';
$continue = true;
if (check_allow('file_view_experiment_all')) {
$experimenter_clause = '';
$pars = array();
} elseif (check_allow('file_view_experiment_my')) {
$experimenter_clause = " AND " . table('experiments') . ".experimenter LIKE :experimenter ";
$pars = array(':experimenter' => '%|' . $expadmindata['admin_id'] . '|%');
} else {
$continue = false;
}
if ($continue) {
$query = "SELECT " . table('experiments') . ".*,\n (SELECT min(session_start) from or_sessions as s1 WHERE s1.experiment_id=" . table('experiments') . ".experiment_id) as first_session_date,\n (SELECT max(session_start) from or_sessions as s2 WHERE s2.experiment_id=" . table('experiments') . ".experiment_id) as last_session_date\n FROM " . table('experiments') . "\n WHERE " . table('experiments') . ".experiment_id IN\n (SELECT DISTINCT experiment_id FROM " . table('uploads') . ")\n " . $experimenter_clause . "\n ORDER BY last_session_date DESC";
$result = or_query($query, $pars);
$experiments = array();
while ($line = pdo_fetch_assoc($result)) {
$experiments[] = $line;
}
if (count($experiments) > 0) {
$out .= '<TABLE width=100% border=0>';
$shade = true;
foreach ($experiments as $exp) {
if ($shade) {
$bgcolor = ' bgcolor="' . $color['list_shade1'] . '"';
$shade = false;
} else {
$bgcolor = ' bgcolor="' . $color['list_shade2'] . '"';
$shade = true;
}
$out .= '<TR' . $bgcolor . '><TD>';
$out .= $exp['experiment_name'] . '</TD><TD>(';
$out .= lang('from') . ' ';
if ($exp['first_session_date'] == 0) {
$out .= '???';
} else {
$out .= ortime__format(ortime__sesstime_to_unixtime($exp['first_session_date']), 'hide_time:true');
}
$out .= ' ' . lang('to') . ' ';
if ($exp['last_session_date'] == 0) {
$out .= '???';
} else {
$out .= ortime__format(ortime__sesstime_to_unixtime($exp['last_session_date']), 'hide_time:true');
}
$out .= ')</TD><TD>';
$out .= experiment__list_experimenters($exp['experimenter'], true, true);
$out .= '</TD><TD><A HREF="download_main.php?experiment_id=' . $exp['experiment_id'] . '">' . lang('show_files') . '</A>';
$out .= '</TD></TR>';
}
$out .= '</TABLE>';
}
}
return $out;
}
function experimentmail__send_participant_statistics()
{
global $lang, $settings;
$now = time();
$from = $settings['support_mail'];
$headers = "From: " . $from . "\r\n";
// remember the current language for later reset
$old_lang = lang('lang');
// preload details with current language
$maillang = $old_lang;
$statistics = stats__get_textstats_for_email();
$subject = load_language_symbol('subject_pool_statistics', $maillang) . ' ' . ortime__format($now, 'hide_time:true');
// get experimenters who want to receive the statistics
$query = "SELECT *\n FROM " . table('admin') . "\n WHERE get_statistics_mail='y'\n AND disabled='n'\n ORDER BY language";
$result = or_query($query);
$i = 0;
$rec_count = pdo_num_rows($result);
while ($admin = pdo_fetch_assoc($result)) {
if ($admin['language'] != $maillang) {
$maillang = $admin['language'];
$lang = load_language($maillang);
$statistics = stats__get_textstats_for_email();
$subject = load_language_symbol('subject_pool_statistics', $maillang) . ' ' . ortime__format($now, 'hide_time:true', $maillang);
}
$mailtext = load_mail("admin_participant_statistics_mailtext", $maillang) . "\n\n" . $statistics . "\n" . experimentmail__get_admin_footer($maillang, $admin) . "\n";
$message = process_mail_template($mailtext, $admin);
$done = experimentmail__mail($admin['email'], $subject, $message, $headers);
if ($done) {
$i++;
}
}
// reset language
if ($maillang != $old_lang) {
$lang = load_language($old_lang);
}
return "statistics sent to " . $i . " out of " . $rec_count . " administrators\n";
}
function expregister__get_participation_statuses()
{
global $participation_statuses, $lang;
if (!(is_array($participation_statuses) && count($participation_statuses) > 0)) {
$participation_statuses = array();
$query = "SELECT *\n \t\t\tFROM " . table('participation_statuses') . " \n \t\t\tORDER BY pstatus_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$participation_statuses[$line['pstatus_id']] = $line;
}
$query = "SELECT *\n \t\t\tFROM " . table('lang') . "\n \t\t\tWHERE content_type='participation_status_internal_name' \n \t\t\tOR content_type='participation_status_display_name'\n \t\t\tORDER BY content_name";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
if ($line['content_type'] == 'participation_status_internal_name') {
$field = 'internal_name';
} else {
$field = 'display_name';
}
$participation_statuses[$line['content_name']][$field] = $line[lang('lang')];
}
}
return $participation_statuses;
}
function participant__load_participants_for_ids($ids = array())
{
$participants = array();
if (count($ids) > 0) {
$par_array = id_array_to_par_array($ids);
$query = "SELECT * FROM " . table('participants') . "\n WHERE participant_id IN (" . implode(',', $par_array['keys']) . ")";
$result = or_query($query, $par_array['pars']);
while ($line = pdo_fetch_assoc($result)) {
$participants[$line['participant_id']] = $line;
}
}
return $participants;
}
function check_clearpixel()
{
$return = false;
$query = "SELECT * from " . table('objects') . "\n WHERE item_type='clearpixel' AND item_name='clearpixel'";
$cp = orsee_query($query);
if (!isset($cp['item_details'])) {
$query = "INSERT IGNORE INTO " . table('objects') . "\n SET item_type='clearpixel', item_name='clearpixel', item_details='" . time() . "'";
$done = or_query($query);
$return = true;
} else {
if (time() - $cp['item_details'] > 24 * 60 * 60) {
$query = "UPDATE " . table('objects') . "\n SET item_details='" . time() . "'\n WHERE item_type='clearpixel' AND item_name='clearpixel'";
$done = or_query($query);
$return = true;
} else {
$return = false;
}
}
return $return;
}
if (isset($_REQUEST['betternot']) && $_REQUEST['betternot']) {
redirect('admin/lang_main.php');
}
}
if ($proceed && $reallydelete) {
// update participants and admin
$tables = array('participants', 'admin');
foreach ($tables as $table) {
$pars = array(':slang' => $slang, ':tlang' => $tlang);
$query = "UPDATE " . table($table) . " SET language= :slang WHERE language= :tlang";
$done = or_query($query, pars);
}
message(lang('updated_language_settings'));
// delete language column
$query = "ALTER TABLE " . table('lang') . "\n DROP column " . $tlang;
$done = or_query($query);
// bye, bye
message(lang('language_deleted') . ': ' . $tlang);
log__admin("language_delete", "language:" . $tlang);
redirect('admin/lang_main.php');
}
if ($proceed) {
// confirmation form
echo '<center>';
echo '
<FORM action="lang_lang_delete.php">
<INPUT type=hidden name="elang" value="' . $tlang . '">
<INPUT type=hidden name="nlang" value="' . $slang . '">
<TABLE class="or_formtable">
<TR><TD colspan=2>
echo '<FORM action="lang_edit.php">
<INPUT type=hidden name="el" value="' . $el . '">
<INPUT type=hidden name="letter" value="' . $letter . '">
<INPUT type=text name="search" size=20 maxlength=200 value="' . $search . '">
<INPUT class="button" type=submit name=dosearch value="' . lang('search') . '">
</FORM><BR>';
$query = "select left(content_name,1) as letter, \n \t\tcount(lang_id) as number, \n \t\tcontent_name \n\t\t\tfrom " . table('lang') . " \n\t\t\twhere content_type='lang' GROUP BY letter";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
if ($line['letter'] != $letter) {
echo '<A HREF="lang_edit.php?el=' . $el . '&letter=' . $line['letter'] . '">' . $line['letter'] . '</A> ';
} else {
echo $letter . ' ';
}
}
$result = or_query($lquery, $lpars);
$number = pdo_num_rows($result);
echo '<BR><BR>' . lang('symbols') . ': ' . $number . '<BR><BR>
<FORM action="lang_edit.php" method=post>
<INPUT type=hidden name="el" value="' . $el . '">
<INPUT type=hidden name="letter" value="' . $letter . '">
<INPUT type=hidden name="search" value="' . $search . '">
<TABLE class="or_listtable" style="width: 95%;"><thead>
<TR style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">
<TD colspan=4 align=center>
<INPUT class="button" type=submit name="alter_lang" value="' . lang('change') . '">
</TD>
</TR>
<TR style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">
<TD><B>' . lang('symbol') . '</B></TD>
function calendar__get_user_for_ics_token($icstoken)
{
$pars = array(':icstoken' => $icstoken);
$query = "SELECT * FROM " . table('admin') . " WHERE MD5(concat(admin_id,'|-|',password_crypt))=:icstoken";
$result = or_query($query, $pars);
$admin = false;
while ($line = pdo_fetch_assoc($result)) {
$admin = $line;
}
return $admin;
}
function query__load_saved_queries($type, $limit = -1, $experiment_id = 0, $details = false, $order = "query_time DESC")
{
// type can be participants_search_active, participants_search_all, assign, deassign
$conditions = array();
if ($type) {
$types = explode(",", $type);
$tqueries = array();
foreach ($types as $t) {
$tqueries[] = "query_type='" . trim($t) . "'";
}
$conditions[] = "( " . implode(' OR ', $tqueries) . " )";
}
if ($experiment_id) {
$conditions[] = "( experiment_id='" . $experiment_id . "' )";
}
$query = "SELECT * FROM " . table('queries');
if (count($conditions) > 0) {
$query .= " WHERE " . implode(" AND ", $conditions);
}
$query .= " ORDER BY " . $order;
if ($limit > 0) {
$query .= " LIMIT " . $limit;
}
$result = or_query($query);
$queries = array();
while ($q = pdo_fetch_assoc($result)) {
if ($details) {
$q['properties'] = db_string_to_property_array($q['properties']);
$queries[] = $q;
} else {
$queries[] = $q['json_query'];
}
}
return $queries;
}
function email__get_count($col, $id, $assigned_to = 0)
{
$pars = array();
$conditions = array();
$conditions[] = "thread_id = message_id";
$conditions[] = "flag_deleted = 0";
if ($col) {
$pars[':id'] = $id;
$conditions[] = $col . " = :id";
}
if ($assigned_to) {
$ass_clause = query__get_experimenter_or_clause(array($assigned_to), 'emails', 'assigned_to');
$conditions[] = $ass_clause['clause'];
foreach ($ass_clause['pars'] as $k => $v) {
$pars[$k] = $v;
}
}
$query = "SELECT flag_processed, count(*) as num_emails\n FROM " . table('emails') . "\n WHERE " . implode(" AND ", $conditions) . "\n GROUP BY flag_processed ";
$result = or_query($query, $pars);
$nums = array('num_all' => 0, 'num_new' => 0);
while ($line = pdo_fetch_assoc($result)) {
if ($line['flag_processed']) {
$nums['num_all'] = $line['num_emails'];
} else {
$nums['num_new'] = $line['num_emails'];
}
}
$nums['num_all'] = $nums['num_all'] + $nums['num_new'];
return $nums;
}
function mailqueue__show_mailqueue($experiment_id = "", $limit = -1)
{
global $lang, $color, $options, $proceed;
if ($proceed) {
$pars = array();
if ($limit == -1 && $experiment_id && isset($options['mailqueue_experiment_number_of_entries_per_page']) && $options['mailqueue_experiment_number_of_entries_per_page']) {
$limit = $options['mailqueue_experiment_number_of_entries_per_page'];
} elseif ($limit == -1 && isset($options['mailqueue_number_of_entries_per_page']) && $options['mailqueue_number_of_entries_per_page']) {
$limit = $options['mailqueue_number_of_entries_per_page'];
} else {
$limit = 100;
}
if (isset($_REQUEST['os']) && $_REQUEST['os'] > 0) {
$offset = $_REQUEST['os'];
} else {
$offset = 0;
}
if ($experiment_id) {
$equery = " AND experiment_id=:experiment_id ";
$pars[':experiment_id'] = $experiment_id;
} else {
$equery = "";
}
if (isset($_REQUEST['deleteall']) && $_REQUEST['deleteall']) {
$dall = true;
} else {
$dall = false;
}
if (isset($_REQUEST['deleteallonpage']) && $_REQUEST['deleteallonpage']) {
$dallpage = true;
} else {
$dallpage = false;
}
if (isset($_REQUEST['deletesel']) && $_REQUEST['deletesel']) {
$dsel = true;
} else {
$dsel = false;
}
}
if ($proceed) {
if ($dall || $dallpage || $dsel) {
if ($experiment_id) {
$allow = check_allow('mailqueue_edit_experiment', 'experiment_mailqueue_show?experiment_id=' . $experiment_id);
} else {
$allow = check_allow('mailqueue_edit_all', 'mailqueue_show.php');
}
$where_clause = " WHERE mail_id IS NOT NULL " . $equery;
$ok = false;
if ($dall) {
$ok = true;
}
if ($dallpage) {
$tallids = array();
if (isset($_REQUEST['allids']) && trim($_REQUEST['allids'])) {
$tallids = explode(",", trim($_REQUEST['allids']));
}
if (count($tallids) > 0) {
$i = 0;
$parnames = array();
foreach ($tallids as $id) {
$i++;
$tparname = ':mailid' . $i;
$parnames[] = $tparname;
$pars[$tparname] = $id;
}
$where_clause .= " AND mail_id IN (" . implode(",", $parnames) . ") ";
$ok = true;
} else {
message(lang('error__mailqueue_delete_no_emails_selected'));
$ok = false;
}
}
if ($dsel) {
$dids = array();
if (isset($_REQUEST['del']) && is_array($_REQUEST['del'])) {
foreach ($_REQUEST['del'] as $k => $v) {
if ($v == 'y') {
$dids[] = $k;
}
}
}
if (count($dids) > 0) {
$i = 0;
$parnames = array();
foreach ($dids as $id) {
$i++;
$tparname = ':mailid' . $i;
$parnames[] = $tparname;
$pars[$tparname] = $id;
}
$where_clause .= " AND mail_id IN (" . implode(",", $parnames) . ") ";
$ok = true;
} else {
message(lang('error__mailqueue_delete_no_emails_selected'));
$ok = false;
}
}
if ($ok) {
$query = "DELETE FROM " . table('mail_queue') . $where_clause;
//echo $query;
$done = or_query($query, $pars);
$number = pdo_num_rows($done);
message($number . ' ' . lang('xxx_emails_deleted_from_queue'));
if ($experiment_id) {
if ($number > 0) {
log__admin("mailqueue_delete_entries", "Experiment: " . $experiment_id . ", Count: " . $number);
}
} else {
if ($number > 0) {
log__admin("mailqueue_delete_entries", "Count: " . $number);
}
}
}
if ($experiment_id) {
redirect("admin/experiment_mailqueue_show.php?experiment_id=" . $experiment_id);
} else {
redirect("admin/mailqueue_show.php");
}
}
}
if ($proceed) {
$pars = array();
if ($experiment_id) {
$equery = " AND experiment_id=:experiment_id ";
$pars[':experiment_id'] = $experiment_id;
} else {
$equery = "";
}
$pars[':offset'] = $offset;
$pars[':limit'] = $limit;
$query = "SELECT * FROM " . table('mail_queue') . "\n WHERE mail_id IS NOT NULL " . $equery . " ORDER BY timestamp DESC\n LIMIT :offset , :limit";
$result = or_query($query, $pars);
$num_rows = pdo_num_rows($result);
if ($experiment_id && check_allow('mailqueue_edit_experiment')) {
echo '<FORM action="experiment_mailqueue_show.php" method="POST">
<INPUT type="hidden" name="experiment_id" value="' . $experiment_id . '">';
} elseif (check_allow('mailqueue_edit_all')) {
echo '<FORM action="mailqueue_show.php" method="POST">';
}
echo '<TABLE width=90% border=0>
<TR><TD width=50%>';
//echo '<FONT class="small">'.lang('query').': '.$query.'</FONT><BR><BR>';
echo ' </TD>
<TD align=right width=50%>';
if (check_allow('mailqueue_edit_all')) {
echo '
<TABLE width="100%" border="0">
<TR><TD width="33%" align="right">
<input class="button" type=submit name="deleteall" value="' . lang('delete_all') . '">
</TD><TD width="33%" align="right">
<input class="button" type=submit name="deleteallonpage" value="' . lang('delete_all_on_page') . '">
</TD><TD width="33%" align="right">
<input class="button" type=submit name="deletesel" value="' . lang('delete_selected') . '">
</TD></TR>
</TABLE>
';
}
echo '</TD></TR></TABLE>';
if ($offset > 0) {
echo '[' . log__link('os=' . ($offset - $limit)) . lang('previous') . '</A>]';
} else {
echo '[' . lang('previous') . ']';
}
echo ' ';
if ($num_rows >= $limit) {
echo '[' . log__link('os=' . ($offset + $limit)) . lang('next') . '</A>]';
} else {
echo '[' . lang('next') . ']';
}
echo '<TABLE class="or_listtable" style="width: 90%;"><thead>';
// header
echo '
<thead>
<TR style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">
<TD>' . lang('id') . '</TD>
<TD>' . lang('date_and_time') . '</TD>
<TD>' . lang('email_type') . '</TD>
<TD>' . lang('email_recipient') . '</TD>
<TD>' . lang('reference') . '</TD>
<TD>' . lang('error') . '</TD>';
if (check_allow('mailqueue_edit_all')) {
echo '<TD>
' . lang('select_all') . '
<INPUT id="selall" type="checkbox" name="selall" value="y">
<script language="JavaScript">
$("#selall").change(function() {
if (this.checked) {
$("input[name*=\'del[\']").each(function() {
this.checked = true;
});
} else {
$("input[name*=\'del[\']").each(function() {
this.checked = false;
});
}
});
</script>
</TD>';
}
echo '
</TR>
</thead>
<tbody>
';
$shade = false;
$ids = array();
$experiment_ids = array();
$entries = array();
while ($line = pdo_fetch_assoc($result)) {
$ids[] = $line['mail_id'];
if ($line['experiment_id']) {
$experiment_ids[] = $line['experiment_id'];
}
$entries[] = $line;
}
$experiments = experiment__load_experiments_for_ids($experiment_ids);
foreach ($entries as $line) {
echo '<TR';
if ($shade) {
$shade = false;
} else {
$shade = true;
}
if ($shade) {
echo ' bgcolor="' . $color['list_shade1'] . '"';
} else {
echo ' bgcolor="' . $color['list_shade2'] . '"';
}
echo '>
<TD>' . $line['mail_id'] . '</TD>
<TD>' . ortime__format($line['timestamp'], 'hide_second:false', lang('lang')) . '</TD>
<TD>' . $line['mail_type'] . '</TD>
<TD>' . $line['mail_recipient'] . '</TD>
<TD>';
$reference = array();
if ($line['experiment_id']) {
$reference[] = 'Experiment: <A HREF="experiment_show.php?experiment_id=' . $line['experiment_id'] . '">' . $experiments[$line['experiment_id']]['experiment_name'] . '</A>';
}
if ($line['session_id']) {
$reference[] = 'Session: <A HREF="session_edit.php?session_id=' . $line['session_id'] . '">' . $line['session_id'] . '</A>';
}
if ($line['bulk_id']) {
$reference[] = 'Bulk email: ' . $line['bulk_id'];
}
echo implode('<BR>', $reference);
echo '</TD>
<TD>' . $line['error'] . '</TD>';
if (check_allow('mailqueue_edit_all')) {
echo '<TD><INPUT type="checkbox" name="del[' . $line['mail_id'] . ']" value="y"></TD';
}
echo '</TR>';
}
echo '</tbody></TABLE>';
if (check_allow('mailqueue_edit_all')) {
echo '<INPUT type="hidden" name="allids" value="' . implode(",", $ids) . '">';
echo '</FORM>';
}
return $num_rows;
}
}
function sessions__load_sessions_for_ids($ids = array())
{
$sessions = array();
if (count($ids) > 0) {
$par_array = id_array_to_par_array($ids);
$query = "SELECT * FROM " . table('sessions') . "\n\t\t\t\tWHERE session_id IN (" . implode(',', $par_array['keys']) . ")";
$result = or_query($query, $par_array['pars']);
while ($line = pdo_fetch_assoc($result)) {
$sessions[$line['session_id']] = $line;
}
}
return $sessions;
}
function stats__get_participant_action_data($months_backward = 12)
{
global $lang, $settings;
$actions = array('subscribe', 'confirm', 'edit', 'delete');
if (isset($settings['stats_months_backward']) && $settings['stats_months_backward'] > 0) {
$months_backward = $settings['stats_months_backward'];
}
// titles ect ...
$d['xname'] = lang('month');
$d['yname'] = lang('count');
$d['title'] = lang('participant_actions');
$d['charttype'] = 'multibars';
$d['type_of_data'] = 'count';
$d['column_names'] = array();
foreach ($actions as $action) {
$d['column_names'][$action] = lang($action);
}
// the data
//first get the stuff from the database
$nums = array();
$first_date_unixtime = strtotime("-" . $months_backward . " month", time());
$query = "SELECT action, date_format(FROM_UNIXTIME(timestamp),'%Y%m') as yearmonth,\n count(log_id) as nractions\n FROM " . table('participants_log') . "\n WHERE date_format(FROM_UNIXTIME(timestamp),'%Y%m')>=date_format(FROM_UNIXTIME(" . $first_date_unixtime . "),'%Y%m')\n AND action IN ('" . implode("','", $actions) . "')\n GROUP BY action, yearmonth\n ORDER BY timestamp DESC";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$nums[$line['action']][$line['yearmonth']] = $line['nractions'];
}
$d['value_names'] = array();
foreach ($nums as $action => $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);
}
$d['data'] = $nums;
return $d;
}
function orsee_db_save_array($array, $table, $key, $keyname)
{
global $site__database_database;
// find out which fields i can save
$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS \n\t\t\tWHERE table_name= :table \n\t\t\tAND table_schema = :table_schema";
$pars = array(':table' => table($table), 'table_schema' => $site__database_database);
$result = or_query($query, $pars);
$columns = array();
while ($line = pdo_fetch_assoc($result)) {
$columns[] = $line['COLUMN_NAME'];
}
// delete key
if (isset($array[$keyname])) {
unset($array[$keyname]);
}
$arraykeys = array_keys($array);
$fields_to_save = array_intersect($arraykeys, $columns);
// build set phrase and param array
$first = true;
$set_phrase = "";
$pars = array();
foreach ($fields_to_save as $field) {
if ($first) {
$first = false;
} else {
$set_phrase = $set_phrase . ", ";
}
$set_phrase = $set_phrase . $field . "=:" . $field;
$pars[':' . $field] = $array[$field];
}
$pars[':key'] = $key;
// check if already saved
$query = "SELECT " . $keyname . " FROM " . table($table) . " WHERE " . $keyname . "=:key";
$result = or_query($query, array(':key' => $key));
$num_rows = pdo_num_rows($result);
if ($num_rows > 0) {
// update query
$query = "UPDATE " . table($table) . " SET " . $set_phrase . " WHERE " . $keyname . "=:key";
} else {
// insert query
$query = "INSERT INTO " . table($table) . " SET " . $keyname . "=:key, " . $set_phrase;
}
$result = or_query($query, $pars);
return $result;
}
if ($proceed) {
$participant_id = participant__participant_get_if_not_confirmed($c);
if (!$participant_id) {
message(lang('already_confirmed_error'));
redirect("public/");
} else {
// change status to active
$default_active_status = participant_status__get("is_default_active");
$pars = array(':participant_id' => $participant_id, ':default_active_status' => $default_active_status);
if ($settings['allow_permanent_queries'] == 'y') {
$qadd = ', apply_permanent_queries = 1 ';
} else {
$qadd = '';
}
$query = "UPDATE " . table('participants') . "\n SET status_id= :default_active_status,\n confirmation_token = ''\n " . $qadd . "\n WHERE participant_id= :participant_id ";
$done = or_query($query, $pars);
echo '<center>';
if (!$done) {
message(lang('database_error'));
redirect("public/");
} else {
log__participant("confirm", $participant_id);
// load participant package
$mess = lang('registration_confirmed') . '<BR><BR>';
$mess .= lang('thanks_for_registration');
message($mess);
show_message();
}
echo '</center>';
}
}
$pars_update = array();
foreach ($newcolors as $oname => $ovalue) {
if (isset($mycolors[$oname])) {
$pars_update[] = array(':value' => $ovalue, ':name' => $oname, ':style' => $style);
} else {
$pars_new[] = array(':value' => $ovalue, ':name' => $oname, ':style' => $style, ':now' => $now);
$now++;
}
}
if (count($pars_update) > 0) {
$query = "UPDATE " . table('options') . "\n SET option_value= :value\n WHERE option_name= :name\n AND option_style= :style\n AND option_type= 'color'";
$done = or_query($query, $pars_update);
}
if (count($pars_new) > 0) {
$query = "INSERT INTO " . table('options') . " SET\n option_id= :now,\n option_name= :name,\n option_value= :value,\n option_style= :style,\n option_type= 'color'";
$done = or_query($query, $pars_new);
}
message(lang('changes_saved'));
log__admin("options_colors_edit", "style:" . $style);
redirect('admin/options_colors.php?style=' . $style);
}
}
if ($proceed) {
if (check_allow('settings_edit_colors')) {
echo '
<FORM action="options_colors.php" method=post>
<INPUT type=hidden name="style" value="' . $style . '">';
}
echo '<TABLE class="or_formtable" style="width: 80%;">';
if (check_allow('settings_edit_colors')) {
echo '
function cron__check_for_participant_exclusion()
{
global $settings;
$mess = "";
if ($settings['automatic_exclusion'] == 'y') {
$status_query = participant_status__get_pquery_snippet("eligible_for_experiments");
$query = "SELECT * FROM " . table('participants') . " \n \tWHERE " . $status_query . " \n \tAND number_noshowup >= '" . $settings['automatic_exclusion_noshows'] . "'";
$result = or_query($query);
$excluded = 0;
$informed = 0;
while ($line = pdo_fetch_assoc($result)) {
$done = participant__exclude_participant($line);
if ($done == 'informed') {
$informed++;
}
$excluded++;
}
if ($excluded > 0) {
$mess .= "participants excluded: " . $excluded;
}
if ($informed > 0) {
$mess .= "\nparticipants informed: " . $informed;
}
}
return $mess;
}
function log__show_log($log)
{
global $limit;
if (!$limit) {
$limit = 50;
}
if (isset($_REQUEST['os']) && $_REQUEST['os'] > 0) {
$offset = $_REQUEST['os'];
} else {
$offset = 0;
}
global $lang, $color;
$pars = array();
if (isset($_REQUEST['action']) && $_REQUEST['action']) {
$aquery = " AND action=:action ";
$pars[':action'] = $_REQUEST['action'];
} else {
$aquery = "";
}
if (isset($_REQUEST['id']) && $_REQUEST['id']) {
$idquery = " AND id=:id ";
$pars[':id'] = $_REQUEST['id'];
} else {
$idquery = "";
}
if (isset($_REQUEST['target']) && $_REQUEST['target']) {
$tquery = " AND target LIKE :target ";
$pars[':target'] = '%' . $_REQUEST['target'] . '%';
} else {
$tquery = "";
}
$logtable = table('participants_log');
switch ($log) {
case "participant_actions":
$logtable = table('participants_log');
$secondtable = " LEFT JOIN " . table('participants') . " ON id=participant_id ";
break;
case "experimenter_actions":
$logtable = table('admin_log');
$secondtable = " LEFT JOIN " . table('admin') . " ON id=admin_id ";
break;
case "regular_tasks":
$logtable = table('cron_log');
$secondtable = " LEFT JOIN " . table('admin') . " ON id=admin_id ";
break;
}
if (isset($_REQUEST['delete']) && $_REQUEST['delete'] && isset($_REQUEST['days']) && $_REQUEST['days']) {
$allow = check_allow('log_file_' . $log . '_delete', 'statistics_show_log.php?log=' . $log);
if (isset($_REQUEST['days']) && $_REQUEST['days'] == "all") {
$where_clause = "";
} else {
$now = time();
$dsec = (int) $_REQUEST['days'] * 24 * 60 * 60;
$dtime = $now - $dsec;
$where_clause = " WHERE timestamp < " . $dtime;
}
$query = "DELETE FROM " . $logtable . $where_clause;
$done = or_query($query);
$number = pdo_num_rows($done);
message($number . ' ' . lang('xxx_log_entries_deleted'));
if ($number > 0) {
log__admin("log_delete_entries", "log:" . $log . "\ndays:" . $_REQUEST['days']);
}
redirect("admin/statistics_show_log.php?log=" . $log);
}
$pars[':offset'] = $offset;
$pars[':limit'] = $limit;
$query = "SELECT * FROM " . $logtable . $secondtable . "\n WHERE id IS NOT NULL " . $aquery . $idquery . $tquery . " ORDER BY timestamp DESC\n LIMIT :offset , :limit ";
$result = or_query($query, $pars);
$num_rows = pdo_num_rows($result);
echo '<TABLE width=80% border=0>
<TR><TD width=50%>';
//echo '<FONT class="small">'.lang('query').': '.$query.'</FONT><BR><BR>';
echo '</TD>
<TD align=right width=50%>';
if (check_allow('log_file_' . $log . '_delete')) {
echo '
<FORM action="statistics_show_log.php">
<INPUT type=hidden name="log" value="' . $log . '">
' . lang('delete_log_entries_older_than') . '
<select name="days">
<option value="all">' . lang('all_entries') . '</option>';
$ddays = array(1, 7, 30, 90, 180, 360);
if (isset($_REQUEST['days']) && $_REQUEST['days']) {
$selected = $_REQUEST['days'];
} else {
$selected = 90;
}
foreach ($ddays as $day) {
echo '<option value="' . $day . '"';
if ($day == $selected) {
echo ' SELECTED';
}
echo '>' . $day . ' ';
if ($day == 1) {
echo lang('day');
} else {
echo lang('days');
}
echo '</option>
';
}
echo ' </select><input type=submit name="delete" value="' . lang('delete') . '">';
}
echo '</TD></TR></TABLE>';
if ($offset > 0) {
echo '[' . log__link('os=' . ($offset - $limit)) . lang('previous') . '</A>]';
} else {
echo '[' . lang('previous') . ']';
}
echo ' ';
if ($num_rows >= $limit) {
echo '[' . log__link('os=' . ($offset + $limit)) . lang('next') . '</A>]';
} else {
echo '[' . lang('next') . ']';
}
echo '<TABLE class="or_listtable" style="width: 90%;">';
// header
echo '<thead>
<TR style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">
<TD>
' . lang('date_and_time') . '
</TD>
<TD>';
if ($log == 'participant_actions') {
echo lang('lastname') . ', ' . lang('firstname');
} elseif ($log == 'experimenter_actions' || $log == 'regular_tasks') {
echo lang('experimenter');
}
if (isset($_REQUEST['id']) && $_REQUEST['id']) {
echo ' ' . log__link('id=', 'os=0') . '<FONT class="small">[' . lang('unrestrict') . ']</FONT></A>';
}
echo ' </TD><TD>' . lang('action');
if (isset($_REQUEST['action']) && $_REQUEST['action']) {
echo ' ' . log__link('action=', 'os=0') . '<FONT class="small">[' . lang('unrestrict') . ']</FONT></A>';
}
echo ' </TD><TD>' . lang('target');
if (isset($_REQUEST['target']) && $_REQUEST['target']) {
echo ' ' . log__link('target=', 'os=0') . '<FONT class="small">[' . lang('unrestrict') . ']</FONT></A>';
}
echo ' </TD></TR>
</thead>
<tbody>';
$shade = false;
while ($line = pdo_fetch_assoc($result)) {
echo '<tr class="small"';
if ($shade) {
echo ' bgcolor="' . $color['list_shade1'] . '"';
} else {
echo 'bgcolor="' . $color['list_shade2'] . '"';
}
echo '>
<TD>' . ortime__format($line['timestamp'], 'hide_seconds:false', lang('lang')) . '</TD>
<TD>';
if ($log == 'participant_actions') {
if ($line['participant_id']) {
echo $line['lname'] . ', ' . $line['fname'] . ' <A HREF="participants_edit.php?participant_id=' . $line['participant_id'] . '"><FONT class="small">[' . lang('edit') . ']</FONT></A>';
} else {
echo $line['id'];
}
} elseif ($log == 'experimenter_actions' || $log == 'regular_tasks') {
echo $line['adminname'];
}
if (!isset($_REQUEST['id']) || $_REQUEST['id'] != $line['id']) {
echo ' ' . log__restrict_link('id', $line['id']);
}
echo ' </TD><TD>' . $line['action'];
if (!isset($_REQUEST['action']) || $_REQUEST['action'] != $line['action']) {
echo ' ' . log__restrict_link('action', $line['action']);
}
echo ' </TD><TD>' . nl2br(stripslashes($line['target']));
if (!isset($_REQUEST['target']) || $_REQUEST['target'] != $line['target'] && $log != 'regular_tasks') {
echo ' ' . log__restrict_link('target', $line['target']);
}
echo ' </TD></TR>';
if ($shade) {
$shade = false;
} else {
$shade = true;
}
}
echo '</tbody></TABLE>';
return $num_rows;
}
' . button_link('faq_edit.php?addit=true', lang('create_new'), 'plus-circle');
}
// load languages
$languages = get_languages();
echo '<BR><BR>
<table class="or_listtable" style="width: 80%;"><thead>
<TR style="background: ' . $color['list_header_background'] . '; color: ' . $color['list_header_textcolor'] . ';">';
foreach ($languages as $language) {
echo '<td class="small">' . $language . '</td>';
}
echo '<TD>' . lang('this_faq_answered_questions_of_xxx') . '</TD>
<TD></TD>
</TR></thead>
<tbody>';
$query = "SELECT *\n FROM " . table('faqs') . ", " . table('lang') . "\n WHERE content_type='faq_question'\n AND " . table('faqs') . ".faq_id=" . table('lang') . ".content_name\n ORDER BY " . lang('lang');
$result = or_query($query);
$shade = false;
while ($line = pdo_fetch_assoc($result)) {
echo ' <tr class="small"';
if ($shade) {
echo ' bgcolor="' . $color['list_shade1'] . '"';
} else {
echo ' bgcolor="' . $color['list_shade2'] . '"';
}
echo '>';
foreach ($languages as $language) {
echo ' <td class="small">' . stripslashes($line[$language]) . '</td>';
}
echo '<TD>' . $line['evaluation'] . ' ' . lang('persons') . '</TD>
<TD><A HREF="faq_edit.php?faq_id=' . $line['faq_id'] . '">' . lang('edit') . '</A>
</TD>
}
}
if ($continue) {
if (isset($_REQUEST['mysql_column_name']) && trim($_REQUEST['mysql_column_type']) == '3') {
$ttypespec = $type_specs[3]['fullspec'];
$tindexspec = $index_specs[3];
} elseif (isset($_REQUEST['mysql_column_name']) && trim($_REQUEST['mysql_column_type']) == '2') {
$ttypespec = $type_specs[2]['fullspec'];
$tindexspec = $index_specs[2];
} else {
$ttypespec = $type_specs[1]['fullspec'];
$tindexspec = $index_specs[1];
}
$name = trim($_REQUEST['mysql_column_name']);
$create_query = "ALTER TABLE " . table('participants') . "\n\t\t\t\t\t\t\tADD COLUMN " . $name . " " . $ttypespec . ",\n\t\t\t\t\t\t\tADD INDEX " . str_replace("#name#", $name, $tindexspec);
$done = or_query($create_query);
if ($done) {
message(lang('mysql_column_created'));
redirect('admin/options_participant_profile.php');
} else {
message(lang('database_error'));
}
}
}
}
if ($proceed) {
if (isset($_REQUEST['mysql_column_name'])) {
$mysql_column_name = trim($_REQUEST['mysql_column_name']);
} else {
$mysql_column_name = '';
}
