IoT: RFID integration with SAP HANA Cloud via SAP BTP

[Ihor_Haranichev]

Hello SAP Community!

Let me share with you an abstract prototype with using RFID reader with power of SAP HANA Cloud platform. Probably this project will bring you a new ideas for using modern SAP landscape.

For instance we have a business case: to register employee’s attendance in the office for further processing such as control reporting, payroll etc.

1. Hardware specification

For this purposes we use a popular computer Raspberry PI with open platform and data bus. It is British computer for learning and various DIY projects. For example, you can create your own smart house using such computers.

Details are by the link https://www.raspberrypi.org

I have Raspberry Pi 3 Model B+. A little bit outdated, but still functional. The computer bus GPIO allows connect a various sensors and devices.

For RFID reader we use SB components RFID HAT:

This device is Raspberry PI compatible; it has GPIO 40 pin header such as Raspberry PI has. Apart of RFID module, there are beeper and little monochrome display on-board.

More details in the link https://github.com/sbcshop/SB-RFID-HAT

Additionally, we have two test RFID tags for test scenarios. These tags have own unique ID.

2. Software specification

We use SAP BTP Trial as development platform and SAP HANA Cloud as database. We use Python for REST API. And for RFID device’s scripts, we will use Python as well. SAP Business Application Studio will be used to develop Fiori application.

 Let's split the task for several parts:

• Creating database artifacts;
• Creating a Rest API in SAP BTP;
• Creating scripts for RFID device;
• Creating a Fiori report.

3. Creating database artifacts

We create an SAP BTP account + space + SAP HANA Cloud database.

I provide a link for creating an account by SAP Learning Hub (developers.sap.com):

https://developers.sap.com/tutorials/hcp-create-trial-account.html

For creating SAP HANA database use next link:

https://developers.sap.com/group.hana-cloud-get-started-1-trial.html

Once development environment is ready, we create database artifacts: a table for users and a attendance log table and view which join two tables.

Here are SQL scripts:

Table RFID_USER, for user maintenance.

CREATE COLUMN TABLE RFID_USER (
    RFID NVARCHAR(12) PRIMARY KEY,
    NAME NVARCHAR(50)
);

Table RFID_USER description

Fieldname	Field type	Description
RFID	NVARCHAR(12)	RFID unique ID
NAME	NVARCHAR(50)	User which assigned to ID

Table RFID_LOG, for attendance registration.

CREATE COLUMN TABLE RFID_LOG (
    ID INT PRIMARY KEY,
    RFID NVARCHAR(12),
    CHECKIN DATETIME,
    CHECKOUT DATETIME
);

Table RFID_LOG description

Fieldname	Field type	Description
ID	INT	Unique key field, counter
RFID	NVARCHAR(50)	User which assigned to ID
CHECKIN	DATETIME	Timestamp for check in
CHECKOUT	DATETIME	Timestamp for check out

View RFID_VIEW, for reporting 

CREATE VIEW RFID_VIEW AS
SELECT RU.NAME, RL.RFID, RL.CHECKIN, RL.CHECKOUT
FROM RFID_LOG RL
JOIN RFID_USER RU ON RL.RFID = RU.RFID;

4. Creating a REST API in SAP HANA Cloud

For REST API development, we use Visual Studio Code. We use a Python as a language for development.

Link for Visual studio code: https://code.visualstudio.com

Link for Python: https://www.python.org

The last but not least is Cloud Foundry CLI: https://docs.cloudfoundry.org/cf-cli/install-go-cli.html

Using Cloud Foundry command line we will deploy our application to SAP BTP.

I recommend to use a comprehensive tutorial, provided by SAP: https://developers.sap.com/tutorials/btp-cf-buildpacks-python-create.html

First thing first we create a folder for the project -  Python_Rfid_Project.

Inside this folder put a file with a name manifest.yml. This file describes the application and how it will be deployed to Cloud Foundry:

---
applications:
- name: rfid_app
  random-route: true
  path: ./
  memory: 128M
  buildpacks: 
  - python_buildpack
  command: python server.py
  services:
  - pyhana_rfid
  - pyuaa_rfid

- name: rfid_web
  random-route: true
  path: web
  memory: 128M
  env:
    destinations: >
      [
        {
          "name":"rfid_app",
          "url":"https://rfidapp-chipper-echidna.cfapps.us10-001.hana.ondemand.com",
          "forwardAuthToken": true
        }
      ]
  services:
  - pyuaa_rfid

Name of application is rfid_app. Command file with the API logic is server.py.

Next, lets create a Python runtime version file, runtime.txt:

