SambaPOS API Integration with NewBook PMS/Booking System

That sounds like a very clean alternative :smile:

Umm, this, I think, works …

EDIT: yes, it definitely works! This is so cool.

function blah(tagname) {
  var stmt="@@dep:"+tagname;
  var r = sql.Exec(stmt);
  return r[0]+"\r\n"+r[1]+"\r\n"+r[2];
}

I had no idea we could do that. Then I was thinking, when we create Reports using @@sql handlers, we pass parameters after the colon, and they are separated by commas. Just like this:

[Current Hours Julia:13,12, 12, 12, 20, 16, 7]
@@EmployeePunchInLatest:Employee,Julia

THats a little over my head, any chance of a short explination of what your showing there :confounded:

It makes it so clean and easy…

Dump your raw SQL into a script:

Then create your JScript, and call the above @@dep SQL handler with a parameter: No ugly concatenation of SQL statement is necessary in the JScript.

You will need to cycle through r[x] after that, but you would have needed to do that anyway.

1 Like

Nice :slight_smile: will give it a go, and apply it to the other sql queries when got head around it.

So @1 is the variable value passed in to the SQL script?
How to send multiple values? comma separated on the @@dep:?
“@@dep:”+var1+’,’+var2 ?

Send multiple values as comma-separated list, then reference them in SQL as @1, @2, @3, etc

Something like that?

Yes, like that.

function blah(tagname) {
  var maxlen = 3; // how long is the value data that we are looking for
  
  var stmt="@@dep:" + tagname +','+ maxlen; // call SQL @@dep handler while passing tagname and maxlen parameters
  var r = sql.Query(stmt).Delimit('~').All;
  
  var depList = '';
  
  var resultCount = r.Length;
  
  for (var d=0; d<resultCount; d++) {
    depList += r[d] + "\r\n";
  }
  return depList;
}

@@dep

declare @tagName varchar(30) = '@1' -- parameter is passed in via JScript
declare @tagNameLen int = len(@tagName)
declare @validx int = @tagNameLen+len('","TV":"')
declare @maxLen int = '@2' -- parameter is passed in via JScript

SELECT DISTINCT
replace(
replace(
replace(
replace(
replace(
replace(
substring([CustomTags]
, charindex(@tagName, [CustomTags])+@validx
, @maxLen)
,'"', '')
,',' ,'')
,'[', '')
,']', '')
,'{', '')
,'}', '')
AS Department

FROM [MenuItems]
WHERE 1=1
AND [CustomTags] like '%{"TN":"'+@tagName+'","TV":"%"}%'
1 Like

Thanks @QMcKay, awesome :smile:

Do I remember right that we can ‘load’ variables from one script into another?
Was hoping to be able to make a ‘master’ variables script which has urls, login details etc centrally in one place?

Yes, you can load scripts from one handler into another.

This uses the 2nd library that I quoted.

I made this routine as flexible as I could for now - it should work for Tags or States.

Sample Output:

-- set the Table to search
declare @tblName varchar(255) = '[MenuItems]'
-- set the Field to search
declare @fieldName varchar(255) = '[CustomTags]'

-- set the JSON name/value pair to return
-- for Tags, they are TN and TV
-- for States, they are SN and S
declare @jsonName varchar(255) = 'TN'
declare @jsonVal varchar(255) = 'TV'

-- if you are looking for a specific JSON element, set it here
-- for example, if you are looking for a Tag named 'HH', specify that TagName here
-- for example, if you are looking for a State named 'Status', specify that StateName here
-- if this is left blank, all name/value pairs are returned
declare @elemName varchar(255) = ''

-- no need to set this variable - it will be overwritten anyway
declare @elemVal varchar(255) = ''

-- set the name-value pair separator
-- for example, if the TagName is HH and the TagValue is 10
-- then the return value will look like this: HH=10
declare @separator varchar(10) = '='

-- set the name/value pair delimiter
-- used when more than 1 name/value pair is returned
-- for example, if there are Tags with names of D1 and HH
-- and their values are 10 and 20
-- then the return value will look like this: D1=10~HH=20
declare @delimiter varchar(10) = '~'

-- if you only want to return the Value portion 
-- of the name/value pair, then set this to 1
-- useful only if you are looking for a particular JSON Element
declare @valueOnly int = 0

-- filter for the results
-- specify 1 or all of the following
declare @firstID varchar(10) = 0
declare @lastID varchar(10) = 0
declare @exactID varchar(10) = 0
declare @itemName varchar(255) = ''

-- nothing else to set beyond here
declare @jn varchar(255)=''
declare @jv varchar(255)=''
declare @delim varchar(10) = ''
declare @sep varchar(10) = ''
declare @jsonCount int = 0
declare @rowCount int = 0
declare @r int = 0
declare @j int = 0
declare @sql nvarchar(max) = ''
declare @hasName int = 0
IF @separator='' SET @sep='=' ELSE SET @sep=@separator
IF @delimiter='' SET @delimiter='~'


--------------------------------------------------
-- we will use a #temp table to store results
--------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#jdata', 'U') IS NOT NULL
  DROP TABLE #jdata; 

create table #jdata (
[Id] int identity(1,1)
,[TableName] varchar(255) not null
,[FieldName] varchar(255) not null
,[ItemId] int null
,[ItemName] varchar(255) null
,[JSONdata] nvarchar(max) null
,[JSONcount] int null
,[JSONpairs] nvarchar(max) null
)

SELECT @hasName=count(COLUMN_NAME)
--TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as isIDENTITY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= replace(replace(@tblName,'[',''),']','')
AND COLUMN_NAME = 'Name'


--------------------------------------------------
-- dynamic creation of query, so we can use this 
-- with different tables and field names
--------------------------------------------------
SET @sql = 'INSERT INTO #jdata ([TableName], [FieldName], [ItemId], [ItemName], [JSONdata], [JSONcount], [JSONpairs])'
SET @sql = @sql + ' SELECT '
SET @sql = @sql + ''''+@tblName+''', '
SET @sql = @sql + ''''+@fieldName+''', '
SET @sql = @sql + '[Id], '
IF (@hasName=1) SET @sql = @sql + '[Name], ' ELSE SET @sql = @sql + ''''', '
SET @sql = @sql + @fieldName+', '
SET @sql = @sql + 'isnull((LEN('+@fieldName+') - LEN(REPLACE('+@fieldName+',''{'',''''))),-1), '
SET @sql = @sql + ''''''
SET @sql = @sql + ' FROM '+@tblName
SET @sql = @sql + ' WHERE 1=1'
IF (@firstID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]>=' + @firstID
IF (@lastID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]<=' + @lastID
IF (@exactID!=0) SET @sql = @sql + ' AND [Id]=' + @exactID
IF (@itemName!='' and @hasName=1) SET @sql = @sql + ' AND [Name]=' + ''''+ @itemName + ''''
SET @sql = @sql + ' AND '+@fieldName+'!=''[]'''
SET @sql = @sql + ' AND '+@fieldName+' is not null'
--IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'{"'+@jsonName+'":"' +@elemName+ '","'+@jsonVal+'":"'+'%'+'"}'+'%'''
IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'"'+@jsonName+'":"' +@elemName+ '"'+'%'''

