>> 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;