<?php
$servername = "sql-server-host";
$username = "solar";
$password = "password";
$dbname = "solar";
$price = 0.42;

$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";
$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";
$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";
$result = $conn->query($sql);
$result2 = $conn->query($sql2);
$result3 = $conn->query($sql3);
$result4 = $conn->query($sql4);

if ($result->num_rows > 0 && $result2->num_rows >0 && $result3->num_rows && $result4->num_rows) {
	while($row = $result->fetch_assoc()) {
		$row2 = $result2->fetch_assoc();
		$row3 = $result3->fetch_assoc();
		$row4 = $result4->fetch_assoc();
		$saved_lifetime = round(($row["lifetime"] / 1000) * $price,2);
		$saved_year = round(($row["year"] / 1000) * $price,2);
		$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"]);
	}
} else {
	$jsonres = array("Error"=>"No result from database.");
}
$conn->close();

echo json_encode($jsonres)
?>