Skip to Content

Previous post https://blogs.sap.com/2017/10/25/using-a-third-party-library-with-sapui5-application-sap-hana-cloud-development-scenario-part-1

Remember the project is structured like this

data Package content :

Create schema SCALENDAR.hdbschema file

schema_name="SCALENDAR";

Add calendars.hdbdd file to create data model for the project

namespace Calendar.data;

@Schema: 'SCALENDAR' 

context calendars 
{ 
    type CString    : String(5); 
    type SString    : String(40); 
    type LString    : String(250); 
    type SDate      : UTCTimestamp; 
    
    type tt_error 
    { 
        HTTP_STATUS_CODE: Integer; 
        ERROR_MESSAGE: String(100); 
        DETAIL: String(200); 
    }; 
        
    type tt_event
    { 
        ID: SString; 
        TITLE: SString; 
        START_DATE: SString; 
        END_DATE: SString; 
        COLOR: SString; 
        STATUS: SString;
        DESCRIPTION: LString; 
        YEAR: Integer; 
        MONTH: Integer; 
        DAY: Integer;         
        OWNERID: SString;
        CUST_COSTCENTER: SString; 
	    SREP_COSTCENTER: SString; 	 
    };

    type tt_request
    { 
        ID: SString; 
        TITLE: SString; 
        START_DATE: SString; 
        END_DATE: SString; 
        COLOR: SString; 
        STATUS: SString;
        DESCRIPTION: LString; 
        YEAR: Integer; 
        MONTH: Integer; 
        DAY: Integer;         
        OWNERID: SString;
        CUST_COSTCENTER: SString; 
        CUST_EMAIL: SString; 
        CUST_LNAME: SString; 
	    CUST_FNAME: SString;   
	    SREP_COSTCENTER: SString; 	 
        SREP_EMAIL: SString; 
        SREP_LNAME: SString; 
	    SREP_FNAME: SString; 	   
    };
    
    @Catalog.tableType : #COLUMN 
    Entity Event 
    {  
        key ID: SString; 
            TITLE: SString; 
            START_DATE: SString; 
            END_DATE: SString; 
            COLOR: SString; 
            STATUS: SString;
            DESCRIPTION: LString; 
            YEAR: Integer; 
            MONTH: Integer; 
            DAY: Integer;         
            OWNERID: SString;
            CUST_COSTCENTER: SString; 
    	    SREP_COSTCENTER: SString; 	 
    };
    
    @Catalog.tableType : #COLUMN 
    Entity Request 
    { 
        key ID: SString; 
        TITLE: SString; 
        START_DATE: SString; 
        END_DATE: SString; 
        COLOR: SString; 
        STATUS: SString;
        DESCRIPTION: LString; 
        YEAR: Integer; 
        MONTH: Integer; 
        DAY: Integer;         
        OWNERID: SString;
        CUST_COSTCENTER: SString; 
        CUST_EMAIL: SString; 
        CUST_LNAME: SString; 
	    CUST_FNAME: SString;   
	    SREP_COSTCENTER: SString; 	 
        SREP_EMAIL: SString; 
        SREP_LNAME: SString; 
	    SREP_FNAME: SString;
    };
    
    @Catalog.tableType : #COLUMN 
    Entity Customer 
    { 
        key CUST_COSTCENTER: SString; 
            CUST_EMAIL: SString; 
            CUST_LNAME: SString; 
	        CUST_FNAME: SString; 
    };    

    @Catalog.tableType : #COLUMN 
    Entity SalesRep
    { 
        key OWNERID: SString;
            SREP_EMAIL: SString; 
            SREP_LNAME: SString; 
	        SREP_FNAME: SString; 
    }; 

    @Catalog.tableType : #COLUMN 
    Entity CostCenterSAP
    { 
        key SREP_COSTCENTER: SString; 	        
    };    
}; 

procedures Package content :

Add insertRequest.hdbprocedure file

PROCEDURE "SCALENDAR"."Calendar.procedures::insertRequest" 
        ( 
    IN row SCALENDAR."Calendar.data::calendars.tt_request",
    OUT error SCALENDAR."Calendar.data::calendars.tt_error" 
        ) 
        LANGUAGE SQLSCRIPT SQL SECURITY INVOKER DEFAULT SCHEMA SCALENDAR AS BEGIN 
        /***************************** Write your procedure logic *****************************/
        declare lv_id string;
        declare lv_title string; 
        declare lv_start_date string; 
        declare lv_end_date string; 
        declare lv_color string; 
        declare lv_status string; 
        declare lv_description string;
        declare lv_year Integer; 
        declare lv_month Integer; 
        declare lv_day Integer; 
        declare lv_ownerid string;
        declare lv_cust_costcenter string;
        declare lv_cust_email string;
        declare lv_cust_lname string;
        declare lv_cust_fname string;
        declare lv_srep_costcenter string;
        declare lv_srep_email string;
        declare lv_srep_lname string;
        declare lv_srep_fname string;
        
        SELECT  ID, 
                TITLE, 
                START_DATE, 
                END_DATE, 
                COLOR, 
                STATUS, 
                DESCRIPTION, 
                YEAR, 
                MONTH,
                DAY,
                OWNERID,
                CUST_COSTCENTER,
                CUST_EMAIL,
                CUST_LNAME,
                CUST_FNAME,
                SREP_COSTCENTER,
                SREP_EMAIL,
                SREP_LNAME,
                SREP_FNAME
        INTO    lv_id, 
                lv_title, 
                lv_start_date, 
                lv_end_date, 
                lv_color, 
                lv_status, 
                lv_description, 
                lv_year, 
                lv_month, 
                lv_day, 
                lv_ownerid, 
                lv_cust_costcenter, 
                lv_cust_email,
                lv_cust_lname,
                lv_cust_fname,
                lv_srep_costcenter, 
                lv_srep_email, 
                lv_srep_lname, 
                lv_srep_fname
                FROM :row;
        
        IF :lv_id = ' ' THEN 
            error = select 400 as http_status_code, 
                    'invalid Event' as error_message, 
                    'Invalid value for Event' as detail 
                        from dummy;
        ELSEIF :lv_cust_costcenter = ' ' THEN
            error = select 400 as http_status_code, 
                    'invalid Customer Cost Center' as error_message, 
                    'Customer Cost Center is empty' as detail 
                        from dummy;
        ELSEIF :lv_srep_costcenter = ' ' THEN
            error = SELECT 400 as http_status_code, 
                    'invalid Sales Rep Cost Center' as error_message, 
                    'Sales Rep Cost Center is empty' as detail 
                        FROM dummy;
        ELSE 

            DECLARE foundCust INT := 1;
            DECLARE foundRep INT := 1;
            DECLARE foundSAPCC INT := 1;
            
            /* Check If Customer exist */    
            SELECT count(*) INTO foundCust FROM "SCALENDAR"."Calendar.data::calendars.Customer" 
                WHERE CUST_COSTCENTER = :lv_cust_costcenter;
           
            IF :foundCust = 0 THEN
                INSERT INTO "SCALENDAR"."Calendar.data::calendars.Customer"
                    VALUES (:lv_cust_costcenter, :lv_cust_email, :lv_cust_lname, :lv_cust_fname);
            END IF;
            
            /* Check If Sales Rep exist */
            SELECT count(*) INTO foundRep FROM "SCALENDAR"."Calendar.data::calendars.SalesRep" 
                WHERE OWNERID = :lv_ownerid;
           
            IF :foundRep = 0 THEN
                INSERT INTO "SCALENDAR"."Calendar.data::calendars.SalesRep"
                    VALUES (:lv_ownerid, :lv_srep_email, :lv_srep_lname, :lv_srep_fname);
            END IF;
            
            /* Check If SAP Cost Center exist */
            SELECT count(*) INTO foundSAPCC FROM "SCALENDAR"."Calendar.data::calendars.CostCenterSAP" 
                WHERE SREP_COSTCENTER = :lv_srep_costcenter;
           
            IF :foundSAPCC = 0 THEN
                INSERT INTO "SCALENDAR"."Calendar.data::calendars.CostCenterSAP"
                    VALUES (:lv_srep_costcenter);
            END IF;
            
            /* Update Event */
            UPDATE "SCALENDAR"."Calendar.data::calendars.Event" 
                SET STATUS          = lv_status,
                    TITLE           = lv_title,
                    COLOR           = lv_color,
                    DESCRIPTION     = lv_description,
                    OWNERID         = lv_ownerid,
                    CUST_COSTCENTER = lv_cust_costcenter, 
                    SREP_COSTCENTER = lv_srep_costcenter
                WHERE ID = lv_id;
        END IF;
