SambaPOS API Integration with NewBook PMS/Booking System

List of unique custom tags of tag name = PMSDepartment.

@emre, the SQL works - it runs fine. It is just the JScript that continues to throw errors.

That error doesn’t say much, but the assumption is that the JScript is not being parsed properly.


EDIT: The followingworks, after save Logout/Login, etc:

@@dep

declare @tagName varchar(30) = 'HH'
declare @tagNameLen int = len(@tagName)
declare @validx int = @tagNameLen+len('","TV":"')

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

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

dep

function blah() {
  var r = sql.Exec("@@dep");
  return r[0];
}

@emre, It would be very nice if we could pass parameters into the @@SQL so that we could do this:

EDIT: apparently we can do this already. This is awesome!

JS

function blah() {
  var stmt="@@dep:D1"; // stored SQL with parameter
  var r = sql.Exec(stmt);
  return r[0]+"\r\n"+r[1];
}

@@dep

--declare @tagName varchar(30) = 'HH'
declare @tagName varchar(30) = '@1' -- retrieve passed paramter
1 Like

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