Account Statements with Template Layout

[chris89]

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

[QMcKay]

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

[silentbob]

Has the method for paying accounts from payment screen been implemented??
This would be a very useful feature.

[QMcKay]

Yes, I just tested it with 5.1.56 … I will post a Tutorial on my full setup today some time.

[silentbob]

Excellent I look forward to seeing how you achieved this!

[Emma]

I am really interested in this setup. Is there any update on the tutorial?

[pauln]

Ummm, Yes it seems “Customer Accounting” is becoming a big thing . I reckon it is just the beginning…

[QMcKay]

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.

[QMcKay]

Tutorial has been posted…

Customer Account Statements and Payments (Custom)

This Tutorial will demonstrate how to build a Custom Account Handling Screen for Customers. It allows for Printing detailed Customer Account Statements, making Payments to Customer Accounts (via the built-in Payment Screen), and Crediting Customer Accounts. Requires SambaPOS 5.1.56 or higher Operations The Customer Account Operations Screen we will build and Automate is shown on the right. [image] Quick Nav: Account Type and Account for Transfers (for Account Payments via Paym…

[pauln]

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…

[kroper]

This is great. However I’d like to know if you could grab ticket details and display on the invoice instead of the transaction type… For example, you would see “Ham & Cheese Sandwich” Instead of “Purchase” on the invoice.

Also, how can this be exported to excel and/or emailed to specific customers?

Thanks!

[JTRTech]

Dislike being the instigator of this topic it’s been a while.
Am trying to find a solution for an itemised statement…
Sure I saw something similar a while back, looking over this topic it was more about formatting rather than itemised…
Itemized referring to showing order breakdown for the account debits.
Anyone got a link, am struggling to find what I’m sure has been done already (before redoing it myself)

[JTRTech]

@QMcKay hoping you might lend a hand with this?
Trying to make an itemized statement.

Am thinking something like this;

SELECT	[Quantity]
		,[MenuItemName]
		,[PortionName]
FROM [RAH].[dbo].[Orders]
WHERE [TicketId] IN 
	(SELECT [AccountTransactionDocumentId]
		FROM [RAH].[dbo].[AccountTransactionValues]
		WHERE [AccountId] = 14)

This gets be orders for all transactions on account 14… (My customer account)

But need to include payments too…

Started trying to expand but am showing my SQL niceness;

SELECT [RAH].[dbo].[AccountTransactionValues].[AccountId],[RAH].[dbo].[AccountTransactionValues].[AccountTransactionDocumentId],[RAH].[dbo].[AccountTransactionValues].[AccountTransactionTypeId],[RAH].[dbo].[Orders].[Quantity],[RAH].[dbo].[Orders].[MenuItemName],[RAH].[dbo].[Orders].[PortionName]
		FROM [RAH].[dbo].[AccountTransactionValues]
		
	INNER JOIN
		[RAH].[dbo].[Orders]
		ON [RAH].[dbo].[Orders].[TicketId] = [RAH].[dbo].[AccountTransactionValues].[AccountTransactionDocumentId] --AND [RAH].[dbo].[AccountTransactionValues].[AccountTransactionTypeId] = 5

	INNER JOIN
		hmmmm where to go from here....

		WHERE [RAH].[dbo].[AccountTransactionValues].[AccountId] = 14

Am trying to get something like this;

Statement							
Date	Transaction	ID	QTY	Product	Value	Tax	Total
10/03/2017	Debit	1					
			1	Carlsberg	3.6		
						 £0.60 	 £3.60 
11/03/2017	Debit	2					
			1	Carlsberg	3.6		
						 £0.60 	 £3.60 
12/03/2017	Credit	3					-£5.00 

Cant get that layed out right but looks like this in excel;

A rolling balance would be nice but can see that being even harder…

I can see where to pull required data from but struggling with the joins etc.

Almost thinking I might have to resport some some scripting as an much more adapt with jscript than SQL…
Get list of transaction id;
Check transaction type and output lines accordingly perhaps into report order details for the breakdown…

[QMcKay]

I think the main reason this type of thing has not been done before is that there are to many scenarios to take into account, so it is not as straight forward as it might seem. If you are confident about making some assumptions as to how a Ticket is Paid, then it can be simplified, but chances are, there will be times when the results will not be correct. For example, here is the values returned for a single Ticket - look at how many rows it can return for something you may consider to be “simple”:

###SQL:

SELECT
tv.[Id]
,[AccountTransactionId] as [TxId]
,[AccountTransactionDocumentId] as [DocId]
,tv.[Date]
--,[AccountTypeId]
,atyp.[Name] as [AccType]
--,[AccountId]
,acc.[Name] as [Account]
,[Debit]
,[Credit]
,[Exchange]
,tv.[Name]
--,[AccountTransactionTypeId]
,txtyp.[Name] as [TxType]
--,[DepartmentId]
FROM [AccountTransactionValues] tv
JOIN [AccountTypes] atyp on atyp.[Id]=tv.[AccountTypeId]
JOIN [Accounts] acc on acc.[Id]=tv.[AccountId]
JOIN [AccountTransactionTypes] txtyp on txtyp.[Id]=tv.[AccountTransactionTypeId]

###RESULT:

So that Ticket contains the Sale, Tax T1 and Tax T2, a Rounding Calculation, Payment by 2 types of Cash Currency, and Change was issued in a single Currency (HNL).

Now imagine that Ticket also had a Service Charge Calculation (ie. Tip), and besides being Paid with Cash USD and Cash HNL, it was also partially paid with Customer Account. Or maybe it was Paid with Cash USD only, but the Change amount (Calculation) went to Credit the Customer Account. Or maybe there was a Discount Calculation on the Ticket as well … it just goes on and on …

I could probably eventually craft something that works for my system that takes into account all of the different Transaction Types and scenarios, but it would be very specific to my system, and probably would not work for most other systems.

The real problem here I think is that we should not assume that there is a 1-to-1 Product-to-Payment ratio, which is what your Excel sheet is doing, but it really is rarely that simple.

---

Anyway, while you can (almost) always get the results you want by building a statement with joins and sub-queries, it can quickly become rather complex and difficult to read and understand. So my preference a lot of the time is to break things down into smaller parts and store the results in @temp tables, then query them and/or loop through them, and store those results in another @temp table.

I mean, if you look at the SQL above, that just gets the Values and returns things in a readable format, but to do so, it already joins 4 tables together. Now you want to join the Tickets table on the DocId, and the Orders table on the TicketId.

So for example, I might first get the Ticket Values first (ie. Sale Tx, Tax Tx, Calc Tx, etc.) and maybe the Orders, and compile them into a @Charges table. Then get all the Payment Values (Cash, CC, Customer Account, etc.) and compile them into a @Payments table. Then gather all Account Payments into a @Credits table so we have records for when the Customer made a Payment to their Account. All the while taking into account whatever particular date-span you want to report on.

Of course the Charges and Payments tables should balance each other out, but the total of the Orders often times will not match due to Discounts/Services etc. And that is where things start to break.

[JTRTech]

@QMcKay very good points.

So first step to keep head from exploding is restrict account payment to whole ticket. Easily done.

Understandable points about calculations, these should indeed be taken into account to cover posible issues although beleive these have been unmapped on the system in question but should be simpler to include those in the breakdown below the order rows.

I am just not sure if it will be better to get all via sql or mix with some samba reporting in the report…

[QMcKay]

Ultimately, you have far greater control and granularity using SQL than you do with Reports. I use Reports for various things because they make things much more simple in many cases, but I also use pure SQL when I want something very specific, which is probably the case here.

Using Reports for this type of thing is going to take you down the same path as pure SQL; one top-level report feeds the next Report which feeds the next - it is like making joins and sub-queries already - it just does it behind the scenes, but often also includes a lot of data you don’t need to fetch which makes it slower. I know there have been recent improvements in .62 for some of this when reading the Release Topic, but I have not bothered to try it out.

I would definitely stay away from JScript in this scenario since it adds a lot of unnecessary complexity that pure SQL will handle far more easily.

[QMcKay]

I should add one more thing … Order Tags. If you do not include Order Tags which have a Price in the Product Price (ie. that box is unchecked) then you open up a whole new can of worms.

For example, I sell a Beer for $3 but for Customer VIP or Happy Hour, that Beer is tagged with a -$1 discount which is not reflected in the Order Price. So now we need to pull out all Order Tags for Product Discounts and Upcharges and subtract/add those Tag Price(s) from the Product Price. So the Ticket Total and Payments will match, but the Product Price will not match the Ticket Total unless we take into account those Order Tags. I mentioned this “small” niggle in the Purchase History Tutorial… it may well not be so “small” a discrepancy depending on the types of Products you sell.

[JTRTech]

Hmmm, ok, so as an alternative, simplification so there is a little more info than just Transaction IS and amount on the statement what about just showing qty and menu item name under the ticket total details rather than a priced breakdown just show the contents of the ticket next to the total.
That would keep things a little cleaner.
Although a full any situation report would be great it is probably unjustifyably complex and time consuming for what is pretty much just so the customer can look and see, oh yes, I remember having that that day rather than how much, how do you come to that amount.
More details breakdown can still be gotten by reprinting the ticket.

[Laura_Viviana_Guille]

@silentbob Could you share the templates with us please?

[croxtonfishbar]

Did you manage to get hold of the a4 template, or have you got one of your own you could share with me?