<?
session_start();
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
 return;
}
include_once('testtot.php');
$fromdt=$_REQUEST['dfrom'];
$todt=$_REQUEST['dto'];
$type=$_REQUEST['type'];
$dfrm=$fromdt;
$dto=$todt;
$df=implode('-', array_reverse(explode('-', $fromdt)));
$dt = implode('-', array_reverse(explode('-', $todt)));;
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);

$brqry="select brname from branch where brcode='$brcode'";
$brrs=mysqli_query($db,$brqry);
$brrw=mysqli_fetch_array($brrs);
$brnchnm=$brrw[0];
  $dfrm=implode('-', array_reverse(explode('-', $df)));
  $dto=implode('-', array_reverse(explode('-', $dt)));
  $file =  'rdreport_prn.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 AND VILLAGE INDUSTRIES BOARD \n";
  $Data .= "                     CENTER NAME : ".$brnchnm."\n";
  $Data .= "      RECEIPT AND DISBURSEMENT STATEMENT FROM <$dfrm> TO <$dto>\n";
  $Data ." \n\n";
$Data .= "|------------------------------------------------|--------------|--------------|\n";
$Data .= "|                Particulars                     |   Receipts   |   Payments   |\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,5)!='Sales' and left(j_type,7)!='Counter' and partic='Cash'";
$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(staffdisc),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 += ($row22[1]*$rrow[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(staffdisc),sum(nettot) from salesretn where invdate<'$df' 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];
}
$mtotdr = $opbal;
//$Data .= "|-------------------------------------------------|--------------|--------------|\n";
    $Data .= "| Opening Balance                                ";
    $mnum = number_format($opbal, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
//print "<tr><td>Opening Balance </td><td align='right'>".number_format($opbal,2,'.',',')."</td><td align='right'>0.00</td></tr>";

$quer12="select led from ledger where brcode='$brcode' and grp = 'Bank Accounts'";
$res12=mysqli_query($db,$quer12)or die('jour err:'.mysqli_error($db));
$mremittance = 0;
$bnk_remittance = 0;
while($row1=mysqli_fetch_array($res12))
{
  $quer13="select sum(dramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and partic = '$row1[0]'";
  $res13=mysqli_query($db,$quer13)or die('jour err:'.mysqli_error($db));
  $r13=mysqli_fetch_row($res13);
  $mremittance = $mremittance + $r13[0];
  $quer13="select sum(dramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and partic = '$row1[0]' and j_type = 'BR'";
  $res13=mysqli_query($db,$quer13)or die('jour err:'.mysqli_error($db));
  $r13=mysqli_fetch_row($res13);
  $bnk_remittance += $r13[0];
}
$mtotcr = $mremittance;
$Data .= "| Remittance                                     |          0.00";
$mnum = number_format($mremittance, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
$Data .= "| Collection through bank                        ";
$mnum = number_format($bnk_remittance, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
//print "<tr><td>Remittance</td><td align='right'>0.00</td><td align='right'>".number_format($mremittance,2,'.',',')."</td></tr>";
//print "<tr><td>Collection through bank</td><td align='right'>".number_format($bnk_remittance,2,'.',',')."</td><td align='right'>0.00</td></tr>";
$mtotdr += $bnk_remittance;
$quer14="select invdate,gname,sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(staffdisc),sum(nettot),pmode from prod_invoice where invdate>='$df' and invdate<='$dt' and brcode='$brcode'  group by gname";
$res14=mysqli_query($db,$quer14)or die('jour err:'.mysqli_error($db));
$taxamt = 0;
$mdebtors =0;
while($row14=mysqli_fetch_array($res14))
{
  $mstr = $row14[gname]." Sales".str_repeat(" ",46);
  $Data .= "| ".substr($mstr,0,47);
  $mnum = number_format($row14[2], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
  $Data .= "|    MDA                                         |          0.00";
  $mnum = number_format($row14[4], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  $Data .= "|    State Rebate                                |          0.00";
  $mnum = number_format($row14[5], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  $Data .= "|    State Rebate Special                        |          0.00";
  $mnum = number_format($row14[6], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  $Data .= "|    Reduction                                   |          0.00";
  $mnum = number_format($row14[3], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
    $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 invdate <= '$dt'  and gname = '$row14[gname]' 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;
                    }
    }
    $mval = $mstaffdisc_amt;
     $Data .= "|    Staff Discount                              |          0.00";
  $mnum = number_format($mval, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  //print "<tr><td align ='left'><b>$row14[gname] Sales</td><td align='right'>".$row14[2]."</td><td align='right'>0.00</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbsp MDA</td><td align='right'>0.00</td><td align='right'>".$row14[4]."</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbspState Rebate</td><td align='right'>0.00</td><td align='right'>".$row14[5]."</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbspState Rebate Special</td><td align='right'>0.00</td><td align='right'>".$row14[6]."</td></tr>";
  $taxamt = $taxamt + $row14[7];
  $mtotdr = $mtotdr + $row14[2];
  $mtotcr = $mtotcr + $row14[4] + $row14[5] + $row14[6] + $row14[3] + $mval;
}
$quer14="select sum(nettot),pmode from prod_invoice where invdate>='$df' and invdate<='$dt' and brcode='$brcode' and pmode = 'CREDIT'";
$res14=mysqli_query($db,$quer14)or die('jour err:'.mysqli_error($db));
$r13=mysqli_fetch_row($res14);
$mdebtors =$r13[0];
$mtotdr = $mtotdr + $taxamt;
$mtotcr +=$mdebtors;
$Data .= "|    Debtors                                     |          0.00";
$mnum = number_format($mdebtors, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
$Data .= "|    VAT Collected                               ";
$mnum = number_format($taxamt, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
//print "<tr><td align ='left'>&nbsp&nbsp&nbspDebtors</td><td align='right'>0.00</td><td align='right'>".number_format($mdebtors,2,'.',',')."</td></tr>";
//print "<tr><td align ='left'>&nbsp&nbsp&nbspVAT Collected</td><td align='right'>".number_format($taxamt,2,'.',',')."</td><td align='right'>0.00</td></tr>";
$quer15="select invdate,sum(nettot) from prod_invoice where invdate>='$df' and invdate<='$dt' and brcode='$brcode' and pmode = 'CREDIT' group by invdate";
$row15=mysqli_query($db,$quer15);
$mtotdr = $mtotdr + $row15[1];
$Data .= "|    Sundry Debtors                              |          0.00";
$mnum = number_format($row15[1], 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
//print "<tr><td align ='left'>&nbsp&nbsp&nbspSundry Debtors</td><td align='right'>0.00</td><td align='right'>".number_format($row15[1],2,'.',',')."</td></tr>";

$quer13="select sum(dramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and j_type = 'CR'";
$res13=mysqli_query($db,$quer13)or die('jour err:'.mysqli_error($db));
$r13=mysqli_fetch_row($res13);
$mdr_collection = $r13[0];
$mtotdr+= $mdr_collection;
$Data .= "|    Debtors Collection                          ";
$mnum = number_format($mdr_collection, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";

$quer13="select sum(cramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and (partic = 'BANK CHARGES FOR CREDIT CARD' or partic = 'BANK CHARGES')";
$res13=mysqli_query($db,$quer13)or die('jour err:'.mysqli_error($db));

$quer22="select sum(dramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and partic = 'Cash' and j_type='BP'";
$res22=mysqli_query($db,$quer22)or die('jour err:'.mysqli_error($db));
$r23=mysqli_fetch_row($res22);
$mtotdr+= $r23[0];
if($r23[0]>0)
{
  $Data .= "|     Cash Cheque Dishounored                  ";
  $mnum = number_format($r23[0], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
}

$quer22="select sum(dramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and partic = 'Cash' and j_type='JV' and narr='Cheque Dishonour Charge'";
$res22=mysqli_query($db,$quer22)or die('jour err:'.mysqli_error($db));
$r23=mysqli_fetch_row($res22);
$mtotdr+= $r23[0];
if($r23[0]>0)
{
	$Data .= "|     Cash Collected(Bank Charges)Cheque Dishounored                  ";
  $mnum = number_format($r23[0], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
}
$r13=mysqli_fetch_row($res13);
$mdr_bnk_chrgs = $r13[0];
$mtotdr+= $mdr_bnk_chrgs;
if ($mdr_bnk_chrgs>0)
{
  $Data .= "|    Bank Charges (Credit Card)                  ";
  $mnum = number_format($mdr_bnk_chrgs, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
}
/*$quer13="select sum(cramt) from journal2 where date >= '$df' and date <= '$dt' and brcode='$brcode' and partic = 'BANK CHARGES'";
$res13=mysqli_query($db,$quer13)or die('jour err:'.mysqli_error($db));
$r13=mysqli_fetch_row($res13);
$mdr_bnk_chrgs1 = $r13[0];
$mtotdr+= $mdr_bnk_chrgs1;
if ($mdr_bnk_chrgs1>0)
{
  $Data .= "|    Bank Charges                                ";
  $mnum = number_format($mdr_bnk_chrgs1, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
}*/
//print "<tr><td align ='left'>&nbsp&nbsp&nbspDebtors Collection</td><td align='right'>".number_format($mdr_collection,2,'.',',')."</td><td align='right'>0.00</td></tr>";
$quer16="select invdate,gname,sum(total),sum(redct),sum(mba),sum(strbtnml),sum(strbtspl),sum(taxamt),sum(staffdisc),sum(nettot) from salesretn where invdate>='$df' and invdate<='$dt' and brcode='$brcode'  group by gname";
$res16=mysqli_query($db,$quer16);
$taxamt = 0;
while($row16=mysqli_fetch_array($res16))
{
  $mtotcr = $mtotcr + $row16[2];
  $mtotdr = $mtotdr + $row16[4]+$row16[5]+$row16[6];
  $Data .= "| ".substr(str_repeat(" ",47)."Sales Return ".$row16[gname],47)."|          0.00|";
  $mnum = number_format($row16[2], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  $Data .= "|    MDA                                         ";
  $mnum = number_format($row16[4], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
  $Data .= "|    State Rebate                                ";
  $mnum = number_format($row16[5], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
  $Data .= "|    State Rebate Special                        ";
  $mnum = number_format($row16[6], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
  //print "<tr><td align ='left'><b>Sales Return&nbsp$row16[gname] </td><td align='right'>0.00</td><td align='right'>".$row16[2]."</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbsp MDA</td><td align='right'>".$row16[4]."</td><td align='right'>0.00</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbspState Rebate</td><td align='right'>".$row16[5]."</td><td align='right'>0.00</td></tr>";
  //print "<tr><td align ='left'>&nbsp&nbsp&nbspState Rebate Special</td><td align='right'>".$row16[6]."</td><td align='right'>0.00</td></tr>";
  $taxamt = $taxamt + $row16[7];
}
$mtotcr = $mtotcr + $taxamt;
$Data .= "|    VAT Amount (Sales Return)                   |          0.00";
$mnum = number_format($taxamt, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
//print "<tr><td align ='left'>&nbsp&nbsp&nbspVAT Amount (Sales Return)</td><td align='right'>0.00</td><td align='right'>".number_format($taxamt,2,'.',',')."</td></tr>";
// Purchase
$quer17="select invdate,gname,sum(total) from brtransferin where invdate>='$df' and invdate<='$dt' and brcode='$brcode'  group by gname";
$res17=mysqli_query($db,$quer17);
$taxamt = 0;
$mtot_purch = 0;
while($row17=mysqli_fetch_array($res17))
{
  $mtotcr = $mtotcr + $row17[2];
  $Data .= "| ".substr("Purchase of ".$row17[gname].str_repeat(" ",40),0,47)."|          0.00";
  print  "| ".substr("Purchase of ".$row17[gname].str_repeat(" ",40),0,47)."|          0.00";
  $mnum = number_format($row17[2], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
  //print "<tr><td align ='left'><b>Purchase of &nbsp$row17[gname] </td><td align='right'>0.00</td><td align='right'>".$row17[2]."</td></tr>";
  $mtot_purch += $row17[2];
}
$mtotdr = $mtotdr + $mtot_purch;
$Data .= "| Sundry Creditors                               ";
$mnum = number_format($mtot_purch, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";

//print "<tr><td align ='left'><b>Sundry Creditors </td><td align='right'>".$mtot_purch."</td><td align='right'>0.00</td></tr>";
$quer18="select invdate,gname,sum(total) from brtransferout where invdate>='$df' and invdate<='$dt' and brcode='$brcode'  group by gname";
$res18=mysqli_query($db,$quer18);
$taxamt = 0;
$mitem_trans = 0;
while($row18=mysqli_fetch_array($res18))
{
  $mtotdr = $mtotdr + $row18[2];
  $mitem_trans += $row18[2];
  $Data .= "| ".substr("Transfer of ".$row18[gname].str_repeat(" ",40),0,47);
  $mnum = number_format($row18[2], 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|          0.00|\n";
  //print "<tr><td align ='left'><b>Transfer of &nbsp$row18[gname] </td><td align='right'>".$row18[2]."</td><td align='right'>0.00</td></tr>";
}
$mtotcr = $mtotcr + $mitem_trans;
$Data .= "| Item Transfer                                  |          0.00";
$mnum = number_format($mitem_trans, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
//print "<tr><td align ='left'><b>Item Transfer </td><td align='right'>0.00</td><td align='right'>".$mitem_trans."</td></tr>";
$mclosing = $mtotdr-$mtotcr;
$mtotcr+=$mclosing;
$Data .= "| Closing Balance                                |          0.00";
$mnum = number_format($mclosing, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|\n";
$Data .= "|------------------------------------------------|--------------|--------------|\n";
$Data .= "| T O T A L                                      ";
$mnum = number_format($mtotdr, 2, '.', '');
$Data .= "|".substr(str_repeat(" ",14).$mnum,-14)."|";
$mnum = number_format($mtotcr, 2, '.', '');
$Data .= substr(str_repeat(" ",14).$mnum,-14)."|\n";
$Data .= "|------------------------------------------------|--------------|--------------|\n";
$Data .= chr(18);
$Data .= chr(12);
fwrite($handle, $Data);
fclose($handle);
$output = shell_exec("lp -o media=Custom.10x6in '$file'");
$output=shell_exec("C:\\WINDOWS\\system32\\cmd.exe /c rdreport_prn.bat");
 header('location:rdreport.php');
//print "<tr><td align ='left'><b>Closing Balance </td><td align='right'>0.00</td><td align='right'>".$mclosing."</td></tr>";
//print "<tr><td align ='center'><b>T O T A L </td><td align='right'>".number_format($mtotdr,2,'.',',')."</td><td align='right'>".number_format($mtotcr,2,'.',',')."</td></tr>";
?>