Name
global.PAUtils
Description
Utility functions for Performance Analytics
Script
var PAUtils = Class.create();
PAUtils.prototype = {
initialize: function() {
},
getBusinessCalendarGroups: function() {
var calendarChoiceList = new GlideChoiceList();
// default value, this would always be available
calendarChoiceList.add(new GlideChoice("standard", gs.getMessage("Standard Calendar")));
var gr = new GlideRecord("business_calendar_group");
gr.query();
while (gr.next()) {
var value = gr.getValue('sys_id');
var label = gr.getDisplayValue('name');
calendarChoiceList.add(new GlideChoice(value, label));
}
return calendarChoiceList;
},
/**
return the query to fetch the business calendars associated wit
the calendar group
**/
getBusinessCalendar: function(dependentValue) {
var calendarChoiceList = new GlideChoiceList();
if (dependentValue == 'standard')
return calendarChoiceList;
var groupSysId = dependentValue;
if (JSUtil.nil(groupSysId))
return calendarChoiceList;
var mtom = new GlideRecord('m2m_business_calendar_group_business_calendars');
mtom.addQuery('business_calendar_group', groupSysId);
mtom.query();
while (mtom.next()) {
var element = mtom.getElement('business_calendar');
var value = element.getValue();
var label = element.getDisplayValue();
calendarChoiceList.add(new GlideChoice(value, label));
}
return calendarChoiceList;
},
/**
Get only cubes, which have same frequency and associated with the same Business Calendar & Groups
*/
getCubes: function(currentRecord) {
var calendarValue = currentRecord.getValue('calendar');
var calQuery = 'calendar=' + calendarValue;
// If its standard calendar, match on frequency else use the calendar_freq.
// Empty value on calendar, implies its a standard calendar
if (calendarValue == 'standard')
calQuery = 'calendar=^ORcalendar=standard^frequency=' + currentRecord.frequency;
else
calQuery += '^calendar_freq=' + currentRecord.calendar_freq;
return calQuery;
},
getCollectionIDFromURI: function() {
var collectionID = null;
var url = gs.action.getGlideURI().toString();
var args = url.split("&");
for (var i = 0; i < args.length; i++) {
var parts = args[i].split("=");
if (parts[0] == "sysparm_collectionID") {
collectionID = parts[1];
break;
}
}
return collectionID;
},
/**
* Only automated indicators are supported for text analytics
*/
ref_qual_indicator_text_phrase: function() {
var query = 'benchmarking=false^ORbenchmarkingISEMPTY';
if (current.widget)
query = query + '^sys_id=' + current.widget.indicator;
else
query = query + '^type=1';
return query;
},
ref_qual_widget_text_phrase: function() {
var query = 'type=text';
if (current.indicator) {
query = query + '^indicator=' + current.indicator;
}
return query;
},
ref_qual_widget_indicator: function() {
var ref_qual = 'benchmarking=false^ORbenchmarkingISEMPTY';
if (current.type == 'pivot' || current.type == 'text')
ref_qual = ref_qual + '^type!=4';
if (current.type != 'text')
return ref_qual;
var textIndexIndicators = new GlideRecord('pa_m2m_indicator_text_indexes');
textIndexIndicators.query();
var indicatorsForRefQual = [];
while (textIndexIndicators.next())
indicatorsForRefQual.push(textIndexIndicators.getValue('indicator'));
return ref_qual + '^sys_idIN' + indicatorsForRefQual.join(',');
},
ref_qual_managing_indicator: function(cur_sys_id, type) {
var gr = new GlideRecord('pa_managed_sources_indicators');
gr.query();
var indicator_sysids = [];
while (gr.next())
indicator_sysids.push(gr.getValue('indicator'));
if (indicator_sysids.length === 0)
return 'sys_id=0';
return 'sys_idIN' + indicator_sysids.toString() + '^sys_id!=' + cur_sys_id + '^type=' + type + '^managing_indicatorISEMPTY^EQ';
},
ref_qual_managed_breakdown: function(managing_indicator) {
var gr = new GlideRecord('pa_managed_sources_indicators');
if (managing_indicator)
gr.addQuery('indicator', managing_indicator);
gr.query();
var managed_source_sysids = [];
while (gr.next())
managed_source_sysids.push(gr.getValue('managed_source'));
if (managed_source_sysids.length === 0)
return 'sys_id=0';
var breakdown_sysids = [];
gr = new GlideRecord('pa_managed_sources');
gr.addQuery('sys_id', 'IN', managed_source_sysids);
gr.query();
while (gr.next())
breakdown_sysids.push(gr.getValue('breakdown'));
if (breakdown_sysids.length === 0)
return 'sys_id=0';
return 'sys_idIN' + breakdown_sysids.toString();
},
ref_qual_indicator_breakdown: function(level, followed) {
if (!level) {
level = 1;
}
var ids = [];
var indicator = null;
var job_indicator = null;
if (current == null) {
var url = gs.action.getGlideURI().toString();
var args = url.split("&");
for (var i = 0; i < args.length; i++) {
var parts = args[i].split("=");
if (parts[0] == "sysparm_collectionID") {
job_indicator = parts[1];
break;
}
}
if (job_indicator != null) {
var gr = new GlideRecord('pa_job_indicators');
gr.addQuery('sys_id', job_indicator);
gr.query();
if (gr.next())
indicator = gr.getValue("indicator");
}
} else if (!current.indicator.nil()) {
indicator = current.indicator;
}
if (indicator != null) {
var has_breakdown_matrix = false;
var formula_indicator = false;
var exclusions = {};
var scopedByBreakdown;
if (current == null) {
scopedByBreakdown = true;
} else {
if (level === 1) {
scopedByBreakdown = current.breakdown_level2;
} else if (level === 2) {
scopedByBreakdown = current.breakdown;
}
}
if (followed)
scopedByBreakdown = current.breakdown;
if (scopedByBreakdown) {
var indicatorGr = new GlideRecord('pa_indicators');
indicatorGr.addQuery('sys_id', indicator);
indicatorGr.query();
indicatorGr.next();
has_breakdown_matrix = (indicatorGr.getValue('collect_breakdown_matrix') == true);
formula_indicator = (indicatorGr.getValue('type') == 2);
if (has_breakdown_matrix) {
var indicatorBrekdownExclGr = new GlideRecord('pa_indicator_breakdown_excl');
indicatorBrekdownExclGr.addQuery('indicator', indicator);
indicatorBrekdownExclGr.query();
while (indicatorBrekdownExclGr.next()) {
exclusions[indicatorBrekdownExclGr.breakdown + ':' + indicatorBrekdownExclGr.breakdown_level2] = true;
exclusions[indicatorBrekdownExclGr.breakdown_level2 + ':' + indicatorBrekdownExclGr.breakdown] = true;
}
}
}
if (!followed && scopedByBreakdown && !has_breakdown_matrix && !formula_indicator) {
ids.push("-1");
} else {
var indicatorBreakdownGr = new GlideRecord('pa_indicator_breakdowns');
indicatorBreakdownGr.addQuery('indicator', indicator);
indicatorBreakdownGr.addActiveQuery();
indicatorBreakdownGr.query();
while (indicatorBreakdownGr.next()) {
var sys_id = indicatorBreakdownGr.breakdown.sys_id;
if (scopedByBreakdown) {
if (scopedByBreakdown == sys_id) {
continue;
}
if (exclusions[scopedByBreakdown + ':' + sys_id]) {
continue;
}
}
ids.push(sys_id);
}
}
}
return 'sys_idIN' + ids.toString();
},
ref_qual_indicator_followed_breakdown: function() {
if (current.type == "list")
return '';
if (current.type == "time" && current.visualization == "relative" && current.tag != "")
return '';
return this.ref_qual_indicator_breakdown(1, true);
},
ref_qual_indicator_followed_breakdown_relation: function() {
var ids = [];
if (current.type == 'breakdown' && current.visualization == 'scorecard' && !current.breakdown.nil()) {
var gr = new GlideRecord('pa_breakdown_relations');
gr.addQuery('related_breakdown', current.breakdown);
gr.orderBy('order');
gr.query();
while (gr.next())
ids.push(gr.getValue('sys_id'));
}
if (ids.length === 0)
ids.push('-1');
return 'sys_idIN' + ids.toString();
},
ref_qual_script: function(tableElement) {
return 'tableIN' + this.getTableAncestors(tableElement).toString();
},
// input: indicator
// output: allowed breakdowns
// - you may link any breakdown to the given indicator if it is
// a manual indicator or a formula indicator
// - you may link any manual breakdown to an automated indicator
// - you may link an automated breakdown to an automated indicator
// if it has a mapping where its facts table is equal to the
// facts table (or any of its parent tables) of the indicator.
ref_qual_m2m_indicator_breakdown: function() {
var indicator = this.getCollectionIDFromURI(),
tableElement = null;
if (indicator != null) {
var gr = new GlideRecord("pa_indicators");
gr.addQuery('sys_id', indicator);
gr.query();
if (gr.next()) {
// External indicators
if (gr.getValue("type") == 4)
return 'type=3';
var cube = new GlideRecord("pa_cubes");
cube.addQuery('sys_id', gr.getValue("cube"));
cube.query();
if (cube.next()) {
tableElement = cube.getValue("facts_table");
}
}
}
if (!tableElement)
return '';
var ancestors = this.getTableAncestors(tableElement);
var mapping = new GlideRecord('pa_breakdown_mappings');
mapping.addQuery('facts_table', ancestors);
mapping.query();
var breakdown_ids = [];
while (mapping.next()) {
var breakdown = '' + mapping.breakdown;
var found = false;
for (var i = 0; i < breakdown_ids.length; i++) {
if (breakdown_ids[i] === breakdown) {
found = true;
break;
}
}
if (!found)
breakdown_ids.push(breakdown);
}
var ref_qual = 'type=2';
if (breakdown_ids.length > 0)
ref_qual = ref_qual + '^ORsys_idIN' + breakdown_ids.toString();
return ref_qual;
},
// input: breakdown
// output: allowed indicators
// - you may link a breakdown to any indicator that shares
// the same facts table (or one of its descendants) as any
// facts table in the breakdown mappings of the breakdown
ref_qual_m2m_breakdown_indicator: function() {
var breakdown = this.getCollectionIDFromURI(),
tableElements = [];
if (breakdown != null) {
var gr = new GlideRecord("pa_breakdowns");
gr.addQuery('sys_id', breakdown);
gr.query();
if (gr.next()) {
// External breakdown
if (gr.getValue("type") == '3')
return 'type=4';
if (gr.getValue("type") == '1') {
var mapping = new GlideRecord('pa_breakdown_mappings');
mapping.addQuery('breakdown', breakdown);
mapping.query();
while (mapping.next()) {
var tables = this.getTableDecendants(mapping.getValue("facts_table"));
for (var i = 0; i < tables.length; i++) {
var found = false;
for (var j = 0; j < tableElements.length; j++) {
if (tableElements[j] === tables[i])
found = true;
}
if (!found)
tableElements.push(tables[i]);
}
}
}
}
}
if (tableElements.length === 0)
return 'benchmarking=false^ORbenchmarkingISEMPTY';
return 'type!=4^benchmarking=false^ORbenchmarkingISEMPTY^cubeISEMPTY^ORcube.facts_tableIN' + tableElements.toString();
},
ref_qual_default_indicator: function() {
var ids = [];
var gr = new GlideRecord('pa_widget_indicators');
gr.addQuery('widget', current.sys_id);
gr.addNullQuery('widget_indicator');
gr.query();
while (gr.next()) {
ids.push(String(gr.sys_id));
}
return 'sys_idIN' + ids.toString();
},
ref_qual_breakdown_source_dashboard: function(dashboardSysId) {
var gr = new GlideRecord('pa_m2m_dashboard_sources');
gr.addQuery('dashboard', dashboardSysId);
gr.query();
var breakdown_source_sysids = [];
while (gr.next())
breakdown_source_sysids.push(gr.getValue('breakdown_source'));
if (breakdown_source_sysids.length === 0)
return 'sys_id=0';
return 'sys_idIN' + breakdown_source_sysids.toString() + '^EQ';
},
ref_qual_tabs_dashboard: function(dashboardSysId) {
var gr = new GlideRecord('pa_m2m_dashboard_tabs');
gr.addQuery('dashboard', dashboardSysId);
gr.query();
var tabs_sysIds = [];
while (gr.next())
tabs_sysIds.push(gr.getValue('tab'));
if (tabs_sysIds.length === 0)
return 'sys_id=0';
return 'sys_idIN' + tabs_sysIds.toString() + '^EQ';
},
ref_qual_text_index_configuration_indicator: function() {
var indicatorSysId = this.getCollectionIDFromURI();
if (indicatorSysId == null)
if (current.indicator.cube)
return 'cube=' + current.indicator.cube;
else
return '';
var indicator = new GlideRecord('pa_indicators');
if (!indicator.get(indicatorSysId))
return '';
return 'cube=' + indicator.getValue('cube');
},
ref_qual_text_index_configuration_fields: function() {
var viewTablesString = new SNC.PADBView().getDatabaseViewTables(current.cube.facts_table);
var viewTables = new JSON().decode(viewTablesString);
var tables = [];
if (viewTables && viewTables.length > 0)
for (var i=0; i<viewTables.length; i++) {
if (i>0)
tables += ',';
tables += new TableUtils(viewTables[i]).getTables().toArray().join();
}
else
tables = new TableUtils(current.cube.facts_table).getTables().toArray().join();
var refQual = 'nameIN' + tables + '^elementISNOTEMPTY^internal_type=string^choice=2^ORchoice=0^ORchoice=NULL';
return refQual;
},
ref_qual_indicator_text_index_configuration: function() {
var textIndexConfigurationSysId = this.getCollectionIDFromURI();
var refQualifier = 'type=1^benchmarking=false^ORbenchmarkingISEMPTY';
if (textIndexConfigurationSysId == null)
if (current.text_index_configuration.cube)
return refQualifier + '^cube=' + current.text_index_configuration.cube;
else
return refQualifier;
var textIndexConfiguration = new GlideRecord('pa_text_index_configurations');
if (!textIndexConfiguration.get(textIndexConfigurationSysId))
return refQualifier;
return refQualifier + '^cube=' + textIndexConfiguration.getValue('cube');
},
ref_qual_indicator_default_aggregate: function(indicatorGR) {
return 'sys_idIN' + SNC.PAIndicator.getValidAggregates(indicatorGR);
},
getTableAncestors: function(tableName) {
var tables = [];
var ar = GlideDBObjectManager.get().getTables(tableName);
for (var i = 0; i < ar.size(); i++) {
tables[i] = ar.get(i);
}
return tables;
},
getTableDecendants: function(tableName) {
var tables = [];
var ar = GlideDBObjectManager.get().getAllExtensions(tableName);
for (var i = 0; i < ar.size(); i++) {
tables[i] = ar.get(i);
}
return tables;
},
hasBreakdownMapping: function(sys_id, breakdown, facts_table) {
var mapping = new GlideRecord('pa_breakdown_mappings');
if (sys_id && sys_id != '')
mapping.addQuery('sys_id', '!=', sys_id);
mapping.addQuery('breakdown', breakdown);
mapping.addQuery('facts_table', facts_table);
mapping.query();
return mapping.next() ? true : false;
},
hasRestrictedOperatorsInConditions: function (conditions) {
return this.hasKeywordsInConditions(conditions) ||
this.hasGTFieldInConditions(conditions) ||
this.hasGTOrEqualInCondition(conditions);
},
hasKeywordsInConditions: function(conditions) {
return (conditions.indexOf("123TEXTQUERY321") > -1);
},
hasGTFieldInConditions: function(conditions) {
var regex = /(GT|LT)_FIELD/;
return regex.test(conditions);
},
hasGTOrEqualInCondition: function(conditions) {
var regex = /(GT|LT)_OR_EQUALS_FIELD/;
return regex.test(conditions);
},
getContributors: function(){
var contributors = [];
var userRoles = new GlideRecord('sys_user_has_role');
userRoles.addQuery('role.name', 'pa_contributor');
userRoles.query();
while(userRoles.next()){
contributors.push(userRoles.getValue('user'));
}
return 'sys_idIN' + contributors.toString();
},
getAvailableAggregates: function() {
/*global PAIndicator:true*/
/*eslint no-undef: "error"*/
return PAIndicator.getAvailableAggregates(current.indicator);
},
ref_qual_element_filters: function() {
/*global PABreakdown:true*/
/*eslint no-undef: "error"*/
return PABreakdown.getElementFiltersSysIds(current.breakdown);
},
ref_qual_pivot_element_filters: function() {
/*global PABreakdown:true*/
/*eslint no-undef: "error"*/
return PABreakdown.getElementFiltersSysIds(current.pivot_breakdown);
},
// DEPRECATED: use new PAUtils().getSnapshotIDs()
getSnapshotSysIDs: function(indicator, period, breakdown, element, breakdown_level2, element_level2, aggregate) {
/*global PASnapshot:true*/
/*eslint no-undef: "error"*/
return PASnapshot.getSysIDs(indicator, period, breakdown, element, breakdown_level2, element_level2, aggregate);
},
getSnapshotIDs: function(uuidScope, period) {
/*global PASnapshot:true*/
/*eslint no-undef: "error"*/
return PASnapshot.getIDs(uuidScope, period);
},
getCompareSnapshotIDs: function(uuidScope, period1, period2, type) {
/*global PASnapshot:true*/
/*eslint no-undef: "error"*/
return PASnapshot.getCompareIDs(uuidScope, period1, period2, type);
},
_getIndicator: function(){
var indicator = null;
var job_indicator = null;
if (current == null) {
var url = gs.action.getGlideURI().toString();
var args = url.split("&");
for (var i=0; i<args.length; i++) {
var parts = args[i].split("=");
if (parts[0] == "sysparm_collectionID") {
job_indicator = parts[1];
break;
}
}
if (job_indicator != null) {
var gr = new GlideRecord('pa_job_indicators');
gr.addQuery('sys_id', job_indicator);
gr.query();
if (gr.next())
indicator = gr.getValue("indicator");
}
} else if (!current.indicator.nil()) {
indicator = current.indicator;
}
return indicator;
},
//Reference qualifier for all the breakdowns of an indicator,
//ignores matrix/levels/exclusions
ref_qual_indicator_breakdown_all: function() {
gs.log("ref_qual_indicator_breakdown_all");
var ids = [];
var indicator = this._getIndicator();
if (indicator !== null) {
var gr = new GlideRecord('pa_indicator_breakdowns');
gr.addQuery('indicator', indicator);
gr.addActiveQuery();
gr.query();
while (gr.next()) {
ids.push(gr.breakdown.sys_id);
}
}
return 'sys_idIN' + ids.toString();
},
getParamFromGlideURI: function(param) {
var transaction = GlideTransaction.get();
if (transaction && transaction.getRequest()) {
var url = gs.action.getGlideURI().toString();
var args = url.split("&");
for (var i = 0; i < args.length; i++) {
var parts = args[i].split("=");
if (parts[0] == param)
return parts[1];
}
}
return "undefined";
},
usedInFormulaIndicators: function(indicator_sys_id) {
var formulas = new GlideRecord('pa_indicators');
formulas.addQuery("type", "2"); // formula indicators only
formulas.addQuery("formula", "CONTAINS", indicator_sys_id);
formulas.setLimit(1);
formulas.query();
return formulas.hasNext();
},
getEncodedSnapshotQuery: function(uuid, date, breakdown, elements, filtered_breakdown, elements_filter) {
return PASnapshot.getEncodedQuery(uuid, date, breakdown, elements, filtered_breakdown, elements_filter);
},
getEncodedComparedSnapshotQuery: function (uuid, date1, date2, type) {
return PASnapshot.getEncodedComparedQuery(uuid, date1, date2, type);
},
// Check if a table has diagnostics defined for
isDiagnosablePATable: function (tableName) {
var gr = new GlideRecord('pa_diagnostic');
gr.addActiveQuery();
gr.addQuery('table', tableName);
gr.setLimit(1);
gr.query();
return gr.hasNext();
},
isScriptedBreakdown: function (breakdownSysId, factTable) {
if (JSUtil.nil(breakdownSysId) || JSUtil.nil(factTable))
return false;
var mapping = new GlideRecord('pa_breakdown_mappings');
mapping.addQuery('breakdown', breakdownSysId);
mapping.addQuery('facts_table', factTable);
mapping.addQuery('scripted', true);
mapping.query();
return mapping.hasNext();
},
getTextWidgetRecordSysId: function (uuid, field, date, drillDownWords) {
return new PATextRecords(uuid, field).getRecordSysId(date, drillDownWords);
},
type: 'PAUtils'
};
Sys ID
5d115002c32301006568506adfba8f28