Ozeki Phone System XE VoIP PBX software
is an advanced PBX built for automated voice calls and 2 way SMS messaging. It
has outstanding APIs for software developers. It can be used for:
Find an SMS sport betting example on this page that will
demonstrate how to build your own SMS system to introduce SMS betting service
for your customers. By downloading the project (available below this page) you
can start the betting service via SMS to make your business more attractive and
let more people know about it. To implement this solution effectively and
properly please follow the configuration guide and watch the video tutorial,
as well.
SMS sport betting service practically means that users can
bet to the outcome of a sport event using their mobile phones. The greatest
advantage of this solution is its simplicity since there is no need for paper
forms but people can send bets via SMS text messages. In this way you can
collect their phone numbers even for further
mobile
marketing purposes or you can achieve that more people will know your
business through your entertaining solution.
How this system works
To be able to implement SMS sport betting service, first, you
need to download and install Ozeki NG SMS
Gateway. This software will provide SMS functionality for your IT environment.
The SMS gateway connects to the mobile network
in two ways. It can operates one or more GSM modem attached to the computer,
called GSM modem connection. Or it can connect
directly to the SMS center of a mobile service provider over the Internet:
IP SMS connection.
After you configure the SMS gateway SMS betting service will work as follows:
Users need to send SMS text messages to a predefined phone number. Ozeki NG SMS
Gateway will process these messages with the help of its ASP user (Learn
how to create an ASP user in Ozeki NG SMS Gateway:
ASP user setup). This ASP user forwards
messages to a MySQL database to be stored. Finally, the
results will appear on the web site (Figure 1). Settings can also be made on
the web site.
Please note that the example below is made for MySQL database but other
databases can also be used with this solution. Ozeki NG SMS Gateway connects
to the database via ODBC
driver.
Figure 1 - System architecture
Configuration guide
I have already downloaded and extracted
ozeki-sms-bet-example.zip
file to the desktop. Figure 1 demonstrates the content of this file:
webpage directory, MySQL-table-structure.txt file, and
sms-bet.aspx file.
Figure 2 - Content of ozeki-sms-bet-example.zip file
Copy the content of webpage to the directory
of the web page (wwwroot). I created an sms-bet directory and copied
webpage into this directory so the full path in this example
is: C:\AppServ\www\sms-bet (Figure 2).
Figure 3 - Copy webpage directory
Open MySQL-table-structure.txt file (Figure 4).
Figure 4 - Open MySQL-table-structure.txt file
MySQL-table-structure.txt file
CREATE DATABASE `bets`;
USE `bets`;
CREATE TABLE `bets` (
`id` int(20) NOT NULL auto_increment,
`sender` varchar(20) NOT NULL,
`originalmsg` varchar(160) NOT NULL,
`formatted_msg` varchar(160) NOT NULL,
`match_number` int(5) NOT NULL,
`team1` varchar(30) NOT NULL,
`team2` varchar(20) NOT NULL,
`time_of_bet` timestamp NOT NULL default CURRENT_TIMESTAMP,
`msgid` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `matches` (
`id` int(10) NOT NULL auto_increment,
`match_number` varchar(10) NOT NULL,
`match_begin` bigint(15) NOT NULL,
`team1` varchar(30) NOT NULL,
`team2` varchar(30) NOT NULL,
`bet_begin` bigint(15) NOT NULL,
`bet_end` bigint(15) NOT NULL,
`final_result` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `settings` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(10) NOT NULL,
`value` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `settings` VALUES (1, 'restrict', 1);
Now copy the content of MySQL-table-structure.txt and
paste it to the SQL console. The content of the file will be executed (Figure 5).
Figure 5 - Execute the content of MySQL-table-structure.txt
Copy the content of sms-bet.aspx file (Figure 6).
Figure 6 - Copy sms-bet.aspx
And paste sms-bet.aspx to its final place. In our example it is
C:\bet\sms-bet.aspx (Figure 7).
Figure 7 - Paste sms-bet.aspx
Install an ASP user in Ozeki
NG SMS Gateway. On the Configuration pane of the user specify the path to the
aspx file. In this example it is C:\bet\sms-bet.aspx (Figure 8).
Figure 8 - Path to sms-bet.aspx file
Open sms-bet.aspx file to be able to customize it. You
can specify the server, the database, the user, and its password (Figure 9).
In our example these parameters are the follows: Server: 127.0.0.1 Database: bets User: root Password: qwe123
Figure 9 - Customize aspx file
sms-bet.aspx file
<%@ Page Language="C#"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%
// Copying the message parameters
string sender = Request.QueryString["sender"];
string messagedata = Request.QueryString["messagedata"];
string messageid = Request.QueryString["messageid"];
// Converting the message datra to lowercase
string data = messagedata.ToLower();
// The country names containing more parts will be considered as one word
data = Regex.Replace(data, "united(.|..|...)states", "unitedstates");
data = Regex.Replace(data, "south(.|..|...)africa", "southafrica");
data = Regex.Replace(data, "korea(.|..|...)republic", "korearepublic");
data = Regex.Replace(data, "korea(.|..|...)dpr", "koreadpr");
data = Regex.Replace(data, "new(.|..|...)zealand", "newzealand");
// Checking the match for the pre-defined message format
bool match = Regex.IsMatch(data, "([0-9]{1,2})([^a-zA-Z0-9]{1,3})([a-zA-Z]*)
([^a-zA-Z0-9]{1,3})([a-zA-Z]*)([^a-zA-Z0-9]{1,3})([0-9]{1,3})([^a-zA-Z0-9]{1,3})([0-9]{1,3})");
// If it not matches, then the message will be treated as an invalid message.
if (!match)
{
bool connectedToDb;
string connectionString = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;
Database=bets;User=root;Password=qwe123;Option=4;";
OdbcConnection oc = new OdbcConnection(connectionString);
oc.Open();
string insertSQL = "insert into bets(`sender`,`originalmsg`,`formatted_msg`,
`match_number`,`team1`,`team2`,`time_of_bet`,`msgid`) values ('" + sender + "','" +
messagedata + "','invalid','0','invalid','invalid',now(),'" + messageid + "');";
OdbcCommand command = new OdbcCommand(insertSQL, oc);
command.ExecuteNonQuery();
oc.Close();
}
else
{
// Replacing the special characters into semicolon.
data = data.Replace('-', ';');
data = data.Replace('.', ';');
data = data.Replace(',', ';');
data = data.Replace('_', ';');
data = data.Replace('<', ';');
data = data.Replace('>', ';');
data = data.Replace('#', ';');
data = data.Replace('&', ';');
data = data.Replace('@', ';');
data = data.Replace('{', ';');
data = data.Replace('}', ';');
data = data.Replace(':', ';');
data = data.Replace('*', ';');
data = data.Replace('\'', ';');
data = data.Replace('\\', ';');
data = data.Replace('"', ';');
data = data.Replace('+', ';');
data = data.Replace('!', ';');
data = data.Replace('%', ';');
data = data.Replace('/', ';');
data = data.Replace('=', ';');
data = data.Replace('|', ';');
data = data.Replace('[', ';');
data = data.Replace(']', ';');
data = data.Replace('(', ';');
data = data.Replace(')', ';');
data = data.Replace('÷', ';');
// Splitting the message by semicolons, and removing the incidental duplicate semicolons
string[] Splitted = data.Split(';', ' ');
string FormattedBet = "";
for (int i = 0; i <= Splitted.Length - 1; i++)
{
if (Splitted[i] != "")
{
FormattedBet = FormattedBet + (Splitted[i] + ";");
}
}
// Bet variable will contain the processed message, in an array
string[] Bet = FormattedBet.Split(';');
bool connectedToDb;
try
{
string connectionString = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;
Database=bets;User=root;Password=qwe123;Option=4;";
OdbcConnection oc = new OdbcConnection(connectionString);
oc.Open();
connectedToDb = true;
try
{
// Ensuring the possibility for the team names could be swithable.
string team1;
string team2;
if ((Bet[1].Substring(0, 3).CompareTo(Bet[2].Substring(0, 3))) < 0)
{
team1 = Bet[1].Trim() + ";" + Bet[3].Trim();
team2 = Bet[2].Trim() + ";" + Bet[4].Trim();
}
else
{
team1 = Bet[2].Trim() + ";" + Bet[4].Trim();
team2 = Bet[1].Trim() + ";" + Bet[3].Trim();
}
// Assembling the SQL query, and inserting the bet.
string insertSQL = "insert into bets(`sender`,`originalmsg`,`formatted_msg`,
`match_number`,`team1`,`team2`,`time_of_bet`,`msgid`) values ('" +
sender + "','" + messagedata + "','" + FormattedBet + "','" + Bet[0].Trim()
+ "','" + team1 + "','" + team2 + "',now(),'"
+ messageid + "');";
OdbcCommand command = new OdbcCommand(insertSQL, oc);
command.ExecuteNonQuery();
oc.Close();
}
catch (Exception z)
{
string errCode = z.Message;
Response.Write(errCode);
}
}
catch (Exception e)
{
string respmsg = "Cannot connect to database." + e.Message;
connectedToDb = false;
Response.Write(respmsg);
}
}
%>
Open query.php file (can be found in
MySQL-table-structure.txt file). You need to specify the server, the user
and its password for the MySQL function. In our example these parameters are
the follows: localhost, root, and qwe123 in order.
Figure 10 - Query.php file
query.php file
<?php
#################################################
### Function name: Query ###
### This helper function queries the database ###
### and returns the results. ###
### parameters: database name, sql statement ###
### returns: the result. ###
#################################################
function Query($dbname,$sql){
$link = mysql_connect('localhost', 'root', 'qwe123') or die
('Could not connect to MySQL database. Reason: ' . mysql_error());
mysql_select_db($dbname) or die ('Cannot select database');
$result = mysql_query($sql);
mysql_close();
return $result;
}
?>
Test the system
I have sent a test message that has been processed by ASP
user (Figure 11). This was a test message and not a bet.
Figure 11 - Test message processed by ASP user
To see the results, click on "view results" on the control pane
on the web site (Figure 12).
Figure 12 - View results
Since my first test message was not a bet, it is considered by
the system as an invalid message (Figure 13).
Figure 13 - Invalid message
Create a new game for betting
First you can enable or disable restrictions to the system. If
you enable it, it means that the system will accept only one bet from one user
during one game period. If you disable this option then users can send several
bets and these bets will be accepted by the system (if they meet the
requirements) (Figure 14).
Figure 14 - Enable/Disable restrictions
In Create game for the next match section I create a
test game:
Ordinal number of the match: it is necessary to specify an identification
number for the game (in this example it is "2"). Users need to include this
number in their SMS bets, without this parameter the bit will be "invalid"
Team1: Specify the name of the first team (France)
Team2: Specify the name of the other team (Uruguay)
Match begin time: Specify the exact date of the match in
Year/Month/Day Hour:Minute:Second format (2010/06/11 20:30:00)
Game period: It is the period in which users can send bets and the system
will accept them if they meet the requirements. You need to specify a
from - to period.
If you do not specify Hour/Minute/Second then the system will count the period
from Hour 00/00/00. So in this example this period is 2010/06/06 - 2010/06/10,
it means that bets are accepted from 2010/06/06 00/00/00 to
2010/06/10 00/00/00
To add the new created game click on Create new game (Figure 15).
Figure 15 - Create new game
Note that the SMS sport betting service only accepts teamnames included in the
$TeamNames array in the newgame.php file.
Now I send a test bet. In the SMS text message the follows need
to be included: Ordinal number of the match, Team1, Team2, Result of Team1,
Result of Team2. Please note that the system is flexible since it is able
to handle cases if users invert the names of teams (it selects only the names
and the order does not matter), however, the names needs to be spelled correctly.
Furthermore, the system picks unnecessary punctuation marks up to 3 characters
between the values in the message. It means that users can write any punctuation
mark in their messages, the system will ignore them up to 3 characters.
If all of the requirements are fulfilled, the system accepts the bet. If
something is missing, or the user misspells the country names the message will
be inserted as an invalid message.
In case of accepted bets, the bet will be inserted into the table of the
respective game which the bet arrived at. The following information is provided
(Figure 16):
ID: this helps identify the bet in the database Sender: the phone number of the sender Original message: shows how the message was received Match number: identifies the game Formatted message: it is the accepted message. It shows the form in which the
message has been processed Team1: (France) the name of the first team Team2: (Uruguay) the name of the other team Status of bet: it shows whether the bet is valid or invalid msgID: this is the message ID that helps identify the message in Ozeki NG
SMS Gateway Time of the match: the starting time of the match Interval of the bidding: it shows the game period in which bets are accepted Time of the bet: it is the time when the bet is received in the system Status according to time: it shows if the message arrives in time, before
the betting period or after the betting period Match result: the final results of the match Won/Lost: it shows that the respective user wins or loses with his bet
Figure 16 - Test bet
Now I enter a test result for the match in Enter the result
of the match section. I specify the result (france-uruguay 3-2) and click
on Insert result (Figure 17).
Figure 17 - Insert result
Since the test bet meets all the requirements (even the result
of the game is agreed) Figure 18 demonstrates that it won the game.
Figure 18 - Won the game
Finally, it is possible to select columns
and by clicking on Export columns to Excel button, the selected columns will
be exported into an Excel file (Figure 19).
Figure 19 - Export columns to Excel
On Figure 20 you can see the exported columns in Excel.
Figure 20 - Exported columns in Excel
If you haven't downloaded Ozeki NG SMS Gateway, download the software or get a
free trial right now:
Download Ozeki NG SMS Gateway
If you have any question, please do not hesitate to contact us at:
info@ozekisms.com
