works ok, but so the print all. 1986-1988-1989–> > etc but only 1984 and 1985 are of that account…
I have an idea.
I changed GetStatement Script as follows. What I basically did is including Ticket Tags.
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 @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')
IF @entId > 0 SET @entityId = @entId
--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
, [TicketTags] varchar(max) null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)
INSERT INTO @tbl_tx ([TxDate], [TicketId], [TicketNo], [TicketTags], [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]
,tkt.[TicketTags]
,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], [TicketTags], [Description], [Amount], [Balance] from @tbl_tx ORDER BY [Id] DESC
.and I used internal JSON parser to parse the needed Ticket Tag value.
[LAYOUT]
<L00>Statement TEST
{REPORT SQL DETAILS:@@GetStatement;John Navy:
F.TxDate.desc,F.TicketTags(TN=Payment Info)TV::
<J00>{0}|{1}}
I highlighted the parser part with yellow color. You can replace Payment Info part with your desired tag name. I also hardcoded entity name (John Navy) for testing but you can use related printing tag to parse the Entity Name.
@QMcKay do you think that may work fine?
2 Likes
Very cool @emre!
I did not know we could do that. Rather, I forgot about the JSON Parser feature.
2 Likes
Many Thanks!! This work fine.
EDIT: For add Total I use {SETTING:AS Entity Balance} , but is whitout ,00 . Is possible to add
Use FN() function, something like:
[=FN('{SETTING:AS Entity Balance}')]
You might need to use that in the Formatting section instead, so please show your full Template. For example, it might be like:
<J00>{0}|{1}|[=FN('{2}')]}
[LAYOUT]
<L00> Data |R.N°|Euro
<F>-
{REPORT SQL DETAILS:@@GetStatement;Customers:
F.TxDate.desc,F.TicketTags(TN=Rievuta Fiscale No)TV,F.Amount,,::
<L00>{0}|{1}|{2}
}
<F>=
<EB>
<L00>TOTALE: {SETTING:AS Entity Balance} EURO
<DB>
<C11>
{ENTITIES}
[ENTITIES:Customer]
-- Customer entity format
<C00>{ENTITY DATA:Intestazione}
<C00>{ENTITY DATA:Indirizzo}
<C00>{ENTITY DATA:Città-Cap-Provincia}
<C00>P.Iva :{ENTITY DATA:Partita Iva}
So either of these should work:
<L00>TOTALE: [=FN('{SETTING:AS Entity Balance}')] EURO
<L00>TOTALE: [=F('{SETTING:AS Entity Balance}','0,00')] EURO
1 Like
I don’t think so…
try
<L00>TOTALE: [=F('{SETTING:AS Entity Balance}','0.00')] EURO
fyi: comma , is used to define thousand separator.
1 Like
this work…Once again thanks a lot.
I have one more thing what’s popping in my head but I can’t do …
when a customer pays his debt I need to clean up your transactions, there is an action that let me?
transactions related to that customer
No. You should keep them for reporting.
While printing account statements you can print transactions only after last zero balance date. See this topic for more information.
2 Likes
And if I us a task… like clear database, but one for any customer? Is possible?
Nearly anything is possible. The real question is: why do you want to do this? You should not be deleting transactions.
Basically once the customer has paid and I printed out a receipt in duplicate are in compliance with the law. And then when the customer returns should create a second ex: Jonh Bosh 2 … because without clearing the transaction printing even those already paid …
So deleting all data is compliant with law? That doesnt sound right to me.
That is not a good reason, sorry.
As Emre mentioned, you should use an Account Statement and/or Reporting to filter which Transactions are printed instead of using the default Account Details screen to print.
Look at the Tutorial linked by Emre. It supports showing/printing Transactions based on Last Zero Balance Date, or even a manually set Start Date.
Ok I try with AS Data filter…
I don’t understand the question/problem.
But your Report syntax does not look correct. You are not using any Report Tags…
1 Like