<?php

$dump_date=date('m-d-Y');
$date=date('d-M-Y');
include("database_connection.php");

  function cleanData($str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // filename for download
  $filename = "Fe Report Done From" . $date.  ".xls";

 	header("Content-Disposition: attachment; filename=\"$filename\"");
  	header("Content-Type: application/vnd.ms-excel");

  $flag = false;
 ?><table border="1">
	<tr> 
		<th class="center" ><b>Sr No</b></th>				
				<th class="center"><b>Bank Name</b></th>				
				<th class="center"><b>App No</b></th>
				<th class="center"><b>Ref No</b></th>
				<th class="center"><b>Type</b></th>
				<th class="center"><b>Applicant Name</b></th>
				<th class="center"><b>Address</b></th>
				<th class="center"><b>FE Name</b></th>
				<th class="center"><b>Allocation date & time</b></th>
				<th class="center"><b>Report date & time </b></th>
				<th class="center"><b>Tat </b></th>
		
	</tr><?php
	$dumpdate1=explode('-',$dump_date);
		$yy=$dumpdate1[2];		
	$queryr="SELECT  allocation_tbl.id,allocation_tbl.bank_name,allocation_tbl.app_no,allocation_tbl.case_id,allocation_tbl.name,allocation_tbl.type,allocation_tbl.address1,allocation_tbl.date,allocation_tbl.dump_date,allocation_tbl.upload_time,allocation_tbl.report_date,allocation_tbl.report_time,allocation_tbl.user_code,allocation_tbl.unix_time FROM `allocation_tbl`,`user_tbl` WHERE  allocation_tbl.report_date = '$dump_date' and (allocation_tbl.month like '%$yy%' ) and allocation_tbl.user_code=user_tbl.user_code and allocation_tbl.name!=''  order by allocation_tbl.id asc";
	$resultr = MYSQL_QUERY($queryr);
	$j=1;
	while($data=mysql_fetch_array($resultr)) {
	$id=$data['id'];
	$user_code=$data['user_code'];
	$sqlfe="select first_name,last_name  from user_tbl where user_code='$user_code' ";
	$resultuser=mysql_query($sqlfe);
	$datauser=mysql_fetch_array($resultuser);
	
	$reportdate1=explode('-',$data['report_date']);
	$m=$reportdate1[0];
	$d=$reportdate1[1];
	$y=$reportdate1[2];
	if($m=="01")
	{
	$m1="Jan";
	}
	elseif($m=="02")
	{
	$m1="Feb";
	}
	elseif($m=="03")
	{
	$m1="Mar";
	}
	elseif($m=="04")
	{
	$m1="Apr";
	}
	elseif($m=="05")
	{
	$m1="May";
	}
	elseif($m=="06")
	{
	$m1="Jun";
	}
	elseif($m=="07")
	{
	$m1="Jul";
	}
	elseif($m=="08")
	{
	$m1="Aug";
	}
	elseif($m=="09")
	{
	$m1="Sep";
	}
	elseif($m=="10")
	{
	$m1="Oct";
	}
	elseif($m=="11")
	{
	$m1="Nov";
	}
	elseif($m=="12")
	{
	$m1="Dec";
	}
	else
	{
	$m1='';
	}
	$reportdate=$d.'-'.$m1.'-'.$y;
	//$uploaddate=$data['dump_date'].' '.$data['upload_time'];
	$uploaddate=$data['date'].' '.$data['upload_time'];
	//$datevisit=$data['report_date'].' '.$data['report_time'];
	$datevisit=$reportdate.' '.$data['report_time'];
		$timestamp= strtotime($uploaddate);
		$timestamp1= strtotime($datevisit);
		 $timestamp1.','.$timestamp;
		$datet=($timestamp1-$timestamp);
		$date2=$datet;
		$fdate= floor($date2/3600);
		$fdate22= floor($date2/(24*3600));
		$fh=$fdate22*24;
		$fdate2= floor(($date2/3600)-$fh);
		//$fdate2= floor($date2/3600);
		$fdate1= floor($date2/60 % 60);
		$visit1=-$fdate.' Hr'.' '.$fdate1.' Min';	
		$visit2=$fdate22.' Days '.$fdate2.' Hr'.' '.$fdate1.' Min'; 
		if(strlen($datevisit)>2)
		{
		if($fdate22>0)
		{
		$visit2=$fdate22.' Days '.$fdate2.' Hr'.' '.$fdate1.' Min';
		}
		else
		{
		$visit2=$fdate2.' Hr'.' '.$fdate1.' Min';
		}
		}
		else
		{
		$visit2='';
		}
	if($j%2==0)
		 {
		 $color="#FFFAFA";  
		 }
		 else
		 {
		 $color="white";  
 		}
		if($pending>0)
		{
		$bg="#FFA07A";
		}
		else
		{
		$bg="white";
		}
		if($pending2>0)
		{
		$bg2="#FFFF00";
		}
		else
		{
		$bg2="white";
		}
	if($id>0)
	{
 ?>
	
	<tr  >
	<td   align="center"><font size="2"><b><?php echo $j ;?></b></font></td>
<td align="center"><font size="2"><b><?php echo strtoupper($data['bank_name']);?></b></font></td>
<td align="center"><font size="2"><b><?php echo strtoupper($data['app_no']);?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $data['case_id'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $data['type'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $data['name'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $data['address1'];?></b></font></td>
<td align="center"><font size="2"><b><?php echo strtoupper($datauser['first_name'].' '.$datauser['last_name']);?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $data['date'].' '.$data['upload_time'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $reportdate.' '.$data['report_time'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $visit2;?></b></font></td>
	</tr>
	<?php
	$j++; } } ?>
	 
</table>

