<?
session_start();
include("opendb.php");
include("test.php");
?>

<?$ops=mysqli_query($db,"select * from plopstock");
$stocks=mysqli_fetch_row($ops);
$ostock=$stocks[0];
$cstock=$stocks[1];
$sumleft=0;
 $result=mysqli_query($db,"select * from ledger where grp='Purchase Accounts'");
 while($row1=mysqli_fetch_array($result))
 {

  $sq="select * from journal2 where partic='$row1[led]' and brcode='$brcode'";
   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot+=$bal;
}
$sumleft+=$tot;
//*********************************************************************************************//
$bal=0; $tot=0;
 $result=mysqli_query($db,"select * from ledger where grp='Direct Expenses' and brcode='$brcode'");
 while($row1=mysqli_fetch_array($result))
 {

  $sq="select * from journal2 where partic='$row1[led]'  and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot+=$bal;
}
$sumleft+=$tot;
$sumleft+=$ostock;
?>
</table>
</td>
<td width=50% valign=top>
<table border=1 width=100%>
<?
//*********************************************************************************************//
$bal=0; $tot=0;
$sumright=0;
 $result=mysqli_query($db,"select * from ledger where grp='Sales Accounts' and brcode='$brcode'");
 while($row1=mysqli_fetch_array($result))
 {

  $sq="select * from journal2 where partic='$row1[led]' and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot+=$bal;
}
$sumright+=$tot;
//*********************************************************************************************//
$bal=0; 
$tot=0;
//$sumright=0;
 $result=mysqli_query($db,"select * from ledger where grp='Direct Income' and brcode='$brcode'");
 while($row1=mysqli_fetch_array($result))
 {

  $sq="select * from journal2 where partic='$row1[led]'  and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot+=$bal;
}
$sumright+=$tot; 
$sumright=abs($sumright)+$cstock;

//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//xxxxxxxxxxxxxxxxxxxxxxxxxxx//
$diff=abs($sumright)-$sumleft;
//$grossprofit=0;$grossloss=0;
if($diff>0)
{
}
else
{
}
?>

<?if($diff<0)
{
$grossloss=$diff;
?>
<?
}
?>

<?
$bal=0; $tot=0;
//$sumright=0;
$sumleft2=0;

//********************************* all other expenses are treated as indirect expenses **//

 $result=mysqli_query($db,"select * from ledger where brcode='$brcode' and (grp='Indirect Expenses' or grp='duties and taxes' or grp='Misc Expenses' or grp='Registration')");
 while($row1=mysqli_fetch_array($result))
 {
  $sq="select * from journal2 where partic='$row1[led]' and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot+=$bal;
}
$sumleft2+=$tot;
$sumleft2=$tot+abs($grossloss);
//print "<tr><td colspan=3 align=right>X".putComa($sumleft2)."</td>";
?>

<!-- *************right indirect income-->
<!--<tr><td>-->
<?if($diff>0)
{
$grossprofit=$diff;
?>
<?
$gp=$diff;
}
$bal=0; $tot=0;
$sumright=0;
$sumright+=$diff;
 $result=mysqli_query($db,"select * from ledger where grp='Indirect Income' and brcode='$brcode'");
 while($row1=mysqli_fetch_array($result))
 {

  $sq="select * from journal2 where partic='$row1[led]' and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
$bal=$tr-$tp;
$tot=$tot+abs($bal);

}
//$gtot=$tot+$diff;
$gtot=$tot+$grossprofit;
$sumright=abs($sumright)+$tot;
$sumright+=$grossprofit;

//$porloss1=$sumleft2-$tot;//+abs($diff));


if($sumleft2>$gtot)//summright)
{
$porloss=abs($sumleft2)-(abs($gtot));//+abs($diff));
}
else 
{
$porloss=abs($sumleft2)-(abs($gtot));//+abs($diff));
}

mysqli_query($db,"update pl set npl='$porloss'") or die(mysqli_error());
?>






<!-- ****************************B sheet starts here ******************** -->
<head>
<script>
function sh()
{
 window.open("prnbsheet.php");
}
</script>
</head>

<style>
 td{font-family:verdana;font-size:14;color:black;}
 th{font-family:verdana;font-size:14;color:white;background-color:#5b5858}
 body{background-color:ivory}
</style>
<table border=0 WIDTH=100%>
<tr><td colspan=2><CENTER> <B><?print $branch?><BR>BALANCE SHEET AS ON 
<?print date("d-M-Y",time())?></TD><td width=30><a href='javascript:sh()' title='Print'><img src='Type001.gif' border=0></a></td></TR>
</table>
<table border=1>
<tr bgcolor=#5b5858><td><CENTER><font color=white><b>LIABILITIES</td><td colspan=2><b><font color=white><CENTER>ASSETS</td></tr>
<tr><td width=50% valign=top >
 <table border=1>
<?
$sql="select distinct b.gname from ledger a,acgroups b where a.grp=b.gname and b.nature='liabilities' and a.brcode='$brcode' order by b.level";
$result=mysqli_query($db,$sql) or die(mysqli_error());
while($row=mysqli_fetch_array($result))
{
 $tot=0;
 print "<tr><td colspan=3> <b><font color=brown>$row[gname]</td></tr>";
 $re=mysqli_query($db,"select * from ledger where grp='$row[gname]' and brcode='$brcode'");
 while($row1=mysqli_fetch_array($re))
 {
/*  $sq="select * from actrans where ledger='$row1[led]' or dr='$row1[led]' or cr='$row1[led]' or mode='$row1[led]'";*/
 $sq="select * from journal2 where  partic='$row1[led]' and brcode='$brcode'";

   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$tr+=$opb[op];
// print "<br>$row1[led], $opb[drcr], tr=$tr, tp=$tp";
}
else if($opb[drcr]=="Cr")
{
$tp+=$opb[op];
}


while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}

}
$bal=$tr-$tp;
$tot+=$bal;

//*************************************************************************************
	$bal=number_format($bal,2,'','');
//*************************************************************************************
  print "<tr><td width=3000>$row1[led]</td><td align=right>".putComa($bal*-1)."</td>";
 }
//*************************************************************************************
	$tot=number_format($tot,2,'','');
//*************************************************************************************
 print "<td align=right>".putComa($tot*-1)."</td></tr>";
$tot2+=$tot;
}

$plval=mysqli_query($db,"select * from pl") or die(mysqli_error());
$pval=mysqli_fetch_array($plval);
// print "<tr><td colspan=2><font color=brown><b> Profit & loss a/c</tr></td>";
// print "<tr><td> Opening Balance(P&L ob)</td><td align=right>$pval[opb]</td></tr>";
 print "<tr><td> Current Period</td><td align=right>".putComa($pval[npl]*-1)."</td><td align=right>".putComa($pval[npl]*-1)."</td></tr>";

$tot2+=$pval[npl];
$tot2+=
print "<tr><td colspan=3 align=right>".putComa($tot2*-1)."</td></tr>";
$totleft=$tot2;
?>
</table>
 </td>
<td valign=top>
 <table border=1>
<?
$tp=0;$tr=0;$bal=0;$tot=0;$tot2=0;
$sql="select distinct b.gname from ledger a,acgroups b where a.grp=b.gname and b.nature='Assets' and a.brcode='$brcode' order by b.level";
$result=mysqli_query($db,$sql) or die(mysqli_error());

 $ops=mysqli_query($db,"select * from plopstock");
 $clstock=mysqli_fetch_row($ops);$clstk=$clstock[1];


while($row=mysqli_fetch_array($result))
{
$tot=0;$optr=0;$optp=0;
 print "<tr><td colspan=3><font color=brown><b>$row[gname]</td></tr>";
 $re=mysqli_query($db,"select * from ledger where grp='$row[gname]' and brcode='$brcode'");

 while($row1=mysqli_fetch_array($re))
 {

/*  $sq="select * from actrans where ledger='$row1[led]' or dr='$row1[led]' or cr='$row1[led]' or mode='$row1[led]'";*/

  $sq="select * from journal2 where partic='$row1[led]' and brcode='$brcode'";
   $re2=mysqli_query($db,$sq);
  $re3=mysqli_query($db,"select * from ledger where led='$row1[led]' and brcode='$brcode'"); 
  $opb=mysqli_fetch_array($re3);
$tr=0; $tp=0;
if($opb[drcr]=="Dr")
{
$optr+=$opb[op];
}
else if($opb[drcr]=="Cr")
{
$optr-=$opb[op];
//$optp+=$opb[op];
}
//print "<br>$row1[led] optr=$optr optp=$optp";

while($r1=mysqli_fetch_array($re2))
{
if($r1[cr]==$row1[led])
{
  $tp+=$r1[amt];
}
else if($r1[dr]==$row1[led])
{
  $tr+=$r1[amt];
}
}
//print "<br>tp=$tp , tr=$tr";
$bal=$optr+$tr-$tp;
$optr=0;$tr=0;
//$bal=$opb-$tp;
$tot+=$bal;
//$tot2+=$clstk;
//*************************************************************************************
		$bal=number_format($bal,2,'','');
//*************************************************************************************
  print "<tr><td width=3000>$row1[led]</td><td align=right>".putComa($bal)."</td>";
 }

//*************************************************************************************
$tot=number_format($tot,2,'','');
//*************************************************************************************
 print "<td align=right>".putComa($tot)."</td></tr>";
$tot2+=$tot;
}
$tot2+=$clstk;
print "<tr><td colspan=2>Closing stock</td><td align=right>".putComa($clstock[1])."</td></tr>";
print "<tr><td colspan=3 align=right>".putComa($tot2)."</td></tr>";
$totright=$tot2;//+$clstk; 

$bal=abs($totleft)-abs($totright);
?>
 </table>
</tr></td>
<?
/*
 if($totleft>$totright)
 {
?>
<tr><td>
<!--
<table border=2 width=100%>
<tr><td>Difference </td><td align=right><?print putComa($bal)?></td></tr>
<tr><td  colspan=2 align=right>Y<?print putComa($totright)?></td></tr>
</table>
-->
<?
}
else
{
 print "<tr><td></td><td>";
?>
<table border=2 width=100%>
<tr><td>Difference </td><td align=right><?print putComa($bal)?></td></tr>
<tr><td> </td><td align=right><?print putComa($totleft)?></td></tr>
</table>
<?
}*/
?>
</table>
<?
print "Difference:".putComa($bal);
?>

