Pulling Ticket Information into a Script

Have hunted the forum but struggling to find as example of how to pull ticket information into a script.
To date (allong with greatly received assistance) have referenced sample scripts on the forum for examples of use but cant find what im looking for.

Kendash implied ticket template tags could be used in scripts however am struggling to work out how.

Additionally I understand I need to use SQL to pull report type details of sales etc for a work period, so am not sure whether just to use SQL for both ticket posts and cash/card sales totals.

At the minute my plan is to mark orders with a PMSDepartment state to allow a wet dry split on script information posted to PMS API.

Could really do with some stating blocks to work from :smile:

:frowning: anyone got a little time to get me going on this?
Cannot find any examples on the forum?

Can I call information from a ticket in a script or do I have to push the info through in the call (brackets)?

You prefer to be able to call within the script as and be able to perform additional calculations within the script before posting the json request.

If you are just trying to read Ticket information, you can do that with SQL, given the TicketId.

If you want to manipulate a Ticket, I don’t think there is a method to do so in the API (yet), but you could also conceivably write updates/inserts using SQL as well. The Punch Editor makes use of this mechanism.

Can you illustrate a more concrete example of what you want to accomplish?

I wasn’t looking to manipulate the ticket, just to do some checks on order states and whether to include them in the script.
Firstly the drinks/food split but potentially also to ignore certain items if they are included in the tarif package which I will define some other way, probably using states.

First part is to return order totals based on state PMSDepartment, which at the minute are either Wet or Dry, may end up with a couple more but principle is the same.

These totals will then be posted to the REST in a surmised department style with ticket summarized in to Drinks and Food lines.
Need to check with the GM if that’s his preferred way to show on room bills but that’s the way their current system records on room bills.

Am hoping for a sniped of code which I can adapt as needed.

This will give all information about a Ticket and it’s Orders, including Entities, Ticket/Order Tags and Ticket/Order States. In the case of Tags and States, the data is stored in JSON format, so you will need to parse it out.

SELECT *
FROM [dbo].[Orders] o
JOIN [Tickets] t on t.[Id] = o.[TicketId]
LEFT JOIN [TicketEntities] te on te.[Ticket_Id] = t.[Id]
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [Accounts] a on a.[Id] = e.[AccountId]

It pulls data from 5 tables, so the record is very wide… you will want to SELECT only those columns from which you need information.

SELECT 
 [TicketId]
,[TicketNumber]
,[TicketTags]
,[TicketStates]
,[EntityName]
,[CustomData]
,e.[AccountId]
,a.[Name] as [AccountName]
,[MenuItemName]
,[PortionName]
,[Price]
,[Quantity]
,[PortionCount]
,[CalculatePrice]
,[OrderTags]
,[OrderStates]
FROM [dbo].[Orders] o
JOIN [Tickets] t on t.[Id] = o.[TicketId]
LEFT JOIN [TicketEntities] te on te.[Ticket_Id] = t.[Id]
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [Accounts] a on a.[Id] = e.[AccountId]
WHERE t.[Id] = 2674

Sorry, let me refraise that question,
Am strugling to get to grips with that, how would I use that to return the total value of orders with state PMSDepartment=Wet?
Just cant visualize what thats doing and exactly what it will return.

Am trying… LOL
But my attempts are virtually stabs in the dark…

function test()
{
	qry = "SELECT  [TicketId],[TicketNumber],[TicketTags],[TicketStates],[EntityName],[CustomData],e.[AccountId],a.[Name] as [AccountName],[MenuItemName],[PortionName],[Price],[Quantity],[PortionCount],[CalculatePrice],[OrderTags],[OrderStates] FROM [dbo].[Orders] o JOIN [Tickets] t on t.[Id] = o.[TicketId] LEFT JOIN [TicketEntities] te on te.[Ticket_Id] = t.[Id] LEFT JOIN [Entities] e on e.[Id] = te.[EntityId] LEFT JOIN [Accounts] a on a.[Id] = e.[AccountId] WHERE t.[Id] = 1";
	var ticket = sql.Query(qry);
	return ticket;
}
SELECT sum([Price] * [Quantity]) as [TotalAmount]
FROM [dbo].[Orders] o
JOIN [Tickets] t on t.[Id] = o.[TicketId]
WHERE 1=1
AND t.[Id] = 2674
AND o.[OrderStates] like '%"S":"Wet","SN":"PMSDepartment"%'
1 Like

Your awesome :smile:

But am still struggling on how to implement that :frowning:
Sorry again for my incompatibly.

Should I be doing along these lines?
qry = ‘…’
var test = sql.Query(qry)

If do that the ’ ’ leave the % red and get expected ;

:frowning: missing something somewhere, gone back to the feature list etc and reread but not getting something somewhere;

function test()
{
	var test = sql.Query('SELECT sum([Price] * [Quantity]) as [TotalAmount] FROM [dbo].[Orders] o WHERE 1=1 AND t.[Id] = 2674 AND o.[OrderStates] like '%"S":"Wet","SN":"PMSDepartment"%'');
	return test;
}

Yes, it is somewhat simpler to keep the query in a single line, but you can also use a backslash to concatenate lines…

function getOrderInfo(ticketId) {
  var qry = " \
  SELECT \
 [TicketId] \
,[TicketNumber] \
,[TicketTags] \
,[TicketStates] \
,[EntityName] \
,[CustomData] \
,e.[AccountId] \
,a.[Name] as [AccountName] \
,[MenuItemName] \
,[PortionName] \
,[Price] \
,[Quantity] \
,[PortionCount] \
,[CalculatePrice] \
,[OrderTags] \
,[OrderStates] \
FROM [dbo].[Orders] o \
JOIN [Tickets] t on t.[Id] = o.[TicketId] \
LEFT JOIN [TicketEntities] te on te.[Ticket_Id] = t.[Id] \
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId] \
LEFT JOIN [Accounts] a on a.[Id] = e.[AccountId] \
WHERE 1=1 \
AND o.[OrderStates] like '%Submitted%' \
AND o.[OrderStates] like '%Status%' \
AND t.[Id] = ";

  qry += ticketId; //2674
  
  var r = sql.Query(qry).All;
  return r[0];
}

Here, your sum query, with another way to concatenate:

function getOrderSum(ticketId) {
  var stateName = "Status"; // PMSDepartment
  var state = "Submitted";  // Wet
  
  var qry = "";
  qry += "SELECT sum([Price] * [Quantity]) as [TotalAmount] FROM [dbo].[Orders] o JOIN [Tickets] t on t.[Id] = o.[TicketId] WHERE 1=1";
  qry += " AND o.[OrderStates] like '%" + stateName + "%'";
  qry += " AND o.[OrderStates] like '%" + state + "%'";
  qry += " AND t.[Id] = " + ticketId;
  
  var r = sql.Query(qry).All;
  return r[0];
}

I prefer the last method shown here, because now if I want to, say, exclude the TicketId, I just comment out the line…

function getOrderSum(ticketId) {
  var stateName = "Status"; // PMSDepartment
  var state = "Submitted";  // Wet
  
  var qry = "";
  qry += "SELECT sum([Price] * [Quantity]) as [TotalAmount] FROM [dbo].[Orders] o JOIN [Tickets] t on t.[Id] = o.[TicketId] WHERE 1=1";
  qry += " AND o.[OrderStates] like '%" + stateName + "%'";
  qry += " AND o.[OrderStates] like '%" + state + "%'";
  // qry += " AND t.[Id] = " + ticketId;
  
  var r = sql.Query(qry).All;
  return r[0];
}
1 Like

whats the [0] on the return?

row?

The query returns a string array, so I select the first index of that array, which is 0.

Otherwise you get this as your return:

Awesome. :smile:
Thanks you so so much.

Am I best to keep these is separate scripts and call within the main one?

If I understand whats going on.

function getOrderSum() {
  var stateName = "PMSDepartment";
  var state = "Wet";
  
  var qry = "";
  qry += "SELECT sum([Price] * [Quantity]) as [TotalAmount] FROM [dbo].[Orders] o JOIN [Tickets] t on t.[Id] = o.[TicketId] WHERE 1=1";
  qry += " AND o.[OrderStates] like '%" + stateName + "%'";
  qry += " AND o.[OrderStates] like '%" + state + "%'";
  
  var r = sql.Query(qry).Today.All;
  return r[0];
}

Would return ALL wet sales for today??? :smile:, seems to…

It would return all Wet sales ever.

You would need to add some more criteria for selecting certain Order and/or Ticket dates to trim the results down.

I thought the .Today.All would show only today work period from what I read on the feature list post.

Soory, missed that part of your script. That may well be the case. But it is more accurate to say that returns the last Workperiod.

Hmm… no, it is returning everything for sure. In any case, it might be best to filter the date. This returns last closed workperiod results…

SELECT sum([Price] * [Quantity]) as [TotalAmount]
FROM [dbo].[Orders] o
JOIN [Tickets] t on t.[Id] = o.[TicketId]
WHERE 1=1
AND o.[OrderStates] like '%Submitted%' -- Wet
AND o.[OrderStates] like '%Status%'    -- PMSDepartment
AND [CreatedDateTime] >= (SELECT [StartDate] FROM [WorkPeriods] WHERE [Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate] != [EndDate]))
AND [CreatedDateTime] <= (SELECT [EndDate] FROM [WorkPeriods] WHERE [Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate] != [EndDate]))
1 Like

Thanks, that’s a good point/tip :slight_smile:

.ByWorkPeriod.Today.All ?

The example only shows .ThisWeek

Are, you put a solution already :slight_smile: thanks

To keep things clean would rather put this in seperate function or script.

How do you call a script within a script?