<?
session_start();
$brcode=$_SESSION['brcode'];
$branch=$_SESSION['branch'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='inde.php' </script>";
  return;
}
include_once("test.php");
include_once("opendb.php");
$br=str_replace(' ','',$branch);
$tbl='cshrcrpt'.$br.$user;
mysqli_query($db,"delete from $tbl");
mysqli_query($db,"create table $tbl(partic varchar(100),dr_op double(14,2) default 0,cr_op double(14,2)default 0,cr_no varchar(10), cr_date date, dr_tr double(14,2) default 0,cr_tr double(14,2)default 0)");
$df=$_REQUEST['fromdt'];
$dt=$_REQUEST['todt'];
?>
<style>
td{font-family:verdana;font-size:13px}
a{text-decoration:none;color:black}
a:hover{text-decoration:underline}
</style>
<body bgcolor=ivory>
<center>
<table>
<tr><td align=center><b><?print $unit.' '.$branch?></td></tr>
<?
$dt11 = implode('-', array_reverse(explode('-', $dt)));
$df11 = implode('-', array_reverse(explode('-', $df)));
$dt = implode('-', array_reverse(explode('-', $dt)));
$df = implode('-', array_reverse(explode('-', $df)));
?>
<tr><td align=center>Debtors Collection from <?print datetohtml($df11)?> to <?print datetohtml($dt11)?> </td></tr>
</table>
<table border=1>
<th>Receipt No<th>Receipt Date<th>Customer <th>Opening Balance <th>Collection Amount<th>Purchase Amount<th>Closing<th>Net Balance
<?
mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',led,op,0,'XXXOP' from ledger where brcode='$brcode' and op >= 0 and grp ='Sundry Debtors'");
mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',led,0,-1*op,'XXXOP' from ledger where brcode='$brcode' and op < 0 and grp ='Sundry Debtors'");
mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',partic,sum(dramt),sum(cramt),'OP_J' from journal2 where brcode='$brcode' and date < '$df' group by partic");
$re=mysqli_query($db,"select partic,sum(dr_op),sum(cr_op),max(cr_no) from $tbl group by partic");
$mcl_tot = 0;
$mop_tot = 0;
while($row=mysqli_fetch_array($re))
{
  if ($row[3]=='XXXOP')
  {
    $mf = 'N';
    $mop = $row[1]-$row[2];
    $mflg = 'N';
    $mop_tot += $mop;
    $re1=mysqli_query($db,"select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df' and date <= '$dt' order by date,cramt asc,tr_no");
	$sql="select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df' and date <= '$dt' order by date,cramt desc,tr_no";
	//echo $sql;
	//echo "<br>";
    while($row1=mysqli_fetch_array($re1))
    {
      $totmop+=$mop;
	  
      if($row1[3] > 0)
      {
        $mflg = 'Y';
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop - $row1[3];
		$mdet=$row1[5].' '.$row1[4];
		if($row1[5]=='Sales Return Credit')
		  {
			$mdet='Sales Return '.substr($row1[6],-6);
		  }
        print "<tr><td align='right'>$mdet</td><td>$dt1</td><td>".$row1[1]."</td><td align='right'>".number_format($mop,2,'.',',')."</td><td align='right'>".number_format($row1[3],2,'.',',')."</td><td align='right'>0.00</td><td align='right'>".number_format($mcls,2,'.',',')."</td>";
        $mf = 'Y';
        $mop = $mcls;
      }
      if($row1[2] > 0 )
      {
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop + $row1[2];
        $mdet=substr($row1[6],-15);
        print "<tr><td align='right'>$mdet</td><td>$dt1</td><td>".$row1[1]."</td><td align='right'>".number_format($mop,2,'.',',')."</td><td align='right'>0.00</td><td align='right'>".number_format($row1[2],2,'.',',')."</td><td align='right'>".number_format($mcls,2,'.',',')."</td>";
        $mf = 'Y';
        $mop = $mcls;
      }
      $totmcls+=$mcls;
      $totpramt+=$row1[2];
      $totcolamt+=$row1[3];
    }
    if(abs($mcls)>0.1)
        print "<td align='right'><b>".number_format($mcls,2,'.','.')."</td></tr>";
    else
    {
        if ($mf=='Y')
             print "<td align='right'><b>0.00</td></tr>";
        }
    $mcl_tot +=  $mcls;
    $mcls=0;
  }

}
print "<tr><td colspan=3><b>Total</td><td align='right'><b>".number_format($mop_tot,2,'.',',')."</td><td align='right'><b>".number_format($totcolamt,2,'.',',')."</td><td align='right'><b>".number_format($totpramt,2,'.',',')."</td><td></td><td align='right'><b>".number_format($mcl_tot,2,'.',',')."</td></tr>";
?>
