Using a Third-party Library with SAPUI5 Application – SAP Cloud Development Scenario – Part 2
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¤tyear=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.
Be the first to leave a comment
You must be Logged on to comment or reply to a post.