Building a SAP CAP Application in the SAP Business Application Studio using the example of the Bundesliga table

Introduction

First of all, please note that this project is part of one of the exam of a module at the FH Aachen – University of Applied Science held by Christian Drumm. So, I am definitely no expert and there might be better ways to implement some parts of this project 😉

As a requirement the following modules needed to be a part of the application:

• A SAP CAP backend
• A SAP Fioiri UI
• Optionally additional SAP CP Services

Since i am a fan of the Bundesliga I decided to use the Bundesliga as my topic of the application. The application will give you the opportunity to enter match results of the Bundesliga. These results will affect the statistics of each team so you can see the full Bundesliga table.

Therefore, the Bundesliga application can be divided in two parts. One will display the table of the Bundesliga and the other one makes it possible to enter the results of matches. For this the entries of the database with all teams playing in the are changed accordingly.

The implemented process looks like this: After a result is entered, it is created in the database table with all results. In the table fields like points, scored goals etc. of the teams which played the match will be changed. These changes then will be visible in the overall table.

A requirement of the project is that as a development environment the SAP Business Application Studio is used. If you need further help setting up the development environment you can find help here: https://github.com/ceedee666/erp_scp_end_2_end/blob/master/docs/rqk_overview.md

Both parts of the application are Fiori Applications. To store and work with the data a database and services are needed.

The reminder of this blog post will have the following chapters:

• Initialisation of the project
• Development of the database model
• Development of the UI for the Bundesliga table
• Development of the UI to submit match results
• Changing the teams table with each result submitted
• Conclusion

 

Initialisation of the project

Here the project – which I called “Bundesliga” – is initialized. The type of the created dev space is SAP Cloud Business Application and the additional extension Workflow Management is activated. To initialize the new project the command cds i bundesliga needs to be executed. After the project is initialized with the basic file structure the actual development can begin.

 

Development of the database model

The first step is the development of the database model using Core Data Services. A new file needs to be created in the db folder. The name of the file is schema.cds. The code inside this file looks like this:

using { managed, cuid } from '@sap/cds/common';

namespace de.fhaachen.bundesliga;

entity Teams : managed {
  key ID            : Integer;
  points            : Integer;
  name              : String(500);
  goals_scored      : Integer;
  goals_against     : Integer;
  goal_difference   : Integer;
}

entity Matches : cuid, managed {
  hometeam : Association to Teams;
  awayteam : Association to Teams;
  goals_hometeam : Integer;
  goals_awayteam : Integer;
}

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/db/schema.cds)

The code defines two entities. By using the managed aspect, both tables will have four additional columns which display when and by whom a table record was created or modified. The entity Teams contains typical attributes of the Bundesliga table and the primary key ID.

A match consists of two teams which score goals against each other. Since we will not need a simple match ID later on, I added the cuid aspect which generates a primary key ID. To compile the schema.cds file into SQL, you need to execute the following command: cds c db/schema -2 sql.

After that you can see the generated SQL commands in the console.

Now the tables of the sqlite database will be filled with some data for testing purposes. To do this, a new folder called data is created in db folder. Inside this folder two csv files are added:

de.fhaachen.bundesliga-Teams.csv

ID,points,name,goals_scored,goals_against,goal_difference
1,20,Bayern Muenchen,0,0,0
2,18,RB Leipzig,0,0,0
3,0,Bayer 04 Leverkusen,0,0,0
4,0,VfL Wolfsburg,0,0,0
5,24,Borussia Dortmund,0,0,0
6,12,1.FC Union Berlin,0,0,0
7,6,VfB Stuttgart,0,0,0
8,17,Bor.Moenchengladbach,0,0,0
9,2,FC Augsburg,0,0,0
10,8,Eintracht Frankfurt,0,0,0
11,10,TSG Hoffenheim,0,0,0
12,8,Hertha BSC,0,0,0
13,6,Werder Bremen,0,0,0
14,2,SC Freiburg,0,0,0
15,0,1.FC Koeln,0,0,0
16,2,Arminia Bielefeld,0,0,0
17,1,1.FSV Mainz 05,0,0,0
18,0,FC Schalke 04,0,0,0

(The points for each team are just for testing the sorting later)

and de.fhaachen.bundesliga-matches.csv

hometeam_ID,awayteam_ID,goals_hometeam,goals_awayteam
1,2,2,1      

(Just one, so we can check if accessing the data works before we enter results via the UI later)

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/db/data/de.fhaachen.bundesliga-matches.csv)

To deploy the data model into the in memory database the command cds watch needs to be executed. Cds watch starts the application in the development environment together with in memory sqlite database which is filled with the data from the csv files.

If you open the started webserver you will notice that you can’t do much because there are no services in the project yet. These services are needed to access the data using the OData protocol.

So to create the first service you need to create a new file called manage-service.cds (you can give it a more fitting name like TeamsService) inside the srv folder with this code:

using { de.fhaachen.bundesliga as bundesliga } from '../db/schema.cds';
service ManageService {
  entity Teams as projection on bundesliga.Teams;
}

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/srv/manage-service.cds)

One disadvantage of using an in memory database is the loss of changes to the data when the application is restarted. To prevent that from happening a SQLite is added by executing this command: cds deploy –to sqlite This deploys the cds to a sqlite database which can be used for testing.

After that you can add a new connection in the SQL tools so the data inside the database can be visible:

You need to choose SQLite as the connection type and choose a name for the connection (here: dbBundesliga). Then you can connect to the database and see the tables from the cds file.

 

Development of the UI for the Bundesliga Table

The type of the UI will be a Fiori Elements List Report and for the creation a build-in generated is used.

To create the UI which displays the Bundesliga Table you can press the F1-button on your keyboard and use the build in Yeoman UI generator. In the following dialogue select SAP Fiori elements application. After that you need to choose what kind of Fiori application you want to create. Since we want to display the table of the Bundesliga the List Report Object Page is best suited. In the next step select the data source (-> Use a local CAP Node.js Project) so the created service can be used. The path to the project folder in this case is /home/user/projects/bundesliga. Finally, the created ManageService and as the main entity Teams is selected.

In the last step you choose a Module name (which I forgot so it is still project1) and an application title and finish.

This will create a new folder inside the app folder with the module name chosen before (=project1).

This will already give us an UI with not really anything in it. So to customize it to our needs the annotation.cds needs some work:

using ManageService as service from '../../srv/manage-service';

annotate service.Teams with @(
    UI : {        
        LineItem: [
            {Value: name, Label: 'Team'},
            {Value: points, Label: 'Points'},
            {Value: goal_difference, Label: 'Goal Difference'},
            {Value: goals_scored, Label: 'Goals Scored'},
            {Value: goals_against, Label: 'Goals against'}
        ],
        PresentationVariant : {
            SortOrder : [
                {
                    $Type : 'Common.SortOrderType',
                    Property : points,
                    Descending : true,
                },
            ],
            Visualizations : [
                '@UI.LineItem',
            ],
        },
    }
);

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/app/project1/annotations.cds)

With the LineItem annotations the Bundesliga Table will have the columns name (of the team), points… The PresentationVariant is used to customize the sort order of the whole table (which obviously is points in soccer).

So you can now start the application by executing cds watch and go to the localhost address (there should appear a popup screen in the bottom right of your screen). Then click on the project1-path and then on the Bundesliga Table tile.

You need to press the Start button to show the table. As you can see every team from the Teams database table is shown and the teams with the most points is on the top. If you like you can change the sorting by clicking on the columns and for example check which team scored the most goals.

 

Development of the UI to submit match results

To create the UI of the match results you can use the same approach used to create the BundesligaTable UI. Before you start the Yeoman UI Generator you need a new service. So just create another file in the srv folder with the name result-service.cds. Since the results will affect the table both entities are needed here.

using { de.fhaachen.bundesliga as bundesliga } from '../db/schema.cds';
service ResultService{
    entity Teams as projection on bundesliga.Teams;
    entity Matches as projection on bundesliga.Matches
        actions{ action submitResult();}
}

When the service is created it needs to be chosen as OData service in the UI Generator:

The main entity is Matches; the module is called matches.

The matches will be displayed as a table so the columns are defined using LineItem annotations again (annotation.cds inside the newly created matches folder).

using ResultService as service from '../../srv/result-service';
annotate service.Matches with @odata.draft.enabled;
annotate service.Matches with @(
    UI: {
        LineItem: [
            {Value: hometeam.name, Label: 'HomeTeam'},
            {Value: goals_hometeam, Label: 'Goals HomeTeam'},
            {Value: awayteam.name, Label: 'AwayTeam'},
            {Value: goals_awayteam, Label: 'Goals HomeTeam'},
        ],

The second line needs to be added so the results can be submitted and stored in the database.

If you look at the table now it should look like this after you click on the start button:

In this table all matches will be shown. If you want to add a match result you need to click the create button (in the screenshot it says “Anlegen” because it is in German) which will take you to the part of the application where you can enter the data of the match. Before you can do that, some annotations need to be added to the annotation.cds.

HeaderInfo : {
            TypeName       : 'Match', 
            TypeNamePlural : 'Matches', 
            TypeImageUrl   : 'sap-icon://alert',
            Title          : {Value : ID}
        },
        Identification: [
            {Value: hometeam.name, Label: 'HomeTeam'}
        ],
        Facets:[
            { $Type: 'UI.ReferenceFacet', Target: '@UI.FieldGroup#Matches'},
            { $Type: 'UI.ReferenceFacet', Target: '@UI.FieldGroup#Matches'}
        ],
        FieldGroup#Matches:{
            Data: [
                {Value: hometeam_ID, Label: 'HomeTeam'},
                {Value: goals_hometeam, Label: 'Goals HomeTeam'},
                {Value: awayteam_ID, Label: 'AwayTeam'},
                {Value: goals_awayteam, Label: 'Goals AwayTeam'}
                
            ]
        }
    }
);

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/app/matches/annotations.cds)

