<?
session_start();
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
//$br=$_SESSION['branch'];
if(strlen($user)<=0)
{
  print "<script> alert('Invalid User'); document.location='inde.php' </script>";
  return;
}

			$dfrom=$_REQUEST['dfrom'];
			$dto=$_REQUEST['dto'];
			$partcode=$_REQUEST['itcod'];
			$partcode1=$_REQUEST['partcode1'];
			$itemcode=$_REQUEST['itemcode'];
			$partname=$_REQUEST['partname'];
			$type=$_REQUEST['type'];


$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);

 $file =  'stock_ledger.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 = "\n\n\n";
  $Data .= "					KERALA KHADI & VILLAGE INDUSTRIES BOARD\n";
  $Data .= "					CENTER NAME : ".$brrow[1]." \n";
	$date1 = date_create($dfrom);
	$date2 = date_create($dto);
  $Data .= "					Stock Report From ".date_format($date1, 'd-m-Y')." To ".date_format($date2, 'd-m-Y')."\n\n";




 $dfrom=implode('-', array_reverse(explode('-', $dfrom)));
 $dto=implode('-', array_reverse(explode('-', $dto)));

$tbl="sparetmpdw".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op_inqty double(10,2) default 0,op_outqty double(10,2) default 0, stkadj_in double(10,2) default 0, stkadj_out double(12,2) default 0,purch_in double(12,2) default 0,salertn_in double(12,2) default 0,trans_in double(12,2) default 0,trans_out double(12,2) default 0,sale_out double(12,2) default 0,purchrtn_out double(12,2) default 0,ref_no varchar(15),ref_date date,index (partcode))");
mysqli_query($db,"delete from $tbl");
$sql=mysqli_query($db,"select partcode from partmaster where partcode>='$itemcode' and partcode<='$partcode1' and gname='$type' ");

mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_outqty) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate<'$dfrom' and gname='$type' group by partcode") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(qty) from itempurchase where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select itemcode,itemname,sum(opstk) from sparestock where brcode='$brcode' and item_grp='$type' group by itemcode") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(qty) from brtransferin where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from purchretn where brcode='$brcode' and invdate<'$dfrom' and gname='$type' group by item_code") or die('err12'.mysqli_error($db)); 
//****************************************************************************** in out*******
mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out,ref_no,ref_date) select partcode,partname,qty,outqty,refno,entrydate from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and  gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,purch_in,ref_no,ref_date) select item_code,item,qty,supinvno,invdate from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,ref_no,ref_date) select item_code,item,rtnqty,invno,invdate from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,trans_in,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 
mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type'") or die('err12'.mysqli_error($db)); 


$sql=mysqli_query($db,"select distinct(partcode),partname from $tbl");

$Data .= "     Item Group :".$type."\n\n";
$Data .= chr(15)."\n\n";
$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
$Data.= "     |   Date.  | Ref No. | Item Code: |    Item Name   |  Transaction Details  | In Qty  | Out Qty |   Closing Bal |\n";
$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";

$tprice=0;
$i=0;

while($row9=mysqli_fetch_array($sql))
{	
	$re1=mysqli_query($db,"select sum(op_inqty),sum(op_outqty) from $tbl where partcode='$row9[0]'");
	$row12=mysqli_fetch_array($re1);
	$op_stock = $row12[0]-$row12[1];

	$re=mysqli_query($db,"select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 and partcode='$row9[0]' order by ref_date");
	$var="select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 and partcode='$row9[0]' order by ref_date";
	$cl_stock = $op_stock;
	$date1 = date_create($dfrom);



	$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
	$Data .= "|".substr(str_repeat(" ",9),0,9);
    $Data .= "|".substr($row9[0].str_repeat(" ",12),0,12);
    $Data .= "|".substr($row9[1].str_repeat(" ",16),0,16);
	$Data .= "| Opening Stock".substr(str_repeat(" ",9),-9);
    $Data .= "|".substr(str_repeat(" ",9).$op_stock,-9);
	$Data .= "|".substr(str_repeat(" ",9),0,9);
	$Data .= "|".substr(str_repeat(" ",15).$op_stock,-15)."|\n";
	$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";

	while($row=mysqli_fetch_array($re))
	{
		if(abs($row[4])>0)
		{
			$cl_stock = $cl_stock + $row[4];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Item Purchase".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[4],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
		}
		if(abs($row[5])>0)
		{
			$cl_stock = $cl_stock + $row[5];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Item Transfer In".substr(str_repeat(" ",6),-6);
			$Data .= "|".substr(str_repeat(" ",9).$row[5],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
			
		}
		if(abs($row[6])>0)
		{
			$cl_stock = $cl_stock + $row[6];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Sales Return".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[6],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
		}
		if(abs($row[7])>0)
		{
			$cl_stock = $cl_stock + $row[7];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Stock Adjustment In".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[7],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
		
		}
		if(abs($row[8])>0)
		{
			$cl_stock = $cl_stock - $row[8];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Sales Invoice".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[8],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
			
		}
		if(abs($row[9])>0)
		{
			$cl_stock = $cl_stock - $row[9];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Item Transfer Out     ";
			$Data .= "|".substr(str_repeat(" ",9).$row[9],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
			
		}

		if(abs($row[10])>0)
		{
			$cl_stock = $cl_stock - $row[10];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Purchase Return".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[10],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
			
		}
		if(abs($row[11])>0)
		{
			$cl_stock = $cl_stock - $row[11];
			$date1 = date_create($row[13]);
	 		$Data .= "     |".substr(date_format($date1, 'd-m-Y').str_repeat(" ",10),0,10);
			$Data .= "|".substr($row[12].str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",12),0,12);
			$Data .= "|".substr(str_repeat(" ",16),0,16);
			$Data .= "| Stock Adjustment Out".substr(str_repeat(" ",9),-9);
			$Data .= "|".substr(str_repeat(" ",9).$row[11],-9);
			$Data .= "|".substr(str_repeat(" ",9),0,9);
			$Data .= "|".substr(str_repeat(" ",15).number_format($cl_stock,2,'.',','),-15)."|\n";
			$Data.= "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
		}

	}

	$re2=mysqli_query($db,"select lstprice from spareprmast where partcode='$row9[0]'");
	$row13=mysqli_fetch_array($re2);
	$tprice = $cl_stock*$row13[0];

	$Data .= "     |                                Total             ";
	$Data .= "|".substr(str_repeat(" ",23),0,23);
	$Data .= "|".substr(str_repeat(" ",9),0,9);
	$Data .= "|".substr(str_repeat(" ",9).$cl_stock,-9);
	$Data .= "|Rs.".substr(str_repeat(" ",12).round($tprice,2),-12)."|\n";
	$Data.=  "     |----------|---------|------------|----------------|-----------------------|---------|---------|---------------|\n";
	
}
	
	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 stock_ledger.bat");
    header('location:stockleddw.php');
?>
