[rt-users] Scrip : Preserve CF on Queue Change

[Russ Roff]

Howdy,

A while back I asked if there was a way to preserve the values of my queue-specific custom fields during a queue change.  Since there wasn't one, I built one and it might be of some value to someone.

Problem - The same custom fields exist in multiple queues, but not in all queues.  Since these fields are usually specific to only one or two queues, using global custom fields isn't the best option.  However, per queue custom fields are queue specific and transparent of each other.

Solution - Assuming that the custom fields have identical names in both queues, compare the fields and update the ticket's fields in the New Queue if they have siblings in the Old Queue.  If any CF doesn't have a sibling in the New Queue, comment on the ticket to that extent.

Admittedly, my solution is a _very_ bad way to do this - I actually had to muck with the database directly, and that's less than ideal.  If anyone has suggestions on a way around this, I'd appreciate hearing about them.  In the meantime, it works (until I upgrade...)  I'd _really_ like to see something to address this on the wish list, if possible.  IMHO, I'm can't be the only one who would find this useful.

Be nice to my less than optimal Perl code - I write about once every 6 months.  Thanks for your time.

----------------------------------------
Scrip Description : Preserve CF on Queue Change
Condition : On Queue Change
Custom Condition : None
Action : User Defined
Custom action preparation code: {follows}
Custom action cleanup code: None
Stage: Transaction Create
Template: Global template: Blank
----------------------------------------
Custom action preparation code

# Retrieve important values
my $CurrentQueueID = $self->TransactionObj->NewValue();
my $OldQueueID = $self->TransactionObj->OldValue;
my $TicketID = $self->TicketObj->id();

my $CurrentQueue = new RT::Queue($RT::SystemUser);
$CurrentQueue->Load($CurrentQueueID);

my $OldQueue = new RT::Queue($RT::SystemUser);
$OldQueue->Load($OldQueueID);

my $CurrentQueueCFs = $CurrentQueue->CustomFields();
my $OldQueueCFs = $OldQueue->CustomFields();

my @CurrentQueueCFNames;
my @OldQueueCFNames;

while (my $CurrentCF = $CurrentQueueCFs->Next()) {
 # Exclude Global CFs
 unless ($CurrentCF->Queue == "0") {
  push (@CurrentQueueCFNames, $CurrentCF->Name);
 }
}

while (my $OldCF = $OldQueueCFs->Next()) {
 # Exclude Global CFs
 unless ($OldCF->Queue == "0") {
  push (@OldQueueCFNames, $OldCF->Name);
 }
}

# @CurrentQueueCFNames now has a list of all CF for the current queue
# @OldQueueCFNames now has a list of all CF for the previous queue

# Find CFs that are common between Old Queue and Current Queue

my @PortableCFs;
my @NonPortableCFs;
my %found;
my $CF_Test = "";

# Find CFs that are in Old Queue and Current Queue
foreach $CF_Test (@OldQueueCFNames) { $found{$CF_Test} = 1 }

foreach $CF_Test (@CurrentQueueCFNames) {
 if ($found{$CF_Test}) { push (@PortableCFs, $CF_Test) }
}

# Find CFs that are in Old Queue but NOT in Current Queue

%found = ();
foreach $CF_Test (@CurrentQueueCFNames) { $found{$CF_Test} = 1 }

foreach $CF_Test (@OldQueueCFNames) {
 unless ($found{$CF_Test}) {push (@NonPortableCFs, $CF_Test) }
}

# @PortableCFs now has a list of all CF that exist in both queues
# @NonPortableCFs now has a list of all CF that exist only in old queue

# Setup database access (MySQL only at this point)

use DBI;

my $RT_DB = "DBI:mysql:$RT::DatabaseName";
my $dbh = DBI->connect($RT_DB,$RT::DatabaseUser,$RT::DatabasePassword);

# For every PortableCF, retrieve the values from the prior queue and
# add them to the ticket in the new queue.

foreach my $CF_To_Copy (@PortableCFs) {
 my $sql="
   SELECT Content
  FROM TicketCustomFieldValues, CustomFields
  WHERE CustomFields.Name = '$CF_To_Copy'
  AND CustomFields.Disabled = 0
  AND CustomFields.Queue = $OldQueueID
  AND TicketCustomFieldValues.CustomField = CustomFields.id
  AND TicketCustomFieldValues.Ticket = $TicketID
  ";

 my $output = $dbh->prepare($sql);
 $output->execute();
 
 while (my $CF_Value = $output->fetchrow_array()) {
  my $CF_to_Update = new RT::CustomField($RT::SystemUser);
  $CF_to_Update->LoadByNameAndQueue
   (Queue => $CurrentQueueID, Name => $CF_To_Copy);
  
  $self->TicketObj->AddCustomFieldValue
   (Field => $CF_to_Update, Value => $CF_Value);
 }

 $output->finish();
}

# For every NonPortableCF, retrieve the values from the prior queue and
# put them in as a Comment on the ticket.

my $Comment;

if (@NonPortableCFs) {
 $Comment =
 "The following custom fields were in use before a queue change.\n";
 $Comment .=
 "These fields don't exist in the current queue.\n";
}

foreach my $CF_To_Comment (@NonPortableCFs) {
 $Comment .= "\n\n$CF_To_Comment : ";
 my $sql="
  SELECT Content
  FROM TicketCustomFieldValues, CustomFields
  WHERE CustomFields.Name = '$CF_To_Comment'
  AND CustomFields.Disabled = 0
  AND CustomFields.Queue = $OldQueueID
  AND TicketCustomFieldValues.CustomField = CustomFields.id
  AND TicketCustomFieldValues.Ticket = $TicketID
  ";

 my $output = $dbh->prepare($sql);
 $output->execute();

 while (my $CF_Value = $output->fetchrow_array()) {
  $Comment .= "( $CF_Value ) ";
 }
}

if ($Comment) {
 $self->TicketObj->Comment(Content => $Comment);
} 

$dbh->disconnect();

return 1;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20040302/50948823/attachment.htm>