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

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

<?
$grand_gtot1=0;
$grand_ttx=0;
$grand_tcess=0;
$grand_ttot=0;
$grand_ttrnd=0;
$grand_rndtot=0;
$invre=mysqli_query($db,"select distinct invtype from spareinvoice where brcode='$brcode' and vatorcst='INTRASTATE'");
$i=1;
while($invrow=mysqli_fetch_array($invre))
{
	$gtot1=0;
    $ttx=0;
    $tcess=0;
    $ttot=0;
    $ttcstot=0;
    $ttrnd=0;
    $re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno,ref,sum(igstamt)igstamt,sum(cessamt)kfc,sum(tcsamt)tcstot,tin from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  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>No<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>KFC Amt<th>Total <th>TCS Amt<th>Rounded<th>G Total<th>Ref<th>Refered By";
    $mbef_tax = 0;
    $mcgst_amt = 0;
    $msgst_amt = 0;
    $migst_amt = 0;
	while($row=mysqli_fetch_array($re))
    {
        
        $cus_result=mysqli_query($db,"select gst from customer where acname = '$row[customer]'")or die  (mysqli_error($db));
		$cus_row=mysqli_fetch_row($cus_result);
		$mtin=$cus_row[0];
	    if($brcode == 'ERA153')
        {
	        //$minv= 'ZFEMCC';
        }
        else if($brcode == 'MAN140')
        {
	        //$minv= 'ZFMDCC';
        }
        else if($brcode == 'CAL556')
        {
	        //$minv= 'ZFCTCC';
        }
        else
        {
	        $minv= '['.substr($brcode,0,3).']';
	        
        }
        if(strlen($row['tin'])<=0)
            $mtin = 'NOT REGISTERED';
	    $invno=$minv.$row['invno'];
        print "\n<tr bgcolor=white><td>$i</td><td>$invno</td><td>".implode('-', array_reverse(explode('-', $row['invdate'])));
        print "<td>".$mtin."</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['kfc'])."</td>";
        print "<td align=right>".putComa($row['tot'])."</td>";
        print "<td align=right>".putComa($row['tcstot'])."</td>";
        $rnd=round($row['tot']+$row['tcstot'])-($row['tot']+$row['tcstot']);
        $rndtot+=$rnd;
        printf("<td align=right>%.2f</td>",$rnd);
        print "<td align=right>".putComa(round($row['tot']+$row['tcstot']))."</td>";
        print "<td>".$row['ref']."</td>";
        print "<td width=50px>".$row['msno']."</td>";
        $mbef_tax += $row['aprice'];
        $mcgst_amt += $row['cgstamt'];
        $msgst_amt += $row['sgstamt'];
        $migst_amt += $row['igstamt'];
        $mkfc_amt +=$row['kfc'];
        $ttrnd+=round($row['tot']);
        $gtot1+=$row['aprice'];
        $ttx+=$row['txamt'];
        $tcess+=$row['cess'];
        $ttot+=$row['tot'];
        $ttcstot+=$row['tcstot'];
        $i++;
    }
    print "<tr><td colspan=4><center><b>Total</td><td align=right></td><td align=right>".putComa($mbef_tax)."</td>";
    print "<td align=right>".putComa($mcgst_amt)."</td>";
    print "<td align=right>".putComa($msgst_amt)."</td>";
    print "<td align=right>".putComa($migst_amt)."</td>";
    print "<td align=right>".putComa($mkfc_amt)."</td>";
    print "<td align=right>".putComa($ttot)."</td>";
    print "<td align=right>".putComa($ttcstot)."</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 "</table><br>";
 

//*********** jcard SPARE
print "<h3>JOB CARD</h3>";
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print " <th>No<th>Inv No <th>Invoice Date<th>GST No";
print "<th>Customer<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>KFC Amt<th>Total <th>Rounded <th>G Total<th>Reffered By<th>MSNO<th>";
$sq="select jcno,cname,entrydate,invno,engineer,msno,dcomm,model 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));

$i=1;

while($jrow=mysqli_fetch_array($jres))
{



  $sql="select sum(amt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(gtot)tt,invno,sum(igstamt)igstamt,sum(cessamt)kfc 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))
    {

        $cus_result=mysqli_query($db,"select gst from customer where cname = '$jrow[cname]'")or die  (mysqli_error($db));
		$cus_row=mysqli_fetch_array($cus_result);
        if($brcode == 'MAN140')
        {
            //$minv = 'ZFMDJC';
        }
        else if($brcode == 'ERA153')
        {
            //$minv = 'ZFEMJC';
        }
        else if($brcode == 'CAL556')
        {   
            //$minv = 'ZFCTJC';
        }
        else
        {
	        $minv =  '['.substr($brcode,0,3).']';
        }
        $invjc=$minv.$invrow['invno'];
        print "<tr bgcolor='white'><td>$i</td><td>$invjc</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$cus_row[0]</td><td>$jrow[cname]</td><td>$jrow[jcno]</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[5]</td>";
        $t22=$t22+$invrow[5];
        $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(cgstamt)cgstamt,sum(servamt*hredutx/100),sum(tot)tt,sum(igstamt)igstamt,sum(hredutx)kfcl 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);
        $kfc=$invrow['kfc']+$jrow2['kfcl'];
        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[5]);
        printf("<td align=right>%.2f</td>",$kfc);
        //printf("<td align=right>%.2f</td>",$jrow2[3]);
        //print "<td align=right>$invrow[2]</td><td align=right>$invrow[3]</td>";
            $t8+=$jrow2[5];
        $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>";
         print "<td>".$jrow['dcomm']."</td>";
                 
        $t1+=$invrow[0];
        $t2+=$invrow[1];
        $t3+=$invrow[2];
        //$t4+=$invrow[3];
$t10+=$kfc;
        $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]);
   $i++;
   }
  } // end if numrow
 else
{
                $cus_result=mysqli_query($db,"select gst from customer where cname = '$jrow[cname]'")or die  (mysqli_error($db));
		$cus_row=mysqli_fetch_array($cus_result);

    $sql="select sum(servamt)sparice,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(hredutx),sum(tot)tt,invno,sum(igstamt)igstamt,sum(hredutx)kfcl 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);
    
          if($brcode == 'MAN140')
        {
            //$minv = 'ZFMDJC';
        }
        else if($brcode == 'ERA153')
        {
            //$minv = 'ZFEMJC';
        }
        else if($brcode == 'CAL556')
        {   
            //$minv = 'ZFCTJC';
        }
        else
        {
	        $minv =  '['.substr($brcode,0,3).']';
        }
    $invjc=$minv.$jrow2['invno'];
    print "<tr bgcolor='white'><td>$i</td><td>$invjc</td><td>".implode('-', array_reverse(explode('-', $jrow['entrydate'])))."</td><td>$cus_row[0]</td><td>$jrow[cname]</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[6]);
    printf("<td align=right>%.2f</td>",$jrow2['kfcl']);
    //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>";
    print "<td>".$jrow[4]."</td>";
         print "<td>".$jrow['dcomm']."</td>";
         print "<td>".$jrow['model']."</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];
    $t8+=$jrow2[6];
    $t9+=($jrow2[4]); //+$invrow[3]);
    $t10+=$jrow2['kfcl'];
    $tdiff+=$dif;
    $tgtot+=round($jrow2[4]); //+$invrow[3]);
$i++;
} // end else

}// end if jrow
   print "<tr><td colspan=6 align=center><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($t22)."</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($t10)."</td>";
   print "<td align=right>".putComa($t9)."</td>";
   printf ("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right>".putComa($tgtot)."</td><td></td>";
  

print "</table>";
$garandtotal=$t1+$mbef_tax;
$garandtota=$tgtot+$ttrnd;
$garandtotal=$t1+$mbef_tax;
$garandtotal=$t1+$mbef_tax;
print "<table border=1 bgcolor='Orange'>";

 print "<tr><b><td colspan=6 align=center><b>Total</td><td align=right><b>".putComa($garandtotal)."</td>";
   print "<b><td align=right>".putComa($tttt)."</td>";
   print "<b><td align=right>".putComa($tttt)."</td><td align=right>".putComa($tttt)."</td><td align=right>".putComa($tttt)."</td>";
   printf ("<td align=right>%.2f</td>",$tttt);
   printf ("<td align=right>%.2f</td>",$tttt);
   printf ("<td align=right>%.2f</td>",$tttt);
   print "<b><td align=right><b>".putComa($t5)."</td>";
   print "<td align=right>".putComa($tttt)."</td>";
   printf ("<td align=right>%.2f</td>",$tdiff);
   print "<td align=right><b>".putComa($garandtota)."</td><td></td></tr>";
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<th>Reffered By";
$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,msno 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<th>ref";
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>";
 print "<td>".$row[msno]."</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>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>


