Name

global.SurveyReportingScript

Description

Includes scripts that help in generating survey reports using database view.

Script

var SurveyReportingScript = Class.create();
SurveyReportingScript.prototype = Object.extendsObject(AbstractAjaxProcessor, {
  
  hideSurveyResultsAction: function(metricId) {
  	// increasing these might impact performance
  	var INSTANCE_LIMIT = 100000; 
  	var METRICS_LIMIT = 5;
  	
  	var metricGr = new GlideRecord('asmt_metric');
  	metricGr.addQuery('metric_type', metricId);
  	metricGr.addActiveQuery();
  	metricGr.query();
  	if (metricGr.getRowCount() > METRICS_LIMIT) return true;
  	
  	
  	var gr = new GlideRecord('asmt_assessment_instance');
  	gr.addQuery('metric_type', metricId);
  	gr.query();
  	return gr.getRowCount() > INSTANCE_LIMIT;
  },
  
  generateViewFields: function(viewTable, fields) {
  	fields.forEach(function(field) {
  		var gr = new GlideRecord('sys_db_view_table_field');
  		gr.initialize();
  		gr.view_table = viewTable.getUniqueValue();
  		gr.table = viewTable.table;
  		gr.field = field;
  		gr.insert();
  	});
  },
  
  generateResultsView : function() {
  	var metricId = this.getParameter('sysparm_metric_id');
  	this.generateResults(metricId);
  	return true;
  },
  
  generateResults: function(metricTypeId) {
  	if (!metricTypeId || !(new AssessmentUtils().canViewScoreCardPage(metricTypeId))) return;
  	var LIST_LIMIT = 8;
  	var order = 0;
  	
  	var gr = new GlideRecord('asmt_metric_type');
  	var dbName = 'u_metric_' + metricTypeId + '_result';
  	if (gr.get(metricTypeId)) {
  		var survey = gr.name + '';
  		var listElems = [];
  		
  		var dbView = new GlideRecord('sys_db_view');
  		dbView.addQuery('name', dbName);
  		dbView.query();
  		if (dbView.next())
  			return; //dont create if dbview already exists
  		
  		dbView.initialize();
  		dbView.name = dbName;
  		dbView.label = gs.getMessage('{0} Results', [survey]);
  		dbView.insert();
  		
  		//instance Table
  		var dbTable = new GlideRecord('sys_db_view_table');
  		dbTable.initialize();
  		dbTable.table = 'asmt_assessment_instance';
  		dbTable.variable_prefix = 'inst';
  		dbTable.order = order = order + 100;
  		dbTable.view = dbView.getUniqueValue();
  		dbTable.where_clause = "inst_metric_type='" + metricTypeId + "'" + " && inst_state = 'complete'";
  		dbTable.insert();
  		
  		//instance table Fields
  		this.generateViewFields(dbTable, ['number', 'taken_on', 'due_date', 'user', 'metric_type', 'expiration_date', 'task_id', 'sys_created_on','state']);
  		
  		//add instance fields to list view
  		listElems.push('inst_number');
  		listElems.push('inst_user');
  		listElems.push('inst_task_id');
  		
  		//Add user table to DB view
  		dbTable = new GlideRecord('sys_db_view_table');
  		dbTable.initialize();
  		dbTable.table = 'sys_user';
  		dbTable.variable_prefix = 'us';
  		dbTable.order = order = order + 100;
  		dbTable.view = dbView.getUniqueValue();
  		dbTable.where_clause = "us_sys_id=inst_user";
  		dbTable.insert();
  		this.generateViewFields(dbTable, ['sys_id']);
  		
  		var questions = new GlideRecord('asmt_metric');
  		questions.addActiveQuery();
  		questions.addQuery('metric_type', metricTypeId);
  		questions.orderBy('order');
  		questions.query();
  		var i = 0;
  		while (questions.next()) {
  			i++;
  			var varPrefix = 'res' + i;
  			dbTable = new GlideRecord('sys_db_view_table');
  			dbTable.initialize();
  			dbTable.table = 'asmt_metric_result';
  			dbTable.variable_prefix = varPrefix;
  			dbTable.where_clause = "inst_sys_id=" + varPrefix + "_instance && "
  									+ varPrefix + "_metric='" + questions.getUniqueValue() + "'";
  			dbTable.left_join = true;
  			dbTable.order = order = order + 100;
  			dbTable.view = dbView.getUniqueValue();
  			dbTable.insert();
  			//add fields
  			this.generateViewFields(dbTable, ['instance', 'metric', 'source_id', 'source_table', 'string_value']);
  			//add label
  			var doc = new GlideRecord('sys_documentation');
  			doc.initialize();
  			doc.name = dbName;
  			doc.label = questions.question.getDisplayValue();
  			doc.element = varPrefix + "_string_value";
  			doc.language = 'en';
  			doc.insert();
  			
  			listElems.push(varPrefix + "_string_value");
  		}
  		
  		//Update UI List
  		var uiList = new GlideRecord('sys_ui_list');
  		uiList.addQuery('name', dbName);
  		uiList.addQuery('view', 'Default view');
  		uiList.query();
  		if (uiList.next()) {
  			var listElement = new GlideRecord('sys_ui_list_element');
  			listElement.addQuery('list_id', uiList.getUniqueValue());
  			listElement.deleteMultiple();
  		} else {
  			uiList.initialize();
  			uiList.name = dbName;
  			uiList.view = 'Default view';
  			uiList.insert();
  		}
  		
  		for (var i=0; i < listElems.length && i < LIST_LIMIT; i++) {
  			var elem = listElems[i];
  			var listElement = new GlideRecord('sys_ui_list_element');
  			listElement.initialize();
  			listElement.list_id = uiList.getUniqueValue();
  			listElement.position = i;
  			listElement.element = elem;
  			listElement.insert();
  		}
  		
  		var listControl = new GlideRecord('sys_ui_list_control');
  		listControl.initialize();
  		listControl.name = dbName;
  		listControl.omit_new_button = true;
  		listControl.omit_count = true;
  		listControl.insert();
  	}
  },
  type: 'SurveyReportingScript'
});

Sys ID

5beb911dc7210110e028dc8703c260d8

Offical Documentation

Official Docs: