Callcenter developers
If you are working on telephone solutions, please check
out the Ozeki VoIP SIP SDK.
It can be used to create: |
Webphone solutions:
- Adobe Flash video phone
- Silverlight video phone
- Web to web calls
- Web to VoIP calls
Custom SIP clients:
- Silverlight SIP VoIP client
- Flash SIP VoIP client
- C# .net SIP VoIP client
- ASP .net SIP VoIP client
- Web based SIP VoIP client
Custom VoIP solutions:
- VoIP SIP softphones
- VoIP call center clients
- VoIP IVR systems
- VoIP predictive dialer systems
- VoIP auto dialer systems
- VoIP call assistant
- VoIP call recording systems
- VoIP intercom solutions
|
Birthday greeting SMS service with MySQL
1. INTRODUCTION
This documentation provides information on how to setup Ozeki NG - SMS Gateway
if you wish to send birthday greeting SMS messages with the help of MySQL database
server.
Our demand is to send birthday greeting SMS messages to your employees, colleagues,
customers, friends, etc. By setting up this system you do not have to keep all
birthday dates in mind, as the system will automatically keep track of these and
will remind you in time. A birthday greeting can either be an endearment to your
friends and colleagues or a marketing tweak to your customers.
In order to set up the system, read carefully and follow the instructions
provided on this page. You can find the structure of the documentation listed
below:
0. Video tutorials
1. Introduction
2. Requirements
3. Process
4. Configuration steps
5. Conclusion
Please find the video tutorials of this configuration process on the top of the
page.
2. REQUIREMENTS
The prerequisites for launching a birthday greeting service are the following:
1. Ozeki NG – SMS Gateway software
2. MySQL database server
3. Mobile network connection
It is assumed that a working SMS service provider is already configured in
Ozeki NG - SMS Gateway and a database user is already setup. The database user
is connected to the MySQL database.
On the diagram below you can see the system architecture of the birthday
greeting SMS service (Figure 2).
Figure 2 – System architecture
3. PROCESS
The following steps describe how the birthday greeting SMS service system works:
1. Ozeki NG applies a query to the database table regarding the people who
have their birthday the current day and have not yet received a
birthday SMS message
2. The database submits this information to Ozeki NG – SMS Gateway
3. The software sends the birthday message to the SMSC
4. The people having their birthday receive the birthday SMS message to
their mobile phones
4. CONFIGURATION STEPS
To setup a Birthday reminder service we use MySQL database server with MyODBC
Database driver.
Step 1.) Configuring MySQL
To setup a birthday reminder service a database table is needed that will hold
birthday information.
Here is the table definition:
CREATE TABLE birthday (
id int auto_increment primary key,
customername varchar (100),
customerphone varchar(100),
birthdate date,
lastnotified date,
status varchar(30)
);
|
After the table has been defined, create it in the database server, using
MySQL Command Line Client.
For this, open MySQL Command Line Client and insert the table definition.
Now put some test data into this database table.
INSERT INTO birthday (customername, customerphone, birthdate) VALUES ('Elisabeth', '+36301111111', '2009-08-04');
INSERT INTO birthday (customername, customerphone, birthdate) VALUES ('George', '+36202222222', '2009-09-04');
|
Copy this test data and paste it into the MySQL Command Line Client table.
Step 2.) Configuring Ozeki - SMS Gateway
Configure Ozeki NG - SMS Gateway to send birthday reminders based on the SQL
commands in the configuration form of the database user.
The first command is used to pick up messages:
SELECT id, '', customerphone, 'Happy Birthday' FROM birthday
WHERE month(current_date)=month(birthdate) AND day(current_date)=day(birthdate)
AND ( NOT lastnotified=current_date) OR lastnotified is null;
|
Copy this command and insert it into the ‘Polling’ field of the ‘SQL for
sending’ tab within the Ozeki NG – SMS Gateway ‘birthday (sql)’ configuration
form.
The second command is used to update the "lastnotified" information in the
birthday table.
UPDATE birthday SET status='sending',
lastnotified=current_date WHERE id='$id';
|
Copy this update into the ‘Sending’ field on the same form. This will make
sure that the birthday reminder is sent out once only.
Finally, the other SQL commands should also be modified to work with the
birthday table.
To test the service, send a birthday message by setting the date of the
computer to the birthday of one of the persons in the example (e.g. George).
After setting the date to 2009-09-04, you can see in the database user's
‘Events’ window that the message was found in the database, and it was
accepted for delivery. If you look into the database table, you will see
that the "lastnotified" field was updated.
For this, open the MySQL Command Line Client, and apply a query to the birthday
table.
As you can see, the ‘lastnotified’ and ‘status’ fields has changed.
Customization
To customize the Happy Birthday greeting, the SQL statement is needed to be modified.
SELECT id, '', customerphone, CONCAT( 'Hello ', customername, '! The date of your birthday is ', birthdate, '. Congratulations!' )
FROM birthday WHERE MONTH( current_date ) = MONTH( birthdate ) AND DAY( current_date ) = DAY( birthdate )
AND (NOT lastnotified = current_date) OR lastnotified IS NULL;
|
The 4ht term becomes the outgoing sms. Both customer name and birth date are queried from the database.
For example: if you insert this SQL statement into the MySQL command prompt, you will get this output:
+----+--+---------------+---------------------------------------------------------------------------------------------------------+
| id | | customerphone | CONCAT( 'Hello ', customername, '! The date of your birthday is ', birthdate, '. Congratulations!' ) |
+----+--+---------------+---------------------------------------------------------------------------------------------------------+
| 1 | | +36301111111 | Hello Elisabeth! The date of your birthday is 2009-08-04. Congratulations! |
| 2 | | +36202222222 | Hello George! The date of your birthday is 2009-09-04. Congratulations! |
+----+--+---------------+---------------------------------------------------------------------------------------------------------+
|
5. CONCLUSION
By performing the steps provided in this documentation your goal has been
achieved,
you have set up a working birthday greeting SMS service in Ozeki NG – SMS
Gateway software with the help of MySQL database server.
Now the system is ready to be used.
Dig deeper!
People who read this also read...
Next page:
Birthday greeting - Oracle
|
