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 
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. 
- 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
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 
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
@JTRTech - your becoming quite handy with JScript! Green with envy 
I have followed the thread a bit (yes chalked up a few in the 2.2k views) - question, what data are you creating in SambaPOS received FROM the PMS System?
How do you mean @pauln?
The main downward data is the room/booking info including the most important is the booking a coins if on PMS which is what the room charge needs to be posted to.
Sorry, might have confused things, I was looking for the SambaPOS “API.” calls in your script such as:
api.EntityType(name).Create(entityName,[defaultStates])
Just seeing how you handled Ticket creation if Ticket Value was created in the PMS system and then needed to be sent to SambaPOS…
No ticket creation in PMS.
PMS handles accommodation and overall accounts,
Samba transfers epos sales to PMS.
PMS then has total overall sales. Samba just epos sales
Ok thanks - so just “1 way communication” at this stage SambaPOS -> PMS? So how do you align your Room Configuration with PMS? Duplicated Room Setup on both?