php/globRadiation.php

<?php
require_once 'env.php';
const URL = 'https://data.stadt-zuerich.ch/dataset/ugz_meteodaten_tagesmittelwerte/download/ugz_ogd_meteo_d1_&y.csv';
$now = time();
outBegin(Date('c', $now), ', y', $year = Date('Y', $now));
$r = enerProd();
$r = strGlo(2020, $year, $r);
ksort($r);
$fo = fopen('globalRadiation.csv', 'w');
fputcsv($fo, ['Datum', 'strGlo', 'prod']);

$yy = [];
foreach($r as [$d, $s, $p]) {
    if (! (is_numeric($s) and is_numeric($p))) {
        out("incomplete", [$d, $s, $p]);
    } else {
        fputcsv($fo, [$d, $s, $p, $p/$s]);
        if (! isset($yy[$y=substr($d, 0, 4)]))
            $yy[$y] = [$y, 0, 0, 0, 0];
        $yy[$y][1] += $s;
        $yy[$y][2] += $p;
        $yy[$y][3] += $p/$s;
        $yy[$y][4] ++;
   }
}
foreach ($yy as $y)
    fputcsv($fo, ["year $y[0]", $y[1]/$y[4], $y[2]/$y[4], $y[3]/$y[4], $y[4]]);
# out('strGlo', $sG);
/* $e = reset($eP);
reset($sG);
while (true) {
    dbq("e", $e);
    if ($e === false or (key($sG) ?? '99999a') < $e['tst']) {
        if (is_null(key($sG)))
            break;
        fputcsv($fo, [key($sG), current($sG), '']);
        next($sG);
    } elseif (is_null($s = key($sG)) or $e['tst'] < $s) {
        fputcsv($fo, [$e['tst'], '', $e['prod']]);
        $e = next($eP);
    } elseif ($e['tst'] === $s) {
         next($sG);
        $e = next($eP);
    } else {
        err("bad");
    }
} */
out(ftell($fo), "bytes to globalRadiation.csv");
fclose($fo);

outEnd();

function enerProd() {
    $pdo = new PDO('mysql:host=localhost;dbname=solar', 'solar', 'ralos');
    $v = $pdo->query("select date(tst) tst, sum(prod) prod from energy group by date(tst) order by date(tst) asc")->fetchAll(PDO::FETCH_ASSOC);
    out("energy", count($v), reset($v), end($v));
    $r = [];
    foreach($v as $a)
        $r[$a['tst']] = isset($r[$a['tst']]) ? err('duplicate', $a) : [$a['tst'], 'no', $a['prod']];
    out('enerProd', count($r));
    return $r;
}

function strGlo($yF, $yT, $r) {
    for ($y=$yF; $y <= $yT; $y++) {
        $ui = str_replace('&y', $y, URL);
        out("y $y ui $ui");
        # $c = file_get_contents($ui);
        $fi = fopen($ui, 'r');
        $hdr = fgetcsv($fi);
 #       var_dump($hdr);
 #       out(bin2hex($hdr[0]), bin2hex('"Datum"'));
 #       out('hdr', $hdr, $hdr[0], strpos($hdr[0], 'Datum') !== false and $hdr[1] === 'Standort');
        (strpos($hdr[0], 'Datum') !== false and $hdr[1] === 'Standort' and $hdr[2] === 'Parameter' and $hdr[4] === 'Einheit' and $hdr[5] === 'Wert') or err('bad header', $hdr);
        while($a = fgetcsv($fi)) {
            if ($a[1] !== 'Zch_Stampfenbachstrasse' or $a[2] !== 'StrGlo' or $a[4] !== 'W/m2')
                continue;
            $w = (is_numeric($a[5]) and $a[5] > 0) ? $a[5] : "bad$a[5]";
            if ( ! isset($r[$d = substr($a[0], 0, 10)]))
                $r[$d] = [$d, $w, 'no'];
            elseif ($r[$d][1] === 'no')
                $r[$d][1] = $w;
            else
                err("duplicate strGlo", $a);
        }
        out("strGlo", count($r));        
    }
    return $r;
}