/*
  Defines ActiveInterface if it doesn’t already exist, using the boolean OR operator
  to provide a default value if one isn’t found.
  Could also use
    if(!window.ActiveInterface) ActiveInterface = {};
*/
var ActiveInterface = window.ActiveInterface || {};

ActiveInterface.DataExplorer = (function() {

  //
  // Private members
  //
  var selMeasure = "";
  var dataFields = [];
  var rowField = "";
  var columnField = "";
  var rowType = null;
  var columnType = null;
  var isPivot = false;
  var years = [];
  var regions = [];
  var selectQuery = "";

  /* 
   blank placeholder entries since we iterate to build selection list
  */
  var periods = ["",
   "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Annual","","Unusual","","","","","",
   "Q1","Q2","Q3","Q4","","","","","","",
   "Q1 YTD","Q2 YTD","Q3 YTD","Q4 YTD"
  ];


  var currentkey = null;
  var metakey = null;
  var baseurl = "http://spreadsheets.google.com/pub?key=";

  // object literal with metadata to drive the interface settings
  // only fields listed here will be exposed in the query tool
  var fieldInfo = 
{
  "region" : {
    "type" : "string",
    "filter" : true,
    "multi" : true,
    "onRows" : true,
    "onCols" : true
  },
  "measure" : {
    "type" : "int",
    "filter" : true,
    "multi" : false,
    "onRows" : false,
    "onCols" : true
  },
  "year" : {
    "type" : "year",
    "filter" : true,
    "multi" : true,
    "onRows" : true,
    "onCols" : true
  },
  "period" : {
    "type" : "period",
    "filter" : true,
    "multi" : true,
    "onRows" : true,
    "onCols" : true
  },
  "value" : {
    "type" : "number",
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  },
  "geography" : {
    "type" : "int",
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  },
/*
  "regiontype" : {
    "type": "lookup",
    "filter" : true,
    "multi" : true,
    "table" : ["County", "Region", "Unique"],
    "onRows" : false,
    "onCols" : false
  },
*/
  "regioncode" : {
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  },
  "fips" : {
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  },
  "lat" : {
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  },
  "lon" : {
    "filter" : false,
    "onRows" : false,
    "onCols" : false
  }
};


  // 
  // Perhaps temp solution for "unusual" (15) period
  var filesWithUnusual = 
{
  "LEED Certified Buildings" : {}
}


  // --------------------------------------------------------
  function log(func, msg) {
    try {
      ; //console.info('ActiveInterface.DataExplorer::' + func + '(): ' + msg);
    } catch(e) {}
  }


  // -------------------------------------------------------------------------
  // Everything returned in the object literal is public, but can access the
  // members in the closure created above.
  return {
    
    // Public members
    dataDocs : null,
    // Anything which needs to be passed in from ThinCMS comes in through this object. 
    // See the line with "ActiveInterface.DataExplorer.init"  in data_explorer.xsl
    viewSettings : null, 
    
    // Methods
    
    init : function(viewsettings, datadocs) {
      log('init', 'Begin initiation');

      this.viewSettings = viewsettings;
      this.dataDocs = datadocs;
      var parentthis = this;

      // fix IE checkbox and radio events
      if ($.browser.msie) {
        $('input:radio').click(function () {
            this.blur();
            this.focus();
        });
      }

      //$("#table_div").empty();

      // handle viewsetting for shape selection
      if (this.viewSettings.shape == "flat") {
        $("#shaperadio_flat").attr("checked", "checked");
        parentthis.togglePivot("flat");
      } else if (this.viewSettings.shape == "pivot") {
        $("#shaperadio_pivot").attr("checked", "checked");
        parentthis.togglePivot("pivot");
      }

      // check for key in querystring (will be present if page invoked from a view)
      var qsparams = $.deparam.querystring();
      if (qsparams["m"]) {
        selMeasure = qsparams["m"];
      }
      if (qsparams["k"]) {
        parentthis.setKey(qsparams["k"]);
        parentthis.getFields();
      } else {
        // handle key which may have been passed into init due to Default_key having
        // a value in page xml
        var defaultkey = this.viewSettings.key;
        if (defaultkey && defaultkey.length > 0) {
          parentthis.setKey(defaultkey);
          parentthis.getFields();
        }
      }

      $('#datafile').change(function(){
        parentthis.setKey($(this).val());
      });
      $("#step1Btn").click(function() {
        parentthis.getFields();
      });
      $("#step2Btn").click(function() {
        // button labeled "Reset"
        parentthis.updateFilters();
      });
      $("#step3Btn").click(function() {
        parentthis.applyFilters();
        if (parentthis.viewSettings.mode == "export") 
          parentthis.preview();
      });
      $("#previewBtn").click(function() {
        parentthis.preview();
      });
      $("#exportBtn").click(function() {
        parentthis.download();
      });
      $("input[name='shaperadio']").change(function(){
        parentthis.togglePivot($(this).val());
        parentthis.updateFilters();
      });
      $("#rowSel").change(function(){
        parentthis.selectRow($(this).val());
        parentthis.updateFilters();
        //parentthis.updateFiltersMultisel();
      });
      $("#columnSel").change(function(){
        parentthis.selectColumn($(this).val());
        parentthis.updateFilters();
        //parentthis.updateFiltersMultisel();
      });


    },

    showInfo : function(msg) {
      $("#info_msg").text(msg);
    },

    appendError : function(msg) {
       $("#errors_div").show().append($("<p>").text(msg));
    },

    clearError : function() {
       $("#errors_div").empty();
    },

    // ---------------------------------------------------------------
    setDataSourceInfo: function( csettings, mid)
    {
       $("#subbody .datasourceinfo").html("<a href='" +mover[4]+ " '>" +mover[3]+ "</a>");
    },

    // ---------------------------------------------------------------
    setKey: function(key)
    {

      // save selected key
      if (key.length > 0) {
        currentkey = key;
        $("#datafile").val(key);
        // also update google doc link

        var l = $("#gdlink").attr("href");
        $("#gdlink").attr("href",l+key);

        // if is "Full Job Growth", get meta key as "Annual Job Growth"
        if (this.dataDocs[currentkey].name == "Full Job Growth") {
          for (var k in this.dataDocs) {
            if (this.dataDocs[k].name == "Annual Job Growth") {
               metakey = k;
               break;
            }
          }
        } else {
          metakey = currentkey;
        }

      }

    },

    // ---------------------------------------------------------------
    getFields: function()
    {
      // run query to get headings
      $("#readyNotice").hide();
      $("#runningNotice").show();

      $("#step1Btn").val("Running").attr("disabled","disabled");
      $("#step2Btn").attr("disabled","disabled");
      $("#step3Btn").attr("disabled","disabled");
      $("#fieldsSel").attr("disabled","disabled");
      $("#rowSel").attr("disabled","disabled");
      $("#columnSel").attr("disabled","disabled");

      $("#table_div").empty();

      var parentthis = this;

      dataFields = [];
      years = [];
      regions = [];

      // clear filters and query
      $("#filtersdiv").empty();
      $("#tablequery").val("");

      // disable step 4
      $("#previewBtn").attr("disabled","disabled");
      $("#exportBtn").attr("disabled","disabled");

      // clear old step 3 selections

      var fieldssel = $("#fieldsSel");
      fieldssel.empty();
      fieldssel.append("<option value=''>select fields</option>");

      var colsel = $("#columnSel");
      colsel.empty();
      colsel.append("<option value=''>select field</option>");

      var rowsel = $("#rowSel");
      rowsel.empty();
      rowsel.append("<option value=''>select field</option>");

      var query = new google.visualization.Query(baseurl + currentkey);
      query.setQuery("select * where A = false");
      query.setTimeout(60);

      query.send(function(response) {
        if (!response.isError()) {
          var datatable = response.getDataTable();
          var id, label, fi, llc, sel, idx;
          if (parentthis.viewSettings.debug) {
            // to debug the metadata query
            var t1 = new google.visualization.Table(document.getElementById("debug_table1_div"));
            t1.draw(datatable, {showRowNumber: true});
          }

          for (var i=0;i<datatable.getNumberOfColumns();i++) {
            //id = datatable.getColumnId(i);
            label = datatable.getColumnLabel(i);
            llc = label.toLowerCase();
            sel = "";
            // see if this field should be available on axes
            fi = fieldInfo[llc];

            if (fi) {
              // add to datafields array
              dataFields.push(label);

              // save query letter
              fi.letter = String.fromCharCode(65+i);

              if (fi.onRows) {
                // region is default row selection
                if (llc == "region") {
                  sel = "selected='selected' ";
                  rowField = label;
                }
                rowsel.append("<option " +sel+ "value='" +label+ "'>" +label+ "</option>");
              }
              if (fi.onCols) {
                // year is default column selection
                if (llc == "year") {
                  sel = "selected='selected'";
                  columnField = label;
                }
                colsel.append("<option " +sel+ "value='" +label+ "'>" +label+ "</option>");
              }
/*
              if (llc == "measure" || llc == "value")
                sel = "selected='selected'";
              fieldssel.append("<option " +sel+ "value='" +label+ "'>" +label+ "</option>");
*/
              fieldssel.append("<option selected='selected' value='" +label+ "'>" +label+ "</option>");
            } // if (fi)
          } // for (var i=0;i<datatable.getNumberOfColumns();i++)

         // now get unique regions and years
         //var qstr = $("#metaquery").val();
         var qstr = parentthis.buildMetaQuery();
         if (parentthis.viewSettings.debug) {
           $("#metaquery").val(qstr);
           $("#currentkey").val(metakey);
         }
         var query2 = new google.visualization.Query(baseurl + metakey);
         query2.setQuery(qstr.trim());
         query2.setTimeout(60);

         query2.send(function(response) {

          if (response.isError()) {
            parentthis.showInfo("metadata unavailable");
            var errmsg = response.getMessage() + " " + response.getDetailedMessage();
            parentthis.appendError(errmsg);
          } else {
            var dt = response.getDataTable();

            // years on columns
            for (var i=1;i<dt.getNumberOfColumns();i++){
              // remove ".0" which query added to year
              years.push(dt.getColumnLabel(i).substring(0,4));
            }
            // regions on rows
            for (var i=0;i<dt.getNumberOfRows();i++){
              regions.push(dt.getValue(i,0));
            }

            if (parentthis.viewSettings.debug) {
              // to debug the metadata query
              var t2 = new google.visualization.Table(document.getElementById("debug_table2_div"));
              t2.draw(dt, {showRowNumber: true});
            }
          }

// todo - something here if above was error?

          // enabled step 1 button
          $("#step1Btn").val("Retrieve data fields").removeAttr("disabled");
          $("#readyNotice").show();
          $("#runningNotice").hide();

          // enabled step 2 selections
          if (isPivot) {
            $("#rowSel").removeAttr("disabled"); 
            $("#columnSel").removeAttr("disabled");
          } else {
            $("#fieldsSel").removeAttr("disabled");
          }
          $("#step2Btn").removeAttr("disabled");

          // new: always show filters at startup
          parentthis.updateFilters();

         });

        } else {
          parentthis.showInfo("field data unavailable");
          var errmsg = response.getMessage() + " " + response.getDetailedMessage();
          parentthis.appendError(errmsg);
          $("#step1Btn").val("Retrieve data fields").removeAttr("disabled"); 
          $("#readyNotice").show();
          $("#runningNotice").hide();

        }

      });

    },

    // ---------------------------------------------------------------
    // handler for $("input[name='shaperadio']").change()
    // change sensitivity to enable corresponding controls
    togglePivot : function(value) {

      isPivot = (value == "pivot");

      if (isPivot) {
        $("#rowSel").removeAttr("disabled"); 
        $("#columnSel").removeAttr("disabled");
        //$("#fieldsSel").val(null);
        $("#fieldsSel").attr("disabled","disabled");
      } else {
        $("#rowSel").attr("disabled","disabled");
        $("#columnSel").attr("disabled","disabled");
        $("#fieldsSel").removeAttr("disabled");
      }
      // clear query if shape changes
      $("#tablequery").val("");


    },

    /*
    when a row or column selection is made, update the other axis
    to reflect what fields remain available
    */

    // ---------------------------------------------------------------
    selectRow : function(field) {

      // save selection
      rowField = field;
      var label, sel;

      // get current selection
      var colsel = $("#columnSel");
      var currField = colsel.val();
      colsel.empty();
      colsel.append("<option value=''>select fields</option>");
      for (var i=0; i<dataFields.length; i++) {
        label = dataFields[i];
        sel = "";
        if (fieldInfo[label.toLowerCase()].onCols && label != rowField) {
          if (label == currField)
            sel = "selected='selected' ";
          colsel.append("<option " +sel+ " value='" +label+ "'>" +label+ "</option>");
        }
      }
    },

    // ---------------------------------------------------------------
    selectColumn : function(field) {

      // save selection
      columnField = field;
      var label, sel;

      if (isPivot) {
        // get current selection
        var rowsel = $("#rowSel");
        var currField = rowsel.val();
        rowsel.empty();
        rowsel.append("<option value=''>select field</option>");
        for (var i=0; i<dataFields.length; i++) {
          label = dataFields[i];
          sel = "";
          if (fieldInfo[label.toLowerCase()].onRows && label != columnField) {
            if (label == currField)
              sel = "selected='selected' ";
            rowsel.append("<option " +sel+ " value='" +label+ "'>" +label+ "</option>");
          }
        }
      }
    },

    // ---------------------------------------------------------------

/*
What I am not clear on here: does any of this logic depend upon the selections
in the Step 2 panel? 

What input objects?
 - shaperadio
 - fieldsSel
   - getter in buildSelectQuery()
 - columnSel
 - rowSel
   - getters in handler
   - used in buildSelectQuery()


*/
    updateFilters : function() {

      var tmp = [];
      var tx = 0;
      var label, llc, fi, rid;
      var dd = this.dataDocs[metakey];
      var measures = dd.measures;
      var periodtype = dd.periods;
      var filename = dd.name;

      var rf = rowField.toLowerCase();
      var cf = columnField.toLowerCase();

      // clear current filters
      var filtersdiv = $("#filtersdiv");
      filtersdiv.empty();

      // build html with filters

      tmp[tx++] = "<table><tbody><tr>";
      for (var i=0; i<dataFields.length; i++) {
         label = dataFields[i];
         llc = label.toLowerCase();
         fi = fieldInfo[llc];
         if (!fi) continue;

         if (!fi.filter) continue;

         tmp[tx++] = "<td>";

         switch (llc) {

           case "region":
if (!isPivot || rf == llc || cf == llc) {
             fi.multi = true;
             tmp[tx++] = "<p>Selected region(s) <span class='stepnote'> *</span></p>";
             tmp[tx++] = "<select id='"+llc+"Sel' size='10' multiple='multiple'>";
             for (var li=0;li<regions.length;li++) {
               tmp[tx++] = "<option value='" +regions[li]+ "'>" +regions[li]+ "</option>";
             }
             tmp[tx++] = "</select>";
             tmp[tx++] = "<p><input type='checkbox' name='regionAll' id='regionAll' /><span><label for='regionAll'>select all</label></span></p>";
} else {
             fi.multi = false;
             tmp[tx++] = "<p>Selected region</p>";
             for (var li=0;li<regions.length;li++) {
               tmp[tx++] = "<input ";
               if (li==0) tmp[tx++] = "checked='checked' ";
               tmp[tx++] = " type='radio' id='reg"+li+"' name='"+llc+"Sel'  value='" +regions[li]+ "'/>";
               tmp[tx++] = "<span><label for='reg" +li+ "'>" +regions[li]+ "</label></span>";
               tmp[tx++] = "<br/>";
             }
}

             break;

           case "year":
if (!isPivot || rf == llc || cf == llc) {
             fi.multi = true;
             tmp[tx++] = "<p>Selected year(s) <span class='stepnote'> *</span></p>";
             tmp[tx++] = "<select id='"+llc+"Sel' size='10' multiple='multiple' width='100' style='width:100px;'>";

             for (var li=0;li<years.length;li++) {
               tmp[tx++] = "<option value='" +years[li]+ "'>" +years[li]+ "</option>";
             }
             tmp[tx++] = "</select>";
             tmp[tx++] = "<p><input type='checkbox' name='yearAll' id='yearAll' /><span><label for='yearAll'>select all</label></span></p>";
} else {
             fi.multi = false;
             tmp[tx++] = "<p>Selected year</p>";
             for (var li=0;li<years.length;li++) {
               tmp[tx++] = "<input ";
               if (li==0) tmp[tx++] = "checked='checked' ";
               tmp[tx++] = " type='radio' id='yr"+li+"' name='"+llc+"Sel'  value='" +years[li]+ "'/>";
               tmp[tx++] = "<span><label for='yr" +li+ "'>" +years[li]+ "</label></span>";
               tmp[tx++] = "<br/>";
             }
}
             break;

           case "period":

/*
annual|monthly|annual and monthly|quarterly|annual and quarterly|ytd quarterly|ytd monthly
*/
if (!isPivot || rf == llc || cf == llc) {
             fi.multi = true;
             tmp[tx++] = "<p>Selected period *</p>";
             tmp[tx++] = "<select id='"+llc+"Sel' size='10' multiple='multiple' width='100' style='width:100px;'>";
             if (periodtype.substring(0,6) == "annual") {
               // Change annuals normal value to the "unusual" value
               // But note that since a single member will never get put in a query that
               // it probably doesnt matter
               if (filesWithUnusual[filename]) {
                 tmp[tx++] = "<option value='15'>Annual</option>";
               } else {
                 tmp[tx++] = "<option value='13'>Annual</option>";
               }
             }
             if (periodtype == "monthly" || periodtype == "annual and monthly") {
               for (var li=1; li<13; li++) {
                 tmp[tx++] = "<option value='" +li+ "'>" +periods[li]+ "</option>";
               }
             }
             if (periodtype == "quarterly" || periodtype == "annual and quarterly") {
               tmp[tx++] = "<option value='21'>Q1</option>";
               tmp[tx++] = "<option value='22'>Q2</option>";
               tmp[tx++] = "<option value='23'>Q3</option>";
               tmp[tx++] = "<option value='24'>Q4</option>";
             }
             if (periodtype == "ytd quarterly") {
               tmp[tx++] = "<option value='31'>Q1 YTD</option>";
               tmp[tx++] = "<option value='32'>Q2 YTD</option>";
               tmp[tx++] = "<option value='33'>Q3 YTD</option>";
               tmp[tx++] = "<option value='34'>Q4 YTD</option>";
             }
             tmp[tx++] = "</select>";
} else {
             fi.multi = false;
             tmp[tx++] = "<p>Selected period</p>";

             if (periodtype.substring(0,6) == "annual") {
               tmp[tx++] = "<input type='radio' id='pr"+li+"' name='"+llc+"Sel' checked='checked' value='";
               if (filesWithUnusual[filename])
                 tmp[tx++] = "15";
               else
                 tmp[tx++] = "13";
               tmp[tx++] = "' /><label for='pr" +li+ "'>Annual</label><br/>";
             }
             if (periodtype == "monthly" || periodtype == "annual and monthly") {
               for (var li=1; li<13; li++) {
                 tmp[tx++] = "<input type='radio' id='pr"+li+"' name='"+llc+"Sel' value='" +li+ "' />";
                 tmp[tx++] = "<label for='pr" +li+ "'>" +periods[li]+ "</label><br/>";
               }
             }
             if (periodtype == "quarterly" || periodtype == "annual and quarterly") {
               tmp[tx++] = "<input type='radio' id='pr21' name='"+llc+"Sel' value='21' />";
               tmp[tx++] = "<label for='pr21'>Q1</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr22' name='"+llc+"Sel' value='22' />";
               tmp[tx++] = "<label for='pr22'>Q2</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr23' name='"+llc+"Sel' value='23' />";
               tmp[tx++] = "<label for='pr23'>Q3</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr24' name='"+llc+"Sel' value='24' />";
               tmp[tx++] = "<label for='pr24'>Q4</label><br/>";
             }
             if (periodtype == "ytd quarterly") {
               tmp[tx++] = "<input type='radio' id='pr31' name='"+llc+"Sel' value='31' />";
               tmp[tx++] = "<label for='pr31'>Q1 YTD</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr32' name='"+llc+"Sel' value='32' />";
               tmp[tx++] = "<label for='pr32'>Q2 YTD</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr33' name='"+llc+"Sel' value='33' />";
               tmp[tx++] = "<label for='pr33'>Q3 YTD</label><br/>";
               tmp[tx++] = "<input type='radio' id='pr34' name='"+llc+"Sel' value='34' />";
               tmp[tx++] = "<label for='pr34'>Q4 YTD</label><br/>";
             }
}


             break;

           case "measure":
             tmp[tx++] = "<p>Selected measure</p>";
             var mid;
             for (var li=0;li<measures.length;li++) {
               mid = measures[li].id;
               tmp[tx++] = "<input ";
               // select current measure or first
               if (mid == selMeasure || (li==0 && selMeasure.length == 0)) tmp[tx++] = "checked='checked' ";
// CCH: changed value to be array index insead of measure id
               tmp[tx++] = " type='radio' id='mr"+mid+"' name='"+llc+"Sel'  value='" +li+ "'/>";
               tmp[tx++] = "<span><label for='mr" +mid+ "'>" +measures[li].label+ "</label></span>";
               tmp[tx++] = "<br/>";
             }
             break;

           default:
             if (fi.type == "lookup") {
if (isPivot) {
               fi.multi = false;
               var sid = label.replace(" ","");
               tmp[tx++] = "<p>Select "+label+"</p>";
               for (var li=0;li<fi.table.length;li++) {

               tmp[tx++] = "<input ";
               // choose first in list as default
               // other than region type, whose best default is "region", 
               if (llc == "regiontype" && li==1)
                  tmp[tx++] = "checked='checked' ";
               else if (llc != "regiontype" && li==0)
                  tmp[tx++] = "checked='checked' ";
               tmp[tx++] = " type='radio' id='"+sid+li+"' name='"+llc+"Sel'  value='" +(li+1)+ "'/>";
               tmp[tx++] = "<span><label for='" +sid+li+ "'>" +fi.table[li]+ "</label></span>";
               tmp[tx++] = "<br/>";
               }
} else {
             fi.multi = true;
             tmp[tx++] = "<p>Select "+label+" *</p>";
             tmp[tx++] = "<select multiple='multiple' id='"+llc+"Sel' size='10'>";
               for (var li=0;li<fi.table.length;li++) {
                 tmp[tx++] = "<option value='" +(li+1)+ "'>" +fi.table[li]+ "</option>";
               }
             tmp[tx++] = "</select>";
}
             }
             break;

         }

         tmp[tx++] = "</td>";
      } // for dataFields

      tmp[tx++] = "</tr></tbody></table>";

      filtersdiv.append(tmp.join(""));


      $("input[name='regionAll']").click(function(){
        if ($(this).is(':checked'))
          $("#regionSel").each(function(){
            $("#regionSel option").attr("selected","selected");
          });
        else
          $("#regionSel").each(function(){
            $("#regionSel option").removeAttr("selected");
          });

      });

      $("input[name='yearAll']").click(function(){
        if ($(this).is(':checked'))
          $("#yearSel").each(function(){
            $("#yearSel option").attr("selected","selected");
          });
        else
          $("#yearSel").each(function(){
            $("#yearSel option").removeAttr("selected");
          });

      });

      // disable step 4
      $("#previewBtn").attr("disabled","disabled");
      $("#exportBtn").attr("disabled","disabled");

      // enabled step 3
      $("#step3Btn").removeAttr("disabled"); 

    },

    // ---------------------------------------------------------------
    // adjust 'multiple" attribute of select controls to enforce
    // that join cells don't aggregate
    updateFiltersMultisel : function(field) {
      
    },

    // ---------------------------------------------------------------
    buildSelectQuery: function() {

    var tmp = [];
    var foo, fi, llc, mtype, qc, elt, listcnt;
    var tx = 0;
    var retobj = new Object();
    retobj.error = false;

    tmp[tx++] = "select ";

    // build a where clause to use in both pivot and flat queries
    var filter = [];
    var fx = 0;

    filter[fx++] = "where ";
    // iterate datafields, looking up each in fieldInfo
    // and then building include clause from corresponding
    // multiselect list or radiobutton selections
    for (var i=0; i<dataFields.length; i++) {
      llc = dataFields[i].toLowerCase();
      fi = fieldInfo[llc];
      if (!fi.filter || llc=="measure") continue;

      // if is multi select, use list. othewise radiobuttons
      if (fi.multi) {

        foo = [];
        // quote char is ' for string type filters
        qc = fi.type == "string" ? "'" : "";
      
        $("#"+llc+"Sel :selected").each(function(j, selected){
           foo[j] = $(selected).val();
        });
        // only add filter if a subset are selected
        if (foo.length > 0 && foo.length < $("#"+llc+"Sel").children().length) {

          filter[fx++] = "(";
          for (var j=0; j<foo.length-1; j++) {
             filter[fx++] = fi.letter+ "="+qc+foo[j]+qc+" or ";
          }
          filter[fx++] = fi.letter+"="+qc+foo[foo.length-1]+qc+")";
          filter[fx++] = " and ";
        }

      } else {
        var rbsel = $("input[name=" +llc+ "Sel]:checked").val();
        filter[fx++] = fi.letter+ "="+qc+rbsel+qc+" and ";
      }

    }

    // add measure filter
    fi = fieldInfo["measure"];

    /*
       radio buttons has array index as value
       this allows the selected value to be used as the index
       into the measures array, where the measure id and type
       are obtained for use in the query
    */
    var midx = $("input[name=measureSel]:checked").val();
    var mobj = this.dataDocs[metakey].measures[midx];
    selMeasure = mobj.id;
    var mformat = mobj.format;

    filter[fx++] = "(" + fi.letter + "=" + selMeasure + ")";

    //if (filter[fx-1] == " and ") filter[fx-1] = "";

    if (isPivot) {
      if (rowField.length == 0 || columnField.length == 0) {
        alert("In Step 2 a Column and a Row must be selected");
        return;
      }

      // generate pivot: select <row>, oper(<value>) where ... group by <row> pivot <column>

      var valLetter = fieldInfo["value"].letter;
      var rfi = fieldInfo[rowField.toLowerCase()];
      var rowLetter = rfi.letter;
      var cfi = fieldInfo[columnField.toLowerCase()];
      var colLetter = cfi.letter;

      // save these for use in post processing table
      rowType = rfi.type;
      columnType = cfi.type;

      tmp[tx++] = rowLetter;
      tmp[tx++] = ", ";

      //var oper = $("#operSel").val();
      //var operfld = oper + "(" + valLetter + ") ";
      var operfld = "avg(" + valLetter + ") ";
      tmp[tx++] = operfld;
      tmp[tx++] = filter.join("");
      tmp[tx++] = " group by ";
      tmp[tx++] = rowLetter;
      tmp[tx++] = " pivot ";
      tmp[tx++] = colLetter;

      if (mformat) {
      tmp[tx++] = " format ";
      tmp[tx++] = operfld;
      tmp[tx++] = " '";
      tmp[tx++] = mformat;
      tmp[tx++] = "' ";
      }

    } else {

      // get list of fields selected

      // list selections
      var fieldsSel = $("#fieldsSel")[0];
      var sel;
      var nonSel = true;
      for (j=0;j<fieldsSel.options.length;j++) {
        sel = fieldsSel.options[j];
        if (sel.selected && sel.value.length > 0) {
          nonSel = false;
          tmp[tx++] = fieldInfo[sel.value.toLowerCase()].letter;
          tmp[tx++] = ", ";
        }
      }
      if (nonSel) {
        retobj.error = true;
        retobj.msg = "No fields selected";
      }

      if (tmp[tx-1] == ", ") tmp[tx-1] = " ";

      // add where clause built earlier
      tmp[tx++] = filter.join("");

    } // if (isPivot)

    retobj.query = tmp.join("");

    return retobj;


    },

    // ---------------------------------------------------------------
    buildMetaQuery : function() {

    var regionLetter;
    var yearLetter;
    var periodLetter; 
    var valueLetter; 
    var measureLetter; 
    var sWhere = " ";
    var periodWhere = null;
    var measureWhere = null;

    var fi, cc, llc;

    // find column letters
    for (var i=0;i<dataFields.length;i++) {
      llc = dataFields[i].toLowerCase();
      fi = fieldInfo[llc];
      cc = fi.letter; 
      switch(llc) {
        case "region":
          regionLetter = cc;
          break;
        case "year":
          yearLetter = cc;
          break;
        case "value":
          valueLetter = cc;
          break;
        case "period":
          /*
             hack: 
             I used "Full Job Growth" key for columns but "Annual Job Growth" for
             pivot. But "Annual Job Growth" doesn't have period columns, so I 
             need to avoid adding it here
          */
          if (metakey == currentkey)
            periodWhere = " ("+cc+" = 1 or "+cc+" = 13 or "+cc+" = 15) ";
          break;
        case "measure":
          measureWhere = " " + cc + " = " + this.dataDocs[metakey].measures[0].id + " ";
          break;

      }
      
    }

    // build a where clause
    // the purpose of the where clause is to make this metadata query run
    // as fast as possible by involving the least data possible.
    if (periodWhere || measureWhere) {
     sWhere = " where ";
     if (periodWhere) {
       sWhere += periodWhere;
     }
     if (measureWhere) {
      if (sWhere != " where ")
        sWhere += " and " + measureWhere;
      else
        sWhere += measureWhere;
     }
    }

    //return "select " +regionLetter+ ", sum(" +valueLetter+ ")" +sWhere+ "group by " +regionLetter+ " pivot " +yearLetter+ " format sum(" +valueLetter+ ") '#.0'";
    return "select " +regionLetter+ ", sum(" +valueLetter+ ")" +sWhere+ "group by " +regionLetter+ " pivot " +yearLetter;

    },

    // ---------------------------------------------------------------
    applyFilters : function() {

      this.clearError();

      // for each field which is a filter
      // get selected values and save to dataFields
      for (var i=0; i<dataFields.length; i++) {
        llc = dataFields[i].toLowerCase();
        fi = fieldInfo[llc];
        if (!(fi && fi.filter)) continue;
        fi.selections = [];
        $("#"+llc+"Sel :selected").each(function(i, selected){
          fi.selections[i] = $(selected).val();
        });
      }

      // build select query
      var sq = this.buildSelectQuery();

      if (sq.error) {

        this.appendError(sq.msg);
        alert(sq.msg);
        $("#previewBtn").attr("disabled","disabled");
        $("#exportBtn").attr("disabled","disabled");

      } else {

        // enable buttons
        selectQuery = sq.query;
        // put into input area
        $("#tablequery").val(selectQuery);
        $("#debugquery").val(selectQuery);
        $("#previewBtn").removeAttr("disabled");
        $("#exportBtn").removeAttr("disabled");
      }

    },

    // ---------------------------------------------------------------
    preview : function() {
      var label, period;
      this.clearError();

      if (this.viewSettings.mode == "explore")
	       selectQuery = $("#tablequery").val();

      if (currentkey == null) {
        this.appendError("no datasource selected");
        return;
      }

      if (selectQuery.trim().length == 0) {
        this.appendError("no query specified");
        return;
      }

      $("#previewBtn").val("Running"); 
      $("#previewBtn").attr("disabled","disabled");

      this.showInfo("running...");

      $("#readyNotice").hide();
      $("#runningNotice").show();

      var parentthis = this;

      var query = new google.visualization.Query(baseurl + currentkey);
      query.setQuery(selectQuery);
      query.setTimeout(60);

      query.send(function(response) {
        if (!response.isError()) {
          var datatable = response.getDataTable();
          if (columnType == "year") {
            for (var i=1;i<datatable.getNumberOfColumns();i++) {
              label = datatable.getColumnLabel(i);
              datatable.setColumnLabel(i,label.substring(0,4));
            }
          } else if (columnType == "period") {
            for (var i=1;i<datatable.getNumberOfColumns();i++) {
              period = parseInt(datatable.getColumnLabel(i));
              datatable.setColumnLabel(i,periods[period]);
            }
          }

          var t = new google.visualization.Table(document.getElementById("table_div"));
          t.draw(datatable, {showRowNumber: true});

          // Google add width style in FF - remove it
          $(".google-visualization-table-table").removeAttr('style');

        } else {
          parentthis.showInfo("data unavailable");
          var errmsg = response.getMessage() + " " + response.getDetailedMessage();
          parentthis.appendError(errmsg);
        }
        $("#previewBtn").val("Preview");
        $("#previewBtn").removeAttr("disabled"); 
        $("#readyNotice").show();
        $("#runningNotice").hide();
      });


    },


    // ---------------------------------------------------------------
    download : function() {

      if (this.viewSettings.mode == "explore")
	       selectQuery = $("#tablequery").val();

      if (currentkey == null) {
        this.appendError("no datasource selected");
        return;
      }
      if (selectQuery.trim().length == 0) {
        this.appendError("no query specified");
        return;
      }

      // encode query for url 
      var querystr = encodeURIComponent(selectQuery);
      window.location = "http://spreadsheets.google.com/tq?tqx=out:csv&tq="+querystr+"&key="+currentkey;

    }

  };
})();