Order Voided after Payment Settled

Version 4 V4 Question
1

i have tried to make a report using sql to check orders that are voided after payment.

but i am not sure if this is how it suppose to be done
please correct me

many thanks

Template:
[Paid Tickets:1,2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Order State|Menu Item|Amount
@@VoidedOrder
@@VoidTotal

Script:@@VoidedOrder
SELECT
T.TicketNumber,
min(convert(nvarchar,T.Date,105)) as Date,
min(convert(nvarchar,T.Date,108)) as Time,
min(TE.EntityName) as TableName,
β€˜Void’,
min([MenuItemName]) as MenuItem,
min([Price]) as TotalAmount
FROM Orders as O
join Tickets as T
on O.TicketId =T.TicketNumber
join TicketEntities as TE
on TE.Ticket_Id = T.Id
Where [OrderStates] like β€˜%Void%’ And [TicketStates] like β€˜%Paid%’
And Date > β€˜{Start}’ and Date < β€˜{End}’
group by T.TicketNumber
order by Date,Time

Script:@@VoidTotal
SELECT β€˜Total’,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™,β€˜β€™, sum([Price])
FROM Orders as O
join Tickets as T
on O.TicketId =T.TicketNumber
Where [OrderStates] like β€˜%Void%’ And [TicketStates] like β€˜%Paid%’
And T.Date > β€˜{Start}’ and T.Date < β€˜{End}’

Output:

e539bc8aff577d12.png1366Γ—768 138 KB

the total seem to be wrong
d80f524e1185dcc9.png1366Γ—768 143 KB

2

You don’t need SQL for this. Try

[Paid Tickets:1,2,2,2,2,2, 2]
>Ticket No|Date|Time|Table|Order State|Menu Item|Amount
{REPORT ORDER DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,OS.GStatus,O.MenuItemName,ON.TotalPrice:(TS.Status=Paid) and (OS.GStatus=Void)} 
>Total||||||{REPORT ORDER TOTAL:(TS.Status=Paid) and (OS.GStatus=Void)}

You’ll find more info here
http://sambapos.com/wiki/doku.php?id=custom_reporting

4 Likes
3

thankyou so much @emre you surely have made things simple and easier :slight_smile: