SambaPOS API Integration with NewBook PMS/Booking System


#305

@JTRTech - your becoming quite handy with JScript! Green with envy :flushed:

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?


#306

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.


#307

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…


#308

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


#309

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?


#310

Data will go both ways but samba will request data or post charges.

I will have 2 keys/references within samba.

The room entities will have a site_id custom field which will need to be set to match the ID of the coraspondong room on PMS, samba will routinely request list of I house bookings, returned as large Json array. It will compair, breakdown and cross reference the PMS list to the room entities to update the room booking/guest details.
Most details will be for reference for staff, or dates, etc. Main one will be the PMS booking charge account which is unique to each booking. This is the account that will be used for room charges.

The post parts will be room charges and general sales at end of day.

The second key will be the gl account (equivilent of product group code in samba) on PMS, this will relate the drinks/food split on charges going over to PMS.

Room charges will go through a script to format into a Json array with descriptions and totals etc, the post will be sent with the account is on record on the room entity which will coraspondong to the current inhouse booking.

Room charges will be marked with room charge payment type so that at end of day all non room charge sales (cash&card) will be accumulated and charged to a master epos account on the PMS.

I have only looked at using these 2 requests provided by the PMS, there are about a dozen others which can be used to call and update data.

At one point I plan to use their other requests for;

There method of setting up packages (b&b or dinner bb) so I can allow samba to know the package to wither show a message or automatically change price for package items (food)

Another one is a method to call company and client accounts list and potentially create in samba. These would be used to allow ledger charges/company accounts for functions etc aswell as just room charges

I also plan to extend the above script to include more detail about the guest so a waitress can press guest info in samba and see for example where they are from, reason for travel, booking source and booking notes to make it easier to make small talk and improve customer service.

The other feature I will be running though samba now that I’m getting the hang of scripts is to use a caller ID device to allow housekeeping to call the extention the caller ID is hooked up to and samba will see the room extention, reference against entities and change a status set on samba which displays which rooms have been cleaned.
This will also be set to pass the room status update on to the PMS

In a couple of months I will be looking at setting up a new pbx phone system at the hotel also and so call logging will be the next project most likely aomehow through samba being the most powerful interim software between the two :slight_smile:

The key thing will be that samba will have all epos data however it passes this on to PMS in one way or another in such a way that general accounting/revenue reports will all be complete in PMS.
Samba would be used for more detailed reporting like product sales for stocktaker and custom reports for things like spend per head etc.


#311

WOw you have some really cool stuff planned :sunglasses: hope it goes well and I will be watching from here!
Interested in the 2 way stuff which you have not started yet as "protocols’ will need to be expanded, well planned and designed in SambaPOS.

All the best for NYE and 2016 :smiley:


#312

This is my primary integration idea. We’ll trigger stuff (rules / config scripts?) inside sambapos via devices and use JS helpers to use SambaPOS features.


#313

@QMcKay ok so am reworking the TagList we previously discussed where you suggested the SQL JSON tool.

This is what I have at the minute;

function test() {
	var tagList					= sql.Query('@@AllCustomTagsList').All;
	var tagCount				= sql.Query('@@CountAllCustomTagsList').First;
	
	var pmsTagListString		= '';

	
	for(var i = 0; i<tagCount;i++)
	
			{
				var tagListRow		= tagList[i];
				var rowJSON			= JSON.parse(tagListRow);
				var rowJSONlength	= rowJSON.length;
				
				for(var a = 0; a<rowJSONlength;a++)
				{
 					if (rowJSON[a].TN == 'PMSDepartment')
					{
					var thisTag	= rowJSON[a].TV;
	    			pmsTagListString += thisTag+'~';
   					break;		
					}
	   			}
			}
	
	var pmsTagList					= pmsTagListString.split('~');
	
	
	return test[1];
}

This gives me a split list of all PMSDepartment tag values without using the clumsy char stripping method previously used.

Am now in need of a way to reduce this list to a list of unique values…
Any suggestions?


#314

Ok, think I have worked something out but greatly appreciate a once over encase Ive missed something simple which could simplify the whole thing.

function uniqueListofTags(inputtagName) {
	var tagList					= sql.Query('@@AllCustomTagsList').All;									//All tags SQL query
	var tagCount				= sql.Query('@@CountAllCustomTagsList').First;							//All tags count
	var pmsTagListString		= '';
	
	for(var i = 0; i<tagCount;i++)																		//Loop through all rows to pull PMS department
			{
			var tagListRow		= tagList[i];															//Array rowfor this loop
			var rowJSON			= JSON.parse(tagListRow);												//JSON parse this row
			var rowJSONlength	= rowJSON.length;														//Array length ready for loop
			
			for(var a = 0; a<rowJSONlength;a++)															//Loop through row array to find PMS department
				{
 					if (rowJSON[a].TN == inputtagName)												//If array TN = poms department tag name
						{
						var thisTag	= rowJSON[a].TV;													//This rows tag value -> thisTag variable
		    				if (i == tagCount-1)														//If last row?
		    					{
		    					pmsTagListString += thisTag;											//If true dont add '~' delimiter into pmsTagListString
	   							} else {
	   							pmsTagListString += thisTag+'~';										//Otherwise add '~' delimiter into pmsTagListString
	   							}
	   					break;		
						}
	   			}
			}
	
	var pmsTagList					= pmsTagListString.split('~');										//Split pmsTagListString to array
	var sortedPmsTagList			= pmsTagList.sort();												//Sort array
	
	var listLength					= sortedPmsTagList.length;											//All tags array length
	
	var finalArray					= new Array();														//New array variable for final array
	for (var x = 0; x<listLength;x++)																	//Loop to check for duplicates
		{
		if (pmsTagList[x] == pmsTagList[x-1])															//If this value = last value
			{} else {																					//Nothing if true
			finalArray.push(pmsTagList[x]);																//If not true add value to new final array
			}
		}
	
//	var test = finalArray.toString();																	//finalArray to string for testing
//	return test																							//Test return

	return finalArray
}

#315

Am banging my head on the desk SO SO hard right now.
A large chunk of the scripting Ive done wasn’t needed…
Worst part is my original plan if I had remembered my progress cuts out half the work.
Adding order states for half of the things im referencing brings them into the orders table!!!
Order CustomTag --> Order State on Order Added saves so much work!!! :cry:

SELECT SUM([Price] * [Quantity]) as TotalAmount
FROM [Orders]
WHERE [CreatedDateTime] > '2016-01-03' AND [OrderStates] LIKE '%"S":"10003","SN":"NewBook GLA"%' AND [OrderStates] NOT LIKE '%"S":"Posted to Room","SN":"PMSPosted"%'

Simple as that!!! FFS


#316

Well… Sounds like a good thing though.


#317

Have hit a slight snag…
Am wanting to do this;

SELECT SUM([Price] * [Quantity]) as TotalAmount
FROM [Orders]
WHERE [CreatedDateTime] > '2016-01-03' AND [OrderStates] LIKE '%"S":"10003","SN":"NewBook GLA"%' AND [OrderStates] NOT LIKE '%"S":"Posted to Room","SN":"PMSPosted"%'

This is it in samba;

declare  @likeFilter varchar(40) = '@2'
declare  @notlikeFilter varchar(40) = '@3'

SELECT SUM([Price] * [Quantity]) as TotalAmount
FROM [Orders]
WHERE [CreatedDateTime] > '@1' AND [OrderStates] LIKE '%'+@likeFilter+'%' AND [OrderStates] NOT LIKE '%'+@notlikeFilter+'%'

However I cant pass '"S":"Posted to Room","SN":"PMSPosted"' from script to SQL as parameter

“PMSPosted” on its own works…
as soon as anything else is added outside the “” it fails or rather returns nothing…

@emre where am I going wrong…

Its not the , comma - well not on its own as “S”:“Posted to Room” doesn’t work either.

The colon??

Seems like it might be the colon…
Escaping the colon with \ gives me a response but now looks like it is passing the \ into the SQL as that parameter (which is a NOT LIKE) is not being removed from the response


Colons [:] in Script SQL Query
#318

Can you show the calling script function.


#319

The solution to this is //–


#320

What a busy week, been fairly low profile this week with the install and switch of the PMS and EPOS for the hotel. Am truly shattered and think its time for a holiday :slightly_smiling:

