Historic Graph

Historic.js

function historic(i)
{	
	alert(i)//marker value passed from inspection point clicked on map
		
var margin = {top: 25, right: 20, bottom: 35, left: 30},
width = 440,
height = 270;
		
var parseDate = d3.time.format("%Y-%m-%d %H:%M:%S").parse;

Date.prototype.getMonthName = function() {
var monthNames = [ "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" ];
	return monthNames[this.getMonth()];}		

var formatTime = d3.time.format("%H:%M:%S");

var x = d3.time.scale().range([0, width]);

var y = d3.scale.linear().range([height, 0]);

var newdate = d3.time.format('%Y-%m-%dT%H:%M:%S.%LZ').parse;

var xAxis = d3.svg.axis()
    	.scale(x)
    	.orient("bottom")
	.tickFormat(d3.time.format("%b %e"))
	.ticks(4);

var yAxis = d3.svg.axis()
    	.scale(y)
    	.orient("left");

var line = d3.svg.line()
	.interpolate('linear')
    	.x(function(d) { return x(newdate(d.key)); })
    	.y(function(d) { return y(d.values.mean); });
				
var svg = d3.select("#historic").append("svg")
    	.attr("width", width + margin.left + margin.right)
    	.attr("height", height + margin.top + margin.bottom)
  	.append("g")
    	.attr("transform", "translate(" + margin.left + "," + margin.top + ")");
		
function make_x_axis() {return d3.svg.axis()
	.scale(x)
	.orient("bottom")
	.ticks(9)}

function make_y_axis() {return d3.svg.axis()
	.scale(y)
	.orient("left")
	.ticks(9)}
				
d3.json("HistoricData.php", function(error,data) {
	
	data = data.filter(function(d) { return d.inspectionPoint_id == i; })//filter data by inspection point chosen from map	
	console.log(data)//not returning all data???
	
  	data.forEach(function(d) {
    	d.dateTimeTaken = parseDate(d.dateTimeTaken);
    	d.reading = +d.reading;
	d.parameterType = d.parameterType;});
		
	 					
var selectedParameter = document.getElementById("parameterType").value;		
var selectedMonth = document.getElementById("dateTimeTaken").value;
	
//filter data by selected parameter and month	
var selectedData = data.filter(function(d) { 
	return d.parameterType == selectedParameter  &&
	d.dateTimeTaken.getMonthName() == selectedMonth;});
		
//get average reading for each day within selected month
var newdata = d3.nest()    	
	.key(function(d) { return d3.time.day(d.dateTimeTaken).toISOString(); })
	.sortKeys(d3.ascending)	
	.rollup(function(d) {
	 return {mean:d3.mean(d, function(d_) {return +d_.reading;})};})
	.entries(selectedData);
		
x.domain(d3.extent(newdata, function(d) { return newdate(d.key); }));
y.domain([0, d3.max(newdata, function(d) { return d.values.mean; })]);
		
svg.append("g")
	.attr("class", "x axis")
	.attr("transform", "translate(0," + height + ")")
	.call(xAxis)
	.append("text") 
	.attr("x", 240 )
	.attr("y", 430 )
	.style("text-anchor", "middle")
	.text("(TIME)");

svg.append("g")
	.attr("class", "y axis")
	.call(yAxis);
       		
svg.append("path")
	.attr("class", "line")
	.attr("d", line(newdata)); 
	
svg.append("g")
	.attr("class", "grid")
	.attr("transform", "translate(0," + height + ")")
	.call(make_x_axis()
	.tickSize(-height, 0, 0)
	.tickFormat(""));

svg.append("g")
	.attr("class", "grid")
	.call(make_y_axis()
	.tickSize(-width, 0, 0)
	.tickFormat(""));
	
var div = d3.select("#historic").append("div")
	.attr("class", "tooltip")
	.style("opacity", 0); 

svg.selectAll("dot")
	.data(newdata)
	.enter()
	.append("circle")
	.attr("class", "dot")
	.attr("r", 4)
	.attr("cx", function(d) { return x(newdate(d.key)); })
	.attr("cy", function(d) { return y(d.values.mean); })
	.attr("fill", "#8cc13f")
	.on("mouseover", function(d) {
	 div.transition()
	.duration(200)
	.style("opacity", .9);	  
	div .html("Inspection Point:"  + "  " + i + "<br/>" + "<br/>" +"Date taken:" + "  " + newdate(d.key) + "<br/>" + "<br/>" + "Average Reading:" + "  " + d.values.mean + "<br/>" + "<br/>" + 		"Parameter:" +  "  " + selectedParameter)
	.style("left", (d3.event.pageX) + "px")
	.style("top", (d3.event.pageY - 28) + "px");
	})
	.on("mouseout", function(d) {
	div.transition()
	.duration(500)
	.style("opacity", 0);
		});
		
d3.select("#parameterType").on("change", upDate);
   
d3.select("#dateTimeTaken").on("change", upDate);

function upDate()
{
var selectedParameter = document.getElementById("parameterType").value;		
var selectedMonth = document.getElementById("dateTimeTaken").value;
	
//filter data by selected parameter and month	
var selectedData = data.filter(function(d) { 			
	return d.parameterType == selectedParameter  &&
	d.dateTimeTaken.getMonthName() == selectedMonth;});
		
//get average reading for each day within selected month	
var newdata = d3.nest()    	
	.key(function(d) { return d3.time.day(d.dateTimeTaken).toISOString(); })
	.sortKeys(d3.ascending)	
	.rollup(function(d) {
	 return {mean:d3.mean(d, function(d_) {return +d_.reading;})};})
	.entries(selectedData);
		
x.domain(d3.extent(newdata, function(d) { return newdate(d.key); }));
y.domain([0, d3.max(newdata, function(d) { return d.values.mean; })]);
		
svg.select("path.line")
   	.transition() 
	.ease("linear")
	.duration(750) 
    	.attr( "d", line(newdata));
 		
svg.select(".x.axis")
  	.transition()
	.duration(750)
	.ease("linear")
	.call(xAxis);
	  	  
svg.select(".y.axis")
  	.transition()
	.duration(750)
	.ease("linear")
	.call(yAxis);
	
svg.selectAll("g.grid")
  	.transition()
	.duration(750)
	.call(make_x_axis)
	.call(make_y_axis)	
				
var circle = svg.selectAll("circle")
    	.data(newdata);

circle.enter().append("circle")
	.transition()
	.duration(750)
    	.attr("r", 4)
	.attr("fill", "#8cc13f")

circle
    	.attr("cx", function(d) { return x(newdate(d.key)); })
	.attr("cy", function(d) { return y(d.values.mean); })
	.on("mouseover", function(d) {
	 div.transition()
	.duration(200)
	.style("opacity", .9);	  
	div .html("Inspection Point:"  + "  " + i + "<br/>" + "<br/>" +"Date taken:" + "  " + newdate(d.key) + "<br/>" + "<br/>" + "Average Reading:" + "  " + d.values.mean + "<br/>" + "<br/>" + 		"Parameter:" +  "  " + selectedParameter)
	.style("left", (d3.event.pageX) + "px")
	.style("top", (d3.event.pageY - 28) + "px");
	})
	.on("mouseout", function(d) {
	div.transition()
	.duration(500)
	.style("opacity", 0);
		});

circle.exit()
	.transition()
	.duration(750)
   	.attr('opacity',0)
	.remove();		
};		
});
}

HistoricDashboard.css

#historic
{
    width: 580px;
    height: 470px;
}
.axis path, .axis line
{
    fill: none;
    stroke: #fff;
    shape-rendering: crispEdges;
}
.line
{
    fill: none;
    stroke-width:0.5px;
}
text
{
    font-size: 11px;
    fill: #9b9b9b;
}

HistoricData.php

<?php
$username="******"; 
$password="******";   
$host="*******";
$database="*******";
$server = mysql_connect($host, $username, $password);
$connection = mysql_select_db($database, $server);
 
$myquery = "SELECT *
FROM TestSourceSampleData 
INNER JOIN TestSources on
TestSourceSampleData.testSource_id = TestSources.id
inner join Parameters on
TestSources.parameter_id = Parameters.id
GROUP BY dateTimeTaken";
 
$query = mysql_query($myquery);
if ( ! $myquery ) {
echo mysql_error();
die;
}
$data = array();
for ($x = 0; $x < mysql_num_rows($query); $x++) {
$data[] = mysql_fetch_assoc($query);
}
echo json_encode($data);
mysql_close($server);
?>

Index.html

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Dashboard</title>
<link href="HistoricDashboard.css" rel="stylesheet" type="text/css">
</head>
<body>
 
  <section id="graph">
     <section id="historic"></section>
  </section>
  
  <section id="selectparameter">              
    <select name='parameterType' id="parameterType">
		</select>       
		</section>
		
		<section id="selectmonth">    
		<select name='dateTimeTaken' id="dateTimeTaken">
		</select>	  
	  </section>
 
<script type="text/javascript" src="http://d3js.org/d3.v3.js"></script>
<script type="text/javascript" src="HistoricGraph.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js"></script>

<!--POPULATE DROPDOWNLISTS FROM DATABASE-->
<script type="text/javascript">

  	function loadlist(selobj, url, nameattr) {
			$(selobj).empty();
			$.getJSON(url, {}, function (data) {
				$.each(data, function (i, obj) {
					$(selobj).append($('<option></option>').val(obj[nameattr]).html(obj[nameattr]));
				});
			});
		}

		$(function () {
			loadlist($('select#parameterType').get(0), 'get-list.php?getlist=parameterType', 'parameterType');
			loadlist($('select#dateTimeTaken').get(0), 'get-list.php?getlist=dateTimeTaken', 'dateTimeTaken');
		});

	</script>
 
</body>
</html>

Output from HistoricData.php

[{"dateTimeTaken":"2013-02-01 13:49:25","reading":"0.90000","parameterType":"Flouride"},{"dateTimeTaken":"2013-02-01 13:49:30","reading":"1.70000","parameterType":"Temperature"},{"dateTimeTaken":"2013-02-01 13:49:35","reading":"0.40000","parameterType":"Flouride"},{"dateTimeTaken":"2013-02-01 13:49:40","reading":"1.60000","parameterType":"Temperature"},{"dateTimeTaken":"2013-02-01 14:02:18","reading":"1.60000","parameterType":"Temperature"},{"dateTimeTaken":"2013-02-01 14:02:23","reading":"0.80000","parameterType":"Flouride"},{"dateTimeTaken":"2013-02-01 14:02:28","reading":"11.00000","parameterType":"pH"},{"dateTimeTaken":"2013-02-01 14:02:33","reading":"1.30000","parameterType":"Temperature"},{"dateTimeTaken":"2013-02-01 14:02:38","reading":"1.00000","parameterType":"Temperature"},{"dateTimeTaken":"2013-02-01 14:02:43","reading":"0.40000","parameterType":"Flouride"},{"dateTimeTaken":"2013-02-01 14:02:48","reading":"0.40000","parameterType":"Flouride"},{"dateTimeTaken":"2013-02-01 14:02:53","reading":"0.60000","parameterType":"Temperature"}]