Customer Account Statements and Payments (Custom)


#102

Try adding highlighted part after INSERT INTO line.

SELECT
 @dateFilterBeg as [Date]
,0 as [TicketId]
,''
,'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

#103

This works perfectly when the date filter has been set but it breaks the balance when no filter has been set which can happen occasionally.

I have tried this but I cant seem to get it to work.
Basically it is

    IF  @dateFilterBeg = '2000-01-01'
    BEGIN   
         "Default query"
    END
        ELSE
        "updated Query with Union"

Here my attempt.

IF  @dateFilterBeg = '2000-01-01'
 BEGIN
 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
END

ELSE

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

FROM [AccountTransactionValues] tv
WHERE 1=2
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

Not sure if this is the best method of achieving this?


#104

Try using a CASE statement. The IF statement is “weird” …

    CASE  @dateFilterBeg = '2000-01-01'
    WHEN '2000-01-01'
    BEGIN   
         "Default query"
    END

    ELSE

    BEGIN   
        "updated Query with Union"
    END

#105

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

#106

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.


#107

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?


#108

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

#109

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?


#110

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.


#111

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…


#112

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.


#113

Well m trying and but I need some guidance.

Thanks


#114

It’s been a hard 6 months of learning for me, but as @kendash 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.


#115

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.


#116

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?


#117

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


#118

@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??


#119

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.


#120

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