Maximo Open Forum

Hello all,

I developed a script to either update an existing timesheet or add a new period of timesheet. In the script I convert all dates attributes into a string before to use them in the Set Where statement. BTY, the script is working but at  some point generate the following error because of "Adding records" and I don't know why ? 

Here is the complete script :

from psdi.server import MXServer

from psdi.mbo import Mbo

from java.util import Calendar

from java.util import Date

from psdi.app.common import DateUtility

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

from java.time.format import DateTimeFormatter

from java.util import Date, Locale

mAmsActions = MXServer.getMXServer().getMboSet("ZZAMSACTIONS", mbo.getUserInfo())

maximo = MXServer.getMXServer()

mUser = maximo.getUserInfo(user)

mAmsActions.moveFirst()

mGap = mAmsActions.getString("ACTIONS")

mFrom = mAmsActions.getString("PERIOD_FROM")

mFromDate = Date(mFrom)

mTo = mAmsActions.getString("PERIOD_TO")

mToDate = Date(mTo)

mDept = mAmsActions.getString("DEPARTMENT")

mVendor = mAmsActions.getString("COMPANY")

mPunchin = mAmsActions.getString("PUNCH_IN")

mPunchout = mAmsActions.getString("PUNCH_OUT")

mFingerno = mAmsActions.getString("FINGERNO")

mCal = Calendar.getInstance()

mCurrDate = mCal.getTime()

mCreatedate = str(mCurrDate)

mCreateby = "maxadmin"

mxServer = MXServer.getMXServer()

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

mEMPTBL = MXServer.getMXServer().getMboSet("ZZAMSEMP", mbo.getUserInfo())

strt = Calendar.getInstance()

end=Calendar.getInstance()

#

startdt = mFromDate

strt.setTime(startdt)

enddt = mToDate

end.setTime(enddt)

enddt=end.getTime()

#

mFingerno='5393'

if mFingerno == '':

    errorgroup = "AMSTSCHANGENO"

    errorkey = "AMSTSCHANGENO"

    # Do it for all employee

    mEmptable = mEMPTBL.moveFirst()

    while mEmptable:

        mFingerno = mEMPTBL.getString("FINGERNO")

        mAmsActions.setValue("FINGERNO",mFingerno)

        mAct = mAmsActions.save()

        #mAmsActions.setValue("DEPARTMENT",str(strt.getTime().date()))

        while strt.getTime().before(enddt):

            mTsday = str(strt.getTime())

            mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

            #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + str(strt.getTime()) +"'");

            if mTS:

                mTIMESHEET = mTS.add()

                mTIMESHEET.setValue("TS_DAY",strt.getTime())

                mTIMESHEET.setValue("FINGERNO",mFingerno)

                mTIMESHEET.setValue("PUNCH_IN",mPunchin)

                mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

                mTIMESHEET.setValue("GAP_ACTION",mGap)

                mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

                mTIMESHEET.setValue("CREATED_BY",mCreateby)

                mTIMESHEET.setValue("DESCRIPTION","*****************")

            else:

                mTS.setValue("PUNCH_IN",mPunchin)

                mTS.setValue("PUNCH_OUT",mPunchout)

                mTS.setValue("GAP_ACTION",mGap)

                mTS.setValue("CHANGE_DATE",mCreatedate)

                mTS.setValue("CHANGE_BY",mCreateby)

            mTIMESHEET = mTS.save()   

            strt.add(Calendar.DATE,1)

        mEmptable = mEMPTBL.moveNext()

else:

    # Do it for one Employee (the one who is entered by the user)

    while strt.getTime().before(enddt):

        #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + strt.getTime() +"'");

        mTsday = str(strt.getTime())

        mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

        if mTS:

            mTIMESHEET = mTS.add()

            mTIMESHEET.setValue("TS_DAY",strt.getTime())

            mTIMESHEET.setValue("FINGERNO",mFingerno)

            mTIMESHEET.setValue("PUNCH_IN",mPunchin)

            mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

            mTIMESHEET.setValue("GAP_ACTION",mGap)

            mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

            mTIMESHEET.setValue("CREATED_BY",mCreateby)

            mTIMESHEET.setValue("DESCRIPTION","*****************")

        else:

            mTS.setValue("PUNCH_IN",mPunchin)

            mTS.setValue("PUNCH_OUT",mPunchout)

            mTS.setValue("GAP_ACTION",mGap)

            mTS.setValue("CHANGE_DATE",mCreatedate)

            mTS.setValue("CHANGE_BY",mCreateby)

        mTIMESHEET = mTS.save()   

        strt.add(Calendar.DATE,1)

#Administration
#Customizations
#EverythingMaximo
#MaximoUserGroups

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

Hi, 

Please check if there is an unique index in this object ZZAMSTIMECARD in DB configuration app. That could be the reason which is validating the data before committing to DB.

------------------------------
Subhransu Sekhar Sahoo
Tata Consultancy Services
------------------------------

Original Message:
Sent: 07-18-2023 05:13
From: Mohammad Moula
Subject: Database error message when running the Script to add new records

Hello all,

I developed a script to either update an existing timesheet or add a new period of timesheet. In the script I convert all dates attributes into a string before to use them in the Set Where statement. BTY, the script is working but at  some point generate the following error because of "Adding records" and I don't know why ? 

Here is the complete script :

from psdi.server import MXServer

from psdi.mbo import Mbo

from java.util import Calendar

from java.util import Date

from psdi.app.common import DateUtility

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

from java.time.format import DateTimeFormatter

from java.util import Date, Locale

mAmsActions = MXServer.getMXServer().getMboSet("ZZAMSACTIONS", mbo.getUserInfo())

maximo = MXServer.getMXServer()

mUser = maximo.getUserInfo(user)

mAmsActions.moveFirst()

mGap = mAmsActions.getString("ACTIONS")

mFrom = mAmsActions.getString("PERIOD_FROM")

mFromDate = Date(mFrom)

mTo = mAmsActions.getString("PERIOD_TO")

mToDate = Date(mTo)

mDept = mAmsActions.getString("DEPARTMENT")

mVendor = mAmsActions.getString("COMPANY")

mPunchin = mAmsActions.getString("PUNCH_IN")

mPunchout = mAmsActions.getString("PUNCH_OUT")

mFingerno = mAmsActions.getString("FINGERNO")

mCal = Calendar.getInstance()

mCurrDate = mCal.getTime()

mCreatedate = str(mCurrDate)

mCreateby = "maxadmin"

mxServer = MXServer.getMXServer()

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

mEMPTBL = MXServer.getMXServer().getMboSet("ZZAMSEMP", mbo.getUserInfo())

strt = Calendar.getInstance()

end=Calendar.getInstance()

#

startdt = mFromDate

strt.setTime(startdt)

enddt = mToDate

end.setTime(enddt)

enddt=end.getTime()

#

mFingerno='5393'

if mFingerno == '':

    errorgroup = "AMSTSCHANGENO"

    errorkey = "AMSTSCHANGENO"

    # Do it for all employee

    mEmptable = mEMPTBL.moveFirst()

    while mEmptable:

        mFingerno = mEMPTBL.getString("FINGERNO")

        mAmsActions.setValue("FINGERNO",mFingerno)

        mAct = mAmsActions.save()

        #mAmsActions.setValue("DEPARTMENT",str(strt.getTime().date()))

        while strt.getTime().before(enddt):

            mTsday = str(strt.getTime())

            mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

            #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + str(strt.getTime()) +"'");

            if mTS:

                mTIMESHEET = mTS.add()

                mTIMESHEET.setValue("TS_DAY",strt.getTime())

                mTIMESHEET.setValue("FINGERNO",mFingerno)

                mTIMESHEET.setValue("PUNCH_IN",mPunchin)

                mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

                mTIMESHEET.setValue("GAP_ACTION",mGap)

                mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

                mTIMESHEET.setValue("CREATED_BY",mCreateby)

                mTIMESHEET.setValue("DESCRIPTION","*****************")

            else:

                mTS.setValue("PUNCH_IN",mPunchin)

                mTS.setValue("PUNCH_OUT",mPunchout)

                mTS.setValue("GAP_ACTION",mGap)

                mTS.setValue("CHANGE_DATE",mCreatedate)

                mTS.setValue("CHANGE_BY",mCreateby)

            mTIMESHEET = mTS.save()   

            strt.add(Calendar.DATE,1)

        mEmptable = mEMPTBL.moveNext()

else:

    # Do it for one Employee (the one who is entered by the user)

    while strt.getTime().before(enddt):

        #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + strt.getTime() +"'");

        mTsday = str(strt.getTime())

        mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

        if mTS:

            mTIMESHEET = mTS.add()

            mTIMESHEET.setValue("TS_DAY",strt.getTime())

            mTIMESHEET.setValue("FINGERNO",mFingerno)

            mTIMESHEET.setValue("PUNCH_IN",mPunchin)

            mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

            mTIMESHEET.setValue("GAP_ACTION",mGap)

            mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

            mTIMESHEET.setValue("CREATED_BY",mCreateby)

            mTIMESHEET.setValue("DESCRIPTION","*****************")

        else:

            mTS.setValue("PUNCH_IN",mPunchin)

            mTS.setValue("PUNCH_OUT",mPunchout)

            mTS.setValue("GAP_ACTION",mGap)

            mTS.setValue("CHANGE_DATE",mCreatedate)

            mTS.setValue("CHANGE_BY",mCreateby)

        mTIMESHEET = mTS.save()   

        strt.add(Calendar.DATE,1)

#Administration
#Customizations
#EverythingMaximo
#MaximoUserGroups

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