SambaPOS API Integration with NewBook PMS/Booking System

Well… Sounds like a good thing though.

1 Like

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

Can you show the calling script function.

The solution to this is //–

1 Like

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…

5 Likes

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

2 Likes

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
2 Likes

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

2 Likes

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:

1 Like

Cant believe I missed that one but using tasks for API logging, much better plan than the original discussed ages ago using txt files.

Note to self;

Using tasks.

1 Like

Hmmm, another thought, ticket log would be better place, tasks for api request runs, responces logged in ticket log, sounds ideal :slight_smile:… now though, do we have script helper for ticket log? if not maybe it would be good to go in to graphql if not already.

I don’ think there is API for Ticket Log, But with Script and GraphQL, you have a lot of power over Tasks - much more than the single Add Task action that we have in Automation. GQL let’s you do just about anything with Tasks.

Both the KD and Timeclock in GQL Modules use Tasks and the power of GQL to manipulate them. It’s all done in Javascript code, which is pretty much the same as JScript in SambaPOS. All the GQL Queries and Mutations are available in Script via the gql.Exec() API command.

1 Like

Made an update to handle calculations.


var ticketDiscountIdList							= '';
	var ticketDiscountIdList							= ticketDiscountList(inputTicketId);																				//--query ticket discount total
	var ticketDiscountIdCount							= '';
	var ticketDiscountIdCount							= ticketDiscountCount(inputTicketId);
	if (ticketDiscountList) {																																				//--if discount defined
		for (d = 0; d < ticketDiscountIdCount ; d++) {
			var discountId								= ticketDiscountIdList[d];
			var discountDetailsArray					= ticketDiscountDetails(discountId).split('~');
			var discountName							= discountDetailsArray[0];
			var discountDecrease						= discountDetailsArray[1];
			var discountAmount							= discountDetailsArray[2];
			
			var discountGLAccountHashIndex				= discountName.indexOf('#');
			
			if (discountGLAccountHashIndex>-1){
				var discountGLAccountNumber				= discountName.slice(discountGLAccountHashIndex+1);
			} else {
				var discountGLAccountNumber				= discountGLAccount;
			}
			
			if (discountAmount<0){
				var discountValue							= discountAmount*-1;
			} else {
				var discountValue							= discountAmount*1;
			}
		//	return discountValue;
			var postDesctiption 						= 'Ticket: '+ticketNumberDescription+' - '+discountName;															//--built post description
			var postGLAccount	 						= discountGLAccountNumber;																							//--post gl_account code
			var postAmount 								= discountValue.toFixed(2);																							//--post amount to two d.places
			var postTaxFree 							= '0';																												//--post taxable number 0/1
			var postJsonData 							= new Object();																										//--define object for json post
			postJsonData.description 					= postDesctiption;																									//--json.description
			postJsonData.gl_account_code 				= postGLAccount;																									//--json.gl_account_code
			postJsonData.amount 						= postAmount;																										//--json.amount
			postJsonData.tax_free 						= postTaxFree;																										//--json.tax_free
			var postJson 								= JSON.stringify(postJsonData);																						//--stringify json object
		//	if(d==1){
		//		return discountDetailsArray[2];
		//	}
			if (discountDecrease==='True'){
				creditsArray							+= postJson+',';
			} else if(discountDecrease==='False'){
				chargesArray							+= postJson+',';																									//--add to credits variable
			}
		}
	}		//--end discount if

function ticketDiscountCount(inputTicketId) {																																//--Discounts on ticket by ticket id
	qry =  "SELECT COUNT([Id]) as [CT]										";
	qry	+= "FROM [Calculations]												";
	qry	+= "WHERE [TicketId] = '"+inputTicketId+"'							"	;																							//--QRY Variable + inputDate + inputPaymentType
	var discountCount = sql.Query(qry).First;																													//--SQL Query responce -> payment totals for work period exc payments
	return discountCount;																																					//--return discounts total
}	

function ticketDiscountList(inputTicketId) {																																//--Discounts on ticket by ticket id
	qry =  "SELECT [Id]														";
	qry	+= "FROM [Calculations]												";
	qry	+= "WHERE [TicketId] = '"+inputTicketId+"'							"	;																							//--QRY Variable + inputDate + inputPaymentType
	var discountList = sql.Query(qry).Delimit('~').All;																													//--SQL Query responce -> payment totals for work period exc payments
	return discountList;																																					//--return discounts total
}																																											//--end function

function ticketDiscountDetails(inputDiscountId) {																															//--Discounts on ticket by ticket id
	qry =  "SELECT [Name],[DecreaseAmount],[CalculationAmount]				";
	qry	+= "FROM [Calculations]												";
	qry	+= "WHERE [Id] = '"+inputDiscountId+"'"	;																															//--QRY Variable + inputDate + inputPaymentType
	var ticketDiscountDetails = sql.Query(qry).Delimit('~').First;																											//--SQL Query responce -> payment totals for work period exc payments
	return ticketDiscountDetails;																																			//--return discounts total
}							

Particularly like this bit;

var discountGLAccountHashIndex				= discountName.indexOf('#');
			
			if (discountGLAccountHashIndex>-1){
				var discountGLAccountNumber				= discountName.slice(discountGLAccountHashIndex+1);
			} else {
				var discountGLAccountNumber				= discountGLAccount;
			}

Which takes the number following the # on the calculation name to set which accounting department the charge/credit is created in NewBook.

So Regular Drinks Discount #2301 discount goes to GLAccount 2301 which is grouped under wet sales on 2101.
The if catches general other calculation should they get used for some reason like round and original discount.

2 Likes

Exactly what I am looking for, but I don’t even know where to start :smile:

Are you looking to use NewBook?
This all would give a near full intergration, you could get away with just posting room sales but personally prefer sending over all so there is a single point of reporting.
The current setup will have had some tweeks from scripts documented here. They also have updated API moving the request type to a value in the array with a single endpoint buy continuing old methods for now.
There could also be some improvements using the graphql calls to avoid some of the direct SQL scripts however the hotels here are still running a pre ql version of samba.

1 Like

I am looking for a solution that I can use for bookings and food sales at the same time. This post looks like it covered all that but it requires some time to grasp the flow. Is there a way we can use Samba itself without intergrating NewBook?

The other room entities topic did in house rooms but not a booking calander for future bookings.
As I have told others if there are enough rooms and plan to use channels like booking.com etc a propper PMS like newbook is the way to go.
For a little B&B or pub with rooms could get by but even our 7 room pub/inn uses newbook and booking.com.
Booking.com etc a counts for arround 40% of our bookings up over 50% sometimes.
Plus online bookings direct? Is this wanted, newbook and other PMS will have this. You can use booking.coms sytem for lower fees on direct refered bookings I guess.

The company I work for is somehow linked to another system called the booking factory.
I pestered them for an API last year and they now have one and are much cheaper than newbook or at least were. There API is pretty basic compaired to newbook but they have the key room details and charging posts available.

1 Like

I think this other tutorial you did here will help me for now since the customer doesn’t have future bookings they just want to handle walk in bookings. I am going to try and implement it.

Just checked and this integration has now processed over 1 million itemized charge lines to the PMS between the 3 sites currently installed at :slight_smile:

2 Likes

Ηello,
I would like some help…
I would like to add to each proof to update the database and the AADE Electronic Books