Cloud Platform Integration Soccer Calendar ⚽

[vvdries]

Hi there football fans!

We all know that Cloud Platform Integration flows come with a lot benefits to our business processes. This by integrating all kind of services with each other and performing desired actions.

But it doesn't always have to be a business process right? What if I told you that you can place all your favorite soccer matches into your Google Calendar by using a Cloud Platform Integration Flow?

 

Introduction ?

Since there are a lot of mobile apps available, this blog might seem a little unnecessary right?

Indeed, but you will always have to check the app for a match of your favorite team, which I forget quite often (what a fan) .... When it is in your calendar you will get your reminders and you will not have to miss a match ever again!

In the end I actually just want to show you how to work with HTTP(s) requests, ZIP-Files, CSV-Files, message mappings and mailing controls in Cloud Platform Integration Flows. That is the main reason of this blog.

 

So how does it work?

The zip file we will fetch by performing an HTPP(s) request will contain a CSV-File with a specific format.

This file contains all the soccer matches in Belgium for the National League.

This ZIP-File is offered by the Royal Belgian Football Association and can be found here:



When you downloaded the ZIP-File and open the CSV-File, you will see it is formatted like this:



It would have been nice if this was immediately in the right format to import it in the Google Calendar. But this is not the case.

The Google Calendar format, which can be found here:

Looks like this:



After we did the required transformations in the flow on the CSV-File, we will send our-self an email containing the final and correct formatted CSV-File that we can import in our Google Calendar.

 

 

Building the Integration Flow ?️

Before we start building our CPI-Flow, I want to give you a quick overview of the Flow. This to show how easily and fast we can build such a flow with Cloud Platform Integration.



 

As you can see it is not a huge flow at all. So let's get started building it.

We will separate our flow in 7 small pieces.



 

The 7 small pieces explained in a nutshell:

1. Provide an incoming HTTP-endpoint and handle X-CSRF-Tokens.


2. Read the incoming payload of the POST-Request.


3. Get the ZIP-File via an HTTP-Request and convert the CSV-File from the zip to JSON + handle encoding.


4. Filter the matches based on the provided team in the body of the POST in step 2.


5. Perform a message-mapping to retrieve the desired XML-Format and convert it to CSV.


6. Add the CSV-File as an attachment for your email.


7. Send the email to the user his email address provided in the body of the POST in step 2.

 

These are the small steps we will perform, to have a working Integration Flow that provides us our Soccer calendar.

 

1. Incoming HTTP-endpoint and X-CSRF-Token handling.

The first thing we want to do is to provide an endpoint where to user can perform a POST-Request on.

This by using the Sender control with an HTTPS-Connection to the Start Message.

When you select the HTTPS-Connection, provided the following values:



 

Next we will use a router to check and handle our X-CSRF-Tokens.

The following router makes sure that it is only possible to continue the flow when you perform a POST-Request with a valid X-CSRF-Token.

• When it is not a POST-Request you will be led away.


• When it is a POST-Request without a (valid) X-CSRF-Token, you will get a Forbidden error message.


• When it is a POST-Request with a valid X-CSRF-Token, you can continue the flow.

So when we have the router control, you can see number 1 is assigned as default route and 2 holds the logic to lead away the request.



Select the first route and check the default route checkbox.



Select the second route and select the Non-XML value as Expression Type.

The condition holds the logic  to lead of wrong requests:

${header.CamelHttpMethod} != 'POST' 

 

 

2. Read the incoming payload of the POST-Request.

When the user requests a soccer calendar for his favorite team, he needs to send a specific payload.

This payload of the Post-Request has the following structure:

{

    "division" : "",

    "team" : "",

    "userEmail" : "",

    "firstName" : ""

}

When the POST-Requests passed the router X-CSRF-Token check, these values will be read via a Groovy-Script. This script will hold the following logic:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

import groovy.json.*;

