Skip to Content

I have recently finished developing a project forecast solution for a customer using native HANA elements for the backend part and UI5 for the frontend.

Image01_wondertool.png

Essentially this solution is based on master data for projects and it allows users(project owner/leads) to update the forecast on their project both hours, travel and more. Forecast are entered in hours and then linked to a specific resource type (which has a rate) making it possible to calculate the forecasted project cost.

During discussions of the future direction of this development several elements have been discussed. One of the ones which I found really interesting was the possibility to integrate the solution build with some off-the-shelves time entry tools out there. This would allow for a time registration which is fairly straight forward coupled with the forecast solution – and fairly quick.

Before continuing – you will notice that I have setup an email which are used when accessing the 3rd party service – that mail is by no means monitored or read it is a one off mail just this blog.

Tickspot setup

There are many different providers in this area offering different solution some offering simple time entry some more advanced ones. For this blog I chose to go with tickspot.com which has a rather clear API documentation available https://github.com/tick/tick-api.

Getting an account

To start we establish an account. Go to https://www.tickspot.com/signup to setup a trial account. Initially enter name(first and last), a mail address and a company.

ImageTickspot_01.png

Once complete start your 30-day trial. Within some time you would receive a mail titled “Welcome to Tick”.

ImageTickspot_02.png

Following the instructions here will eventually take you to the login page where you will be prompted to change your password.

ImageTickspot_03.png

Well now you have an account on tickspot and you can start using it.

ImageTickspot_04.png

Keying in some data which we use for testing

Before we continue lets add a little bit of test data in tickspot. Time entries in tickspot are done on a task which in turn belongs to a project that has a client. In the following we create a project (which in turn allow us to setup a client and a number of tasks).

ImageTickspot_Project_01.png

Now lets add some hours to the project just created. Tickspot has a web app as well as apps for IOS and Android. I create two entries one using the web app and one using the IOS app.

From the web app I add 5hrs.

ImageTickspot_entry_web_01.png

At this point hit the “Enter Time” button to save and the “Time entered for the day” will be updated. The next 7 hrs are added thru the IOS app.

ImageTickspot_entry_ios_01.png

So at this point we have 12 hrs added on the project – for now we are done with the Tick application and will focus on understanding the API and next implement some of that in HANA.

Understanding the Tickspot API and elements

In order to work with the API we need to take a look at the available documentation and test before being able to build the solution in detail. For tickspot the documentation is available on github following this link https://github.com/tick/tick-api. To make the analysis and testing of an web based API quicker consider using getpostman.com, paw from luckymarmot.com or something similar.

BTW you don’t have to read this at all you can fast forward to the implementation in HANA by scrolling down.

In the following section the web requests are available in CURL (don’t worry the hana javascript will be revealed later on) but for now the CURL notation is shorter and “to the point”.

Getting a token for your solution

To start with we need to call the API with our user and credentials to receive the subscription ID, and token which are used later – below you will see that the user and password have been base64 encoded and set into the header element “Authorization:” of the request. The request looks as follows:

curl -X "GET" "https://www.tickspot.com/api/v2/roles.json" \
     -H "Authorization: Basic <username and password encoded>" \
     -H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)"

Before continuing be sure to change the headers part where it says authorizations and include your own user and password encoded – you should get a JSON text in return with something like the following:

[
  {
    "subscription_id": 90497,
    "company": "NoComp",
    "api_token": "<secret token goes here>"
  }
]

Note that in the following calls our URL endpoint will use the subscription ID eg like the following https://www.tickspot.com/90497/api/v2/projects.json furthermore the api_token returned will be used.

Getting a user list

As a part of this initial API call we do not receive a userid for our user – this needs to be obtained subsequently. If the user which we have is an admin user then this call returns all users – however in our case we only at this point have one user.

curl -X "GET" "https://www.tickspot.com/90497/api/v2/users.json" \
     -H "Authorization: Token token=<secret token goes here>" \
     -H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)"

It returns something similar to

[
  {
    "id": 255227,
    "first_name": "John",
    "last_name": "TryOutAlot",
    "email": "johntryout@maildrop.cc",
    "timezone": "Eastern Time (US & Canada)",
    "created_at": "2016-07-26T10:58:29.000-04:00",
    "updated_at": "2016-07-27T03:03:53.000-04:00"
  }
]

And from here we use the owner creating a project later on we need to use the ID as owner.

Creating a Client

Creating a client from the API side requires us to do a POST operation. The clients part return the client ID which is a prerequisite before creating a project. The call is a POST and it looks something like this where the data name and archive are supplied in the data part as json.

curl -X "POST" "https://www.tickspot.com/90497/api/v2/clients.json" \
     -H "Content-Type: application/json" \
     -H "Authorization: Token token=<secret token goes here>" \
     -H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)" \
     -d $'{
  "name":"New Large Account Client",
  "archive":false
}'

The call returns the project id and some details

{
  "id": 289679,
  "name": "New Large Account Client",
  "archive": false,
  "url": "https://www.tickspot.com/90497/api/v2/clients/289677.json",
  "updated_at": "2016-07-27T03:32:20.654-04:00"
}

Creating projects for a Client

With the token and subscription ID, client id and user id from the previous calls it will be possible to create a project thru the API. In this call we create a project “Once again A-Project,..” and set a budget of 100 hours to it. The call looks as follows:

curl -X "POST" "https://www.tickspot.com/90497/api/v2/projects.json" \
     -H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)" \
     -H "Authorization: Token token=<secret token goes here>" \
     -H "Content-Type: application/json" \
     -d $'{
   "client_id": 289679",
   "owner_id" : "255227",
   "name"     : "Once again A-Project, superDuper project ",
   "budget"   : 100
}'

It returns the following JSON

{
  "id": 1354120,
  "name": "Once again A-Project, superDuper project ",
  "budget": 100.0,
  "date_closed": null,
  "notifications": false,
  "billable": true,
  "recurring": false,
  "client_id": 289677,
  "owner_id": 255227,
  "url": "https://www.tickspot.com/90497/api/v2/projects/1354120.json",
  "created_at": "2016-07-27T03:47:29.514-04:00",
  "updated_at": "2016-07-27T03:47:29.514-04:00"
}

Hours are registered on task level

Besides having a project, client and user set we need to create tasks as this is where hours can be booked. For each project I want to create four tasks representing the four phases as below

  • Discover & Design
  • Build
  • Train & Test
  • Deploy

In this call I create the first task on the project which has been created – creating the subsequent tasks follows the same remedy.

curl -X "POST" "https://www.tickspot.com/90497/api/v2/tasks.json" \
     -H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)" \
     -H "Authorization: Token token=<secret token goes here>" \
     -H "Content-Type: application/json" \
     -d $'{
  "name":"Discover & Design",
  "project_id":1354120,
  "billable":true
}'

From this call we receive the following information

{
  "id": 8768032,
  "name": "Discover & Design",
  "budget": null,
  "position": 1,
  "project_id": 1354120,
  "date_closed": null,
  "billable": true,
  "url": "https://www.tickspot.com/90497/api/v2/tasks/8768031.json",
  "created_at": "2016-07-27T05:39:46.048-04:00",
  "updated_at": "2016-07-27T05:39:46.048-04:00"
}

Reading entered times from the project

Up to this point we have called the API to setup a client, and create a project and task. Now the original idea was to use tickspot for a user to enter his hours spent and transfer that to HANA. Extracting hours using the API is done as follows (in this call I specified the range for which I want hours to be extracted using the start and end date).

