Name
sn_ppt_export.GeneratePPTUtil
Description
Content-Generation The purpose of this script include is to provide common methods. Warning Customers should not modify this file.
Script
var GeneratePPTUtil = Class.create();
GeneratePPTUtil.prototype = {
initialize: function(templateRecordSysID) {
this.templateRecordSysID = templateRecordSysID;
this._setTemplateGR(templateRecordSysID);
this.instanceRecordMap = {};
this.formatters = {};
//new cached query map
this.cachedRemoteTableQuery = {};
},
getParsedDataJSONString: function() {
if (GeneratePPTUtil.TEMPLATE_GR != null)
if (!global.JSUtil.nil(GeneratePPTUtil.TEMPLATE_GR.getValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSED_JSON_FIELD_NAME)))
return JSON.parse(GeneratePPTUtil.TEMPLATE_GR.getValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSED_JSON_FIELD_NAME));
gs.info("LOG: GeneratePPTUtil template is missing or parsed json is empty");
throw new Error(gs.getMessage("There was an error loading your file. You can upload the template again or try with a new template."));
},
updateJSONField: function() {
var util = new PPTRequestHandler(this.templateRecordSysID);
var JSONobject = util.runTemplateParser();
var gr = new GlideRecord(GeneratePPTConstants.TEMPLATE_TABLENAME);
gr.get(this.templateRecordSysID);
if (util.getStatusCode() === 200 && JSONobject) {
gr.setValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSED_JSON_FIELD_NAME, JSONobject);
gr.setValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSE_ERROR_FIELD_NAME, false);
} else {
gr.setValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSED_JSON_FIELD_NAME, "");
gr.setValue(GeneratePPTConstants.TEMPLATE_TABLE_PARSE_ERROR_FIELD_NAME, true);
}
gr.update();
},
getReportTypeSysID: function() {
if (GeneratePPTUtil.TEMPLATE_GR != null)
return GeneratePPTUtil.TEMPLATE_GR.getValue(GeneratePPTConstants.TEMPLATE_TABLE_REFERENCED_REPORT_TYPE_FIELD_NAME);
gs.info("LOG: GeneratePPTUtil report type id is null");
throw new Error(gs.getMessage("There was an error loading your file. You can upload the template again or try with a new template."));
},
isAttachmentExist: function() {
var attachment = new GlideRecord('sys_attachment');
attachment.addQuery('table_name', GeneratePPTConstants.TEMPLATE_TABLENAME);
attachment.addQuery('table_sys_id', this.templateRecordSysID);
attachment.setLimit(1);
attachment.query();
if (attachment.next()) {
return true;
}
return false;
},
_setTemplateGR: function() {
var gr = new GlideRecordSecure(GeneratePPTConstants.TEMPLATE_TABLENAME);
gr.get(this.templateRecordSysID);
if (gr.isValidRecord())
GeneratePPTUtil.TEMPLATE_GR = gr;
else {
GeneratePPTUtil.TEMPLATE_GR = null;
}
},
getTemplateGR: function() {
return GeneratePPTUtil.TEMPLATE_GR;
},
getMapFromHyperlink: function(link) {
var map = {};
link = decodeURIComponent(link);
if (global.JSUtil.notNil(link)) {
var mainParamsList = link.split('https://servicenow.com/');
if (mainParamsList.length === 2) {
var mainParams = mainParamsList[1].split('/');
for (var i = 0; i < mainParams.length; i++) {
var key = mainParams[i].slice(0, mainParams[i].indexOf('='));
var value = mainParams[i].slice(mainParams[i].indexOf('=') + 1);
map[key] = value;
}
}
}
return map;
},
getChildRecordsCount: function(parentTable, childTable, parentRecordSysID, encodedFilter) {
var gr = new GlideRecordSecure(childTable);
var relationObj = this.getParentChildRelationKey(parentTable, childTable, parentRecordSysID);
if (!relationObj.isEncodedQuery)
gr.addQuery(relationObj.query, parentRecordSysID);
else
gr.addEncodedQuery(relationObj.query);
gr.addEncodedQuery(encodedFilter);
gr.query();
return gr.getRowCount();
},
getParentChildRelationKey: function(parentTable, childTable, parentRecordSysID) {
var relatedTableGR;
var customEncodedFilterQuery = '';
var evaluator = new GlideScopedEvaluator();
var reportTypeGR = new GlideRecordSecure(GeneratePPTConstants.REPORT_TYPE_TABLENAME);
reportTypeGR.addQuery("sys_id", this.getReportTypeSysID());
reportTypeGR.addQuery("main_table", parentTable);
reportTypeGR.setLimit(1);
reportTypeGR.query();
if (reportTypeGR.next()) {
relatedTableGR = new GlideRecordSecure(GeneratePPTConstants.RELATED_TYPE_TABLENAME);
relatedTableGR.addQuery("table", childTable);
relatedTableGR.addQuery("report_type", this.getReportTypeSysID());
} else {
relatedTableGR = new GlideRecordSecure(GeneratePPTConstants.RELATED_TYPE_TABLENAME);
relatedTableGR.addQuery("parent_table.table", parentTable);
relatedTableGR.addQuery("table", childTable);
}
relatedTableGR.setLimit(1);
relatedTableGR.query();
if (relatedTableGR.next()) {
if ("1" === relatedTableGR.getValue("enable_custom_script")) {
try {
customEncodedFilterQuery = evaluator.evaluateScript(relatedTableGR, "custom_script", {"parentRecordSysID": parentRecordSysID});
} catch (e) {
gs.info("LOG: GeneratePPTUtil " + e.message);
throw new Error(gs.getMessage("Error completing the operation. Check system logs for more information."));
}
return {
'isEncodedQuery': true,
'query': customEncodedFilterQuery
};
}
return {
'isEncodedQuery': false,
query: relatedTableGR.getValue("relation_column")
};
}
},
getTablesForReportType: function(ReportTypeSysId) {
var tables = [];
var reportTypeRecord = new GlideRecord('sn_ppt_export_ppt_report_type');
reportTypeRecord.get(ReportTypeSysId);
var table = reportTypeRecord.getValue('main_table');
tables.push(table);
var relatedTableRecord = new GlideRecord('sn_ppt_export_ppt_related_table');
relatedTableRecord.addQuery('report_type', ReportTypeSysId).addOrCondition("parent_table.report_type", ReportTypeSysId);
relatedTableRecord.query();
while (relatedTableRecord.next()) {
var table = relatedTableRecord.getValue('table');
if (tables.indexOf(table) == -1)
tables.push(table);
}
return tables;
},
getFormatterMappings: function() {
var tables = this.getTablesForReportType(this.getReportTypeSysID());
var formatterMappings = new GlideRecord(GeneratePPTConstants.FORMATTER_MAPPING);
formatterMappings.addQuery('table', 'IN', tables.join(','));
formatterMappings.query();
while (formatterMappings.next()) {
var table = formatterMappings.getValue('table');
var column = formatterMappings.getValue('column');
if (!(table in this.formatters))
this.formatters[table] = {};
var formatter = formatterMappings.getValue('formatter');
var cellFormatters = this.getCellFormatters(formatter);
while (cellFormatters.next()) {
if (!(column in this.formatters[table]))
this.formatters[table][column] = [];
var cellFormatterObject = {};
cellFormatterObject['condition'] = cellFormatters.getValue('condition');
cellFormatterObject['style'] = cellFormatters.getValue('style');
cellFormatterObject['order'] = cellFormatters.getValue('order');
this.formatters[table][column].push(cellFormatterObject);
}
}
},
getCellFormatters: function(formatter) {
var formatters = new GlideRecord(GeneratePPTConstants.CELL_FORMATTER);
formatters.addQuery('formatter', formatter);
formatters.orderBy('order');
formatters.query();
return formatters;
},
getMatchedCellFormatter: function(table, column, recordId) {
var tableGR = new GlideRecord(table);
tableGR.get(recordId);
var cellFormatter = {};
var cellFormatters = this.formatters[table][column];
for (var i = 0; i < cellFormatters.length; i++) {
var condition = cellFormatters[i].condition;
var newQueries = condition.split('^NQ');
var matchedFormatter = false;
for (var j = 0; j < newQueries.length; j++) {
var orQueries = newQueries[j].split('^OR');
if (orQueries.length > 1) {
for (var k = 0; k < orQueries.length && !matchedFormatter; k++) {
if (this.checkCondition(orQueries[k], tableGR))
matchedFormatter = true;
}
if (matchedFormatter)
return cellFormatters[i];
} else {
var andQueries = newQueries[j].split('^');
for (var k = 0; k < andQueries.length && !matchedFormatter; k++) {
if (!this.checkCondition(andQueries[k], tableGR))
matchedFormatter = true;
}
if (!matchedFormatter)
return cellFormatters[i];
}
}
}
return cellFormatter;
},
checkCondition: function(query, tableGR) {
var leftHandColumn = '';
var rightHandColumn = '';
var leftHandColumnObj = {};
var rightHandColumnObj = {};
var conditionFields = '';
var operator = '';
if (query.indexOf('>=') != -1)
operator = '>=';
else if (query.indexOf('>') != -1)
operator = '>';
else if (query.indexOf('<=') != -1)
operator = '<=';
else if (query.indexOf('<') != -1)
operator = '<';
else if (query.indexOf('!=') != -1)
operator = '!=';
else if (query.indexOf('=') != -1)
operator = '=';
conditionFields = query.split(operator);
if ((conditionFields[0].indexOf('{') != -1) && (conditionFields[0].indexOf('}') != -1)) {
var leftHandColumnField = conditionFields[0].substring(conditionFields[0].indexOf('{') + 1, conditionFields[0].indexOf('}'));
leftHandColumnObj = this.getValueAndTypeBasedOnFieldType(tableGR, leftHandColumnField);
} else
leftHandColumn = conditionFields[0] + '';
if ((conditionFields[1].indexOf('{') != -1) && (conditionFields[1].indexOf('}') != -1)) {
var rightHandColumnField = conditionFields[1].substring(conditionFields[1].indexOf('{') + 1, conditionFields[1].indexOf('}'));
rightHandColumnObj = this.getValueAndTypeBasedOnFieldType(tableGR, rightHandColumnField);
} else
rightHandColumn = conditionFields[1] + '';
if ((leftHandColumnObj && leftHandColumnObj.type && this._checkForCurrencyAndDecimal(leftHandColumnObj.type)) || (rightHandColumnObj && rightHandColumnObj.type && this._checkForCurrencyAndDecimal(rightHandColumnObj.type))) {
if (leftHandColumnObj && leftHandColumnObj.type && this._checkForCurrencyAndDecimal(leftHandColumnObj.type))
leftHandColumn = this._getRoundOffValues(leftHandColumnObj.value);
else
leftHandColumn = this._getRoundOffValues(leftHandColumn.trim());
if (rightHandColumnObj && rightHandColumnObj.type && this._checkForCurrencyAndDecimal(rightHandColumnObj.type))
rightHandColumn = this._getRoundOffValues(rightHandColumnObj.value);
else
rightHandColumn = this._getRoundOffValues(rightHandColumn.trim());
} else if ((leftHandColumnObj && leftHandColumnObj.type && leftHandColumnObj.type == "glide_date") && (rightHandColumnObj && rightHandColumnObj.type && rightHandColumnObj.type == "glide_date")) {
if (gs.nil(leftHandColumnObj.value) || gs.nil(rightHandColumnObj.value))
return false;
var columnObj = this._doGlideDateComparision(leftHandColumnObj.value, rightHandColumnObj.value);
leftHandColumn = columnObj.leftHandColumnValue;
rightHandColumn = columnObj.rightHandColumnValue;
} else if ((leftHandColumnObj && leftHandColumnObj.type && leftHandColumnObj.type == "glide_date_time") && (rightHandColumnObj && rightHandColumnObj.type && rightHandColumnObj.type == "glide_date_time")) {
if (gs.nil(leftHandColumnObj.value) || gs.nil(rightHandColumnObj.value))
return false;
var columnObj = this._doGlideDateTimeComparision(leftHandColumnObj.value, rightHandColumnObj.value);
leftHandColumn = columnObj.leftHandColumnValue;
rightHandColumn = columnObj.rightHandColumnValue;
} else {
if (leftHandColumnObj && leftHandColumnObj.type)
leftHandColumn = leftHandColumnObj.value;
if (rightHandColumnObj && rightHandColumn.type)
rightHandColumn = rightHandColumn.value;
leftHandColumn = leftHandColumn.trim();
rightHandColumn = rightHandColumn.trim();
}
switch (operator) {
case '>':
return leftHandColumn > rightHandColumn;
case '<':
return leftHandColumn < rightHandColumn;
case '>=':
return leftHandColumn >= rightHandColumn;
case '<=':
return leftHandColumn <= rightHandColumn;
case '=':
return leftHandColumn == rightHandColumn;
case '!=':
return leftHandColumn != rightHandColumn;
}
return false;
},
_checkForCurrencyAndDecimal: function(type) {
return (type == 'currency' || type == 'currency2' || type == 'decimal' || type == 'integer' || type == 'percent_complete');
},
_doGlideDateComparision: function(leftHandColumn, rightHandColumn) {
var leftHandColumnDate = new GlideDate();
leftHandColumnDate.setValue(leftHandColumn);
var rightHandColumnDate = new GlideDate();
rightHandColumnDate.setValue(rightHandColumn);
return {
'leftHandColumnValue': leftHandColumnDate.getNumericValue(),
'rightHandColumnValue': rightHandColumnDate.getNumericValue()
};
},
_doGlideDateTimeComparision: function(leftHandColumn, rightHandColumn) {
var leftHandColumnDateTime = new GlideDateTime(leftHandColumn);
var rightHandColumnDateTime = new GlideDateTime(rightHandColumn);
return {
'leftHandColumnValue': leftHandColumnDateTime.getNumericValue(),
'rightHandColumnValue': rightHandColumnDateTime.getNumericValue()
};
},
_getRoundOffValues: function(value) {
if (value != 0 && Math.floor(value) != value) {
value = Math.round(value * 100) / 100;
}
return value;
},
getValueAndTypeBasedOnFieldType: function(tableGr, column) {
var elem = tableGr.getElement(column);
var elementDescriptor = elem.getED();
var internalType = elementDescriptor.getInternalType();
if (internalType == 'currency') {
return {
'type': internalType,
'value': elem.getCurrencyValue()
};
} else if (internalType == 'currency2')
return {
'type': internalType,
'value': elem.amount
};
else
return {
'type': internalType,
'value': tableGr.getValue(column)
};
},
getRelatedListData: function(parentTable, childTable, parentRecordSysID, key, orderNumber, encodedFilter, orderByObj) {
try {
var orderByColumnName = orderByObj['orderByColumnName'];
var orderByDesc = orderByObj['orderByDesc'];
var cacheKey = childTable + "_" + parentRecordSysID + "_" + orderNumber + "_" + encodedFilter + "_";
cacheKey += global.JSUtil.notNil(orderByColumnName) ? orderByColumnName : '';
cacheKey += "_";
cacheKey += global.JSUtil.notNil(orderByDesc) ? orderByDesc : false;
if (this.instanceRecordMap.hasOwnProperty(cacheKey)) {
if (this.instanceRecordMap[cacheKey].getElement(key).getED().getInternalType() === "html")
return this._getTextValueFromHTML(this.instanceRecordMap[cacheKey].getDisplayValue(key));
return this.instanceRecordMap[cacheKey].getDisplayValue(key);
}
var gr = this.getRelatedRecordForRelatedList(parentTable, childTable, parentRecordSysID, key, orderNumber, encodedFilter, orderByObj);
var count = 0;
while (gr.next() && gr.canRead()) {
this.instanceRecordMap[cacheKey] = gr;
return this.getFieldValue(gr, key);
}
return "";
} catch (e) {
gs.info("LOG: GeneratePPTUtil "+e.message);
return "";
}
},
getRelatedRecordForRelatedList: function(parentTable, childTable, parentRecordSysID, key, orderNumber, encodedFilter, orderByObj) {
try {
var orderByColumnName = orderByObj['orderByColumnName'];
var orderByDesc = orderByObj['orderByDesc'];
var gr = new GlideRecordSecure(childTable);
var relationObj = this.getParentChildRelationKey(parentTable, childTable, parentRecordSysID);
if (!relationObj.isEncodedQuery)
gr.addQuery(relationObj.query, parentRecordSysID);
else
gr.addEncodedQuery(relationObj.query);
if (global.JSUtil.notNil(orderByColumnName)) {
if (orderByDesc)
gr.orderByDesc(orderByColumnName);
else
gr.orderBy(orderByColumnName);
}
gr.addEncodedQuery(encodedFilter);
if (orderNumber)
gr.chooseWindow(orderNumber - 1, orderNumber);
gr.query();
return gr;
} catch (e) {
gs.info("LOG: GeneratePPTUtil "+e.message);
return new GlideRecordSecure(childTable);
}
},
getFieldValue: function(gr, key) {
try {
if (gr.isValidRecord()) {
var elementDescriptor = gr.getElement(key).getED();
var internalType = elementDescriptor.getInternalType();
if (internalType === 'html')
return this._getTextValueFromHTML(gr.getDisplayValue(key));
return gr.getDisplayValue(key);
}
return "";
} catch (e) {
gs.info("LOG: GeneratePPTUtil "+e.message);
return "";
}
},
getRemoteTableCachedQuery: function(key) {
if (this.cachedRemoteTableQuery.hasOwnProperty(key))
return this.cachedRemoteTableQuery[key];
return '';
},
isRemoteTable: function(tableName) {
var remoteTableGr = new GlideRecord("sys_db_object");
remoteTableGr.addQuery("name", tableName);
remoteTableGr.addQuery("scriptable_table", true);
remoteTableGr.setLimit(1);
remoteTableGr.query();
return remoteTableGr.hasNext();
},
_applyRelatedListGlideRecordQuery: function(childTable, relationObj, parentRecordSysID, orderByDesc, orderByColumnName, encodedFilter) {
try{
var gr = new GlideRecordSecure(childTable);
if (!relationObj.isEncodedQuery)
gr.addQuery(relationObj.query, parentRecordSysID);
else
gr.addEncodedQuery(relationObj.query);
if (global.JSUtil.notNil(orderByColumnName)) {
if (orderByDesc)
gr.orderByDesc(orderByColumnName);
else
gr.orderBy(orderByColumnName);
}
gr.addEncodedQuery(encodedFilter);
gr.query();
return gr;
} catch (e) {
gs.info("LOG: GeneratePPTUtil "+e.message);
return new GlideRecordSecure(childTable);
}
},
getRelatedListGlideRecord: function(parentTable, childTable, parentRecordSysID, encodedFilter, orderByObj) {
var orderByColumnName = orderByObj['orderByColumnName'];
var orderByDesc = orderByObj['orderByDesc'];
var relationObj = this.getParentChildRelationKey(parentTable, childTable, parentRecordSysID);
var gr = this._applyRelatedListGlideRecordQuery(childTable, relationObj, parentRecordSysID, orderByDesc, orderByColumnName, encodedFilter);
if (this.isRemoteTable(childTable)) {
while(gr.next()) {
if (!this.cachedRemoteTableQuery.hasOwnProperty(gr.getUniqueValue()))
this.cachedRemoteTableQuery[gr.getUniqueValue()] = {"parentTable":parentTable, "childTable": childTable, "parentRecordSysID": parentRecordSysID, "encodedFilter":encodedFilter, "orderByObj": orderByObj};
}
gr = this._applyRelatedListGlideRecordQuery(childTable, relationObj, parentRecordSysID, orderByDesc, orderByColumnName, encodedFilter);
}
return gr;
},
getBaseRecordData: function(baseTable, baseRecordSysID, key, orderNumber) {
try {
if (this.instanceRecordMap.hasOwnProperty(baseTable + "_" + orderNumber)) {
if (this.instanceRecordMap[baseTable + "_" + orderNumber].getElement(key).getED().getInternalType() === "html")
return this._getTextValueFromHTML(this.instanceRecordMap[baseTable + "_" + orderNumber].getDisplayValue(key));
return this.instanceRecordMap[baseTable + "_" + orderNumber].getDisplayValue(key);
}
var gr = new GlideRecord(baseTable);
gr.addQuery("sys_id", baseRecordSysID);
gr.query();
var count = 0;
if (gr.next() && gr.canRead()) {
this.instanceRecordMap[baseTable + "_" + orderNumber] = gr;
var elementDescriptor = gr.getElement(key).getED();
var internalType = elementDescriptor.getInternalType();
if (internalType === 'html')
return this._getTextValueFromHTML(gr.getDisplayValue(key));
return gr.getDisplayValue(key);
}
return "";
} catch (e) {
gs.info("LOG: GeneratePPTUtil "+e.message);
return "";
}
},
_getTextValueFromHTML: function(html) {
if (!html)
return "";
var text = html.replace(/<\/?p>/gi, "\n")
.replace(/<\/?br\s*\/?>/gi, "\n")
.replace(/<li>/gi, " * ")
.replace(/(<(?:.|\n)*?>)/gm, "")
.replace(/ /g, " ")
.replace(/(\n\s*)+/g, "\n")
.trim();
return text;
},
type: 'GeneratePPTUtil'
};Sys ID
64a8c2de53310110b595ddeeff7b1206