SambaPOS Forum
English Boards => Support => Topic started by: DQ on October 31, 2013, 05:50:19 pm
-
I am implementing Kitchen display for my store where I need to display last 8 tickets with items, void and other details. What would be the SQL query to retrieve this data?
My idea is running a python web server on a back office computer where MS SQL Server is running. Web Server will process incoming request, execute SQL query to retrieve recent 8 tickets details and return data in a formated HTML page.
Client (Raspberry Pi) will be accessing web page and refresh every 1 minute.
Thanks,
DQ
- See more at: http://www.sambapos.org/en/content/sql-query-retrieving-recent-8-tickets#sthash.JSWooiDV.dpuf (http://www.sambapos.org/en/content/sql-query-retrieving-recent-8-tickets#sthash.JSWooiDV.dpuf)
-
It will be quite a bit more complicated than this, to parse Order Tags and Order States into a friendly format, but you'll want to look at the "Orders" table...
SELECT [TicketId]
,[OrderNumber]
,[MenuItemName]
,[PortionName]
,[Price]
,[Quantity]
,[PortionCount]
,[CreatedDateTime]
,[Tag]
,[OrderTags]
,[OrderStates]
FROM [SambaPOS3].[dbo].[Orders]
WHERE [CreatedDateTime] >= '2013-11-01 07:35:38.220' AND [CreatedDateTime] <= '2013-11-02'
ORDER BY [TicketId], [OrderNumber]
-
First it depends if you are referring to V2 or V3 as they use different database tables.
If you just simply want the very last 8 entered a query such as:
SELECT TOP 8 [Id]
,[Name]
,[DepartmentId]
,[LastUpdateTime]
,[TicketNumber]
,[PrintJobData]
,[Date]
,[LastOrderDate]
,[LastPaymentDate]
,[LocationName]
,[CustomerId]
,[CustomerName]
,[CustomerGroupCode]
,[IsPaid]
,[RemainingAmount]
,[TotalAmount]
,[Note]
,[Locked]
,[Tag]
FROM [SambaData2].[dbo].[Tickets]
ORDER BY [LastUpdateTime] DESC
This is the fastest way to recall the information that you require, from here you will need to reference the [Id] in the dbo.TicketItems table to recall the relevant items in the ticket and then display.
You do not need to include all the columns I mentioned it just depends what information you want to recall from dbo.Tickets, place it in an array, then query the dbo.TicketItems for the product items on each ticket.
V3 has a different setup so you can use the same principal but reference the correct tables and data.
-
Thanks Guys.
I came up with below query.
SELECT TicketItems.Id as TicketItemsId, TicketId,MenuItemName,TicketItems.PortionName,TicketItems.Quantity,Price,Voided,Gifted, CreatingUserId, Tickets.Id, Tickets.TicketNumber, Tickets.TotalAmount, CONVERT(varchar, Tickets.LastPaymentDate, 20) as LastPaymentDate, Users.Name FROM TicketItems, Tickets, Users where TicketId in (SELECT TOP(8) Id from Tickets ORDER BY LastUpdateTime DESC) AND TicketId = Tickets.Id and CreatingUserId=Users.Id
To get the flavors/properties of each item
SELECT TicketItemProperties.Name as Flavor FROM TicketItemProperties WHERE TicketItemProperties.TicketItemId = " + str(row.TicketItemsId)
Let me know if there is a optimized way combine both the queries.
I have created python based web interface to view live transactions remotely. Will share with team once completed.