/**
 * Name: Xml_SqlSelect.js
 * Type: class module
 * Created By: Robert Satterwhite
 * Edited Date: 2009-10-28
 *
 * (THIS WORKS IN [msie,netscape,firefox,safari,opera]), from versions 6+ to current
 *
 * * Xml_SqlSelect is a class-object designed to open database-configured XML files
 *   and extract data by using the most basic SQL-select queries.
 * * Database-configured XML files are standard formats that Relational Database
 *   applications can import/export.
 *   The Tag-Names within the (record) represent the table fields to be queried.
 *   The <dataroot> && <record> Tag-Names, as exampled below, are arbitrary.
 * * Xml_SqlSelect knows the difference between numbers and strings.
 * * Results values are not sorted.
 * * Multiple queries may be made on the same class object created.
 *
 * * Data-load Types: file, xml-string, xml-object
 *   // Object extracted by HTTP request.responseXML.
 *   var Sql = new Xml_SqlSelect.open('personnel.xml', callback); // callback optional
 *   // String such as HTTP request.responseText, or dynamically created.
 *   var Sql = new Xml_SqlSelect.open(forms[0].personnel.value); // no callback!
 *   // Object such as HTTP request.responseXML, or dynamically created.
 *   var Sql = new Xml_SqlSelect.open([xml-object]); // no callback!
 *
 * SELECT SAMPLES
 * Sql.query("SELECT email FROM personnel);
 * Sql.query("SELECT * FROM personnel); // all fields
 * Sql.query("SELECT email FROM personnel WHERE name='John Smith' ");
 * Sql.query("SELECT email FROM personnel WHERE name=~'Smith' "); // Perl Match
 * Sql.query("SELECT orderID FROM orders WHERE total > 1200.00");
 * ---- TEST CONFIGURATIONS ----
 * MATCH:       (=~ | like)
 * MATCH_NOT:   (!~ | not like)
 * GREATEREQUAL:(>= | !<)
 * LESSEQUAL:   (<= | !>)
 * GREATER:     (>)
 * LESS:        (<)
 * EQUAL_NOT:   (!= | ^= | <>)
 * EQUAL:       (=)
 *
 *
 * DATABASE-CONFIGURED XML EXAMPLE: (personnel.xml)
 * <?xml version="1.0" encoding="UTF-8"?>
 * <dataroot>
 *   <record>
 *     <name>John Smith</name>
 *     <phone>555-555-5555</phone>
 *     <email><![CDATA[jsmith@abc.com]]></email>
 *   </record>
 *   <record>
 *     <name>Jane Doe</name>
 *     <phone>666-666-6666</phone>
 *     <email><![CDATA[jdoe@abc.com]]></email>
 *   </record>
 * </dataroot>
 *
 * EXAMPLE:
 * -------------------------------------------------
 * var Sql;
 * var callback = function() {
 *     // Query for a single [String] result.
 *     // Even if there are multiple answers, the result is taken from the top of the list.
 *     var result = Sql.query_singlecell("SELECT email FROM personnel WHERE name=~'Smith' ");
 *     //RESULT
 *     //jsmith@abc.com
 *
 *     // Retrieve a [Numerical Array] of [Numerical Array]s.
 *     var N_result = Sql.query("SELECT * FROM personnel");
 *     var N_result = Sql.query("SELECT * FROM personnel", Xql_NUM);
 *     //RESULT
 *     //[
 *     //    [
 *     //        "John Smith",
 *     //        "555-555-5555",
 *     //        "jsmith@abc.com"
 *     //    ],
 *     //    [
 *     //        "Jane Doe",
 *     //        "666-666-6666",
 *     //        "jdoe@abc.com"
 *     //    ]
 *     //]
 *     alert(N_result[0][2]) // (jsmith@abc.com)
 *     while(N_ = N_result.shift()) {
 *       alert(N_[2]);
 *     }
 *
 *     // Retrieve a [Numerical Array] of [Associative Array]s.
 *     var N_result = Sql.query("SELECT * FROM personnel", Xql_ASSOC);
 *     //RESULT
 *     //[
 *     //    {
 *     //        name: "John Smith",
 *     //        phone: "555-555-5555",
 *     //        email: "jsmith@abc.com"
 *     //    },
 *     //    {
 *     //        name: "Jane Doe",
 *     //        phone: "666-666-6666",
 *     //        email: "jdoe@abc.com"
 *     //    }
 *     //]
 *     alert(N_result[0].email) // (jsmith@abc.com)
 *     while(A_ = N_result.shift()) {
 *       alert(A_.email);
 *     }
 * };
 * Sql = new Xml_SqlSelect.open('personnel.xml', callback);
 * -------------------------------------------------
 */

var Xml_SqlSelect;
if (!Xml_SqlSelect) Xml_SqlSelect = {};

/**
 * Result arrays-types may be selected for query.
 */
var Xql_NUM = 'Xql_NUM';
/**
 * Result arrays-types may be selected for query.
 */
var Xql_ASSOC = 'Xql_ASSOC';

/**
 * Create Class object.
 */
Xml_SqlSelect.open = function(file, callback) {
  this.callback = callback;

  /**
   * Load as XML object....
   */
  if (typeof file == 'object') {
    // presumed documentElement
    this.XML = file;

    // else make file quality and then get DocumentElement
    if (Xml_SqlSelect.serialize(file).match(/\?xml/i)) {
      file = Xml_SqlSelect.serialize(file);
      file = Xml_SqlSelect.parse(file);
      this.XML = file.documentElement;
    }
  }

  /**
   * incomming string...
   */
  else if (typeof file == "string") {
    // Load XML from file.
    if (file.match(/\.xml/i)) {
      file = file.replace(/\s*(\w+)\s*/, "$1");
      // Get XML file content.
      var request = null;
      try { request = new XMLHttpRequest(); } catch(e) {}
      try { request = new ActiveXObject('Msxml2.XMLHTTP'); } catch(e) {}
      try { request = new ActiveXObject('Microsoft.XMLHTTP'); } catch(e) {}

      if (request) {
        var self = this;
        request.onreadystatechange = function() {
          if (request.readyState == 4 && request.status == 200) {
            // Store XML content and trigger callback if provided.
            // In the end, it is simply better to land as TEXT and then parse.
            self.XML = Xml_SqlSelect.parse(request.responseText);
            self.XML = self.XML.documentElement;
            //self.XML = request.responseXML.documentElement;
            if (self.callback) self.callback();
          }
        };
        request.open('GET', file);
        request.send(null);
      }
    }

    // Create XML from string.
    else if (file.match(/\<\w/i)) {
      this.XML = Xml_SqlSelect.parse(file).documentElement;
    }
  }
};

/**
 * Query for a single [string] result.
 * Even if there are multiple answers, the result is taken from the top of the list.
 */
Xml_SqlSelect.open.prototype.query_singlecell = function(query_) {
  // Send standard query.
  var N_result = this.query(query_);

  // If there is result content extract and return it.
  if (N_result.length && N_result[0].length) {
    var result = N_result.shift().shift();

    // See if the result value is a [number] or a [string].
    // Return it in the object-type it was meant to be.
    result = result.toString();
    var v1_2 = parseFloat(result.replace(/[$,]/, ''));
    if (v1_2.toString().length == result.length && typeof v1_2 == 'number') result = v1_2;
    return result;
  }
  // Else...
  else {
    return null;
  }
};

/**
 * Query for a possible [array] result.
 */