curl -X "GET" "https://www.tickspot.com/90497/api/v2/entries.json?start_date=%272016-07-01%27&  \
end_date=%272016-07-31%27" \
-H "Content-Type: application/json; charset=utf-8" \
-H "Authorization: Token token=<secret token goes here>" \
-H "User-Agent: ourHanaIntegrationJob (johntryout@maildrop.cc)" \
-d "{}"

It returns the entered hours which was keyed in earlier it looks as follows

[
   {
     "id": 53571926,
     "date": "2016-07-26",
     "hours": 7.0,
     "notes": "Initial session",
     "task_id": 8768031,
     "user_id": 255227,
     "url": "https://www.tickspot.com/90497/api/v2/entries/53571926.json",
     "created_at": "2016-07-27T07:46:25.000-04:00",
     "updated_at": "2016-07-27T07:46:25.000-04:00"
   },
   {
     "id": 53571252,
     "date": "2016-07-27",
     "hours": 5.0,
     "notes": "Spent on something",
     "task_id": 8768031,
     "user_id": 255227,
     "url": "https://www.tickspot.com/90497/api/v2/entries/53571252.json",
     "created_at": "2016-07-27T06:43:45.000-04:00",
     "updated_at": "2016-07-27T06:43:45.000-04:00"
   }
 ]

Using the API from HANA

Up to this point we have fiddled with the API from outside HANA. For the purpose of this demo I decided to define a couple of webservices to extract data from tickspot into a hana table(and vice versa). Before getting to that part there are some config and coding to be done.

Setup tickspot as a httpdestination

Before we can consume webservices we have to configure two http destinations for tickspot. We define two as we want to use basic authentification for the initial call(where we receive our token). We define another one which runs with no authentification but uses only the token. The xshttpdest files are as follows

tickspot.xshttpdest

description = "Time Tracking";
host = "www.tickspot.com";
port = 443;
pathPrefix = "";
proxyType = none;
proxyHost = "";
proxyPort = 0;
authType = basic;
useSSL = true;
timeout = 0;
sslHostCheck = true;
sslAuth = anonymous;

tickspotToken.xshttpdest

description = "Time Tracking with Token";
host = "www.tickspot.com";
port = 443;
pathPrefix = "";
proxyType = none;
proxyHost = "";
proxyPort = 0;
authType = none;
useSSL = true;
timeout = 0;
sslHostCheck = true;
sslAuth = anonymous;

Besides it is necessary to configure the user and password on the first destination from the xsadmin page like so:

ImageXSadmin_Httpdest.png

Defining tables for the data

The original UI5/Native HANA solution shown at the very top has a complex data model instead of me explaining that in detail I have chosen to create a few simple tables which makes the following  a little bit simpler. Lets define two tables (I have a schema called TICKSPOT for it) – the sql are shown here:

Table for project actuals

This will hold the hours entered on a given project and task.

CREATE COLUMN TABLE "projectActuals"
(
     "projectID" INTEGER      CS_INT NOT NULL ,
      "taskID"    INTEGER      CS_INT NOT NULL ,
      "userID"    INTEGER      CS_INT NOT NULL ,
      "date"      NVARCHAR     (8)    NOT NULL ,
      "notes"     NVARCHAR     (256),
      "hours"     DECIMAL      (5,2) CS_FIXED,
     PRIMARY KEY ("projectID", "taskID", "userID", "date")
)

Table for project forecast

This will hold the projects available and their forecast(we will initially fill this from tickspot but the intention here is to make sure that the budget are synchronized from HANA to tickspot).

CREATE COLUMN TABLE "projectForecast"
(
   "projectID" INTEGER      CS_INT  NOT NULL,
   "name"      NVARCHAR     (256),
   "clientID"  INTEGER      CS_INT,
   "ownerID"   INTEGER      CS_INT,
   "budgetHrs" DECIMAL      (5,2)   CS_FIXED,
   PRIMARY KEY ("projectID")
);

Creating a xsjs library for tickspot related functions

