<?
 session_start();
 include('includes/top.php'); 
$brcode = $_SESSION['brcode'];
$branch = $_SESSION['branch'];
$user   = $_SESSION['user'];
$level   = $_SESSION['level'];
$check=$_REQUEST['check'];

if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='ind.php' </script>";
  return;
}

 include("test.php");
if($unit=='Mahindra')
{
$db=mysqli_connect("localhost","root");
mysqli_select_db("mahdb1") or die(mysqli_error($db));
print $unit;
}
else
 include ("opendb.php");
$br=str_replace(' ','',$branch);
$br=str_replace('(','',$br);
$br=str_replace(')','',$br);

$tbl='tbcls1'.$brcode.$user;
$tblop='tbclsop1'.$brcode.$user;
$tbl1='tbclssum'.$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"drop table $tblop");
mysqli_query($db,"drop table $tbl1");

mysqli_query($db,"delete from $tbl");
mysqli_query($db,"delete from $tblop");
mysqli_query($db,"delete from $tbl1");

mysqli_query($db,"create table $tbl(partic varchar(100),totdr double(14,2) default 0,totcr double(14,2)default 0)");
mysqli_query($db,"create table $tblop(partic varchar(100),totdr double(14,2) default 0,totcr double(14,2)default 0)");

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))");
/*
$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;
}
*/
$d1=$_REQUEST['d1'];
$m1=$_REQUEST['m1'];
$y1=$_REQUEST['y1'];
$d=$_REQUEST['d'];
$m=$_REQUEST['m'];
$y=$_REQUEST['y'];

$df=$y.'-'.$m.'-'.$d;
  $dt=$y1.'-'.$m1.'-'.$d1;
mysqli_query($db,"create table $tbl(partic varchar(100),totdr double(14,2) default 0,totcr double(14,2)default 0");
mysqli_query($db,"create table $tblop(partic varchar(100),totdr double(14,2) default 0,totcr double(14,2)default 0");
$ad=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($ad);

?>

<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>PSN AUTOMOTIVE MARKETING PVT. LTD</td></tr>
<tr><td align=center><p style={letter-spacing:2;font-size:15}><b><?print $brrow[brname]?></TD></TR>
<TR><TD ALIGN=CENTER><font size=2></td></TR>

<TR><TD ALIGN=CENTER><font size=2><?print strtoupper($brrow[braddr]).', '.strtoupper($brrow[brcity])?></td></tr>

<tr><td align=center><br><U><B><h3><? print $jtype?></h3> </B></U></TD></TR>


<tr><td align=center>Trial Balances upto <?print datetohtml($dt)?></td></tr>
<tr><td align=center>[Printed by <?print $user?> on <?print date("d-m-Y  h:i:s")?>]</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>

<?
$jour_opdet = mysqli_query($db,"insert into $tblop select partic,sum(dramt) as totdr,sum(cramt) as totcr from journal2 where brcode='$brcode' and date < '$df' group by partic") or die(mysqli_error($db));
$jour_det = mysqli_query($db,"insert into $tbl select partic,sum(dramt) as totdr,sum(cramt) as totcr from journal2 where  brcode='$brcode' and date >= '$df' and date<='$dt' group by partic") or die(mysqli_error($db));

//$resultg = mysqli_query($db,"select distinct grp from ledger order by grp") or die("ERR:".mysqli_error($db));
$resultg = mysqli_query($db,"select gname from acgroups where gname in (select distinct grp from ledger where brcode='$brcode' )order by level") or die("ERR:".mysqli_error($db));

$i=1;
$mopdrtot = 0;
$mopcrtot = 0; 
$mdrtot = 0;
$mcrtot = 0;
$mcldrtot =0;
$mclcrtot =0;
$mgrp = '';
while($rowg=mysqli_fetch_row($resultg))
{ 
print"<tr><td><b>$rowg[0]</b></td><td colspan=6 ></td></tr>";

$result = mysqli_query($db,"select * from ledger where grp='$rowg[0]' and brcode='$brcode' order by led") or die("ERR:".mysqli_error($db));



while($row=mysqli_fetch_row($result))
{ 
    $gp=trim($row[1]);
    
    $reop=mysqli_query($db,"select * from $tblop where partic='$row[0]' ") or die("ERR:".mysqli_error($db));
    $roop1=mysqli_fetch_row($reop);

    $re=mysqli_query($db,"select * from $tbl where partic='$row[0]' ") or die("ERR:".mysqli_error($db));
    $ro=mysqli_fetch_row($re);
    $mopdr = 0;
    $mopcr = 0; 
    if($row[3]=='Dr')
    {
         if ($row[2]>=0)
	      $mopdr = $row[2];
         else
             $mopcr = $row[2] * -1;
    }
    if($row[3]=='Cr')
    {
         if ($row[2]>=0)
	      $mopcr = $row[2];
         else
             $mopdr = $row[2] * -1;
    }
    $mdr = 0;
    $mcr = 0;
    $mopt_trans = 0;
    if($roop1[1]>0) 
        $mopt_trans = $roop1[1];
    if($roop1[2]>0)
	 $mopt_trans = $mopt_trans - $roop1[2];
    $mopdr = $mopdr - $mopcr + $mopt_trans;
    $mopcr = 0;
    if($mopdr < 0)
        {
        $mopcr = $mopdr * -1;
        $mopdr = 0;
        }
    if ($ro[1]>0)
        $mdr = $ro[1];
    if ($ro[2]>0)
        $mcr = $ro[2];    
    $mopdrtot +=$mopdr;
    $mopcrtot +=$mopcr; 
    $mdrtot += $mdr;
    $mcrtot += $mcr;
    $mclbal = $mopdr - $mopcr + $mdr - $mcr;
    $mcldr = 0;
    $mclcr = 0;
    if ($mclbal>=0)
        $mcldr = $mclbal;
    else
        $mclcr = $mclbal * -1;
    $mcldrtot +=$mcldr; 
    $mclcrtot +=$mclcr;
    
    $mopdr=number_format($mopdr,2,'.','');
    $mopcr=number_format($mopcr,2,'.','');
    $mdr=number_format($mdr,2,'.','');
    $mcr=number_format($mcr,2,'.','');
    $mcldr=number_format($mcldr,2,'.','');
    $mclcr=number_format($mclcr,2,'.','');
    
    if($check=='Suppress')
    {
       if($mopdr>'0' ||$mopcr>'0' || $mdr>'0'|| $mcr>'0')
	{
       ///   if($mgrp!=$gp)
         // {
          //    print "<tr><td><b>$gp</td></tr>";
         //   $mgrp=$gp;
        //   }
           
          print "<tr><td><a href='accounts/vmledger.php?cd=$row[0]' title='Click to view ledger' target='m'>".strtoupper($row[0])."</a></td><td align='right'>".number_format($mopdr,2,'.','')."</td><td align='right'>".number_format($mopcr,2,'.','')."</td><td align='right'>".number_format($mdr,2,'.','')."</td><td align='right'>".number_format($mcr,2,'.','')."</td><td align='right'>".number_format($mcldr,2,'.','')."</td><td align='right'>".number_format($mclcr,2,'.','')."</td></tr>";
          
	   $jour_sum = mysqli_query($db,"insert into $tbl1 (led,opdr,opcr,dr,cr,cldr,clcr,grp1) values ('$row[0]','$mopdr','$mopcr','$mdr','$mcr','$mcldr','$mclcr','$row[1]')") or die(mysqli_error($db));
	}
	
    }
    
    
       if($check=='closing')
    {
       if($mcldr>'0' ||$mclcr>'0' )
	{
         // if($mgrp!=$row[1])
        //  {
           //   print "<tr><td><b>$row[1]</td></tr>";
            //  $mgrp=$row[1];
        //   }
          print "<tr><td><a href='accounts/vmledger.php?cd=$row[0]' title='Click to view ledger' target='m'>".strtoupper($row[0])."</a></td><td align='right'>".number_format($mopdr,2,'.','')."</td><td align='right'>".number_format($mopcr,2,'.','')."</td><td align='right'>".number_format($mdr,2,'.','')."</td><td align='right'>".number_format($mcr,2,'.','')."</td><td align='right'>".number_format($mcldr,2,'.','')."</td><td align='right'>".number_format($mclcr,2,'.','')."</td></tr>";
          
	   $jour_sum = mysqli_query($db,"insert into $tbl1 (led,opdr,opcr,dr,cr,cldr,clcr,grp1) values ('$row[0]','$mopdr','$mopcr','$mdr','$mcr','$mcldr','$mclcr','$row[1]')") or die(mysqli_error($db));
	}
	
    }
    
    
    if($check=='All')
    {
	print "<tr><td><a href='accounts/vmledger.php?cd=".str_replace('&','~',$row[0])."' title='Click to view ledger' target='m'>$row[0]</td><td align='right'>".number_format($mopdr,2,'.','')."</td><td align='right'>".number_format($mopcr,2,'.','')."</td><td align='right'>".number_format($mdr,2,'.','')."</td><td align='right'>".number_format($mcr,2,'.','')."</td><td align='right'>".number_format($mcldr,2,'.','')."</td><td align='right'>".number_format($mclcr,2,'.','')."</td></tr>";
	
       $jour_sum = mysqli_query($db,"insert into $tbl1 (led,opdr,opcr,dr,cr,cldr,clcr,grp1) values ('$row[0]','$mopdr','$mopcr','$mdr','$mcr','$mcldr','$mclcr','$row[1]')") or die(mysqli_error($db));
    }
    
    
}
}
$mopdrtot=number_format($mopdrtot,2,".","");
$mopcrtot=number_format($mopcrtot,2,".","");

$mdrtot=number_format($mdrtot,2,".","");
$mcrtot=number_format($mcrtot,2,".","");

$mcldrtot=number_format($mcldrtot,2,".","");
$mclcrtot=number_format($mclcrtot,2,".","");
if ($mopdrtot>$mopcrtot)
{
	print "<tr><td>Opening Contra</td><td align='right'>".putComa(0)."</td><td align='right'>".putComa($mopdrtot-$mopcrtot)."</td>";
	$mopcrtot=$mopdrtot;
}
elseif ($mopdrtot<$mopcrtot)
{
	print "<tr><td>Opening Contra</td><td align='right'>".putComa($mopcrtot-$mopdrtot)."</td><td align='right'>".putComa(0)."</td>";
	$mopdrtot=$mopcrtot;
}
else 
	print "<tr><td>Opening Contra</td><td align='right'>".putComa(0)."</td><td align='right'>".putComa(0)."</td>";
if ($mdrtot>$mcrtot)
{
	print "<td align='right'>".putComa(0)."</td><td align='right'>".putComa(round($mdrtot-$mcrtot),2)."</td>";
	$mcrtot=$mdrtot;
}
elseif ($mdrtot<$mcrtot)
{
	print "<td align='right'>".putComa(round($mcrtot-$mdrtot),2)."</td><td align='right'>".putComa(0)."</td>";
	$mdrtot=$mcrtot;
}
else
	print "<td align='right'>".putComa(0)."</td><td align='right'>".putComa(0)."</td>";
if (mcldrtot>mclcrtot)
{
	print "<td align='right'>".putComa(0)."</td><td align='right'>".putComa(round($mcldrtot-$mclcrtot),2)."</td></tr>";
	$mclcrtot=$mcldrtot;
}
elseif (mcldrtot<mclcrtot)
{
	print "<td align='right'>".putComa(round($mclcrtot-$mcldrtot),2)."</td><td align='right'>".putComa(0)."</td></tr>";
	$mcldrtot=$mclcrtot;
}

else
	print "<td align='right'>".putComa(0)."</td><td align='right'>".putComa(0)."</td></tr>";
print "<tr><td>T O T A L</td><td align='right'>".putComa($mopdrtot)."</td><td align='right'>".putComa($mopcrtot)."</td><td align='right'>".putComa($mdrtot)."</td><td align='right'>".putComa($mcrtot)."</td><td align='right'>".putComa($mcldrtot)."</td><td align='right'>".putComa($mclcrtot)."</td></tr>";
?>
<script>
 //window.open('listclstbsum1.php?df=<?print $df?>&dt=<?print $dt?>');
</script>

