Last active
April 26, 2021 11:00
-
-
Save Egregius/9ce592b556f63bf6186551df916716a0 to your computer and use it in GitHub Desktop.
Store temperature data from Domoticz in mySQL for historical analyses
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
define('sqlserver','127.0.0.1'); | |
define('sqluser','domotica'); | |
define('sqlpassword','domotica'); | |
define('sqldatabase','domotica'); | |
define('updatedatabase',true);//Set to false once the database is created, set to true to add devices | |
define('domoticzip','127.0.0.1'); | |
define('domoticzport','8080'); | |
define('numberofhours',1);//Defines how many hours of detailed data is updated in _day table. Set to a high number for initial run. | |
define('numberofdays',1);//Defindes how many days of min,avg and max is updated in _month table. Set to a high number for initial run. | |
define('devices',array(231,236,238,573,588,1260,1475));//IDXs of temperature devices | |
define('sleep',10000);//Defines how many microseconds of sleep there should be between queries, can be usefull on low power devices | |
if (updatedatabase) { | |
createupdatedatabase(); | |
} | |
$db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase); | |
if ($db->connect_errno>0) { | |
die('Unable to connect to database ['.$db->connect_error.']'); | |
} | |
foreach (devices as $idx) { | |
$device=json_decode( | |
file_get_contents( | |
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
true | |
); | |
if (!empty($device['result'][0]['Name'])) { | |
$name=$device['result'][0]['Name']; | |
$day=json_decode( | |
file_get_contents( | |
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=day'), | |
true | |
); | |
if (!empty($day['result'])) { | |
foreach ($day['result'] as $i) { | |
$stamp=$i['d']; | |
if(strtotime($stamp)>time()-(3600*numberofhours)){ | |
$temp=$i['te']; | |
$query="INSERT INTO `temp_day` (`stamp`,`$name`) | |
VALUES ('$stamp','$temp') | |
ON DUPLICATE KEY UPDATE `$name`='$temp'"; | |
echo $query.'<br>'; | |
if (!$result=$db->query($query)) { | |
echo('There was an error running the query "'.$query.'" - '.$db->error); | |
} | |
usleep(sleep); | |
} | |
} | |
} | |
usleep(sleep); | |
$month=json_decode( | |
file_get_contents( | |
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=graph&sensor=temp&idx='.$idx.'&range=month'), | |
true | |
); | |
if (!empty($month['result'])) { | |
foreach ($month['result'] as $i) { | |
$stamp=$i['d']; | |
if (strtotime($stamp)>time()-(86400*numberofdays)) { | |
$min=$i['tm']; | |
$avg=$i['ta']; | |
$max=$i['te']; | |
$cmin=$name.'_min'; | |
$cavg=$name.'_avg'; | |
$cmax=$name.'_max'; | |
$query="INSERT INTO `temp_month` (`stamp`,`$cmin`,`$cavg`,`$cmax`) | |
VALUES ('$stamp','$min','$avg','$max') | |
ON DUPLICATE KEY UPDATE `$cmin`='$min',`$cavg`='$avg',`$cmax`='$max'"; | |
echo $query.'<br>'; | |
if (!$result=$db->query($query)) { | |
echo('There was an error running the query "'.$query.'" - '.$db->error); | |
} | |
usleep(sleep); | |
} | |
} | |
} | |
usleep(sleep); | |
} | |
} | |
function createupdatedatabase() | |
{ | |
$db=new mysqli(sqlserver,sqluser,sqlpassword,sqldatabase); | |
if ($db->connect_errno>0){ | |
die('Unable to connect to database ['.$db->connect_error.']'); | |
} | |
$sqldatabase=sqldatabase; | |
$result=mysqli_fetch_assoc( | |
mysqli_query( | |
$db, | |
"SELECT count(*) as count | |
FROM information_schema.TABLES | |
WHERE (TABLE_SCHEMA = '$sqldatabase') | |
AND (TABLE_NAME = 'temp_day')" | |
) | |
); | |
if ($result['count']==0) { | |
$query="CREATE TABLE temp_day ( | |
stamp datetime PRIMARY KEY | |
)"; | |
if (!$result=$db->query($query)) { | |
die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
} | |
} | |
foreach (devices as $idx) { | |
$device=json_decode( | |
file_get_contents( | |
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
true | |
); | |
if (!empty($device['result'][0]['Name'])) { | |
$name=$device['result'][0]['Name']; | |
$result=mysqli_fetch_assoc( | |
mysqli_query( | |
$db, | |
"SELECT COLUMN_TYPE | |
FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE table_name = 'temp_day' | |
AND COLUMN_NAME = '$name';" | |
) | |
); | |
if ($result['COLUMN_TYPE']!='float(3,1)') { | |
$query="ALTER TABLE `temp_day` ADD `$name` FLOAT(3,1) NULL;"; | |
if (!$result=$db->query($query)) { | |
die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
} | |
} | |
} | |
} | |
$result=mysqli_fetch_assoc( | |
mysqli_query( | |
$db, | |
"SELECT count(*) as count | |
FROM information_schema.TABLES | |
WHERE (TABLE_SCHEMA = '$sqldatabase') | |
AND (TABLE_NAME = 'temp_month')" | |
) | |
); | |
if ($result['count']==0) { | |
$query="CREATE TABLE temp_month ( | |
stamp date PRIMARY KEY | |
)"; | |
if (!$result=$db->query($query)) { | |
die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
} | |
} | |
foreach (devices as $idx) { | |
$device=json_decode( | |
file_get_contents( | |
'http://'.domoticzip.':'.domoticzport.'/json.htm?type=devices&rid='.$idx), | |
true | |
); | |
if (!empty($device['result'][0]['Name'])) { | |
$name=$device['result'][0]['Name']; | |
$tables=array($name.'_min',$name.'_avg',$name.'_max'); | |
foreach ($tables as $table) { | |
$result=mysqli_fetch_assoc( | |
mysqli_query( | |
$db, | |
"SELECT COLUMN_TYPE | |
FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE table_name = 'temp_month' | |
AND COLUMN_NAME = '$table';" | |
) | |
); | |
if ($result['COLUMN_TYPE']!='float(3,1)') { | |
$query="ALTER TABLE `temp_month` ADD `$table` FLOAT(3,1) NULL;"; | |
if (!$result=$db->query($query)) { | |
die('There was an error running the query ['.$query.' - '.$db->error.']'); | |
} | |
} | |
} | |
} | |
} | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
So far, only the temperature works for me, I couldn't give the moisture.
INSERT INTO
tme
(kdy
,teplota
) VALUES ('2021-04-26 12:00','9.2') ON DUPLICATE KEY UPDATEteplota
='9.2'