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


<?
include("opendb.php");
include("test.php");
$acperiod=mysqli_query($db,"select * from acperiod");
$acrow=mysqli_fetch_row($acperiod);
//$dtto=$yea."-".$mo."-1";
$dateval=explode('-',$dtto);
$yea=$dateval[0];
$mo=$dateval[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')");
$grps=mysqli_query($db,"select led from ledger where grp in('cash in hand')");
$bnksql="select cr,sum(amt) from actrans where dr in(";
while($grpsrow=mysqli_fetch_row($grps))
{
 $bnksql.="'".$grpsrow[0]."',";
}
$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><tr><td valign=top>";
print "<table border=1><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>";


//**************8 opening cash balance calculation
$bal=mysqli_query($db,"select op from ledger where led='Cash'");
$opbalance=mysqli_fetch_row($bal);
$drtotresult=mysqli_query($db,"select sum(amt) from actrans where dr='cash' and month(dt)='$mo_minus'");
$drtotrow=mysqli_fetch_row($drtotresult);

$crtotresult=mysqli_query($db,"select sum(amt) from actrans where cr='cash' and month(dt)='$mo_minus'");
$crtotrow=mysqli_fetch_row($crtotresult);

$cash_opbal=$opbalance[0]+($drtotrow[0]-$crtotrow[0]);
$cash_opbal=number_format($cash_opbal,2,'','');
print "<tr><td colspan=2>Cash Opening</td><td align=right>".putComa($cash_opbal)."</td>";
print "<tr><td colspan=2><b>Grand Total</td><td align=right><b>".putComa($cash_opbal+$totreceipt)."</td></tr>";
print "</table></td><td valign=top>";


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


$totreceipt=0;
//$grps=mysqli_query($db,"select led from ledger where grp in('bank accounts','cash in hand')");
$grps=mysqli_query($db,"select led from ledger where grp in('cash in hand')");
$bnksql="select distinct dr,sum(amt) from actrans where cr in(";
while($grpsrow=mysqli_fetch_row($grps))
{
 $bnksql.="'".$grpsrow[0]."',";
}
$bnksql= substr($bnksql,0,strlen($bnksql)-1);
$bnksql.=") and month(dt)='$mo' and year(dt)='$yea' group by dr order by dr";
//print $bnksql."<br>";
$getdrtrans=mysqli_query($db,$bnksql) or die(mysqli_error());
$i=1;

print "<table border=1><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,'','');

  $op_month=mysqli_query($db,"select sum(amt) from actrans where cr='$drtransrow[0]' and month(dt)='$mo_minus' and year(dt)='$yea'");
  $op_month_row=mysqli_fetch_row($op_month);
  $op_month_bal2=$op_month_row[0];
  $op_month_bal2=number_format($op_month_bal2,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>";

// **************** closing balace calculation

$drtotresult=mysqli_query($db,"select sum(amt) from actrans where dr='cash' and month(dt)='$mo'");
$drtotrow=mysqli_fetch_row($drtotresult);

$crtotresult=mysqli_query($db,"select sum(amt) from actrans where cr='cash' and month(dt)='$mo'");
$crtotrow=mysqli_fetch_row($crtotresult);
$cash_closing=$cash_opbal+$drtotrow[0]-$crtotrow[0];
$cash_closing=number_format($cash_closing,2,'','');
print "<tr><td colspan=2>Closing balance</td><td align=right>".putComa($cash_closing)."</td></tr>";
print "<tr><td colspan=2><B>Grant Total</td><td align=right><b>".putComa($cash_closing+$totreceipt)."</td></tr>";
print "</table>";

?>



