<?

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

$CaseId=`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 rUnique,tText as Category,rInstance from Responses,ResponseType where qID=`RE_X_Category` and CaseID=`$CaseId` and isDeleted=0 and tID=`RE_X_Categories` and tValue=rValue) ");
ExecuteSQL("create temporary table b as (select rUnique,rValue as Description,rInstance from Responses where qID=`RE_X_Description` and CaseID=`$CaseId` and isDeleted=0) ");
ExecuteSQL("create temporary table c as (select rUnique,rValue as Date,rInstance from Responses where qID=`RE_X_Date` and CaseID=`$CaseId` and isDeleted=0) ");
ExecuteSQL("create temporary table d as (select rUnique,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 rUnique,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 rUnique,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 ");

?>
