PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
function http_post_form(string $url, array $data): array {
$ch = curl_init($url);
curl_setopt_array($ch, [
CURLOPT_POST => true,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_HTTPHEADER => ['Content-Type: application/x-www-form-urlencoded'],
CURLOPT_POSTFIELDS => http_build_query($data),
CURLOPT_TIMEOUT => 45,
]);
$resp = curl_exec($ch);
if ($resp === false) throw new RuntimeException('cURL error: '.curl_error($ch));
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($code < 200 || $code >= 300) throw new RuntimeException("HTTP $code: $resp");
$json = json_decode($resp, true);
if (!is_array($json)) throw new RuntimeException('Invalid JSON response');
return $json;
}
function http_get_json(string $url, array $headers): array {
$ch = curl_init($url);
curl_setopt_array($ch, [
CURLOPT_RETURNTRANSFER => true,
CURLOPT_HTTPHEADER => $headers,
CURLOPT_TIMEOUT => 45,
]);
$resp = curl_exec($ch);
if ($resp === false) throw new RuntimeException('cURL error: '.curl_error($ch));
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($code < 200 || $code >= 300) throw new RuntimeException("HTTP $code: $resp");
$json = json_decode($resp, true);
if (!is_array($json)) throw new RuntimeException('Invalid JSON response');
return $json;
}
// -------------------------------------------------------
// 2) Access Token per Refresh holen
// -------------------------------------------------------
$oauth = http_post_form('https://api.netatmo.com/oauth2/token', [
'grant_type' => 'refresh_token',
'refresh_token' => $REFRESH_TOKEN,
'client_id' => $CLIENT_ID,
'client_secret' => $CLIENT_SECRET,
]);
if (empty($oauth['access_token'])) throw new RuntimeException('No access_token from Netatmo');
$accessToken = $oauth['access_token'];
if (!empty($oauth['refresh_token']) && $oauth['refresh_token'] !== $REFRESH_TOKEN) {
error_log('Hinweis: Netatmo hat einen neuen refresh_token geliefert. Bitte config.php aktualisieren.');
}
// -------------------------------------------------------
// 3) Stations- & Modul-Infos + aktuelle Dashboard-Werte
// -------------------------------------------------------
$stations = http_get_json('https://api.netatmo.com/api/getstationsdata', [
'Authorization: Bearer '.$accessToken,
]);
if (empty($stations['body']['devices'])) {
// Nichts zu tun
exit(0);
}
// Prepared Upsert
$upsert = $pdo->prepare("
INSERT INTO netatmo_readings
(station_id, module_id, measure_time_utc, temperature, humidity, pressure,
co2, noise, wind_strength, wind_angle, rain_60min, rain_24h, raw_json)
VALUES
(:station_id, :module_id, :time_utc, :temperature, :humidity, :pressure,
:co2, :noise, :wind_strength, :wind_angle, :rain_60min, :rain_24h, :raw_json)
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
humidity = VALUES(humidity),
pressure = VALUES(pressure),
co2 = VALUES(co2),
noise = VALUES(noise),
wind_strength = VALUES(wind_strength),
wind_angle = VALUES(wind_angle),
rain_60min = VALUES(rain_60min),
rain_24h = VALUES(rain_24h),
raw_json = VALUES(raw_json)
");
function saveDashboard(PDOStatement $stmt, string $stationId, string $moduleId, array $d): void {
$timeUtc = isset($d['time_utc']) ? gmdate('Y-m-d H:i:s', (int)$d['time_utc']) : gmdate('Y-m-d H:i:s');
$params = [
':station_id' => $stationId,
':module_id' => $moduleId,
':time_utc' => $timeUtc,
':temperature' => isset($d['Temperature']) ? (float)$d['Temperature'] : null,
':humidity' => isset($d['Humidity']) ? (int)$d['Humidity'] : null,
':pressure' => isset($d['Pressure']) ? (float)$d['Pressure'] : null,
':co2' => isset($d['CO2']) ? (int)$d['CO2'] : null,
':noise' => isset($d['Noise']) ? (int)$d['Noise'] : null,
':wind_strength'=> isset($d['WindStrength']) ? (int)$d['WindStrength'] : null,
':wind_angle' => isset($d['WindAngle']) ? (int)$d['WindAngle'] : null,
':rain_60min' => isset($d['sum_rain_1']) ? (float)$d['sum_rain_1'] : null,
':rain_24h' => isset($d['sum_rain_24']) ? (float)$d['sum_rain_24'] : null,
':raw_json' => json_encode($d, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE),
];
$stmt->execute($params);
}
// Speichere Dashboard + sammle Module
$devices = $stations['body']['devices'];
$allModules = []; // Liste für getmeasure
foreach ($devices as $device) {
$stationId = $device['_id'];
$mainModuleId = $device['_id'];
if (!empty($device['dashboard_data'])) {
saveDashboard($upsert, $stationId, $mainModuleId, $device['dashboard_data']);
}
$allModules[] = [
'device_id' => $stationId,
'module_id' => null, // Hauptmodul
'types' => inferTypesFromDashboard($device['dashboard_data'] ?? []), // für getmeasure
];
if (!empty($device['modules'])) {
foreach ($device['modules'] as $mod) {
$moduleId = $mod['_id'];
if (!empty($mod['dashboard_data'])) {
saveDashboard($upsert, $stationId, $moduleId, $mod['dashboard_data']);
}
$allModules[] = [
'device_id' => $stationId,
'module_id' => $moduleId,
'types' => inferTypesFromDashboard($mod['dashboard_data'] ?? []),
];
}
}
}
/**
* Leitet aus vorhandenen Dashboard-Feldern ab, welche Typen wir über getmeasure laden sollten.
*/
function inferTypesFromDashboard(array $d): string {
$map = [
'Temperature' => 'Temperature',
'Humidity' => 'Humidity',
'Pressure' => 'Pressure',
'CO2' => 'CO2',
'Noise' => 'Noise',
'WindStrength' => 'WindStrength',
'WindAngle' => 'WindAngle',
'sum_rain_1' => 'Rain', // getmeasure liefert "Rain" als Typ
'sum_rain_24' => 'Rain',
];
$types = [];
foreach ($map as $k => $t) {
if (array_key_exists($k, $d)) $types[$t] = true;
}
// Falls leer (z. B. Modul ohne aktuellen Dashboard-Wert), lieber Standardtypen versuchen:
if (!$types) $types = ['Temperature'=>true,'Humidity'=>true,'Pressure'=>true,'CO2'=>true,'Noise'=>true,'WindStrength'=>true,'WindAngle'=>true,'Rain'=>true];
return implode(',', array_keys($types));
}
// -------------------------------------------------------
// 4) Lücken via getmeasure nachladen (scale=max, in Chunks)
// -------------------------------------------------------
foreach ($allModules as $m) {
fetchMeasuresAndUpsert(
$pdo,
$accessToken,
$m['device_id'],
$m['module_id'],
$m['types'] ?: 'Temperature,Humidity,Pressure,CO2,Noise,WindStrength,WindAngle,Rain',
'max'
);
}
function fetchMeasuresAndUpsert(PDO $pdo, string $accessToken, string $deviceId, ?string $moduleId, string $typesCsv, string $scale): void {
// letzten Timestamp in DB finden
$moduleKey = $moduleId ?? $deviceId;
$stmt = $pdo->prepare("SELECT MAX(measure_time_utc) AS last_ts FROM netatmo_readings WHERE module_id = :m");
$stmt->execute([':m' => $moduleKey]);
$last = $stmt->fetchColumn();
$dateBegin = $last ? (strtotime($last.' UTC') + 1) : (time() - 30*24*3600); // neu: 30 Tage rückwirkend
$dateEnd = time();
if ($dateBegin > $dateEnd) return;
// Chunking: 7 Tage pro Request ist i.d.R. safe
$chunk = 7*24*3600;
$insert = $pdo->prepare("
INSERT INTO netatmo_readings
(station_id, module_id, measure_time_utc, temperature, humidity, pressure,
co2, noise, wind_strength, wind_angle, rain_60min, rain_24h, raw_json)
VALUES
(:station_id, :module_id, :time_utc, :temperature, :humidity, :pressure,
:co2, :noise, :wind_strength, :wind_angle, :rain_60min, :rain_24h, :raw_json)
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
humidity = VALUES(humidity),
pressure = VALUES(pressure),
co2 = VALUES(co2),
noise = VALUES(noise),
wind_strength = VALUES(wind_strength),
wind_angle = VALUES(wind_angle),
rain_60min = VALUES(rain_60min),
rain_24h = VALUES(rain_24h),
raw_json = VALUES(raw_json)
");
// Mapping der Reihenfolge von getmeasure-Values auf Spalten:
$types = array_map('trim', explode(',', $typesCsv));
for ($from = $dateBegin; $from <= $dateEnd; $from += $chunk + 1) {
$to = min($from + $chunk, $dateEnd);
$params = array_filter([
'device_id' => $deviceId,
'module_id' => $moduleId,
'scale' => $scale, // 'max' = höchste Auflösung
'type' => $typesCsv, // z.B. "Temperature,Humidity,Pressure"
'date_begin' => $from,
'date_end' => $to,
'optimize' => 'false',
'real_time' => 'false',
], fn($v) => $v !== null && $v !== '');
$url = 'https://api.netatmo.com/api/getmeasure?'.http_build_query($params);
$data = http_get_json($url, ['Authorization: Bearer '.$accessToken]);
if (empty($data['body'])) continue;
foreach ($data['body'] as $series) {
$begTime = (int)($series['beg_time'] ?? 0);
$stepSec = scaleToSeconds($scale);
foreach (($series['value'] ?? []) as $i => $vals) {
$ts = gmdate('Y-m-d H:i:s', $begTime + $i * $stepSec);
// Messwerte initialisieren
$row = [
'temperature' => null,
'humidity' => null,
'pressure' => null,
'co2' => null,
'noise' => null,
'wind_strength' => null,
'wind_angle' => null,
// Rain kommt als Intensität pro 5min-Step bei scale=max – wir speichern hier nur den Rohwert
'rain_60min' => null,
'rain_24h' => null,
];
// Werte den Typen entsprechend zuordnen
foreach ($types as $idx => $t) {
$v = $vals[$idx] ?? null;
switch ($t) {
case 'Temperature': $row['temperature'] = is_numeric($v) ? (float)$v : null; break;
case 'Humidity': $row['humidity'] = is_numeric($v) ? (int)$v : null; break;
case 'Pressure': $row['pressure'] = is_numeric($v) ? (float)$v : null; break;
case 'CO2': $row['co2'] = is_numeric($v) ? (int)$v : null; break;
case 'Noise': $row['noise'] = is_numeric($v) ? (int)$v : null; break;
case 'WindStrength': $row['wind_strength'] = is_numeric($v) ? (int)$v : null; break;
case 'WindAngle': $row['wind_angle'] = is_numeric($v) ? (int)$v : null; break;
case 'Rain':
// Optional: hier nur die 5-min Regenmenge (mm) ablegen – Aggregation (60min/24h) machst du in SQL/View
$row['rain_60min'] = $row['rain_60min'] ?? null; // Platzhalter, wir lassen es hier leer
break;
}
}
// Rohdaten zusammenbauen (nützlich für spätere Auswertung)
$raw = [
'types' => $types,
'values'=> $vals,
'scale' => $scale,
];
$insert->execute([
':station_id' => $deviceId,
':module_id' => $moduleId ?? $deviceId,
':time_utc' => $ts,
':temperature' => $row['temperature'],
':humidity' => $row['humidity'],
':pressure' => $row['pressure'],
':co2' => $row['co2'],
':noise' => $row['noise'],
':wind_strength'=> $row['wind_strength'],
':wind_angle' => $row['wind_angle'],
':rain_60min' => $row['rain_60min'],
':rain_24h' => $row['rain_24h'],
':raw_json' => json_encode($raw, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE),
]);
}
}
}
}
function scaleToSeconds(string $s): int {
// Bei scale=max sind es i.d.R. 5 Minuten Steps
return match($s) {
'max' => 300,
'30min' => 1800,
'1hour' => 3600,
'3hours' => 10800,
'1day' => 86400,
'1week' => 604800,
'1month' => 2592000,
default => 300,
};
}
