psychemedia/govspending.php

## govspending.php
<html><head><title>Guardian Datastore Explorer - Gov Spending Data, Released November 2010</title>

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {'packages':['table', 'scatterchart', 'linechart','piechart', 'barchart','columnchart']});

<?php if ($_GET['run']) echo "google.setOnLoadCallback(preview2);"; ?>

function removeChildrenFromNode(node) {
    if(node.hasChildNodes()) {
        while(node.childNodes.length >= 1 ) {
            node.removeChild(node.firstChild);
        }
    }
}

var deliObj;
var dps="<?php if($_GET['dps']!='') echo $_GET['dps']  ?>";
function deliList(o){
	deliObj=o;
	var ds=document.getElementById('datastore');
	for (var i=0; i<o.length;i++) {
		//o[i].d, o[i].u o[i].n.split('--')[1]
		var opt=document.createElement('option');
		opt.setAttribute('value',i);
		opt.innerHTML=o[i].d;
		ds.appendChild(opt);
	}
}

function preview3(){
	var sel=document.getElementById('datastore');
	var selection=sel.selectedIndex;
	sel=sel[selection].value;
	//var key=deliObj[sel].n.split('--')[1];
	var key=deliObj[sel].u;
	if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];
	document.getElementById('gsKey').value=key;
	var txt='<a href="'+deliObj[sel].u+'">'+deliObj[sel].d+'</a>';
	document.getElementById('deptsrc').innerHTML=deliObj[sel].d;
	dps=deliObj[sel].d;
	document.getElementById('statrep').innerHTML=txt;
	preview();
}

