<?
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>HSN CODE SUMMARY REPORT</H4>";

print " from ".implode('-', array_reverse(explode('-', $dfrom))).' to '.implode('-', array_reverse(explode('-', $dto)));
?>
</td></tr>
</table>
<?
$gtot1=0;
$ttx=0;
$tcess=0;
$ttot=0;
$ttrnd=0;
$tqty=0;
$tbl="hsnsummary".$brcode;
mysqli_query($db,"drop table if exists $tbl");
mysqli_query($db,"CREATE TABLE $tbl (hsncode varchar(20),qty double(10,2) default 0,aprice double(10,2) default 0,igstamt double(10,2) default 0,cgstamt double(10,2) default 0,sgstamt double(10,2) default 0,cessamt double(10,2),item_type varchar(40))");
mysqli_query($db,"delete from $tbl");
mysqli_query($db,"insert into $tbl (qty,aprice,igstamt,cgstamt,sgstamt,cessamt,item_type,hsncode) select count(invno)qty ,sum(amt2)aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(tcsamt)cessamt,'Commercial Vehicles',hsncode from veh_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  group by hsncode");

mysqli_query($db,"insert into $tbl (qty,aprice,igstamt,cgstamt,sgstamt,cessamt,item_type,hsncode) select count(refno)qty ,sum(before_tax)aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(tcsamt)cessamt,'Commercial Vehicles(GST Inc)',hsncode from gst_income where brcode='$brcode' and date>='$dfrom' and date<='$dto'  group by hsncode");


$re=mysqli_query($db,"select hsncode,sum(qty) qty,sum(aprice)aprice,sum(igstamt)igstamt,sum(cgstamt)cgstamt,sum(sgstamt)sgstamt,sum(cessamt)cess,item_type from $tbl group by hsncode")or die('err'.mysqli_error());
print "<table border=0 cellspacing=1 cellpadding=4 bgcolor='lightsteelblue'>";
print "<tr><th>HSN Code No <th>Description<th>UQC<th>Total Quantity<th>Total Value<th>Taxable Value<th>Integrated Tax Amount<th>Central Tax Amount<th>State/UT Tax Amount<th>TCS Amount";
while($row=mysqli_fetch_array($re))
{
    print "\n<tr bgcolor=white><td>$row[hsncode]</td><td>$row[item_type]</td><td>Nos</td>";
    print "<td align=right>".$row['qty']."</td>";
//    print "<td align=right>".($row[aprice]+$row[igstamt]+$row[cgstamt]+$row[sgstamt]+$row[cess])."</td>";
    print "<td align=right>".($row['aprice']+$row['cgstamt']+$row['sgstamt']+$row['igstamt'])."</td>";
    print "<td align=right>".$row['aprice']."</td>";
    print "<td align=right>".putComa($row['igstamt'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td>";
    print "<td align=right>".putComa($row['cess'])."</td>";
    $tqty+=round($row['qty']);
    $gnet += ($row['aprice']+$row['cgstamt']+$row['sgstamt']+$row['igstamt']);
    $gtot1+=$row['aprice'];
    $tcess+=$row['cess'];
    $grand_cgsttx+=$row['cgstamt'];
    $grand_sgsttx+=$row['sgstamt'];
    $grand_igsttx+=$row['igstamt'];
}

$re=mysqli_query($db,"select * from crnote_gst where brcode='$brcode' and date>='$dfrom' and date<='$dto' and jvno > 0 ");
while($row=mysqli_fetch_array($re))
{
    $invno = $row['sale_invoice'];
    $invres=mysqli_query($db,"select hsncode from veh_invoice where brcode='$brcode' and invno = $invno");
    $invrow=mysqli_fetch_array($invres);
    $hsncode=$invrow[0];
    $mbef_tax = $row['amt2'];
    if($row['mcode']=='INTRASTATE')
    {
        $igstamt=0;
        $cgstamt=$row['tax']/2;
        $sgstamt=$row['tax']/2;
    }
    else
    {
        $cgstamt=0;
        $sgstamt=0;
        $igstamt=$row['tax'];
    }
    $tcsamt=0;
    mysqli_query($db,"insert into $tbl (qty,aprice,igstamt,cgstamt,sgstamt,cessamt,item_type,hsncode) values(1,$mbef_tax,$igstamt,$cgstamt,$sgstamt,$tcsamt,'CR Note','$hsncode')");
}
$re=mysqli_query($db,"select hsncode,sum(qty) qty,sum(aprice*-1)aprice,sum(igstamt*-1)igstamt,sum(cgstamt*-1)cgstamt,sum(sgstamt*-1)sgstamt,sum(cessamt*-1)cess,item_type from $tbl where item_type='CR Note' group by hsncode")or die('err'.mysqli_error());
while($row=mysqli_fetch_array($re))
{
    print "\n<tr bgcolor=white><td>$row[hsncode]</td><td>$row[item_type]</td><td>Nos</td>";
    print "<td align=right>".$row['qty']."</td>";
//    print "<td align=right>".($row[aprice]+$row[igstamt]+$row[cgstamt]+$row[sgstamt]+$row[cess])."</td>";
    print "<td align=right>".($row['aprice']+$row['cgstamt']+$row['sgstamt']+$row['igstamt'])."</td>";
    print "<td align=right>".$row['aprice']."</td>";
    print "<td align=right>".putComa($row['igstamt'])."</td><td align=right>".putComa($row['cgstamt'])."</td><td align=right>".putComa($row['sgstamt'])."</td>";
    print "<td align=right>".putComa($row['cess'])."</td>";
    $tqty+=round($row['qty']);
    $gnet += ($row['aprice']+$row['cgstamt']+$row['sgstamt']+$row['igstamt']);
    $gtot1+=$row['aprice'];
    $tcess+=$row['cess'];
    $grand_cgsttx+=$row['cgstamt'];
    $grand_sgsttx+=$row['sgstamt'];
    $grand_igsttx+=$row['igstamt'];
}


print "<tr><td colspan=2><center><b>Total</td><td align=right></td><td align=right>".putComa($tqty)."</td>";
print "<td align=right>".putComa($gnet)."</td>";
print "<td align=right>".putComa($gtot1)."</td>";
print "<td align=right>".putComa($grand_igsttx)."</td>";
print "<td align=right>".putComa($grand_cgsttx)."</td>";
print "<td align=right>".putComa($grand_sgsttx)."</td>";
print "<td align=right>".putComa($tcess)."</td>";



$grand_gtot1+=$gtot1;
$grand_ttx+=$ttx;
$grand_tcess+=$tcess;
$grand_ttot+=$ttot;
$grand_rndtot+=$rndtot;
$grand_ttrnd+=$ttrnd;
print "</table>";
return;
}
?>

<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>


