<?
session_start();
include("../opendb.php");
include("../test.php");
?>
<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
.noprint, .print {display:none;}
@media screen {
.noprint, .printns {display:block;}
.print {display:none;}
}
@media print {
.print {display:block;}
.noprint, .printns, {display:none;}
}
	th{background-color:steelblue;color:white}
	td{font-size:12}
.right{text-align:right}
</style>
<center>
<h3>SPARE INVOICE REPORT - 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='<?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>18
</td></tr>
</table>
</form>

<?

if($show)
{
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;
    mysqli_query($db,"delete from jbgrid");


//**************************************** jcardparts
$sql3="select distinct engineer from jcardparts";
  $grre3=mysqli_query($db,$sql3);
  while($grrow3=mysqli_fetch_array($grre3))
{

$sql="select distinct group1 from partmaster";
  $grre=mysqli_query($db,$sql);
  while($grrow=mysqli_fetch_array($grre))
{
$jres=mysqli_query($db,"select jcno from jobcard where entrydate>='$dfrom' and entrydate<='$dto' and jvno>0 and brcode='$brcode'")or die(mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
 //   $sql="select a.partcode,sum(qty)sqty,sum(amt)sparice,a.partname,sum(taxamt)stx,sum(cessamt)ces,sum(gtot)tt from jcardparts a,partmaster b where a.partcode=b.partcode and jcno='$jrow[0]' and a.brcode='$brcode' group by a.partcode order by b.group1";
      $sql="select a.partcode,sum(qty)sqty,sum(amt)sparice,a.partname,sum(taxamt)stx,sum(cessamt)ces,sum(gtot)tt,a.engineer from jcardparts a,partmaster b where a.partcode=b.partcode and b.group1='$grrow[0]' and a.engineer='$grrow3[0]'and jcno='$jrow[0]' and a.brcode='$brcode' group by a.partcode order by b.group1";

    $invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invre)>0)
  {

    while($invrow=mysqli_fetch_array($invre))
    {

   $sql="insert into jbgrid values('$invrow[partname]','$invrow[1]','$invrow[2]','$invrow[stx]', '$invrow[ces]','$invrow[tt]','$brrow[brname]','$grrow[0]','$invrow[engineer]','$jrow[0]')";
 mysqli_query($db,$sql);
    }
  } // end if numrow

}// end if jrow
  
}
}
}

//*************************service
$sql3="select distinct engineer from jcardserv";
  $grre3=mysqli_query($db,$sql3);

  while($grrow3=mysqli_fetch_array($grre3))
{

$jress=mysqli_query($db,"select jcno from jobcard where entrydate>='$dfrom' and entrydate<='$dto' and jvno>0 and brcode='$brcode'")or die(mysqli_error($db));
while($jrow=mysqli_fetch_array($jress))
{
 $servres=mysqli_query($db,"select sum(tot),jcno,sum(servamt),sum((servamt*servtx/100)+(servamt*edutx/100)+(servamt*hredutx/100)),engineer from jcardserv where jcno='$jrow[0]' and engineer='$grrow3[0]' and brcode='$brcode' group by jcno");
  $servrow=mysqli_fetch_row($servres);
  $sql="insert into jbgrid values('Service (JCNo $servrow[1])','-','$servrow[2]','$servrow[3]', '-','$servrow[0]','$brrow[brname]','Service','$servrow[4]')";
 
 mysqli_query($db,$sql);
  

}// end if jrow
  

}


//***************************************************8888888
print "<H3>Report From".datetohtml($dfrom).' to '.datetohtml($dto)." </h3>";
$repore=mysqli_query($db,"select distinct engineer from jbgrid order by engineer");
while($reporow=mysqli_fetch_array($repore))
{
  print "<fieldset><legend>$reporow[engineer]</legend>";
  $re2=mysqli_query($db,"select distinct grp from jbgrid order by grp");
  while($re_row=mysqli_fetch_array($re2))
  {
    $tqty=0;
    $tapr=0;
    $tstx=0;
    $tces=0;
    $ttot=0;

    print "<table border=1 width=100%> <th colspan=10>Group :$re_row[0]</th><tr>";
    print "<th>Part Name <th width=90>Qty <th width=90> Amount <th width=90>Tax <th width=90>Cess <th width=90>Total";
    $re3=mysqli_query($db,"select partname,sum(qty)sqty,sum(aprice)sparice,sum(taxamt)stx,sum(cessamt)ces,sum(tot)tt from jbgrid where grp='$re_row[0]' and engineer='$reporow[engineer]' group by partname")or die(mysqli_error($db));
    while($re2_row=mysqli_fetch_array($re3))
     {
      
     print "<tr><td>$re2_row[partname]</td><td align=right>$re2_row[1]</td><td align=right>$re2_row[2]</td>";
       print "<td align=right bgcolor='ivory'>$re2_row[stx]</td><td align=right>$re2_row[ces]</td><td align=right>$re2_row[tt]</td>";
    $tqty+=$re2_row[1];
    $tapr+=$re2_row[2];
    $tstx+=$re2_row[3];
    $tces+=$re2_row[4];
    $ttot+=$re2_row[5];

     }
    print "<tr bgcolor='lightsteelblue'><td align=center><b>Total</td>";
    print "<td align=right><b>".putComa($tqty)."</td><td align=right><b>".putComa($tapr)."</td>";
    print "<td align=right bgcolor='ivory'><b>".putComa($tstx)."</td>";
    print "<td align=right><b>".putComa($tces)."</td><td align=right><b>".putComa($ttot)."</td></tr>";

     print "</table><br>";
  }
  print "</fieldset><br>";
}
?>
