<?
session_start();
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;
}
$dfrom=$_REQUEST['dfrom'];
$dto=$_REQUEST['dto'];
?>
<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=$dfrom;
 $dt=$dto;
?>
<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>
<?
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
$file =  'cshbook.prn';
$handle = fopen($file, 'w');
$dss=chr(27).chr(71).chr(N);
$dse=chr(27).chr(72);
$initialized = chr(27).chr(64);
$load = chr(27).chr(25).chr(66);
$bold = chr(27).chr(14);
$unbold = chr(20);
$pagelenth=chr(27).chr(67).chr(36);
$Data = $initialized;
$Data .= "                  KERALA KHADI & VILLAGE INDUSTRIES BOARD\n";
$Data .= "                     CENTER NAME : ".$brnchnm." \n";
$Data .=  "                 Cash Book From $df To $dt".chr(15)."\n\n";
$df1=$df;
$dt1=$dt;
$df=implode('-', array_reverse(explode('-', $df)));
$dt=implode('-', array_reverse(explode('-', $dt)));
$Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
$Data .= " |     Date     |                         Ledger                        |     Receipt      |       Payment      |      Closing     |\n";
$Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
$bal=mysqli_query($db,"select op from ledger where led='Cash' and brcode='$brcode'");
$sql="select op from ledger where led='Cash' and brcode='$brcode'";
$opbalance=mysqli_fetch_row($bal);
$res=mysqli_query($db,"select acfrom from acperiod");
$r1=mysqli_fetch_row($res);

$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' 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];
//if($r21[0]+$r21[0]>0)
//{
  $opbal = $opbal + $r21[0]-$r21[1];
//}
$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' and left(narr,19) != 'Cash Received .(CR:' order by date")or die(mysqli_error($db));
$head=1;
$table="tempjournal".$brcode.$user;
mysqli_query($db,"delete from $table");
$qr="create table $table(invdt date,partic varchar(100),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' and left(narr,19) != 'Cash Received .(CR:'");
    while($row12=mysqli_fetch_array($sql1))
    {
        $sql12=mysqli_query($db,"select partic,j_type,narr from journal2 where brcode='$brcode' and jvno = $row12[jvno] and partic!='Cash' and left(narr,19) != 'Cash Received .(CR:'");
        $row13=mysqli_fetch_array($sql12);
        $sql="insert into $table(invdt,partic,dbttot,crtot) values ('$row12[date]','$row13[0] ($row13[1]-$row13[2])',$row12[dramt],$row12[cramt])";
        $rs=mysqli_query($db,$sql);
    }
}
if($opbal>0)
{
  $mnum = number_format($opbal, 2, '.', '');
  $mstr  = substr(str_repeat(" ",17).$mnum,-16);
  $Data .= " |              |  Opening Balance                                      |".$mstr."  |                    |                  |\n";
  //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
{
  $mnum = number_format($opbal, 2, '.', '');
  $mstr  = substr(str_repeat(" ",17).$mnum,-16);
  $Data .= " |              |  Opening Balance                                      |".$mstr."  |                    |                  |\n";
  //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_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;
$mline_ctr = 2;
while($frow=mysqli_fetch_array($fnl))
{
       if($mdate < $frow[0])
       {
           $mnum = number_format($mtotdr, 2, '.', '');
           $mstr  = substr(str_repeat(" ",17).$mnum,-16);
           $mnum1 = number_format($mtotcr, 2, '.', '');
           $mstr1  = substr(str_repeat(" ",17).$mnum1,-17);
           $Data .= " |              |                                                       |".$mstr."  | ".$mstr1."  |                  |\n";
           $closebal=$mtotdr-$mtotcr;
           if ($closebal<0)
           {
                $mnum = number_format($closebal, 2, '.', '');
                $mstr  = substr(str_repeat(" ",17).$mnum,-17);
                $Data .= " |              |    by Closing Balance                                 |                  |                    |".$mstr." |\n";
                $mnum = number_format($closebal, 2, '.', '');
                $mstr  = substr(str_repeat(" ",17).$mnum,-17);
                $Data .= " |              |    To Opening Balance                                 |                  | ".$mstr."  |                  |\n";
                $mline_ctr=$mline_ctr+2;
           }
           else
           {
               $mnum = number_format($closebal, 2, '.', '');
               $mstr  = substr(str_repeat(" ",17).$mnum,-17);
               $Data .= " |              |    by Closing Balance                                 |                  |                    |".$mstr." |\n";
               $mnum = number_format($closebal, 2, '.', '');
               $mstr  = substr(str_repeat(" ",17).$mnum,-17);
               $Data .= " |              |    To Opening Balance                                 |                  | ".$mstr."  |                  |\n";
               $mline_ctr=$mline_ctr+2;
           }
           $mtotcr = 0;
           $mtotdr = $closebal;
           $mdate  = $frow[0];
       }
       $dfrm=implode('-', array_reverse(explode('-', $frow[0])));
       $mstr  = substr($frow[1].str_repeat(" ",50),0,50);
       $mnum = number_format($frow[2], 2, '.', '');
       $mstr1 =substr(str_repeat(" ",17).$mnum,-16);
       $mnum1 = number_format($frow[3], 2, '.', '');
       $mstr2 = substr(str_repeat(" ",17).$mnum1,-17);
       $Data .= " | ".$dfrm."   | ".$mstr."    |".$mstr1."  | ".$mstr2."  |                  |\n";
       $mtotdr+= $frow[2];
       $mtotcr+= $frow[3];
       if ($mline_ctr>49)
       {
            $mline_ctr = 0;
            $Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
            $Data .= "\n\n\n\n";
            $Data .= chr(18);
            $Data .= chr(12);
            $Data .=  "       Cash Book From $df1 To $dt1".chr(15)."\n\n";
            $Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
            $Data .= " |     Date     |                         Ledger                        |     Receipt      |       Payment      |      Closing     |\n";
            $Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
       }
       $mline_ctr++;
}
$mnum = number_format($mtotdr, 2, '.', '');
$mstr  = substr(str_repeat(" ",17).$mnum,-16);
$mnum1 = number_format($mtotcr, 2, '.', '');
$mstr1  = substr(str_repeat(" ",17).$mnum1,-17);
$Data .= " |              |                                                       |".$mstr."  | ".$mstr1."  |                  |\n";
$closebal=$mtotdr-$mtotcr;
$Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
if ($closebal<0) {
      $mnum = number_format($closebal, 2, '.', '');
      $mstr  = substr(str_repeat(" ",17).$mnum,-17);
      $Data .= " |              |    by Closing Balance                                 |                  |                    |".$mstr." |\n";
}
else
{
      $mnum = number_format($closebal, 2, '.', '');
      $mstr  = substr(str_repeat(" ",17).$mnum,-17);
      $Data .= " |              |    by Closing Balance                                 |                  |                    |".$mstr." |\n";
}
$Data .= " |--------------|-------------------------------------------------------|------------------|--------------------|------------------|\n";
$Data .= chr(18);
$Data .=chr(12);
fwrite($handle, $Data);
fclose($handle);
fclose($handle);
$output = shell_exec("lp -o media=Custom.10x6in '$file'");
$output=shell_exec("C:\\WINDOWS\\system32\\cmd.exe /c prt.bat");
header('Location:cbookmnu.php');
?>
</table>