<?
include_once('includes/top.php');
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');
			$y1=$_REQUEST['y1'];
			$m1=$_REQUEST['m1'];
			$d1=$_REQUEST['d1'];
			$y2=$_REQUEST['y2'];
			$m2=$_REQUEST['m2'];
			$d2=$_REQUEST['d2'];
			$type=$_REQUEST['type'];
			
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<body bgcolor="#0033FF">
<?php //include('includes/top.php'); ?>
<center>
<style>
.trcls
{
font-family:Calibri;
}	
.tbl1 td{
border: 1px solid black;
border-bottom:0px solid white;
font-family:Calibri;
border-collapse:collapse;
text-align:center;
margin-top:25px;
}
.tbl2 td{
//border: 1px solid black;
//border-bottom:0px solid white;
font-family:Calibri;
font-size:18px;
//border-collapse:collapse;
text-align:left;
margin-top:25px;
}
.tbl1 tr{
border-bottom:1px solid black;

}
</style>
<br>
<font font-size='10px'> <? print "<B>Monthly Stock For The Month</B> <BR> From ".$dfrom." To ".$dto;?></font>
<link rel="stylesheet" href="style.css" type="text/css" />
<link type="text/css" href="includes/style1.css" rel="stylesheet" />
<link type="text/css" href="css/form.css" rel="stylesheet" />

<style>
	//tr1{background-color:white}
	th{
            background-color:steelblue;color:white;
            font-weight: normal;
        }
        
</style>
<center>

<?
$tbl="form4".$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,price double(12,2),gname varchar(100))");
mysqli_query($db,"delete from $tbl");

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,gname) select itemcode,itemname,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,gname) select item_code,item,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,gname) select item_code,item,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,gname) select item_code,item,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,gname) select item_code,item,sum(qty),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,gname) select item_code,item,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,gname) select item_code,item,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,gname) select item_code,item,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,gname) select item_code,item,sum(qty),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)); 

//**************************************
$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),sum(price),max(gname) 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;
$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]);
		$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]);
		$mclose[$key] +=(($close)*$row[12]);
	}
	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]);
		$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]);
		$mclose[$ctr] +=(($close)*$row[12]);
		$ctr++;
	}
	}
}
?>
<?
for ($ctr1=1;$ctr1<=$ctr;$ctr1++)
{
//	print $mgname[$ctr1]."dqqw<br>";
}
?>
<table border="0">
<tr><td align='center'><? echo $brrow[brname] ?></td>
</tr>

<br>
<tr><td align='center'>Monthly Stock Statement for the Month April </td>
</tr>
</table>
<br>
<?
$qr=mysqli_query($db,"select * from branch where brname='$rw[2]'");
$qw=mysqli_fetch_array($qr);
?>
<table width=90%   align='center' class='tbl1' border='1'>
<tr colspan='6'><td>Item</td><td>Current Month</td><td>Progressive Total</td><td>Item</td><td>Current Month</td><td>Progressive Total</td></tr>
<tr><th colspan="3" align="left">OPENING STOCK:</th><th colspan="3" align="left">SALES:</th></tr>

   
<?
for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
{
	print "<tr><td align='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mop[$ctr1], 2, '.', ',')."</td><td>".number_format($msales[$ctr1], 2, '.', ',')."</td>";
	print "<td align='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($msales[$ctr1], 2, '.', ',')."</td><td>".number_format($mop[$ctr1], 2, '.', ',')."</td>";
}
?>

    <tr><th colspan="3" align="left">PURCHASES:</th><th colspan="3" align="left">TRANSFER:</th></tr>

<?
for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
{
	print "<tr><td align ='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mtran_in[$ctr1], 2, '.', ',')."</td><td>".number_format($mtran_in[$ctr1], 2, '.', ',')."</td>";
	print "<td align='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mtran_out[$ctr1], 2, '.', ',')."</td><td>".number_format($mtran_out[$ctr1], 2, '.', ',')."</td>";
}?>
<tr><th colspan="3" align="left">SALES RETURN:</th><th colspan="3" align="left">CLOSING:</th></tr>
<?
for ($ctr1=1;$ctr1<=$ctr-1;$ctr1++)
{
	print "<tr><td align ='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($msalesrtn[$ctr1], 2, '.', ',')."</td><td>".number_format($msalesrtn[$ctr1], 2, '.', ',')."</td>";
	print "<td align ='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mclose[$ctr1], 2, '.', ',')."</td><td>".number_format($msalesrtn[$ctr1], 2, '.', ',')."</td>";
//	print "<tr><td></td><td></td><td></td><td>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mclose[$ctr1], 2, '.', ',')."</td><td></td>";
//	print "<td align='left'>&nbsp &nbsp &nbsp".$mgname[$ctr1]."</td><td align='right'>".number_format($mtran_out[$ctr1], 2, '.', ',')."</td><td></td>";
}?>
<tr colspan='6'><td>Total</td><td></td><td></td><td>Total</td><td></td><td></td></tr>
</table>
<br>
<table width=100%   align='center' class='tbl2'>
<tr><td>Note:</td><td>
1.If any purchase invoices are pending adjustment and the sale there of is included in the above statement
a note to that effect is to be given on the statemnent.
<br>2.Figures in the above statement should tally with the actual value of stock and sales of each item.
</td><td></td></tr><tr></tr>
<tr><td></td>
<td>Name of Centre:Khadi Gramodyog Bhavan/Khadi Bhavan :Khadi Grama
Soubhagya, SVSC
</td><td>Signature.................</td></tr>
<tr><td></td>
<td>Institution:Kerala Khadi&Village Industries Board,Trivandrum.
</td><td></td></tr><tr></tr>
<tr><td>Date:.............
</td><td>Project Officer
</td><td>Manager</td></tr>
</table>
<br><br>