function preview2(){
	preview();
	drawViz();
}

  var key='rvWgEEGK9xuUQBR1EFcxHWA';

  function preview() {
  	  if (document.getElementById('gsKey').value!='') key=document.getElementById('gsKey').value;
  	  if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];

  	  var sheet=document.getElementById('gsSheet').value;
  	  if (sheet!='') key+='&gid='+sheet;

  	  var url='http://spreadsheets.google.com/tq?tq=select%20*where%20A=false&key='+key;
      var query = new google.visualization.Query(url);

      query.send(handleQueryResponse);
    }

  function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();
      visualization = new google.visualization.Table(document.getElementById('headings'));
      visualization.draw(data, null);
     columnList(data);
     selTest(data)
  }

  function createCell(str){
    var el=document.createElement('td');
    el.innerHTML=str;
  	return el;
  }

  function columnList(data){

  	var t=document.getElementById('colLookup');
  	removeChildrenFromNode(t);
  	var labels=document.createElement('tr');
  	var headings=document.createElement('tr');

  	for (var i=0;i<data.getNumberOfColumns();i++){
  	  var cell=createCell(data.getColumnId(i));
  	  labels.appendChild(cell);
  	  cell=createCell(data.getColumnLabel(i));
  	  headings.appendChild(cell);
  	}

  	t.appendChild(labels);
  	t.appendChild(headings);
  }

  function createSelItem(str, col){
    var el=document.createElement('option');
    el.innerHTML=str;
    el.setAttribute('value',col);
   // el.setAttribute('label',col);
  	return el;
  }

  function optionsChange(){
  	var gqo; var flag=false;
  	var sel=document.getElementById('nameTest3');
  	for (var i = 0; i < sel.options.length; i++)
    	if (sel.options[ i ].selected) {
    		if (flag){gqo+=','+sel.options[i].value;}
    		else {gqo=sel.options[i].value;flag=true;}
    	}
    document.getElementById('gqo').value=gqo;
  }

  function whereChange(){
  	var gqw='';
    var where=document.getElementById('nameTest2');
    var flag=-1;
    for (var i = 0; i < where.options.length; i++)
    	if (where.options[ i ].selected) {
    		if (flag!=-1){if (flag==0) gqw='('+gqw;gqw+=' and|or '+where.options[i].value+' **contains|matches|<|>|=[0-9|\']**';flag++}
    		else {gqw+=where.options[i].value+' **contains|matches|<|>|=[0-9|\']**';flag++; }
    }
    if (flag>0) gqw+=')';
    document.getElementById('gqw').value=gqw;
  }

  function whereSel(){
  	//iterate options in nameTest and clone selected ones into here
  	var sel=document.getElementById('nameTest');
  	var where=document.getElementById('nameTest2');
  	var order=document.getElementById('nameTest3');

  	while (where.childNodes[0]) { where.removeChild(where.childNodes[0]);}
  	while (order.childNodes[0]) { order.removeChild(order.childNodes[0]);}

	var flag=false;
	var gqc; var gqo;
  	for (var i = 0; i < sel.options.length; i++)
    	if (sel.options[ i ].selected) {
    		if (flag){gqo=gqc+=','+sel.options[i].value;}
    		else {gqo=gqc=sel.options[i].value;flag=true;}
    		where.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
    		order.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
    	}
    document.getElementById('gqc').value=gqc;
   // document.getElementById('gqo').value=gqo;
  }

  function childSelTest(id,fn,node){
    var sl=document.createElement('select');
    sl.setAttribute('id',id);//sl.setAttribute('id','nameTest2');
    sl.setAttribute('multiple','');
    sl.setAttribute('size','8');
    sl.setAttribute('onchange',fn);//sl.setAttribute('onchange','whereChange()');

    document.getElementById(node).appendChild(sl);//document.getElementById('boxes2').appendChild(sl);
  }

  function selTest(data){
    var sl=document.createElement('select');
    sl.setAttribute('id','nameTest');
    sl.setAttribute('multiple','');
    sl.setAttribute('size','8');
    sl.setAttribute('onchange','whereSel()');
    for (var i=0;i< data.getNumberOfColumns();i++){
      var txt='('+data.getColumnId(i)+') '+data.getColumnLabel(i);
      sl.appendChild(createSelItem(txt, data.getColumnId(i)));
    }
    removeChildrenFromNode(document.getElementById('boxes'));
    removeChildrenFromNode(document.getElementById('boxes2'));
    removeChildrenFromNode(document.getElementById('boxes3'));

    document.getElementById('boxes').appendChild(sl);

    childSelTest('nameTest2','whereChange()','boxes2');
    childSelTest('nameTest3','optionsChange()','boxes3');
  }

  function drawViz() {
  	  var gqc=document.getElementById('gqc').value
  	  var gq='select '+gqc;
  	  gq=encodeURIComponent(gq);

  	  var gqw=document.getElementById('gqw').value;

  	  if (gqw!='') gq+=' where '+escape(gqw);

  	  var gqg=document.getElementById('gqg').value;
  	  if (gqg!='') gq+=' group by '+encodeURIComponent(gqg);

  	  var gqo=document.getElementById('gqo').value;
  	  var sel=document.getElementById('gqad');
	  var selection=sel.selectedIndex;
	  gqad=sel[selection].value;
  	  if (gqo!='') gq+=' order by '+encodeURIComponent(gqo)+' '+gqad;

	  var gql=document.getElementById('gql').value;
  	  if (gql!='') gq+=' limit '+encodeURIComponent(gql);

	  document.getElementById('gqpreview').innerHTML=gq;
  	  //gq=encodeURIComponent(gq);

  	  var url='http://spreadsheets.google.com/tq?tq='+gq+'&key='+key;

  	  var purl='http://spreadsheets.google.com/tq?tqx=out:html&tq='+gq+'&key='+key;
  	  var l=document.getElementById('htmlout');
  	  l.innerHTML="<a href='"+purl+"'>HTML preview URL</a>";
  	  purl='http://spreadsheets.google.com/tq?tqx=out:csv&tq='+gq+'&key='+key;
  	  l=document.getElementById('csvout');
  	  l.innerHTML="<a href='"+purl+"'>CSV URL</a>";


  	  var b=document.getElementById('bookmark');
  	  var bookmark="govspending.php?run=true&gsKey="+key+"&gqc="+encodeURIComponent(gqc)+"&gqw="+escape(gqw)+"&gqo="+encodeURIComponent(gqo)+"&gql="+encodeURIComponent(gql)+"&gqg="+encodeURIComponent(gqg)+"&dps="+encodeURIComponent(dps);
  	  b.innerHTML="<a href='"+bookmark+"'>bookmark</a>";

      var query = new google.visualization.Query(url);

      query.send(handleQueryResponse2);
    }


   function handleQueryResponse2(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();


      removeChildrenFromNode(document.getElementById('preview'));

      var typ;
      var sel=document.getElementsByName('opType');
      for (var i = 0; i < sel.length; i++)
    	if (sel[ i ].checked) typ=sel[i].value;
      switch (typ){
      	case 'table': tablechart(data);break;
      	case 'linechart': linechart(data);break;
      	case 'scatterchart': scatterchart(data);break;
      	case 'piechart': piechart(data);break;
      	case 'barchart': barchart(data);break;
      	case 'columnchart': columnchart(data);break;
      	default:;
      }

      //visualization = new google.visualization.Table(document.getElementById('preview'));
      //visualization.draw(data, null);

      //linechart(data);
      //piechart(data);
      //scatterchart(data);
  }

  function tablechart(data){
     new google.visualization.Table(document.getElementById('preview')).draw(data, null);
  }

  function scatterchart(data){
  	new google.visualization.ScatterChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
  }

  function linechart(data){
  	new google.visualization.LineChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
  }

  function piechart(data){
  	new google.visualization.PieChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
  }

  function barchart(data){
  	new google.visualization.BarChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
  }

  function columnchart(data){
  	new google.visualization.ColumnChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
  }

</script>

</head>
<body>
<h1>Guardian Datastore Explorer - UK Gov Spending Data, Released November 2010</h1>
<p> Explore the details of the UK Government Spending Data by using the departmental spreadsheets as individual databases (<a href="http://www.guardian.co.uk/news/datablog/2010/nov/19/government-spending-data?CMP=ouseful_gsdde">Guardian Data Blog: data details</a>). For information about the query syntax, see <a href="http://code.google.com/apis/visualization/documentation/querylanguage.html">Google Chart Tools: Query Language Reference (Version 0.7)</a>. For more details about this application, see <a href="http://blog.ouseful.info">OUseful.info</a>. <em>Tony Hirst, Dept of Communication and Systems, The Open University</em></p>
<form name="config">
<input type="hidden" size=30 id='gsKey' <?php if($_GET['gsKey']!='') echo "value=\"".$_GET['gsKey']."\""  ?> /> <input type="hidden" size=3 id='gsSheet' <?php if($_GET['gid']!='') echo "value=\"".$_GET['gid']."\""  ?> /> <br/>
<p>Grab a sheet from the Guardian Data store: <select name="dList" id="datastore"></select><input type="button" value="Preview" onclick="preview3()" />
<p>If you are prompted to sign in, wait a few seconds and then try again.</p>
<div id="statrep"></div>
</form>
<hr/>
<div id="headings"></div>

<br/><br/>
<p>Using data from spreadsheet:<p><h1 id='deptsrc'><?php if($_GET['dps']!='') echo $_GET['dps']  ?></h1>
<br/><br/><hr/>
<div id="preview"></div>
<div id="linechart"></div>
<div id="scatterchart"></div>
<div id="piechart"></div>
<br/>
<p>Try out some visualisation queries here...</p>
<table id='colLookup' border='1'></table>

<br/>

<p>Select the columns from the list box (ctrl+click for multiple selections in IE, command+click on a Mac) or type the elements directly into the appropriate text box.</p><br/>
<form>
<div><tt>select</tt> <span id='boxes'></span> <input type="text" id="gqc" size=20 <?php if($_GET['gqc']!='') echo "value=\"".$_GET['gqc']."\""  ?> /><br/>
<br/><tt>where</tt> <span id='boxes2'></span> <input type="text" id="gqw" size=30 <?php if($_GET['gqw']!='') echo "value=\"".stripslashes($_GET['gqw'])."\""  ?> /> <tt>group by</tt> <input type="text" id="gqg" size=30 <?php if($_GET['gqg']!='') echo "value=\"".$_GET['gqg']."\""  ?> />
<br/><br/><tt>order by</tt> <span id='boxes3'></span> <input type="text" id="gqo" size=30 <?php if($_GET['gqo']!='') echo "value=\"".$_GET['gqo']."\""  ?> /> <select id="gqad" name="gqad"><option value='asc' selected>asc</option><option value='desc'>desc</option>
</select> <tt>limit</tt> <input type="text" id="gql" size=10 <?php if($_GET['gql']!='') echo "value=\"".$_GET['gql']."\""  ?> /></div>
<div>Display as:
| Table <input type="radio" name="opType" value="table" checked />
| Scatter chart <input type="radio" name="opType" value="scatterchart"/>
| Line chart <input type="radio" name="opType" value="linechart"/>
| Pie chart <input type="radio" name="opType" value="piechart"/>
| Bar chart <input type="radio" name="opType" value="barchart"/>
| Column chart <input type="radio" name="opType" value="columnchart"/>
|
<input type="button" value='Go Fish' onclick="drawViz()"/>
</div>
<div>So you are asking: <em id='gqpreview'></em></div>
<div>Here is the URL for that query: <span id='htmlout'></span>, <span id='csvout'></span></div>
<div>here is a link to this page: <span id="bookmark"></span></div>

</form>


<h2>Go fish...</h2>
<form name='qform'>
<div>Now it's time to write your own query in two parts - the columns you want to select, and the conditions you want each returned result to satisfy. ***FOLLOWING EXAMPLES NEED TWEAKING FOR CURRENT CONTEXT</div>
<table border=1><tr><th>Department</th><th><tt>select</tt> input</th><th><tt>where</tt> input</th><th><tt>group by</tt> input</th><th><tt>order by</tt> input</th><th><tt>limit</tt> input</th><th>Comments</th></tr>
<tr><td>All</td><td>*</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>20</td><td>Display everything (dangerous! May take some time and/or kill your browser! Hence the limit term...) [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=*&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
<tr><td>Treasury</td><td>F,G,H</td><td>&nbsp;</td><td>Display columns F,G, H (Expense Type, Expense area and supplier)</td><td>&nbsp;</td><td>20</td><td>Use this approach to get a feel for what a column contains [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=F%2CG%2CH&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
<tr><td>Home Office</td><td>E,I,K</td><td>K > 20000000</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>Display columns E, I and K (Entity, Supplier, Amount) sums over 20 million [<a href="govspending.php?run=true&gsKey=tnBJa5GzHGs6BdHL2K-5N9w&gqc=E%2CI%2CK&gqw=K%20%3E20000000&gqo=&gql=&gqg=">try it</a>]</td></tr>
<tr><td>DFID</td><td>H,I,K</td><td>H contains 'Africa' and K > 10000000</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>View suppliers and amounts over a million for expense areas containing 'Africa'; (as well as the free text search 'contains', 'matches' must match exactly) [<a href="govspending.php?run=true&gsKey=0AonYZs4MzlZbdFdjaGVOVFAydm5sUUlTb09JUzFaNXc&gqc=H%2CI%2CK&gqw=H%20contains%20%27Africa%27%20and%20K%20%3E%2010000000&gqo=&gql=&gqg=&dps=International%20Development%20(DFID)">try it</a>]</td></tr>
<tr><td>Government Equalities Office</td><td>F,G,H,J</td><td>J > 100000</td><td>&nbsp;</td><td>J <em>desc</em></td><td>&nbsp;</td><td>View suppliers and amounts over a hundred thousand, sorted by amount (note that if you click on column headings in the table, you can sort the table by that column) [<a href="govspending.php?run=true&gsKey=tKUQuJiBekBQxlDcasQFFaQ&gqc=F%2CG%2CH%2CJ&gqw=J%20%3E%20100000&gqo=J&gql=&gqg=&dps=Government%20Equalities%20Office%20(GEO)">try it</a>]</td></tr>

<tr><td>Health</td><td>H,J</td><td>J < 0</td><td>&nbsp;</td><td>J <em>asc</em></td><td>&nbsp;</td><td>View suppliers and amounts where the amount is negative... [<a href="govspending.php?run=true&gsKey=tPdRIE1Dtovo5adgjt_D5rA&gqc=H%2CJ&gqw=J%20%3C%200&gqo=J&gql=&gqg=&dps=Health%20(DOH)">try it</a>]</td></tr>

<tr><td>DCMS</td><td>G,H,I,sum(K),count(K)</td><td>K >0 and H contains 'Museums'</td><td>G,H,I</td><td>&nbsp;</td><td>&nbsp;</td><td>Look at expense type, area and supplier where area contains 'Museums', then group by expense type, area and supplier, reporting total amount awarded to each supplier correspondingly, along with a count of how many payments were made[<a href="govspending.php?run=true&gsKey=tKlkQ_ocEHaUa3RDgIee5sQ&gqc=G%2CH%2CI%2Csum(K)%2Ccount(K)&gqw=K%20%3E0%20and%20H%20contains%20%27Museums%27&gqo=&gql=&gqg=G%2CH%2CI&dps=Culture%2C%20Media%20and%20Sport%20(DCMS)">try it</a>]</td></tr>
</table>
<script type="text/javascript" src="http://feeds.delicious.com/v2/json/psychemedia/gdatastore_11_2010?callback=deliList"></script>
</body>
</html>
	<html><head><title>Guardian Datastore Explorer - Gov Spending Data, Released November 2010</title>

	<script type="text/javascript" src="http://www.google.com/jsapi"></script>
	<script type="text/javascript">
	google.load('visualization', '1', {'packages':['table', 'scatterchart', 'linechart','piechart', 'barchart','columnchart']});

	<?php if ($_GET['run']) echo "google.setOnLoadCallback(preview2);"; ?>

	function removeChildrenFromNode(node) {
	if(node.hasChildNodes()) {
	while(node.childNodes.length >= 1 ) {
	node.removeChild(node.firstChild);
	}
	}
	}

	var deliObj;
	var dps="<?php if($_GET['dps']!='') echo $_GET['dps'] ?>";
	function deliList(o){
	deliObj=o;
	var ds=document.getElementById('datastore');
	for (var i=0; i<o.length;i++) {
	//o[i].d, o[i].u o[i].n.split('--')[1]
	var opt=document.createElement('option');
	opt.setAttribute('value',i);
	opt.innerHTML=o[i].d;
	ds.appendChild(opt);
	}
	}

	function preview3(){
	var sel=document.getElementById('datastore');
	var selection=sel.selectedIndex;
	sel=sel[selection].value;
	//var key=deliObj[sel].n.split('--')[1];
	var key=deliObj[sel].u;
	if (/key=([^&])/.test(key)) key=/key=([^&])/.exec(key)[1];
	document.getElementById('gsKey').value=key;
	var txt='<a href="'+deliObj[sel].u+'">'+deliObj[sel].d+'</a>';
	document.getElementById('deptsrc').innerHTML=deliObj[sel].d;
	dps=deliObj[sel].d;
	document.getElementById('statrep').innerHTML=txt;
	preview();
	}

	function preview2(){
	preview();
	drawViz();
	}

	var key='rvWgEEGK9xuUQBR1EFcxHWA';

	function preview() {
	if (document.getElementById('gsKey').value!='') key=document.getElementById('gsKey').value;
	if (/key=([^&])/.test(key)) key=/key=([^&])/.exec(key)[1];

	var sheet=document.getElementById('gsSheet').value;
	if (sheet!='') key+='&gid='+sheet;

	var url='http://spreadsheets.google.com/tq?tq=select%20*where%20A=false&key='+key;
	var query = new google.visualization.Query(url);

	query.send(handleQueryResponse);
	}

	function handleQueryResponse(response) {
	if (response.isError()) {
	alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
	return;
	}

	var data = response.getDataTable();
	visualization = new google.visualization.Table(document.getElementById('headings'));
	visualization.draw(data, null);
	columnList(data);
	selTest(data)
	}

	function createCell(str){
	var el=document.createElement('td');
	el.innerHTML=str;
	return el;
	}

	function columnList(data){

	var t=document.getElementById('colLookup');
	removeChildrenFromNode(t);
	var labels=document.createElement('tr');
	var headings=document.createElement('tr');

	for (var i=0;i<data.getNumberOfColumns();i++){
	var cell=createCell(data.getColumnId(i));
	labels.appendChild(cell);
	cell=createCell(data.getColumnLabel(i));
	headings.appendChild(cell);
	}

	t.appendChild(labels);
	t.appendChild(headings);
	}

	function createSelItem(str, col){
	var el=document.createElement('option');
	el.innerHTML=str;
	el.setAttribute('value',col);
	// el.setAttribute('label',col);
	return el;
	}

	function optionsChange(){
	var gqo; var flag=false;
	var sel=document.getElementById('nameTest3');
	for (var i = 0; i < sel.options.length; i++)
	if (sel.options[ i ].selected) {
	if (flag){gqo+=','+sel.options[i].value;}
	else {gqo=sel.options[i].value;flag=true;}
	}
	document.getElementById('gqo').value=gqo;
	}

	function whereChange(){
	var gqw='';
	var where=document.getElementById('nameTest2');
	var flag=-1;
	for (var i = 0; i < where.options.length; i++)
	if (where.options[ i ].selected) {
	if (flag!=-1){if (flag==0) gqw='('+gqw;gqw+=' and\|or '+where.options[i].value+' contains\|matches\|<\|>\|=[0-9\|\']';flag++}
	else {gqw+=where.options[i].value+' contains\|matches\|<\|>\|=[0-9\|\']';flag++; }
	}
	if (flag>0) gqw+=')';
	document.getElementById('gqw').value=gqw;
	}

	function whereSel(){
	//iterate options in nameTest and clone selected ones into here
	var sel=document.getElementById('nameTest');
	var where=document.getElementById('nameTest2');
	var order=document.getElementById('nameTest3');

	while (where.childNodes[0]) { where.removeChild(where.childNodes[0]);}
	while (order.childNodes[0]) { order.removeChild(order.childNodes[0]);}

	var flag=false;
	var gqc; var gqo;
	for (var i = 0; i < sel.options.length; i++)
	if (sel.options[ i ].selected) {
	if (flag){gqo=gqc+=','+sel.options[i].value;}
	else {gqo=gqc=sel.options[i].value;flag=true;}
	where.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
	order.appendChild(createSelItem(sel.options[i].innerHTML, sel.options[i].value));
	}
	document.getElementById('gqc').value=gqc;
	// document.getElementById('gqo').value=gqo;
	}

	function childSelTest(id,fn,node){
	var sl=document.createElement('select');
	sl.setAttribute('id',id);//sl.setAttribute('id','nameTest2');
	sl.setAttribute('multiple','');
	sl.setAttribute('size','8');
	sl.setAttribute('onchange',fn);//sl.setAttribute('onchange','whereChange()');

	document.getElementById(node).appendChild(sl);//document.getElementById('boxes2').appendChild(sl);
	}

	function selTest(data){
	var sl=document.createElement('select');
	sl.setAttribute('id','nameTest');
	sl.setAttribute('multiple','');
	sl.setAttribute('size','8');
	sl.setAttribute('onchange','whereSel()');
	for (var i=0;i< data.getNumberOfColumns();i++){
	var txt='('+data.getColumnId(i)+') '+data.getColumnLabel(i);
	sl.appendChild(createSelItem(txt, data.getColumnId(i)));
	}
	removeChildrenFromNode(document.getElementById('boxes'));
	removeChildrenFromNode(document.getElementById('boxes2'));
	removeChildrenFromNode(document.getElementById('boxes3'));

	document.getElementById('boxes').appendChild(sl);

	childSelTest('nameTest2','whereChange()','boxes2');
	childSelTest('nameTest3','optionsChange()','boxes3');
	}

	function drawViz() {
	var gqc=document.getElementById('gqc').value
	var gq='select '+gqc;
	gq=encodeURIComponent(gq);

	var gqw=document.getElementById('gqw').value;

	if (gqw!='') gq+=' where '+escape(gqw);

	var gqg=document.getElementById('gqg').value;
	if (gqg!='') gq+=' group by '+encodeURIComponent(gqg);

	var gqo=document.getElementById('gqo').value;
	var sel=document.getElementById('gqad');
	var selection=sel.selectedIndex;
	gqad=sel[selection].value;
	if (gqo!='') gq+=' order by '+encodeURIComponent(gqo)+' '+gqad;

	var gql=document.getElementById('gql').value;
	if (gql!='') gq+=' limit '+encodeURIComponent(gql);

	document.getElementById('gqpreview').innerHTML=gq;
	//gq=encodeURIComponent(gq);

	var url='http://spreadsheets.google.com/tq?tq='+gq+'&key='+key;

	var purl='http://spreadsheets.google.com/tq?tqx=out:html&tq='+gq+'&key='+key;
	var l=document.getElementById('htmlout');
	l.innerHTML="<a href='"+purl+"'>HTML preview URL</a>";
	purl='http://spreadsheets.google.com/tq?tqx=out:csv&tq='+gq+'&key='+key;
	l=document.getElementById('csvout');
	l.innerHTML="<a href='"+purl+"'>CSV URL</a>";


	var b=document.getElementById('bookmark');
	var bookmark="govspending.php?run=true&gsKey="+key+"&gqc="+encodeURIComponent(gqc)+"&gqw="+escape(gqw)+"&gqo="+encodeURIComponent(gqo)+"&gql="+encodeURIComponent(gql)+"&gqg="+encodeURIComponent(gqg)+"&dps="+encodeURIComponent(dps);
	b.innerHTML="<a href='"+bookmark+"'>bookmark</a>";

	var query = new google.visualization.Query(url);

	query.send(handleQueryResponse2);
	}


	function handleQueryResponse2(response) {
	if (response.isError()) {
	alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
	return;
	}

	var data = response.getDataTable();


	removeChildrenFromNode(document.getElementById('preview'));

	var typ;
	var sel=document.getElementsByName('opType');
	for (var i = 0; i < sel.length; i++)
	if (sel[ i ].checked) typ=sel[i].value;
	switch (typ){
	case 'table': tablechart(data);break;
	case 'linechart': linechart(data);break;
	case 'scatterchart': scatterchart(data);break;
	case 'piechart': piechart(data);break;
	case 'barchart': barchart(data);break;
	case 'columnchart': columnchart(data);break;
	default:;
	}

	//visualization = new google.visualization.Table(document.getElementById('preview'));
	//visualization.draw(data, null);

	//linechart(data);
	//piechart(data);
	//scatterchart(data);
	}

	function tablechart(data){
	new google.visualization.Table(document.getElementById('preview')).draw(data, null);
	}

	function scatterchart(data){
	new google.visualization.ScatterChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
	}

	function linechart(data){
	new google.visualization.LineChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
	}

	function piechart(data){
	new google.visualization.PieChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
	}

	function barchart(data){
	new google.visualization.BarChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
	}

	function columnchart(data){
	new google.visualization.ColumnChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
	}

	</script>

	</head>
	<body>
	<h1>Guardian Datastore Explorer - UK Gov Spending Data, Released November 2010</h1>
	<p> Explore the details of the UK Government Spending Data by using the departmental spreadsheets as individual databases (<a href="http://www.guardian.co.uk/news/datablog/2010/nov/19/government-spending-data?CMP=ouseful_gsdde">Guardian Data Blog: data details</a>). For information about the query syntax, see <a href="http://code.google.com/apis/visualization/documentation/querylanguage.html">Google Chart Tools: Query Language Reference (Version 0.7)</a>. For more details about this application, see <a href="http://blog.ouseful.info">OUseful.info</a>. <em>Tony Hirst, Dept of Communication and Systems, The Open University</em></p>
	<form name="config">
	<input type="hidden" size=30 id='gsKey' <?php if($_GET['gsKey']!='') echo "value=\"".$_GET['gsKey']."\"" ?> /> <input type="hidden" size=3 id='gsSheet' <?php if($_GET['gid']!='') echo "value=\"".$_GET['gid']."\"" ?> /> <br/>
	<p>Grab a sheet from the Guardian Data store: <select name="dList" id="datastore"></select><input type="button" value="Preview" onclick="preview3()" />
	<p>If you are prompted to sign in, wait a few seconds and then try again.</p>
	<div id="statrep"></div>
	</form>
	<hr/>
	<div id="headings"></div>

	<br/><br/>
	<p>Using data from spreadsheet:<p><h1 id='deptsrc'><?php if($_GET['dps']!='') echo $_GET['dps'] ?></h1>
	<br/><br/><hr/>
	<div id="preview"></div>
	<div id="linechart"></div>
	<div id="scatterchart"></div>
	<div id="piechart"></div>
	<br/>
	<p>Try out some visualisation queries here...</p>
	<table id='colLookup' border='1'></table>

	<br/>

	<p>Select the columns from the list box (ctrl+click for multiple selections in IE, command+click on a Mac) or type the elements directly into the appropriate text box.</p><br/>
	<form>
	<div><tt>select</tt> <span id='boxes'></span> <input type="text" id="gqc" size=20 <?php if($_GET['gqc']!='') echo "value=\"".$_GET['gqc']."\"" ?> /><br/>
	<br/><tt>where</tt> <span id='boxes2'></span> <input type="text" id="gqw" size=30 <?php if($_GET['gqw']!='') echo "value=\"".stripslashes($_GET['gqw'])."\"" ?> /> <tt>group by</tt> <input type="text" id="gqg" size=30 <?php if($_GET['gqg']!='') echo "value=\"".$_GET['gqg']."\"" ?> />
	<br/><br/><tt>order by</tt> <span id='boxes3'></span> <input type="text" id="gqo" size=30 <?php if($_GET['gqo']!='') echo "value=\"".$_GET['gqo']."\"" ?> /> <select id="gqad" name="gqad"><option value='asc' selected>asc</option><option value='desc'>desc</option>
	</select> <tt>limit</tt> <input type="text" id="gql" size=10 <?php if($_GET['gql']!='') echo "value=\"".$_GET['gql']."\"" ?> /></div>
	<div>Display as:
	\| Table <input type="radio" name="opType" value="table" checked />
	\| Scatter chart <input type="radio" name="opType" value="scatterchart"/>
	\| Line chart <input type="radio" name="opType" value="linechart"/>
	\| Pie chart <input type="radio" name="opType" value="piechart"/>
	\| Bar chart <input type="radio" name="opType" value="barchart"/>
	\| Column chart <input type="radio" name="opType" value="columnchart"/>
	\|
	<input type="button" value='Go Fish' onclick="drawViz()"/>
	</div>
	<div>So you are asking: <em id='gqpreview'></em></div>
	<div>Here is the URL for that query: <span id='htmlout'></span>, <span id='csvout'></span></div>
	<div>here is a link to this page: <span id="bookmark"></span></div>

	</form>


	<h2>Go fish...</h2>
	<form name='qform'>
	<div>Now it's time to write your own query in two parts - the columns you want to select, and the conditions you want each returned result to satisfy. ***FOLLOWING EXAMPLES NEED TWEAKING FOR CURRENT CONTEXT</div>
	<table border=1><tr><th>Department</th><th><tt>select</tt> input</th><th><tt>where</tt> input</th><th><tt>group by</tt> input</th><th><tt>order by</tt> input</th><th><tt>limit</tt> input</th><th>Comments</th></tr>
	<tr><td>All</td><td></td><td> </td><td> </td><td> </td><td>20</td><td>Display everything (dangerous! May take some time and/or kill your browser! Hence the limit term...) [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
	<tr><td>Treasury</td><td>F,G,H</td><td> </td><td>Display columns F,G, H (Expense Type, Expense area and supplier)</td><td> </td><td>20</td><td>Use this approach to get a feel for what a column contains [<a href="govspending.php?run=true&gsKey=tVryVDy3K3O6kfV7vt0SdSg&gqc=F%2CG%2CH&gqw=&gqo=&gql=20&gqg=">try it</a>]</td></tr>
	<tr><td>Home Office</td><td>E,I,K</td><td>K > 20000000</td><td> </td><td> </td><td> </td><td>Display columns E, I and K (Entity, Supplier, Amount) sums over 20 million [<a href="govspending.php?run=true&gsKey=tnBJa5GzHGs6BdHL2K-5N9w&gqc=E%2CI%2CK&gqw=K%20%3E20000000&gqo=&gql=&gqg=">try it</a>]</td></tr>
	<tr><td>DFID</td><td>H,I,K</td><td>H contains 'Africa' and K > 10000000</td><td> </td><td> </td><td> </td><td>View suppliers and amounts over a million for expense areas containing 'Africa'; (as well as the free text search 'contains', 'matches' must match exactly) [<a href="govspending.php?run=true&gsKey=0AonYZs4MzlZbdFdjaGVOVFAydm5sUUlTb09JUzFaNXc&gqc=H%2CI%2CK&gqw=H%20contains%20%27Africa%27%20and%20K%20%3E%2010000000&gqo=&gql=&gqg=&dps=International%20Development%20(DFID)">try it</a>]</td></tr>
	<tr><td>Government Equalities Office</td><td>F,G,H,J</td><td>J > 100000</td><td> </td><td>J <em>desc</em></td><td> </td><td>View suppliers and amounts over a hundred thousand, sorted by amount (note that if you click on column headings in the table, you can sort the table by that column) [<a href="govspending.php?run=true&gsKey=tKUQuJiBekBQxlDcasQFFaQ&gqc=F%2CG%2CH%2CJ&gqw=J%20%3E%20100000&gqo=J&gql=&gqg=&dps=Government%20Equalities%20Office%20(GEO)">try it</a>]</td></tr>

	<tr><td>Health</td><td>H,J</td><td>J < 0</td><td> </td><td>J <em>asc</em></td><td> </td><td>View suppliers and amounts where the amount is negative... [<a href="govspending.php?run=true&gsKey=tPdRIE1Dtovo5adgjt_D5rA&gqc=H%2CJ&gqw=J%20%3C%200&gqo=J&gql=&gqg=&dps=Health%20(DOH)">try it</a>]</td></tr>

	<tr><td>DCMS</td><td>G,H,I,sum(K),count(K)</td><td>K >0 and H contains 'Museums'</td><td>G,H,I</td><td> </td><td> </td><td>Look at expense type, area and supplier where area contains 'Museums', then group by expense type, area and supplier, reporting total amount awarded to each supplier correspondingly, along with a count of how many payments were made[<a href="govspending.php?run=true&gsKey=tKlkQ_ocEHaUa3RDgIee5sQ&gqc=G%2CH%2CI%2Csum(K)%2Ccount(K)&gqw=K%20%3E0%20and%20H%20contains%20%27Museums%27&gqo=&gql=&gqg=G%2CH%2CI&dps=Culture%2C%20Media%20and%20Sport%20(DCMS)">try it</a>]</td></tr>
	</table>
	<script type="text/javascript" src="http://feeds.delicious.com/v2/json/psychemedia/gdatastore_11_2010?callback=deliList"></script>
	</body>
	</html>