SambaPOS API Integration with NewBook PMS/Booking System


I installed the JSON functions from this other coder - Jose Segarra:

They want registration to download the library, so I’ll save you that step and attach the ZIP file containing the source code: (86.4 KB)

The library contains other useful functions which make it easier to use for our purpose. Check this out:

,dbo.json_value([CustomTags],'0.TN') as [Tag], dbo.json_value([CustomTags],'0.TV') as [Val]
,dbo.json_value([CustomTags],'1.TN') as [Tag], dbo.json_value([CustomTags],'1.TV') as [Val]
FROM [dbo].[MenuItems]
where 1=1
and [CustomTags] != '[]' and [CustomTags] is not null

A very simple query using one of the functions gives us nice output:


Thanks for the help @QMcKay but think I will stick with the previous suggestion for now.
One issue though, your sample script only works properly is the Department tag is the only or lest record in the json.
If there are others after it it leaves a trailing "},{"

It seems to be the 10 in the sub string section.
Tried myself but not quite there, is there a way to dynamically add length of the tag value as well based on the " after it in the string?


Correct. That is 1 of the assumptions that I was talking about. And that is it’s downfall…


There isnt an easy way to strip right of the first " once the parts in from of the value are stripped?

Your json way is clean but would result in stagard columns for tag names/ values wouldn’t it?


Yes, another assumption, because I don’t know the length of the data.

Change that to:

SELECT DISTINCT replace(replace(substring([CustomTags],charindex(@tagName,[CustomTags])+@validx,10),'"}]',''),'"},{"','')

Yes it could in it’s current simplicity. Need to do more processing. I was just showing proof of concept and how easy it was to use. It processes all records instead of only 1, and there isn’t a bunch extraneous code to get the same result as the first library that I tried.

There are more functions in the library as well, but I cannot find any docs on their use:


They are mostly probably just used internally by the parsing engine, but some of them can certainly be called upon along, such as [json_Value].


Thanks but isn’t that just then virtually the same as setting a 5 length? If shorter than 5 (giving the training "},{") you would then get the T or TN etc of a following tag.
Not that I dont appreciate your help. :smile:

Where would a charindex go it give a value base on the results from your last script?
Is that is defined AS Department can you then run expressions on that column?


Assumption. You said earlier that they were not always length of 5.

Do more nested replacements - one for each character that you want to get rid of:


Now you begin to see the plethora of issues that I was trying to explain. Change your SELECT line to this:

,'"', '')
,',' ,'')
,'[', '')
,']', '')
,'{', '')
,'}', '')

I am not going down this path any further. I am going to concentrate on creating a method using the library for pulling json name-value pairs, then we can filter out pairs we don’t care about.


OK, I concede :-). I think we will look to try and use this with a fixed department name for now.
Thinking about it the main split from till would be Wet and Dry anyway which would be 3, can put anything else as ‘Sun’ (sundries)

Intend to have Room Pose (wet/dry) to go to seperate accounts from EPOS (non room post wet/dry) so that we can see easily on the PMS reports the sales coming from ‘over the counter’ and 'room ‘charges’ but this can easily be achieved using PMS Department tag of Wet or Dry and the scripts prefixing the department with ‘EPOS’ and ‘Room Charge’ as the room post and ‘non room charge’ posts are seperate scripts resulting in 4 accounts used by integration of;
Room Charge Wet
Room Charge Dry

I will then look at refining this at a later date once there is integrated JSON handling functions in SQL 2016.
Unless your new method gets to the point where it will easily give just a plane list of a single tags values :smile:


It already does, sort of. I was running it in such a way as to get distinct list of values for PMSdep. Need to do a little bit more to allow for data placement (determine where the tag is located in multi-tag strings).

Working on it…


:slight_smile: ok great, you working a little over my head with that so will await to see how you get on.


I could just hard code the script but am trying to keep it dynamic so it could easily be rolled out as a configuration task for other systems.


