SambaPOS API Integration with NewBook PMS/Booking System


#225

Arrr, ok, thats much better :slight_smile: thanks.


#226

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…


#227

No problem. The solution is in the details. Need specific details. Code, illustrations, data, data, data… :stuck_out_tongue_winking_eye:


#228

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.


#229

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


#230

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;


#231

@QMcKay AM GETTING THERE :smile:
Have managed to join the orders and menu items tables by menuitem id :smile:

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 :slight_smile: using google;


Still struggling to find a solution to the CustomTag values…


#232

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 …


#233

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?


#234

Yes, they are stored as JSON data.


#235

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.


#236

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.


#237

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


#238

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…


#239

@QMcKay not sure if you will be proud or not but have kind of sorted turning a order into a charge JSON :smile:

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.


#240

Am on a role tonight :grinning:
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 :slight_smile:


#241

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

#242

Now you’re cookin’ with gas. Good work. :thumbsup:


#243

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


#244

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