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
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 … 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 
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?