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
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"%'
But am still struggling on how to implement that
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 ;
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];
}
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??? , seems to…
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]))