<?

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

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

$begbal=90000.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>~;
echo ~<tr><td>Beginning Balance:</td><td align=right><b>$~ , number_format($begbal,2) , ~</b></td></tr>~;

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(7,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 ) ~);

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

ExecuteSQL(~create temporary table h as (select rValue as Note,rInstance from Responses where qID=`RE_X_Note` and CaseID=`$CaseId` and isDeleted=0 and rValue=`mom`) ~);
//ShowQueryResult(~select * from h~);
//die(`debug done`);
ExecuteSQL(~create temporary table i as (select rValue as VendorClientId,rInstance from Responses where qID=`RE_X_VendorClient` and CaseID=`$CaseId` and isDeleted=0)  ~);
//ShowQueryResult(~select * from i~);
//die(`debug done`);
ExecuteSQL(~create temporary table j as (select rValue as VendorClient,rUnique as VendorClientId from Responses where qID=`CaseFirstName` and isDeleted=0)  ~);
//ShowQueryResult(~select * from j~);
//die(`debug done`);
ExecuteSQL(~create temporary table k as (select i.VendorClientId, j.VendorClient, rInstance from j,i where i.VendorClientId=j.VendorClientId) ~);
//ShowQueryResult(~select * from k~);
//die(`debug done`);
ExecuteSQL(~create temporary table l as (select VendorClient as Sibling, Amount, Date from h,k,d,c where h.rInstance=k.rInstance and h.rInstance=c.rInstance and k.rInstance=d.rInstance) ~);

ExecuteSQL(~create temporary table m as (select Sibling, sum(Amount) as Total from l group by Sibling order by Sibling) ~);

//$mrl=round(DateDiff(now(),`2015-09-01`)/30.25);
//die(`mrl=`.$mrl);


//$expenses = ShowQueryResultScalar(~select sum(Amount) from d,f where f.rInstance=d.rInstance ~);
$expenses = ShowQueryResultScalar(~select sum(Amount) from d,f where f.rInstance=d.rInstance and d.rInstance not in (select rInstance from h) ~);
$revenues = ShowQueryResultScalar(~select sum(Total) from m ~);

echo ~<tr><td>Expenses:</td><td align=right><b>~, number_format($expenses,2) , ~</b></td></tr>~;
echo ~<tr><td>Revenues:</td><td align=right><b>~, number_format($revenues,2) , ~</b></td></tr>~;
$outstanding=$begbal-$expenses-$revenues;
echo ~<tr><td>Outstanding Balance:</td><td align=right><b>$~ , number_format($outstanding,2) , ~</b></td></tr>~;

echo ~</table>~;

$date3 = date(`Y-m-d`, strtotime(`-3 year`));
$date2 = date(`Y-m-d`, strtotime(`-1 year`));
//echo `date3=`,$date3,`<br>`;

$expenses = ShowQueryResultScalar(~select sum(Amount) from d,g where g.rInstance=d.rInstance  and d.rInstance not in (select rInstance from h)~);
$expenses3 = ShowQueryResultScalar(~select sum(Amount) from d,g,c where g.rInstance=d.rInstance and c.rInstance=d.rInstance and Date > `$date3` and d.rInstance not in (select rInstance from h)~);

echo ~<p>All time average monthly expenses (excluding major repairs): $~ , number_format($expenses/((DateDiff(Now(),`2006-09-01`))/30.25),2);

echo ~<p>Average monthly expenses: $~ , number_format($expenses3/36,2),` - last 3 yrs`;
echo ~<p>Contribution per sibling: $~ , number_format($expenses3/36/6,2),` - last 3 yrs`;


echo ~<p>Months to zero: ~ , number_format($outstanding/($expenses/((DateDiff(Now(),`2006-09-01`))/30.25)),0);

echo ~<p><hr><h2>Contribution Total By Sibling</h2>~;
ShowQueryResult(~select * from m~);

echo ~<p><hr><h2>Past Year Expenses Over $100 Details</h2>~;
ShowQueryResult(~select distinct Category,Description,Date,Amount from f left join b on b.rInstance=f.rInstance left join c on c.rInstance=f.rInstance left join d on d.rInstance=f.rInstance left join a on a.rInstance=f.rInstance where Amount>100 and Date >`~. $date2 .~` order by Date DESC,Category ~);

echo ~<p><h2>Contribution Detail By Sibling</h2>~;
ShowQueryResult(~select * from l order by Sibling, Date~);


echo ~<p><hr><h2>Expense Details</h2>~;
/*
ShowQueryResult(~select distinct Category,Description,Date,Amount,Paid from f left join b on b.rInstance=f.rInstance left join c on c.rInstance=f.rInstance left join d on d.rInstance=f.rInstance left join e on e.rInstance=f.rInstance left join a on a.rInstance=f.rInstance order by Date DESC,Category ~);
*/
ShowQueryResult(~select distinct Category,Description,Date,Amount from f left join b on b.rInstance=f.rInstance left join c on c.rInstance=f.rInstance left join d on d.rInstance=f.rInstance left join a on a.rInstance=f.rInstance where Amount>0 order by Date DESC,Category ~);

echo ~<hr></center>~;
echo ~<a href=../inc/DirList.php?dir=../upload/LucasProperties/mom/detail&breadcrumbstop=detail>Click here for supporting documents</a>~;
echo ~<hr></form>~;

?>