SambaPOS API Integration with NewBook PMS/Booking System

QMcKay · September 11, 2015, 4:06pm

function getEntities(entityType) {
  var entitylist = '';
  var qry = "";
  var entityCount = 0;
  
  qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"')";
  entityCount = sql.Query(qry).First;

  qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"') ORDER BY [Name]";
  var entities = sql.Query(qry).Delimit(',').All;

  for (var n=0; n<entityCount; n++) {
    entitylist += entities[n] + "\r";
  }
  return entitylist;
}

The thing to take away from this is that you will have entityCount to build your for-loop and an array called entities[x] containing all of the Entity Names. You won’t actually use the entitylist return value - it is just a long string containing a list.

P.S. @emre, the forum syntax highlighting doesn’t like these types of scripts for some reason and messes up the colors

JTRTech · September 11, 2015, 4:08pm

Your are AWESOME

JTRTech · September 11, 2015, 5:06pm

OK, so thanks again for that code, although predictably not sure how to take it on from that array.

As it stands my script looks like this;

function roomdataupdate()
{
/*--- VARABLES -------------------------------------------------------------------------------------------------------*/ 
 var url = 'https://testau.newbookpms.com/rest/bookings_inhouse_list';
 var username           	= 'sambapos';
 var password           	= 'xxxxxxxxxxxxxxxxxxxxxxx';
 var apikey             	= "xxxxxxxxxxxxxxxxxxxxxx";
 var entityField_status		= 'Guest Status';
 var entityField_fullname	= 'Guest Name';
 var entityField_arrive		= 'Arrival Date';
 var entityField_depart		= 'Departure Date';
 var entityField_pax		= 'No. Guests';
 var newbookSitenameref		= 'NewBook Site Name';
 var entityField_accountid	= 'NewBook Booking AccountNo';
 var entityAccountState		= 'BookingAccount'
 var entityChargable		= 'Chargable';
 var entityNonChargable		= 'NonChargable';
 var roomEntityType			= 'Rooms';

/*--- JSON REQUEST DATA FORATTING-------------------------------------------------------------------------------------*/ 
 var requestdata = new Object();
 requestdata.api_key = apikey;
 var requestjson = JSON.stringify(requestdata);

/*--- JSON POST & RESPONCE--------------------------------------------------------------------------------------------*/ 
 var response = web.PostJson(url,requestjson,username,password);
 var responseObject = JSON.parse(response);

/*=== ENTITY NAME COUNT & ARRAY=============================================================================================================================*/
  var entitylist = '';
  var qry = "";
  var entityCount = 0;
  
  qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+roomEntityType+"')";
  entityCount = sql.Query(qry).First;

  qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+roomEntityType+"') ORDER BY [Name]";
  var entities = sql.Query(qry).Delimit(',').All;

  for (var n=0; n<entityCount; n++) {
    entitylist += entities[n] + "\r";
  }
/*===================> ENTITY LOOP OUTPUT ROOMS NAMES AS var roomname = 
*/

/*--- SEARCH JSON ARRAYS FOR SITE_NAME--------------------------------------------------------------------------------*/
 var roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();
 for(var i = 0;i < responseObject.length;i++) {
 if (responseObject[i].site_name == roomnameofsite) {
    var roomsiteindex = i;

/*==================> IF OPENED - IF responseObject[roomsiteindex].booking_status == 'Arrived'
											- if true BOOKING LOOKUP VALUES & ENTITY VALUES INHOUSE UPDATE
											- if false ENTITY VALUES CHECKOUT UPDATE													
*/    
    
    break;
    }
   }

/*---BOOKING VALUES LOOKUP--------------------------------------------------------------------------------------------*/ 
 var result_status 			= responseObject[roomsiteindex].booking_status;
 var result_firstname		= responseObject[roomsiteindex].guests[0].firstname;
 var result_lastname		= responseObject[roomsiteindex].guests[0].lastname;
 var created_fullname 		= result_firstname+' '+result_lastname;
 var result_arrive 			= responseObject[roomsiteindex].booking_arrival;
 var result_depart 			= responseObject[roomsiteindex].booking_departure;
 var result_pax 			= responseObject[roomsiteindex].booking_adults;
 var result_accountid 		= responseObject[roomsiteindex].account_id;

/*---ENTITY VALUES INHOUSE UPDATE ------------------------------------------------------------------------------------*/ 
 api.Entity(roomname).Data(entityField_status).Update(result_status);
 api.Entity(roomname).Data(entityField_fullname).Update(created_fullname);
 api.Entity(roomname).Data(entityField_arrive).Update(result_arrive);
 api.Entity(roomname).Data(entityField_depart).Update(result_depart);
 api.Entity(roomname).Data(entityField_pax).Update(result_pax);
 api.Entity(roomname).Data(entityField_accountid).Update(result_accountid);
 api.Entity(roomname).State(entityAccountState).Update(entityChargable)
 
/*---ENTITY VALUES CHECKOUT UPDATE -----------------------------------------------------------------------------------*/ 
 api.Entity(roomname).Data(entityField_status).Update('');
 api.Entity(roomname).Data(entityField_fullname).Update('');
 api.Entity(roomname).Data(entityField_arrive).Update('');
 api.Entity(roomname).Data(entityField_depart).Update('');
 api.Entity(roomname).Data(entityField_pax).Update('');
 api.Entity(roomname).Data(entityField_accountid).Update('');
 api.Entity(roomname).State(entityAccountState).Update(entityNonChargable)

/*==================> IF CLOSED
/*==================> LOOP CLOSED
*/
return entitylist
}