The Data annotation inside FieldGroup#Matches are needed for the input fields. So the IDs of the hometeam and awayteam as well as the goals each team scored need to be entered. After pressing the create button you will see the following pop-up window:

This input field is for entering the ID of the match. Because the CUID aspect is used in the definition of the data model a UUID is generated automatically so you don’t need to enter an ID and can just click the create button (“Anlegen”). This will lead you to the following page where you can enter the result:

Now the result is saved in the matches table of the database.

 

Changing the Teams table with each result submitted

With every result submitted the teams which played the match need to be changed in the Teams table because the points and goals need to be updated. To do this a new file called result-service.js is created in the srv folder.

The function inside this file is executed every time a new match is created because in line 3 the event ‘…on(‘CREATE’…’ is used . The entity Teams and a transaction in which the request data is available are needed. The data which was submitted in the match can be accessed by using the request variable: req.data.x

The first things which are updated are the goals and goal difference of each team. After that the points are taken care of. For that it needs to be determined which team won the game. Obviously that’s the team which scored more goals, so that team gains 3 points and the other none. In case the match is a draw both teams have one point added to their total points.

const cds = require('@sap/cds')
module.exports = function () {
    this.on('CREATE', 'Matches', async (req, next) => {
        const { Teams } = cds.entities
        var tx = cds.transaction(req);
        if (req.data.hometeam_ID != null) {
            const n = await tx.run(
                UPDATE(Teams)
                    .set({ // Update Goals Hometeam
                        goals_scored: { '+=': req.data.goals_hometeam },
                        goals_against: { '+=': req.data.goals_awayteam },
                        goal_difference: {'+=': req.data.goals_hometeam - req.data.goals_awayteam}
                    }).where({ ID: req.data.hometeam_ID })
            )
            const o = await tx.run(
                UPDATE(Teams)
                    .set({ // Update Goals Awayteam
                        goals_scored: { '+=': req.data.goals_awayteam},
                        goals_against: {'+=': req.data.goals_hometeam},
                        goal_difference: {'+=': req.data.goals_awayteam - req.data.goals_hometeam}
                    }).where({ID: req.data.awayteam_ID})
            )
            if (req.data.goals_hometeam > req.data.goals_awayteam){ // Win Hometeam
                const p = await tx.run(
                    UPDATE(Teams)
                    .set({ 
                        points:{'+=':3}
                    }).where({ID: req.data.hometeam_ID})
                )
            }
            else if(req.data.goals_hometeam < req.data.goals_awayteam){ // Win Awayteam
                const p = await tx.run(
                    UPDATE(Teams)
                    .set({ 
                        points:{'+=':3}
                    }).where({ID: req.data.awayteam_ID})
                )
            }
            else if(req.data.goals_hometeam == req.data.goals_awayteam){ // Draw
                const p = await tx.run(
                    UPDATE(Teams)
                    .set({
                        points:{'+=':1}
                    }).where({ID: req.data.hometeam_ID})
                )
                const q = await tx.run(
                    UPDATE(Teams)
                    .set({
                        points:{'+=':1}
                    }).where({ID: req.data.hometeam_ID})
                )
            }
        }
        return next()
    })
}

(https://github.com/marcH318/SAP_Bundesliga/blob/main/bundesliga/srv/result-service.js)

It might be possible to implement all this with fewer transactions. Unfortunately, my attempts to do that failed.

Now all functionalities are implemented so the Bundesliga application can be used. To reset the table and get rid of the sample data just change the points of each team in the .csv file to 0. Also, every result can be deleted. After that just deploy everything again as mentioned before. For example, I entered the results of the last matchday (https://www.kicker.de/bundesliga/spieltag/2020-21/15) so you can see the table filled with actual data.

 

 

Conclusion

There are more than a few things which can optimized. It would be more user friendly if you wouldn’t have to enter the ID of a team but instead could choose the teams from a dropdown menu.

In the Bundesliga table you could add the placement of each (1-18). The popup window which appears when you want to enter a new match result should also be removed because the user doesn’t have to enter an ID since it is generated automatically.

The input isn’t checked so you could enter the same ID for the home- and awayteam which makes absolutely no sense.

There is also no authentication implemented (which wasn’t a requirement of this project, only optional).

Since this is part of my exam, unfortunately I didn’t have unlimited time to implement more of these ideas. So feel free to rebuild the application with some improvements.

 

 

Link to git repository:

https://github.com/marcH318/SAP_Bundesliga

 

Links with additional information:

https://cap.cloud.sap/docs/node.js/cds-ql#UPDATE

https://cap.cloud.sap/docs/node.js/services#srv-impls