Send an email at closure with the latest solution included

Sometimes for certain implementation, is it required to send to send towards the end-user a notification with the solution. And this at closure time.
Sending the solution at the time we create it, is far easier. It's done like sending out 'Log Comments'.

This is how we implement this. We presume that other possible way's are possible.

1.

Create a Perl file, named mail.pl

 

# Created by Daniel Paessens (HP)
# Email: daniel.paessens@paessens.org
#
# Purpose: Send an email at closure of the ticket
# and includes the solution if the ticket
#
# Version: 0.2
# Filename: Mail.pl
# File location: C:\DataScripts
# Creation Date: 21/02/2008
# Modified: 21/02/2008
#

use DBI;
use Net::SMTP;

$dbh = DBI->connect('dbi:ODBC:String', 'Username', 'Password');

$ref_num = "@ARGV[0]";
$email = "@ARGV[1]";
$summary = "@ARGV[2]";

$sti = $dbh->prepare("select persid from call_req where ref_num = '$ref_num'")
|| die("Can't prepare SQL statement $DBI::errstr");
$sti->execute() || die("Can't execute SQL statement $DBI::errstr");
$ref_num2 = $sti->fetchrow_array();
$sth = $dbh->prepare("select top 1 description from act_log where (call_req_id = '$ref_num2' AND type = 'SOLN') ORDER BY system_time DESC")
|| die("Can't prepare SQL statement $DBI::errstr");
$sth->execute() || die("Can't execute SQL statement $DBI::errstr");
$sol_desc = $sth->fetchrow_array();

&email;
exit;

sub email
{
my $cnt_type = shift;
my $email_msg = create_email();
my $smtp = Net::SMTP->new( 'mail.server.com', Hello => 'mail.server.com' );
$smtp->mail( 'servicedesk@nowhere.com' );
$smtp->recipient($email);
$smtp->data();
$smtp->datasend( "reply-to: servicedesk\@nowhere.com\n" );
$smtp->datasend( "To:" . $email . "\n");
$smtp->datasend( "Subject: Your ticket $ref_num has been closed\n" );
$smtp->datasend( "Content-type: text/html\n\n" );
$smtp->datasend( "\n" );
$smtp->datasend( "$email_msg" );
$smtp->dataend();
$smtp->quit;
}

sub create_email
{
my $msg = "<html><head></head><body>";
$msg = $msg . "<table width=\"500px\"><tr><td>";
$msg = $msg . "<table border=0 width=\"600px\" style=\"font: Arrial; font-size: 12pt;\"><tr><td>";
$msg = $msg . "<span style=\"color: #006682\">Service Desk </span>";
$msg = $msg . "</tr><tr><td><br>";
$msg = $msg . "Dear User r</td></tr><tr><td><br>";
$msg = $msg . "Your ticket $ref_num concerning $summary has been solved </td></tr><tr><td>";
$msg = $msg . "The following solution was used for this :</td></tr><tr><td>";
$msg = $msg . "$sol_desc</td></tr><tr><td>";
$msg = $msg . "If any questions contact us .</td></tr><tr><td><br><br>";
$msg = $msg . "</tr></table>";
$msg = $msg . "</td></tr></table>";
$msg = $msg . "</body></html>";
return $msg;
}

 
3.

Go to Administration - Service Desk - Application Data - Remote References.
Create a new remote references as follow:

Be aware that you need to use double back slahs.

4.

Go to Administration - Events & Macros - Macros
Create your marco, which will contain the remote reference mentioned above.

5.

Go to Administration - Events & Macros - Events
Create your Event
There should be no repeat and the action on true is the marco you just created
6. Use the Schema Desinger to add the trigger to the cr table.
POST_VALIDATE zcr_attach_closed_events() 90 FILTER(status { -> 'CL'});
7. Create your spl file
cr::zcr_attach_closed_events(...) {
object group_leader;
object attached_events_table_record;
send_wait(0, top_object(), "get_co_group");
if (msg_error()) {
logf(ERROR, "%s - %s", ref_num, msg[0]);
}
group_leader = msg[0];
send_wait( 0, top_object(), "call_attr", "atev", "get_new_dob", NULL, NULL, group_leader);
if (msg_error()) {
logf(ERROR, "%s - %s", ref_num, msg[0]);
}
attached_events_table_record = msg[0];
attached_events_table_record.obj_id = persistent_id;
attached_events_table_record.event_tmpl = "evt:NNNNNNN";
send_wait(0, group_leader, "checkin");
if (msg_error()) {
logf(ERROR, "%s - failed to attach event 'NNNNNNN' via the zcr_attach_closed_events script", ref_num);
} else {
logf(SIGNIFICANT, "%s - successfully attached event 'NNNNNNN' via the zcr_attach_closed_events script", ref_num); }
}

Some remarks: