Arrr, ok, thats much better 
thanks.
Ok, so have decided on a slight different approach for posting room charges from Samba to NewBook.
There existing system they have posts in summarized departmental breakdown however other POS systems they have interrogations for post itemised (so I am told) which actually wouldnât be so bad.
However am not sure on where to start on generating a JSON post in the above format on a per order basisâŠ
Could do with some suggestions.
Expect I will need to use something like the âloopâ used for the searching of the room details JSON responce
@QMcKay am banking on your assistance yet again - sorryâŠ
No problem. The solution is in the details. Need specific details. Code, illustrations, data, data, data⊠
1 Like
Am just trying to get to grips with SQL but struggling a little (with adding a âTotal Amountâ column.
Ok, firstly I think we need;
MenuItemName, PortionName, Price, Quantity, TotalAmount, PMSDepartment, PMSPrice columns where the last two are product tags.
This will be based on a ticket ID fed into the script.
Ok, so starting with getting the required data Ive started script;
SELECT TicketId, MenuItemName, PortionName, Price, Quantity
FROM Orders
WHERE TicketId=360
But am struggling with where to put sum([Price] * [Quantity]) as [TotalAmount]
EDIT:
Sorted that; SELECT [TicketId], [MenuItemId], [MenuItemName], [PortionName], [Price], [Quantity], [CustomTags], [Quantity] * [Price] as [TotalAmount]
Expect we will need to include the join the actual products table âmenuitemsâ to be able to pull the PMSDepartemnt relative to the Products
The aim is to achieve a JSON formatted post where each order line has an array;
{
description: â{TICKET ID}: {QUANTITY} x {PRODUCT NAME}â,
gl_account_code: â{PMSDEPARTMENT}â,
amount: â{TOTAL AMOUNT}â,
tax_free: 0
}
Excuse the tags/code, is just a mockup.
This would obviously go into the charges array;
@QMcKay AM GETTING THERE 
Have managed to join the orders and menu items tables by menuitem id
SELECT TicketId, MenuItemId, MenuItemName, PortionName, Price, Quantity, GroupCode
FROM Orders o JOIN MenuItems m on m.Id = o.MenuItemId
WHERE TicketId=360
However its not group code I need, I need to pull the product tag for PMSDepartment which looks a bit trickierâŠ
How can I pull PMS_Department value out of the array inn that column?
Can you see where I am going with this?
Have managed to sus out only showing non Normal portions 
using google;
Still struggling to find a solution to the CustomTag valuesâŠ
Yes, tricky. You are using JScript to fire the SQL? Probably⊠and that is good, because guess what? You have a JSON.parse() function⊠use it.
You are going to have multi-column, multi-row data returned from sql.Query, something like this:
var r = new Array(n);
r = sql.Query(qry).Delimit('~').All;
So you need to loop through the records, and with each of those loops, you need to loop through the columns. Each time you get to the column containing the JSON data, you parse it.
Look at this code. This returns information from the last 10 Workperiods, and each row has 5 columns. So we have an outer loop from 0-9 (10 iterations for the rows in var r) and for each of those, we use the split function to break apart the delimited column data (into var w).
function recent(n) {
var nl = '<linebreak/>';
var workperiods = '';
n = typeof n !== 'undefined' ? n : 10;
if (n=='') {
n=10;
}
var qry = "SELECT TOP "+n+" [Id],left(convert(varchar(25),[StartDate],126),19),left(convert(varchar(25),[EndDate],126),19),[StartDescription],[EndDescription] FROM [WorkPeriods] ORDER BY [Id] DESC";
var r = new Array(n);
r = sql.Query(qry).Delimit(' ').All; // r is Array containing all rows
var w = new Array(5); // w is an array we will use to store column data
var tb = new Array(2);
for (var a=0; a<n; a++) { // main loop, by default this goes from 0 to 9 (10 iterations)
if (typeof r[a] !== 'undefined') { // check to see if row has data (exists)
w = r[a].split(' '); // split the column data for the row into array w
w[0] = pad(w[0],'left',5,'0'); // WPID
tb = w[1].split('T'); // WP Start DateTime split into array tb
tb[1] = pad(tb[1],'left',8,' '); // the time portion of WP Start
te = w[2].split('T'); // WP End DateTime split into array te
te[1] = pad(te[1],'left',8,' '); // the time portion of WP End
w[3] = pad(w[3],'right',25,' '); // WP Start Description
w[4] = pad(w[4],'right',25,' '); // WP End Description
// build string that looks like the following, and append to var workperiods with newLine for each row/record
// ID StartDate StartTime ... EndDate EndTime (StartDesc) (EndDesc)
// w[0] tb[0] tb[1] ... te[0] te[1] (w[3]) (w[4])
workperiods += '<color Yellow>' + w[0] + '</color> ' + tb[0] + ' <color Green>' + tb[1] + '</color> ... ' + te[0] + ' <color Red>' + te[1] + '</color> (<color Green>' + w[3] + '</color>) (<color Red>' + w[4] + '</color>)' + nl;
}
}
return workperiods;
}
In a picture âŠ
1 Like
Crikey, ok, origionally was going to transfer the PMS_Department Product Tag to an order state on order added for the sumary method but that is same issue presumably as states are stored in arrays also right?
Would it be easier, as all products will have a PMS_Department to have the PMS_Department the first tag and strip the value out rather than have to do all that?
Yes, they are stored as JSON data.
1 Like
Ok, while im trying to absorb that code above am struggling, figured it would be easier to assemble the charge description into a new column in the query but am struggling;
[TicketId] ': ' [Quantity] ' x ' [MenuItemName] ' ' [PortionName] AS [Description]
or
[TicketId] + ': ' + [Quantity] + ' x ' + [MenuItemName] + ' ' + [PortionName] AS [Description]
Think the : is causing first issue.
Secondly the + makes it think am trying to add them I think which makes sense.
Donât try to put columns together using SQL for this scenario. Just pull the rows as-is in JScript, and delimit columns using something you know is not used in the record data. You can combine column data later in JScript, because it doesnât care about datatype as much as SQL does.
r = sql.Query(qry).Delimit('~').All;
As you loop through your records, you will use something like:
for (var a=0; a<=10; a++) {
c = r[a].split('~'); // now you have column data in c[0], c[1], c[2] ...
// when you know the column contains JSON data ...
cjson = JSON.parse(c[3]);
}
And for your aggregates (Total Amount), run that in a separate query. It can be done in SQL all at once using nested queries and/or unions, but then your total row data wonât match order-line data, which makes things more complicated on the JS side.
2 Likes
Would you take out the ânormalâ portions in sql or jscript?
At the minute have;
CASE WHEN [PortionName] = 'Normal' THEN '' ELSE [PortionName] END AS [Portion],
On the subject of the product tags/custom tags column is there a particular reason for only listing fields with values?
Just seems to me would make more sense to have all tag names listed and have no value? That way you ywouldnt need to loop to find the tag name and value as the array index would be similar for all productsâ> tags.
However suppose this is partly due to the {} tags not looking for a particular array number but a tag nameâŠ
@QMcKay not sure if you will be proud or not but have kind of sorted turning a order into a charge JSON 
function RoomPostTicket(OrderId) {
var unspecified_post_pmsdepartment = 'Sundries';
var pms_glaccount_tagname = 'PMS_Department';
var order_ticket = sql.Query('SELECT [TicketId] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_quantity = sql.Query('SELECT [Quantity] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_quantity_rounded = +(Math.round(order_quantity + "e+2") + "e-2");
var order_name = sql.Query('SELECT [MenuItemName] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_DBportion = sql.Query('SELECT [PortionName] FROM [Orders] WHERE [Id]='+OrderId).First;
if(order_DBportion == 'Normal'){var order_portion = '';}else{var order_portion = order_DBportion;}
var order_price = sql.Query('SELECT [Price] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_total = order_quantity * order_price;
var order_tag_json_raw = sql.Query('SELECT m.CustomTags FROM Orders o JOIN MenuItems m on m.Id = o.MenuItemId WHERE o.Id='+OrderId).First;
var order_tag_json = JSON.parse(order_tag_json_raw);
for(var i = 0; i<order_tag_json.length;i++)
{
if (order_tag_json[i].TN == pms_glaccount_tagname) /*<===search arays for site_name for room of the loop */
{
var tag_array_number = i; /*<===set room roomsiteindex with array number */
break;
}
}
if (tag_array_number >= 0)
{
var pms_glaccount = order_tag_json[tag_array_number].TV;
}else{
var pms_glaccount = unspecified_post_pmsdepartment;
}
var post_desctiption = 'Ticket '+order_ticket+' - '+order_quantity_rounded+' x '+order_name+' '+order_portion;
var post_pmsdepartment = pms_glaccount;
var post_amount = order_total.toFixed(2);
var post_taxfree = '0';
var post_json_data = new Object();
post_json_data.description = post_desctiption;
post_json_data.gl_account_code = post_pmsdepartment;
post_json_data.amount = post_amount;
post_json_data.tax_free = post_taxfree;
var post_json = JSON.stringify(post_json_data);
return post_json;
}
I couldnât get my head round delimiting the sql columns so thought Id at least try an alternative approach.
Anyway, this script will return the JSON formatted âCHARGEâ section for any order by order Id.
My thought is I will now use SQL to return a list of order numbers for a given ticket (the ticket to be posted) and feed them into this on a loop.
My main worry is how to take the loop responses and feed them in a âlistâ into the actual API post.
So with;
var order_list = sql.Query('SELECT [Id] FROM [Orders] WHERE [TicketId]='+PostTicketId).Delimit(',').All;
I will get a list of all order ids for the ticket in question.
1 Like
Am on a role tonight 
Pretty much there on a script to post a whole ticket on order line levelâŠ
function RoomPostCharges(PostTicketId)
{
var url = 'https://testau.newbookpms.com/rest/pos_sale';
var username = '**********';
var password = '************';
var apikey = '****************';
var test_account_id = 14098;
var generated_when = 'now';
var order_list = sql.Query('SELECT [Id] FROM [Orders] WHERE [TicketId]='+PostTicketId).Delimit(',').All;
var orderid_qty = sql.Query('SELECT count([Id]) as [CT] FROM [Orders] WHERE [TicketId]='+PostTicketId).Delimit(',').First;
var charge_list = '';
for (n = 0; n < orderid_qty ; n++)
{
var OrderId = order_list[n];
var unspecified_post_pmsdepartment = 5720;
var pms_glaccount_tagname = 'PMSDepartment';
var order_ticket = sql.Query('SELECT [TicketId] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_quantity = sql.Query('SELECT [Quantity] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_quantity_rounded = +(Math.round(order_quantity + "e+2") + "e-2");
var order_name = sql.Query('SELECT [MenuItemName] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_DBportion = sql.Query('SELECT [PortionName] FROM [Orders] WHERE [Id]='+OrderId).First;
if(order_DBportion == 'Normal'){var order_portion = '';}else{var order_portion = order_DBportion;}
var order_price = sql.Query('SELECT [Price] FROM [Orders] WHERE [Id]='+OrderId).First;
var order_total = order_quantity * order_price;
var order_tag_json_raw = sql.Query('SELECT m.CustomTags FROM Orders o JOIN MenuItems m on m.Id = o.MenuItemId WHERE o.Id='+OrderId).First;
var order_tag_json = JSON.parse(order_tag_json_raw);
for(var i = 0; i<order_tag_json.length;i++)
{
if (order_tag_json[i].TN == pms_glaccount_tagname)
{
var tag_array_number = i;
break;
}
}
if (tag_array_number >= 0)
{ var pms_glaccount = order_tag_json[tag_array_number].TV;
}else{
var pms_glaccount = unspecified_post_pmsdepartment;
}
var post_desctiption = 'Ticket '+order_ticket+' - '+order_quantity_rounded+' x '+order_name+' '+order_portion;
var post_pmsdepartment = pms_glaccount;
var post_amount = order_total.toFixed(2);
var post_taxfree = '0';
var post_json_data = new Object();
post_json_data.description = post_desctiption;
post_json_data.gl_account_code = post_pmsdepartment;
post_json_data.amount = post_amount;
post_json_data.tax_free = post_taxfree;
var post_json = JSON.stringify(post_json_data);
if (n < orderid_qty-1){charge_list += post_json + ','}else{charge_list += post_json}
}
/*--- POST DETAILS JSON DATA---*/
var sales_json = '{\"account_id\":' + test_account_id + ',"generated_when\":\"' + generated_when + '\",\"charges\":[' + charge_list + ']}';
/*--- JSON REQUEST BUILDING---*/
var pos_sale_json = '{\"api_key\":\"' + apikey + '\",\"sales\":[' + sales_json + ']}';
/*--- JSON POST & RESPONCE---*/
var response = web.PostJson(url,pos_sale_json,username,password);
var responseObject = JSON.parse(response);
return responseObject;
}
Iâm sure you will point out that there are allot of individual SQL queries which can be done is a single one. I will at some point have a look to simplify this, I was struggling to diliminate and use the values of a multi colum/row responce. However I am perticularly proud of getting to grips with the âforâ loop function especially taking it from a loop query to a loop construct for the charge_list variable and adding the final if to add the Json comma between the loops but not on the last one
1 Like
Think I have managed to simplify the script to at least to 2 SQL queries outside the loop to get OrderId Count and OrderId List and a single SQL query per order line by delimiting in turn each order row then converting to array to pull the needed column values.;
function RoomPostCharges(PostTicketId)
{
var url = 'https://testau.newbookpms.com/rest/pos_sale';
var username = '*********';
var password = '****************';
var apikey = '***************';
var test_account_id = 14098;
var generated_when = 'now';
var unspecified_post_pmsdepartment = 5720;
var pms_glaccount_tagname = 'PMSDepartment';
var order_list = sql.Query('SELECT [Id] FROM [Orders] WHERE [TicketId]='+PostTicketId).Delimit(',').All;
var orderid_qty = sql.Query('SELECT count([Id]) as [CT] FROM [Orders] WHERE [TicketId]='+PostTicketId).Delimit(',').First;
var charge_list = '';
for (n = 0; n < orderid_qty ; n++)
{
var OrderId = order_list[n];
var order_row_string = sql.Query('SELECT o.[Id], [TicketId], [MenuItemName], [PortionName], [Price], [Quantity], [CustomTags] FROM [Orders] o JOIN [MenuItems] m on m.[Id] = o.[MenuItemId] WHERE o.[Id]='+OrderId).Delimit('~').First;
var order_row_array = order_row_string.split('~');
var order_ticket = order_row_array[1];
var order_quantity = order_row_array[5];
var order_quantity_rounded = +(Math.round(order_quantity + "e+2") + "e-2");
var order_name = order_row_array[2];
var order_DBportion = order_row_array[3];
if(order_DBportion == 'Normal'){var order_portion = '';}else{var order_portion = order_DBportion;}
var order_price = order_row_array[4];
var order_total = order_quantity * order_price;
var order_tag_json_raw = order_row_array[6];
var order_tag_json = JSON.parse(order_tag_json_raw);
for(var i = 0; i<order_tag_json.length;i++)
{
if (order_tag_json[i].TN == pms_glaccount_tagname)
{
var tag_array_number = i;
break;
}
}
if (tag_array_number >= 0)
{ var pms_glaccount = order_tag_json[tag_array_number].TV;
}else{
var pms_glaccount = unspecified_post_pmsdepartment;
}
var post_desctiption = 'Ticket '+order_ticket+' - '+order_quantity_rounded+' x '+order_name+' '+order_portion;
var post_pmsdepartment = pms_glaccount;
var post_amount = order_total.toFixed(2);
var post_taxfree = '0';
var post_json_data = new Object();
post_json_data.description = post_desctiption;
post_json_data.gl_account_code = post_pmsdepartment;
post_json_data.amount = post_amount;
post_json_data.tax_free = post_taxfree;
var post_json = JSON.stringify(post_json_data);
if (n < orderid_qty-1){charge_list += post_json + ','}else{charge_list += post_json}
}
/*--- POST DETAILS JSON DATA---*/
var sales_json = '{\"account_id\":' + test_account_id + ',"generated_when\":\"' + generated_when + '\",\"charges\":[' + charge_list + ']}';
/*--- JSON REQUEST BUILDING---*/
var pos_sale_json = '{\"api_key\":\"' + apikey + '\",\"sales\":[' + sales_json + ']}';
/*--- JSON POST & RESPONCE---*/
var response = web.PostJson(url,pos_sale_json,username,password);
/*var responseObject = JSON.parse(response);*/
return sales_json;
}
1 Like
Now youâre cookinâ with gas. Good work. 
Since I am getting to grips with for loops and following recent discussions about validation/constraining product tags I might look to setup an additional loop to cross reference the PMSDepartment agains a list of available gl_accounts given that they have a REST request for gl_account list.
I was originally planning to have a manually started script to run a check on all product PMSDepartment tag values although probably will do that as the script for both would be repetitively similar, if there isnt a impact of script performance I might set a trigger to âdownloadâ a list of gl_accounts to a program setting routinely (probably only once a day or every 12 hours (with manual refresh option - or better yet on program start)) so the list is readilly available locally to save waiting for API response in each loop although a single request at script start shouldnât have too bigger impact.
Anyway someone seems to have screwed up their developer api instance so is getting a whipe/refresh at the moment.
Next task is to work out the payment processor to ensure the ticket is only marked as âposted to roomâ if their is a positive response.
I intend to impenitent a 3 attempt setup, as its cloud based internet connectivity is a vulnerability so plan to try and configure an ask quest to retry twice and if 3rd attempt fails to process differently and print a different âroom post receiptâ which has a bold - THIS TICKET NEEDS TO BE MANUALLY POSTED.
Then next project will be to sort reporting the âover the counter salesâ as a bulk post like sales: WET = ÂŁ1200, DRY =ÂŁ1500, payments: CASH = ÂŁ850, CARD = ÂŁ1850.
The sales and payment will be automatically balanced on PMS so long as the totals are the same.
This post would obviously need to NOT include the âRoom Post Salesâ.
@QMcKay am in need of some advice for working out the bulk sales post for ânon room postsâ.
What I need to do is post sales and payments in two parts.
First think which is easy is to exclude tickets with payment type of Room Post.
This is my SQL at the minute;
SELECT o.[TicketID], o.[MenuItemName], m.[GroupCode], m.[CustomTags], p.[Name]
FROM [Orders] o JOIN [Payments] p ON p.[TicketId] = o.[TicketId] JOIN [MenuItems] m ON o.[MenuItemId] = m.[Id]
WHERE 1=1 AND p.[Name] <> âRoom Postâ
It gives me;
I need the custom tags as they contain the PMS account to charge sale to.
I have no idea how I can efficiently group/total the different Departmental tags.
Ideally it would be good to be able to âListâ the PMSDepartment tag values in order to loop through them however think this is going to be a quite âtaxingâ loop.