<style>
 td{font-family:verdana;font-size:14;color:black;}
 th{font-family:verdana;font-size:14;color:black;background-color:white}
 body{background-color:white}
</style>
<center>


<?
include("opendb.php");
include("test.php");
$acperiod=mysqli_query($db,"select * from acperiod");
$acrow=mysqli_fetch_row($acperiod);
$sp=explode('-',$dtto); //=$yea."-".$mo."-1";
$yea=$sp[0];
$mo=$sp[1];
?>

<FONT FACE='ARIAL BLACK'>
ALPHONSA HOSPITAL, PULLURAMPARA<BR>
RECEIPTS AND DISBURSEMENT LEDGER <br>
</font>
FOR THE MONTH <?PRINT $mo."-".$yea?> <hr>
</FONT>
<?

$totreceipt=0;
//$grps=mysqli_query($db,"select led from ledger where grp in('bank accounts','cash in hand')");
$rnds=mysqli_query($db,"select * from acgroups where rnd='yes'")or die(mysqli_error());
$sq2="select led from ledger where grp in(";
while($rnds_row=mysqli_fetch_row($rnds))
{
 $sq2.="'".$rnds_row[0]."',";
}
$sq2= substr($sq2,0,strlen($sq2)-1);
$sq2.=")";


$grps=mysqli_query($db,$sq2);
$bnksql="select cr,sum(amt) from actrans where dr in(";
while($grpsrow=mysqli_fetch_row($grps))
{
 $bnksql.="'".$grpsrow[0]."',";
 $list_led.="'".$grpsrow[0]."',";
}
$list_led= substr($list_led,0,strlen($list_led)-1);
$bnksql= substr($bnksql,0,strlen($bnksql)-1);
$bnksql.=") and month(dt)='$mo' and year(dt)='$yea' group by cr order by cr";


$getdrtrans=mysqli_query($db,$bnksql) or die(mysqli_error());
$i=1;
print "<table width=90%><tr><td valign=top align=center>";
print "<table border=0><tr><td colspan=5 align=center> <b>Receipts</td></tr>";
print "<tr align=center><td>No.</td><td>Head Account</td><td>Receipts<br>During the month</td>";
print "</tr>";
while($drtransrow=mysqli_fetch_row($getdrtrans))
{
$op_bal=0;
$op_month_bal1=0;
$op_month_bal2=0;
$op_month_diff=0;
$prog_total=0;

  $opresult=mysqli_query($db,"select op from ledger where led='$drtransrow[0]'");
  $oprow=mysqli_fetch_row($opresult);
  $op_bal=$oprow[0];

 $mo_minus=$mo-1;
  $sq="select sum(amt) from actrans where cr='$drtransrow[0]' and month(dt)='$mo_minus' and year(dt)='$yea'";
//print $sq."<br>";
  $op_month=mysqli_query($db,$sq);
  $op_month_row=mysqli_fetch_row($op_month);
  $op_month_bal1=$op_month_row[0];
  $op_month_bal1=number_format($op_month_bal1,2,'','');

 print "<tr><td>".$i++."</td><td>$drtransrow[0]</td><td align=right>".putComa($drtransrow[1])."</td></tr>";
 $totreceipt+=$drtransrow[1];
$totreceipt=number_format($totreceipt,2,'','');
}
print "<tr><td colspan=2>Total</td><td align=right><b>".putComa($totreceipt)."</td></tr>";

print "<tr><td colspan=3 align=center><u>OPENING BALANCES</u></td></tr>";

//**************8 opening cash balance calculation
$sq="select led,op from ledger where led in(".$list_led.")";

$bal=mysqli_query($db,$sq);
$n=0;
while($opbalance=mysqli_fetch_row($bal))
{
$sq="select sum(amt) from actrans where dr='$opbalance[0]' and month(dt)='$mo_minus' and year(dt)='$yea'";

$drtotresult=mysqli_query($db,$sq)or die(mysqli_error());
$drtotrow=mysqli_fetch_row($drtotresult);

$sq="select sum(amt) from actrans where cr='$opbalance[0]' and month(dt)='$mo_minus' and year(dt)='$yea'";


$crtotresult=mysqli_query($db,$sq);
$crtotrow=mysqli_fetch_row($crtotresult)or die(mysqli_error());

$cash_opening=$opbalance[1]+($drtotrow[0]-$crtotrow[0]);
$cash_opening=number_format($cash_opening,2,'','');
print "<tr><td colspan=2>$opbalance[0]</td><td align=right>".putComa($cash_opening)."</td></tr>";
$t+=$cash_opening;
$val_closing[$n++]=$cash_opening;
}

print "<tr><td colspan=2><b> Total</td><td align=right><b>".putComa($t)."</td></tr>";

