<?php
$url="http://earthquake.usgs.gov/earthquakes/feed/geojson/2.5/week";

$json = file_get_contents($url); 
$data = json_decode($json, TRUE);

// Parse with sections
$ini = parse_ini_file("conf.ini", true);
mysql_connect($ini['db']['host'],$ini['db']['user'],$ini['db']['pass']);
mysql_select_db($ini['db']['db']);

      if ($ini['db']['log']=="1"){
       $arrival_time=microtime(true);
       $filename=basename(__FILE__);
       $filename="log_".$filename.".txt";
       $chunck=strval($arrival_time)." ".$_SERVER['REQUEST_URI']."\r\n";
       file_put_contents($filename,$chunck,FILE_APPEND);
      }

if (count($data)>0)
{
 //extract the maximum date
 $sql=mysql_query("select data from automatic_temp order by data DESC limit 1");
 if (mysql_num_rows($sql)){
	$max_date=mysql_result($sql,0,"data");
 }
 
 //empty the table
 $sql=mysql_query("TRUNCATE automatic_temp");

 foreach($data['features'] as $quake) {
    $place=mysql_real_escape_string($quake['properties']['place']);
    $stamp=$quake['properties']['time']/1000;
    $time=date('Y-m-d H:i:s',$stamp);
	$felt=$quake['properties']['felt'];
	$cdi=$quake['properties']['cdi'];
	$mmi=$quake['properties']['mmi'];
	$tsunami=$quake['properties']['tsunami'];
	$url=$quake['properties']['url'];
	if ($felt==null){
	  $felt=0;
	}
	if ($cdi==null){
	  $cdi=0;
	}
	if ($mmi==null){
	  $mmi=0;
	}
	
	$cdi=max($cdi,$mmi);
	
	if ($tsunami==null){
	  $tsunami=-1;
	}

    $mag=(float)$quake['properties']['mag']; 
    $lat=(float)$quake['geometry']['coordinates'][1];
    $lon=(float)$quake['geometry']['coordinates'][0];
    $dep=(float)$quake['geometry']['coordinates'][2];
    $code=$quake['properties']['code'];
    	
	$lat_idx=$lat;
    $lat_idx=(round($lat_idx*10)/10+0.025)*1000;
	if ($lat_idx>90000){
		$lat_idx=90000-25;
	}
	$lon_idx=$lon;
    $lon_idx=(round($lon_idx*10)/10+0.025)*1000;
	if ($lon_idx>180000){
		$lon_idx=$lon_idx-360000;
	}	
	
	$sql=mysql_query("select pop25,pop100,pop200 from worldpop_recent where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
	if (mysql_num_rows($sql)){
		$pop25=mysql_result($sql,0,"pop25");
		$pop100=mysql_result($sql,0,"pop100");
		$pop200=mysql_result($sql,0,"pop200");
	}
	else{
		if ($lon_idx<=-120025){
			$sql=mysql_query("select pop25,pop100,pop200 from worldpop1 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
		}
		else{
			if ($lon_idx<=-60025){
				$sql=mysql_query("select pop25,pop100,pop200 from worldpop2 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
			}
			else{
				if ($lon_idx<=-25){
					$sql=mysql_query("select pop25,pop100,pop200 from worldpop3 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
				}
				else{
					if ($lon_idx<=59975){
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop4 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
					}
					else{
						if ($lon_idx<=119975){
							$sql=mysql_query("select pop25,pop100,pop200 from worldpop5 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
						else{
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop6 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
					}
				}
			}
		}
		if (mysql_num_rows($sql))
		{ 
			$pop25=mysql_result($sql,0,"pop25");
			$pop100=mysql_result($sql,0,"pop100");
			$pop200=mysql_result($sql,0,"pop200");
		}
		else
		{ 
			$pop25=0;
			$pop100=0;
			$pop200=0;
		} 
		$sql=mysql_query("insert into worldpop_recent (latitude,longitude,pop25,pop100,pop200) values ($lat_idx,$lon_idx,$pop25,$pop100,$pop200)");
	}		
	$sql=mysql_query("insert into automatic_temp (latitude,longitude,depth,magnitude,felt,cdi,pop25,pop100,pop200,place,data,code,tsunami,url,provider) values ($lat,$lon,$dep,$mag,$felt,$cdi,$pop25,$pop100,$pop200,'$place','$time','$code',$tsunami,'$url','USGS')");
 }
}

$sql=mysql_query("select automatic_temp.latitude,automatic_temp.longitude,automatic_temp.depth,automatic_temp.magnitude,automatic_temp.data,automatic_temp.code,automatic_temp.place from automatic_temp left join contest_earthquakes on automatic_temp.code=contest_earthquakes.code where automatic_temp.magnitude>=5.5 AND contest_earthquakes.code is null AND automatic_temp.code<>'' AND automatic_temp.data > (select data from contest_earthquakes order by data desc limit 1)");

if (mysql_num_rows($sql)){
 $q=mysql_query("select ID,current_contest_number from contest_state");
 $contest_number=mysql_result($q,0,"current_contest_number");
 $contest_ID=mysql_result($q,0,"ID");

 $num_rows = mysql_num_rows($sql);
 for ($i=0;$i<$num_rows;$i++){
   $latitude=mysql_result($sql,$i,"latitude");
   $longitude=mysql_result($sql,$i,"longitude");
   $depth=mysql_result($sql,$i,"depth");
   $magnitude=mysql_result($sql,$i,"magnitude");
   $data=mysql_result($sql,$i,"data");
   $code=mysql_result($sql,$i,"code");
   $place=mysql_real_escape_string(mysql_result($sql,$i,"place"));
   $q=mysql_query("insert into contest_earthquakes (latitude,longitude,depth,magnitude,data,code,place,contest_number) values ($latitude,$longitude,$depth,$magnitude,'$data','$code','$place',$contest_number)");  
   
   $q=mysql_query("insert into contest_earthquakes_notify (contest_number) values ($contest_number)");     
 }
 
 $q=mysql_query("select * from contest_users where contest_number=$contest_number");
 $num_quakes = mysql_num_rows($sql);
 $num_users = mysql_num_rows($q);
 for ($i=0;$i<$num_users;$i++){
   $distance=99999;

   $user_id=mysql_result($q,$i,"user_id");
   $user_code=mysql_result($q,$i,"user_code");
   for ($j=0;$j<$num_quakes;$j++){
    $latitude_earthquake=mysql_result($sql,$j,"latitude");
    $longitude_earthquake=mysql_result($sql,$j,"longitude");
    $latitude_user=mysql_result($q,$i,"latitude");
    $longitude_user=mysql_result($q,$i,"longitude");

    $dlat=($latitude_user-$latitude_earthquake)/360*2*pi();
    $dlong=($longitude_user-$longitude_earthquake)/360*2*pi();
    $latitude_user=$latitude_user/360*2*pi();
    $latitude_earthquake=$latitude_earthquake/360*2*pi();
    $a=pow(sin($dlat/2),2)+cos($latitude_user)*cos($latitude_earthquake)*pow(sin($dlong/2),2);
    $c=2*atan2(sqrt($a),sqrt(1-$a));
    $current_distance=6371*$c;

    if ($current_distance<$distance){
      $distance=$current_distance;
    }
   }
   
   $score=5;
   if ($distance<50){
     $score=100;
   }
   else{
    if ($distance<100){
     $score=75;
    }
    else{
     if ($distance<250){
       $score=50;
     }
     else{
	  if ($distance<500){
        $score=25;
      }
      else{
       if ($distance<3000){
         $score=10; 
       } 
      }
     }     
    }
   }

   if ($user_code==0){
	   $q2=mysql_query("select * from contest_rank where user_id=$user_id");
	   if (mysql_num_rows($q2)){
		 $ID=mysql_result($q2,0,"ID");
		 $q3=mysql_query("update contest_rank SET number_contests=number_contests+1, score=score+$score where ID=$ID");    }
	   else{
		$nc=1;
		$q3=mysql_query("insert into contest_rank (user_id,score,number_contests) values ($user_id,$score,$nc)");
	   }
   }
   else{
   	   $q2=mysql_query("select * from contest_rank where user_code=$user_code");
	   if (mysql_num_rows($q2)){
		 $ID=mysql_result($q2,0,"ID");
		 $q3=mysql_query("update contest_rank SET number_contests=number_contests+1, score=score+$score where ID=$ID");    }
	   else{
		$nc=1;
		$q3=mysql_query("insert into contest_rank (user_code,score,number_contests) values ($user_code,$score,$nc)");
	   }
   }
 }
 
 //invio notifica tramite gcm
 $dataArray = array('contest_number'=>$contest_number,'type'=>'contest');

 //extract users for notifications
 $sql2=mysql_query("select reg_id from gcm");
 $counter=0;
 $num_rows = mysql_num_rows($sql2);
 for ($i = 0; $i < $num_rows; $i++) 
 {
   $regidArray[$counter]=mysql_result($sql2,$i,"reg_id");
   $counter=$counter+1;
   if ($counter==500 or $i==($num_rows-1))
   {
    $headers = array( 
                   'Authorization: key=AIzaSyAYaXDEapyd-mF9V9LNW1dmuoS-ea9GwWI',
                   'Content-Type: application/json');
    $fields = array(
                'registration_ids'  => $regidArray,
                'data'              => $dataArray);				

	$context = stream_context_create(array(
        'http' => array(
        'method' => 'POST',
        'header' => $headers,
        'content' => json_encode($fields))));
    $response = file_get_contents('https://android.googleapis.com/gcm/send', FALSE, $context);
	var_dump($response);
	$counter=0;	
	$regidArray=array();
   }
 }
 $q=mysql_query("UPDATE contest_state SET current_contest_number=current_contest_number+1 WHERE ID=$contest_ID");
}

$sql=mysql_query("select count(*) as mag_45 from automatic_temp where magnitude>4.5 and data > date_sub(now(), interval 3 day)");
$mag_45=mysql_result($sql,0,"mag_45");
$sql=mysql_query("select count(*) as mag_55 from automatic_temp where magnitude>5.5 and data > date_sub(now(), interval 3 day)");
$mag_55=mysql_result($sql,0,"mag_55");
$sql=mysql_query("select count(*) as mag_65 from automatic_temp where magnitude>6.5 and data > date_sub(now(), interval 3 day)");
$mag_65=mysql_result($sql,0,"mag_65");

$sql=mysql_query("select count(*) as mer_40 from automatic_temp where cdi>4.0 and data > date_sub(now(), interval 3 day)");
$mer_40=mysql_result($sql,0,"mer_40");
$sql=mysql_query("select count(*) as mer_60 from automatic_temp where cdi>6.0 and data > date_sub(now(), interval 3 day)");
$mer_60=mysql_result($sql,0,"mer_60");
$sql=mysql_query("select count(*) as mer_80 from automatic_temp where cdi>8.0 and data > date_sub(now(), interval 3 day)");
$mer_80=mysql_result($sql,0,"mer_80");

$sql=mysql_query("select magnitude,cdi,TIMESTAMPDIFF(MINUTE,data, NOW()) AS difference from automatic_temp where data > date_sub(now(), interval 3 day)");

$num_rows = mysql_num_rows($sql);
$geo_index=0;
$pop_index=0;
for ($i=0;$i<$num_rows;$i++){
   $magnitude=mysql_result($sql,$i,"magnitude");
   $cdi=mysql_result($sql,$i,"cdi");        
   $difference=mysql_result($sql,$i,"difference");
   $factor_time=pow(1-$difference/(3*1440),0.3);

   $geo_index=$geo_index+exp($magnitude)*$factor_time;
   if ($cdi>0){
	$pop_index=$pop_index+exp($cdi)*$factor_time;
   }
}

$sql=mysql_query("insert into pressure (mag_45,mag_55,mag_65,mer_40,mer_60,mer_80,geo_index,pop_index) values ($mag_45,$mag_55,$mag_65,$mer_40,$mer_60,$mer_80,$geo_index,$pop_index)");

//Italian quakes

$url="http://geoserver.rm.ingv.it/prociv/ingvrss.xml";

$str = file_get_contents($url); 
$xml = simplexml_load_string($str, 'SimpleXMLElement',LIBXML_NOCDATA);

date_default_timezone_set("UTC");
$date1=date("Y-m-d H:i:s", time());
date_default_timezone_set("Europe/Rome");
$date2=date("Y-m-d H:i:s", time()); 
$hourdiff = round((strtotime($date2) - strtotime($date1))/3600, 1);

foreach ($xml->channel->item as $item) {
    $pos=strpos($item->description,"Data:");
    $desc=substr($item->description,$pos);
   
    $pos1=strpos($desc,"Data:");
    $pos2=strpos($desc,"<br");
    $data=substr($desc,$pos1+11,$pos2-$pos1-11);
    $y=substr($data,6,4);
    $m=substr($data,3,2);
    $d=substr($data,0,2);
    $time=substr($data,11,8);
    $delta='+'.$hourdiff.' hour';
    $stamp=$y.'-'.$m.'-'.$d.' '.$time;
    $timestamp = strtotime($stamp);
    $stamp2=strtotime($delta,$timestamp);
    $stamp=date('Y-m-d H:i:s',$stamp2);

    $pos1=strpos($desc,"Magnitudo:");
    $pos2=strpos($desc,"Distretto:");
    $magnitudo=substr($desc,$pos1+16,$pos2-$pos1-28);

    $pos1=strpos($desc,"Distretto:");
    $pos2=strpos($desc,"Lat:");
    $distretto=substr($desc,$pos1+16,$pos2-$pos1-28);
    $distretto=str_replace("_"," ",$distretto);

    $pos1=strpos($desc,"Lat:");
    $pos2=strpos($desc,"Lon:");
    $lat=substr($desc,$pos1+10,$pos2-$pos1-19);

    $pos1=strpos($desc,"Lon:");
    $pos2=strpos($desc,"Profondit");
    $lon=substr($desc,$pos1+10,$pos2-$pos1-22);

    $pos1=strpos($desc,"Profondit");
    $pos2=strpos($desc,"km");
    $depth=substr($desc,$pos1+24,$pos2-$pos1-25);
   
    $tsunami=-1;
   
	$lat_idx=$lat;
    $lat_idx=(round($lat_idx*10)/10+0.025)*1000;
	if ($lat_idx>90000){
		$lat_idx=90000-25;
	}
	$lon_idx=$lon;
    $lon_idx=(round($lon_idx*10)/10+0.025)*1000;
	if ($lon_idx>180000){
		$lon_idx=$lon_idx-360000;
	}	
	
	$sql=mysql_query("select pop25,pop100,pop200 from worldpop_recent where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
	if (mysql_num_rows($sql)){
		$pop25=mysql_result($sql,0,"pop25");
		$pop100=mysql_result($sql,0,"pop100");
		$pop200=mysql_result($sql,0,"pop200");
	}
	else{
		if ($lon_idx<=-120025){
			$sql=mysql_query("select pop25,pop100,pop200 from worldpop1 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
		}
		else{
			if ($lon_idx<=-60025){
				$sql=mysql_query("select pop25,pop100,pop200 from worldpop2 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
			}
			else{
				if ($lon_idx<=-25){
					$sql=mysql_query("select pop25,pop100,pop200 from worldpop3 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
				}
				else{
					if ($lon_idx<=59975){
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop4 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
					}
					else{
						if ($lon_idx<=119975){
							$sql=mysql_query("select pop25,pop100,pop200 from worldpop5 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
						else{
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop6 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
					}
				}
			}
		}
		if (mysql_num_rows($sql))
		{ 
			$pop25=mysql_result($sql,0,"pop25");
			$pop100=mysql_result($sql,0,"pop100");
			$pop200=mysql_result($sql,0,"pop200");
		}
		else
		{ 
			$pop25=0;
			$pop100=0;
			$pop200=0;
		} 
		$sql=mysql_query("insert into worldpop_recent (latitude,longitude,pop25,pop100,pop200) values ($lat_idx,$lon_idx,$pop25,$pop100,$pop200)");
	}	   
	$sql=mysql_query("insert into automatic_temp (latitude,longitude,depth,magnitude,pop25,pop100,pop200,place,data,tsunami,provider) values ($lat,$lon,$depth,$magnitudo,$pop25,$pop100,$pop200,'$distretto','$stamp',$tsunami,'INGV')");
}

//Spanish quakes

$data = file_get_contents("http://www.ign.es/ign/layoutIn/sismoTerremotos30Spain.do");
$data = mb_convert_encoding($data, 'UTF-8',mb_detect_encoding($data, 'UTF-8, ISO-8859-1', true));
$data = str_replace("\n", '', $data); // remove new lines
$data = str_replace("\r", '', $data); // remove carriage returns
preg_match_all('|<td>.*?</td>|',$data,$result);
$result=array_slice($result[0],11);

if (count($result)%11 != 0)
{
  print("Errore - Numero di celle errato");
}
else
{
    date_default_timezone_set("GMT");
    $date1=date("Y-m-d H:i:s", time());
    date_default_timezone_set("Europe/Rome");
    $date2=date("Y-m-d H:i:s", time()); 
    $hourdiff = round((strtotime($date2) - strtotime($date1))/3600, 1);
	
	$count=count($result)/11;
	for ($i=0;$i<$count;$i++)
	{
		$code=$result[$i*11];
		$code=substr($code,4,strlen($code)-9);
		$date=$result[$i*11+1];
		$date=substr($date,4,strlen($date)-9);
		$time=$result[$i*11+2];
		$time=substr($time,4,strlen($time)-9);
		$lat=$result[$i*11+3];
        $lat=substr($lat,4,strlen($lat)-9);
		$lon=$result[$i*11+4];
		$lon=substr($lon,4,strlen($lon)-9);
		$depth=$result[$i*11+5];
		$depth=substr($depth,4,strlen($depth)-9);
		$magnitude=$result[$i*11+6];
		$magnitude=substr($magnitude,4,strlen($magnitude)-9);
		$intensity=$result[$i*11+8];
		$intensity=substr($intensity,4,strlen($intensity)-9);
		$place=$result[$i*11+9];
		$place=substr($place,4,strlen($place)-9);
		$place=ucwords(strtolower($place));
		$y=substr($date,6,4);
		$m=substr($date,3,2);
		$d=substr($date,0,2);
		$delta='+'.$hourdiff.' hour';
		$stamp=$y.'-'.$m.'-'.$d.' '.$time;
		$timestamp = strtotime($stamp);
        $stamp2=strtotime($delta,$timestamp);
        $stamp=date('Y-m-d H:i:s',$stamp2);

		$tsunami=-1;
   
		$lat_idx=$lat;
		$lat_idx=(round($lat_idx*10)/10+0.025)*1000;
		if ($lat_idx>90000){
			$lat_idx=90000-25;
		}
		$lon_idx=$lon;
		$lon_idx=(round($lon_idx*10)/10+0.025)*1000;
		if ($lon_idx>180000){
			$lon_idx=$lon_idx-360000;
		}	
		
		$sql=mysql_query("select pop25,pop100,pop200 from worldpop_recent where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
		if (mysql_num_rows($sql)){
			$pop25=mysql_result($sql,0,"pop25");
			$pop100=mysql_result($sql,0,"pop100");
			$pop200=mysql_result($sql,0,"pop200");
		}
		else{
			if ($lon_idx<=-120025){
				$sql=mysql_query("select pop25,pop100,pop200 from worldpop1 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
			}
			else{
				if ($lon_idx<=-60025){
					$sql=mysql_query("select pop25,pop100,pop200 from worldpop2 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
				}
				else{
					if ($lon_idx<=-25){
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop3 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
					}
					else{
						if ($lon_idx<=59975){
							$sql=mysql_query("select pop25,pop100,pop200 from worldpop4 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
						else{
							if ($lon_idx<=119975){
								$sql=mysql_query("select pop25,pop100,pop200 from worldpop5 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
							}
							else{
							$sql=mysql_query("select pop25,pop100,pop200 from worldpop6 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
							}
						}
					}
				}
			}
			if (mysql_num_rows($sql))
			{ 
				$pop25=mysql_result($sql,0,"pop25");
				$pop100=mysql_result($sql,0,"pop100");
				$pop200=mysql_result($sql,0,"pop200");
			}
			else
			{ 
				$pop25=0;
				$pop100=0;
				$pop200=0;
			} 
			$sql=mysql_query("insert into worldpop_recent (latitude,longitude,pop25,pop100,pop200) values ($lat_idx,$lon_idx,$pop25,$pop100,$pop200)");
		}
		$sql=mysql_query("insert into automatic_temp (latitude,longitude,depth,magnitude,pop25,pop100,pop200,place,data,tsunami,provider) values ($lat,$lon,$depth,$magnitude,$pop25,$pop100,$pop200,'$place','$stamp',$tsunami,'IGN')");
	}
}

//EMCS quakes
$url="http://www.emsc-csem.org/Earthquake/Map/earth/kml.php";

$str = file_get_contents($url); 
$xml = simplexml_load_file($url);

date_default_timezone_set("GMT");
$date1=date("Y-m-d H:i:s", time());
date_default_timezone_set("Europe/Rome");
$date2=date("Y-m-d H:i:s", time()); 
$hourdiff = round((strtotime($date2) - strtotime($date1))/3600, 1);

foreach ($xml->Document->Folder as $folder) {
    foreach($folder->Placemark as $place){
	  $description=(string)$place->description;
	  preg_match_all('|-.*?</h2>|',$description,$result);
	  $result=$result[0][0];
	  $result=substr($result,2,strlen($result)-7);
	  $place=ucwords(strtolower($result));
	  
	  preg_match_all('|</h2><br><b>.*?</b>|',$description,$result);
	  $result=$result[0][0];
	  $stamp=substr($result,12,strlen($result)-22);
	  $timestamp = strtotime($stamp);
      $stamp2=strtotime($delta,$timestamp);
      $stamp=date('Y-m-d H:i:s',$stamp2);

	  if($stamp2>strtotime('-7 days'))
	  {
		  preg_match_all('|Latitude</b></td><td style="padding-left:5px;">.*?<br>|',$description,$result);
		  $result=$result[0][0];
		  $lat=substr($result,49,strlen($result)-53);
		  $sign=substr($lat,strlen($lat)-1,1);
		  $lat=substr($lat,0,strlen($lat)-2);
		  if (strcmp($sign,'S')==0){
			$lat=$lat*-1;
		  }
		  
		  preg_match_all('|Longitude</b></td><td style="padding-left:5px;">.*?<br>|',$description,$result);
		  $result=$result[0][0];
		  $lon=substr($result,50,strlen($result)-54);
		  $sign=substr($lon,strlen($lon)-1,1);
		  $lon=substr($lon,0,strlen($lon)-2);
		  if (strcmp($sign,'W')==0){
			$lon=$lon*-1;
		  }
		  
		  preg_match_all('|Deph</b></td><td style="padding-left:5px;">.*?Km|',$description,$result);
		  $result=$result[0][0];
		  $depth=substr($result,45,strlen($result)-50);
		  
		  preg_match_all('|Magnitude</b></td><td style="padding-left:5px;">.*?<br>|',$description,$result);
		  $result=$result[0][0];
		  $magnitude=substr($result,53,strlen($result)-57);
		  
		  $tsunami=-1;
	   
		  $lat_idx=$lat;
		  $lat_idx=(round($lat_idx*10)/10+0.025)*1000;
		  if ($lat_idx>90000){
				$lat_idx=90000-25;
		  }
		  $lon_idx=$lon;
		  $lon_idx=(round($lon_idx*10)/10+0.025)*1000;
		  if ($lon_idx>180000){
				$lon_idx=$lon_idx-360000;
		  }	
			
		  $sql=mysql_query("select pop25,pop100,pop200 from worldpop_recent where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
		  if (mysql_num_rows($sql)){
				$pop25=mysql_result($sql,0,"pop25");
				$pop100=mysql_result($sql,0,"pop100");
				$pop200=mysql_result($sql,0,"pop200");
		  }
		  else{
				if ($lon_idx<=-120025){
					$sql=mysql_query("select pop25,pop100,pop200 from worldpop1 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
				}
				else{
					if ($lon_idx<=-60025){
						$sql=mysql_query("select pop25,pop100,pop200 from worldpop2 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
					}
					else{
						if ($lon_idx<=-25){
							$sql=mysql_query("select pop25,pop100,pop200 from worldpop3 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
						}
						else{
							if ($lon_idx<=59975){
								$sql=mysql_query("select pop25,pop100,pop200 from worldpop4 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
							}
							else{
								if ($lon_idx<=119975){
									$sql=mysql_query("select pop25,pop100,pop200 from worldpop5 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
								}
								else{
								$sql=mysql_query("select pop25,pop100,pop200 from worldpop6 where latitude=$lat_idx and longitude=$lon_idx LIMIT 1");
								}
							}
						}
					}
				}
				if (mysql_num_rows($sql))
				{ 
					$pop25=mysql_result($sql,0,"pop25");
					$pop100=mysql_result($sql,0,"pop100");
					$pop200=mysql_result($sql,0,"pop200");
				}
				else
				{ 
					$pop25=0;
					$pop100=0;
					$pop200=0;
				} 
			$sql=mysql_query("insert into worldpop_recent (latitude,longitude,pop25,pop100,pop200) values ($lat_idx,$lon_idx,$pop25,$pop100,$pop200)");
		}
		$sql=mysql_query("insert into automatic_temp (latitude,longitude,depth,magnitude,pop25,pop100,pop200,place,data,tsunami,provider) values ($lat,$lon,$depth,$magnitude,$pop25,$pop100,$pop200,'$place','$stamp',$tsunami,'EMSC')");
    }
  }  
}

//empty the table
$sql=mysql_query("TRUNCATE automatic");
$sql=mysql_query("INSERT INTO automatic SELECT * FROM automatic_temp");

//extract the new earthquakes
$sql=mysql_query("select latitude,longitude,magnitude,place,provider from automatic_temp where TIMESTAMPDIFF(MINUTE,'$max_date',data)>1");
if (mysql_num_rows($sql))
{
 $num_rows = mysql_num_rows($sql);
 for ($j = 0; $j < $num_rows; $j++) 
 {
  $dataArray = array('latitude'=>mysql_result($sql,$j,"latitude"),
                     'longitude'=>mysql_result($sql,$j,"longitude"),
				     'magnitude'=>mysql_result($sql,$j,"magnitude"),
					 'place'=>mysql_result($sql,$j,"place"),
					 'provider'=>mysql_result($sql,$j,"provider"),
					 'type'=>'official');

  //extract users for notifications
  $sql2=mysql_query("select reg_id from gcm");
  $counter=0;
  $num_rows2 = mysql_num_rows($sql2);
  for ($i = 0; $i < $num_rows2; $i++) 
  {
    $regidArray[$counter]=mysql_result($sql2,$i,"reg_id");
    $counter=$counter+1;
    if ($counter==500 or $i==($num_rows2-1))
    {
     $headers = array( 
                    'Authorization: key=AIzaSyAYaXDEapyd-mF9V9LNW1dmuoS-ea9GwWI',
                    'Content-Type: application/json');
	 $fields = array(
                'registration_ids'  => $regidArray,
                'data'              => $dataArray);				

	 $context = stream_context_create(array(
        'http' => array(
        'method' => 'POST',
        'header' => $headers,
        'content' => json_encode($fields))));
     $response = file_get_contents('https://android.googleapis.com/gcm/send', FALSE, $context);
	 var_dump($response);
	 $counter=0;	 
	 $regidArray=array();
    }
  }
 }
}

$output[]="ok";      
print(json_encode($output));
mysql_close();
?>