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

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

ExecuteSQL("create temporary table tmpI as select rInstance from Responses where qID=`RE_X_Date` and rValue>`$yrb-12-31` and rValue<`$yre-01-01`");

ExecuteSQL("create temporary table tmpA as SELECT Responses.rInstance, rValue as Amount FROM Responses,tmpI WHERE qID=`RE_X_Amount` and rValue>0 and rValue<10000 and Responses.rInstance=tmpI.rInstance");

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

ExecuteSQL("create temporary table tmpT as SELECT CaseId, Responses.rInstance, rValue as Type FROM Responses,tmpI WHERE qID=`RE_X_Category` and Responses.rInstance=tmpI.rInstance");

echo `<center>`;

echo "<br><font size=4>Total - $yr</font>";
ShowQueryResult("SELECT cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT WHERE CaseId<>`309 Main St` and tmpT.rInstance=tmpA.rInstance and tValue=Type");

echo "<p><font size=4>By Category - $yr</font>";
ShowQueryResult("SELECT tText as Category, cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT WHERE CaseId<>`309 Main St` and tmpT.rInstance=tmpA.rInstance and tValue=Type group by tText order by tText");

echo "<p><font size=4>By Property - $yr</font>";
ShowQueryResult("SELECT CaseId as Property, cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpA,tmpTT WHERE CaseId<>`309 Main St` and tmpT.rInstance=tmpA.rInstance and tValue=Type group by CaseId order by CaseId");

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

echo "<p><font size=4>Rental By Property - $yr</font>";
ShowQueryResult("SELECT CaseId as Property, -cast(sum(Amount) as decimal(8,2)) as Total FROM tmpT,tmpR,tmpTT WHERE CaseId<>`309 Main St` and tmpT.rInstance=tmpR.rInstance and tValue=Type group by CaseId order by CaseId");

?>
