<link href="../css/form.css" type="text/css" rel="stylesheet" media="screen"  />
<html>
<body >

<?
session_start();
include("../opendb.php");
include("../test.php");
$brcode = $_SESSION['brcode'];
$user = $_SESSION['user'];
$d1     = $_REQUEST['d1'];
$m1     = $_REQUEST['m1'];
$y1     = $_REQUEST['y1'];
$d2     = $_REQUEST['d2'];
$m2     = $_REQUEST['m2'];
$y2     = $_REQUEST['y2'];

$dfrom=$y1.'-'.$m1.'-'.$d1;
$dto=$y2.'-'.$m2.'-'.$d2;

$thirty1=$_REQUEST['thirt'];
$sixty1=$_REQUEST['sixt'];
$ninty1=$_REQUEST['nint'];

$show = $_REQUEST['show'];
if($show)
{
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<center>
<table>
<TR><TD COLSPAN=2 ALIGN=CENTER><b> PSN AUTOMOBILES PVT LTD </b><BR>
	 <?print $brrow[braddr].",".$brrow[brcity].",<br><u> PH - ".$brrow[brphone]?></u>
TIN: <?print $brrow[tin]?>, CST: <?print $brrow[cstno]?>&nbsp;
</td></tr>
</TABLE>

<?
print "<B>BRANCH STOCK REPORT </B> <BR> From ".datetohtml($dfrom)." To ".datetohtml($dto);
?>

<center>
<center>
<table border=1>
<th>Partcode<th>Partname<th>Op. Stock<th>Purchase<th>Sales<th>Service<th>Trans IN<th>Trans OUT<th>Sales Ret<th>Purch Ret<th>Warr Sale<th>Warr Sale Ret<th>Stk Adj(IN)<th>Stk Adj(OUT)<th>Closing Stock
<?

$ker=mysqli_query($db,"select brstate from branch where brcode='$brcode'");
$kar=mysqli_fetch_array($ker);

if($dfrom>$dto)
{
print "Invalid Date";
return;
}
$tbl_pur="purchasedetbr".$brcode.$user;

mysqli_query($db,"drop table $tbl_pur");

if($kar[0]=='Kerala') 
{
mysqli_query($db,"CREATE TABLE $tbl_pur select * from sparepurchase where entrydate<='$dto' and brcode='$brcode' ");
mysqli_query($db,"insert into $tbl_pur select * from psn_zfdb1617.sparepurchase where entrydate<='$dto' and brcode='$brcode' ");
mysqli_query($db,"insert into $tbl_pur select * from psn_zfdb1516.sparepurchase where entrydate<='$dto' and brcode='$brcode' ");

mysqli_query($db,"insert into $tbl_pur (entrydate,partcode,supqty,price,aprice,brcode) select entrydate,partcode,qty,amount,qty*amount,brcode from sparetransin where brcode='$brcode'");
mysqli_query($db,"insert into $tbl_pur (entrydate,partcode,supqty,price,aprice,brcode) select entrydate,partcode,qty,amount,qty*amount,brcode from psn_zfdb1617.sparetransin where brcode='$brcode'");
mysqli_query($db,"insert into $tbl_pur (entrydate,partcode,supqty,price,aprice,brcode) select entrydate,partcode,qty,amount,qty*amount,brcode from psn_zfdb1516.sparetransin where brcode='$brcode'");

}

else

{
mysqli_query($db,"CREATE TABLE $tbl_pur select * from sparepurchase where entrydate<='$dto' and brcode = '$brcode'");
mysqli_query($db,"insert into $tbl_pur (entrydate,partcode,supqty,price) select entrydate,partcode,qty,amount from sparetransin where brcode='$brcode'");

//mysqli_query($db,"insert into $tbl_pur select * from edayardb1112.sparepurchase where (brcode = 'BAN682')");
}

$tbl="stockagingbr".$brcode.$user;
mysqli_query($db,"drop table $tbl"); 

mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op double(10,2)default 0,purch double(10,2)default 0,serv double(10,2)default 0,sale double(10,2)default 0,transin double(10,2)default 0,transout double(10,2)default 0,sal_ret double(10,2)default 0,pur_ret double(10,2)default 0,war_sal double(10,2)default 0,war_sal_ret double(10,2)default 0, adj_in double(10,2)default 0,adj_out double(10,2)default 0,opin double(10,2)default 0,opout double(10,2)default 0,brcode varchar(20),grp varchar(100),state varchar(50),dto date,closing double(10,2),type varchar(100))");
mysqli_query($db,"delete from $tbl");


//opening new
$op=mysqli_query($db,"select brcode from branch where brcode='$brcode'") or die("ERR:".mysqli_error($db));

while($op1=mysqli_fetch_array($op))
{


mysqli_query($db,"insert into $tbl (partcode,partname,op,brcode,type) select itemcode as partcode,itemname as partname,sum(qty)op,brcode,'opening' from spareopstock where (brcode = '$op1[brcode]') group by  itemcode");

mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select partcode,partname,sum(supqty)opin,brcode,'purchase' from sparepurchase where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");


mysqli_query($db,"insert into $tbl (partcode,partname,opout,brcode,type) select partcode,partname,sum(qty)opout,brcode,'spareinvoice' from spareinvoice where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");


mysqli_query($db,"insert into $tbl (partcode,partname,opout,brcode,type) select partcode,partname,sum(qty)opout,brcode,'jobcard' from jcardparts where (brcode = '$op1[brcode]') and date<'$dfrom' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select partcode,partname,sum(qty)opin,brcode,'transin' from sparetransin where (brcode = '$op1[brcode]' ) and entrydate<'$dfrom' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,opout,brcode,type) select partcode,partname,sum(qty)opout,brcode,'transout' from sparetransout where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select partcode,partname,sum(qty)opin,brcode,'salesreturn' from salesreturn where (brcode = '$op1[brcode]' ) and entrydate<'$dfrom' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,opout,brcode,type) select partcode,partname,sum(retqty)opout,brcode,'purchaseret' from sparepurreturn where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");


mysqli_query($db,"insert into $tbl (partcode,partname,opout,brcode,type) select partcode,partname,sum(qty)opout,brcode,'warspareinv' from warspareinvoice where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");


mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select partcode,partname,sum(qty)opin,brcode,'wsalereturn' from wsalesreturn where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");


mysqli_query($db,"insert into $tbl (partcode,partname,opin,opout,,brcode,type) select partcode,partname,sum(qty)opin,sum(outqty)opout,brcode,'adjstock' from adjstock where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");

//Transactions

mysqli_query($db,"insert into $tbl (partcode,partname,purch,brcode,type) select partcode,partname,sum(supqty)purch,brcode,'sparepurchase' from sparepurchase where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,sale,brcode,type) select partcode,partname,sum(qty)sale,brcode,'spareinvoice' from spareinvoice where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,serv,brcode,type) select partcode,partname,sum(qty)serv,brcode,'jobcard' from jcardparts where (brcode = '$op1[brcode]') and date>='$dfrom' and date<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,transin,brcode,type) select partcode,partname,sum(qty)transin,brcode,'transin' from sparetransin where(brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,transout,brcode,type) select partcode,partname,sum(qty)transout,brcode,'transout' from sparetransout where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,sal_ret,brcode,type) select partcode,partname,sum(qty)sal_ret,brcode,'salesreturn' from salesreturn where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,pur_ret,brcode,type) select partcode,partname,sum(retqty)pur_ret,brcode,'sparepurreturn' from sparepurreturn where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,war_sal,brcode,type) select partcode,partname,sum(qty)war_sal,brcode,'warspareinv' from warspareinvoice where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,war_sal_ret,brcode,type) select partcode,partname,sum(qty)war_sal_ret,brcode,'wsalesret' from wsalesreturn where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");

mysqli_query($db,"insert into $tbl (partcode,partname,adj_in,adj_out,brcode,type) select partcode,partname,sum(qty)adj_in,sum(outqty)adj_out,brcode,'adjstock' from adjstock where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");


}



mysqli_query($db,"update $tbl set dto='$dto',grp=(select partmaster.group1 from partmaster where partmaster.partcode = $tbl.partcode)");

$trantbl=mysqli_query($db,"select partcode,partname,sum(op),sum(purch),sum(sale),sum(transin),sum(transout),sum(sal_ret),sum(pur_ret),sum(war_sal),sum(war_sal_ret),sum(adj_in),sum(adj_out),sum(opin),sum(opout),sum(serv) from $tbl group by partcode") or die("ERR:".mysqli_error($db));
while($tran=mysqli_fetch_array($trantbl))
{
	$mop = $tran[2]+$tran[13]-$tran[14];
	$mcl = $mop+$tran[3]-$tran[4]+$tran[5]-$tran[6]+$tran[7]-$tran[8]-$tran[9]+$tran[10]+$tran[11]-$tran[12]-$tran[15];
mysqli_query($db,"update $tbl set closing='$mcl'where partcode = $tran[0])");

	print "<tr><td><a href='stockled1.php?pcode=$tran[0]' title='Click to view ledger' target='x'>$tran[0]</td><td>$tran[1]</td><td align=right>$mop</td><td align=right>$tran[3]</td><td align=right>$tran[4]</td><td align=right>$tran[15]</td><td align=right>$tran[5]</td><td align=right>$tran[6]</td><td align=right>$tran[7]</td><td align=right>$tran[8]</td><td align=right>$tran[9]</td><td align=right>$tran[10]</td><td align=right>$tran[11]</td><td align=right>$tran[12]</td><td align=right>$mcl</td><td></td></tr>";


}
}

?>
</table>
<script> window.open('newval2br.php?thirty1=<?print $thirty1 ?>&sixty1=<?print $sixty1 ?>&ninty1=<?print $ninty1 ?>'); </script>