In order to make the latter examples a little more simpler I have added the tickspot related functions to a library (tickSpotFunctions.xsjslib) which has been defined below.

tickSpotFunctions.xsjslib

/**
 * tickSpotFunctions.xsjslib
 *
 * contains functions used for tickspot integration
 *
 * Created by mbruhn on 02/08/16.
 */


/**
 * Stringify()
 * encloses the incomming str as a string meaning adding ' infront and back
 * @param str
 * @returns {string}
 */
function stringify(str)
{
    return "\'"+str+"\'";
}

/**
 * getTickSpotToken()
 * executes a call to tickspot and uses basic authentication (user, pwd)
 * it obtains the token and subscription necessary
 *
 * @param hanaPackage, hana package in where the httpdest file is located
 * @param destination, name of httpdest file
 * @param userAgent,   text which acts as the header for userAgent
 * @returns {{subscriptionID: *, apiToken: string}} subscription ID, and token
 */
function getTickSpotToken(hanaPackage, destination, userAgent)
{
// Step a - read the destination
    var dest = $.net.http.readDestination(hanaPackage, destination);
    var client = new $.net.http.Client();

// Step b - setup request and header required by tickspot
    var req = new $.web.WebRequest($.net.http.GET, "/api/v2/roles.json");
    req.headers.set("User-Agent", userAgent);

// Step c - execute request and get the response
    client.request(req, dest);
    var response = client.getResponse();

// Step d - Parse the JSON and pickup token and subscription which is needed later
    var obj = JSON.parse(response.body.asString());

    return {
        subscriptionID : obj[0].subscription_id,
        apiToken : "Token token=" + obj[0].api_token };
}

/**
 * getPostedHours()
 * Calls the api to retrieve the posted hours with in a range
 *
 * @param hanaPackage, hana package in where the httpdest file is located
 * @param destination, name of httpdest file
 * @param userAgent,   text which acts as the header for userAgent
 * @param token,       token and subscription id
 * @param startDate,   start date YYYY-MM-DD for when the extract should run
 * @param endDate,     end date YYYY-MM-DD for when the extract should run
 * @returns {*}
 */
function getPostedHours(hanaPackage, destination, userAgent, token, startDate, endDate)
{
    // Step  a - We use a different destionation for this call as the authorizations are
    //           handled different from the first call
    var destToken = $.net.http.readDestination(hanaPackage, destination);
    var clientToken = new $.net.http.Client();

    // Step  b - we configure the URL with the endpoint for entries
    //           further more we have added start and end date (hardcoded)
    var timeEntryUrl = "/" + token.subscriptionID + "/api/v2/entries.json" + "?start_date=" + startDate + "&end_date=" +endDate;
    var timeEntriesReq = new $.web.WebRequest($.net.http.GET, timeEntryUrl);

    // We configure the headers, user agent was already uset earlier
    // but we set the Authorization to be our token just received from step 1
    timeEntriesReq.headers.set("Authorization", token.apiToken);
    timeEntriesReq.headers.set("User-Agent", userAgent);

    // Step c - execute request and get the response
    clientToken.request(timeEntriesReq, destToken);

    var responseWithEntries = clientToken.getResponse();
    var responseAsString = responseWithEntries.body.asString();
    return JSON.parse(responseAsString);
}

/**
 * getProjectList()
 * Calls the api to retrieve all available projects
 *
 * @param hanaPackage, hana package in where the httpdest file is located
 * @param destination, name of httpdest file
 * @param userAgent,   text which acts as the header for userAgent
 * @param token,       token and subscription id
 * @returns {*}        records object
 */
function getProjectList(hanaPackage, destination, userAgent, token)
{
    // Step  a - We use a different destionation for this call as the authorizations are
    //           handled different from the first call
    var destToken = $.net.http.readDestination(hanaPackage, destination);
    var clientToken = new $.net.http.Client();

    // Step  b - we configure the URL with the endpoint for project
    var projectListUrl = "/" + token.subscriptionID + "/api/v2/projects.json";
    var projectListsReq = new $.web.WebRequest($.net.http.GET, projectListUrl);

    // We configure the headers, user agent was already uset earlier
    // but we set the Authorization to be our token just received from step 1
    projectListsReq.headers.set("Authorization", token.apiToken);
    projectListsReq.headers.set("User-Agent", userAgent);

    // Step c - execute request and get the response
    clientToken.request(projectListsReq, destToken);

    var responseWithEntries = clientToken.getResponse();
    var responseAsString = responseWithEntries.body.asString();
    return JSON.parse(responseAsString);
}

/**
 * updateProjectBudget()
 * Calls the api to update the budget of the projects sent along
 *
 * @param hanaPackage, hana package in where the httpdest file is located
 * @param destination, name of httpdest file
 * @param userAgent,   text which acts as the header for userAgent
 * @param token,       token and subscription id
 * @param projects,    list of projects
 *
 */
function updateProjectBudget(hanaPackage, destination, userAgent, token, projects)
{
    // Step  a - We use a different destionation for this call as the authorizations are
    //           handled different from the first call
    var destToken = $.net.http.readDestination(hanaPackage, destination);
    var clientToken = new $.net.http.Client();
    var i = 0;
    var projectUrl;
    var projectReq;
    var oProject;
    var responseData;

    for (i = 0; i < projects.length; i++ )
    {
        oProject = projects[i];

        // Step  b - we configure the URL with the endpoint for projects
        projectUrl = "/" + token.subscriptionID + "/api/v2/projects/" + oProject.projectID + ".json";
        projectReq = new $.web.WebRequest($.net.http.PUT, projectUrl);

        // We configure the headers, user agent was already uset earlier
        // but we set the Authorization to be our token just received from step 1
        projectReq.headers.set("Authorization", token.apiToken);
        projectReq.headers.set("User-Agent", userAgent);

        // configure the budget parameter necessary in the body of the request
        projectReq.contentType = "application/json";

        projectReq.setBody('{"budget":' + oProject.budgetHrs + '}');

        clientToken.request(projectReq, destToken);
        responseData = clientToken.getResponse();
    }
}

/**
 * savePostedHours()
 * Saves the hours passed as registeredHrs
 *
 * @param registeredHrs, registered hours read from APi
 * @param hot, if true then commit to data base
 * @returns {string}, DB statements as plain text
 */
