I did 
it’s being tested and worked on internally I’m sure he will release something soon.
Was verry subtle hint!!!
‘Blow your mind, literally!’ = self distructing samba? :-p
Oh no… literally broken grammar 
1 Like
Was this released in .55?
Yes it was in .55 it’s the REPORT SQL DETAILS tag
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:
This was my final result, using a Custom Entity Screen …
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
Thanks that is exactly what I am looking for.
Any plans for a tutorial or even a quick guide to give me an idea what exactly you are doing to achieve this?
He just showed you a great example. Copy it and play around with it.
1 Like
I have tried to get printing working first but I cant get it working.
This is the result
It does not seem to be passing entity details
If I manually add {ENTITY NAME} to printer template it works
The new update looks great and looks to have brought a solution to the account statement queries we’ve had. However, is there a way to include an date filter?
I use my accounts for room tabs, so on checkout I’ll print out a statement similar to the one @QMcKay showed above. But this shows all transactions, so some will be from previous guests. If I could filter it either to only show the dates the guest stayed in the room, or maybe show only transactions that are still outstanding (ie all transactions since the last cash payment), that would be perfect.
Thanks,
Chris
You can filter by date for sure.
I guess it is just about time to post the setup that produces the screen shown.
At the top-right included a date filter box to show transactions after the date entered.
The setup also determines, displays and stores the Last 0 Balance date, so this could be used as well.
The Print Statement button is configured to obey the date filter, so it will only print transactions that you want.
You can also make a Payment to the Account directly from this screen.
I am currently waiting for a small tweak that @emre has implemented for paying accounts via the Payment Screen, so when that comes, I hope to post the full implementation as a Tutorial.
As I mentioned earlier, I separated the SQL into a script… and the script has the date filter in it. But you need to know how to access the date, which is stored in a Program setting, which is done via Actions/Rules and then the SQL Script reads the setting directly from the Database.
@@GetStatement
declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()
SET @dateFilterBeg = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Date Filter Beg')
IF @dateFilterBeg = ''
BEGIN
SET @dateFilterBeg = '2000-01-01'
END
--SET @dateFilterBeg = @dateFilterBeg + 'T00:00:00.000'
--declare @entityName varchar(255) = ''
--declare @accountId int = 0
--declare @accountName varchar(255) = ''
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
--SET @entityName = (SELECT [Name] FROM [Entities] WHERE [Id]=@entityId)
--SET @accountId = (SELECT [AccountId] FROM [Entities] WHERE [Id]=@entityId)
--SET @accountName = (SELECT [Name] FROM [Accounts] WHERE [Id]=@accountId)
--UPDATE [ProgramSettingValues] SET [Value]=@entityName WHERE [Name]='AS Entity Name'
--UPDATE [ProgramSettingValues] SET [Value]=@accountId WHERE [Name]='AS Account Id'
--UPDATE [ProgramSettingValues] SET [Value]=@accountName WHERE [Name]='AS Account Name'
declare @txcount int = 0
declare @i int = 1
declare @balance decimal(7,2) = 0.00
declare @tbl_tx table (
[Id] INT IDENTITY(1,1) NOT NULL
, [Date] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Desc] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)
INSERT INTO @tbl_tx ([Date], [TicketId], [TicketNo], [Desc], [Amount], [Balance])
--SELECT TOP 1 @entityId as [Date], @entityName as [TicketId], ' ' as [TicketNo], @accountId as [Desc], @accountName as [Amount] FROM [Entities]
--UNION
SELECT
tv.[Date] as [Date]
,tkt.[Id] as [TicketId]
,CASE
WHEN tkt.[TicketNumber]>0 Then tkt.[TicketNumber]
ELSE d.[Id]
END as [TicketNo]
,CASE
WHEN [Credit] > 0 Then 'Payment [' + d.[Name] + '] ' + tx.[Name]
WHEN [Debit] > 0 Then 'Purchase'
END as [Desc]
,[Debit]-[Credit] as [Amount]
,0 as [Balance]
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] and te.[EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name]=@entityType)
-- 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] = @entityName)
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Id] = @entityId)
AND tv.[Date] >= @dateFilterBeg
ORDER BY [Date] ASC
SELECT @txcount = count([Id]) FROM @tbl_tx
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx WHERE [Id]=@i)
UPDATE @tbl_tx SET [Balance] = @balance WHERE [Id]=@i
IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [Date] FROM @tbl_tx WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
SET @i = @i + 1
END
select [Date], [TicketId], [TicketNo], [Desc], [Amount], [Balance] from @tbl_tx ORDER BY [Id] DESC
1 Like
Has the method for paying accounts from payment screen been implemented??
This would be a very useful feature.
Yes, I just tested it with 5.1.56 … I will post a Tutorial on my full setup today some time.
4 Likes
Excellent I look forward to seeing how you achieved this!
I am really interested in this setup. Is there any update on the tutorial?
Ummm, Yes it seems “Customer Accounting” is becoming a big thing 
. I reckon it is just the beginning…
I am building the Tutorial. The entire setup that I use is quite a bit more elaborate than a simple “Print Statement” configuration that I already posted, so it is taking me a little more time than I anticipated to put it together. I should be finished with it today some time.
2 Likes
Tutorial has been posted…
3 Likes
That Tutorial is Legendary! 
You are one of the Kings of Tutorials, I have tried to study each and every one of them, thanks is not enough said…