Once you have completed the basic setup required to build an IVR system as I have described in the Part 1, let's continue our journey to complete the remaining tasks:
- Create HANA table & view. Import the data from the CSV file to the HANA table.
- Configure Linksys SPA3102 voice modem gateway in order to connect to the IVR Asterisk server.
- Adding the VoIP gateway to the IVR Asterisk server
- Create a dialplan. The dialplan specifies how to interpret digit sequences dialed by the user and how to convert those sequences into a meaningful action. In our case, the dialplan will execute the shell script to query the HANA SQL database in order to get the net value of the particular billing document number.
- Create the Node.JS shell script to connect to HANA database or ODATA service (if the source is ODATA).
- Test IVR system. To test the IVR configuration that we have done and check if everything is being setup correctly.
- Demo video.
Create HANA Table & View
- IVR.hdbschema
schema_name="IVR";
- KNA1.hdbtable
table.schemaName = "IVR";
table.tableType = COLUMNSTORE;
table.description = "IVR";
table.loggingType = NOLOGGING;
table.columns = [
{name = "MANDT"; sqlType = NVARCHAR; length = 3; nullable = false;},
{name = "KUNNR"; sqlType = NVARCHAR; length = 10; nullable = false;},
{name = "LAND1"; sqlType = NVARCHAR; length = 3; nullable = false;},
{name = "NAME1"; sqlType = NVARCHAR; length = 35; nullable = false;},
{name = "NAME2"; sqlType = NVARCHAR; length = 35; nullable = false;},
{name = "ORT01"; sqlType = NVARCHAR; length = 35; nullable = false;},
{name = "PSTLZ"; sqlType = NVARCHAR; length = 10; nullable = false;},
{name = "REGIO"; sqlType = NVARCHAR; length = 3; nullable = false;},
{name = "SORTL"; sqlType = NVARCHAR; length = 10; nullable = false;},
{name = "STRAS"; sqlType = NVARCHAR; length = 35; nullable = false;},
{name = "TELF1"; sqlType = NVARCHAR; length = 16; nullable = false;},
{name = "TELFX"; sqlType = NVARCHAR; length = 31; nullable = false;}
];
table.primaryKey.pkcolumns = ["KUNNR"];
- VBRK.hdbtable
table.schemaName = "IVR";
table.tableType = COLUMNSTORE;
table.description = "IVR";
table.loggingType = NOLOGGING;
table.columns = [
{name = "MANDT"; sqlType = NVARCHAR; length = 3; nullable = false;},
{name = "VBELN"; sqlType = NVARCHAR; length = 10; nullable = false;},
{name = "FKART"; sqlType = NVARCHAR; length = 4; nullable = false;},
{name = "WAERK"; sqlType = NVARCHAR; length = 5; nullable = false;},
{name = "VKORG"; sqlType = NVARCHAR; length = 4; nullable = false;},
{name = "NETWR"; sqlType = NVARCHAR; length = 15; nullable = false;},
{name = "KUNAG"; sqlType = NVARCHAR; length = 10; nullable = false;}
];
- ATTRIBUTE_IVR.attributeview
Join the KUNNR.KNA1 with KUNAG.VBRK and import the data in the CSV format into these two tables. I have attached the sample data in CSV format. And then perform the select query to the field NETWR which is consist of the net value:
select NETWR from "_SYS_BIC"."ivr.hana/ATTRIBUTE_IVR" Where VBELN='" + val + "'";
Asterisk PBX Console
You have already installed the PBX server on the part 1. To view the console and verify it is running, execute the command: asterisk -r in the raspberry pi console.
If you want to quite the console, just type exit.
To control and manage the Asterisk configuration from the web console using FreePBX, just open the web browser and type http://<Raspberr_Pi_Address> :
Log on to "Free PBX Administration" with userid admin and password that you have defined in the part 1 - Preparing the PBX server: Step 3 - Installing IncrediblePBX.
Once you have successfully logged-on, you will see the system resources consumption statistic and status.
Configure Linksys SPA3102
Linksys SPA3102 acts as a VoIP gateway between the PSTN line and network.
Once you have done the step "Setup the Voice Modem Gateway" in Part 1, connect a handset to phone plug and press **** to enter the configuration menu.
- To get the IP address:
dial 110# and note down the IP address - To enable Web Interface:
dial 7932# followed by 1# and 1 - To reset the admin's password, press **** followed by 73738#, and confirm with 1
- Open the web browser and type http://linksys_ip_address and click on Admin Login and advanced:
- Wan Setup
- Lan Setup
- Voice > System
- Voice > SIP
- Regional (for Singapore)
In case of echo, adjust the value of FXS Port Impedance and FXS Port Output/Input Gain.
More information about the regional settings, please refer to these links:
http://www.3amsystems.com/wireline/tone-search.htm
http://www.3amsystems.com/wireline/daa-search.htm - Line 1
Set the SIP port of Line 1 to 5060.
Set the proxy to IP address of Raspberry PI (PBX server), user ID: line1 with password: papamama
- PSTN Line
Set the SIP port of PSTN Line to 5061.
Set the proxy to IP address of Raspberry PI (PBX server), user ID: pstn with password: papamama
"S0<:123@192.168.0.12>" means that incoming PSTN calls will call the extension 123 on the PBX.
Please make sure you set the correct value of Disconnect Tone and FXO Port Impedance. PSTN Answer Delay is the number of seconds before the SPA3102 will call the PBX.
In case of echo, adjust the value of SPA to PSTN Gain and PSTN to SPA Gain.
Adding the VoIP Gateway to the IVR Asterisk Server
From the web console FreePBX, select Connectivity > Trunk > Add SIP Trunk:
We will add trunk line1 and pstn:
[line1]
disallow=all
type=friend
host=dynamic
context=internal
username=line1
secret=papamama
mailbox=line1@internal
nat=force_rport,comedia
canreinvite=no
dtmfmode=rfc2833
qualify=yes
allow=g722
allow=silk8
allow=silk16
allow=silk24
allow=ulaw
allow=alaw
allow=gsm
allow=h263
videosupport=yes
[pstn]
disallow=all
type=friend
host=dynamic
context=pstn
username=pstn
secret=papamama
mailbox=pstn@pstn
nat=force_rport,comedia
canreinvite=no
dtmfmode=rfc2833
qualify=yes
insecure=port,invite
allow=g722
allow=silk8
allow=silk16
allow=silk24
allow=ulaw
allow=alaw
Click Submit Changes and Apply Config. Do the same for pstn trunk.
The configuration will be saved in the file /etc/asterisk/sip_additional.conf. Just open the file and see if the config is there.
Create Dialplan
We will create a simple IVR flow in the dialplan. We start with a greeting "Welcome to IVR HANA Demo" as described in the below diagram:
In the Raspberry Pi console, go to /etc/asterisk and modify the extensions_custom.conf:
cd /etc/asterisk
nano extensions_custom.conf
Add the following lines and save it. Any incoming call from the pstn or line1 will be routed to extension 123 in the PBX. Asterisk uses the Google Text to Speech for the IVR agent.
[pstn]
exten => 123,1,Answer
exten => 123,n,Set(VOLUME(TX)=10)
exten => 123,n,Set(VOLUME(RX)=10)
exten => 123,n,Set(TIMEOUT(digit)=7)
exten => 123,n,Set(TIMEOUT(response)=10)
exten => 123,n,agi(googletts2.agi, "Welcome to IVR Hana Demo")
exten => 123,n,agi(googletts2.agi, "Please type your 8 digit billing document number after the beep")
exten => 123,n,NoOp(STORE NUMBER: ${DIGIT8})
exten => 123,n,Read(DIGIT8,beep,8)
exten => 123,n,GotoIf($["${DIGIT8}" = ""]?dial2)
exten => 123,n,agi(googletts2.agi, "Please hold for response.")
exten => 123,n,agi(ivrhana.sh,${DIGIT8})
exten => 123,n,NoOp(Received answer: ${answer})
exten => 123,n,GotoIf($["${answer}" = "No"]?dial1)
exten => 123,n,agi(googletts2.agi,"The net value is ${answer}. Thank you")
exten => 123,n,Hangup
exten => 123,n(dial1),agi(googletts2.agi,"No Result found")
exten => 123,n,Hangup
exten => 123,n(dial2),agi(googletts2.agi,"You did not type any number. Good bye")
exten => 123,n,Hangup
[internal]
exten => 123,1,Answer
exten => 123,n,Set(TIMEOUT(digit)=7)
exten => 123,n,Set(TIMEOUT(response)=10)
exten => 123,n,agi(googletts2.agi, "Welcome to IVR Hana Demo")
exten => 123,n,agi(googletts2.agi, "Please type your 8 digit billing document number after the beep")
exten => 123,n,NoOp(STORE NUMBER: ${DIGIT8})
exten => 123,n,Read(DIGIT8,beep,8)
exten => 123,n,GotoIf($["${DIGIT8}" = ""]?dial2)
exten => 123,n,agi(googletts2.agi, "Please hold for response.")
exten => 123,n,agi(ivrhana.sh,${DIGIT8})
exten => 123,n,NoOp(Received answer: ${answer})
exten => 123,n,GotoIf($["${answer}" = "No"]?dial1)
exten => 123,n,agi(googletts2.agi,"The net value is ${answer}. Thank you")
exten => 123,n,Hangup
exten => 123,n(dial1),agi(googletts2.agi,"No Result found")
exten => 123,n,Hangup
exten => 123,n(dial2),agi(googletts2.agi,"You did not type any number. Good bye")
exten => 123,n,Hangup
The variable DIGIT8 stores the 8-digit numbers input from the user and pass it to shell script ivhrana.sh. The output/result will be stored in the variable answer.
Let's create the shell script. Go to /var/lib/asterisk/agi-bin and create ivrhana.sh:
cd /var/lib/asterisk/agi-bin
nano ivrhana.sh
Add the following lines and save it:
#!/bin/bash
# Do some work and set the value of 'answer'
VALUE=`/usr/local/bin/node /root/hana/node-hdb/ivrhana.js $1`
#VALUE=1234 # Value passed back for 'asnwer'
echo -e "SET VARIABLE answer $VALUE"
Create the Node.JS script: ivrhana.js
Go to /root/hana/node-hdb and create ivrhana.js:
cd /root/hana/node-hdb
nano ivrhana.js
Add the following lines and save it.
process.argv.forEach(function (val, index, array) {
if (index==2) {
var hdb = require('hdb');
var client = hdb.createClient({
host : 'hana2.vm.cld.sr',
port : 30015,
user : 'SYSTEM',
password : 'password'
});
client.connect(function (err) {
if (err) {
return console.error('Connect error', err);
}
var url = "select NETWR from \"_SYS_BIC\".\"ivr.hana/ATTRIBUTE_IVR\" Where VBELN='" + val + "'";
//console.log(url);
client.exec(url, function (err, rows) {
client.end();
if (err) {
return console.error('Execute error:', err);
}
if(!isEmptyObject(rows)) {
console.log(rows[0].NETWR);
} else {
console.log('No Result');
}
});
});
//console.log(index + ': ' + val);
}
});
function isEmptyObject(obj) {
return !Object.keys(obj).length;
}
The above script performs a query to HANA database by executing the select SQL statement and write the result to the console. Update the host, user ID and password with yours. To run the script, type the following command:
node ivrhana.js <billing_document_number>
You also can connect to ODATA service instead of HANA database. Below is the snippet of JavaScript: getMaterialDescr.js to query to ODATA service "/sap/opu/odata/sap/ZGW_MATERIAL_SRV/Materials('0009620-081')" to get the material description:
var http = require('http'),
xml2js = require('xml2js');
var username = 'username',
password = 'password';
var sapgw = {
host: 'sapnetweavergatewayserver.com',
port: 8000, // Change the port number accordingly
path: "/sap/opu/odata/sap/ZGW_MATERIAL_SRV/Materials('0009620-081')",
headers: {
'Authorization': 'Basic ' + new Buffer(username + ':' + password).toString('base64')
}
}
request = http.get(sapgw, function(res){
var body = "";
res.on('data', function(data) {
body += data;
});
res.on('end', function(result) {
//console.log(body);
var tag = 'd:MatlDesc'; // Print the material description
var value = getValue(tag,body);
console.log(value);
});
res.on('error', function(e) {
console.log("Got error: " + e.message);
});
});
function getValue(tag,xmlString){
var value;
var tempString;
var startTag,endTag;
var startPos,endPos;
startTag = "<"+tag+">";
endTag = "</"+tag+">";
tempString=xmlString;
startPos = tempString.search(startTag) + startTag.length;
endPos = tempString.search(endTag);
value = tempString.slice(startPos,endPos);
return value;
};
Test IVR System
On your Raspberry Pi console, type:
asterisk -r
reload
sip reload
sip show peers
Once you have executed the last command, you will see the status of pstn line is active.
Dial your IVR hotline number and after the "beep", type in the billing document number "30247008". The system will speak out the net value "566.05".
If you didn't hear anything, please check again the configuration on the Linksys SPA3102 and the Asterisk PBX. The IVR hotline number is my landline number.
To debug, type the following command at the Asterisk's command prompt:
core set verbose 5
Redial again and see the information showed in the command prompt and check if there is any error. Below is the debug screenshot:
Instead of calling the pstn line, you also can dial in from the network using the SIP client. Download and install the Linphone in your iPhone/Android and configure the SIP address.
Demo Video
Conclusion
In this blog, we have walked through how to configure the PBX server using the Asterisk, writing a dialplan, installing Node.JS and SAP HANA Database Client for Node, creating the HANA Table & View and also write a JavaScript to connect to the SAP HANA Database/ODATA service.
You can improve the IVR system to response on the voice input using the voice recognition system and also to gather more complex business scenario.
Please feel free to drop me any email/question and see you until next time :smile:
- SAP Managed Tags:
