Customer Account Statements and Payments (Custom)

[silentbob]

When Using the Pay Account from the Account Statement Entity screen.
When Printing a reciept how can I have the tendered amount displayed?
I have tried {TENDERED TOTAL} {AMOUNT} {TARGET AMOUNT} {TARGET CREDIT} {TARGET DEBIT} {SOURCE AMOUNT} {SOURCE CREDIT} {SOURCE DEBIT} {TENDERED TOTAL} {PAYMENT TOTAL}

[QMcKay]

You could create a Template for that, then use the Print Account Transactions Document action, and supply the DocId from the Report.

[silentbob]

The printing seems to have stopped working since the 5.1.58 update.
{REPORT SQL DETAILS:@@GetStatement: F.Date.desc,F.TicketId,F.TicketNo,F.Desc,F.Amount,F.Balance:: <J00>{0}| {1}| {2}| {3}| {4}| {5} returns no results.
The report runs fine

But when I print it does not give any transaction data

I thought it may be a template issue (I’m using the document printer) but I changed it to a windows printer and used the template provided here and no change

Has anyone else noticed this or is it just my setup?

QMcKay are you still using this setup? I have tried using the REPORT SQL DETAILS: tag to call the results from other queries and they are working fine but for some reason the @@GetStatement: will not work. If you still have this could you check to see if its working on your setup

[QMcKay]

I do use it for display, but I never actually use it for printing. I will check to see if it still works for printing…

EDIT: broken…

[silentbob]

At least its not just me. I have been trying to troubleshoot this all day. I thought the REPORT SQL DETAILS tag was broken in the latest update but it works fine for other queries

It was working fine the last time I checked back in February. I only recently updated to 5.1.58 so I am presuming it has something to do with this update.

[QMcKay]

Ok, here is the fix.

It has to do with the name of one of the columns in the SQL, and in the Template. Specifically, I believe it is the field named [Desc]. I think the parsing of the Template is confused when it sees Desc, since that is a reserved word. So we need to change it…

I changed in the SQL every occurance of [Desc] with [Description].
I also changed [Date] with [TxDate] just for good measure.

The Template fields must match the names of the SQL fields being returned, so I modified the field names to match the SQL names:

[ENTITY]
<T>ACCOUNT STATEMENT
<J00>Customer: {ENTITY NAME}|Account: {ACCOUNT NAME}
<J00>Tx Since: {SETTING:AS Date Filter Beg}|Balance: {ENTITY BALANCE}
<F>=
<J00>Date| TID| TNo| Description| Amount| Balance
<F>-
{REPORT SQL DETAILS:@@GetStatement:
F.TxDate.desc,F.TicketId,F.TicketNo,F.Description,F.Amount,F.Balance::
<J00>{0}| {1}| {2}| {3}| {4}| {5}
}
<F>=

And the matching SQL:

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
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)

INSERT INTO @tbl_tx ([TxDate], [TicketId], [TicketNo], [Description], [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 [TxDate] FROM @tbl_tx WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
SET @i = @i + 1
END

select [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] from @tbl_tx ORDER BY [Id] DESC

[silentbob]

That did the trick. Thanks for your help!

[Eliel_Lop]

is no way to do this with one click

[Shivan]

There is using a configuration task. But it needs to be created. It wasn’t created because if people follow every step in the tutorial they would have a better understanding what’s been implemented, and in return have better knowledge on how things run in samba, thus allowing each user to use that knowledge to customize it to their own preference.

Bare in mind this tutorial was created based on the Author’s version, thus what you are actually implementing is the Author’s version(Which is perfectly fine if it suits your needs also). For most people, if they did it in one click, then suddenly they wanted to add or remove a few things from it, there would be sooo many questions regarding how this or that is done, and wouldn’t really know how it all connects together.

Step by step allows the knowledge of exactly that :D. And it also relieves the author from the mountain of questions people would have regarding the tutorial if it was done in one click.

[Jesse]

following tutorials should be the main way to configure Sambapos.

[inaleemz]

Can you please provide a script for this or an updated version including all this functions? Thank you in advance.

[JTRTech]

Q posted the SQL and sample report…

Customer Account Statements and Payments (Custom)

Ok, here is the fix. It has to do with the name of one of the columns in the SQL, and in the Template. Specifically, I believe it is the field named [Desc]. I think the parsing of the Template is confused when it sees Desc, since that is a reserved word. So we need to change it… I changed in the SQL every occurance of [Desc] with [Description]. I also changed [Date] with [TxDate] just for good measure. The Template fields must match the names of the SQL fields being returned, so I modifie…

[exit_inn]

I’ve been following along the tutorial just fine, but I can’t for the life of me figure out how to add multiple instances of the same action in a Rule. Can anybody help?

[silentbob]

I posted this above

Customer Account Statements and Payments (Custom)

For anyone wondering how to add an action multiple times like AS Store Value in this tutorial. Just add the action then move back to Action List, right click and click Display All Values as many times as required. I have just discovered this so im not sure if its the correct way to do this. [image]

[GreatShakesBar]

Have got as far as creating the Entity Screen, ive obviously done something wrong but have gone over it a couple of times and cant work itout.

So To get the entity screen I need to edit and make widgets, I’m selecting a customer, clicking Account Operations and it takes me to a blank screen… however I cannot do anything with it because it is totally blank, no buttons or anything. If I right click its all greyed out.

[QMcKay]

@GreatShakesBar

If I right click its all greyed out.

You must be logged in as Admin to enable Design Mode. And sometimes, I have seen that I needed to shutdown SambaPOS, restart it, and login as admin to get the Design Mode to enable.

[GreatShakesBar]

I tried that, take note in the pic, not even the Main Menu button bottom right.

I cant shrink the window or do anything, only way to close is ctrl alt and del

ive done something wrong blatently…

[Jesse]

Was it correctly set as Custom Entity screen under Entity Screen Type? Remember its hard for us to know what you did wrong because well we dont know and all you have given us to work with is.

@GreatShakesBar

So To get the entity screen I need to edit and make widgets, I’m selecting a customer, clicking Account Operations and it takes me to a blank screen… however I cannot do anything with it because it is totally blank, no buttons or anything. If I right click its all greyed out.

Go back through and compare your setup to his tutorial be sure you didnt miss a step.

[GreatShakesBar]

yeah its correct what you put.

I agree I didn’t give you much, but I dunno either lol.

I’m away to cornwall for a few days now so will have to look when I get back.

Thanks though

Matt

[Jesse]

It sounds to me like its loading an entity screen but the parameter for it is wrong so its coming up blank.

I mean its switching to Entity Screen but its not loading anything.

Look here closely: Pay attention to Command Parameter.

Customer Account Statements and Payments (Custom)

Automation Commands These are the Automation Commands we will use. Only 1 of them has a Mapping. [image] Account Operations [image] Load Account Statement [image] Print Account Statement [image] Pay Account [image] Customer Account Screen - button addition [image]