<?
session_start();
include("../opendb.php");
include("../test.php");

if($show)
{
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<link rel="stylesheet" href="../style.css" type="text/css" />

<style>
	//tr1{background-color:white}
	th{background-color:steelblue;color:white}
</style>

<center>
<font color=red> Under construction</font>
<table>
<TR><TD COLSPAN=2 ALIGN=CENTER><b>PSN AUTOMOBILES 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 ".datetohtml($dfrom)." To ".datetohtml($dto);
$tbl="sparetmpdwgr".$brcode;
//mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),supqty double(10,2), inqty double(10,2), outqty double(12,2))");
mysqli_query($db,"delete from $tbl");

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


while($res=mysqli_fetch_array($re))
{
  $sql="insert into $tbl (partcode,partname,supqty) values('$res[0]','$res[1]','$res[2]')"; 
  mysqli_query($db,$sql)or die('pur '.mysqli_error($db));
}

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

while($ress=mysqli_fetch_array($ree))
{
  $rr=mysqli_query($db,"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]'";
	mysqli_query($db,$sql)or die(mysqli_error($db));
  }
  else
   	{
	$sql="insert into $tbl  (partcode,partname,supqty) values('$ress[0]','".addslashes(trim($ress[1]))."','$ress[2]')";
	mysqli_query($db,$sql)or die(mysqli_error($db));
   	}
}


$sqq="select partcode,partname,sum(qty) from salesreturn where brcode='$brcode' and entrydate<'$dfrom'   group by  partcode";
$ree=mysqli_query($db,$sqq)or die('ERr retrn'.mysqli_error($db));

while($ress=mysqli_fetch_array($ree))
{
  $rr2=mysqli_query($db,"select * from $tbl where partcode='$ress[partcode]'");
  if(mysqli_num_rows($rr2)>0)
  {
	$inrow=mysqli_fetch_array($rr2);
    	$sql="update $tbl set supqty=(supqty+".$ress[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl  (partcode,partname,supqty) values('$ress[0]','$ress[1]','$ress[2]')";
	mysqli_query($db,$sql);
   	}
}


$sq1="select partcode,partname,sum(qty) from sparetransin where brcode='$brcode' and entrydate<'$dfrom'   group by  partcode";
$re1=mysqli_query($db,$sq1);

while($res1=mysqli_fetch_array($re1))
{
  $rr3=mysqli_query($db,"select * from $tbl where partcode='$res1[partcode]'");
  if(mysqli_num_rows($rr3)>0)
  {
    $inrow=mysqli_fetch_array($rr3);
	$sql="update $tbl set supqty=(supqty+".$res1[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl  (partcode,partname,supqty) values('$res1[0]','$res1[1]','$res1[2]')";
	mysqli_query($db,$sql);
   	}
}



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

$re2=mysqli_query($db,$sq2);


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

mysqli_query($db,$sql)or die('err2'.mysqli_error($db));
}

//*********************
$sq2="select partcode,partname,sum(qty) from spareinvoice where brcode='$brcode' and entrydate<'$dfrom'  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));
}
//************

$sq2="select partcode,partname,sum(qty) from warspareinvoice where brcode='$brcode' and entrydate<'$dfrom'  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));
}
//************



$jre=mysqli_query($db,"select jcno from jobcard where date<'$dfrom' and brcode='$brcode'")or die('Err j:'.mysqli_error($db));
//date>='$dfrom'
while($jrow=mysqli_fetch_array($jre))
{
$sq2="select partcode,partname,sum(qty) from jcardparts where brcode='$brcode' and jcno='$jrow[0]' group by  partcode";

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

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

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

}

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

$sq2="select partcode,partname,sum(retqty) from sparepurreturn where brcode='$brcode' and entrydate<'$dfrom' group by  partcode";
$re23=mysqli_query($db,$sq2);
while($res2=mysqli_fetch_array($re23))
{
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";
mysqli_query($db,$sql)or die('err6'.mysqli_error($db));
}

//************ transout -war
$tbl1="spareinvwar".$brcode;
$sq5="select partcode,partname,sum(psnqty) from $tbl1 and entrydate<'$dfrom' group by  partcode";
$re51=mysqli_query($db,$sq5);
while($res5=mysqli_fetch_array($re51))
{
$sql="update $tbl set supqty=(supqty-".$res5[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err8'.mysqli_error($db));
}
//************ trans in war

$sq6="select partcode,partname,sum(psnqty) from sparetrinwar where brcode='$brcode' and entrydate<'$dfrom' group by  partcode";
$re52=mysqli_query($db,$sq6);
while($res51=mysqli_fetch_array($re52))
{
$sql="update $tbl set supqty=(supqty+".$res51[2].") where partcode='$res51[partcode]'";
mysqli_query($db,$sql)or die('err10'.mysqli_error($db));
}

//************ foc invoice

$sq5="select partcode,partname,sum(qty) from spareinvoicefoc where brcode='$brcode' and entrydate<'$dfrom' group by  partcode";
$re53=mysqli_query($db,$sq5);
while($res5=mysqli_fetch_array($re53))
{
$sql="update $tbl set supqty=(supqty+".$res5[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err12'.mysqli_error($db));
}

//****************************************************************************** in out*******
// ************* purchase *************//

$sq="select partcode,partname,sum(supqty) from sparepurchase where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' 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 inqty=(inqty+".$resin[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql)or die(mysqli_error($db));
  }
  else
  {
   $sql="insert into $tbl (partcode,partname,inqty) values('$resin[0]','$resin[1]','$resin[2]')";
   mysqli_query($db,$sql)or die(mysqli_error($db));
  }
}

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

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


//************ trans in war

$sq6="select partcode,partname,sum(psnqty) from sparetrinwar where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' group by  partcode";
$re521=mysqli_query($db,$sq6);
while($res5=mysqli_fetch_array($re521))
{
$sql="update $tbl set inqty=(inqty+".$res6[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err10'.mysqli_error($db));
}

//*************888888888888 
$sqq="select partcode,partname,sum(qty) from salesreturn where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto'   group by  partcode";
$reein=mysqli_query($db,$sqq)or die('ERr retrn'.mysqli_error($db));

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


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

$sq2="select partcode,partname,sum(qty) from spareinvoice where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' 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]'";

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

$jrej=mysqli_query($db,"select jcno from jobcard where entrydate>='$dfrom' and entrydate <='$dto'  and brcode='$brcode'")or die('Err j:'.mysqli_error($db));
//date>='$dfrom'
while($jrow=mysqli_fetch_array($jrej))
{
$sq2="select partcode,partname,sum(qty) from jcardparts where brcode='$brcode' and jcno='$jrow[0]' group by  partcode";

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

while($res2=mysqli_fetch_array($re212j))
{
 
$sql="update $tbl set outqty=(outqty+".$res2[2].") where partcode='$res2[partcode]'";

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

}

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

$re2trout=mysqli_query($db,$sq2);


while($res2=mysqli_fetch_array($re2trout))
{
 
$sql="update $tbl set outqty=(outqty+".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err2'.mysqli_error($db));
}

//************ transout -war
$tbl12="spareinvwar".$brcode;
$sq5="select partcode,partname,sum(psnqty) from $tbl12 and entrydate>='$dfrom' and entrydate <='$dto' group by  partcode";
$re51t=mysqli_query($db,$sq5);
while($res5=mysqli_fetch_array($re51t))
{
$sql="update $tbl set outqty=(outqty+".$res5[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err8'.mysqli_error($db));
}

//********************************************************************* report 
$re1=mysqli_query($db,"select distinct subgroup from partmaster");
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>"; 
print "<th>Group <th>OP Qty <th>In Qty <th>Out Qty
<th>Closing";

while($r1=mysqli_fetch_array($re1))
{
 $sql="select sum(a.supqty)supqty, sum(a.inqty)inqty,sum(a.outqty)outqty from $tbl a,partmaster b where b.subgroup='$r1[subgroup]' and a.partcode=b.partcode";
// print $sql.'<br>';
  $rr1=mysqli_query($db,$sql) or die(mysqli_error($db));
$i=1;
$tprice=0;
$row=mysqli_fetch_array($rr1);
 print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
 print "<td>$r1[0]</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>";
 $close=($row[supqty]+$row[inqty]-$row[outqty]);
 print "<td align=right bgcolor='ivory'>$close</td>";

/*  $prres=mysqli_query($db,"select lstprice from spareprmast where partcode='$row[0]'");
  $prrow=mysqli_fetch_row($prres);
 $pr=($prrow[0]*($row[supqty]+$row[inqty]-$row[outqty]));
 $tprice+=$pr;
 print "<td align=right>".putComa($prrow[0])."</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></tr>";
 print "<tr ><td colspan=7 align=center bgcolor='silver'>Total</td>";
 print "<td  bgcolor='silver' align=right>".putComa($tprice)."</td><td bgcolor='silver'></td></tr>";
*/

}
print "</table>";
return;
} // end if show
?>

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

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

<h3>SPARE STOCK REPORT REPORT DATE WISE/Group Wise</H3>
<form action='<?print $PHP_SELF?>' method='post'>
<table cellspacing=1 cellpadding=1 bgcolor='steelblue'>
<tr><td bgcolor='white' align=center>
<table class=noprint>
<tr><td>Date from </td><td>dd <input type=text name='d1' size=2 value='1'>
mm <input type=text name='m1' size=2 value='4'>
yy <input type=text name='y1' size=4 value='<?print date("Y")?>'></td></tr>

<tr><td align=right>to </td><td>dd <input type=text name='d2' size=2 value='<?print date("d")?>'>
mm <input type=text name='m2' size=2 value='<?print date("m")?>'>
yy <input type=text name='y2' size=4 value='<?print date("Y")?>'>
</td></tr>

<tr><td colspan=2 align=center bgcolor='lightsteelblue'><input type=submit value='Show' name='show'></td></tr>
</table>
</td></tr>
</table>
</form>
<font color=red> Under construction</font>
