Difference between revisions of "Team:William and Mary/outreach db js"

Revision as of 14:39, 25 October 2017

/* This file contains functions for initializing and accessing the database; It assumes that alasql has been included in whichever page calling it;

/

var OUTREACH_URL = 'https://2017.igem.org/Team:William_and_Mary/Outreach_DB/database_csv?action=raw&ctype=text/csv' var outreach_db ={}; console.log('init_outreach.js'); (function(outreach_db){

 var initialized = false;
 outreach_db.init = function (clear_local=false){
   return alasql.promise('CREATE TABLE IF NOT EXISTS outreach').then(
     function(){
       return alasql.promise('SELECT * INTO outreach FROM CSV(?)', [OUTREACH_URL])
     }
   )
 }

 outreach_db.list_tags = function(){
   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);
 }

 outreach_db.compile_sql = function(selected_tags){
   // use existing template, or compile one in case there's none
   outreach_db.compile_sql.sql_template = outreach_db.compile_sql.sql_template||Handlebars.compile($('#querystring-template').html())
   // none selected = don't filter on this tag type
   for (tag_type in selected_tags){
     if (selected_tags[tag_type].length == 0){
       delete selected_tags[tag_type]
     }
   }
   return outreach_db.compile_sql.sql_template(selected_tags)
 }

 outreach_db.get_by_id = function(id){
   data = alasql('SELECT * FROM outreach WHERE CAST([id] as STRING)=?', [id]);// need to cast id as string or numeric ids won't work
   return data[0];
 }

})(outreach_db) console.log('done outreach.js')

@@ Line 3: / Line 3: @@
 It assumes that alasql has been included in whichever page calling it;
 */
-var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true'
+var OUTREACH_URL = 'https://2017.igem.org/Team:William_and_Mary/Outreach_DB/database_csv?action=raw&ctype=text/csv'
 var outreach_db ={};
 console.log('init_outreach.js');
@@ Line 11: / Line 11: @@
      return alasql.promise('CREATE TABLE IF NOT EXISTS outreach').then(
        function(){
-        everything = "CAST([id] AS STRING), CAST([School] AS STRING),  CAST([Section] AS STRING), CAST([Year] AS STRING), CAST([Wiki hyperlink] AS STRING), CAST([Award] AS STRING),   CAST([Outreach Category] AS  STRING),   CAST([Outreach Project] AS STRING),    CAST([Project Tag] AS STRING), CAST([Product] AS STRING), CAST([Product Description] AS STRING), CAST([Product Tag] AS STRING), CAST([Audience] AS STRING), CAST([Goal] AS STRING), CAST([Objectives] AS STRING), CAST([Data Type] AS STRING),CAST([Data] AS STRING), CAST([Reach] AS STRING), CAST([Analysis] AS STRING), CAST([References] AS STRING), CAST([Description] AS STRING)"
+         return alasql.promise('SELECT * INTO outreach FROM CSV(?)', [OUTREACH_URL])
-         return alasql.promise('SELECT * INTO outreach FROM TABLETOP(?, ?)', [OUTREACH_URL, {parseNumbers:false}])
        }
      )