Name
sn_risk_advanced.QueryFactorUtilsBase
Description
No description available
Script
var QueryFactorUtilsBase = Class.create();
QueryFactorUtilsBase.prototype = Object.extendsObject(AutomatedFactorUtils, {
initialize: function() {},
//Executes factor for given response and returns score
executeAndGetFactorResponse: function(assessmentResponse) {
return this._executeAndGetFactorResponse(assessmentResponse);
},
//Executes given factor and updates its score for all applicable responses
executeAndSetResponsesForFactor: function(factor) {
this._executeAndSetResponsesForFactor(factor);
},
//Executes given factor and updates its score for all applicable responses with given filters
executeFactorFilterResponses: function(factor, entityID, riskID, controlID, objectID) {
this._executeFactorFilterResponses(factor, entityID, riskID, controlID, objectID);
},
createSupportingData: function(responseIds) {
this._createSupportingData(responseIds);
},
_executeAndGetFactorResponse: function(assessmentResponse) {
var factor = assessmentResponse.getElement('factor').getRefRecord();
return this._executeQueryFactor(factor, this._getFilterData(assessmentResponse));
},
_getFilterData: function(assessmentResponse) {
var filterData = {};
filterData.entityId = assessmentResponse.assessment_instance_id.entity_1;
filterData.riskId = assessmentResponse.assessment_instance_id.risk;
filterData.controlId = assessmentResponse.control;
filterData.sourceRecordId = assessmentResponse.assessment_instance_id.source_record;
return filterData;
},
_executeAndSetResponsesForFactor: function(factor) {
var automatedFactorResponse;
if (this._isFactorHavingReferenceFilter(factor)) {
automatedFactorResponse = new GlideAggregate('sn_risk_advanced_risk_assessment_instance_response');
automatedFactorResponse.addQuery('factor', factor.getUniqueValue());
automatedFactorResponse.addEncodedQuery('assessment_instance_id.stateNOT IN0,1,8');
if (factor.getValue('factor_usage') == '1') {
if (factor.getValue('filter_by_entity') == '1')
automatedFactorResponse.groupBy('assessment_instance_id.entity_1');
if (factor.getValue('filter_by_risk') == '1')
automatedFactorResponse.groupBy('assessment_instance_id.risk');
if (factor.getValue('filter_by_control') == '1')
automatedFactorResponse.groupBy('control');
} else {
if (factor.getValue('filter_by_source') == '1')
automatedFactorResponse.groupBy('assessment_instance_id.source_record');
}
automatedFactorResponse.query();
while (automatedFactorResponse.next()) {
var filterData = {};
filterData.entityId = automatedFactorResponse.getValue('assessment_instance_id.entity_1');
filterData.riskId = automatedFactorResponse.getValue('assessment_instance_id.risk');
filterData.controlId = automatedFactorResponse.getValue('control');
filterData.sourceRecordId = automatedFactorResponse.getValue('assessment_instance_id.source_record');
this._executeAndUpdateResponses(factor, filterData);
}
} else {
this._executeAndUpdateResponses(factor, null);
}
},
_executeAndUpdateResponses: function(factor, filterData) {
var result = {};
try {
result = this._executeQueryFactor(factor, filterData);
} catch (ex) {
this._setErrorFromException(result, ex, factor);
}
result.sourceRecordId = factor.getUniqueValue();
result.table = 'sn_risk_advanced_automated_query_factor';
if (result.error) {
this._createException(result);
return;
}
this._markExistingExceptionAsResolved(result);
this._updateResponses(factor, filterData, result);
},
_executeFactorFilterResponses: function(factor, entityID, riskID, controlID, objectID) {
if (!entityID && !riskID && !controlID && !objectID) {
this._executeAndSetResponsesForFactor(factor);
} else {
var filterData = {};
filterData.entityId = entityID;
filterData.riskId = riskID;
if (new sn_risk_advanced.RiskUtilities().isComplianceInstalled())
filterData.controlId = controlID;
filterData.sourceRecordId = objectID;
this._executeAndUpdateResponses(factor, filterData);
}
},
_isFactorHavingReferenceFilter: function(factor) {
if (factor.getValue('factor_usage') == '1')
return factor.filter_by_entity == '1' || factor.filter_by_risk == '1' || factor.filter_by_control == '1';
else
return factor.filter_by_source == '1';
},
_executeQueryFactor: function(factor, filterData) {
var factorConfigurationError = this._getErrorsForFactor(factor);
if (!gs.nil(factorConfigurationError.error))
throw factorConfigurationError;
var tableOrView = factor.table_name;
var result = {};
result.score = 0;
var gr = new GlideAggregate(tableOrView);
this._buildQuery(factor, gr, filterData);
if (factor.aggregation_type == 'COUNT_DIST') {
gr.groupBy(factor.aggregation_column);
gr.query();
while (gr.next())
result.score++;
} else {
gr.addAggregate(factor.aggregation_type, factor.aggregation_column);
gr.setGroup(false);
gr.query();
if (gr.next()) {
result.score = gr.getAggregate(factor.aggregation_type, factor.aggregation_column);
this._setError(result, factor);
if (gs.nil(result.error) && factor.user_response == '4')
result.score = this._getReferenceCurrencyCode() + ";" + result.score;
}
}
return result;
},
_getErrorsForFactor: function(factor) {
var result = {};
var tableOrView = factor.getValue('table_name');
var aggregationColumn = factor.getValue('aggregation_column');
var gr = new GlideRecord(tableOrView);
gr.initialize();
if (!gr.isValid()) {
if (gs.nil(tableOrView))
result.error = gs.getMessage('Table or view is empty in the automated factor as it might have been deleted.');
else
result.error = gs.getMessage('Table or view ({0}) selected in the automated factor is invalid.', [tableOrView]);
result.resolution = gs.getMessage('Select a valid table or view.');
} else if (factor.getValue('aggregation_type') != 'COUNT' && !gr.isValidField(aggregationColumn)) {
if (gs.nil(aggregationColumn))
result.error = gs.getMessage('Aggregation column is empty in the automated factor as it might have been deleted.');
else
result.error = gs.getMessage('Aggregation column ({0}) selected in the automated factor is invalid.', [aggregationColumn]);
result.resolution = gs.getMessage('Select a valid column');
}
return result;
},
_buildQuery: function(factor, gr, filterData) {
if (filterData) {
if (factor.getValue('factor_usage') == '1') {
if (factor.getValue('filter_by_risk') == '1')
gr.addQuery(factor.risk_column, filterData.riskId);
if (factor.getValue('filter_by_entity') == '1') {
var entityColumn = factor.getValue('entity_column');
var table = factor.getValue('table_name');
if ((entityColumn == 'sys_id' && table == 'sn_grc_profile') || (this._getReferenceTable(table, entityColumn) == 'sn_grc_profile'))
gr.addQuery(entityColumn, filterData.entityId);
else {
var entityRecord = new GlideRecord('sn_grc_profile');
entityRecord.get(filterData.entityId);
if (entityRecord.getValue('refers_to_existing_table') == '1')
gr.addQuery(entityColumn, entityRecord.getValue('applies_to'));
else
gr.addQuery(entityColumn, filterData.entityId);
}
}
if (factor.getValue('filter_by_control') == '1')
gr.addQuery(factor.control_column, filterData.controlId);
} else {
if (factor.getValue('filter_by_source') == '1')
gr.addQuery(factor.source_record_column, filterData.sourceRecordId);
}
}
gr.addEncodedQuery(factor.condition);
},
_getReferenceTable: function(tableName, referenceField) {
var record = new GlideRecord(tableName);
record.initialize();
var element = record.getElement(referenceField);
var type = element.getED().getInternalType();
if (type == 'reference')
return element.getReferenceTable();
else
return '';
},
_updateResponses: function(factor, filterData, result) {
var response = this._getResponsesToBeAssessed(factor);
if (filterData) {
if (factor.getValue('factor_usage') == '1') {
if (filterData.entityId)
response.addQuery('assessment_instance_id.entity_1', filterData.entityId);
if (filterData.riskId)
response.addQuery('assessment_instance_id.risk', filterData.riskId);
if (filterData.controlId)
response.addQuery('control', filterData.controlId);
} else {
if (filterData.sourceRecordId)
response.addQuery('assessment_instance_id.source_record', filterData.sourceRecordId);
}
}
response.query();
var responseIds = [];
while (response.next()) {
if (!this.isFactorOptedOut(response)) {
response.setValue('factor_response', result.score);
response.update();
responseIds.push(response.getUniqueValue());
}
}
if (responseIds.length != 0 && factor.getValue('supporting_data') == 1 && result.score != '')
this._createBulkSupportingData(responseIds, factor, filterData);
},
_createSupportingData: function(responseIds) {
for (var i = 0; i < responseIds.length; i++) {
var response = new GlideRecord('sn_risk_advanced_risk_assessment_instance_response');
if (response.get(responseIds[i])) {
var factor = response.getElement('factor').getRefRecord();
if (factor.getValue('supporting_data') == 1 && response.getValue('factor_response') != '') {
var filterData = this._getFilterData(response);
this._createAttachments(factor, filterData, response, null);
}
}
}
},
_createBulkSupportingData: function(responseIds, factor, filterData) {
this._deleteAttachments('sn_risk_advanced_risk_assessment_instance_response', responseIds);
this._createAttachments(factor, filterData, null, responseIds);
},
_createAttachments: function(factor, filterData, response, responseIds) {
var supportingFields = factor.getValue('supporting_data_fields').split(',');
var gr = new GlideRecord(factor.table_name);
gr.initialize();
var header = "";
for (var i = 0; i < supportingFields.length; i++) {
header = header + '"' + gr.getElement(supportingFields[i]).getLabel() + '"';
if (i != supportingFields.length - 1)
header = header + ",";
}
header = header + "\r\n";
this._buildQuery(factor, gr, filterData);
gr.query();
var batchSize = 10000;
var attachmentCount = 1;
var tableLabel = gr.getClassDisplayValue();
var fileName = "";
if (gr.hasNext()) {
while (gr.hasNext()) {
var counter = 0;
data = header;
while (counter < batchSize && gr.next()) {
for (var j = 0; j < supportingFields.length; j++) {
var value = gr.getElement(supportingFields[j]).getDisplayValue();
if (value.indexOf(',') != -1 || value.indexOf('"') != -1) {
value = value.replace(/"/g, '""');
data = data + '"' + value + '"';
} else {
data = data + value;
}
if (j != supportingFields.length - 1)
data = data + ",";
}
data = data + "\r\n";
counter++;
}
fileName = tableLabel + attachmentCount + ".csv";
if (response)
this._createCSVAttachment(response, data, fileName);
else
this._createBulkCSVAttachments(responseIds, data, fileName);
attachmentCount++;
}
} else {
//If there is no data create an empty attachment with header
fileName = tableLabel + ".csv";
var data = header + "Supporting data is not available for collection\r\n";
if (response)
this._createCSVAttachment(response, data, fileName);
else
this._createBulkCSVAttachments(responseIds, data, fileName);
}
},
_createBulkCSVAttachments: function(responseIds, data, fileName) {
for (var i = 0; i < responseIds.length; i++) {
var response = new GlideRecord('sn_risk_advanced_risk_assessment_instance_response');
response.get(responseIds[i]);
var attachment = new GlideSysAttachment();
attachment.write(response, fileName, 'application/csv', data);
}
},
type: 'QueryFactorUtilsBase'
});Sys ID
8e3133b2775300102a272f589a10611b