
<link type="text/css" href="includes/style1.css" rel="stylesheet" />
<link type="text/css" href="css/form1.css" rel="stylesheet" />
<?
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");
include('includes/top.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> <?PRINT $brrow[brman]?> </b><BR>
	 <?print $brrow[braddr].",".$brrow[brcity].",<br><u> PH - ".$brrow[brphone]?></u>
TIN: <?print $brrow[tin]?>, Ser Tax No: <?print $brrow[cstno]?>&nbsp;
</td></tr>
<tr><td COLSPAN=2 ALIGN=CENTER>
<?print "<h4><br>SPARE INVOICE REPORT - VAT</H4>";

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

<?
$invre=mysqli_query($db,"select distinct invtype from spareinvoice where brcode='$brcode' and vatorcst='vat'");
while($invrow=mysqli_fetch_array($invre))
{
$gtot1=0;
$ttx=0;
$tcess=0;
$ttot=0;
$ttrnd=0;
$re=mysqli_query($db,"select invno,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 vatorcst='vat' group by invno")or die('err'.mysqli_error($db));
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<th colspan=10><h3>Invoice Type $invrow[0]</th></tr>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Before Tax <th>Tax <th>Cess<th>Total <th>Rounded<th>G Total";
while($row=mysqli_fetch_array($re))
{
 print "\n<tr bgcolor=white><td>[".substr($brcode,0,3)."] S-$row[invno]</td><td>".datetohtml($row[invdate]);
 print "<td>".strtoupper($row[customer])."</td>";
  
 print "<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>";
$rnd=round($row[tot])-$row[tot];
$rndtot+=$rnd;
 printf("<td align=right>%.2f</td>",$rnd);

 print "<td align=right>".putComa(round($row[tot]))."</td>";
$ttrnd+=round($row[tot]);
  $gtot1+=$row[aprice];
$ttx+=$row[txamt];
$tcess+=$row[cess];
$ttot+=$row[tot];

}
}
print "<tr><td colspan=3><center><b>Total</td><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>";
 printf("<td align=right>%.2f</td>",$rndtot);

print "<td align=right>".putComa($ttrnd)."</td>";

 print "</tr></table><br>";

//*********** jcard SPARE
print "<h3>JOB CARD</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date <th>JobNo.";
print "<th>Reg No<th>Customer<th>Before Tax <th>Tax <th>Cess <th>Serv Amt <th>S Tax <th>Edu Cess <th>HR Edu <br>Cess <th>Total <th>Rounded <th>G Total";
$sq="select jcno,cname,entrydate,invno,msno from jobcard where entrydate>='$dfrom' and entrydate<='$dto' and jvno>0 and brcode='$brcode' order by invno";

$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    $sql="select sum(amt)sparice,sum(taxamt)stx,sum(cessamt)ces,sum(gtot)tt from jcardparts where jcno='$jrow[0]' and brcode='$brcode' group by jcno";
//print $sql.'<br>';
    $invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invre)>0)
  {
    while($invrow=mysqli_fetch_array($invre))
    {

   print "<tr bgcolor='white'><td>WS-$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[jcno]</td><td>$jrow[msno]</td><td>$jrow[cname]</td>";
   print "<td align=right>".putComa($invrow[0])."</td><td align=right>$invrow[1]</td>";
   print "<td align=right>$invrow[2]</td>";
//  print "<td align=right> -- $invrow[3]</td>"; j tot
   //printf("<td align=right>%.2f</td>",$rnd);
  // print "<td align=right>".putcoma(round($invrow[3]))."</td>";

    $sql="select sum(servamt)sparice,sum(servamt*servtx/100)stx,sum((servamt*servtx/100)*edutx/100),sum((servamt*servtx/100)*hredutx/100),sum(tot)tt from jcardserv where jcno='$jrow[0]' and brcode='$brcode' group by jcno";

$jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
$jrow2=mysqli_fetch_array($jres2);

print "<td align=right>".putComa($jrow2[0])."</td>";
   printf("<td align=right>%.2f</td>",$jrow2[1]);
   printf("<td align=right>%.2f</td>",$jrow2[2]);
   printf("<td align=right>%.2f</td>",$jrow2[3]);

//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   $rnd=round($jrow2[4]+$invrow[3]);
   $dif=$rnd-($jrow2[4]+$invrow[3]);
   print "<td align=right>".putComa($jrow2[4]+$invrow[3])."</td>";
   printf("<td align=right>%.2f</td>",$dif);
   print "<td align=right>".putComa(round($jrow2[4]+$invrow[3]))."</td>";
  
   $t1+=$invrow[0];
   $t2+=$invrow[1];
   $t3+=$invrow[2];
   //$t4+=$invrow[3];

   $t5+=$jrow2[0];
   $t6+=$jrow2[1];
   $t7+=$jrow2[2];
   $t8+=$jrow2[3];
   $t9+=($jrow2[4]+$invrow[3]);
   $tdiff+=$dif;
   $tgtot+=round($jrow2[4]+$invrow[3]);
    }
  } // end if numrow
 else
{
    $sql="select sum(servamt)sparice,sum(servamt*servtx/100)stx,sum((servamt*servtx/100)*edutx/100),sum((servamt*servtx/100)*hredutx/100),sum(tot)tt from jcardserv where jcno='$jrow[0]' and brcode='$brcode' group by jcno";

$jres21=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
$jrow2=mysqli_fetch_array($jres21);
   print "<tr bgcolor='white'><td>WS-$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[jcno]</td><td>$jrow[msno]</td><td>$jrow[cname]</td>";
print "<td></td><td></td><td></td>";
print "<td align=right>".putComa($jrow2[0])."</td>";
   printf("<td align=right>%.2f</td>",$jrow2[1]);
   printf("<td align=right>%.2f</td>",$jrow2[2]);
   printf("<td align=right>%.2f</td>",$jrow2[3]);

//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   $rnd=round($jrow2[4]);
   $dif=$rnd-($jrow2[4]);
   print "<td align=right>".putComa($jrow2[4])."</td>";
   printf("<td align=right>%.2f</td>",$dif);
   print "<td align=right>".putComa(round($jrow2[4]))."</td>";
  
/*   $t1+=$invrow[0];
   $t2+=$invrow[1];
   $t3+=$invrow[2];*/
   //$t4+=$invrow[3];

   $t5+=$jrow2[0];
   $t6+=$jrow2[1];
   $t7+=$jrow2[2];
   $t8+=$jrow2[3];
   $t9+=($jrow2[4]); //+$invrow[3]);
   $tdiff+=$dif;
   $tgtot+=round($jrow2[4]); //+$invrow[3]);

} // end else

}// end if jrow
   print "<tr><td colspan=4 align=right><b>Total</td><td align=right>".putComa($t1)."</td>";
   print "<td align=right>".putComa($t2)."</td>";
   print "<td align=right>".putComa($t3)."</td><td align=right>".putComa($t5)."</td>";
   printf ("<td align=right>%.2f</td>",$t6);
   printf ("<td align=right>%.2f</td>",$t7);
   printf ("<td align=right>%.2f</td>",$t8);
   print "<td align=right>".putComa($t9)."</td>";
   printf ("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right>".putComa($tgtot)."</td>";
  

print "</table>";
//***************88 J CARD SERVICE
/*
 $t10=0;
   $t0=0;
   $t1=0;
   $t2=0;
   $t4=0;
   $t5=0;
print "<h3>JOB CARD SERVICE</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Before Tax <th>Tax <th>Edu Cess<th>HR <br>EduCess<th>Total <th>Rounded<th>G Total";
$sq="select jcno,cname,date,invno from jobcard where date>='$dfrom' and date<='$dto' and jvno>0 and brcode='$brcode' order by invno";

$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    $sql="select sum(servamt)sparice,sum(servamt*servtx/100)stx,sum(servamt*edutx/100),sum(servamt*hredutx/100),sum(tot)tt from jcardserv where jcno='$jrow[0]' and brcode='$brcode' group by jcno";
//print $sql.'<br>';
    $invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invre)>0)
  {

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

   print "<tr bgcolor='white'><td>jc-$jrow[invno]</td><td>".datetohtml($jrow[date])."</td><td>$jrow[cname]</td>";
   print "<td align=right>".putComa($invrow[0])."</td>";
   printf("<td align=right>%.2f</td>",$invrow[1]);
   printf("<td align=right>%.2f</td>",$invrow[2]);
   printf("<td align=right>%.2f</td>",$invrow[3]);

//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   print "<td align=right>$invrow[4]</td>";
   $rnd=round($invrow[3])-$invrow[3];
   printf("<td align=right>%.2f</td>",$rnd);
   print "<td align=right>".putcoma(round($invrow[3]))."</td>";

   $t10+=$invrow[0];
   $t0+=$invrow[1];
   $t1+=$invrow[2];
   $t2+=$invrow[3];
   $t4+=$rnd;
   $t5+=round($invrow[3]);
    }
  } // end if numrow

}// end if jrow
   print "<tr><td colspan=3 align=right><b>Total</td><td align=right>".putComa($t10)."</td>";
   print "<td align=right>".putComa($t0)."</td>";
   print "<td align=right>".putComa($t1)."</td><td align=right>".putComa($t2)."</td>";
   printf ("<td align=right>%.2f</td>",$t4);
   printf ("<td align=right>%.2f</td>",$t5);
print "</table>";

*/

 //******************* CST
/*
print "<h4><br>SPARE INVOICE REPORT - CST</H4>";
$invre1=mysqli_query($db,"select distinct invtype from spareinvoice where brcode='$brcode' and vatorcst='cst'");
$rndtot=0;
while($invrow=mysqli_fetch_array($invre1))
{
$gtot1=0;
$ttx=0;
$tcess=0;
$ttot=0;
$ttrnd=0;
$re1=mysqli_query($db,"select invno,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 vatorcst='cst' group by invno")or die('err'.mysqli_error($db));
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<th colspan=10><h3>Invoice Type $invrow[0]</th></tr>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Before Tax <th>Tax <th>Cess<th>Total <th>Rounded<th>G Total";
while($row=mysqli_fetch_array($re1))
{
 print "\n<tr bgcolor=white><td>$row[invno]</td><td>".datetohtml($row[invdate]);
 print "<td>".strtoupper($row[customer])."</td>";
  
 print "<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>";
$rnd=round($row[tot])-$row[tot];
$rndtot+=$rnd;
 printf("<td align=right>%.2f</td>",$rnd);

 print "<td align=right>".putComa(round($row[tot]))."</td>";
$ttrnd+=round($row[tot]);
  $gtot1+=$row[aprice];
$ttx+=$row[txamt];
$tcess+=$row[cess];
$ttot+=$row[tot];

}
print "<tr><td colspan=3><center><b>Total</td><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>";
 printf("<td align=right>%.2f</td>",$rndtot);

print "<td align=right>".putComa($ttrnd)."</td>";

 print "</tr></table><br></center>";

}
*/
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><font color='white'>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>

