Loop through Tickets for a report

Im trying to make a report that will be auto-printed when work period ends.

I need to run a loop through each ticket for the work period and print some info like:
Ticket ID, Date/Time, Order Items Count, subtotal, discount, extra charges, tax, payment accounts, total.

I would assume there should be a basic loop in SambaPOS to accomplish these basic things like get all tickets, and print out all relative info from it… Considering the fact the SambaPOS revolves around the Ticket Object, there has to be a loop function to run through them for reporting purposes.

Ive been trying to research for a while, and most of the solutions are taking me down to making huge DB queries with Joins and etc. Any simpler suggestions ?

here is a sample of output im looking for:

I see all these keywords in the Predefined reports in SambaPOS,

Example:
$1|{REPORT TICKET COUNT:(TY=$1)}|{REPORT TICKET TOTAL:(TY=$1)}
Total|{REPORT TICKET COUNT}|{REPORT TICKET TOTAL}
Amount per Ticket||[=F(TN(’{REPORT TICKET TOTAL}’)/TN(’{REPORT TICKET COUNT}’))]

Where is the documentation for all these tags ?

This is something basic and probably close to what you want but you need to adjust it for your needs by updating calculation names (CA) and payment type names (PA)

[TICKETS:1,1,1, 1, 1, 1, 1, 1, 1, 1, 1]
>Ticket#|Date|Items|Sub Total|Discount|Delivery|TAX|Cash|Credit Card|Online Payment|Total
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Orders.Count,T.PlainSum,CA.Discount,CA.Delivery,T.Tax,PA.Cash,PA.Credit Card,PA.Online Payment,T.TotalAmount}

Documentation: https://sambapos.com/wiki/doku.php/custom_reporting

1 Like

Wow!
Thanks a lot @emre you pretty much gave me the complete solution!
However, how do you print the totals in the end?

I tried this but doesn’t seem to work…

{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Orders.Count,T.PlainSum,CA.Discount,CA.Delivery Charge,T.Tax,PA.Cash,PA.Online Payment,T.TotalAmount}
>Total||{REPORT TICKET TOTAL:T.Orders.Count,T.PlainSum,CA.Discount,CA.Delivery Charge,T.Tax,PA.Cash,PA.Online Payment,T.TotalAmount}

> {REPORT TICKET DETAILS} just repeats all the rows.

Ok, i managed to figure some of them out… not ideally what i was expecting:

>Total||0|0|{REPORT CALCULATION TOTAL:(CT=Discount)}|{REPORT CALCULATION TOTAL:(CT=Delivery Charge)}|{REPORT CALCULATION TOTAL:(CT=Tax)}|{REPORT PAYMENT TOTAL:(PT=Cash)}|{REPORT PAYMENT TOTAL:(PT=Online Payment)}|{REPORT TICKET TOTAL}

However, {REPORT CALCULATION TOTAL:(CT=Tax)} does not work. So i think there is another way to calculate Tax Total.

How do i print the Sum of an Entire Column?

Otherwise, how do i create Scripts that can take the table output from {REPORT TICKET DETAILS} and do my own manipulation to it (SQL Sum for example) ?

My Current Goal is to:
• Get TAX total: Sum(T.Tax)
• Get total count on order items in the tickets. Basically Sum(T.Orders.Count)

Ok. Figured out how to get the Sum in a report using .Sum and .Count

{REPORT TICKET DETAILS:T.Orders.Count}|{REPORT TICKET DETAILS:T.PlainSum.Sum} …
and so on…

So far so good. WIll post back if i face more roadblocks.

Ok. a little bit of an issue now…

i notice T.Orders.Count is the count of all orders.
How do i skip the Void orders ?

And also {REPORT TICKET DETAILS} i want to filter only the completed orders. Its also showing ongoing orders right now.

You can include (ODI=True) expression in your tags to exclude voided items.

You can find some examples if you search forum for ODI=True

Using ODI=True makes everything disappear. I’m assuming that’s because i’m trying to use this with {REPORT TICKET DETAILS}. In the example i notice its being used with {REPORT ORDER DETAILS}. Anyways to put the check directly in the tickets ?

I need to:
• Run the loop only for settled tickets only.
• {REPORT TICKET DETAILS{T.Orders.Count} to return only non-void orders.

Can you post here how you used it? Maybe there is a syntax issue..

OH OK got it. Hmm

Thanks! Last stretch for me here… Reports and I’m done.

Few things im trying to figure out… landed up on another situation:
All the prices and delivery charge is VAT included. So when printing the monthly VAT report, i get something like this:

Subtotal: 2530
Delivery: 60
TAX: 153.73
Grand Total: 2410
As you can see, this feels rather ODD because Subtotal+Delivery+TAX != Grand Total.

I have standard 7% tax, so i thought /1.07 would give me the right numbers, but that’s not adding up to be correct. Slight difference probably due to rounding, but the Govt. department will not be happy taking a report without the numbers adding exactly!
what do i do ?

I did try a bit of cheating like Subtotal = grand total - delivery - tax + (Delivery0.07)
But don’t think i know how to make nested calculations here… [a -b -c [d
0.07] ] didnt work.

I added T.LineCount and T.ItemCount values for next version (.61).

I didn’t fully understand second question. If all prices are VAT included and you have standard %7 tax, dividing grand total by 1.07 should give to correct tax amount.

I was trying to divide the individual subtotals and delivery charges to get their prices without the TAX.
Anyways, I talked to the client and agreed on a simpler Report for VAT… so that is okay.

Can you share the SQL queries defined for {REPORT TICKET DETAILS} and other keywords in reports ?
that way i can take the queries and modify them to my need and define them as custom scripts or views directly into my DB.

Didnt find any solution yet, got a dirty fix to work so far:

[Appetizers Sold:3,2,2]

Item|Qty|Revenue
@{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc:(ODI=True):{0}:,}
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName=“$1” and (MG=Appetizers)}

So i have the same table code block repeated for each ‘MenuGroup’. Copied from an older table that i had created… Dont love it, but it does the job. Any proper solutions would be much appreciated.

Explain exactly what your struggling to do?
Just off the bat, why not put the MG= in the @list rather than list all orders then filter in the report, probably not big difference but might load a little quicker…

1 Like

Good point!
moved the MG= to the @ list.

Am trying to list the items sold in groups by category.

All seems to work for the while.