<?php
$id=$_GET['id'];
$idd=explode('//',$id);
$zone=$idd[0];
$dump_date=$idd[0];
$dump_date1=$idd[1];
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" .$id.  ".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>Location</th>
		<th>Allocation</th>
		<th>Done</th>
		<th>Pending</th>
		
	</tr><?php
	
	$dump_date=$dump_date;
	$dump_date1=$dump_date1;
	$dy=explode('-',$dump_date1);
		$y=$dy[2];
	if($dump_date)
		{
		
	$sql2="select max(`unix_time`) as max FROM `allocation_tbl` WHERE dump_date between '$dump_date' and '$dump_date1' ";
		$result2 = MYSQL_QUERY($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 = MYSQL_QUERY($queryr);
	}	
	 $j=1;
	while($data=mysql_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=mysql_query($sqlfe);
	$datauser=mysql_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=mysql_query($sqlalloc);
	$dataalloc=mysql_fetch_array($resultalloc);
	
	$sqldone="select count(id) as num from allocation_tbl where user_code='$user_code' and report_date between '$dump_date'  and '$dump_date1'  " ;
	$resultdone=mysql_query($sqldone);
	$datadone=mysql_fetch_array($resultdone);
	$done=$datadone[0];
	$allocations=$dataalloc[0];
	//$allocations2=$dataalloc2[0];
	$allocation=$allocations+$done;
	$allocationt +=$allocation;
	$donet +=$done;
	$pending=$allocation-$done;
	$pendingt +=$pending;
	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 $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>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>

