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