Name
sn_nlu_workbench.NLUWorkbenchExportUtil
Description
This utility helps in generating the CSV file from the table data and place in the sys_attachment
Script
var NLUWorkbenchExportUtil = Class.create();
(function() {
/*
Exports the data from give table to sys_attachment in background:
*/
NLUWorkbenchExportUtil.exportInBackground = function(table, filter, fields, execTable, execId, fileName) {
var script = "global.NLUWorkbenchExportUtil.exportFromTable('" + table + "', '" + filter + "', '" + JSON.stringify(fields) + "', '" + execTable + "', '" + execId + "', '" + fileName + "');";
return global.NLUWorkbenchGlobalScript.scheduleScript(script, execTable, execId);
};
/*
Ex1 :
-----
NLUWorkbenchExportUtil.exportFromTable('sys_nlu_model');
Ex 2:
-----
// * For the key: 'value', we can pass script as string and we can use varirables gr & field
// * For the key: 'valueCb', it we can pass a callback function (this is not supported in schedule, as we convert json to string)
var fields = {
language: {
label: 'Model Language',
value: "'>' + gr.getValue(field) + '<'"
},
sys_id: {
label: 'ID',
},
display_name: {
label: 'Model Name',
valueCb: function(gr, field) {
return 'NLUModel: ' + gr.getValue(field)
}
}
};
NLUWorkbenchExportUtil.exportFromTable('sys_nlu_model', 'language=en', fields);
*/
NLUWorkbenchExportUtil.exportFromTable = function(table, filter, fields, attachTable, attachId, fileName) {
var exportUtil = new NLUWorkbenchExportUtil(table, filter, fields);
exportUtil.setOptions({
attachTable: attachTable,
attachId: attachId,
fileName: fileName
});
return exportUtil.exportData();
};
/*
[Only from REST API] you can directly write to output stream
Ex:
---
NLUWorkbenchExportUtil.streamFromTable('sys_nlu_model', null, null, response.getStreamWriter());
*/
NLUWorkbenchExportUtil.streamFromTable = function(table, filter, fields, writer) {
var exportUtil = new NLUWorkbenchExportUtil(table, filter, fields);
exportUtil.setOptions({
writer: writer
});
return exportUtil.exportData();
};
var MODES = {
SCHEDULE: 1,
STREAM: 2
};
var splitFieldSeperator = ',';
var tables = NLUWorkbenchConstants.tables;
var escapeFormulas = gs.getProperty(NLUWorkbenchConstants.sysProps.EXPORT_ESCAPE_FORMULAS, false);
var FORMULA_CHARACTERS = ['=', '+', '-', '@'];
var ESCAPE_CHARACTER = '\'';
NLUWorkbenchExportUtil.prototype = {
initialize: function(table, filter, fieldDef) {
this.table = table;
this.filter = filter;
this.fieldDef = fieldDef;
this.fileName = table + '.csv';
this.evaluator = new NLUScopedEvaluator();
},
setOptions: function(options) {
if (!options) return;
if (options.execTable && options.execId) {
this.mode = MODES.SCHEDULE;
this.attachTable = options.execTable;
this.attachId = options.execId;
if (options.fileName)
this.fileName = options.fileName;
this.csvData = '';
} else if (options.writer) {
this.mode = MODES.STREAM;
this.writer = options.writer;
}
},
exportData: function() {
this._prepareFieldsDef();
this._addHeaders();
this._addData();
return this._endExport();
},
_prepareFieldsDef: function() {
if (!this.fieldDef) {
this.fieldDef = {};
// TODO: As GlideTableDescriptor can not be used in scoped app, using sys_dictionary
var dictGr = new GlideRecord(tables.SYS_DICTIONARY);
dictGr.addQuery('name', this.table);
dictGr.addEncodedQuery('internal_type!=collection^ORinternal_type=NULL'); // Ignore system fields
dictGr.query();
while (dictGr.next()) {
this.fieldDef[dictGr.element.toString()] = {
label: dictGr.column_label.toString()
};
}
} else if ('string' === typeof this.fieldDef) {
// From ScheduleOnce, we get this fields as string, so convert back to object
this.fieldDef = JSON.parse(this.fieldDef);
}
},
_addHeaders: function() {
var headerData = [];
for (var eachField in this.fieldDef) {
headerData.push(this.fieldDef[eachField].label);
}
this._processAndAddLines(headerData);
},
_addData: function() {
// Add values:
var gr = new GlideRecord(this.table);
gr.addActiveQuery();
if (this.filter) gr.addEncodedQuery(this.filter);
gr.query();
while (gr.next()) {
this._addRecord(gr);
}
},
_addRecord: function(gr) {
var recordData = [];
var numLines = 1; // Max values a split field can have
for (var field in this.fieldDef) {
var value = '';
var splitField = this.fieldDef[field].splitField;
if (this.fieldDef[field].valueCb) {
value = this.fieldDef[field].valueCb(gr, field);
} else if (this.fieldDef[field].value) {
var params = {
gr: gr,
field: field
};
value = this.evaluator.evaluate(this.fieldDef[field].value, params);
} else {
value = gr.getDisplayValue(field);
}
if (splitField && value.indexOf(splitFieldSeperator) !== -1) {
value = value.split(splitFieldSeperator);
if (value.length > numLines)
numLines = value.length;
} else
value = value.toString();
recordData.push(value);
}
this._processAndAddLines(recordData, numLines);
},
/*
- data is an array of strings or arrays
- numLines is the total number of lines we want to create from this data
Ex data for 2 lines:
[
"Laptop heating up",
"correct",
"hardwareissues, laptopissues",
[
"hardwareissues",
"laptopissues"
],
[
"78%",
"82%"
]
]
*/
_processAndAddLines: function(data, numLines) {
numLines = numLines || 1;
var linesArray = [];
var firstField = true;
var j = 0;
for (var i = 0; i < data.length; i++) {
var eachValue = value = data[i];
for (j = 0; j < numLines; j++) {
if (j >= linesArray.length) linesArray.push('');
if (Array.isArray(value))
eachValue = (j < value.length) ? value[j] : '--';
linesArray[j] += (firstField ? '"' : ',"') + this._cleanseValue(eachValue) + '"';
}
firstField = false;
}
for (j = 0; j < numLines; j++)
this._addLine(linesArray[j]);
},
_cleanseValue: function(value) {
if (!value) return '';
if (value !== '--') {
value = value.trim().replaceAll('"', "\"\"");
if (escapeFormulas && FORMULA_CHARACTERS.indexOf(value.charAt(0)) !== -1) {
value = ESCAPE_CHARACTER + value;
}
}
return value;
},
_addLine: function(data) {
data = data ? data + "\r\n" : '';
if (this.mode === MODES.SCHEDULE) {
this.csvData += data;
} else {
this.writer.writeString(data);
}
},
_endExport: function() {
if (this.mode === MODES.SCHEDULE) {
// attach the file to a record.
if (this.attachTable && this.attachId) {
var grRec = new GlideRecord(this.attachTable);
if (grRec.get(this.attachId)) {
var grAttachment = new GlideSysAttachment();
return grAttachment.write(grRec, this.fileName, 'application/csv', this.csvData);
}
}
return this.csvData;
}
},
type: 'NLUWorkbenchExportUtil'
};
})();
Sys ID
e417da96070c701028ef0a701ad3002a