<?php
//prints select list of days in a month, with the $select_value selected
function days_in_month_select($select_name, $select_value){
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
for ($day=1; $day<=31; $day++){
echo "\n<option value=\"" . $day . "\"";
if ($select_value == $day)
echo " selected";
echo ">" . $day . "</option>";
}//for
echo "\n</select>";
}//days_in_month_select()
//prints select list of Canadian provinces, with the $select_value selected
function provinces_select($select_name, $select_value){
$provinces = array("AB", "BC", "MB", "NB", "NF", "NS", "NT", "NT", "ON", "PE", "PQ", "SK", "YT");
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
foreach ($provinces as $province_abv){
echo "\n<option value=\"" . $province_abv . "\"";
if ($select_value == $province_abv)
echo " selected";
echo ">" . $province_abv . "</option>";
}//foreach
echo "\n</select>";
}//provinces_select()
//prints select list of months, with the $select_value selected
function month_name_select($select_name, $select_value){
$months = array("01" => "Jan", "02" => "Feb", "03" => "Mar", "04" => "Apr", "05" => "May", "06" => "Jun", "07" => "Jul", "08" => "Aug", "09" => "Sep", "10" => "Oct", "11" => "Nov", "12" => "Dec");
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
foreach ($months as $month_digit => $month_abv){
echo "\n<option value=\"" . $month_digit . "\"";
if ($select_value == $month_digit)
echo " selected";
echo ">" . $month_abv . "</option>";
}//foreach
echo "\n</select>";
}//month_name_select()
//prints select list of years with the $select_value selected and year range between $start_year and $end_year
//2012-04-10 - wsopko - added start_year and end_year as parameters so could override the date ranges - did so season reports could have dates in the past
//function year_select($select_name, $select_value){
function year_select($select_name, $select_value, $start_year = 0, $end_year = 0){
//2012-04-10 - wsopko - if no start or end date parameters set, set to defaults
//$start_year = date('Y');
//$end_year = date('Y')+5;
if($start_year == 0){
$start_year = date('Y');
}//if
if($end_year == 0){
$end_year = date('Y')+5;
}//if
if (!empty($select_value) && $select_value < $start_year) //just in case the select year is less than the start year, we want to be able to output the select year
$start_year = $select_value;
if (!empty($select_value) && $select_value > $end_year) //just in case the select year is greater than the end year, we want to be able to output the select year
$end_year = $select_value;
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
for ($year=$start_year; $year<=$end_year; $year++){
echo "\n<option value=\"" . $year . "\"";
if ($select_value == $year)
echo " selected";
echo ">" . $year . "</option>";
}//for
echo "\n</select>";
}//year_select
//prints select list of hours in a day (12 hour clock), with the $select_value selected
function hours_in_day_select($select_name, $select_value){
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
for ($hour=1; $hour<=12; $hour++){
echo "\n<option value=\"" . $hour . "\"";
if ($select_value == $hour)
echo " selected";
echo ">" . $hour . "</option>";
}//for
echo "\n</select>";
}//hours_in_day_select()
//prints select list of minutes in an hour (five minute increments), with the $select_value selected
function minutes_in_hour_select($select_name, $select_value){
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
for ($minute=0; $minute<=55; $minute+=5){
if($minute<10) //pad one digit minutes with a zero
$minute = "0" . $minute;
echo "\n<option value=\"" . $minute . "\"";
if ($select_value == $minute)
echo " selected";
echo ">" . $minute . "</option>";
}//for
echo "\n</select>";
}//minutes_in_hour_select()
//prints select list of part of day (AM vs PM), with the $select_value selected
function meridiem_select($select_name, $select_value){
$meridiem = array("AM", "PM");
echo "\n<select class='form-control' name=\"" . $select_name . "\">";
foreach ($meridiem as $meridiem_abv){
echo "\n<option value=\"" . $meridiem_abv . "\"";
if ($select_value == $meridiem_abv)
echo " selected";
echo ">" . $meridiem_abv . "</option>";
}//foreach
echo "\n</select>";
}//meridiem_select()
//prints a select list based on data selected from a database with SQL from $sql
//$select_name is the key field name in the database
//$select_key is the key field value in the database
//$select_value is the field name of the value we show to the user in the select list
function build_db_select_list($sql, $select_name, $select_key, $select_value, $select_parms = ''){
echo "\n<select class='form-control' name=\"" . $select_name . "\" $select_parms>";
while ($query_result = mysqli_fetch_array($sql)) {
echo "\n<option value=\"" . $query_result[$select_name] . "\"";
if ($select_key == $query_result[$select_name])
echo " selected";
echo ">" . $query_result[$select_value] . "</option>";
}//while
echo "\n</select>";
}//build_db_select_list()
//prints a select list based on data selected from a database with SQL from $sql
// first selection will be blank
//$select_name is the key field name in the database
//$select_key is the key field value in the database
//$select_value is the field name of the value we show to the user in the select list
function build_db_select_list_with_blank($sql, $select_name, $select_key, $select_value, $select_parms = ''){
echo "\n<select class='form-control' name=\"" . $select_name . "\" $select_parms>";
echo "\n<option></option>";
while ($query_result = mysqli_fetch_array($sql)) {
echo "\n<option value=\"" . $query_result[$select_name] . "\"";
if ($select_key == $query_result[$select_name])
echo " selected";
echo ">" . $query_result[$select_value] . "</option>";
}//while
echo "\n</select>";
}//build_db_select_list()
//takes a hour in 12 hour format and returns it in 24 hour format
function convert_hour($hour, $meridiem){
if($meridiem == 'PM' && $hour <> 12)
return $hour += 12;
elseif ($meridiem == 'AM' && $hour == 12)
return 0; //12:00AM is 00:00 on 24 hour clock
else //no hour adjustment necessary
return $hour;
}//convert_hour()
function register_person_in_session($session_id, $id, $location_id, $on_waiting_list, $already_on_waiting_list, $attend = "", $num_guests_attending = 0){
//Text for this function is wrong when an ADMIN unregisters a user, and another user from the wait list is added automatically
global $db; //connection to database already established
global $default_date_format; //set in config.php
$result = '';
$session_type = session_type($session_id);
if ($num_guests_attending == ''){
$num_guests_attending = 0;
}
$via_video = 0;
$via_audio = 0;
$online = 0;
$webex = 0;
if($attend == "video"){
$via_video = 1;
$on_waiting_list = 0;
}
elseif($attend == "audio"){
$via_audio = 1;
$on_waiting_list = 0;
}
elseif($attend == "recorded"){
$on_waiting_list = 0;
$online = 1;
}
elseif($attend == "webex"){
$on_waiting_list = 0;
$webex = 1;
}
if($already_on_waiting_list == '1'){
$sql = "UPDATE ces_course_registration SET on_waiting_list = '0' WHERE session_id = '$session_id' AND id = '$id'";
}//if
else{
$sql = "INSERT INTO ces_course_registration (session_id, id, location_id, registration_dt, on_waiting_list, num_guests_attending, via_audio, via_video) VALUES ('$session_id', '$id', '$location_id', now(), '$on_waiting_list', '$num_guests_attending', '$via_audio', '$via_video')";
}//else
if(mysqli_query($db, $sql)){ //if the registration was successful send confirmation email
//2012-02-04 - wsopko - log the event to event_log table
log_event("register_person_in_session()", "registered user ID $id in session ID $session_id, location ID $location_id, on_waiting_list = $on_waiting_list");
if($session_type === "recorded" || $session_type === "webex" ){
$session_sql = mysqli_query($db, "SELECT session.session_id, session.url, course.course_id, course.course_name, course.course_description, unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt
FROM ces_sessions session
LEFT OUTER JOIN ces_courses course ON session.course_id = course.course_id
WHERE session.session_id = '$session_id'");
}
else{
//get the course details to include in the email
$session_sql = mysqli_query($db, "SELECT session.session_id, session.url, 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 = '$location_id'
WHERE session.session_id = '$session_id'
and location.location_id = '$location_id'");
}
//now send confirmation email to person
while ($session_query = mysqli_fetch_array($session_sql)){ //course details, should only be one row
//2008-01-27 - wsopko - only send email if the course has not started yet
$current_date_GMT = time(); //timezone is now set properly in no need to convert it
$start_date_GMT = convert_local_to_GMT($session_query['start_dt']); //convert session start date to GMT
if(($start_date_GMT > $current_date_GMT || $online) && $session_type != "webex" ){ //only send email if session has not started yet (dates are both in seconds since UNIX Epoch - Jan 1, 1970)
//find details about the person we're sending the email to
$user_sql = mysqli_query($db, "SELECT email FROM phplist_user_user WHERE id = '$id'");
$user_array = mysqli_fetch_row($user_sql);
$email = $user_array[0];
if($on_waiting_list == 1){ //putting person on waiting list
$subject = "CES - Course Registration - On Waiting List - " . stripslashes($session_query['course_name']);
$body = "As requested, you have been put on the waiting list for the following course. If space in the course becomes available, you will be automatically registered and receive an email confirmation.";
//$body .= "\n\nYour current positon on the wait list is: ";
$body .= "\n\nPlease login to your account to see updates on your position on the waiting list. http://community.hmhc.ca/login/\n";
}//if
elseif($online){ //person is being registered in online session
$subject = "CES - Course Registration - Confirmation - " . stripslashes($session_query['course_name']);
$body = "As requested, you have been registered to attend the following Recorded session:";
}//else
elseif ($via_audio) {
$subject = "CES - Course Registration - Audioline Confirmation - " . stripslashes($session_query['course_name']);
$body = "As requested, you have been registered to attend the following session via AUDIOLINE:";
}elseif($via_video){
$subject = "CES - Course Registration - Telehealth Confirmation - " . stripslashes($session_query['course_name']);
$body = "As requested, you have been registered to attend the following session via TELEHEALTH / VIDEO CONFERENCING:";
}else{ //person is being registered in session
$subject = "CES - Course Registration - Confirmation - " . stripslashes($session_query['course_name']);
$body = "As requested, you have been registered to attend the following session IN PERSON:>";
}//else
$body .= "\n\nCourse Name: " . stripslashes($session_query['course_name']);
$body .= "\nCourse Start: " . date($default_date_format, $session_query['start_dt']);
$body .= "\nCourse End: " . date($default_date_format, $session_query['end_dt']);
//$body .= "\n\nCourse Description: " . stripslashes($session_query['course_description']);
//if(!$via_video && !$via_audio && !$online){
$haystack = array("recorded", "webex");
if(!in_array($session_type, $haystack) && !$via_video && !$via_audio){
$body .= "\n\nLocation:\n\t " . stripslashes($session_query['location_name']);
if (strlen($session_query['address_line1']) > 0)
$body .= "\n\t " . stripslashes($session_query['address_line1']);
if (strlen($session_query['address_line2']) > 0)
$body .= "\n\t " . stripslashes($session_query['address_line2']);
if (strlen($session_query['city']) > 0)
$body .= "\n\t " . stripslashes($session_query['city']);
if (strlen($session_query['province']) > 0)
$body .= ", " . stripslashes($session_query['province']);
if (strlen($session_query['postal_code']) > 0)
$body .= "\n\t " . stripslashes($session_query['postal_code']);
}
if($num_guests_attending > 0){
$body .= "\n\nGuests Attending: " . $num_guests_attending;
}//if
if($via_audio){ // AUDIOLINE MESSAGE
$body .= "\n\nAudioline Registration Instructions:";
$body .= "\n\n\t1. Prior to the session (48 hrs):";
$body .= "\n\t\tFor handout, sign-in sheet and evaluation, visit:";
$body .= "\n\t\thttp://community.hmhc.ca/";
$body .= "\n\t\tor login and view 'Your Courses'.";
$body .= "\n\n\t2. 15 minutes before the start of the session:";
$body .= "\n\t\tDial 1-866-236-8306 and enter part code 6530253.";
$body .= "\n\n\t\tRemember to MUTE your phone once connected.";
$body .= "\n\n\t\tShould you have any problems during the video conference,";
$body .= "\n\t\tcall 310-8822 (no area code needed if you are calling within Alberta).";
$body .= "\n\n\t3. After the session:";
$body .= "\n\n\t\tReturn the sign-in sheet and evaluation within 2 days by:";
$body .= "\n\t\tFax - 403-955-8184 or,";
$body .= "\n\t\tEmail - ces@ahs.ca";
$body .= "\n\nSessions with low registration may be cancelled within 48hrs.";
$body .= "\n\nWe hope you find the session useful.";
}
elseif($via_video){ // Video MESSAGE
$body .= "\n\nTo complete your registration, please read the following carefully:";
$body .= "\n\nTelehealth/Video Conferencing Instructions";
$body .= "\n\nIf you are an AHS employee, use VCScheduler to book and confirm your site:";
$body .= "\n\n\thttps://ischeduler.albertahealthservices.ca/ischeduler/eceptionist.asp";
$body .= "\n\n\tOnce your site has been confirmed:";
$body .= "\n\n\t\t1. Prior to the session (48 hrs):";
$body .= "\n\t\t\tFor handout, sign-in sheet and evaluation, visit:";
$body .= "\n\t\t\thttp://community.hmhc.ca/";
$body .= "\n\t\t\tor login and view 'Your Courses'.";
$body .= "\n\n\t\t2. After the session:";
$body .= "\n\t\t\tReturn the sign-in sheet and evaluation within 2 days by:";
$body .= "\n\t\t\tFax - 403-955-8184 or,";
$body .= "\n\t\t\tEmail - ces@ahs.ca";
$body .= "\n\nIf you are NOT an AHS employee:";
$body .= "\n\n\t1. If you know that your facility has the necessary equipment";
$body .= "\n\t\tcontact the AHS Bridge techs at 403-310-8822 to conduct a test connection";
$body .= "\n\t\tto determine if we are able to connect to your site.";
$body .= "\n\n\t2. If you would like to request to attend a site close to you (i.e. Local Hospital):";
$body .= "\n\t\temail ces@ahs.ca specifying the session title,";
$body .= "\n\t\tdate and the name and location of the site you are requesting.";
$body .= "\n\n\tOnce your site has been confirmed:";
$body .= "\n\n\t\t1. Prior to the session (48 hrs):";
$body .= "\n\t\t\tFor handout, sign-in sheet and evaluation, visit:";
$body .= "\n\t\t\thttp://community.hmhc.ca";
$body .= "\n\t\t\tor login and view 'Your Courses'.";
$body .= "\n\n\t\t2. After the session:";
$body .= "\n\t\t\tReturn the sign-in sheet and evaluation within 2 days by:";
$body .= "\n\t\t\tFax - 403-955-8184 or,";
$body .= "\n\t\t\tEmail - ces@ahs.ca";
$body .= "\n\nSessions with low registration may be cancelled within 48hrs.";
$body .= "\n\nWe hope you find the session useful,";
}elseif($online){//Online MESSAGE
$body .= "\n\nPlease note:";
$body .= "\n\n\tThis session is presented in Windows Media Video (WMV) format and requires";
$body .= "\n\tInternet Explorer and/or Windows Media Player to view.";
$body .= "\n\n\tTo access this session please login with your CES account, and visit 'Your Courses'. ";
$body .= "\n\tThe session link will be available until ". date($default_date_format, $session_query['end_dt']);
$body .= "\n\n\tHere is a direct link to Video Page (you will need to be logged in):";
$body .= "\n\thttp://community.hmhc.ca/sessions/online/?id=".$session_id;
$body .= "\n\nSessions with low registration may be cancelled within 48hrs.";
$body .= "\n\nWe hope you find the session useful,";
}elseif ($on_waiting_list==0){//In Person MESSAGE
$body .= "\n\nPlease note:";
$body .= "\n\nTo allow time for parking and registration, arrive at least 15 minutes prior to the session.";
$body .= "\n\nSession handouts will be provided upon sign in. Bring a pen/pencil and extra notepaper.";
$body .= "\n\nAt the end of the session, you will be given an Attendance Receipt.";
$body .= "\n\nAlberta Children's Hospital website:";
$body .= "\nhttp://www.albertahealthservices.ca/Facilities/ACH/";
$body .= "\n\nParking Options, If the session is at Alberta Children's Hospital:";
$body .= "\nhttp://www.albertahealthservices.ca/Facilities/ACH/page55.asp";
$body .= "\n\nSessions with low registration may be cancelled within 48hrs.";
$body .= "\n\n*** To remove yourself from this session, login and go to Your Courses:*** http://community.hmhc.ca/login/";
$body .= "\n\nThank you for your ongoing interest and support of our program. Please share our homepage link with anyone you feel would benefit from CES' programming. http://community.hmhc.ca/.";
}
$return_code = fcrc_send_email($email, $subject, $body);
if($return_code == 1){
//email sent successfully
//2012-02-04 - wsopko - only output message if person was not on the waiting list. Don't want to output this message when a user removes themselves from a session, and another user is automatically registered from the waiting list
if($already_on_waiting_list == '0'){ //user was not on the waiting list
$result = "<p class='alert alert-success'>Thank you for for registering. A confirmation email has been sent to <strong>" . $email."</strong></p>";
}//if
elseif(is_admin_logged_in()){
$result = "<p class='alert alert-success'><strong>".$email."</strong> Has been registered from the waiting list. A confirmation email has been sent</p>";
}
}//if
else{
echo "<p class='alert alert-error'>Confirmation email to " . $email . " could not be sent.</p>";
echo "<p class='alert alert-error'>Mailer Error: " . $mail->ErrorInfo . "</p>";
exit;
}//else
}//if
else{ //no email sent since person was registered after course started
if($session_type === "webex"){
$result = "<p class='alert alert-success'>Thank for for registering. A confirmation email has been sent.</p>";
}
else{
$result = "<p class='alert alert-success'>Thank for for registering. No confirmation email sent since registration occurred after session started.</p>";
}
}//else
}//while
} //if
else { //else the update did not happen so output an error
print_sql_error('Error - registration failed when doing:', $sql);
}//end else
//return true success, false on fail.
return $result;
}//register_person_in_session()
function print_sql_error($error_message, $sql){
global $db; //connection to database already established
echo "<div class='alert alert-error'><br>" . $error_message . "<br>" . $sql . "<br>" . mysqli_error($db) . "</div>";
//2012-02-04 - wsopko - log the event to event_log table
log_event("print_sql_error", $error_message . " SQL: " . $sql);
}//print_sql_error()
function return_sql_error($error_message, $sql){
global $db; //connection to database already established
$error = "<div class='alert alert-error'>" . $error_message . "<br>" . $sql . "<br>" . mysqli_error($db) . "</div>";
//2012-02-04 - wsopko - log the event to event_log table
log_event("print_sql_error", $error_message . " SQL: " . $sql);
return $error;
}//retunr_sql_error()
//function takes a string as input and returns the same string with new line characters "\n" converted to HTML newline characters "<br>"
function convert_line_returns($input_string){
return str_replace("\n", "<br>", $input_string);
}//convert_line_returns()
//function returns how many mailing lists a user is on
function num_mailing_lists($id){
global $db; //connection to database already established
$mailing_list_sql = mysqli_query($db, "SELECT COUNT(*) FROM phplist_listuser WHERE userid = '$id'");
$num_lists = mysqli_fetch_row($mailing_list_sql);
return $num_lists[0];
}//num_mailing_lists()
//function returns true if session if full, false otherwise
function is_session_full($session_id, $location_id){
global $db; //connection to database already established
$session_type = session_type($session_id);
if ($session_type == 'online' || $session_type == 'webex'){
return false;
}
//find out how many seats are available in the session
$location_id = get_session_location_id($session_id);
$seats_sql = mysqli_query($db, "SELECT seats FROM ces_locations WHERE location_id = '$location_id'");
$num_seats = mysqli_fetch_row($seats_sql);
//2015-07-31 - nwmosses - account for users who signed up for video or audio sessions
//find out how many people are registered in the session
//$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id' AND location_id = '$location_id' AND on_waiting_list <> 1");
$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id' AND location_id = '$location_id' AND on_waiting_list <> 1 AND via_video <> 1 AND via_audio <> 1");
$num_attendees = mysqli_fetch_row($attendees_sql);
//find out if session if full or not
//2011-05-23 - wsopko - take guests attending into account
//if($num_attendees[0] < $num_seats[0]){
//for debugging
//echo "num_attendees=" . ($num_attendees[0] + $num_attendees[1]);
//echo "num_seats=" . $num_seats[0];
if(($num_attendees[0] + $num_attendees[1]) < $num_seats[0]){
return false;
}//if
else{
return true;
}//else
}//is_session_full()
//print report in table format based on SQL in $sql
//2015-07-13 - nwmosses - update to work with mysqli
function generate_report($sql_query, $title, $border, $width, $column_names){
global $db; //connection to database already established
$sql = mysqli_query($db, $sql_query);
//Calculate how many fields are there in supplied resource and store their name in $this->fields[] array
$field_count = mysqli_field_count($db);
$i = 0;
//Now start table generation - draw table according to number of fields
echo "\n<h5>". $title . "</h5>";
echo "\n<table class='table table-striped'>";
echo "\n<thead>";
echo "\n<tr>";
//Header Draw
/* 2007-12-09 - wsopko - had to change for apache seg fault bug
for ($i = 0; $i< $field_count; $i++){
//Now Draw Headers
echo "\n<td class=reportTableHeading>" .$this->fields[$i] . "</td>";
}//for
*/
foreach($column_names as $name){
//Now Draw Headers
echo "\n\t<th>" . $name . "</th>";
}//foreach
echo "\n\t</tr>";
echo "\n\t</thead>";
echo "\n\t<tbody>";
//Now fill the table with data
while ($rows = mysqli_fetch_row($sql)){
//2011-09-04 - wsopko - added table striping
echo "\n\t<tr>";
for ($i = 0; $i < $field_count; $i++)
{
//Now Draw Data
echo "\n\t<td>" . $rows[$i] . "</td>";
}//for
echo "\n\t</tr>";
}//while
echo "\n</tbody>";
echo "\n</table>";
}//generate_report()
//check if session is authenticated, redirect otherwise
function check_if_authenticated($user_type){
session_start();
if($user_type =='user' || ((isset($_GET['user_type']) && $_GET['user_type'] == 'user') || (isset($_POST['user_type']) && $_POST['user_type'] == 'user'))){
if(!is_user_logged_in()){
header("location:/login/");
exit;
}//if
}
elseif($user_type =='admin' || ((isset($_GET['user_type']) && $_GET['user_type'] == 'admin') || (isset($_POST['user_type']) && $_POST['user_type'] == 'admin'))){
if(!is_admin_logged_in()){
header("location:/admin/");
exit;
}//if
}
}
//universal session_status check
function is_session_started()
{
if ( php_sapi_name() !== 'cli' ) {
if ( version_compare(phpversion(), '5.4.0', '>=') ) {
return session_status() === PHP_SESSION_ACTIVE ? TRUE : FALSE;
} else {
return session_id() === '' ? FALSE : TRUE;
}
}
return FALSE;
}
//check if the current user is logged in as an admin
function is_admin_logged_in(){
//if (session_status() == PHP_SESSION_NONE) {
if (!is_session_started()) {
session_start();
}
if(isset($_SESSION['adminusername']) && $_SESSION["adminloggedin"] == $_SERVER["REMOTE_ADDR"]){
return true;
}//if
else{
return false;
}//else
}//is_admin_logged_in()
//check if the current user is a normal user (not an admin)
function is_user_logged_in(){
if(isset($_SESSION['userid']) && $_SESSION["userloggedin"] == $_SERVER["REMOTE_ADDR"]){
return true;
}//if
else{
return false;
}//else
}//is_user_logged_in()
//make sure email address is in valid format
function validate_email($email){
if(!empty($email)){
$pattern = "/^([a-zA-Z0-9])+([\.a-zA-Z0-9_-])*@([a-zA-Z0-9_-])+(\.[a-zA-Z0-9_-]+)+/";
if(preg_match($pattern, $email)){
return true;
}//if
else{
return false;
}//else
}//if
else{
return false;
}//else
}//validate_email()
//make sure postal code is in valid format (A#A#A#)
//No postal code includes the letters D, F, I, O, Q, or U. The letters W and Z are used, but are not currently used as the first letter
function validate_postal_code($postal_code){
$postal_code = strtoupper($postal_code);
if(!empty($postal_code)){
$pattern = "/^[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVXWYZ][0-9][ABCEGHJKLMNPRSTVXWYZ][0-9]/";
if(preg_match($pattern, $postal_code)){
return true;
}//if
else{
return false;
}//else
}//if
else{
return false;
}//else
}//validate_postal_code()
//converts a timestamp from the local time to GMT
//$time_stamp needs to be in unix timestamp format (seconds since the epoch)
function convert_local_to_GMT($time_stamp){
global $timezone_offset; //set in config.php
//return $time_stamp - $timezone_offset; //convert date to GMT
return $time_stamp; //timezone set in php, no need to convert
}//convert_local_to_GMT()
//converts a timestamp from the system time to GMT
//$time_stamp needs to be in unix timestamp format (seconds since the epoch)
function convert_system_to_GMT($time_stamp){
//return $time_stamp - date('Z'); //convert to GMT since system timezone could be different than registration system timezone
return time(); //timezone is now set properly in no need to convert it
}//convert_system_to_GMT()
//sends an email using the PHPMailser class
//made function for this as need to get some common variables (from email address, reply to email address etc. for all system generated emails
function fcrc_send_email($email_to, $email_subject, $email_body){
global $db; //connection to database already established
//2015-08-21 - nwmosses - PHPlist changed their PHPMailer class with the newest version, using old version to avoid changes
if(defined('STDIN')){ //script is running from the command line so document root is different
//require_once($_SERVER['OLDPWD'] . "/web/phplist/admin/phpmailer/class.phpmailer.php");
require_once($_SERVER['OLDPWD'] . "/phplist/admin/oldmailer/phpmailer/class.phpmailer.php");
}//if
else{ //script running from web browser
//require_once($_SERVER['DOCUMENT_ROOT'] . "/phplist/admin/phpmailer/class.phpmailer.php");
require_once($_SERVER['DOCUMENT_ROOT'] . "/phplist/admin/oldmailer/phpmailer/class.phpmailer.php");
}//else
$mail = new PHPMailer();
$mail->IsSMTP(); // set mailer to use SMTP
$mail->Host = PHPMAILERHOST; // specify main and backup server
$mail->SMTPAuth = false; // turn on SMTP authentication
//$mail->Username = "phplist_bounce@sacyhn.ca"; // SMTP username
//$mail->Password = "PHPlist2007"; // SMTP password
//get the domain from the phplist setup - in case phplist has been setup with [DOMAIN] placeholders in the phplist config
$domain_sql = mysqli_query($db, "SELECT value FROM phplist_config WHERE item = 'domain'");
$domain = mysqli_fetch_row($domain_sql);
//get the from address from the phplist setup
$from_sql = mysqli_query($db, "SELECT value FROM phplist_config WHERE item = 'message_from_address'");
$from = mysqli_fetch_row($from_sql);
//set from email address to the same value that is defined in phplist config
//in case the [DOMAIN] placeholder has been used in phplist config, replace it with the $domain from the phplist config
$mail->From = preg_replace('/\[DOMAIN\]/i', $domain[0], $from[0]);
$from_name_sql = mysqli_query($db, "SELECT value FROM phplist_config WHERE item = 'message_from_name'");
$from_name = mysqli_fetch_row($from_name_sql); //set from name to the same value that is defined in phplist config
$mail->FromName = $from_name[0];
$mail->AddAddress($email_to);
$reply_to_sql = mysqli_query($db, "SELECT value FROM phplist_config WHERE item = 'message_replyto_address'");
$reply_to = mysqli_fetch_row($reply_to_sql);
//set reply to email address to the same value that is defined in phplist config
//in case the [DOMAIN] placeholder has been used in phplist config, replace it with the $domain from the phplist config
$mail->AddReplyTo(preg_replace('/\[DOMAIN\]/i', $domain[0], $reply_to[0]));
$mail->IsHTML(false); // set email format to HTML if user has defined that in their phplist preferences
$mail->Subject = $email_subject;
//common footer to include in all system emails
$body_footer = "\n\n--\nCommunity Education Service (CES)\nAlberta Health Services\nemail: ces@ahs.ca\nphone: 403-955-4730\nfax: 403-955-8184\n";
$mail->Body = $email_body . $body_footer;
//$mail->AltBody = $body;
//2015-07-21 - nwmosses - send mail error on test server, halts script with no error messages
if($mail->Send()){
return 1;
}//if
else{
return $mail->ErrorInfo;
}
}//fcrc_send_email()
//2011-05-23 - wsopko - added function to output row class based on if the parameter is odd or even - for row strping
function output_row_stripe($row_count){
if($row_count % 2 == 0){
return "courseTableRowEven";
}//if
else{
return "courseTableRowOdd";
}//else
}//ouput_row_stripe()
//sanitize input for use in database queries
function check_input($value){
global $db; //connection to database already established
// Stripslashes
if (get_magic_quotes_gpc()){
$value = stripslashes($value);
}//
// Quote if not a number
if (!is_numeric($value)){
$value = "'" . mysqli_real_escape_string($db, $value) . "'";
}//if
return $value;
}//check_input()
//output a HTML link to a google map with a given location shown
function output_map_link($location_id){
global $db; //connection to database already established
$location_sql = mysqli_query($db, "SELECT location.location_name, location.address_line1, location.address_line2, location.city, location.province, location.postal_code
FROM ces_locations location
WHERE location.location_id = '$location_id'");
while ($location_query = mysqli_fetch_array($location_sql)){
echo "<a target='_blank' href='http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=" . urlencode($location_query['address_line1'] . " " . $location_query['address_line2'] . " " . $location_query['city'] . " " . $location_query['province']) . "' title='". $location_query['address_line1'] . " " . $location_query['address_line2'] . " " . $location_query['city'] . " " . $location_query['province'] ."'>" . $location_query['location_name'] . "</a>";
}//while
}//output_map_link
//2011-08-27 - wsopko - added function to output all sessions a user is registered in
function output_registered_sessions($user_id){
global $db; //connection to database already established
global $default_date_format; //already set in config.php
//find out if person is registered in any current sessions
$registered_courses_sql = mysqli_query($db, "SELECT COUNT(*)
FROM ces_course_registration registration
LEFT OUTER JOIN ces_sessions session ON session.session_id = registration.session_id
WHERE registration.id = '$user_id'
AND session.end_dt >= now()");
$num_courses = mysqli_fetch_row($registered_courses_sql);
if ($num_courses[0] > 0){ //person is registered in at least one current session, so output it
$session_sql = mysqli_query($db, "SELECT course.course_id, session.session_id, session.url, course.course_name, course.course_description, location.location_id, location.location_name, unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt, registration.on_waiting_list, registration.num_guests_attending, session.telehealth, session.webex_url, session.webex_password
FROM ces_course_registration registration
LEFT OUTER JOIN ces_sessions session ON session.session_id = registration.session_id
LEFT OUTER JOIN ces_courses course ON course.course_id = session.course_id
LEFT OUTER JOIN ces_locations location ON location.location_id = registration.location_id
WHERE registration.id = '$user_id'
AND session.end_dt >= now()
ORDER BY session.start_dt, session.end_dt, course.course_name, location.location_name");
while ($session_query = mysqli_fetch_array($session_sql)) { //output the session information - one session per table row
$session_type = session_type($session_query['session_id']);
//$online = is_session_online($session_query['session_id']);
//replace 12:00 PM with Noon
$start_time = date('g:i A', $session_query["start_dt"]);
if ($start_time == "12:00 PM"){
$start_time = "Noon";
}
$end_time = date('g:i A', $session_query["end_dt"]);
if ($end_time == "12:00 PM"){
$end_time = "Noon";
}
$session_user_id = "&id=";
if (isset($_SESSION["userid"])){ $session_user_id = '&id='.$_SESSION["userid"];}
echo "<section class='presentation ces-presentation'>\n";
echo " <div class='ces-session-left'>\n";
echo " <div class='date'>\n";
echo " <span class='day'>" . date('d', $session_query["start_dt"]) . "</span>\n";
echo " <span class='month'>" . date('M', $session_query["start_dt"]) . "</span>\n";
echo " <span class='year'>" . date('Y', $session_query["start_dt"]) . "</span>\n";
echo " </div>\n";
echo " </div>\n";
echo " <div class='info ces-session-center'>\n";
echo " <h3>". $session_query["course_name"]."</h3>\n";
if($session_type === "recorded")
echo " <p class='ces-course-time'>Avalible until: ". date('F j, Y', $session_query["end_dt"]) ." - ".$end_time. "</p>\n";
else
echo " <p class='ces-course-time'>". $start_time . " - ". $end_time . "</p>\n";
if($session_type != "recorded" && $session_type != "webex"){
echo " <p class='ces-course-location'>";
output_map_link($session_query["location_id"]);
echo " </p>\n";
}
echo " <p class='ces-course-description'>". $session_query["course_description"] ."</p>\n";
//Instructions
echo " <div style='margin-top:10px;'>";
echo registration_type($user_id, $session_query['session_id']);
//links
if($session_type != "webex"){
echo "<a class='btn btn-lg btn-primary ces-orange-btn' href='../unregister/?session_id=" . $session_query["session_id"] . "&id=" . $user_id . "&location_id=" . $session_query["location_id"] . "&user_type=user'><i class='fa fa-minus-square' aria-hidden=true'></i> Unregister</a>";
}
if(is_session_cancelled($session_query['session_id'])){
echo " <span class='btn ces-red-btn' style='cursor:default;'><i class='fa fa-calendar-times-o' aria-hidden=true'></i> Session Cancelled</span>\n";
}
if($session_query["on_waiting_list"] == '1'){
$waitlist_position = waitlist_position($user_id, $session_query['session_id']);
echo "<div class='btn ces-green-btn' style='cursor:default; title='Position on Waiting List'><i class='fa fa-list-ul' aria-hidden=true'></i> Waitlist Position - ".$waitlist_position. "</div>";
}
echo "</div>";
//online link
if(!is_session_cancelled($session_query['session_id'])){
if($session_type == "recorded" && $session_query["start_dt"] < time()){
echo " <p class='ces-session-links'><a class='btn btn-lg ces-green-btn' href='../../sessions/recorded/?id=".$session_query['session_id']."'><i class='fa fa-play fa-fw'></i> View Recorded Session</a></p>\n";
}elseif($session_type == "webex"){
echo " <p class='ces-session-links'><a href='".$session_query['webex_url']."' title='".$session_query['webex_url']."' target='blank'>Click here</a> to join the online event.</p>\n";
echo " <p class='ces-session-links'>Event password: <strong>".$session_query['webex_password']."</strong></p>\n";
}
}
echo " <p class='ces-session-files'>";
output_session_files($session_query['session_id'], "<br>");
echo " </p>\n";
echo " </div>\n";
echo " <div>\n";
echo " </div>\n";
echo "</section>\n\n";
}//while
}//if
else{ //no registered sessions
echo "<p class='alert alert-info'>No sessions found.</p>";
}//else
}//output_registered_sessions()
function output_past_registered_sessions($user_id){
global $db; //connection to database already established
global $default_date_format; //already set in config.php
//find out if person was registered in any past sessions
$past_registered_courses_sql = mysqli_query($db, "SELECT COUNT(*)
FROM ces_course_registration registration
LEFT OUTER JOIN ces_sessions session ON session.session_id = registration.session_id
WHERE registration.id = '$user_id'
AND session.end_dt < now()");
$num_courses = mysqli_fetch_row($past_registered_courses_sql);
if ($num_courses[0] > 0){ //person aws registered in at least one past session, so output it
$session_sql = mysqli_query($db, "SELECT course.course_id, session.session_id, course.course_name, location.location_id, location.location_name, unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt, registration.on_waiting_list, registration.num_guests_attending, session.telehealth, course.course_description
FROM ces_course_registration registration
LEFT OUTER JOIN ces_sessions session ON session.session_id = registration.session_id
LEFT OUTER JOIN ces_courses course ON course.course_id = session.course_id
LEFT OUTER JOIN ces_locations location ON location.location_id = registration.location_id
WHERE registration.id = '$user_id'
AND session.end_dt < now()
ORDER BY session.start_dt DESC, session.end_dt, course.course_name, location.location_name");
while ($session_query = mysqli_fetch_array($session_sql)) { //output the session information - one session per table row
//replace 12:00 PM with Noon
$start_time = date('g:i A', $session_query["start_dt"]);
if ($start_time == "12:00 PM"){
$start_time = "Noon";
}
$end_time = date('g:i A', $session_query["end_dt"]);
if ($end_time == "12:00 PM"){
$end_time = "Noon";
}
$session_user_id = "&id=";
if (isset($_SESSION["userid"])){ $session_user_id = '&id='.$_SESSION["userid"];}
echo "<section class='presentation ces-presentation'>\n";
echo " <div class='ces-session-left'>\n";
echo " <div class='date'>\n";
echo " <span class='day'>" . date('d', $session_query["start_dt"]) . "</span>\n";
echo " <span class='month'>" . date('M', $session_query["start_dt"]) . "</span>\n";
echo " <span class='year'>" . date('Y', $session_query["start_dt"]) . "</span>\n";
echo " </div>\n";
echo " </div>\n";
echo " <div class='info ces-session-center'>\n";
echo " <h3>". $session_query["course_name"];
echo "</h3>\n";
if(!is_session_online($session_query['session_id'])){
echo " <p class='ces-course-time'>". $start_time . " - ". $end_time . "</p>\n";
echo " <p class='ces-course-location'>";
output_map_link($session_query["location_id"]);
echo " </p>\n";
}
echo " <p class='ces-course-description'>". $session_query["course_description"] ."</p>\n";
echo " <p class='ces-session-files'>";
output_session_files_past($session_query['session_id'], "<br>");
echo "</p>\n";
echo "</div>\n";
echo " <div class='ces-session-right'>\n";
echo " </div>\n";
echo "</section>\n\n";
}//while
}//if
else{ //no registered sessions
echo "<p class='alert alert-info'>No past sessions found.</p>";
}//else
}//output_past_registered_sessions()
//2011-09-14 - wsopko - created function to output a link to each file that is associated with a session
//only output link to file if session is within 48 hours of starting, and 48 hours after finishing
//seperator parameter is an optional string that will be output after each link
function output_session_files($session_id, $seperator = ''){
global $db; //connection to database already established
//if display_immediately is true, show the file as long as the end of the session is not more than 48 hours ago
//if display_immediately is false, show the file as long as the session starts less than 48 hours from now, and ended not more than 48 hours ago
$file_sql = mysqli_query($db, "SELECT filename, file_description
FROM ces_sessions session
LEFT OUTER JOIN ces_files_to_sessions fts ON fts.session_id = session.session_id
WHERE session.session_id = '$session_id'
AND ((fts.display_immediately = 0 AND DATE_ADD(session.end_dt, INTERVAL 48 HOUR) >= now() AND DATE_SUB(session.start_dt, INTERVAL 48 HOUR) <= now())
OR (fts.display_immediately = 1 AND DATE_ADD(session.end_dt, INTERVAL 48 HOUR) >= now()))
ORDER BY fts.display_immediately desc, file_description");
while ($file_query = mysqli_fetch_array($file_sql)) {
//2009-01-21 - wsopko - bug fix - if special characters in filename, HTML link not working - addslashes() was already added to edit_session.php
//echo " <a href=\"" . $file_query['filename'] . "\" target=_new>" . $file_query['file_description'] . "</a><br>";
echo "<a href=\"/sessions/files/" . addslashes($file_query['filename']) . "\" target=_blank>" . $file_query['file_description'] . "</a>" . $seperator;
}//while
}//output_session_files()
function output_session_files_past($session_id, $seperator = ''){
global $db; //connection to database already established
$file_sql = mysqli_query($db, "SELECT filename, file_description
FROM ces_sessions session
LEFT OUTER JOIN ces_files_to_sessions fts ON fts.session_id = session.session_id
WHERE session.session_id = '$session_id'
ORDER BY fts.display_immediately desc, file_description");
while ($file_query = mysqli_fetch_array($file_sql)) {
//2009-01-21 - wsopko - bug fix - if special characters in filename, HTML link not working - addslashes() was already added to edit_session.php
//echo " <a href=\"" . $file_query['filename'] . "\" target=_new>" . $file_query['file_description'] . "</a><br>";
echo "<a href=\"" . addslashes($file_query['filename']) . "\" target=_blank>" . $file_query['file_description'] . "</a>" . $seperator;
}//while
}//output_session_files()
//2012-02-04 - wsopko - created function to log events in the system
//added this in response to question of why some users received a confirmation email stating they were registered in a session, but they didn't show up on registration list
function log_event($event_type = "unknown", $event_text = ""){
global $db; //connection to database already established
global $web_user_email_address; //defined in config.php
global $database; //defined in config.php
if(is_user_logged_in()){
//user trying to access admin page, so check if admin is already logged in, if not send to admin login page
$user_type = "user";
$user_name = $_SESSION["email"];
$user_id = $_SESSION["userid"];
$ip_address = "'" . $_SESSION["userloggedin"] . "'";
}//if
elseif(is_admin_logged_in()){//user trying to access admin page, so check if admin is already logged in, if not send to admin login page
$user_type = "admin";
$user_name = $_SESSION["adminusername"];
$user_id = $_SESSION["adminuserid"];
$ip_address = "'" . $_SESSION["adminloggedin"] . "'";
}//elseif
else{ //no one is logged in
$user_type = "not logged in";
$user_name = "not logged in";;
$user_id = 0;
$ip_address = "NULL";
}//else
$event_text = "username " . $user_name . " " . $event_text; //add the friendly user name to the log for easier identification
$sql = "INSERT INTO ces_event_log (event_dt, user_type, user_id, ip_address, event_type, event_text) VALUES (now(), '$user_type', $user_id, $ip_address, '$event_type', '$event_text')";
if(!mysqli_query($db, $sql)){ //if the log entry could not be created, send email to web user to notify
$body = "A problem occurred while trying to write to the event log";
$body .= "\n\r\n\r" . "user_type: " . $user_type;
$body .= "\n\r" . "user_id: " . $user_id;
$body .= "\n\r" . "ip_address: " . $ip_address;
$body .= "\n\r" . "event_type: " . $event_type;
$body .= "\n\r" . "event_text: " . $event_text;
fcrc_send_email($web_user_email_address, "$database - problem - event log", $body);
}//if
}//log_event()
function waitlist_position($userid, $session_id){
global $db; //connection to database already established
$waitlist_sql = mysqli_query($db, "SELECT id, registration_dt FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list = 1 AND via_video <> 1 AND via_audio <> 1 ORDER BY registration_dt");
$waitlist_position = 0;
while($file_query = mysqli_fetch_array($waitlist_sql)){
$waitlist_position += 1;
if ($file_query['id'] == $userid){
break;
}
}
return $waitlist_position;
}
function registration_type($user_id, $session_id){
global $db; //connection to database already established
$registration_type_sql = mysqli_query($db, "SELECT via_video, via_audio FROM ces_course_registration WHERE session_id = '$session_id' AND id = '$user_id'");
$user_info = mysqli_fetch_row($registration_type_sql);
$registration_type = "";
if(session_type($session_id) === 'online'){
$registration_type = "<a href='../../sessions/instructions/online.php' class='btn ces-blue-btn' title='Online'><i class='fa fa-desktop fa-fw' aria-hidden='true'></i> Instructions</a>";
}
elseif(session_type($session_id) === 'webex'){
$registration_type = "<a href='../../sessions/instructions/webex.php' class='btn ces-blue-btn' title='Webex'><i class='fa fa-tablet fa-fw' aria-hidden='true'></i> Instructions</a>";
}
elseif ($user_info[0] == 1 && $user_info[1] == 0){
$registration_type = "<a href='../../sessions/instructions/telehealth.php' class='btn ces-blue-btn' title='Via Video'><i class='fa fa-video-camera fa-fw' aria-hidden='true'></i> Instructions</a>";
;
}elseif ($user_info[0] == 0 && $user_info[1] == 1) {
$registration_type = "<a href='../../sessions/instructions/audioline.php' class='btn ces-blue-btn' title='Via Audio'><i class='fa fa-phone fa-fw' aria-hidden='true'></i> Instructions</a>";
}else{
$registration_type = "<a href='../../sessions/instructions/inperson.php' class='btn ces-blue-btn' title='In Person'><i class='fa fa-users fa-fw' aria-hidden='true'></i> Instructions</a>";
}
return $registration_type;
}
//2015-07-31 - nwmosses - added some stat functions -
function registered_users($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id'");
$row = mysqli_fetch_row($attendees_sql);
return $row[0];
}
function registered_seats($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list <> 1 AND via_video <> 1 AND via_audio <> 1");
$row = mysqli_fetch_row($attendees_sql);
return $row[0];
}
function registered_guests($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list <> 1 AND via_video <> 1 AND via_audio <> 1");
$row = mysqli_fetch_row($attendees_sql);
return $row[1];
}
function registered_waitlist($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*), SUM(num_guests_attending) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list = 1 AND via_video <> 1 AND via_audio <> 1");
$attendees = mysqli_fetch_row($attendees_sql);
return ($attendees[0] + $attendees[1]);
}
function registered_video($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list <> 1 AND via_video = 1 AND via_audio <> 1");
$row = mysqli_fetch_row($attendees_sql);
return $row[0];
}
function registered_audio($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT COUNT(*) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list <> 1 AND via_video <> 1 AND via_audio = 1");
$row = mysqli_fetch_row($attendees_sql);
return $row[0];
}
function attended_session($session_id){
global $db; //connection to database already established
$attendees_sql = mysqli_query($db, "SELECT SUM(confirm_attended) FROM ces_course_registration WHERE session_id = '$session_id' AND on_waiting_list <> 1 AND via_video <> 1 AND via_audio <> 1");
$row = mysqli_fetch_row($attendees_sql);
return $row[0];
}
function instructors_sessions($instructor_id){
global $db; //connection to database already established
$sql = mysqli_query($db, "SELECT COUNT(*) FROM ces_instructors_to_sessions WHERE instructor_id = '$instructor_id'");
$row = mysqli_fetch_row($sql);
return $row[0];
}
function location_sessions($id){
global $db; //connection to database already established
$sql = mysqli_query($db, "SELECT COUNT(*) FROM ces_sessions WHERE location_id = '$id'");
$row = mysqli_fetch_row($sql);
return $row[0];
}
function file_count($session_id){
global $db;
$file_sql = mysqli_query($db, "SELECT session_id FROM ces_files_to_sessions WHERE session_id = '$session_id'");
return mysqli_num_rows($file_sql);
}
function human_filesize($bytes, $decimals = 2) {
$sz = 'BKMGTP';
$factor = floor((strlen($bytes) - 1) / 3);
return sprintf("%.{$decimals}f ", $bytes / pow(1024, $factor)) . @$sz[$factor];
}
function get_users_email($user_id){
global $db;
$email_sql = "SELECT email FROM phplist_user_user WHERE id = '$user_id'";
$result = mysqli_query($db, $email_sql);
$row = mysqli_fetch_row($result);
return $row[0];
}
function get_course_name($session_id){
global $db;
$name_sql = "SELECT course_name FROM ces_courses
LEFT OUTER JOIN ces_sessions ON ces_sessions.course_id = ces_courses.course_id
WHERE ces_sessions.session_id = '$session_id'";
$result = mysqli_query($db, $name_sql);
$row = mysqli_fetch_row($result);
return $row[0];
}
function get_course_id($session_id){
global $db;
$sql = "SELECT course_id FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
return $row[0];
}
function get_session_location_id($session_id){
global $db;
$sql = "SELECT location_id FROM ces_sessions WHERE session_id = '$session_id'";
$location_id = mysqli_query($db, $sql);
$row = mysqli_fetch_row($location_id);
return $row[0];
}
function get_session_time($session_id, $start_or_end){
global $db;
global $default_date_format;
$session_sql = mysqli_query($db, "SELECT unix_timestamp(start_dt) start_dt, unix_timestamp(end_dt) end_dt FROM ces_sessions WHERE session_id = '$session_id'");
$time_query = mysqli_fetch_row($session_sql);
if($start_or_end == 'start'){
return date($default_date_format, $time_query[0]);
}
elseif($start_or_end == 'end'){
return date($default_date_format, $time_query[1]);
}
}
function output_location_address($location_id, $indent = ""){
global $db;
$sql = mysqli_query($db, "SELECT location_name, address_line1, address_line2, city, province, postal_code FROM ces_locations location WHERE location_id = '$location_id'");
$location_query = mysqli_fetch_array($sql);
$location .= $indent . stripslashes($location_query['location_name']);
if (strlen($location_query['address_line1']) > 0)
$location .= "\n".$indent . stripslashes($location_query['address_line1']);
if (strlen($location_query['address_line2']) > 0)
$location .= "\n".$indent . stripslashes($location_query['address_line2']);
if (strlen($location_query['city']) > 0)
$location .= "\n".$indent . stripslashes($location_query['city']);
if (strlen($location_query['province']) > 0)
$location .= ", " . stripslashes($location_query['province']);
if (strlen($location_query['postal_code']) > 0)
$location .= "\n".$indent . stripslashes($location_query['postal_code']);
return $location;
}
function remove_attendee($session_id, $user_id, $location_id, $reason = 'user'){
global $db;
$result = false;
$delete_sql = "DELETE FROM ces_course_registration WHERE session_id = '$session_id' AND id = '$user_id'";
if(mysqli_query($db, $delete_sql)){ //if we were able to delete the attendee from the session
$result = true;
//send email
$email = get_users_email($user_id);
$subject = "CES - Session Removal - ". get_course_name($session_id);
$body = "As requested, you have been removed from the following session:\n";
$body .= "\nCourse Name: ". get_course_name($session_id);
$body .= "\nCourse Start: ". get_session_time($session_id, 'start');
$body .= "\nCourse End: ". get_session_time($session_id, 'end');
if(!is_session_online($session_id))
$body .= "\n\nLocation:\n". output_location_address(get_session_location_id($session_id), "\t");
$body .= "\n";
if($reason != 'cancel'){
$return_code = fcrc_send_email($email, $subject, $body);
if($return_code == 1){
//email sent successfully
}//if
else{
echo "<p class='alert alert-error'>Email to " . $email . " could not be sent.</p>";
echo "<p class='alert alert-error'>Mailer Error: " . $mail->ErrorInfo . "</p>";
exit;
}//else
}
log_event("remove attendee", "removed user ID $_POST[id] from session ID $_POST[session_id]");
$waitlist_result = check_waitlist($session_id, $location_id);
if(is_admin_logged_in())
echo $waitlist_result;
}else{
$result = false;
}
return $result;
}//remove_attendee
function check_waitlist($session_id, $location_id){
global $db;
$result = '';
while(!is_session_full(check_input($session_id), check_input($location_id)) && (registered_waitlist($session_id) > 0) ){
//find out if there is anyone on the waiting list for this session, if yes, then register the first person (by registration_dt) on the waiting list in the session
$waiting_list_sql = mysqli_query($db, "SELECT reg.id, reg.location_id FROM ces_course_registration reg
WHERE reg.session_id = '$session_id'
AND reg.on_waiting_list = '1'
AND reg.registration_dt = (SELECT MIN(registration_dt) FROM ces_course_registration WHERE session_id = reg.session_id AND on_waiting_list = reg.on_waiting_list)");
$waiting_list_attendee = mysqli_fetch_row($waiting_list_sql);
if ($waiting_list_attendee[0] > 0){ //we found someone on the waiting list for this session, so register them
$result .= register_person_in_session($session_id, $waiting_list_attendee[0], $waiting_list_attendee[1], '0', '1');
}//if
}
return $result;
}
function updated_capacity($location_id){
global $db;
$return = "";
$sql = mysqli_query($db, "SELECT session_id FROM ces_sessions WHERE location_id ='$location_id'");
while($result = mysqli_fetch_array($sql)){
$return .= check_waitlist($result["session_id"], $location_id);
}
return $return;
}
function is_cancelled($session_id){
global $db;
$sql = mysqli_query($db, "SELECT COUNT(*) FROM ces_sessions WHERE session_id = '$session_id' AND cancelled = 1");
$result = mysqli_fetch_row($sql);
if($result[0] == 0) {
return false;
}else{
return true;
}
}//is_cancelled()
//2015-09-30 - nwmosses - old version of cancel session - removes all registered users and emails them
// - this proved to be too powerful, a less final approach was requested
function cancel_session_remove_all($session_id){
global $db;
global $default_date_format;
$result = 1;
$error = 0;
$body ="Please note that the following session has been CANCELLED:";
if(mysqli_query($db, "UPDATE ces_sessions SET cancelled=1 WHERE session_id = '$session_id'")){
//get session info
$session_sql = mysqli_query($db, "SELECT course.course_id, course.course_name, course.course_description, unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt
FROM ces_sessions session
LEFT OUTER JOIN ces_courses course ON session.course_id = course.course_id
WHERE session.session_id = '$session_id'");
if($session_query = mysqli_fetch_array($session_sql)){ //course details, should only be one row
$body .= "\n\nCourse Name: " . stripslashes($session_query['course_name']);
$body .= "\nCourse Start: " . date($default_date_format, $session_query['start_dt']);
$body .= "\nCourse End: " . date($default_date_format, $session_query['end_dt']);
}
$body .= "\n\nLocation: \n".output_location_address(get_session_location_id($session_id), "\t");
$body .= "\n\nThis session may be offered again.";
$body .= "\n\nPlease watch the website:";
$body .= "\nhttp://community.hmhc.ca/sessions/current/";
$body .= "\n\nOr the newsletter:";
$body .= "\nhttp://community.hmhc.ca/newsletter/";
$subject = "CES - Session Cancellation - ".$session_query['course_name'];
//get user info, cancel sessions and email users
$attendee_sql = mysqli_query($db, "SELECT registration.session_id, registration.id, location.location_id, location.location_name, 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'
ORDER BY registration_dt, last_name, first_name");
while ($attendee_query = mysqli_fetch_array($attendee_sql)) {
remove_attendee($session_id, $attendee_query['id'], $attendee_query['location_id'], 'cancel');
//$last_name = $attendee_query["last_name"];
//$first_name = $attendee_query["first_name"];
$email = $attendee_query["email"];
if(fcrc_send_email($email, $subject, $body)){
$result = 1;
}else{
//error
echo "<p class='alert alert-error'>Cancellation email to " . $email . " could not be sent.</p>";
//echo "<p class='alert alert-error'>Mailer Error: " . $mail->ErrorInfo . "</p>";
//exit;
$error = $error+1;
}
}
if($error > 0){
return 0;
}else{
return 1;
}
}//IF (update_query)
else{
return 1;
}
}//cancel_session_remove_all()
//2015-09-30 - nwmosses - marks the session as cancelled
function cancel_session($session_id){
global $db;
if(mysqli_query($db, "UPDATE ces_sessions SET cancelled=1 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//cancel_session()
//2015-09-30 - nwmosses - marks the session as not cancelled
function uncancel_session($session_id){
global $db;
if(mysqli_query($db, "UPDATE ces_sessions SET cancelled=0 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//uncancel_session()
function is_session_cancelled($session_id){
global $db;
$sql = "SELECT cancelled FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
//2015-09-30 - nwmosses - marks the session as closed
function lock_session($session_id){
global $db;
if(mysqli_query($db, "UPDATE ces_sessions SET open=0 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//close_session()
//2015-09-30 - nwmosses - marks the session as open
function open_session($session_id){
global $db;
if(mysqli_query($db, "UPDATE ces_sessions SET open=1 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//open_session()
function is_session_open($session_id){
global $db;
$sql = "SELECT open FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
//2015-09-30 - nwmosses - marks the session as hidden
function hide_session($session_id){
global $db;
$result = 1;
if(mysqli_query($db, "UPDATE ces_sessions SET display=0 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//hide_session()
//2015-09-30 - nwmosses - marks the session as hidden
function show_session($session_id){
global $db;
$result = 1;
if(mysqli_query($db, "UPDATE ces_sessions SET display=1 WHERE session_id = '$session_id'")){
return 1;
}//IF (update_query)
else{
return 0;
}
}//show_session()
function is_session_displayed($session_id){
global $db;
$sql = "SELECT display FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
function has_session_started($session_id, $specific_time = 0){
//$spcific_time should be a string of PHP Supported Date and Time Formats => http://php.net/manual/en/datetime.formats.php
global $db;
$session_sql = mysqli_query($db, "SELECT unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt
FROM ces_sessions session
WHERE session.session_id = '$session_id'");
$time_query = mysqli_fetch_row($session_sql);
$start_date_GMT = convert_local_to_GMT($time_query[0]); //convert session start date to GMT
if ($specific_time = 0){
//$current_date = getdate();
//$current_date_GMT = convert_system_to_GMT($current_date[0]); //convert current date to GMT since system timezone could be different than registration system timezone
$current_date_GMT = time(); //timezone is now set properly in no need to convert it
}else{
$current_date_GMT = convert_local_to_GMT(strtotime($specific_time));
}
if($start_date_GMT < $current_date_GMT){
return true;
}else{
return false;
}
}
function has_session_ended($session_id, $specific_time = 0){
//$spcific_time should be a string of PHP Supported Date and Time Formats => http://php.net/manual/en/datetime.formats.php
global $db;
$session_sql = mysqli_query($db, "SELECT unix_timestamp(session.start_dt) start_dt, unix_timestamp(session.end_dt) end_dt
FROM ces_sessions session
WHERE session.session_id = '$session_id'");
$time_query = mysqli_fetch_row($session_sql);
$end_date_GMT = convert_local_to_GMT($time_query[1]); //convert session start date to GMT
if ($specific_time == 0){
//$current_date = getdate();
//$current_date_GMT = convert_system_to_GMT($current_date[0]); //convert current date to GMT since system timezone could be different than registration system timezone
$current_date_GMT = time(); //timezone is now set properly in no need to convert it
}else{
$current_date_GMT = convert_local_to_GMT(strtotime($specific_time));
}
if($end_date_GMT < $current_date_GMT){
return true;
}else{
return false;
}
}
function is_session_online($session_id){
global $db;
$sql = "SELECT online FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
function is_session_telehealth($session_id){
global $db;
$sql = "SELECT telehealth FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
function is_session_webex($session_id){
global $db;
$sql = "SELECT webex FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0] == 1)
return true;
else
return false;
}
function session_type($session_id){
global $db;
if(is_session_telehealth($session_id)){
return "telehealth";
}
elseif(is_session_online($session_id)){
return "recorded";
}
elseif(is_session_webex($session_id)){
return "webex";
}
else{
return "inperson";
}
}
function get_session_link($session_id){
global $db;
$sql = "SELECT url FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
$link = $row[0];
return $link;
}
function get_webex_meeting_key($session_id){
global $db;
$sql = "SELECT webex_meeting_key FROM ces_sessions WHERE session_id = '$session_id'";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
$key = $row[0];
return $key;
}
function randomPassword() {
$alphabet = "abcdefghjkmnpqrstuwxyzABCDEFGHJKLMNPQRSTWXYZ23456789";
$pass = array(); //remember to declare $pass as an array
$alphaLength = strlen($alphabet) - 1; //put the length -1 in cache
for ($i = 0; $i < 8; $i++) {
$n = rand(0, $alphaLength);
$pass[] = $alphabet[$n];
}
return implode($pass); //turn the array into a string
}
function is_user_registered($user_id, $session_id){
global $db;
$sql = "SELECT registration_dt FROM ces_course_registration WHERE session_id = $session_id AND id = $user_id";
$result = mysqli_query($db, $sql);
$row = mysqli_fetch_row($result);
if ($row[0])
return true;
else
return false;
}
function session_icon($session_id){
global $db;
$class_options = 'fa fa-fw fa-lg';
$base_url = '/sessions/instructions';
$session_type = session_type($session_id);
echo "<div style='padding: 5px 0;'>";
if($session_type === "inperson" || $session_type === "telehealth")
echo "<a href='$base_url/inperson.php' title='In Person\nClick for more information.'><i class='$class_options fa-users blue' aria-hidden='true' ></i>";
if($session_type === "telehealth")
echo "<a href='$base_url/telehealth.php' title='Telehealth/Video-Conference Available\nClick for more information'><i class='$class_options fa-video-camera orange' aria-hidden='true' ></i></a>
<a href='$base_url/audioline.php' title='Audioline Available\nClick for more information.'><i class='$class_options fa-phone green' aria-hidden='true' ></i></a>";
if($session_type === "recorded")
echo "<a href='$base_url/recorded.php' title='Recorded\nClick for more information.'><i class='$class_options fa-desktop red' aria-hidden='true' ></i></a>\n";
if($session_type === "webex")
echo "<a href='$base_url/webex.php' title='WebEx\nClick for more information.'><i class='$class_options fa-tablet green' aria-hidden='true' ></i></a>\n";
echo "</div>";
}
function session_icon_for_title($session_id){
global $db;
$class_options = 'fa fa-fw';
$base_url = 'sessions/instructions';
$session_type = session_type($session_id);
echo "<span style='padding: 0 5px;'>";
if($session_type === "inperson" || $session_type === "telehealth")
echo "<a href='$base_url/inperson.php' title='In Person\nClick for more information.'><i class='$class_options fa-users blue' aria-hidden='true' ></i>";
if($session_type === "telehealth")
echo "<a href='$base_url/telehealth.php' title='Telehealth/Video-Conference Available\nClick for more information'><i class='$class_options fa-video-camera orange' aria-hidden='true' ></i></a>
<a href='$base_url/audioline.php' title='Audioline Available\nClick for more information.'><i class='$class_options fa-phone green' aria-hidden='true' ></i></a>";
if($session_type === "online")
echo "<a href='$base_url/online.php' title='Online\nClick for more information.'><i class='$class_options fa-desktop red' aria-hidden='true' ></i></a>\n";
if($session_type === "webex")
echo "<a href='$base_url/webex.php' title='WebEx\nClick for more information.'><i class='$class_options fa-tablet green' aria-hidden='true' ></i></a>\n";
echo "</span>";
}
function webex_registration_link($user_id, $session_id){
global $db;
$mk = get_webex_meeting_key($session_id);
$attendee_sql = mysqli_query($db, "SELECT first_name.value as first_name, last_name.value as last_name, user.email, city.value as city
FROM phplist_user_user user
LEFT OUTER JOIN phplist_user_user_attribute first_name ON user.id = first_name.userid AND first_name.attributeid = '1'
LEFT OUTER JOIN phplist_user_user_attribute last_name ON user.id = last_name.userid AND last_name.attributeid = '2'
LEFT OUTER JOIN phplist_user_user_attribute city ON user.id = city.userid AND city.attributeid = '10'
WHERE user.id = '$user_id'");
$attendee = mysqli_fetch_row($attendee_sql);
$first_name = $attendee[0];
$last_name = $attendee[1];
$attendee_email = $attendee[2];
if($attendee[3] != ''){
$city = $attendee[3];
}else{
$city = "None";
}
$url = "https://communityeducationservice.webex.com/communityeducationservice/m.php";
$url .= "?AT=EN";
$url .= "&MK=$mk";
$url .= "&FN=$first_name";
$url .= "&LN=$last_name";
$url .= "&AE=$attendee_email";
$url .= "&CY=$city";
$url .= "&BU=http://community.hmhc.ca/sessions/registration/%3Fsession_id%3D".$session_id;
return $url;
}
?>