END;

services Package content :

Add BookingEBC.xsdota file

service namespace "Calendar.services.BookingEBC" 
{ 
    "Calendar.data::calendars.Event" as "Events";
    "Calendar.data::calendars.Customer" as "Customers";
    "Calendar.data::calendars.CostCenterSAP" as "CostCenterSAPs";
    "Calendar.data::calendars.Request" as "Requests"
        create using "Calendar.procedures::insertRequest";
} 

Add eventsdata.xsjs this script will initialize table Event with data for full year

function generateUUID() 
    {
        var d = new Date().getTime();
        var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
            var r = (d + Math.random() * 16) % 16 | 0;
            d = Math.floor(d / 16);
            return (c === 'x' ? r : (r&0x3 | 0x8)).toString(16);
        });
        return uuid;
    } 

function isEmpty(str) 
    { 
        return ( !str || str.length === 0 ); 
    }
    
function getDaysInMonth(month, year) 
{
     var date = new Date(year, month, 1);
     var days = [];
     while (date.getMonth() === month) 
     {
     	var d = new Date(date);
		var datestring = (d.getFullYear() + "-" + ("0"+(d.getMonth()+1)).slice(-2) +  "-" + ("0"+d.getDate()).slice(-2));
		days.push(datestring);
        date.setDate(date.getDate() + 1);
     }
     return days;
}

