Tracking Ticket Status by Taking Calendar and Shift into Account

January 3, 2017 Yalcin Kumbasar

Ticket records, i.e. service requests (SR), incidents, problems, have historical status data stored in TKSTATUS table. The duration between status change dates is calculated on STATUSTRACKING field of TKSTATUS table. This calculation is a simple subtraction of two dates which ignores the Calendar and Working Time (Shift) of the person changing the status of ticket. Below, you can see the original dialog which is opened via View History option from Select Actions of ticket applications (sr, incident, problem applications).

<dialog id="viewhist" label="View History">
    <helpgrid id="viewhist_viewhist_history_help" innerhtml="If the user assigns the ticket to a person, the assigned owner group is the person group this person belongs to. If  the user assigns the ticket to a person group, the owner group is that person group."/>
    <table beanclass="com.ibm.tsd.pmcom.webclient.beans.PmStatusHistoryBean" id="viewhist_viewhist_history_pStatustbl" inputmode="readonly" label="Status History" orderby="changedate desc">
        <tablebody displayrowsperpage="5" filterable="true" filterexpanded="false" id="viewhist_viewhist_history_pStatustbl_tablebody">
            <tablecol dataattribute="status" id="viewhist_viewhist_history_pStatustbl_tablebody_1"/>
            <tablecol dataattribute="owner" id="tsd_viewhist_history_pStatustbl_tablebody_1_1"/>
            <tablecol dataattribute="ownergroup" id="tsd_viewhist_history_pStatustbl_tablebody_1_2"/>
            <tablecol dataattribute="assignedownergroup" id="tsd_viewhist_history_assignedOwnerGrp"/>
            <tablecol dataattribute="changedate" id="viewhist_viewhist_history_pStatustbl_tablebody_2"/>
            <tablecol dataattribute="changeby" id="viewhist_viewhist_history_pStatustbl_tablebody_3"/>
            <tablecol dataattribute="memo" id="viewhist_viewhist_history_pStatustbl_tablebody_4"/>
            <tablecol dataattribute="statustracking" id="tsd_viewhist_viewhist_history_pStatustbl_tablebody_5" label="Time Spent"/>
        </tablebody>
    </table>
    <!-- omitted part -->
    <buttongroup id="viewhist_2">
        <pushbutton default="true" id="viewhist_2_1" label="OK" mxevent="dialogclose"/>
    </buttongroup>
</dialog>

From Database Configuration application, we add a new field named WORKTIMETRACKING to TKSTATUS table which is created same as the attribute STATUSTRACKING.

Then, we create the following custom field class and put it under the folder com/custom/app/ticket in businessobjects.jar. We restart the system and update the Class Name of STATUSTRACKING attribute as com.custom.app.ticket.FldStatusTracking from Database Configuration application. Later, we turn admin mode on, apply configurations and turn admin mode off.

NOTE: Alternatively, the Java class can be converted to Jython script and defined as an attribute level (TKSTATUS.STATUSTRACKING) automation script with "after action" option via Automation Scripts application . This is a better approach which omits the operations done in the previous paragraph.

package com.custom.app.ticket;

import java.rmi.RemoteException;
import java.util.Date;

import com.ibm.tsd.util.PmUtility;

import psdi.app.common.DateUtility;
import psdi.app.person.PersonRemote;
import psdi.mbo.MboRemote;
import psdi.mbo.MboSetRemote;
import psdi.mbo.MboValue;
import psdi.mbo.MboValueAdapter;
import psdi.mbo.SqlFormat;
import psdi.util.MXException;

public class FldStatusTracking extends MboValueAdapter{

    public FldStatusTracking(MboValue mbv) {
        super(mbv);
        }
    
    @Override
    public void action() throws MXException, RemoteException {
        super.action();
        calculateWorkTimeTracking();
    }
    
