<?php
$page_title = "Season 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 - Season Report</h2>
<?php
include_once("../admin/php/nav-admin.php");
//show the report prompts
echo "<form action=$_SERVER[PHP_SELF] method=post>";
echo "<div class='form-group'>";
echo "\n<label>Report Start Date:</label>";
echo "<div class='ces-session-start-date'>";
echo month_name_select('start_dt_month', date('m'));
echo days_in_month_select('start_dt_day', date('j'));
echo year_select('start_dt_year', date('Y'), date('Y')-4, date('Y')+1);
echo " </div>";
echo "</div>";
echo "<div class='form-group'>";
echo "\n<label>Report End Date:</label>";
echo "<div class='ces-session-start-date'>";
echo month_name_select('end_dt_month', date('m'));
echo days_in_month_select('end_dt_day', date('j'));
echo year_select('end_dt_year', date('Y'), date('Y')-4, date('Y')+1);
echo "</div>";
echo "</div>";
echo "\n<input class='btn btn-lg btn-block ces-green-btn' type='submit' name='submit' value=\"Run Report\">";
echo "\n</form>";
if(isset($_POST['submit'])){ //if user entered report dates, show report
//opens connection to database
$db = mysqli_connect($mysql_server, $user, $pass, $database);
$border = 0;
$report_start_dt = $_POST['start_dt_year'] . '-' . $_POST['start_dt_month'] . "-" . $_POST['start_dt_day'];
$report_end_dt = $_POST['end_dt_year'] . '-' . $_POST['end_dt_month'] . "-" . $_POST['end_dt_day'];
//START season report - total registrants by registrant type
//get the total amount of registrants in a season (do not count those on the waiting list)
$registrant_sql = mysqli_query($db, "SELECT count(*)
FROM ces_course_registration reg
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0");
$num_registrants = mysqli_fetch_row($registrant_sql);
//get the registrants in a session (do not count those on the waiting list) grouped by registrant type and professional organization (agency)
/*2011-10-18 - removed professional organization as it is no longer a field
$sql = mysql_query("SELECT reg_value.name as \"Registrant Type\", agency.value as \"Professional Organization\", 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
LEFT OUTER JOIN phplist_user_user_attribute agency on agency.attributeid = $PROFESSIONAL_AGENCY_ID and agency.userid = reg.id
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
GROUP BY reg_value.name, agency.value", $db);
*/
$report_sql = "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
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
GROUP BY reg_value.name";
$title = "Total Registrants (not including waiting list) by Registrant Type";
//2011-10-18 - removed professional organization as it is no longer a field
//$column_names = array("Registrant Type", "Professional Organization", "Total Registrants", "Percentage");
$column_names = array("Registrant Type", "Total Registrants", "Percentage");
generate_report($report_sql, $title, $border, 500, $column_names);
echo "\n<div class=reportTotal>Total Registrants: " . $num_registrants[0] . "</div>";
//END - total registrants by registrant type
//START season report - total attendees
$attendee_sql = mysqli_query($db, "SELECT count(*)
FROM ces_course_registration reg
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
AND reg.confirm_attended = 1");
$num_attendees = mysqli_fetch_row($attendee_sql);
echo "\n<br><br>\n<div class=reportMainHeading>Number of people who attended sessions: " . $num_attendees[0] . "</div>";
echo "\n<div class=reportMainHeading>Percentage of total registrants: " . round(($num_attendees[0] / $num_registrants[0]) * 100, 2) . "%</div>";
//END season report - total attendees
//START season report - people who registered but did not show up
$no_shows_sql = mysqli_query($db, "SELECT count(*)
FROM ces_course_registration reg
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
AND reg.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 sessions: " . $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 season report - people who registered but did not show up
//START season report - people who registered after the session start time
$registered_late_sql = mysqli_query($db, "SELECT count(*)
FROM ces_course_registration reg
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
AND reg.registration_dt >= session.start_dt");
$num_registered_late = mysqli_fetch_row($registered_late_sql);
echo "\n<br><br>\n<div class=reportMainHeading>Number of people who registered after the session's start date: " . $num_registered_late[0] . "</div>";
echo "\n<div class=reportMainHeading>Percentage of total registrants: " . round(($num_registered_late[0] / $num_registrants[0]) * 100, 2) . "%</div>";
//END season report - people who registered after the session start time
//START season report - where registrants are from
//get the registrants (do not count those on the waiting list) grouped by postal code or quadrant
/* 2011-10-19 - wsopko - we are not tracking postal code or quadrant anymore, so do location based on city
$location_sql = mysql_query("SELECT
CASE TRIM(LCASE(city.value))
WHEN 'calgary' THEN quadrant_value.name
ELSE postal_code.value
END 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 postal_code on postal_code.attributeid = $POSTAL_CODE_ID and postal_code.userid = reg.id
LEFT OUTER JOIN phplist_user_user_attribute quadrant on quadrant.attributeid = $QUADRANT_ID and quadrant.userid = reg.id
LEFT OUTER JOIN phplist_listattr_quadrant quadrant_value on quadrant_value.id = quadrant.value
LEFT OUTER JOIN phplist_user_user_attribute city on city.attributeid = $CITY_ID and city.userid = reg.id
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
GROUP BY Location", $db);
*/
$location_sql = "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
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
GROUP BY location";
$title = "Total Registrants (not including waiting list) by Location";
echo "\n<br><br>";
$column_names = array("Location", "Total Registrants", "Percentage");
generate_report($location_sql, $title, $border, 500, $column_names);
//END season report - where registrants are from
//START season report - total telehealth sessions (if more than one telehealth location in one session, still considered one telehealth session)
$telehealth_sql = mysqli_query($db, "SELECT COUNT(*) FROM (SELECT distinct session.session_id
FROM ces_sessions session
LEFT OUTER JOIN ces_locations location ON location.location_id = session.location_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND location.telehealth = 1) AS sub1");
$telehealth_sessions = mysqli_fetch_row($telehealth_sql);
echo "\n<br><br>\n<div class=reportMainHeading>Number of video conference sessions: " . $telehealth_sessions[0] . "</div>";
//END season report - total telehealth sessions
//START season report - total traditional sessions
$traditional_sql = mysqli_query($db, "SELECT COUNT(*) FROM (SELECT distinct session.session_id
FROM ces_sessions session
LEFT OUTER JOIN ces_locations location ON location.location_id = session.location_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND location.telehealth = 0) AS sub1");
$traditional_sessions = mysqli_fetch_row($traditional_sql);
echo "\n<br><br>\n<div class=reportMainHeading>Number of Traditional sessions: " . $traditional_sessions[0] . "</div>";
//END season report - total traditional sessions
//START season report - total attendees by type
//get the attendees in a session (do not count those on the waiting list) grouped by registrant type (get total parents and professionals)
$attendees_type_sql = "SELECT reg_value.name as \"Registrant Type\", COUNT(*) as \"Total Registrants\"
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
LEFT OUTER JOIN ces_sessions session ON session.session_id = reg.session_id
WHERE DATE_FORMAT(session.start_dt, '%Y-%m-%d') BETWEEN '$report_start_dt' AND '$report_end_dt'
AND reg.on_waiting_list = 0
AND reg.confirm_attended = 1
GROUP BY reg_value.name";
$title = "Total Attendees (not including waiting list) by Registrant Type";
echo "\n<br><br>\n";
$column_names = array("Registrant Type", "Total Registrants");
generate_report($attendees_type_sql, $title, $border, 500, $column_names);
//END season report - total attendees by type
}//if
include_once("admin_html_bottom.php"); //get the bottom HTML common to all pages in the CES admin module
?>