<?
include_once('top.php');
//include_once('../opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='../inde.php' </script>";
  return;
}
$df=$_REQUEST['fromdt'];
$df=implode('-', array_reverse(explode('-', $df)));
$dt=$_REQUEST['todt'];
$dt=implode('-', array_reverse(explode('-', $dt)));
?>
<style>
/* td{font-family:arial narrow;font-size:14;color:black;}

 select
  {border-style:ridge;
   border-color:black;
   height:25px;
   border-size=1;
   font-family:verdana;
   color:black;
  }
 input
  {border-style:ridge;
   border-color:#22437f;
   background-color:white;
   height:25px;
   font-weight:bold;
   border-size=1;
   font-family:sans-serif;
   color:BLACK;
  }*/
</style>
<?
// $df=$y."-".$m."-".$d;
// $dt=$y1."-".$m1."-".$d1;
?>
<script>
function show()
{
//window.open("prncbook.php?df=<?print$df?>&dt=<?print$dt?>");
}
</script>
<title>Khadi</title>
    <script type="text/javascript" src="../scripts/jquery-latest.js"></script>
  <link type="text/css" href="../includes/style1.css" rel="stylesheet" />
  <link type="text/css" href="../css/form.css" rel="stylesheet" />
    <script type="text/javascript">
        $(function() {
          if ($.browser.msie && $.browser.version.substr(0,1)<7)
          {
      $('li').has('ul').mouseover(function(){
        $(this).children('ul').show();
        }).mouseout(function(){
        $(this).children('ul').hide();
        })
          }
        });
    </script>

<body>


<table class="container"><tr><td>

 <center>
<font face=verdana size=3>
<?$fromdt=implode('-', array_reverse(explode('-', $df)));
$todt=implode('-', array_reverse(explode('-', $dt)));?>
Cash Book from <? print $fromdt.' to '.$todt?>
<table cellpadding=1 cellspacing=1 class='report'>
<tr><th width=90><center><b>Date</th> <th><center><b>Ledger</th> <th width=100><center><b>Receipt</th><th width=100><center><b> Payment</th><th width=100>Closing</th>

<?
$bal=mysqli_query($db,"select op from ledger where led='CASH IN HAND' and brcode='$brcode'");
$sql="select op from ledger where led='CASH IN HAND' and brcode='$brcode'";
$opbalance=mysqli_fetch_row($bal);
//$opbal = $opbalance[0];
$res=mysqli_query($db,"select acfrom from acperiod");
$r1=mysqli_fetch_row($res);
$ssq="select sum(dramt)-sum(cramt) from journal2 where brcode='$brcode' and date>='$r1[0]' and date<'$df' and (partic='CASH IN HAND')";

$res2=mysqli_query($db,$ssq)or die('jour err:'.mysqli_error($db));
$r2=mysqli_fetch_row($res2);

