static function graphData($type, $locationid, $period, $graph, $timezone)
{
if (empty($locationid)) {
return "ERROR, no measurement id specified for deleting measurement";
}
$conxn = apiDB::getConnection();
$sql = "SELECT CASE WHEN r." . $type . " IS NULL THEN 0 ELSE r." . $type . " END, d.dt as fdate, CASE WHEN r.days IS NULL THEN 1 ELSE r.days END, CASE WHEN r.d_ave IS NULL THEN 0 ELSE r.d_ave END\n\t\t\tFROM getAllDays(CURRENT_DATE, " . $period . ") d left join (\n\t\t\t\tSELECT " . $type . ", cast(todate - interval '8 hours' - interval '1 second' as date) as todate, CEIL(CAST ((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0 AS NUMERIC)) AS days,\n\t\t\t\t\tROUND( CAST((" . $type . "/((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0)) AS NUMERIC), 1) AS d_ave\n\t\t\t\tFROM " . $type . "measurement\n\t\t\t\tWHERE locationid = " . $locationid . "\n\t\t\t\t) r ON d.dt = r.todate\n\t\t\tORDER BY d.dt DESC ";
//error_log( $sql2);
$result = pg_query($conxn, $sql);
$results_array = array();
$data = array();
if ($result) {
while ($row = pg_fetch_array($result)) {
for ($i = 0; $i < $row["days"]; $i++) {
$date = new DateTime($row["fdate"], new DateTimeZone($timezone));
$date->sub(new DateInterval('P' . $i . 'D'));
$item = array();
array_push($item, date_format($date, 'm/d'));
array_push($item, floatval($row["d_ave"]));
array_push($data, $item);
if ($i > 0) {
pg_fetch_array($result);
}
//otherwise the range shows up in duplicate
}
}
//$datasets = Array();
//$datasets["type"] = $graph;
//$datasets["data"] = array_reverse($data);
//$results_array["JSChart"]["datasets"][0] = $datasets;
}
//return $results_array;
return array_reverse($data);
}
static function graphData($type, $locationid, $period, $graph, $timezone)
{
if (empty($locationid)) {
return "ERROR, no measurement id specified for deleting measurement";
}
$conxn = apiDB::getConnection();
$sql_old = "SELECT " . $type . ", todate::date, ROUND(CAST ((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0 AS NUMERIC), 0) AS days, \n ROUND( CAST((rain/((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0)) AS NUMERIC), 1) AS d_ave \n FROM " . $type . "measurement \n WHERE locationid = " . $locationid . "\n AND fromdate >= CURRENT_TIMESTAMP - INTERVAL '" . $period . " days'\n ORDER BY fromdate ";
$sql = "SELECT CASE WHEN r." . $type . " IS NULL THEN 0 ELSE r." . $type . " END, d.dt as fdate, CASE WHEN r.days IS NULL THEN 1 ELSE r.days END, CASE WHEN r.d_ave IS NULL THEN 0 ELSE r.d_ave END\n\t\t\tFROM getAllDays(CURRENT_DATE, " . $period . ") d left join (\n\t\t\t\tSELECT " . $type . ", fromdate::date, ROUND(CAST ((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0 AS NUMERIC), 0) AS days,\n\t\t\t\t\tROUND( CAST((" . $type . "/((EXTRACT(EPOCH FROM todate)-EXTRACT(EPOCH FROM fromdate)) / 86400.0)) AS NUMERIC), 1) AS d_ave\n\t\t\t\tFROM " . $type . "measurement\n\t\t\t\tWHERE locationid = " . $locationid . "\n\t\t\t\t) r ON d.dt = r.fromdate\n\t\t\tORDER BY d.dt ";
//error_log( $sql2);
$result = pg_query($conxn, $sql);
$results_array = array();
$data = array();
if ($result) {
while ($row = pg_fetch_array($result)) {
for ($i = 0; $i < $row["days"]; $i++) {
$date = new DateTime($row["fdate"], new DateTimeZone($timezone));
$date->add(new DateInterval('P' . $i . 'D'));
$item = array();
array_push($item, date_format($date, 'm/d'));
array_push($item, floatval($row["d_ave"]));
array_push($data, $item);
}
}
$datasets = array();
$datasets["type"] = $graph;
$datasets["data"] = $data;
$results_array["JSChart"]["datasets"][0] = $datasets;
}
//return $results_array;
return $data;
}
