SQL / Script to see if a order is mapped for tax from OrderId

Any suggestions on where to begin of a script/SQL to see if a product is mapped for a tax?

Can you explain why you need this? We could explore all possibilities not just SQL.

My PMS integration requires a taxable field (0 for tax free and 1 for taxable).
While pretty much everything at the hotel will be vat-able it would be nice to ‘generalise’ the scripts should a non tax product be required.
Chocolates I think is about the only thing a but would sell which would be VAT free but you never know the future needs or others needs when I offer PMS integration to my clients.

This is the script I have for generating the ‘charges’ part of the JSON post;

function itemized(PostTicketId)
  var orderid_qty						= dbTools.ticketOrderCount(PostTicketId);																	//Call ticket order ID qty
  var orderid_list						= dbTools.ticketOrderList(PostTicketId);																	//Call ticket order list
  var chargesArray 						= '';
  for (n = 0; n < orderid_qty ; n++)
    var OrderId 						= orderid_list[n];

    var order_row_string 				= dbTools.orderDetails(OrderId);
    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);
    var tag_array_number				= JSONTools.searchArrayNamesForValue(order_tag_json,pmsDepartment)
	if (tag_array_number >= 0)
		var pms_glaccount 			= order_tag_json[tag_array_number].TV;
		var pms_glaccount 			= unspecifiedDepartment;

    var post_desctiption 				= chargePrefix+' '+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 					= '1';
    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)
    	chargesArray += post_json + ',';
    	} else {
    	chargesArray += post_json;

	return chargesArray;

Am currently pulling;

SELECT o.[Id], [TicketId], [MenuItemName], [PortionName], [Price], [Quantity], [CustomTags]
FROM [Orders] o JOIN [MenuItems] m on m.[Id] = o.[MenuItemId]
WHERE o.[Id]='@1'

In a loop for order ID however tax mapping seems to work a little differently with no mention in menuItems or Orders tables.

These will generate a charge array for a ticket which looks like this;

{"description":"Ticket 2 - 2 x 1664 Pint","gl_account_code":"10003","amount":"8.20","tax_free":"1"},
{"description":"Ticket 2 - 1 x Fosters Half","gl_account_code":"10003","amount":"1.90","tax_free":"1"},
{"description":"Ticket 2 - 2 x Fosters Pint","gl_account_code":"10003","amount":"7.60","tax_free":"1"},
{"description":"Ticket 2 - 1 x 1664 Half","gl_account_code":"10003","amount":"2.05","tax_free":"1"}

I have currently hard coded tax_free to 1 as 99.999 of the time the products in my situation atm will be vat-able.

Looking at the way the tax mapping works Im not sure where to even begin given there are multiple columns to work with if we used an SQL join…
If wanting to keep it truely globaly valid script would need to work with all columns…
Although I can only ever imagine working with Menu Item Group and Menu Item…

I can only imagine a solution by pulling lists of the mapping columns and running multiple for loops to cross reference the appropriate details.

@emre maybe you can offer some incite to how you have worked it behind the scenes to help with a possible simpler solution or would it be a big job to have a simple helper dropped in to check based on OrderId?
Understand if its not simple and if so will look at just using menu item and group but maybe you might have one of your magic solutions you usually come up with…

Un-comment 1 of the Criteria in the final two AND statements to filter.

,isnull(tt.[Name],'No Tax Mapping') as [TaxName]
,isnull(tt.[Rate],1) as [TaxRate]
FROM [MenuItems] p
left join [TaxTemplateMaps] tm on tm.[MenuItemGroupCode]=p.[GroupCode] or tm.[MenuItemId]=p.[Id]
left join [TaxTemplates] tt on tt.[Id]=tm.[TaxTemplateId]
--AND tt.[Name] is null     -- show only Unmapped products
--AND tt.[Name] is not null -- show only Mapped products
ORDER BY p.[GroupCode], p.[Name]

P.S. the reason I change a null Tax Rate to 1 instead of 0 is because a Rate of 0 would give you a 0-priced product when you do:

ProductPrice * TaxRate

But ultimately, it comes down to how you use the data returned by the query.

1 Like

my products would be inclusive pricing and the sure the PMS treats prices as gross also so no need to calculate gross price luckily.
Thanks @QMcKay, will try tomorrow but sure it will work :smile:

As I said not tried yet but looks like this will generate a list of all mapped or unmapped product correct?

Yes, it is down to the criteria used in the WHERE clause.

##List all:

##List Mapped:

##List Unmapped:

1 Like

Ok quick question qmckay before I try this later, what’s the best way to check the order Id against this list? Another for loop or what I might try first is to combine this SQL in with my order details query rather than have a loop nested in loop nested in loop although I’m sure as you have said before it is still pretty low demand.

@QMcKay sorry, you going to have loads of taged posts from tonight :frowning:
Just wanting to apologies for missing a more obvious simpler solution…
There is a Taxes column in the orders table… :flushed: sorry I missed that LOL

1 Like

Good find :wink: That makes things much more simple.

1 Like