<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($user)<=0)
{
  print "<script> alert('Invalid User'); document.location='index.html' </script>";
  return;
}
include("../opendb.php");
include("../test.php");
$y1=$_REQUEST['y1'];
$m1=$_REQUEST['m1'];
$d1=$_REQUEST['d1'];
$y2=$_REQUEST['y2'];
$m2=$_REQUEST['m2'];
$d2=$_REQUEST['d2'];
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;
$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 AUTOMOTIVE MARKETING P 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>STOCK REPORT</B> <BR> From ".implode('-', array_reverse(explode('-', $dfrom)))." To ".implode('-', array_reverse(explode('-', $dto)));

?>
<link rel="stylesheet" href="../style.css" type="text/css" />

<style>
	//tr1{background-color:white}
	th{background-color:steelblue;color:white}
</style>
<center>
<center>
<?
$tbl="sparetmpdwitem".$brcode.$user;
mysqli_query($db,"drop table if exists $tbl");
mysqli_query($db,"CREATE TABLE if not exists  $tbl (partcode varchar(200),partname varchar(200),supqty double(10,2) default 0, inqty double(10,2) default 0, outqty double(12,2) default 0)");
mysqli_query($db,"delete from $tbl");

$sqq="insert into $tbl (partcode,partname,supqty) select itemcode,itemname,sum(qty) from spareopstockitem where brcode='$brcode' group by  itemcode";
$ree=mysqli_query($db,$sqq)or die('ERr spareopstock'.mysqli_error($db));





//****************
$sq="select item_code,chasis,sum(qty) from item_purchase_veh where brcode='$brcode' and curdate<'$dfrom' group by  item_code";
//and entrydate>='$dfrom' 
$re=mysqli_query($db,$sq)or die('pur11 '.mysqli_error($db));

while($res=mysqli_fetch_array($re))
{
 $chinstk=mysqli_query($db,"select * from $tbl where partcode='$res[0]'");
 if(mysqli_num_rows($chinstk)>0)
 {
 $sql="update $tbl set supqty=(supqty+".$res[2].") where partcode='$res[0]'";
 mysqli_query($db,$sql)or die('pur '.mysqli_error($db));
 //print $sql;
 }
else
{
 $sql="insert into $tbl (partcode,partname,supqty) values('$res[0]','".addslashes($res[1])."','$res[2]')";
  mysqli_query($db,$sql)or die('pur '.mysqli_error($db));
  //print $sql;
}
}

/*
$sqq="select itemcode,itemname,sum(qty) from spareopstockitem where brcode='$brcode' group by  itemcode";
$ree=mysqli_query($db,$sqq)or die('ERr spareopstock'.mysqli_error($db));

while($ress=mysqli_fetch_array($ree))
{
// print $ress[itemcode].'-'.$ress[2].'<br>';
  $rr=mysqli_query($db,"select * from $tbl where partcode='$ress[itemcode]'");
  print "select * from $tbl where partcode='$ress[itemcode]'";
  if(mysqli_num_rows($rr)>0)
  {
	$inrow=mysqli_fetch_array($rr);
    	$sql="update $tbl set supqty=(supqty+".$ress[2].") where partcode='$inrow[partcode]'";
    	print $sql;
	mysqli_query($db,$sql)or die(mysqli_error($db));
  }
  else
   	{
	$sql="insert into $tbl  (partcode,partname,supqty) values('".addslashes($ress[0])."','".addslashes(trim($ress[1]))."','$ress[2]')";
	print $sql;
	mysqli_query($db,$sql)or die('opstk'.mysqli_error($db));
   	}
}

*/

//excess in op

$sq="select partcode,partname,sum(qty) from sparetransinexcess where brcode='$brcode' and entrydate<'$dfrom'  group by  partcode";
//and entrydate>='$dfrom' 
$rein=mysqli_query($db,$sq)or die(mysqli_error($db));


while($resin=mysqli_fetch_array($rein))
{

  $rr3in=mysqli_query($db,"select * from $tbl where partcode='$resin[partcode]'");
  if(mysqli_num_rows($rr3in)>0)
  {
    $inrow=mysqli_fetch_array($rr3in);
	$sql="update $tbl set supqty=(supqty+".$resin[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql)or die(mysqli_error($db));
//print $sql;
  }
  else
  {
   $sql="insert into $tbl (partcode,partname,inqty) values('$resin[0]','".addslashes($resin[1])."','$resin[2]')";
   mysqli_query($db,$sql)or die(mysqli_error($db));
  // print $sql;
  }
  
}
//**************************************

//salesreturn item op calc
$sqsr="select partcode,partname,sum(qty) from salesreturnitem where brcode='$brcode' and entrydate<'$dfrom'  group by  partcode";
//and entrydate>='$dfrom' 
$reinsr=mysqli_query($db,$sqsr)or die(mysqli_error($db));


while($resinsr=mysqli_fetch_array($reinsr))
{

  $rr3insr=mysqli_query($db,"select * from $tbl where partcode='$resinsr[partcode]'");
  if(mysqli_num_rows($rr3insr)>0)
  {
    $inrowsr=mysqli_fetch_array($rr3insr);
	$sqlsr="update $tbl set supqty=(supqty+".$resinsr[2].") where partcode='$inrowsr[partcode]'";
	mysqli_query($db,$sqlsr)or die(mysqli_error($db));
//print $sql;
  }
  else
  {
   $sqlsr="insert into $tbl (partcode,partname,inqty) values('$resinsr[0]','".addslashes($resinsr[1])."','$resinsr[2]')";
   mysqli_query($db,$sqlsr)or die(mysqli_error($db));
  // print $sql;
  }
  
}
//**************************************


//*********************
$sq2="select partcode,partname,sum(qty) from spareinvoice_oth_veh where brcode='$brcode' and entrydate<'$dfrom' and qty>0 group by  partcode";

$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));


while($res2=mysqli_fetch_array($re21))
{
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
//print $sql;
}
//************

//*********************
$sq2="select partcode,partname,sum(qty) from spareinvoice_oth where brcode='$brcode' and entrydate<'$dfrom' and qty>0 group by  partcode";

$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));


while($res2=mysqli_fetch_array($re21))
{
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
//print $sql;
}
//************

//********************* purchase ret
$sq2r="select partcode,partname,sum(retqty) from item_purchase_ret where brcode='$brcode' and entrydate<'$dfrom' and retqty>0 group by  partcode";

$re21r=mysqli_query($db,$sq2r)or die('Err3:'.mysqli_error($db));


while($res2r=mysqli_fetch_array($re21r))
{
$sqlr="update $tbl set supqty=(supqty-".$res2r[2].") where partcode='$res2r[partcode]'";

mysqli_query($db,$sqlr)or die('err4'.mysqli_error($db));
}
//************



// trans out op  


$sq2o="select partcode,partname,sum(qty) from sparetransoutexcess where brcode='$brcode' and entrydate<'$dfrom'  and qty>0 group by  partcode";

$re21saleo=mysqli_query($db,$sq2o)or die('Err3:'.mysqli_error($db));

while($res2o=mysqli_fetch_array($re21saleo))
{
    
    
    
  $rr3ino=mysqli_query($db,"select * from $tbl where partcode='$res2o[0]'");
  if(mysqli_num_rows($rr3ino)>0)
  {
$sqlo="update $tbl set  supqty=(supqty-".$res2o[2].")  where partcode='$res2o[0]'";
//print $sqlo.'<br>';
mysqli_query($db,$sqlo)or die('err4out'.mysqli_error($db));
}


}

//trans out op end


// ************* purchase *************//

$sq="select item_code,chasis,sum(qty) from item_purchase_veh where brcode='$brcode' and curdate>='$dfrom' and curdate <='$dto' group by  item_code";
//and entrydate>='$dfrom' 
$rein=mysqli_query($db,$sq)or die(mysqli_error($db));


while($resin=mysqli_fetch_array($rein))
{

  $rr3in=mysqli_query($db,"select * from $tbl where partcode='$resin[item_code]'");
  if(mysqli_num_rows($rr3in)>0)
  {
    $inrow=mysqli_fetch_array($rr3in);
	$sql="update $tbl set inqty=(inqty+".$resin[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql)or die(mysqli_error($db));
  }
  else
  {
      //print $sql;
   $sql="insert into $tbl (partcode,partname,inqty) values('$resin[0]','".addslashes($resin[1])."','$resin[2]')";
   
   mysqli_query($db,$sql)or die(mysqli_error($db));
  }
}





// trans in  


$sq2="select partcode,partname,sum(qty) from sparetransinexcess where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and qty>0 group by  partcode";

$re21sale=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21sale))
{
    
    
  $rr3in=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]'");
  if(mysqli_num_rows($rr3in)>0)
  {
$sql="update $tbl set  inqty=(inqty+".$res2[2].")  where partcode='$res2[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}
else
{
 $sql="insert into $tbl (partcode,partname,inqty) values('$res2[0]','".addslashes($res2[1])."','$res2[2]')";
   mysqli_query($db,$sql)or die(mysqli_error($db));   
}

}


// retrun item 


$sq2="select partcode,partname,sum(qty) from salesreturnitem  where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and qty>0 group by  partcode";

$re21sale=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21sale))
{
    
    
  $rr3in=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]'");
  if(mysqli_num_rows($rr3in)>0)
  {
$sql="update $tbl set  inqty=(inqty+".$res2[2].")  where partcode='$res2[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}
else
{
 $sql="insert into $tbl (partcode,partname,inqty) values('$res2[0]','".addslashes($res2[1])."','$res2[2]')";
   mysqli_query($db,$sql)or die(mysqli_error($db));   
}

}


//*(************ sales

$sq2="select partcode,partname,sum(qty) from spareinvoice_oth_veh where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and qty>0 group by  partcode";

$re21sale=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21sale))
{
$sql="update $tbl set outqty=(outqty+".$res2[2].") where partcode='$res2[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}


//*(************ pur ret

$sq2rr="select partcode,partname,sum(retqty) from item_purchase_ret where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and retqty>0 group by  partcode";

$re21salerr=mysqli_query($db,$sq2rr)or die('Err3:'.mysqli_error($db));

while($res2rr=mysqli_fetch_array($re21salerr))
{
$sqlrr="update $tbl set outqty=(outqty+".$res2rr[2].") where partcode='$res2rr[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sqlrr)or die('err4rr'.mysqli_error($db));
}

// sales 2

$sq2a="select partcode,partname,sum(qty) from spareinvoice_oth where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and qty>0 group by  partcode";

$re21salea=mysqli_query($db,$sq2a)or die('Err3:'.mysqli_error($db));

while($res2a=mysqli_fetch_array($re21salea))
{
$sqla="update $tbl set outqty=(outqty+".$res2a[2].") where partcode='$res2a[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sqla)or die('err4'.mysqli_error($db));
}
//************ stock trfr out


$sq2="select partcode,partname,sum(qty) from sparetransoutexcess where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and qty>0 group by  partcode";

$re21sale=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21sale))
{
$sql="update $tbl set outqty=(outqty+".$res2[2].") where partcode='$res2[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}


//**************************************

$re=mysqli_query($db,"select * from $tbl where partcode!='ACINSTA' order by partcode");
//print "select * from $tbl order by partcode";
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>"; 
print "<th>No.<th>Part Code <th>Part Name<th>OP Qty <th>In Qty <th>Out Qty
<th>Closing";
if($brcode!='3791')
 	{
print"<th>NDP <th>Amount";
}
$i=1;
$tprice=0;
$inq=0;
$outq=0;
$opsq=0;
while($row=mysqli_fetch_array($re))
{
    
    	$mgrp=mysqli_query($db,"select group1,subgroup,partname from partmaster where partcode='$row[partcode]'");
  	$mgrp1=mysqli_fetch_row($mgrp);
 	print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'><td>".$i++."</td>";
 	print "<td>$row[partcode]</td><td>$mgrp1[2]</td><td align=right>$row[supqty]</td>";
	print "<td align=right bgcolor='ivory'>$row[inqty]</td>";
	print "<td align=right bgcolor='ivory'>$row[outqty]</td>";
	$opsq+=$row['supqty'];
	$inq+=($row['supqty']+$row['inqty']);
	$outq+=$row['outqty'];
	$close=($row['supqty']+$row['inqty']-$row['outqty']);
  	$tclose+=$close;
 	print "<td align=right bgcolor='ivory'>$close</td>";
	$purres=mysqli_query($db,"select price,tot,supqty,taxtype from sparepurchase where partcode='$row[0]' and entrydate<='$dto' order by entrydate desc limit 1");
/*	if(mysqli_num_rows($purres)<=0)
	{
		$purres=mysqli_query($db,"select price,tot,supqty,taxtype from psn_psneicherdb.sparepurchase where partcode='$row[0]' order by entrydate desc limit 1");
	}		
	if(mysqli_num_rows($purres)<=0)
	{
		$purres=mysqli_query($db,"select price,tot,supqty,taxtype from psn_psneicherdb.sparepurchase where partcode='$row[0]' order by entrydate desc limit 1");

	}
	*/
	$purrow=mysqli_fetch_row($purres);
  	if($purrow[0]>0)
  	{
		if($purrow[3]=='CST')
		{
			$mndp=$purrow[1]/$purrow[2];
		}
		else
		{
			$mndp=$purrow[0];
		}
	  }
	  else
	  {
		$prres=mysqli_query($db,"select ndpcost from spareprmast where partcode='$row[0]' order by effectfrom desc");
		$prrow=mysqli_fetch_row($prres);
		$mndp=$prrow[0];
	  }
 	$pr=($mndp*($row['supqty']+$row['inqty']-$row['outqty']));
 	$tprice+=$pr;
 	
 	if($brcode!='3791')
 	{
 	print "<td align=right>".putComa($mndp)."</td><td align=right>".putcoma($pr)."</td>";
 	}
  	$locres=mysqli_query($db,"select location from sparelocmast where itemcode='$row[partcode]' and brcode='$brcode'");
  	$locrow=mysqli_fetch_row($locres);

//	print "<td>$locrow[0]</td><td>$mgrp1[0]</td><td>$mgrp1[1]</td></tr>";
//***
//	mysqli_query($db,"insert into spareopstock1 values('$row[partcode]','$row[partname]','','$brcode','$close')");
//***	
}
 print "<tr ><td colspan=3 align=right bgcolor='silver'>Total</td>";
 print "<td align=right>$opsq</td><td align=right>$inq</td><td align=right>$outq</td><td align=right>$tclose</td>";
 if($brcode!='3791')
 	{
 print"<td bgcolor='silver'></td><td  bgcolor='silver' align=right>".putComa(round($tprice,2))."</td>";
 	}
 print "</tr>";

?>
</table>



