record support time on ticket create transactions, #20743

diff --git a/FS/FS/TicketSystem.pm b/FS/FS/TicketSystem.pm
index c1553f1..165856e 100644 (file)
--- a/FS/FS/TicketSystem.pm
+++ b/FS/FS/TicketSystem.pm
@@ -87,6 +87,8 @@ sub _upgrade_data {
   # bypass RT ACLs--we're going to do lots of things
   my $CurrentUser = $RT::SystemUser;
 
+  my $dbh = dbh;
+
   # selfservice and cron users
   foreach my $username ('%%%SELFSERVICE_USER%%%', 'fs_daily') {
     my $User = RT::User->new($CurrentUser);
@@ -252,6 +254,71 @@ sub _upgrade_data {
     die $msg if !$val;
   } #foreach (@Scrips)
 
+  # one-time fix: accumulator fields (support time, etc.) that had values 
+  # entered on ticket creation need OCFV records attached to their Create
+  # transactions
+  my $sql = 'SELECT first_ocfv.ObjectId, first_ocfv.Created, Content '.
+    'FROM ObjectCustomFieldValues as first_ocfv '.
+    'JOIN ('.
+      # subquery to get the first OCFV with a certain name for each ticket
+      'SELECT min(ObjectCustomFieldValues.Id) AS Id '.
+      'FROM ObjectCustomFieldValues '.
+      'JOIN CustomFields '.
+      'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
+      'WHERE ObjectType = \'RT::Ticket\' '.
+      'AND CustomFields.Name = ? '.
+      'GROUP BY ObjectId'.
+    ') AS first_ocfv_id USING (Id) '.
+    'JOIN ('.
+      # subquery to get the first transaction date for each ticket
+      # other than the Create
+      'SELECT ObjectId, min(Created) AS Created FROM Transactions '.
+      'WHERE ObjectType = \'RT::Ticket\' '.
+      'AND Type != \'Create\' '.
+      'GROUP BY ObjectId'.
+    ') AS first_txn ON (first_ocfv.ObjectId = first_txn.ObjectId) '.
+    # where the ticket custom field acquired a value before any transactions
+    # on the ticket (i.e. it was set on ticket creation)
+    'WHERE first_ocfv.Created < first_txn.Created '.
+    # and we haven't already fixed the ticket
+    'AND NOT EXISTS('.
+      'SELECT 1 FROM Transactions JOIN ObjectCustomFieldValues '.
+      'ON (Transactions.Id = ObjectCustomFieldValues.ObjectId) '.
+      'JOIN CustomFields '.
+      'ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) '.
+      'WHERE ObjectCustomFieldValues.ObjectType = \'RT::Transaction\' '.
+      'AND CustomFields.Name = ? '.
+      'AND Transactions.Type = \'Create\''.
+      'AND Transactions.ObjectType = \'RT::Ticket\''.
+      'AND Transactions.ObjectId = first_ocfv.ObjectId'.
+    ')';
+    #whew
+
+  # prior to this fix, the only name an accumulate field could possibly have 
+  # was "Support time".
+  my $sth = $dbh->prepare($sql);
+  $sth->execute('Support time', 'Support time');
+  my $rows = $sth->rows;
+  warn "Fixing support time on $rows rows...\n" if $rows > 0;
+  while ( my $row = $sth->fetchrow_arrayref ) {
+    my ($tid, $created, $content) = @$row;
+    my $Txns = RT::Transactions->new($CurrentUser);
+    $Txns->Limit(FIELD => 'ObjectId', VALUE => $tid);
+    $Txns->Limit(FIELD => 'ObjectType', VALUE => 'RT::Ticket');
+    $Txns->Limit(FIELD => 'Type', VALUE => 'Create');
+    my $CreateTxn = $Txns->First;
+    if ($CreateTxn) {
+      my ($val, $msg) = $CreateTxn->AddCustomFieldValue(
+        Field => 'Support time',
+        Value => $content,
+        RecordTransaction => 0,
+      );
+      warn "Error setting transaction support time: $msg\n" unless $val;
+    } else {
+      warn "Create transaction not found for ticket $tid.\n";
+    }
+  }
+
   return;
 }
 

diff --git a/rt/lib/RT/Action/Accumulate.pm b/rt/lib/RT/Action/Accumulate.pm
index 14675b8..03ac8e4 100644 (file)
--- a/rt/lib/RT/Action/Accumulate.pm
+++ b/rt/lib/RT/Action/Accumulate.pm
@@ -23,20 +23,38 @@ the same name, and should be single-valued fields.
 sub Prepare {
     my $self = shift;
     my $cfname = $self->Argument or return 0;
-    $self->{'inc_by'} = $self->TransactionObj->FirstCustomFieldValue($cfname) 
-                        || '';
-    return ( $self->{'inc_by'} =~ /^(\d+)$/ );
+    RT::Logger->info('Accumulate::Prepare called on transaction '.
+                       $self->TransactionObj->Id." field $cfname");
+    my $TransObj = $self->TransactionObj;
+    my $TicketObj = $self->TicketObj;
+    if ( $TransObj->Type eq 'Create' and
+         !defined($TransObj->FirstCustomFieldValue($cfname)) ) {
+        # special case: we're creating a new ticket, and the initial value
+        # may have been set on the ticket instead of the transaction, so
+        # update the transaction to match
+        $self->{'obj'} = $TransObj;
+        $self->{'inc_by'} = $TicketObj->FirstCustomFieldValue($cfname);
+    } else {
+        # the usual case when updating an existing ticket
+        $self->{'obj'} = $TicketObj;
+        $self->{'inc_by'} = $TransObj->FirstCustomFieldValue($cfname) 
+                            || '';
+    }
+    return ( $self->{'inc_by'} =~ /^(\d+)$/ ); # else it's empty
 }
 
 sub Commit {
     my $self = shift;
     my $cfname = $self->Argument;
+    my $obj = $self->{'obj'};
     my $newval = $self->{'inc_by'} + 
-      ($self->TicketObj->FirstCustomFieldValue($cfname) || 0);
-    my ($val) = $self->TicketObj->AddCustomFieldValue(
-      Field => 'Support time',
-      Value => $newval,
-      RecordTransaction => 0,
+      ($obj->FirstCustomFieldValue($cfname) || 0);
+    RT::Logger->info('Accumulate::Commit called on '.ref($obj).' '.
+                       $obj->Id." field $cfname");
+    my ($val) = $obj->AddCustomFieldValue(
+        Field => $cfname,
+        Value => $newval,
+        RecordTransaction => 0,
     );
     return $val;
 }