<?php
$page_title = "List Attendees | CES Admin";
include_once($_SERVER['DOCUMENT_ROOT'] . "/php/common_functions.php"); //common functions in the course registartion system
include_once($_SERVER['DOCUMENT_ROOT'] . "/php/config.php"); //holds global config variables
check_if_authenticated('admin');//check if user is logged in
//opens connection to database
$db = mysqli_connect($mysql_server, $user, $pass, $database);
$session_id = isset($_GET['session_id']) ? $_GET['session_id'] : $_POST['session_id'];
$course_name = '';
include_once($_SERVER['DOCUMENT_ROOT'] . "/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 - List of Attendees</h2>
<?php
include_once($_SERVER['DOCUMENT_ROOT'] . "/admin/php/nav-admin.php");
//Submit Bulk Confirm Attended
if(isset($_POST['submit']) && isset($_POST['attended'])){
$sql = "UPDATE ces_course_registration
SET confirm_attended = !confirm_attended
WHERE session_id = '$session_id'
AND id IN (" . implode(',', $_POST['attended']) . ")";
if (!mysqli_query($db, $sql)) //updates the database
echo "<p class='alert alert-error'>ERROR - Record not updated when doing:<br>" . $sql . "</p>";
}
//get the course details
$session_sql = mysqli_query($db, "SELECT session.session_id, session.online, session.telehealth, course.course_id, course.course_name, course.course_description, unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt, location.location_name, location.address_line1, location.address_line2, location.city, location.province, location.postal_code
FROM ces_sessions session
LEFT OUTER JOIN ces_courses course ON session.course_id = course.course_id
LEFT OUTER JOIN ces_locations location ON location.location_id = session.location_id
WHERE session.session_id = '$session_id'");
while ($session_query = mysqli_fetch_array($session_sql)){
$course_name = $session_query["course_name"];
?>
<h3>Session Information:</h3>
<dl class='ces-description-list'>
<dt>Course Name:</dt>
<dd><?php echo $session_query['course_name']; ?></dd>
<dt>Course Description:</dt>
<dd><?php echo ($session_query['course_description'] > ""? $session_query['course_description']: "None"); ?></dd>
<dt>Start:</dt>
<dd><?php echo date($default_date_format, $session_query['start_dt']); ?></dd>
<dt>End:</dt>
<dd><?php echo date($default_date_format, $session_query['end_dt']); ?></dd>
<dt>Location:</dt>
<dd>
<?php
echo $session_query['location_name'];
if (strlen($session_query['address_line1']) > 0)
echo "\n<br>" . $session_query['address_line1'];
if (strlen($session_query['address_line2']) > 0)
echo "\n<br>" . $session_query['address_line2'];
if (strlen($session_query['city']) > 0)
echo "\n<br>" . $session_query['city'];
if (strlen($session_query['province']) > 0)
echo "\n<br>" . $session_query['province'];
if (strlen($session_query['postal_code']) > 0)
echo "\n<br>" . $session_query['postal_code'];
?>
</dd>
</dl>
<?php
}//while
//if $confirm_attended_change is set then need to update one attendee record to flip ces_course_registration.confirm_attended
if(isset($_GET['confirm_attended_change']) && ($_GET['confirm_attended_change'] == '1' || $_GET['confirm_attended_change'] == '0')){
$sql = "UPDATE ces_course_registration
SET confirm_attended = \"$_GET[confirm_attended_change]\"
WHERE session_id = '$session_id'
AND id = '$_GET[id]'";
if (!mysqli_query($db, $sql)) //updates the database
echo "<div class=error>ERROR - Record not updated when doing:<br>" . $sql . "</div>";
}//if
//REGISTERED
echo "\n<h3>Registered (".(registered_seats($session_id) + registered_guests($session_id))."):</h3>";
echo "\n<form method='post' action='" . $_SERVER['PHP_SELF'] . "'>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=all\" class='btn ces-green-btn' title='Includes In-Person, Telehealth/Audioline, and Waitlist'><span class='fa fa-envelope' aria-hidden='true'></span> All</a>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=registered\" class='btn ces-green-btn' title='Includes In-Person and Telehealth/Audioline, Excludes Waitlist'><span class='fa fa-envelope' aria-hidden='true'></span> Attendees</a>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=inperson\" class='btn ces-green-btn' title='In-Person Only'><span class='fa fa-envelope' aria-hidden='true'></span> In Person</a>";
echo "\n<input type='hidden' name='session_id' value=" . $session_id . ">"; //so we can keep track of which record we're modifying
echo "\n<table class='table table-striped' id='ces-attendees-table'>";
echo "\n<thead>";
echo "\n <tr>";
echo " <th>Last Name</th>\n";
echo " <th>First Name</th>\n";
echo " <th>Email Address</th>\n";
echo " <th>Registration Date</th>\n";
echo " <th># Guests</th>\n";
echo " <th>Mailing List?</th>\n";
echo " <th>Attended?</th>\n";
echo " <th>Remove</th>\n";
echo "</tr>\n";
echo "</thead>\n";
echo "\n<tfoot style='text-align:right;'>";
echo "\n <tr>";
echo "\n <td colspan='6'></td>";
echo "\n <td colspan='2'>";
echo "<input class='btn btn-lg btn-block ces-orange-btn' type='submit' name='submit' value='Bulk Update'>";
echo " </td>";
echo "\n </tr>";
echo "\n <tr>";
echo "\n <td colspan='8'>";
echo "\n <p><strong>Registered:</strong> ".registered_seats($session_id)."</p>";
echo "\n <p><strong>Guests:</strong> ".registered_guests($session_id)."</p>";
if(registered_seats($session_id)>0 && attended_session($session_id)>0){
$percent = 100*attended_session($session_id)/registered_seats($session_id);
$percent = " (".round($percent, 2)."%)";
}
else {
$percent = '';
}
echo "\n <p><strong>Attended:</strong> ".attended_session($session_id). $percent."</p>";
echo "\n </td></tr>";
echo "\n</tfoot>";
echo "<tbody>\n";
$attendee_sql = mysqli_query($db, "SELECT registration.session_id, registration.id, location.location_id, location.location_name, CASE registration.confirm_attended WHEN '1' THEN 'Yes' ELSE 'No' END AS confirm_attended, first_name.value as first_name, last_name.value as last_name, user.email, unix_timestamp(registration.registration_dt) as registration_dt, registration.num_guests_attending
FROM ces_course_registration registration
LEFT OUTER JOIN ces_locations location ON location.location_id = registration.location_id
LEFT OUTER JOIN phplist_user_user user ON registration.id = user.id
LEFT OUTER JOIN phplist_user_user_attribute first_name ON registration.id = first_name.userid AND first_name.attributeid = '1'
LEFT OUTER JOIN phplist_user_user_attribute last_name ON registration.id = last_name.userid AND last_name.attributeid = '2'
WHERE registration.session_id = '$session_id'
AND registration.on_waiting_list <> '1'
AND registration.via_video <> '1'
AND registration.via_audio <> '1'
ORDER BY registration_dt, last_name, first_name");
//output the query results in html table format
while ($attendee_query = mysqli_fetch_array($attendee_sql)) {
//$confirm_attended_change is the opposite of the confirm_attended of the current attendee - used so we can flip confirm status of an attendee
if ($attendee_query['confirm_attended'] == 'Yes')
$confirm_attended_change = 0;
else
$confirm_attended_change = 1;
//figure out if person is on one or more mailing lists
if (num_mailing_lists($attendee_query['id']) > 0){
$on_mailing_list = "Yes";
}//if
else{
$on_mailing_list = "No";
}//else
echo "\n<tr title='User ID: ".$attendee_query['id']."'>";
echo "\n <td>" . $attendee_query["last_name"] . "</td>";
echo "\n <td>" . $attendee_query["first_name"] . "</td>";
echo "\n <td>" . $attendee_query["email"] . "</td>";
echo "\n <td>" . date($iso_date_format, $attendee_query["registration_dt"]) . "</td>";
echo "\n <td>" . $attendee_query["num_guests_attending"] . "</td>";
echo "\n <td>" . $on_mailing_list . "</td>";
echo "\n <td style='text-align:center'><a href='" . $_SERVER['PHP_SELF'] . "?session_id=" . $session_id . "&id=" . $attendee_query["id"] . "&confirm_attended_change=" . $confirm_attended_change . "'>" . $attendee_query['confirm_attended'] . "</a><br><input type='checkbox' name='attended[]' value='".$attendee_query['id']."'>";
echo "</td>";
echo "\n <td style='text-align:center'><a href='remove-attendee.php?session_id=" . $session_id . "&id=" . $attendee_query["id"] . "&location_id=" . $attendee_query["location_id"] . "'>[remove]</a></td>";
echo "\n</tr>";
}//while
echo "</tbody>\n";
echo "</table>";
echo "</form>\n";
if(!is_session_online($session_id)){
//now show people on the waiting list for this session
echo "<h3>Waiting list (".registered_waitlist($session_id)."):</h3>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=waitlist\" class='btn ces-green-btn' title='Send e-mail to everyone on the waitlist for this session'><span class='fa fa-envelope' aria-hidden='true'></span> Email Waitlist</a>";
echo "\n<table class='table table-striped' id='ces-waitlist-table'>\n";
echo "<thead>\n";
echo " <tr>\n";
echo " <th>Last Name</th>\n";
echo " <th>First Name</th>\n";
echo " <th>Email Address</th>\n";
echo " <th>Registration Date</th>\n";
echo " <th># Guests</th>\n";
echo " <th>Mailing List?</th>\n";
echo " <th>Position</th>\n";
echo " <th>Remove</th>\n";
echo " </tr>\n";
echo "</thead>\n";
echo "<tbody>\n";
//get the attendees for a session that are on the waiting list
$attendee_wait_sql = mysqli_query($db, "SELECT registration.session_id, registration.id, location.location_id, location.location_name, CASE registration.confirm_attended WHEN '1' THEN 'Yes' ELSE 'No' END AS confirm_attended, first_name.value as first_name, last_name.value as last_name, user.email, unix_timestamp(registration.registration_dt) as registration_dt, registration.num_guests_attending
FROM ces_course_registration registration
LEFT OUTER JOIN ces_locations location ON location.location_id = registration.location_id
LEFT OUTER JOIN phplist_user_user user ON registration.id = user.id
LEFT OUTER JOIN phplist_user_user_attribute first_name ON registration.id = first_name.userid AND first_name.attributeid = '1'
LEFT OUTER JOIN phplist_user_user_attribute last_name ON registration.id = last_name.userid AND last_name.attributeid = '2'
WHERE registration.session_id = '$session_id'
AND registration.on_waiting_list = '1'
ORDER BY location.telehealth, location.location_name, last_name, first_name");
//output the query results in html table format
$row_count = 0;
while ($attendee_wait_query = mysqli_fetch_array($attendee_wait_sql)) {
$row_count++;
//$confirm_attended_change is the opposite of the confirm_attended of the current attendee - used so we can flip confirm status of an attendee
if ($attendee_wait_query['confirm_attended'] == 'Yes')
$confirm_attended_change = 0;
else
$confirm_attended_change = 1;
//figure out if person is on one or more mailing lists
if (num_mailing_lists($attendee_wait_query['id']) > 0){
$on_mailing_list = "Yes";
}//if
else{
$on_mailing_list = "No";
}//else
//2011-05-23 - wsopko - modified so could do table striping
//echo "\n<tr>\n<td class=courseTableData>" . $attendee_wait_query["location_name"] . "</td>";
echo "\n<tr title='User ID: ".$attendee_wait_query['id']."'>";
echo "\n <td>" . $attendee_wait_query["last_name"] . "</td>";
echo "\n <td>" . $attendee_wait_query["first_name"] . "</td>";
echo "\n <td>" . $attendee_wait_query["email"] . "</td>";
echo "\n <td>" . date($iso_date_format, $attendee_wait_query["registration_dt"]) . "</td>";
//2011-05-23 - wsopko - added # guests field
echo "\n <td>" . $attendee_wait_query["num_guests_attending"] . "</td>";
echo "\n <td>" . $on_mailing_list . "</td>";
//echo "\n <td><a href=" . $_SERVER['PHP_SELF'] . "?session_id=" . $session_id . "&id=" . $attendee_wait_query["id"] . "&confirm_attended_change=" . $confirm_attended_change . ">" . $attendee_wait_query['confirm_attended'] . "</a></td>";
echo "\n <td>" . waitlist_position($attendee_wait_query["id"], $attendee_wait_query["session_id"]) . "</td>";
echo "\n <td><a href=remove-attendee.php?session_id=" . $session_id . "&id=" . $attendee_wait_query["id"] . "&location_id=" . $attendee_wait_query["location_id"] . ">[remove]</a></td>";
echo "\n</tr>";
}//while
echo "\n</tbody>";
echo "\n<tfoot>";
echo "\n <tr>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n <td></td>";
echo "\n</tr>";
echo "\n</tfoot>";
echo "\n</table>";
}
if(!is_session_online($session_id) && is_session_telehealth($session_id)){
//TELEHEALTH LIST
echo "\n<h3>Telehealth (".(registered_audio($session_id) + registered_video($session_id))."):</h3>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=remote\" class='btn ces-green-btn' title='Both Telehealth and Audioline'><span class='fa fa-envelope' aria-hidden='true'></span> Remote Attendees</a>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=telehealth\" class='btn ces-green-btn' title='Telehealth Only'><span class='fa fa-envelope' aria-hidden='true'></span> Telehealth</a>";
echo "\n<a href=\"email-attendees.php?session_id=$session_id&email_type=audioline\" class='btn ces-green-btn' title='Audioline Only'><span class='fa fa-envelope' aria-hidden='true'></span> Audioline</a>";
echo "\n<form method='post' action='" . $_SERVER['PHP_SELF'] . "'>";
echo "\n<input type='hidden' name='session_id' value=" . $session_id . ">"; //so we can keep track of which record we're modifying
echo "\n<table class='table table-striped' id='ces-telehealth-table'>";
echo "\n<thead>";
echo "\n <tr>";
echo " <th>Last Name</th>\n";
echo " <th>First Name</th>\n";
echo " <th>Email Address</th>\n";
echo " <th>Registration Date</th>\n";
echo " <th>Type</th>\n";
echo " <th>Mailing List?</th>\n";
echo " <th>Attended?</th>\n";
echo " <th>Remove</th>\n";
echo "</tr>\n";
echo "</thead>\n";
echo "\n<tfoot style='text-align:right;'>";
echo "\n <tr>";
echo "\n <td colspan='6'></td>";
echo "\n <td colspan='2'>";
echo "<input class='btn btn-lg btn-block ces-orange-btn' type='submit' name='submit' value='Bulk Update'>";
echo " </td>";
echo "\n </tr>";
echo "\n <tr>";
echo "\n <td colspan='8'>";
echo "\n <p><strong>Audio:</strong> ".registered_audio($session_id)."</p>";
echo "\n <p><strong>Video:</strong> ".registered_video($session_id)."</p>";
echo "\n </td></tr>";
echo "\n</tfoot>";
echo "<tbody>\n";
$telehealth_sql = mysqli_query($db, "SELECT registration.session_id, registration.id, location.location_id, location.location_name, CASE registration.confirm_attended WHEN '1' THEN 'Yes' ELSE 'No' END AS confirm_attended, first_name.value as first_name, last_name.value as last_name, user.email, unix_timestamp(registration.registration_dt) as registration_dt, registration.num_guests_attending, registration.via_audio, registration.via_video
FROM ces_course_registration registration
LEFT OUTER JOIN ces_locations location ON location.location_id = registration.location_id
LEFT OUTER JOIN phplist_user_user user ON registration.id = user.id
LEFT OUTER JOIN phplist_user_user_attribute first_name ON registration.id = first_name.userid AND first_name.attributeid = '1'
LEFT OUTER JOIN phplist_user_user_attribute last_name ON registration.id = last_name.userid AND last_name.attributeid = '2'
WHERE registration.session_id = '$session_id'
AND (registration.via_audio = '1'OR registration.via_video = '1')
ORDER BY registration_dt");
//output the query results in html table format
while ($telehealth_query = mysqli_fetch_array($telehealth_sql)) {
//$confirm_attended_change is the opposite of the confirm_attended of the current attendee - used so we can flip confirm status of an attendee
if ($telehealth_query['confirm_attended'] == 'Yes')
$confirm_attended_change = 0;
else
$confirm_attended_change = 1;
//figure out if person is on one or more mailing lists
if (num_mailing_lists($telehealth_query['id']) > 0){
$on_mailing_list = "Yes";
}//if
else{
$on_mailing_list = "No";
}//else
if($telehealth_query['via_video'] == 1){
$telehealth = 'Video';
}elseif($telehealth_query['via_audio'] == 1){
$telehealth = 'Audio';
}else{
$telehealth = "?";
}
echo "\n<tr title='User ID: ".$telehealth_query['id']."'>";
echo "\n <td>" . $telehealth_query["last_name"] . "</td>";
echo "\n <td>" . $telehealth_query["first_name"] . "</td>";
echo "\n <td>" . $telehealth_query["email"] . "</td>";
echo "\n <td>" . date($iso_date_format, $telehealth_query["registration_dt"]) . "</td>";
echo "\n <td>" . $telehealth . "</td>";
echo "\n <td>" . $on_mailing_list . "</td>";
echo "\n <td style='text-align:center'><a href='" . $_SERVER['PHP_SELF'] . "?session_id=" . $session_id . "&id=" . $telehealth_query["id"] . "&confirm_attended_change=" . $confirm_attended_change . "'>" . $telehealth_query['confirm_attended'] . "</a><br><input type='checkbox' name='attended[]' value='".$telehealth_query['id']."'>";
echo "</td>";
echo "\n <td style='text-align:center'><a href='remove-attendee.php?session_id=" . $session_id . "&id=" . $telehealth_query["id"] . "&location_id=" . $telehealth_query["location_id"] . "'>[remove]</a></td>";
echo "\n</tr>";
}//while
echo "</tbody>\n";
echo "</table>";
echo "</form>\n";
}
?>
<script type="text/javascript">
$(document).ready(function(){
$('#ces-attendees-table').DataTable({
paging:false,
searching:false,
info:false,
"order": [[ 3, 'asc' ]],
"columnDefs": [
{ "orderable": false, "targets": 7 }],
dom: 'Bfrtip',
buttons: [
{
extend: 'print',
text: 'Print Attendee List',
title: <?php echo json_encode(utf8_encode($course_name)); ?> + ' - Attendee List',
exportOptions: {
columns:[0,1,2,3,4]
}
}]
});
});
$(document).ready(function(){
$('#ces-waitlist-table').DataTable({
paging:false,
searching:false,
info:false,
"order": [[ 3, 'asc' ]],
"columnDefs": [
{ "orderable": false, "targets": 7 }
],
dom: 'Bfrtip',
buttons: [
'print'],
buttons: [
{
extend: 'print',
text: 'Print Wait List',
title: <?php echo json_encode(utf8_encode($course_name)); ?> + ' - Waitlist List',
exportOptions: {
columns:[0,1,2,3,4,6]
}
}]
});
});
$(document).ready(function(){
$('#ces-telehealth-table').DataTable({
paging:false,
searching:false,
info:false,
"order": [[ 3, 'asc' ]],
"columnDefs": [
{ "orderable": false, "targets": 7 }
],
dom: 'Bfrtip',
buttons: [
'print'],
buttons: [
{
extend: 'print',
text: 'Print Telehealth List',
title: <?php echo json_encode(utf8_encode($course_name)); ?> + ' - Telehealth List',
exportOptions: {
columns:[0,1,2,3,4]
}
}]
});
});
</script>
</article>
</section><!-- #middle-->
</div><!-- #wrapper -->
<?php require_once $_SERVER['DOCUMENT_ROOT'].'/require/footer.php';?>