function savePostedHours(registeredHrs, hot)
{
    var stmtUpd = "";
    var stmtUpdInfo = "";
    var i = 0;
    var oRecord;
    var pstmt;
    var conn = $.db.getConnection();

//  process each entry 1-by-1
    for (i = 0; i < registeredHrs.length; i++ )
    {
        oRecord = registeredHrs[i];
        oRecord.date = oRecord.date.replace(/-/g, '');  // remove dashes from date field

        /* Setup database call using upsert to update already existing records */
        stmtUpd  = 'upsert \"TICKSPOT".\"projectActuals\"' +
            ' ("projectID", "taskID", "userID", "date", "notes", "hours") VALUES (' +
            oRecord.id + ', ' + oRecord.task_id + ', ' + oRecord.user_id + ', ' +
            stringify(oRecord.date) + ', ' + stringify(oRecord.notes) + ', ' + oRecord.hours + ') WITH PRIMARY KEY';

        stmtUpdInfo += stmtUpd+"
"; // just want to return the database statements to the body         /* Fire away */         if (hot === true)         {             pstmt = conn.prepareStatement(stmtUpd);             pstmt.execute();             pstmt.close();             conn.commit();         }     }     conn.close();     return stmtUpdInfo; } /** * saveProjectList() * Saves the projectList passed as projectList * * @param projectList, registered projects read from API * @param hot, if true then commit to data base * @returns {string}, DB statements as plain text */ function saveProjectList(projectList, hot) {     var stmtUpd = "";     var stmtUpdInfo = "";     var i = 0;     var oRecord;     var pstmt;     var conn = $.db.getConnection(); //  process each entry 1-by-1     for (i = 0; i < projectList.length; i++ )     {         oRecord = projectList[i];         /* Setup database call using upsert to update already existing records */         stmtUpd  = 'upsert \"TICKSPOT".\"projectForecast\"' +             ' ("projectID", "name", "clientID", "ownerID", "budgetHrs") VALUES (' +             oRecord.id + ', ' + stringify(oRecord.name) + ', ' + oRecord.client_id + ', ' +             oRecord.owner_id + ', ' +  oRecord.budget + ') WITH PRIMARY KEY';         stmtUpdInfo += stmtUpd+"
"; // just want to return the database statements to the body         /* Fire away */         if (hot === true)         {             pstmt = conn.prepareStatement(stmtUpd);             pstmt.execute();             pstmt.close();             conn.commit();         }     }     conn.close();     return stmtUpdInfo; } /** * readProjectList() * reads the projectList from projectForecast table * in HANA * @returns {string}, records returned */ function readProjectList() {     var stmtUpd = "";     var oRecord={};     var oRecordArray=[];     var pstmt;     var rs;     var conn = $.db.getConnection();     /* Select records to read */     stmtUpd  = 'select * from \"TICKSPOT".\"projectForecast\"';     pstmt = conn.prepareStatement(stmtUpd);     rs =  pstmt.executeQuery();     while(rs.next())     {         oRecord.projectID = rs.getInteger(1);         oRecord.name      = rs.getNString(2);         oRecord.clientID  = rs.getInteger(3);         oRecord.ownerID   = rs.getInteger(4);         oRecord.budgetHrs = rs.getDecimal(5);         oRecordArray.push(JSON.parse(JSON.stringify(oRecord)));     }     pstmt.close();     conn.close();     return oRecordArray; }

The main functions available in the library are as follows:

  • getTickSpotToken(). Calls the tickspot api and authenticates it self inorder to obtain subscription and tokens
  • getPostedHours(). Calls the tickspot api to retrieve the posted hours with in a given date range
  • savePostedHours(). This saves the hours to the projectActuals table.
  • getProjectList(). Calls the tickspot api to retrieve the list of available projects.
  • updateProjectBudget(). Calls the tickspot api to update the project budget.
  • saveProjectList(). Saves the project list to HANA.
  • readProjectList(). Reads the project list from HANA.

Using Get – Reading Actual data and storing them in a simple table.

In this part here the integration moves the actual spent hours from tickspot to the HANA solution.

getRecordedEntries.XSJS

/**
 * getRecordedEntries.xsjs
 *
 * Reads posted hours from tickspot
 * and updates the actual entries table in HANA
 *
 * Created by mbruhn on 02/08/16.
 */

/* Import library with functions for tickspot*/
$.import("getTickTimes", "tickSpotFunctions");
var gTickSpot = $.getTickTimes.tickSpotFunctions;

/* PROCESSING */
var hot = true; // update db(true) or not(false)

var userAgentInfo = "ourHanaIntegrationJob (johntryout@maildrop.cc)";
var tokens = gTickSpot.getTickSpotToken("getTickTimes", "tickspot", userAgentInfo);
var registeredEntries = gTickSpot.getPostedHours("getTickTimes", "tickspotToken", userAgentInfo, tokens, "2016-07-01", "2016-07-31");

// sending back simple text information
$.response.setBody(gTickSpot.savePostedHours(registeredEntries, hot));

A simple test is done from the browser using the URL

<HOST>:<PORT>/getTickTimes/getRecordedEntries.xsjs

Notice that you will need to change the package if you didn’t the same place as I did. You should see something along the lines of this:

upsert "TICKSPOT"."projectActuals" ("projectID", "taskID", "userID", "date", "notes", "hours")
VALUES (53571926, 8768031, 255227, '20160726', 'Initial session', 7) WITH PRIMARY KEY .....

Using Get – read the created projects

In HANA we don’t have the project ID and description connected. This will prove beneficial for the update which we will do later on. For this purpose we initially load from tickspot to the table projectForecast. Afterwards we will use the budget figure maintained in this table and synchronize it with tickspot.

getProjectList.xsjs

/**
 * getProjectList.xsjs
 *
 * Reads the available projects
 * and updates the forecast entries table in HANA
 *
 * Created by mbruhn on 02/08/16.
 */

/* Import library with functions for tickspot*/
$.import("getTickTimes", "tickSpotFunctions");
var gTickSpot = $.getTickTimes.tickSpotFunctions;

/* PROCESSING */
var hot = true; // update db(true) or not(false)

var userAgentInfo = "ourHanaIntegrationJob (johntryout@maildrop.cc)";
var tokens = gTickSpot.getTickSpotToken("getTickTimes", "tickspot", userAgentInfo);
var registeredProjects = gTickSpot.getProjectList("getTickTimes", "tickspotToken", userAgentInfo, tokens);

// sending back simple text information
$.response.setBody(gTickSpot.saveProjectList(registeredProjects, hot));

Again a simple check to see if we pickup anything using the URL

<HOST>:<PORT>/getTickTimes/getProjectList.xsjs

Response should have the following form:

upsert "TICKSPOT"."projectForecast" ("projectID", "name", "clientID", "ownerID", "budgetHrs")
VALUES (1354119, 'A-Project, superDuper project ', 289677, 255227, 100) WITH PRIMARY KEY........

When looking into the hana table it should looks like this – (I have added yet another project with a budget of 20 hrs as you see).

HanaProjectForecast_initialLoad.png

Using Put – Update the budget on projects

As I mentioned initially the starting point for this blog was a project forecast tool. Among the things forecasted are the hours and in this section we take the forecasted hours for a project and push them back to tickspot.com as the project budget.

updateProjectBudget.xsjs

/**
 * updateProjectBudget.xsjs
 *
 * Reads project budgets from hana
 * and update the same budgets
 * in tickspot
 *
 * Created by mbruhn on 02/08/16.
 */

/* Import library with functions for tickspot*/
$.import("getTickTimes", "tickSpotFunctions");
var gTickSpot = $.getTickTimes.tickSpotFunctions;

/* PROCESSING */
var hot = true; // update db(true) or not(false)
var userAgentInfo = "ourHanaIntegrationJob (johntryout@maildrop.cc)";
var projectList = gTickSpot.readProjectList();

if (hot === true)
{
  var tokens = gTickSpot.getTickSpotToken("getTickTimes", "tickspot", userAgentInfo);
  gTickSpot.updateProjectBudget("getTickTimes", "tickspotToken", userAgentInfo, tokens, projectList);
}

$.response.setBody(JSON.stringify(projectList));

In order to test a synchronization we can change the existing budget value from 100 to 275 hrs on this one project we have. We update this using the following SQL (almost identical to what we received from the project list above – less fields though)

upsert "TICKSPOT"."projectForecast" ("projectID", "budgetHrs")
VALUES (1354119, 275) WITH PRIMARY KEY;

In the table again we see that the budget was changed accordingly on this particular project.

HanaProjectForecast_budgetChanged.png

And from tickspot we look at the projects before our update noticing the two projects and their budget figures.

ImageTickspot_twoProjects_before.png

Again we use the URL for our test purpose:

<HOST>:<PORT>/getTickTimes/updateProjectBudget.xsjs

Lets confirm the update from tickspot

ImageTickSpot_twoProjects_after.png

Finally

You made it – and are able to interface two systems. Effectively bringing data back and forth between tickspot and HANA. However the code presented is simplified and is by no means production grade – I will leave that part to you.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply