SBM AppScript Use Cases - 04. Subtask Time Calculation

Subtask에 대해 처리한 시간을 취합하여 상위 WCR에 입력하는 스크립트.
Child Task의 등록시간과 종료시간의 차이를 계산하여 취합.
첨부할 Note의 이름은 Number of Hours spent on Assessment. 보여지는 시간 포맷은 hh:mm:ss.
Assessment 완료 시점에 Post-transition을 통하여 실행이 된다.

' Name: calcSubTasksTime
' Function: After the completion of all subtasks for a parent item,
'    retrieve each subtask and calculate the total amount of time
'    that it took for all subtasks to complete. This total equals the
'    summation of the open time (Close - Submit dates) for each subtask.
'    Store the results in a new Note on the parent item indicating
'    the time using the following format: hh:mm:ss (01:23:09)
' Context: Post-transition for Assessment Complete transition
'
' Date: 03/09/2006
' For use in the TeamTrack TeamScript course examples.

' Require all vars to be declared before use (and then declare some)
Option Explicit

' Parent Item information
Const SUBTASK_FLD = "CHILD_TASKS"
Const PARENT_TABLE_ID = 1008

' Sub-task project ID, table ID, and Start and End time fields
Const PROJECT_ID = 31
Const CHILD_TABLE_ID = 1000
Const START_FLD = "SUBMITDATE"
Const END_FLD = "CLOSEDATE"

' Attachement type for a Note attachment
Const ATTACH_TYPE = 128

' Variables used to retreive data from the items
Dim objItem, objFlds, objFld, strSubtaskIds

' Get the current item
Set objItem = Shell.Item

' Read the comma separated unique id list of subtask items
Set objFlds = objItem.Fields()
Set objFld = objFlds.FindField(SUBTASK_FLD)
objFld.GetValue strSubtaskIds

' Parse the list of sub-task items so we can look them up individually
Dim nStart, nEnd, nLength, objSub, nSum, nId, nStartFld, nEndFld
' Set variables needed to parse the parent's list of child subtasks (multi-relational field value)
nLength = Len(strSubtaskIds)
nStart = 2
nEnd = nStart
' Create the object to hold a Child subtask item
Set objSub = Ext.CreateVarRecord(CHILD_TABLE_ID)
' Loop through the Parent's list of subtasks and process each child
do while nStart <= nLength
  ' The child record IDs reside between pairs of commas
  nEnd = InStr(nStart,strSubtaskIds, ",")
  nId = Mid(strSubtaskIds,nStart, nEnd - nStart)
  Call Ext.LogInfoMsg("Id: "&nId)
  ' Retrieve the child item and calculate the time the item was open
  call objSub.Read(nId)
  call objSub.GetFieldValue(START_FLD,nStartFld)
  call objSub.GetFieldValue(END_FLD,nEndFld)
  nSum = nSum + (nEndFld - nStartFld)
  ' Position for the next child ID
  nStart = nEnd + 1
loop

' Calculate the Hours:Minutes:Seconds
Dim nHours, nMinutes, nSeconds, strTotalTime
nHours = Int(nSum / 3600)
nSum = nSum - (nHours * 3600)
nMinutes = Int(nSum / 60)
nSeconds = nSum - (nMinutes * 60)
' Format the Hours:Minutes:Seconds
strTotalTime = ""
If nHours < 10 Then
  strTotalTime = strTotalTime&"0"&nHours
Else
  strTotalTime = strTotalTime&nHours
End If
If nMinutes < 10 Then
  strTotalTime = strTotalTime&":0"&nMinutes
Else
  strTotalTime = strTotalTime&":"&nMinutes
End If
If nSeconds < 10 Then
  strTotalTime = strTotalTime&":0"&nSeconds
Else
  strTotalTime = strTotalTime&":"&nSeconds
End If

Dim objAttachment
Set objAttachment = Ext.CreateAppRecord(Ext.TableId("TS_ATTACHMENTS"))
' The attachment record has a lot of fields to fill in
nId= objItem.GetId()
call objAttachment.SetFieldValue("CASEID", nId)
nId = Shell.User.GetId()
call objAttachment.SetFieldValue("AUTHORID",nId)
call objAttachment.SetFieldValue("TIME", Ext.DateToDBLong(now))
call objAttachment.SetFieldValue("TYPE",ATTACH_TYPE)
call objAttachment.SetFieldValue("TITLE","Number of Hours spent on Assessment")
call objAttachment.SetFieldValue("CONTENTS", "Total Hours = "&strTotalTime)
call objAttachment.SetFieldValue("SRCTABLEID",PARENT_TABLE_ID)

' Add the new Note record
Call objAttachment.Add()