<?
session_start();
$brcode = $_SESSION['brcode'];
$user = $_SESSION['user'];
if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='../ind.php' </script>";
  return;
}
include("../test.php");
include ("../opendb.php");
$tbl='tbcls'.$brcode.$user;
$tbl1='tbclssum'.$brcode.$user;

mysqli_query($db,"drop table if exists $tbl");
mysqli_query($db,"drop table if exists $tbl1");

mysqli_query($db,"create table $tbl(led varchar(100),dr double(14,2) default 0,cr double(14,2)default 0,ltype varchar(10))");
mysqli_query($db,"create table $tbl1(led varchar(100),opdr double(14,2) default 0,opcr double(14,2)default 0,dr double(14,2) default 0,cr double(14,2)default 0,cldr double(14,2) default 0,clcr double(14,2)default 0,ltype varchar(10),grp1 varchar(50))");
//$sq="select distinct partic from journal2  where partic not in(select led from ledger where brcode='$brcode') and brcode='$brcode'";
//print $sq;

/*
$chkres=mysqli_query($db,$sq);
if(mysqli_num_rows($chkres)>0)
{
 print "<font color=red> Following ledgers could not be found</font> <br>";
 $i=1;
 while($chrow=mysqli_fetch_array($chkres))
 {
   print $i++.". $chrow[0] <br>";
 }
 print "<a href='../ledge.php'>Create Ledger now </a>";
 return;
}
*/
$d = $_REQUEST['d'];
$m = $_REQUEST['m'];
$y = $_REQUEST['y'];
$d1 = $_REQUEST['d1'];
$m1 = $_REQUEST['m1'];
$y1 = $_REQUEST['y1'];
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;

?>

<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>
<tr><td align=center>Trial Balance from <?print implode('-', array_reverse(explode('-',$df))).' to '.implode('-', array_reverse(explode('-',$dt)))?></td></tr>
<tr><td align=center>[Printed by <?print $user?> on <?print date("d-m-Y  h:i:s a")?>]</td></tr>
</table>
<table border=1>
<th>Ledger <th colspan=2>Opening Balance <th colspan=2>Transaction <th colspan=2>Closing
<tr align=center><td>&nbsp;</td><td>Debit </td><td>Credit</td><td>Debit </td><td>Credit</td><td>Debit </td><td>Credit</td></tr>

<?
$result=mysqli_query($db,"select distinct grp from ledger where brcode='$brcode' order by grp")or die(mysqli_error());
$i=1;
while($row=mysqli_fetch_row($result))
{ 
    $sql="insert into $tbl (led,ltype) values('$row[0]','grp')";
    mysqli_query($db,$sql) or die('ins grp err:'.mysqli_error());
    print "<tr><td colspan=7 bgcolor='aliceblue'><b>$row[0]</td></tr>";
    $sqq="select * from ledger where brcode='$brcode' and grp='$row[0]' order by led ";
    $re=mysqli_query($db,$sqq) or die("ERR:".mysqli_error());
    while($row2=mysqli_fetch_array($re))
    {
        $opdr=0;
        $opcr=0;
	    $drcrforop=mysqli_query($db,"select sum(dramt),sum(cramt) from journal2 where partic='$row2[0]' ".
	    "and brcode='$brcode' and date <'$df'")or die(mysqli_error());
	    $op4row=mysqli_fetch_row($drcrforop)or die(mysqli_error());
        //*********** check for empty*/
        if($row2['op']==0)
        {
 $sq="select sum(dramt),sum(cramt) from journal2 where partic='$row2[led]' and brcode='$brcode'";
// print $sq;
  $chktr=mysqli_query($db,$sq);
  $chktrrow=mysqli_fetch_row($chktr);
  if($chktrrow[0]==0 && $chktrrow[1]==0)
    { //print "</table><br>"; 
     continue;
    }
}

//*****************************************/*
	  if($row2['drcr']=='Dr')
           {
 	      if($row2['op']>=0)
                  $opdr=($row2['op']+$op4row[0])-$op4row[1];
              else
                  $opcr=(abs($row2['op'])+$op4row[1])-$op4row[0];
	   }
	  if($row2['drcr']=='Cr')
           {
 	      if($row2['op']<=0)
                  $opdr=(abs($row2['op'])+$op4row[0])-$op4row[1];
              else
                  $opcr=($row2['op']+$op4row[1])-$op4row[0];
	   }

if($opdr<0)
{
 $t=abs($opdr);
  $opdr=abs($opcr);
  $opcr=$t;
}

if($opcr<0)
{
 $t=abs($opcr);
 $opcr=abs($opdr);
 $opdr=$t;
}

$opdr=round($opdr,2);
$opcr=round($opcr,2);
print "<tr><td><a href='../vmledger.php?cd=$row2[led]' title='Click to view ledger' target='m'>".strtoupper($row2['led'])."</a></td><td align=right>&nbsp;".
nozero(putcoma($opdr)).'</td><td align=right>&nbsp;'.nozero(putcoma($opcr))."</td>";

//***************

	$drcrforop1=mysqli_query($db,"select sum(dramt),sum(cramt) from journal2 where partic='$row2[0]' ".
	"and brcode='$brcode' and date>='$df' and date<='$dt'")or die(mysqli_error());
	$op4row1=mysqli_fetch_row($drcrforop1)or die(mysqli_error());

print "<td align=right bgcolor='f6e0c1'>&nbsp;".nozero(putcoma(round($op4row1[0],2))).
"</td><td align=right  bgcolor='f6e0c1'>&nbsp;".nozero(putcoma(round($op4row1[1],2)))."</td>";

$tot1+=$op4row1[0];
$tot2+=$op4row1[1];


//********************


$topdr+=($opdr);
$topcr+=($opcr);

//************************** closing

$opdr1=0;
$opcr1=0;

	$drcrforcl=mysqli_query($db,"select sum(dramt),sum(cramt) from journal2 where partic='$row2[0]' ".
	"and brcode='$brcode' and date>='$df' and date<='$dt'")or die(mysqli_error());
//	$drcrforcl=mysqli_query($db,"select sum(dramt),sum(cramt) from journal2 where partic='$row2[0]' ".
//	"and brcode='$brcode'")or die(mysqli_error());

	$cl4row=mysqli_fetch_row($drcrforcl)or die(mysqli_error());
	  if($row2['drcr']=='Dr')
           {
 	      if($row2['op']>=0)
                  //$opdr1=($row2['op']+$cl4row[0])-$cl4row[1];
		$opdr1=($cl4row[0])-$cl4row[1];
              else
                  //$opcr1=(abs($row2['op'])+$cl4row[1])-$cl4row[0];
		$opcr1=($cl4row[1])-$cl4row[0];
	   }
	  if($row2['drcr']=='Cr')
           {
 	      if($row2['op']<=0)
                  //$opdr1=(abs($row2['op'])+$cl4row[0])-$cl4row[1];
		$opdr1=$cl4row[0]-$cl4row[1];
              else
                  //$opcr1=($row2['op']+$cl4row[1])-$cl4row[0];
		$opcr1=($cl4row[1])-$cl4row[0];
	   }

//***********************************************************************

$opdr1+=($opdr);
$opcr1+=($opcr);

//***********************************************************************

if($opdr1<0)
{
 $t=abs($opdr1);
  $opdr1=abs($opcr1);
  $opcr1=$t;
}

if($opcr1<0)
{
 $t=abs($opcr1);
 $opcr1=abs($opdr1);
 $opdr1=$t;
}

$opdr1=round($opdr1,2);
$opcr1=round($opcr1,2);

print "<td align=right>&nbsp;".nozero(putcoma($opdr1)).'</td><td align=right>&nbsp;'.
nozero(putcoma($opcr1))."</td>";
$sql="insert into $tbl values('$row2[led]','$opdr1','$opcr1','led')";
mysqli_query($db,$sql) or die('ins err'.mysqli_error());
if($opdr1<=0)
    $opdr1=0;
if($opcr1<=0)
    $opcr1=0;
if($opdr<=0)
    $opdr=0;    
if($opcr<=0)
    $opcr=0;    
if($op4row1[0]<=0)    
    $mop4row1=0;
else
    $mop4row1=$op4row1[0];
if($op4row1[1]<=0)    
    $mop4row2=0;
else
    $mop4row2=$op4row1[1];
$sql="insert into $tbl1 values('$row2[led]','$opdr','$opcr','$mop4row1','$mop4row2','$opdr1','$opcr1','led','$row[0]')";
mysqli_query($db,$sql) or die('ins2 err'.mysqli_error());


$topdr1+=($opdr1);
$topcr1+=($opcr1);


//***********************************
     }
}
$diff=($topdr-$topcr);
//$diff=number_format($diff,2,'','');

$diffclose=($topdr1-$topcr1);
//$diffclose=number_format($diffclose,2,'','');


print "<tr><td> <b>Opening Contra</td><td align=right>".abs($diff);
print "<td></td><td></td><td></td><td align=right>".abs($diffclose)."</td>";
print "<tr><td><b>Total</td><td align=right>".$topdr."</td><td align=right>".
$topcr."</td>";
print "<td align=right  bgcolor='f6e0c1'>".$tot1."</td><td align=right  bgcolor='f6e0c1'>".
$tot2."</td>";
print "<td align=right>".$topdr1."</td><td align=right>".$topcr1."</td>";

/*
print "<tr><td> <b>Opening Contra</td><td align=right>".putComa(abs($diff));
print "<td></td><td></td><td></td><td align=right>".putComa(abs($diffclose))."</td>";
print "<tr><td><b>Total</td><td align=right>".putcoma($topdr)."</td><td align=right>".
putcoma($topcr)."</td>";
print "<td align=right  bgcolor='f6e0c1'>".putComa($tot1)."</td><td align=right  bgcolor='f6e0c1'>".
putcoma($tot2)."</td>";
print "<td align=right>".putComa($topdr1)."</td><td align=right>".putcoma($topcr1)."</td>";
*/
?>
<script>
 window.open('listclstb.php?df=<?print $df?>&dt=<?print $dt?>');
 window.open('listclstbsum.php?df=<?print $df?>&dt=<?print $dt?>');

</script>
