joola.io.engine/routes/query.js

/**
 *  joola.io
 *
 *  Copyright Joola Smart Solutions, Ltd. <info@joo.la>
 *
 *  Licensed under GNU General Public License 3.0 or later.
 *  Some rights reserved. See LICENSE, AUTHORS.
 *
 *  @license GPL-3.0+ <http://spdx.org/licenses/GPL-3.0+>
 */

var
  router = require('./index'),

  pushU = global.pushU, //for jslint
  _datasources = require('../lib/objects/datasources'),
  _datatables = require('../lib/objects/datatables'),
  _dimensions = require('../lib/objects/dimensions'),
  _metrics = require('../lib/objects/metrics'),
  manager = require('../lib/caching/manager'),
  utils = require('../lib/shared/utils'),

  moment = require('moment'),
  twix = require('twix');

exports = index = exports.fetch = {
  name: 'query/fetch',
  description: 'I will execute a query',
  inputs: {
    "required": ['startdate', 'enddate', 'metrics'],
    "optional": ['dimensions', 'resolution', 'filter', 'sortKey', 'sortDir']
  },
  outputExample: {},
  permission: ['access_system'],
  run: function (req, res) {
    var sendResponse = function (merged, dimensions, metrics) {
      var response = {};
      response.resultcode = 200;
      response.resulttext = '';
      response.results = {};
      response.results.dimensions = dimensions;

      _.each(metrics, function (m) {
        if (m.formatter)
          m.formatter = stringify(m.formatter, 'Function');
      });

      response.results.metrics = metrics;
      response.results.rows = merged;

      var runTime = (new Date().getTime() - _timer_start.getTime());
      joola.logger.info('Action: Query, Finished at ' + runTime.toString() + 'ms.');
      joola.logger.log('info', 'Query Finished', {level: 103, querytime: runTime});

      return router.responseSuccess(response, req, res);
    };

    if (!req.user)
      return router.responseError(new router.AuthErrorTemplate('Missing user token'), req, res);

    try {
      var _timer_start = new Date();
      var startdate = new Date(req.params.startdate).fixDate(true, true);
      //onsole.log(new Date(connection.params.enddate));
      var enddate = new Date(req.params.enddate).fixDate(true, true);

      var dt = _datatables.get(joola.config.integration.datasources[0].enddate.maxcachedate);
      dt.datasource = _datasources.get(dt.datasourceid);
      manager.maxCacheDate(dt, function (maxDate) {

        if (maxDate == null)
          maxDate = new Date();
        if (maxDate < enddate)
          enddate = new Date(maxDate).fixDate(true, true);

        enddate.setMilliseconds(enddate.getMilliseconds() - 1);

        var dimensions = [];
        var dimensionsForCount = [];
        var metrics = [];
        var metricsForCount = [];
        var metricsToRemove = [];

        var splits = req.params.dimensions.split(',');
        _.each(splits, function (dimension) {
          dimension = dimension.trim().toLowerCase();
          var _dimension = _dimensions.get(dimension);

          if (_dimension) {
            if (_dimension.class == 'calculated') {
              _dimension._name = _dimension.name;
              _dimension.name = _dimension.column;
            }

            dimensions.push(_dimension);

            //else
            //    joola.logger.warn('User [' + connection.session.user.roles + '] does not have permission for dimension [' + _dimension.name + '].')
          }
        });

        splits = req.params.metrics.split(',');
        _.each(splits, function (metric) {
          metric = metric.trim().toLowerCase();
          var _metric;
          if (metric.class == 'calculated') {
            _metric = _metrics.get(metric);
          }
          else {
            _metric = _metrics.get(metric);
          }
          if (_metric) {
            if (_metric.class != 'calculated')
              pushU(metrics, _metric);
            else
              pushU(metricsForCount, _metric);

            if (_metric.aggregation == 'count') {
              exist = _.find(dimensions, function (d) {
                return d.id == _metric.dimension;
              });
              if (!exist) {
                _dimension = _dimensions.get(_metric.dimension);
                //dimensions.push(_dimension);
                dimensionsForCount.push(_dimension);
              }
            }
            else {
              if (_metric.formula) {
                var m_left = _metrics.get(_metric.formula.left);
                var m_right = _metrics.get(_metric.formula.right);


                var exist, _dimension;

                if (m_left.aggregation == 'count') {
                  exist = _.find(dimensions, function (d) {
                    return d.id == m_left.dimension;
                  });
                  if (!exist) {
                    _dimension = _dimensions.get(m_left.dimension);
                    //dimensions.push(_dimension);
                    dimensionsForCount.push(_dimension);
                  }
                }

                if (m_left.class != 'calculated') {
                  if (!_.find(metrics, function (m) {
                    return m.id == m_left.id;
                  })) {
                    m_left.remove = true;
                    pushU(metrics, m_left);
                    pushU(metricsToRemove, m_left);
                  }
                }
                else if (!_.find(metricsForCount, function (m) {
                  return m.id == m_left.id;
                })) {
                  m_left.remove = true;
                  pushU(metricsForCount, m_left);
                  pushU(metricsToRemove, m_left);
                }


                if (m_right.aggregation == 'count') {
                  exist = _.find(dimensions, function (d) {
                    return d.id == m_right.dimension;
                  });
                  if (!exist) {
                    _dimension = _dimensions.get(m_right.dimension);
                    //dimensions.push(_dimension);
                    dimensionsForCount.push(_dimension);
                  }
                }

                if (m_right.class != 'calculated') {
                  if (!_.find(metrics, function (m) {
                    return m.id == m_right.id;
                  })) {
                    m_right.remove = true;
                    pushU(metrics, m_right);
                    pushU(metricsToRemove, m_right);
                  }
                }
                else if (!_.find(metricsForCount, function (m) {
                  return m.id == m_right.id;
                })) {
                  m_right.remove = true;
                  pushU(metricsForCount, m_right);
                  pushU(metricsToRemove, m_right);
                }

              }
            }
            //else
            //    joola.logger.warn('User [' + connection.session.user.roles + '] does not have permission for metric [' + _metric.name + '].')
          }
        });

        var merged = [];
        var query = {};
        query.startdate = startdate;
        query.enddate = enddate;
        //query.datatable = _datatables.get(metrics[0].datatableid);
        query.dimensions = dimensions;
        query.metrics = metrics;
        query.filtertext = req.params.filter || '' + (req.user._filter ? req.user._filter : '').toString();
        query.filters = [];

        console.log(req.params);

        //console.log(query);
        console.log(query.filtertext);

        var filterSplit = query.filtertext.split('[AND]');
        _.each(filterSplit, function (filter) {
          var _filter = {};
          var parts = filter.split('*=');
          if (parts.length > 1) {
            _filter.dimension = parts[0].trim();
            _filter.operator = '*=';
            _filter.value = parts[1].trim();
          }
          else {
            parts = filter.split('=');
            if (parts.length > 0) {
              _filter.dimension = parts[0];
              _filter.operator = '=';
              _filter.value = parts[1]
            }
          }
          if (_filter.dimension) {
            var _fdimension = _dimensions.getByName(_filter.dimension);
            if (!_fdimension)
              _fdimension = _dimensions.get(_filter.dimension);
            if (_fdimension) {
              _filter.dimension = _fdimension;
              query.filters.push(_filter);
            }
          }
        });

        query.sortKey = req.params.sortKey;
        if (query.sortKey) {
          query.sortDir = req.params.sortDir;

          var _sortKey = _dimensions.get(query.sortKey);
          if (_sortKey) {
            if (_sortKey.class == 'calculated') {
              _sortKey.name = _sortKey.column;
              query.sortKey = _sortKey;
            }
            else
              query.sortKey = _sortKey;
          }
          else {
            _sortKey = _metrics.get(query.sortKey);
            if (_sortKey) {
              query.sortKey = _sortKey;
            }
            else
              query.sortKey = null;
          }
        }

        query.resolution = req.params.resolution.toLowerCase() || 'day';

        //if (!cachedResult) {
        joola.logger.debug('Compiling list of participating collections...');
        var collections = [];
        _.each(query.metrics, function (m) {

          if (m.class != 'calculated') {
            if (!m.datatable)
              m.datatable = _datatables.get(m.datatableid);
            var _table = _.find(collections, function (c) {
              if (c)
                return c.id == m.datatable.id;
              else
                return false;
            });
            if (!_table) {

              pushU(collections, ce.clone(m.datatable));
            }
          }
          else {

          }
        });

        var calls = [];
        var subcalls = [];
        var results = [];

        _.each(collections, function (c) {
          var call = function (query, c, callback) {
            query.datatable = ce.clone(c);
            var dlist = '';
            var mlist = '';
            _.each(query.dimensions, function (_d) {
              dlist += _d.id;
            });

            _.each(query.metrics, function (_m) {
              mlist += _m.id;
            });

            var sortKey = (sortKey ? sortKey.name : '');
            var resultKey = c.id + '_' + query.startdate + '_' + query.enddate + '_' + dlist + '_' + mlist + '_' + query.resolution + '_' + query.filtertext + '_' + sortKey + '_' + query.sortDir;
            resultKey = resultKey.clean();
            var cachedResult = false;

            joola.cache.load('results', resultKey, function (err, value) {
              if (err) {
                cachedResult = false;
              }
              else {
                joola.logger.info('Found cached result [' + resultKey + ']');
                //cachedResult = true;
              }
              if (!cachedResult) {
                manager.fetch(ce.clone(query), function (err, result, query) {
                  joola.logger.debug('Received result from fetch [' + query.datatable.id + '], rows: ' + result.length);
                  results.push({source: query.datatable.id, data: result});
                  joola.cache.save('results', resultKey, result, function (err) {
                    callback(err);
                  });
                });
              }
              else {
                results.push({source: query.datatable.id, data: value});
                callback();
              }
            });

          };
          calls.push(function (callback) {
            call(ce.clone(query), ce.clone(c), function () {
              callback();
            });
          });
        });

        var calcMetrics = _.find(metricsForCount, function (m) {
          return m.class == 'calculated' && m.aggregation == 'count';
        });

        if (calcMetrics && !Array.isArray(calcMetrics)) {
          var _calcMetrics = [];
          _calcMetrics.push(calcMetrics);
          calcMetrics = _calcMetrics;
        }

        _.each(calcMetrics, function (m) {
          var call = function (query, callback) {
            var _query = ce.clone(query);

            _query.type = 'distinct';
            var _metric = _metrics.get(m.metric);
            _metric.datatable = _datatables.get(_metric.datatableid);
            _query.datatable = ce.clone(_metric.datatable);
            _query.distinctCount = true;

            var _dimension = _dimensions.get(m.dimension);
            _query.dimensions.push(_dimension);
            _query.metrics = [m];

            _query.sortKey = query.sortKey;
            _query.sortDir = query.sortDir;

            var dlist = '';
            var mlist = '';
            _.each(_query.dimensions, function (_d) {
              dlist += _d.id;
            });

            _.each(_query.metrics, function (_m) {
              mlist += _m.id;
            });

            var sortKey = (sortKey ? sortKey.name : '');
            var resultKey = 'distinct_' + m.name + '_' + _query.startdate + '_' + _query.enddate + '_' + dlist + '_' + mlist + '_' + _query.resolution + '_' + _query.filtertext + '_' + sortKey + '_' + _query.sortDir;
            resultKey = resultKey.clean();
            var cachedResult = false;
            joola.cache.load('results', resultKey, function (err, value) {
              if (err) {
                cachedResult = false;
              }
              else {
                joola.logger.info('Found cached result [' + resultKey + ']');
                //cachedResult = true;
              }
              if (!cachedResult) {
                manager.fetch(ce.clone(_query), function (err, result, query) {
                  joola.logger.debug('Received result from fetch [' + query.datatable.id + '], rows: ' + result.length);
                  results.push({source: 'distinct_' + m.name, data: result, dimension: _dimension});


                  joola.cache.save('results', resultKey, result, function (err) {
                    callback(err);
                  });
                });
              }
              else {
                results.push({source: 'distinct_' + m.name, data: value, dimension: _dimension});
                callback();
              }
            });
          };
          subcalls.push(function (callback) {
            call(ce.clone(query), callback);
          });
        });

        /*
         if (calls.length == 0) {
         calls.push(function (callback) {
         callback();
         })
         }*/

        joola.logger.info('Fetching collections [' + calls.length + ',' + subcalls.length + ']...');
        fork(calls, function () {
          fork(subcalls, function () {
            try {
              calls = [];
              _.each(results, function (result) {
                  if (result.data) {


                    var exist = _.find(dimensions, function (d) {
                      return d.id == 'date.date'
                    });
                    if (exist) {
                      var dt = _datatables.get(result.source);
                      var itr = moment.twix(query.startdate, query.enddate).iterate("days");
                      while (itr.hasNext()) {
                        var _d = itr.next();
                        const formattedDate = _d.format('YYYY-M-D');

                        var dateExist = _.find(result.data, function (document) {
                          //noinspection JSReferencingMutableVariableFromClosure
                          return new Date(document._id['date']).format('yyyy-m-d') == formattedDate;
                        });

                        if (!dateExist && _d._d.fixDate(true, true) < query.enddate) {
                          var structure = {_id: {}};
                          var document = result.data[0];
                          _.each(document, function (value, key) {
                            if (typeof(value) == 'object') {
                              structure[key] = {};
                              _.each(value, function (_value, _key) {
                                structure[key][_key] = 0;
                              });
                            }
                            else
                              structure[key] = 0;
                          });

                          structure._id['date'] = _d._d;

                          if (dt) {
                            var ds = _datasources.get(dt.datasourceid);
                            if (ds.GMTbased)
                              structure._id['date'] = structure._id['date'].fixDate(true, false, true);//.format('yyyy-mm-ddThh:nn:ss.fffZ');
                            else
                              structure._id['date'] = structure._id['date'].fixDate(true, false, false).format('yyyy-mm-ddThh:nn:ss.fffZ');
                          }
                          else
                            structure._id['date'] = structure._id['date'].fixDate(true, false, false).format('yyyy-mm-ddThh:nn:ss.fffZ');
                          result.data.push(structure);
                        }
                      }

                      result.data = _.sortBy(result.data, function (document) {
                        return new Date(document._id['date']);
                      });
                    }

                    var call = function (result, callback) {
                      //var pool = new functionpool.Pool({size: 20}, function (callback) {
                      var dt = _datatables.get(result.source);

                      if (!dt && result.source.indexOf('distinct_') > -1) {
                        var d = result.dimension;
                        _.each(result.data, function (document) {
                          delete document._id[d.name];
                        });
                      }
                      var allmetrics = _metrics.list();

                      var filteredDimensions = _.filter(dimensions, function (d) {
                        return d.class == 'calculated';
                      });

                      _.each(query.filters, function (f) {
                        if (f.dimension.type == 'date') {
                          var _d = f.dimension;
                          _d._name = _d.name;
                          filteredDimensions.push(_d);

                        }
                      });

                      _.each(result.data, function (document) {
                        var id = document._id;

                        var distinctKeys = {};
                        var rowcount = document.rowcount;

                        _.each(metricsForCount, function (metric) {
                          if (metric.formula)
                            document[metric.name] = -1;
                        });

                        if (document._id.date)
                          document._id.date = new Date(document._id.date);

                        _.each(filteredDimensions, function (d) {
                          if (d.class == 'calculated') {
                            if (d.type == 'date') {
                              switch (d._name.toLowerCase()) {
                                case 'date':
                                  document._id[d._name] = document._id.date;
                                  break;
                                case 'day of week':
                                  document._id[d._name] = document._id.date.dayName();
                                  break;
                                case 'month':
                                  document._id[d._name] = utils.formatDate(document._id.date, 'mmm yyyy');
                                  break;
                                case 'quarter':
                                  document._id[d._name] = 'Q' + document._id.date.quarterNumber();
                                  break;
                                case 'week':
                                  document._id[d._name] = 'Week ' + document._id.date.weekNumber()[1];
                                  break;
                                case 'year':
                                  document._id[d._name] = utils.formatDate(document._id.date, 'yyyy');
                                  break;
                                default:
                                  document._id[d._name] = document.date;
                                  break;
                              }
                            }
                          }
                        });
                        delete document._id.date;

                        var exist = _.find(merged, function (merge) {
                          return JSON.stringify(merge.dimensions) == JSON.stringify(id);
                        });
                        if (exist) {
                          row = exist;
                        }
                        else {
                          joola.logger.silly('Pushing new dimensions key to merged collection [' + id + '].');
                          var row = {};
                          row.dimensions = id;
                          _.each(document._id, function (value, key) {
                            row[key] = value;
                          });
                          merged.push(row);
                        }

                        _.each(document, function (value, key) {
                          var exist;
                          if (dt) {
                            exist = _.find(dt.metrics, function (metric) {
                              return metric.name == key || '_' + metric.name == key;
                            });
                          }

                          if (!exist) {
                            exist = _.find(metricsForCount, function (metric) {
                              return metric.name == key || '_' + metric.name == key;
                            });
                          }

                          if (exist) {
                            if (exist.aggregation == 'min') {
                              if (parseFloat(value) < parseFloat(row[key])) {
                                row[key] = value;
                              }
                            }
                            else if (exist.aggregation == 'max') {
                              if (parseFloat(value) > parseFloat(row[key])) {
                                row[key] = value;
                              }
                            }
                            else if (exist.aggregation == 'avg') {
                              value = (parseFloat(value) / parseFloat(rowcount)).toFixed(2);
                              row[key] = parseFloat(value);
                            }

                            else if (exist.aggregation == 'count') {
                              row[key] = value;
                            }
                            else if (exist.aggregation == 'sum') {
                              row[key] = value;
                            }
                            else
                              row[key] = value;
                          }
                          else {
                            if (!row[key]) {
                              exist = _.find(allmetrics, function (metric) {
                                return metric.name == key || '_' + metric.name == key;
                              });
                              if (exist)
                                row[key] = 0;
                            }
                          }
                        });

                      });
                      callback();
                    };

                    calls.push(function (callback) {
                      call(result, callback);
                    });
                  }
                }
              );

              fork(calls, function () {
                try {
                  _.each(metricsForCount, function (m) {
                    if (m.formula) {
                      var left = _metrics.get(m.formula.left);
                      var right = _metrics.get(m.formula.right);

                      _.each(merged, function (row) {
                        var left_value = parseFloat(row[left.name]);
                        var right_value = parseFloat(row[right.name]);

                        var value = eval(left_value + m.formula.function + right_value + (m.formula.post ? m.formula.post : ''));
                        row[m.name] = parseFloat(value.toFixed(2));
                        if (m.remove && m.remove == true) {
                          //delete row[m.name];
                        }

                        _.each(metrics, function (_m) {
                          if (_m.remove == true && (_m.id == left.id || _m.id == right.id)) {
                          }
                          //delete row[_m.name];
                        });
                        _.each(metricsForCount, function (_m) {
                          if (_m.remove == true && (_m.id == left.id || _m.id == right.id)) {
                          }
                          // delete row[_m.name];
                        });
                      });
                    }
                  });

                  _.each(dimensions, function (d) {
                    if (d._name) {
                      d.name = d._name;
                      delete d._name;
                    }
                  });

                  if (calcMetrics && calcMetrics.length > 0)
                    metrics = metrics.concat(calcMetrics);
                  if (metricsForCount && metricsForCount.length > 0)
                    metrics = metrics.concat(metricsForCount);

                  var __metrics = [];
                  _.each(metrics, function (m) {
                    if (m.remove != true) {
                      pushU(__metrics, m);
                    }
                  });
                  metrics = __metrics;

                  //organize keys
                  var _merged = [];
                  var _out_dimensions = [];
                  var _out_metrics = [];
                  splits = req.params.dimensions.split(',');

                  _.each(splits, function (d) {
                    d = _dimensions.get(d.trim());
                    if (d)
                      _out_dimensions.push(d);
                  });
                  splits = req.params.metrics.split(',');
                  _.each(splits, function (m) {
                    m = _metrics.get(m.trim());
                    if (m)
                      _out_metrics.push(m);
                  });

                  _.each(merged, function (row) {
                    var _row = {};

                    _.each(_out_dimensions, function (d) {
                      if (row[d.name])
                        _row[d.name] = row[d.name];
                    });

                    _.each(_out_metrics, function (m) {
                      if (row[m.name])
                        _row[m.name] = row[m.name];
                      else
                        _row[m.name] = 0;
                    });

                    _.each(query.filters, function (filter) {
                      if (filter.dimension.type == 'date') {
                        var bfound = true;

                        if (filter.dimension.id == 'date.date') {

                          bfound = row[filter.dimension.name].getTime() == new Date(filter.value).getTime();
                        }
                        else if (filter.dimension.type == 'date') {

                          bfound = row[filter.dimension.name] == filter.value;
                        }
                        if (!bfound) {
                          _.each(_out_metrics, function (m) {
                            _row[m.name] = 0;
                          });
                        }
                      }
                    });

                    _merged.push(_row);
                  });
                  merged = _merged;

                  sendResponse(merged, _out_dimensions, _out_metrics);
                }
                catch (ex) {
                  joola.logger.error('Query action error (1): ' + ex.message);
                  console.log(ex.stack);
                }
              });
            } catch (ex) {
              joola.logger.error('Query action error (2): ' + ex.message);
              console.log(ex.stack);
            }
          })
        });
      });
    }
    catch (ex) {
      //joola.logger.error('Query action failed (3): ' + ex.message);
      throw ex;
    }
  }
};