IF (@hasName=1) SET @sql = @sql + ' ORDER BY [Name]'
print @sql
-- execute the query
EXEC sp_executesql @sql

SELECT @rowCount = max([Id]) FROM #jdata
print @rowCount

--------------------------------------------------
-- now go to work... we will store results by
-- updating column #jdata.[JSONpairs]
--------------------------------------------------
WHILE @r<=@rowCount -- iterator for rows/records
BEGIN
  SET @j=0
  SET @jsonCount=0
  SELECT @jsonCount=[JSONcount] FROM #jdata WHERE [Id]=@r
  WHILE @j<@jsonCount -- iterator for name/value pairs
  BEGIN
    IF (@j>0) SET @delim=@delimiter ELSE SET @delim=''
	-- get the name
	SELECT @jn=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonName) FROM #jdata WHERE [Id]=@r
	-- get the value
	SELECT @jv=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonVal) FROM #jdata WHERE [Id]=@r
	-- if we are looking for a specific name, and we matched it, write that name/value pair
	IF (@elemName!='' and @elemName=@jn)
	BEGIN
	IF (@valueOnly=0) UPDATE #jdata SET [JSONpairs] = @jn +@sep+ @jv WHERE [Id]=@r
	IF (@valueOnly=1) UPDATE #jdata SET [JSONpairs] = @jv WHERE [Id]=@r
	END
    -- if we are not looking for a specific name, append the name/value pair
	IF (@elemName='') UPDATE #jdata SET [JSONpairs] = [JSONpairs] + @delim + @jn +@sep+ @jv WHERE [Id]=@r
    SET @j=@j+1
  END
  SET @r=@r+1
END

SELECT * FROM #jdata

--drop table #jdata
2 Likes

@JTRTech, for your purpose of retrieving a list of PMSDepartment, you would use something like this:

-- set the Table to search
declare @tblName varchar(255) = '[MenuItems]'
-- set the Field to search
declare @fieldName varchar(255) = '[CustomTags]'

-- set the JSON name/value pair to return
-- for Tags, they are TN and TV
-- for States, they are SN and S
declare @jsonName varchar(255) = 'TN'
declare @jsonVal varchar(255) = 'TV'

-- if you are looking for a specific JSON element, set it here
-- for example, if you are looking for a Tag named 'HH', specify that TagName here
-- for example, if you are looking for a State named 'Status', specify that StateName here
-- if this is left blank, all name/value pairs are returned
declare @elemName varchar(255) = 'PMSDepartment'

-- no need to set this variable - it will be overwritten anyway
declare @elemVal varchar(255) = ''

-- set the name-value pair separator
-- for example, if the TagName is HH and the TagValue is 10
-- then the return value will look like this: HH=10
declare @separator varchar(10) = '='

-- set the name/value pair delimiter
-- used when more than 1 name/value pair is returned
-- for example, if there are Tags with names of D1 and HH
-- and their values are 10 and 20
-- then the return value will look like this: D1=10~HH=20
declare @delimiter varchar(10) = '~'

-- if you only want to return the Value portion 
-- of the name/value pair, then set this to 1
-- useful only if you are looking for a particular JSON Element
declare @valueOnly int = 1

-- filter for the results
-- specify 1 or all of the following
declare @firstID varchar(10) = 0
declare @lastID varchar(10) = 0
declare @exactID varchar(10) = 0
declare @itemName varchar(255) = ''

-- nothing else to set beyond here
declare @jn varchar(255)=''
declare @jv varchar(255)=''
declare @delim varchar(10) = ''
declare @sep varchar(10) = ''
declare @jsonCount int = 0
declare @rowCount int = 0
declare @r int = 0
declare @j int = 0
declare @sql nvarchar(max) = ''
declare @hasName int = 0
IF @separator='' SET @sep='=' ELSE SET @sep=@separator
IF @delimiter='' SET @delimiter='~'


--------------------------------------------------
-- we will use a #temp table to store results
--------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#jdata', 'U') IS NOT NULL
  DROP TABLE #jdata; 

create table #jdata (
[Id] int identity(1,1)
,[TableName] varchar(255) not null
,[FieldName] varchar(255) not null
,[ItemId] int null
,[ItemName] varchar(255) null
,[JSONdata] nvarchar(max) null
,[JSONcount] int null
,[JSONpairs] nvarchar(max) null
)

SELECT @hasName=count(COLUMN_NAME)
--TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as isIDENTITY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= replace(replace(@tblName,'[',''),']','')
AND COLUMN_NAME = 'Name'


--------------------------------------------------
-- dynamic creation of query, so we can use this 
-- with different tables and field names
--------------------------------------------------
SET @sql = 'INSERT INTO #jdata ([TableName], [FieldName], [ItemId], [ItemName], [JSONdata], [JSONcount], [JSONpairs])'
SET @sql = @sql + ' SELECT '
SET @sql = @sql + ''''+@tblName+''', '
SET @sql = @sql + ''''+@fieldName+''', '
SET @sql = @sql + '[Id], '
IF (@hasName=1) SET @sql = @sql + '[Name], ' ELSE SET @sql = @sql + ''''', '
SET @sql = @sql + @fieldName+', '
SET @sql = @sql + 'isnull((LEN('+@fieldName+') - LEN(REPLACE('+@fieldName+',''{'',''''))),-1), '
SET @sql = @sql + ''''''
SET @sql = @sql + ' FROM '+@tblName
SET @sql = @sql + ' WHERE 1=1'
IF (@firstID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]>=' + @firstID
IF (@lastID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]<=' + @lastID
IF (@exactID!=0) SET @sql = @sql + ' AND [Id]=' + @exactID
IF (@itemName!='' and @hasName=1) SET @sql = @sql + ' AND [Name]=' + ''''+ @itemName + ''''
SET @sql = @sql + ' AND '+@fieldName+'!=''[]'''
SET @sql = @sql + ' AND '+@fieldName+' is not null'
--IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'{"'+@jsonName+'":"' +@elemName+ '","'+@jsonVal+'":"'+'%'+'"}'+'%'''
IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'"'+@jsonName+'":"' +@elemName+ '"'+'%'''

IF (@hasName=1) SET @sql = @sql + ' ORDER BY [Name]'
print @sql
-- execute the query
EXEC sp_executesql @sql

SELECT @rowCount = max([Id]) FROM #jdata
print @rowCount

--------------------------------------------------
-- now go to work... we will store results by
-- updating column #jdata.[JSONpairs]
--------------------------------------------------
WHILE @r<=@rowCount -- iterator for rows/records
BEGIN
  SET @j=0
  SET @jsonCount=0
  SELECT @jsonCount=[JSONcount] FROM #jdata WHERE [Id]=@r
  WHILE @j<@jsonCount -- iterator for name/value pairs
  BEGIN
    IF (@j>0) SET @delim=@delimiter ELSE SET @delim=''
	-- get the name
	SELECT @jn=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonName) FROM #jdata WHERE [Id]=@r
	-- get the value
	SELECT @jv=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonVal) FROM #jdata WHERE [Id]=@r
	-- if we are looking for a specific name, and we matched it, write that name/value pair
	IF (@elemName!='' and @elemName=@jn)
	BEGIN
	IF (@valueOnly=0) UPDATE #jdata SET [JSONpairs] = @jn +@sep+ @jv WHERE [Id]=@r
	IF (@valueOnly=1) UPDATE #jdata SET [JSONpairs] = @jv WHERE [Id]=@r
	END
    -- if we are not looking for a specific name, append the name/value pair
	IF (@elemName='') UPDATE #jdata SET [JSONpairs] = [JSONpairs] + @delim + @jn +@sep+ @jv WHERE [Id]=@r
    SET @j=@j+1
  END
  SET @r=@r+1
END

SELECT distinct [JSONpairs] FROM #jdata ORDER BY [JSONpairs]

--drop table #jdata

1 Like

I get an error;

Msg 4121, Level 16, State 1, Line 129
Cannot find either column “dbo” or the user-defined function or aggregate “dbo.json_value”, or the name is ambiguous.

You need to install the function library…

JSON functions from Jose Segarra:

They want registration to download the library, so I’ll save you that step and attach the ZIP file containing the source code:

TSQL_json.zip (86.4 KB)

1 Like

Incredibly late night and my eyes have gone square but now have a complete setup for room data update via API including very thorough log system for updates;

Main Room Update Script;

//Load NewBook Variables
var vars 								= script.Load("NewBookVariables","initialize");																//Load NewBook Variables
vars.initialize();																																	//Instalise NewBook Variables

//Load Scripts
var NewBookRequests 					= script.Load("NewBookRequests","inhouseList");																//Load NewBook Inhouse Request
var EntityTools		 					= script.Load("EntityTools","entityCount","entityList");													//Load EntityTools Scripts
var JSONTools							= script.Load("JSONTools","searchForsite_name");															//Search JSON array for sire_name == xxx
var programSettings						= script.Load("programSettings","read","write","makenewName");												//Program settings tools.
var updateRoomData						= script.Load("updateRoomData","updateRoom","clearRoom","checkUpdatesForRoom");								//Update Room Data Scripts
var logScripts							= script.Load("logScripts","updateRoomDataScriptLog","updateRoomDataLog");									//Log Scripts

function runupdate()
{
	//Define LogDate
	var runDate							= new Date()																								//Set script run time

	//Log Clear & Set Date
	var updateScriptLog					= '';																										//Clear script log
	var updateScriptLog					= runDate+': ';																								//Prefix script log with runDate
	var updateScriptLogUD				= '';																										//Clear updated rooms log
	var updateScriptLogCLR				= '';																										//Clear cleared rooms log

	//NewBook Request & Response
	var response						= NewBookRequests.inhouseList('');																			//NewBook Inhouse List Request
	var responseObject					= JSON.parse(response);																						//JSON Responce Parse
	
	//Check JSON Response to Last Response
	if (programSettings.read(lastRoomUpdateResponce) == response)																					//Check if any changes since last run
			{
			updateScriptLog		+= 'No Updates - Script Not Run'; 																					//If request response is same as last time log script not run
			} else {																																//If else
	
			//Room Entity Count & List 
			var entityList 				= '';																										//Clear Variable 'entitylist'
			var entityCount 			= 0;																										//Reset Variable 'entityCount' to '0'
			var entityCount 			= EntityTools.entityCount(roomEntityType)																	//SQL Query responce -> entityCount Variable
			var entityList 				= EntityTools.entityList(roomEntityType)																	//SQL Query responce -> entities Variable (comma seperated list of entities)
	
			//Search request response by Room from entityList
			var roomnameofsite = '';																												//Clear Variable 'roomnameofsite'
			for (var n=0; n<entityCount; n++) 																										//<=== OPEN ENTITY LOOP (Loop cycle count based on 'entityCount' Variable
 				{
				var roomname = entityList[n]																										//Set 'roomname' Variable as Room Entity Name from 'entityList' list from above SQL query responce
				var roomnameofsite = api.Entity(roomname).Data(newbookSitenameref).Get();															//Pull Entity Data for NewBook site name field from Entity Name 'roomname' in Loop
		
				var roomsiteindex = JSONTools.searchForsite_name(responseObject,roomnameofsite)														//Search request response for sitename - returns response JSON array intex

 					if (roomsiteindex >= 0)																											//LOOP CONCLUSIOIN IF (if less than 0 site not listed as 'inhouse' so any listed room is 'Arrived'/'Inhouse')
 						{ 																															//<=== OPEN IF
 						var updatesForRoom = updateRoomData.checkUpdatesForRoom(responseObject,roomname,roomsiteindex)
 						if (! updatesForRoom)
 							{
 							} else {
	 						RoomUpdateScript	= updateRoomData.updateRoom(responseObject,roomname,roomsiteindex,runDate);								//Run update of room data for roomname
							if (! RoomUpdateScript)
								{
																																						//Do nothing is response for log undefined
								} else {
								updateScriptLogUD	+= RoomUpdateScript+', ';																				//Log room data updated
								}
							}
						}else{																														//IF TRUE CLOSE & ELSE OPEN(if less than 0 site not listed as 'inhouse')
						RoomUpdateScript	= updateRoomData.clearRoom(roomname,runDate);															//Run room data clear script
						if (! RoomUpdateScript)
							{
																																					//Do nothing is response for log undefined
							} else {
							updateScriptLogCLR	+= RoomUpdateScript+', ';																			//Log room data cleared
							}
						}																															//<=== CLOSE IF ELSE
				}																																	//<=== CLOSE ENTITY LOOP
				
			if (! updateScriptLogUD)																												//If updateScriptLogUD is none set 'None'
			{updateScriptLogUD = 'None'} else {updateScriptLogUD}
			
			if (! updateScriptLogCLR)																												//If updateScriptLogCLR is none set 'None'
			{updateScriptLogCLR = 'None'} else {updateScriptLogCLR}
			
			updateScriptLog 			+= 'Updated: '+updateScriptLogUD+' - Checked Out: '+updateScriptLogCLR;
			}
	
	programSettings.write(lastRoomUpdateResponce,response);
	logScripts.updateRoomDataScriptLog(runDate,updateScriptLog);
	return updateScriptLog;
}

Has several dependencies in other scripts;

Have setup a variables script for a single place for commonly used variables;

	//NewBook URLs
	var instanceURL 			= 'https://testau.newbookpms.com/rest/';					//NewBook Instance URL (exc request)
	
	//Request Suffixes
	var authTest				= 'auth_test';												//In the URL, we will use auth_test in place of request_action to tell NewBook what type of request is taking place.
	var bookingList				= 'bookings_list';											//Retrieve a list of bookings within the given time period
	var earnedRevenue			= 'reports_earned_revenue';									//Pull data from the Earned Revenue Report for the dates specified
	var inventoryItems			= 'reports_inventory_items';								//Pull data from the Inventory Items Report for the dates specified
	var netPromoterScore		= 'reports_net_promoter_score';								//Retrieves a list of guests and their response to the Net Promoter Score question, based off when they answered the question
	var reportOccupancy			= 'reports_occupancy';										//Retrieves a list of Categories and their occupancy for the given period
	var leadInterests			= 'lead_interests';											//Retrieves the list of available Lead Interests
	var createLead				= 'create_lead';											//Create a lead within NewBook
	var bookingOnlineQuote		= 'bookings_online_quote';									//Retrieve a list of Categories with the number of Rooms/Sites available and the calculated tariff (cost) for bookings
	var siteBooking				= 'site_inhouse_booking';									//Check a particular Room to see if a Guest is currently In-House.
	var siteList				= 'site_list';												//Pull the list of possible Sites/Rooms from NewBook.
	var siteStatus				= 'site_status';											//Retrieve/update the Status of a partilcular Site in NewBook.
	var accountList				= 'gl_account_list';										//Pull the list of possible GL Accounts from NewBook.
		
	// NewBook Auth Details
	var username       	    	= 'xxxxxxxxxx';												//NewBook Username
	var password        	   	= 'xxxxxxxxxxxxxxxxx';										//NewBook Password
	var apikey          	   	= "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";				//NewBook API Key
	
	// NewBook <-> SambaPOS Fields
	var entityField_bookingid	= 'Booking Number';											//SambaPOS Entity Fields <-> NewBook Details (booking_id)
	var entityField_status		= 'Booking Status';											//SambaPOS Entity Fields <-> NewBook Details (booking_status)
	var entityField_fullname	= 'Guest Name';												//SambaPOS Entity Fields <-> NewBook Details (guests: firstname + lastname)
	var entityField_arrive		= 'Arrival Date';											//SambaPOS Entity Fields <-> NewBook Details (booking_arrival)
	var entityField_depart		= 'Departure Date';											//SambaPOS Entity Fields <-> NewBook Details (booking_departure)
	var entityField_pax			= 'No. Guests';												//SambaPOS Entity Fields <-> NewBook Details (booking_adults)
	var newbookSitenameref		= 'NewBook Site Name';										//SambaPOS Entity Fields <-> NewBook Details (site_name)
	var entityField_accountid	= 'NewBook Client Account';									//SambaPOS Entity Fields <-> NewBook Details (account_id)
	
	// SambaPOS Options
	var entityAccountState		= 'BookingAccount'											//SambaPOS Entity State Name
	var entityChargable			= 'Chargable';												//SambaPOS Entity State Chargeable Value 
	var entityNonChargable		= 'NonChargable';											//SambaPOS Entity State Non-Chargeable Value
	var roomEntityType			= 'Rooms';													//SambaPOS Room Entity Type Name for SQL Lookup & Count
	var lastRoomUpdateResponce	= 'LastInhouseListResponse';								//Last Inhouse List Request Program Setting Name
	
	//Log Directories
	var logFolderDirectory		= 'C:/SambaPOS/Logs';										//Log folder directory include trailing
	var roomUpdateScriptLog		= 'Update Script';
	var roomUpdateDetailsFolder	= 'Room Updates';											//Folder for Room Update Details Log
	var roomChargeLogFolder		= 'Room Charges';											//Folder for Room Charges Log
	
function initialize() {
}

A request building script

//Load NewBook Variables
var vars = script.Load("NewBookVariables","initialize");																	//Load NewBook Variables
vars.initialize();																											//Instalize variables

//Autherisation test script
function authtest() {
	var url 				= instanceURL+authTest;																			//Request URL
	var requestdata 		= new Object();																					//Request JSON Object
	requestdata.api_key 	= apikey;																						//JSON Data API Key
	var requestjson 		= JSON.stringify(requestdata);																	//Stringify Request Data
	var response 			= web.PostJson(url,requestjson,username,password);												//JSON POST
 return response;
}

//Inhouse Bookings List
function inhouseList() {
	var url 				= instanceURL+bookingList;																		//Request URL
	var requestdata 		= new Object();																					//Request JSON Object
	requestdata.api_key 	= apikey;																						//JSON Data API Key
	requestdata.list_type 	= 'inhouse';																					//JSON Data List Type
	var requestjson 		= JSON.stringify(requestdata);																	//Stringify Request Data
	var response 			= web.PostJson(url,requestjson,username,password);												//JSON POST
 return response;
}

EntityTools Script

function entityCount(entityType) {
	qry = "@@EntityCount:" + entityType;																		//QRY Variable '@@EntityCount' Script: + 'roomEntityType' Variable
	var entityCount = sql.Query(qry).First;																		//SQL Query responce -> entityCount Variable
	return entityCount;																							//Return
}

function entityList(entityType) {
	qry = "@@EntityList:" + entityType;																			//QRY Variable '@@EntityList' Script: + 'roomEntityType' Variable
	var entityList = sql.Query(qry).Delimit(',').All;															//SQL Query responce -> entities Variable (comma seperated list of entities)
	return entityList;																							//Return
}

SQL for EntityTools

1

SELECT count([Name]) as [CT]
FROM [Entities]
WHERE [EntityTypeId]=	(
						SELECT [Id]
						FROM [EntityTypes]
						WHERE [Name]='@1'
						)

2

SELECT [Name] 
FROM [Entities] 
WHERE [EntityTypeId]=	(
						SELECT [Id]
						FROM [EntityTypes]
						WHERE [Name]='@1'
						)
ORDER BY [Name]

JSON search arrays script

function searchForsite_name(jsonData,searchValue) {
	arrayIndex = -1; 																												//<===reset index for each sub loop
	for(var i = 0; i<jsonData.length;i++)																							//<=== OPEN SUBLOOP - RESPONCE SEARCH FOR ENTITY NAME
			{
				if (jsonData[i].site_name == searchValue)																			//SUBLOOP IF NewBook site_name  = Entity Data NewBook Site Name
					{
			   		var arrayIndex = i;																								//<===set room roomsiteindex with array number when if is TRUE
					break;																											//<=== END SUBLOOP when if is TRUE
					}
			}
	return arrayIndex;
}

