<?
$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 from Responses where qID=`RE_X_Date` and isDeleted=0 and rValue>`$yrb-12-31` and rValue<`$yre-01-01`~);

//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 rValue>0 and Responses.rInstance=tmpI.rInstance~);

//ShowQueryResult(`select * from tmpA order by CaseID,Amount,rInstance`);
//ShowQueryResult(`select CaseID,rInstance,count(*) as Freq from tmpA group by CaseID,rInstance order by Freq desc`);
//echo `tot=`,ShowQueryResultScalar(~SELECT cast(sum(Amount) as decimal(8,2)) from tmpA~);

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

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

ExecuteSQL(~create temporary table tmpT as SELECT Responses.CaseId, Responses.rInstance, rValue as Type FROM Responses,tmpI WHERE qID=`RE_X_Category` and isDeleted=0 and Responses.rInstance=tmpI.rInstance~);
//ShowQueryResult(`select * from tmpT`);

ExecuteSQL(~create temporary table tmpS as SELECT tmpT.CaseID, Property, -cast(sum(Amount) as decimal(8,2)) as Sell FROM tmpT,tmpR,tmpTT,tmpP WHERE tText=`Sell` and tmpT.CaseId=tmpP.CaseId and tmpT.rInstance=tmpR.rInstance and tValue=Type group by tmpT.CaseId~);
//ShowQueryResult(~select * from tmpS~);
$tbuy=ShowQueryResultScalar(~select tValue from ResponseType where tText=`Buy`~);
//echo ~tbuy=$tbuy~;
ExecuteSQL(~create temporary table tmpBI as select CaseID, rInstance from Responses where qID=`RE_X_Category` and isDeleted=0 and rValue=`$tbuy`~);
//ShowQueryResult(~select * from tmpBI~);
ExecuteSQL(~create temporary table tmpB as SELECT tmpBI.CaseID, cast(sum(rValue) as decimal(8,2)) as Buy FROM tmpBI, Responses WHERE qID=`RE_X_Amount` and tmpBI.CaseId=Responses.CaseId and tmpBI.rInstance=Responses.rInstance group by tmpBI.CaseId~);
//ShowQueryResult(~select * from tmpB~);

ExecuteSQL(~create temporary table tmpTR as SELECT -cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpR,tmpTT,tmpP WHERE tText not in (`Buy`,`Sell`) and tmpT.CaseId=tmpP.CaseId and tmpT.rInstance=tmpR.rInstance and tValue=Type union SELECT sum(cast(Sell-Buy as decimal(8,2))) as Total FROM tmpS,tmpB WHERE tmpS.CaseID=tmpB.CaseID~);

echo `<center><table border=0><tr><td>`;

echo ~<h1>Total Expenses - $yr</h1>~;
ShowQueryResult(~SELECT cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT WHERE tText not in (`Buy`,`Sell`) and tmpT.rInstance=tmpA.rInstance and tValue=Type~);

echo `</td><td>`;

echo ~<h1>Total Revenue - $yr</h1>~;

ShowQueryResult(~select sum(Total) as TotalRevenue from tmpTR~);

echo `</td></tr><tr><td><hr></td><td><hr></td></tr><tr><td>`;

echo ~<p><font size=4>Expenses By Property - $yr</font>~;
ShowQueryResult(~SELECT Property, cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT,tmpP WHERE tText not in (`Buy`,`Sell`) and tmpT.CaseId=tmpP.CaseId and tmpT.rInstance=tmpA.rInstance and tValue=Type group by tmpT.CaseId order by tmpT.CaseId~);

echo `</td><td>`;

echo ~<p><font size=4>Revenue By Property - $yr</font>~;
ShowQueryResult(~SELECT Property, -cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpR,tmpTT,tmpP WHERE tText not in (`Buy`,`Sell`) and tmpT.CaseId=tmpP.CaseId and tmpT.rInstance=tmpR.rInstance and tValue=Type group by tmpT.CaseId order by tmpT.CaseId~);

echo `</td></tr><tr><td><hr></td><td><hr></td></tr><tr><td>`;

echo ~<p><font size=4>Expenses By Category - $yr</font>~;
ShowQueryResult(~SELECT tText as Category, cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT WHERE tText not in (`Buy`,`Sell`) and  tmpT.rInstance=tmpA.rInstance and tValue=Type group by tText order by tText~);

echo `</td><td>`;

echo ~<p><font size=4>Expenses By Property By Category - $yr</font>~;
ShowQueryResult(~SELECT Property, tText as Category, cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT,tmpP WHERE tText not in (`Buy`,`Sell`) and tmpT.CaseId=tmpP.CaseId and tmpT.rInstance=tmpA.rInstance and tValue=Type group by tmpT.CaseId, tText order by tmpT.CaseId, tText~);

echo `</td></tr><tr><td><hr></td><td><hr></td></tr><tr><td>`;

echo ~<p><font size=4>Capital Gains By Property - $yr</font>~;

ShowQueryResult(~SELECT tmpS.Property, Buy, Sell, cast(Sell-Buy as decimal(8,2)) as GainLoss FROM tmpS,tmpB WHERE tmpS.CaseID=tmpB.CaseID order by tmpS.Property~);


echo `</td><td></td></tr></table></center>`;
?>
