Name
global.PAIndicatorBreakdownUtil
Description
Utility for new Indicator creation.
Script
var PAIndicatorBreakdownUtil = Class.create();
PAIndicatorBreakdownUtil.prototype = Object.extendsObject(AbstractAjaxProcessor, {
PA_BREAKDOWNS: 'pa_breakdowns',
PA_BREAKDOWN_SOURCE: 'pa_dimensions',
PA_BREAKDOWN_MAPPING: 'pa_breakdown_mappings',
PA_INDICATION_BREAKDOWN_MAPPING: 'pa_indicator_breakdowns',
PA_INDICATOR_SOURCE: 'pa_cubes',
PA_INDICATORS: 'pa_indicators',
SYS_DICTIONARY: 'sys_dictionary',
SYS_DB_VIEW: 'sys_db_view',
SYS_DB_VIEW_TABLE: 'sys_db_view_table',
AUTOMATED_BREAKDOWN: '1',
EXTERNAL_BREAKDOWN: '3',
FIELD_LABEL_SEPARATOR: ' . ',
getUnMappedBreakdowns: function(){
var indicatorSyId = this.getParameter('sysparm_indicator');
var keyword = this.getParameter('sysparm_search');
var page = this.getParameter('sysparm_page') || 0;
var pageSize = this.getParameter('sysparm_page_size') || 0;
var breakdowns = this._getUnMappedBreakdowns(indicatorSyId, keyword, page, pageSize);
return breakdowns;
},
getEnabledBreakdowns: function(){
var indicatorSyId = this.getParameter('sysparm_indicator');
var keyword = this.getParameter('sysparm_search');
var breakdowns = this._getEnabledBreakdowns(indicatorSyId, keyword);
return breakdowns;
},
_getUnMappedBreakdowns: function(indicatorSysId, keyword, page, pageSize){
var breakdowns = [];
var mappedBreakdowns = this._getBreakdownsForIndicator(indicatorSysId);
var indicator = this._getIndicator(indicatorSysId);
var grBreakdowns = new GlideRecordSecure(this.PA_BREAKDOWNS);
grBreakdowns.addActiveQuery();
grBreakdowns.addQuery("type", "!=", this.EXTERNAL_BREAKDOWN);
grBreakdowns.addQuery("sys_id","NOT IN",mappedBreakdowns.sysIds);
if(keyword)
grBreakdowns.addQuery("name","CONTAINS",keyword);
var lastRow = 0;
var startRow = 0;
if(page > 0 && pageSize > 0) {
lastRow = page * pageSize;
startRow = lastRow - pageSize;
grBreakdowns.chooseWindow(startRow, lastRow, true);
}
grBreakdowns.orderBy('name');
grBreakdowns.query();
var isLastPage = false;
var rowCount = grBreakdowns.getRowCount();
if(page > 0 && pageSize > 0) {
if(rowCount <= lastRow)
isLastPage = true;
}
while(grBreakdowns.next()){
var breakdownMappings = this._getIndicatorBreakdownMappings(indicator,grBreakdowns.getValue("sys_id"));
var hasBreakdownMapping = grBreakdowns.getValue("type") != this.AUTOMATED_BREAKDOWN || this._hasBreakdownMapping(breakdownMappings);
breakdowns.push({
name: grBreakdowns.getValue("name"),
sysId:grBreakdowns.getValue("sys_id"),
type:grBreakdowns.getValue("type"),
breakdownSource: this._getBreakdownSource(grBreakdowns.getValue("dimension")),
breakdownMappings:breakdownMappings,
hasBreakdownMapping: hasBreakdownMapping,
isLinkedToIndicator: false,
indicatorType: indicator.type
});
}
var breakdownData = {
breakdowns: breakdowns,
isLastBreakdown: isLastPage,
count: rowCount,
};
return JSON.stringify({indicator:indicator, breakdowns: breakdownData});
},
_getEnabledBreakdowns: function(indicatorSysId, keyword){
var breakdowns = [];
var mappedBreakdowns = this._getBreakdownsForIndicator(indicatorSysId);
var indicator = this._getIndicator(indicatorSysId);
var grBreakdowns = new GlideRecordSecure(this.PA_BREAKDOWNS);
grBreakdowns.addActiveQuery();
grBreakdowns.addQuery("type", "!=", this.EXTERNAL_BREAKDOWN);
grBreakdowns.addQuery("sys_id","IN",mappedBreakdowns.sysIds);
if(keyword)
grBreakdowns.addQuery("name","CONTAINS",keyword);
grBreakdowns.orderBy('name');
grBreakdowns.query();
while(grBreakdowns.next()){
var breakdownMappings = this._getIndicatorBreakdownMappings(indicator,grBreakdowns.getValue("sys_id"));
breakdowns.push({
name: grBreakdowns.getValue("name"),
sysId:grBreakdowns.getValue("sys_id"),
type:grBreakdowns.getValue("type"),
breakdownSource: this._getBreakdownSource(grBreakdowns.getValue("dimension")),
breakdownMappings:breakdownMappings,
hasBreakdownMapping: grBreakdowns.getValue("type") != this.AUTOMATED_BREAKDOWN || this._hasBreakdownMapping(breakdownMappings),
isLinkedToIndicator: true,
breakdownIndicatorLink: this._getBreakdownsLinkForIndicator(indicatorSysId,grBreakdowns.getValue("sys_id")),
indicatorType: indicator.type
});
}
return JSON.stringify({indicator:indicator, breakdowns: breakdowns});
},
_getBreakdownsForIndicator:function(indicatorSysId){
var mappedBreakdowns = {
sysIds: [],
records: [],
};
var grIBDM = new GlideRecordSecure(this.PA_INDICATION_BREAKDOWN_MAPPING);
grIBDM.addActiveQuery();
grIBDM.addQuery("indicator","=",indicatorSysId);
grIBDM.query();
while(grIBDM.next()){
mappedBreakdowns.sysIds.push(grIBDM.getValue("breakdown"));
mappedBreakdowns.records.push({
indicatorSysId: grIBDM.getValue("indicator"),
breakdownSysId: grIBDM.getValue("breakdown"),
sysId: grIBDM.getValue("sys_id")
});
}
return mappedBreakdowns;
},
_getBreakdownsLinkForIndicator:function(indicatorSysId, breakdownSysId){
var linkedBreakdownIndicatorDetails = {};
var grIBDM = new GlideRecordSecure(this.PA_INDICATION_BREAKDOWN_MAPPING);
grIBDM.addActiveQuery();
grIBDM.addQuery("indicator","=",indicatorSysId);
grIBDM.addQuery("breakdown","=",breakdownSysId);
grIBDM.query();
while(grIBDM.next()){
linkedBreakdownIndicatorDetails = {
indicatorSysId: grIBDM.getValue("indicator"),
breakdownSysId: grIBDM.getValue("breakdown"),
sysId: grIBDM.getValue("sys_id")
};
}
return linkedBreakdownIndicatorDetails;
},
_getBreakdownSource: function(breakdownSysId){
var breakdownSource = {};
var grBS = new GlideRecordSecure(this.PA_BREAKDOWN_SOURCE);
grBS.get(breakdownSysId);
grBS.query();
if (grBS.isValidRecord()) {
breakdownSource = {
sysId: grBS.getValue("sys_id"),
name: grBS.getValue("name"),
factsTable: grBS.getValue("facts_table"),
field: grBS.getValue("field"),
conditions: grBS.getValue("conditions"),
recordCount: this._getTableRowCount(grBS.getValue("facts_table"),grBS.getValue("conditions")),
};
}
return breakdownSource;
},
_getIndicator: function(indicatorSysId){
var indicator = {};
var gr = new GlideRecordSecure(this.PA_INDICATORS);
gr.get(indicatorSysId);
gr.query();
if (gr.isValidRecord()) {
indicator = {
sysId: gr.getValue("sys_id"),
name: gr.getValue("name"),
isScripted: gr.getValue("scripted"),
script: gr.getValue("script"),
conditions: gr.getValue("conditions"),
cube: gr.getValue("cube"),
formula: gr.getValue("formula"),
type: gr.getValue("type"),
indicatorSource: this._getIndicatorSource(gr.getValue("cube"))
};
}
return indicator;
},
_getIndicatorSource: function(cubeId){
var indicatorSource = {};
if(cubeId){
var gr = new GlideRecordSecure(this.PA_INDICATOR_SOURCE);
gr.get(cubeId);
gr.query();
if (gr.isValidRecord()) {
indicatorSource = {
sysId: gr.getValue("sys_id"),
name: gr.getValue("name"),
factsTable: gr.getValue("facts_table"),
conditions: gr.getValue("conditions"),
};
}
}
return indicatorSource;
},
_getIndicatorBreakdownMappings: function(indicator,breakdownSysId){
var indicatorBreakdownMappings = [];
var FORMULA_INDICATOR = "2";
if(indicator.cube && indicator.cube.length >0){
indicatorBreakdownMappings = [this._getBreakdownMappingDetails(indicator, breakdownSysId)];
}
if(indicator.type == FORMULA_INDICATOR && indicator.formula && indicator.formula.trim().length > 0) {
var formulaIndicatorSysIds = this._getFormulaIndicatorSysId(indicator.formula);
indicatorBreakdownMappings = [];
for(var i =0 ; i < formulaIndicatorSysIds.length ;i ++){
var formulaIndicator = this._getIndicator(formulaIndicatorSysIds[i]);
var breakdownMappingDetails = this._getBreakdownMappingDetails(formulaIndicator,breakdownSysId);
indicatorBreakdownMappings.push(breakdownMappingDetails);
}
}
return indicatorBreakdownMappings;
},
_getBreakdownMappingDetails : function(indicator, breakdownSysId){
var indicatorBreakdownMapping = {};
var gr = new GlideRecordSecure(this.PA_BREAKDOWN_MAPPING);
gr.addActiveQuery();
gr.addQuery('breakdown','=',breakdownSysId);
var tables = this._getHierarchyTables(indicator.indicatorSource.factsTable);
gr.addQuery('facts_table',tables);
gr.query();
indicatorBreakdownMapping.hasMapping = gr.getRowCount() > 0 ;
while (gr.next()) {
indicatorBreakdownMapping.factsTable = gr.getValue("facts_table");
var field = gr.getValue("field");
if(field) {
indicatorBreakdownMapping.fieldLabel = this._getFieldLabel(field.split('.'), gr.getValue("facts_table"), []);
}
indicatorBreakdownMapping.field = gr.getValue("field");
indicatorBreakdownMapping.isScripted = gr.getValue("scripted") === "1";
indicatorBreakdownMapping.script = gr.getValue("script");
indicatorBreakdownMapping.scriptName = gr.getDisplayValue("script");
}
indicatorBreakdownMapping.indicator = indicator;
return indicatorBreakdownMapping;
},
_getHierarchyTables: function(tableName) {
if(tableName)
return GlideDBObjectManager.getTables(tableName);
else
return [];
},
_getFormulaIndicatorSysId: function(formula){
var regPattern = /[0-9a-f]{8}[0-9a-f]{4}[0-9a-f]{4}[0-9a-f]{4}[0-9a-f]{12}/g;
return formula.match(regPattern);
},
_getTableRowCount: function(table,encodedQuery){
var count = new GlideAggregate(table);
if(encodedQuery)
count.addEncodedQuery(encodedQuery);
count.addAggregate('COUNT');
count.query();
var recCount = 0;
if (count.next()) {
recCount = count.getAggregate('COUNT');
}
return recCount;
},
_hasBreakdownMapping: function(breakdownMappings){
var hasBreakdownMappingDone = false;
for(var i = 0; i< breakdownMappings.length ; i++){
if(breakdownMappings[i].hasMapping){
hasBreakdownMappingDone = true;
break;
}
}
return hasBreakdownMappingDone;
},
_getTableNameFromView: function(field, viewSysID) {
var prefixMap = {};
var response = [];
var grViewTable = new GlideRecord(this.SYS_DB_VIEW_TABLE);
grViewTable.addQuery('view', viewSysID);
grViewTable.query();
while(grViewTable.next()) {
var prefix = grViewTable.getValue('variable_prefix');
var tableName = grViewTable.getValue('table');
prefixMap[prefix] = tableName;
}
var varName = field.substr(field.indexOf('_') + 1);
var varPrefix = field.split('_')[0];
response.push(varName);
response.push(prefixMap[varPrefix]);
return response;
},
_getFieldLabel: function(fields, table, labels) {
var grIsView = new GlideRecord(table);
if(grIsView.isView()) {
var grView = new GlideRecord(this.SYS_DB_VIEW);
grView.get('name',table);
var viewSysID = grView.getUniqueValue();
var fieldTable = this._getTableNameFromView(fields[0], viewSysID);
fields[0] = fieldTable[0];
table = fieldTable[1];
}
var labelArray = this._getFieldLabelArray(fields, table, labels) || [];
return labelArray.join(this.FIELD_LABEL_SEPARATOR);
},
_getFieldLabelArray: function(fields, table, labels) {
if(fields.length === 0 || !table) return;
var gr = new GlideRecord(this.SYS_DICTIONARY);
var compositeElement = new GlideCompositeElement(fields[0], table);
table = compositeElement.getTargetED().getTableName();
gr.addQuery('name', table);
gr.addQuery('element', fields[0]);
gr.query();
if(gr.next()) {
var type = gr.getValue('internal_type');
if(type == 'reference') {
fields = fields.splice(1,fields.length);
table = gr.getValue('reference');
labels.push(gr.getValue('column_label'));
this._getFieldLabelArray(fields,table,labels);
} else
labels.push(gr.getValue('column_label'));
}
return labels;
},
type: 'PAIndicatorBreakdownUtil'
});
Sys ID
ffa8e6d1c7201300bde4435eaa9763b6