SambaPOS API Integration with NewBook PMS/Booking System

[QMcKay]

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

JSON for SQL Server. Part 1

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

TSQL_json.zip (86.4 KB)

---

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

SELECT DISTINCT
[CustomTags]
,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:

[JTRTech]

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?

[QMcKay]

@JTRTech

One issue though, your sample script only works properly is the Department tag is the only or lest record in the json.

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

@QMcKay

The code will change whether I am looking for the D1 value or the HH value. And I also need to assume that both of the values exist, and always in the same order. These assumptions will make the code very simple. But I don’t know if I am comfortable with the assumptions; that is, I don’t know if those assumptions are guaranteed to be true all the time.

[JTRTech]

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?

[QMcKay]

@JTRTech

It seems to be the 10 in the sub string section.

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

@QMcKay

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

Change that to:

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

---

@JTRTech

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

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:

[json_Array]
[json_Item]
[json_Message]
[json_Name]
[json_Natural]
[json_Number]
[json_Object]
[json_Parse]
[json_Skip]
[json_SkipUntil]
[json_String]
[json_toJson]
[json_Value]
[json_Value2]
[json_XmlToJson]
[ufn_split]

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].

[JTRTech]

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.

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?

[QMcKay]

@JTRTech

virtually the same as setting a 5 length?

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:

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

---

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.

[JTRTech]

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;
EPOS Wet
EPOS Dry
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

[QMcKay]

@JTRTech

Unless your new method gets to the point where it will easily give just a plane list of a single tags values

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…

[JTRTech]

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

Given

@JTRTech

EPOS Wet
EPOS Dry
Room Charge Wet
Room Charge Dry

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.

[JTRTech]

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

[QMcKay]

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.

[JTRTech]

Thanks, I will try myself first

Meant to ask, whats the % doing?

[QMcKay]

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

[JTRTech]

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];

[QMcKay]

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];

[emre]

What does this SQL Script returns?

[JTRTech]

List of unique custom tags of tag name = PMSDepartment.

[QMcKay]

@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:

@@dep

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

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

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

---

dep

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

[QMcKay]

@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!

JS

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

@@dep

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