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;
// }
}