@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?
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 
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.
WOw you have some really cool stuff planned 
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 
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.
1 Like
@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?
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
}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!!! 
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
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.
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 
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
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.
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
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
3 Likes
Wow your todo list is taller than mine. 
3 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
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 
1 Like