Program Setting Script

function read(settingname) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+settingname+"'";
  var r = sql.Query(qry).First;
  return r;
}

function write(settingname,settingvalue) {
  var qry = "UPDATE [ProgramSettingValues] SET [Value]='"+settingvalue+"' WHERE [Name]='"+settingname+"'";
  var r = sql.ExecSql(qry);
  return qry;
}

function makenewName(settingname) {
  var qry = "INSERT INTO [ProgramSettingValues] (Name) VALUES('"+settingname+"')";
  var r = sql.ExecSql(qry);
  return qry;
}

Update Rooms Scripts

//Load NewBook Variables
var vars 						= script.Load("NewBookVariables","initialize");														//Load NewBook Variables
vars.initialize();																													//Instalise NewBook Variables

//Load Scripts
var programSettings				= script.Load("programSettings","read","write","makenewName");										//Program settings tools.
var logScripts					= script.Load("logScripts","updateRoomDataScriptLog","updateRoomDataLog");							//Log Scripts



function updateRoom(responseObject,roomname,roomsiteindex,runDate) {
	//Script Log
	var updateRoomRunLog	= '';																									//Define and clear room update log
	var updateRoomRunLog	= runDate+'\r\n';																						//Prefix log with runDate
	var updateDataCount		= '';																									//Clear update count log

	//Define variables data
	var result_bookingid	= responseObject[roomsiteindex].booking_id+'';															//Set variables with responce values to be used
	var result_status		= responseObject[roomsiteindex].booking_status;															//Set variables with responce values to be used
	var result_firstname    = responseObject[roomsiteindex].guests[0].firstname;													//Set variables with responce values to be used
	var result_lastname     = responseObject[roomsiteindex].guests[0].lastname;														//Set variables with responce values to be used
	var created_fullname    = result_firstname+' '+result_lastname;																	//Set variables with responce values to be used
	var result_arrive       = responseObject[roomsiteindex].booking_arrival;														//Set variables with responce values to be used
	var arrive_todate		= DateTime.Parse(result_arrive);																		//Set variables with responce values to be used
	var formatted_arrive	= arrive_todate.ToString('dd/MM/yyyy');																	//Set variables with responce values to be used
	var result_depart       = responseObject[roomsiteindex].booking_departure;														//Set variables with responce values to be used
	var depart_todate		= DateTime.Parse(result_depart);																		//Set variables with responce values to be used
	var formatted_depart	= depart_todate.ToString('dd/MM/yyyy');																	//Set variables with responce values to be used
	var result_pax          = responseObject[roomsiteindex].booking_adults;															//Set variables with responce values to be used
	var result_accountid    = responseObject[roomsiteindex].account_id;																//Set variables with responce values to be used
	
	//Clear Current room data variables
	var current_bookingid	= '';																									//Clear current data fields for update check
	var current_status		= '';																									//Clear current data fields for update check
	var current_fullname	= '';																									//Clear current data fields for update check
	var current_arrive		= '';																									//Clear current data fields for update check
	var current_depart		= '';																									//Clear current data fields for update check
	var current_pax			= '';																									//Clear current data fields for update check
	var current_accountid	= '';																									//Clear current data fields for update check
	
	//Define Current room data variables
	var current_bookingid	= api.Entity(roomname).Data(entityField_bookingid).Get();												//Define current data fields for update check
	var current_status		= api.Entity(roomname).Data(entityField_status).Get();													//Define current data fields for update check
	var current_fullname	= api.Entity(roomname).Data(entityField_fullname).Get();												//Define current data fields for update check
	var current_arrive		= api.Entity(roomname).Data(entityField_arrive).Get();													//Define current data fields for update check
	var current_depart		= api.Entity(roomname).Data(entityField_depart).Get();													//Define current data fields for update check
	var current_pax			= api.Entity(roomname).Data(entityField_pax).Get();														//Define current data fields for update check
	var current_accountid	= api.Entity(roomname).Data(entityField_accountid).Get();												//Define current data fields for update check
				
	//Update entity data
	if (current_bookingid == result_bookingid)																						//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_bookingid+': NO CHANGE - NOT UPDATED \r\n';											//Log no change
		} else {
		api.Entity(roomname).Data(entityField_bookingid).Update(result_bookingid);													//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_bookingid+': '+result_bookingid+'\r\n';												//Log update value
		updateDataCount			+= '1';
		}
	if (current_status == result_status)																							//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_status+': NO CHANGE - NOT UPDATED \r\n';												//Log no change
		} else {
		api.Entity(roomname).Data(entityField_status).Update(result_status);														//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_status+': '+result_status+'\r\n';													//Log update value
		updateDataCount			+= '1';
		}
	if (current_fullname == created_fullname)																						//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_fullname+': NO CHANGE - NOT UPDATED \r\n';											//Log no change
		} else {
		api.Entity(roomname).Data(entityField_fullname).Update(created_fullname);													//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_fullname+': '+created_fullname+'\r\n';												//Log update value
		updateDataCount			+= '1';
		}
	if (current_arrive == formatted_arrive)																							//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_arrive+': NO CHANGE - NOT UPDATED \r\n';												//Log no change
		} else {
		api.Entity(roomname).Data(entityField_arrive).Update(formatted_arrive);														//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_arrive+': '+formatted_arrive+'\r\n';													//Log update value
		updateDataCount			+= '1';
		}
	if (current_depart == formatted_depart)																							//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_depart+': NO CHANGE - NOT UPDATED \r\n';												//Log no change
		} else {
		api.Entity(roomname).Data(entityField_depart).Update(formatted_depart);														//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_depart+': '+formatted_depart+'\r\n';													//Log update value
		updateDataCount			+= '1';
		}
	if (current_pax == result_pax)																									//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_pax+': NO CHANGE - NOT UPDATED \r\n';												//Log no change
		} else {
		api.Entity(roomname).Data(entityField_pax).Update(result_pax);																//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_pax+': '+result_pax+'\r\n';															//Log update value
		updateDataCount			+= '1';
		}
	if (current_accountid == result_accountid)																						//Check current data vs new data
		{
		updateRoomRunLog		+= entityField_accountid+': NO CHANGE - NOT UPDATED \r\n';											//Log no change
		} else {
		api.Entity(roomname).Data(entityField_accountid).Update(result_accountid);													//Update Entity Data Fields with responce variables data
		updateRoomRunLog		+= entityField_accountid+': '+result_accountid+'\r\n';												//Log update value
		updateDataCount			+= '1';
		}

	api.Entity(roomname).State(entityAccountState).Update(entityChargable);															//Update Entity State to Chargable State Value
	
	if (! updateDataCount)
		{
		
		} else {
		logScripts.updateRoomDataLog(runDate,updateRoomRunLog,roomname);																//Log updateRoomRunLog data
		return roomname;
		}
}

