4.2.8 Automated Appointment Reminders

Preface

This basic script is adequate for running on the OSCAR server to server Appointment Reminders in both official languages.  It can be used with some configuration to also update appointment status to indicate that a reminder has been sent for that appointment. 

Document Version History

• v1.0 – initial public release to oscarmanual.org – June 7, 2013
• v1.1 – added French language logic and appointment status update code – June 7, 2013
• v1.2 – added warnings about deprecated methods – March 13, 2017
  
  

Prerequisites

It is assumed that

1. OSCAR needs to have the demographics populated with valid emails
   
2. For SMS support the cell number has to be postpended with @email.to.sms.address 
   
3. All of the rest of the set up is listed in the script header
   

**WARNING**

Code

Ensure that you have the patient's email recorded.

For SMS messaging add one of the following codes to the Cell Phone number in the Patient master Demographic Record:
Rogers Wireless: [10-digit phone number]@pcs.rogers.com
Fido: [10-digit phone number]@fido.ca
Telus: [10-digit phone number]@msg.telus.com
Bell Mobility: [10-digit phone number]@txt.bell.ca
Kudo Mobile: [10-digit phone number]@msg.koodomobile.com
MTS: [10-digit phone number]@text.mtsmobility.com
President’s Choice: [10-digit phone number]@txt.bell.ca
Sasktel: [10-digit phone number]@sms.sasktel.com
Solo: [10-digit phone number]@txt.bell.ca
Virgin: [10-digit phone number]@vmobile.ca
Northern Tel: [10-digit phone number]@txt.northerntelmobility.ca

So, if the patient is on Rogers and their cell is 514-555-1212 then you would enter 5145551212@pcs.rogers.com into the Cell Phone field of the Patient Master record.

Note: You can add spaces and dashes for readability,

#! /usr/bin/perl
use strict;
no strict 'refs';
use warnings;
use DBI;
use Date::Calc qw(:all);
use File::Temp qw/ tempfile tempdir /;

my $version = '0.1';

#############################################################################
#    CONTENTS
#############################################################################

#Section 1. Description and copyright statement
#Section 2. Setup and instructions
#Section 3. User definable variables
#Section 4. Code



#############################################################################
#    Section 1. DESCRIPTION AND COPYRIGHT STATEMENT
#############################################################################


#this script is used to send emails reminders to patients with appointments

#	 (c) Peter Hutten-Czapski 2013

#    This code is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This code is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    see <http://www.gnu.org/licenses/>.





#############################################################################
#    Section 2. SETUP AND INSTRUCTIONS
#############################################################################


# Copy this script to a suitable location (eg: /home/user/cron)

# Make sure that it can only be run by the owner
#     sudo chmod 700 /home/user/cron/reminder_email.pl


#For this script to work you need to install perl modules
# sudo perl -MCPAN -e shell

# cpan> install Bundle::CPAN
# cpan> install Bundle::DBI
# cpan> install Date::Calc
# cpan> install File::Temp
# cpan> exit

#You should have a package containing sendmail installed
# sudo apt-get install postfix

#you will need to run the following command at 8am via crontab
#
#crontab -e
# 0 8 * * *    perl /home/user/cron/reminder_email.pl;

#ensure that access to the oscar database is limited in scope

#mysql> GRANT select ON oscar_12_1.* TO OscarRead@localhost IDENTIFIED BY '[password1]';
#mysql> GRANT select, insert, update, delete ON oscar_12_1.* TO OscarWrite@localhost IDENTIFIED BY '[password2]';




#############################################################################
#    Section 3. USER VARIABLES
#############################################################################

my $login_id;
my $user_name='peter';


my %pref;
$pref{mysql_database}	= 'oscar_12_1';
$pref{mysql_read}		= 'OscarRead';
$pref{mysql_read_pass}	= 'password1';

#uncomment the following if you want the status of the patient changed if an email was sent
#$pref{mysql_write}		= 'OscarWrite';
#$pref{mysql_write_pass}= 'password2';

