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?
Very cool @emre!
I did not know we could do that. Rather, I forgot about the JSON Parser feature.
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
I don’t think so…
try
<L00>TOTALE: [=F('{SETTING:AS Entity Balance}','0.00')] EURO
fyi: comma , is used to define thousand separator.
this work…Once again thanks a lot.
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.
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…