Account Statements with Template Layout

V5 Question
60

This is one of the latest uses of that tag:

[ENTITY]
<T>ACCOUNT STATEMENT
<J00>Customer:{ENTITY NAME}|Account:{ACCOUNT NAME}
<F>=
<J00>Date| TID| TNo| Description| Amount
<F>-
{REPORT SQL DETAILS:
SELECT
 tv.[Date]
,tkt.[Id] as [TicketId]
,tkt.[TicketNumber] as [TicketNo]
,CASE
  WHEN [Credit] > 0 Then 'Payment'
  WHEN [Debit] > 0 Then 'Purchase'
 END as [Desc]
,[Debit]-[Credit] as [Amount]
FROM [AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
-- Accounts
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id]
-- Entity
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
WHERE 1=1
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Name] = '{ENTITY NAME}')
:F.Date.desc,F.TicketId,F.TicketNo,F.Desc,F.Amount::<J00>{0}| {1}| {2}| {3}| {4}
}
<F>=
<J00>Balance:|{ENTITY BALANCE}

Now I use Automation > Scripts and put the query in @@GetStatement, but the idea is the same. You would use this in a Printer Template, as such:

Pasted image736×491 20.2 KB

This was my final result, using a Custom Entity Screen …

Pasted image1500×894 74.7 KB

Show Statement and Print Statement are Automation Commands that have been added to the Customer Account Screen, with their parameters set to Entity.

When you click Show Statement, it loads a Custom Entity Screen containing details for the highlighted Account. This screen contains a Report Widget that uses the same SQL to generate the detail as you would see on a printed statement, but it also has some other features such as methods for paying the account or displaying the Ticket.

When you click Print Statement, it fires an Execute Print Job Print Entity Action that is set to use an Invoice Printer and the Account Statement Printer Template.

4 Likes