python-3.11.*

Another file is requirements.txt, which contains the necessary versions of packages:

Flask==2.3.*
cfenv==0.5.3
hdbcli==2.17.*
flask-cors==3.0.10

Flask is a framework for building easy and lightweight web applications

Cfenv is node.js library for simplify process of accessing environment variables and services provided by cloud platform.

Hdbcli – is python library for connecting and interacting with SAP HANA Databases

Flask-CORS is a Flask extension that simplifies the process of dealing with Cross-Origin Resource Sharing (CORS) in Flask applications. In this project we will simplify this connection to avoid CORS errors. The SAP recommendation is to register and consume Destinations. You may see it in SAP BTP:

However in scope of my project I will simplify this process to use Flask-CORS extension. Here I'm opened for discussion, possibly someone will propose an another approach.

It is important to install all this packages on local machine:

Commands are:

pip install Flask

pip install cfenv

pip install hdbcli

pip install flask-cors

Next, main file, as I mentioned before is server.py

import os

from flask import Flask, request, jsonify
from flask_cors import CORS
from hdbcli import dbapi
from cfenv import AppEnv
import json

app = Flask(__name__)
CORS(app)  # Enable CORS for all routes
#CORS(app, resources={r"/*": {"origins": "*"}})

env = AppEnv()

import json
sap_hana_config_file = "hana_cloud_config.json"
with open(sap_hana_config_file) as f:
    sap_hana_config = json.load(f)
    db_url  = sap_hana_config['url']
    db_port = sap_hana_config['port']
    db_user = sap_hana_config['user']
    db_pwd  = sap_hana_config['pwd']
    db_database = sap_hana_config['database']

# Get the service bindings

hana_service = 'hana'
hana = env.get_service(label=hana_service)

port = int(os.environ.get('PORT', 3000))

# SAP HANA database connection configuration
conn = dbapi.connect(address=db_url,
            port=db_port,
            user=db_user,
            password=db_pwd,
            database=db_database)        

# routine for database execution
def execute_query(query, params=None):
    cursor = conn.cursor()
    if params:
        cursor.execute(query, params)
    else:
        cursor.execute(query)
    try:
        data = cursor.fetchall()
    except:
        data = []
    cursor.close()
    data_list = []
    for row in data:
        data_dict = {}
        for idx, col in enumerate(cursor.description):
            data_dict[col[0]] = row[idx]
        data_list.append(data_dict)
    return data_list

# endpoints

@app.route('/data', methods=['GET'])
def get_data():
    top_count = int(request.args.get('TOP')) if request.args.get('TOP') else 0
    if top_count > 0:
        query = "SELECT * FROM RFID_VIEW ORDER BY CHECKIN DESC LIMIT ?"    
        params = (top_count)
    else:
        query = "SELECT * FROM RFID_VIEW ORDER BY CHECKIN DESC"
        params = None

    data = execute_query(query, params)
    return jsonify(data)

@app.route('/user/<rfid>', methods=['GET'])
def get_user_by_rfid(rfid):
    query = "SELECT * FROM RFID_USER WHERE RFID = ?"
    data = execute_query(query, (rfid,))
    return jsonify(data)

@app.route('/rfid/<rfid>', methods=['GET'])
def get_data_by_rfid(rfid):
    query = "SELECT RL.ID, RL.RFID, RL.CHECKIN, RL.CHECKOUT,RU.NAME FROM RFID_LOG RL JOIN RFID_USER RU ON RL.RFID = RU.RFID WHERE RL.RFID = ?"
    data = execute_query(query, (rfid,))
    return jsonify(data)

@app.route('/lastrfid/<rfid>', methods=['GET'])
def get_last_data_by_rfid(rfid):
    query = "SELECT TOP 1 RL.ID, RL.RFID, RL.CHECKIN, RL.CHECKOUT,RU.NAME FROM RFID_LOG RL JOIN RFID_USER RU ON RL.RFID = RU.RFID WHERE RL.RFID = ? AND RL.CHECKIN IS NOT NULL AND RL.CHECKOUT IS NULL ORDER BY RL.CHECKIN DESC"
    data = execute_query(query, (rfid,))
    return jsonify(data)

@app.route('/rfid/<rfid>', methods=['POST'])
def add_data(rfid):
    # new_data = request.json
    query = "INSERT INTO RFID_LOG (RFID, CHECKIN, CHECKOUT) VALUES (?,CURRENT_TIMESTAMP, NULL)"
    # for new_data_line in new_data:
    #     params = (new_data_line['RFID'])
    execute_query(query, (rfid,))
    return jsonify({"message": "Data added successfully"})

@app.route('/id/<int:id>', methods=['PUT'])
def update_data(id):
    # updated_data = request.json
    query = "UPDATE RFID_LOG SET CHECKOUT = CURRENT_TIMESTAMP  WHERE ID = ?"
updated_data_line['READING1'], updated_data_line['READING2'], updated_data_line['READING3'], updated_data_line['UNIQUEDEVICEID'], id)
    execute_query(query,(id,))
    return jsonify({"message": "Data updated successfully"})

# for local testing
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=port)

In this script we implement GET, PUT, POST methods with respective endpoints.

GET

• Endpoint /data

Get all data.

Example:

http://127.0.0.1:3000//data?TOP=5

• Endpoint /user/<RFID>

Check user/RFID registration.

Example:

http://127.0.0.1:3000/rfid/123456789101

• Endpoint /lastrfid/<RFID>

Getting last attendance.

Example:

http://127.0.0.1:3000/lastrfid/123456789101

POST

• Endpoint /rfid/<RFID>

A new attendance registration for check in.

Example:

http://127.0.0.1:3000/rfid/123456789101

PUT

• Endpoint /id/<int:id>

Check out registration.

Example:

http://127.0.0.1:3000/id/1

For database connection I put the credentials into a json file, hana_cloud_config.json

{
    "user": "DBADMIN",
    "pwd": "*********",
    "url": "????????-????-????-????-???????????.hana.trial-us10.hanacloud.ondemand.com",
    "port": 443,
    "database": "HANA_Cloud_Trial"
  }

We take your database administrator login+password, which you initiated during SAP HANA Database initialization.

The URL we take here in SAP BTP:

This connection is performed by command:

conn = dbapi.connect(address=db_url,
            port=db_port,
            user=db_user,
            password=db_pwd,
            database=db_database)   

We open terminal window Visual Studio code and connect to Cloud Foundry.

Initially it requests API endpoint which you may take from SAP BTP Cockpit:

And provide your name and password:

To deploy the application use command cf push.

After successful deployment and start of your application you may see in the Terminal:

In SAP BTP Cockpit you may see the following:

Now we can trigger our REST API with command, like was provided above. For testing the API I used POSTMAN utility - https://www.postman.com

For instance, if entries exist in database, you receive next response for the request http://127.0.0.1:3000/data

5. Creating IoT - RFID reader

After RFID HAT installation the Raspberry PI will look like this:

In Raspberry PI terminal, in command line we install required libraries:

sudo apt-get install python-smbus

sudo apt-get install i2c-tools

A test script is provided for the RFID device out of the box. I modified and implemented communications with the developed REST API.

Rfid_with_oled_project.py

from oled_091 import SSD1306
from subprocess import check_output
from time import sleep
from datetime import datetime
from os import path
import serial
import RPi.GPIO as GPIO
import requests
import json

GPIO.setmode(GPIO.BCM)
GPIO.setwarnings(False)
GPIO.setup(17,GPIO.OUT)

DIR_PATH = path.abspath(path.dirname(__file__))
DefaultFont = path.join(DIR_PATH, "Fonts/GothamLight.ttf")
url = 'https://rfidapp-chipper-echidna.cfapps.us10-001.hana.ondemand.com'
Checkin = ""
Checkout = ""
Id = ""

class read_rfid:
    def read_rfid (self):
        ser = serial.Serial ("/dev/ttyS0")                           #Open named port 
        ser.baudrate = 9600                                            #Set baud rate to 9600
        data = ser.read(12)                                            #Read 12 characters from serial port to data
        if(data != " "):
            GPIO.output(17,GPIO.HIGH)
            sleep(.2)
            GPIO.output(17,GPIO.LOW)
        ser.close ()                                                   #Close port
        data=data.decode("utf-8")
        return data

def info_print():
    print("Waiting for TAG...")
    # display.WhiteDisplay()
    display.DirImage(path.join(DIR_PATH, "Images/SB.png"))
    display.DrawRect()
    display.ShowImage()
    sleep(1)
    display.PrintText("Place your TAG", FontSize=14)
    display.ShowImage()
    

display = SSD1306()
SB = read_rfid()

if __name__ == "__main__":
    info_print()
    while True:
        id=SB.read_rfid()
        print (id)
        #CPU = info.CPU_Info()
        # display.DirImage("Images/CPU.png", size=(24, 24), cords=(0, 0))
        #display.PrintText("ID : " +(id), cords=(4, 8), FontSize=11)
        endpoint_get = '/user/' + id
        try:
            r = requests.get(url + endpoint_get)
            r.raise_for_status()
            js = r.json()
            for js_line in js:
                Name = js_line['NAME']
                Rfid = js_line['RFID']
            if js == []:
                print ("No user found")
                display.DrawRect()
                display.PrintText("No user found", cords=(4, 8), FontSize=14)
                display.ShowImage()
                sleep(2)
            else:
                #print(Name)
                #display.DrawRect()
                #display.ShowImage()
                #display.PrintText("Hello," +(Name), cords=(4, 8), FontSize=14)
                #display.ShowImage()
                #sleep(2)
                #display.ShowImage()
                
                endpoint_get = '/lastrfid/' + Rfid
                try:
                    r = requests.get(url + endpoint_get)
                    r.raise_for_status()
                    js = r.json()
                    for js_line in js:
                        Checkin = js_line['CHECKIN']
                        Checkout = js_line['CHECKOUT']
                        Id = js_line['ID']
                    if js == []:
                        endpoint_post = '/rfid/' + Rfid
                        response_post = requests.post(url + endpoint_post)
                        print("Check In->",Name)
                        display.DrawRect()
                        #display.ShowImage()
                        display.PrintText("Hello, " +(Name) +"!", cords=(4, 8), FontSize=12)
                        display.ShowImage()
                        sleep(2)
                        
                    elif Checkin != None and Checkout == None:
                        endpoint_put = '/id/' + str(Id)
                        response_put = requests.put(url + endpoint_put)
                        print("Check Out->",Name)
                        display.DrawRect()
                        #display.ShowImage()
                        display.PrintText("Bye, " +(Name) +"!", cords=(4, 8), FontSize=12)
                        display.ShowImage()
                        sleep(2)
                        
                    elif Checkin != None and Checkout != None:
                        endpoint_post = '/rfid/' + Rfid
                        response_post = requests.post(url + endpoint_post)
                        print("Check In->",Name)
                        display.DrawRect()
                        #display.ShowImage()
                        display.PrintText("Hello, " +(Name) +"!", cords=(4, 8), FontSize=12)
                        display.ShowImage()
                        sleep(2)
                        
                except requests.exceptions.HTTPError as err:
                    print("Error - 404")
                
        except requests.exceptions.HTTPError as err:
            print("Error - 404")
        #sleep(2)    
        display.DrawRect()
        display.ShowImage()
        #sleep(2)
        display.PrintText("Place your TAG", FontSize=14)
        display.ShowImage()

The logic is next: initially we are checking if RFID ID exists. If exists, fetching last registered data for the ID. If exists, checking if check in date and time is not initial, if exists – setting check out date and time. If no records in the database – we insert a check in date and time for the particular ID.

Our device and script are ready.

6. Creating a SAP Fiori application

Last stage – we will create a simple Fiori report for data reflection.

Here we create a Dev Space FIORI_RFID in SAP Business application studio and specify it for SAP Fiori:

Once Dev Space will be created,  we create a Fiori project from template:

A project will be generated with all necessary files and folders.

In our Fiori application we create one screen for a list report.

All necessary files are generated. We need put changes to view file and controller file.

View.controller.js

sap.ui.define([
    "sap/ui/core/mvc/Controller"
],
    /**
     *  {typeof sap.ui.core.mvc.Controller} Controller
     */
    function (Controller) {
        "use strict";

        return Controller.extend("rfidproject.controller.View", {
            onInit: function () {

                sap.ui.getCore().HANA = new Object();
                sap.ui.getCore().HANA.URL = "https://??????-?????? -???????.???????.????-??.hana.ondemand.com/data";

                this.router = sap.ui.core.UIComponent.getRouterFor(this);
                this.router.attachRoutePatternMatched(this._handleRouteMatched, this);
                this.url = sap.ui.getCore().HANA.URL;
                var oModelData =  this.loadModel(this.url);
                this.getView().setModel(oModelData, "viewModel");

                
                // Set up automatic refresh every 5 minutes (300,000 milliseconds)
                setInterval(this.refreshData.bind(this), 1000);                         
                },
        
                _handleRouteMatched: function(evt) {
            //      this.empIndex = evt.getParameter("arguments").data;
            //
            //      var context = sap.ui.getCore().byId("App").getModel().getContext('/entityname/' + this.empIndex);
            //
            //      this.getView().setBindingContext(context);
                },
                backToHome: function(){
                    this.router.navTo("default");        
            },

            handleLiveChange: function(evt) {

                // create model filter
                var filters = [];
                var sQuery = evt.getParameters().newValue;
                if (sQuery && sQuery.length > 0) {
                    var filter = new sap.ui.model.Filter("NAME", sap.ui.model.FilterOperator.Contains, sQuery);
                    filters.push(filter);
                }
                // update list binding
                var list = this.getView().byId("Table");
                var binding = list.getBinding("items");
                binding.filter(filters);
            },
        
                // Event handler for live change in search field
        
            loadModel: function(url) {
                var url = url;
                var oModel = new sap.ui.model.json.JSONModel();
                oModel.loadData(url, null, false);
                return oModel;
            },
            refreshData: function() {
                var oModelData = this.loadModel(this.url);
                this.getView().setModel(oModelData, "viewModel");
            }
                                         

        });
    });

For OnInit event we maintain connection to REST API and viewModel.

We consume viewModel in View.view.xml

<mvc:View controllerName="rfidproject.controller.View"
    xmlns:mvc="sap.ui.core.mvc" displayBlock="true"
    xmlns="sap.m">
    <Page id="page" title="{i18n>title}">
        <content>

    <Table id="Table" growing = "true" busyIndicatorDelay="400" growingThreshold="20" mode="{device>/listMode}" inset="false" selectionChange="onItemSelection" updateFinished="onItemsUpdateFinished"
                updateStarted="onItemsUpdateStarted" width="auto" items="{viewModel>/}">
                <headerToolbar>
                    <Toolbar id="TB">
                        <Label id="LB" text="All entries"/>
                        <ToolbarSpacer id="TS"/>
                      <SearchField id="SF" search="handleSearch" liveChange="handleLiveChange" width="10rem" />
                      <!-- <CheckBox id="automaticRefreshCheckBox" text="Automatic Refresh" select="toggleRefreshMode"/>
                      <Button id="BTN" text="Refresh" press="refreshData" enabled="{= !viewModel>/autoRefresh}"/>                      -->
                      <Button id="BTN" text="Refresh" press="refreshData"/>
                    </Toolbar>
                </headerToolbar>
                <columns>
                    <Column demandPopin="true"  id="NAME" minScreenWidth="Small" visible="true">
                        <Text id="NM" text="NAME"/>
                    </Column>
                    <Column demandPopin="true" hAlign="Center"  id="CHECKIN" minScreenWidth="Medium" visible="true">
                        <Text id="CI" text="CHECK IN"/>
                    </Column>
                    <Column demandPopin="true"  id="CHECKOUT" minScreenWidth="Small" visible="true">
                        <Text id="CO" text="CHECK OUT"/>
                    </Column>
                </columns>
                <items>
                    <ColumnListItem id="CLI">
                        <cells>
                            <Text id="VNM" text="{viewModel>NAME}"/>
                            <Text id="VCI" text="{viewModel>CHECKIN}"/>
                            <Text id="VCO" text="{viewModel>CHECKOUT}"/>
                        </cells>
                    </ColumnListItem>
                </items>
            </Table>
        </content>
    </Page>
</mvc:View>

In the view we will use control Table. For the Table we maintain necessary fields: Name, Check in, Check out.

The result will look like this:

7. Testing

Now we can test our project!

Initially, let’s maintain users and RFID IDs:

On Raspberry PI – execute the script – rfid_with_oled_project.py

Now we can test our project!

 

On PC – execute the Fiori application:

Let’s place one tag on RFID reader:

RFID reader registers an user, who came to the office (for instance).

In the Fiori report we may see the entry:

Let’s place the tag again:

The user was unregistered, he has left the office.

Respective entry appeared in the Fiori application:

8. Conclusion

Our project is ready. RFID device successfully interacts with SAP HANA Database via API.

I'm looking forward to your feedback. Hope this blog will inspire you to create new projects and allow to discover new capabilities of SAP platform.

[Tomas_Buryanek]

Hi Igor, very cool project 👍 It shows nicely connection of multiple technologies. And makes programming more fun in regards of interaction with RFID reader, monochrome display... Thank you for explaining it nicely in detail with screenshots + photos!

[mchrista]

Nice and inspiring blog! 
Can I ask you about your decision to use python over CAP?

[Ihor_Haranichev]

Hi @Tomas_Buryanek. Thank you for your feedback!

[Ihor_Haranichev]

Hello @mchrista. Thank you!

Regards why I chose Python. Initially, I wanted to build REST API using Spring Boot. I spent plenty of time but did not have any success in deploying the program. Later that I've seen a tutorial https://developers.sap.com/tutorials/btp-cf-buildpacks-python-create.html  and book "Building RESTFful Python Web Services" by Gaston C.Hillar and decided to try create it using Python.