Customer Account Statements and Payments (Custom)

I have the IF or CASE after the INSERT INTO statement, moving it outside this seems to have worked.

Here is the updated @@Get Statement Account Statement Script if anyone needs it

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

IF @dateFilterBeg = '2000-01-01'

BEGIN
INSERT INTO @tbl_tx ([Date], [TicketId], [TicketNo], [Description], [Amount], [Balance])
--SELECT TOP 1 @entityId as [Date], @entityName as [TicketId], ' ' as [TicketNo], @accountId as [Description], @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 - ' + tx.[Name]
  WHEN [Debit] > 0 Then 'Purchase'
 END as [Description]
,[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
END

ELSE

BEGIN
INSERT INTO @tbl_tx ([Date], [TicketId], [TicketNo], [Description], [Amount], [Balance])

SELECT
 @dateFilterBeg as [Date]
,''
,''
,'Balance Brought forward'
,SUM([Debit])-SUM([Credit]) as [Amount]
,0 as [Balance]

FROM [AccountTransactionValues] tv
WHERE 1=1
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Id] = @entityId)
AND tv.[Date] < @dateFilterBeg

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 - ' + tx.[Name]
  WHEN [Debit] > 0 Then 'Purchase'
 END as [Description]
,[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
END

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], [Description], [Amount], [Balance] from @tbl_tx 
ORDER BY [Date] ASC
3 Likes

QmcKay, I’m not too sure what exactly is happening here but it it possible to set it that if it cannot find a balance at @dateFilterBeg then to assume balance as 0

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

The issue I’m having is that when I send a statement at the end of the month, Eg.Date filter set at 2017-04-01 but a customer did not purchase something until the 5th of this month then the balance does not calculate. Simply removing the filter fixes this, but I am trying to further automate this.

Basically I want to try looping automation to email a statement to every customer (I’m not even sure this can be done. I have never used looping features of samba) At the moment some of these statements will not calculate the balance if the filter has been applied and they have only made their purchases at a later date.

What that SQL Loop does is sum the Transaction Amounts in the Temp Table named @tbl_tx.

Of course, the records in @tbl_tx have been filtered to only include Transactions made on or after @dateFilterBeg.

So there is a problem for sure, because it will report the incorrect Balance of the Account.

I guess, we should really not filter @tbl_tx to begin with, so that we get correct Balance Reporting, first.

Then we can reload @tbl_tx with filtered Transactions, and include a ‘Balance Brought Forward’ amount which becomes part of the current Balance, no matter what @dateFilterBeg is set to, similar to SambaPOS Account Screens. Make sense?

This seems to work well …

declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()
declare @entName varchar(255) = '@1'
declare @entId int = 0

SET @dateFilterBeg = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Date Filter Beg')

SET @entId = (SELECT isnull([Id],0) FROM [Entities] WHERE [Name]=@entName)

IF @dateFilterBeg = ''
 BEGIN
  SET @dateFilterBeg = '2000-01-01'
END

SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
IF @entId > 0 SET @entityId = @entId


declare @txcount int = 0
declare @i int = 1
declare @balance decimal(7,2) = 0.00
declare @lastZeroDate varchar(50) = '2000-01-01'

declare @tbl_tx_all 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
)

declare @tbl_tx_filtered 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_all ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
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)

ORDER BY [Date] ASC


-- get count of records
SELECT @txcount = count([Id]) FROM @tbl_tx_all


-- loop all records to set Last 0 Balance Date
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
--IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [TxDate] FROM @tbl_tx_filtered WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
IF @balance = 0 SET @lastZeroDate = replace((SELECT convert(varchar(50),[TxDate],126) FROM @tbl_tx_all WHERE [Id]=@i),'T',' ')
SET @i = @i + 1
END

UPDATE [ProgramSettingValues] SET [Value]=@lastZeroDate WHERE [Name]='AS Entity Date_ZeroBalance'
--SELECT isnull(max([TxDate]),'2000-01-01') FROM @tbl_tx_all WHERE [Balance] = 0.00
--print '------------------------------------------- ' + @lastZeroDate

-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 1

WHILE @i<=@txcount
BEGIN
IF @dateFilterBeg > (SELECT [TxDate] FROM @tbl_tx_all WHERE [Id]=@i)
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
END
SET @i = @i + 1
END


-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([TxDate]) FROM @tbl_tx_all)
BEGIN
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT @dateFilterBeg, 0, 0, 'Balance Brought Forward', @balance, @balance
END

-- insert Filtered Transactions
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] FROM @tbl_tx_all WHERE [TxDate] >= @dateFilterBeg


-- loop records to use filtered amounts and Balance Brought Forward
SELECT @txcount = count([Id]) FROM @tbl_tx_filtered
SET @i=1
SET @balance = 0.00

WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_filtered WHERE [Id]=@i)
UPDATE @tbl_tx_filtered SET [Balance] = @balance WHERE [Id]=@i
SET @i = @i + 1
END

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

That works perfect thank you.
Any tips for having this automatically print or email for all entities with open balances? Or are there any tutorials that demonstrate looping automation?

