Maximo Open Forum

 View Only

  • 1.  SQL in Automations script

    Posted 07-11-2023 04:40
    Edited by Mohammad Moula 07-11-2023 06:21

    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
    ------------------------------



Related Content