<?
$yrb=substr(NOW(),0,4)-2;
$yre=substr(NOW(),0,4);
$yr=$yre-1;

ExecuteSQL(~create temporary table tmpP as select CaseID,rValue as Property from Responses where qID=`CaseFirstName` and isDeleted=0 and CaseID in (select CaseID from Responses where qID=`CaseType` and rValue=`P`)~);

//ShowQueryResult(`select * from tmpP`);

ExecuteSQL(~create temporary table tmpTT as select tValue, tText from ResponseType where tID=`RE_X_Cat`~);

//ShowQueryResult(`select * from tmpTT`);

ExecuteSQL(~create temporary table tmpI as select CaseID, rInstance, rValue as ExpDate, substring(rValue,1,4) as Eyear from Responses where qID=`RE_X_Date` and isDeleted=0 ~);

//ShowQueryResult(`select * from tmpI`);

ExecuteSQL(~create temporary table tmpA as SELECT Responses.CaseID, Responses.rInstance, rValue as Amount FROM Responses,tmpI WHERE qID=`RE_X_Amount` and isDeleted=0 and Responses.rInstance=tmpI.rInstance~);

//ShowQueryResult(`select * from tmpA order by CaseID,Amount,rInstance`);

ExecuteSQL(~create temporary table tmpM as SELECT distinct tmpP.CaseID, Property, Amount, Eyear, tmpI.rInstance FROM tmpP, tmpI, tmpA where tmpP.CaseID=tmpA.CaseId and tmpI.rInstance=tmpA.rInstance ~);

//ShowQueryResult(`select * from tmpM where Property=~777 Shade Tree Rd~ and EYear=~2007~ order by CaseID,Amount,rInstance`);

ExecuteSQL(~create temporary table tmpR as SELECT Property,  cast(sum(Amount) as decimal(8,2)) as Revenue, Eyear FROM tmpM where Amount<0 group by Property, Eyear ~);

//ShowQueryResult(`select * from tmpR order by Property, Eyear`);

ExecuteSQL(~create temporary table tmpE as SELECT Property,  cast(sum(Amount) as decimal(8,2)) as Expense, Eyear FROM tmpM where Amount>0 group by Property, Eyear ~);

//ShowQueryResult(`select * from tmpE order by Property, Eyear`);

ExecuteSQL(~create temporary table tmpRE as SELECT tmpR.Property, Revenue, Expense, Revenue+Expense as Net, tmpR.Eyear FROM tmpR left join tmpE on tmpR.Property=tmpE.Property and tmpR.Eyear=tmpE.Eyear ~);

ShowQueryResult(`select * from tmpRE order by Property, Eyear`);

echo `<br><p>`;
?>
