<?
session_start();
$brcode=$_SESSION['brcode'];
$branch=$_SESSION['branch'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='inde.php' </script>";
  return;
}
include_once("test.php");
include_once("opendb.php");
$br=str_replace(' ','',$branch);
$tbl='cshrcrpt'.$br.$user;
mysqli_query($db,"delete from $tbl");
mysqli_query($db,"create table $tbl(partic varchar(100),dr_op double(14,2) default 0,cr_op double(14,2)default 0,cr_no varchar(10), cr_date date, dr_tr double(14,2) default 0,cr_tr double(14,2)default 0)");
$df=$_REQUEST['dfrom'];
$dt=$_REQUEST['dto'];
//echo $dt;
$dt11 = implode('-', array_reverse(explode('-', $dt)));
$df11 = implode('-', array_reverse(explode('-', $df)));
//$dt = implode('-', array_reverse(explode('-', $dt)));
//$df = implode('-', array_reverse(explode('-', $df)));

$file =  'debtcollect_rpt.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 .= "       Debtors Collection from for the period from <$df> to <$dt>";
  $Data .= chr(15)."\n\n";
  $Data .= "|----------------------------------|---------------|----------|---------------|-----------------|---------------|-------|-------------|\n";
  $Data .= "|           Receipt No            | Receipt Date | Customer |Opening Balance|Collection Amount|Purchase Amount| Closing| Net Balance |\n";
  $Data .= "|----------------------------------|---------------|----------|---------------|-----------------|---------------|-------|-------------|\n";


mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',led,op,0,'XXXOP' from ledger where brcode='$brcode' and op >= 0 and grp ='Sundry Debtors'");
mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',led,0,-1*op,'XXXOP' from ledger where brcode='$brcode' and op < 0 and grp ='Sundry Debtors'");
mysqli_query($db,"insert into $tbl(cr_date,partic,dr_op,cr_op,cr_no) select '0000-00-00',partic,sum(dramt),sum(cramt),'OP_J' from journal2 where brcode='$brcode' and date < '$df11' group by partic");

$re=mysqli_query($db,"select partic,sum(dr_op),sum(cr_op),max(cr_no) from $tbl group by partic");
$mcl_tot = 0;
$mop_tot = 0;


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

 if ($row[3]=='XXXOP')
  {
	  $mf = 'N';
    $mop = $row[1]-$row[2];
    $mflg = 'N';
    $mop_tot += $mop;
    $re1=mysqli_query($db,"select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df11' and date <= '$dt11' order by date,cramt asc,tr_no");
	$sql="select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df11' and date <= '$dt11' order by date,cramt asc,tr_no";
	//echo $sql;
	while($row1=mysqli_fetch_array($re1))
    {
      $totmop+=$mop;
	  if($row1[3] > 0)
      {
        $mflg = 'Y';
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop - $row1[3];
		$mdet=$row1[5].' '.$row1[4];
		if($row1[5]=='Sales Return Credit')
		  {
			$mdet='Sales Return '.substr($row1[6],-6);
		  }
			

		$Data .= "| ".substr(str_repeat(" ",16).$mdet,-16);
      $mnum = $dt1;
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = $row1[1];
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($mop, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($row1[3], 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = '0.00';
	  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
	  $mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";

		 


        $mf = 'Y';
        $mop = $mcls;
      }

	  if($row1[2] > 0 )
      {
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop + $row1[2];
        $mdet=substr($row1[6],-15);

		$Data .= "| ".substr($mdet.str_repeat(" ",33),0,33);
      $mnum = $dt1;
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = $row1[1];
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($mop, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($row1[2], 2, '.', '');
      
	  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
	  $mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |";




        
        $mf = 'Y';
        $mop = $mcls;
      }
      $totmcls+=$mcls;
      $totpramt+=$row1[2];
      $totcolamt+=$row1[3];
    
	}
	 if(abs($mcls)>0.1)
	  {
		$mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";
	  }
        
    else
    {
        if ($mf=='Y')
		{
		 $mnum = '0.00';
		$Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";
        }
    $mcl_tot +=  $mcls;
    $mcls=0;
  }
  }

	/*
  if ($row[3]=='XXXOP')
  {
	  $mf = 'N';
    $mop = $row[1]-$row[2];
    $mflg = 'N';
    $mop_tot += $mop;
    $re1=mysqli_query($db,"select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df11' and date <= '$dt11' order by date,cramt asc,tr_no");
	$sql="select date,partic,dramt,cramt,tr_no,j_type,narr from journal2 where dramt+cramt > 0 and brcode = '$brcode' and partic = '$row[0]' and date >= '$df11' and date <= '$dt11' order by date,cramt asc,tr_no";
	echo $sql;
	echo "<br>";
	
    while($row1=mysqli_fetch_array($re1))
    {
      $totmop+=$mop;
	  
      if($row1[3] > 0)
      {
        $mflg = 'Y';
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop - $row1[3];
		$mdet=$row1[5].' '.$row1[4];
		if($row1[5]=='Sales Return Credit')
		  {
			$mdet='Sales Return '.substr($row1[6],-6);
		  }
			

		$Data .= "| ".substr($mdet.str_repeat(" ",33),0,33);
      $mnum = $dt1;
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = $row1[1];
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($mop, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($row1[3], 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = '0.00';
	  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
	  $mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";

		 


        $mf = 'Y';
        $mop = $mcls;
      }
	 
      if($row1[2] > 0 )
      {
        $dt1 = implode('-', array_reverse(explode('-', $row1[0])));
        $mcls = $mop + $row1[2];
        $mdet=substr($row1[6],-15);

		$Data .= "| ".substr($mdet.str_repeat(" ",33),0,33);
      $mnum = $dt1;
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = $row1[1];
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($mop, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
      $mnum = number_format($row1[2], 2, '.', '');
      
	  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
	  $mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |";




        
        $mf = 'Y';
        $mop = $mcls;
      }
      $totmcls+=$mcls;
      $totpramt+=$row1[2];
      $totcolamt+=$row1[3];
    }

 
    if(abs($mcls)>0.1)
		$mnum = number_format($mcls, 2, '.', '');
      $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";
        
    else
    {
        if ($mf=='Y')
             
		 $mnum = '0.00';
		$Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";
        }
    $mcl_tot +=  $mcls;
    $mcls=0;
  }
  */

}











$Data .= "|----------------------------------|-----------------|-----------------|-----------------|-----------------|-----------------|\n";
   $Data .= "|           T O T A L              ";
  $mnum = number_format($mop_tot, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
  $mnum = number_format($totcolamt, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
  $mnum = number_format($totpramt, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
  $mnum = number_format($mtbnk_amt, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." ";
  $mnum = number_format($mcl_tot, 2, '.', '');
  $Data .= "|".substr(str_repeat(" ",16).$mnum,-16)." |\n";






   
  
  

  $Data .= "|----------------------------------|-----------------|-----------------|-----------------|-----------------|-----------------|\n";
  $Data .= chr(18);
$Data .= chr(12);
fwrite($handle, $Data);
fclose($handle);
//print "lp -o media=Custom.10x6in $file";
$output = shell_exec("lp -o media=Custom.10x6in '$file'");
$output=shell_exec("C:\\WINDOWS\\system32\\cmd.exe /c debtcollect_rpt.bat");
header('location:cshrecrept1.php');




  
?>