public function csv_export($feedid, $start, $end, $outinterval, $usertimezone)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator;
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
$filename = $feedid . ".csv";
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$exportfh = @fopen('php://output', 'w');
$data = $this->get_data($feedid, $start * 1000, $end * 1000, $outinterval, 0, 0);
$max = sizeof($data);
for ($i = 0; $i < $max; $i++) {
$timenew = $helperclass->getTimeZoneFormated($data[$i][0] / 1000, $usertimezone);
$value = $data[$i][1];
if ($value != null) {
$value = number_format($value, $csv_decimal_places, $csv_decimal_place_separator, '');
}
fwrite($exportfh, $timenew . $csv_field_separator . $value . "\n");
}
fclose($exportfh);
exit;
}
public function csv_export($feedid, $start, $end, $outinterval, $usertimezone)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator, $data_sampling;
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
$interval = intval($outinterval);
$feedid = intval($feedid);
$start = round($start);
$end = round($end);
$skipmissing = 0;
if ($interval < 1) {
$interval = 1;
}
$dp = ceil(($end - $start) / $interval);
// datapoints for desied range with set interval time gap
$end = $start + $dp * $interval;
if ($dp < 1) {
return false;
}
if ($end == 0) {
$end = time();
}
// Check if datatype is daily so that select over range is used rather than skip select approach
static $feed_datatype_cache = array();
// Array to hold the cache
if (isset($feed_datatype_cache[$feedid])) {
$datatype = $feed_datatype_cache[$feedid];
// Retrieve from static cache
} else {
$result = $this->mysqli->query("SELECT datatype FROM feeds WHERE `id` = '{$feedid}'");
$row = $result->fetch_array();
$datatype = $row['datatype'];
$feed_datatype_cache[$feedid] = $datatype;
// Cache it
}
if ($datatype == 2) {
$dp = 0;
}
$feedname = "feed_" . trim($feedid) . "";
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
$filename = $feedid . ".csv";
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$exportfh = @fopen('php://output', 'w');
$range = $end - $start;
// window duration in seconds
if ($data_sampling && $range > 180000 && $dp > 0) {
$td = $range / $dp;
// time duration for each datapoint
$stmt = $this->mysqli->prepare("SELECT time, data FROM {$feedname} WHERE time BETWEEN ? AND ? ORDER BY time ASC LIMIT 1");
$t = $start;
$tb = 0;
$stmt->bind_param("ii", $t, $tb);
$stmt->bind_result($time, $dataValue);
for ($i = 0; $i < $dp; $i++) {
$tb = $start + intval(($i + 1) * $td);
$stmt->execute();
if ($stmt->fetch()) {
if ($dataValue != NULL || $skipmissing === 0) {
// Remove this to show white space gaps in graph
$timenew = $helperclass->getTimeZoneFormated($time, $usertimezone);
fwrite($exportfh, $timenew . $csv_field_separator . number_format((double) $dataValue, $csv_decimal_places, $csv_decimal_place_separator, '') . "\n");
}
}
$t = $tb;
}
} else {
if ($range > 5000 && $dp > 0) {
$td = intval($range / $dp);
$sql = "SELECT time DIV {$td} AS time, AVG(data) AS data" . " FROM {$feedname} WHERE time BETWEEN {$start} AND {$end}" . " GROUP BY 1 ORDER BY time ASC";
} else {
$td = 1;
$sql = "SELECT time, data FROM {$feedname}" . " WHERE time BETWEEN {$start} AND {$end} ORDER BY time ASC";
}
$result = $this->mysqli->query($sql);
if ($result) {
while ($row = $result->fetch_array()) {
$dataValue = $row['data'];
if ($dataValue != NULL || $skipmissing === 0) {
// Remove this to show white space gaps in graph
$time = $row['time'] * $td;
$timenew = $helperclass->getTimeZoneFormated($time, $usertimezone);
fwrite($exportfh, $timenew . $csv_field_separator . number_format((double) $dataValue, $csv_decimal_places, $csv_decimal_place_separator, '') . "\n");
}
}
}
}
fclose($exportfh);
exit;
}
public function csv_export($feedid, $start, $end, $outinterval, $usertimezone)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator;
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
$feedid = (int) $feedid;
$start = (int) $start;
$end = (int) $end;
$outinterval = (int) $outinterval;
if ($outinterval < 1) {
$outinterval = 1;
}
$dp = ceil(($end - $start) / $outinterval);
$end = $start + $dp * $outinterval;
if ($dp < 1) {
return false;
}
$fh = fopen($this->dir . "feed_{$feedid}.MYD", 'rb');
$filesize = filesize($this->dir . "feed_{$feedid}.MYD");
$pos = $this->binarysearch($fh, $start, $filesize);
$interval = ($end - $start) / $dp;
// Ensure that interval request is less than 1
// adjust number of datapoints to request if $interval = 1;
if ($interval < 1) {
$interval = 1;
$dp = ($end - $start) / $interval;
}
$data = array();
$time = 0;
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
$filename = $feedid . ".csv";
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$exportfh = @fopen('php://output', 'w');
for ($i = 0; $i < $dp; $i++) {
$pos = $this->binarysearch($fh, $start + $i * $interval, $filesize);
fseek($fh, $pos);
// Read the datapoint at this position
$d = fread($fh, 9);
// Itime = unsigned integer (I) assign to 'time'
// fvalue = float (f) assign to 'value'
$array = unpack("x/Itime/fvalue", $d);
$last_time = $time;
$time = $array['time'];
$timenew = $helperclass->getTimeZoneFormated($time, $usertimezone);
// $last_time = 0 only occur in the first run
if ($time != $last_time && $time > $last_time || $last_time == 0) {
fwrite($exportfh, $timenew . $csv_field_separator . number_format($array['value'], $csv_decimal_places, $csv_decimal_place_separator, '') . "\n");
}
}
fclose($exportfh);
exit;
}
$firstline = true;
foreach ($exportdata as $time => $data) {
$dataline = array();
foreach ($exportdata['Timestamp'] as $feedid => $name) {
if ($firstline) {
$dataline[$feedid] = $data[$feedid];
} else {
if (isset($data[$feedid])) {
$dataline[$feedid] = number_format((double) $data[$feedid], $csv_decimal_places, $csv_decimal_place_separator, '');
} else {
$dataline[$feedid] = "";
}
}
}
if (!$firstline) {
$time = $helperclass->getTimeZoneFormated($time, $usertimezone);
}
fputcsv($fh, array($time) + $dataline, $csv_field_separator);
$firstline = false;
}
fclose($fh);
if ($firstline == false) {
$emailbody = "Attached is CSV for '" . $tag . "' tag.";
$emailbody .= "\nTime range: " . $startText . " to " . $endText;
require_once "Lib/email.php";
$email = new Email();
//$email->from(from);
$email->to($emailto);
$email->subject('Emoncms CSV Export ' . $tag . " (" . $startText . "-" . $endText . ")");
$email->body($emailbody);
$email->attach($filename);
public function csv_export($feedid, $start, $end, $outinterval, $usertimezone)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator;
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
$feedid = (int) $feedid;
$start = (int) $start;
$end = (int) $end;
$outinterval = (int) $outinterval;
$layer = 0;
// If meta data file does not exist then exit
if (!($meta = $this->get_meta($feedid))) {
return false;
}
if ($outinterval < $meta->interval[0]) {
$outinterval = $meta->interval[0];
}
$dp = floor(($end - $start) / $outinterval);
if ($dp < 1) {
return false;
}
$end = $start + $dp * $outinterval;
$dpratio = $outinterval / $meta->interval[0];
if ($meta->nlayers > 1) {
if ($dpratio >= $meta->interval[1] / $meta->interval[0]) {
$layer = 1;
}
}
if ($meta->nlayers > 2) {
if ($dpratio >= $meta->interval[2] / $meta->interval[0]) {
$layer = 2;
}
}
if ($meta->nlayers > 3) {
if ($dpratio >= $meta->interval[3] / $meta->interval[0]) {
$layer = 3;
}
}
$dp_in_range = ceil(($end - $start) / $meta->interval[$layer]);
$start_time_avl = floor($meta->start_time / $meta->interval[$layer]) * $meta->interval[$layer];
// Divided by the number we need gives the number of datapoints to skip
// i.e if we want 1000 datapoints out of 100,000 then we need to get one
// datapoints every 100 datapoints.
$skipsize = round($dp_in_range / $dp);
if ($skipsize < 1) {
$skipsize = 1;
}
// Calculate the starting datapoint position in the timestore file
if ($start > $meta->start_time) {
$startpos = ceil(($start - $start_time_avl) / $meta->interval[$layer]);
} else {
$startpos = 0;
}
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
$filename = $feedid . ".csv";
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$exportfh = @fopen('php://output', 'w');
$data = array();
$i = 0;
// The datapoints are selected within a loop that runs until we reach a
// datapoint that is beyond the end of our query range
$mstart = microtime(true);
$fh = fopen($this->dir . $meta->id . "_{$layer}.dat", 'rb');
fseek($fh, $startpos * 4);
$layer_values = unpack("f*", fread($fh, 4 * $dp_in_range));
fclose($fh);
$count = count($layer_values) - 1;
$naverage = $skipsize;
for ($i = 1; $i < $count - $naverage; $i += $naverage) {
// Calculate the average value of each block
$point_sum = 0;
$points_in_sum = 0;
for ($n = 0; $n < $naverage; $n++) {
$value = $layer_values[$i + $n];
if (!is_nan($value)) {
$point_sum += $value;
$points_in_sum++;
}
}
// If there was a value in the block then add to data array
if ($points_in_sum) {
$time = $start_time_avl + $meta->interval[$layer] * ($startpos + $i - 1);
$average = $point_sum / $points_in_sum;
//$data[] = array($time*1000,$average);
$timenew = $helperclass->getTimeZoneFormated($time, $usertimezone);
fwrite($exportfh, $timenew . $csv_field_separator . number_format($average, $csv_decimal_places, $csv_decimal_place_separator, '') . "\n");
}
}
fclose($exportfh);
exit;
}
public function csv_export_multi($feedids, $start, $end, $outinterval, $datetimeformat, $name)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator;
$feedids = (array) explode(",", $feedids);
$exportdata = $this->csv_export_multi_prepare($feedids, $start, $end, $outinterval);
if (isset($exportdata['success']) && !$exportdata['success']) {
return $exportdata;
}
if ($datetimeformat == 1) {
global $user, $session;
$usertimezone = $user->get_timezone($session['userid']);
} else {
$usertimezone = false;
}
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
$start = DateTime::createFromFormat("U", $start);
if ($usertimezone) {
$start->setTimezone(new DateTimeZone($usertimezone));
}
$startText = $start->format("YmdHis");
$end = DateTime::createFromFormat("U", $end);
if ($usertimezone) {
$end->setTimezone(new DateTimeZone($usertimezone));
}
$endText = $end->format("YmdHis");
if ($name != "") {
$filename = $startText . "_" . $endText . "_" . $name . ".csv";
} else {
$filename = $startText . "_" . $endText . "_" . implode("_", $feedids) . ".csv";
}
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$fh = @fopen('php://output', 'w');
$firstline = true;
foreach ($exportdata as $time => $data) {
$dataline = array();
foreach ($exportdata['Timestamp'] as $feedid => $name) {
if ($firstline) {
$dataline[$feedid] = $data[$feedid];
} else {
if (isset($data[$feedid])) {
$dataline[$feedid] = number_format((double) $data[$feedid], $csv_decimal_places, $csv_decimal_place_separator, '');
} else {
$dataline[$feedid] = "";
}
}
}
if (!$firstline) {
$time = $helperclass->getTimeZoneFormated($time, $usertimezone);
}
fputcsv($fh, array($time) + $dataline, $csv_field_separator);
$firstline = false;
}
fclose($fh);
exit;
}
public function csv_export($feedid, $start, $end, $outinterval, $usertimezone)
{
global $csv_decimal_places, $csv_decimal_place_separator, $csv_field_separator;
require_once "Modules/feed/engine/shared_helper.php";
$helperclass = new SharedHelper();
$feedid = intval($feedid);
$start = intval($start);
$end = intval($end);
$outinterval = (int) $outinterval;
// If meta data file does not exist exit
if (!($meta = $this->get_meta($feedid))) {
return false;
}
$meta->npoints = $this->get_npoints($feedid);
if ($outinterval < $meta->interval) {
$outinterval = $meta->interval;
}
$dp = ceil(($end - $start) / $outinterval);
$end = $start + $dp * $outinterval;
// $dpratio = $outinterval / $meta->interval;
if ($dp < 1) {
return false;
}
// The number of datapoints in the query range:
$dp_in_range = ($end - $start) / $meta->interval;
// Divided by the number we need gives the number of datapoints to skip
// i.e if we want 1000 datapoints out of 100,000 then we need to get one
// datapoints every 100 datapoints.
$skipsize = round($dp_in_range / $dp);
if ($skipsize < 1) {
$skipsize = 1;
}
// Calculate the starting datapoint position in the timestore file
if ($start > $meta->start_time) {
$startpos = ceil(($start - $meta->start_time) / $meta->interval);
} else {
$start = ceil($meta->start_time / $outinterval) * $outinterval;
$startpos = ceil(($start - $meta->start_time) / $meta->interval);
}
$data = array();
$time = 0;
$i = 0;
// There is no need for the browser to cache the output
header("Cache-Control: no-cache, no-store, must-revalidate");
// Tell the browser to handle output as a csv file to be downloaded
header('Content-Description: File Transfer');
header("Content-type: application/octet-stream");
$filename = $feedid . ".csv";
header("Content-Disposition: attachment; filename={$filename}");
header("Expires: 0");
header("Pragma: no-cache");
// Write to output stream
$exportfh = @fopen('php://output', 'w');
// The datapoints are selected within a loop that runs until we reach a
// datapoint that is beyond the end of our query range
$fh = fopen($this->dir . $feedid . ".dat", 'rb');
while ($time <= $end) {
// $position steps forward by skipsize every loop
$pos = $startpos + $i * $skipsize;
// Exit the loop if the position is beyond the end of the file
if ($pos > $meta->npoints - 1) {
break;
}
// read from the file
fseek($fh, $pos * 4);
$val = unpack("f", fread($fh, 4));
// calculate the datapoint time
$time = $meta->start_time + $pos * $meta->interval;
$timenew = $helperclass->getTimeZoneFormated($time, $usertimezone);
// add to the data array if its not a nan value
if (!is_nan($val[1])) {
fwrite($exportfh, $timenew . $csv_field_separator . number_format($val[1], $csv_decimal_places, $csv_decimal_place_separator, '') . "\n");
}
$i++;
}
fclose($exportfh);
exit;
}