A simple email notification system using perl

April 8, 2010

A simple email notification system using perl
In any organisation, keeping the customers/suppliers informed about orders/status etc., is a significant work. This tutorial
aims at providing a system which sends an automated email.

Scenarios and assumptions
a. A script is run on the database at fixed time and collects all transactions, supplierwise. The data so generated is stored in a table – called email.
b. A script is run to query the email table and send the email using a mail server.
c.Only one mail-id permitted per supplier.
d.Notifcations will be sent in a batchmode – not as and when transaction occur. This is so to give time to the operator to correct any mistake.

Software requirement
Database of your choice
perl with corresponding dbi/dbd modules
Mail::Sender from cpan.org (Offcourse there are many mailing modules- no specifc reason to choose this)

Step – 1 Create Tables
Supplier table: This is a standard master
create table supplier
suppcode char(6),
suppliername char(50),
supplier_address char(100),
email_id char(30),
primary key (suppcode)
Email table: This table is will hold the generated emails.
create table email
transno serial,
suppcode char(6),
toadd char(30),
subj char(50),
body text,
create_time timestamp,
sent_time timestamp,
expiry_time timestamp,
sent_flag char(1),
return_code char(30)

Bill details table. This table will contain deatails of bills passed.
create table bill_passed(
transdate date,
suppcode char(6),
billdetails char(40),
amount_passed money

Step -2
Create a script to collect various transactions, suppliercode wise. Create body using the data so collected. For the sake of simplicity let us assume only data from bill_passed table is used.
Note: A supplier may have multiple transaction on a given day. Here is simple script to read this table and create email.

use strict;
use DBI;

my $username=’x;
my $password=’y’;
my ($tdate,$suppcode,$billdetails,$amount_passed,$dbh,$query,


$query=”select * from bill_passed where transdate=CURRENT_DATE”;
$sth=$dbh->prepare($query) or die “could not do $query”;
while (($tdate,$suppcode,$billdetails,$amount_passed)=$sth->fetchrow_array()){

$suphash{$suppcode} .=sprintf “Billdetails:%s Amount Passed:%f\n”,$billdetails,$amount_passed;

#Now populate the email table with the data by looping through the hash
$query=”select email_id from supplier where suppcode=?”;
$sth=$dbh->prepare($query) or die “could not prepare $query”;
for $suppcode (keys %suphash){
$query=sprintf “insert into email (suppcode,toadd,subj,body,create_time) values (%s,%s,%s,%s,current_date) “,$dbh->quote($suppcode),$dbh->quote($mailid),$dbh->quote(‘Mail from system’),$dbh->quote($suphash{$suppco de});
print “query=$query\n”;
$dbh->do($query) or die “could not do $query”;

Step -3
Create a script which will send email from the table. Here it is
use strict;
use DBI;
use Mail::Sender;
#set smtp server address
my $sender = new Mail::Sender { smtp => ‘x.x.x.x’ };
#Database user name and password
my $username = ‘x’;
my $password = ‘y’;
my ( $href, $dbh, $query, $sth, $debug, $returncode );

#connect to database
$dbh = DBI->connect( “DBI:Pg:host=localhost;database=testdb”, $username, $password );
#get all mails pending from email table
$query = “select * from email where sent_flag is null”;
$sth = $dbh->prepare($query) or die “could not prepare $query”;
#Store data in a hash
$href = $sth->fetchall_hashref(‘transno’);
#process the hash one at a time and send email
foreach my $key ( sort keys %{$href} ) {
$returncode = $sender->MailMsg(
to => $href->{$key}->{toadd}, #To address
from => “dbarhper\@sr.railnet.gov.in”, #our emailid
subject => $href->{$key}->{subj},
msg => sprintf “%s”, $href->{$key}->{body}
#If successful update the table
if ( ref($returncode) eq “Mail::Sender” ) {
$query = “update email
set sent_flag=’Y’,
sent_time=now() “;
where transno=%d”, $href->{$key}->{transno};
$dbh->do($query) or die “could not do $query”;

#Put some sleep 10 ensure proper tranmission -else you pound mail server too fast.

sleep 10;



Step 4
Put the scripts in cron at appropriate time

Note: a. Ensure that proper permission to scripts as passwords are stored inside. Alternatively in case of Postgresql system user’s ident authentication can be used without password, though I have not tried it.
b.The scripts can be run frequently if you want notification frequently.