Name

sn_sd.UpdateFormulaUtils

Description

Helper functions to update cost and count formulas of Primary indicators.

Script

var UpdateFormulaUtils = Class.create();


UpdateFormulaUtils.prototype = {
  initialize: function() {
      this.CONTRIBUTOR_REGISTRY_TABLE = 'sn_sd_contributing_indicator_registry';
      this.PRIMARY_INDICATOR_TABLE = 'sn_sd_primary_indicator';
      this.SD_FORMULA_VIEW = 'sn_sd_formula_view'; //Created to get persona time saving and kpi detail in single view and glide record
      this.PA_INDICATOR_TABLE = 'pa_indicators';
      this.objConstants = new sn_sd.SDConstants();
  },

  //Formula Diff
  isFormulaUpdated: function(primaryIndicator) {
      var primary_indicator = new GlideRecord(this.PRIMARY_INDICATOR_TABLE);
      primary_indicator.addQuery('primary_indicator', primaryIndicator);
      primary_indicator.addQuery('primary_indicator.indicator_level', this.objConstants.getIndicatorLevel(1));
      primary_indicator.query();
      var formula = new sn_sd.UpdateFormulaUtils();
      count_formula = formula.getCountFormula(primaryIndicator);
      cost_formula = formula.getCostFormula(primaryIndicator);

      while (primary_indicator.next()) {
          var source_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
          source_indicator.get(primary_indicator.indicator);

          if (primary_indicator.type == 'count' && source_indicator.formula != count_formula)
              return false;
          else if (primary_indicator.type == 'cost' && source_indicator.formula != cost_formula)
              return false;
      }
      return true;
  },

  getIndicatorFormula: function(indicatorSysId) {
      var pa_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
      pa_indicator.get(indicatorSysId);
      return pa_indicator.formula;
  },

  getIndicatorName: function(indicatorSysId) {
      var pa_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
      pa_indicator.get(indicatorSysId);
      return pa_indicator.getDisplayValue('name');
  },


  //Count
  getCountFormula: function(primaryIndicator, serviceGroup) {
      var gr = new GlideRecord(this.CONTRIBUTOR_REGISTRY_TABLE);
      gr.addQuery('primary_kpi', primaryIndicator);
      gr.addQuery('active', true);
      if (serviceGroup != '')
          gr.addQuery('service_group', serviceGroup);
      gr.orderBy('order');
      gr.query();
      var count_formula = '';
      while (gr.next()) {
          if (count_formula != '')
              count_formula = count_formula + " + [[" + gr.contributing_indicator.sys_id + "]]";
          else {
              count_formula = count_formula + "[[" + gr.contributing_indicator.sys_id + "]]";
          }
      }
      return count_formula;
  },

  updateCountFormula: function(primaryIndicator) {
      var success_kpis = [];
      var error_kpis = [];
      var correct_kpis = [];
      var primary_indicator = new GlideRecord(this.PRIMARY_INDICATOR_TABLE);
      primary_indicator.addQuery('type', 'count');
      primary_indicator.addQuery('primary_indicator', primaryIndicator);
      primary_indicator.addQuery('primary_indicator.indicator_level', this.objConstants.getIndicatorLevel(1));
      primary_indicator.query();
      while (primary_indicator.next()) {
              var count_formula = this.getCountFormula(primaryIndicator, primary_indicator.service_group);
              var source_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
              source_indicator.get(primary_indicator.indicator);
              if (source_indicator.formula != count_formula) {
                  source_indicator.formula = count_formula;
                  if (source_indicator.update())
                      success_kpis.push(this.getIndicatorName(primary_indicator.indicator));
                  else
                      error_kpis.push(this.getIndicatorName(primary_indicator.indicator));
              } else
                  correct_kpis.push(this.getIndicatorName(primary_indicator.indicator));
      }
      var result = {};
      result.success_kpis = success_kpis;
      result.error_kpis = error_kpis;
      result.correct_kpis = correct_kpis;
      return result;
  },


  //Cost
  getCostFormula: function(primaryIndicator, serviceGroup) {
      var gr = new GlideRecord(this.SD_FORMULA_VIEW);
      gr.addQuery('contri_primary_kpi', primaryIndicator);
      gr.addQuery('contri_active', true);
      if (serviceGroup != '')
          gr.addQuery('contri_service_group', serviceGroup)
      gr.orderBy('contri_order');
      gr.orderBy('ptime_time_saved');
      gr.query();
      var cost_formula = '';
      while (gr.next()) {
          var time_saved = gr.ptime_time_saved.dateNumericValue() * gr.pgroup_hourly_salary.getCurrencyValue() / 1000 / 3600;
          if (cost_formula != '')
              cost_formula = cost_formula + " + " + time_saved + " * [[" + gr.contri_contributing_indicator.sys_id + "]]";
          else {
              cost_formula = cost_formula + time_saved + " * [[" + gr.contri_contributing_indicator.sys_id + "]]";
          }
      }
      return cost_formula;

  },

  updateCostFormula: function(primaryIndicator) {
      var success_kpis = [];
      var error_kpis = [];
      var correct_kpis = [];
      var primary_indicator = new GlideRecord(this.PRIMARY_INDICATOR_TABLE);
      primary_indicator.addQuery('type', 'cost');
      primary_indicator.addQuery('primary_indicator', primaryIndicator);
      primary_indicator.addQuery('primary_indicator.indicator_level', this.objConstants.getIndicatorLevel(1));
      primary_indicator.query();
      while (primary_indicator.next()) {
          var cost_formula = this.getCostFormula(primaryIndicator, primary_indicator.service_group);
          var source_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
          source_indicator.get(primary_indicator.indicator);
          if (source_indicator.formula != cost_formula) {
              source_indicator.formula = cost_formula;
              if (source_indicator.update())
                  success_kpis.push(this.getIndicatorName(primary_indicator.indicator));
              else
                  error_kpis.push(this.getIndicatorName(primary_indicator.indicator));
          } else
              correct_kpis.push(this.getIndicatorName(primary_indicator.indicator));
      }
      var result = {};
      result.success_kpis = success_kpis;
      result.error_kpis = error_kpis;
      result.correct_kpis = correct_kpis;
      return result;
  },


  //Average Count
  getAverageCountFormula: function(primaryIndicator, serviceGroup) {
      var pattern = /[0-9a-f]{32}/g;
      var regExp = /\(([^)]+)\)/;

      // Get KPIs contributing to target KPI
      var gr = new GlideRecord(this.CONTRIBUTOR_REGISTRY_TABLE);
      gr.addQuery('primary_kpi', primaryIndicator);
      gr.addQuery('active', true);
      if (serviceGroup != '')
          gr.addQuery('service_group', serviceGroup);
      gr.orderBy('order');
      gr.query();
      var kpi_numerator = '';
      var kpi_denominator = '';

      // Get formula of contributing KPIs & extract numerator & denominator
      while (gr.next()) {
          var formula = this.getIndicatorFormula(gr.contributing_indicator.sys_id);
          if (formula) {
              if (formula.match(regExp) != null)
                  formula = formula.match(regExp)[1];
              formula = formula.split('/');
              var contri_numerator = null;
              var contri_denominator = null;
              for (i = 0; i < formula.length; i++) {
                  if (contri_denominator != null)
                      break;
                  if (formula[i].match(pattern) != null) {
                      if (contri_numerator == null)
                          contri_numerator = formula[i].trim();
                      else
                          contri_denominator = formula[i].trim();
                  }
              }
              if (contri_numerator != null && contri_denominator != null) {
                  if (kpi_numerator == '')
                      kpi_numerator = contri_numerator;
                  else
                      kpi_numerator = kpi_numerator + " + " + contri_numerator;
                  if (kpi_denominator == '')
                      kpi_denominator = contri_denominator;
                  else
                      kpi_denominator = kpi_denominator + " + " + contri_denominator;
              }
          }
      }
      if ((!kpi_numerator || !kpi_denominator) && gr.getRowCount() != 0)
          return -1;

      // Update formula as numerator/denominator for target KPI'
      var count_average_formula = '';
      if (kpi_numerator != '' && kpi_denominator != '')
          count_average_formula = '(' + kpi_numerator + ') / (' + kpi_denominator + ')';
      return count_average_formula;
  },

  updateAverageCountFormula: function(primaryIndicator) {
      var success_kpis = [];
      var error_kpis = [];
      var correct_kpis = [];
      var primary_indicator = new GlideRecord(this.PRIMARY_INDICATOR_TABLE);
      primary_indicator.addQuery('type', 'count');
      primary_indicator.addQuery('primary_indicator', primaryIndicator);
      primary_indicator.addQuery('primary_indicator.indicator_level', this.objConstants.getIndicatorLevel(1));
      primary_indicator.query();
      while (primary_indicator.next()) {
          var count_average_formula = this.getAverageCountFormula(primaryIndicator, primary_indicator.service_group);
          if (count_average_formula == -1)
              error_kpis.push(this.getIndicatorName(primary_indicator.indicator));
          else {
              var source_indicator = new GlideRecord(this.PA_INDICATOR_TABLE);
              source_indicator.get(primary_indicator.indicator);
              if (count_average_formula != '') {
                  if (source_indicator.unit.getDisplayValue() == "%")
                      count_average_formula = '(' + count_average_formula + ') * 100';
                  else if (source_indicator.unit.getDisplayValue() == "Days")
                      count_average_formula = '(' + count_average_formula + ') / 24';
              }
              if (source_indicator.formula != count_average_formula) {
                  source_indicator.formula = count_average_formula;
                  if (source_indicator.update())
                      success_kpis.push(this.getIndicatorName(primary_indicator.indicator));
                  else error_kpis.push(this.getIndicatorName(primary_indicator.indicator));
              } else
                  correct_kpis.push(this.getIndicatorName(primary_indicator.indicator));
          }
      }
      var result = {};
      result.success_kpis = success_kpis;
      result.error_kpis = error_kpis;
      result.correct_kpis = correct_kpis;
      return result;
  },

  type: 'UpdateFormulaUtils'
};

Sys ID

1391970a2c613010f8779bd3fe956f55

Offical Documentation

Official Docs: