156 lines
7.3 KiB
PHP
156 lines
7.3 KiB
PHP
<?php
|
|
|
|
require_once("../helper.php");
|
|
if(!isset($_GET["year"])){
|
|
$_GET["year"] = date("Y");
|
|
}else{
|
|
$_GET["year"] = intval($_GET["year"]);
|
|
}
|
|
if($_GET["year"] < 2018 || $_GET["year"] > date("Y")){
|
|
exit;
|
|
}
|
|
if(isset($_GET["type"])){
|
|
if(strtolower($_GET["type"]) =="lastyear"){
|
|
$_GET["year"] = date("Y")-1;
|
|
}elseif(strtolower($_GET["type"]) =="prelastyear"){
|
|
$_GET["year"] = date("Y")-2;
|
|
}
|
|
}
|
|
|
|
$Query = "SELECT
|
|
SUM(gridPcons/12000) as 'Stromverbrauch',
|
|
SUM((gridPcons*cost)/12000)+140 as 'Stromkosten (140€ fix)',
|
|
SUM(pvP - IF(gridP < 0, -gridP,0) - IF(battP < 0, -battP,0)- heaterPwr + IF(battP > 0, battP,0))*cost/12000 as 'Verbrauchsersparnis',
|
|
SUM((gridPfeed*gain)/12000) as 'Einspeisevergütung',
|
|
SUM(IF(gridP < 500, heaterPwr, 0)*0.063/12000) as 'Ersparnis Heizung',
|
|
SUM(IF(battP > 0, battP, 0)*cost/12000) as 'Ersparnis Batterie',
|
|
AVG(autonomy) AS 'Ø Autarkie',
|
|
(SUM(PL1_EV+PL2_EV+PL3_EV))/12 AS 'Autoladung ges. EG',
|
|
(((SUM(IF(gridP>100,0,PL1_EV+PL2_EV+PL3_EV)))/(SUM(PL1_EV+PL2_EV+PL3_EV))))*100 AS 'Autoladung Solar EG',
|
|
(SUM(IF(gridP>100,0,(PL1_EV+PL2_EV+PL3_EV)*cost))/12) AS 'Ersparnis Solarladung EG',
|
|
(SUM(PL1_EV+PL2_EV+PL3_EV))/24.5 AS 'Benzin gespart EG',
|
|
(SUM(PL1_EVog+PL2_EVog+PL3_EVog))/12 AS 'Autoladung ges. OG',
|
|
(((SUM(IF(gridP>100,0,PL1_EVog+PL2_EVog+PL3_EVog)))/(SUM(PL1_EVog+PL2_EVog+PL3_EVog))))*100 AS 'Autoladung Solar OG',
|
|
(SUM(IF(gridP>100,0,(PL1_EVog+PL2_EVog+PL3_EVog)*cost))/12) AS 'Ersparnis Solarladung OG',
|
|
(SUM(PL1_EVog+PL2_EVog+PL3_EVog))/24.5 AS 'Benzin gespart OG'
|
|
FROM EnergyFlow JOIN gridCosts ON DATE(datetime) >= DATE(gridCosts.active_date) AND DATE(datetime) <= DATE(gridCosts.end_date)
|
|
WHERE year(datetime) = ".$_GET["year"].";";
|
|
|
|
$PrevQuery = "SELECT
|
|
SUM(gridPcons/12000) as 'Stromverbrauch',
|
|
SUM((gridPcons*cost)/12000)+140 as 'Stromkosten (140€ fix)',
|
|
SUM(pvP - IF(gridP < 0, -gridP,0) - IF(battP < 0, -battP,0)- heaterPwr + IF(battP > 0, battP,0))*cost/12000 as 'Verbrauchsersparnis',
|
|
SUM((gridPfeed*gain)/12000) as 'Einspeisevergütung',
|
|
SUM(IF(gridP < 500, heaterPwr, 0)*0.063/12000) as 'Ersparnis Heizung',
|
|
SUM(IF(battP > 0, battP, 0)*cost/12000) as 'Ersparnis Batterie',
|
|
AVG(autonomy) AS 'Ø Autarkie',
|
|
(SUM(PL1_EV+PL2_EV+PL3_EV))/12 AS 'Autoladung ges. EG',
|
|
(((SUM(IF(gridP>100,0,PL1_EV+PL2_EV+PL3_EV)))/(SUM(PL1_EV+PL2_EV+PL3_EV))))*100 AS 'Autoladung Solar EG',
|
|
(SUM(IF(gridP>100,0,(PL1_EV+PL2_EV+PL3_EV)*cost))/12) AS 'Ersparnis Solarladung EG',
|
|
(SUM(PL1_EV+PL2_EV+PL3_EV))/24.5 AS 'Benzin gespart EG',
|
|
(SUM(PL1_EVog+PL2_EVog+PL3_EVog))/12 AS 'Autoladung ges. OG',
|
|
(((SUM(IF(gridP>100,0,PL1_EVog+PL2_EVog+PL3_EVog)))/(SUM(PL1_EVog+PL2_EVog+PL3_EVog))))*100 AS 'Autoladung Solar OG',
|
|
(SUM(IF(gridP>100,0,(PL1_EVog+PL2_EVog+PL3_EVog)*cost))/12) AS 'Ersparnis Solarladung OG',
|
|
(SUM(PL1_EVog+PL2_EVog+PL3_EVog))/24.5 AS 'Benzin gespart OG'
|
|
FROM EnergyFlow JOIN gridCosts ON DATE(datetime) >= DATE(gridCosts.active_date) AND DATE(datetime) <= DATE(gridCosts.end_date)
|
|
WHERE year(datetime) = ".($_GET["year"]-1).";";
|
|
|
|
|
|
$units = Array("kWh","€","€","€","€","€","€","%","kWh","%","€","L","kWh","%","€","L");
|
|
$LessIsBetter = Array(true,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false);
|
|
|
|
function array_insert($array,$values,$offset) {
|
|
return array_slice($array, 0, $offset, true) + $values + array_slice($array, $offset, NULL, true);
|
|
}
|
|
|
|
|
|
if (checkLogin()) {
|
|
|
|
$html = "<div class='row row-info'>";
|
|
$mysql = new mysqli($mysql_server, $mysql_solarUser, $mysql_solarPass, $mysql_solarDB);
|
|
$Res = mysqli_query($mysql,$Query);
|
|
$ResPrev = mysqli_query($mysql,$PrevQuery);
|
|
if(!$Res || !$ResPrev){
|
|
echo "Error:<br>".mysqli_error($mysql)."<br />";
|
|
}
|
|
if ($Res->num_rows > 0) {
|
|
$i = 0;
|
|
$row = $Res->fetch_assoc();
|
|
$rowPrev = $ResPrev->fetch_assoc();
|
|
//$row["Vergütung+Einsparung Strom"] = $row["Einspeisevergütung"]+$row["Verbrauchsersparnis"];
|
|
$row = array_insert($row,["Verg.+Einsp. Strom" => $row["Einspeisevergütung"]+$row["Verbrauchsersparnis"]],4);
|
|
$rowPrev = array_insert($rowPrev,["Verg.+Einsp. Strom" => $rowPrev["Einspeisevergütung"]+$rowPrev["Verbrauchsersparnis"]],4);
|
|
$i = 0;
|
|
foreach ($row as $name => $value) {
|
|
if(str_starts_with($name,"Autoladung ges.")){
|
|
$html .= "</div><hr class='mt-2 mb-3 border-light' />";
|
|
$html .= "<div class='row row-info'>";
|
|
}
|
|
/*$html .= "<div class='col-12 col-sm-4 col-md-2 col-xl-1'>
|
|
<div class='info-box'>
|
|
<div class='info-box-content'>
|
|
<span class='info-box-text'>".$name."</span>
|
|
<span class='info-box-number'>".
|
|
number_format(floatval($value),2,",",".")
|
|
."<small>".$units[$i++]."</small>
|
|
</span>
|
|
</div>
|
|
<!-- /.info-box-content -->
|
|
</div>
|
|
</div>";*/
|
|
$html .= "<div class='col'>
|
|
<div class='card stats border-0'>
|
|
<div class='card-body bg-dark bg-gradient rounded-top pb-1 pt-1'>
|
|
".$name."
|
|
</div>
|
|
<div class='card-footer text-center'>";
|
|
if($_GET["year"] != date("Y")){
|
|
if($LessIsBetter[$i]){
|
|
if($value == 0)
|
|
$dev=100;
|
|
else
|
|
$dev = $rowPrev[$name]/$value;
|
|
//$first = round($rowPrev[$name]*10/$rowPrev[$name]); //allow for 10% deviation for displaying no tendency
|
|
//$second = round($value); //allow for 10% deviation for displaying no tendency
|
|
$arrowGood = "down";
|
|
$arrowBad = "up";
|
|
}else{
|
|
if($rowPrev[$name] == 0)
|
|
$dev=100;
|
|
else
|
|
$dev = $value/$rowPrev[$name];
|
|
//$first = round($value); //allow for 10% deviation for displaying no tendency
|
|
//$second = round($rowPrev[$name]); //allow for 10% deviation for displaying no tendency
|
|
$arrowGood = "up";
|
|
$arrowBad = "down";
|
|
}
|
|
if($dev > 1.05){
|
|
$html .= "<span class='float-right text-success-emphasis'>
|
|
<i class='bi bi-arrow-".$arrowGood."' style='font-size: 0.9em;'></i> ";
|
|
}elseif($dev < 0.95){
|
|
$html .= "<span class='float-right text-danger-emphasis'>
|
|
<i class='bi bi-arrow-".$arrowBad."' style='font-size: 0.9em;'></i> ";
|
|
}else{
|
|
$html .= "<span class='float-right'>
|
|
<i class='bi bi-arrow-right' style='font-size: 0.9em;'></i> ";
|
|
}
|
|
}
|
|
else{
|
|
$html .= "<span class='float-right'>";
|
|
}
|
|
$html .= number_format(floatval($value),2,",",".")
|
|
."<small> ".$units[$i]."</small></span></div>
|
|
<!-- /.info-box-content -->
|
|
</div>
|
|
</div>";
|
|
$i++;
|
|
}
|
|
}
|
|
$html .= "</div>";
|
|
}
|
|
|
|
|
|
//header('Content-Type: application/json');
|
|
echo $html;
|
|
//echo '{"labels":[1761322682000,1761322782000,1761322882000,1761322982000,1761323082000,1761323182000,1761323282000],"datasets":[{"stack": "Stack 0","cubicInterpolationMode":"monotone","fill":"origin","label":"Acquisitions by year","data":[10,20,50,20,10,5,70]},{"fill": "false","stack": "Stack 1","cubicInterpolationMode": "monotone","label": "Acquisitions by year","data": [10,20,50,20,10,5,70]}]}';
|