<?
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 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 - VAT</H4>";

print " from ".implode('-', array_reverse(explode('-', $dfrom))).' to '.implode('-', array_reverse(explode('-', $dto)));
?>
</td></tr>
</table>

<?
$grand_gtot1=0;
$grand_ttx=0;
$grand_cgsttx=0;
$grand_sgsttx=0;
$grand_igsttx=0;
$grand_tcess=0;
$grand_ttot=0;
$grand_ttrnd=0;
$grand_rndtot=0;
$invre=mysqli_query($db,"select distinct cgstp from spareinvoice where brcode='$brcode' and cgstp > 0 and invdate>='$dfrom' and invdate<='$dto'");
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    //if($ordby=='Invoice')
        $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno from spareinvoice  where brcode='$brcode' and cgstp='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invno,cgstp")or die('err'.mysqli_error($db));
//    else
  //      $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,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));    
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    $mtax = $invrow[0]+ $invrow[0];
    print "<th colspan=10><h3>Invoice Type CGST $mtax%</th></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Before Tax <th>CGST Amt <th>SGST Amt <th>IGST Amt <th>Cess<th>Total <th>Rounded<th>G Total<th>Ref";
    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];
        print "\n<tr bgcolor=white><td>$row[0]</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$mtin."</td>";
        print "<td>".strtoupper($row['customer'])."</td><td></td><td></td>";
        print "<td align=right>".putComa($row['aprice'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td><td align=right>".putComa($row['igstamt'])."</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>";
        print "<td>".$row['msno']."</td>";
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $grand_cgsttx+=$row['cgstamt'];
        $grand_sgsttx+=$row['sgstamt'];
        $grand_igsttx+=$row['igstamt'];
       
           }
    print "<tr><td colspan=5><center><b>Total</td><td align=right></td><td align=right>".putComa($gtot1)."</td>";
    print "<td align=right>".putComa($grand_cgsttx)."</td>";
    print "<td align=right>".putComa($grand_sgsttx)."</td>";
    print "<td align=right>".putComa($grand_igsttx)."</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></tr><br>";
    $grand_gtot1+=$gtot1;
    $grand_ttx+=$ttx;
    $grand_tcess+=$tcess;
    $grand_ttot+=$ttot;
    $grand_rndtot+=$rndtot;
    $grand_ttrnd+=$ttrnd;
}
print "<tr><td colspan=3><center><b>Grand Total</td><td align=right></td><td align=right>".$grand_gtot1."</td>";
print "<td align=right>".$grand_ttx."</td>";
print "<td align=right>".$grand_tcess."</td>";
print "<td align=right>".$grand_ttot."</td>";
printf("<td align=right>%.2f</td>",$grand_rndtot);
print "<td align=right>".$grand_ttrnd."</td></tr>";
print "</table><br>";
 
//Other 
$invre=mysqli_query($db,"select distinct cgstp from spareinvoice_oth where brcode='$brcode' and cgstp > 0 and invdate>='$dfrom' and invdate<='$dto'");
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    //if($ordby=='Invoice')
        $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin from spareinvoice_oth  where brcode='$brcode' and cgstp='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invno,cgstp")or die('err'.mysqli_error($db));