function clearRoom(roomname,runDate) {
	//Clear Current room data variables																								
	var current_bookingid	= '';																									//Clear current data fields for update check
	
	//Define Current room data variables
	var current_bookingid	= api.Entity(roomname).Data(entityField_bookingid).Get();												//Define current data fields for update check
	
	//Clear if current_bookingid aleady blank
	if (current_bookingid == '')
	{																																//Do nothing if already blank
	}else{
	api.Entity(roomname).Data(entityField_bookingid).Update('');																	//Clear Entity Data Fields for non inhouse rooms 
	api.Entity(roomname).Data(entityField_status).Update('');																		//Clear Entity Data Fields for non inhouse rooms
	api.Entity(roomname).Data(entityField_fullname).Update('');																		//Clear Entity Data Fields for non inhouse rooms
	api.Entity(roomname).Data(entityField_arrive).Update('');																		//Clear Entity Data Fields for non inhouse rooms
	api.Entity(roomname).Data(entityField_depart).Update('');																		//Clear Entity Data Fields for non inhouse rooms
	api.Entity(roomname).Data(entityField_pax).Update('');																			//Clear Entity Data Fields for non inhouse rooms
	api.Entity(roomname).Data(entityField_accountid).Update('');																	//Clear Entity Data Fields for non inhouse rooms
	
	api.Entity(roomname).State(entityAccountState).Update(entityNonChargable);														//Update Entity State to nonChargable State Value
	
	updateRoomRunLog	= 'CHECKED OUT: ALL DATA CLEARED \r\n';
	logScripts.updateRoomDataLog(runDate,updateRoomRunLog,roomname);																//Log updateRoomRunLog data
	
	return roomname;
	}
}

function checkUpdatesForRoom(responseObject,roomname,roomsiteindex) {
	//Script Log
	var checkUpdatesForRoomLog	= '';																								//Define and clear room update log
																				

	//Define variables data
	var result_bookingid	= responseObject[roomsiteindex].booking_id+'';															//Set variables with responce values to be used
	var result_status		= responseObject[roomsiteindex].booking_status;															//Set variables with responce values to be used
	var result_firstname    = responseObject[roomsiteindex].guests[0].firstname;													//Set variables with responce values to be used
	var result_lastname     = responseObject[roomsiteindex].guests[0].lastname;														//Set variables with responce values to be used
	var created_fullname    = result_firstname+' '+result_lastname;																	//Set variables with responce values to be used
	var result_arrive       = responseObject[roomsiteindex].booking_arrival;														//Set variables with responce values to be used
	var arrive_todate		= DateTime.Parse(result_arrive);																		//Set variables with responce values to be used
	var formatted_arrive	= arrive_todate.ToString('dd/MM/yyyy');																	//Set variables with responce values to be used
	var result_depart       = responseObject[roomsiteindex].booking_departure;														//Set variables with responce values to be used
	var depart_todate		= DateTime.Parse(result_depart);																		//Set variables with responce values to be used
	var formatted_depart	= depart_todate.ToString('dd/MM/yyyy');																	//Set variables with responce values to be used
	var result_pax          = responseObject[roomsiteindex].booking_adults;															//Set variables with responce values to be used
	var result_accountid    = responseObject[roomsiteindex].account_id;																//Set variables with responce values to be used
	
	//Clear Current room data variables
	var current_bookingid	= '';																									//Clear current data fields for update check
	var current_status		= '';																									//Clear current data fields for update check
	var current_fullname	= '';																									//Clear current data fields for update check
	var current_arrive		= '';																									//Clear current data fields for update check
	var current_depart		= '';																									//Clear current data fields for update check
	var current_pax			= '';																									//Clear current data fields for update check
	var current_accountid	= '';																									//Clear current data fields for update check
	
	//Define Current room data variables
	var current_bookingid	= api.Entity(roomname).Data(entityField_bookingid).Get();												//Define current data fields for update check
	var current_status		= api.Entity(roomname).Data(entityField_status).Get();													//Define current data fields for update check
	var current_fullname	= api.Entity(roomname).Data(entityField_fullname).Get();												//Define current data fields for update check
	var current_arrive		= api.Entity(roomname).Data(entityField_arrive).Get();													//Define current data fields for update check
	var current_depart		= api.Entity(roomname).Data(entityField_depart).Get();													//Define current data fields for update check
	var current_pax			= api.Entity(roomname).Data(entityField_pax).Get();														//Define current data fields for update check
	var current_accountid	= api.Entity(roomname).Data(entityField_accountid).Get();												//Define current data fields for update check
				
	//Update entity data
	if (current_bookingid == result_bookingid)																						//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_status == result_status)																							//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_fullname == created_fullname)																						//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_arrive == formatted_arrive)																							//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_depart == formatted_depart)																							//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_pax == result_pax)																									//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	if (current_accountid == result_accountid)																						//Check current data vs new data
		{
		checkUpdatesForRoomLog		+= '';																							//Log no change
		} else {
		checkUpdatesForRoomLog		+= '1';																							//Log update needed
		}
	
//	if (! checkUpdatesForRoomLog)
//		{
//		return '';
//		} else {
		return checkUpdatesForRoomLog;
//		}
}
1 Like

Log Scripts

//Load NewBook Variables
var vars 								= script.Load("NewBookVariables","initialize");																//Load NewBook Variables
vars.initialize();																																	//Instalise NewBook Variables

//Load Scripts
var logFolders							= script.Load("logFolders","directoryCheck");
var fileop								= script.Load("fileop","readfile","writefile","appendfile");

function updateRoomDataScriptLog(inputlogDate,inputlogData) {
	var dirdateYear		= inputlogDate.getYear();																				//Seperate Year from Log Date
	var dirdateMonth	= inputlogDate.getMonth()+1;																			//Seperate Month from Log Date
	var dirdateDay		= inputlogDate.getDate();																				//Seperate Day from Log Date
	logFolders.directoryCheck(inputlogDate,roomUpdateScriptLog);
	var logTime = inputlogDate.getHours()+'-'+inputlogDate.getMinutes()+'-'+inputlogDate.getSeconds()+'.'+inputlogDate.getMilliseconds();
	fileop.appendfile(logFolderDirectory+'/'+roomUpdateScriptLog+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay+'/Update Script Log '+dirdateDay+'-'+dirdateMonth+'-'+dirdateDay+'.txt',inputlogData+'\r\n');
}

