/* 4/2/11 - acctView-2-sql */
#  based on https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html
#  this creates the view - doesn't need to be called

CREATE OR REPLACE VIEW transactions_view AS 
	SELECT accounts.account_type,
		accounts_tree.dummy AS Tree,
		accounts_tree.level0,
		accounts_tree.level1,
		accounts_tree.level2,
		accounts_tree.level3,
		accounts_tree.level4,
		accounts_tree.level5,

		CAST(transactions.post_date AS date) AS post_date,
		CAST(transactions.enter_date AS date) AS enter_date,
		CAST(substring(transactions.post_date FROM 1 FOR 4) AS SIGNED) AS  yyyy,
		CAST(substring(transactions.post_date FROM 6 FOR 2) AS SIGNED) AS  mmm,
		CAST(substring(transactions.post_date FROM 9 FOR 2) AS SIGNED) AS  ddd,

		if(left(transactions.description,15)="Closing Entries",1,0) AS close,

		accounts.name,
		transactions.num,
		transactions.description,
		splits.memo,
		CAST(splits.quantity_num AS decimal)/splits.quantity_denom AS  quantity_num,
		CAST(splits.value_num AS decimal)/splits.value_denom AS value_num
		,accounts.guid AS acct_guid
		,transactions.guid AS tx_guid
		,slots.name AS tax
		,commodities.mnemonic AS Security
	FROM
	   accounts,
	   accounts_tree,
	   commodities,
	   splits left join slots on (slots.obj_guid=splits.account_guid AND slots.name='tax-related'),
	   transactions
	WHERE
	   splits.account_guid = accounts.guid AND
	   splits.account_guid = accounts_tree.guid AND
	   commodities.guid=accounts.commodity_guid AND
	   splits.tx_guid = transactions.guid;