<?php
$page_title = "Session Report | CES Admin";
include_once("../php/common_functions.php"); //common functions in the course registartion system
include_once("../php/config.php"); //holds global config variables
check_if_authenticated('admin');//check if user is logged in
include_once("../admin/php/top-admin.php"); //get the HTML heading common to all pages in the CES admin module
?>
<article class="ces-admin">
<h2>CES Admin - Session Report</h2>
<?php
include_once("../admin/php/nav-admin.php");
//opens connection to database
$db = mysqli_connect($mysql_server, $user, $pass, $database);
$border = 0;
//first get some information about the session to print out as a header
$course_sql = mysqli_query($db, "SELECT course.course_name, unix_timestamp(session.start_dt), unix_timestamp(session.end_dt)
FROM ces_sessions session
LEFT OUTER JOIN ces_courses course ON course.course_id = session.course_id
WHERE session.session_id = '$_GET[session_id]'");
$course = mysqli_fetch_row($course_sql);
//output some session information as a report heading
echo "<table class='table table-striped'>";
echo "\n<tr>";
echo "\n<td>Course Name:</td>";
echo "\n<td>" . $course[0]. "</td>";
echo "\n</tr>";
echo "\n<tr>";
echo "\n<td>Start Date:</td>";
echo "\n<td>" . date($default_date_format, $course[1]) . "</td>";
echo "\n</tr>";
echo "\n<tr>";
echo "\n<td>End Date:</td>";
echo "\n<td class=reportMainHeading>" . date($default_date_format, $course[2]) . "</td>";
echo "\n</tr>";
echo "\n</table><br><br>";
//START session report - total registrants by registrant type
//get the total amount of registrants in a session (do not count those on the waiting list)
$registrant_sql = mysqli_query($db, "SELECT count(*) FROM ces_course_registration WHERE session_id = '$_GET[session_id]' AND on_waiting_list = 0");
$num_registrants = mysqli_fetch_row($registrant_sql);
$sql_query = "SELECT reg_value.name as \"Registrant Type\", COUNT(*) as \"Total Registrants\", ROUND((COUNT(*) / $num_registrants[0]) * 100, 2) as \"Percentage\"
FROM ces_course_registration reg
LEFT OUTER JOIN phplist_user_user_attribute reg_type on reg_type.attributeid = $REGISTRANT_TYPE_ID and reg_type.userid = reg.id
LEFT OUTER JOIN phplist_listattr_registrant reg_value on reg_value.id = reg_type.value
WHERE reg.session_id = '$_GET[session_id]'
AND reg.on_waiting_list = 0
GROUP BY reg_value.name";
//$sql = mysqli_query($db, $sql_query);
$title = "Total Registrants (not including waiting list) by Registrant Type";
$column_names = array("Registrant Type", "Total Registrants", "Percentage");
generate_report($sql_query, $title, $border, 500, $column_names);
echo "\n<div class=reportTotal>Total Registrants: " . $num_registrants[0] . "</div>";
//END - total registrants by registrant type
//START session report - people who registered but did not show up
$no_shows_sql = mysqli_query($db, "SELECT count(*) FROM ces_course_registration WHERE session_id = '$_GET[session_id]' AND on_waiting_list = 0 AND confirm_attended = 0");
$num_no_shows = mysqli_fetch_row($no_shows_sql);
echo "\n<br><br>\n<div class=reportMainHeading>Number of people who registered but did not attend this session: " . $num_no_shows[0] . "</div>";
echo "\n<div class=reportMainHeading>Percentage of total registrants: " . round(($num_no_shows[0] / $num_registrants[0]) * 100, 2) . "%</div>";
//END session report - people who registered but did not show up
$location_sql_query = "SELECT TRIM(city.value) as location, COUNT(*) as \"Total Registrants\", ROUND((COUNT(*) / $num_registrants[0]) * 100, 2) as \"Percentage\"
FROM ces_course_registration reg
LEFT OUTER JOIN phplist_user_user_attribute city on city.attributeid = $CITY_ID and city.userid = reg.id
WHERE reg.session_id = '$_GET[session_id]'
AND reg.on_waiting_list = 0
GROUP BY location";
//$location_sql = mysqli_query($db, $sql_query);
$title = "Total Registrants (not including waiting list) by Location";
echo "\n<br><br>";
$column_names = array("Location", "Total Registrants", "Percentage");
generate_report($location_sql_query, $title, $border, 500, $column_names);
//generate_report($location_sql, $title, $border, 500, $column_names);
//END session report - where registrants are from
//START session report - registration survey results
$survey_sql_query = "SELECT SUM(answer_word_mouth) \"Word of mouth\",
SUM(answer_community_agency) \"Community Agency\",
SUM(answer_colleague) \"Colleague\",
SUM(answer_friend_family) \"Friend / Family\",
SUM(answer_email_list) \"E-mail list\",
SUM(answer_flyer_poster) \"Flyer / Poster\",
SUM(answer_newsletter) \"Newsletter\",
SUM(answer_website) \"Web site\",
COUNT(answer_other) \"Other\"
FROM ces_course_registration
WHERE session_id = '$_GET[session_id]'";
//$survey_sql = mysqli_query($db, $sql_query);
//$course = mysql_fetch_row($course_sql);
include_once("admin_html_bottom.php"); //get the bottom HTML common to all pages in the CES admin module
?>