function updateRoomDataLog(inputlogDate,inputlogData,inputroomName) {
	var dirdateYear		= inputlogDate.getYear();																				//Seperate Year from Log Date
	var dirdateMonth	= inputlogDate.getMonth()+1;																			//Seperate Month from Log Date
	var dirdateDay		= inputlogDate.getDate();																				//Seperate Day from Log Date
	logFolders.directoryCheck(inputlogDate,roomUpdateDetailsFolder,'Room '+inputroomName);
	var logTime = inputlogDate.getHours()+'-'+inputlogDate.getMinutes()+'-'+inputlogDate.getSeconds()+'.'+inputlogDate.getMilliseconds();
	fileop.appendfile(logFolderDirectory+'/'+roomUpdateDetailsFolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay+'/'+'Room '+inputroomName+'/'+'Update Room Data - Room '+inputroomName+' ['+logTime+'].txt',inputlogData+'\r\n');
}

//function roomCharge(inputlogDate,inputlogData,inputroomName) {
//	var inputlogDate	= new Date();
//	var dirdateYear		= inputlogDate.getYear();																				//Seperate Year from Log Date
//	var dirdateMonth	= inputlogDate.getMonth()+1;																			//Seperate Month from Log Date
//	var dirdateDay		= inputlogDate.getDate();																				//Seperate Day from Log Date
//	var logTime = inputlogDate.getHours()+'-'+inputlogDate.getMinutes()+'-'+inputlogDate.getSeconds()+'.'+inputlogDate.getMilliseconds();
//	fileop.appendfile(logFolderDirectory+'/'+roomChargeLogFolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay+'/Room Charge - '+inputroomName+' ['+logTime+'].txt',inputlogData+'\r\n');
//}

Log Folder/Directory Build Script - Particularly proud of this one.

//Load ActiveObject Tools
var fso = new ActiveXObject("Scripting.FileSystemObject");																	//Load folder creation tools

//Load NewBook Variables
var vars = script.Load("NewBookVariables","initialize");																	//Load NewBook Variables
vars.initialize();																											//Instalise NewBook Variables

function directoryCheck(inputdate,inputfolder,inputSubFolder,inputrootfolder) {
	
	//Variable definitions
	var dirDirectory	= logFolderDirectory;																				//Root directory (MUST EXIST)
	var dirdate 		= inputdate;																						//Directory date for folders
	var dirfolder 		= inputfolder;																						//Top level directory folder
	
	if (! inputSubFolder)
		{
		var dirSubFolder = '';
		} else {
		var dirSubFolder = inputSubFolder;
		}
	
	if (! inputrootfolder)
		{
		var dirRootFolder = '';
		} else {
		var dirRootFolder = inputRootFolder+'/';
		}
	
	//Seperate Day, Month & Year from log date
	var dirdateYear		= dirdate.getYear();																				//Seperate Year from Log Date
	var dirdateMonth	= dirdate.getMonth()+1;																				//Seperate Month from Log Date
	var dirdateDay		= dirdate.getDate();																				//Seperate Day from Log Date

	//Check Update Script Log folder exists if not create it		
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder))																	//Does specified logRoot folder exisit?
	 	{
																															//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder);																//Create folder if not
		}
	
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder+dirfolder))															//Does specified log folder exisit?
	 	{
																															//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder+dirfolder);														//Create folder if not
		}

	//Check log year folder exists if not create it
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear))											//Does specified log year folder exisit?
	 	{
																														 	//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear);										//Create folder if not
		}
	
	//Check log month folder exists if not create it
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth))						//Does specified log year folder exisit?
	 	{
																															//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth);					//Create folder if not
		}
	
	//Check log day folder exists if not create it
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay))			//Does specified log day folder exisit?
	 	{
																															//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay);		//Create folder if not
		}
	
	//Check log sub folder exists if not create it
	if (fso.FolderExists(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay+'/'+dirSubFolder))			//Does specified log sub folder exisit?
	 	{
																																				//Do nothing if exists	 	
	 	} else {
			fso.CreateFolder(dirDirectory+'/'+dirRootFolder+dirfolder+'/'+dirdateYear+'/'+dirdateMonth+'/'+dirdateDay+'/'+dirSubFolder);		//Create folder if not
		}
}

This one gives be a date based directory tree for detailed logs;

3 Likes

Am converted on scripts LOL

That all boils down to 12 scripts, 1 trigger, 1 action and 1 rule.

2 Likes

Yeah we can see my script support worked perfectly. :slight_smile:
- Dr.Magnusson

lol.j/k. Just wanted to reference HL2. Great implementation. While first adding JScript support I never thought such thing would be possible. I think we can shorten it a little by improving directory creation support for file helper and setting helper. I’ll also review entity related scripts to find how you’ve used count, list etc…

Sorry that my sound silly but seeing a working solution better reveals how we should improve api. Without seeing this it will be hard to understand how you’ll use them. That was what I’ve tried to explain with my broken English while suggesting you to publish your progress…

2 Likes

This would be good handy as some of the logs wouldnt work well with single line or even multi line ie the update details support.

Just a little comment RE scripts but would be nice if the colour formmatting would take // into account, was using /---- XXXX ----/ for section definitions but it looks ugly on single line comments LOL.
After all the time making scrits you want them to look good too LOL

1 Like

Don’t you just love it when a plan comes together and multiple/compound scripts work pretty much first time :smile:

Have whittled my room charge script down to this sexy little thing;

var NewBookRequests 					= script.Load("NewBookRequests","postRoomCharge");															//Load NewBook Inhouse Request
var ticketChargeArrays 					= script.Load("ticketChargeArrays","itemized");															//Load NewBook Inhouse Request

function test(ticketId,roomNumber){
	var chargeAccount					= api.Entity(roomNumber).Data(entityField_accountid).Get();
	var itemizedArray					= ticketChargeArrays.itemized(ticketId);
	var chargesResponse					= NewBookRequests.postRoomCharge(chargeAccount,itemizedArray);
return chargesResponse;
}

The power comes in the background :wink:


Expanded master requests scripts

//Load NewBook Variables
var vars = script.Load("NewBookVariables","initialize");																	//Load NewBook Variables
vars.initialize();																											//Instalize variables

//Autherisation test script
function authtest() {
	var url 				= instanceURL+authTest;																			//Request URL
	var requestdata 		= new Object();																					//Request JSON Object
	requestdata.api_key 	= apikey;																						//JSON Data API Key
	var requestjson 		= JSON.stringify(requestdata);																	//Stringify Request Data
	var response 			= web.PostJson(url,requestjson,username,password);												//JSON POST
 return response;
}

//Inhouse Bookings List
function inhouseList() {
	var url 				= instanceURL+bookingList;																		//Request URL
	var requestdata 		= new Object();																					//Request JSON Object
	requestdata.api_key 	= apikey;																						//JSON Data API Key
	requestdata.list_type 	= 'inhouse';																					//JSON Data List Type
	var requestjson 		= JSON.stringify(requestdata);																	//Stringify Request Data
	var response 			= web.PostJson(url,requestjson,username,password);												//JSON POST
 return response;
}

