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