Xml_SqlSelect.open.prototype.query = function(query_, arrayType) {
  this.A_fields = {};
  this.arrayType = arrayType;
  this.N_AND = [];
  this.N_finishedTable = [];
  this.query_ = query_;

  COMMAND: {
    // Remove detritus from around query string.
    this.query_ = this.query_.replace(/\s*;\s*$/i, "");
    this.query_ = this.query_.replace(/^\s*\w*\s+/i, '');

    // If there are any condition statements...
    WHERE: {
      if (this.query_.match(/\s+where\s+/i)) {
        var N_s = this.query_.split(/\s+where\s+/i);
        var ands = N_s.pop();

        // Possible conditions allowed.
        var A_E_ = {
          E_MATCH: /\s*(=~|like)\s*/i,
          E_MATCH_NOT: /\s*(!~|not\s+like)\s*/i,
          E_GREATEREQUAL: /\s*(>=|!\<)\s*/,
          E_LESSEQUAL: /\s*(<=|!>)\s*/,
          E_GREATER: /\s*(>)\s*/,
          E_LESS: /\s*(<)\s*/,
          E_EQUAL_NOT: /\s*(!=|\^=|\<>)\s*/,
          E_EQUAL: /\s*(=)\s*/
        };

        // Iterate the conditions list and translate to Javascript format.
        // Store optimized conditions for later use.
        var N_ANDS = ands.split(/\s+and\s+/i);
        var AND;
        var operator;
        while (AND = N_ANDS.shift()) {
          for (var KEY in A_E_) {
            if (operator = AND.match(A_E_[KEY])) {
              // Want [value1,value2,re-exp name,operator].
              operator = operator.pop();
              if (KEY == 'E_MATCH_NOT') operator = '!~';
              if (KEY == 'E_MATCH') operator = '=~';
              if (KEY == 'E_GREATEREQUAL') operator = '>=';
              if (KEY == 'E_LESSEQUAL') operator = '<=';
              if (KEY == 'E_EQUAL_NOT') operator = '!=';
              var N_ = AND.split(A_E_[KEY]);
              if (N_[1] == operator) N_[1] = N_.pop();
              N_[0] = N_[0].replace(/^\s*['"]\s*/g, '');
              N_[0] = N_[0].replace(/\s*['"]\s*$/g, '');
              N_[1] = N_[1].replace(/^\s*['"]\s*/g, '');
              N_[1] = N_[1].replace(/\s*['"]\s*$/g, '');
              N_.push(KEY, operator);
              this.N_AND.push(N_);
              break;
            }
          }
        }
      }
    };

    // Create a list of field names from query or XML file.
    TABLESANDFIELDS: {
      var field;
      var fields = this.query_.split(/\s+from\s+/i)[0];

      // Collect possible field names provided by the query.
      var N_fields = fields.toString().split(/\s*,\s*/g);

      // If fields position contains an asterisk (*),
      // we will need all possible fields the table represents.
      // These field names should be represented in the XML
      // file by tag name.
      if (!!fields.toString().match(/\*/)) {
        var O_field = this.XML.childNodes[0].firstChild;
        if (!O_field) O_field = this.XML.childNodes[1].firstChild;
        N_fields = [];

        while (O_field) {
          if (O_field.nodeType != 1) {
            O_field = O_field.nextSibling;
            continue;
          }
          N_fields.push(O_field.nodeName);
          this.A_fields[field] = field;
          O_field = O_field.nextSibling;
        }
      }

      // Store the field names for later use.
      while (field = N_fields.shift()) {
        this.A_fields[field] = field;
      }
      delete this.A_fields['undefined'];
    };

    // Iterate XML records and fields and qualify values for return.
    SELECT: {
      // The number of conditions to be tested must equal the
      // number of conditions applied against a record.
      var A_validRec = {};

      // At least one conditions is required to run the search.
      // If no conditions are required by user,
      // create a condition that can never be met.
      if (!this.N_AND.length) {
        this.N_AND.push(['X',,'E_EQUAL_NOT','!='])
      }

      for (var n_ = 0; n_ < this.N_AND.length; n_++) {
        var N_and = this.N_AND[n_];
        var field1 = N_and[0];
        var field2 = N_and[1];
        var expr = N_and[3];
        var exprName = N_and[2];
        if (expr == '=') expr = '==';

        var O_record1;
        var O_table1 = this.XML;
        var N_recNodes1 = Xml_SqlSelect.getChildren(O_table1.childNodes);
        while (O_record1 = N_recNodes1.shift()) {
          var O_field1;
          var N_fieldNodes1 = Xml_SqlSelect.getChildren(O_record1.childNodes);
          var N_field1Values = [];
          while (O_field1 = N_fieldNodes1.shift()) {
            var fieldName1 = O_field1.nodeName;
            var fieldValue1 = Xml_SqlSelect.getValue(O_field1);
            if (!this.A_fields[fieldName1]) continue;
            N_field1Values.push(fieldValue1);
          }
          if (!field2) this.N_finishedTable.push(N_field1Values);
          var N_fieldNodes1 = Xml_SqlSelect.getChildren(O_record1.childNodes);
          while (O_field1 = N_fieldNodes1.shift()) {
            var fieldName1 = O_field1.nodeName;
            var fieldValue1 = Xml_SqlSelect.getValue(O_field1);
            if (!this.A_fields[field1] && field1 != fieldName1) continue;

            EVAL: {
              // See if the key values are a [number] or a [string].
              // test it in the object-type it was meant to be.
              var v1 = field2.toString();
              var v1X = v1;
                v1X = v1X.replace(/0*$/, '');
                v1X = v1X.replace(/\.$/, '');
              var v2 = fieldValue1.toString();
              var v2X = v2;
                v2X = v2X.replace(/0*$/, '');
                v2X = v2X.replace(/\.$/, '');
              var v1_2 = parseFloat(v1.replace(/[$,]/, ''));
              if (v1_2.toString().length == v1X.length && typeof v1_2 == 'number') v1 = v1_2;
              else v1 = "\""+v1+"\"";
              var v2_2 = parseFloat(v2.replace(/[$,]/, ''));
              if (v2_2.toString().length == v2X.length && typeof v2_2 == 'number') v2 = v2_2;
              else v2 = "\""+v2+"\"";

              var evalString = v2+''+expr+''+v1;
              var b_valid = false;
              if (exprName.match(/E_MATCH/)) {
               var v1E = v1.replace(/^\s*['"]\s*/g, '');
               v1E = v1E.replace(/\s*['"]\s*$/g, '');
               var E_ = RegExp(v1E);
               if (exprName == 'E_MATCH' && E_.test(v2)) b_valid = true;
              }
              else {
                try { if (eval(evalString)) b_valid = true; }
                catch(e){}
              }
              /*
              alert(
                "N_and:\t"+N_and+"\n"+
                "v1:\t["+v1+"] ->"+(typeof v1)+"\n"+
                "v2:\t["+v2+"] ->"+(typeof v2)+"\n"+
                "evalStr:\t"+evalString+"\n"+
                "b_valid:\t"+b_valid
              );
              */
            };
            if (b_valid) {
              if (!A_validRec[N_field1Values]) A_validRec[N_field1Values] = 0;
              A_validRec[N_field1Values]++;
              if (A_validRec[N_field1Values] == this.N_AND.length) {
                this.N_finishedTable.push(N_field1Values);
              }
            }
          }
        }
      }
     };
  };

  // Create a numerical array of fields in query for possible associative array keys.
  var N_fields = [];
  for (var FIELD in this.A_fields) N_fields.push(FIELD);

  var N_result = [];
  for (var n_a = 0; n_a < this.N_finishedTable.length; n_a++) {
    var N_tmp = [];
    var A_result = {};
    for (var n_b = 0; n_b < this.N_finishedTable[n_a].length; n_b++) {
      var value_ = this.N_finishedTable[n_a][n_b];

      // See if the value is a [number] or a [string].
      // Return it in the object-type it was meant to be.
      value_ = value_.toString();
      var value_2 = value_.toString();
        value_2 = value_2.replace(/0*$/, '');
        value_2 = value_2.replace(/\.$/, '');

      v1_2 = parseFloat(value_.replace(/[$,]/, ''));
      if (v1_2.toString().length == value_2.length && typeof v1_2 == 'number') value_ = v1_2;
      A_result[N_fields[n_b]] = value_;

      N_tmp.push(value_);
    }
    if (this.arrayType == Xql_ASSOC) N_result.push(A_result);
    else N_result.push(N_tmp);
  }

  return N_result;
};

// Retrieve array list of node children.
Xml_SqlSelect.getChildren = function(n) {
  var N_ = [];
  for (var n_ = 0; n_ < n.length; n_++) {
    if (n[n_].nodeType == 1) N_.push(n[n_]);
  }
  return N_;
};

// Retrieve node value.
// Tunneling through node children may be required
// especially for non-MSIE CDATA content.
Xml_SqlSelect.getValue = function(n) {
  for (var n_ = 0; n_ < n.childNodes.length; n_++) {
    if (n.childNodes[n_].nodeType == 4) {
      return n.childNodes[n_].nodeValue;
    }
  }
  for (var n_ = 0; n_ < n.childNodes.length; n_++) {
    if (n.childNodes[n_].nodeType == 3) {
      return n.childNodes[n_].nodeValue;
    }
  }
  return n.nodeValue;
};

/**
 * Parse the XML document contained in the string argument and return object
 * that represents it.
 **/
Xml_SqlSelect.parse = function(text) {
  if (typeof DOMParser != "undefined") {
    // Mozilla, FireFox, and related browsers.
    return (new DOMParser()).parseFromString(text, "application/xml");
  }
  else if (typeof ActiveXObject != "undefined") {
    // Internet Explorer.
    var doc = Xml_SqlSelect.newDocument(); // Create an empty document.
    doc.loadXML(text); // Parse text into it.
    return doc; // Return it.
  }
  else {
    // As a last resort, try loading the document from a data: url
    // This is supposed to work in Safari.
    var url = "data:text/xml;charset-utf-8," + encodeURIComponent(text);
    var request = new XMLHttpRequest();
    request.send(null);
    return request.responseXML;
  }
};

/**
 * Create a new Document object. If no arguments are specified, the document
 * will be empty. If the root tag is specified, the document will contain that
 * single root tag.
 **/
Xml_SqlSelect.newDocument = function( rootTagName ) {
  if (!rootTagName) rootTagName = "";

  if (document.implementation && document.implementation.createDocument) {
    // This is the W3C standard.
    return document.implementation.createDocument("", rootTagName, null);
  }
  else {
    // This is the IE standard.
    var doc = new ActiveXObject("MSXML.DOMDocument");

    // If there is a root tag, initialize the document
    if (rootTagName) doc.loadXML("<"+rootTagName+" \>");
    return doc;
  }
};

// Serialize an XML Document or Element and return it as a string.
Xml_SqlSelect.serialize = function(node) {
  var xmlStr = null;

  if (typeof XMLSerializer != "undefined") {
    xmlStr = (new XMLSerializer()).serializeToString(node);
  }
  else if (node.xml) {
    xmlStr = node.xml;
  }

  // The XML version tag sometimes gets removed.
  // Put in a default one and hope it is enough.
  if (!xmlStr.match(/\?xml/i)) {
    xmlStr = "<?xml version=\"1.0\"?>\n"+xmlStr;
  }
  return xmlStr;
};