Name

sn_customerservice.CaseReportUtils

Description

Script for case column migrations.

Script

var CaseReportUtils = Class.create();
CaseReportUtils.prototype = {
  initialize: function() {
  },
  process: function(caseId, calculateFCR, calculateReopen, calculateReassignment) {
  	// if caseId is null, all the case records will be processed as a migration process
  	if(calculateFCR || calculateReopen || calculateReassignment){
  		var caseList = new GlideRecord('sn_customerservice_case');
  		if(!gs.nil(caseId)){
  			caseList.addQuery('sys_id', caseId);
  		}else{
  			caseList.addQuery('case_report', 'NULL');
  		}
  		caseList.query();
  		while(caseList.next()){
  			var current = caseList;
  			var caseReport = new GlideRecord('sn_customerservice_case_report');
  			caseReport.addQuery("case", current.sys_id);
  			caseReport.query();
  			if(!caseReport.next()){
  				caseReport.initialize();
  				caseReport.setValue("case", current.sys_id);
  				caseReport.insert();
  				current.case_report = caseReport.sys_id;
  				current.setWorkflow(false);
  				current.autoSysFields(false);
  				current.update();
  			}
  			
  			var historySet = new GlideRecord('sys_history_set');
  			historySet.addQuery('id', current.sys_id);
  			historySet.query();
  			historySet.next();
  			
  			//For reopen_count field update -- START
  			if(calculateReopen){
  				caseReport.reopen_count = this.caseReopen(current, caseReport, historySet);
  			}
  			//For reopen_count field update -- END
  			
  			//For agent_reassignment_count field update -- START
  			if(calculateReassignment){
  				caseReport.agent_reassignment_count = this.caseAgentReassignment(current, caseReport, historySet);
  			}
  			//For agent_reassignment_count field update -- END
  			
  			//For is_fcr field update -- START
  			if(calculateFCR && (caseReport.is_fcr!=2 || gs.nil(caseId))){
  				caseReport.is_fcr = this.caseFCR(current, caseReport, historySet);
  			}
  			//For is_fcr field update -- END
  			
  			caseReport.update();
  		}
  		if(gs.nil(caseId))
  			gs.info('Case column migration completed', 'CaseReportUtils');
  	}else{
  		if(gs.nil(caseId))
  			gs.info('None of the flag is true to run the migration for case', 'CaseReportUtils');
  	}
  	
  },
  
  caseAgentReassignment : function(current, caseReport, historySet){
  	var reassignment = 0;
  	if(historySet.isValid()){
  		var history = new GlideRecord('sys_history_line');
  		history.addQuery('set', historySet.sys_id);
  		history.addQuery('field', 'assigned_to');
  		history.orderBy('sys_created_on');
  		history.query();
  		while(history.next()){
  			reassignment++;
  		}
  		if(reassignment>0){
  			reassignment--;
  		}
  	}
  	return reassignment;
  },
  
  caseReopen : function(current, caseReport, historySet){
  	var reopen = 0;
  	if(historySet.isValid()){
  		var history = new GlideRecord('sys_history_line');
  		history.addQuery('set', historySet.sys_id);
  		history.addQuery('field', 'state');
  		var qc2 = history.addQuery('old', 'Resolved');
  		qc2.addOrCondition('old', 'Closed');
  		history.addQuery('new', 'Open');
  		history.orderBy('sys_created_on');
  		history.query();
  		while(history.next()){
  			reopen += 1;
  		}
  	}
  	return reopen;
  },
  
  caseFCR : function(current, caseReport, historySet){
  	var resolvedDate = '';
  	if(caseReport.reopen_count>0 || current.state == 18){
  		return 2;
  	}
  	if(historySet.isValid()){
  		var history = new GlideRecord('sys_history_line');
  		history.addQuery('set', historySet.sys_id);
  		history.addQuery('label', 'State');
  		history.addQuery('new', 'Awaiting Info');
  		history.query();
  		if(history.next()){
  			return 2;
  		}
  		if(this.caseReopen(current, caseReport, historySet)>0){
  			return 2;
  		}
  		if(!gs.nil(current.resolved_at)){
  			resolvedDate = current.resolved_at;
  		}else {
  			history = new GlideRecord('sys_history_line');
  			history.addQuery('set', historySet.sys_id);
  			var qc2 = history.addQuery('new', 'Resolved');
  			qc2.addOrCondition('new', 'Closed');
  			history.orderByDesc('sys_created_on');
  			history.query();
  			if(history.next()){
  				resolvedDate = history.update_time;
  			}
  		}
  	}
  	var phoneLog = new GlideRecord('sn_openframe_phone_log');
  	if(phoneLog.isValid()){
  		phoneLog.addQuery('task', current.sys_id);
  		phoneLog.addQuery("sys_created_on", '<=', resolvedDate);
  		phoneLog.query();
  		if(phoneLog.next()){
  			return 2;
  		}
  	}
  	var appLog = new GlideRecord('sn_customerservice_appointment');
  	if(appLog.isValid()){
  		appLog.addQuery('task', current.sys_id);
  		appLog.addQuery("sys_created_on", '<=', resolvedDate);
  		appLog.query();
  		if(appLog.next()){
  			return 2;
  		}
  	}
  	var emailLog = new GlideRecord('sys_email');
  	if(emailLog.isValid()){
  		emailLog.addQuery('instance', current.sys_id);
  		emailLog.addQuery('type', 'sent');
  		emailLog.addQuery('sys_created_by', '!=', 'system');
  		emailLog.addQuery('sys_created_on', '<=', resolvedDate);
  		emailLog.query();
  		if(emailLog.next()){
  			return 2;
  		}
  	}
  	var workOrder = new GlideRecord('wm_order');
  	if(workOrder.isValid()){
  		workOrder.addQuery('initiated_from', current.sys_id);
  		workOrder.addQuery('state', '!=', 7);
  		workOrder.addQuery('sys_created_on', '<=', resolvedDate);
  		workOrder.query();
  		if(workOrder.next()){
  			return 2;
  		}
  	}
  	if(current.state == 6 || current.state == 3){
  		return 1;
  	}
  	
  	return 0;
  },
  type: 'CaseReportUtils'
};

Sys ID

f2c66f763b640300c68fe9b534efc42e

Offical Documentation

Official Docs: