How to Handle Appointments and Send Automated Reminder Calls using PHP and your MySQL Database

• Download source - 3.8 KB

Introduction

In this article, I am going to show a simple example of how to insert reminders about appointments, meetings to MySQL table with the help of PHP. Then, in case of a need, initiate reminder calls to a given phone number. I suppose that you are aware of the basic knowledge of PHP and SQL. To test the calls, you will need a softphone, too.

Background

I am going to show you what you are going to execute in this article via a simple example. In this sample, there will be three participants. Your application that reads and – with the help of a PHP code- stores your reminders in a database. A database that contains the reminders. A PBX that reads the datas from the database, initiates the calls and reads out the reminder call for the called person.

Preparing your Database Server

In this example, you will need two tables. A table that stores your reminders (phone number, reminder time, datas of the calls), and another one that stores your messages that should be read, or other possible commands that should be executed during calls (playing audio files, record calls, sending SMS or e-mail etc.). Let’s see the first table that you can create with the help of MySQL having run the following SQL script:

CREATE TABLE `ozmlout` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `DialedNumber` varchar(40) NOT NULL,
  `Status` varchar(40) DEFAULT NULL,
  `Duration` int(10) DEFAULT NULL,
  `ScriptId` int(10) DEFAULT NULL,
  `RecordUrl` varchar(150) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `ScheduledTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)) 

Code Example 1 – Create ozmlout table

As you can see, it contains not just the phone number of the called person and the reminder time, but other call datas, too. Such call datas are:

• Status: It indicates the actual condition of your reminder (Init, InCall, Completed, etc.)
• Duration: The complete length of the call
• ScriptID: As soon as the call is picked up, an Extension will execute the OzML script with that ID from the ozmlscrpit table.
• RecordUrl: If there is a call recording amongst the commands, this parameter indicates where the conversation can be listened to.
• StartTime: Time when the call was started.
• ScheduledTime: If the actual time passed the date, the call is going to be initiated.

Then let’s generate the second table. In this table, there are going to be those OzML scripts which are going to be executed after the call is being configurated. All of them have an ID, and this is what we can refer to in a previously generated table. The column containing the OzML script itself is also here, where we can give what we would like to run down when the call is answered. The table can be created having run the following SQL script:

CREATE TABLE `ozmlscripts` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Ozml` varchar(10000) NOT NULL,
  PRIMARY KEY (`ID`)) 

Code Example 2 – Create ozmlscripts table

Configuring the PBX

After you are ready with the build of your database, configure the PBX, too. In this article, we use the Ozeki Phone System XE as a PBX. It can be downloaded from www.ozekiphone.com. Of course, there are similar PBXes to this, too, but for the execution of the actual exercise, I reckon this one as the most convenient and reliable. Furthermore, it is quite easy to handle and configure the components of the system. As the first step, let’s create the SQL OzML Extension:

Initial Steps & SQL OzML

• Step 1: Download, then install Ozeki Phone System XE on your PC
• Step 2: Open the http://127.0.0.1:7777 address in your browser and sign in with the user name/password combination that you have given during the installation.
• Step 3: On the Home screen, click on Add button on the top of Extensions part, that is on the right side.
• Step 4: Choose the SQL OzML from the upcoming list with the help of the Install button
• Step 5: Fill in the signup datas on the Database Connenction tab
• Step 6: Click on the Outgoing Calls tab on the top. Change the following part from the SQL statements templates block:
  SELECT id, dialednumber, scriptid FROM ozmlout WHERE Status='call'to this:
  SELECT Id, DialedNumber, ScriptId FROM ozmlout WHERE Status='call' and (ScheduledTime < Now() or ScheduledTime IS NULL)

Having done these measures, you are ready with the creation of the OzML SQL Extension. Now, please connect on a Softphone to the PBX that the reminder call is going to be forwarded to. We are going to use X-Lite Softphone because it is simple, but you can also use other Softphones available for you, of course.

Connect SoftPhone to PBX

• Step 1: Download, then install X-Lite on your PC (http://www.counterpath.com/x-lite.html)
• Step 2: Launch it, and choose the Softphone/Account Settings menu.
• Step 3: Fill in the User Details block. The Domain should be the IP address of the previously installed and running Ozeki Phone System XE (e.g. 127.0.0.1). The User ID should be 888, this is going to be your X-Lite phone number.
• Step 4: Save it. If you have done all the settings correctly, you will see the appearing Softphone 888 on the surface of Ozeki Phone System XE Extensions.

Using the Code

You are ready with the build of the database and you have installed the PBX and the Softphone. You can start to use the devices in your hand.

At first, you should create two HTML forms that you send the data into the database with:

Appointments Form

This HTML Form store and send:

• Which telephone number should the call reminder be sent to
• When the time of the reminder should be
• Which OzML Script (Script ID) is that should be run when the phone is picked up.

For this, you only have to create the following Form below:

<form action="http://localhost/HandleAppointments.php">
	<label>DialedNumber:</label>  <input name="DialedNumber" 
	type="text" value="+3670123456789"/>
	<label>ScheduledDate:</label> <input name="ScheduledDate" 
	type="date" value="2013-10-03"/>
	<label>ScheduledTime:</label> <input name="ScheduledTime" 
	type="time" value="10:00"/>
	<label>Script ID:</label> <input name="ScriptId" 
	type="number" value="1"/>
	
	<input id="ResultDiv" type="hidden" value="resultDivOfInsert" />
	<input type="submit" />	
</form>  

Code Example 3 – Appointments Form

As you can see, the HandleAppoinment.php file is going to be called during the posting of the Form. This PHP file is going to process your query and insert the given appointment datas to the database. Such a PHP file can look like this:

<?php
if(isset($_POST['DialedNumber']) && isset($_POST['ScheduledDate']) 
&& isset($_POST['ScheduledTime']) && isset($_POST['ScriptId']) )
  {
   	 $con=mysqli_connect("127.0.0.1","root","","mysql");

	//check the state of the connection
	if (mysqli_connect_errno())
	{
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		exit();
	}
	
	$tableExist = mysqli_query($con,"SELECT 1 FROM 'ozmlout'");
	
	//create table, if it is not exist
	if ($tableExist !== true)
	{
		if(mysqli_query($con,"CREATE TABLE ozmlout(
					ID int(10) NOT NULL AUTO_INCREMENT,
					DialedNumber varchar(40) NOT NULL,
					Status varchar(40) DEFAULT NULL,
					Duration int(10) DEFAULT NULL,
					ScriptId int(10) DEFAULT NULL,
					RecordUrl varchar(150) DEFAULT NULL,
					StartTime datetime DEFAULT NULL,
					ScheduledTime datetime DEFAULT NULL,
					PRIMARY KEY (ID))"))
		{
			echo "Table ozmlout created. ";
		}
	}

	//insert data
	$state = mysqli_query($con,"INSERT INTO ozmlout (ID, DialedNumber, 
	Status, Duration, ScriptId, RecordUrl, StartTime, ScheduledTime)
			VALUES (NULL, '".$_POST['DialedNumber']."', 
			'call', NULL, ".$_POST['ScriptId'].", NULL, 
			NULL, '".$_POST['ScheduledDate']." ".
			$_POST['ScheduledTime']."')");
	mysqli_close($con);
	
	if ($state == 1)
		echo "Success";
	else
		echo "Unsuccess";
  }
else
	echo "Unsuccess";
?>

Code Example 4 – Insert Appointments into ozmlout table

It tries to connect to the MySQL database with the default datas. If you have other settings, use them here, too. When you managed to connect, it will execute a simple insert on the previously created ozmlout table, having run the mysqli_query command. Moreover, if such a table did not exist, it creates it before the insert.

It gives a feedback in the form of an Echo about its success to the calling Form. I would notice that where we inserted NULL value, the value is going to be changed during later stages of the process, for example the State column. When a call is initiated, its PBX SQL OzML Extension executes a modification on the table, having changed its value into Calling state. With this measure, we achieve that the Extension is not going to call a telephone number twice (at least according to the basic SQL Template).

Besides this, you have the possibility to get the actual set reminders from the database with the help of a simple PHP file. If you want to have a look at the description of this, too, please download the code example and look at how the GetData.php works.

Scripts Form

With this HTML Form, you have the chance to insert OzML scripts into the database. If you connect it to each ozmlout lines correctly, the commands in the script will run when the call is answered. I show an example for better understanding:

<Response>
	<Speak >
		This text will be read to the called user
	</Speak>
</Response> 

Code Example 5 – OzML Command for Speak a text to the called user

It is only a simple Speak command. Besides this, there are a lot of other commands in the system of the Ozeki Phone System XE, for example for sending SMS and e-mail, recording calls, forwarding calls and many other things, too.

Your Form can look like this:

<form action="http://localhost:8080/HandleScripts.php">
	<label>OzML Commands</label> 
	<input type="text" size="80" 
	name="OzMLCommands" value="<Speak>
	This text will read to the called user</Speak>"/>	
	<input id="ResultDiv" type="hidden" 
	value="resultDivOfInsert" />
	<input type="submit" />
</form>  

Code Example 6 – Scripts Form

During the run of the submit event, a PHP file is going to run similarly to the previous case. The difference is only that now you insert the datas into the ozmlscripts table.

In the attached example code, the data is posted in an asynchronous way with the help of JQuery, and the input that has the ResultDiv ID is used to this. It is only that the actual site is not going to be reloaded, but with the help of an Ajax post, we are going to load the return value of the PHP file to a div reserved for this purpose.

Using the Program

You are ready with the most difficult exercises, now you only have to try your application made.

At first, you have to check that the following programs are running:

• MySql
• Ozeki Phone System XE
• X-Lite

If you are ready with this, run the two forms -written by you -on your Web-server, or download the example codes and run them in each browser window via your web-server.

Firstly, we should pick a new line in the ozmlscripts record with the help of the Scripts Form.

Click on the Submit button. You are going to see a message about the success of the insert. If this message is "Unsuccess”, you should have a look at on the HandleScripts.php file and check whether your signin data is correct.

Look in the database what ID this OzML script got (default is 1, and it is increasing with the insert of each line) Memorize the ID and go to the Appointments Form!

Give the phone number that you would like to receive the reminder to. In this particular case, it is going to be the phone number of the registered line of the X-Lite SoftPhone. In our example, it is 888.

Give the date and time of the reminder. If that time becomes actual, the SQL OzML Extension initiates the call. You can set on the configuration surface of the Extension, how often it should check the content of the database.

The last step is to give that Script ID that you have created before. As soon as the caller picks it up, the commands given there will run.

Click on the Submit button. You can see the success of the insertion from the returning message. If this message is "Unsuccess”, please have a look at on the HandleAppointment.php file and check whether every signin data was given correctly.

After this, the example records are there in the Script and the Appointment table as well. If you did everything correctly, you will receive a call in the given time having played the set text into the call.

If it doesn’t happen, I recommend you to check the log of the SQL OzML Extension. You can easily conclude what the problem can be from that. You can reach this by clicking on the Open button that is next to the SQL OzML Extension on the Home Page. If you are still at a loss, you can get further information by choosing the PBX Features/Logs menu in the above menu.

You are ready with your own Appointment Reminder application with this. If you feel that there is a need for that, you can make any further development free on the application made here, of course.

References

• You can download the Wampserver free, that contains the MySQL, and with the help of it you can run your written application, too, on your own PC: http://www.wampserver.com/en/
• You can download the Ozeki Phone System XE, and you can find more help at www.ozekiphone.com
• The X-Lite SoftPhone can be downloaded from this site: http://www.counterpath.com/x-lite.html

Good luck!