Turn Your Handwriting into a Font

Standard

I was reading this blog by Christine and I thought it was a good idea to try and share it here. Turn your handwriting into a font and use it in your SAPUI5 application.

How-to-turn-your-handwriting-into-a-font-for-free.jpg

IMG_0785.PNG

Just follow the instruction from Christine’s blog above and download the output TTF file: myfont.ttf.

Now is the SAPUI5 part.

Declare the font-face and .sapMLabel tag in your CSS:

<style>

@font-face {

font-family: ‘myfont’;

src: url(‘fonts/myfont.ttf’) format(‘truetype’);

}

.sapMLabel {

font-family: ‘myfont’ !important;

font-size: 1.5rem;

font-weight: normal;

}

</style>

And print out some words:

  1. <script>
  2.   var app = new sap.m.App(“IconFontApp”, {initialPage:“page”});
  3.   var page = new sap.m.Page(“page”, {
  4.   title: “Hello World this is testing of SAPUI5”,
  5.   content : new sap.m.Label({text:“Hello Mobile World! Welcome to the SAPUI5”})
  6.   });
  7.   app.addPage(page);
  8.   app.placeAt(“content”);
  9.     </script>

The complete code can be found in the attachment.

Advertisements

Consolidate JSON Data from Multiple Queries into a Model

Standard

I would like to share how to load and consolidate JSON data (with the same structure) from OData services into one JSON model.

Let’s assume you need to load the following OData services into one JSON model:

/sap/opu/odata/sap/ZGW_SRV/lpadSet?&$filter=ActivityGroup%20eq%20′” + menu[a] + “‘&$format=json”

Where menu[a] is a different source of OData service.

  1. var oBundle = jQuery.sap.resources({url : “res/menu.properties”});
  2. var menu = oBundle.getText(“LOCAL_MENU”).split(“,”);

Here is a sample of JSON data structure:

{

“d”: {

“results”: [

{

“__metadata”: {

“id”: “https://sapnetweaver.com:8200/sap/opu/odata/sap/ZGW_SRV/lpadSet(‘SAP_BW_GLB_FIN_MENU‘)”,

“uri”: “https://sapnetweaver.com:8200/sap/opu/odata/sap/ZGW_SRV/lpadSet(‘SAP_BW_GLB_FIN_MENU‘)”,

“type”: “ZGW_SRV.lpad”

},

“ActivityGroup”: “SAP_BW_GLB_FIN_MENU”,

“ParentId”: “0000000011”,

“ObjectId”: “0000000002”,

“NodeType”: “F”,

“Text”: “Asset Accounting”,

“PDFUrl”: “”,

“Menulevel”: “02”,

“Description”: “”,

“Url”: “”

},

{

“__metadata”: {

“id”: “https://sapnetweaver.com:8200/sap/opu/odata/sap/ZGW_SRV/lpadSet(‘SAP_BW_GLB_SD_MENU‘)”,

“uri”: “https://sapnetweaver.com:8200/sap/opu/odata/sap/ZGW_SRV/lpadSet(‘SAP_BW_GLB_SD_MENU‘)”,

“type”: “ZGW_SRV.lpad”

},

“ActivityGroup”: “SAP_BW_GLB_SD_MENU”,

“ParentId”: “0000000011”,

“ObjectId”: “0000000003”,

“NodeType”: “F”,

“Text”: “Accounts Payable”,

“PDFUrl”: “”,

“Menulevel”: “02”,

“Description”: “”,

“Url”: “”

}

]

}

}

Let’s defining the necessary variables. Variable ml is the counter to indicate how many Ajax query we have perform. Variable outputdata is an array to store the result of OData query.

  1. var outputdata = [];
  2.   oModel = new sap.ui.model.json.JSONModel();
  3.   oModel.setSizeLimit(500);
  4.   console.log(menu.length);
  5.   var ml = 0;
  6.   var rund = true;

Create a function timeout() to check if all Ajax queries have been completed. How do we know? by checking if the menu length is the same as the variable ml. Once it has been completed, put the outpdata into a JSON model and exit the loop.

  1. function timeout() {
  2.   function run() {
  3.          if(menu.length==ml && rund == true){
  4.           rund = false;
  5.           console.log(“Done”);
  6.           clearInterval(myVar);
  7.   oModel.setData({
  8.   modelData:outputdata
  9.          });
  10.   oController.getView().setBusy(false);
  11.   console.log(oModel);
  12.          }
  13.      }
  14.   var myVar = setInterval(run, 10);
  15.   }

Set Busy dialog and run the timeout() function:

  1. oController.getView().setBusy(true);
  2.   timeout();

Perform a loop based on the total number of query in menu[a].

  1. for (a in menu ) {
  2.   $.when(
  3.   $.get(“/sap/opu/odata/sap/ZGW_SRV/lpadSet?&$filter=ActivityGroup%20eq%20′” + menu[a] + “‘&$format=json”)
  4.       .success(function(data) {
  5.       for (z=0; z<data.d.results.length; z++){
  6.       outputdata.push({ActivityGroup: data.d.results[z].ActivityGroup, Description: data.d.results[z].Description, Menulevel: data.d.results[z].Menulevel, NodeType: data.d.results[z].NodeType, ObjectId: data.d.results[z].ObjectId, PDFUrl: data.d.results[z].PDFUrl, ParentId: data.d.results[z].ParentId, Text: data.d.results[z].Text, Url: data.d.results[z].Url });
  7.       }
  8.       ml = ml + 1;
  9.       })
  10.       .error(function(jqXHR, textStatus, errorThrown) {
  11.       })
  12.   ).fail(function() {
  13.   ml = ml + 1;
  14.   }
  15.   ).done(function() {
  16.   });
  17.   }

We perform the Ajax “Get” query. If is success, under success function we put the result in outputdata array and increase the counter of variable ml.

  1. .success(function(data) {
  2.       for (z=0; z<data.d.results.length; z++){
  3.            outputdata.push({ActivityGroup: data.d.results[z].ActivityGroup, Description: data.d.results[z].Description, Menulevel: data.d.results[z].Menulevel, NodeType: data.d.results[z].NodeType, ObjectId: data.d.results[z].ObjectId, PDFUrl: data.d.results[z].PDFUrl, ParentId: data.d.results[z].ParentId, Text: data.d.results[z].Text, Url: data.d.results[z].Url });
  4.       }
  5.       ml = ml + 1;
  6.       })

If fail to get the result, we also increase the counter of variable ml.

  1. ).fail(function() {
  2.        ml = ml + 1;
  3.   }

Let’s test it out.

In the below example, the total number of query is 7. I have disabled 6 so only 1 is working. We managed to captured all the information and stored in JSON model: modelData.

blog1.jpg

That’s it for now. Thanks for reading my blog and let me know if you have any comments.

Create QR Barcode Scanner Function in SAP Screen Personas

Standard

For this to be working, you need to use the Google Chrome browser and desktop/laptop with webcam.

b1.jpg

Step by Step to Create the QR Code Scanner

  1. Create two scripts in SAP Screen Personas: Scan QR Code and Get QR Code.
    b3.jpg
  2. Scan QR Code will call the javascript that will launch the HTML5 QR Barcode Scanner.
    b2.jpg
    The javascript code as follows:

    1. var pageURL = ‘http//<sap_netweaver>/sap/bc/ui5_ui5/sap/ZBarcode1/index.html’;
    2. var w = 400;
    3. var h = 400;
    4. var left = (screen.width – w) / 2;
    5. var top = (screen.height – h) / 4;
    6. var targetWin = window.open(pageURL, ‘toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width=’ + w + ‘, height=’ + h + ‘, top=’ + top + ‘, left=’ + left);
    7. var timer = setInterval(checkChild, 500);
    8. function checkChild(val) {
    9. if (targetWin.closed) {
    10. clearInterval(timer);
    11. }
    12. }
  3. Get QR Code will get the barcode value from the browser local storage qrcode, copy the value to textfield and clear the local storage.
    b4.jpg
  4. Link the textfield to the Get QR Code button.
    b5.jpg
  5. And finally we need to hide the Get QR Code button.

HTML5 QR Barcode Scanner

For the QR barcode scanner, I have modified a little bit of source-code (index.html & webqr.js)  from https://github.com/LazarSoft/jsqrcode.

In the webqr.js, I have modified the code in the read function:

  1. function read(a) {
  2.   localStorage.setItem(‘qrcode’, a); // variable a is the value of QR code
  3.   localStream.stop(); //stop the webcam stream
  4.   var html = “<br>”;
  5.   if (a.indexOf(http://&#8221;) === 0 || a.indexOf(https://&#8221;) === 0)
  6.   html += “<a target=’_blank’ href='” + a + “‘>” + a + “</a><br>”;
  7.   html += “<b>” + htmlEntities(a) + “</b><br><br>”;
  8.   document.getElementById(“result”).innerHTML = html;
  9.   closeWindows(); //close the child window
  10. }

It’s basically to store the QR code result (variable a) in the browser local storage qrcode, stop the webcam stream and close the child window, closeWindows();

The completed source code can be found in the attached file or in my GitHub: ferrygun/PersonasQRBarcode · GitHub

Demo Video

In summary we can add the barcode scanner functionality in the SAP Personas with help from HTML5 localstorage. Thank you for reading my blog.

SAPUI5 WebSocket Chat with WhatsApp

Standard

Intro

I would like to show you how to create a WebSocket chat application to send and receive a WhatsApp message from the SAPUI5 web browser with the open source Yowsup.

c1.jpgc2.jpg

c3.PNG

How it works?

c4.jpg

Firstly, client will establish the connection to the server via the websocket address: ws://localhost:8080/fd6/websocket and register the following event listeners:

onerror: When a WebSocket encounters a problem, the “onerror” event handler is called.

onopen: When a WebSocket in the open state, the “onopen” event handler is called.

onmessage: When a WebSocket receives new data from the server, the “onmessage” event handler is called.

  1. var webSocket = new WebSocket(‘ws://localhost:8080/fd6/websocket’);
  2.   webSocket.onerror = function(event) {
  3.   onError(event)
  4.   };
  5.   webSocket.onopen = function(event) {
  6.   onOpen(event)
  7.   };
  8.   webSocket.onmessage = function(event) {
  9.   onMessage(event)
  10.   };

Once the connection to the server is established, it can send and receive the WhatsApp message with the following steps:

Send Message to WhatsApp

Step 1

When user sends a message from the browser, the function sendMsg() will send the message to the server: webSocket.send(msg);

  1. function sendMsg() {
  2.   var msg = oMsg.getValue();
  3.   if (msg == “”)
  4.   notify(‘Info’‘Please enter a message’‘information’);
  5.   else {
  6.   webSocket.send(msg);
  7.   oMsg.setValue();
  8.   oMsg.focus();
  9.   lastMsg = oModel.oData.chat;
  10.   if (lastMsg.length > 0)
  11.   lastMsg += “\r\n”;
  12.   oModel.setData({
  13.   chat : lastMsg + “You:” + msg
  14.   }, true);
  15.   $(‘#chatBox’).scrollTop($(‘#chatBox’)[0].scrollHeight);
  16.   }
  17.   return false;
  18.   }

Step 2

Method goInteractive in the python Yowsup code listens for the incoming message from the server: message = self.ws.recv() and send the message to the WhatsApp: msgId = self.methodsInterface.call(“message_send”, (jid, message))

  1. def goInteractive(self, jid):
  2.   print(“Starting Interactive chat with %s” % jid)
  3.   jid = “%s@s.whatsapp.net” % jid
  4.   print(self.getPrompt())
  5.   while True:
  6.   message = self.ws.recv()
  7.   if not len(message):
  8.   continue
  9.   if not self.runCommand(message.strip()):
  10.   msgId = self.methodsInterface.call(“message_send”, (jid, message))
  11.   self.sentCache[msgId] = [int(time.time()), message]
  12.   self.done = True

Receive Message from WhatsApp

Step 3

If there is any incoming message from WhatsApp, method onMessageReceived is called. It will send the message to the server: self.ws.send(messageContent)

  1. def onMessageReceived(self, messageId, jid, messageContent, timestamp, wantsReceipt, pushName, isBroadcast):
  2.   if jid[:jid.index(‘@’)] != self.phoneNumber:
  3.   return
  4.   formattedDate = datetime.datetime.fromtimestamp(timestamp).strftime(‘%d-%m-%Y %H:%M’)
  5.   print(“%s [%s]:%s”%(jid, formattedDate, messageContent))
  6.   if wantsReceipt and self.sendReceipts:
  7.   self.methodsInterface.call(“message_ack”, (jid, messageId))
  8.   print(self.getPrompt())
  9.   self.ws.send(messageContent)

Step 4

Finally, the function onMessage will print out the  incoming message from the server to the client browser.

  1. function onMessage(event) {
  2.   msg = event.data
  3.   lastMsg = oModel.oData.chat;
  4.   if (lastMsg.length > 0)
  5.   lastMsg += “\r\n”;
  6.   oModel.setData({
  7.   chat : lastMsg + “WhatsApp:” + msg
  8.   }, true);
  9.   $(‘#chatBox’).scrollTop($(‘#chatBox’)[0].scrollHeight);
  10.   notify(‘WhatsApp’, msg, ‘information’);
  11.   }

To display the message, I am using noty – a jQuery Notification Plugin:

  1. function notify(title, text, type) {
  2.   // [alert|success|error|warning|information|confirm]
  3.   noty({
  4.   text : text,
  5.   template : ‘<div class=”noty_message”><b>’
  6.   + title
  7.   + ‘:</b> <span class=”noty_text”></span><div class=”noty_close”></div></div>’,
  8.   layout : “center”,
  9.   type : type,
  10.   timeout : 4000
  11.   });
  12.   }

It’s pretty easy to use and implement the WebSocket in SAPUI5. Thanks for reading my blog and let me know if you have any comments/questions. Enjoy chatting !

Source Code:

I have attached the complete source code fd6.war (rename fd6.war.txt to fd6.war) and the modified Yowsup python code in the GitHub:

ferrygun/WebSocketChat · GitHub

References:

Playing with WhatsApp and SAP HANA Cloud Platform

Standard

Let’s do something fun with WhatsApp and SAP HANA Cloud Platform. We will build an app whereby you can take a picture from your smartphone and send it via WhatsApp, save it to HANA database and finally view the picture from the browser. I named it as “Whana” = WhatsApp and Hana

 

b10.jpg

Prerequisites

 

 

Diagram

 

Presentation1.jpg

 

Yowsup Installation & Configuration

 

After you have downloaded the Yowsup library, copy the folder yowsup-master to the Python folder (in my case is C:\Python34). You also need to download the python-dateutil and copy dateutil folder to C:\Python34\Lib.

 

Now go to https://coderus.openrepos.net/whitesoft/whatsapp_sms to get the WhatsApp code. Enter your dedicated mobile phone number and select SMS.

b3.jpg

If no error, you will get the SMS message and note down the number:

WhatsApp code 192-828

 

Under C:\Python34\yowsup-master\src, copy the config.example to yowsup-cli.config, and indicate the following:

cc=country code; for example: 65

phone=your phone number: for example: 651234567 (without +)

leave the id and password blank at the moment.

 

Execute the following command to register with WhatsApp:

python C:\Python34\yowsup-master\src\yowsup-cli –register 192-828 –config C:\Python34\yowsup-master\src\yowsup-cli.config

 

If no error, you will get the following message:

status: ok

kind: free

pw: S1nBGCvZhb6TBQrbm2sQCfSLkXM=

price: 0,89

price_expiration: 1362803446

currency: SGD

cost: 0.89

expiration: 1391344106

login: 651234567

type: new

 

Copy the password S1nBGCvZhb6TBQrbm2sQCfSLkXM= and paste into pw field in yowsup-cli.config. In the end your yowsup-cli.config will look like this:

 

cc=65

phone=651234567

id=

password=S1nBGCvZhb6TBQrbm2sQCfSLkXM=

 

Now let’s test to send a message to this phone number 6597312234. Execute the following command:

C:\Python34>python C:\Python34\yowsup-master\src\yowsup-cli –send 6597312234 “Test message” –config C:\Python34\yowsup-master\src\yowsup-cli.config

If no error, you will see the following result and the message will be sent to 6597312234:

Authed 651234567

Sent message

 

I have added the functions to save the image received from the WhatsApp to the local folder: C:\java\imageWA. Just overwrite the CmdClient.py in the  folder Example and downloader.py in folder Media.

 

 

 

Java Folder Monitoring

On this part, we will create the Java program to monitor the folder where the Yowsup stored the WhatsApp image (i.e., C:\java\imageWA) and check if there is any image file. If there is, the program will read the file and insert into the HANA database.

Below is the snippet of the java code:

 

  1. static final String IMGFolder = “C:\\java\\imageWA”;
  2. String INSERT_PICTURE = “INSERT INTO \”NEO_CG2SX3P5XHHQEO58DKM7BWU0V\”.\”p1940803061trial.fd2.data::mytable\” VALUES(?,?)”;
  3. public synchronized void insert(String fileName) throws Exception, IOException, SQLException{
  4.   FileInputStream fis = null;
  5.   PreparedStatement ps = null;
  6.   Date currentDate = new Date();
  7.   String s = Long.toString(currentDate.getTime() / 1000);
  8.   System.out.println(s);
  9.   try {
  10.   System.out.println(“filename: “ + fileName);
  11.   File file = new File(IMGFolder + “\\” + fileName);
  12.   fis = new FileInputStream(file);
  13.   ps = conn.prepareStatement(INSERT_PICTURE);
  14.   ps.setString(1, s);
  15.   ps.setBinaryStream(2, fis, (int) file.length());
  16.   int rowsInserted = ps.executeUpdate();
  17.   conn.commit();
  18.   if (rowsInserted > 0) {
  19.   System.out.println(“A new record was inserted successfully!”);
  20.   }
  21.   if(file.delete()){
  22.   System.out.println(file.getName() + ” is deleted!”);
  23.   }
  24.   else{
  25.     System.out.println(“Delete operation is failed.”);
  26.     }
  27.   } finally {
  28.   ps.close();
  29.   fis.close();
  30.   }
  31.   }

 

HANA Cloud Platform Setup

 

I will go through the key important files. You can find the complete source code on GitHub: https://github.com/ferrygun/Whana

b4.jpg

mytable.hdbtable

 

We need to create a table, mytable.hdbtable in SAP HANA database to store the image from the WhatsApp.

 

mytable.hdbtable
table.schemaName = “NEO_CG2SX3P5XHHQEO58DKM7BWU0V”;table.tableType = COLUMNSTORE;table.description = “My Table”;

table.columns = [

{name = “File_Name”; sqlType = NVARCHAR; nullable = true; length=20;},

{name = “File_Content”; sqlType = BLOB; nullable = true;}

];

 

b5.jpg

 

GetImage.xsjs

Create the GetImage.xsjs to retrieve the image binary stream from the HANA database:

  1.   var id = $.request.parameters.get(‘id’);
  2.     var conn = $.db.getConnection();
  3.     try {
  4.         var query = “Select \”File_Content\” From \”NEO_CG2SX3P5XHHQEO58DKM7BWU0V\”.\”p1940803061trial.fd2.data::mytable\” Where \”File_Name\” = “ + id;
  5.         var pstmt = conn.prepareStatement(query);
  6.         var rs = pstmt.executeQuery();
  7.         rs.next();
  8.         $.response.headers.set(“Content-Disposition”“Content-Disposition: attachment; filename=filename.jpg”);
  9.         $.response.contentType = ‘image/jpg’;
  10.         $.response.setBody(rs.getBlob(1));
  11.         $.response.status = $.net.http.OK;
  12.     } catch (e) {
  13.         $.response.setBody(“Error while downloading : “ + e);
  14.         $.response.status = 500;
  15.     }
  16.     conn.close();

 

GetFileName.xsjs

 

Create the GetFileName.xsjs to list down all the image file names so user can select  from the SAPUI5 table:

  1. var query = “Select \”File_Name\” From \”NEO_CG2SX3P5XHHQEO58DKM7BWU0V\”.\”p1940803061trial.fd2.data::mytable\” “;
  2. function close(closables) {
  3.     var closable;
  4.     var i;
  5.     for (i = 0; i < closables.length; i++) {
  6.         closable = closables[i];
  7.         if (closable) {
  8.             closable.close();
  9.         }
  10.     }
  11. }
  12. function getFileName() {
  13.     var FNameList = [];
  14.     var connection = $.db.getConnection();
  15.     var statement = null;
  16.     var resultSet = null;
  17.     try {
  18.         statement = connection.prepareStatement(query);
  19.         resultSet = statement.executeQuery();
  20.         while (resultSet.next()) {
  21.             var fname = {};
  22.             fname.file_name = resultSet.getString(1);
  23.             FNameList.push(fname);
  24.         }
  25.     } finally {
  26.         close([resultSet, statement, connection]);
  27.     }
  28.     return FNameList;
  29. }
  30. function doGetFileName() {
  31.     try {
  32.         $.response.contentType = “application/json”;
  33.         $.response.setBody(JSON.stringify(getFileName()));
  34.     } catch (err) {
  35.         $.response.contentType = “text/plain”;
  36.         $.response.setBody(“Error while executing query: [“ + err.message + “]”);
  37.         $.response.returnCode = 200;
  38.     }
  39. }
  40. doGetFileName();


Embed an Image in HTML Code

In order to view the image, we will call the function GetImage.xsjs and embed the picture into the HTML code with base64 encoding using the following tag:

  1. <img src=“data:{mime};base64,{data}” alt=“My picture”/>

where {Mime} is in ‘image/imagetype’ format (eg. ‘image/jpg’ or image/png) and {data} is the base64 encoded

 

Here is an example when I viewed the source code of the “WhatsApp” image in the Chrome browser:

 

b6.jpg

b7.jpg

 

The actSearch function in the view controller will call the GetImage.xsjs based on the given image ID (filename) and encode the content in the base64 format.

 

  1. actSearch: function(fname) {
  2.         var xmlHTTP = new XMLHttpRequest();
  3.         xmlHTTP.open(‘GET’‘../fd2/image/GetImage.xsjs?id=’ + fname, true);
  4.         xmlHTTP.responseType = ‘arraybuffer’;
  5.         xmlHTTP.onload = function(e) {
  6.             var arr = new Uint8Array(this.response);
  7.             var raw = String.fromCharCode.apply(null, arr);
  8.             var b64 = btoa(raw);
  9.             var dataURL = “data:image/jpeg;base64,” + b64;
  10.             document.getElementById(“image”).src = dataURL;
  11.         };
  12.         xmlHTTP.send();
  13.     }

Running the App

Firstly you need to open the tunnel to the SAP HANA Cloud Platform. Under folder neo-javaee6-wp-sdk, execute the following command:

neo open-db-tunnel -h “hanatrial.ondemand.com” -u “p1940803061” -a “p1940803061trial” –id “fd2”

 

Replace “p1940803061” with your HANA trial account ID and “fd2” with your HANA instance.

 

b8.jpg

If successfull, you will get the the password: Df2sxp5HH8H6BGv and user: DEV_5GKYRR9GKKT9NTV5RD7U0DGMG. Update the pwd and user value in the config.properties:

user=DEV_5GKYRR9GKKT9NTV5RD7U0DGMG

pwd=Df2sxp5HH8H6BGv

Run the Yowsup. Execute below command:

python C:\Python34\yowsup-master\src\yowsup-cli –interactive <phone_number> –wait –autoack –keepalive –config C:\Python34\yowsup-master\src\yowsup-cli.config

b9.jpg

And finally run the Java Folder Monitoring app:

java FolderMonitor

 

Demo Video

 

 

Summary

 

In other case, I have also built a CCTV home security and upload the video clip to the DropBox (or HANA). Here is the video:

 

Hope your are enjoy “Whana” and thank you for reading my blog. Please let me know if you have any question/comment.

Build an Interactive Voice Response (IVR) System connected to SAP HANA – Part 2

Standard

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 + “‘”;
    view.jpg

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.

a0.jpg

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> :

a2.jpg

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.

a3.jpg

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:

a1.jpg

  • Wan Setup
    wan.jpg
  • Lan Setup
    lan.jpg
  • Voice > System
    voice.jpg
  • Voice > SIP
    sip1.jpgsip2.jpg
  • Regional (for Singapore)
    regional1.jpgregional3.jpg
    regional4.jpgIn 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.
    line1a.jpg
    Set the proxy to IP address of Raspberry PI (PBX server), user ID: line1 with password: papamama
    line1b.jpg
  • PSTN Line
    Set the SIP port of PSTN Line to 5061.
    pstnline1.jpg
      Set the proxy to IP address of Raspberry PI (PBX server), user ID: pstn with password: papamama
    pstnline2.jpg
      “S0<:123@192.168.0.12>” means that incoming PSTN calls will call the extension 123 on the PBX.   
    pstnline3.jpg
    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.
    pstnline4.jpg
    pstnline5.jpg
    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:

siptrunk1.jpg

We will add trunk line1 and pstn:

trunk2.jpg

trunk3.jpg

[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.
submit.jpgapply.jpg

The configuration will be saved in the file /etc/asterisk/sip_additional.conf. Just open the file and see if the config is there.

puttysip.jpg

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:

ivrflow.jpg

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:

  1. var http = require(‘http’),
  2.     xml2js = require(‘xml2js’);
  3. var username = ‘username’,
  4.     password = ‘password’;
  5. var sapgw = {
  6.   host: ‘sapnetweavergatewayserver.com’,
  7.   port: 8000// Change the port number accordingly
  8.   path: “/sap/opu/odata/sap/ZGW_MATERIAL_SRV/Materials(‘0009620-081’)”,
  9.   headers: {
  10.     ‘Authorization’‘Basic ‘ + new Buffer(username + ‘:’ + password).toString(‘base64’)
  11.   }
  12. }
  13. request = http.get(sapgw, function(res){
  14.   var body = “”;
  15.   res.on(‘data’function(data) {
  16.   body += data;
  17.   });
  18.   res.on(‘end’function(result) {
  19.   //console.log(body);
  20.   var tag = ‘d:MatlDesc’// Print the material description
  21.   var value = getValue(tag,body);
  22.   console.log(value);
  23.   });
  24.   res.on(‘error’function(e) {
  25.       console.log(“Got error: “ + e.message);
  26.   });
  27. });
  28. function getValue(tag,xmlString){
  29.     var value;
  30.     var tempString;
  31.     var startTag,endTag;
  32.     var startPos,endPos;
  33.     startTag = “<“+tag+“>”;
  34.     endTag = “</”+tag+“>”;
  35.     tempString=xmlString;
  36.     startPos = tempString.search(startTag) + startTag.length;
  37.     endPos = tempString.search(endTag);
  38.     value = tempString.slice(startPos,endPos);
  39.     return value;
  40. };

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.

testivr.jpg

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:

debug.jpg

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.

photo.PNG

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

Build an Interactive Voice Response (IVR) System connected to SAP HANA – Part 1

Standard

On the first part of this blog post, I would like to walk through how to build an IVR (Interactive Voice Response) system that is connected to SAP HANA Database or ODATA service. IVR is an automated telephony system that interacts with the callers, gathers information and routes calls to the appropriate recipient.

For the demo, we will make a call to the hotline (IVR) number to inquiry on the amount of the net value. The system will ask you to type the 8 digit billing document number. Once you have entered the number, if the record is exist, the system will tell you the net value.

2.jpg

Required Components

Software:

Hardware:

Preparing the PBX Server

We will be using a Raspberry Pi as an IVR server.

  1. Download the latest image of Asterisk from http://www.raspberry-asterisk.org/downloads/ and write it to the SD card.
    I am using the below version:
    1.jpg
    Once you have written the SD card, boot it up and run this command on the console to update with the latest components:

    raspbx-upgrade

  2. Configure the static IP-address.
    Edit the file /etc/network/interface by typing the following command:

    nano /etc/network/interfaces

    Remove the following line:

    iface eth0 inet dhcp

    And add the following lines:

    iface eth0 inet staticaddress 192.168.0.12
    netmask 255.255.255.0
    gateway 192.168.0.1
    dns-nameservers 192.168.0.1

    Update those numbers highlighted in blue with your IP address. And finally run the following command to restart the network service:

    service networking restart

  3. Installing IncrediblePBXExecute the following  command on the Raspberry Pi console:

    cd /
    wget http://incrediblepbx.com/incrediblepbx11-raspbx.gz
    gunzip incrediblepbx11-raspbx.gz
    chmod +x incrediblepbx11-raspbx./incrediblepbx11-raspbx

    It takes about 15~30 minutes to complete. It will ask you to reset all the root password, the FreePBX  and ARI admin password, the extension 701 password, the extension 701 voicemail password, the email delivery address for extension 701 and the telephone reminders password for scheduling reminders by phone. Please proceed to reset all the password.

Installing Node.JS

Just to ensure the Raspberry Pi is up to date and ready to go, please execute the following commands:

sudo apt-get upgrade

sudo apt-get update

Download the latest version of Node.JS:

sudo wget http://node-arm.herokuapp.com/node_latest_armhf.deb

Install it:

sudo dpkg -i node_latest_armhf.deb

Check if the installation is successful by typing the following command:

node -v

You will see the version if the installation is successful, for my case is:

v0.10.29

Installing SAP HANA Database Client for Node

npm.png

The next step that we are going to perform is to install the JavaScript client for node to connect to the SAP HANA database and execute the SQL query.

In the Raspberry Pi console, type the following command:

git clone https://github.com/SAP/node-hdb.git

cd node-hdb

npm install

Setup the Voice Modem Gateway

Setting-up the voice modem gateway Linksys SPA3102 is pretty much simple.

The main important point here is the connection to the IVR server (Raspberry Pi). I will touch in more detail in the Part 2.

The Line port connected to the RJ-11 in the DSL/Cable Modem router. The Phone port connected to your analog phone and the Internet port connected to the DSL/Cable Modem router.

Slide2.JPG

That’s all for Part 1.  We will continue on the Part 2 to perform the following:

  • Configure Linksys SPA3102 voice modem gateway in order to connect to the IVR server.
  • Create and setup the HANA database. We will import the data from the CSV to the HANA table.
  • 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.

Stay tune for part 2.