Below is a script that exports a Java ResultSet to an Excel spreadsheet with an example of emailing the results. It includes the column names as the headers, makes them bold and uses the auto width for the column so it should be formatted correctly. It also formats dates and times according to your Maximo configuration.
I have includes the scriptConfig variable so you can deploy it using the VS Code Maximo Development Tools extension https://marketplace.visualstudio.com/items?itemName=sharptree.maximo-script-deploy
If you have questions please feel free to reach out. You can find other automation script examples on our blog, https://www.sharptree.io/blog
Regards,
Jason
Boolean = Java.type("java.lang.Boolean")
ByteArrayOutputStream = Java.type('java.io.ByteArrayOutputStream');
Types = Java.type('java.sql.Types');
ZoneId = Java.type("java.time.ZoneId");
Date = Java.type("java.util.Date");
Properties = Java.type("java.util.Properties")
DataHandler = Java.type("javax.activation.DataHandler")
Message = Java.type("javax.mail.Message")
Session = Java.type("javax.mail.Session")
InternetAddress = Java.type("javax.mail.internet.InternetAddress")
MimeBodyPart = Java.type("javax.mail.internet.MimeBodyPart")
MimeMessage = Java.type("javax.mail.internet.MimeMessage")
MimeMultipart = Java.type("javax.mail.internet.MimeMultipart")
Transport = Java.type("javax.mail.Transport")
ByteArrayDataSource = Java.type("javax.mail.util.ByteArrayDataSource")
Cell = Java.type('org.apache.poi.ss.usermodel.Cell');
Row = Java.type('org.apache.poi.ss.usermodel.Row');
XSSFSheet = Java.type('org.apache.poi.xssf.usermodel.XSSFSheet');
XSSFWorkbook = Java.type('org.apache.poi.xssf.usermodel.XSSFWorkbook');
XSSFFont = Java.type('org.apache.poi.xssf.usermodel.XSSFFont');
DBShortcut = Java.type('psdi.mbo.DBShortcut');
MXServer = Java.type('psdi.server.MXServer');
SmtpAuthenticator = Java.type("psdi.server.MXServer$SmtpAuthenticator")
CommonUtil = Java.type("psdi.util.CommonUtil")
HTML = Java.type("psdi.util.HTML")
MXFormat = Java.type("psdi.util.MXFormat");
main();
function main() {
var data;
var db = new DBShortcut();
try {
db.connect(MXServer.getMXServer().getSystemUserInfo().getConnectionKey());
var rs = db.executeQuery("select personid, displayname, status, statusdate, hiredate from person");
var data = exportResultSet(rs);
sendEmail("badaddress@sharptree.io", "maximo@sharptree.io", "Export of Person Table", "Jason,\n\nPlease find attached an export of the Maximo person table.\n\nRegards,\nMaximo", data);
} finally {
db.close();
}
return;
}
/**
* Send an email with an Excel workbook attachment.
* @param {String} to The to email address
* @param {String} from The from email address
* @param {String} subject The subject for the email
* @param {String} message The message for the email
* @param {byte[]} workbook A Java byte array representing the Apache POI Excel workbook.
* @param {String} attacmentName An optional attachment name, if not included "export.xlsx" is used.
*/
function sendEmail(to, from, subject, message, workbook, attachmentName) {
if (!to) {
throw Error("The to email address is required.");
}
if (!from) {
throw Error("The from email address is required.");
}
if (!subject) {
throw Error("A subject is required.");
}
if (!message) {
throw Error("A message body is required.");
}
if (!workbook) {
throw Error("A workbook attachment is required.");
}
if (!attachmentName) {
attachmentName = 'export.xlsx';
}
var session = _getMailSession();
var mimeMessage = new MimeMessage(session);
mimeMessage.addHeaderLine("method=REQUEST");
mimeMessage.addHeaderLine("charset=UTF-8");
mimeMessage.setFrom(new InternetAddress(from));
mimeMessage.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
mimeMessage.setSentDate(MXServer.getMXServer().getDate());
var charset = MXServer.getMXServer().getProperty("mxe.email.charset");
var charsetAvailable = typeof charset !== 'undefined' && !charset.isEmpty();
// if the subject was provided then remove new lines
if (typeof subject !== 'undefined' && !subject.isEmpty()) {
subject = CommonUtil.removeNewLines(subject);
// if the charset was available then use it, otherwise allow the system default to be used.
if (charsetAvailable) {
mimeMessage.setSubject(subject, charset);
} else {
mimeMessage.setSubject(subject);
}
}
var multipart = new MimeMultipart();
// if a non-empty message was provided then add it as the first message part.
if (typeof message !== 'undefined' && !message.isEmpty()) {
var mimeMessagePart = new MimeBodyPart();
var emailContentType = MXServer.getMXServer().getConfig().getProperty("mxe.email.content.type");
var convertToPlainText = MXServer.getMXServer().getConfig().getProperty("mxe.email.convertToPlainText");
if (!"text/html".equalsIgnoreCase(emailContentType) && Boolean.parseBoolean(equalsIgnoreCase(convertToPlainText))) {
message = HTML.toPlainText(message);
} else if (!HTML.isHtml(message) || HTML.isHtml(message) && !HTML.containsHtmlBreakTags(message)) {
message = HTML.replaceNewLineWithBR(message);
}
if (emailContentType) {
if (charsetAvailable) {
mimeMessagePart.setText(message, charset);
mimeMessagePart.setHeader("Content-Type", emailContentType + ";charset=" + charset);
} else {
mimeMessagePart.setText(message);
mimeMessagePart.setHeader("Content-Type", emailContentType);
}
} else if (charsetAvailable) {
mimeMessagePart.setText(message, charset);
} else {
mimeMessagePart.setText(message);
}
// add the message part to the multipart message.
multipart.addBodyPart(mimeMessagePart);
var attachment = new MimeBodyPart();
// add the Excel workbook message to the invite multi part.
attachment.setDataHandler(new DataHandler(new ByteArrayDataSource(workbook, 'application/vnd.ms-excel;name="' + attachmentName + '"')));
multipart.addBodyPart(attachment);
mimeMessage.setContent(multipart);
var sslEnabled = Boolean.parseBoolean(MXServer.getMXServer().getProperty("mail.smtp.ssl.enable"));
// if ssl is enabled send it via https otherwise just send the message.
if (sslEnabled) {
var transport = session.getTransport("smtps");
transport.connect();
transport.sendMessage(mimeMessage, mimeMessage.getAllRecipients());
transport.close();
} else {
Transport.send(mimeMessage);
}
}
}
/**
* Exports the provided ResultSet to an Excel workbook. Returns a Java byte array representing the workbook.
* @param {ResultSet} resultSet The Java JDBC ResultSet to export to Excel
* @param {String} sheetName An optional name for the workbook sheet, defaults to the table name of the first column in the result set.
*/
function exportResultSet(resultSet, sheetName) {
if (!resultSet) {
return null;
}
var metadata = resultSet.getMetaData();
var columnCount = metadata.getColumnCount()
var columns = [];
var types = [];
for (var i = 1; i <= columnCount; i++) {
columns.push(metadata.getColumnLabel(i));
types.push(metadata.getColumnType(i));
}
var workbook = new XSSFWorkbook();
var font = workbook.createFont();
font.setBold(true);
style = workbook.createCellStyle();
style.setFont(font);
var sheet = workbook.createSheet(sheetName ? sheetName : metadata.getTableName(1));
var headers = sheet.createRow(0);
for (i = 0; i < columnCount; i++) {
var header = headers.createCell(i);
header.setCellValue(columns[i]);
header.setCellStyle(style);
}
var rowIndex = 1;
while (resultSet.next()) {
var row = sheet.createRow(rowIndex++);
for (var j = 0; j < columnCount; j++) {
var value = resultSet.getString(j + 1);
if (value) {
switch (types[j]) {
case Types.DATE:
row.createCell(j).setCellValue(MXFormat.dateToString(Date.from(resultSet.getDate(j + 1).toLocalDate().atStartOfDay(ZoneId.systemDefault()).toInstant())));
break;
case Types.TIMESTAMP:
var value = resultSet.getTimestamp(j + 1);
row.createCell(j).setCellValue(MXFormat.dateToString(Date.from(resultSet.getTimestamp(j + 1).toInstant())) + " " + MXFormat.timeToString(Date.from(resultSet.getTimestamp(j + 1).toInstant())));
break;
case Types.SMALLINT:
case Types.INTEGER:
row.createCell(j).setCellValue(resultSet.getInt(j + 1));
break;
case Types.BIGINT:
row.createCell(j).setCellValue(resultSet.getLong(j + 1));
break;
case Types.DECIMAL:
case Types.FLOAT:
case Types.NUMERIC:
case Types.DOUBLE:
row.createCell(j).setCellValue(resultSet.getDouble(j + 1));
break;
default:
row.createCell(j).setCellValue(value);
break;
}
}
}
}
for (var j = 0; j < columnCount; j++) {
sheet.autoSizeColumn(j);
}
var out = new ByteArrayOutputStream();
workbook.write(out);
workbook.close();
return out.toByteArray();
}
// Use the Maximo properties to manually establish a new JavaMail session that can be used directly.
function _getMailSession() {
var mailProps = new Properties();
// Copy all the properties from the system config to a new variable that we can change.
mailProps.putAll(MXServer.getMXServer().getMxServerConfig());
var smtpHost = null;
smtpHost = MXServer.getMXServer().getProperty("mail.smtp.host");
if (smtpHost != null && !smtpHost.isEmpty()) {
mailProps.put("mail.smtp.host", smtpHost);
}
var sslEnabled = Boolean.parseBoolean(MXServer.getMXServer().getProperty("mail.smtp.ssl.enable"));
if (sslEnabled) {
mailProps.put("mail.transport.protocol", "smtps");
mailProps.put("mail.smtps.ssl.enable", sslEnabled);
mailProps.put("mail.smtps.socketFactory.port", MXServer.getMXServer().getProperty("mail.smtp.port"));
mailProps.put("mail.smtps.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
mailProps.put("mail.smtps.socketFactory.fallback", "false");
}
var user = MXServer.getMXServer().getProperty("mxe.smtp.user");
var password = MXServer.getMXServer().getProperty("mxe.smtp.password");
if (user != null && !user.isEmpty()) {
mailAuth = new SmtpAuthenticator(MXServer.getMXServer(), user, password);
} else {
mailAuth = null;
}
if (mailAuth != null) {
mailProps.put("mail.smtp.auth", "true");
}
var smtpTimeout = MXServer.getMXServer().getProperty("mxe.smtp.timeout");
if (smtpTimeout != null && !smtpTimeout.isEmpty()) {
mailProps.put("mail.smtp.timeout", smtpTimeout);
}
var smtpConnectionTimeout = MXServer.getMXServer().getProperty("mxe.smtp.connectiontimeout");
if (smtpConnectionTimeout != null && !smtpConnectionTimeout.isEmpty()) {
mailProps.put("mail.smtp.connectiontimeout", smtpConnectionTimeout);
}
mailProps.put("mail.smtp.sendpartial", mailProps.getProperty("mail.smtp.sendpartial").equals("0") ? "false" : "true");
return Session.getInstance(mailProps, mailAuth);
}
var scriptConfig = {
"autoscript": "SHARPTREE.EXCEL.EXPORT",
"description": "Sharptree excel export script.a",
"version": "",
"active": true,
"logLevel": "ERROR"
};
------------------------------
Jason VenHuizen
Sharptree
------------------------------
Original Message:
Sent: 05-15-2023 02:21
From: Deepa P
Subject: Fetch query results through automation script
Hi All,
I require your valuable suggestions on this:
There are several select queries that we run in sql to check if everything is going well. Queries like if workorders have been generated from pms, through the pmwogen cron, if reorder is running properly, etc. Is it possible to run fetch the results of these queries in an email attachment through an automation script?
Please reply
#Administration
#Analytics
#Architecture
#Assets
#CivilInfrastructure
#Customizations
#EndUser
#EverythingMaximo
#HSE/OilandGas
#Infrastructure
#Integrations
#Inventory
#IoT
#LifeScience/Calibration
#Linear
#MaximoApplicationSuite
#MaximoForAviation
#MaximoUserGroups
#Mobility
#Nuclear
#Procurement
#Reporting
#Scheduling
#Security
#ServiceProvider
#Spatial
#Transportation
#Utilities
#WorkCenters
#WorkManagement
------------------------------
Deepa P
------------------------------