SetTimeWorkedAutomatically

Hi,

Searching on past threads, I couldn't find how to set the "TimeWorked" automatically when a ticket was resolved, taking into account the "Started" and the "Resolved" dates. This prompted me to developed the following Scrip, which uses Date::Calc to find the date difference and update the TimeWorked field. Does RT already provide such an option?

Thanks,

Miguel

That's not what the field is meant for. It's hours spent working, not days until the ticket was closed. In general the former will be a small fraction of the latter. --Jerrad

I built upon the original scrip to add the option to control closed hours and weekends. I also added comments and color-coding to the perl text. --Max

- To change the amount of closed hours, edit the number value of the "$closed_hours" variable. It is currently set to 15.( E.G. 9am -> 6pm = 9 hours. 24 - 9 = 15 hours) 

- To change the amount of weekend days to count, edit the number value of the "$weekend_closed_days" variable. It is currently set to 2 (E.G. Saturday + Sunday = 2 days).

• Condition: On Resolve
• Action: User Defined
• Template: Global template: Blank
• Stage: TransactionBatch
• Custom action preparation code: return 1;
• Custom action cleanup code:

---

use Date::Calc qw(Delta_DHMS);

# Declare relevant variables
my $time_elapsed = "0";
my $not_started = undef;
my $not_resolved = undef;
my (@diff, @date_created, @date_started, @date_resolved, @date_last_updated);

# Get ticket date values and hold them in variables
my $date_created_string = $self->TicketObj->Created;
my $date_started_string = $self->TicketObj->Started;
my $date_resolved_string = $self->TicketObj->Resolved;
my $date_last_updated_string = $self->TicketObj->LastUpdated;

# If the ticket created date string is in a (YYYY-MM-DD HH:MM:SS) format
if ($date_created_string =~ /(\d+)\-(\d+)\-(\d+)\s(\d+)\:(\d+)\:(\d+)/) {
    # Create an array out of the individual date & time values
    @date_created = ($1, $2, $3, $4, $5, $6);
}

# If the ticket started date string is in a (YYYY-MM-DD HH:MM:SS) format
if ($date_started_string =~ /(\d+)\-(\d+)\-(\d+)\s(\d+)\:(\d+)\:(\d+)/) {
    # If ticket starts date is unset
    if($1 == 1970){
        # Set not_started variable to true
        $not_started = "1";
    }
    # Otherwise
    else {
        # The ticket started date has not been set
        @date_started = ($1, $2, $3, $4, $5, $6);
    }
}

# If the ticket resolved date string is in a (YYYY-MM-DD HH:MM:SS) format
if ($date_resolved_string =~ /(\d+)\-(\d+)\-(\d+)\s(\d+)\:(\d+)\:(\d+)/) {
    # Create an array out of the individual date & time values
    # If ticket resolved date is unset
    if ($1 == 1970){
        # Set not_resolved variable to true
        $not_resolved = "1";
    }
    # Otherwise
    else {
        # The ticket resolved date has not been set
        @date_resolved = ($1, $2, $3, $4, $5, $6);
    }
}

# If the ticket last updated date string is in a (YYYY-MM-DD HH:MM:SS) format
if ($date_last_updated_string =~ /(\d+)\-(\d+)\-(\d+)\s(\d+)\:(\d+)\:(\d+)/) {
    # Create an array out of the individual date & time values
    @date_last_updated = ($1, $2, $3, $4, $5, $6);
}

# If the ticket started and resolved dates have not been set
if ($not_started && $not_resolved) {
    # Create an array of delta values using the ticket created & last updated dates
    @diff = Delta_DHMS(@date_created, @date_last_updated);
}
# If the ticket started date has not been set
elsif ($not_started) {
    # Create an array of delta values using the ticket created & resolved dates
    @diff = Delta_DHMS(@date_created, @date_resolved);
}
# If the ticket resolved date has not been set
elsif ($not_resolved) {
    # Create an array of delta values using the ticket started & last updated dates
    @diff = Delta_DHMS(@date_started, @date_last_updated);
}
else {
    # Create an array of delta values using the ticket started & resolved dates
    @diff = Delta_DHMS(@date_started, @date_resolved);
}

# Only count open business hours
my $closed_hours = 12;
my $days = $diff[0];

# Option to remove weekends
my $weekend_closed_days = 2;
my $week_count = 0;
for (my $i = 0; $i <= $days; $i += 7) {
    $week_count += 1;
}
$days = $days - ($week_count * $weekend_closed_days);

# Convert the results into minutes
my $day_to_minutes = $days * 1440;
my $hour_to_minutes = $diff[1] * 60;
my $closed_hours_to_minutes = (60 * $closed_hours) * $days;

# Calculate the elapsed time
my $time_elapsed_sum = $day_to_minutes + $hour_to_minutes + $diff[2] - $closed_hours_to_minutes;

# If the sum of the elapsed times is greater then zero
if ($time_elapsed_sum > 0) {
    # Set the elapsed time
    $time_elapsed = qq($time_elapsed_sum\.$diff[3]);
}

# Log successful scrip execution
$RT::Logger->info("\n\nSetting TimeWorked on ticket to: $time_elapsed");

# Set the ticket worked time to the elapsed time
$self->TicketObj->SetTimeWorked( $time_elapsed );

----