$opbal=($opbalance[0]+$r2[0]);
/*
$quer1="select sum(dramt),sum(cramt) from journal2 where date < '$df' and brcode='$brcode' and left(j_type,7)!='Counter' and j_type!='Sales Return Cash' and partic='CASH IN HAND' and left(narr,19) != 'Cash Received .(CR:'";
$res21=mysqli_query($db,$quer1)or die('jour err:'.mysqli_error($db));
$r21=mysqli_fetch_row($res21);
$quer="select sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(total*staffdisc/100),sum(nettot) from prod_invoice where invdate<'$df' and brcode='$brcode' ";
$res2=mysqli_query($db,$quer)or die('jour err:'.mysqli_error($db));
$r2=mysqli_fetch_row($res2);
$quer_2="select sum(nettot) from prod_invoice where invdate<'$df' and brcode='$brcode' and pmode = 'CREDIT'";
$res_2=mysqli_query($db,$quer_2)or die('jour err:'.mysqli_error($db));
$r_2=mysqli_fetch_row($res_2);
$opbal = $opbalance[0];
  $opbal = $opbal + $r21[0]-$r21[1];
     // $mstaffdisc_amt1 = $r2[6];
      $mstaffdisc_amt = 0;
      $mdummy = 0;
      $rbt_tbl=mysqli_query($db,"select item_code,qty,staffdisc,total from prod_invoice where brcode = '$brcode' and invdate < '$df' and staffdisc > 0");
      while($rrow=mysqli_fetch_array($rbt_tbl))
      {
            $rbt_val=mysqli_query($db,"select rbtbl,rbtvl from partmaster where partcode = '$rrow[0]'");
            $row22=mysqli_fetch_row($rbt_val);
            if($row22[0]=='Yes')
            {
                   $mstaffdisc_amt += ($rrow[1]*$row22[1])*$rrow[2]/100;

                   }
            else
            {
                   $mstaffdisc_amt += $rrow[3]*$rrow[2]/100;
                   $mdummy +=  $rrow[3]*$rrow[2]/100;

             }
      }
     $mval = $mstaffdisc_amt;
if($r2[0]>0)
{
    $opbal = $opbal + $r2[0]-$r2[1]-$r2[2]-$r2[3]-$r2[4]+$r2[5]-$mval;
}
if($r_2[0]>0)
{
  $opbal = $opbal - $r_2[0];
}
$quer="select sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(total*staffdisc/100),sum(nettot) from salesretn where invdate<'$df' and biltpe=1 and brcode='$brcode' ";
$res22=mysqli_query($db,$quer)or die('jour err:'.mysqli_error($db));
$r22=mysqli_fetch_row($res22);
if($r22[0]>0)
{
  $opbal = $opbal - $r22[0]+$r22[1]+$r22[2]+$r22[3]+$r22[4]-$r22[5]+$r22[6];
}

*/
$result1=mysqli_query($db,"select distinct date from journal2 where brcode='$brcode' and date>='$df' and date<='$dt' and partic='CASH IN HAND' and left(narr,19) != 'Cash Received .(CR:' order by date")or die(mysqli_error($db));
$head=1;
$table="tempjournal".$brcode.$user;
mysqli_query($db,"drop table $table");
$qr="create table $table(invdt date,partic varchar(200),dbttot double(14,2),crtot double(14,2))";
mysqli_query($db,$qr);
while($row1=mysqli_fetch_array($result1))
{
  $tr=0;
  $tp=0;
    $sql1=mysqli_query($db,"select date,partic,dramt,cramt,jvno from journal2 where date='$row1[date]' and brcode='$brcode' and left(j_type,7)!='Counter' and j_type!='Sales Return Cash' and partic='CASH IN HAND' and left(narr,19) != 'Cash Received .(CR:'");
    while($row12=mysqli_fetch_array($sql1))
    {
        $sql12=mysqli_query($db,"select partic,j_type,narr,tr_no from journal2 where brcode='$brcode' and jvno = '$row12[jvno]' and partic!='CASH IN HAND'");
        $row13=mysqli_fetch_array($sql12);
        $sql="insert into $table(invdt,partic,dbttot,crtot) values ('$row12[date]','$row13[0] ($row13[1]-$row13[3])-$row13[2])',$row12[dramt],$row12[cramt])";
        $rs=mysqli_query($db,$sql);
    }
}
if($opbal>0)
{
print "<tr><td></td><td><font color=green>To Opening Balance</td><td align=right><font color=green>".number_format($opbal,2,'.',',')."</td><td></td></tr>";
}
else
{
print "<tr ><td></td><td><font color=green>To Opening Balance</td><td align=right><font color=red>".number_format($opbal,2,'.',',')."</td><td></td></tr>";
}
//------------------------************** From Sales
$quer="select invdate,gname,sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(total*staffdisc/100),sum(nettot) from prod_invoice where invdate>='$df' and invdate<='$dt' and brcode='$brcode'  group by invdate,gname";
//echo $quer;
$rsqr=mysqli_query($db,$quer);
while($row=mysqli_fetch_array($rsqr))
{
  if($row[2]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Sale','$row[2]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[3]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Reduction',0,'$row[3]')";
      mysqli_query($db,$rqury);
    }
    if($row[4]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].MDA',0,'$row[4]')";
      mysqli_query($db,$rqury);
    }
    if($row[5]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].StateRebateNormal',0,'$row[5]')";
      mysqli_query($db,$rqury);
    }
    if($row[6]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].StateRebateSpecial',0,'$row[6]')";
      mysqli_query($db,$rqury);
    }
    if($row[7]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Tax','$row[7]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[8]>0)
    {
            //$mstaffdisc_amt1 = $row[8];
            $mstaffdisc_amt = 0;
            $mdummy=0;
            $rbt_tbl=mysqli_query($db,"select item_code,qty,staffdisc,total from prod_invoice where brcode = '$brcode' and invdate = '$row[0]' and gname = '$row[1]' and staffdisc > 0");
            while($rrow=mysqli_fetch_array($rbt_tbl))
            {
                $rbt_val=mysqli_query($db,"select rbtbl,rbtvl from partmaster where partcode = '$rrow[0]'");
                $row22=mysqli_fetch_row($rbt_val);
                if($row22[0]=='Yes')
                    $mstaffdisc_amt += ($row22[1]*$rrow[1])*$rrow[2]/100;
                else
                {
                    $mstaffdisc_amt += $rrow[3]*$rrow[2]/100;
                    $mdummy +=  $rrow[3]*$rrow[2]/100;
                    }
            }
            //if($mstaffdisc_amt>0)
                $mval = $mstaffdisc_amt;
            //else
              //  $mval = $mstaffdisc_amt1;
      $rqury="insert into $table values('$row[0]','$row[1].Staffdisc',0,$mval)";
      mysqli_query($db,$rqury);
    }
    //if($row[9]>0)
    //{
    //  $rqury="insert into $table values('$row[0]','$row[1].Cash',$row[9],0)";
    //  mysqli_query($db,$rqury);
    //}
    //echo $rqury;
    //echo "<br>";
}
//----------------------*** From Sales Return

//------------------------************** From Sales
$quer="select invdate,sum(nettot) from prod_invoice where invdate>='$df' and invdate<='$dt' and brcode='$brcode' and pmode = 'CREDIT' group by invdate";
//echo $quer;
$rsqr=mysqli_query($db,$quer);
while($row=mysqli_fetch_array($rsqr))
{
    if($row[0]>0)
    {
      $rqury="insert into $table values('$row[0]','Debtors',0,'$row[1]')";
      mysqli_query($db,$rqury);
    }
}
//----------------------*** From Sales Return


$quer="select invdate,gname,sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(total*staffdisc/100),sum(nettot) from salesretn where invdate>='$df' and invdate<='$dt' and biltpe=1 and brcode='$brcode'  group by invdate,gname";
//echo $quer;
$rsqr=mysqli_query($db,$quer);
while($row=mysqli_fetch_array($rsqr))
{
  if($row[2]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Sales Return',0,'$row[2]')";
      mysqli_query($db,$rqury);
    }
    if($row[3]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Reduction (Sales Return)','$row[3]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[4]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].MDA (Sales Return)','$row[4]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[5]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].StateRebateNormal (Sales Return)','$row[5]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[6]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].StateRebateSpecial (Sales Return)','$row[6]',0)";
      mysqli_query($db,$rqury);
    }
    if($row[7]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Tax (Sales Return)',0,'$row[7]')";
      mysqli_query($db,$rqury);
    }
    if($row[8]>0)
    {
      $rqury="insert into $table values('$row[0]','$row[1].Staffdisc (Sales Return)',$row[8],0)";
      mysqli_query($db,$rqury);
    }
    //if($row[9]>0)
    //{
    //  $rqury="insert into $table values('$row[0]','$row[1].Cash',$row[9],0)";
    //  mysqli_query($db,$rqury);
    //}
    //echo $rqury;
    //echo "<br>";
}

$fnl=mysqli_query($db,"select * from $table order by invdt,partic");
$fn2=mysqli_query($db,"select * from $table order by invdt,partic");
$row12=mysqli_fetch_row($fn2);
$mdate = $row12[0];
$mtotdr = $opbal;
$mtotcr = 0;
while($frow=mysqli_fetch_array($fnl))
{
  //echo "sdf".$mdate;

  if($mdate < $frow[0])
  {
    print "<tr><td colspan=2><td align=right><font color=green>".number_format($mtotdr,2,'.',',')."<br><hr></td><td align=right> <font color=green>".number_format($mtotcr,2,'.',',')."<br><hr></td><td></td></tr>";
    $closebal=$mtotdr-$mtotcr;
        if ($closebal<0) {
            print "<tr ><td colspan=3 align=center><font color=green><b>by Closing Balance</td><td></td><td align=right><font color=red>" . number_format($closebal, 2, '.', ',') . "</td>";
            print "<tr><td></td><td align=left><font color=green><b>To Opening Balance</td><td align=right><font color=red>".number_format($closebal,2,'.',',')."</td><td></td></tr>";
        }
        else
        {
            print "<tr ><td colspan=3 align=center><font color=green><b>by Closing Balance</td><td></td><td align=right><font color=green>" . number_format($closebal, 2, '.', ',') . "</td>";
            print "<tr><td></td><td align=left><font color=green><b>To Opening Balance</td><td align=right><font color=green>".number_format($closebal,2,'.',',')."</td><td></td><td></td></tr>";
        }
    $mtotcr = 0;
    $mtotdr = $closebal;
    $mdate  = $frow[0];
  }
  $date1=implode('-', array_reverse(explode('-', $frow[0])));
  print "<tr ><td>".$date1."</td><td>".$frow[1]."</td>";
  //echo $fnl_row[0];
    print "<td align=right>".$frow[2]."</td><td align=right>".$frow[3]."</td><td></td>";
  print "</tr>";
  $mtotdr+= $frow[2];
  $mtotcr+= $frow[3];

//  $tp+=$fnl_row[2]; $tr+=$fnl_row[3];
//  $closebal=($tr+$opbal)-$tp;
}
print "<tr><td colspan=2><td align=right><font color=green>".number_format($mtotdr,2,'.',',')."<br><hr></td><td align=right> <font color=green>".number_format($mtotcr,2,'.',',')."<br><hr></td><td></td></tr>";
$closebal=$mtotdr-$mtotcr;
if ($closebal<0) {
    print "<tr ><td colspan=3 align=center><font color=green><b>by Closing Balance</td><td></td><td align=right><b><font color=red>" . number_format($closebal, 2, '.', ',') . "</td>";
}
else
{
    print "<tr ><td colspan=3 align=center><font color=green><b>by Closing Balance</td><td></td><td align=right><b><font color=green>" . number_format($closebal, 2, '.', ',') . "</td>";
}
?>
</table>