def Message processData(Message message) {

    

    def body = message.getBody(java.lang.String) as String;

    

    def jsonSlurper = new JsonSlurper();

    

    def oRequest = jsonSlurper.parseText(body);



    def division = oRequest.division;

    def team = oRequest.team;

    def userEmail = oRequest.userEmail;

    def firstName = oRequest.firstName;



    message.setProperty("division", division);

    message.setProperty("team", team);

    message.setProperty("userEmail", userEmail);

    message.setProperty("firstName", firstName);



    def messageLog = messageLogFactory.getMessageLog(message);

    if(messageLog != null){

        messageLog.setStringProperty("Logging#1", "Printing Payload As Attachment")

        messageLog.addAttachmentAsString("Request payload", "The incoming payload: " + body, "text/plain");

     }

    

    return message;

}

All the payload properties are stored in a different property and available in the next part of the flow.

 

 

3. Get the ZIP file, Unzip it, handle encoding, convert the CSV to JSON.

So now we know the user his preferences, we can fetch the ZIP-File which holds a CSV-File with all the matches.

For this we make a Local Integration process, to make our flow a little bit more understandable and large.

So first, add the Local integration process control:



In here we place the following controls:

A. Start Event

B. Request-Reply

C. ZIP Decompress

D. Handle encoding

E. CSV To XML Converter

F. XML To JSON Converter

G. End event

 

A. So obvious the Start Event is just the Start Event.

 

B. Next we draw an HTTPS-Connection to a Receiver control from our Request-Reply control.

This with the following properties:



The URL where you can get the ZIP-File from:

http://static.belgianfootball.be/project/publiek/download/natresdownP.zip

 

---

How to find the URL (Optional)

When you go the following URL :

https://www.belgianfootball.be/nl/competities/downloads

You open your network tab in the browser console.

You download the ZIP-File like shown in the introduction of this blog.

You filter the network requests in the network tab of your browser by "natresdownP.zip".

Then you will find the URL.

---

 

C. Place the ZIP Decompress control.

This control will automatically unzip your ZIP-File.

 

D. Handle encoding

Depending on how your file is encoded, you can have some strange results in the end if you do not handle this encoding properly.

For example:

Standard de Liège would result in Standard de Li�ge 

If we would not handle the encoding.

To handle this we create the following Groovy script and call it just after the unzipping/decompressing of the ZIP-File:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

def Message processData(Message message) {

    def body = message.getBody();

    def bodyListCsv = body.readLines('windows-1252');

    message.setBody(bodyListCsv.join('\n'));

    return message;

}

Here every line of the CSV-File is read with the windows-1512 encoding.

Next all the lines are joined again by a break line \n.

In the end we have a proper encoded CSV.

 

E. CSV To XML Converter.

Because the CSV-File in the ZIP-File has a specific format, we create an XSD-File and upload it in our CPI-Flow.

First you create a file on your computer and you place the following content in it. This XSD Schema holds the structure of the CSV-File format. Next you save it as: incomingMatches.xsd

<?xml version="1.0" encoding="UTF-8"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="matches">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="match" maxOccurs="unbounded" minOccurs="0">

          <xs:complexType>

            <xs:sequence>

                <xs:element type="xs:string" name="DIV"/>

                <xs:element type="xs:string" name="DATE"/>

                <xs:element type="xs:string" name="HOUR"/>

                <xs:element type="xs:string" name="HOME"/>

                <xs:element type="xs:string" name="AWAY"/>

                <xs:element type="xs:string" name="RH"/>

                <xs:element type="xs:string" name="RA"/>

                <xs:element type="xs:string" name="STATUS"/>

                <xs:element type="xs:string" name="MD"/>

                <xs:element type="xs:string" name="REGNUMBERHOME"/>

                <xs:element type="xs:string" name="REGNUMBERAWAY"/>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

Place the CSV To XML Converter control in the flow and upload the created XSD-File by pressing the select button.



Next in the Path to Target Element in XSD:

matches/match

Choose the option Semicolon(;) as Field Separator.

Check the checkbox for Exclude First Line Header.

 

F. XML to JSON Converter.

Since I like to work with JSON I will convert this XML to JSON.

SO I place an XML to JSON control and provide the following config:



 

G. End Event.

We end our Local Integration process with and End Event control. This will return us to our Main Integration Process.

 

Next you can call it in the flow with the Process Call control, after your Groovy-Script which read the incoming payload. Here in the config, you select your earlier created Local Integration Process.



 

 

4. Filter the matches.

Next we will filter the matches based on the provided team in the body of the POST in the second step.

We use our JSON created from the data in the CSV-File in the Local Integration Process.

This filtering process happens in a Groovy Script.

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

import groovy.json.*;

def Message processData(Message message) {

    

    def body = message.getBody(java.lang.String) as String;

    

    def propertyMap = message.getProperties()

    

    def division = propertyMap.get("division");

    

    def team = propertyMap.get("team");

    

    def jsonSlurper = new JsonSlurper();

    

    def oRequest = jsonSlurper.parseText(body);



    def aMatches = [];



    oRequest.matches.match.each { match ->

        if(match.DIV == division && (match.HOME.contains(team) || match.AWAY.contains(team))) {

            aMatches.push(match);

        }

    }

    

    def finalJsonBuilder = new JsonBuilder();

    def result = finalJsonBuilder {

        data aMatches

    }

            

    message.setBody(finalJsonBuilder.toString());



    def messageLog = messageLogFactory.getMessageLog(message);

    if(messageLog != null){

        messageLog.setStringProperty("Logging#1", "Printing Payload As Attachment")

        messageLog.addAttachmentAsString("Request payload", "The incoming payload: " + body, "text/plain");

     }

    

    return message;

}

We get the properties from our flow, we set in step 2 where we read our incoming payload.

Next we filter our matches in the JSON based on the provided team.

We search for the teams in the correct provided division and select the matches of the provided team Home and Away.

As last we create a final JSON Object and pass it to our message body, so we can work with it in the next step of our flow.

Since the upcoming step will be using a message mapping control, we convert the JSON already to an XML-Format. For this we will add a Root Element to the XML. So make sure you check the checkbox.



 

 

5. Perform a message-mapping and convert the XML output to CSV.

With this message mapping we want to place our filtered matches in our CSV-Output-File.

For this we place a Message-Mapping control in our flow.

After placing the control, select it and press the '+' sign next to it.

This will ask you for a Mapping name, name it: FilteredMatchesToOutgoingCalendarCSV

You will get the following screen:



Before we add our source and target message we will create two new XSD Schemas.

Create the following XSD Schema and name it: filteredMatches.xsd

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="root">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="data" maxOccurs="unbounded" minOccurs="0">

          <xs:complexType>

            <xs:sequence>

              <xs:element type="xs:string" name="DIV"/>

              <xs:element type="xs:string" name="DATE"/>

              <xs:element type="xs:string" name="HOUR"/>

              <xs:element type="xs:string" name="HOME"/>

              <xs:element type="xs:string" name="AWAY"/>

              <xs:element type="xs:string" name="RH"/>

              <xs:element type="xs:string" name="RA"/>

              <xs:element type="xs:string" name="STATUS"/>

              <xs:element type="xs:byte" name="MD"/>

              <xs:element type="xs:short" name="REGNUMBERHOME"/>

              <xs:element type="xs:short" name="REGNUMBERAWAY"/>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

This one looks similar as our earlier created XSD-Schema but is slightly different when you look at the root element and element data. We named it data in our Groovy script and added the root element afterwards.

 

The second XSD-Schema is the schema for the final output. The format that the Google Calendar uses. Create this XSD-Schema and name it: outgoingCalendarCSV.xsd

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="root">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="data" maxOccurs="unbounded" minOccurs="0">

          <xs:complexType>

            <xs:sequence>

              <xs:element type="xs:string" name="Subject"/>

              <xs:element type="xs:string" name="Startdate"/>

              <xs:element type="xs:string" name="Starttime"/>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

 

Now you can provide the source and target message in the message mapping control by pressing the links 'Add source message' and 'Add target message'. Upload the filteredMatches.xsd as source and the outgoingCalendarCSV.xsd as target message.

Once you uploaded the files, draw your mapping lines like this:



Obviously we are working with multiple elements so we connect both root elements we each other and next the "sequence" "multiple elements" data with each other.

