<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
	print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
	return;
}
include("../opendb1.php");
include('mysqli_table.php');
$brqry="select brname from branch where brcode='$brcode'";
$brrs=mysqli_query($db,$brqry);
$brrw=mysqli_fetch_array($brrs);
$brnchnm=$brrw[0];
$fromdt=$_REQUEST['dfrom'];
$todt=$_REQUEST['dto'];
$type=$_REQUEST['type'];
$dfrom = implode('-', array_reverse(explode('-', $fromdt)));
$dto = implode('-', array_reverse(explode('-', $todt)));

$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brname= $brrow[brname];
$brcity= $brrow[brcity];
$tbl="form4pdf".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op_inqty_apr double(10,2) default 0,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,price double(12,2),gname varchar(100),sale_out_apr double(12,2),trans_in_apr double(12,2),trans_out_apr double(12,2),salertn_in_apr double(12,2),sale_amt double(12,2))");
mysqli_query($db,"delete from $tbl");
if($type=='All')
{
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_outqty,gname) select partcode,partname,sum(qty),sum(outqty),gname from adjstock where  brcode='$brcode' and entrydate<'$dfrom' group by partcode") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,price) select partcode,lstprice from spareprmast where brcode='$brcode'") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,gname) select item_code,item,sum(qty),gname from itempurchase where brcode='$brcode' and  invdate<'$dfrom' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_inqty_apr,gname) select itemcode,itemname,sum(opstk),sum(opstk),item_grp from sparestock where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,salertn_in_apr,gname) select item_code,item,sum(rtnqty),sum(rtnqty),gname from salesretn where brcode='$brcode' and invdate<'$dfrom'  group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty,trans_out_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferout where brcode='$brcode' and invdate<'$dfrom' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,trans_in_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferin where brcode='$brcode' and invdate<'$dfrom' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty,sale_out_apr,gname) select item_code,item,sum(qty),sum(total),gname from prod_invoice where brcode='$brcode' and invdate<'$dfrom' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty,gname) select item_code,item,sum(qty),gname from purchretn where brcode='$brcode' and invdate<'$dfrom' group by item_code") or die('err12'.mysqli_error($db));
//****************************************************************************** in out*******
  mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out,gname) select partcode,partname,sum(qty),sum(outqty),gname from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' group by partcode") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,purch_in,gname) select item_code,item,sum(qty),gname from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,salertn_in_apr,gname) select item_code,item,sum(rtnqty),sum(rtnqty),gname from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,trans_out_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,trans_in,trans_in_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,sale_out_apr,sale_amt,gname) select item_code,item,sum(qty),sum(total),sum(total),gname from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out,gname) select item_code,item,sum(qty),gname from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
}
else
{
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_outqty,gname) select partcode,partname,sum(qty),sum(outqty),gname 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,price) select partcode,lstprice from spareprmast where brcode='$brcode'") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,gname) select item_code,item,sum(qty),gname 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,op_inqty_apr,gname) select itemcode,itemname,sum(opstk),sum(opstk),item_grp 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,salertn_in_apr,gname) select item_code,item,sum(rtnqty),sum(rtnqty),gname 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,trans_out_apr,gname) select item_code,item,sum(qty),sum(qty),gname 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,trans_in_apr,gname) select item_code,item,sum(qty),sum(qty),gname 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,sale_out_apr,gname) select item_code,item,sum(qty),sum(total),gname 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,gname) select item_code,item,sum(qty),gname 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,gname) select partcode,partname,sum(qty),sum(outqty),gname from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and gname = '$type' group by partcode") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,purch_in,gname) select item_code,item,sum(qty),gname from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,salertn_in_apr,gname) select item_code,item,sum(rtnqty),sum(rtnqty),gname from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,trans_out_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,trans_in,trans_in_apr,gname) select item_code,item,sum(qty),sum(qty),gname from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,sale_out_apr,sale_amt,gname) select item_code,item,sum(qty),sum(total),sum(total),gname from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
  mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out,gname) select item_code,item,sum(qty),gname from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
}
//**************************************
$re=mysqli_query($db,"select partcode,partname,sum(op_inqty),sum(op_outqty),sum(purch_in),sum(trans_in),sum(salertn_in),sum(stkadj_in),sum(sale_out),sum(trans_out),sum(purchrtn_out),sum(stkadj_out),max(price),max(gname),sum(op_inqty_apr),sum(salertn_in_apr),sum(trans_out_apr),sum(trans_in_apr),sum(sale_out_apr),sum(sale_amt) from $tbl group by partcode");

$i=1;
$tprice=0;
$mgname[]='';
$mop[]=0;
$mpurch[]=0;
$mtran_in[]=0;
$msalesrtn[]=0;
$msales[]=0;
$mtran_out[]=0;
$mpurchret[]=0;
$mclose[]=0;
$mop_prog[]=0;
$msalesrtn_prog[]=0;
$mtran_out_prog[]=0;
$mtran_in_prog[]=0;
$msales_prog[]=0;
$mtot1 = 0;
$mtot2 = 0;
$mptot1 = 0;
$mptot2 = 0;
$ctr=1;
while($row=mysqli_fetch_array($re))
{

  if($row[13])
  {
  $key = array_search($row[13],$mgname);
  if($key)
  {
    $op_qty =($row[2]-$row[3]);
    $mop[$key] += (($row[2]-$row[3])*$row[12]);
    $mpurch[$key] += (($row[4])*$row[12]);
    $mtran_in[$key] += (($row[5])*$row[12]);
    $msalesrtn[$key] += (($row[6])*$row[12]);
//    $msales[$key] += (($row[8])*$row[12]);
    $msales[$key] += (($row[19]));
    $mtran_out[$key] += (($row[9])*$row[12]);
    $mpurchret[$key] += (($row[10])*$row[12]);
//    $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
    $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[9]+$row[10]+$row[11]);
//    $mclose[$key] +=(($close)*$row[12]);
    $mclose[$key] +=($close*$row[12])-$row[19];
    $mop_prog[$key] +=(($row[14]*$row[12]));
    $msalesrtn_prog[$key] +=(($row[15]*$row[12]));
    $mtran_out_prog[$key] +=(($row[16]*$row[12]));
    $mtran_in_prog[$key] +=(($row[17]*$row[12]));
    $msales_prog[$key] +=$row[18];
  }
  else
  {
    $mgname[$ctr] = $row[13];
    $op_qty =($row[2]-$row[3]);
    $mop[$ctr] += (($row[2]-$row[3])*$row[12]);
    $mpurch[$ctr] += (($row[4])*$row[12]);
    $mtran_in[$ctr] += (($row[5])*$row[12]);
    $msalesrtn[$ctr] += (($row[6])*$row[12]);
//    $msales[$ctr] += (($row[8])*$row[12]);
    $msales[$ctr] += (($row[19]));
    $mtran_out[$ctr] += (($row[9])*$row[12]);
    $mpurchret[$ctr] += (($row[10])*$row[12]);
//    $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
    $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[9]+$row[10]+$row[11]);
//    $mclose[$ctr] +=(($close)*$row[12]);
    $mclose[$ctr] +=($close*$row[12])-$row[19];
    $mop_prog[$ctr] +=(($row[14]*$row[12]));
    $msalesrtn_prog[$ctr] +=(($row[15]*$row[12]));
    $mtran_out_prog[$ctr] +=(($row[16]*$row[12]));
    $mtran_in_prog[$ctr] +=(($row[17]*$row[12]));
    $msales_prog[$ctr] +=$row[18];
    $ctr++;
  }
  }
}

 	$pdf = new FPDF('L','mm','A4');
	$pdf->AddPage();
	$pdf->SetFont('Arial','',10);
	//$pdf->SetFont('','B');
	$dedres1=mysqli_query($db,"select brname from branch where brcode='$brcode'") or die("er5:".mysqli_error($db));
	$br_1=mysqli_fetch_array($dedres1);
	$mon = date("M Y", strtotime("$dto"));
	//$pdf->SetFont('','BU');
	$pdf->Cell(260,8,$brrow[brname],0,1,'C');
	$pdf->SetFont('Arial','',11);
	$brnchnm=strtoupper($brnchnm);						
        $pdf->Cell(260,8,'Monthly Stock Statement for the Month of '.$mon,0,1,'C');
        $qr=mysqli_query($db,"select * from branch where brname='$rw[2]'");
	$qw=mysqli_fetch_array($qr);                                                
        $pdf->SetFont('Arial','',9);
        $pdf->Cell(40,7,'','');$pdf->Cell(40,7,'Particulars','LT');$pdf->Cell(30,7,'Current Month','LT');$pdf->Cell(30,7,'Progressive Total','LTR');
	$pdf->Cell(40,7,'Particulars','LTR');
        $pdf->Cell(30,7,'Current Month','LTR');$pdf->Cell(30,7,'Progressive Total','LTR');                                              
	$pdf->Ln(7);
	$pdf->Cell(40,6,'','');$pdf->Cell(100,6,'OPENING STOCK:','LTR');$pdf->Cell(100,6,'SALES:','LTR');
        $pdf->Ln(6);
	$pdf->SetFont('Arial','',9);
	for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
	{
		$pdf->Cell(40,6,'','');
		$pdf->Cell(40,6,$mgname[$ctr1],'LTR');$pdf->Cell(30,6,number_format($mop[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$pdf->Cell(30,6,number_format($mop_prog[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(40,6,$mgname[$ctr1],'LTR');
		$pdf->Cell(30,6,number_format($msales[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(30,6,number_format($msales_prog[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$mtot1 += $mop[$ctr1];
		$mptot1 += $mop_prog[$ctr1];
		$mtot2 += $msales[$ctr1];
		$mptot2 += $msales_prog[$ctr1];
		$pdf->Ln(6);
	}
	$pdf->Cell(40,6,'','');$pdf->Cell(100,6,'PURCHASES:','LTR');$pdf->Cell(100,6,'TRANSFER:','LTR');
        $pdf->Ln(6);
	for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
	{
		$pdf->Cell(40,6,'','');
		$pdf->Cell(40,6,$mgname[$ctr1],'LTR');$pdf->Cell(30,6,number_format($mtran_in[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$pdf->Cell(30,6,number_format($mtran_in_prog[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(40,6,$mgname[$ctr1],'LTR');
		$pdf->Cell(30,6,number_format($mtran_out[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(30,6,number_format($mtran_out_prog[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$mtot1 += $mtran_in[$ctr1];
  		$mptot1 += $mtran_in_prog[$ctr1];
		$mtot2 += $mtran_out[$ctr1];
  		$mptot2 += $mtran_out_prog[$ctr1];
		$pdf->Ln(6);
	}
	
	$pdf->Cell(40,6,'','');$pdf->Cell(100,6,'SALES RETURN:','LTR');$pdf->Cell(100,6,'CLOSING:','LTR');
        $pdf->Ln(6);
	for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
	{
		$pdf->Cell(40,6,'','');
		$pdf->Cell(40,6,$mgname[$ctr1],'LTR');$pdf->Cell(30,6,number_format($msalesrtn[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$pdf->Cell(30,6,number_format($msalesrtn_prog[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(40,6,$mgname[$ctr1],'LTR');
		$pdf->Cell(30,6,number_format($mclose[$ctr1], 2, '.', ','),'LTR',0, 'R');$pdf->Cell(30,6,number_format($mclose[$ctr1], 2, '.', ','),'LTR',0, 'R');
		$mtot1 += $msalesrtn[$ctr1];
  		$mptot1 += $msalesrtn_prog[$ctr1];
		$mtot2 += $mclose[$ctr1];
  		$mptot2 += $mclose[$ctr1];
		$pdf->Ln(6);
	}
		$mnum = number_format($mtot1, 2, '.', '');
		$mnum1 = number_format($mtot2, 2, '.', '');
		$mpnum = number_format($mptot1, 2, '.', '');
		$mpnum1 = number_format($mptot2, 2, '.', '');
		$pdf->Cell(40,6,'','');
		$pdf->Cell(40,6,'TOTAL','LTRB');$pdf->Cell(30,6,number_format($mnum, 2, '.', ','),'LTRB',0, 'R');$pdf->Cell(30,6,number_format($mpnum, 2, '.', ','),'LTRB',0, 'R');$pdf->Cell(40,6,'TOTAL','LTRB');
		$pdf->Cell(30,6,number_format($mnum1, 2, '.', ','),'LTRB',0,'R');$pdf->Cell(30,6,number_format($mpnum1, 2, '.', ','),'LTRB',0, 'R');	
		$pdf->Ln(6);$pdf->Ln(6);
		$pdf->Cell(40,6,'','');	
		$pdf->Cell(30,7,'Note:-    1)  If any purchase invoices are pending adjustment and the sales there of is included in the above ','');
		$pdf->Ln(7);
		$pdf->Cell(50,7,'','');$pdf->Cell(40,7,'       statement a note to that effect is to be given on the statement.','');$pdf->Cell(30,6,'','');		
		$pdf->Ln(7);
		$pdf->Cell(50,7,'','');$pdf->Cell(30,7,' 2)  Figures in the above statement should tally with the actual value of stock and sales of each item.','');		
		$pdf->Ln(7);
		$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Name of Centre/Institution','');		
		$pdf->Ln(7);
		$pdf->Cell(40,7,'','');$pdf->Cell(30,7,$brname,'');		
		$pdf->Ln(7);$pdf->Ln(7);
		$pdf->Cell(100,7,'','');$pdf->Cell(30,7,'Signature','');$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Signature','');	
		$pdf->Ln(7);$pdf->Ln(7);
		$pdf->Cell(100,7,'','');$pdf->Cell(30,7,'Project Officer','');$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Manager','');	
		$pdf->Ln(7);
		$pdf->Ln(7);
		$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Place : '.$brcity,'');
		$pdf->Ln(7);
		$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Date  : '.date('d-m-Y'),'');
		$pdf->Ln(7);$pdf->Ln(7);
		$pdf->Cell(40,7,'','');$pdf->Cell(30,7,'Office Seal','');
		$pdf->Output();	
?>
