
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.
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.
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:
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;
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
Get all data.
Example:
http://127.0.0.1:3000//data?TOP=5
Check user/RFID registration.
Example:
http://127.0.0.1:3000/rfid/123456789101
Getting last attendance.
Example:
http://127.0.0.1:3000/lastrfid/123456789101
POST
A new attendance registration for check in.
Example:
http://127.0.0.1:3000/rfid/123456789101
PUT
Check out registration.
Example:
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
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.
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:
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
23 | |
19 | |
9 | |
8 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |