<?php
$id=$_GET['id'];
$zone=$id;

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.  ".xls";

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

  $flag = false;
 ?><table border="1">
	<tr height="25">
				<th class="center" ><b>Sr No</b></th>				
				<th class="center"><b>Fe Name</b></th>				
				<th class="center"><b>Mobile No</b></th>
				<th class="center"><b>Zone</b></th>
				<th class="center"><b>City</b></th>
				<td style="width:200px;">
				<table  style="width:200px;" >
				<tr>
				<td style="width:200px;" align="center"><b>Allocation</b></td>
				</tr>				
				<tr>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b>Backlog</b></td>
				<td style="width:50px;" align="center"><b>AM</b></td>
				<td style="width:50px;" align="center"><b>PM</b></td>
				</tr>
				</table>
				</td>
				</tr>
				</table>
				</td>
						
				<td style="width:200px;">
				<table  style="width:200px;" >
				<tr>
				<td style="width:200px;" align="center"><b>Done</b></td>
				</tr>				
				<tr>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b>Backlog</b></td>
				<td style="width:50px;" align="center"><b>AM</b></td>
				<td style="width:50px;" align="center"><b>PM</b></td>
				</tr>
				</table>
				</td>
				</tr>
				</table>
				</td>
				<td style="width:200px;">
				<table  style="width:200px;" >
				<tr>
				<td style="width:200px;" align="center"><b>Pending</b></td>
				</tr>				
				<tr>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b>Backlog</b></td>
				<td style="width:50px;" align="center"><b>AM</b></td>
				<td style="width:50px;" align="center"><b>PM</b></td>
				</tr>
				</table>
				</td>
				</tr>
				</table>
				</td>
			</tr>
	</tr><?php
	$reportdate=date('m-d-Y');
		$y=date('Y');

		
	$queryr="SELECT distinct(allocation_tbl.user_code) FROM `allocation_tbl`,`user_tbl` WHERE  allocation_tbl.user_code=user_tbl.user_code and (allocation_tbl.month like '%$y%' )  and (allocation_tbl.status='4' or allocation_tbl.report_date='$reportdate') order by user_tbl.zone asc";
	$resultr = MYSQL_QUERY($queryr);
			
	 $j=1;
	date_default_timezone_set('Asia/Calcutta');
	$d=date('d-m-Y');
	$t="02:01 PM";
	$dt=$d.' '.$t;
	$unix_time= strtotime(($dt));	
	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'" ;
	$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='$reportdate'   and month like '%$y%'" ;
	$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;
	$dir=$user_code;
	$dir2=$zone.'//'.$dump_date.'//'.$dump_date1;
	$sqlallocbacklog="select count(id) as num from allocation_tbl where user_code='$user_code' and status='4' and dump_date!='$reportdate'" ;
	$resultbacklog=mysql_query($sqlallocbacklog);
	$databacklog=mysql_fetch_array($resultbacklog);	
	$backlog=$databacklog[0];
	$sqlallocam="select count(id) as num from allocation_tbl where user_code='$user_code' and status='4' and dump_date='$reportdate' and unix_time<='$unix_time' " ;
	$resultam=mysql_query($sqlallocam);
	$dataam=mysql_fetch_array($resultam);	
	$am=$dataam[0];	
	$sqlallocpm="select count(id) as num from allocation_tbl where user_code='$user_code' and status='4' and dump_date='$reportdate' and unix_time>'$unix_time' " ;
	$resultpm=mysql_query($sqlallocpm);
	$datapm=mysql_fetch_array($resultpm);	
	$pm=$datapm[0];	
	$sqlbkdone="select count(id) as num from allocation_tbl where user_code='$user_code' and report_date='$reportdate' and dump_date!='$reportdate'   and month like '%$y%'" ;
	$resultbkdone=mysql_query($sqlbkdone);
	$databkdone=mysql_fetch_array($resultbkdone);
	$backlogdone=$databkdone[0];
        $sqlamdone="select count(id) as num from allocation_tbl where user_code='$user_code' and report_date='$reportdate' and dump_date='$reportdate' and unix_time<='$unix_time'   and month like '%$y%'" ;
	$resultamdone=mysql_query($sqlamdone);
	$dataamdone=mysql_fetch_array($resultamdone);
	$amdone=$dataamdone[0];
	$sqlpmdone="select count(id) as num from allocation_tbl where user_code='$user_code' and report_date='$reportdate' and dump_date='$reportdate' and unix_time>'$unix_time'   and month like '%$y%'" ;
	$resultpmdone=mysql_query($sqlpmdone);
	$datapmdone=mysql_fetch_array($resultpmdone);
	$pmdone=$datapmdone[0];
	$backlogpending=$backlog;
	$ampending=$am;
	$pmpending=$pm;
	$backlogtotal +=($backlog+$backlogdone);
	$amtotal += ( $am+$amdone);
	$pmtotal +=($pm+$pmdone);
	$backlogdonetotal +=$backlogdone;
	$amdonet +=$amdone;
	$pmdonet +=$pmdone;
	$backlogpendingt +=$backlogpending;
	$ampendingt +=$ampending;
	$pmpendingt +=$pmpending;
	if($backlog>0)
	{
	$backlog=$backlog;
	}
	else
	{
	$backlog=0;
	}
	if($ampending>0)
	{
	$ampending=$ampending;
	}
	else
	{
	$ampending=0;
	}
	if($pmpending>0)
	{
	$pmpending=$pmpending;
	}
	else
	{
	$pmpending=0;
	}
	if(($backlog==0) and ($ampending==0 ) and ($pmpending==0) ) 
		{
		$color="#008000";  
		 }	
	elseif($j%2==0)
		 {
		 $color="#FFFAFA";  
		 }
		 else
		 {
		 $color="white";  
 		}
		if($pending>0)
		{
		$bg="#FFA07A";
		}
		else
		{
		$bg="white";
		}
		if($pending2>0)
		{
		$bg2="#FFFF00";
		}
		else
		{
		$bg2="white";
		}
		$bklg=$backlog+$backlogdone;
		if($bklg>0)
		{
		$bgcolor="red";
		}
		else
		{
		$bgcolor="black"; 
		}
		if($backlog>0)
		{
		$bgcolor2="red";
		}
		else
		{
		$bgcolor2="#4682B4";
		}
		if($amdone>0)
		{
		$amcolor="#008000";
		}
		else
		{
		$amcolor="#4682B4";
		}
		/*if($backlog==0 and $ampending==0 and $pmpending==0)
		{
		$greencolor="#008000";
		}
		else
		{
		$greencolor=$bg2;
		}*/
	if($allocation>0)
	{ 
	?>

<tr   height="15">
<td   align="center"><font size="2"><b><?php echo $j ;?></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 strtoupper($datauser['mobile']);?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $datauser['zone'];?></b></font></td>
<td   align="center"><font size="2"><b><?php echo $datauser['location'];?></b></font></td>
<td style="width:300px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"> <b><?php echo $backlog+$backlogdone;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $am+$amdone;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pm+$pmdone;?></b></td>
				</tr>
				</table>
				</td>
			       <td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b><?php echo $backlogdone;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $amdone;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pmdone;?></b></a></td>
				</tr>
				</table>
				</td>
 				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b><?php echo $backlogpending;?></font></b></td>
				<td style="width:50px;" align="center"><b><?php echo $ampending;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pmpending;?></b></td>
				</tr>
				</table>
				</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="4"><b>Total</b></font></td>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b><?php echo $backlogtotal;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $amtotal;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pmtotal;?></b></td>
				</tr>
				</table>
				</td>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b><?php echo $backlogdonetotal;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $amdonet;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pmdonet;?></b></td>
				</tr>
				</table>
				</td>
				<td style="width:200px;">
				<table  style="width:200px;" border="1">
				<tr>
				<td style="width:100px;" align="center"><b><?php echo $backlogpendingt;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $ampendingt;?></b></td>
				<td style="width:50px;" align="center"><b><?php echo $pmpendingt;?></b></td>
				</tr>
				</table>
				</td>
</tr>
</table>

