Re: [otrs] freefield autofill

23 Nov 2009

      ...
...
Is there anyone that has an freefield autofiller just ready and
modifiable ? I mean something like a
...
...
mysql trigger or something else that parse incoming mail (so insert
into) and take values and update
...
...
the same record ?
Not sure if what we have done on our old OTRS 1.3.1 system is of any
help to you.

We have a New Start web form and a Leaver web form that is completed
when someone new is coming to work in our offices or if someone is
leaving. Each form generates an e-mail in a specific format with a
string in the subject field that can be searched in the OTRS system. 

In OTRS we have set up two GenericAgent jobs which are run every 10
minutes with names such as 'New Start' and 'Leavers'. For example, the
GenericAgent 'Leavers' job runs every 10 minutes, searches for "Leaver:
*" at the start of the subject field, moves the ticket to the Leavers
queue, CMD runs the /opt/otrs/CustomCode/Leavers.pl file, etc. The 'New
Start' GenericAgent job is similar but searches for "New User: *" at the
start of the subject field, moves the ticket to the 'New Start' queue,
CMD runs the /opt/otrs/CustomCode/NewStarts.pl file.

The Perl code for the NewStarts.pl and Leavers.pl scripts is appended to
the end of this e-mail.

Regards

Paul McIlfatrick

Leavers.pl

==========

#!/usr/bin/perl -w

use DBI;

if (scalar @ARGV == 0) {

  print "This Perl script must be invoked with a ticket number as a
parameter!\n";

  exit;

}

$TicketNumber = $ARGV[0];

# $TicketID = $ARGV[1];

$freekey1 = "Centre last day";

$freetext1 = "?";

$freekey2 = "BT last day";

$freetext2 = "";

$freekey3 = "Approved";

$freetext3 = "Not yet";

$freekey4 = "FindFone";

$freetext4 = "Not yet disabled";

$freekey5 = "VoIP number";

$freetext5 = "Not yet freed up";

$freekey6 = "Locker key";

$freetext6 = "Not yet returned";

$freekey7 = "Mouse";

$freetext7 = "Not yet returned";

$freekey8 = "";

$freetext8 = "";

$going_offshore = 0;

$dbh = DBI->connect("dbi:mysql:otrs:otrs.bfsec.bt.co.uk", "root",
"serv1ces");

# Get contents from submitted Leaver form.

$sql = "SELECT article.a_subject FROM article,ticket WHERE ticket.tn =
$TicketNumber AND article.ticket_id = ticket.id";

$sth = $dbh->prepare($sql);

$sth->execute();

@email_subject = $sth->fetchrow_array;

$sth->finish;

$subject = grep(/Leaver:/, @email_subject);

if ($subject == 0) {

  print "Not a 'Leaver' SR!\n";

  exit;

}

$going_offshore = grep(/going offshore/, @email_subject);

if ($going_offshore == 1) {

  $freetext2 = "Going offshore";

}

#print "@email_subject\n";

#print "\n";

#exit;

$sql = "SELECT article.a_body FROM article,ticket WHERE ticket.tn =
$TicketNumber AND article.ticket_id = ticket.id";

$sth = $dbh->prepare($sql);

$sth->execute();

@email_body = $sth->fetchrow_array;

$sth->finish;

@lines = split(/\n/, $email_body[0]);

foreach $line_val (@lines){

  if ($line_val =~ m/^Last day in Centre:/i) {

    if ($line_val =~ m/\s*(\d+\/\d+\/\d+)\s*$/) {

      $freetext1 = $1;

    }

    #print "Centre last day='$freetext1'\n";

  }

  elsif ($line_val =~ m/^Last day in BT:/i) {

    if ($line_val =~ m/\s*(\d+\/\d+\/\d+)\s*$/) {

      $freetext2 = $1;

    }

    #print "BT last day='$freetext2'\n";

  }

}

#exit;

$sql = "update ticket set freekey1='$freekey1', freetext1='$freetext1',
freekey2='$freekey2', freetext2='$freetext2', freekey3='$freekey3',
freetext3='$freetext3', freekey4='$freekey4', freetext4='$freetext4',
freekey5='$freekey5', freetext5='$freetext5', freekey6='$freekey6',
freetext6='$freetext6', freekey7='$freekey7', freetext7='$freetext7',
freekey8='$freekey8', freetext8='$freetext8' WHERE tn='$TicketNumber'";

$sth = $dbh->prepare($sql);

$sth->execute || die "Could not execute SQL statement ... maybe
invalid?";

NewStarts.pl

============

#!/usr/bin/perl -w

use DBI;

if (scalar @ARGV == 0) {

  print "This Perl script must be invoked with a ticket number as a
parameter!\n";

  exit;

}

$TicketNumber = $ARGV[0];

$freekey1 = "Start date";

$freetext1 = "?";

$freekey2 = "Approved";

$freetext2 = "Not yet";

$freekey3 = "FindFone";

$freetext3 = "Not done";

$freekey4 = "Email";

$freetext4 = "Not done";

$freekey5 = "Enquiry email";

$freetext5 = "Not sent";

$freekey6 = "VoIP number";

$freetext6 = "Not allocated";

$freekey7 = "Locker number";

$freetext7 = "Not allocated";

$freekey8 = "Mouse";

$freetext8 = "Not given";

$dbh = DBI->connect("dbi:mysql:otrs:otrs.bfsec.bt.co.uk", "root",
"serv1ces");

# Get contents from submitted new start form.

$sql = "SELECT article.a_subject FROM article,ticket WHERE ticket.tn =
$TicketNumber AND article.ticket_id = ticket.id";

$sth = $dbh->prepare($sql);

$sth->execute();

@email_subject = $sth->fetchrow_array;

#$contents = $row[0];

$sth->finish;

$subject = grep(/New user:/, @email_subject);

if ($subject == 0) {

  print "Not a 'New Start' SR!\n";

  exit;

}

#print "@email_subject\n";

$sql = "SELECT article.a_body FROM article,ticket WHERE ticket.tn =
$TicketNumber AND article.ticket_id = ticket.id";

$sth = $dbh->prepare($sql);

$sth->execute();

@email_body = $sth->fetchrow_array;

$sth->finish;

@lines = split(/\n/, $email_body[0]);

foreach $line_val (@lines){

  if ($line_val =~ m/^User start date:/i) {

    if ($line_val =~ m/\s*(\d+\/\d+\/\d+)\s*$/) {

      $freetext1 = $1;

    }

    #print "User start date='$freetext1'\n";

  }

}

#exit;

$sql = "update ticket set freekey1='$freekey1', freetext1='$freetext1',
freekey2='$freekey2', freetext2='$freetext2', freekey3='$freekey3',
freetext3='$freetext3', freekey4='$freekey4', freetext4='$freetext4',
freekey5='$freekey5', freetext5='$freetext5', freekey6='$freekey6',
freetext6='$freetext6', freekey7='$freekey7', freetext7='$freetext7',
freekey8='$freekey8', freetext8='$freetext8' WHERE tn='$TicketNumber'";

$sth = $dbh->prepare($sql);

$sth->execute || die "Could not execute SQL statement ... maybe
invalid?";

$dbh->disconnect;

Re: [otrs] freefield autofill

paul.mcilfatrick＠bt.com