//    else
  //      $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin 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));    
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    $mtax = $invrow[0]+ $invrow[0];
    print "<th colspan=10><h3>Invoice Type CGST $mtax%</th></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Before Tax <th>CGST Amt <th>SGST Amt <th>IGST Amt <th>Cess<th>Total <th>Rounded<th>G Total<th>Ref";
    while($row=mysqli_fetch_array($re))
    {
        $cus_result=mysqli_query($db,"select kgst from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
	    $cus_row=mysqli_fetch_row($cus_result);
	    $mtin=$cus_row[0];
        print "\n<tr bgcolor=white><td>$row[0]</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$mtin."</td>";
        print "<td>".strtoupper($row['customer'])."</td><td></td><td></td>";
        print "<td align=right>".putComa($row['aprice'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td><td align=right>".putComa($row['igstamt'])."</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>";
        print "<td>".$row['msno']."</td>";
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $grand_cgsttx+=$row['cgstamt'];
        $grand_sgsttx+=$row['sgstamt'];
        $grand_igsttx+=$row['igstamt'];
        
    }
    print "<tr><td colspan=5><center><b>Total</td><td align=right></td><td align=right>".putComa($gtot1)."</td>";
    print "<td align=right>".putComa($grand_cgsttx)."</td>";
    print "<td align=right>".putComa($grand_sgsttx)."</td>";
    print "<td align=right>".putComa($grand_igsttx)."</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></tr><br>";
    $grand_gtot1+=$gtot1;
    $grand_ttx+=$ttx;
    $grand_tcess+=$tcess;
    $grand_ttot+=$ttot;
    $grand_rndtot+=$rndtot;
    $grand_ttrnd+=$ttrnd;
}
print "<tr><td colspan=3><center><b>Grand Total</td><td align=right></td><td align=right>".$grand_gtot1."</td>";
print "<td align=right>".$grand_ttx."</td>";
print "<td align=right>".$grand_tcess."</td>";
print "<td align=right>".$grand_ttot."</td>";
printf("<td align=right>%.2f</td>",$grand_rndtot);
print "<td align=right>".$grand_ttrnd."</td></tr>";
print "</table><br>";
 
//Others
$invre=mysqli_query($db,"select distinct igstp from spareinvoice where brcode='$brcode' and igstp > 0 and invdate>='$dfrom' and invdate<='$dto'");
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    //if($ordby=='Invoice')
        $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin from spareinvoice  where brcode='$brcode' and igstp='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invno,igstp")or die('err'.mysqli_error($db));
//    else
  //      $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin 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));    
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    $mtax = $invrow[0];
    print "<th colspan=10><h3>Invoice Type IGST $mtax%</th></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Before Tax <th>CGST Amt <th>SGST Amt <th>IGST Amt <th>Cess<th>Total <th>Rounded<th>G Total<th>Ref";
    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];
        print "\n<tr bgcolor=white><td>$row[0]</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$row['gstin']."</td>";
        print "<td>".strtoupper($row['customer'])."</td>";
        print "<td align=right>".putComa($row['aprice'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td><td align=right>".putComa($row['igstamt'])."</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>";
        print "<td>".$row['msno']."</td>";
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $grand_cgsttx+=$row['cgstamt'];
        $grand_sgsttx+=$row['sgstamt'];
        $grand_igsttx+=$row['igstamt'];
    }
    print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtot1)."</td>";
    print "<td align=right>".putComa($grand_cgsttx)."</td>";
    print "<td align=right>".putComa($grand_sgsttx)."</td>";
    print "<td align=right>".putComa($grand_igsttx)."</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></tr><br>";
    $grand_gtot1+=$gtot1;
    $grand_ttx+=$ttx;
    $grand_tcess+=$tcess;
    $grand_ttot+=$ttot;
    $grand_rndtot+=$rndtot;
    $grand_ttrnd+=$ttrnd;
}
print "<tr><td colspan=3><center><b>Grand Total</td><td align=right></td><td align=right>".$grand_gtot1."</td>";
print "<td align=right>".$grand_ttx."</td>";
print "<td align=right>".$grand_tcess."</td>";
print "<td align=right>".$grand_ttot."</td>";
printf("<td align=right>%.2f</td>",$grand_rndtot);
print "<td align=right>".$grand_ttrnd."</td></tr>";
print "</table><br>"; 

$invre=mysqli_query($db,"select distinct igstp from spareinvoice_oth where brcode='$brcode' and igstp > 0 and invdate>='$dfrom' and invdate<='$dto'");
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    //if($ordby=='Invoice')
        $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin from spareinvoice_oth  where brcode='$brcode' and igstp='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invno,igstp")or die('err'.mysqli_error($db));
//    else
  //      $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin 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));    
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    $mtax = $invrow[0];
    print "<th colspan=10><h3>Invoice Type IGST $mtax%</th></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Before Tax <th>CGST Amt <th>SGST Amt <th>IGST Amt <th>Cess<th>Total <th>Rounded<th>G Total<th>Ref";
    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];
        print "\n<tr bgcolor=white><td>$row[0]</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$row['gstin']."</td>";
        print "<td>".strtoupper($row['customer'])."</td>";
        print "<td align=right>".putComa($row['aprice'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td><td align=right>".putComa($row['igstamt'])."</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>";
        print "<td>".$row['msno']."</td>";
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $grand_cgsttx+=$row['cgstamt'];
        $grand_sgsttx+=$row['sgstamt'];
        $grand_igsttx+=$row['igstamt'];
    }
    print "<tr><td colspan=3><center><b>Total</td><td align=right></td><td align=right>".putComa($gtot1)."</td>";
    print "<td align=right>".putComa($grand_cgsttx)."</td>";
    print "<td align=right>".putComa($grand_sgsttx)."</td>";
    print "<td align=right>".putComa($grand_igsttx)."</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></tr><br>";
    $grand_gtot1+=$gtot1;
    $grand_ttx+=$ttx;
    $grand_tcess+=$tcess;
    $grand_ttot+=$ttot;
    $grand_rndtot+=$rndtot;
    $grand_ttrnd+=$ttrnd;
}
print "<tr><td colspan=3><center><b>Grand Total</td><td align=right></td><td align=right>".$grand_gtot1."</td>";
print "<td align=right>".$grand_ttx."</td>";
print "<td align=right>".$grand_tcess."</td>";
print "<td align=right>".$grand_ttot."</td>";
printf("<td align=right>%.2f</td>",$grand_rndtot);
print "<td align=right>".$grand_ttrnd."</td></tr>";
print "</table><br>"; 

//*********** jcard SPARE

$sq="select distinct cgstp from jcardparts where date>='$dfrom' and date<='$dto' and cgstp > 0 and brcode='$brcode'";
$jres11=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow11=mysqli_fetch_array($jres11))
{
    $tax = $jrow11[0]+$jrow11[0];
print "<h3>JOB CARD : CGST $tax%</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>Inv No <th>Invoice Date ";
print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Jcno<th>Before Tax <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Serv Amt <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Total <th>Rounded <th>G Total<th>Reffered By";
$sq="select jcno,cname,entrydate,invno,engineer,gstin,ship_to,ship_togst from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  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,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt)igstamt from jcardparts where invno='$jrow[3]' and brcode='$brcode' and cgstp = '$jrow11[0]' group by invno";
    //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))
        {
            if ($jrow['entrydate']<='2015-11-15')
	            $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' group by invno";
	        else
	            $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and cgstp = '$jrow11[0]' group by invno";
            $jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
            $jrow2=mysqli_fetch_array($jres2);
            if($jrow2[0]>0||$invrow[0]>0)
            {
                print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$jrow[gstin]</td><td>$jrow[cname]</td><td>$jrow[ship_to]</td><td>$jrow[ship_togst]</td><td>$jrow[jcno]</td>";
                print "<td align=right>".putComa($invrow[0])."</td><td align=right>$invrow[4]</td><td align=right>$invrow[5]</td><td align=right>$invrow[6]</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]);
                $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>";
                print "<td>".$jrow[4]."</td>";
            }
            $t1+=$invrow[0];
            $t2+=$invrow[1];
            $t3+=$invrow[2];
            $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(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and cgstp = '$jrow11[0]' group by invno";
        $jres21=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
        $jrow2=mysqli_fetch_array($jres21);
        if($jrow2[0]>0)
        {
            print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$jrow[gstin]</td><td>$jrow[cname]</td><td>$jrow[ship_to]</td><td>$jrow[ship_togst]</td><td>$jrow[jcno]</td>";
            print "<td></td><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]);
            $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>";
            print "<td>".$jrow[4]."</td>";
        }
        $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 "</table>";
}

$sq="select distinct igstp from jcardparts where date>='$dfrom' and date<='$dto' and igstp > 0 and brcode='$brcode'";
$jres11=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow11=mysqli_fetch_array($jres11))
{
    $tax = $jrow11[0];
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    print "<tr><td colspan=4><h3>JOB CARD : IGST $tax%</h3><td></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Jcno<th>Before Tax <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Serv Amt <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Total <th>Rounded <th>G Total<th>Reffered By";
    $sq="select jcno,cname,entrydate,invno,engineer,gstin,ship_to,ship_togst from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  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,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt)igstamt from jcardparts where invno='$jrow[3]' and brcode='$brcode' and igstp = '$jrow11[0]' group by invno";
            //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))
           {
                  if ($jrow['entrydate']<='2015-11-15')
                      $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' group by invno";
                  else
                      $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and igstp = '$jrow11[0]' group by invno";
                  $jres2=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
                  $jrow2=mysqli_fetch_array($jres2);
                  if($jrow2[0]>0||$invrow[0]>0)
                  {
                      print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$jrow[gstin]</td><td>$jrow[cname]</td><td>$jrow[ship_to]</td><td>$jrow[ship_togst]</td><td>$jrow[jcno]</td>";
                      print "<td align=right>".putComa($invrow[0])."</td><td align=right>$invrow[4]</td><td align=right>$invrow[5]</td><td align=right>$invrow[6]</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]);
                      $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>";
                      print "<td>".$jrow[4]."</td>";
                  }
                  $t1+=$invrow[0];
                  $t2+=$invrow[1];
                  $t3+=$invrow[2];
                  $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(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and igstp = '$jrow11[0]'  group by invno";
            $jres21=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
            $jrow2=mysqli_fetch_array($jres21);
            if($jrow2[0]>0)
            {
            print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$jrow[gstin]</td><td>$jrow[cname]</td><td>$jrow[ship_to]</td><td>$jrow[ship_togst]</td><td>$jrow[jcno]</td>";
            print "<td></td><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]);
            $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>";
            print "<td>".$jrow[4]."</td>";
            }
            $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 "</table>";

    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    print "<tr><td colspan=4><h3>JOB CARD : IGST 18%</h3><td></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Jcno<th>Before Tax <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Serv Amt <th>CGST Amt<th>SGST Amt <th>IGST Amt <th>Total <th>Rounded <th>G Total<th>Reffered By";
    $sq="select jcno,cname,entrydate,invno,engineer,gstin,ship_to,ship_togst from jobcard where entrydate>='$dfrom' and entrydate<='$dto'  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(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(igstamt),sum(tot)tt from jcardserv where invno='$jrow[3]' and brcode='$brcode' and igstp = '18'  group by invno";
            $jres21=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
            $jrow2=mysqli_fetch_array($jres21);
            if($jrow2[0]>0)
            {
            print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$jrow[gstin]</td><td>$jrow[cname]</td><td>$jrow[ship_to]</td><td>$jrow[ship_togst]</td><td>$jrow[jcno]</td>";
            print "<td></td><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]);
            $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>";
            print "<td>".$jrow[4]."</td>";
            }
            $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 jrow
print "</table>";

print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<tr><td colspan=4><h3>JOB CARD PARTS : 0%</h3><td></tr>";
print " <th>Inv No <th>Invoice Date <th>Jcno<th>Before Tax ";
$sq="select invno,jcno,sum(amt),date from jcardparts where date>='$dfrom' and date<='$dto' and brcode='$brcode' and cgstp+sgstp+igstp = 0 and amt > 0 group by invno";
$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow['date'])))."</td><td>$jrow[jcno]</td>";
    print "<td align=right>".putComa($jrow[2])."</td></tr>";
    $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 jrow
print "</table>";

print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<tr><td colspan=4><h3>JOB CARD LABOUR : 0%</h3><td></tr>";
print " <th>Inv No <th>Invoice Date <th>Jcno<th>Before Tax ";
$sq="select invno,jcno,sum(servamt) from jcardserv where brcode = '$brcode'  and cgstp+sgstp+igstp = 0 and servamt > 0 group by invno";
$jres=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
while($jrow=mysqli_fetch_array($jres))
{
    $sql="select entrydate from jobcard where invno='$jrow[0]' and entrydate>='$dfrom' and entrydate<='$dto' and brcode='$brcode'";
    $jres21=mysqli_query($db,$sql)or die('Err'.mysqli_error($db));
    $jrow2=mysqli_fetch_array($jres21);
    if($jrow2[0]>'')
    {
        print "<tr bgcolor='white'><td>$jrow[invno]</td><td>".implode('-', array_reverse(explode('-', $jrow2[0])))."</td><td>$jrow[jcno]</td>";
        print "<td align=right>".putComa($jrow[2])."</td></tr>";
    }
    $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 jrow
print "</table>";

//Lab
$invre=mysqli_query($db,"select distinct cgstp from spareinvoice_lab where brcode='$brcode' and cgstp > 0 and invdate>='$dfrom' and invdate<='$dto'");
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttrnd=0;
    //if($ordby=='Invoice')
        $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin from spareinvoice_lab  where brcode='$brcode' and cgstp='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invno,cgstp")or die('err'.mysqli_error($db));
//    else
  //      $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,gstin 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));    
    print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
    $mtax = $invrow[0]+ $invrow[0];
    print "<th colspan=10><h3>Invoice Type CGST $mtax%</th></tr>";
    print " <th>Inv No <th>Invoice Date ";
    print "<th>GST No.<th>Customer<th>Bill To Party<th>Bill To Party(GSTIN)<th>Before Tax <th>CGST Amt <th>SGST Amt <th>IGST Amt <th>Cess<th>Total <th>Rounded<th>G Total<th>Ref";
    while($row=mysqli_fetch_array($re))
    {
        $cus_result=mysqli_query($db,"select kgst from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
	    $cus_row=mysqli_fetch_row($cus_result);
	    $mtin=$cus_row[0];
        print "\n<tr bgcolor=white><td>$row[0]</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$mtin."</td>";
        print "<td>".strtoupper($row['customer'])."</td><td></td><td></td>";
        print "<td align=right>".putComa($row['aprice'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td><td align=right>".putComa($row['igstamt'])."</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>";
        print "<td>".$row['msno']."</td>";
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $grand_cgsttx+=$row['cgstamt'];
        $grand_sgsttx+=$row['sgstamt'];
        $grand_igsttx+=$row['igstamt'];
        
    }
    print "<tr><td colspan=5><center><b>Total</td><td align=right></td><td align=right>".putComa($gtot1)."</td>";
    print "<td align=right>".putComa($grand_cgsttx)."</td>";
    print "<td align=right>".putComa($grand_sgsttx)."</td>";
    print "<td align=right>".putComa($grand_igsttx)."</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></tr><br>";
    $grand_gtot1+=$gtot1;
    $grand_ttx+=$ttx;
    $grand_tcess+=$tcess;
    $grand_ttot+=$ttot;
    $grand_rndtot+=$rndtot;
    $grand_ttrnd+=$ttrnd;
}
print "<tr><td colspan=3><center><b>Grand Total</td><td align=right></td><td align=right>".$grand_gtot1."</td>";
print "<td align=right>".$grand_ttx."</td>";
print "<td align=right>".$grand_tcess."</td>";
print "<td align=right>".$grand_ttot."</td>";
printf("<td align=right>%.2f</td>",$grand_rndtot);
print "<td align=right>".$grand_ttrnd."</td></tr>";
print "</table><br>";
print "<tr><td colspan=4 align=right><b>Total</td><td align=right>".putComa($t1)."</td>";
print "<td align=right>".putComa($t2/2)."</td><td align=right>".putComa($t2/2)."</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>";
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>Order By</td><td><select name=ordby><option>Invoice</option><option>Datewise</option></select></td></td></tr>
<tr><td colspan=2 align=center bgcolor='lightsteelblue'><input type=submit value='Show' name='show'></td></tr>

</table>
</td></tr>
</table>
</form>


