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;
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.
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’)