I have a Highstock graph where I create a CSV from my database to then show this data in the chart, but it turns out that the graph only recognizes CSV if after CSV is created and when I open CSV . Also, it also happens when I go back to guarding as CSV .
The script PHP I have to create CSV is this:
<?php
// mysql database connection details
$host = "localhost";
$username = "user";
$password = "pass";
$dbname = "basedados";
// open connection to mysql database
$connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));
// fetch mysql table rows
$sql = "SELECT 'tempo', 's1', 's2', 's3' FROM 'tabela'";
$result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));
$f = "perfiskwh.csv";
$fp = fopen('perfiskwh.csv', 'w+');
while($row = mysqli_fetch_assoc($result))
{
fputcsv($fp,$row);
}
// read into array
$arr = file($f);
// edit first line
$arr[0] = 'Data,KWh1,KWh2,KWh3,'. "\n";
// write back to file
file_put_contents($f, implode($arr));
fclose($fp);
//close the db connection
mysqli_close($connection);
?>
As for the graph I have this code so far:
$.get('dados/perfiskwh.csv', function (csvFile) {
var data = parseCSVData(csvFile);
var highchartsOptions = Highcharts.setOptions({
lang: {
loading: 'Aguarde...',
months: ['Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho', 'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro'],
weekdays: ['Domingo', 'Segunda-feira', 'Terça-feira', 'Quarta-feira', 'Quinta-feira', 'Sexta-feira', 'Sábado'],
shortMonths: ['Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez'],
exportButtonTitle: "Exportar",
printButtonTitle: "Imprimir",
rangeSelectorFrom: "De",
rangeSelectorTo: "Até",
rangeSelectorZoom: "Periodo",
downloadPNG: 'Descarregar gráfico como imagem PNG',
downloadJPEG: 'Descarregar gráfico como imagem JPEG',
downloadSVG: 'Descarregar gráfico como imagem SVG',
downloadPDF: 'Salvar em documento PDF',
contextButtonTitle: 'Opções',
noData: 'Sem dados para mostrar',
printChart: 'Imprimir gráfico',
numericSymbols: null,
// resetZoom: "Reset",
// resetZoomTitle: "Reset,
thousandsSep: ".",
decimalPoint: ','
}
}
);
$('#container').highcharts('StockChart', {
navigation: {
buttonOptions: {
enabled: true,
}
},
colors:[
'#993333',
'#404040',
'#003366',
'#006600',
'#CC3300'
],
credits: {
enabled: false
},
rangeSelector : {
selected: 1,
allButtonsEnabled: true,
buttons: [{
type: 'day',
count: 1,
text: '1 dia'
}, {
type: 'week',
count: 1,
text: '1 Semana'
}, {
type: 'all',
text: 'Tudo'
}],
buttonTheme: {
width: 75,
style: {
color: '#333',
fontWeight: 'bold',
fontSize: '14px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
},
},
},
chart: {
renderTo: 'container',
backgroundColor: {
linearGradient: [500, 400, 250, 0],
stops: [
[0, '#F0F7FF'],
[1, '#F0F7FF']
]
},
style: {
color: '#333',
fontWeight: 'bold',
fontSize: '14px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
},
type: 'spline'
},
title: {
text: 'Perfis de Consumos',
style: {
color: '#333',
fontWeight: 'bold',
fontSize: '20px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
}
},
xAxis: {
labels: {
style: {
color: 'black',
fontWeight: 'bold',
fontSize: '12px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
}
},
type: 'datetime',
tickInterval: 3600 * 1000,
categories: ['Mar','Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb']
},
tooltip: {
valueDecimals: 1,
valueSuffix: ' KWh'
},
yAxis: {
labels: {
style: {
color: 'black',
fontWeight: 'bold',
fontSize: '12px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
}
},
opposite: false,
title: {
text: 'Consumos da Energia [KWh]',
style: {
color: '#333',
fontWeight: 'bold',
fontSize: '16px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
}
}
},
navigator:{
xAxis: {
style: {
color: '#333',
fontWeight: 'bold',
fontSize: '16px',
fontFamily: 'Trebuchet MS, Verdana, sans-serif'
}
},
enabled: true,
handles: {
backgroundColor: 'Black',
borderColor: 'White'
},
series: {
color: '#0000FF',
lineWidth: 1
},
outlineColor: 'black',
outlineWidth: 1,
},
series: [{
name:'Distribuidor',
data: data[0],
}, {
name: 'Edificios',
data: data[1]
}, {
name: 'Avac',
data: data[2]
}]
});
});
function parseCSVData(csvFile) {
//Array para armazenar dados do Gráfico
var Geral= [];
var Distribuidor= [];
var AVAC= [];
var Data_inicio = "January 1, 2014";
var Data_hoje = new Date();
//Separar os dados do CSV em linhas
var lines= csvFile.split("\n");
//Verifica todas as linhas do CSV
$.each(lines, function (lineNumber, line){
if(lineNumber !=0){ //Salta a linha do cabeçalho
var fields = line.split(",");
if(fields.length==4 && Date.parse(fields[0]) >= Date.parse(Data_inicio) && Date.parse(fields[0]) <= Date.parse(Data_hoje)) {//Salta as linhas invalidas e começa na data especificada até ao presente dia
var timestamp = Date.parse(fields[0]);
var data1 = parseInt(fields[1]);
var data2 = parseInt(fields[2]);
var data3 = parseInt(fields[3]);
Geral.push([timestamp, data1]);
Distribuidor.push([timestamp, data2]);
AVAC.push([timestamp, data3]);
}
}
});
return [Geral,Distribuidor,AVAC];
}
And the CSV Output is this:
Apr 28 2016 11:00,219,23,221
Apr 28 2016 11:15,221,28,222
Apr 28 2016 11:30,220,9,222
Apr 28 2016 11:45,221,366,223
Apr 28 2016 12:00,221,355,223
Apr 28 2016 12:15,221,381,222
Apr 28 2016 12:30,221,395,223
Apr 28 2016 12:45,222,115,223
Apr 28 2016 13:00,221,5,222
Apr 28 2016 13:15,222,21,223
Apr 28 2016 13:30,221,10,223
Apr 28 2016 13:45,222,5,224
Apr 28 2016 14:00,222,21,223
Apr 28 2016 14:15,222,27,223