2023-05-29 09:09:08 +02:00
< ? php
$servername = " sql-server-host " ;
$username = " solar " ;
$password = " password " ;
$dbname = " solar " ;
2023-06-24 15:43:15 +02:00
$price = 0.42 ;
2023-05-29 09:09:08 +02:00
$conn = new mysqli ( $servername , $username , $password , $dbname );
if ( $conn -> connect_error ) {
$jsonres = array ( " Error " => $conn -> connect_error );
die ( " Connection failed: " . $conn -> connect_error );
}
$sql = " SELECT * FROM solardata ORDER BY id DESC LIMIT 1 " ;
2023-06-11 12:05:13 +02:00
$sql2 = " select sum(today) as week from (select today
from (
select last_update , today , row_number () over
( partition by date ( last_update )
order by last_update desc ) as row_num
from solardata ) as subquery
where row_num = 1
order by last_update desc limit 7 ) as weeksub " ;
$sql3 = " select sum(today) as week from (select today
from (
select last_update , today , row_number () over
( partition by date ( last_update )
order by last_update desc ) as row_num
from solardata ) as subquery
where row_num = 1
order by last_update desc limit 7 offset 7 ) as weeksub " ;
2023-06-24 15:33:36 +02:00
$sql4 = " SELECT today FROM `solardata` WHERE last_update >= Date(NOW()) - INTERVAL 1 DAY AND last_update < Date(NOW()) AND today <> 0.0 ORDER BY last_update DESC LIMIT 1 " ;
2023-05-29 09:09:08 +02:00
$result = $conn -> query ( $sql );
2023-06-11 12:05:13 +02:00
$result2 = $conn -> query ( $sql2 );
$result3 = $conn -> query ( $sql3 );
2023-06-24 15:33:36 +02:00
$result4 = $conn -> query ( $sql4 );
2023-05-29 09:09:08 +02:00
2023-06-24 15:33:36 +02:00
if ( $result -> num_rows > 0 && $result2 -> num_rows > 0 && $result3 -> num_rows && $result4 -> num_rows ) {
2023-05-29 09:09:08 +02:00
while ( $row = $result -> fetch_assoc ()) {
2023-06-11 12:05:13 +02:00
$row2 = $result2 -> fetch_assoc ();
$row3 = $result3 -> fetch_assoc ();
2023-06-24 15:43:15 +02:00
$row4 = $result4 -> fetch_assoc ();
$saved_lifetime = round (( $row [ " lifetime " ] / 1000 ) * $price , 2 );
$saved_year = round (( $row [ " year " ] / 1000 ) * $price , 2 );
2023-06-24 15:44:41 +02:00
$saved = round (( $row [ " today " ] / 1000 ) * $price , 2 );
$jsonres = array ( " today " => $row [ " today " ], " yesterday " => $row4 [ " today " ], " week " => $row2 [ " week " ], " last_week " => $row3 [ " week " ], " month " => $row [ " month " ], " year " => $row [ " year " ], " lifetime " => $row [ " lifetime " ], " current " => $row [ " current " ], " saved " => $saved , " saved_lifetime " => $saved_lifetime , " saved_year " => $saved_year , " last_update " => $row [ " last_update " ]);
2023-05-29 09:09:08 +02:00
}
} else {
$jsonres = array ( " Error " => " No result from database. " );
}
$conn -> close ();
echo json_encode ( $jsonres )
?>