Tracking Ticket Status by Taking Calendar and Shift into Account

Body

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("workhours");
      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>

Additional Information:

Assumptions:

1) In any case, before you apply this solution to your system, you should be aware that this algorithm has been tested only for regular shifts and might not work for all shift structures. For complex shifts with multiple daily work time interruptions one should check the validation of this algorithm and update it accordingly if it is needed.

Note: In comments, HaroldMM mentioned that he has a requirement to replicate same solution to calculate the working time due to the calendar and shift data of the SLA applied to the ticket instead of the calendar and shift data of the person changing the status of ticket.

2) For the ticket SLA processing rule, it is assumed that the SLAs applied to one ticket are all of same organization, calendar and shift. Otherwise, it will not be possible to track the change of independent SLA and status changes with the given code.

The Java code above could be modified as follows in order to replace the processing rule:

    // remove work time processing rule based on status changeby
/*
    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");
*/

    // add work time processing rule based on ticket SLA
    MboSetRemote slaRecSet = mbo.getMboSet("TICKET.SLARECORDS");
    // if no SLA is applied to the ticket, then set as the status tracking time
    if(slaRecSet.isEmpty())
    {
        getMboValue("WORKTIMETRACKING").setValue(statustracking);
        return;
    }
    MboRemote slaRec = slaRecSet.getMbo(0);
    MboRemote sla = slaRec.getMboSet("SLA").getMbo(0);
    // if the calendar or shift of the SLA applied to the ticket is null, then set as the status tracking time
    if (sla.isNull("CALORGID") || sla.isNull("CALCCALENDAR") || sla.isNull("CALCSHIFT"))
    {
        getMboValue("WORKTIMETRACKING").setValue(statustracking);
        return;
    }
    String orgid = sla.getString("CALORGID");
    String calendar = sla.getString("CALCCALENDAR");
    String shift = sla.getString("CALCSHIFT");