<?
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>
</td></tr>
</table>
</form>

<?

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

$brres=mysqli_query($db,"select distinct brcode,brname from branch order by brname");
while($brrow=mysqli_fetch_array($brres))
{

$invre=mysqli_query($db,"select distinct invtype from spareinvoice where brcode='$brrow[0]'");
while($invrow=mysqli_fetch_array($invre))
{
// print "<fieldset><legend> Type $invrow[0] from ".datetohtml($dfrom).' to '.datetohtml($dto)." Branch : $brrow[brname]</legend>";
  $sql="select distinct group1 from partmaster";
  $grre=mysqli_query($db,$sql);
  while($grrow=mysqli_fetch_array($grre))
  {
    $sql="select a.partcode,sum(qty)sqty,sum(aprice)sparice,a.partname,sum(taxamt)stx,sum(cessamt)ces,sum(tot)tt from spareinvoice a,partmaster b where a.partcode=b.partcode and b.group1='$grrow[0]' and a.entrydate>='$dfrom' and a.entrydate<='$dto'  and a.brcode='$brrow[0]' group by a.partcode order by b.group1";

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

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

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

//**************************************** jcardparts
$jres=mysqli_query($db,"select jcno from jobcard where date>='$dfrom' and date<='$dto'")or die(mysqli_error());
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 b.group1='$grrow[0]' and jcno='$jrow[0]' and a.brcode='$brrow[brcode]' group by a.partcode order by b.group1";

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

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

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

}// end if jrow
  } // end while grrow

// print "</fieldset><br>";
}
} // end while br
} //  end if

//***************************************************8888888
print "<H3>Invoiced and Not Invoiced Report <br>From".datetohtml($dfrom).' to '.datetohtml($dto)." </h3>";
$repore=mysqli_query($db,"select distinct brcode from tempbrrepo order by brcode");
while($reporow=mysqli_fetch_array($repore))
{
  print "<fieldset><legend>$reporow[brcode]</legend>";
  $re2=mysqli_query($db,"select distinct grp from tempbrrepo 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 tempbrrepo where grp='$re_row[0]' and brcode='$reporow[0]' group by partname")or die(mysqli_error());
    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>";
  }
  print "</fieldset><br>";
}
?>