    private void calculateWorkTimeTracking() throws MXException, RemoteException
  {
    // method to calculate and set TKSTATUS.WORKTIMETRACKING attribute
    MboRemote mbo = getMboValue().getMbo();
    String statustracking = getMboValue().toString();
    Date reportDate = mbo.getDate("CHANGEDATE");
    // if reportdate is null, do nothing
    if (reportDate == null) {
      return;
    }
    PersonRemote changeby = (PersonRemote) mbo.getMboSet("CHANGEBY").getMbo(0); 
    // if the calendar or shift of the person changing the ticket status is null, then set as the status tracking time
    if (changeby.isNull("PRIMARYCALORG") || changeby.isNull("PRIMARYCALNUM") || changeby.isNull("PRIMARYSHIFTNUM"))
    {
        getMboValue("WORKTIMETRACKING").setValue(statustracking);
        return;
    }
    String orgid = changeby.getString("PRIMARYCALORG");
    String calendar = changeby.getString("PRIMARYCALNUM");
    String shift = changeby.getString("PRIMARYSHIFTNUM");
    // convert the status tracking time to minutes as totalMinutes
    int totalMinutes = Integer.valueOf(statustracking.split(":")[0])*60 + Integer.valueOf(statustracking.split(":")[1]);
    Date today = DateUtility.getDate(reportDate);
    Date yesterday = DateUtility.addDays(today, -1);
    String where = "calnum = :1 and shiftnum = :2 and orgid = :3 and workdate >= :4";
    
    SqlFormat sqf = new SqlFormat(mbo, where);
    sqf.setObject(1, "WORKPERIOD", "calnum", calendar);
    sqf.setObject(2, "WORKPERIOD", "shiftnum", shift);
    sqf.setObject(3, "WORKPERIOD", "orgid", orgid);
    sqf.setDate(4, yesterday);

    where = sqf.format();
    MboSetRemote workPeriods = mbo.getMboSet("$workperiod", "WORKPERIOD", where);
    workPeriods.setOrderBy("workdate");
    // accumulate daily non-working minutes in accumulatedMinutes 
    int accumulatedMinutes = 0;
    Date workdate = null;
    Date start = null;
    Date end = null;
    for (int m = 0;; m++)
    {
      MboRemote workPeriod = workPeriods.getMbo(m);
      if (workPeriod == null) {
        break;
      }
      workdate = workPeriod.getDate("workdate");
      start = workPeriod.getDate("starttime");
      end = workPeriod.getDate("endtime");
      double wHours = workPeriod.getDouble("workMinutes");
      wHours *= 60.0D;
      int workMinutes = (int)wHours;
      if (workdate.getTime() < today.getTime())
      {
        if (DateUtility.compareTime(start, end) > 0)
        {
          int diff = DateUtility.timeDiff(end, reportDate);
          if (diff > 0)
          {
            accumulatedMinutes += diff;
            if (accumulatedMinutes >= totalMinutes) {
                getMboValue("WORKTIMETRACKING")
                        .setValue(PmUtility.elapseTime(totalMinutes*60000L));
                return;
            }
          }
        }
      }
      else if (workdate.getTime() == today.getTime())
      {
        if (DateUtility.compareTime(start, reportDate) >= 0)
        {
          accumulatedMinutes += workMinutes;
          if (accumulatedMinutes >= totalMinutes)
          {
            Date date = DateUtility.combineDate(workdate, start);
            int t = accumulatedMinutes - totalMinutes;
            t = workMinutes - t;
            getMboValue("WORKTIMETRACKING")
                    .setValue(PmUtility.elapseTime(t*60000L));
            return;
          }
        }
        else if ((DateUtility.compareTime(start, end) >= 0) || (DateUtility.compareTime(end, reportDate) > 0))
        {
          if (DateUtility.compareTime(start, end) == 0) {
              getMboValue("WORKTIMETRACKING")
                        .setValue(PmUtility.elapseTime(totalMinutes*60000L));
              return;
          }
          int diff = DateUtility.timeDiff(end, reportDate);
          if (DateUtility.compareTime(start, end) > 0) {
            diff += 1440;
          }
          if (diff > 0)
          {
            accumulatedMinutes += diff;
            if (accumulatedMinutes >= totalMinutes) {
                getMboValue("WORKTIMETRACKING")
                        .setValue(PmUtility.elapseTime(totalMinutes*60000L));
                return;
            }
          }
        }
      }
      else
      {
        accumulatedMinutes += workMinutes;
        if (accumulatedMinutes >= totalMinutes)
        {
          Date date = DateUtility.combineDate(workdate, start);
          int t = accumulatedMinutes - totalMinutes;
          t = workMinutes - t;
          getMboValue("WORKTIMETRACKING")
                    .setValue(PmUtility.elapseTime(t*60000L));
          return;
        }
      }
    }
    if (workdate == null) {
        getMboValue("WORKTIMETRACKING")
                .setValue(PmUtility.elapseTime(totalMinutes*60000L));
        return;
    }
    Date date = DateUtility.combineDate(workdate, end);
    getMboValue("WORKTIMETRACKING")
            .setValue(PmUtility.elapseTime((totalMinutes - accumulatedMinutes)*60000L));
    return;
  }
}