@QMcKay am struggling to move the sql in to jscript.
Lots of ’ and " I need to escape but am struggling to remember how…

Tried \ but doesnt change anything or is it that the highlighting just doesnt take the \ into account?


First, surround all the query parts with double-quotes, instead of single-quotes. You need the single quotes within the SQL statement. This cuts down on the escaping you need to do.

Then you just need to escape the few double-quotes.

Hmm… I don’t think that will work with the declare statements in there like that either. You will need to terminate those with ;

You can paste your code here and I will fix it.


Thanks, I will try myself first :smile:

Meant to ask, whats the % doing?


Percent is a wildcard in SQL, like asterisk is in other languages.


Ok, am strugling to get straight in my head with the highlighting not taking the escape into account.;

  var PMSDepartment_List = "";
  PMSDepartment_List += "declare @tagName varchar(30) = '" + PMSDepartmentTagName + "' declare @tagNameLen int = len(@tagName) declare @validx int = @tagNameLen+len('\",\"TV\":\"')";
  PMSDepartment_List += "SELECT DISTINCT replace(substring([CustomTags],charindex(@tagName,[CustomTags])+@validx,5),'\"}]','') AS Department";
  PMSDepartment_List += "FROM [MenuItems]";
  PMSDepartment_List += "WHERE 1=1";
  PMSDepartment_List += "AND [CustomTags] like '%{\"TN\":\"'+@tagName+'\",\"TV\":\"%\"}%";

  var r = sql.Query(PMSDepartment_List).All;
  return r[0];


I cannot get it work. This is where I stopped:

  var PMSDepartment_List = "";
  PMSDepartment_List += "declare @tagName varchar(30) = '" + PMSDepartmentTagName + "'\r\n";
  PMSDepartment_List += "declare @tagNameLen int = len(@tagName)\r\n";
  PMSDepartment_List += "declare @validx int = @tagNameLen+len('\",\"TV\":\"')\r\n\r\n";

  PMSDepartment_List += "SELECT DISTINCT\r\n";
  PMSDepartment_List += " replace(\r\n";
  PMSDepartment_List += "substring([CustomTags],\r\n";
  PMSDepartment_List += "charindex(@tagName,[CustomTags])+@validx\r\n";
  PMSDepartment_List += ",10)\r\n";
  PMSDepartment_List += ",'" + '"' + "}]" + "','')\r\n";
  PMSDepartment_List += "  AS Department\r\n";

  PMSDepartment_List += " FROM [MenuItems]\r\n";
  PMSDepartment_List += " WHERE 1=1\r\n";
  PMSDepartment_List += " AND [CustomTags] like '%{\"TN\":\"'+@tagName+'\",\"TV\":\"%\"}%'";

  return PMSDepartment_List;
  var r = sql.Query(PMSDepartment_List).All;
  return r[0];


What does this SQL Script returns?


List of unique custom tags of tag name = PMSDepartment.


@emre, the SQL works - it runs fine. It is just the JScript that continues to throw errors.

That error doesn’t say much, but the assumption is that the JScript is not being parsed properly.

EDIT: The followingworks, after save Logout/Login, etc:


declare @tagName varchar(30) = 'HH'
declare @tagNameLen int = len(@tagName)
declare @validx int = @tagNameLen+len('","TV":"')

AS Department

FROM [MenuItems]
AND [CustomTags] like '%{"TN":"'+@tagName+'","TV":"%"}%'


function blah() {
  var r = sql.Exec("@@dep");
  return r[0];


@emre, It would be very nice if we could pass parameters into the @@SQL so that we could do this:

EDIT: apparently we can do this already. This is awesome!


function blah() {
  var stmt="@@dep:D1"; // stored SQL with parameter
  var r = sql.Exec(stmt);
  return r[0]+"\r\n"+r[1];


--declare @tagName varchar(30) = 'HH'
declare @tagName varchar(30) = '@1' -- retrieve passed paramter