WebCompDev / COMPONENTS

  function alasql_QUERY (data, fields, filters, aggregators, orders) {

      if(fields && fields.length == 0)

          return [];
  

      return alasql(alasql_QUERY_string(fields, filters, aggregators, orders), [data]);
  }
  
  function alasql_QUERY_string (fields, filters, aggregators, orders) {

      if(fields) {
          var _fields = _addParenthesis(fields);
          var select = _alasql_SELECT(_fields);
      }

  
      var where = "";
      if(filters && filters.length) {
          var _filters = _copy(filters);
          where = _alasql_WHERE(_filters);
      }
  
      var orderBy = "";
      if(orders && orders.length) {
          var _orders = _copy(orders);
          orderBy = _alasql_ORDERBY(_orders);
      }
  
      var groupBy = "";
      if(aggregators && aggregators.length) {
          var _aggregators = _copy(aggregators);
          groupBy = _alasql_GROUPBY(_aggregators);
          select = groupBy[0];
          groupBy = groupBy[1];
      }
  
      var query = select + " FROM ?" + where + " " + groupBy + " " + orderBy;
  

      return query;

  }
  
  function _alasql_SELECT (fields) {
  
      var select = "SELECT ";
  
      if(fields[0] == "*")
          return select += "*";
  
      for (var i = 0; i < fields.length; i++)
          select += fields[i] + ", ";
      return select.slice(0, -2);
  }
  
  function _alasql_WHERE (filters) {

      //var logicalOperator;
      //
      ///*DEPRECATED*/if(filters[0].logicalOperator == undefined) {
      //    logicalOperator = "AND"
      //}
      //else {
      //    logicalOperator = filters[0].logicalOperator;
      //    filters = filters.slice(1);
      //}

      var where = " WHERE ";
      var logicalOperator = filters[0].logicalOperator;
      filters = filters.slice(1);

  
      for (var i=0; i < filters.length; i++)
          filters[i]["field"] = _normalizeField(filters[i]["field"]);
  
      for (var i=0; i < filters.length; i++) {
          if(filters[i]["operation"] == "contains")
              where += filters[i]["field"] + " like '%" + filters[i]["value"] + "%' " + logicalOperator + " ";

          else if(filters[i]["operation"] == "notContains")

              where += filters[i]["field"] + " not like '%" + filters[i]["value"] + "%' " + logicalOperator + " ";
          else if(filters[i]["operation"] == "start")
              where += filters[i]["field"] + " like '" + filters[i]["value"] + "%' " + logicalOperator + " ";
          else if(filters[i]["operation"] == "ends")
              where += filters[i]["field"] + " like '%" + filters[i]["value"] + "' " + logicalOperator + " ";
          else
              where += filters[i]["field"] + " " + filters[i]["operation"] + " " + filters[i]["value"] + " " + logicalOperator + " ";
      }
  
      return where.slice(0, -4);
  }
  
  function _alasql_GROUPBY (aggregators) {
      for (var i=0; i < aggregators.length; i++)
          aggregators[i]["field"] = _normalizeField(aggregators[i]["field"]);
  
      var select = "SELECT ";
      var groupBy = "GROUP BY ";
  
      for (var i = 0; i < aggregators.length; i++) {
          if(aggregators[i]["operation"] == "GROUP BY") {
              select += aggregators[i]["field"] + ", ";
              groupBy += aggregators[i]["field"] + ", ";
          }
          else

              //select += aggregators[i]["operation"] + "(" + aggregators[i]["field"] + ") as " + aggregators[i]["field"] + ", ";
              select += aggregators[i]["operation"] + "(" + aggregators[i]["field"] + "), ";

      }
  
      return [select.slice(0, -2), groupBy.slice(0, -2)];
  }
  
  function _alasql_ORDERBY (orders) {
      for (var i=0; i < orders.length; i++)
          orders[i]["field"] = _normalizeField(orders[i]["field"]);
  
      var orderBy = "ORDER BY ";
  
      for (var i = 0; i < orders.length; i++)
          orderBy += orders[i]["field"] + " " + orders[i]["operation"] + ", ";
  
      return orderBy.slice(0, -2);
  }
  
  function _addParenthesis (fields) {
      var result = [];
  
      for (var i=0; i < fields.length; i++)
          result.push(_normalizeField(fields[i]));
  
      return result;
  }
  
  function _normalizeField (field) {

      return "`" + field + "`";
      //return "[" + field + "]";

  }
  

  function alasql_transformData (data, fields, round) {

      if(!data || data.length == 0)
          return [];
  

      var tData = [];
  
      for (var i in fields){
  
          var field = fields[i];
          var values = [];
  

          for (var j in data) {
              var v = data[j][field];
              if(round)

                  if(!isNaN(v) && v % 1 != 0)

                      v = Math.round(v * 1000000) / 1000000;

              values.push(v);
          }
  
          tData.push({
              name: field,
              data: values
          });
      }
  
      return tData;
  };
  
  function _copy (o) {
      var out, v, key;
      out = Array.isArray(o) ? new Array(o.length) : {};
      for (key in o) {
          v = o[key];
          out[key] = (typeof v === "object") ? this._copy(v) : v;
      }
      return out;
  }