Hello, I would like to share a way to create reports using Maximo automation scripts and send them as an .xls or .txt file attachment by email. Thanks
In this example, an SQL query returns a list of users who have borrowed tools from the warehouse.
In this metod is possible to define more rules to generate a report and send considerating another criteria.
from psdi.server import MXServer
from psdi.mbo import SqlFormat
def getSqlReport(): #Get the report by SQL
conKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey()
con = MXServer.getMXServer().getDBManager().getConnection(conKey)
s = con.createStatement()
q1 = u"select storeloc as WAREHOUSE, description as TOOL, issueto as USERID from matusetrans where quantity < 0 and issueid is null"
rs1 = s.executeQuery(q1)
resultList = []
metaData = rs1.getMetaData()
columnCount = metaData.getColumnCount()
while(rs1.next()):
row = {}
for i in range(1, columnCount + 1):
columnName = metaData.getColumnName(i)
row[columnName] = rs1.getString(columnName)
resultList.append(row)
rs1.close()
s.close()
con.commit()
MXServer.getMXServer().getDBManager().freeConnection(conKey)
return resultList
def buildAttatchment(report): #Build the body of attatchment
head = 'WAREHOUSE\tTOOL\tUSERID\n'
rows = len(report)
row = 0
body = ''
while row < rows:
body += report[row]['WAREHOUSE']
body += '\t'
body += report[row]['TOOL']
body += '\t'
body += report[row]['USERID']
body += '\n'
row += 1
return head + body
mailTo = 'user@usercompany.com'
mailFrom = 'maximo@usercompany.com'
mailSubj = 'Tools awaiting return.'
mailBody = 'Tools awaiting return.'
fileBody = buildAttatchment(getSqlReport())
fileName = 'attatchmentName.xls'
# Just as it is possible to build a .txt, .csv, ... file.
MXServer.getMXServer().sendEMail(mailTo, mailFrom, mailSubj, mailBody, fileBody, fileName)
#Reporting
------------------------------
Aurelio Daniel Moura
Business analyst and Product owner at Coamo
------------------------------