<?
session_start();
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='../index.html'; </script>";
 return;
}
include("../opendb.php");
include("../test.php");

if($show)
{
?>
<style>

	td,th,body{font-size:14;font-family:'arial narrow';letter-spacing:1}
</style>
<center>
<?
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;

$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<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>
<tr><td COLSPAN=2 ALIGN=CENTER>
<?print "<h4><br>SPARE INVOICE REPORT - ENGINEER WISE</H4>";

print " From ".datetohtml($dfrom).' to '.datetohtml($dto);
?>
</td></tr>
</table>

<?
$tbl="eng_gr".$brcode;
$sql="create table $tbl (engineer varchar(200),invno int(10),invdate date,customer varchar(200), aprice double(14,2),taxamt double(14,2),cess double(14,2), tot double(14,2),group1 varchar(100))";

mysqli_query($db,$sql);
mysqli_query($db,"delete from $tbl");

$invre=mysqli_query($db,"select distinct invtype from spareinvoice where brcode='$brcode'");
while($invrow=mysqli_fetch_array($invre))
{

$engre=mysqli_query($db,"select distinct engineer from spareinvoice where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' order by engineer");
while($engrow=mysqli_fetch_array($engre))
{
$gtot1=0;
$ttx=0;
$tcess=0;
$ttot=0;
$ttrnd=0;

$sql="select invno,partcode,invdate,customer,sum(aprice) aprice,sum(taxamt)txamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto' and engineer='$engrow[0]' group by invno,partcode";

$re=mysqli_query($db,$sql)or die(mysqli_error($db));

while($row=mysqli_fetch_array($re))
{
 $grr=mysqli_query($db,"select group1 from partmaster where partcode='$row[partcode]'");
 $gr_row=mysqli_fetch_row($grr)or die(mysqli_error($db));
$sql="insert into $tbl values('$engrow[0]','$row[invno]','$row[invdate]','$row[customer]', '$row[aprice]','$row[txamt]','$row[cess]','$row[tot]','$gr_row[0]')";

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

//******************* report
$re2=mysqli_query($db,"select distinct engineer from $tbl order by engineer");
while($engrow=mysqli_fetch_row($re2))
{
$gtot1=0;
$ttx=0;
$tcess=0;
$ttot=0;
$ttrnd=0;

print "\n<table border=1 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue' width=100%>";
print "<th colspan=10><h3>Invoice Type $invrow[0] - Engineer : $engrow[0]</th></tr>";
print "<tr><td colspan=10>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue' width=100%>  ";
print "<th>Before Tax <th>Tax <th>Cess<th>Total <th>Rounded <th>Group";
$s="select distinct group1 from partmaster";

$gr=mysqli_query($db,$s) or die(mysqli_error($db));
while($grrow=mysqli_fetch_row($gr))
{

$sq="select sum(a.aprice) aprice,sum(a.taxamt)txamt,sum(a.cess)cess ,sum(a.tot)tot, a.group1 from $tbl a where  a.invdate>='$dfrom' and a.invdate<='$dto' and a.engineer='$engrow[0]' and a.group1='$grrow[0]' group by a.group1";

 $grres=mysqli_query($db,$sq)or die('err'.mysqli_error($db));
while($row=mysqli_fetch_array($grres))
{
/*
print "\n<tr bgcolor=white><td>$row[invno]</td><td>".datetohtml($row[invdate]);
 print "<td>".strtoupper($row[customer])."</td>";
*/  
 print "<tr><td align=right>".putComa($row[aprice])."</td><td align=right>".putComa($row[txamt])."</td>";
 print "<td align=right>".putComa($row[cess])."</td>";
 print "<td align=right>".putComa($row[tot])."</td>";
 print "<td align=right>".putComa(round($row[tot]))."</td>";
 print "<td>".$row[group1]."</td></tr>";

$ttrnd+=round($row[tot]);
  $gtot1+=$row[aprice];
$ttx+=$row[txamt];
$tcess+=$row[cess];
$ttot+=$row[tot];
}

}
print "\n<tr bgcolor='white'><td align=right>".putComa($gtot1)."</td>";
print "<td align=right>".putComa($ttx)."</td>";
print "<td align=right>".putComa($tcess)."</td>";
print "<td align=right>".putComa($ttot)."</td>";
print "<td align=right>".putComa($ttrnd)."</td>";
print "</tr></table></td></tr></table><br>";
}
print "</table></td></tr>";
}
print "</table>";
 print "</center><p align=right> Printed by $user</p>";

return;
}// end if
?>

<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
//	tr{background-color:white}
	th{background-color:steelblue;color:white}
	td{font-size:13}
.right{text-align:right};
</style>

<center>
<h3>SPARE INVOICE REPORT -2</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='<?print date("d")?>'>
mm <input type=text name='m1' size=2 value='<?print date("m")?>'>
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>

