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.