Custom .scm script: Query account balance

Eric Wheeler gnucash-devel at lists.ewheeler.net
Wed Oct 12 14:25:21 EDT 2016


On Wed, 5 Oct 2016, John Ralls wrote:
> 
> > 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.

Thank you everyone for your help getting balanaces from an account for our 
invoice report!

These are the defines that wrote to simplify the implementation in case it helps anyone else:

(define (account-exists name)
	(let* (
			(accts
				(gnc-account-get-descendants-sorted (gnc-get-current-root-account))
			)
		)
		(not
			(=
				(length
					(filter
						(lambda (acct) (string=? name (gnc-account-get-full-name acct)))
						accts
					)
				)
				0
			)
		)
	)
)

(define (get-account-balance acct)
	(let* (
			(bal 
				(gnc:account-get-balance-at-date
					acct 
					(gnc:date->timepair (localtime (current-time)))
					0
				)
			)
			(num (gnc:gnc-numeric-num bal))
			(denom (gnc:gnc-numeric-denom bal))
		)
		(/ num denom 1.0)
	)
)

(define (get-account-balance-by-name name)
	(let* (
			(accts
				(gnc-account-get-descendants-sorted (gnc-get-current-root-account))
			)
			(balpair
				(map
					(lambda (acct) (get-account-balance acct))
					(filter
						(lambda (acct) (string=? name (gnc-account-get-full-name acct)))
						accts
					)
				)
			)
		)
		(car balpair)
	)
)

----------------------

<?scm
(let* (
	(retaineracct (string-append "Liabilities:Retainers:" ownername))
	)
	(if (account-exists retaineracct)
		(let* (
				(bal (get-account-balance-by-name retaineracct))
				(bal2
					(if (and (> bal -0.01) (<= bal 0.0))
						0
						(* -1 bal)
					)
				)
			)
			(begin ?>
				$<?scm:d (format #f "~,2f" bal2) ?>
				remains on account as of 
				<?scm:d (gnc-print-date (gnc:date->timepair (localtime (current-time)))) ?>.
				<?scm
			)
		)
		(begin ?>
			<b>No retainer on account.</b>
			<?scm
		)
	)
)
?>

> 
> Regards,
> John Ralls
> 
> 


More information about the gnucash-devel mailing list