All the components work as expected and have commented with =====> where I believe the loop and if should open and close?

Am I looking close? What do you think?

what part of this;

entitylist += entities[n] + "\r";

causes the loop, the ending ‘;’
If so I need to being this down the the end of my loop and define the ‘var roomname’ ready to go in to the if…

Have got the entity name loop working, just working on the if.

JTRTech · September 11, 2015, 5:35pm

Am struggling to get my head round the if.

OK so instead am splitting the if into Arrived and another for NOT Arrived

My issues now is after thinking about it more that the values returned are only for rooms with checked in bokings.
So I need to move my checkout/clear fields section as is the room is not arrived the;

 if (responseObject[i].site_name == roomnameofsite) {
    var roomsiteindex = i;
    break;
    }

Will not return a roomsiteindex value as its not listed.
Am so annoyingly close…
Any suggestions?

QMcKay · September 11, 2015, 5:55pm

You don’t need that - it was illustration only to show that entities[n] is an array of room names.

You are missing the semi-colon there, which will cause an error. But you don’t need to return it either.

JTRTech · September 11, 2015, 6:04pm

Thanks, I sussed out the entity loop but am struggling with the if

I have 3 main blocks of script in the last section.

pull data as varables
update entity fields with those variables
checkout block = clear entity fields and set entity state BookingAccount=NonChargable

In theory I need to have blocks 1 and 2 run for rooms with have a site_name listed in the JSON return which is fine as have booking_status to verify as Arrived.

The problem is that Non-Arrived bookings do not have a booking_status of something else they are not listed at all in the json data.
The rest URL is ‘bookings_inhouse_list’ - bookings inhouse list
Obviously a full bookings list would end up being absolutely enormous.

