/* AccountsTreeScript*/
# -04/02/11 - this sets up transactions_view which can be used directly in OOCalc or Excel
#  based on https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html by marksluser

# this version has 5 levels of accounts, Sluser's is dynamic.

#  there are three routines in addition to this script.
#  Once set up, nothing more needs to be done, the triggers do all the updating, and I can open the
#	spreadsheet and refresh it.  (I have a spreadsheet with two sheets, Balance and Income Stmt).

# Usage - mysql -u user -ppassword <gnucash-database> < AccountsTreeScript
# then in OOCalc, first register the database (File/New/Database/Connect to an existing database???)
#	Once the connection is configured, just open a blank spreadsheet, 
#		Select Data/DataPilot/Start,
#		Select "Data Source registered in OpenOffice.org
#		Select <gnucash-database>.transactions_view
#	Now in the DataPilot wizard, Drag
#		close to Page field
#		yyyy to column field
#		level1,level2 to row fields
#		value_num to Data fields	(actually gets Sum - value_num))
#	    Select OK
#
#	At this point one can deselect unwanted years, level entries, etc.
#		For a Balance sheet, I deselect all level1 except Assets/Liabilities/Networth
#			I also set the data field to do a running sum (in the wizard,
#			select the Sum-value_num, Options, More, Type=Running Sum, Base Field=yyyy).
#			I also hide the unwanted columns (running sum doesn't work if I deselect them).
#		For an Income Statement, I select only Income/Expenses, and select only close=0
#			For this to work, I close all years on 12/31/yyyy with a description of "Closing Entries-yyyy"

 	
#   once set up, no need to call anything, triggers and views do it all - just open the spreadsheet and refresh the data

source AccountsTreeBlank-sql;	#creates a blank accounts_tree and creates triggers which call acct_tree
source AccountsTree-sql;	#creates procedure acct_tree() which populates accounts_tree upon trigger
source acctView-2-sql;		#creates view transactions_view (looks like a table in show tables);