# Portfolio-sql - reports portfolio shares on date
# Usage: call portfolio(start,end,increment,unit);

# based on https://lists.gnucash.org/pipermail/gnucash-user/2010-September/036424.html

DELIMITER go

/********************************************* price function *****************************/
# returns a query that selects two columns: mnemonic, price_on_date
DROP FUNCTION IF EXISTS price;
CREATE FUNCTION price()

RETURNS varchar(2048)
BEGIN
DECLARE sqlStr varchar(2048);


set sqlStr := concat('select 
		x.guid, 
		x.quote, 
		x.closest 
	FROM (SELECT 
		c.guid, 
		ABS(DATEDIFF(p.date,?)) AS closest,'); 
set sqlStr := concat(sqlStr, ' 
		ROUND((CAST(p.value_num AS DECIMAL)/p.value_denom),6) AS quote');
set sqlStr := concat(sqlStr, ' 
	FROM commodities c 
		JOIN prices p ON p.commodity_guid=c.guid');
set sqlStr := concat(sqlStr, ' 
	ORDER BY c.guid, closest) x 
	GROUP BY guid');

RETURN sqlStr;

END;
/*********************************************** shares function ***********************************/
# returns a query that selects four columns: mnemonic, level, shares, cost as of date

DROP FUNCTION IF EXISTS shares;

CREATE FUNCTION shares ()
RETURNS varchar(2048)
BEGIN
DECLARE sqlStr varchar(2048);

set sqlStr := concat('select 
			b.security as mnemonic,
			b.level2 as level, 
			b.shares, 
			b.cost from 
		(select 
			a.security,
			a.level2,
			a.post_date,
			sum(quantity_num) AS shares, 
			sum(value_num) as cost 
		from '); 
set sqlStr := concat(sqlStr,'(select 
#				tv.account_type,
				tv.security,
				tv.level2,
				tv.post_date,
				tv.quantity_num,
				tv.value_num 
			from transactions_view as tv');
set sqlStr := concat(sqlStr, ' 
		where account_type="stock" 
			or account_type = "mutual" 
		order by security,post_date) as a
		where post_date <= ? group by security,post_date with rollup) as b
		where post_date is null');
RETURN sqlStr;

END;

/********************************************** portfolio_shares procedure ******************/
DROP PROCEDURE IF EXISTS portfolio;
CREATE PROCEDURE portfolio(start CHAR(10), end CHAR(10), inc INT, unit char(32))
BEGIN
DROP TABLE IF EXISTS portfolio;
CREATE TABLE portfolio ( mnemonic VARCHAR(2048), ValueDate DATE, price DECIMAL(10,6), shares DECIMAL(20,6), value DECIMAL(10,2), cost DECIMAL(10,2), level VARCHAR(2048));

# this builds the date, mnemonic, shares, cost

	set @sql0 := concat('INSERT INTO portfolio (ValueDate, mnemonic, level, shares, price, cost, value) 
		select 
			?,	
			s.mnemonic,
			s.level,
			s.shares,
			e.quote,
			s.cost,
			ROUND(s.shares*e.quote,2) as value
		 from (', shares(), ') as s');
# this adds prices and value - returns two columns named mnemonic, quote
# new dgh
	set @sql1 := concat('SELECT 
			c.mnemonic,
			d.quote 
		from (', price(),') as d JOIN commodities c ON c.guid=d.guid ');
	set @sql2 := concat(' 
		JOIN (', @sql1, ') as e ON s.mnemonic=e.mnemonic');
	set @sql3 := concat(@sql0, @sql2);
#select @sql3;

	PREPARE s1 from @sql3;

set @d := start;
year_loop:  WHILE (@d<=end) DO

	execute s1 USING @d,@d,@d;

/* increment */

	set @sql := concat('set @d := LAST_DAY(DATE_ADD( @d,  INTERVAL ', inc,' ', unit,'))');
	PREPARE s2 FROM @sql;
	EXECUTE s2; 


END WHILE year_loop;

	DELETE FROM portfolio WHERE shares = 0;
DEALLOCATE PREPARE s1;
DEALLOCATE PREPARE s2;

end;
go
DELIMITER ;