Maximo Open Forum

Hello all,

Following is a script that I developed to fetch the a certain records using SQL and then I want to read record by record to the end of result set "RS". I have a problem that I do not get any error but there is no impact of the process as the script does not do any modification.

NOTE: When I use " while rs is not None:"  instead of ""while (rs.next())" then 

 it seems it is get into the loop but then I've an error at "mTSDAY = str(rs.getDate(TS_DAY))"

Any hep much appreciated.

from java.util.concurrent import TimeUnit

from java.lang import Math

from java.text import SimpleDateFormat

from psdi.security import ConnectionKey

from java.sql import Connection

from java.sql import Statement

mxServer = MXServer.getMXServer()

mTS = MXServer.getMXServer().getMboSet("ZZAMSTIMECARD", mbo.getUserInfo())

mBIO = MXServer.getMXServer().getMboSet("ZZICLOCK", mbo.getUserInfo())

mxServer = MXServer.getMXServer()

connKey = mbo.getThisMboSet().getUserInfo().getConnectionKey()

conn = mbo.getThisMboSet().getMboServer().getDBConnection(connKey)

sql = "SELECT DISTINCT FINGERNO, TS_DAY, TS_YEAR, TS_MONTH, PUNCH_IN, PUNCH_OUT, TSDAY_CHAR FROM (SELECT A.EMP_CODE FINGERNO, TRUNC(A.PUNCH_TIME) TS_DAY, EXTRACT(YEAR FROM PUNCH_TIME) TS_YEAR, EXTRACT(MONTH FROM PUNCH_TIME) TS_MONTH, SUBSTR(TO_CHAR ((SELECT MIN(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),12,5) PUNCH_IN, SUBSTR(TO_CHAR ((SELECT MAX(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),12,5) PUNCH_OUT,SUBSTR(TO_CHAR ((SELECT MAX(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),1,10) TSDAY_CHAR FROM ZZICLOCK A where trunc(a.punch_time)>=(select max(TRUNC(ts_day)) from zzamstimecard)) order by FINGERNO, ts_day"

s = conn.createStatement()

rs = s.executeQuery(sql)

conn.commit()

while (rs.next()):

    mTSDAY = str(rs.getDate("TS_DAY"))

    #mTS.setWhere("TS_DAY=:rs.ts_day and FINGERNO=:rs.fingerno")

    mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + rs.getString("FINGERNO") + "'");

    if mTS.isEmpty():

        mTIMESHEET = mTS.add()

        mTIMESHEET.setValue("TS_DAY",rs.getDate("TS_DAY"))

        mTIMESHEET.setValue("FINGERNO",rs.getString("FINGERNO"))

        mTIMESHEET.setValue("PUNCH_IN",rs.getString("PUNCH_IN"))

        mTIMESHEET.setValue("PUNCH_OUT",rs.getString("PUNCH_OUT"))

    else:

        mTS.setValue("PUNCH_IN",rs.getString("PUNCH_IN"))

        mTS.setValue("PUNCH_OUT",rs.getString("PUNCH_OUT"))

    mTIMESHEET = mTS.save()

#Administration
#Customizations
#Integrations
#MaximoApplicationSuite
#MaximoUserGroups

------------------------------
Mohammad Moula
eSolution
------------------------------