$pref{mysql_host}		= '127.0.0.1';
$pref{mysql_port}		= '3306';
$pref{mysql_log}		= '/home/user/cron/cron_log';

#uppercase output
my $uppercase_output = 0;

# SMS length 
my $desired_string_length=140;

# Email variables
my $to;
my $from= 'no_reply@haileyburyfht.org';
my $body;
my $subject='Automated Appointment Reminder';
my $boiler1="Should there be a need to reschedule or cancel your appointment, we require 24hr notification at (705)-672-xxxx There is a 25 dollar charge for missed appointments.\n\nPlease note that we do not reply to medical issues by email because of privacy rules but only through MyOSCAR at http://www.haileybury.doctor-doc.org To get your username and login you must present Photo ID at your next appointment.\n\nThis e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized.  If you received this e-mail in error or do not wish to recieve email appointment reminders, please advise us.\n---------------------------------------\n\n";
my $boiler2="Veuillez nous aviser de tout changement ou cancellation au plus tard 24 heures avant l'heure prévue du rendez-vous au (705)-672-xxxx. Des frais de 25 dollar seront perçus pour tous les rendez-vous manqués.\n\nVeuillez noter que nous ne répondons pas aux questions médicales par courriel non sécurisé, mais seulement par MyOSCAR à http://www.haileybury.doctor-doc.org Pour obtenir votre nom d'utilisateur et nom de passe vous devez présenter une arte d'identité lors de votre prochain rendez-vous.\n\nCe courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.  Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez nous en aviser immédiatement.";

# Subroutine variables

#month variables
my @month_names = qw(
	January
	February
	March
	April
	May
	June
	July
	August
	September
	October
	November
	December
);


#month variables
my @day_names = qw(
	Sunday
	Monday
	Tuesday
	Wednesday
	Thursday
	Friday
	Saturday
);

my $sec;
my $min;
my $hr;
my $mday;
my $mon;
my $year;
my $wday;
my $yday;
my $isdst;
my $longyr;
my $fixmo;
my $sql_date;
my $sql_time;
my $sql_datetime;
my $mysql_user;
my $mysql_login;
my $mysql_pass;
my $dbh_name;
my $mysql_preferred_user;
my $mysql_original_user;
my $dbh_inuse;
my $sql;
my @mysql_row;
my $mysql_query;
my $mysql_record;
my @mysql_result;
my @mysql_insert;
my $mysql_update_table;
my $mysql_update_table_array;
my $mysql_disconnect;
my $error;
my $this_sql_insert;
my $mysql_insert_table;
my $sql_data;
my $tempath;
my $path;
my $mysql_result;
my $mysql_set_insert;
my %mysql_set_insert;



#############################################################################
#    Section 4. CODE
#############################################################################

get_time();

#connect to the database
mysql_connect();

#get the clinic name
my $clinic = mysql_hash_query("SELECT * FROM clinic LIMIT 0,1");
if ($uppercase_output) {
	${$clinic}{'clinic_name'} = uc ${$clinic}{'clinic_name'};
}

