SQL query to retrieving recent 8 tickets

[DQ]

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

[QMcKay]

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

Code: [Select]

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]

[lemmings]

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.

[DQ]

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