-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcanvasChart3.html
330 lines (288 loc) · 14.9 KB
/
canvasChart3.html
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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
<!DOCTYPE html>
<!--=====================================================================================================
<< Create HTML page, and request data from the database server using AJAX.
<< Data is retieved on load, and when new SQL is entered
<< Must provide the database connection info in fields called db, user, pswd
<< Must provide SQL statements in a field called sql
<< Must provide output format output=json
<< Must provide a div tag called "divTable" where the table will be displayed
<< Must provide a canvas tag to chart the data
======================================================================================================-->
<html>
<head>
<title>Read data from server using AJAX and Chart It</title>
<script src=database/ajax.js> </script>
<script src=database/table.js></script>
<script src=chartBar.js> </script>
<script src=chartPie.js> </script>
<script src=chartLine.js> </script>
<script src=chartBubble.js> </script>
<script src=chartScatter.js> </script>
<script src=dump.js> </script>
<style>
@import url("database/table.css");
body {white-space: nowrap}
select {width:150px;}
#database {position:absolute; top:72px; left:710px; }
#divTable {position:absolute; top:190px; left:100px; height:250px; width:600px; border:2px solid lightgray; background-color:white;}
#chartType {position:absolute; top:450px; left:100px; }
#message {position:absolute; top:455px; left:560px; }
#chart {position:absolute; top:490px; left:100px; height:300px; width:600px; border:2px solid lightgray}
#fields {position:absolute; top:490px; left:710px; }
#canvas {float:left}
#legend {padding-left:320px}
#connect {visibility:hidden}
</style>
</head>
<body bgcolor=lightyellow>
<h2>Read from Database using AJAX Web Service and Chart It</h2>
<table>
<tbody>
<tr>
<td>Enter SQL.... <br>
or JSON....<br><br>
<a href=examples.html target=_blank><i>examples</i></a></td>
<td><textarea id="_sql" style='height:74px; width:595px' wrap="off"></textarea>
</tr>
<tr>
<td></td>
<td><input type="submit" value=" Execute " onClick="doRequest()">
<input type="radio" name="db" id="demo" checked
onClick="document.getElementById('connect').style.visibility='hidden'; doRequest()"> Demo Database
<input type="radio" name="db" id="demods"
onClick="document.getElementById('connect').style.visibility='hidden'; doRequest()"> Demo ODS
<input type="radio" name="db" id="demodw"
onClick="document.getElementById('connect').style.visibility='hidden'; doRequest()"> Demo Data Warehouse
<input type="radio" name="db" id="other"
onClick="document.getElementById('connect').style.visibility='visible'">Other
<span id="connect">
<input type="text" id="user" size=5 placeholder='user'>
<input type="password" id="pswd" size=5 placeholder='pswd'>
</span>
</tr>
</tbody>
</table>
<div id="database"><select id="dbtype" style='width:70px'>
<option value='mysql' selected> Mysql </option>
<option value='oracle'> Oracle</option>
</select>
<br><br><input type="checkbox" id="seeJSON">see returned JSON
</div>
<div id="divTable"></div>
<div id="chartType">
<img src=bar.jpg border=1 height=30 width=30 onclick="chartIt('bar')">
<img src=pie.jpg border=1 height=30 width=30 onclick="chartIt('pie')">
<img src=line.jpg border=1 height=30 width=30 onclick="chartIt('line')">
<img src=bubble.jpg border=1 height=30 width=30 onclick="chartIt('bubble')">
<img src=scatter.jpg border=1 height=30 width=30 onclick="chartIt('scatter')">
</div>
<div id="message">
<i>(Maximum 12 Rows)</i>
</div>
<div id="chart" style='background-color:white'>
<canvas id="canvas" width=300 height=300></canvas>
<div id="legend"></div>
</div>
<div id="fields"></div>
<script>
//=================================================================================================
lastType = 'bar'; //start with bar chart
doRequest()
//=================================================================================================
// doRequest: process the request at beginning and when execute button is clicked
//=================================================================================================
function doRequest()
{
if (document.getElementById('demo').checked) //which database?
{
var connect = "user=demo&pswd=demo";
var db = "demo";
}
if (document.getElementById('demods').checked)
{
var connect = "user=demods&pswd=demods";
var db = "demods";
}
if (document.getElementById('demodw').checked)
{
var connect = "user=demodw&pswd=demodw";
var db = "demodw";
}
if (document.getElementById('other').checked)
{
var connect = "user=" + document.getElementById('user').value
+ "&pswd=" + document.getElementById('pswd').value;
var db = document.getElementById('user').value
}
if (document.getElementById('dbtype').value == 'mysql')
connect += '&dbtype=mysql&db=' + db;
else
connect += '&dbtype=oracle&db=ORCL';
var sql = document.getElementById("_sql").value; //get search string entered in the form
if (!sql) //get list of tables
if (document.getElementById('dbtype').value == 'mysql')
sql = 'show tables';
else
sql = 'select table_name from user_tables';
sql = sql.replace(/;\s*$/ , ''); //eliminate last ';' if any
if (sql.match(/^\s*(select|with|show|desc)/i) ) //if entry is 'select','with','show' or 'desc'
{
sql = escape(sql); //URL encode the sql
var param = connect +"&sql="+ sql +"&format=json"; //create "db=...&sql=...&format=json"
var callback = doResponse; //setup a callback function
ajaxRequest(url, "GET", param, callback); //call ajax request
}
else
{
alert('assuming JSON') //assume entry is JSON
json = sql
doResponse(json,'',json,'') //continue processing
}
}
//========================================================================================
// doResponse: receives the ajax response in all the following formats:
// respText - the response as a text format. Also used for HTML
// respXML - the response as an XML DOM object
// respJSON - the response as a JSON object
// respHeaders - All response headers as a single string
//========================================================================================
function doResponse(respText, respXML, respJSON, respHeaders)
{
if (document.getElementById('seeJSON').checked)
alert(respText);
try {
var json = JSON.parse(respText); //convert returned text to JSON array of objects
}
catch(ex) {
alert('Bad JSON string: \n' + ex) //display JSON parse error
}
headers = []; //global array for headers
data2 = []; //global 2 dimensional array for data
//convert json into headers array and 2 dim data array
for (var i=0; i < json.length; i++) //loop through the outer json array
{
var j = 0;
var array = [];
var obj = json[i]; //each json line is an object
for (prop in obj) //loop through the inner json object
{
headers[j] = prop; //property name
array[j] = obj[prop] //property value
j++;
}
data2[i] = array; //build 2 dimensional data array
}
var dataCopy = data2.slice(0); //copy the array (so any Table sort not effect order of values)
Table(headers, dataCopy, -1, 'asc') //call table.js to display
display_fields(headers); //call display_fields
chartIt(lastType); //call chartIt
}
//===================================================================================================
// Display a list of column names in checkboxes
// Check the first dimension (non numeric) and the first measure (numeric)
//===================================================================================================
function display_fields()
{
var col;
var alpha = false
var numeric = false
for (col=0; col<headers.length; col++) //check to see if there is an alpha column
if (isNaN(data2[0][col]))
alpha = true;
for (col=0; col<headers.length; col++) //check to see if there is a numeric column
if (!isNaN(data2[0][col]))
numeric = true;
if (!alpha) //if there are no alpha columns
{
headers[col] = 'DimX'; //add an alpha column header
for (var i=0; i < data2.length; i++) //loop thru the rows
data2[i][col] = 'X'; //add an alpha column as last column
}
if (!numeric) //if there are no numeric columns
{
headers[col] = 'Num'; //add a numeric column header
for (var i=0; i < data2.length; i++) //loop thru the rows
data2[i][col] = i+1; //add a numeric column as last column
}
var dims = '<b>Attributes </b><br> <select id=dims size=8 onChange=chartIt(lastType) >';
for (i=0; i<headers.length; i++)
{
if (isString(data2[0][i]) && isNaN(data2[0][i])) //if string
dims += "<option id=d" + i + "> "+ headers[i] + "</option>";
}
dims += '</select><br>';
var facts = '<b>Facts/Measures </b><br> <select id=facts size=7 onChange=chartIt(lastType) >';
for (i=0; i<headers.length; i++)
{
if (! isNaN(data2[0][i])) //if numeric
facts += "<option id=f" + i + "> "+ headers[i] +"</option>";
}
facts += '</select>';
document.getElementById('fields').innerHTML = dims + facts;
for (i=0; i<headers.length; i++)
if (isString(data2[0][i]) && isNaN(data2[0][i])) //if string
{
document.getElementById('d'+i).selected = true; //select the first dimension
break;
}
for (i=0; i<headers.length; i++)
if (!isNaN(data2[0][i])) //if numeric
{
document.getElementById('f'+i).selected = true; //select the first fact/measure
break;
}
}
//===================================================================================================
// Test for a string
//===================================================================================================
function isString (obj)
{
return (Object.prototype.toString.call(obj) === '[object String]');
}
//===================================================================================================
// Chart a 2 dimensional array.
// Array columns: col 1 for labels, column 2 or 3 must be numeric
// Array rows: maximum = 12 rows
//===================================================================================================
function chartIt(chartType)
{
var dataObj = {}; //object to chart {label1:[val1,val2,val3], label2:[...]}
// if (document.getElementById('dims').length ==0 ||
// document.getElementById('facts').length==0)
// alert('Must have at least one dimension and one measure column for chart');
var i = document.getElementById('dims').selectedIndex //the chosen dimension
var dimId = document.getElementById('dims')[i].id //the id of the chosen dimension
var dim = dimId.substr(1); //get rid of the leading "d"
var j = document.getElementById('facts').selectedIndex //the chosen fact
var factId = document.getElementById('facts')[j].id //the id of the chosen fact
var fact = factId.substr(1); //get rid of the leading "f"
var prev_label = '';
var values = []; //array to hold all the values for the same data point (col 1)
for (var i=0; i < data2.length; i++) //pivot data from rows to columns (based on value of col 1)
{
if (Object.keys(dataObj).length >= 12) //graph can only process 12 data points (300px/25)
break;
var label = data2[i][dim]; //use the dimension for label
var value = 0;
value = Number(data2[i][fact]); //use the fact for the value
if (! dataObj[label])
dataObj[label] = new Array();
values = dataObj[label]; //get the value array
values.push(value); //add the new value to the array
dataObj[label] = new Array()
dataObj[label] = values; //add the array to the object
}
if (chartType=='bar')
drawBarChart(dataObj,'canvas','legend');
if (chartType=='pie')
drawPieChart(dataObj,'canvas','legend');
if (chartType=='line')
drawLineChart(dataObj,'canvas','legend');
if (chartType=='bubble')
drawBubbleChart(dataObj,'canvas','legend');
if (chartType=='scatter')
drawScatterChart(dataObj,'canvas','legend');
lastType = chartType; //save the last chart type
}
</script>
</body>