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 experiment__current_experiment_summary($experimenter = "", $finished = "n", $show_filter = false, $addbutton = true)
{
global $lang, $expadmindata, $color;
$experimentclasses = experiment__load_experimentclassnames();
$experimenters = experiment__load_experimenters();
$pars = array();
$experimenter_arr = array();
if (isset($_REQUEST['experimenter_search']) && $_REQUEST['experimenter_search']) {
$experimenter_arr = multipicker_json_to_array($_REQUEST['experimenter_search']);
}
if ($experimenter && count($experimenter_arr) == 0) {
$experimenter_arr = array($experimenter);
}
$exp_clause = query__get_experimenter_or_clause($experimenter_arr);
if ($exp_clause['clause']) {
$expq = ' AND ' . $exp_clause['clause'];
foreach ($exp_clause['pars'] as $k => $v) {
$pars[$k] = $v;
}
} else {
$expq = "";
}
$class_arr = array();
if (isset($_REQUEST['class_search']) && $_REQUEST['class_search']) {
$class_arr = multipicker_json_to_array($_REQUEST['class_search']);
}
$class_clause = query__get_class_or_clause($class_arr);
if ($class_clause['clause']) {
$classq = ' AND ' . $class_clause['clause'];
foreach ($class_clause['pars'] as $k => $v) {
$pars[$k] = $v;
}
} else {
$classq = "";
}
$finq = " " . table('experiments') . ".experiment_finished= :finished";
$pars[':finished'] = $finished;
$aquery = $finq . $expq . $classq;
$query = "SELECT " . table('experiments') . ".*,\n\t\t\t\tcount(*) as num_sessions,\n\t\t\t\tif(session_start IS NULL, 1,0) as no_sessions,\n\t\t\t\tmin(if(session_start > date_format(now(),'%Y%m%d%H%i'), \n\t\t\t\tsession_start,NULL)) as time,\n \t\t\tmin(session_start) as first_session_date,\n \t\t\tmax(session_start) as last_session_date\n \t\t\tFROM " . table('experiments') . "\n \t\t\tLEFT JOIN " . table('sessions') . " ON " . table('experiments') . ".experiment_id=" . table('sessions') . ".experiment_id \n \t\t\tWHERE " . table('experiments') . ".experiment_id IS NOT NULL \n \t\t\tAND " . $aquery . " \n \t\t\tGROUP BY experiment_id \n \t\t\tORDER BY no_sessions, time, last_session_date DESC, experiment_id";
$result = or_query($query, $pars);
$experiments = array();
$eids = array();
while ($line = pdo_fetch_assoc($result)) {
$line['sessions'] = array();
$experiments[$line['experiment_id']] = $line;
$eids[] = $line['experiment_id'];
}
if (count($eids) > 0) {
$query = "SELECT * \n\t \t\t\tFROM " . table('sessions') . " \n\t\t\t\t\tWHERE (session_status='planned' OR session_status='live') \n\t\t\t\t\tAND experiment_id IN (" . implode(',', $eids) . ") \n\t\t\t\t\tORDER BY session_start, session_id";
$result = or_query($query);
$sids = array();
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['sessions'][$line['session_id']] = $line;
$sids[] = $line['session_id'];
}
// get counts at experiment level
// performance is better if doing this separately
$query = "SELECT experiment_id, \n\t\t\t\t\tcount(*) as num_assigned \n\t\t\t\t\tFROM " . table('participate_at') . " \n\t\t\t\t\tWHERE experiment_id IN (" . implode(',', $eids) . ")\n\t\t\t\t\tGROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['num_assigned'] = $line['num_assigned'];
}
$query = "SELECT experiment_id, \n\t\t\t\t\tcount(*) as num_registered \n\t\t\t\t\tFROM " . table('participate_at') . " \n\t\t\t\t\tWHERE session_id!=0 \n\t\t\t\t\tAND experiment_id IN (" . implode(',', $eids) . ")\n\t\t\t\t\tGROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['num_registered'] = $line['num_registered'];
}
$participated_clause = expregister__get_pstatus_query_snippet("participated");
$query = "SELECT experiment_id, \n\t\t\t\t\tcount(*) as num_participated \n\t\t\t\t\tFROM " . table('participate_at') . " \n\t\t\t\t\tWHERE " . $participated_clause . " \n\t\t\t\t\tAND experiment_id IN (" . implode(',', $eids) . ")\n\t\t\t\t\tGROUP BY experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['num_participated'] = $line['num_participated'];
}
//
if ($finished == 'y') {
$noshow_clause = expregister__get_pstatus_query_snippet("noshow");
// get showup counts at session level
// couldn't get much better performance if separating counts
$query = "SELECT " . table('participate_at') . ".experiment_id, \n\t\t\t\t\t\tcount(*) as comp_num_registered,\n\t\t\t\t\t\tsum(if(" . $noshow_clause . ",1,0)) as comp_num_noshow \n\t\t\t\t\t\tFROM " . table('participate_at') . ", " . table('sessions') . " \n\t\t\t\t\t\tWHERE " . table('participate_at') . ".session_id=" . table('sessions') . ".session_id \n\t\t\t\t\t\tAND (" . table('sessions') . ".session_status='completed' OR " . table('sessions') . ".session_status='balanced') \n\t\t\t\t\t\tAND " . table('participate_at') . ".experiment_id IN (" . implode(',', $eids) . ")\n\t\t\t\t\t\tGROUP BY " . table('participate_at') . ".experiment_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['comp_num_registered'] = $line['comp_num_registered'];
$experiments[$line['experiment_id']]['comp_num_noshow'] = $line['comp_num_noshow'];
}
}
if (count($sids) > 0) {
$query = "SELECT experiment_id, session_id, \n\t\t\t\t\t\tcount(*) as num_registered \n\t\t\t\t\t\tFROM " . table('participate_at') . " \n\t\t\t\t\t\tWHERE session_id IN (" . implode(',', $sids) . ")\n\t\t\t\t\t\tGROUP BY experiment_id, session_id";
$result = or_query($query);
while ($line = pdo_fetch_assoc($result)) {
$experiments[$line['experiment_id']]['sessions'][$line['session_id']]['num_registered'] = $line['num_registered'];
}
}
}
echo '
<center>
<BR>
<table class="or_panel">';
if ($show_filter) {
echo '<TR><TD colspan=2>
<FORM action="' . thisdoc() . '"><TABLE border=0><TR><TD>' . lang('restrict_list_to_experiments_of_class') . '</TD><TD>';
echo experiment__experiment_class_select_field('class_search', $class_arr, true, array('cols' => 30, 'picker_maxnumcols' => 3));
echo ' </TD><TD rowspan=2 valign=middle>
<INPUT class="button" style="font-size: 8pt; margin: 0;" type=submit name="show" value="' . lang('show') . '">
</TD></TR><TR><TD>' . lang('restrict_list_to_experimenters') . '</TD><TD>';
echo experiment__experimenters_select_field("experimenter_search", $experimenter_arr, true, array('cols' => 30, 'tag_color' => '#f1c06f', 'picker_color' => '#c58720', 'picker_maxnumcols' => 3));
echo ' </TD></TR></TABLE></FORM>
</TD></TR>';
}
echo '
<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'] . '">';
if ($finished == "y") {
echo lang('finished_experiments');
} elseif ($experimenter) {
echo lang('my_experiments');
} else {
echo lang('experiments');
}
echo '</TD><TD style="background: ' . $color['panel_title_background'] . '; color: ' . $color['panel_title_textcolor'] . '">';
if ($addbutton && check_allow('experiment_edit')) {
echo button_link("experiment_edit.php?addit=true", lang('register_new_experiment'), 'plus-circle');
}
if (!$experimenter) {
if ($finished == "n") {
echo button_link("experiment_old.php", lang('finished_experiments'), 'fast-backward');
} else {
echo button_link("experiment_main.php", lang('current_experiments'), 'fast-forward');
}
}
echo '</TD></TR>
</TABLE>
</TD></TR>
<TR><TD colspan=2>';
echo count($experiments) . ' ';
if ($finished == "n") {
echo lang('xxx_current_experiments');
} else {
echo lang('xxx_finished_experiments');
}
echo '
</TD></TR>
<TR><TD width="5%"> </TD>
<TD width="95%" colspan=2>
<TABLE border=0 width="100%" cellspacing="0">';
foreach ($experiments as $id => $exp) {
if ($finished == "n") {
experiment__experiments_format_alist($exp);
} else {
experiment__old_experiments_format_alist($exp);
}
}
echo '</TABLE>
</TD></TR>
</TABLE>
</center>
<BR><BR>
';
}
