<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='../index.html'; </script>";
 return;
}
include("../opendb.php");
include("../test.php");
if(isset($_POST['show']))
{
?>
<style>
td,th,body{font-size:14;font-family:'arial narrow';letter-spacing:1}
</style>
<center>
<?
$d1=$_REQUEST['d1'];
$m1=$_REQUEST['m1'];
$y1=$_REQUEST['y1'];

$d2=$_REQUEST['d2'];
$m2=$_REQUEST['m2'];
$y2=$_REQUEST['y2'];
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;

$ordby=$_REQUEST['ordby'];

$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<table>
<TR><TD COLSPAN=2 ALIGN=CENTER><b> PSN AUTOMOTIVE MARKETING 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 </H4>";

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

<?
        $grand_gtot1=0;
        $grand_ttx=0;
        $grand_tcess=0;
        $grand_ttot=0;
        $grand_ttrnd=0;
        $grand_rndtot=0;

	
      
     $ttxsgst=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    
    
    $gtota5=0;
    $ttxgst5=0;
    $gtota12=0;
    $ttxgst12=0;
     $gtota18=0;
    $ttxgst18=0;
    
     $gtota28=0;
    $ttxgst28=0;
    $tthand=0;
    
    //if($ordby=='Invoice')
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<th colspan=10><h3>INTRA STATE</th></tr>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Tin<th>Customer<th>Before Tax 5% <th>GST 5% <th>Before Tax 12% <th>GST 12% <th>Before Tax 18%<th> GST 18%<th>Before Tax 28% <th>GST 28%<th>Hand<th>Total <th>Rounded<th>G Total<th>Ref";

    $reinvno=mysqli_query($db,"select distinct invno,invdate,customer from spareinvoice  where brcode='$brcode' and cgstp>0 and invdate>='$dfrom' and invdate<='$dto' ")or die('err'.mysqli_error($db));
    
    while($rowinvno=mysqli_fetch_array($reinvno))
{
$re=mysqli_query($db,"select sum(aprice) aprice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt ,sum(tot)tot, cgstp,sgstp,sum(hand)hand from spareinvoice  where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and invno='$rowinvno[0]' group by invno,cgstp")or die('err'.mysqli_error($db));
    //else
//$re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(taxamt)txamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invdate,invno")or die('err'.mysqli_error($db));    
$bta5=0;
$gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
$totcs=0;
    $hand1=0;

while($row=mysqli_fetch_array($re))
{
    $cus_result=mysqli_query($db,"select tin from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
	$cus_row=mysqli_fetch_row($cus_result);
	$mtin=$cus_row[0];
	//$btgstamt=$row[3];
		$totcs+= $row['tot'];
	$hand1+=$row['hand'];
	$tthand+=$row['hand']; 
	 if(($row['cgstp']+$row['sgstp'])==5.00)
	 {
	     $bta5=$row['aprice'];
	  $gsta5= $row['cgstamt']+$row['sgstamt'];  
	  $gtota5+=$bta5;
    $ttxgst5+=$gsta5;
	 }
	 else if(($row['cgstp']+$row['sgstp'])==12.00)
	 {
	     $bta12=$row['aprice'];
	  $gsta12= $row['cgstamt']+$row['sgstamt']; 
	  $gtota12+=$bta12;
    $ttxgst12+=$gsta12;
	 }
	 else if(($row['cgstp']+$row['sgstp'])==18.00)
	 {
	     $bta18=$row['aprice'];
	  $gsta18= $row['cgstamt']+$row['sgstamt'];  
	  $gtota18+=$bta18;
    $ttxgst18+=$gsta18;
	 }
	 else if(($row['cgstp']+$row['sgstp'])==28.00)
	 {
	     $bta28=$row['aprice'];
	  $gsta28= $row['cgstamt']+$row['sgstamt']; 
	   $gtota28+=$bta28;
    $ttxgst28+=$gsta28;
	 }
	 else
	 {
	     
	 }

	 
}
	 
    print "\n<tr bgcolor=white><td>$rowinvno[0]</td><td>".datetohtml($rowinvno['invdate']);
    print "<td>".$mtin."</td>";
    print "<td>".strtoupper($rowinvno['customer'])."</td>";
   
    print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";
    print "<td align=right>".putComa($hand1)."</td>";
    print "<td align=right>".putComa($totcs)."</td>";
    $rnd=round($totcs)-$totcs;
    $rndtot+=$rnd;
    printf("<td align=right>%.2f</td>",$rnd);
    print "<td align=right>".putComa(round($totcs))."</td>";
    print "<td>".$row['msno']."</td>";
    $ttrnd+=round($totcs);
    $gtot1+=$row['aprice'];
    $ttxcgst+=$row['cgstamt'];
     $ttxsgst+=$row['sgstamt'];
 
    $tcess+=$row['cess'];
    $ttot+=$totcs;

}

print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota5)."</td>";
print "<td align=right>".putComa($ttxgst5)."</td>";
print "<td align=right>".putComa($gtota12)."</td>";
print "<td align=right>".putComa($ttxgst12)."</td>";
print "<td align=right>".putComa($gtota18)."</td>";
print "<td align=right>".putComa($ttxgst18)."</td>";

print "<td align=right>".putComa($gtota28)."</td>";
print "<td align=right>".putComa($ttxgst28)."</td>";
print "<td align=right>".putComa($tthand)."</td>";
print "<td align=right>".putComa($ttot)."</td>";


printf("<td align=right>%.2f</td>",$rndtot);
print "<td align=right>".putComa($ttrnd)."</td></tr><br>";

//  inter statespare invoice

$grand_gtot1=0;
        $grand_ttx=0;
        $grand_tcess=0;
        $grand_ttot=0;
        $grand_ttrnd=0;
        $grand_rndtot=0;

	$gtot1=0;
   $ttxigst=0;
     
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    
    //new
    $gtota5=0;
    $ttxgst5=0;
    $gtota12=0;
    $ttxgst12=0;
     $gtota18=0;
    $ttxgst18=0;
    
     $gtota28=0;
    $ttxgst28=0;
    $tthand=0;
    
    //if($ordby=='Invoice')
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<th colspan=10><h3>INTER STATE</th></tr>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Tin<th>Customer<th>Before Tax 5% <th>GST 5% <th>Before Tax 12% <th>GST 12% <th>Before Tax 18%<th> GST 18%<th>Before Tax 28% <th>GST 28%<th>Hand<th>Total <th>Rounded<th>G Total<th>Ref";

    $reinvno=mysqli_query($db,"select distinct invno,invdate,customer from spareinvoice  where brcode='$brcode' and igstp>0 and invdate>='$dfrom' and invdate<='$dto' ")or die('err'.mysqli_error($db));
    
    while($rowinvno=mysqli_fetch_array($reinvno))
{
$re=mysqli_query($db,"select sum(aprice) aprice,sum(igstamt)igstamt ,sum(tot)tot, igstp,sum(hand) hand from spareinvoice  where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and invno='$rowinvno[0]' group by invno,igstp")or die('err'.mysqli_error($db));
    //else
//$re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(taxamt)txamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invdate,invno")or die('err'.mysqli_error($db));    
$bta5=0;
$gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
 $totcs=0;
    $hand1=0;
while($row=mysqli_fetch_array($re))
{
    $cus_result=mysqli_query($db,"select tin from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
	$cus_row=mysqli_fetch_row($cus_result);
	$mtin=$cus_row[0];
	//$btgstamt=$row[3];
		$totcs+= $row['tot'];
	$hand1+=$row['hand'];
	$tthand+=$row['hand']; 
	 if($row['igstp']==5.00)
	 {
	     $bta5=$row['aprice'];
	  $gsta5= $row['igstamt'];  
	  $gtota5+=$bta5;
    $ttxgst5+=$gsta5;
	 }
	 else if($row['igstp']==12.00)
	 {
	     $bta12=$row['aprice'];
	  $gsta12= $row['igstamt']; 
	  $gtota12+=$bta12;
    $ttxgst12+=$gsta12;
	 }
	 else if($row['igstp']==18.00)
	 {
	     $bta18=$row['aprice'];
	  $gsta18= $row['igstamt'];  
	  $gtota18+=$bta18;
    $ttxgst18+=$gsta18;
	 }
	 else if($row['igstp']==28.00)
	 {
	     $bta28=$row['aprice'];
	  $gsta28= $row['igstamt']; 
	   $gtota28+=$bta28;
    $ttxgst28+=$gsta28;
	 }
	 else
	 {
	     
	 }
	 
}
	 //$tthand+=$row[hand];
    print "\n<tr bgcolor=white><td>$rowinvno[0]</td><td>".datetohtml($rowinvno['invdate']);
    print "<td>".$mtin."</td>";
    print "<td>".strtoupper($rowinvno['customer'])."</td>";
   
    print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";
    print "<td align=right>".putComa($hand1)."</td>";
    print "<td align=right>".putComa($totcs)."</td>";
    $rnd=round($totcs)-$totcs;
    $rndtot+=$rnd;
    printf("<td align=right>%.2f</td>",$rnd);
    print "<td align=right>".putComa(round($totcs))."</td>";
    print "<td>".$row['msno']."</td>";
    $ttrnd+=round($totcs);
    $gtot1+=$row['aprice'];
    $ttxcgst+=$row['cgstamt'];
     $ttxsgst+=$row['sgstamt'];
 
    $tcess+=$row['cess'];
    $ttot+=$totcs;

}

print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota5)."</td>";
print "<td align=right>".putComa($ttxgst5)."</td>";
print "<td align=right>".putComa($gtota12)."</td>";
print "<td align=right>".putComa($ttxgst12)."</td>";
print "<td align=right>".putComa($gtota18)."</td>";
print "<td align=right>".putComa($ttxgst18)."</td>";

print "<td align=right>".putComa($gtota28)."</td>";
print "<td align=right>".putComa($ttxgst28)."</td>";
print "<td align=right>".putComa($tthand)."</td>";
print "<td align=right>".putComa($ttot)."</td>";


printf("<td align=right>%.2f</td>",$rndtot);
print "<td align=right>".putComa($ttrnd)."</td></tr><br>";


//0 tax invoice

$gtota0=0;
    $ttxgst0=0;
    
    $tthand=0;
    
    //if($ordby=='Invoice')
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<th colspan=10><h3>0 % Tax Invoice</th></tr>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Tin<th>Customer<th>Before Tax 0% <th>GST 0% <th>Hand<th>Total <th>Rounded<th>G Total<th>Ref";

    $reinvno=mysqli_query($db,"select distinct invno,invdate,customer from spareinvoice  where brcode='$brcode' and (cgstp+sgstp+igstp)=0 and invdate>='$dfrom' and invdate<='$dto' ")or die('err'.mysqli_error($db));
    while($rowinvno=mysqli_fetch_array($reinvno))
    
{
$re=mysqli_query($db,"select sum(aprice) aprice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt ,sum(tot)tot, cgstp,sgstp,sum(hand) hand from spareinvoice  where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and invno='$rowinvno[0]' group by invno")or die('err'.mysqli_error($db));
    //else
//$re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(taxamt)txamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invdate,invno")or die('err'.mysqli_error($db));    
$bta0=0;
$gsta0=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
$hand1=0;
$totcs=0;
 $totcs=0;
    $hand1=0;
while($row=mysqli_fetch_array($re))
{
    $cus_result=mysqli_query($db,"select tin from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
	$cus_row=mysqli_fetch_row($cus_result);
	$mtin=$cus_row[0];
	//$btgstamt=$row[3];
	 $totcs+= $row['tot'];
	$hand1+=$row['hand'];
	$tthand+=$row['hand']; 
	 if(($row['cgstp']+$row['sgstp']+$row['igstp'])==0.00)
	 {
	     $bta0=$row['aprice'];
	  $gsta0= $row['cgstamt']+$row['sgstamt']+$row['igstamt'];  
	  $gtota0+=$bta0;
    $ttxgst0+=$gsta0;
	 }
	
}	 
	 
    print "\n<tr bgcolor=white><td>$rowinvno[0]</td><td>".datetohtml($rowinvno['invdate']);
    print "<td>".$mtin."</td>";
    print "<td>".strtoupper($rowinvno['customer'])."</td>";
   
    print "<td align=right>".putComa($bta0)."</td><td align=right>".putComa($gsta0)."</td>";
      print "<td align=right>".putComa($hand1)."</td>";
    print "<td align=right>".putComa($totcs)."</td>";
    $rnd=round($totcs)-$totcs;
  $rndtot+=$rnd;
    printf("<td align=right>%.2f</td>",$rnd);
    print "<td align=right>".putComa(round($totcs))."</td>";
    print "<td>".$row['msno']."</td>";
    $ttrnd+=round($totcs);
    $gtot1+=$row['aprice'];
    $ttxcgst+=$row['cgstamt'];
     $ttxsgst+=$row['sgstamt'];
 
    $tcess+=$row['cess'];
    $ttot+=$totcs;

}

print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota0)."</td>";
print "<td align=right>".putComa($ttxgst0)."</td>";
print "<td align=right>".putComa($tthand)."</td>";
print "<td align=right>".putComa($ttot)."</td>";


printf("<td align=right>%.2f</td>",$rndtot);
print "<td align=right>".putComa($ttrnd)."</td></tr><br>";


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

//*********** jcard LOCAL

$t1=0;
$tgtot=0;
print "<h3>JOB CARD  LOCAL</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Jcno<th>Before Tax 5% <th>GST 5% <th>Before Tax 12% <th>GST 12% <th>Before Tax 18%<th> GST 18%<th>Before Tax 28% <th>GST 28% <th>Serv Amt  <th>GST AMT <th>Total <th>Rounded <th>G Total";
   // if($ordby=='Invoice')
$sq="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by invno";
   // else
//$sq="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by entrydate,invno";
 $gtota5=0;
    $ttxgst5=0;
    $gtota12=0;
    $ttxgst12=0;
     $gtota18=0;
    $ttxgst18=0;
    
     $gtota28=0;
    $ttxgst28=0;
    $gtotserv=0;
    $gtotsertax=0;
    $ginvtot=0;
    
$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    
    $servflg=0;
   // $servflg2=0;
    $invtot=0;
    $jcno=$jrow['jcno'];
    $invno=$jrow['invno'];
    $entrydate=$jrow['entrydate'];
    $cname=$jrow['cname'];
    $jcservamt=0;
    $jcservtax=0;
  $sql="select sum(amt)aprice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(gtot)tot ,cgstp,sgstp from jcardparts where invno='$jrow[3]' and brcode='$brcode' and cgstp>0 group by invno,cgstp";
//print $sql.'<br>';
  $invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invre)>0)
  {
       $servflg2=0;
      $bta5=0;
$gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
    while($row=mysqli_fetch_array($invre))
    {
        
        if(($row['cgstp']+$row['sgstp'])==5.00)
	 {
	     $bta5=$row['aprice'];
	  $gsta5= $row['cgstamt']+$row['sgstamt'];  
	  $gtota5+=$bta5;
    $ttxgst5+=$gsta5;
    
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
    
	 }
	 else if(($row['cgstp']+$row['sgstp'])==12.00)
	 {
	     $bta12=$row['aprice'];
	  $gsta12= $row['cgstamt']+$row['sgstamt']; 
	  $gtota12+=$bta12;
    $ttxgst12+=$gsta12;
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else if(($row['cgstp']+$row['sgstp'])==18.00)
	 {
	     $bta18=$row['aprice'];
	  $gsta18= $row['cgstamt']+$row['sgstamt'];  
	  $gtota18+=$bta18;
    $ttxgst18+=$gsta18;
    
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else if(($row['cgstp']+$row['sgstp'])==28.00)
	 {
	     $bta28=$row['aprice'];
	  $gsta28= $row['cgstamt']+$row['sgstamt']; 
	   $gtota28+=$bta28;
    $ttxgst28+=$gsta28;
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else
	 {
	     
	 }
	 
    }
//$btgstamtp=$invrow[0];
  /* print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[cname]</td><td>$jrow[jcno]</td>";
   print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";*/
 
   //print "<td align=right> -- $invrow[3]</td>";
   //printf("<td align=right>%.2f</td>",$rnd);
   //print "<td align=right>".putcoma(round($invrow[3]))."</td>";
   
   $t1+=$btgstamtp;
   $t2+=$invrow[1];
   $t3+=$invrow[2];
   $t8+=$invrow[3];
    $t9+=$invrow[3];
	if($servflg==0)
	{
	    
	
	   $sql11="select sum(servamt)sparice,sum(cgstamt)cgsttax,sum(sgstamt)sgsttax,cgstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and cgstp>0 group by invno,cgstp";

$jres2=mysqli_query($db,$sql11)or die('Err'.mysqli_error($db));
$jrow2=mysqli_fetch_array($jres2);
$btgstamts=$jrow2[0];
   //print "<td align=right>".putComa($btgstamts)."</td>";
   
  //print "<td align=right>".putComa($jrow2[cgsttax]+$jrow2[sgsttax])."</td>";
   //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]);
  
  $jcservamt=$btgstamts;
  $jcservtax=$jrow2['cgsttax']+$jrow2['sgsttax'];
  $invtot+=$jrow2['tt'];
  $tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); 
   $gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow2['tt'];
  // print "<td align=right>".putComa($invtot)."</td>";
  // printf("<td align=right>%.2f</td>",$tdiff);
   //print "<td align=right>".putComa($tgtot)."</td>";
   //print "<td>".$jrow[4]."</td>";
  
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow2[1]+$jrow2[2];
   $t7+=$jrow2[2];
   $t8+=$jrow2[4];
   $t9+=$jrow2[4];
   $tdiff+=$dif;
   $tgtot+=round($t9);
   $servflg=1;
	
   }
   
   
  
   
  } 
  
  // end if numrow
  
  
 else
{
$servflg2=1;

     $sql444="select sum(servamt)sparice,sum(cgstamt)cgsttax,sum(sgstamt)sgsttax,cgstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and cgstp>0 group by invno,cgstp";

$jres21=mysqli_query($db,$sql444)or die('Err'.mysqli_error($db));
$jrow24=mysqli_fetch_array($jres21);
   //print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[cname]</td><td>$jrow[jcno]</td>";

$btgstamts=$jrow24[0];
  // print "<td align=right>".putComa($btgstamts)."</td>";
   // print "<td align=right>".putComa($jrow24[cgsttax]+$jrow24[sgsttax])."</td>";
  $jcservamt=$btgstamts;
  $jcservtax=$jrow24['cgsttax']+$jrow24['sgsttax'];
  
 $invtot+=$jrow24['tt'];
$tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); //+$invrow[3]);
$gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow24['tt'];
    
//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   //$rnd=round($jrow24[4]);
  // $dif=$rnd-($jrow24[4]);
  //print "<td align=right>".putComa($invtot)."</td>";
   //printf("<td align=right>%.2f</td>",$tdiff);
  // print "<td align=right>".putComa($tgtot)."</td>";
  /*   $t1+=$invrow[0];
   $t2+=$invrow[1];
   $t3+=$invrow[2];*/
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow24[1]+$jrow24[2];
   $t7+=$jrow24[2];
   $t8+=$jrow24[4];
   //$t9+=($jrow2[4]); //+$invrow[3]);
   
} // end else

 if($invtot>0)
  {
   print "<tr bgcolor='white'><td>$invno</td><td>".datetohtml($entrydate)."</td><td>$cname</td><td>$jcno</td>";
   if($servflg2==1)
   {
   // print "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>";   
   }
    print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";
   print "<td align=right>".putComa($jcservamt)."</td>";
   
  print "<td align=right>".putComa($jcservtax)."</td>";
  
   print "<td align=right>".putComa($invtot)."</td>";
  printf("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right>".putComa(round($invtot))."</td>";
   
  }

}// end if jrow
  // print "<tr><td colspan=4 align=right><b>Total1</td>";
   print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota5)."</td>";
print "<td align=right>".putComa($ttxgst5)."</td>";
print "<td align=right>".putComa($gtota12)."</td>";
print "<td align=right>".putComa($ttxgst12)."</td>";
print "<td align=right>".putComa($gtota18)."</td>";
print "<td align=right>".putComa($ttxgst18)."</td>";

print "<td align=right>".putComa($gtota28)."</td>";
print "<td align=right>".putComa($ttxgst28)."</td>";
   print "<td align=right>".putComa($gtotserv)."</td>";
print "<td align=right>".putComa($gtotsertax)."</td>";
print "<td align=right>".putComa($ginvtot)."</td>";
$ttround=round($ginvtot)-$ginvtot;
   printf ("<td align=right>%.2f</td>",$ttround);
   print "<td align=right>".putComa(round($ginvtot))."</td>";
  

print "</table>";


//******************* JOBCARD IGST START



//*********** jcard

$t1=0;
$tgtot=0;
print "<h3>JOB CARD  INTER STATE</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Jcno<th>Before Tax 5% <th>GST 5% <th>Before Tax 12% <th>GST 12% <th>Before Tax 18%<th> GST 18%<th>Before Tax 28% <th>GST 28% <th>Serv Amt  <th>GST AMT <th>Total <th>Rounded <th>G Total";
   // if($ordby=='Invoice')
$sqig="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by invno";
   // else
//$sq="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by entrydate,invno";
 $gtota5=0;
    $ttxgst5=0;
    $gtota12=0;
    $ttxgst12=0;
     $gtota18=0;
    $ttxgst18=0;
    
     $gtota28=0;
    $ttxgst28=0;
    $gtotserv=0;
    $gtotsertax=0;
    $ginvtot=0;
    
    $bta5=0;
    $gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;

$jres=mysqli_query($db,$sqig)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    $servflg=0;
    $invtot=0;
    $jcno=$jrow['jcno'];
    $invno=$jrow['invno'];
    $entrydate=$jrow['entrydate'];
    $cname=$jrow['cname'];
    $jcservamt=0;
    $jcservtax=0;
    
  $sqlig="select sum(amt)aprice,sum(igstamt)igstamt,sum(gtot)tot ,igstp from jcardparts where invno='$jrow[3]' and brcode='$brcode' and igstp>0 group by invno,igstp";
//print $sql.'<br>';
  $invreig=mysqli_query($db,$sqlig) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invreig)>0)
  {
      
      $bta5=0;
$gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
    while($row=mysqli_fetch_array($invreig))
    {
        
        if($row['igstp']==5.00)
	 {
	     $bta5=$row['aprice'];
	  $gsta5= $row['igstamt'];  
	  $gtota5+=$bta5;
    $ttxgst5+=$gsta5;
    
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
    
	 }
	 else if($row['igstp']==12.00)
	 {
	     $bta12=$row['aprice'];
	  $gsta12= $row['igstamt']; 
	  $gtota12+=$bta12;
    $ttxgst12+=$gsta12;
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else if($row['igstp']==18.00)
	 {
	     $bta18=$row['aprice'];
	  $gsta18= $row['igstamt'];  
	  $gtota18+=$bta18;
    $ttxgst18+=$gsta18;
    
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else if($row['igstp']==28.00)
	 {
	     $bta28=$row['aprice'];
	  $gsta28= $row['igstamt']; 
	   $gtota28+=$bta28;
    $ttxgst28+=$gsta28;
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
	 }
	 else
	 {
	     
	 }
	 
    }
//$btgstamtp=$invrow[0];
  // print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[cname]</td><td>$jrow[jcno]</td>";
  /*
   print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";
 */
   //print "<td align=right> -- $invrow[3]</td>";
   //printf("<td align=right>%.2f</td>",$rnd);
   //print "<td align=right>".putcoma(round($invrow[3]))."</td>";
   
   $t1+=$btgstamtp;
   $t2+=$invrow[1];
   $t3+=$invrow[2];
   $t8+=$invrow[3];
    $t9+=$invrow[3];
	if($servflg==0)
	{
	    
	
	   $sql11="select sum(servamt)sparice,sum(igstamt)igsttax,igstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and igstp>0 group by invno,igstp";

$jres2=mysqli_query($db,$sql11)or die('Err'.mysqli_error($db));
$jrow2=mysqli_fetch_array($jres2);
$btgstamts=$jrow2[0];
   //print "<td align=right>".putComa($btgstamts)."</td>";
   
  //print "<td align=right>".putComa($jrow2[igsttax])."</td>";
  
  
   //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]);
  
  $jcservamt=$btgstamts;
  $jcservtax=$jrow2['igsttax'];
  
  $invtot+=$jrow2['tt'];
  $tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); 
   $gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow2['tt'];
   //print "<td align=right>".putComa($invtot)."</td>";
  // printf("<td align=right>%.2f</td>",$tdiff);
  // print "<td align=right>".putComa($tgtot)."</td>";
   //print "<td>".$jrow[4]."</td>";
  
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow2[1];
   $t7+=$jrow2[2];
   $t8+=$jrow2[4];
   $t9+=$jrow2[4];
   $tdiff+=$dif;
   $tgtot+=round($t9);
   $servflg=1;
	
	
   }
  
   
  } // end if numrow
 else
{


     $sql44="select sum(servamt)sparice,sum(igstamt)igsttax,igstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and igstp>0 group by invno,igstp";
//print $sql44;
$jres21=mysqli_query($db,$sql44)or die('Err'.mysqli_error($db));
$jrow24=mysqli_fetch_array($jres21);
  // print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[cname]</td><td>$jrow[jcno]</td>";
//print "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>";
$btgstamts=$jrow24[0];
  // print "<td align=right>".putComa($btgstamts)."</td>";
    //print "<td align=right>".putComa($jrow24[igsttax])."</td>";
  
  $jcservamt=$btgstamts;
  $jcservtax=$jrow24['igsttax'];
 $invtot+=$jrow24['tt'];
$tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); //+$invrow[3]);
$gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow24['tt'];
    
//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   //$rnd=round($jrow24[4]);
  // $dif=$rnd-($jrow24[4]);
  //print "<td align=right>".putComa($invtot)."</td>";
   //printf("<td align=right>%.2f</td>",$tdiff);
   //print "<td align=right>".putComa($tgtot)."</td>";
   /*   $t1+=$invrow[0];
   $t2+=$invrow[1];
   $t3+=$invrow[2];*/
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow24[1]+$jrow2[2];
   $t7+=$jrow24[2];
   $t8+=$jrow24[4];
   //$t9+=($jrow2[4]); //+$invrow[3]);
   
} // end else

if($invtot>0)
  {
   print "<tr bgcolor='white'><td>$invno</td><td>".datetohtml($entrydate)."</td><td>$cname</td><td>$jcno</td>";
   //print "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>";
    print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    print "<td align=right>".putcoma($bta12)."</td><td align=right>".putComa($gsta12)."</td>";
    print "<td align=right>".putcoma($bta18)."</td><td align=right>".putComa($gsta18)."</td>";
   
   print "<td align=right>".putcoma($bta28)."</td><td align=right>".putComa($gsta28)."</td>";
   print "<td align=right>".putComa($jcservamt)."</td>";
   
  print "<td align=right>".putComa($jcservtax)."</td>";
  
   print "<td align=right>".putComa($invtot)."</td>";
  printf("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right>".putComa(round($invtot))."</td>";
   
  }
  
}// end if jrow
  // print "<tr><td colspan=4 align=right><b>Total1</td>";
   print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota5)."</td>";
print "<td align=right>".putComa($ttxgst5)."</td>";
print "<td align=right>".putComa($gtota12)."</td>";
print "<td align=right>".putComa($ttxgst12)."</td>";
print "<td align=right>".putComa($gtota18)."</td>";
print "<td align=right>".putComa($ttxgst18)."</td>";

print "<td align=right>".putComa($gtota28)."</td>";
print "<td align=right>".putComa($ttxgst28)."</td>";
   print "<td align=right>".putComa($gtotserv)."</td>";
print "<td align=right>".putComa($gtotsertax)."</td>";
print "<td align=right>".putComa($ginvtot)."</td>";
$ttround=round($ginvtot)-$ginvtot;
   printf ("<td align=right>%.2f</td>",$ttround);
   print "<td align=right>".putComa(round($ginvtot))."</td>";
  

print "</table>";
///*****************************************************************************


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

//*********** jcard 0%

$t1=0;
$tgtot=0;
print "<h3>JOB CARD  0 %</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date ";
print "<th>Customer<th>Jcno<th>Before Tax 0% <th>GST 0%  <th>Serv Amt  <th>GST AMT <th>Total <th>Rounded <th>G Total";
   // if($ordby=='Invoice')
$sq="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by invno";
   // else
//$sq="select jcno,cname,entrydate,invno,engineer from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  and brcode='$brcode' order by entrydate,invno";
 $gtota5=0;
    $ttxgst5=0;
    $gtota12=0;
    $ttxgst12=0;
     $gtota18=0;
    $ttxgst18=0;
    
     $gtota28=0;
    $ttxgst28=0;
    $gtotserv=0;
    $gtotsertax=0;
    $ginvtot=0;
    
$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    
    $servflg=0;
   // $servflg2=0;
    $invtot=0;
    $jcno=$jrow['jcno'];
    $invno=$jrow['invno'];
    $entrydate=$jrow['entrydate'];
    $cname=$jrow['cname'];
    $jcservamt=0;
    $jcservtax=0;
  $sql="select sum(amt)aprice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt)igstamt,sum(gtot)tot ,cgstp,sgstp,igstp from jcardparts where invno='$jrow[3]' and brcode='$brcode' and (cgstp+sgstp+igstp)=0 group by invno,cgstp";
//print $sql.'<br>';
  $invre=mysqli_query($db,$sql) or die('Err:'.mysqli_error($db));
  if(mysqli_num_rows($invre)>0)
  {
       $servflg2=0;
      $bta5=0;
$gsta5=0;
$bta12=0;
$gsta12=0;
$bta18=0;
$gsta18=0;
$bta28=0;
$gsta28=0;
    while($row=mysqli_fetch_array($invre))
    {
        
        if(($row['cgstp']+$row['sgstp']+$row['igstp'])==0.00)
	 {
	     $bta5=$row['aprice'];
	  $gsta5= $row['cgstamt']+$row['sgstamt'];  
	  $gtota5+=$bta5;
    $ttxgst5+=$gsta5;
    
    $invtot+=$row['tot'];
     $ginvtot+=$row['tot'];
    
	 }
	 
	 else
	 {
	     
	 }
	 
    }

   $t1+=$btgstamtp;
   $t2+=$invrow[1];
   $t3+=$invrow[2];
   $t8+=$invrow[3];
    $t9+=$invrow[3];
	if($servflg==0)
	{
	    
	
	   $sql11="select sum(servamt)sparice,sum(cgstamt)cgsttax,sum(sgstamt)sgsttax,sum(igstamt)igsttax,cgstp,sgstp,igstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and (cgstp+sgstp+igstp)=0 group by invno,cgstp";

$jres2=mysqli_query($db,$sql11)or die('Err'.mysqli_error($db));
$jrow2=mysqli_fetch_array($jres2);
$btgstamts=$jrow2[0];
   //print "<td align=right>".putComa($btgstamts)."</td>";
   
  //print "<td align=right>".putComa($jrow2[cgsttax]+$jrow2[sgsttax])."</td>";
   //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]);
  
  $jcservamt=$btgstamts;
  $jcservtax=$jrow2['cgsttax']+$jrow2['sgsttax']+$jrow2['igsttax'];
  $invtot+=$jrow2['tt'];
  $tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); 
   $gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow2['tt'];
  // print "<td align=right>".putComa($invtot)."</td>";
  // printf("<td align=right>%.2f</td>",$tdiff);
   //print "<td align=right>".putComa($tgtot)."</td>";
   //print "<td>".$jrow[4]."</td>";
  
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow2[1]+$jrow2[2];
   $t7+=$jrow2[2];
   $t8+=$jrow2[4];
   $t9+=$jrow2[4];
   $tdiff+=$dif;
   $tgtot+=round($t9);
   $servflg=1;
	
   }
   
   
  
   
  } 
  
  // end if numrow
  
  
 else
{
$servflg2=1;

     $sql444="select sum(servamt)sparice,sum(cgstamt)cgsttax,sum(sgstamt)sgsttax,sum(igstamt)igsttax,cgstp,sgstp,igstp,sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and (cgstp+sgstp+igstp)=0 group by invno,cgstp";

$jres21=mysqli_query($db,$sql444)or die('Err'.mysqli_error($db));
$jrow24=mysqli_fetch_array($jres21);
   //print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".datetohtml($jrow[entrydate])."</td><td>$jrow[cname]</td><td>$jrow[jcno]</td>";

$btgstamts=$jrow24[0];
  // print "<td align=right>".putComa($btgstamts)."</td>";
   // print "<td align=right>".putComa($jrow24[cgsttax]+$jrow24[sgsttax])."</td>";
  $jcservamt=$btgstamts;
  $jcservtax=$jrow24['cgsttax']+$jrow24['sgsttax']+$jrow24['igsttax'];
  
 $invtot+=$jrow24['tt'];
$tdiff=round($invtot)-$invtot;
   $tgtot+=round($invtot); //+$invrow[3]);
$gtotserv+=$jcservamt;
    $gtotsertax+=$jcservtax;
    $ginvtot+=$jrow24['tt'];
    
//   print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
   //$rnd=round($jrow24[4]);
  // $dif=$rnd-($jrow24[4]);
  //print "<td align=right>".putComa($invtot)."</td>";
   //printf("<td align=right>%.2f</td>",$tdiff);
  // print "<td align=right>".putComa($tgtot)."</td>";
  /*   $t1+=$invrow[0];
   $t2+=$invrow[1];
   $t3+=$invrow[2];*/
   //$t4+=$invrow[3];

   $t5+=$btgstamts;
   $t6+=$jrow24[1]+$jrow24[2];
   $t7+=$jrow24[2];
   $t8+=$jrow24[4];
   //$t9+=($jrow2[4]); //+$invrow[3]);
   
} // end else

 if($invtot>0)
  {
   print "<tr bgcolor='white'><td>$invno</td><td>".datetohtml($entrydate)."</td><td>$cname</td><td>$jcno</td>";
   if($servflg2==1)
   {
   // print "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>";   
   }
    print "<td align=right>".putComa($bta5)."</td><td align=right>".putComa($gsta5)."</td>";
    
   print "<td align=right>".putComa($jcservamt)."</td>";
   
  print "<td align=right>".putComa($jcservtax)."</td>";
  
   print "<td align=right>".putComa($invtot)."</td>";
  printf("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right>".putComa(round($invtot))."</td>";
   
  }

}// end if jrow
  // print "<tr><td colspan=4 align=right><b>Total1</td>";
   print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtota5)."</td>";
print "<td align=right>".putComa($ttxgst5)."</td>";
   print "<td align=right>".putComa($gtotserv)."</td>";
print "<td align=right>".putComa($gtotsertax)."</td>";
print "<td align=right>".putComa($ginvtot)."</td>";
$ttround=round($ginvtot)-$ginvtot;
   printf ("<td align=right>%.2f</td>",$ttround);
   print "<td align=right>".putComa(round($ginvtot))."</td>";
  

print "</table>";




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>


