Account Statements with Template Layout


#41

OK. I’ve finally finished my inventory stuff (I hope) now we can focus on this.

First of all @JTRTech thank you very much for detailed explanation of your need. Seems like Entity Print with Transaction Details report will solve your issue. I’ll create needed detail report to see if it works fine for that or not.

Report tags works to list in work period values but this report tag will list entire transactions for selected account.


#42

@emre looking forward for the statement which an print in A4 paper and send to clients …samba keep growing like tree


#43

Hey gsreddy… I’m taking samples as shown on the post I mentioned below. I’ll be glad to read if you have something specific not covered there…

http://forum.sambapos.com/t/account-statements-with-template-layout/8029/12


#44

@emre not sure this report will be hardcoded or custom report as my opinion once your doing hard work let it like proper statement which can filtered by date/per month so monthly statements printed every month send to clients, same time information as all transactions on the account debit and credit and total due . at last bottom we may able to customise text remmitence/payment advise to direct client to pay to mentioned account and due date etc.
I have a sample statement which I used earlier POS I will post those,

  • Also few of them asked tax invoice which is same but only show the current transaction/ticket on the day like a bill but in a A4 page format also I will post soon thanks
    This is the invoice iam using in Samba but it does only normal text format .

1.PDF (12.4 KB)


#45

@JTRTech It is not very clear what you mean with tags like {ENTITY NAME:Rooms}, {ENTITY DATA:Account} etc… What entities you have and which entity you’re planning to use to print that statement? Room or Customer entity? Can you post your entity custom fields here?


#46

@gsreddy a new printer type ‘Document Printer’ was introduced which allows greater formatting for invoices.
http://forum.sambapos.com/t/sambapos-5-1-54-release/7816

Here is what I have done using this Printer Type. It would be great if this header formatting could be included In the Account statement. Report tags that can be included in printer template would be excellent but some way of filtering by date or even last month will be required. Entire transaction list over a couple of years could be 30 pages long.

Even if was possible to edit the account transaction template I think it would help
Here is a sample statement

Here is the statement builder on my accounts package. I know something like this is beyond the scope of this project. Its just to give you an idea of the data imput usually required


Printer Template A4 Size Printer for Invoice
#47

It depends on what your new statement report allows.
The booking on I did used room entities and booking entities.
Booking entities were used to have seperate account per booking.
The setup I made updated the room entity with the booking entity name in the account field in the hope that a statement could be produced using only the room entity as the booking entity/account name would be defined in the room custom field of Account.

The room entityies were the ones on the entity screen but creating a ticket with/selecting room entity also updated the ticket with the booking entity using the booking number set in the booking number custom field.

That will probably confuse the hell out of you LOL

Ideally the transaction details report tag would want to accept date rang expession and account name which can be fed using other tags or a script.

The big thing everyone seems to want is that we can format the template/layout to show business and customer info.


#48

Had a thought today (I sure whenever I say that you all thing God what now!)
What if there was an additional reference on account transactions - a bit like description - that we can filter to?
The room accounts could be filtered to this using booking number as a reference/filter rather than date (although date would be good)
What this could also lead to is a solution for @pauln plan of sub accounts, customer account is overall but the ‘sub’ accounts were filtered using this reference?

Just an idea…


#49

@silentbob that’s great looks what I need too , is it possible to share the setup thakns.


#50

@emre have you had chance to sort a solution for customisable statement/account transactions tag?


#51

He is working on something that will blow your mind. literally :stuck_out_tongue:


#52

Serious or you messing with me :stuck_out_tongue:
Something on this topic or something else which will blow minds?


#53

Im actually serious.


#54

Come on, don’t toy with me, give us a hint at least… :smile:


#55

I did :slight_smile: it’s being tested and worked on internally I’m sure he will release something soon.


#56

Was verry subtle hint!!!
‘Blow your mind, literally!’ = self distructing samba? :-p


#57

Oh no… literally broken grammar :wink:


#58

Was this released in .55?


#59

Yes it was in .55 it’s the REPORT SQL DETAILS tag


#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:


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.


Print bill with customer