Database Clickable Charts

In this section, we will present an example that employs both database and image maps.

This example displays a bar chart that shows the revenue for the last 10 years. When the user clicks on a bar, it will "drill down" to another bar chart showing the monthly revenue for the year represented by the bar clicked. All data come from a database.

The code that creates the clickable bar chart for the last 10 years are as follows.

[CGI Version] perldemo_cgi\dbdemo3.pl

#!/usr/bin/perl

# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;

use perlchartdir;

# SQL statement to get the revenue for the 12 years from 1990 to 2001
my $SQL =
    "Select Sum(Software + Hardware + Services), Year(TimeStamp) From revenue Where ".
    "Year(TimeStamp) >= 1990 And Year(TimeStamp) <= 2001 Group By Year(TimeStamp) Order By ".
    "Year(TimeStamp)";

#
# Connect to database and read the query result into arrays
#
use DBI;
my $dbh = DBI->connect('dbi:mysql:sample', 'test', 'test');
my $sth = $dbh->prepare($SQL);
$sth->execute();

my $revenue = [];
my $timestamp = [];
while (my @row = $sth->fetchrow_array) {
    push @$revenue, $row[0];
    push @$timestamp, $row[1];
}
$dbh->disconnect;

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 360 pixels
my $c = new XYChart(600, 360);

# Set the plotarea at (60, 40) and of size 480 x 280 pixels. Use a vertical gradient color from
# light blue (eeeeff) to deep blue (0000cc) as background. Set border and grid lines to white
# (ffffff).
$c->setPlotArea(60, 40, 480, 280, $c->linearGradientColor(60, 40, 60, 280, 0xeeeeff, 0x0000cc), -1,
    0xffffff, 0xffffff);

# Add a title to the chart using 18pt Times Bold Italic font
$c->addTitle("Annual Revenue for Star Tech", "timesbi.ttf", 18);

# Add a multi-color bar chart layer using the supplied data
my $layer = $c->addBarLayer3($revenue);

# Use glass lighting effect with light direction from the left
$layer->setBorderColor($perlchartdir::Transparent, perlchartdir::glassEffect(
    $perlchartdir::NormalGlare, $perlchartdir::Left));

# Set the x axis labels
$c->xAxis()->setLabels($timestamp);

# Set y-axis tick density to 30 pixels. ChartDirector auto-scaling will use this as the guideline
# when putting ticks on the y-axis.
$c->yAxis()->setTickDensity(30);

# Synchronize the left and right y-axes
$c->syncYAxis();

# Set the y axes titles with 10pt Arial Bold font
$c->yAxis()->setTitle("USD (Millions)", "arialbd.ttf", 10);
$c->yAxis2()->setTitle("USD (Millions)", "arialbd.ttf", 10);

# Set all axes to transparent
$c->xAxis()->setColors($perlchartdir::Transparent);
$c->yAxis()->setColors($perlchartdir::Transparent);
$c->yAxis2()->setColors($perlchartdir::Transparent);

# Set the label styles of all axes to 8pt Arial Bold font
$c->xAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis2()->setLabelStyle("arialbd.ttf", 8);

# Create the image
my $chart1URL = $c->makeTmpFile("/tmp/tmpcharts");

# Create an image map for the chart
my $imageMap = $c->getHTMLImageMap("dbdemo3a.pl", "", "title='{xLabel}: US\$ {value|0}M'");

print "Content-type: text/html\n\n";
print <<EndOfHTML
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Database Clickable Charts
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt; font-family:verdana; width:600px; margin-bottom:20px">
    The example demonstrates creating a clickable chart using data from a database.
    Click on a bar below to "drill down" onto a particular year.
<br /><br />
<a href='viewsource.pl?file=$ENV{"SCRIPT_NAME"}'>
    View source code
</a>
</div>

<img src="getchart.pl?img=/tmp/tmpcharts/$chart1URL" border="0" usemap="#map1">
<map name="map1">
$imageMap
</map>

</body>
</html>
EndOfHTML
;

The above code first performs a database query and read the data into arrays. It then uses the data to create a bar chart. The chart is saved in a temporary file using BaseChart.makeTmpFile. An <IMG> tag is used to retrieve the chart with "getchart.pl?img=/tmp/tmpcharts/$chart1URL" as the URL. "getchart.pl" is a simple utility that comes with ChartDirector for retrieving images from files. The image map for the chart is created using BaseChart.getHTMLImageMap with "dbdemo3a.pl" as the handler.

NOTE: This sample script uses "getchart.pl" and assumes it is in the same directory as the script. When developing your own script using this sample script as a template, please ensure you copy "getchart.pl" to the same directory as your script. If you put "getchart.pl" in other directory, please adjust the paths in your script accordingly.

When the user clicks on the bar chart, "dbdemo3a.pl" will be invoked with a number of HTTP query parameters to indicate which bar the user has clicked. In particular, the xLabel parameter will contain the x-axis label for the bar clicked. With this parameter, "dbdemo3a.pl" can determine which year the user has clicked. It can then query the database for the data in that year, and produces the bar chart for that year.

In this example, "dbdemo3a.pl" will produce another clickable chart using "xystub.pl" as the handler.

[CGI Version] perldemo_cgi\dbdemo3a.pl

#!/usr/bin/perl

# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;

use perlchartdir;

# Get HTTP query parameters
use CGI;
my $query = new CGI;

#
# Displays the monthly revenue for the selected year. The selected year should be passed in as a
# query parameter called "xLabel"
#
my $selectedYear = ($query->param("xLabel") or 2001);

# SQL statement to get the monthly revenues for the selected year.
my $SQL =
    "Select Software, Hardware, Services From revenue Where Year(TimeStamp) = $selectedYear Order ".
    "By TimeStamp";

#
# Connect to database and read the query result into arrays
#

use DBI;
my $dbh = DBI->connect('dbi:mysql:sample', 'test', 'test');
my $sth = $dbh->prepare($SQL);
$sth->execute();

my $software = [];
my $hardware = [];
my $services = [];
while (my @row = $sth->fetchrow_array) {
    push @$software, $row[0];
    push @$hardware, $row[1];
    push @$services, $row[2];
}
$dbh->disconnect;

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 360 pixels
my $c = new XYChart(600, 360);

# Set the plotarea at (60, 50) and of size 480 x 270 pixels. Use a vertical gradient color from
# light blue (eeeeff) to deep blue (0000cc) as background. Set border and grid lines to white
# (ffffff).
$c->setPlotArea(60, 50, 480, 270, $c->linearGradientColor(60, 50, 60, 270, 0xeeeeff, 0x0000cc), -1,
    0xffffff, 0xffffff);

# Add a title to the chart using 15pt Times Bold Italic font
$c->addTitle("Global Revenue for Year $selectedYear", "timesbi.ttf", 18);

# Add a legend box at (60, 25) (top of the plotarea) with 9pt Arial Bold font
$c->addLegend(60, 25, 0, "arialbd.ttf", 9)->setBackground($perlchartdir::Transparent);

# Add a line chart layer using the supplied data
my $layer = $c->addLineLayer2();
$layer->addDataSet($software, 0xffaa00, "Software")->setDataSymbol($perlchartdir::CircleShape, 9);
$layer->addDataSet($hardware, 0x00ff00, "Hardware")->setDataSymbol($perlchartdir::DiamondShape, 11);
$layer->addDataSet($services, 0xff0000, "Services")->setDataSymbol(perlchartdir::Cross2Shape(), 11);

# Set the line width to 3 pixels
$layer->setLineWidth(3);

# Set the x axis labels. In this example, the labels must be Jan - Dec.
my $labels = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"];
$c->xAxis()->setLabels($labels);

# Set y-axis tick density to 30 pixels. ChartDirector auto-scaling will use this as the guideline
# when putting ticks on the y-axis.
$c->yAxis()->setTickDensity(30);

# Synchronize the left and right y-axes
$c->syncYAxis();

# Set the y axes titles with 10pt Arial Bold font
$c->yAxis()->setTitle("USD (Millions)", "arialbd.ttf", 10);
$c->yAxis2()->setTitle("USD (Millions)", "arialbd.ttf", 10);

# Set all axes to transparent
$c->xAxis()->setColors($perlchartdir::Transparent);
$c->yAxis()->setColors($perlchartdir::Transparent);
$c->yAxis2()->setColors($perlchartdir::Transparent);

# Set the label styles of all axes to 8pt Arial Bold font
$c->xAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis2()->setLabelStyle("arialbd.ttf", 8);

# Create the image and save it in a temporary location
my $chart1URL = $c->makeTmpFile("/tmp/tmpcharts");

# Create an image map for the chart
my $imageMap = $c->getHTMLImageMap("xystub.pl", "",
    "title='{dataSetName} @ {xLabel} = USD {value|0}M'");

print "Content-type: text/html\n\n";
print <<EndOfHTML
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Database Clickable Charts
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt; font-family:verdana; width:600px; margin-bottom:20px">
    You have click the bar for the year $selectedYear.
    Below is the "drill-down" chart showing the monthly details.
<br /><br />
<a href='viewsource.pl?file=$ENV{"SCRIPT_NAME"}'>
    View source code
</a>
</div>

<img src="getchart.pl?img=/tmp/tmpcharts/$chart1URL" border="0" usemap="#map1">
<map name="map1">
$imageMap
</map>

</body>
</html>
EndOfHTML
;

For demo purpose, "xystub.pl" simply displays information on what is clicked. It's source code is as follows.

[CGI Version] perldemo_cgi\xystub.pl

#!/usr/bin/perl

# Get HTTP query parameters
use CGI;
my $query = new CGI;

print "Content-type: text/html\n\n";
print <<EndOfHTML
<!DOCTYPE html>
<html>
<head>
    <title>Simple Clickable XY Chart Handler</title>
</head>
<body style="margin:5px 0px 0px 5px">
<div style="font:bold 18pt verdana;">
    Simple Clickable XY Chart Handler
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt verdana; margin-bottom:20px">
    <a href="viewsource.pl?file=$ENV{"SCRIPT_NAME"}">View Source Code</a>
</div>
<div style="font:10pt verdana;">
<b>You have clicked on the following chart element :</b><br />
<ul>
    <li>Data Set : @{[$query->param("dataSetName")]}</li>
    <li>X Position : @{[$query->param("x")]}</li>
    <li>X Label : @{[$query->param("xLabel")]}</li>
    <li>Data Value : @{[$query->param("value")]}</li>
</ul>
</div>
</body>
</html>
EndOfHTML
;