/* Script to output nonhidden expense bucket balances.
   Aggregates amounts from ztransaction and zmoneyflow tables into a temporary table to 
   simplify calculation of the balance.  Temporary table is deleted upon completion.*/

-- some output formatting
.mode column
.width 27 8 

-- create a temporary table and seed it with nonpending transactions from nonhidden buckets beginning from original cash flow start date
create temporary table transactions as 
select zb.z_pk as pk, zb.zname as name, zt.zamount as amount from zbucket zb
left join ztransaction zt on zb.z_pk = zt.zbucket 
where zb.zishidden = 0 and zb.ztype = 2 
and zt.zispending = 0
and zt.zdate >= (select zdatein from zrollover where zisinitialrollover = 1);

-- insert moneyflows into buckets
insert into transactions 
select zb.z_pk as pk, zb.zname as name, zm.zamount as amount from zbucket zb
left join zmoneyflow zm on zb.z_pk = zm.zbucketin 
where zb.zishidden = 0 and zb.ztype = 2 
and zm.zdate >= (select zdatein from zrollover where zisinitialrollover = 1);

-- insert moneyflows out of buckets, subtracting from zero to negate numbers (for later sum() function)
insert into transactions 
select zb.z_pk as pk, zb.zname as name, 0-zm.zamount as amount from zbucket zb
left join zmoneyflow zm on zb.z_pk = zm.zbucketout 
where zb.zishidden = 0 and zb.ztype = 2 
--and datetime(zm.ZDATE,'unixepoch','31 years') >= '2008-03-01 12:00:00';
and zm.zdate >= (select zdatein from zrollover where zisinitialrollover = 1);

-- output summary of buckets and their balances
select name, round(sum(amount),2) from transactions group by pk order by name;

-- remove the temporary table 'if exists' just for good measure

-- exit sqlite3
.exit