Since we are working with CSV-Files, (;) separated headers and values in the CSV-File will need their double quotes around each value. Otherwise all the values for every row will be placed in the first header element for every row.

We can place all these necessary double quotes in our mapping immediately.

This can be done by creating a custom function in the mapping expression.

To create this custom function select a row in the message mapping table and in the bottom of the screen under Mapping Expression press the add icon.



Here you can upload a Groovy script again with the following code:

This code will add a " before and a " after the value. The double quote sign needs to be escaped.

import com.sap.it.api.mapping.*;



def String addQuotes(String value){

    String quote = "\"";

    String result = quote + value + quote;

	return result; 

}

 

Subject

The subject exists of the Home team against the Away team concatenated and delimited by a '-' sign.

Next we also call our custom function to add the double quotes.



Startdate

The start date is transformed from its original date format to the format the Google Calendar is using.

Next again the double quotes function.



Starttime

Start date only needs the double quotes.



By performing these steps you created a working message-mapping.

The next thing we need to do is convert this XML to a CSV-File. Therefor we use the XML to CSV Converter. This with the following config:



Our message-mapping output XML had the "/root/data" path to the elements so this is what we provide here in the Path to Source Element in XSD.

Again and obviously we select the Semicolon(;) as Field Separator in CSV.

 

Now we have our CSV content we want to add our headers to it, this is done in the Content Modifier.

Yes indeed I hear you thinking and I was doing too. If we check the box "Include Field Name as Headers" in the XML To CSV Converter we do not need the Content Modifier for this. Correct, but the problem is that the Google Calendar needs the values start date and start time with a space between the words. This is something that is not possible in the XSD-Schema. So we are forced to use the Content Modifier, which is not a problem.

 

In the Content Modifier under "Message Body", select Type Expression and add the following Body:

"Subject";"Start date";"Start time"

${in.body}

This holds our headers with double quotes and separated by ; and the body gathered from our body property in the flow.

Next if you want to log the result in the flow, you can add a Groovy-Script with the following code to show the CSV-File content.

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

import groovy.json.*;

def Message processData(Message message) {

    

    def body = message.getBody(java.lang.String) as String;

    // Log the incoming payload message

    def messageLog = messageLogFactory.getMessageLog(message);

    if(messageLog != null){

        messageLog.setStringProperty("Logging#1", "Printing Payload As Attachment")

        messageLog.addAttachmentAsString("ResponsePayload:", body, "text/plain");

     }

    

    return message;

}

 

 

6. Add the CSV-File as an attachment for your email.

To add the CSV-File to the email you will send you add the following Groovy-Script to the flow:

import com.sap.gateway.ip.core.customdev.util.Message

import org.apache.camel.impl.DefaultAttachment

import javax.mail.util.ByteArrayDataSource

import java.util.Map

import java.util.Iterator

import javax.activation.DataHandler

def Message processData(Message message) {



   def body = message.getBody(java.lang.String); 

  

   // 2: Construct a ByteArrayDataSource object with the byte array and the content's MIME type

   def dataSource = new ByteArrayDataSource(body, 'text/csv');

   

   // 3: Construct a DefaultAttachment object

   def attachment = new DefaultAttachment(dataSource)



   // 4: Add the attachment to the message

   message.addAttachmentObject('matchCalendar.csv', attachment)

  

   return message



}

This script will read in your CSV-File and add the Mime-type CSV.

Next it will add it as an attachment to the message and uses the provided name matchCalendar.csv

 

7. Send the email to the user his email address.

To setup SMTP on your Cloud Platform Integration tenant you can follow the steps 2.1 & 2.2 & 2.3 in this blog: How to Send Notifications from UI5 App over CPI Flow with Data from HANA DB. An earlier created blog by myself, where I send data from a HANA-Database to users, triggered by a UI5-Application.Application.

First we connect the 'End-Message' with the Receiver and we choose the connection type 'Mail adapter'.

Next we provide the following values for the configuration:

CONNECTION DETAILS

Addresss	smtp.gmail.com:587
Proxy Type	None
Timeout (in ms)	30000
Protection	STARTTLS Mandatory
Authentication	Plain User/Password
Credential Name	Name you used to create the mail credentials in the tenant.

