Kod PHP wygląda tak:
function Temperaturechart($days) { if($days == 1) { // Hour average } foreach($sensors as $sensor) { $data = ''; $lastvalue = 0; //Yesterday from current H to midnight for($YH = $startH; $YH < 24; $YH++) { $m = 0; while($m < 60) { $mn = $m+15; if($mn == 60) { if($YH == 23) { $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$yday $YH:$m' AND '$tday 00:00'"; if($sensor == 'IN') { $labels .= "\"$ydayshort $YH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $data .= "\"$value\","; } else { //Next hour, minute 00 $NH = $YH+1; $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$yday $YH:$m' AND '$yday $NH:00'"; if($sensor == 'IN') { $labels .= "\"$ydayshort $YH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $data .= "\"$value\","; } } else { $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$yday $YH:$m' AND '$yday $YH:$mn'"; if($sensor == 'IN') { $labels .= "\"$ydayshort $YH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $data .= "\"$value\","; } $m=$mn; } } // Today from 0 to current hour for($TH = 0; $TH < $startH; $TH++) { $m = 0; while($m < 60) { $mn = $m+15; if($mn == 60) { if($TH == 23) { $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$tday $YH:$m' AND '$tday 23:59';"; if($sensor == 'IN') { $labels .= "\"$tdayshort $TH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $dataAkwa .= "\"$value\","; } else { //Next hour, minute 00 $NH = $TH+1; $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$tday $TH:$m' AND '$tday $NH:00';"; if($sensor == 'IN') { $labels .= "\"$tdayshort $TH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $data .= "\"$value\","; } } else { $q="SELECT AVG(value) AS value, COUNT(id) AS number FROM `temperatures` WHERE sensor='$sensor' AND `date` BETWEEN '$tday $TH:$m' AND '$tday $TH:$mn';"; if($sensor == 'IN') { $labels .= "\"$tdayshort $TH:$m\","; } if($temp['value'] == 0) { $value = $lastvalue; } else { $lastvalue = $temp['value']; $value = $temp['value']; } $data .= "\"$value\","; } $m=$mn; } } ${"data$sensor"} = $data; } echo '<div class="card mb-3"> <div class="card-body"> <canvas id="Temperaturechart"></canvas> </div> </div> <script> var ctxL = document.getElementById("Temperaturechart").getContext(\'2d\'); var myLineChart = new Chart(ctxL, { type: \'line\', data: { labels: ['.$labels.'], datasets: [{ label: "OUT", data: ['.$dataOUT.'], backgroundColor: [ \'rgba(0,0,0, .2)\', ], borderColor: [ \'#aaaaaa\', ], borderWidth: 2 },{ label: "IN", data: ['.$dataIN.'], backgroundColor: [ \'rgba(154, 207, 75, .2)\', ], borderColor: [ \'#9acf4b\', ], borderWidth: 2 },{ label: "Akwa", data: ['.$dataAkwa.'], backgroundColor: [ \'rgba(66, 170, 243, .2)\', ], borderColor: [ \'#42aaf3\', ], borderWidth: 2 }, ] }, options: { responsive: true } }); </script>'; }
Kod HTML wygląda tak:
https://pastebin.com/VQPFZPKF
Nie do końca podoba mi się to rozwiązanie. Raz ze względu na obciążenie - prawie 300 kwerend dla trzech czujników. Dwa że kod jest bardzo skomplikowany a to tylko wykres dla jednego dnia. Boje się pomyśleć nad tygodniem, miesiącem a może rokiem.
Czy istnieje coś innego, gotowiec, który generuje dane dla Chart.js który by mi w tym pomógł?
Lub może w ogóle zrezygnować z MySQLa i oprzeć to na czymś innym?