Few scripts that might help when dealing with Accounts

This isn’t really a tutorial, just thought I’d offer a few ‘starter’ scripts to help others.
One of the KEY factors of these is that they use SQL to access the account transactions so are not bound by work period like {ACCOUNT TOTAL:xxxx} also where you may need to call the figure but not have access to {ENTITY BALANCE}.
Not that there is obviously £ and 2 decimal places set to the values but these can obviously be changed as needed.

Current balance of an account;

function balancenow(AccountName) {
  var balance = sql.Query("SELECT sum(t.[Debit])-sum(t.[Credit]) AS [Balance] FROM [AccountTransactionValues] t JOIN [Accounts] a on a.[Id] = t.[AccountId] WHERE a.[Name] = '"+AccountName+"'").First;
  return '£'+parseFloat(balance*-1).toFixed(2);
}

Total credits to an account;

function credits(AccountName) {
  var credits = sql.Query("SELECT sum(t.[Credit]) AS [Balance] FROM [AccountTransactionValues] t JOIN [Accounts] a on a.[Id] = t.[AccountId] WHERE a.[Name] = '"+AccountName+"'").First;
  return '£'+parseFloat(credits).toFixed(2);
}

Total Debits to an account;

function debits(AccountName) {
  var debits = sql.Query("SELECT sum(t.[Debit]) AS [Balance] FROM [AccountTransactionValues] t JOIN [Accounts] a on a.[Id] = t.[AccountId] WHERE a.[Name] = '"+AccountName+"'").First;
  return '£'+parseFloat(debits).toFixed(2);
}

I used these in my printer template to setup a voucher summary section on the bottom of voucher receipts;

This is the summary section of the template;

<F>=
<C11>Gift Voucher Summary
<C10>Voucher Number: {ENTITY NAME:Gift Vouchers}
<C00>Customer Name: {ENTITY DATA:Gift Vouchers:Customer Name}
<J00>Credits: {CALL:accountinfo.credits({ENTITY NAME:Gift Vouchers})} |Debits: {CALL:accountinfo.debits({ENTITY NAME:Gift Vouchers})}
<C10>Remaining Balance: {CALL:accountinfo.balancenow({ENTITY NAME:Gift Vouchers})}
<EC>
<BAR>{ENTITY NAME:Gift Vouchers}
<EL>
<C00>VC: {ENTITY NAME:Gift Vouchers}
<F>=
5 Likes

I’m trying to use your scripts to handle float in and balance float, amount and description working on it
i have a question: Can I point at account name instead of entity ?

This gets me the Float In Description and Name
SELECT TOP (1)
[Amount] AS AMOUNT
,[Name] AS “DESCRIPTION”
FROM [SambaPOS5].[dbo].[AccountTransactions]
WHERE [AccountTransactionTypeId] = 10
ORDER BY [Id] DESC

And this one get me Description and name From Balance Float:
SELECT TOP (1)
[Amount] AS AMOUNT
,[Name] AS “DESCRIPTION”
FROM [SambaPOS5].[dbo].[AccountTransactions]
WHERE [AccountTransactionTypeId] = 11
ORDER BY [Id] DESC

I’ll be nice to know how to format this in to a report or a jscript lolz

In script you just use your SQL as a string and use that in the SQL helper call.

From what I remember the queries go straight to accounts which are not specific to entities it’s just using the entity name to specify the account, an alternative account can be called exactly the same just using the account name whatever it is.

Based on your Scripts I build this 2 cause i am very noob at jscripting Lol
Balance Float Account ID = 11 in my DB

To get Balance Float Description:

To get Balance Float Amount:

They seem to work as i get the last input from the table
i’ll try to make the template work

the results doesnt work

CB

Do you have an example of helper that I can see so i can build the helper for the script ? @JTRTech

I’m no expert but isn’t there the @@ missing from the call function? Shouldn’t it be {CALL:@@bfamount.bfamount}?

1 Like

No, that’s is you use the pure SQL only method, this above is jscript with SQL function inside.
@Laura_Viviana_Guille your already using it…
SQL.Query(‘query string or variable containing string’)

You are correct that the Handler Name in the call does not match the Handler for the Script. However…

Don’t do that for JSscript.

Remove the @@ from your JScript Handler Names.

JScript Scripts should not being using the @@ prefix.

The @@ prefix should only be used for pure SQL Scripts.


@Laura_Viviana_Guille, you can (and should) combine the 2 Functions into a single Script. So you have a single Handler Name with multiple Functions.

2 Likes

I remove them, the script does work, but i can’t make it print anything on printer template, but i can in a custom report problem solved XD