MAIL ATTRIBUTES

From	Email you used together with the password in the credentials.
To	${property.userEmail}
Subject	Your ${property.team} Football Calendar
Mail Body	Hello ${property.firstName}, Please find your ${property.team} football calendar file attached. Kind regards, Soccer Calendar CPI Flow
Body Mime-Type	TEXT/HTML
Body Encoding	UTF-8
Add Message Attachments	Checkbox checked

${property.userEmail} will be replaced with the email provided in the POST-Request by the user.

${property.firstName} will be replaced with the name provided in the POST-Request by the user.

${property.team} will be replaced with the team provided in the POST-Request by the user.



NOW OUR CPI-FLOW IS READY TO BE DEPLOYED!!!

 

 

 

Testing the Integration Flow ?

After deploying the CPI-Flow, you can find you created endpoint in the 'Manage Integration Content' section. Search for your flow and copy the URL endpoint.



 

Next we open the Postman tool, we will perform our HTTP-POST-REQUEST via this tool.

Here we first perform a GET Request and fetch our X-CSRF-Token.

1. Place your copied URL


2. Select the GET type


3. Put your username (S or P user) and password with basic authentication


4. Under Headers set Content-Type to application/json and X-CSRF-Token to fetch


5. Perform the request


6. On the bottom of the screen in the menu, go to headers and copy the value of the X-CSRF-Token

 

Now we have our token, we can perform the HTTP-POST-Request and provide the payload with it.

1. Place your copied URL


2. Select the POST type


3. Put your username (S or P user) and password with basic authentication


4. Under Headers set Content-Type to application/json and X-CSRF-Token to your copied token


5. Add the following payload under body:

{

    "division" : "1A",

    "team" : "Club Brugge",

    "userEmail" : "email address where you want to receive your csv file on.",

    "firstName" : "Dries"

}

6. Perform the request

7. You will see the email that has been sent as a success message.

Hello Dries,



Please find your Club Brugge football calendar file attached.



Kind regards,

Soccer Calendar CPI Flow

 

Let us check if we received an email.

Yep we did:



If we have a look at our CSV-File, it should look like this:



Looks good right? So now close the file but don't save it.

 

 

 

Import the CSV-File in Google Calendar ?

In your Google Calendar go to Settings > Import and Export > Import.

Upload the received CSV-File and press Import.



See the success message on the screen, that says the 30 appointments are imported.



Lets's have a look at our calendar:

 



Yes, all our matches are imported in our Google Calendar! Awesome!

Remember that we handled our file it's encoding right after the unzipping process?

By doing this our special characters are handled the way the should:



Liège is displayed and not Li�ge.

So we are all set!

 

Notifications ?

Not only the desktop notifications are going to safe us from missing a match. Our smartphones are about to play a very important role once again. If you have your gmail/Google calendar synced with your phone, you will get these wonderful notifications that will remind you to watch this important matches!

Lovely! ?



To be honest I totally forgot about this match.

So thank you a lot Cloud Platform Integration! ?

 

What did we learn? ?

In this blog I went over a some nice Cloud Platform Integration tips and tricks and showed how to use maybe some less common controls in CPI-Flows.

So what did we take care of:

1. Provide an incoming HTTP-endpoint and handle X-CSRF-Tokens.


2. Read the incoming payload of the POST-Request.


3. Get the ZIP-File via an HTTP-Request and convert the CSV-File from the zip to JSON.


4. Filter the matches based on the provided team in the body of the POST in step 2.


5. Perform a message-mapping to retrieve the desired XML-Format and convert it to CSV.


6. Add the CSV-File as an attachment for your email.


7. Send the email to the user his email address provided in the body of the POST in step 2.


8. Download your CSV-File.


9. Upload and import your CSV-File in your Google Calendar.

I really hope you liked following this blog and that you found some interesting things that can solve your problems and that they may help you in the future building you own CPI-Flows.

 

Oh and not to forget, I really hope you never have to miss a match again from now on! ?

Kind regards,

Dries