//Room Charge
function postRoomCharge(inputAccount,inputChargesArray) {
	var url 						= instanceURL+posSale;																				//Request URL
	
	//Sales array building
	var requestSalesArrayAccount	= '"account_id":"'+inputAccount+'"';																//Account ID from script parameter
	var requestSalesArrayGenerated	= '"generated_when":"'+chargeGenerated+'"';															//Generated when from newbook variables
	var requestSalesArrayCharges	= '"charges":['+inputChargesArray+']';																//Charges array from script parameter
	var requestSalesArray			= '{'+requestSalesArrayAccount+','+requestSalesArrayGenerated+','+requestSalesArrayCharges+'}';		//Build sales array from above variables
	
	//Request Building
	var requestAPI					= '"api_key":"'+apikey+'"';																			//API key from newbook variables
	var requestSales				= '"sales":['+requestSalesArray+']';																//Sales value with array from above
	var requestjson					= '{'+requestAPI+','+requestSales+'}';																//Request build
	
	var response 					= web.PostJson(url,requestjson,username,password);													//JSON Request POST
 return response;
}

//Sales Post
function postEposSales(inputChargesArray,inputPaymentsArray) {
	var url 						= instanceURL+posSale;																												//Request URL
	
	//Sales array building
	var requestSalesArrayAccount	= '"account_id":"'+posAccount+'"';																									//Account ID from script parameter
	var requestSalesArrayGenerated	= '"generated_when":"'+chargeGenerated+'"';																							//Generated when from newbook variables
	var requestSalesArrayCharges	= '"charges":['+inputChargesArray+']';																								//Charges array from script parameter
	var requestSalesArrayPayments	= '"payments":['+inputPaymentsArray+']';																							//Payments array from script parameter
	
	var requestSalesArray			= '{'+requestSalesArrayAccount+','+requestSalesArrayGenerated+','+requestSalesArrayCharges+','+requestSalesArrayPayments+'}';		//Build sales array from above variables
	
	//Request Building
	var requestAPI					= '"api_key":"'+apikey+'"';																											//API key from newbook variables
	var requestSales				= '"sales":['+requestSalesArray+']';																								//Sales value with array from above
	var requestjson					= '{'+requestAPI+','+requestSales+'}';																								//Request build
	
	var response 					= web.PostJson(url,requestjson,username,password);																					//JSON Request POST
 return response;
}

First of 3 charge array scripts for ‘itemized’ charge array’

//Load NewBook Variables
var vars 								= script.Load("NewBookVariables","initialize");																//Load NewBook Variables
vars.initialize();																																	//Instalise NewBook Variables

//Load Scripts
var dbTools		 						= script.Load("dbTools","ticketOrderCount","ticketOrderList","orderDetails");								//Load dbTools Scripts
var JSONTools							= script.Load("JSONTools","searchArrayNamesForValue");														//Search JSON array for data name value

function itemized(PostTicketId)
{
  var orderid_qty						= dbTools.ticketOrderCount(PostTicketId);																	//Call ticket order ID qty
  var orderid_list						= dbTools.ticketOrderList(PostTicketId);																	//Call ticket order list
  var chargesArray 						= '';
  
  for (n = 0; n < orderid_qty ; n++)
  {
    var OrderId 						= orderid_list[n];

    var order_row_string 				= dbTools.orderDetails(OrderId);
    var order_row_array 				= order_row_string.split('~');
   
    var order_ticket 					= order_row_array[1];
    var order_quantity	 				= order_row_array[5];
    var order_quantity_rounded 			= +(Math.round(order_quantity + "e+2")  + "e-2");
    var order_name 						= order_row_array[2];
    var order_DBportion 				= order_row_array[3];
    if(order_DBportion == 'Normal')
    	{
    	var order_portion 				= '';
    	} else {
    	var order_portion 				= order_DBportion;
    	}
    var order_price 					= order_row_array[4];
    var order_total 					= order_quantity * order_price;
    var order_tag_json_raw 				= order_row_array[6];
    var order_tag_json 					= JSON.parse(order_tag_json_raw);
    var tag_array_number				= JSONTools.searchArrayNamesForValue(order_tag_json,pmsDepartment)
	if (tag_array_number >= 0)
		{
		var pms_glaccount 			= order_tag_json[tag_array_number].TV;
		}else{
		var pms_glaccount 			= unspecifiedDepartment;
		}

    var post_desctiption 				= chargePrefix+' '+order_ticket+' - '+order_quantity_rounded+' x '+order_name+' '+order_portion;
    var post_pmsdepartment 				= pms_glaccount;
    var post_amount 					= order_total.toFixed(2);
    var post_taxfree 					= '1';
  
    var post_json_data 					= new Object();
    post_json_data.description 			= post_desctiption;
    post_json_data.gl_account_code 		= post_pmsdepartment;
    post_json_data.amount 				= post_amount;
    post_json_data.tax_free 			= post_taxfree;
    var post_json 						= JSON.stringify(post_json_data);
 
    if (n < orderid_qty-1)
    	{
    	chargesArray += post_json + ',';
    	} else {
    	chargesArray += post_json;
    	}
  }

	return chargesArray;
}

dbTools scripts for ticket order count, ticket order id list and order details from order ID

function ticketOrderCount(ticketId) {
	qry = "@@TicketOrderCount:" + ticketId;																		//QRY Variable '@@TicketOrderCount' Script: + 'ticketId' Variable
	var orderCount = sql.Query(qry).First;																		//SQL Query responce -> entities Variable (comma seperated list of entities)
	return orderCount;																							//Return
}

function ticketOrderList(ticketId) {
	qry = "@@TicketOrderIdList:" + ticketId;																	//QRY Variable '@@EntityList' Script: + 'roomEntityType' Variable
	var orderList = sql.Query(qry).Delimit(',').All;															//SQL Query responce -> entities Variable (comma seperated list of entities)
	return orderList;																							//Return
}

function orderDetails(orderId) {
	qry = "@@OrderDetails:" + orderId;																			//QRY Variable '@@EntityList' Script: + 'roomEntityType' Variable
	var orderDetails = sql.Query(qry).Delimit('~').First;														//SQL Query responce -> entities Variable (comma seperated list of entities)
	return orderDetails;	

Search Samba JSON (tags/states) for Array index for Tag Name=X

function searchArrayNamesForValue(jsonData,inputName) {
	arrayIndex = -1; 																												//<===reset index for each sub loop
	for(var i = 0; i<jsonData.length;i++)																							//<=== OPEN SUBLOOP - RESPONCE SEARCH FOR ENTITY NAME
			{
				if (jsonData[i].TN == inputName)																					//SUBLOOP if JSON Name = inputName
					{
			   		var arrayIndex = i;																								//<===set arrayIndex with array number when if is TRUE
					break;																											//<=== END SUBLOOP when if is TRUE
					}
			}
	return arrayIndex;
}

Just need to integrate my logs system for debug/reference.

1 Like