<?

GLOBAL $LINENUMBERS,$NOSORT;
$LINENUMBERS=1;
$NOSORT=1;

$CaseId=ShowQueryResultScalar(~select CaseID from Responses where qID=`CaseFirstName` and rValue=`209 West Martin St`~);

$begbal=0.00;

$orderby=$_GET[`orderby`];
if ($orderby==~~) $orderby=~m_date~;

echo `<body bgcolor=#F0EFFF><form name=MomReport method=~POST~><center>`;
echo ~<hr><b style=font-size:30;>Summary</b><br><b style=font-size:10;>as of: ~ , date(~Y-m-d h:i:sa~, time()), ~</b><p><table border=3 bordercolor=darkblue bgcolor=#DFFFFF>~;

ExecuteSQL(~create temporary table a as (select tText as Category,rInstance from Responses,ResponseType where qID=`RE_X_Category` and CaseID=`$CaseId` and isDeleted=0 and tID=`RE_X_Cat` and tValue=rValue) ~);

ExecuteSQL(~create temporary table b as (select rValue as Description,rInstance from Responses where qID=`RE_X_Description` and CaseID=`$CaseId` and isDeleted=0) ~);

ExecuteSQL(~create temporary table c as (select rValue as Date,rInstance from Responses where qID=`RE_X_Date` and CaseID=`$CaseId` and isDeleted=0) ~);

ExecuteSQL(~create temporary table d as (select cast(rValue as Decimal(6,2)) as Amount,rInstance from Responses where qID=`RE_X_Amount` and CaseID=`$CaseId` and isDeleted=0) ~);

ExecuteSQL(~create temporary table e as (select tText as Paid,rInstance from Responses,ResponseType where qID=`RE_X_Paid` and CaseID=`$CaseId` and isDeleted=0 and tID=`YesNo` and tValue=rValue ) ~);

ExecuteSQL(~create temporary table f as (select rValue as Note,rInstance from Responses where qID=`RE_X_Note` and CaseID=`$CaseId` and isDeleted=0 and instr(rValue,`Shop`)>0 ) ~);

$expenses = ShowQueryResultScalar(~select sum(Amount) from d,f where f.rInstance=d.rInstance ~);

echo ~<tr><td>Expenses:</td><td align=right><b>~, number_format($expenses,2) , ~</b></td></tr>~;

echo ~</table>~;

echo ~<p>Average monthly expenses: $~ , number_format($expenses/((DateDiff(Now(),`2006-09-01`))/30.25),2);

echo ~ <p><hr><h2>Details</h2>~;

ShowQueryResult(~select distinct Category,Description,Date,Amount,Paid from a,b,c,d,e,f where b.rInstance=a.rInstance and c.rInstance=a.rInstance and d.rInstance=a.rInstance and e.rInstance=a.rInstance and f.rInstance=a.rInstance order by Date DESC,Category ~);

?>
