<?php
$id=$_GET['id'];
$idd=explode('//',$id);
$zone=$idd[0];
$dump_date=$idd[1];
$dump_date1=$idd[2];
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 Zone" .$zone. ' From '. $dump_date.' To '.$dump_date1.  ".xls";

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

  $flag = false;
 ?><table border="1">
	<tr> 
		<th>Sr No</th>
		<th>Fe Name</th>
		<th>Mobile No</th>
		<th>Zone</th>		
		<th>City</th>
		<th>First Attempt</th>
		<th>Last Attempt</th>
		<th>Allocation</th>
		<th>Done</th>
		<th>Pending</th>
		
	</tr><?php
	$zone=$zone;
	$dump_date=$dump_date;
	$dump_date1=$dump_date1;
	$dy=explode('-',$dump_date1);
		$y=$dy[2];
		if($dump_date and $dump_date1 and $zone!="All")
		{
		$sql2="select max(`unix_time`) as max FROM `allocation_tbl` WHERE dump_date between '$dump_date' and '$dump_date1' ";
		$result2 = MYSQLI_QUERY($conn,$sql2);
		$data2=mysqli_fetch_array($result2);
		$unix_time=$data2['max'];
if($zone=="DELHI NCR")
		{
		$queryr="SELECT distinct(allocation_tbl.user_code) FROM `allocation_tbl`,`user_tbl` WHERE (allocation_tbl.unix_time<='$unix_time' or allocation_tbl.report_date between '$dump_date' and '$dump_date1') and allocation_tbl.user_code !='2000001918' and allocation_tbl.user_code=user_tbl.user_code  and user_tbl.zone in ('EAST','CENTRAL','GHAZIABAD','NOIDA','NORTH','SOUTH','WEST','FARIDABAD','GURGAON') and (allocation_tbl.month like '%$y%' )  order by user_tbl.zone asc";
	$resultr = MYSQLI_QUERY($conn,$queryr);
		}
		elseif($zone=="NATIONAL")
		{
		$queryr="SELECT distinct(allocation_tbl.user_code) FROM `allocation_tbl`,`user_tbl` WHERE (allocation_tbl.unix_time<='$unix_time' or allocation_tbl.report_date between '$dump_date' and '$dump_date1') and allocation_tbl.user_code !='2000001918' and allocation_tbl.user_code=user_tbl.user_code  and user_tbl.zone not in ('EAST','CENTRAL','GHAZIABAD','NOIDA','NORTH','SOUTH','WEST','FARIDABAD','GURGAON') and (allocation_tbl.month like '%$y%' )  order by user_tbl.zone asc";
	$resultr = MYSQLI_QUERY($conn,$queryr);
		}
		else
		{
	$queryr="SELECT distinct(allocation_tbl.user_code) FROM `allocation_tbl`,`user_tbl` WHERE (allocation_tbl.unix_time<='$unix_time' or allocation_tbl.report_date between '$dump_date' and '$dump_date1')  and allocation_tbl.user_code=user_tbl.user_code and (allocation_tbl.month like '%$y%' ) and user_tbl.zone='$zone' order by user_tbl.first_name asc";
	$resultr = MYSQLI_QUERY($conn,$queryr);
	}
	}	
	elseif($dump_date and $dump_date1 and $zone=="All")
		{
		$sql2="select max(`unix_time`) as max FROM `allocation_tbl` WHERE dump_date between '$dump_date' and '$dump_date1' ";
		$result2 = MYSQLI_QUERY($conn,$sql2);
		$data2=mysql_fetch_array($result2);
		$unix_time=$data2['max'];
		
	$queryr="SELECT distinct(allocation_tbl.user_code) FROM `allocation_tbl`,`user_tbl` WHERE (allocation_tbl.unix_time<='$unix_time' or allocation_tbl.report_date between '$dump_date' and '$dump_date1')  and allocation_tbl.user_code=user_tbl.user_code and (allocation_tbl.month like '%$y%' )  order by user_tbl.zone asc";
	$resultr = MYSQLI_QUERY($conn,$queryr);
	}
	else
	{
	}	
	 $j=1;
	while($data=mysqli_fetch_array($resultr)) {
	
	$user_code=$data['user_code'];
	$sqlfe="select first_name,last_name,mobile,zone,location from user_tbl where user_code='$user_code' ";
	$resultuser=mysqli_query($conn,$sqlfe);
	$datauser=mysqli_fetch_array($resultuser);
	$sqlalloc="select count(id) as num from allocation_tbl where user_code='$user_code' and status='4' and unix_time<='$unix_time' " ;
	$resultalloc=mysqli_query($conn,$sqlalloc);
	$dataalloc=mysqli_fetch_array($resultalloc);
	/*echo$sqlalloc2="select count(id) as num from allocation_tbl where user_code='$user_code' and  unix_time>'$min' and unix_time<='$unix_time' and status!='4'   and report_date!='$dump_date'" ;
	$resultalloc2=mysql_query($sqlalloc2);
	$dataalloc2=mysql_fetch_array($resultalloc2);*/
	$sqldone="select count(id) as num from allocation_tbl where user_code='$user_code' and report_date between '$dump_date'  and '$dump_date1'  and month like '%$y%'" ;
	$resultdone=mysqli_query($conn,$sqldone);
	$datadone=mysqli_fetch_array($resultdone);
	$done=$datadone[0];
	$allocations=$dataalloc[0];
	//$allocations2=$dataalloc2[0];
	$allocation=$allocations+$done;
	$allocationt +=$allocation;
	$donet +=$done;
	$pending=$allocation-$done;
	$pendingt +=$pending;
	$dir=$user_code.'//'.$dump_date.'//'.$dump_date1.'//'.$zone;
	$dir2=$zone.'//'.$dump_date.'//'.$dump_date1;
	$sqlattempt="select report_time from allocation_tbl where  user_code='$user_code' and report_date = '$dump_date'  and month like '%$y%'  order by feunixtime asc ";
	$resultattempt=mysqli_query($conn,$sqlattempt);
	$dataattempt=mysqli_fetch_array($resultattempt); 
	$sqlattemptl="select report_time from allocation_tbl where  user_code='$user_code' and report_date = '$dump_date'  and month like '%$y%'  order by feunixtime desc ";
	$resultattemptl=mysqli_query($conn,$sqlattemptl);
	$dataattemptl=mysqli_fetch_array($resultattemptl); 
	if($allocation>0)
	{
 ?>
	
	<tr  >
	<td><?php echo trim(strtoupper($j));?></td>	
	<td><b><?php echo strtoupper($datauser['first_name'].' '.$datauser['last_name']);?></b></td>
	<td><b><?php echo strtoupper($datauser['mobile']);?></b></td>
	<td><b><?php echo $datauser['zone'];?></b></td>
	<td><b><?php echo $datauser['location'];?></b></td>
	<td><b><?php echo $dataattempt['report_time'];?></b></td>
	<td><b><?php echo $dataattemptl['report_time'];?></b></td>
	<td><b><?php echo $allocation;?></b></td>
	<td><b><?php echo $done;?></b></td>	
	<td><b><?php echo $pending;?></b></td>
	
	</tr>
	<?php
	$j++; } } ?>
	 <tr >
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b></b></font></td>
<td align="center"><font size="2"><b>Total</b></font></td>
<td  align="center" ><font size="4"><b><?php echo $allocationt;?></b></font></td>
<td  align="center" ><font size="4"><b><?php echo $donet;?></b></font></td>
<td  align="center" ><font size="4"><b><?php echo $pendingt;?></b></font></td>
</tr>
</table>