Its been almost 5 months since I started this topic and my system is now installed and been live for 5 days now.

It has been a huge revilement that bar 2 very minor tweaks in the booking data for a scenario which didn’t come to light until in a production environment all seems to be working great.

My sales script and room charge setup so far has all worked as expected :slightly_smiling:

Thanks again to those who helped my on this. Especially Q and Kendash, your help in pretty much assisting me in Jscript and SQL coding 101 in the beginning. :slightly_smiling:

I have allowed a 2 week ‘cooling’ period to ensure system doesnt have any glitches in more rare scenarios but pretty confident I constructed to cover any potential problems I could imagine.

Once the dust has settled at the hotel following the full switch over of booking system and till system I will sort a run through of all I have achieved on this project not just the integration.
Although I dont think a tutorial on the integration itself would be the best solution as quite specific to my setup I do plan to try and work out a configuration task for it as the Hotel directer has shown interest in rolling out the system to the other hotels.

There will be a couple more projects over the next few months that might interest some people, although not directly EPOS related will be using power of samba as a bridge to the PMS but the ideas and methods may prove helpful for others so will be documenting these on the forum for anyone whos interested.

As previously mentioned I will sort a selection of pictures, screenshots and workflow videos which might be good for the samba portfolio…


#321

Second most viewed topic on the last 12 months :slight_smile:


#322

Ideas for future v2.0 Intergration improvements and new functions.

  • improved logging
  • improved over counter sales sync not reliant on headless server/single eod run
  • more flexible package setup
  • backdate late night (after midnight tickets/sales to 23:59 for time based reporting to work period reporting parity
  • improved posting method to ensure PMS account reconciliation correctly interprets bulk transfers of over counter sales
  • gl_account validation script for products to ensure all products have a valid newbook department code
  • improved management/director account posting using alternative to direct account number entry
  • improved discount processing
  • improved ‘transferred’ tagging and reporting of over counter sales
  • investigate system for non booking default client account for company or group billing
  • improved guest signing bill/template
  • variable option for different posting methods (itemised, departmentalised or ticket total - ticket total would require gl_account split via some form of balancing charge/credit system)
  • configuration task/easier method of customisation for installing in new places
  • improved room entity screen display state formatting for group bookings
  • investigate spend allowance functionality
  • investigate booking/guest note functionality for EPOS related notes like allergies etc which would be a guest note and recurring for future stays.
  • cash back and card tips options for transferring payment sources amounts correctly
  • method for marking cash/card paid tickets using the ‘room post’ department gl_account variation for improved spend per head of residents reporting
  • function for date processing not requiring system date/time format to be in SQL format

Possible new features;

  • guest info display inc first/last night notification for grating/small talk
  • phone link using caller id for making rooms clean by housekeeping
  • availability checker and prices for at bar accommodation queries
  • creation of reservation system integrating a system for automatically reserving tables for package/half board bookings
  • gift voucher utilisation (no direct API for vouchers so alternative method investigation checking account credit balance and requiring balance payment at till without manual pc interaction)
  • investigate possible idea for checkin/checkout at till
  • petty cash processing system
  • possible alternative solution for kitchen allowance on package items through credit/charge transfer from accommodation to dry
  • option for work period end to print a report including newbook room payments and other figures to allow easy end of day reconciliation for where cash and card processing is shared with epos drawer/pdq machines from till where cashing up is done by closing down bar staff/manager
  • sync for companies and company staff into entities similar to rooms system (possible solution to manager/director account charging without account number entry) givin api offers requests to implement
  • additional small talk info like last stay date - maybe finding previous room charges to ‘predict’ regular drink from last visit maybe
  • query dev team on tagging charged with booking number when charged to non default booking client account for easier invoice processing for companies

#323

Wow your todo list is taller than mine. :wink:


#324

Well done JTR, you and the team have truly revolutionised samba to what was originally a hospitality pos to something completely in a different department which combines hospitality with accommodation!
What an achievement. Kudos to all of you.

For every achievement that each user achieves, it brings me even more joy and confidence that using and marketing samba was really the right path to take for me :slight_smile:

It inspires me to chip in as much as I can even though half of you guys are miles ahead haha… I’ll get there one day :stuck_out_tongue: