-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathchart.ejs
139 lines (104 loc) · 5.38 KB
/
chart.ejs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
<!DOCTYPE html>
<html>
<head>
<%- partial("partials/_header", { title: "Measure G Spending by School"}) %>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<!--Load the AJAX API. Do this only once per web page! -->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
var getSelectedYear = function() {
return <%= latestExpenditureDataYear %>;
};
// Capitalizes first letter
var formatSchoolName = function (school) {
// school = school.toLowerCase();
// var formatted = [];
// $.each(school.split(" "), function (k,word) {
// formatted.push(word.charAt(0).toUpperCase() + word.slice(1));
// });
// return formatted.join(" ");
return school;
};
google.charts.load('current', {'packages':['table']});
// this is a global variable in _harp.json
var latestEnrollmentYear = <%= latestEnrollmentYear %>;
var sql = "SELECT spend.*, e.total_enrollment, " +
"round((spend.ytd_actual / e.total_enrollment)::numeric, 2) as per_student " +
"FROM (SELECT " +
"SUM(mg.ytd_actual) as ytd_actual, " +
"s.schnam, " +
"s.site_type, " +
"s.ousd_site, " +
"MIN(s.ncessch) ncessch " +
"FROM <%= expenditureDataTable %> mg, measure_g_schools_lat_long s " +
"WHERE " +
"mg.site = s.ousd_site " +
"AND mg.year = " + getSelectedYear() + " " +
"GROUP BY s.schnam, s.site_type, s.ousd_site) spend, <%= enrollmentDataTable %> e " +
"WHERE spend.ncessch = e.ncessch " +
"AND e.year = " + latestEnrollmentYear;
var dataBySchool = [];
var windowheight = window.innerHeight - 100;
function drawTable() {
dataBySchool = google.visualization.arrayToDataTable(dataBySchool);
var table = new google.visualization.Table(document.getElementById('table_div'));
var formatter = new google.visualization.NumberFormat(
{prefix: '$', fractionDigits: 0});
formatter.format(dataBySchool, 2); // Apply formatter to $ columns
formatter.format(dataBySchool, 3);
//formatter.format(dataBySchool, 4);
// formatter.format(dataBySchool, 5);
// var formatter = new google.visualization.NumberFormat(
// {suffix: '%', fractionDigits: 1});
// formatter.format(dataBySchool, 6);
table.draw(dataBySchool,
{
showRowNumber: true,
width: '100%',
height: windowheight,
allowHtml: true,
sortColumn: 0
});
}
$.getJSON('https://jbaldo.cartodb.com/api/v2/sql/?q='+sql, function(data) {
$.each(data.rows, function(key, row) {
var schoolname = "<a href='school.html?schoolID="+row.ousd_site+"' target='_blank'>"+formatSchoolName(row.schnam)+"</a>";
var perPupilMeasureG = row.per_student;
//var measuregOverTotal = row.g2014/row.budget;
//item = [schoolname, row.total_enrollment, row.ytd_actual, perPupilMeasureG, row.budget, row.per_pupil_expenditures_formula, measuregOverTotal * 100]
item = [{v: row.schnam, f: schoolname}, row.total_enrollment, row.ytd_actual, perPupilMeasureG];
dataBySchool.push(item);
});
//Add a header to each program data set (only happens once)
//dataBySchool.unshift(['School', 'Enrollment', 'Measure G Total $', 'Measure G $ Per Pupil', 'School Total $', 'Total $ Per Pupil', '% from Measure G']);
dataBySchool.unshift(['School', 'Enrollment (' + latestEnrollmentYear + ')', 'Measure G Total $ (' + getSelectedYear() + ')', 'Measure G $ Per Pupil']);
}).then(function() {
google.charts.setOnLoadCallback(drawTable);
});
</script>
<!--Google analytics snippet -->
<script>
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-35908939-11', 'auto');
ga('send', 'pageview');
</script>
</head>
<body>
<!-- Wrap all page content here -->
<div id="wrap">
<%- partial("partials/_navbar") %>
<!-- Begin page content -->
<div class="center-container">
<div class="center-row">
<div class="container">
<div id="table_div"></div>
</div>
</div>
</div>
</div> <!-- end wrap -->
<%- partial("partials/_uservoice.ejs") %>
</body>
</html>