Custom .scm script: Query account balance

[John Ralls]

> On Oct 5, 2016, at 1:35 AM, Eric Wheeler <gnucash-devel at lists.ewheeler.net> wrote:
> 
> On Tue, 4 Oct 2016, Derek Atkins wrote:
> 
>> Hi,
>> 
>> Eric Wheeler <gnucash-devel at lists.ewheeler.net> writes:
>> 
>>> Hello all,
>>> 
>>> I would like to update our invoice .scm template to include a balance from 
>>> an account containing the customer's name (this is separate from any 
>>> accounts receivable, it is a retainer liability account).
>>> 
>>> I tried digging through the account piechart.scm and the taxinvoice.scm to 
>>> see if I can come up with a way to query the account something like this:
>>> 
>>> gnc:account-get-comm-balance-at-date append("Liabilities::Retainers::" coyname)
>> 
>> First, you probably don't need the double-colons.
>> 
>>> But I'm not sure how to pass today's date. It's been years since I've 
>>> written scheme so any help would be greatly appreciated!
>> 
>> (current-time) ??   Or perhaps (localtime (current-time)) -- depending
>> on what you need.
> 
> I'm still trying to get to the point where I can specify an account 
> (hard-coded) by name in my report to get that account's balance.
> 
> I can map all of numerical values for balanaces, but they report all 0s 
> for all accounts. This makes me think that "current-time" is being 
> evaluated as the epoch, perhaps because it is an invalid data type for the 
> gnc:account-get-balance-at-date function.
> 
> Do you know how the gnc:account-get-balance-at-date function expects date 
> to be represented?
> 
> (display (append
>    (map
>        (lambda (x) (gnc:gnc-numeric-num (gnc:account-get-balance-at-date x (current-time) 0)))
>        (gnc-account-get-descendants-sorted (gnc-get-current-root-account))
>    ))
> )
> 


It's calling gnc:account-get-comm-balance-at-date (src/report/report-system/report-utilities.scm:462) which in turn sets the date in the query with xaccQueryAddDateMatchTS (ibid:489). That "TS" on the end means it's looking for a timespec, a struct containing an int64_t of seconds since the epoch and an int32_t of nanoseconds (always 0 in GnuCash, so we're working slowly on replacing timespecs with just an int64_t of seconds). You'll find functions for creating timespecs in src/app-utils/date-utilities.scm.

Regards,
John Ralls