Here's a script I use to notify users that there has been a change to their
work order in our tracking system. It shows most things you might want to
do with SQL via Perl. Note that this script assumes that you already have a
DSN set up to connect to the DB using ODBC, and you may have to tweak it a
bit to apply to your environment. I'm new to dbi myself, so doubtless I
could do this better, but it works.
## INFO ################################################
my $SCRIPT = 'TaskMod_Notify';
my $AUTHOR = 'Tim Johnson';
my $VERSION = '0.1.20030709';
my $CONTACTINFO = '[EMAIL PROTECTED]';
my $DESCRIPTION = 'Notifies Track-It users that their work order has been
modified.';
## SCRIPT ##############################################
use strict;
use warnings;
no warnings qw(uninitialized);
use DBI;
use Net::SMTP;
#connect to the db
my $dbh = DBI->connect("dbi:ODBC:TRACKIT6",'','');
$dbh->{LongReadLen} = 65535;
$dbh->{LongTruncOk} = 1;
#set up some globals
my %WO_hash;
my @modified;
my $ref;
my @mod_wo;
#get a list of all completed work orders
my $sth = $dbh->prepare( qq{SELECT * FROM TASKS WHERE COMPLETED IS NOT NULL}
);
$sth->execute();
while($ref = $sth->fetchrow_hashref()){
$completed{$ref->{WO_NUM}} = 1;
}
$sth->finish();
#get a list of all incomplete work orders
$sth = $dbh->prepare( qq{SELECT * FROM TASKS WHERE COMPLETED IS NULL} );
$sth->execute();
while($ref = $sth->fetchrow_hashref()){
$ref->{WO_NUM} = $ref->{WO_NUM} + 0;
$WO_hash{$ref->{WO_NUM}} = $ref->{MODIDATE};
}
$sth->finish();
#get all records from the custom table of task modified dates
$sth = $dbh->prepare( qq{SELECT * FROM cust_taskmod} );
$sth->execute();
while($ref = $sth->fetchrow_hashref()){
next if $ref->{WO} == 1;
#make sure it's a number, not a string
$ref->{WO} = $ref->{WO} + 0;
my $wo = $ref->{WO};
#make sure it's a number, not a string
$wo = $wo + 0;
push @mod_wo,$wo;
#Check if the date is different
if($ref->{Modified} && $ref->{Modified} ne $WO_hash{$ref->{WO}}){
push @modified,$ref->{WO};
#make sure it's not completed before you send the
notification
Notify($WO_hash{$ref->{REQUEST}},$ref->{WO},$ref->{Modified}) unless
$completed{$ref->{WO}};
}
}
#delete old table's contents
$sth = $dbh->prepare( qq{DELETE FROM cust_taskmod} );
$sth->execute();
#select open tickets
$sth = $dbh->prepare( qq{SELECT * FROM TASKS WHERE COMPLETED IS NULL} );
$sth->execute();
while($ref = $sth->fetchrow_hashref()){
push @hash,$ref;
}
#add them to the custom table
$sth = $dbh->prepare( "INSERT INTO cust_taskmod (WO,Modified) VALUES (?,?)"
) || die;
foreach(@hash){
$ref = $_;
$sth->execute($ref->{WO_NUM},$ref->{MODIDATE});
}
#Notify the user
sub Notify{
open(LOG,">>taskmod_notify.csv") || die;
my($name,$wo_num,$description) = @_;
#Get the record that corresponds to the work order
$sth = $dbh->prepare( qq{SELECT * FROM TASKS WHERE WO_NUM = $wo_num}
);
$sth->execute();
my $hashref = $sth->fetchrow_hashref();
#log the email
my($date,$time) = Date_Time::SimpleDT();
print LOG
"$hashref->{REQUEST},$hashref->{WO_NUM},$hashref{TASK},$date,$time\n";
#send the email
my $data;
my $smtp = new Net::SMTP('mailserver.company.com') || die "Could not
open a mail session to smiley!\n";
$smtp->mail('[EMAIL PROTECTED]');
$smtp->to($hashref->{EMAILADDR});
$data .= "Subject: Progress on your HelpDesk Ticket,
\"$hashref->{TASK}\"\n";
$data .= "From: IS Helpdesk\n";
$data .= "To: $hashref->{REQUEST}\n";
$data .= "\n";
$data .= "Our records indicate that there have been changes made to
your Track-It ticket.\n";
$data .= "\n";
$data .= "To check the current status of your work order, point your
browser to http://server and look for work order \#$hashref->{WO_NUM}.\n";
$data .= "\n";
$data .= uc($hashref->{RESPONS})." is the technician assigned to
your work order. Please contact him/her with any additional questions about
this issue. For emergency assistance with this or any other IS issue,
please call the IS Hotline at x#####.\n";
$data .= "\n";
$smtp->data([$data]);
$smtp->quit();
}