I am trying to recreate the sales report in SQL but there is a difference in the quantities and amounts. I believe this is because my query doesn’t exclude voids, price changes and discounts. Can you please help me modify it?
The report I’m trying to do:
[Sales:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1"}
{REPORT ORDER DETAILS:' '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1":2}
and my query is:
select MenuItemName, CONVERT(DECIMAL(10,2),sum(Quantity)) as 'Quantity', CONVERT(DECIMAL(10,2),sum(Price*Quantity)) as 'Sales'
from Orders
where CreatedDateTime between '2017-05-01 00:00:00.000' AND '2017-05-29 23:59:59.999'
group by MenuItemName
order by 'Sales' desc
That will be tricky I think as order states are stored in Json I believe which SQL would need to parse.
In reports you would use ODI to key on items sold and exclude void.
Price changes would be reflected as sure these are logged in the orders table…
I’m trying to give certain people access to the report through a webpage without having them install SambaPOS on their machines since I have the database on a server on the network.
So you don’t keep track of void items in the database? is there any way to achieve this?
What do you mean by that? Yes void items are tracked but excluded from sales reports using report expressions to not include ODI=False or perticular order states.
Your better solution is to use the samba API ‘graphql’ as kendash says. That can pull samba reports as on terminal, no need for direct sql.
This is a Module in GQL Modules. It lists all Reports that are marked as Show in Report Explorer. It fetches the Report that you select using GraphQL and displays it.
You can use GQL Modules, or build your own implementation using Javascript and GraphQL.
Using SQL, you can check the [CalculatePrice] field. If it set to 1 that means the Item was not Voided nor Gifted. If it is set to 0 that means the Order was probably Voided and/or Gifted.
You can also look at the [OrderStates] column to check for the strings “Void” and “Gift” if you want to be more specific about what is included or excluded.
Use one or more of these in your WHERE clause to get rid of Voids and Gifts:
WHERE 1=1
AND [CalculatePrice] != 0
AND [OrderStates] NOT like '%"S":"Gift","SN":"GStatus"%'
AND [OrderStates] NOT like '%"S":"Void","SN":"GStatus"%'
A Price Change on an Item should be in the [Orders] table. The original Price will not be recorded.
When it comes to Discounts, that is a different story. There are so many ways to implement Discounts. By Item Price Update, by Order Tags, or by Ticket Total Discount.
You need to know how the Discount was applied, and work out your Query for each possible scenario that you use.
A Ticket-level Discount will never be applied in the Order table; it will be reflected in the Tickets table, so SUMing Orders is always going to give you incorrect results in this scenario.