function insertEvents() 
    { 
        var body = ''; 
        var EVENT_ID = ' ';
        var EVENT_TITLE = "Empty Task";
        var EVENT_DESCRIPTION = generateUUID();
        var EVENT_YEAR = $.request.parameters.get('currentyear'); 
        var EVENT_MONTH = ' '; 
        var EVENT_DAY = ' ';
        var EVENT_OWNER_ID = ' '; 
        var EVENT_CUST_CC = ' ';
        var EVENT_SREP_CC = ' '; 

        if ( isEmpty(EVENT_YEAR) === true ){ $.response.status = $.net.http.BAD_REQUEST; $.response.setBody('Year cannot be empty'); return; }

        var conn = $.hdb.getConnection();
        var queryInsert;
        var queryDelete;
        try 
        {
                // Delete Year
                queryDelete = "DELETE FROM \"SCALENDAR\".\"Calendar.data::calendars.Event\" WHERE YEAR='" + EVENT_YEAR + "'";
                conn.executeUpdate(queryDelete);
                conn.commit();
                
                // Insert new record
                queryInsert = "INSERT INTO \"SCALENDAR\".\"Calendar.data::calendars.Event\" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";          
                
                // Calculate all days for current Year
                for (var i = 0; i < 12; i++)
                {
                    EVENT_MONTH = i + 1;
                    var days = getDaysInMonth(i, EVENT_YEAR);
                    for(var j = 0; j < days.length; j++)
                    {
                        var str_date = days[j];
                        var arrDate = str_date.split('-');
                        EVENT_DAY = arrDate[2];
                        
                        // For each day generate 5 slots
                        var slot1_start = days[j] + "T" + "09:00";
                        var slot1_end = days[j] + "T" + "10:30";
                        EVENT_ID = generateUUID();
                        conn.executeUpdate(queryInsert, EVENT_ID, EVENT_TITLE, slot1_start, slot1_end, '#77DD77', 'F', EVENT_DESCRIPTION, EVENT_YEAR, EVENT_MONTH, EVENT_DAY, EVENT_OWNER_ID, EVENT_CUST_CC, EVENT_SREP_CC);
                      
                        var slot2_start = days[j] + "T" + "11:00";
                        var slot2_end = days[j] + "T"+ "12:30";   
                        EVENT_ID = generateUUID();
                        conn.executeUpdate(queryInsert, EVENT_ID, EVENT_TITLE, slot2_start, slot2_end, '#77DD77', 'F', EVENT_DESCRIPTION, EVENT_YEAR, EVENT_MONTH, EVENT_DAY, EVENT_OWNER_ID, EVENT_CUST_CC, EVENT_SREP_CC);
                    
                        var slot3_start = days[j] + "T" + "14:00";
                        var slot3_end = days[j] + "T" + "15:30";
                        EVENT_ID = generateUUID();
                        conn.executeUpdate(queryInsert, EVENT_ID, EVENT_TITLE, slot3_start, slot3_end, '#77DD77', 'F', EVENT_DESCRIPTION, EVENT_YEAR, EVENT_MONTH, EVENT_DAY, EVENT_OWNER_ID, EVENT_CUST_CC, EVENT_SREP_CC);
                    
                        var slot4_start = days[j] + "T" + "16:00";
                        var slot4_end = days[j] + "T" + "17:30";     
                        EVENT_ID = generateUUID();
                        conn.executeUpdate(queryInsert, EVENT_ID, EVENT_TITLE, slot4_start, slot4_end, '#77DD77', 'F', EVENT_DESCRIPTION, EVENT_YEAR, EVENT_MONTH, EVENT_DAY, EVENT_OWNER_ID, EVENT_CUST_CC, EVENT_SREP_CC);
                        
                        var slot5_start = days[j] + "T" + "18:00";
                        var slot5_end = days[j] + "T" + "19:30";   
                        EVENT_ID = generateUUID();
                        conn.executeUpdate(queryInsert, EVENT_ID, EVENT_TITLE, slot5_start, slot5_end, '#77DD77', 'F', EVENT_DESCRIPTION, EVENT_YEAR, EVENT_MONTH, EVENT_DAY, EVENT_OWNER_ID, EVENT_CUST_CC, EVENT_SREP_CC);
                    }
                }
                
                conn.commit();                  
                conn.close();
        } 
        catch (error) 
        {
                $.response.status = $.net.http.INTERNAL_SERVER_ERROR;
                $.response.setBody(error.message);
                return;
        }

        body = 'Submission Successful'; 
        // Success 
        $.trace.debug(body); 
        $.response.setBody(body); 
        $.response.headers.set('access-control-allow-origin','*'); 
        $.response.status = $.net.http.OK;
    }
    
var aCmd = $.request.parameters.get('cmd'); 

switch (aCmd) 
{ 
    case "InsertEvents": 
        insertEvents(); 
        break; 
    default: 
        $.response.status = $.net.http.INTERNAL_SERVER_ERROR; 
        $.response.setBody('InvalidCommand'); 
 }

No UI needed to execute this script. Select the file -> RUN

you get an error message the first time

Your url look like https://mdcpXXXXXXtrial.hanatrial.ondemand.com/Calendar/services/eventsdata.xsjs

You have to add year value for exemple 2017 as an url paramater and a commande parameter like this

https://mdcpXXXXXXtrial.hanatrial.ondemand.com/Calendar/services/eventsdata.xsjs?cmd=InsertEvents&currentyear=2017

Copy past your url on your browser and this time you will get

Back to your project to test Odata service

Select Event Entity and you will see data generated by xsjs script executed befor

Conclusion :

The back end project is now ready. In the next topic i will use an external tool to validate the Odata service created here.

Next Post => https://blogs.sap.com/2017/10/31/using-a-third-party-library-with-sapui5-application-sap-cloud-development-scenario-part-3/

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