<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='index.php'; </script>";
 return;
}
include("../opendb.php");
?>
<html>
    <head>
<link rel="stylesheet" href="../css/form.css" type="text/css" />
<link type="text/css" href="../jquery/jquery-ui.min.css" rel="stylesheet" />	
<script type="text/javascript" src="../js/jquery.js"></script>
<script type="text/javascript" src="../cal/jquery-ui1.js"></script>
<script type="text/javascript" src="../jquery/jquery-1.7.min.js"></script>
<script type="text/javascript" src="../jquery/jquery-ui.min.js"></script>
<script type="text/javascript" src="../jquery/jquery.jqEasyCharCounter.min.js"></script>
<script type="text/javascript">

$(function() {

    $("#fr_date").datepicker({
	dateFormat: "yy-mm-dd"
   });
 $("#to_date").datepicker({
	dateFormat: "yy-mm-dd"
   });
 });
  </script>

</head>
<body>
<form method='post'><center><table border=1 class=report style="width: 50px;">
<tr><td align=center colspan=2><h3 >Waranty And Coupon Invoice Report</h3></td></tr>
<tr><td>FROM</td><td>To</td></tr>
<tr><td align='center'><input type='text'  readonly name='frmdt' value='2017-04-01' id='fr_date'></td><td align=center><input type=text readonly name=todt id='to_date' value=<? echo date('Y-m-d')?>></td></tr>
<tr><td colspan=2 align=center ><input type=submit name=btn value=Show></td></tr>

</table>
</form>
<?
$btn=$_REQUEST['btn'];
$frmdt=$_REQUEST['frmdt'];
$todt=$_REQUEST['todt'];
if($btn)
{
?>
<center>
    
<table border=1 class=report width=60%>
<tr><td align=center colspan=13><h3 >Waranty Invoice Report</h3></td></tr>
<tr><th>No:<th>Claim Number<th>Invoice No<th>Claim Date<th>Customer<th>Waranty Type<th>Spare Value<th>Spare Tax<th>Labor Value<th>Labor Tax<th>Lube Value<th>Lube Tax<th>Total Claim value</tr>

<?
$i=1;
$query=mysqli_query($db,"select distinct(claimno) from warspareinvoice where brcode='$brcode' and invtype='Warranty' and claimno>'0' and invdate>='$frmdt' and invdate<='$todt'");
//echo "select distinct invno,invdate,invtype,disc,clamt from warspareinvoice where brcode='$brcode'";
$mtot_sp_value=0;
$mtot_sp_tax=0;
while($row1=mysqli_fetch_array($query))
{
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Spares' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_s="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    $mtot=0;
    if($row12[4]>0)
    {
        $mtot_sp_value+=$row12[4];
        $mtot_sp_tax+=$row12[5];
        $mtot=$row12[4]+$row12[5];
    }
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Labor Value' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    ///echo "select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Labor Value' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0";
    $nstr_l="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lab_value+=$row12[4];
        $mtot_lab_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    }
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Lubricant' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_o="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lub_value+=$row12[4];
        $mtot_lub_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    }
    $query1=mysqli_query($db,"select invno,invdate,customer,invtype,sum(tot) from warspareinvoice where claimno='$row1[claimno]' and brcode='$brcode'");
    $row12=mysqli_fetch_array($query1);
    print "<tr><td>$i</td><td>$row1[0]</td><td>$row12[0]</td><td>$row12[1]</td><td>$row12[2]</td><td>$row12[3]</td>";
    print $nstr_s.$nstr_l.$nstr_o;
    print "<td  align=right>$mtot</td></tr>";
	$i++;
}
$mtot_tot=$mtot_sp_value+$mtot_sp_tax+$mtot_lab_value+$mtot_lab_tax+$mtot_lub_value+$mtot_lub_tax;
print "<tr><td colspan=6 align='center'><b>T O T A L </td><td align='right'>$mtot_sp_value</td><td align='right'>$mtot_sp_tax</td><td align='right'>$mtot_lab_value</td><td align='right'>$mtot_lab_tax</td><td align='right'>$mtot_lub_value</td><td align='right'>$mtot_lub_tax</td><td align='right'>$mtot_tot</td></tr>";
print "</table><center><table border=1 class=report width=60%><tr><td align=center colspan=13><h3>Coupon Invoice Report</h3></td></tr>";
print "<tr><th>No:<th>Claim Number<th>Invoice No<th>Claim Date<th>Customer<th>Waranty Type<th>Spare Value<th>Spare Tax<th>Labor Value<th>Labor Tax<th>Lube Value<th>Lube Tax<th>Total Claim value</tr>";

$i=1;
$query=mysqli_query($db,"select distinct(claimno) from warspareinvoice where brcode='$brcode' and invtype='Coupon'  and claimno>'0' and invdate>='$frmdt' and invdate<='$todt'");
//echo "select distinct invno,invdate,invtype,disc,clamt from warspareinvoice where brcode='$brcode'";
$mtot_sp_value=0;
$mtot_sp_tax=0;
$mtot_lab_value=0;
$mtot_lab_tax=0;
$mtot_lub_value=0;
$mtot_lub_tax=0;
$mtot=0;
while($row1=mysqli_fetch_array($query))
{
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Spares' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_s="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    $mtot=0;
    if($row12[4]>0)
    {
        $mtot_sp_value+=$row12[4];
        $mtot_sp_tax+=$row12[5];
        $mtot=$row12[4]+$row12[5];
    }    
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Labor Value' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_l="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lab_value+=$row12[4];
        $mtot_lab_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    }    
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Lubricant' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_o="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lub_value+=$row12[4];
        $mtot_lub_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    }
    $query1=mysqli_query($db,"select invno,invdate,customer,invtype,sum(tot) from warspareinvoice where claimno='$row1[claimno]' and brcode='$brcode'");
    $row12=mysqli_fetch_array($query1);
    print "<tr><td>$i</td><td>$row1[0]</td><td>$row12[0]</td><td>$row12[1]</td><td>$row12[2]</td><td>$row12[3]</td>";
    print $nstr_s.$nstr_l.$nstr_o;
    print "<td  align=right>$mtot</td></tr>";
	$i++;
}
$mtot_tot=$mtot_sp_value+$mtot_sp_tax+$mtot_lab_value+$mtot_lab_tax+$mtot_lub_value+$mtot_lub_tax;
print "<tr><td colspan=6 align='center'><b>T O T A L </td><td align='right'>$mtot_sp_value</td><td align='right'>$mtot_sp_tax</td><td align='right'>$mtot_lab_value</td><td align='right'>$mtot_lab_tax</td><td align='right'>$mtot_lub_value</td><td align='right'>$mtot_lub_tax</td><td align='right'>$mtot_tot</td></tr>";
print "</table><center><table border=1 class=report width=60%><tr><td align=center colspan=13><h3 >Waranty-Other Invoice Report</h3></td></tr>";
print "<tr><th>No:<th>Claim Number<th>Invoice No<th>Claim Date<th>Customer<th>Waranty Type<th>Spare Value<th>Spare Tax<th>Labor Value<th>Labor Tax<th>Lube Value<th>Lube Tax<th>Total Claim value</tr>";
$i=1;
$mtot_sp_value=0;
$mtot_sp_tax=0;
$mtot_lab_value=0;
$mtot_lab_tax=0;
$mtot_lub_value=0;
$mtot_lub_tax=0;
$mtot=0;
$query=mysqli_query($db,"select distinct(claimno) from warspareinvoice where brcode='$brcode' and (invtype!='Coupon' and invtype!='Warranty') and claimno>'0'");
//echo "select distinct invno,invdate,invtype,disc,clamt from warspareinvoice where brcode='$brcode'";
while($row1=mysqli_fetch_array($query))
{
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Spares' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_s="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    $mtot=0;
    if($row12[4]>0)
    {
        $mtot_sp_value+=$row12[4];
        $mtot_sp_tax+=$row12[5];
        $mtot=$row12[4]+$row12[5];
    } 
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Labor Value' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_l="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lab_value+=$row12[4];
        $mtot_lab_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    } 
    $query1=mysqli_query($db,"select invno,invdate,customer,sum(qty),sum(aprice),sum(taxamt),sum(tot),invtype from warspareinvoice where itemtype='Lubricant' and claimno='$row1[claimno]' and brcode='$brcode' and tot>0");
    $row12=mysqli_fetch_array($query1);
    $nstr_o="<td align=right>$row12[4]</td><td align=right>$row12[5]</td>";
    if($row12[4]>0)
    {
        $mtot_lub_value+=$row12[4];
        $mtot_lub_tax+=$row12[5];
        $mtot+=$row12[4]+$row12[5];
    }
    $query1=mysqli_query($db,"select invno,invdate,customer,invtype,sum(tot) from warspareinvoice where claimno='$row1[claimno]' and brcode='$brcode'");
    $row12=mysqli_fetch_array($query1);
    print "<tr><td>$i</td><td>$row1[0]</td><td>$row12[0]</td><td>$row12[1]</td><td>$row12[2]</td><td>$row12[3]</td>";
    print $nstr_s.$nstr_l.$nstr_o;
    print "<td  align=right>$mtot</td></tr>";
	$i++;
}
$mtot_tot=$mtot_sp_value+$mtot_sp_tax+$mtot_lab_value+$mtot_lab_tax+$mtot_lub_value+$mtot_lub_tax;
print "<tr><td colspan=6 align='center'><b>T O T A L </td><td align='right'>$mtot_sp_value</td><td align='right'>$mtot_sp_tax</td><td align='right'>$mtot_lab_value</td><td align='right'>$mtot_lab_tax</td><td align='right'>$mtot_lub_value</td><td align='right'>$mtot_lub_tax</td><td align='right'>$mtot_tot</td></tr>";
print "</table>";
}
?>
</body>
</html>