Lastly, we add our custom attribute to the View History dialog right next to the original status tracking field.

<dialog id="viewhist" label="View History">
    <helpgrid id="viewhist_viewhist_history_help" innerhtml="If the user assigns the ticket to a person, the assigned owner group is the person group this person belongs to. If  the user assigns the ticket to a person group, the owner group is that person group."/>
    <table beanclass="com.ibm.tsd.pmcom.webclient.beans.PmStatusHistoryBean" id="viewhist_viewhist_history_pStatustbl" inputmode="readonly" label="Status History" orderby="changedate desc">
        <tablebody displayrowsperpage="5" filterable="true" filterexpanded="false" id="viewhist_viewhist_history_pStatustbl_tablebody">
            <tablecol dataattribute="status" id="viewhist_viewhist_history_pStatustbl_tablebody_1"/>
            <tablecol dataattribute="owner" id="tsd_viewhist_history_pStatustbl_tablebody_1_1"/>
            <tablecol dataattribute="ownergroup" id="tsd_viewhist_history_pStatustbl_tablebody_1_2"/>
            <tablecol dataattribute="assignedownergroup" id="tsd_viewhist_history_assignedOwnerGrp"/>
            <tablecol dataattribute="changedate" id="viewhist_viewhist_history_pStatustbl_tablebody_2"/>
            <tablecol dataattribute="changeby" id="viewhist_viewhist_history_pStatustbl_tablebody_3"/>
            <tablecol dataattribute="memo" id="viewhist_viewhist_history_pStatustbl_tablebody_4"/>
            <tablecol dataattribute="statustracking" id="tsd_viewhist_viewhist_history_pStatustbl_tablebody_5" label="Time Spent"/>
            <tablecol dataattribute="worktimetracking" id="tsd_viewhist_viewhist_history_pStatustbl_tablebody_6" label="Work Time Spent"/>
        </tablebody>
    </table>
    <!-- omitted part -->
    <buttongroup id="viewhist_2">
        <pushbutton default="true" id="viewhist_2_1" label="OK" mxevent="dialogclose"/>
    </buttongroup>
</dialog>

 

Previous Article
Maximo Asset Management 7.6.0.1 Interim Fix 017 released

The Interim Fix (IFIX) is available at Fix Central.   As with all IFIXes, MAM 7601 IFIX 017 is cumulative a...

Next Article
Changing Ticket Owner to the New Assignee of Workflow Reassignment (v7.5 or v7.6)
Changing Ticket Owner to the New Assignee of Workflow Reassignment (v7.5 or v7.6)

If a ticket, i.e. service request, incident or problem, is in workflow; then it might have both workflow as...

×

Want our latest news? Subscribe to our blog!

Last Name
First Name
Thank you!
Error - something went wrong!