//$gtot_receipt=$cash_opbal+$gtot_opbal_receipt+$totreceipt;
$gtot_receipt=$totreceipt+$t;
$gtot_receipt=number_format($gtot_receipt,2,'','');
print "<tr><td colspan=2><b>Grant Total</td><td align=right><b>".putComa($gtot_receipt)."</td></tr>";
print "</table></td><td valign=top>";
$grant_totreceipt=$gtot_receipt;

//**************8888888888888 payment


$totreceipt=0;
$cash_opbal=0;
$rnds=mysqli_query($db,"select * from acgroups where rnd='yes'")or die(mysqli_error());
$sq2="select led from ledger where grp in(";
while($rnds_row=mysqli_fetch_row($rnds))
{
 $sq2.="'".$rnds_row[0]."',";
}
$sq2= substr($sq2,0,strlen($sq2)-1);
$sq2.=")";


$grps=mysqli_query($db,$sq2);
$bnksql="select dr,sum(amt) from actrans where cr in(";
while($grpsrow=mysqli_fetch_row($grps))
{
 $bnksql.="'".$grpsrow[0]."',";
 $list_led.="'".$grpsrow[0]."',";
}
$list_led= substr($list_led,0,strlen($list_led)-1);
$bnksql= substr($bnksql,0,strlen($bnksql)-1);
$bnksql.=") and month(dt)='$mo' and year(dt)='$yea' group by dr order by dr";


$getdrtrans=mysqli_query($db,$bnksql) or die(mysqli_error());
$i=1;
print "<table><tr><td valign=top>";
print "<table border=0><tr><td colspan=5 align=center> <b>Payments</td></tr>";
print "<tr align=center><td>No.</td><td>Head Account</td><td>Payments<br>During the month</td>";
print "</tr>";
while($drtransrow=mysqli_fetch_row($getdrtrans))
{
$op_bal=0;
$op_month_bal1=0;
$op_month_bal2=0;
$op_month_diff=0;
$prog_total=0;

  $opresult=mysqli_query($db,"select op from ledger where led='$drtransrow[0]'");
  $oprow=mysqli_fetch_row($opresult);
  $op_bal=$oprow[0];

$mo_minus=$mo-1;
  $sq="select sum(amt) from actrans where dr='$drtransrow[0]' and month(dt)='$mo_minus' and year(dt)='$yea'";
//print $sq."<br>";
  $op_month=mysqli_query($db,$sq);
  $op_month_row=mysqli_fetch_row($op_month);
  $op_month_bal1=$op_month_row[0];
  $op_month_bal1=number_format($op_month_bal1,2,'','');

 print "<tr><td>".$i++."</td><td>$drtransrow[0]</td><td align=right>".putComa($drtransrow[1])."</td></tr>";
 $totreceipt+=$drtransrow[1];
$totreceipt=number_format($totreceipt,2,'','');
}
print "<tr><td colspan=2>Total</td><td align=right><b>".putComa($totreceipt)."</td></tr>";

print "<tr><td colspan=3 align=center><u>CLOSING BALANCES</u></td></tr>";
//**************8 CLOSING balance calculation


$sq="select led,op from ledger where led in(".$list_led.")";
$bal=mysqli_query($db,$sq);
$k=0;
while($opbalance=mysqli_fetch_row($bal))
{
$sq="select sum(amt) from actrans where dr='$opbalance[0]' and month(dt)='$mo'";

$drtotresult=mysqli_query($db,$sq)or die(mysqli_error());
$drtotrow=mysqli_fetch_row($drtotresult);

$sq="select sum(amt) from actrans where cr='$opbalance[0]' and month(dt)='$mo'";


$crtotresult=mysqli_query($db,$sq);
$crtotrow=mysqli_fetch_row($crtotresult)or die(mysqli_error());


$cash_closing=$val_closing[$k++]+$drtotrow[0]-$crtotrow[0];

$cash_closing=number_format($cash_closing,2,'','');
print "<tr><td colspan=2>$opbalance[0]</td><td align=right>".putComa($cash_closing)."</td></tr>";
$q+=$cash_closing;
}

print "<tr><td colspan=2><b> Total</td><td align=right><b>".putComa($q)."</td></tr>";

$gtot_receipt=$totreceipt+$q;
$gtot_receipt=number_format($gtot_receipt,2,'','');
print "<tr><td colspan=2><b>Grant Total</td><td align=right><b>".putComa($gtot_receipt)."</td></tr>";
print "</table></td></tr></table>";
$grant_totpayment=$gtot_receipt;

$diff=($grant_totreceipt-$grant_totpayment);
if($diff !=0)
{
 print "Difference:".putComa($diff);
}
?>

<p>&nbsp;

