Difference between revisions of "Team:William and Mary/Outreach DB/Tag Search"

Line 7: Line 7:
 
<style>
 
<style>
 
ul.tag-type{
 
ul.tag-type{
−
column-width: 500px;
+
column-width: 300px;
 
list-style: none;
 
list-style: none;
 
}
 
}
  
 
</style>
 
</style>
−
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/4.0.10/handlebars.min.js"></script>
+
<script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/4.0.10/handlebars.min.js"></script>
 
<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.2.min.js"></script>
 
<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.2.min.js"></script>
 
<script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script>
 
<script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script>
 
<script src="http://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
 
<script src="http://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
 +
<script src='outreach_db.js'></script>
 
<script id="checkbox-area-template" type="text/x-handlebars-template">
 
<script id="checkbox-area-template" type="text/x-handlebars-template">
 
{{#each this}}
 
{{#each this}}
−
<fieldset id='{{@key}}' value='{{@key}}'>
+
<fieldset id='{{@key}}' value='{{@key}}'>
−
<legend>{{@key}}</legend>
+
<legend>{{@key}}</legend>
−
<ul class='tag-type'>
+
<ul class='tag-type'>
 
{{#each this}}
 
{{#each this}}
 
<li class='tag'>
 
<li class='tag'>
−
<input type='checkbox' name='{{@../key}}' value='{{this}}' class='tag-checkbox' style='float:left'/><label class='tag-label'> {{this}}</label>
+
<input type='checkbox' name='{{@../key}}' value='{{this}}' class='tag-checkbox' style='float:left'/><label class='tag-label'>{{this}}</label>
 
</li>
 
</li>
 +
<br>
 
{{/each}}
 
{{/each}}
−
<ul>
+
</ul>
−
</fieldset>
+
</fieldset>
 
{{/each}}
 
{{/each}}
 
</script>
 
</script>
Line 48: Line 50:
  
 
<script id="search-results-template" type="text/x-handlebars-template">
 
<script id="search-results-template" type="text/x-handlebars-template">
−
<table>
+
<div class='search-result'>
−
{{#each this}}
+
{{#each this}}
−
<tr><td>{{[Year]}}</td><td>{{[School Level]}}<td></tr>
+
<h3>{{[Outreach Project]}}</h3>
−
{{/each}}
+
<span class='school-info'><a href='{{[Wiki hyperlink]}}'>{{[School]}}-{{[Year]}}-{{[School Level]}}</a></span>
−
</table>
+
<br />
 +
<span class='outreach-category'>{{[Outreach Category]}}</span>
 +
<p>
 +
{{[Product Description]}}
 +
</p>
 +
<dl>
 +
<dt>
 +
Award
 +
</dt>
 +
<dd>
 +
{{[Award]}}
 +
</dd>
 +
<dt>
 +
Project Tag
 +
</dt>
 +
<dd>
 +
{{[Project Tag]}}
 +
</dd>
 +
</dl>
 +
{{/each}}
 +
</div>
 +
 
 
</script>
 
</script>
 
<script type='text/javascript' >
 
<script type='text/javascript' >
 
$(document).ready(function(){
 
$(document).ready(function(){
 
var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true'
 
var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true'
−
alasql.promise(
+
outreach_db.init(
−
'CREATE TABLE outreach'
+
−
).then(  // read in data
+
−
function(){
+
−
return alasql.promise('SELECT * INTO outreach FROM TABLETOP(?)', OUTREACH_URL)
+
−
}
+
 
).then(  // scrape all tags from data
 
).then(  // scrape all tags from data
−
function(){
+
outreach_db.list_tags
−
tag_data = {}
+
−
tag_types = ['School Level', 'Year', 'Outreach Category',
+
−
'Award', 'Project Tag', 'Product Tag',
+
−
'Audience', 'Goal', 'Data Type']
+
−
for (tag_type of tag_types){
+
−
tags = alasql('COLUMN OF SELECT DISTINCT ['+tag_type+'] FROM outreach')
+
−
tags = tags.filter(  // to remove 0 for null rows in column
+
−
obj=> obj
+
−
).map(    // to split multitags
+
−
s=>String(s).split(',')
+
−
.map(jQuery.trim)
+
−
).reduce(  // to flatten array of form [[a, b], [c, d]]
+
−
(a, b)=>a.concat(b),
+
−
[]
+
−
)
+
−
tag_data[tag_type]=Array.from(new Set(tags))
+
−
}
+
−
return(tag_data);
+
−
}
+
 
).then(  // initialize checkbox area using tag data
 
).then(  // initialize checkbox area using tag data
 
init_checkbox_area
 
init_checkbox_area
Line 106: Line 105:
 
}
 
}
  
−
function generateTable(data) {
+
function generate_table(data) {
  
 
var html = '<table>';
 
var html = '<table>';
Line 131: Line 130:
 
}
 
}
 
}
 
}
−
 
+
1
 
if(data[0].constructor === Object) {
 
if(data[0].constructor === Object) {
 
for(var row in data) {
 
for(var row in data) {
Line 144: Line 143:
 
return html;
 
return html;
 
}
 
}
 +
 +
  
 
function search(){
 
function search(){
−
// use existing template, or compile one in case there's none
+
 
−
search.querystring_template = search.querystring_template || Handlebars.compile($('#querystring-template').html())
+
 
// From light comes darkness, and from darkness, light.
 
// From light comes darkness, and from darkness, light.
 
// i.e. Don't filter types of which no tags has been selected
 
// i.e. Don't filter types of which no tags has been selected
−
selected_tags = read_checkbox_area()
+
 
−
for (tag_type in selected_tags){
+
−
if (selected_tags[tag_type].length == 0){
+
−
delete selected_tags[tag_type]
+
−
}
+
−
}
+
 
var ordburl = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true';
 
var ordburl = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true';
−
// qString = 'SELECT * INTO HTML("#search-results") FROM outreach  ' + search.querystring_template(selected_tags)
+
selected_tags = read_checkbox_area()
−
qString = 'SELECT * FROM outreach  ' + search.querystring_template(selected_tags)
+
 
 +
qString = 'SELECT * FROM outreach  ' + outreach_db.compile_sql(selected_tags)
 
console.time(qString);
 
console.time(qString);
−
alasql(qString, [ordburl], callback= function(data){
+
alasql(qString, [], callback= function(data){
 
show_results(search_result=data)
 
show_results(search_result=data)
 
console.log(data);
 
console.log(data);
Line 170: Line 166:
 
show_results.template = show_results.template || Handlebars.compile($('#search-results-template').html())
 
show_results.template = show_results.template || Handlebars.compile($('#search-results-template').html())
 
//$('#search-results').html(show_results.template(search_result));
 
//$('#search-results').html(show_results.template(search_result));
−
$('#search-results').html(generateTable(search_result));
+
$('#search-results').html(show_results.template(search_result));
 
}
 
}
  
Line 178: Line 174:
 
$('#checkbox-area').html(checkbox_area_html)
 
$('#checkbox-area').html(checkbox_area_html)
 
}
 
}
−
 
−
 
−
 
−
 
 
</script>
 
</script>
 
</head>
 
</head>

Revision as of 15:28, 16 August 2017

Outreach Database: select tags for search