How to setup Ozeki NG SMS Gateway Autoreply voucher sender

Find an Autoreply example on this page that will demonstrate how to build your own SMS system to introduce voucher service for your customers. To implement this solution effectively and properly please follow the configuration guide below this page.

Configuration guide

Step 1 - Installing Ozeki NG SMS Gateway software

In order to start using Ozeki NG SMS Gateway, first you need to install it to your computer. If you have any problem during the installation process, just follow this guide: Installation Steps.

Step 2 - Connecting the gateway to your service provider

After you have installed the two Ozeki NG SMS Gateway software products, you need to setup at least one service provider connection to send out your SMS messages. There are two ways of sending SMS messages:


You can configure the service provider connections based on the guides that can be found here.

Step 3 - Configuring MySQL

To setup Autoreply service a database table is needed that will hold voucher information. Here is the table definition:

 CREATE TABLE IF NOT EXISTS `voucher` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `voucher` int(11) DEFAULT NULL,
   `phonenumber` varchar(100) DEFAULT NULL,
   `date_time` timestamp DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `id` (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now a stored procedure has to be created in your database server which will help you to create the random voucher.

First you need to login to your database management software. You can use mysql console, phpmyadmin or any other management software. Then select the database in which you created the voucher table and issue the following create script. 

 DELIMITER $$
CREATE PROCEDURE Getphonenumber( IN phone varchar(100))
BEGIN
	DECLARE random INT(11);
	SET random = (select UNIX_TIMESTAMP()+FLOOR(RAND()*1000)+FLOOR(RAND()*9999));
	IF random NOT IN (SELECT voucher FROM voucher) THEN
    	INSERT INTO voucher (phonenumber,voucher, date_time) VALUES(phone, random, NOW());
     SELECT phone, random;       
     END IF;   
     
END $$
DELIMITER ;

Step 4 - Adding new Autoreply Database user and modifying it

After you have created the table and the stored procedure, you need to add a new Autoreply Database user to your Ozeki NG SMS Gateway software product.

new user
Figure 1 - New user

autoreply database user
Figure 2 - Autoreply Database user

In order to connect the gateway to a MySQL database, first you need to install a MySQL ODBC Driver to your Windows operating system and then add a new data source to it which will be connected to your database server.

new data source
Figure 3 - New data source

If you have added the new data source, return to your Ozeki NG SMS Gateway, install a new Autoreply Database user and provide a connection string (related to your database server) under the Database connection tab in the Connection String textbox and select ODBC from the Connection string type dropdown menu. 

Driver={MySQL ODBC 5.1 Driver};Server=localhost;
Database=voucher;User=root;Password=qwe123;Option=4;

provide connection information
Figure 4 - Provide connection information

Now go to the Autoreply script tab and add the path of the sqlscript.txt which default is C:\Program Files (x86)\Ozeki\OzekiNG - SMS Gateway\Config\Autoreply\sqlscript.txt. You have to open this file, delete the last row and add to the following line CALL Getphonenumber ("$sender");

sqlscript.txt file path
Figure 5 - sqlscript.txt path

sqlscript.txt file
Figure 6 - sqlscript.txt

This guide shows you a solution how to send Autoreply voucher message using Ozeki NG SMS Gateway software product. The stored procedure can also be created in different ways. It is also possible to use other database servers, such as MSSQL or Oracle.

If you have any other question, please send us an e-mail at info@ozekisms.com.

More information