Create an iCal Appointment Reminder System
Article posted on October 21st, 2011
Article posted on October 21st, 2011
This tutorial will show you how to create a reminder system that will call you to remind you of upcoming meetings.
Ten minutes before each event in your calendar, your Switchvox PBX will call you to remind you that you have an upcoming appointment. If a phone number is found in your meeting description, you will have the option to press ‘1′ and have the Switchvox PBX call you back and connect you to that number at the meeting time. When your PBX calls you to connect you to that number, it will read to you any pin or extensions that it found in the appointment description before connecting you to the call.
For example, take the following scenario:
Let’s say I have the following appointment in iCal:
At 1:20 on that day, my phone will ring, and I’ll hear the following prompt:
“You have an appointment beginning in ten minutes. The following phone number was found in your appointment details: eight, five, eight, five, five, five, one, two, one, two. Press 1 if you would like the PBX it initiate a call to this number at the appointment time. Otherwise press 2.”
Since I know that the number I heard is the conference bridge I use with Joe and Fred, I press 1 so that I can automatically call in to the meeting.
Ten minutes later, my phone rings and I hear the following:
“The system found the following pin or extension in your appointment details: five, five, five, five. You will now be connected to you call.”
I then hear ringing on my phone, and am then connected to the conference bridge that Joe and Fred use. When prompted, I know to enter 5555 to join the correct conference room and Joe, Fred, and I begin our meeting.
This all sounds pretty complicated, but it’s actually quite simple to accomplish with some IVRs, the switchvox.call API, and just a small bit of coding. I will walk you through the whole process. This example is written in perl, but it shouldn’t be too difficult to write this using another language. This tutorial also assumes that the calendar application you are using stores or can export your appointments in iCalendar format. Most major applications do. A list of applications that support the iCalendar format can be found here.
Prerequisite: Review the Documentation
I suggest that before you start this tutorial, you read over some of the documentation and become familiar with the Extend API. This way, you will have some background to better understand everything in this tutorial. If you choose to skip this reading now, I strongly recommend you go back and review it before writing any applications of your own.
Recommended Reading:
Application Overview
The first item you’ll need to address for building this application is getting the most up to date iCalendar file of your appointments. There are many ways to achieve this, but I won’t be going over them in detail. I accomplished this task by creating an Automator workflow on my Mac to export my “Appointments” calendar from iCal. I then used iCal to schedule the workflow to run every night at 3 am (see here). Finally, I have a cron job on my Ubuntu machine that fetches the most recent iCal export at 3:15am every day.
Once you have a method for getting the most up to date iCalendar compliant file containing all of your appointments, the application consists of the following:
- A perl script that runs on a cron to check if any calls need to be made.
- A mod_perl script running on Apache that accepts requests from the Switchvox PBX.
- A postgresql database with two tables for storing information about calls we need to make.
- Some IVRs on your Switchvox PBX.
That’s it!
Step 1: Verify your setup
If the machine where you’ll be hosting your application is on the same network as your Switchvox PBX, you shouldn’t have to do anything to get access to the Extend API. If not, you’ll have to make sure that the application can access port 443 on your Switchvox. Also, be sure that the Switchvox Access Control lets your web server have access to the Admin API Service.
The easiest way to test all this is to ssh into your web server and then follow the Interact with the Extend API using Wget tutorial. By the end of that tutorial, you should have sent and received some sample XML, and you’ll be ready to continue.
Step 2: Setting up Switchvox
First we’ll tackle all the tasks we need to perform on our Switchvox to make this application works:
Recording Sounds
We need to record a few sounds that we’ll use as prompts in IVRs. In your Switchvox Admin interface, got to Tools -> Media -> Sound Manager. Here, we’re going to need to create 5 sounds. Here are the sounds I created and what I named them:
| Sound Name | Description |
|---|---|
| Upcoming Appointment | “You have an upcoming appointment in 10 minutes.” |
| Upcoming Appointment: has number | “The following phone number was found in your appointment details.” |
| Upcoming Appointment: callback | “Press 1 if you would like the PBX to initiate a call to this number at the appointment time. Otherwise press 2.” |
| Upcoming Appointment: pin or ext | “The system found the following pin or extension in your appointment details.” |
| Upcoming Appointment: connecting | “You will now be connected to your call.” |
IVR Menus
The next step is to create the IVR menus that will process our reminders. I created 4 IVR menus in call. First I created one which I named Appointment Reminders:
Here’s a breakdown of the actions in the IVR menu:
- The first action this IVR menu does is play the sound that tells you that you have an appointment in 10 minutes.
- It then has a conditional cause that checks an IVR variable called has_number. If this variable is not equal to 1, the caller will be redirected to the “Hangup” IVR menu because there is nothing more to do.
- Otherwise, the next action is to play the sound which informs the user that a phone number was found in the appointment details.
- It then uses the “Say Digits/Letters” action to read the phone number we found. This phone number is found in an IVR variable called callback_number.
- It then plays the sound that asks the user to press 1 if they would like their Switchvox to set up the call at the appointment time, or 2 if not.
- Finally, it listens for the user to select an option. If the caller presses 2, they are redirected to the “Hangup” IVR, and if they press 1, they are redirected to the “Appointment Setup callback” IVR menu.
You might be wondering how the IVR variables are getting set. We’ll get into that with our perl script later on, so for now just trust me that those variables will be set with the appropriate values.
The “Appointment Setup callback” IVR menu consists only of two actions. It uses Send Call Values to URL, and then redirects the call to the Hangup IVR menu.
The key here is the Send Call Values to URL action. Since the user wants to be called at the appointment time I need to send the information from the call back to my application. The URL for my action is the following:
http://10.10.8.0/confirm_callback?callback_number=%callback_number%&callback_pin=%callback_pin%&appointment_id=%appointment_id%
I’m sending the following IVR variables to my mod_perl script:
- callback_number - The number to call
- callback_pin - Any pin or extension we found in the appointment details
- appointment_id - The ID of the appointment
With those two IVR menus we’ve handle the reminder part of our application covered, next we’ll create two more IVR menus that handle the callback part.
First let’s have a look at the “Appointment callback” IVR menu:
This IVR menu is very simple. It has only three actions:
- The first action is a Conditional Clause. If the has_number IVR variable is equal to 1, it sends the caller to the “Appointment remind pin” IVR Menu.
- Otherwise, it continues and plays the sound that informs the caller they are being connected to their call.
- Finally it sends the caller to an external number. The number to send the call to is in the callback_number IVR Variable. (Please note that depending upon your system setup, you may need to create a call rule to be used by this action to ensure your 10 digit number gets routed out to the correct provider on your system. Please see your Switchvox documentation on call rules for more details.)
The “Appointment remind pin” IVR menu is equally as simple:
This IVR Menu has three actions:
- It plays a sound letting the caller know that a pin or extension was found along with the phone number in the appointment details.
- It then uses the Say Digits/Letters action to read the number back to the caller from the callback_pin IVR variable.
- It then redirects the caller to action two in the “Appointment callback” IVR menu.
Create IVR Extensions
The last step in all this is to create a couple of IVR Extensions. For my application I set up extension 600 to route to the “Appointment Reminders” IVR menu and extension 601 to route to the “Appointment callback” IVR menu.
With that we are done setting up all the pieces we need on our Switchvox PBX.
Step 3: Database Schema
The rest of my application will be running on an Ubuntu machine. On that machine I set up a PostgreSQL database to store some information about calls that need to be made. I created two tables in my database:
CREATE TABLE appointment_callbacks (
appointment_id varchar(128),
callback_number varchar(16),
callback_pin varchar(6)
);
CREATE TABLE finished_reminders (
appointment_id varchar(128),
call_type varchar(16)
);
The finished_reminders table is used to store appointments we’ve already called about to make sure we don’t accidentally call ourselves about the same appointment twice. The appointment_callbacks table is used to schedule calls that connect us to our meeting phone numbers.
Step 4: Getting perl Modules
You are going to need a few perl modules to write the rest of the application. In addition to all the modules for mod_perl and connecting to a PostgreSQL database, you’ll need the following:
- iCal::Parser
- Switchvox::API
If you’re unfamiliar with how to get or install perl modules, http://www.cpan.org is a good place to start reading.
Step 5: Writing the SetCallback Handler
The next step is to write our SetCallback handler. This is what will handle the Send Call Values to URL action from our “Appointment Setup callback” IVR menu. The code for this handler is pretty straightforward. We simply take the values we get from the IVR action and insert them into our appointment_callbacks table:
package SetCallback;
use strict;
use warnings;
use Apache2::RequestRec;
use Apache2::Const -compile => qw(OK);
use DBI;
use CGI;
use Data::Dumper;
sub handler {
my $apache = shift;
my $cgi = new CGI($apache->args);
my ($sql, $sth, $db);
$db = DBI->connect("dbi:Pg:dbname=appointments", "db_user", "db_pass");
my $insert_values = {};
$insert_values->{appointment_id} = $db->quote($cgi->param('appointment_id'));
$insert_values->{callback_number} = $db->quote($cgi->param('callback_number'));
$insert_values->{callback_pin} = $db->quote($cgi->param('callback_pin'));
if (!$insert_values->{callback_pin}) {
delete $insert_values->{callback_pin};
}
my $sql = "INSERT INTO appointment_callbacks (" . join(', ', keys %$insert_values) . ") ".
"VALUES (" . join(', ', values %$insert_values) . ")";
$sth = $db->prepare($sql);
$sth->execute();
return Apache2::Const::OK;
}
1;
Now, when a caller presses 1 to ask to be called back at the appointment time, our handler will take the store the call information in our database.
Step 6: Appointment Checking Script
Finally, we’re at our last step. This script is the brains of our application and is a little more involved than our handler script, but still not too bad. I called my script checkAppointments.pl and I’ll break it down for you here in sections.
Setting up modules and variables
This part of the script simply loads the needed perl modules and sets up some variables I’ll need later on.
#!/usr/bin/perl
BEGIN { $ENV{PERL_LWP_SSL_VERIFY_HOSTNAME} = 0; }
use Data::Dumper;
use DateTime;
use DBI;
use iCal::Parser;
use Switchvox::API;
my $ICAL_FILE = 'icsFiles/Appointments.ics';
my $EXTENSION = 100;
my $ACCOUNT_ID = 1102;
my $REMIND_IVR = 600;
my $CONNECT_IVR = 601;
my $SV_HOST = '10.10.2.202';
my $SV_ADMIN = 'admin';
my $SV_PASSWORD = 'password';
my $db;
I set the PERL_LWP_SSL_VERIFY_HOSTNAME environment variable equal to 0 because I haven’t set up my Switchvox with a valid SSL certificate, and I don’t want my API request to fail. My phone extension is 100 and my account_id is 1102. You can find the account_id for your extension by calling the switchvox.extensions.getInfo API. The REMIND_IVR and CONNECT_IVR variables are set to the two IVR extensions we created earlier. The ICAL_FILE variable should point to where your iCalendar compliant file can be found. Everything else should be pretty self explanatory.
In the next chunk of code, I parse the iCalendar file and use the current date to get only today’s events.
#Get current date/time
my $now = DateTime->now();
my $day = $now->day();
my $month = $now->month();
my $year = $now->year();
my $now_epoch = $now->epoch();
#parse iCalendar file
my $parser = new iCal::Parser();
my $hash = $parser->parse($ICAL_FILE);
my $calendar = $parser->calendar();
my $todays_events = $calendar->{events}{$year}{$month}{$day};
If you read the documentation for iCal::Parser, you’ll see that the calendar object that the parser returns is a hash broken up by date. For each date, the value is a hash of events, with the event’s id as a key, and an event hash as the value. Knowing that, we’ll loop through the todays_events hash in our next block of code:
# check all of todays events
foreach my $event_uid (keys %{$todays_events}) {
my $event = $todays_events->{$event_uid};
my $event_start_datetime = $event->{DTSTART};
# make sure event hasn't happened yet
if ($event_start_datetime->epoch() > $now_epoch) {
my $time_difference = $event_start_datetime->delta_ms($now);
if ($time_difference->{minutes} < 11 && $time_difference->{minutes} > 9) {
make_reminder_call(
event_uid => $event_uid,
db => $db,
event => $event
);
} elsif ($time_difference->{minutes} < 2) {
check_for_callback(
event_uid => $event_uid,
db => $db
);
}
}
}
We get the start time of each event and compare it to the current time to make sure the event time has not passed yet. If it hasn’t we use the delta_ms function of the DateTime perl module to determine how many minutes away the event’s start time is from the current time. If that time difference is between 11 and 9 minutes, we call our make_reminder_call subroutine. If it is less than 2 minutes, we call the check_for_callback subroutine. We give ourselves a couple minutes of leeway just in case there is a delay in running our script. The two subroutines we call will account for this to prevent from multiple calls occurring for the same appointment.
Next, here’s a peek at our make_reminder_call subroutine:
# Initiate a call to remind user of appointment
sub make_reminder_call {
my %in = @_;
my ($sql, $sth, $callback_number, $callback_pin);
my $db = $in{db};
my $event_uid = $in{event_uid},
my $event = $in{event};
my $has_number = 0;
if (!$db) {
$db = DBI->connect("dbi:Pg:dbname=appointments", "ajp", "ajp");
}
# check if we've already made a call for this event
$sql = "SELECT COUNT(*) FROM finished_reminders ".
" WHERE call_type = 'remind' AND appointment_id = '$event_uid'";
$sth = $db->prepare($sql);
$sth->execute();
my $already_reminded = $sth->fetchrow_array;
return if $already_reminded;
if ($event->{DESCRIPTION} =~ m/\(?(\d{3})\)?[\s-.]?(\d{3})[\s-.]?(\d{4})/) {
$has_number = 1;
$callback_number = $1.$2.$3;
$event->{DESCRIPTION} =~ m/(\(?\d{3}\)?[\s-.]?\d{3}[\s-.]?\d{4})/;
my $whole_num = $1;
# see if we have a pin or ext in the description after the phone number
my $index_after_number = index($event->{DESCRIPTION}, $whole_num) + length($whole_num);
my $string_after_number = substr($event->{DESCRIPTION}, $index_after_number);
if ($string_after_number =~ m/(\d{3,6})/) {
$callback_pin = $1;
}
}
$sql = "INSERT INTO finished_reminders (call_type, appointment_id) ".
"VALUES ('remind', '$event_uid')";
$sth = $db->prepare($sql);
$sth->execute();
make_call(
has_number => $has_number,
callback_number => $callback_number,
callback_pin => $callback_pin,
appointment_id => $event_uid,
ivr => $REMIND_IVR
);
}
Because we have a two minute window for when a reminder call will be initiated, we first check the finished_reminders table to make sure we haven’t already made a reminder call for this appointment. If we have we’ll just return. Otherwise, we use a regular expression to see if we can find anything that matches a phone number in the event’s description. If we find a phone number, we set the has_number variable equal to 1. We also look at the description after the phone number to see if we can find anything that might be a pin or an extension. Finally, we insert into the finished_reminders table to make sure we don’t make a reminder call for this appointment again, and then we call the make_call subroutine to actually make the call.
Next is the check_for_callback routine which is called to see if an appointment that is about to start requires a call to connect the user to a phone number in the event description:
# Check if the user wants a call for this meeting, initiate callback if yes
sub check_for_callback {
my %in = @_;
my ($sql, $sth);
my $has_number = 0;
my $db = $in{db};
my $event_uid = $in{event_uid};
if (!$db) {
$db = DBI->connect("dbi:Pg:dbname=appointments", "ajp", "ajp");
}
# check if we're supposed to initiate the call for this event
$sql = "SELECT * FROM appointment_callbacks WHERE appointment_id = '$event_uid'";
$sth = $db->prepare($sql);
$sth->execute();
my $callback = $sth->fetchrow_hashref;
return if !$callback->{appointment_id};
# check if we've already made a call for this event
$sql = "SELECT COUNT(*) FROM finished_reminders ".
" WHERE call_type = 'callback' AND appointment_id = '$event_uid'";
$sth = $db->prepare($sql);
$sth->execute();
my $already_reminded = $sth->fetchrow_array;
return if $already_reminded;
if ($callback->{callback_pin}) {
$has_number = 1;
}
$sql = "INSERT INTO finished_reminders (call_type, appointment_id) ".
"VALUES ('callback', '$event_uid')";
$sth = $db->prepare($sql);
$sth->execute();
make_call(
has_number => $has_number,
callback_number => $callback->{callback_number},
callback_pin => $callback->{callback_pin},
appointment_id => $event_uid,
ivr => $CONNECT_IVR
);
}
This subroutine first queries the appointment_callbacks table to see if we need to make a call for this appointment. If you look back at the perl handler that is called by the Send Call Values to URL IVR action, you’ll recall that if the user requested to be called back at the appointment start time, we inserted a row into this table. If we don’t find a row for our appointment in the appointment_callbacks table, then we just return from the subroutine. Next, because we have two minute window for this subroutine to be called for an appointment, we check the finished_reminders table to see if we’ve already made a call for this appointment. If we’ve already made a call, we just return. Finally, we insert into the finished_reminders table and then take the values from the appointment_callbacks table and call the make_call subroutine.
That brings us to the last piece of the puzzle, which is our make_call subroutine.
# actually make the call
sub make_call {
my %in = @_;
my $has_number = $in{has_number};
my $callback_number = $in{callback_number};
my $callback_pin = $in{callback_pin};
my $appointment_id = $in{appointment_id};
my $ivr = $in{ivr};
my $api = new Switchvox::API(
hostname => $SV_HOST,
username => $SV_ADMIN,
password => $SV_PASSWORD
);
my $response = $api->api_request(
method => 'switchvox.call',
parameters => {
dial_first => $EXTENSION,
dial_as_account_id => $ACCOUNT_ID,
dial_second => $ivr,
variables => [ {
variable => [
'has_number=' . $has_number,
'callback_number=' . $callback_number,
'callback_pin=' . $callback_pin,
'appointment_id=' . $appointment_id
]
} ]
}
);
}
This subroutine simply takes the values that are passed into it and makes and request to the switchvox.call API. The “variables” parameters that we pass in are used by the call API to set IVR variables. That’s how our IVR knows which way to route the call and which digits to read back on the call.
Wrap Up
The last thing I did for this application was to create a cron job to call my checkAppointments.pl script once every minute. Now I’ll never forget about another meeting again. That’s the end of my little tutorial, but there’s probably more you should do if you wanted to use this application in a production environment. For example, some error checking and exception handling would be a good idea, and you probably only want to parse your iCalendar file if it had actually changed. I hope this little write up has given you some ideas for using the Switchvox Extend API. It makes it possible to create some pretty powerful applications with relatively minimal effort. You can download all the code from this example here