Payments by Terminal Report

Version 4 V4 Custom Reports
1

This custom report displays payments by terminal.

@@PaymentsByTerminal 

select PT.Name,
    SUM(Amount) as Amount
  from Payments
  inner join PaymentTypes as PT on PT.Id = PaymentTypeId
  where TerminalId in (select Id from Terminals where Name = '@1') 
     and Date > '{Start}' and Date < '{End}'
group by PT.Name

Template

[Payments by Terminal:1, 1]
>Server|
@@PaymentsByTerminal:Server

>Till 1|
@@PaymentsByTerminal:Till 1

This report displays results for 2 Terminals named Server and Till 1. You need to rename these terminal names with your terminal names. If you want to add more terminals to report you need to create a new section for it. For example if we want to add a new section for Till 2 terminal, template becomes…

[Payments by Terminal:1, 1]
>Server|
@@PaymentsByTerminal:Server

>Till 1|
@@PaymentsByTerminal:Till 1

>Till 2|
@@PaymentsByTerminal:Till 2

Result

30146a00526e1773.jpg2302×1446 155 KB

We can insert this report into End of Day report.

100 defines the section of End of Day report and it will append it to the end. Try 90, 80, 70,… etc to display it on upper sections. Leave it 0 to remove it from End of Day report.

3411f5bf0408edb1.jpg2302×1446 178 KB

Database Tools Import file

Payments by Terminal.zip (641 Bytes)

:warning: Needs at least 4.1.50 Version.

2 Likes
2

Custom Report works beautiful. It resolved the issue with crashing Work Period Report after voiding and order.

Once again. Thank you for all your help. :smile:

3

Hi! How can i check Payments by department :slight_smile:
I’m new to SQL. Can you recommend a basic guide to understanding how the SQL script works?

4

###@@PaymentsByDepartment

select 
    PT.Name,
    Convert(nvarchar,Cast(sum(Amount) as Money),1) as Amount
  from payments
  inner join PaymentTypes as PT on PT.Id = PaymentTypeId
  where DepartmentId in (select id from Departments where Name = '@1') 
     and Date > '{Start}' and Date < '{End}'
group by PT.Name

###Template

[Payments by Department:1, 1]
>Restaurant|
@@PaymentsByDepartment:Restaurant

>Delivery|
@@PaymentsByDepartment:Delivery

It displays report for two departments named Restaurant and Delivery. You can change these departments by changing @@PaymentsByDepartment:Restaurant part to your department name. Also don’t forget to change headers by changing >Restaurant| line.

This sql script also demonstrate how to format amounts by thousand separators.

###Database Tools Import File

Payments by Department.zip (670 Bytes)

1 Like
5

For learning SQL http://www.w3schools.com/sql/ might be a good start.