Yet another PMS Integration

V5 Question
1

Hey all.
I’ve looked through the tutorials etc an the forums and have fairly successfully set up SambaPOS to be used in our hotel restaurant, but have a question that I can’t seem to find a clear direction on.

Our PMS uses 2 simple CSV files to communicate to our current POS and I have written an SQL statement to read the in/out file to create and remove customers in SambaPOS.
the issue that I am having is with the other file that contain a list of all the room charge transactions to be posted to the PMS.

So far I have achieved this by using a Add Line to Text File action and rule that is triggered when making a payment via a ‘Room Charge’ payment type and posts the information to the csv.

A post to this file would look like this:

RoomNo, AcctNo, GL-Code, Description, Amount, Date, Time

eg:  101,103300,4-3043,Retaurant Meals [GUEST NAME] Ticket 1301,15.00,27/01/2019,13:18:38

The code I am using on the Rule to create the csv file is as follows:

{ENTITY DATA:Customers:RoomNo},{ENTITY DATA:Customers:AccountNo},4-3043,Retaurant Meals - {ENTITY DATA:Customers:FirstName} {ENTITY DATA:Customers:Surname} - Ticket {TICKET ID},{TICKET TOTAL},{TICKET DATE},[=Helper.GetUniqueString().substr(8,2)]:[=Helper.GetUniqueString().substr(10,2)]:[=Helper.GetUniqueString().substr(12,2)]

This works fine for meals, however if the ticket also contains drinks or other charges (breakfast, misc charges etc) each charge type should be on a separate line with its own GL Code for the PMS.

Action:
image

Rule:

image
image.png1113×592 23.8 KB

So, anyone have a clue on how to et it up to work with different GL codes based on ticket content?

My thought at the moment is simply to add extra Payment types and copies of the Rule for each type… then split the ticket and only pay for each item type at a time… however that is simply impractical.

2

Did you see my PMS intergration? It has facility to set an account/department for each product.
I made intergration which puts guest details on each room entity including half board packages etc.
Direct room charges
Also made it charge all non room sales from walk in/bar sales so that PMS reports represented a full complete accounting for hotel.

Csv is a dated method for this type of thing. Do they not have a proper API?

What’s the PMS?

1 Like
3

@JTRTech thanks for the quick reply.
I did have a look about at your integration, but the APIs etc that you were using seems to be quite different from what we use, so that a fair amount of it didn’t seem compatible with my requirements.

PMS system is RMS (https://www.rmscloud.com/)
they do have APIs but from what I’ve seen (and used) are only available for online bookings and availability.

I do completely agree that csv is an outdated method, however it seems to be the only method I have to work with at this time, as it is how out previous POS system communicated with the PMS.

Personally, I would rather have SambaPOS talk directly to the PMS’s database, however it is a cloud based service and that is not really an option, so I’m kinda just working with what I have at the moment.

I have an idea that I may need to use a file printer and a printer template to write to/create the csv instead, however I have not attempted that option yet.

4

They wouldn’t give direct database access.
Have you asked for full API details? NewBook originally only sent me the POS sales documentation but eventually got full docs for the complete API which included all the bookings, availability and other features.
The scripts I did could be repourposed without too much hastle for csv, just adapting the json building parts for the post to API for add line to file script. Just generate the line in a csv format
Either way, product custom tags would be the solution for your GL accounts.

5

I’ve asked… but it seems its not a popular feature request, so I doubt its a priority.

I wouldn’t expect any company to give direct database access, as that opens a whole load of potential security issues. If they did I would personally be quite wary of them. In fact the only reason i mentioned it is because a couple of versions back, the software used locally hosted databases, so direct access would have been very much possible.

I already have custom products tags for courses etc. So I will use them and have another look at your scripts… thanks.

6

My setup posted room charges there and then.
Other ‘cash’ sales were posted to a control account every half hour with a manual push button on nav screen and a trigger for 23.59 (PMS is midnight auto end of day/reporting) and also on work period close.

7

NewBook does have a file transfer option for older POS which the group had used a long time ago but it was problematic and there was no confirmation of the charge/processing.
Using the API we get a response in reply so samba knows if the charge was processed.
Room charges which are there and then shows a message giving option to try again to wait till later.
Cash sales are synced based on ticket is closed timestamp and the time of successful sync is logged so if there is issue or internet down it will just keep trying using the time from the last successful transfer.
We have had in place for almost 3 years now passing over over 200,000 tickets with very few issues.
Only issue which comes up a couple of times a year is sales at bar after midnight as didn’t code a workaround for the PMS having a midnight reporting time. They have since made it possible for our API user to backdate charges so I would make the script adjust date/time to 23.59 day before for sales say between midnight and 4am. We don’t serve late very often so it’s usually only new years Eve and the odd night the staff have some after hours drinks.

For your setup you could likely just use reporting expressions, I had to do a few things using SQL but changes in samba since v1 including the graphql API would mean for a v2 I would likely not have to do any direct SQL in scripts.

1 Like
8

@JTRTech do you offer paid setup & support option for your integration?

9

I have had a referral from the PMS for someone else who was using Samba and seen my posts on forum but nothing ever came of it.

I would be hesitant to offer a paid/supported service remotely on v1. Scripts are there for a self managed version but there are still a few querks I manage inhouse which i wouldnt be happy putting out a a paid/professional solution to remote setups.
If/when I refreshed the scripts etc for a v2 I would be more willing however just haven’t had chance to rebuild a v2 of the integration.
Being more experienced now I would definatly look to open it up to be more capable of using for other systems however this one if quite customized to newbook and is so in scattered places of code so it would not be a straight forward switch over.

1 Like
10

I’ve been playing some more, and have changed the Amount posted from using
{TICKET TOTAL} to {ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = “Food”}
this now only posts the sale amount for any Food/Beverage items using the product tags.

I have experimented with both adding a second rule for Beverages, and it seems to work fine, however now I’m having issues with the rule constraints, stopping it from firing when there is no items or value > 0, so I’m getting lines added for both Food and Beverages in the csv file when only one or the other is needed.

{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = “Food”} > 0 does not seem to be a valid constraint rule, and I’m having trouble finding documentation on the correct syntax to use in the constraints.

1 Like
11

Can you show it in use?
You generally want to use TN(’’) when using smaller or larger to ensure its treated as a number rather than a string.
{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = “Food”} returns the correct amount yer?
So try TN('{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = “Food”}') > 0
Can you post a screenshot either way so we understand the context of its use.

1 Like
12

image
image.png796×609 18.7 KB

If I use that and set to >0 then the rule does not trigger.
I added a Show message action with the content and this is what it displays (if i remove the constraint)
image

The value does work in the rule however…

{ENTITY DATA:Customers:RoomNo},{ENTITY DATA:Customers:AccountNo},4-3043,Retaurant Meals - {ENTITY DATA:Customers:FirstName} {ENTITY DATA:Customers:Surname} - Ticket# {TICKET ID},{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = "Food"},{TICKET DATE},[=Helper.GetUniqueString().substr(8,2)]:[=Helper.GetUniqueString().substr(10,2)]:[=Helper.GetUniqueString().substr(12,2)]
and 
{ENTITY DATA:Customers:RoomNo},{ENTITY DATA:Customers:AccountNo},4-3020,Retaurant Drinks - {ENTITY DATA:Customers:FirstName} {ENTITY DATA:Customers:Surname} - Ticket# {TICKET ID},{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = "Beverages"},{TICKET DATE},[=Helper.GetUniqueString().substr(8,2)]:[=Helper.GetUniqueString().substr(10,2)]:[=Helper.GetUniqueString().substr(12,2)]

gives an output of

103,103348,4-3043,Retaurant Meals - John Doe - Ticket# 1331,11.50,28/01/2019,03:19:70
103,103348,4-3020,Retaurant Drinks - John Doe - Ticket# 1331,,28/01/2019,03:19:67

$11.50 charge for meals, nothing for drinks which i correct in this case, however the drinks line should not be generated if no charge is there.

13

Sorry, if might have needed [=xxxx]
Try
[= TN(’{ORDER DETAILS:O.ExactTotal.Sum:MenuItem.Tag = “Food”}’)] > 0

14

Do you have two rules then?
You know you could do in one by having two lines to file actions and moving the food/beverage constraints onto the actions individually. Help keep things clean.

15

I tried that earlier… does not trigger
when i remove the constraint and try that in the Show message action, I don’t get a dialog box at all.
===> Totally Confused :exploding_head:<===

16

Hmmm.
You know I dont think that report expression is really ideal for that use as the event if not order specific.

Try something like these;

Drinks:|£{TICKET ORDER TOTAL EXP:(ODI=True) AND (OS.NewBook GLA=2101)}
Food:|£{TICKET ORDER TOTAL EXP:(ODI=True) AND (OS.NewBook GLA=3101)}

These are two lines of my print template which show the wet/dry split.
Your case you would change OS.Newbook GLA part to your menu item tag.
ODI is my method to exclude voids etc.

17

Also you may want to adapt that.
Looking at your rule - answer me this;
What would happen if the user put in a payment for £10 cash and then the remaining balance as room post?
On my system this would result in the whole ticket being counted as a room post and the £10 cash would also be processed as a payment on the room account.
I dont think it would in your case. Im pretty sure you would get the whole ticket total charged to the room as a payable amount.
Easiest solution is to restrict payment for room charges to the whole ticket.

18

Hmm… your correct - just tested and confirmed.

okay… not sure how to go about that, but definitely something to look into.

so… something like this?

{TICKET ORDER TOTAL EXP:(OS.Food)}

Still trying to wrap my head around the syntax… although I’m sure the fact it’s after 4am isn’t helping either lol

19

No, OS is order state.
I transfer all of my custom product tags into order states on adding as have all sorts of other automation and found it easier at the time to have everything as states. Mainly things like course as I allow these to be changed for start/main etc grouping on kitchen print but thats irrelevant hear.
Im not sure of expression for tag off top of my head but id start by trying;
{TICKET ORDER TOTAL EXP:MenuItem.Tag='Food'}
As I did though I would recommend keeping the ODI part as is 99% likly relivent to your setup to. Without it void items will also be counted.

{TICKET ORDER TOTAL EXP:(ODI=True) AND MenuItem.Tag='Food'}

20

Hi…
Sorry about the delay in responding - I’ve had a busy couple of weeks with other projects.
I will test and update asap.

Thanks again for your help.