<?
 session_start();
if(strlen($brcode)<=0)
{
  print "<script> alert('Invalid User'); parent.document.location='ind.php' </script>";
  return;
}
include("../opendb.php");
include("../test.php");
?>
<style>
body,td{font-family:'arial narrow';letter-spacing:1;font-size:13}
//tr{background-color:white} 
th{background-color:c1d799;font-size:13}
.mnu{margin-left:5}
body{BACKGROUND-COLOR:fcfff1}
</style> 
<?
if($show)
{
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;
$rr1=mysqli_query($db,"select formno from veh_invoice where formno!=0 and brcode='$brcode' ");
while($vrow=mysqli_fetch_array($rr1))
{
 $s1.=$vrow[0].",";
}
$s1=substr($s1,0,strlen($s1)-1);

?>
<center>
<h2><?print $branch?> <br>ORDER FORM STATUS AT BRANCH</h2>
[Printed by <?print $user?> On <?print date("r")?>]<br>&nbsp;
</center>
<?print "<center><b>From ".datetohtml($df). " to ".datetohtml($dt)."</b>";?>
<table border=1>
<tr><td valign=top>
<?
$sq="select config,count(config) from orderform  where brcode='$brcode' and dt>='$df' and dt<='$dt' and stat='Active' and  formno not in($s1) group by config order by config";
$ct=0;
 $conres=mysqli_query($db,$sq) or die(mysqli_error());
print "<table border=1><th colspan=2>Configuration";
 while($conrow=mysqli_fetch_array($conres))
 {
  print "<tr><td>$conrow[0]</td><td align=right>$conrow[1]</td></tr>";
 $ct+=$conrow[1];
 }
print "<tr><td align=right><b>Total</td><td align=right><b>$ct</td></tr>";
print "</table></td>";
//********
print "<td>";
$sq="select seg,count(seg) from orderform  where brcode='$brcode' and dt>='$df' and dt<='$dt' and stat='Active' and  formno not in($s1)  group by seg order by seg";
 $conres=mysqli_query($db,$sq) or die(mysqli_error());
print "<table border=1><th colspan=2>Segment</th>";
$segt=0;
 while($conrow=mysqli_fetch_array($conres))
 {
 	$segres=mysqli_query($db,"select sname from seg_master where code='$conrow[0]'");
        $segrow=mysqli_fetch_row($segres);
  print "<tr><td>$segrow[0]</td><td align=right>$conrow[1]</td></tr>";
 $segt+=$conrow[1];
 }
print "<tr><td align=right><b>Total</td><td align=right><b>$segt</td></tr>";

print "</table></td>";
//********
print "<td valign=top>";
$sq="select fine,count(fine) from orderform  where brcode='$brcode' and dt>='$df' and dt<='$dt' and stat='Active' and  formno not in($s1)  group by fine order by fine";
 $conres=mysqli_query($db,$sq) or die(mysqli_error());
print "<table border=1><th colspan=2>Financier</th>";
$fint=0;
 while($conrow=mysqli_fetch_array($conres))
 {
 	$segres1=mysqli_query($db,"select finname from fin_master where code='$conrow[0]'");
        $segrow=mysqli_fetch_row($segres1);
	$fint+=$conrow[1];
  print "<tr><td>$segrow[0]</td><td align=right>$conrow[1]</td></tr>";
 }
print "<tr><td align=right><b>Total</td><td align=right><b>$fint</td></tr>";
print "</table></td>";


print "</table>";
//********8888
$sq="select rname,config,count(config) from orderform where brcode='$brcode' and dt>='$df' and dt<='$dt' and stat='Active' and  formno not in($s1)  group by rname,config order by rname";
$repres=mysqli_query($db,$sq)or die(mysql-error());
print "<table border=1><th colspan=3>Representative";
$rept=0;
while($reprow=mysqli_fetch_array($repres))
{
  $rep=mysqli_query($db,"select repname from rep_master where code='$reprow[0]'");
  $repr=mysqli_fetch_row($rep);
  print "<tr><td>$repr[0]</td><td>$reprow[1]</td><td align=right>$reprow[2]</td></tr>";
  $rept+=$reprow[2];
}
print "<tr><td align=right colspan=2> <b>Total</td><td align=right><b>$rept</td></tr>";

print "</table>";
print "<center>";
//********************************************
//print $s;
$sq="select distinct location from orderform where brcode='$brcode' and stat='Active' and  formno not in($s1) and dt>='$df' and dt<='$dt' order by location ";
$res1=mysqli_query($db,$sq)or die(mysqli_error());
while($locrow=mysqli_fetch_array($res1))
{
 print "<fieldset><legend>$locrow[0]</legend>";
 print "<table border=1 width=100%><tr><td width=50% valign=top align=center>";
 $sq="select config,count(config) from orderform where brcode='$brcode' and stat='Active' and  formno not in($s1) and dt>='$df' and dt<='$dt' and location='$locrow[0]' group by config order by config";
 $concntres=mysqli_query($db,$sq);
 print "<table bgcolor=ivory border=1 width=100%><th>Config<th>Count";
 $tt=0;
 while($concntrow=mysqli_fetch_array($concntres))
  {
    print "\n<tr><td>$concntrow[0]</td><td align=center width=50>$concntrow[1]</td></tr>";
    $tt+=$concntrow[1];
  }
 print "<tr><th>Total</th><th>$tt</th>";
 print "</table>";
 print "</td>";
 print "<td bgcolor='yellow' valign=top align=center width=30%>";
  $advres1=mysqli_query($db,"select sum(amount) from advcollection where date>='$df' and date<='$dt' and brcode='$brcode' and formno in(select formno from orderform where location='$locrow[0]' and dt>='$df' and dt<='$dt' and brcode='$brcode' and stat='Active' and  formno not in($s1))")or die(mysqli_error());
 $advamtrow=mysqli_fetch_array($advres1);
  print "<table border=1 width=90%><th colspan=2>Advance Cellection<tr bgcolor='white'><td>Advance Amount </td><td>".putcoma($advamtrow[0])."</td></tr></table>";
print "<td bgcolor=white valign=top align=center><table border=1><th>Representative<th>Count";
$sq="select  rname,count(rname) from orderform where brcode='$brcode' and stat='Active' and  formno not in($s1) and dt>='$df' and dt<='$dt' and  location='$locrow[0]' group by rname";
//print $sq;
$repres=mysqli_query($db,$sq)or die (mysqli_err());
while($rerow=mysqli_fetch_array($repres))
{
 print "\n<tr><td>".getRep($rerow[0])."</td><td align=center>$rerow[1]</td></tr>";
}
print"</table>";
 print "</td></tr></table>";
 print "</fieldset>";
}

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

?>
<br>&nbsp;
<table border=0 bgcolor=gray cellspacing=1 cellpadding=1>
<th>NO.<th>Form NO <th>Date <th>Customer <th>Rep. <th>Config <th>Location
<?
$rr=mysqli_query($db,"select formno from veh_invoice where formno!=0 and brcode='$brcode' ");
while($vrow=mysqli_fetch_array($rr))
{
 $s.=$vrow[0].",";
}
$s=substr($s,0,strlen($s)-1);
//print $s;
$sq="select * from orderform where brcode='$brcode' and stat='Active' and actvtd='y' and  formno not in($s) and dt>='$df' and dt<='$dt' order by location,rname ";
$res=mysqli_query($db,$sq)or die(mysqli_error());
$i=1;
while($row=mysqli_fetch_array($res))
{
  $repres=mysqli_query($db,"select repname from rep_master where code='$row[rname]'");
  $reprow=mysqli_fetch_row($repres);
 print "\n<tr bgcolor=white valign=top><td align=center rowspan=2 valign=top>".$i++."</td><td align=center rowspan=2 valign=top><a href='viewform.php?fno=$row[formno]' target='OF'>$row[formno]</a></td><td>".datetohtml($row[dt])."</td>";
print "<td>$row[cust]</td><td align=center>$reprow[0]</td>";
 print "<td>$row[config]</td><td>$row[location]</td>";
   $advres=mysqli_query($db,"select * from advcollection where formno='$row[formno]'");
    print "\n<tr bgcolor=white><td colspan=8><table bgcolor=silver width=300><tr><td colspan=3 align=center><b>Payment Details</td></tr>";
    if(mysqli_num_rows($advres)>0)
    {
     print "<th>Date <th>Type <th>Amount <th>Cleared";
     $t=0;
    while($advrow=mysqli_fetch_array($advres))
    {
      
       print "\n<tr bgcolor=white><td>".datetohtml($advrow[date])."</td><td>$advrow[type]</td><td align=right>$advrow[amount]</td><td align=center>$advrow[clearing]</td></tr>";
    $t+=$advrow[amount];
   }
     print "\n<tr bgcolor=white><td colspan=2><b>Total</td><td align=right><b>".putComa($t)."</td></tr>";
   print "\n<tr><td colspan=2>Vehicle Amount</td><td align=right>".putComa($row[netamt])."</td></tr>";
 $bal=putComa($row[netamt]-$t);
  if($bal<=0)
   print "<tr><td colspan=2><font color=red><blink><b>Balance Amount</td><td align=right><b>".$bal."</td></tr>";
else
   print "\n<tr><td colspan=2><b>Balance Amount</td><td align=right><b>".$bal."</td></tr>";

  }
print " </table>";
}
return;
} // end if
function getRep($x)
{
include("../opendb.php");
 $eeres=mysqli_query($db,"select repname from rep_master where code='$x'")or die ('Err'.mysqli_error()); 
 $eerow=mysqli_fetch_row($eeres);
 return $eerow[0];
}
?>
<CENTER>
<h2>ORDER FORM STATUS AT BRANCH</H2>
<form action='<?print $PHP_SELF?>' method=post>
<table bgcolor='silver'>
<tr><td> Date from </td><td>dd<input type=text name='d' size=2 value='<?print date('d',time())?>'>
				mm<input type=text name='m' size=2 value='<?print date('m',time())?>'>
				yy<input type=text name='y' size=4 value='<?print date('Y',time())?>'>
			</td></tr>
<tr><td> To </td><td>dd<input type=text name='d1' size=2 value='<?print date('d',time())?>'>
				mm<input type=text name='m1' size=2 value='<?print date('m',time())?>'>
				yy<input type=text name='y1' size=4 value='<?print date('Y',time())?>'>
			</td>
</tr>
<th colspan=2 align=center><input type=submit name='show' value='Show'>
</td></tr>
</table>
</form>
