Name
sn_hr_core.hr_Criteria
Description
Util for evaluating HR criteria and underlying conditions
Script
var hr_Criteria = Class.create();
hr_Criteria.prototype = {
type: 'hr_Criteria',
initialize: function() {
this.userTables = new GlideTableHierarchy("sys_user").getAllExtensions();
this.optimizeCriteria = gs.getProperty('sn_hr_core.hr_criteria.optimize', 'false') === 'true';
this.hrCriteriaCacheName = 'HRCriteria'; // this is OOB data in sys_scoped_cache table
},
/* Return the count of users meeting @param hrCriteriaId conditions
*
* @param hrCriteriaId String sys_id of an HR Criteria
* @return number Count of the users that meet @param hrCriteriaId conditions
*/
getUserCountForHRCriteria: function(hrCriteriaId) {
var userList = this.getUsersForHRCriteria(hrCriteriaId, true);
return userList.length;
},
/* Return a list of users meeting any @param hrCriteriaId
*
* @param hrCriteriaId String Comma separated list of HR Criteria sys_id's
* @param isCount [boolean] (optional) Whether this API is being used to get a count
* @return Array List of sys_id's of Users that meet @param hrCriteriaId's conditions
*/
getUsersForHRCriteria: function(hrCriteriaId, isCount) {
var userList = [];
var finalUserMap = {};
// Get the maximum number of aggregates allowed, and disable optimization if below 50000
var maxAggregates = parseInt(gs.getProperty('glide.db.max.aggregates', 100000));
if (!maxAggregates || isNaN(maxAggregates) || maxAggregates < 50000)
this.optimizeCriteria = false;
// Ensure criteria is active and exists (table is only required if there is a user_column)
var criteriaGr = new GlideRecord("sn_hr_core_criteria");
criteriaGr.addQuery("sys_id", "IN", hrCriteriaId);
criteriaGr.addActiveQuery();
criteriaGr.query();
while (criteriaGr.next()) {
// Ensure that if there is a user_column then it and the table are valid
var criteriaTable = criteriaGr.getValue('table');
var criteriaUserColumn = criteriaGr.getValue('user_column');
var criteriaTableGr;
if (criteriaTable) {
criteriaTableGr = new GlideRecord(criteriaTable);
if (criteriaUserColumn && (!criteriaTableGr.isValid() || !criteriaTableGr.isValidField(criteriaUserColumn)))
continue;
}
// Build object of all conditions for given criteria, e.g., {'TABLE_NAME': { 'USER_COLUMN': ['condition', ...]}
var conditions = {};
var conditionGr = new GlideRecord('sn_hr_core_condition');
conditionGr.addActiveQuery();
conditionGr.addNotNullQuery('table');
conditionGr.addNotNullQuery('condition');
var m2mQc = conditionGr.addJoinQuery('sn_hr_core_m2m_condition_criteria', 'sys_id', 'hr_condition');
m2mQc.addCondition('hr_criteria', criteriaGr.getUniqueValue());
m2mQc.addCondition('hr_criteria.active', true);
conditionGr.query();
var queryCount = 0;
var invalidCondition = false;
while (conditionGr.next()) {
var conditionTable = conditionGr.getValue('table');
if (!conditions.hasOwnProperty(conditionTable))
conditions[conditionTable] = {};
// Determine user column to use
var userColumn;
if (!gs.nil(conditionGr.user_column))
userColumn = conditionGr.getElement('user_column').toString();
else if (this.userTables.indexOf(conditionTable) > -1)
userColumn = 'sys_id';
else {
invalidCondition = true;
break;
}
if (!conditions[conditionTable].hasOwnProperty(userColumn)) {
conditions[conditionTable][userColumn] = [];
queryCount++; // Count total queries, where a query may include multiple conditions
}
conditions[conditionTable][userColumn].push(conditionGr.getElement('condition').toString().trim());
}
// Empty or invalid condition column here should skip current criteria
if (invalidCondition) {
gs.info(this.type + ": Invalid user_column for sn_hr_core_condition " + conditionGr.getUniqueValue());
continue;
}
if (this.optimizeCriteria) {
// TODO (Future optimization) Can join conditions if other table is an extension of sys_user and using sys_id as the user column
// Combine conditions for the sys_user table with a non-sys_user table conditions, where applicable
// Look for a non sys_user table and column to join to
var joinTable;
var joinColumn;
for (var t in conditions) {
for (var c in conditions[t]) {
if ((t && t != 'sys_user') && c && (c != 'sys_id')) {
joinTable = t;
joinColumn = c;
break;
}
}
if (joinTable && joinColumn)
break;
}
// Check that there is at least one sys_user condition to join
// Combine sys_user table only when the user column is 'sys_id' to prevent having to query for each user getting the dot walked column
if (joinTable && joinColumn && conditions.hasOwnProperty('sys_user') && conditions['sys_user'].hasOwnProperty('sys_id')) {
// Join sys_user conditions to join table conditions
for (var j in conditions['sys_user']['sys_id'])
conditions[joinTable][joinColumn].push(this._getUserQuery(conditions['sys_user']['sys_id'][j], joinColumn));
// Only remove sys_user conditions that were joined
if (Object.keys(conditions['sys_user']).length == 1)
delete conditions['sys_user'];
else
delete conditions['sys_user']['sys_id'];
queryCount--;
}
}
var conditionQueries = [];
// Build array of queries
for (var tableName in conditions) {
for (var columnName in conditions[tableName]) {
if (this.optimizeCriteria || conditions[tableName][columnName].filter(function(item) { return item.indexOf('^NQ') > -1; }).length == 0) {
conditionQueries.push({
'table': tableName + '',
'column': columnName + '',
'conditions': conditions[tableName + ''][columnName + '']
});
} else { // GlideRecord may not work when adding together conditions containing an ^NQ
for (var l = 0; l < conditions[tableName + ''][columnName + ''].length; l++) {
conditionQueries.push({
'table': tableName + '',
'column': columnName + '',
'conditions': [conditions[tableName + ''][columnName + ''][l] + '']
});
}
}
}
}
queryCount = conditionQueries.length;
// Build object of users that meet criteriaGr's conditions
var userQueriesMet = {};
for (var m = 0; m < conditionQueries.length; m++) {
var table = conditionQueries[m].table;
var column = conditionQueries[m].column;
var conditionArray = conditionQueries[m].conditions;
// Vars used for pagination of GlideAggregate, if needed
var count = 0;
var total = 1;
while (count < total) {
// Condition for single criteria and single condition where we do not need to intersect query results and can use aggregate count instead
var isSingleQueryCount = isCount && this.optimizeCriteria && criteriaGr.getRowCount() == 1
&& conditionQueries.length == 1 && (!criteriaUserColumn || !criteriaTable);
var conditionTableG;
// Use GlideAggregate to reduce the amount of data returned for each record queried
// Example: If there are 300 fields on a table, then GlideAggregate is quicker than GlideRecord for iterating over records
if (this.optimizeCriteria) {
conditionTableG = new GlideAggregate(table);
if (isSingleQueryCount) {
conditionTableG.setGroup(false);
conditionTableG.addAggregate('COUNT(DISTINCT', column);
} else
conditionTableG.groupBy(column);
} else
conditionTableG = new GlideRecord(table);
conditionTableG.addNotNullQuery(column);
// Allow multiple conditions in same query
for (var i = 0; i < conditionArray.length; i++)
conditionTableG.addEncodedQuery(conditionArray[i]);
if (this.optimizeCriteria && !isSingleQueryCount)
conditionTableG.chooseWindow(count, count + maxAggregates);
conditionTableG.query();
// If this is a single query where only the count is needed, then return the aggregate count
if (this.optimizeCriteria && isSingleQueryCount) {
conditionTableG.next();
userList.length = conditionTableG.getAggregate('COUNT(DISTINCT', column) || 0;
return userList;
} else {
// Avoid calling getRowCount each iteration
if (count == 0)
total = conditionTableG.getRowCount();
// Redundant check in case the total does not match the count, but there are no more records
if (!conditionTableG.hasNext())
break;
while (conditionTableG.next()) {
count++;
var userId = conditionTableG.getValue(column);
if (!userQueriesMet.hasOwnProperty(userId))
userQueriesMet[userId] = {};
// Use key=true rather than a count to prevent double counting a user in a query
userQueriesMet[userId][table + 'm' + column + '-' + m] = true;
}
}
}
}
// Add users who meet every condition to a list
for (var userSysId in userQueriesMet) {
var queriesMet = Object.keys(userQueriesMet[userSysId]).length;
if (queriesMet != queryCount)
continue;
// Only check criteria record exists if criteria table and user_column are provided
if (!criteriaUserColumn || !criteriaTableGr || criteriaTableGr.get(criteriaUserColumn, userSysId))
finalUserMap[userSysId] = true;
}
}
for (var finalUserSysId in finalUserMap)
userList.push(finalUserSysId);
return userList;
},
/*
* Adapt a sys_user encoded query to work on a non-sys_user table using the @param userColumn
* Example: For @param encodedQuery of "active=true^ORmarital_status=single"
* return "user.active=true^ORuser.marital_status=single" for sn_hr_core_profile
* return "active=true^ORmarital_status=single" for sys_user
* @param encodedQuery (String) Encoded query to adapt
* @param userColumn (String) User field name to use to adapt encoded query
* @return (String) Parsed encoded query
*/
_getUserQuery: function(encodedQuery, userColumn) {
if (!encodedQuery || !userColumn || userColumn == 'sys_id')
return encodedQuery;
if (encodedQuery.endsWith('^EQ'))
encodedQuery = encodedQuery.substring(0, encodedQuery.length - 3);
var queries = encodedQuery.split('^NQ');
var queriesRet = [];
for (var i = 0; i < queries.length; i++) {
var orConditions = queries[i].split('^OR');
var orConditionsRet = [];
for (var j = 0; j < orConditions.length; j++) {
var andConditions = orConditions[j].split('^');
var andConditionsRet = [];
for (var k = 0; k < andConditions.length; k++)
andConditionsRet.push(userColumn + '.' + andConditions[k]);
if (andConditionsRet.length > 0)
orConditionsRet.push(andConditionsRet.join('^'));
}
if (orConditionsRet.length > 0)
queriesRet.push(orConditionsRet.join('^OR'));
}
return queriesRet.join('^NQ');
},
/* Evaluates the HR Criteria specified by @param criteriaId. If @param criteriaId is not specified, then return true.
*
* @param criteriaId String sys_id of a sn_hr_core_criteria
* @param userId (optional) String sys_id of a sys_user or extension, uses current user if not provided
* @param caseId (optional) String LE case sys_id to add the right survey instance to hr_condition
* @param simulation (optional) Boolean
* @param useCache (optional) Boolean Try to use scoped cache to retrieve / store result. Not all audience types support caching.
* @return boolean,
* For the criteria to evaluate the following must be true:
* 1. The Criteria record must be active
* 2. There must be at least one active related HR Condition with a table and condition
* 3. All applicable conditions must evaluate to true (have at least one result)
*
* Inactive or empty Conditions are ignored
*/
evaluateById : function(criteriaId, userId, simulation, caseId, useCache) {
if (gs.nil(criteriaId))
return true;
userId = userId || gs.getUserID();
// Ensure criteria exists (table is only required if there is a user_column)
var criteriaGr = new GlideRecord("sn_hr_core_criteria");
if (!criteriaGr.get(criteriaId))
return false;
// Ensure that if there is a user_column then it and the table are valid
if (criteriaGr.table) {
var criteriaUserColumn = criteriaGr.user_column;
var criteriaTableGr = new GlideRecord(criteriaGr.table);
if (criteriaUserColumn && (!criteriaTableGr.isValid() || !criteriaTableGr.isValidField(criteriaUserColumn)))
return false;
// Check if a record exists in the criteria's table, similar to @function getUsersForHRCriteria
if (criteriaUserColumn && !criteriaTableGr.get(criteriaUserColumn, userId))
return false;
}
// Value is in minutes, and TTL of zero turns off caching
var ttl = parseInt(gs.getProperty('sn_hr_core.cache.hrcriteria.ttl', 0));
if (ttl == 0 || caseId)
useCache = false;
if (useCache) {
var resultFromCache = this._checkCacheForResult(userId, criteriaId, ttl);
if (resultFromCache != null)
return resultFromCache;
}
var grCondition = new GlideRecord("sn_hr_core_condition");
grCondition.addQuery("active", true);
grCondition.addNotNullQuery("condition");
grCondition.addNotNullQuery("table");
if (simulation)
grCondition.addQuery("table", "sn_hr_core_profile").addOrCondition("table", "sys_user");
var qc = grCondition.addJoinQuery("sn_hr_core_m2m_condition_criteria");
qc.addCondition("hr_criteria", criteriaId);
qc.addCondition("hr_criteria.active", true);
grCondition.query();
if (!grCondition.hasNext())
return !!simulation;
var result = true;
while (grCondition.next()) {
var hrCriteriaPrivate = new hr_Criteria_Private();
var cond = hrCriteriaPrivate._addUserToCondition(grCondition, userId);
if (grCondition.employee_form)
cond = hrCriteriaPrivate._addSurveyInstance(grCondition, userId, caseId);
if (!(hrCriteriaPrivate._meetsHrCondition(grCondition.table, cond))) {
result = false;
break;
}
}
if (useCache)
this._saveResultToCache(userId, criteriaId, result);
return result;
},
/* Check if the HR Criteria cache contains the result for the given userId and criteriaId, and if it is still valid
* based upon the time to live.
*
* @param userId String sys_id of a sys_user or extension
* @param criteriaId String sys_id of a sn_hr_core_criteria
* @param ttl int The time to live in minutes for entries in the cache; used to evict for the criteria id within a userId key.
* @return boolean The cached value, or null if not found or expired.
*/
_checkCacheForResult: function(userId, criteriaId, ttl) {
var value = sn_scoped_cache.ScopedCacheManager.get(this.hrCriteriaCacheName, userId);
if (!value)
return null;
var userCachedResults = JSON.parse(value);
if (userCachedResults) {
var resultByCriteriaId = userCachedResults[criteriaId];
if (resultByCriteriaId) {
ttl = ttl * 60 * 1000; // convert ttl from minutes to milliseconds to match getNumericValue from GlideDateTime
// see if the cache entry should be evicted based upon the Time to Live from the system property
var goodThru = ttl + parseInt(resultByCriteriaId.lastComputed);
if (new GlideDateTime().getNumericValue() < goodThru)
return resultByCriteriaId.result;
else {
delete userCachedResults[criteriaId];
sn_scoped_cache.ScopedCacheManager.put(this.hrCriteriaCacheName, userId, JSON.stringify(userCachedResults));
}
}
}
return null;
},
/* Save the result for a userId and criteriaId to the HR Criteria cache.
*
* @param userId String sys_id of a sys_user or extension
* @param criteriaId String sys_id of a sn_hr_core_criteria
* @param result boolean The result for the given userId and criteriaId
*/
_saveResultToCache: function(userId, criteriaId, result) {
var value = sn_scoped_cache.ScopedCacheManager.get(this.hrCriteriaCacheName, userId);
var userCachedResults;
if (value)
userCachedResults = JSON.parse(value);
else
userCachedResults = {};
var answer = {};
answer.lastComputed = new GlideDateTime().getNumericValue();
answer.result = result;
userCachedResults[criteriaId] = answer;
sn_scoped_cache.ScopedCacheManager.put(this.hrCriteriaCacheName, userId, JSON.stringify(userCachedResults));
},
};Sys ID
e9a507e4532312003585c3c606dc3455