mysql_hash_query("select dem.email as Email, DATE_FORMAT(app.appointment_date,'%d/%m/%Y') as Date, 
TIME_FORMAT(app.start_time,'%h:%i %p') as Time, CONCAT(prov.first_name, ' ', prov.last_name) as Provider, 
dem.last_name, dem.first_name , dem.official_lang, dem.demographic_no, app.location 
	FROM appointment app, demographic dem, provider prov
	where LOCATE('\@', dem.email ) > 2
	&& appointment_date=CURDATE()
	&& app.demographic_no = dem.demographic_no
	&& app.provider_no = prov.provider_no
ORDER by app.appointment_date, app.provider_no, app.start_time 
");
my @patients = @mysql_result;


for my $patient (@patients) {
	$to=${$patient}{'Email'};
	if (${$patient}{'official_lang'} == "French"){
		$body="English email follows\n\n ${$patient}{'first_name'} ${$patient}{'last_name'} a un rendez-vous avec ${$patient}{'Provider'} le ${$patient}{'Date'} à ${$patient}{'Time'}. \n $boiler2\n\n${$patient}{'first_name'} ${$patient}{'last_name'} has an appointment with ${$patient}{'Provider'} at ${$patient}{'location'} on ${$patient}{'Date'} at ${$patient}{'Time'} $boiler1";
	} else {
		$body="Courriel en francais suive\n\nThis is a system generated e-mail reminder.\n${$patient}{'first_name'} ${$patient}{'last_name'} has an appointment with ${$patient}{'Provider'} at ${$patient}{'location'} on ${$patient}{'Date'} at ${$patient}{'Time'} $boiler1 ${$patient}{'first_name'} ${$patient}{'last_name'} a un rendez-vous avec ${$patient}{'Provider'} le ${$patient}{'Date'} à ${$patient}{'Time'}. \n $boiler2";
	}
	open(MAIL, "|/usr/sbin/sendmail -t");
	## Mail Header
	print MAIL "To: $to\n";
	print MAIL "From: $from\n";
	print MAIL "Subject: $subject\n\n";
	## Mail Body
	print MAIL $body;
	close(MAIL);
	
	# uncomment the following if you want the patients status to change to indicate that an email was sent
	#mysql_write_simple(
	#	"UPDATE appointment
	#	SET status='e'
	#	WHERE demographic_no='${$patient}{'demographic_no'}'
	#	&& appointment_date=CURDATE()"
	#);
	
	#print "A message has been sent from $from to $to saying \n$body\n";

}

mysql_hash_query("select DATE_FORMAT(app.appointment_date,'%c-%d') as Date, TIME_FORMAT(app.start_time,'%H:%i') as Time, prov.last_name as Provider,
app.name, REPLACE( REPLACE( REPLACE( REPLACE(ext.value,'(',''),')',''), ' ','') ,'-','') as Email, dem.official_lang, dem.demographic_no
	FROM appointment app
	left join demographic dem on app.demographic_no = dem.demographic_no
	left join provider prov on app.provider_no = prov.provider_no
	left join demographicExt ext on ( app.demographic_no = ext.demographic_no && ext.key_val = 'demo_cell' && ext.date_time =
     ( select max(date_time) from demographicExt ext1 where ext1.demographic_no = ext.demographic_no) )
	WHERE LOCATE('\@', ext.value ) > 2
	&& appointment_date=CURDATE()
order by app.appointment_date, app.provider_no, app.start_time 
");
my @patients2 = @mysql_result;


for my $patient2 (@patients2) {
	$to=${$patient2}{'Email'};
	##a short email body for SMS
	$body="Your appointment with ${$patient2}{'Provider'} is today at ${$patient2}{'Time'}";
	if (${$patient2}{'official_lang'}== "French"){
		$body="Vous avez un rendez-vous avec ${$patient2}{'Provider'} aujourd hui à ${$patient2}{'Time'}.";
	}
	##next check if the string is too long, and truncate if required
	if (length $body > $desired_string_length) {
		$body=substr ($body, 0, $desired_string_length);
	}

	open(MAIL, "|/usr/sbin/sendmail -t");
	## Mail Header
	print MAIL "To: $to\n";
	print MAIL "From: $from\n";
	print MAIL "Subject: $subject\n\n";
	## Mail Body
	print MAIL $body;
	#close(MAIL);

	# uncomment the following if you want the patients status to change to indicate that an email was sent
	#mysql_write_simple(
	#	"UPDATE appointment
	#	SET status='f'
	#	WHERE demographic_no='${$patient2}{'demographic_no'}'
	#	&& appointment_date=CURDATE()"
	#); 
	#print "A message has been sent from $from to $to saying \n$body\n";

}

#disconnect from the database
mysql_disconnect();


exit;



###########################
#subroutines
###########################


sub get_time {	
	
	#get the date time
	( $sec, $min, $hr, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time);
	
	$longyr = $year + 1900;
	$fixmo = $mon + 1;
	
	if ($isdst == 1) {
		my $tz = "CDT";
	} else {
		my $tz = "CST";
	}
	
	if ($hr<10) {
		$hr="0".$hr;
	}
	
	if ($min<10) {
		$min="0".$min;
	}
	
	my $sqlmo=$fixmo;
	if ($sqlmo<10) {
		$sqlmo="0".$sqlmo;
	}
	
	my $sqlmday=$mday;
	if ($sqlmday<10) {
		$sqlmday="0".$sqlmday;
	}
	
	$sql_date="$longyr-$fixmo-$mday";
	$sql_time="$hr:$min:$sec";
	$sql_datetime="$sql_date $sql_time";

}

sub mysql_log {

	if ($_[0]) {
		$sql_data=$_[0];
	}

	if ($pref{mysql_log}) {
		unless ($sql_data) {	$sql_data=$sql;	}
		$sql_data=~s/\s/ /g;
		open (MYSQL,">>$pref{mysql_log}");
		flock (MYSQL, 2);
		print MYSQL "$longyr-$fixmo-$mday $hr:$min:$sec - $sql_data\n";
		close (MYSQL);
	}
	return;

}





sub mysql_connect {

	#set up login and password for this user
	unless ($mysql_user) {
		$mysql_user="read";
	}
	$mysql_login="mysql_".$mysql_user;
	$mysql_pass="mysql_".$mysql_user."_pass";
	$dbh_name="dbh_$mysql_user";

	# Connect to the database
	mysql_log("mysql_connect : mysql_user=$mysql_user, dbh_name=$dbh_name, mysql_login=$mysql_login, mysql_pass=$mysql_pass");
	${$dbh_name} = DBI->connect("DBI:mysql:$pref{mysql_database}:$pref{mysql_host}:$pref{mysql_port}","$pref{$mysql_login}","$pref{$mysql_pass}") || ErrorMessage('Could not connect to database');
	
	#record that this database is in use
	$dbh_inuse="dbh_inuse_$mysql_user";
	${$dbh_inuse}=1;

	return;

}




sub mysql_disconnect {
	# Disconnect the current user ($mysql_user) from the database
	# but don't disconnect the read-only user
	#get the passed user name if passed directly
	if ($_[0]) {
		$mysql_user=$_[0];
	}
	
	if (($mysql_user) && ($mysql_user ne "read")) {
	
		#check that the user is connected first
		$dbh_inuse="dbh_inuse_$mysql_user";
		
		if (${$dbh_inuse}) {
			#this user is  connected
			
			$dbh_name="dbh_$mysql_user";
			mysql_log("mysql_disconnect : mysql_user=$mysql_user, dbh_name=$dbh_name, mysql_login=$mysql_login, mysql_pass=$mysql_pass");
			${$dbh_name}->disconnect || mysql_log('ERROR could not disconnect database');
			
			#record that this database is no longer in use
			${$dbh_inuse}=0;
		}	
	}
	
	return;

}



sub mysql_check_connection {
	#check that the preferred user is the one currently selected
	if ($mysql_user eq $mysql_preferred_user) {
		$mysql_original_user=$mysql_user;
	} else {
		$mysql_original_user=$mysql_user;
		$mysql_user=$mysql_preferred_user;
	}
	
	$dbh_name="dbh_$mysql_user";
	$dbh_inuse="dbh_inuse_$mysql_user";
	$mysql_disconnect=0;
	
	#check that the preferred user is connected
	unless (${$dbh_inuse}) {
		#this user is not connected
		$mysql_disconnect=1;
		mysql_connect();
	}	
	return;

}


sub mysql_check_connection_finish {	

	#check if this user was only connected for this event
	if ($mysql_disconnect) {
		mysql_disconnect();
	}
	
	#reset the current user to the original user
	$mysql_user=$mysql_original_user;
	
	return $mysql_user;

}



sub mysql_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as a 'read' user
	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 	
 	$mysql_preferred_user="read";
	mysql_submit_simple($sql);
	return 1;

}


sub mysql_write_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as a 'write' user
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
	
	#print "\n\n**test mode** sub mysql_write_simple has had mysql_submit_simple($sql) commented out - your data has NOT been saved!\n\n$sql\n\n";
	
	$mysql_preferred_user="write";
	mysql_submit_simple($sql);
	return 1;

}


sub mysql_submit_simple {
	#this subroutine is passed a full statement as $sql and sends it to the database as the user $preferred_user
	
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 	
	@mysql_row=();
	
	mysql_check_connection();
	
	mysql_log("mysql_submit_simple : $sql");
	$mysql_query = ${$dbh_name}->do ($sql) || ErrorMessage('Could not submit data to the database');
	
	mysql_check_connection_finish();
	return 1;

}



sub mysql_array_query {
 
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
	
	$mysql_preferred_user="read";
	mysql_check_connection();
	
	$mysql_query = ${$dbh_name}->prepare ($sql);
	@mysql_result=();
	
	if (defined($mysql_query)) {
	
		mysql_log("mysql_array_query : $sql");
		$mysql_query->execute() || ErrorMessage('Could not execute array query on database');
		
		while (@mysql_row = $mysql_query->fetchrow_array()) {
			@mysql_result=@mysql_row;
		}
		
	} else {
		mysql_log("mysql_array_query (not found) : $sql");
		ErrorMessage('Could not find query to submit to database');
	}
	$mysql_query->finish();
	
	mysql_check_connection_finish();
	
	#pass the first result back as a return value (or undefined, if no result)
	if ($mysql_result[0])	{
		return $mysql_result[0];
	}
	
	return;
	
}



sub mysql_hash_query {
 
 	#get passed sql string if present
 	if ($_[0]) {
 		$sql=$_[0];
 	}
 
	$mysql_preferred_user="read";
	mysql_check_connection();
	$mysql_query = ${$dbh_name}->prepare ($sql);
	@mysql_result=();
	
	if (defined($mysql_query)) {
		
		mysql_log("mysql_hash_query : $sql");
		$mysql_query->execute() || ErrorMessage('Could not execute hash query on database');
		
		
		
		@mysql_result=();
		while ($mysql_record = $mysql_query->fetchrow_hashref()) {
			push (@mysql_result, $mysql_record);
		}

		
	} else {
		mysql_log("mysql_hash_query (not found) : $sql");
		ErrorMessage('Could not find hash query to submit to database');
	}
	$mysql_query->finish();
	
	mysql_check_connection_finish();
	
	#return the mysql_result array (containing hash references), or undefined if no result
	if ($mysql_result[0])	{
		return $mysql_result[0];
	}
		
	return;
}


sub mysql_set_insert {

	#this subroutine is passed:
	#	1. the table name ($mysql_update_table)
	#	2. a hash of elements to include (%mysql_update)
	#and it then creates a mysql statement ($sql) and sends it to the database as a 'write' user

	#get the passed table name if passed directly
	if ($_[0]) {
		$mysql_insert_table=$_[0];
	}
	
	$mysql_preferred_user="write";
	mysql_check_connection();

	#insert this line in the database
	$error="mysql_insert";
	$sql="INSERT INTO $mysql_insert_table SET ";
	for $this_sql_insert (keys %mysql_set_insert) {
		$mysql_set_insert{$this_sql_insert}=~s/\'/\`/g;
		$sql.="$this_sql_insert='$mysql_set_insert{$this_sql_insert}', ";
	}
	$sql=~s/, $//;
	
	mysql_write_simple($sql);
	
	mysql_check_connection_finish();
	
	return;

}



sub rand_id {

	my $rand_id_digits = $_[0];
        
        unless ($rand_id_digits) {
            $rand_id_digits = 5;
        }
        
        my $rand_id='';
        
	while (length $rand_id < $rand_id_digits) {
		my $ftemp=int(rand 9)+1;
		$rand_id.=$ftemp;
	}
	
	return $rand_id;
	
}


sub ErrorMessage {
	print "\n";
	print "\n";
	print "Error: $_[0]\n";
	exit;
}