Small updates to Tickets and Orders models

DBO.TICKETS

Add column => PlainTotal

PlainTotal should be total amount before taxes and calculations. 
Basically sum of all order & order tags within 
a ticket (Order.Price * Order.Quantity) + Order.OrderTag.Total

This would make it easy to query subtotal of the invoice via SQL, 
instead of having to calculate it manually.

DBO.ORDERS

Add column => GroupCode

GroupCode for Orders table has been missing for a while now. 
As soon as menu items are deleted, it fails to categorize orders.

Add column => OrderTagTotal

Total amount for all OrderTags for the order row

This would make it easy to build SQL queries for Orders to include OrderTags. 
At the moment it takes ~100 lines of SQL cursors and other complex 
queries to get the OrderTag total amounts from JSON objects for each order. 

Add column => Subtotal

Same formula for PlainTotal but for each order row

Add column => TaxableAmount

Calculation Types currently have Include Tax field. 
When this field is not selected, the calculations affect taxable amount. 

If the order total (including order tags) is 10$, and you apply 10% discount, 
taxable amount becomes 9$. 

Instead of being calculated by Report Engine (O.ExactTotal), 
we can pull it directly from DB via SQL. 

To get to taxable amount is a colossal PITA to do at the moment.
This would make it really easy to build tax related reports for orders.

Add key-value pair => Taxes

Add a key value pair for amount (AMT) of tax calculated into the Taxes JSON object. 

CURRENT:
[{"AC":false, "AT":19, "RN":0, "TN":"VAT", "TR":10.00, "TT":2}]

IDEAL:
[{"AC":false, "AT":19, "RN":0, "TN":"VAT", "TR":10.00, "TT":2, "AMT": 1}]

This would make it easy to pull taxes out of the order tables. 
At the moment, it requires a whole spectrum of leg work 
to get taxable amount for each order, then to re-calculate taxes 
via flags in the JSON object.

Conclusion

This small additions would make it extremely easy to build some of the reports. At the moment, this is really complex to do. It takes over 1000 lines of complex SQL queries and advanced features to build a generic aggregated reports. It really shouldn’t be this hard to build a simple tax filing report.

2 Likes

Can you share the report you are trying to build?

~ 1000 lines of SQL queries and 4 tables in the background aggregating data before this final report is shown.

Date                   -- generic date formatted as string
Type                   -- ticket type
Entity                 -- Table, customer, delivery etc.
SN                     -- TicketNumber
Rate                   -- Conversion rate of USD to KHR
Pax                    -- Ticket tag for number of people
Food, Soda, Beer, etc. -- aggregated total for Orders with this Tag
Other                  -- aggregated total for Orders that dont match any of the above
SubTotal               -- subtotal SUM(Order.Price * Order.QTY) + Order.OrderTagTotal
Discounts, Tips etc.   -- sum total of calculation type amounts per ticket
ST, PLT, VAT etc.      -- sum of taxes for each order per ticket (calculated from taxable amount of each order)
Total                  -- ticket TotalAmount
Cash, KHQR etc.        -- sum of payment type amounts

In an essence a structure to build any type of aggregated reports - by groupcode, by tag, by custom tag, by name, by date etc.