I have an existing sql query that I want to convert into scripting which will be programmed using Automation Script in Maximo, however, I'm not familiar on how it will work, can someone help me with my autoscript? Thank you!
Here is the sql query:
select transdate, laborcode, regularhrs,premiumpayhours, premiumpayrate, enterby, timereportid from labtrans where ext_paysent = 0 and trunc(sysdate) = (select DueDate from EXT_TIMEREPORTPAYPERIOD where active=1)
and to_date(transdate) between (select PAYDATEBEGIN from EXT_TIMEREPORTPAYPERIOD where active =1)
and (select PAYDATEEND from EXT_TIMEREPORTPAYPERIOD where active = 1) and timereportid is not null
and regularhrs =0 and PREMIUMPAYHOURS is null;
Here is the autoscript:
from psdi.server import MXServer;
from java.sql import Statement;
from java.sql import PreparedStatement;
from java.sql import Connection;
from java.sql import ResultSet;
from psdi.mbo import Mbo;
import time;
mxserver = MXServer.getMXServer();
userInfo = mxserver.getSystemUserInfo();
# Get a Database COnnection from a Maximo Server
currentSet = mxserver.getMboSet("LABTRANS",userInfo);
currentMbo = currentSet.getMbo(0);
con = currentMbo.getMboServer().getDBConnection(userInfo.getConnectionKey());
# Get a schema name of the server to know from which instance it is running
schema = currentMbo.getMboServer().getSchemaOwner();
schema = schema.upper();
str = ['Maximo 7.6.1.2 Database ',schema,' is working!','\n','\n'];
# ' is not acceptable as part of String. it is included in Backlash \ in List and converted to String
labtransQuery = ['select laborcode, regularhrs, premiumpayhours, premiumpayrate, enterby, timereportid from labtrans where changeby = ','\'','MXINTADM','\'','group by timereportid'];
labtransQuery = ''.join(labtransQuery);
outboundQuery = ['select duedate, paydatebegin, paydateend from ext_timereportpayperiod where active = 1 and group by timereportid']
outboundQuery = ''.join(outboundQuery);
try:
s= con.createStatement();
rs1 = s.executeQuery(labtransQuery);
str.append('--------------------------------------');
str.append('\n');
str.append(' Labor Code');
str.append('\n');
str.append('--------------------------------------');
str.append('\n');
while(rs1.next()):
laborcode= rs1.getString('timereportid');
# Converts a time to a String of readable format
#changetimeString = time.strftime("%d-%b-%Y %I:%M:%S",changetime)
# Conversion of date to string depends on Database DATE format
str.append(companyset);
str.append('\t');
#str.append(changetimeString);
str.append(date);
str.append('\n');
rs1.close();
str.append('\n');
rs2 = s.executeQuery(outboundQuery);
str.append('--------------------------------------');
str.append('\n');
str.append('DUEDATE| PAYDATEBEGIN| PAYDATEEND');
str.append('\n');
str.append('--------------------------------------');
str.append('\n');
if (rs2.next()== False):
str.append('--NO RECORDS--');
str.append('\n');
else:
while True:
orgid = rs2.getString('duedate');
siteid = rs2.getString('paydatebegin');
count = rs2.getString('paydateend');
str.append(duedate);
str.append('\t');
str.append(paydatebegin);
str.append('\t');
str.append(paydateend);
str.append('\n');
if (rs3.next() == False) :
break
rs2.close();
str.append('\n');
s.close();
except Exception, e:
str.append("error in db");
#EndUser#EverythingMaximo------------------------------
Eugenio Cabalhin
IBM Solutions Delivery Inc
------------------------------