(Perl Programming) How to read XML file using XML-SIMPLE module and generate 3-D Bar graph using DBD-Chart module?

The XML::Simple module provides a simple API layer on top of an underlying XML parsing module.


Install XML::Simple module using cpan or ppm utilities. After Installation process completed, check for ParserDetails.ini file (For eg C:\Perl\site\lib\XML\SAX folder) if file is not present then create a new file (ParserDetails.ini) and add below mentioned two lines.

[XML::SAX::PurePerl]

http://xml.org/sax/features/namespaces = 1

#===========================#
Sample Xml File :  sample_sales.xml

#-----Perl Program-----#

# use module

#Easy API to maintain XML
use XML::Simple;
use DBI;

# declare variable
my $Xml_File_Path = "c:\\perlprog\\sample_sales.xml";

# Check file exist and not empty
if ( -z $Xml_File_Path) {
print "File Doesn't Exist!";
exit 1;
}

# create XML::Simple parser object
my $xml = new XML::Simple;

# read XML file
#XMLin() accepts an optional XML specifier followed by zero or more 'name => value' option pairs.
#Parses XML formatted data and returns a reference to a data structure which contains the same #information in a more readily accessible form.
# ForceArray = force the contents of all elements to be parsed as arrays instead of hashes.
# KeyAttr = which applies to XMLin() and XMLout() to name attributes, or sub-element
#as keys to be used to promote the parent element from array to hash.
#Remember that there is default list: "name", "key", and "id".

my $Doc = $xml->XMLin( $Xml_File_Path,
                                           ForceArray => 1,
                                           KeyAttr => {},
                                         );

# create connection with Char database (Chart is data source name)
my $dbh = DBI->connect('dbi:Chart:') or die "Cannot connect\n";

# Once connection is established, we want to create bar graph so create bar table with required fields
$dbh->do('CREATE TABLE bars (quarter SMALLINT, East FLOAT, Southeast FLOAT, Midwest FLOAT, Southwest FLOAT, Northwest FLOAT)');

my $sth = $dbh->prepare('INSERT INTO bars VALUES(?, ?, ?, ?, ?, ?)');

# read xml data and and insert it into bar table

foreach my $sales (@{$Doc->{sales}}) {

# If you want to check output on command prompt else comment below mentioned lines
print $sales->{quarter}->[0], "\n";
print $sales->{East}->[0], "\n";
print $sales->{Southeast}->[0], "\n";
print $sales->{Midwest}->[0], "\n";
print $sales->{Southwest}->[0], "\n";
print $sales->{Northwest}->[0], "\n";

$sth->execute($sales->{quarter}->[0],$sales->{East}->[0],$sales->{Southeast}->[0],$sales->{Midwest}->[0],$sales->{Southwest}->[0],$sales->{Northwest}->[0]);

}

$sth->finish(); # free up resources

# chart Width, Height
# chat x axis field name (X_AXIS) and y axis field name (Y_AXIS) (we have two fields year and amount)
# TITLE (Title of Chart)
# COLOR (represent Color of line chart), SHOWPOINTS (if 1 display points on Line chart)
#SHOWVALUES=1 (if 1 display the value of points)
#THREE_D=1 (if 1, sets the 3-D effect on barchart)
#SIGNATURE='Copyright(C) 2010, LampSys'");

$rsth = $dbh->prepare( "SELECT BARCHART FROM bars WHERE WIDTH=700 AND HEIGHT=500 AND X_AXIS='Quarter' AND Y_AXIS='Revenue' AND TITLE = 'Quarterly Revenue By Region' AND THREE_D=1 AND SHOWVALUES=1 AND COLORS IN ('red', 'green', 'blue', 'yellow', 'dbrown') AND SIGNATURE='Copyright(C) 2011, LampSys'");

$rsth->execute;
#Binds a variable ($buf) with attributes (output columns) of a SELECT statement.
$rsth->bind_col(1, \$buf);
$rsth->fetch;
$rsth->finish(); #free up resources

#open a file (as PNG, JPEG, GIF) in binary mode
open(OUTF, '>Sales.png');
binmode OUTF;
print OUTF $buf;
close(OUTF);

#disconnect Database connection
$dbh->disconnect;

                                          #-----------------OutPut----------------------#

(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