There are some Tutorials that use Loop Value Action and Value Looped Event (Rule).

Basically…

  • store a comma-separated list of IDs

  • then you fire the Loop Value Action which picks out each ID from the list, one by one and triggers the Value Looped event for each ID in the list.

  • then you capture the Value Looped Event which will contain a single ID, and perform some Action using that ID, like Printing, Sending Email, etc.

There is a quick setup here to Print all Unpaid/Open Tickets … it uses the Ticket List Screen, and when you fire an Automation Command from that screen, the [:CommandValue] will be populated with a comma-separated list of selected TicketIds - that is our list.

So we fire Loop Values action using the [:CommandValue] as our Value-list.

Then we capture Value Looped Event where we fire Execute Print Job action to Print a Ticket using the [:LoopValue] which is a single TicketId.

There are other Tutorials, but having trouble locating them.

2 Likes

Hello

Do you have any tutorial for Supplier Accounts?
For detailed report and printing. Actually I tried so many tutorials but messed up with actions and rules.
Plz guide me…

I recommend you keep practicing tutorials and get familiar with rules and actions. You will never be able to really customise it if you don’t.

1 Like

Well m trying and but I need some guidance.

Thanks

It’s been a hard 6 months of learning for me, but as @Jesse says, over time you will understand a lot more about how SambaPOS works.

Think of it more as a framework, rather than a software product. You won’t find an option for “enable customer account transaction tracking” or similar. Instead, you need to build such functionality yourself.

You do this mainly using:

  • Automation commands
  • Rules
  • Actions

But also you sometime use Custom Reports to present information to you and Entity Screens to layout a series of reports and or action commands (buttons).

When Emre built SambaPOS he included a bunch of events that we can hook in to. For example when a payment is made a Payment Processed event happens. You can create a rule to fire when this event happens and the rule you create can fire an action. This action can then do something in SambaPOS - It could record something against the customer’s record, or it could do something simple like creating a pop-up message to remind the operator about something.

These automation functions are all explained better by others elsewhere, but start with an easier tutorial such as allowing a miscellaneous product at a custom price and you will start to see how these things work.

No, I don’t.

The Tutorial is guidance. You just need to change some things to make it work for Supplier Entities and Supplier Accounts.

Yes I read the whole tutorial but then I realised it I can’t make a ticket for supplier because supplier transaction is not connected to ticket, this whole process is different than customer.
And yes @QMcKay first I exactly think the same that I can change only customer entities to supplier entity but later on I realised that supplier entities are not connected to ticket.
Am I right or wrong?

Thanks mjb2000 for your help.
Actually I put my eeffortsffort on everything like action, rules & automation command.
First I created automation command and insert it in accounts screen with supplier accounts, then I created two actions.
First to execute print and I selected the supplier print job which I already created with template and printer.
Second to print entity in which I selected supplier entity.
Then I created a rule in which I put the automation command equals to print(I.e automation command name).
And then I selected the two actions in this rule which I already created i.e execute command and print entity and then I went to accounts screen and click print but the print I got was the same template no information :’(
All this was my thought and I implemented it and then I came to forum you people need to understand :’( @QMcKay
I implemented sambapos in a restaurant with kitchen screen, different departments, entity screens. And now m implementing sambapos in a small store for retail.

Thank you all

1 Like

@JTRTech
check this out, I wanted this…
as you see in this tutorial it have customer account statement and in many templates it have ticket as a transaction.
But for supplier when we are receiving items from warehouse can we make transaction as a ticket??

Is there a tutorial in general accounts templates, formatting and linking them to printers of my choice and so forth? I have a hard time getting anything else other than the seems to be hard-code print button and print format with the accounts section.

Hi guys; Is it available to print items summary including in each statement or not?

Hi, i have this done correct but, i encounterd one issue, when i debit the first customer with a bill, i cant settle the next customer or the same customer because the customer account settle button becomes inactive unless i clear the balance to zero in the accounts. i want a situation where i can settle and pile all their bills for a month and clear their individual accounts as submit their payments.

Don’t duplicate questions, doesnt get you more answers.

1 Like

How to sort transactions by date and start from only remaining balances ? not all and random dates confuseing customers my template as below

{REPORT SQL DETAILS:
  	select Date,
  	CASE
  	  When Credit > 0 Then 'Payment'
  	  When Debit > 0 Then 'Invoice'
  	END as Name,
  	Debit-Credit as Amount from AccountTransactionValues
	Where AccountId in (select top 1 AccountId from Entities where Name = '{ENTITY NAME}')
	:F.Date,F.Name,F.Amount::<J00>{0}      {1}|${2}
}

screen shot

This should order it by date in descending order

{REPORT SQL DETAILS:
  	select Date,
  	CASE
  	  When Credit > 0 Then 'Payment'
  	  When Debit > 0 Then 'Invoice'
  	END as Name,
  	Debit-Credit as Amount from AccountTransactionValues
	Where AccountId in (select top 1 AccountId from Entities where Name = '{ENTITY NAME}') ORDER BY Date DESC
	:F.Date,F.Name,F.Amount::<J00>{0}      {1}|${2}
}
2 Likes