forked from science-ation/science-ation
375 lines
11 KiB
PHP
375 lines
11 KiB
PHP
<?
|
|
|
|
/*
|
|
* This file is part of the 'Science Fair In A Box' project
|
|
* SFIAB Website: http://www.sfiab.ca
|
|
*
|
|
* Copyright (C) 2005-2006 Sci-Tech Ontario Inc <info@scitechontario.org>
|
|
* Copyright (C) 2005-2006 James Grant <james@lightbox.org>
|
|
*
|
|
* This program is free software; you can redistribute it and/or
|
|
* modify it under the terms of the GNU General Public
|
|
* License as published by the Free Software Foundation, version 2.
|
|
*
|
|
* This program is distributed in the hope that it will be useful,
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
* General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU General Public License
|
|
* along with this program; see the file COPYING. If not, write to
|
|
* the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
|
|
* Boston, MA 02111-1307, USA.
|
|
*/
|
|
?>
|
|
<?
|
|
require ('../common.inc.php');
|
|
require_once ('../user.inc.php');
|
|
user_auth_required('committee', 'admin');
|
|
require ('../register_participants.inc.php');
|
|
|
|
if (get_value_from_array($_GET, 'year'))
|
|
$year = $_GET['year'];
|
|
else
|
|
$year = $config['FAIRYEAR'];
|
|
|
|
send_header('Registration Statistics',
|
|
array('Committee Main' => 'committee_main.php',
|
|
'Administration' => 'admin/index.php',
|
|
'Participant Registration' => 'admin/registration.php'));
|
|
|
|
echo '<br />';
|
|
echo i18n('Choose Status') . ':';
|
|
echo '<form name="statuschangerform" method="get" action="registration_stats.php">';
|
|
echo '<select name="showstatus" onchange="document.forms.statuschangerform.submit()">';
|
|
|
|
$status_str = array();
|
|
$status_str[''] = i18n('Any Status');
|
|
$status_str['complete'] = i18n('Complete');
|
|
// if there is no reg fee, then we dont need to show this status, because nobody will ever be in this status
|
|
if ($config['regfee'] > 0) {
|
|
$status_str['paymentpending'] = i18n('Payment Pending');
|
|
$status_str['completeorpaymentpending'] = i18n('Complete or Payment Pending');
|
|
}
|
|
$status_str['open'] = i18n('Open');
|
|
$status_str['new'] = i18n('New');
|
|
|
|
$showstatus = $_GET['showstatus'];
|
|
|
|
foreach ($status_str as $s => $str) {
|
|
$sel = ($showstatus == $s) ? 'selected="selected"' : '';
|
|
echo "<option $sel value=\"$s\">$str</option>\n";
|
|
}
|
|
echo '</select>';
|
|
echo '</form>';
|
|
|
|
$q = $pdo->prepare("SELECT * FROM projectcategories WHERE year=? ORDER BY id");
|
|
$q->execute([$year]);
|
|
while ($r = $q->fetch(PDO::FETCH_OBJ))
|
|
$cats[$r->id] = $r->category;
|
|
|
|
$q = $pdo->prepare("SELECT * FROM projectdivisions WHERE year=? ORDER BY id");
|
|
$q->execute([$year]);
|
|
while ($r = $q->fetch(PDO::FETCH_OBJ))
|
|
$divs[$r->id] = $r->division;
|
|
|
|
if ($showstatus) {
|
|
switch ($showstatus) {
|
|
case 'complete':
|
|
$wherestatus = "AND status='complete' ";
|
|
break;
|
|
case 'paymentpending':
|
|
$wherestatus = "AND status='paymentpending' ";
|
|
break;
|
|
case 'completeorpaymentpending':
|
|
$wherestatus = "AND (status='complete' OR status='paymentpending') ";
|
|
break;
|
|
case 'open':
|
|
$wherestatus = "AND status='open' ";
|
|
break;
|
|
case 'new':
|
|
$wherestatus = "AND status='new' ";
|
|
break;
|
|
default:
|
|
$wherestatus = '';
|
|
}
|
|
} else
|
|
$wherestatus = '';
|
|
switch (get_value_from_array($_GET, 'sort')) {
|
|
case 'status':
|
|
$ORDERBY = 'registrations.status DESC, projects.title';
|
|
break;
|
|
case 'num':
|
|
$ORDERBY = 'registrations.num';
|
|
break;
|
|
case 'projnum':
|
|
$ORDERBY = 'projects.projectsort, projects.projectnumber';
|
|
break;
|
|
case 'title':
|
|
$ORDERBY = 'projects.title, registrations.status DESC';
|
|
break;
|
|
case 'cat':
|
|
$ORDERBY = 'projects.projectcategories_id, projects.title';
|
|
break;
|
|
case 'div':
|
|
$ORDERBY = 'projects.projectdivisions_id, projects.title';
|
|
break;
|
|
default:
|
|
$ORDERBY = 'registrations.status DESC, projects.title';
|
|
break;
|
|
}
|
|
|
|
$q = $pdo->prepare("SELECT registrations.id AS reg_id,
|
|
registrations.num AS reg_num,
|
|
registrations.status,
|
|
registrations.email,
|
|
projects.title,
|
|
projects.projectnumber,
|
|
projects.projectcategories_id,
|
|
projects.projectdivisions_id,
|
|
projects.language
|
|
FROM
|
|
registrations
|
|
left outer join projects on projects.registrations_id=registrations.id
|
|
WHERE
|
|
1
|
|
AND registrations.year=?,
|
|
$wherestatus
|
|
ORDER BY
|
|
$ORDERBY
|
|
");
|
|
$q->execute([$year]);
|
|
show_pdo_errors_if_any($pdo);
|
|
|
|
$stats_totalprojects = 0;
|
|
$stats_totalstudents = 0;
|
|
$stats_divisions = array();
|
|
$stats_categories = array();
|
|
$stats_students_catdiv = array();
|
|
$stats_projects_catdiv = array();
|
|
$stats_students_schools = array();
|
|
$stats_projects_schools = array();
|
|
$stats_projects_lang = array();
|
|
$schools_names = array();
|
|
$languages = array();
|
|
|
|
while ($r = $q->fetch(PDO::FETCH_OBJ)) {
|
|
$stats_totalprojects++;
|
|
|
|
$stats_divisions[$r->projectdivisions_id] = add_or_initialize($stats_divisions, $r->projectdivisions_id);
|
|
$stats_categories[$r->projectcategories_id] = add_or_initialize($stats_categories, $r->projectcategories_id);
|
|
$stats_projects_catdiv[$r->projectcategories_id][$r->projectdivisions_id] = add_or_initialize_multi_2($stats_projects_catdiv, $r->projectcategories_id, $r->projectdivisions_id);
|
|
$stats_projects_lang[$r->projectcategories_id][$r->projectdivisions_id][$r->language] = add_or_initialize_multi_3($stats_projects_lang, $r->projectcategories_id, $r->projectdivisions_id, $r->language);
|
|
|
|
$languages[$r->language] = add_or_initialize($languages, $r->language);
|
|
|
|
switch ($r->status) {
|
|
case 'new':
|
|
$status_text = 'New';
|
|
break;
|
|
case 'open':
|
|
$status_text = 'Open';
|
|
break;
|
|
case 'paymentpending':
|
|
$status_text = 'Payment Pending';
|
|
break;
|
|
case 'complete':
|
|
$status_text = 'Complete';
|
|
break;
|
|
}
|
|
$status_text = i18n($status_text);
|
|
|
|
$sq = $pdo->prepare("SELECT students.firstname,
|
|
students.lastname,
|
|
students.id,
|
|
schools.school,
|
|
schools.board,
|
|
schools.id AS schools_id
|
|
FROM
|
|
students,schools
|
|
WHERE
|
|
students.registrations_id=?
|
|
AND
|
|
students.schools_id=schools.id
|
|
");
|
|
$sq->execute([$r->reg_id]);
|
|
show_pdo_errors_if_any($pdo);
|
|
|
|
$studnum = 1;
|
|
$schools = '';
|
|
$students = '';
|
|
$lastschoolid = -1;
|
|
while ($studentinfo = $sq->fetch(PDO::FETCH_OBJ)) {
|
|
$stats_totalstudents++;
|
|
$stats_students_catdiv[$r->projectcategories_id][$r->projectdivisions_id]++;
|
|
$stats_students_schools[$r->projectcategories_id][$studentinfo->schools_id]++;
|
|
$schools_names[$studentinfo->schools_id] = $studentinfo->school . " ($studentinfo->board)";
|
|
$lastschoolid = $studentinfo->schools_id;
|
|
}
|
|
// this really isnt right, its only taking the school from the last student in the project to count towards the school's project totals
|
|
// but there's really no other way
|
|
$stats_projects_schools[$r->projectcategories_id][$lastschoolid] = add_or_initialize_multi_2($stats_projects_schools, $r->projectcategories_id, $lastschoolid);
|
|
}
|
|
|
|
echo '<table style="margin-left: 50px;">';
|
|
echo "<tr><td colspan=\"2\"><h3>{$status_str[$showstatus]} - " . i18n('Students / projects per age category / division') . '</h3></td></tr>';
|
|
echo '<tr><td colspan="2">';
|
|
echo '<table class="tableview" width="100%">';
|
|
echo '<thead><tr><td width="50%"></td>';
|
|
foreach ($cats AS $c => $cn) {
|
|
echo "<th>$cn<br /><nobr>" . i18n('Stud | Proj') . '</nobr></th>';
|
|
}
|
|
echo '<th>' . i18n('Total') . '<br /><nobr>' . i18n('Stud | Proj') . '</th>';
|
|
echo '</tr></thead>';
|
|
foreach ($divs AS $d => $dn) {
|
|
echo "<tr><td>$dn</td>";
|
|
$tstud = 0;
|
|
$tstudcat = array();
|
|
$tproj = 0;
|
|
$tprojcat = array();
|
|
foreach ($cats AS $c => $cn) {
|
|
echo '<td align="center">';
|
|
|
|
echo ($stats_students_catdiv[$c][$d] ?? 0);
|
|
echo ' ';
|
|
echo ($stats_projects_catdiv[$c][$d] ?? 0);
|
|
echo '</td>';
|
|
|
|
$tstud += $stats_students_catdiv[$c][$d] ?? 0;
|
|
$tproj += $stats_projects_catdiv[$c][$d] ?? 0;
|
|
|
|
$tstudcat[$c] = add_or_initialize($tstudcat, $c, $stats_students_catdiv[$c][$d] ?? 0);
|
|
$tprojcat[$c] = add_or_initialize($tprojcat, $c, $stats_projects_catdiv[$c][$d] ?? 0);
|
|
}
|
|
echo '<td align="center"><b>';
|
|
echo ($tstud ? $tstud : 0);
|
|
echo ' ';
|
|
echo ($tproj ? $tproj : 0);
|
|
echo '</b></td>';
|
|
echo '</tr>';
|
|
}
|
|
echo '<tr><td><b>' . i18n('Total') . '</b></td>';
|
|
$tstud = 0;
|
|
$tproj = 0;
|
|
foreach ($cats AS $c => $cn) {
|
|
echo '<td align="center"><b>';
|
|
echo ($tstudcat[$c] ? $tstudcat[$c] : 0);
|
|
echo ' ';
|
|
echo ($tprojcat[$c] ? $tprojcat[$c] : 0);
|
|
echo '</b></td>';
|
|
$tstud += $tstudcat[$c];
|
|
$tproj += $tprojcat[$c];
|
|
}
|
|
echo '<td align="center"><b>';
|
|
echo ($tstud);
|
|
echo ' ';
|
|
echo ($tproj);
|
|
echo '</b></td>';
|
|
echo '</tr>';
|
|
|
|
echo '</table>';
|
|
echo '</td></tr>';
|
|
|
|
echo '<tr><td colspan="2"><br /></td></tr>';
|
|
echo "<tr><td colspan=\"2\"><h3>{$status_str[$showstatus]} - " . i18n('Students / projects per age category / school') . '</h3></td></tr>';
|
|
echo '<tr><td colspan="2">';
|
|
echo '<table class="tableview" width="100%">';
|
|
echo '<thead><tr><td width="50%"></td>';
|
|
foreach ($cats AS $c => $cn) {
|
|
echo "<th>$cn<br /><nobr>" . i18n('Stud | Proj') . '</nobr></th>';
|
|
}
|
|
echo '<th>' . i18n('Total') . '<br /><nobr>' . i18n('Stud | Proj') . '</nobr></th>';
|
|
echo '</tr></thead>';
|
|
|
|
asort($schools_names);
|
|
foreach ($schools_names AS $id => $sn) {
|
|
echo "<tr><td>$sn</td>";
|
|
$tstud = 0;
|
|
$tproj = 0;
|
|
foreach ($cats AS $c => $cn) {
|
|
echo '<td align="center">' . ($stats_students_schools[$c][$id] ? $stats_students_schools[$c][$id] : 0);
|
|
echo ' ';
|
|
echo ($stats_projects_schools[$c][$id] ? $stats_projects_schools[$c][$id] : 0) . '</td>';
|
|
$tstud += $stats_students_schools[$c][$id];
|
|
$tproj += $stats_projects_schools[$c][$id];
|
|
}
|
|
echo '<td align="center"><b>' . ($tstud ? $tstud : 0);
|
|
echo ' ';
|
|
echo ($tproj ? $tproj : 0) . '</b></td>';
|
|
echo '</tr>';
|
|
}
|
|
echo '</table>';
|
|
echo i18n('%1 schools total', array(count($schools_names)));
|
|
|
|
echo '</td></tr>';
|
|
echo '<tr><td colspan="2"><br /></td></tr>';
|
|
echo "<tr><td colspan=\"2\"><h3>{$status_str[$showstatus]} - " . i18n('Projects per age category / division / language') . '</h3></td></tr>';
|
|
echo '<tr><td colspan="2">';
|
|
echo '<table class="tableview" width="100%">';
|
|
echo '<thead><tr><td rowspan=\'2\' width="50%"></td>';
|
|
foreach ($cats AS $c => $cn) {
|
|
echo "<th colspan='" . count($languages) . "'>$cn</th>";
|
|
}
|
|
echo "<th colspan='" . count($languages) . "'>" . i18n('Total') . '</nobr></th>';
|
|
echo '</tr><tr>';
|
|
ksort($languages);
|
|
$tprojcat = array();
|
|
foreach ($cats AS $c => $cn) {
|
|
foreach ($languages AS $l => $ln) {
|
|
echo "<th>$l</th>";
|
|
}
|
|
}
|
|
foreach ($languages AS $l => $ln) {
|
|
echo "<th>$l</th>";
|
|
}
|
|
echo '</tr></thead>';
|
|
foreach ($divs AS $d => $dn) {
|
|
echo "<tr><td>$dn</td>";
|
|
$tproj = array();
|
|
foreach ($cats AS $c => $cn) {
|
|
foreach ($languages AS $l => $ln) {
|
|
echo '<td align="center">';
|
|
echo ($stats_projects_lang[$c][$d][$l] ?? 0);
|
|
echo '</td>';
|
|
$tproj[$l] = add_or_initialize($tproj, $l, $stats_projects_lang[$c][$d][$l] ?? 0);
|
|
$tprojcat[$c][$l] = add_or_initialize_multi_2($tprojcat, $c, $l, $stats_projects_lang[$c][$d][$l] ?? 0);
|
|
}
|
|
}
|
|
foreach ($tproj AS $l => $ln) {
|
|
echo '<td align="center"><b>';
|
|
echo ($ln ? $ln : 0);
|
|
echo '</b></td>';
|
|
}
|
|
echo '</tr>';
|
|
}
|
|
echo '<tr><td><b>' . i18n('Total') . '</b></td>';
|
|
$tproj = array();
|
|
foreach ($cats AS $c => $cn) {
|
|
foreach ($languages AS $l => $ln) {
|
|
echo '<td align="center"><b>';
|
|
echo ($tprojcat[$c][$l] ? $tprojcat[$c][$l] : 0);
|
|
echo '</b></td>';
|
|
$tproj[$l] = add_or_initialize($tproj, $l, $tprojcat[$c][$l] ?? 0);
|
|
}
|
|
}
|
|
foreach ($tproj AS $l => $ln) {
|
|
echo '<td align="center"><b>';
|
|
echo ($ln);
|
|
echo '</b></td>';
|
|
}
|
|
echo '</tr>';
|
|
|
|
echo '</table>';
|
|
|
|
echo '</td></tr>';
|
|
|
|
echo '</table>';
|
|
|
|
echo '<br />';
|
|
echo i18n("Note: statistics reflect the numbers of the current 'Status' selected at the top of the page");
|
|
echo '<br />';
|
|
echo '<br />';
|
|
|
|
send_footer();
|
|
?>
|