Have tried flipping it arrould and doing checkout first and made the if to if(roomsiteindex==null or if(responseObject[roomsiteindex].booking_status ==null
But i get error is null or not an object.
As the one is asking if null i presume it is ‘not an object’
Any ideas how to work with that?

QMcKay · September 11, 2015, 6:39pm

Maybe this is what you want - my terminology in the IF statement is not correct, but the idea is there…

Specifically this line:

 if (result_status=='booked') {

You may also want to check if roomsiteindex is not -1 … meaning the data was was found in JSON.

var roomnameofsite = '';
var roomsiteindex = -1;

for (var n=0; n<entityCount; n++) {

  roomname = entities[n];

  roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();

  for(var i = 0;i < responseObject.length;i++) {
    if (responseObject[i].site_name == roomnameofsite) {
    roomsiteindex = i;
    break;
  } // JSON loop

  /*---BOOKING VALUES LOOKUP--------------------------------------------------------------------------------------------*/ 
  var result_status             = responseObject[roomsiteindex].booking_status;
  var result_firstname        = responseObject[roomsiteindex].guests[0].firstname;
  var result_lastname        = responseObject[roomsiteindex].guests[0].lastname;
  var created_fullname         = result_firstname+' '+result_lastname;
  var result_arrive             = responseObject[roomsiteindex].booking_arrival;
  var result_depart             = responseObject[roomsiteindex].booking_departure;
  var result_pax             = responseObject[roomsiteindex].booking_adults;
  var result_accountid         = responseObject[roomsiteindex].account_id;

  if (result_status=='booked') {

    /*---ENTITY VALUES INHOUSE UPDATE ------------------------------------------------------------------------------------*/ 
    api.Entity(roomname).Data(entityField_status).Update(result_status);
    api.Entity(roomname).Data(entityField_fullname).Update(created_fullname);
    api.Entity(roomname).Data(entityField_arrive).Update(result_arrive);
    api.Entity(roomname).Data(entityField_depart).Update(result_depart);
    api.Entity(roomname).Data(entityField_pax).Update(result_pax);
    api.Entity(roomname).Data(entityField_accountid).Update(result_accountid);
    api.Entity(roomname).State(entityAccountState).Update(entityChargable)

  } else { // not booked

    /*---ENTITY VALUES CHECKOUT UPDATE -----------------------------------------------------------------------------------*/ 
    api.Entity(roomname).Data(entityField_status).Update('');
    api.Entity(roomname).Data(entityField_fullname).Update('');
    api.Entity(roomname).Data(entityField_arrive).Update('');
    api.Entity(roomname).Data(entityField_depart).Update('');
    api.Entity(roomname).Data(entityField_pax).Update('');
    api.Entity(roomname).Data(entityField_accountid).Update('');
    api.Entity(roomname).State(entityAccountState).Update(entityNonChargable)

  }

} // Entity Loop

QMcKay · September 11, 2015, 6:52pm

This might work better… uses the Ternary operator, and checks the roomsiteindex to decide how to set the data before the update is performed …

var roomnameofsite = '';
var roomsiteindex = -1; // set this to -1 because even 0 is a valid index

for (var n=0; n<entityCount; n++) {

  roomname = entities[n];

  roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();

  roomsiteindex = -1; // reset index to invalid

  for(var i = 0;i < responseObject.length;i++) {
    if (responseObject[i].site_name == roomnameofsite) {
    roomsiteindex = i;
    break;
  } // JSON loop


  /*---BOOKING VALUES LOOKUP--------------------------------------------------------------------------------------------*/ 
   result_status    = (roomsiteindex >= 0 ? responseObject[roomsiteindex].booking_status : '');
   result_firstname = (roomsiteindex >= 0 ? responseObject[roomsiteindex].guests[0].firstname : '');
   result_lastname  = (roomsiteindex >= 0 ? responseObject[roomsiteindex].guests[0].lastname : '');
   created_fullname = (roomsiteindex >= 0 ? result_firstname+' '+result_lastname : '');
   result_arrive    = (roomsiteindex >= 0 ? responseObject[roomsiteindex].booking_arrival : '');
   result_depart    = (roomsiteindex >= 0 ?  responseObject[roomsiteindex].booking_departure : '');
   result_pax       = (roomsiteindex >= 0 ? responseObject[roomsiteindex].booking_adults;
   result_accountid = (roomsiteindex >= 0 ? responseObject[roomsiteindex].account_id : '');


    /*---ENTITY VALUES UPDATE ------------------------------------------------------------------------------------*/ 
    api.Entity(roomname).Data(entityField_status).Update(result_status);
    api.Entity(roomname).Data(entityField_fullname).Update(created_fullname);
    api.Entity(roomname).Data(entityField_arrive).Update(result_arrive);
    api.Entity(roomname).Data(entityField_depart).Update(result_depart);
    api.Entity(roomname).Data(entityField_pax).Update(result_pax);
    api.Entity(roomname).Data(entityField_accountid).Update(result_accountid);
    api.Entity(roomname).State(entityAccountState).Update(entityChargable)

} // Entity Loop

JTRTech · September 11, 2015, 7:19pm

Sweet, no errors on running the script HOWEVER if site_name not found AKA not checked in (by setting site_name to value known to not be in the response) it seems to repreat the values from the previous room/entity rather than leave/update to blank, see below;

If I set Room 1 to a not check en site_name as well as Room 2 they are left blank.

By looks of it the leaving blank part is not working, its just not updating the var from the last loop.
(roomsiteindex >= 0 ? responseObject[roomsiteindex].booking_arrival : ’ ’ )
Tried setting it to ‘space’ but the same just with space on first two rooms and repeated at in picture on room 5

emre · September 11, 2015, 7:39pm

It may not autodetect language. You can set a language like

```javascript

or

```sql

QMcKay · September 11, 2015, 8:07pm

for (var n=0; n<entityCount; n++) {

  roomname = entities[n];

  roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();

  roomsiteindex = -1; // add line here to reset index for each loop

  for(var i = 0;i < responseObject.length;i++) {
    if (responseObject[i].site_name == roomnameofsite) {
    roomsiteindex = i;
    break;
  } // JSON loop

JTRTech · September 11, 2015, 8:37pm

I had put it in in the wrong place
Thanks

JTRTech · September 11, 2015, 8:54pm

Great, a nice milestone to finish the week
Script for a full API data update for all rooms.
Thanks again to you all for your assistance, much appreciated.

For reference at this point this is current working script.
Some minor tweaks required for date formatting but on a whole looks to be 95% complete.

function roomdataupdate()
{
	/*--- VARABLES ---*/ 
	var url = 'https://testau.newbookpms.com/rest/bookings_inhouse_list';
	var username       	    	= 'sambapos';
	var password        	   	= 'xxxxxxxxxxxxxxxxxxxxxx';
	var apikey          	   	= "instances_xxxxxxxxxxxxxxxxxxxxxxxxxx";
	var entityField_status		= 'Guest Status';
	var entityField_fullname	= 'Guest Name';
	var entityField_arrive		= 'Arrival Date';
	var entityField_depart		= 'Departure Date';
	var entityField_pax			= 'No. Guests';
	var newbookSitenameref		= 'NewBook Site Name';
	var entityField_accountid	= 'NewBook Booking AccountNo';
	var entityAccountState		= 'BookingAccount'
	var entityChargable			= 'Chargable';
	var entityNonChargable		= 'NonChargable';
	var roomEntityType			= 'Rooms';
	
	/*--- JSON REQUEST DATA FORATTING---*/ 
	var requestdata = new Object();
	requestdata.api_key = apikey;
	var requestjson = JSON.stringify(requestdata);
	
	/*--- JSON POST & RESPONCE---*/ 
	var response = web.PostJson(url,requestjson,username,password);
	var responseObject = JSON.parse(response);
	
	/*=== ENTITY NAME COUNT & ARRAY===*/
	var entitylist = '';
	var qry = "";
	var entityCount = 0;
	
	qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+roomEntityType+"')";
	entityCount = sql.Query(qry).First;
	
	qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+roomEntityType+"') ORDER BY [Name]";
	var entities = sql.Query(qry).Delimit(',').All;
	
	var roomnameofsite = '';
		
	for (var n=0; n<entityCount; n++) 								/*<=== OPEN ENTITY LOOP*/
 		{
		var roomname = entities[n]									/*<===ENTITY LOOP OUTPUT ROOMS NAMES AS var roomname */
		
		/*--- SEARCH JSON ARRAYS FOR SITE_NAME---*/
		var roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();
		roomsiteindex = -1; 										/*<===reset index for each loop */
		for(var i = 0;i < responseObject.length;i++)
 			{
 			if (responseObject[i].site_name == roomnameofsite)		/*<===search arays for site_name for room of the loop */
				{
    		    var roomsiteindex = i;								/*<===set room roomsiteindex with array number */
   				break;		
    			}
   			}
 			if (roomsiteindex >= 0)									/*<===if less than 0 site not listed as 'inhouse' so any listed room is 'Arrived */
 			{ 											
			/*---BOOKING VALUES LOOKUP-------------------------------------------*/
			var result_status		= responseObject[roomsiteindex].booking_status;
			var result_firstname    = responseObject[roomsiteindex].guests[0].firstname;
			var result_lastname     = responseObject[roomsiteindex].guests[0].lastname;
			var created_fullname    = result_firstname+' '+result_lastname;
			var result_arrive       = responseObject[roomsiteindex].booking_arrival;
			var result_depart       = responseObject[roomsiteindex].booking_departure;
			var result_pax          = responseObject[roomsiteindex].booking_adults;
			var result_accountid    = responseObject[roomsiteindex].account_id;
			
			/*---ENTITY VALUES INHOUSE UPDATE ----------------------------------*/
			api.Entity(roomname).Data(entityField_status).Update(result_status);
			api.Entity(roomname).Data(entityField_fullname).Update(created_fullname);
			api.Entity(roomname).Data(entityField_arrive).Update(result_arrive);
			api.Entity(roomname).Data(entityField_depart).Update(result_depart);
			api.Entity(roomname).Data(entityField_pax).Update(result_pax);
			api.Entity(roomname).Data(entityField_accountid).Update(result_accountid);
			api.Entity(roomname).State(entityAccountState).Update(entityChargable);
			}else{													/*  <===if less than 0 site not listed as 'inhouse' */
			/*---ENTITY VALUES NO BOOKING/CHECKOUT UPDATE -----------------------*/
			api.Entity(roomname).Data(entityField_status).Update('');
			api.Entity(roomname).Data(entityField_fullname).Update('');
			api.Entity(roomname).Data(entityField_arrive).Update('');
			api.Entity(roomname).Data(entityField_depart).Update('');
			api.Entity(roomname).Data(entityField_pax).Update('');
			api.Entity(roomname).Data(entityField_accountid).Update('');
			api.Entity(roomname).State(entityAccountState).Update(entityNonChargable);
			}
			
 		}															/*<=== CLOSE ENTITY LOOP */

	return 'Script Finished';
}

Am really understanding now what you mean when saying about the power of scripts

QMcKay · September 11, 2015, 9:04pm

VERY COOL. BOOM. DONE!

Yes, Scripting in V5 is extremely powerful.

Jesse · September 11, 2015, 10:42pm

Scripting almost turns us into developers for SambaPOS. There are some amazing things you can do.

JTRTech · September 14, 2015, 3:10pm

Out of interest what would you deam a reasonable refresh rate for this script?
Obviously want to be fairly frequent but on the other end as it is an ‘overwrite’ rather than a ‘compare & update’.
Also what is a respectable API request rate? Obviously everyone will have different expectations.

I originally though every 15 mins but that’s probably a little too sparing, probably start looking at 10 minutes and see if any issues but unlikely many people will be charging items to there room inn less than 10 minutes after checking in.

Jesse · September 14, 2015, 4:43pm

I would probably set it on demand. Only when the screen is accessed to look at it maybe?

JTRTech · September 15, 2015, 5:44pm

That was my original thought however there is a noticeable delay in the script response.
Although I havn’t put into a call action yet was anticipating that this delay would be the same when used in action and be annoying in live setup.
Additionally I plan to setup the entity screen to only show/filter room entities by a ‘checked in’ state or configure a ‘greyed out’ state for ‘non-checked in’ rooms which would mean the update would want to be independent to the entity selection.

QMcKay · September 15, 2015, 6:08pm

We know the script executes lightning fast. I can run complex SQL and loops in the JScript and it returns in under a second.

The delay is certainly caused by the web functions (auth, post, download)… so you need to determine when is most important to you for calling it to get updated data, and, use it sparingly.

QMcKay · September 15, 2015, 6:12pm

I just had a thought…

What if we could schedule a background task (even in SambaPOS) that executes periodically to connect/download/query (no parsing) and save the data to a JSON/XML file on the local drive using file.WriteToFile(file,content).

Then later whenever we open a screen that needs the data, we run another/different script to read and parse the JSON/XML file… this should have almost no delay at all.