Skip to content

Instantly share code, notes, and snippets.

@gscalzo

gscalzo/gist:5928618

Last active December 19, 2015 08:49
Show Gist options
  • Save gscalzo/5928618 to your computer and use it in GitHub Desktop.
Save gscalzo/5928618 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.php
<?php
class Weather extends Common
{
function __construct()
{
}
public function deviceHistoryAggregate($deviceId,$type,$time=null){
switch($type){
case"day":
$day = date("Ymd",$time);
$dataFile = md5($deviceId.$type.$day).".wsdf";
if(
$day < date("Ymd") && //se il giorno richiesto è prima di oggi
(
!is_file(STATIC_ROOT."/".$dataFile) ||
filemtime(STATIC_ROOT."/".$dataFile) < (time()-864000) //10gg
)
) {
$dataOut = new stdClass();
$dataOut->history = array();
$q="SELECT
DATE_FORMAT(FROM_UNIXTIME(timeSent),'%h') AS ora,
DATE_FORMAT(FROM_UNIXTIME(timeSent),'%Y-%m-%d') AS giorno,
AVG(tem) AS tem,
AVG(pre) AS pre,
AVG(umi) AS umi,
AVG(temv) AS temv,
AVG(prev) AS prev,
AVG(umiv) AS umiv
FROM `hist`
WHERE deviceId='".$deviceId."'
AND DATE_FORMAT(FROM_UNIXTIME(timeSent),'%Y%m%d')='".$day."'
GROUP BY deviceId,giorno,ora
ORDER BY giorno,ora";
$res=mysql_query($q);
while($row=mysql_fetch_object($res)){
$bean = new stdClass();
$bean->giorno = $row->giorno;
$bean->ora = $row->ora;
$bean->t = $row->tem;
$bean->p = $row->pre;
$bean->h = $row->umi;
$bean->tv = $row->temv;
$bean->pv = $row->prev;
$bean->hv = $row->umiv;
$dataOut->history[] = $bean;
}
$fp = fopen(STATIC_ROOT."/".$dataFile,"w");
fwrite($fp,json_encode($dataOut));
fclose($fp);
echo json_encode($dataOut);
exit;
} else { //da cache
echo file_get_contents(STATIC_ROOT."/".$dataFile);
exit;
}
break;
case"week":
$week = date("W",$time);
$dataFile = md5($deviceId.$type.$week).".wsdf";
if(
(
!is_file(STATIC_ROOT."/".$dataFile) ||
filemtime(STATIC_ROOT."/".$dataFile) < (time()-3600) //1h
)
) {
$dataOut = new stdClass();
$dataOut->history = array();
$resArray = array();
$q="SELECT
DATE_FORMAT(FROM_UNIXTIME(timeSent),'%Y-%m-%d') AS giorno,
DATE_FORMAT(FROM_UNIXTIME(timeSent),'%h') AS ora,
AVG(tem) AS tem,
AVG(pre) AS pre,
AVG(umi) AS umi,
AVG(temv) AS temv,
AVG(prev) AS prev,
AVG(umiv) AS umiv
FROM `hist`
WHERE deviceId='".$deviceId."'
AND DATE_FORMAT(FROM_UNIXTIME(timeSent),'%u')='".$week."'
#AND DATE_FORMAT(FROM_UNIXTIME(timeSent),'%h') BETWEEN 6 AND 24 #roba
GROUP BY deviceId,giorno,ora
ORDER BY giorno,ora";
$res=mysql_query($q);
while($row=mysql_fetch_object($res)){
switch($row->ora){
case"00":
case"01":
case"02":
case"03":
case"04":
case"05":
case"06":
$fascia=6;
break;
case"07":
case"08":
case"09":
case"10":
case"11":
case"12":
$fascia=12;
break;
case"13":
case"14":
case"15":
case"16":
case"17":
case"18":
$fascia=18;
break;
case"19":
case"20":
case"21":
case"22":
case"23":
$fascia=24;
break;
}
$resArray[$row->giorno][$fascia]["t"]+=$row->tem;
$resArray[$row->giorno][$fascia]["p"]+=$row->pre;
$resArray[$row->giorno][$fascia]["h"]+=$row->umi;
$resArray[$row->giorno][$fascia]["tv"]+=$row->temv;
$resArray[$row->giorno][$fascia]["pv"]+=$row->prev;
$resArray[$row->giorno][$fascia]["hv"]+=$row->umiv;
$resArray[$row->giorno][$fascia]["c"]=$resArray[$row->giorno][$fascia]["c"]!=""?($resArray[$row->giorno][$fascia]["c"]+1):1;
}
foreach($resArray AS $data => $dati){
//print_r($dati);
$bean = new stdClass();
$bean->ora = array();
foreach($dati AS $ora=>$dettagli){
$bean->ora[$ora]["t"] = ($dettagli["t"]/$dettagli["c"]);
$bean->ora[$ora]["p"] = ($dettagli["p"]/$dettagli["c"]);
$bean->ora[$ora]["h"] = ($dettagli["h"]/$dettagli["c"]);
$bean->ora[$ora]["tv"] = ($dettagli["tv"]/$dettagli["c"]);
$bean->ora[$ora]["pv"] = ($dettagli["pv"]/$dettagli["c"]);
$bean->ora[$ora]["hv"] = ($dettagli["hv"]/$dettagli["c"]);
}
$dataOut->history[$data] = $bean;
}
$fp = fopen(STATIC_ROOT."/".$dataFile,"w");
fwrite($fp,json_encode($dataOut));
fclose($fp);
echo json_encode($dataOut);
exit;
} else { //da cache
echo file_get_contents(STATIC_ROOT."/".$dataFile);
exit;
}
break;
case"month":
$month = date("Ym",$time);
$dataFile = md5($deviceId.$type.$month).".wsdf";
if(
(
!is_file(STATIC_ROOT."/".$dataFile) ||
filemtime(STATIC_ROOT."/".$dataFile) < (time()-86400) //1gg
)
) {
$dataOut = new stdClass();
$dataOut->history = array();
$q="SELECT
DATE_FORMAT(FROM_UNIXTIME(timeSent),'%Y-%m-%d') AS giorno,
AVG(tem) AS tem,
AVG(pre) AS pre,
AVG(umi) AS umi,
AVG(temv) AS temv,
AVG(prev) AS prev,
AVG(umiv) AS umiv
FROM `hist`
WHERE deviceId='".$deviceId."'
AND DATE_FORMAT(FROM_UNIXTIME(timeSent),'%Y%m')='".$month."'
AND DATE_FORMAT(FROM_UNIXTIME(timeSent),'%h') BETWEEN 6 AND 24 #roba
GROUP BY deviceId,giorno
ORDER BY giorno";
$res=mysql_query($q);
while($row=mysql_fetch_object($res)){
$bean = new stdClass();
$bean->giorno = $row->giorno;
$bean->t = $row->tem;
$bean->p = $row->pre;
$bean->h = $row->umi;
$bean->tv = $row->temv;
$bean->pv = $row->prev;
$bean->hv = $row->umiv;
$dataOut->history[] = $bean;
}
$fp = fopen(STATIC_ROOT."/".$dataFile,"w");
fwrite($fp,json_encode($dataOut));
fclose($fp);
echo json_encode($dataOut);
exit;
} else { //da cache
echo file_get_contents(STATIC_ROOT."/".$dataFile);
exit;
}
break;
}
}
public function deviceHistory($deviceId,$timeFrom,$timeTo=null){
$start=microtime(true);
$dataOut=new stdClass();
$dataOut->history = array();
if($timeTo==""){
$timeTo=time();
}
$q="SELECT * FROM hist
WHERE deviceId='".$deviceId."'
AND timeSent BETWEEN ".$timeFrom." AND ".$timeTo."
ORDER BY timeSent ASC";
$res = mysql_query($q);
while($row=mysql_fetch_object($res)){
$bean=new stdClass();
$bean->ts=$row->timeSent;
$bean->lat=$row->lat;
$bean->lon=$row->lon;
$bean->t=$row->tem;
$bean->p=$row->pre;
$bean->h=$row->umi;
$bean->tv=$row->temv;
$bean->pv=$row->prev;
$bean->hv=$row->umiv;
$dataOut->history[] = $bean;
}
$stop=microtime(true);
//$dataOut->time_taken = ($stop-$start);
echo json_encode($dataOut);
exit;
}
public function mapForecast($l,$r,$t,$b){
set_time_limit(0);
ini_set("display_errors",false);
DEFINE("WEATHER_HIRES",false);
//estrazione lrtb su geospots
$area = $_SESSION["Weather"]->toArea($l,$r,$t,$b);
if(WEATHER_HIRES==true){
if($area>=500){ //città principali type=1
$qAdd = " pop > 50000 ";
}else if($area<500 && $area>=250){ //città principali type=1
$qAdd = " pop > 20000 ";
}else if($area<250 && $area>=125) { //città type = 2
$qAdd = " pop > 10000 ";
}else if($area<125 && $area>=62.5){ //città type=3
$qAdd = " pop > 5000 ";
}else if($area<62.5 && $area>=30){ //direct
$qAdd = " pop > 3000 ";
} else if($area<30 && $area>=15){ //direct
$qAdd = " pop > 2000 ";
} else if($area<15 && $area>=8){ //direct
$qAdd = " pop > 1000 ";
} else{
$qAdd = " pop > 500 ";
}
} else {
if($area>=1000){ //città principali type=1
$qAdd = " abstractType<2 ";
}else if($area<1000 && $area>=500){ //città principali type=1
$qAdd = " abstractType<2 ";
}else if($area<500 && $area>=125) { //città type = 2
$qAdd = " abstractType<2 ";
}else if($area<125){ //città type=3
$qAdd = " abstractType<=3 ";
}
}
//language
$customLanguage=false;
if($_GET["i"]!=""){
$isoArrayToDb = array("it"=>1,"en"=>2,"de"=>3,"fr"=>4,"es"=>5);
foreach($isoArrayToDb AS $key=>$value){
if($_GET["i"]==$key){
$customLanguageId=$value;
$customLanguage=true;
}
}
}
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y,city,country,id,pop
FROM geospots_1000
WHERE
$qAdd
AND
( X(geoPoint) BETWEEN ".($l<$r?$l:$r)." AND ".($r>$l?$r:$l)." ) AND
( Y(geoPoint) BETWEEN ".($t<$b?$t:$b)." AND ".($b>$t?$b:$t)." )
ORDER BY pop DESC LIMIT 30";
//echo $sql;
$start = microtime(true);
$res = mysql_query($sql);
$stop = microtime(true);
//$dataOut->query = $stop-$start;
$spotsFound = array();
$locations = array();
$start = microtime(true);
while($row=mysql_fetch_object($res)){
$templat=$_SESSION["Weather"]->decimalTruncate($row->X,2);
$templon=$_SESSION["Weather"]->decimalTruncate($row->Y,2);
if($customLanguage==true){
$tempn=$_SESSION["Weather"]->languagedLabel($row->id,$customLanguageId,$row->city);
} else {
$tempn=$row->city;
}
$key = $templat."|".$templon;
if(!array_key_exists($key,$spotsFound)){
$spotsFound[$key] = array("label"=>$tempn);
$locations[] = $tempn.", ".$row->country." ".$row->pop;
}
}
/*
echo "<pre>";
print_r($spotsFound);
echo "</pre>";
*/
$dataOut = new stdClass();
//$dataOut->locations = $locations;
$dataOut->spots = array();
foreach($spotsFound AS $key => &$spot){
$latlon=explode("|",$key);
$lat = $latlon[0];
$lon = $latlon[1];
$location = new stdClass();
$location->lat = $lat;
$location->lon = $lon;
$location->label = $spot["label"];
$location->forecast = $_SESSION["Weather"]->forecast($lat,$lon,true);
$dataOut->spots[] = $location;
}
//echo "<pre>";
//print_r($dataOut);
echo json_encode($dataOut);
//echo "</pre>";
exit;
/*
echo "<pre>";
print_r($dataOut);
echo "</pre>";
*/
}
public function forecast($lat,$lon,$return=false){
ini_set("display_errors",false);
//variabili
$lat = $_SESSION["Weather"]->decimalTruncate($lat,2);
$lon = $_SESSION["Weather"]->decimalTruncate($lon,2);
if(substr($lat,strlen($lat)-1,1)=="0"){
$lat = $_SESSION["Weather"]->decimalTruncate($lat,1);
}
if(substr($lon,strlen($lon)-1,1)=="0"){
$lon = $_SESSION["Weather"]->decimalTruncate($lon,1);
}
if(substr($lat,strlen($lat)-1,1)=="0"){
$lat = substr($lat,0,strlen($lat)-2);
}
if(substr($lon,strlen($lon)-1,1)=="0"){
$lon = substr($llon,0,strlen($lon)-2);
}
//cache
$data1gg = date("Ymd",mktime(0,0,0,date("m"),date("d"),date("Y")));
$data2gg = date("Ymd",mktime(0,0,0,date("m"),date("d")+1,date("Y")));
$data3gg = date("Ymd",mktime(0,0,0,date("m"),date("d")+2,date("Y")));
$data4gg = date("Ymd",mktime(0,0,0,date("m"),date("d")+3,date("Y")));
$data5gg = date("Ymd",mktime(0,0,0,date("m"),date("d")+4,date("Y")));
$dataOut = new stdClass();
$dataOut->weatherBeans = array();
//..su db
//echo "SELECT COUNT(1) AS qty FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity ='".$data5gg."'";
//echo "<br/>";
//echo "<br/>";
$row = mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity ='".$data5gg."'"));
if($row->qty>0){ //ha tutti i 5 giorni!!
//echo "ALL DAYS";
//echo "<br/>";
//echo "SELECT * FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity >= ".$data1gg." ORDER BY validity";
//echo "<br/>";
$res = mysql_query("SELECT * FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity >= ".$data1gg." ORDER BY validity");
while($row=mysql_fetch_object($res)){
$bean = new stdClass();
$bean->lat=$row->lat;
$bean->lon=$row->lon;
$bean->date=substr($row->validity,0,4)."-".substr($row->validity,4,2)."-".substr($row->validity,6,2);
//$bean->precipMM=$row->precipMM;
$bean->p=$row->precipMM;
//$bean->tempMaxC=$row->tempMaxC;
$bean->tma=$row->tempMaxC;
//$bean->tempMaxF=$row->tempMaxF;
//$bean->tMaF=$row->tempMaxF;
//$bean->tempMinC=$row->tempMinC;
$bean->tmi=$row->tempMinC;
//$bean->tempMinF=$row->tempMinF;
//$bean->winddir16Point=$row->winddir16Point;
//$bean->winddirDegree=$row->winddirDegree;
$bean->wd=$row->winddirDegree;
//$bean->winddirection=$row->winddirection;
$bean->wdr=$row->winddirection;
//$bean->windspeedKmph=$row->windspeedKmph;
$bean->wkh=$row->windspeedKmph;
//$bean->windspeedMiles=$row->windspeedMiles;
//$bean->weatherCode=$row->weatherCode;
$bean->wc=$row->weatherCode;
//$bean->weatherIconUrl=$row->weatherIconUrl;
//$bean->i=$row->weatherIconUrl;
//$bean->weatherDesc=$row->weatherDesc;
//sun
$sun_info = date_sun_info(strtotime($bean->date),$bean->lat,$bean->lon);
$bean->sr = date("H:i:s",$sun_info["sunrise"]);
$bean->ss = date("H:i:s",$sun_info["sunset"]);
//$bean->stc = ($sun_info["sunset"]-$sun_info["sunrise"]);
$stc = ($sun_info["sunset"]-$sun_info["sunrise"]);
//$bean->st = $sun_info["transit"];
$bean->stt = $_SESSION["Weather"]->toTimeDiff($stc);
$dataOut->weatherBeans[] = $bean;
}
if($return==true){
return $dataOut;
}
echo json_encode($dataOut);
exit;
} else {
//recupero 5 gg
$json = json_decode(file_get_contents("http://free.worldweatheronline.com/feed/weather.ashx?q=".$lat.",".$lon."&format=json&num_of_days=5&key=7bf3b38ee1135411122510"));
//echo "<pre>";
//print_r($json);
foreach($json->data->weather AS $weatherBean){
//print_r($weatherBean);
$validity = str_replace("-","",$weatherBean->date);
$precipMM = $weatherBean->precipMM;
$tempMaxC = $weatherBean->tempMaxC;
$tempMaxF = $weatherBean->tempMaxF;
$tempMinC = $weatherBean->tempMinC;
$tempMinF = $weatherBean->tempMinF;
$winddir16Point = $weatherBean->winddir16Point;
$winddirDegree = $weatherBean->winddirDegree;
$winddirection = $weatherBean->winddirection;
$windspeedKmph = $weatherBean->windspeedKmph;
$windspeedMiles = $weatherBean->windspeedMiles;
$weatherCode = $weatherBean->weatherCode;
$weatherIconUrl = $weatherBean->weatherIconUrl[0]->value;
$weatherDesc = $weatherBean->weatherDesc[0]->value;
//insert o update?
$row=mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity='".$validity."'"));
if($row->qty==0) {
$q="INSERT INTO forecast SET
lat='".$lat."',
lon='".$lon."',
validity='".$validity."',
precipMM = '".$precipMM."',
tempMaxC = '".$tempMaxC."',
tempMaxF = '".$tempMaxF."',
tempMinC = '".$tempMinC."',
tempMinF = '".$tempMinF."',
winddir16Point = '".$winddir16Point."',
winddirDegree = '".$winddirDegree."',
winddirection = '".$winddirection."',
windspeedKmph = '".$windspeedKmph."',
windspeedMiles = '".$windspeedMiles."',
weatherCode = '".$weatherCode."',
weatherIconUrl = \"".mysql_real_escape_string($weatherIconUrl)."\",
weatherDesc = \"".mysql_real_escape_string($weatherDesc)."\",
lastUpdate=".time();
//echo $q;
mysql_query($q);
}else{
$q="UPDATE forecast SET
precipMM = '".$precipMM."',
tempMaxC = '".$tempMaxC."',
tempMaxF = '".$tempMaxF."',
tempMinC = '".$tempMinC."',
tempMinF = '".$tempMinF."',
winddir16Point = '".$winddir16Point."',
winddirDegree = '".$winddirDegree."',
winddirection = '".$winddirection."',
windspeedKmph = '".$windspeedKmph."',
windspeedMiles = '".$windspeedMiles."',
weatherCode = '".$weatherCode."',
weatherIconUrl = \"".mysql_real_escape_string($weatherIconUrl)."\",
weatherDesc = \"".mysql_real_escape_string($weatherDesc)."\",
lastUpdate=".time()."
WHERE lat='".$lat."'
AND lon='".$lon."'
AND validity='".$validity."'";
//echo $q;
mysql_query($q);
}
}
//echo "</pre>";
//recupero da db
$res = mysql_query("SELECT * FROM forecast WHERE lat = '".$lat."' AND lon = '".$lon."' AND validity >= ".$data1gg." ORDER BY validity");
while($row=mysql_fetch_object($res)){
$bean = new stdClass();
$bean->lat=$row->lat;
$bean->lon=$row->lon;
$bean->date=substr($row->validity,0,4)."-".substr($row->validity,4,2)."-".substr($row->validity,6,2);
//$bean->precipMM=$row->precipMM;
$bean->p=$row->precipMM;
//$bean->tempMaxC=$row->tempMaxC;
$bean->tma=$row->tempMaxC;
//$bean->tempMaxF=$row->tempMaxF;
//$bean->tMaF=$row->tempMaxF;
//$bean->tempMinC=$row->tempMinC;
$bean->tmi=$row->tempMinC;
//$bean->tempMinF=$row->tempMinF;
//$bean->winddir16Point=$row->winddir16Point;
//$bean->winddirDegree=$row->winddirDegree;
$bean->wd=$row->winddirDegree;
//$bean->winddirection=$row->winddirection;
$bean->wdr=$row->winddirection;
//$bean->windspeedKmph=$row->windspeedKmph;
$bean->wkh=$row->windspeedKmph;
//$bean->windspeedMiles=$row->windspeedMiles;
//$bean->weatherCode=$row->weatherCode;
$bean->wc=$row->weatherCode;
//$bean->weatherIconUrl=$row->weatherIconUrl;
//$bean->i=$row->weatherIconUrl;
//$bean->weatherDesc=$row->weatherDesc;
//sun
$sun_info = date_sun_info(strtotime($bean->date),$bean->lat,$bean->lon);
$bean->sr = date("H:i:s",$sun_info["sunrise"]);
$bean->ss = date("H:i:s",$sun_info["sunset"]);
//$bean->stc = ($sun_info["sunset"]-$sun_info["sunrise"]);
$stc = ($sun_info["sunset"]-$sun_info["sunrise"]);
//$bean->st = $sun_info["transit"];
$bean->stt = $_SESSION["Weather"]->toTimeDiff($stc);
$dataOut->weatherBeans[] = $bean;
}
if($return==true){
return $dataOut;
}
echo json_encode($dataOut);
exit;
}
}
public function toTimeDiff($sec){
$ore = floor(($sec/3600));
$resto = $sec - ($ore*3600);
$minuti = floor(($resto/60));
$resto = $resto - ($minuti*60);
return $ore."h ".$minuti."m ".$resto."s";
}
function calculateDistance($lat1,$lon1,$lat2,$lon2){
$Radius = 6371.00;
$dLat = $this->toRadians($lat2-$lat1);
$dLon = $this->toRadians($lon2-$lon1);
$a = sin($dLat/2) * sin($dLat/2) + cos($this->toRadians($lat1)) * cos($this->toRadians($lat2)) * sin($dLon/2) * sin($dLon/2);
$c = 2 * asin(sqrt($a));
return $Radius * $c;
}
function toRadians($degree){
$res = $degree * 3.1415926 / 180;
return $res;
}
/*{
"deviceId": "xxxx",
"data":[
{
"temperature": "xx",
"humidity": "xx",
"pressure": "xx",
"lat": "xx",
"long": "xx",
"timestamp": "xx"
},
...
{
"temperature": "xx",
"humidity": "xx",
*
*
*/
public function postData(){
$json = file_get_contents('php://input');
$rawData = json_decode($json);
/*
$conn = new Mongo("mongodb://localhost");
$db = $conn->weatherstation;
$table = $db->hist;
*/
if($rawData->deviceId!=""){
$tempDataArray = array();
$c=0;
foreach($rawData->data AS $dataBean){
$tempDataArray[$c]["temperature"] = $dataBean->temperature;
$tempDataArray[$c]["humidity"] = $dataBean->humidity;
$tempDataArray[$c]["pressure"] = $dataBean->pressure;
$tempDataArray[$c]["temperaturev"] = $dataBean->temperaturev;
$tempDataArray[$c]["humidityv"] = $dataBean->humidityv;
$tempDataArray[$c]["pressurev"] = $dataBean->pressurev;
$tempDataArray[$c]["lat"] = $dataBean->lat;
$tempDataArray[$c]["long"] = $dataBean->long;
$tempDataArray[$c]["timestamp"] = $dataBean->timestamp;
$tempDataArray[$c]["icon"] = $dataBean->icon;
$tempDataArray[$c]["deviceLocation"] = $dataBean->deviceLocation;
/*
* country e provincia vengono riconosciuti qui
* per non far diventare devastante l'aggregazione
$q="SELECT country,provincia,
3956 * 2 * ASIN(SQRT( POWER(SIN((".$dataBean->lat." -abs( X(geoPoint) )) * pi()/180 / 2),2) + COS(".$dataBean->lat." * pi()/180 ) * COS( abs (X(geoPoint)) * pi()/180) * POWER(SIN((".$dataBean->long." - Y(geoPoint)) * pi()/180 / 2), 2) )) as distance
FROM geospots ORDER BY distance limit 1";
$row=mysql_fetch_object(mysql_query($q));
$tempDataArray[$c]["country"] = $row->country;
$tempDataArray[$c]["provincia"] = $row->provincia;
*/
$c++;
}
//riordino in base al timestamp
$orderedArray = $_SESSION["Common"]->riordina($tempDataArray,"timestamp","ASC");
//archivio
foreach($orderedArray AS $tupla){
$sql = "INSERT INTO `hist` SET
deviceId='".$rawData->deviceId."',
geoPoint=(GeomFromText('POINT(".$tupla["lat"]." ".$tupla["long"].")')),
timeSent = ".$tupla["timestamp"].",
lat=".$tupla["lat"].",
lon=".$tupla["long"].",
icon=".$tupla["icon"].",
location=\"".mysql_real_escape_string($tupla["deviceLocation"])."\",
temv=".$tupla["temperaturev"].",
prev=".$tupla["pressurev"].",
umiv=".$tupla["humidityv"].",
tem=".$tupla["temperature"].",
pre=".$tupla["pressure"].",
umi=".$tupla["humidity"];
@mysql_query($sql);
/*
//insert mongo
$u->deviceId = utf8_encode($rawData->deviceId);
$u->timeSent = $tupla["timestamp"];
$u->geoPoint = array("lat"=>(float)$tupla["lat"],"lon"=>(float)$tupla["long"]);
$u->lat = (float)$tupla["lat"];
$u->lon = (float)$tupla["long"];
$u->tem = (float)$tupla["temperature"];
$u->pre = (float)$tupla["pressure"];
$u->umi = (float)$tupla["humidity"];
$u->temv = $tupla["temperaturev"];
$u->prev = $tupla["pressurev"];
$u->umiv = $tupla["humidityv"];
@$table->insert($u);
*/
}
//snapshot
$orderedArray = $_SESSION["Common"]->riordina($tempDataArray,"timestamp","DESC");
$row = mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM `last` WHERE deviceId='".$rawData->deviceId."'"));
if($row->qty==0){
$sql = "INSERT INTO `last` SET
timeSent=".$orderedArray[0]["timestamp"].",
deviceId='".$rawData->deviceId."',
tem=".$orderedArray[0]["temperature"].",
pre=".$orderedArray[0]["pressure"].",
umi=".$orderedArray[0]["humidity"].",
temv=".$orderedArray[0]["temperaturev"].",
prev=".$orderedArray[0]["pressurev"].",
umiv=".$orderedArray[0]["humidityv"].",
icon=".$orderedArray[0]["icon"].",
location=\"".mysql_real_escape_string($orderedArray[0]["deviceLocation"])."\",
geoPoint = ( GeomFromText( 'POINT(".$orderedArray[0]["lat"]." ".$orderedArray[0]["long"].") ' ) )";
} else {
$sql = "UPDATE `last` SET
timeSent=".$orderedArray[0]["timestamp"].",
tem=".$orderedArray[0]["temperature"].",
pre=".$orderedArray[0]["pressure"].",
umi=".$orderedArray[0]["humidity"].",
temv=".$orderedArray[0]["temperaturev"].",
prev=".$orderedArray[0]["pressurev"].",
umiv=".$orderedArray[0]["humidityv"].",
icon=".$orderedArray[0]["icon"].",
location=\"".mysql_real_escape_string($orderedArray[0]["deviceLocation"])."\",
geoPoint = ( GeomFromText( 'POINT(".$orderedArray[0]["lat"]." ".$orderedArray[0]["long"].") ' ) )
WHERE deviceId='".$rawData->deviceId."'";
}
@mysql_query($sql);
$headers = 'From: [email protected]' . "\r\n" .
'Reply-To: [email protected]' . "\r\n" .
'X-Mailer: PHP/' . phpversion();
@mail("[email protected]","ws point",$json."<hr>".json_encode($orderedArray)."<hr>".$sql,$headers);
header("Status: 200 OK");
exit;
}else{
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
}
public function deviceName($deviceId,$stationName){
$row = mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM deviceNames WHERE deviceId='".$deviceId."'"));
if($row->qty>0){
if(mysql_query("UPDATE deviceNames SET label=\"". mysql_real_escape_string($stationName)."\" WHERE deviceId='".$deviceId."'")){
header($_SERVER['SERVER_PROTOCOL'] . ' 200 OK', true, 200);
exit;
} else {
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
}else{
if(mysql_query("INSERT INTO deviceNames SET deviceId='".$deviceId."',label=\"". mysql_real_escape_string($stationName)."\"")){
header($_SERVER['SERVER_PROTOCOL'] . ' 200 OK', true, 200);
exit;
}else{
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
}
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
public function deviceNameReset($deviceId){
$row = mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM deviceNames WHERE deviceId='".$deviceId."'"));
if($row->qty>0){
if(mysql_query("DELETE FROM deviceNames WHERE deviceId='".$deviceId."'")){
header($_SERVER['SERVER_PROTOCOL'] . ' 200 OK', true, 200);
exit;
} else {
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
}else{
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
exit;
}
public function toDeviceLabel($deviceId){
$row = mysql_fetch_object(mysql_query("SELECT COUNT(1) AS qty FROM deviceNames WHERE deviceId='".$deviceId."'"));
if($row->qty>0){
$row=mysql_fetch_object(mysql_query("SELECT label FROM deviceNames WHERE deviceId='".$deviceId."'"));
return $row->label;
}else{
return "Ws";
}
}
public function getData(){
getDataAndClusters();
}
/*
* la getData
* si occupa di richiedere dati dalla snapshot o dall'archivio
*/
public function getData(){
set_time_limit(0);
$dataOut=new stdClass();
$time_discriminante = time() - 3600*24*7*4;
//$lat = $_GET["lat"];
//$long = $_GET["long"];
/*
//in che luogo sono
$q="SELECT
city,
country,
provincia,
id,
((GLength(
LineStringFromWKB(
LineString(
geoPoint,
GeomFromText('POINT(".$lat." ".$long.")')
)
)
)) * 100)
AS distance
FROM geospots
WHERE country='IT'
GROUP BY distance
ORDER BY distance ASC
LIMIT 1";
$res = mysql_query($q);
$row = mysql_fetch_object($res);
$luogoid = $row->id;
$dataOut->lu = $row->city;
$dataOut->luogoprovincia = $row->provincia;
$dataOut->ld = $row->distance;
//provincia
$q="SELECT id,city,X(geoPoint) AS X,Y(geoPoint) AS Y FROM geospots WHERE country='".$row->country."' AND provincia='".$row->provincia."' ORDER BY pop DESC LIMIT 1";
$res = mysql_query($q);
$row = mysql_fetch_object($res);
$provinciaid = $row->id;
$dataOut->pu = $row->city;
$dataOut->dp = $this->calculateDistance($lat,$long,$row->X,$row->Y);
*/
if(isset($_GET["timestamp"]) && $_GET["timestamp"]!=""){
$table = "hist";
$firstTs = $_GET["timestamp"] - (60*10);
$lastTs = $_GET["timestamp"];
} else {
$table = "last";
}
$start = microtime(true);
$area = $_SESSION["Weather"]->toArea($_GET["l"],$_GET["r"],$_GET["t"],$_GET["b"]);
$stop = microtime(true);
//$dataOut->toarea = $stop-$start;
//$dataOut->area = $area;
$decimali = 2;
if($area>=1000){
$table = "aggregatogeo";
$type = 1;
//$qAdd = " AND pop > 200000 ";
}else if($area>=500 && $area<1000){ //città principali type=1
$table = "aggregatogeo";
$type = 1;
//$qAdd = " AND pop > 100000 ";
}else if($area<500 && $area>=250){ //città principali type=1
$table = "aggregatogeo";
$type = 1;
//$qAdd = " AND pop > 20000 ";
}else if($area<250 && $area>=125) { //città type = 2
$table = "aggregatogeo";
//$qAdd = " AND pop > 5000 ";
$type = 1;
}else if($area<125 && $area>=62.5){ //città type=3
$table = "aggregatogeo";
$type = 1;
}else if($area<62.5 && $area>=30){ //direct
$table = "aggregatogeo";
$type = 2;
//$qAdd = " AND pop > 2000 ";
} else if($area<30 && $area>=15){ //direct
$table = "aggregatogeo";
$type = 3;
//$qAdd = " AND pop > 2000 ";
} else if($area<15 && $area>=8){ //direct
$table = "aggregato";
$type = 3;
//$qAdd = " AND pop > 1000 ";
} else{
$table = "aggregato";
}
//language
$customLanguage=false;
if($_GET["i"]!=""){
$isoArrayToDb = array("it"=>1,"en"=>2,"de"=>3,"fr"=>4,"es"=>5);
foreach($isoArrayToDb AS $key=>$value){
if($_GET["i"]==$key){
$customLanguageId=$value;
$customLanguage=true;
}
}
}
switch($table){
case"aggregatogeo":
$l = $_SESSION["Weather"]->decimalTruncate($_GET["l"],3);
$r = $_SESSION["Weather"]->decimalTruncate($_GET["r"],3);
$b = $_SESSION["Weather"]->decimalTruncate($_GET["b"],3);
$t = $_SESSION["Weather"]->decimalTruncate($_GET["t"],3);
$l = $_GET["l"];
$r = $_GET["r"];
$b = $_GET["b"];
$t = $_GET["t"];
//da aggregato geo
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y, t, u, p,tv,uv,pv,ts,spots,label,locId
FROM aggregatogeo
WHERE
type=".$type."
$qAdd
AND ts >= ".$time_discriminante."
AND
( X(geoPoint) BETWEEN ".($l<$r?$l:$r)." AND ".($r>$l?$r:$l)." ) AND
( Y(geoPoint) BETWEEN ".($t<$b?$t:$b)." AND ".($b>$t?$b:$t)." )";
$s1=microtime(true);
$res = mysql_query($sql);
$s2=microtime(true);
//$dataOut->querytime = ($s2-$s1);
$spotsFound = array();
$start = microtime(true);
while($row=mysql_fetch_object($res)){
$temp = new stdClass();
$temp->lat=$row->X;
$temp->lon=$row->Y;
$temp->t=$row->t;
$temp->p=$row->p;
$temp->h=$row->u;
$temp->tv=$row->tv;
$temp->pv=$row->pv;
$temp->hv=$row->uv;
$temp->ts=$row->ts;
$temp->l=$row->spots;
if($customLanguage==true){
$temp->n=$_SESSION["Weather"]->languagedLabel($row->locId,$customLanguageId,$row->label);
} else {
$temp->n=$row->label;
}
//$temp->pop=$row->pop;
$spotsFound[] = $temp;
}
$stop = microtime(true);
//$dataOut->loop = ($stop-$start);
//$dataOut->table=$table;
//$dataOut->set = sizeof($spotsFound);
$dataOut->spots = $spotsFound;
/*area forecast
$areaForecastFound = false;
$args=array("=1","<=2","<=3");
$c=0;
while($areaForecastFound == false){
//area forecast
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y
FROM geoSpots
WHERE
abstractType ".$args[$c]."
AND
( X(geoPoint) BETWEEN ".($l<$r?$l:$r)." AND ".($r>$l?$r:$l)." ) AND
( Y(geoPoint) BETWEEN ".($t<$b?$t:$b)." AND ".($b>$t?$b:$t)." )
ORDER BY pop DESC limit 1";
$rowAF = @mysql_fetch_object(mysql_query($sql));
if($rowAF->X != ""){
$forecast = $_SESSION["Weather"]->forecast($rowAF->X,$rowAF->Y,true);
$bean = $forecast->weatherBeans[0];
$dataOut->wc = $bean->wc;
$dataOut->sr = $bean->sr;
$dataOut->ss = $bean->ss;
//print_r($forecast);
$areaForecastFound = true;
break;
}
$c++;
}
*/
echo json_encode($dataOut);
exit;
break;
case"aggregato":
$l = $_SESSION["Weather"]->decimalTruncate($_GET["l"],3);
$r = $_SESSION["Weather"]->decimalTruncate($_GET["r"],3);
$b = $_SESSION["Weather"]->decimalTruncate($_GET["b"],3);
$t = $_SESSION["Weather"]->decimalTruncate($_GET["t"],3);
$l = $_GET["l"];
$r = $_GET["r"];
$b = $_GET["b"];
$t = $_GET["t"];
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y, tem, umi, pre,temv,prev,umiv, deviceId,icon,location,timeSent
FROM last
WHERE
( X(geoPoint) BETWEEN ".($l<$r?$l:$r)." AND ".($r>$l?$r:$l)." ) AND
( Y(geoPoint) BETWEEN ".($t<$b?$t:$b)." AND ".($b>$t?$b:$t)." )
AND timeSent >= ".$time_discriminante;
$s1=microtime(true);
$res = mysql_query($sql);
$s2=microtime(true);
//$dataOut->querytime = ($s2-$s1);
$spotsFound = array();
while($row=mysql_fetch_object($res)){
$temp = new stdClass();
$temp->lat=$row->X;
$temp->lon=$row->Y;
$temp->t=$row->tem;
$temp->p=$row->pre;
$temp->h=$row->umi;
$temp->tv=$row->temv;
$temp->pv=$row->prev;
$temp->hv=$row->umiv;
$temp->l=1;
$temp->n = $_SESSION["Weather"]->toDeviceLabel($row->deviceId);
$temp->d = $row->deviceId;
$temp->ts = $row->timeSent;
$temp->icon = $row->icon;
$temp->location = $row->location;
$spotsFound[] = $temp;
}
//$dataOut->query = $sql;
//$dataOut->table=$table;
//$dataOut->set = sizeof($spotsFound);
$dataOut->spots = $spotsFound;
/*area forecast
$areaForecastFound = false;
$args=array("=1","<=2","<=3");
$c=0;
while($areaForecastFound == false){
//area forecast
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y
FROM geoSpots
WHERE
abstractType ".$args[$c]."
AND
( X(geoPoint) BETWEEN ".($l<$r?$l:$r)." AND ".($r>$l?$r:$l)." ) AND
( Y(geoPoint) BETWEEN ".($t<$b?$t:$b)." AND ".($b>$t?$b:$t)." )
ORDER BY pop DESC limit 1";
$rowAF = @mysql_fetch_object(mysql_query($sql));
if($rowAF->X != ""){
$forecast = $_SESSION["Weather"]->forecast($rowAF->X,$rowAF->Y,true);
$bean = $forecast->weatherBeans[0];
$dataOut->wc = $bean->wc;
$dataOut->sr = $bean->sr;
$dataOut->ss = $bean->ss;
//print_r($forecast);
$areaForecastFound = true;
break;
}
$c++;
}
*/
echo json_encode($dataOut);
exit;
break;
}
/*
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y, tem, umi, pre
FROM ".$table."
WHERE MBRContains
(
LineString (
Point
(
".$lat." + ".$radius." / 111.1,
".$long." + ".$radius." / ( 111.1 / COS(RADIANS(".$lat.")))
) ,
Point
(
".$lat." - ".$radius." / 111.1,
".$lat." - ".$radius." / ( 111.1 / COS(RADIANS(".$lat.")))
)
), geoPoint ) ";
if($firstTs!="" && $lastTs!=""){
$sql.="AND timeSent BETWEEN (".$firstTs." AND ".$lastTs.") ";
$sql.="GROUP BY deviceId ORDER BY id ";
}
*/
//echo $sql;
}
public function languagedLabel($locId,$customLanguageId,$label){
$row=@mysql_fetch_object(mysql_query("SELECT label FROM labels WHERE rid=".$locId." AND lid=".$customLanguageId));
return ($row->label!=""?$row->label:$label);
}
public function aggrega(){
ini_set("display_errors","true");
set_time_limit(0);
$startEXQ = time();
$decimals = array(1,2,3,4);
foreach($decimals AS $decimali){
$sql = "SELECT X(geoPoint) AS X, Y(geoPoint) AS Y, tem, umi, pre FROM `last`";
echo $sql."<br/>";
$dataSet = array();
$lats = array();
$longs = array();
$merges = array();
$c=0;
$tAverage=0;
$hAverage=0;
$pAverage=0;
$res=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_object($res)){
$dataSet[$c]["temperature"] = $row->tem;
$dataSet[$c]["humidity"] = $row->umi;
$dataSet[$c]["pressure"] = $row->pre;
$dataSet[$c]["lat"] = $row->X;
$dataSet[$c]["long"] = $row->Y;
$dataSet[$c]["lat_norm"] = $_SESSION["Weather"]->decimalTruncate($row->X,$decimali);
$dataSet[$c]["long_norm"] = $_SESSION["Weather"]->decimalTruncate($row->Y,$decimali);
$llk = $dataSet[$c]["lat_norm"]."-".$dataSet[$c]["long_norm"];
$merges[ "$llk" ]["count"] = $merges[ "$llk" ]["count"]+1;
$merges[ "$llk" ]["avglat"]+=$row->X;
$merges[ "$llk" ]["avglong"]+=$row->Y;
$merges[ "$llk" ]["avgt"]+=$row->tem;
$merges[ "$llk" ]["avgp"]+=$row->pre;
$merges[ "$llk" ]["avgh"]+=$row->umi;
$c++;
}
$merges_ordered = $_SESSION["Common"]->riordina($merges,"count","DESC");
$c=1;
$spotsFound = array();
foreach($merges_ordered AS $key=>$value){
$spotsFound[] = $_SESSION["Weather"]->averageFromMerge($value);
}
//insert
mysql_query("DELETE FROM aggregato WHERE decimali=".$decimali);
foreach($spotsFound AS $spot){
$sql = "INSERT INTO aggregato SET
decimali=".$decimali.",
spots=".$spot->l.",
geoPoint=(GeomFromText('POINT(".str_replace(",",".",$spot->lat)." ".str_replace(",",".",$spot->lon).")')),
t='".$spot->t."',
u='".$spot->h."',
p='".$spot->p."'";
mysql_query($sql) or die(mysql_error()." ".$sql);
}
}
$stopEXQ = time();
$extime=($stopEXQ-$startEXQ);
echo "Aggregazione eseguita in ".$extime." secondi";
}
public function distance($lat1, $lon1, $lat2, $lon2) {
$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
$dist = ($miles * 1.609344);
$kdist = round($dist,4);
return $kdist;
}
public function toArea($l,$r,$t,$b){
$larghezza = $_SESSION["Weather"]->distance($t,$l,$t,$r);
$altezza = $_SESSION["Weather"]->distance($t,$l,$b,$l);
$area = sqrt( $larghezza*$altezza );
//return $larghezza;
return $area;
}
public function decimalTruncate($value,$digits){
$int = intval($value);
$temp = @explode(".",$value);
$rest = substr($temp[1],0,$digits);
return $int.".".$rest;
}
public function averages($lat,$long,$dataSet){
$dataOut = new stdClass();
$latSize = strlen($lat);
$longSize = strlen($long);
$matches=0;
$avgLat=0;
$avgLong=0;
$avgTemp=0;
$avgPress=0;
$avgHumi=0;
foreach($dataSet AS $key=>$value){
if(
stristr($value["lat"],$lat) &&
stristr($value["long"],$long)
){
$avgLat+=$value["lat"];
$avgLong+=$value["long"];
$avgTemp+=$value["temperature"];
$avgPress+=$value["pressure"];
$avgHumi+=$value["humidity"];
$matches++;
}
}
$dataOut->lat=$avgLat/$matches;
$dataOut->lon=$avgLong/$matches;
$dataOut->t=round(($avgTemp/$matches),1);
$dataOut->p=intval(($avgPress/$matches));
$dataOut->h=round(($avgHumi/$matches),1);
return $dataOut;
}
public function averageFromMerge($value){
$dataOut = new stdClass();
$dataOut->lat=$value["avglat"]/$value["count"];
$dataOut->lon=$value["avglong"]/$value["count"];
$dataOut->t=round(($value["avgt"]/$value["count"]),1);
$dataOut->p=intval(($value["avgp"]/$value["count"]));
$dataOut->h=round(($value["avgh"]/$value["count"]),1);
$dataOut->l=$value["count"];
return $dataOut;
}
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment