- SAP Managed Tags:
Back to Part I
Back to Part II
Welcome to the third part of this blog series. If you are new to this blog series, please use the links above to follow the prerequisites for this part as we will dive directly into the topic.
In this part we will create a plugin, which will gather additional information from a backend system. In our case it will be SAP Business One via a REST Api which is presented from the B1i Integration Service. In this API we will get data from a UDF from Business Partner Masterdata, which has a loyalty points factor.
Intention is to save a numeric value in your Business Partner Masterdata to control how many loyalty points your customer will gain when purchasing goods in SAP Customer Checkout.
To achieve this, we will need to implement the following things.
So let's start! Open your B1i Integration Service. Because I have installed it on my local machine, I can access it with http://localhost:8080/B1iXcellerator
First of all, we need to check which namespace is set in our B1i. Log into your b1i, click on MAINTENANCE (1) and Cfg Dev Env (2).
Check your Development Prefix (3). In my case it is b1e, but you can choose whatever you want. Please exchange your choice in the later steps. Also activate the Embedded XML Editor (4) and click on save (5).
Next we will check, if our b1i is set as a development system. Go to MAINTENANCE(1) and System Info(2).
If it is already set to Development System you are good to go. If not, change it and restart your b1i.
After that we will create a new b1i scenario package. Go to SCENARIOS(1) and Package Design(2). Click on the plus(3) symbol to create a new b1i scenario package.
At Scenario Package Identifier(4) I will name the scenario package, we will set up as b1e.cco.ext. Please exchange b1e as your chosen development prefix from earlier.
Save(5) this new package. Now we will add a scenario step. Go to Step Design(1) and choose in Scenario Package Filter(2) the package we created earlier. Name your new scenario step in Scenario Step Identifier(3). With the three-dot-button in Scenario Package Identifier(4) you now bind your new step to the existing package.
Note: Select the package with the button. If you just enter the name of your package, your step will not be connected correctly!
Save the scenario package (5).
Now we will configure the inbound channel, which means, we configure, how our scenario step will be triggered.
Click on Inbound(1). A new form will appear. Click on Channel(1). In this Channel form we will configure the following. As I am no b1i expert, I wont go into much detail about this.
Close all forms so that you will be on the Scenario Step Design main form again. Now to the actual processing of our b1i scenario. Click on Processing(1).
Now we need 3 different steps or atoms what they are called in b1i ecosystem. First we need to extract the values of the REST Call e.g. the system number which represents an actual Business One database and some further parameters for handling large sync jobs. With these parameters we will create our sql query for extracting the necessary data.
The second atom is the actual sql call. This atom consumes data from our first atom e.g. the system number.
The third atom will consume the result of our query and transform it to a json response.
Click on the small triangle(1).
Now we add an XSL Transformation atom.
After adding this atom we need to add another atom after the XSL Transformation (xform) atom. Click on this triangle(1).
Now we add a Call SQL Atom.
Now our flow should look something like this.
Now we are going to edit the first atom. Click on the yellowish box on the first atom. The embedded xml editor will now open. Click on this button to "pretty print" the xml.
In the section <xsl:template name="transform"> will be the part, where we will extracting our needed values and create the sql query.
Please add the following section in exchange for the <xsl:template name=transform"></xsl:template> section at the end, I will roughly explain, what I have done here.
In the first 3 lines, I defined some variables called LastModifiedDate, QueryHitsMaximumValue and LastReturnedObjectId. These parameters we will later get from CCO, so that we can handle large sets of responses e.g. if you have thousands of customers to be synced.
After these lines, I check, if the request has an additional parameter called CardCode. With this approach you tackle the following situation:
A new business partner was added in business one but his loyalty factor has not being synced into cco at this moment. The customer buys items at Customer Checkout. So instead of waiting for the next Loyalty Factor synchronization, we will make a dedicated call to the REST Webservice to get the loyalty factor.
For both scenarios (sync mode and one time call) I am merging request data with the sql sync.
Now we will configure the sqlCall atom. Click on the small greenish pen and paper icon(1) of the atom.
First of all please click on the button "HANA" at the end of the form. Now a second SQL Statement inputfield should appear. Maybe now it is becoming more clearer, what I wanted to achieve in the first atom with the two queries for sql and hana. The sqlCall will extract exaclty these values of <sql> or <hanasql> from the previous atom. Now you can safely write hana and mssql queries and b1i will decide, which query it should choose, depending on the connected database.
Furthermore we also extract the SysId from the CCO request. Last thing, we set the Processing Mode to Blocked Processing in batch mode.
Now the last thing we need to do is to transform the output of the sqlCall atom to a json format, which will be sent back to our cco plugin.
Click on the yellowish box of the final atom.
The embedded xml editor will show up, pretty print it like before by pressing this icon(1).
You will find another <xsl:template name="transform"> section. Replace this with the following.
So now we iterate through the sql result set with <xsl:for-each> and extract CardCode and U_LoyFac row by row. At the end of the resultset, we are adding the field LastReturnedObjectId. The cco plugin will then be able to consume the data in batches. This will become more clearer, when we implement the synchronisation. Save this.
Now we just need to tell our scenario step which sender system will accept requests for this. This is done in the Setup Form. Go to SCENARIOS(1) and Setup(2). Choose our package(3). Click on the button Sender(4).
In the Sender Form we choose Define Sender List. A new form will open, where all systems are listed, which can act as sender system. For our reasons, we choose the CustomerCheckoutHttp(1) as it is automatically added, when you are using Customer Checkout with Business One. Save this(2).
After that, we just activate our scenario step to the scenario package. This is done by clicking on steps(1).
Activate the scenario step(1) and save this.
Now we can activate(1) our scenario package and test it.
If everything went well, you should now activate your scenario(1).
It takes some time, but you should see now that the activation was complete.
Congratulations, you just built your first b1i scenario. 🙂
This is actually pretty straight forward. Open the Business One Client, log on to your database, which is connected to your Customer Checkout and add the UDF LoyFac in Business Partner Masterdata.
Open the User-Defined-Fields - Management Form via Tools -> Customization Tools -> User-Defined-Fields Management.
Our UDF should look like this:
Now go to the business partners and fill in some values e.g. for C2000 (I am using the SBODEMODE Database).
Now lets test, if everything works as expected.
For testing purposes, please download postman.
Download and install it. Go to your b1i to SCENARIOS(1) and Control(2). Look for your package (in my case it is b1e.cco.ext) and click on Trigger(3).
In the next form, you will see the url(1) for your webservice. Copy this url.
Now go to your postman, create a new request. Change the mode to POST(1) and insert the url from your webservice(2).
Note: Append &bpm.pltype=json to your url, so b1i knows, that we will send json
Go to the authorization tab(3), change the type to Basic Auth(4) and fill in your B1iadmin credentials.
After this, we will change to the body tab and change it to raw(1), set the body type to JSON (application/json) and fill in the body.
As you can see, we are sending the SYSID (which corresponds to a Business One database) right in the request and we are sending a CardCode to get the LoyaltyFactor for this Business Partner.
The response should look something like that.
Nice! Now that everything is working, lets do some coding to develop our Customer Checkout Plugin.
So please create a new Maven Project like in Part I described. I am going to name it blogpluginpart3.
First of all, we are going to create a user-defined table in CCO to store the different loyalty factors for the business partners (we could use the user-defined fields for business partners in CCO, but for the sake of demonstration we save this in a udt).
As always extend to BasePlugin Class from CCO, set Id, Name and Version of your plugin accordingly.
Lets create a new class, which will create our table (if not existing) and offers convenient access to our data. I will call it LoyaltyFactorDAO (DAO for data access object).
This class has the following methods to access our data.
save, findOne, findAll, dropOne and dropAll. Now we override the startup method in our plugin, so that our user-defined table is going to be set up at plugin startup.
We also create a DTO (data transfer object) class for storing the data from the database in a java object.
Now we just override the startup method and call our setupTable() method from our LoyaltyFactorDAO class.
Build your plugin, copy it into your Customer Checkout pluginfolder, start your cco. With a little help from squirrelSQL you could now check, that your table was created.
After we took care of storing the data in our local customer checkout database, we now take the next step: Calling our b1i webservice via REST.
Anytime I want to access REST-APIs I am using a package called feign. It makes communication with REST-APIs much more convenient, because you don't have to implement much. Feign handles alle the request building, mapping objects from classes to json and vice versa. But you will get the idea as we build this.
First we need some more packages. Go to your pom.xml and add the following packages in the dependencies section.
After all dependencies were download, we create an Interface, which we will call LoyaltyFactorRestInterface.
In the @RequestLine Annotation we insert POST for telling feign, that that the communication with this REST endpoint should be with POST. The rest is the path to our webservice e.g. http://localhost:8080/[path-to-your-webservice].
Now we will use feigns builder pattern, to create a REST client. Feign will automatically create a discrete implementation of the LoyaltyFactorRestInterface.
We get the b1i host, port, sysid and credentials from the ConfigurationHelper class. We also tell feign to use the ApacheHttpClient Implementation, for (de)serialization the Jackson classes and also we set an requestInterceptor, which automatically intercepts every request, we send to our webservice, so that the http basicauthentication is seamlessly added.
Now we just need to create two DTO classes for handling the request and response data.
We are just putting the pieces together. We now use the @Schedulable annotation to create a plugin sync job. This can be configured just like the sync job for business partners, items, and so on.
So add the following method in our Plugin base class.
Furthermore we implement the LoyaltyFactorSyncThread class, where the actual request and response and saving to the database will be handled.
The actual sync is done in the run method within the do - while loop. If the response is empty, we set the syncComplete variable to true, so that the loop is finished.
Now we implement a check whenever a business partner is added to the receipt, it shall be checked, if we have a loyaltyfactor in our local database. If not, we make a dedicated request.
Implement the following method in our base plugin class.
I presume you have already configured your cco for local loyalty point calculation. To implement your own calculation logic, you just have to implement the PointCalculationLogic Interface provided by the API.
Implement this implementation.
As you can see, the overallAmount will be multiplied with the factor, which is stored in our user-defined table.
The last thing we need to do is register this implementation in the plugin startup method. So implement this registration in the startup method of your base plugin class.
Done! Build your plugin and put it into your AP Folder of your Customer Checkout. Go to your configuration.
You should now see a plugin sync job:
Go to Sales(1) and LOYALTY MANAGEMENT(2) and edit your configuration. You should now see your custom point calculation implementation (3).
I chose business partner C2000 which has the factor of 4.
The customer now gets 4 loyalty points for every euro spent. A customer with factor 1 gets 1 point for every euro.
We learned how to implement a small webservice with b1i to expose Business One data based on a sql query.
We learned how easily we can create user-defined tables in cco and access data in a very convenient way.
We saw how easy you can talk to REST Endpoints with the help of openfeign.
With this power we implemented a synchronisation of the data in a separate thread.
And finally we created a custom loyalty point calculation logic.
That’s all for this part of this part. A rather complex one, I think. If you have any questions or feedback, please do not hesitate to get in touch with me.
The code as well as the b1i scenario is hosted on github.
https://gitlab.com/ccoplugins/blogpluginpart3
Stay tuned for the next part!
Back to Part II
Part III: Create a synchronization job to get data from a backend
Introduction
Welcome to the third part of this blog series. If you are new to this blog series, please use the links above to follow the prerequisites for this part as we will dive directly into the topic.
In this part we will create a plugin, which will gather additional information from a backend system. In our case it will be SAP Business One via a REST Api which is presented from the B1i Integration Service. In this API we will get data from a UDF from Business Partner Masterdata, which has a loyalty points factor.
Intention is to save a numeric value in your Business Partner Masterdata to control how many loyalty points your customer will gain when purchasing goods in SAP Customer Checkout.
To achieve this, we will need to implement the following things.
- Create a B1i Scenario which presents the data via REST
- Create a User-defined Table in CCO
- Synchronize Data from B1 via REST over B1i into SAP Customer Checkout
- Override the local loyalty points calculation in SAP Customer Checkout
Create the REST Webservice with B1i
So let's start! Open your B1i Integration Service. Because I have installed it on my local machine, I can access it with http://localhost:8080/B1iXcellerator
First of all, we need to check which namespace is set in our B1i. Log into your b1i, click on MAINTENANCE (1) and Cfg Dev Env (2).
Check your Development Prefix (3). In my case it is b1e, but you can choose whatever you want. Please exchange your choice in the later steps. Also activate the Embedded XML Editor (4) and click on save (5).
Next we will check, if our b1i is set as a development system. Go to MAINTENANCE(1) and System Info(2).
If it is already set to Development System you are good to go. If not, change it and restart your b1i.
After that we will create a new b1i scenario package. Go to SCENARIOS(1) and Package Design(2). Click on the plus(3) symbol to create a new b1i scenario package.
At Scenario Package Identifier(4) I will name the scenario package, we will set up as b1e.cco.ext. Please exchange b1e as your chosen development prefix from earlier.
Save(5) this new package. Now we will add a scenario step. Go to Step Design(1) and choose in Scenario Package Filter(2) the package we created earlier. Name your new scenario step in Scenario Step Identifier(3). With the three-dot-button in Scenario Package Identifier(4) you now bind your new step to the existing package.
Note: Select the package with the button. If you just enter the name of your package, your step will not be connected correctly!
Save the scenario package (5).
Now we will configure the inbound channel, which means, we configure, how our scenario step will be triggered.
Click on Inbound(1). A new form will appear. Click on Channel(1). In this Channel form we will configure the following. As I am no b1i expert, I wont go into much detail about this.
- Inbound Type is HTTP Call
- Process Mode is Synchronous
- Process Trigger is Call
- Identification Method is URL Parameter
- Identification Parameter is urlpar(action)
- Identifier is getLoyaltyFactor
- Identification Namespace is xmlns:b1mb="http://tempuri.org/"
Close all forms so that you will be on the Scenario Step Design main form again. Now to the actual processing of our b1i scenario. Click on Processing(1).
Now we need 3 different steps or atoms what they are called in b1i ecosystem. First we need to extract the values of the REST Call e.g. the system number which represents an actual Business One database and some further parameters for handling large sync jobs. With these parameters we will create our sql query for extracting the necessary data.
The second atom is the actual sql call. This atom consumes data from our first atom e.g. the system number.
The third atom will consume the result of our query and transform it to a json response.
Click on the small triangle(1).
Now we add an XSL Transformation atom.
After adding this atom we need to add another atom after the XSL Transformation (xform) atom. Click on this triangle(1).
Now we add a Call SQL Atom.
Now our flow should look something like this.
Now we are going to edit the first atom. Click on the yellowish box on the first atom. The embedded xml editor will now open. Click on this button to "pretty print" the xml.
In the section <xsl:template name="transform"> will be the part, where we will extracting our needed values and create the sql query.
Please add the following section in exchange for the <xsl:template name=transform"></xsl:template> section at the end, I will roughly explain, what I have done here.
<xsl:template name="transform">
<xsl:variable name="LastModifiedDate" select="utils2:handleSQLString(string(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='LastModifiedDate']))"></xsl:variable>
<xsl:variable name="QueryHitsMaximumValue" select="utils2:handleSQLString(string(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='QueryHitsMaximumValue']))"></xsl:variable>
<xsl:variable name="LastReturnedObjectId" select="utils2:handleSQLString(string(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='LastReturnedObjectId']))"></xsl:variable>
<xsl:choose>
<xsl:when test="count(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='CardCode']) > 0">
<hanasql>SELECT T0."CardCode", IFNULL(T0."U_LoyFac", 1) as "U_LoyFac" from "OCRD" T0 Where T0."CardCode" ='<xsl:value-of select="utils2:handleSQLString(string(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='CardCode']))"></xsl:value-of>'</hanasql>
<sql> SELECT T0.CardCode, ISNULL(T0.U_LoyFac, 1) as 'U_LoyFac' from OCRD T0 Where T0.CardCode = '<xsl:value-of select="utils2:handleSQLString(string(/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='CardCode']))"></xsl:value-of>'</sql>
</xsl:when>
<xsl:otherwise>
<hanasql> Select * FROM (Select ROW_NUMBER() OVER (ORDER BY "CardCode") as Rownumber, "CardCode", IFNULL("U_LoyFac", 1) as "U_LoyFac" FROM OCRD where ("CreateDate" >= '<xsl:value-of select="$LastModifiedDate"></xsl:value-of>' or "UpdateDate" >= '<xsl:value-of select="$LastModifiedDate"></xsl:value-of>')) as QueryResult where QueryResult.Rownumber > <xsl:value-of select="$LastReturnedObjectId"></xsl:value-of> AND QueryResult.Rownumber <= <xsl:value-of select="$LastReturnedObjectId"></xsl:value-of> + <xsl:value-of select="$QueryHitsMaximumValue"></xsl:value-of></hanasql>
<sql> Select * FROM (Select ROW_NUMBER() OVER (ORDER BY "CardCode") as Rownumber, "CardCode", ISNULL("U_LoyFac", 1) as "U_LoyFac" FROM OCRD where ("CreateDate" >= '<xsl:value-of select="$LastModifiedDate"></xsl:value-of>' or "UpdateDate" >= '<xsl:value-of select="$LastModifiedDate"></xsl:value-of>')) as QueryResult where QueryResult.Rownumber > <xsl:value-of select="$LastReturnedObjectId"></xsl:value-of> AND QueryResult.Rownumber <= <xsl:value-of select="$LastReturnedObjectId"></xsl:value-of> + <xsl:value-of select="$QueryHitsMaximumValue"></xsl:value-of></sql>
</xsl:otherwise>
</xsl:choose>
</xsl:template>In the first 3 lines, I defined some variables called LastModifiedDate, QueryHitsMaximumValue and LastReturnedObjectId. These parameters we will later get from CCO, so that we can handle large sets of responses e.g. if you have thousands of customers to be synced.
After these lines, I check, if the request has an additional parameter called CardCode. With this approach you tackle the following situation:
A new business partner was added in business one but his loyalty factor has not being synced into cco at this moment. The customer buys items at Customer Checkout. So instead of waiting for the next Loyalty Factor synchronization, we will make a dedicated call to the REST Webservice to get the loyalty factor.
For both scenarios (sync mode and one time call) I am merging request data with the sql sync.
Now we will configure the sqlCall atom. Click on the small greenish pen and paper icon(1) of the atom.
First of all please click on the button "HANA" at the end of the form. Now a second SQL Statement inputfield should appear. Maybe now it is becoming more clearer, what I wanted to achieve in the first atom with the two queries for sql and hana. The sqlCall will extract exaclty these values of <sql> or <hanasql> from the previous atom. Now you can safely write hana and mssql queries and b1i will decide, which query it should choose, depending on the connected database.
Furthermore we also extract the SysId from the CCO request. Last thing, we set the Processing Mode to Blocked Processing in batch mode.
- Set the SysId to #/vpf:Msg/vpf:Body/vpf:Payload[./@Role='S']/bfa:io/bfa:object/bfa:string[./@name='SYSID']
- Set the Processing Mode to Blocked Processing in batch mode
- Set Default SQL Statement to #/vpf:Msg/vpf:Body/vpf:Payload[@id='atom1']/sql
- Set HANA SQL Statement to #/vpf:Msg/vpf:Body/vpf:Payload[@id='atom1']/hanasql
Now the last thing we need to do is to transform the output of the sqlCall atom to a json format, which will be sent back to our cco plugin.
Click on the yellowish box of the final atom.
The embedded xml editor will show up, pretty print it like before by pressing this icon(1).
You will find another <xsl:template name="transform"> section. Replace this with the following.
<xsl:template name="transform">
<xsl:attribute name="pltype">json</xsl:attribute>
<io xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:com.sap.b1i.bizprocessor:bizatoms" pltype="json" xsi:schemaLocation="urn:com.sap.b1i.bizprocessor:bizatoms json_pltype.xsd">
<array>
<!--optional multiple of the following elements-->
<xsl:for-each select="/vpf:Msg/vpf:Body/vpf:Payload[./@id='atom2']/jdbc:ResultSets/jdbc:ResultSet/jdbc:Row">
<object>
<string name="CardCode">
<xsl:value-of select="./jdbc:CardCode"></xsl:value-of>
</string>
<string name="U_LoyFac">
<xsl:value-of select="./jdbc:U_LoyFac"></xsl:value-of>
</string>
</object>
<xsl:if test="position() = last()">
<object>
<string name="LastReturnedObjectId">
<xsl:value-of select="./jdbc:Rownumber"></xsl:value-of>
</string>
</object>
</xsl:if>
</xsl:for-each>
</array>
</io>
</xsl:template>So now we iterate through the sql result set with <xsl:for-each> and extract CardCode and U_LoyFac row by row. At the end of the resultset, we are adding the field LastReturnedObjectId. The cco plugin will then be able to consume the data in batches. This will become more clearer, when we implement the synchronisation. Save this.
Now we just need to tell our scenario step which sender system will accept requests for this. This is done in the Setup Form. Go to SCENARIOS(1) and Setup(2). Choose our package(3). Click on the button Sender(4).
In the Sender Form we choose Define Sender List. A new form will open, where all systems are listed, which can act as sender system. For our reasons, we choose the CustomerCheckoutHttp(1) as it is automatically added, when you are using Customer Checkout with Business One. Save this(2).
After that, we just activate our scenario step to the scenario package. This is done by clicking on steps(1).
Activate the scenario step(1) and save this.
Now we can activate(1) our scenario package and test it.
If everything went well, you should now activate your scenario(1).
It takes some time, but you should see now that the activation was complete.
Congratulations, you just built your first b1i scenario. 🙂
Adding the user-defined field in Business One
This is actually pretty straight forward. Open the Business One Client, log on to your database, which is connected to your Customer Checkout and add the UDF LoyFac in Business Partner Masterdata.
Open the User-Defined-Fields - Management Form via Tools -> Customization Tools -> User-Defined-Fields Management.
Our UDF should look like this:
Now go to the business partners and fill in some values e.g. for C2000 (I am using the SBODEMODE Database).
Now lets test, if everything works as expected.
Testing our webservice
For testing purposes, please download postman.
Download and install it. Go to your b1i to SCENARIOS(1) and Control(2). Look for your package (in my case it is b1e.cco.ext) and click on Trigger(3).
In the next form, you will see the url(1) for your webservice. Copy this url.
Now go to your postman, create a new request. Change the mode to POST(1) and insert the url from your webservice(2).
Note: Append &bpm.pltype=json to your url, so b1i knows, that we will send json
Go to the authorization tab(3), change the type to Basic Auth(4) and fill in your B1iadmin credentials.
After this, we will change to the body tab and change it to raw(1), set the body type to JSON (application/json) and fill in the body.
{
"SYSID" : "0010000100",
"CardCode" : "C20000"
}As you can see, we are sending the SYSID (which corresponds to a Business One database) right in the request and we are sending a CardCode to get the LoyaltyFactor for this Business Partner.
The response should look something like that.
Nice! Now that everything is working, lets do some coding to develop our Customer Checkout Plugin.
Customer Checkout Plugin
Creating the user-defined table
So please create a new Maven Project like in Part I described. I am going to name it blogpluginpart3.
First of all, we are going to create a user-defined table in CCO to store the different loyalty factors for the business partners (we could use the user-defined fields for business partners in CCO, but for the sake of demonstration we save this in a udt).
As always extend to BasePlugin Class from CCO, set Id, Name and Version of your plugin accordingly.
Lets create a new class, which will create our table (if not existing) and offers convenient access to our data. I will call it LoyaltyFactorDAO (DAO for data access object).
public class LoyaltyFactorDao {
// use the logger from cco
private static final Logger logger = Logger.getLogger(LoyaltyFactorDao.class);
// name our table
private static final String TABLE_NAME = "B1E_LOYALTYFACTOR";
// create query
private static final String QUERY_CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ("
+ "CARDCODE varchar(20) not null PRIMARY KEY,"
+ "LOYALTYFACTOR decimal(18,4) not null)";
// query for adding
private static final String QUERY_INSERT_ROW = "INSERT INTO " + TABLE_NAME + " VALUES(?,?)";
// query for updating
private static final String QUERY_UPDATE_ROW = "UPDATE " + TABLE_NAME
+ " SET LOYALTYFACTOR = ?2 WHERE CARDCODE = ?1";
// query for find all
private static final String QUERY_FIND_ALL = "SELECT CARDCODE, LOYALTYFACTOR FROM " + TABLE_NAME;
// query for find one
private static final String QUERY_FIND_ONE = "SELECT CARDCODE, LOYALTYFACTOR FROM " + TABLE_NAME
+ " where CARDCODE = ?";
// query to drop one
private static final String QUERY_DROP_ONE = "DELETE FROM " + TABLE_NAME + " WHERE CARDCODE = ?";
/**
* Create the table if not existing
*/
public void setupTable() {
// lets open a session
CDBSession session = CDBSessionFactory.instance.createSession();
try {
session.beginTransaction();
EntityManager em = session.getEM();
Query q = em.createNativeQuery(QUERY_CREATE_TABLE);
q.executeUpdate();
session.commitTransaction();
logger.info("Created table " + TABLE_NAME);
} catch (Exception e) {
session.rollbackDBSession();
logger.info("Error or table " + TABLE_NAME + " already existing");
} finally {
session.closeDBSession();
}
}
/**
* Create or update an entry in our table.
*
* @param loyaltyFactor
*/
private void save(LoyaltyFactorDto loyaltyFactor, boolean isAlreadyInDB) {
CDBSession session = CDBSessionFactory.instance.createSession();
String query = isAlreadyInDB ? QUERY_UPDATE_ROW : QUERY_INSERT_ROW;
try {
session.beginTransaction();
EntityManager em = session.getEM();
Query q = em.createNativeQuery(query);
q.setParameter(1, loyaltyFactor.getCardCode());
q.setParameter(2, loyaltyFactor.getLoyaltyFactor().doubleValue());
q.executeUpdate();
session.commitTransaction();
} catch (Exception e) {
session.rollbackDBSession();
logger.info("Could not create LoyaltyFactor");
logger.info(e.getLocalizedMessage());
} finally {
session.closeDBSession();
}
}
/**
* Get all Customer LoyaltyFactors
*
* @return
*/
public List<LoyaltyFactorDto> findAll() {
CDBSession session = CDBSessionFactory.instance.createSession();
ArrayList<LoyaltyFactorDto> resultList = new ArrayList<LoyaltyFactorDto>();
try {
session.beginTransaction();
EntityManager em = session.getEM();
Query q = em.createNativeQuery(QUERY_FIND_ALL);
@SuppressWarnings("unchecked")
List<Object[]> results = q.getResultList();
if (results.isEmpty()) {
// return an empty list rather than null
return resultList;
}
for (Object[] resultRow : results) {
resultList.add(new LoyaltyFactorDto((String) resultRow[0], (BigDecimal) resultRow[1]));
}
} catch (Exception e) {
logger.info("Error while getting results from table " + TABLE_NAME);
} finally {
session.closeDBSession();
}
return resultList;
}
/**
* Get one loyaltyfactor
*
* @param cardCode
* @return
*/
public LoyaltyFactorDto findOne(String cardCode) {
CDBSession session = CDBSessionFactory.instance.createSession();
LoyaltyFactorDto loyaltyFactor = new LoyaltyFactorDto();
try {
EntityManager em = session.getEM();
Query q = em.createNativeQuery(QUERY_FIND_ONE);
q.setParameter(1, cardCode);
@SuppressWarnings("unchecked")
List<Object[]> results = q.getResultList();
if (results.isEmpty()) {
// return empty dto
return loyaltyFactor;
}
loyaltyFactor.setCardCode((String) results.get(0)[0]);
loyaltyFactor.setLoyaltyFactor((BigDecimal) results.get(0)[1]);
} catch (Exception e) {
logger.info("Error while getting " + cardCode + " from table " + TABLE_NAME);
} finally {
session.closeDBSession();
}
return loyaltyFactor;
}
/**
* Drop one loyalty factor
*
* @param cardCode
*/
public void dropOne(String cardCode) {
CDBSession session = CDBSessionFactory.instance.createSession();
try {
session.beginTransaction();
EntityManager em = session.getEM();
Query q = em.createNativeQuery(QUERY_DROP_ONE);
q.setParameter(1, cardCode);
q.executeUpdate();
session.commitTransaction();
} catch (Exception ex) {
} finally {
session.closeDBSession();
}
}
/**
* Drop all loyaltyfactors
*/
public void dropAll() {
List<LoyaltyFactorDto> list = this.findAll();
for (LoyaltyFactorDto entry : list) {
this.dropOne(entry.getCardCode());
}
}
/**
* Save a loyaltyFactor
*
* @param loyaltyFactor
* @return
*/
public void save(LoyaltyFactorDto loyaltyFactor) {
LoyaltyFactorDto loyaltyInDB = this.findOne(loyaltyFactor.getCardCode());
boolean isAlreadyInDb = loyaltyFactor.getCardCode().equals(loyaltyInDB.getCardCode());
// check if entity is already in database, so that we update rather than insert.
this.save(loyaltyFactor, isAlreadyInDb);
}
}This class has the following methods to access our data.
save, findOne, findAll, dropOne and dropAll. Now we override the startup method in our plugin, so that our user-defined table is going to be set up at plugin startup.
We also create a DTO (data transfer object) class for storing the data from the database in a java object.
public class LoyaltyFactorDto {
private String cardCode;
private BigDecimal loyaltyFactor;
public LoyaltyFactorDto() {}
public LoyaltyFactorDto(String inCardCode, BigDecimal inLoyaltyFactor) {
this.cardCode = inCardCode;
this.loyaltyFactor = inLoyaltyFactor;
}
public String getCardCode() {
return cardCode;
}
public void setCardCode(String cardCode) {
this.cardCode = cardCode;
}
public BigDecimal getLoyaltyFactor() {
return loyaltyFactor;
}
public void setLoyaltyFactor(BigDecimal loyaltyFactor) {
this.loyaltyFactor = loyaltyFactor;
}
@Override
public String toString() {
return "LoyaltyFactorDTO [cardCode=" + cardCode + ", loyaltyFactor=" + loyaltyFactor + "]";
}
}Now we just override the startup method and call our setupTable() method from our LoyaltyFactorDAO class.
public class App extends BasePlugin {
private static final Logger logger = Logger.getLogger(App.class);
private LoyaltyFactorDao loyaltyFactorDao;
@Override
public String getId() {
return "pluginpartthree";
}
@Override
public String getName() {
return "CCO Plugin Part 3";
}
@Override
public String getVersion() {
return getClass().getPackage().getImplementationVersion();
}
@Override
public void startup() {
loyaltyFactorDao = new LoyaltyFactorDao();
loyaltyFactorDao.setupTable();
super.startup();
}
}Build your plugin, copy it into your Customer Checkout pluginfolder, start your cco. With a little help from squirrelSQL you could now check, that your table was created.
Create the REST Interface
After we took care of storing the data in our local customer checkout database, we now take the next step: Calling our b1i webservice via REST.
Anytime I want to access REST-APIs I am using a package called feign. It makes communication with REST-APIs much more convenient, because you don't have to implement much. Feign handles alle the request building, mapping objects from classes to json and vice versa. But you will get the idea as we build this.
First we need some more packages. Go to your pom.xml and add the following packages in the dependencies section.
<dependency>
<groupId>io.github.openfeign</groupId>
<artifactId>feign-core</artifactId>
<version>10.0.1</version>
</dependency>
<dependency>
<groupId>io.github.openfeign</groupId>
<artifactId>feign-jackson</artifactId>
<version>10.0.1</version>
</dependency>
<dependency>
<groupId>io.github.openfeign</groupId>
<artifactId>feign-httpclient</artifactId>
<version>10.0.1</version>
</dependency>After all dependencies were download, we create an Interface, which we will call LoyaltyFactorRestInterface.
public interface LoyaltyFactorRestInterface {
@RequestLine("POST /B1iXcellerator/exec/ipo/vP.001sap0013.in_HCSX/com.sap.b1i.vplatform.runtime/INB_HT_CALL_SYNC_XPT/INB_HT_CALL_SYNC_XPT.ipo/proc?action=getLoyaltyFactor&bpm.pltype=json")
List<LoyaltyFactorResponseDto> requestLoyaltyData(LoyaltyFactorRequestDto request);
}In the @RequestLine Annotation we insert POST for telling feign, that that the communication with this REST endpoint should be with POST. The rest is the path to our webservice e.g. http://localhost:8080/[path-to-your-webservice].
Now we will use feigns builder pattern, to create a REST client. Feign will automatically create a discrete implementation of the LoyaltyFactorRestInterface.
@Override
public void startup() {
// setting up the local user-defined-table
loyaltyFactorDao = new LoyaltyFactorDao();
loyaltyFactorDao.setupTable();
// getting the host and port of our b1i server
String b1iHost = ConfigurationHelper.INSTANCE.getB1ServiceDestination().getHost();
int b1iPort = ConfigurationHelper.INSTANCE.getB1ServiceDestination().getPort();
// getting the http basic auth credentials
String httpBasicAuthUser = ConfigurationHelper.INSTANCE.getB1ServiceDestination().getUserName();
String httpBasicAuthPassword = new String(ConfigurationHelper.INSTANCE.getB1ServiceDestination().getPassword());
// use the string builder to create host + port
StringBuilder sb = new StringBuilder();
sb.append(b1iHost);
sb.append(":");
sb.append(b1iPort);
// get the sysid
this.sysId = ConfigurationHelper.INSTANCE.getB1IntegrationSettings().getSystemId();
// setting up our REST Interface
restInterface = Feign.builder()
.client(new ApacheHttpClient())
.encoder(new JacksonEncoder())
.decoder(new JacksonDecoder())
.requestInterceptor(new BasicAuthRequestInterceptor(httpBasicAuthUser, httpBasicAuthPassword))
.target(LoyaltyFactorRestInterface.class, sb.toString());
super.startup();
}We get the b1i host, port, sysid and credentials from the ConfigurationHelper class. We also tell feign to use the ApacheHttpClient Implementation, for (de)serialization the Jackson classes and also we set an requestInterceptor, which automatically intercepts every request, we send to our webservice, so that the http basicauthentication is seamlessly added.
Now we just need to create two DTO classes for handling the request and response data.
@JsonInclude(Include.NON_NULL)
public class LoyaltyFactorRequestDto {
@JsonProperty("SYSID")
private String sysId;
@JsonProperty("LastModifiedDate")
private String lastModifiedDate;
@JsonProperty("QueryHitsMaximumValue")
private String queryHitsMaximumValue;
@JsonProperty("LastReturnedObjectId")
private String lastReturnedObjectId;
@JsonProperty("CardCode")
private String cardCode;
public String getSysId() {
return sysId;
}
public void setSysId(String sYSID) {
this.sysId = sYSID;
}
public String getLastModifiedDate() {
return lastModifiedDate;
}
public void setLastModifiedDate(String lastModifiedDate) {
this.lastModifiedDate = lastModifiedDate;
}
public String getQueryHitsMaximumValue() {
return queryHitsMaximumValue;
}
public void setQueryHitsMaximumValue(String queryHitsMaximumValue) {
this.queryHitsMaximumValue = queryHitsMaximumValue;
}
public String getLastReturnedObjectId() {
return lastReturnedObjectId;
}
public void setLastReturnedObjectId(String lastReturnedObjectId) {
this.lastReturnedObjectId = lastReturnedObjectId;
}
public String getCardCode() {
return cardCode;
}
public void setCardCode(String cardCode) {
this.cardCode = cardCode;
}
}@JsonInclude(Include.NON_NULL)
public class LoyaltyFactorResponseDto {
@JsonProperty("CardCode")
private String cardCode;
@JsonProperty("U_LoyFac")
private String uLoyFac;
@JsonProperty("LastReturnedObjectId")
private String lastReturnedObjectId;
public String getCardCode() {
return cardCode;
}
public void setCardCode(String cardCode) {
this.cardCode = cardCode;
}
public String getULoyFac() {
return uLoyFac;
}
public void setULoyFac(String uLoyFac) {
this.uLoyFac = uLoyFac;
}
public String getLastReturnedObjectId() {
return lastReturnedObjectId;
}
public void setLastReturnedObjectId(String lastReturnedObjectId) {
this.lastReturnedObjectId = lastReturnedObjectId;
}
}Create the sync job and sync the data in a separate thread
We are just putting the pieces together. We now use the @Schedulable annotation to create a plugin sync job. This can be configured just like the sync job for business partners, items, and so on.
So add the following method in our Plugin base class.
@Schedulable("LoyaltyFactor Sync")
public void syncLoyaltyFactor(PluginJob job, TriggerParameter triggerParam, I14YContext context, Object[] params){
// We get the SyncStatusmanager from the database
CDBSession session = CDBSessionFactory.instance.createSession();
SynchStatusManager syncStatusManager = new SynchStatusManager(session);
Date lastUpdate = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
// We use the customerquerymax results for this. Maybe a plugin configuration would also be suitable.
Integer batchSize = ConfigurationHelper.INSTANCE.getB1IntegrationSettings().getCustomerQueryMaxResults();
// Is it a fully snyc?
if(triggerParam.isCleanSync())
{
try {
lastUpdate = dateFormat.parse("1970-01-01");
} catch (ParseException e) {
logger.severe(e.getLocalizedMessage());
}
}
else
{
lastUpdate = syncStatusManager.readLastUpdateOfObject(PluginJob.createObjectId(triggerParam.getPluginId(), triggerParam.getPluginMethodId()));
}
// Create the sync thread.
try {
ExecutorService executor = Executors.newCachedThreadPool();
executor.execute(new LoyaltyFactorSyncThread(this.restInterface, this.sysId, dateFormat.format(lastUpdate), batchSize));
} catch (Exception e) {
logger.info("Error while sync");
} finally {
session.closeDBSession();
}
}Furthermore we implement the LoyaltyFactorSyncThread class, where the actual request and response and saving to the database will be handled.
public class LoyaltyFactorSyncThread implements Runnable {
private LoyaltyFactorRestInterface restClient;
private LoyaltyFactorDao loyaltyFactorDao;
private String sysId;
private String lastUpdate;
private Integer messageSize;
private String lastReturnedObjectId = "0";
public LoyaltyFactorSyncThread(LoyaltyFactorRestInterface restClient, String sysId, String lastUpdate, Integer messageSize) {
this.restClient = restClient;
this.sysId = sysId;
this.lastUpdate = lastUpdate;
this.messageSize = messageSize;
this.loyaltyFactorDao = new LoyaltyFactorDao();
}
public void run() {
boolean syncComplete = false;
do {
LoyaltyFactorRequestDto request = new LoyaltyFactorRequestDto();
request.setLastModifiedDate(this.lastUpdate);
request.setSysId(this.sysId);
request.setQueryHitsMaximumValue(this.messageSize.toString());
request.setLastReturnedObjectId(lastReturnedObjectId);
List<LoyaltyFactorResponseDto> response = this.restClient.requestLoyaltyData(request);
if(!response.isEmpty()) {
for(int i = 0; i < response.size(); i++) {
if(i == response.size() - 1) {
this.lastReturnedObjectId = response.get(i).getLastReturnedObjectId();
break;
}
this.loyaltyFactorDao.save(new LoyaltyFactorDto(response.get(i).getCardCode(), new BigDecimal(response.get(i).getULoyFac())));
}
} else {
syncComplete = true;
}
} while (!syncComplete);
}
}The actual sync is done in the run method within the do - while loop. If the response is empty, we set the syncComplete variable to true, so that the loop is finished.
Now we implement a check whenever a business partner is added to the receipt, it shall be checked, if we have a loyaltyfactor in our local database. If not, we make a dedicated request.
Implement the following method in our base plugin class.
@PluginAt(pluginClass = IReceiptManager.class, method ="setBusinessPartner", where = POSITION.AFTER)
public Object checkForLoyaltyFactor(Object proxy, Object[] args, Object ret, StackTraceElement caller) {
try {
BusinessPartnerEntity bp = (BusinessPartnerEntity) args[1];
LoyaltyFactorDto loyaltyFactorDto = this.loyaltyFactorDao.findOne(bp.getExternalId());
// If cardcode is empty, then it is not in the database...
if(StringUtils.isEmpty(loyaltyFactorDto.getCardCode())) {
LoyaltyFactorRequestDto request = new LoyaltyFactorRequestDto();
request.setSysId(this.sysId);
request.setCardCode(bp.getExternalId());
List<LoyaltyFactorResponseDto> response = restInterface.requestLoyaltyData(request);
if(!response.isEmpty()) {
this.loyaltyFactorDao.save(new LoyaltyFactorDto((String) response.get(0).getCardCode(), new BigDecimal(response.get(0).getULoyFac())));
}
}
} catch (Exception e) {
logger.info(e.getLocalizedMessage());
}
return ret;
}Loyalty Point Calculation
I presume you have already configured your cco for local loyalty point calculation. To implement your own calculation logic, you just have to implement the PointCalculationLogic Interface provided by the API.
Implement this implementation.
public class LoyaltyPointCalculation implements PointCalculationLogic {
public BigDecimal calculatePointValue(ReceiptEntity receipt) {
BigDecimal overallAmount = BigDecimal.ZERO;
// get the overall amount for this receipt
for(SalesItemEntity item : receipt.getSalesItems()) {
overallAmount = overallAmount.add(item.getLoyaltyPoints());
}
// if a businesspartner is set, get the factor and multiply
if(receipt.getBusinessPartner() != null) {
LoyaltyFactorDao loyaltyFactorDao = new LoyaltyFactorDao();
// get the loyaltyFactor for this business partner
LoyaltyFactorDto loyaltyFactorDto = loyaltyFactorDao.findOne(receipt.getBusinessPartner().getExternalId());
overallAmount = overallAmount.multiply(loyaltyFactorDto.getLoyaltyFactor());
}
return overallAmount;
}
public BigDecimal calculatePointValue(SalesItemEntity salesItem) {
return SalesItemEntity.SalesItemTypeCode.MATERIAL.equals(salesItem.getTypeCode()) ? salesItem.getPaymentGrossAmount() : BigDecimal.ONE;
}
public void distributePointsToItems(ReceiptEntity arg0) {
}
public String getId() {
return "LoyaltyPointCalc";
}
}As you can see, the overallAmount will be multiplied with the factor, which is stored in our user-defined table.
The last thing we need to do is register this implementation in the plugin startup method. So implement this registration in the startup method of your base plugin class.
CDBSession session = CDBSessionFactory.instance.createSession();
try {
LoyaltyPosService loyaltyPosService = new LoyaltyPosServiceImpl(session);
loyaltyPosService.registerPointCalculationLogic(new LoyaltyPointCalculation());
} catch (Exception e) {
logger.info(e.getMessage());
} finally {
session.closeDBSession();
}Done! Build your plugin and put it into your AP Folder of your Customer Checkout. Go to your configuration.
You should now see a plugin sync job:
Go to Sales(1) and LOYALTY MANAGEMENT(2) and edit your configuration. You should now see your custom point calculation implementation (3).
The plugin in action
I chose business partner C2000 which has the factor of 4.
The customer now gets 4 loyalty points for every euro spent. A customer with factor 1 gets 1 point for every euro.
Wrap-up
We learned how to implement a small webservice with b1i to expose Business One data based on a sql query.
We learned how easily we can create user-defined tables in cco and access data in a very convenient way.
We saw how easy you can talk to REST Endpoints with the help of openfeign.
With this power we implemented a synchronisation of the data in a separate thread.
And finally we created a custom loyalty point calculation logic.
That’s all for this part of this part. A rather complex one, I think. If you have any questions or feedback, please do not hesitate to get in touch with me.
The code as well as the b1i scenario is hosted on github.
https://gitlab.com/ccoplugins/blogpluginpart3
Stay tuned for the next part!
12 Comments
