(Perl Programming) How to create a simple report from database and send it as an email using Google SMTP server?

use warnings;

use strict;

#Multipurpose Internet Mail Extensions (MIME) MIME::Lite is intended as a simple, standalone module for #generating MIME messages.

use MIME::Lite; 

#Implements the same API as Net::SMTP, but uses IO::Socket::SSL for its network operations.

#(Net::SMTP) This module implements a client interface to the SMTP and ESMTP protocol, enabling a perl5 #application to talk to SMTP servers.

use Net::SMTP::SSL;

#Database independent interface for Perl

use DBI;

sub Create_Course_Enrollee_Report {

my $total=0;

my $Course_Name;

my $Enrollee;

#Perl uses a writing template called a 'format' to output reports. 

#REPORT represents the name of the format.

#fieldline can contain any text or fieldholders. Fieldholders ( e.g @<<<<<<<<<<<<<<<<) hold space for #data that will be placed there at a later date.

#The values lines represent the values that will be entered into the field line. ($Course_Name)

format REPORT =

  @<<<<<<<<<<<<<<<<  @<<<<<<<<<<<<<<<  

   $Course_Name,                $Enrollee

.

format REPORT_TOTAL =

  ------------------------------------

@###

                $total

.

# Report Header: Format statements for a report heading use the same format as the detail line format #statement, except that _TOP is appended to the file handle. In the case of REPORT, you must specify #REPORT_TOP. Simply using _TOP will not work.

format REPORT_TOP =

  @|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||      Pg @<

  "LampSys College Course Enrollee Report",   $%

  Course Name         Enrollee            

  -----------------  ----------------  

.

#=======Calculate Current Year==============#

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

$year += 1900;

my $Signature_Msg="Copyright(C) $year, LampSys College";

#=================================================#

# Report Footer: The first part of the assignment, '-' x 40, creates a line of 40 dash characters. Then a newline #character is concatenated to the line of dashes. Next, the copyright line is appended. Finally, two more #linefeeds are appended to separate the two pages of output.

$^L = '-' x 40 . "\n" .

      "$Signature_Msg\n" .

      "\n\n";

#Writing a report to a file Open the Course_Enrollee.rpt file for output to hold the report.

open( REPORT, ">Course_Enrollee.rpt"); 

#create connection with data base, Connect function requires database name(test), user name(root) and #password(xyz).

# if connection is not established die option display an error message (standard Error Message) and your #script will die

my $DBH = DBI->connect('DBI:mysql:test', 'root', 'xyz') or die "Connection Error: $DBI::errstr\n";

my $Query="select course_name, enrollee from course_enrollee"; #Query statement

#Prepares a statement for later execution by the database engine and returns a reference to a statement #handle object.

my $Sth = $DBH->prepare("$Query");

$Sth->execute;

#$Sth->fetchrow_array returns an array of the results.

while(my @Row = $Sth->fetchrow_array())

{

    $Course_Name = $Row[0];   # store Course Name

    $Enrollee    = $Row[1];         # store Enrollee

    $Course_Name  = "" if !defined($Course_Name);

    $Enrollee = 0 if !defined($Enrollee);

    $total=$total+$Enrollee;

    write(REPORT); 

    #Invoke the format statement by using the write() function specifying the REPORT file name.

}

#The statement that changes a default file handle: First, REPORT is selected as the default file handle for # the print and write statements, and then the $~ variable is changed to the new format name. By enclosing #the two statements inside parentheses, their return values are used in an array context. You have probably #already guessed that the [0] notation is then used to retrieve the first element of the array: the value returned #from the select() function. Because the select() function returns the value of the previous default file handle, #after executing the second select(), the default file handle is restored to its previous value.

# Ref "http://affy.blogspot.com/p5be/ch11.htm"

select((select(REPORT), $~ = "REPORT_TOTAL")[0]);

write(REPORT);

print REPORT $^L; # Add Footer

close(REPORT);

$DBH->disconnect; #disconnect Database connection

}

sub Send_Report {

my $To = $_[0]; #

my @Course_Enrollee_Rpt=();

#===========Open Report File and Create a Message============#

open ( FILE, " < Course_Enrollee.rpt") or die "File Course_Enrollee.rpt is not exist";

@Course_Enrollee_Rpt = < FILE > ;  

close(FILE);

#Create a simple message containing just text 

my $msg = MIME::Lite->new(

  Data     =>  \@Course_Enrollee_Rpt #Passing Array Reference 

    );

### Get entire message as a string

my $str = $msg->as_string;    

#========================================================#

my $From='lampsystems@gmail.com';

my $Password='xyz';

my $Subject="Course Enrollee Report";

my $Smtp;

# create Smtp object HOST is the name of the remote host to which an SMTP connection is required.

if (not $Smtp = Net::SMTP::SSL->new('smtp.gmail.com',

                            Port => 465,

                            Debug => 1)) {

die "Could not connect to server\n";

}

 #Attempt SASL authentication. To support SASL you need to install the following modules on top of the #standard Perl distribution: Net_SSLeay.pm (Open SSL), IO-Socket-SSL, Authen-SASL, Net-SMTP-SSL

$Smtp->auth($From, $Password) || die "Authentication failed!\n";

$Smtp->mail($From . "\n");

$Smtp->to($To . "\n");

$Smtp->data();     #initiate the sending of the data from the current message.

$Smtp->datasend("From: " . $From . "\n"); 

 #data must be sent using the datasend:Send data to the remote server, converting LF to CRLF

$Smtp->datasend("To: " . $To . "\n");

$Smtp->datasend("Subject: " . $Subject . "\n");

$Smtp->datasend("\n");

$Smtp->datasend( $str . "\n" );

$Smtp->dataend(); #End the sending of data to the remote server.

$Smtp->quit;

}

#=============Main=================#

Create_Course_Enrollee_Report();

Send_Report('perlworld@gmail.com');

#==============Output====================#

MIME-Version: 1.0
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Content-Type: text/plain
X-Mailer: MIME::Lite 3.027 (F2.77; T1.31; A2.07; B3.08; Q3.08)
Date: Wed, 5 Jan 2011 14:33:10 -0500

   LampSys College Course Enrollee Report    Pg 1

 Course Name         Enrollee
 -----------------  ----------------
 Data Structures    30
 Digital Design     20
 Computer Organiza  35
 System Design      40
 ------------------------------------
                                125
----------------------------------------
 Copyright(C) 2011, LampSys College