CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Need assistance in creating a chart

 



terrykhatri531
User

Jul 27, 2014, 7:27 AM

Post #1 of 9 (916 views)
Need assistance in creating a chart Can't Post

Hi,

I need assistance in linking the values driven from database to X and Y axis i.e. as per following script to variable $data and $lables, please help me putting this right - Many thanks, Terry.


Code
 
#!/usr/local/bin/perl

use strict;
use warnings;
use perlchartdir;
use DBI;

my $dbh = dbh();

$dbh->do("SET search_path to northwind") or die;

my $sql = qq! SELECT
a."FirstName"::TEXT || ' ' ||a."LastName" AS "Name",
ROUND(SUM((b."UnitPrice" - b."Discount") * b."Quantity")) AS "Sales"
FROM "Employees" a, "Order_Details" b, "Orders" c
WHERE a."EmployeeID" = c."EmployeeID" AND
b."OrderID" = c."OrderID"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10!;
my $sth=$dbh->prepare("$sql");
$sth->execute() || quit();
while (my $ref = $sth->fetchrow_hashref()) {

my $empname = $ref->{'Name'};
my $sales = $ref->{'Sales'};
}


#The data for the bar chart
my $data = $sales;
#my $data = [450, 560, 630, 800, 1100, 1350, 1600, 1950, 2300, 2700];

#The labels for the bar chart
my $labels = $empname;
#my $labels = ["1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003", "2004", "2005"];


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

#Add a title to the chart using 18pts Times Bold Italic font
$c->addTitle("Top Ten Employees", "timesbi.ttf", 18);

# Set the plotarea at (60, 40) and of size 500 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, 500, 280, $c->linearGradientColor(60, 40, 60, 280, 0xeeeeff,
0x0000cc), -1, 0xffffff, 0xffffff);

# Add a multi-color bar chart layer using the supplied data. Use soft lighting effect
# # with light direction from left.
$c->addBarLayer3($data)->setBorderColor($perlchartdir::Transparent,
perlchartdir::softLighting($perlchartdir::Left));

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

# Draw the ticks between label positions (instead of at label positions)
$c->xAxis()->setTickOffset(0.5);

# Add a title to the y axis with 10pts Arial Bold font
$c->yAxis()->setTitle("USD (millions)", "arialbd.ttf", 10);

# Set axis label style to 8pts Arial Bold
$c->xAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis()->setLabelStyle("arialbd.ttf", 8);

# Set axis line width to 2 pixels
$c->xAxis()->setWidth(2);
$c->yAxis()->setWidth(2);

# Output the chart
$c->makeChart("top10emps.png");

# connect to database
sub dbh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $dbh;
}



FishMonger
Veteran / Moderator

Jul 27, 2014, 8:13 AM

Post #2 of 9 (914 views)
Re: [terrykhatri531] Need assistance in creating a chart [In reply to] Can't Post

Declare $empname and $name OR $data and $lables before the while loop and assign them inside the loop.


terrykhatri531
User

Jul 27, 2014, 9:19 AM

Post #3 of 9 (906 views)
Re: [FishMonger] Need assistance in creating a chart [In reply to] Can't Post

Hi Ron/Chris

Now I am getting the follow error:


Code
 
Error converting argument 1 to type 11DoubleArray
*** Stack back trace ***
calling XYChart.addBarLayer3 at top10emps.pl:49

[/coe]

Line 49 is :


Code
 
$c->addBarLayer3($data)->setBorderColor($perlchartdir::Transparent,
perlchartdir::softLighting($perlchartdir::Left));


Please help, many many thanks !!!

Terry


terrykhatri531
User

Jul 27, 2014, 9:33 AM

Post #4 of 9 (903 views)
Re: [terrykhatri531] Need assistance in creating a chart [In reply to] Can't Post

We have to get data in the following format I don't know how to to that :



my $data = [450, 560, 630, 800, 1100, 1350, 1600, 1950, 2300, 2700]; in our case its $sales

my $labels = ["1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003", "2004", "2005"]; in our case its $empname








FishMonger
Veteran / Moderator

Jul 27, 2014, 10:27 AM

Post #5 of 9 (901 views)
Re: [terrykhatri531] Need assistance in creating a chart [In reply to] Can't Post

Then you need to fetch the data from the db as an array not a hash.


(This post was edited by FishMonger on Jul 27, 2014, 10:30 AM)


FishMonger
Veteran / Moderator

Jul 27, 2014, 10:38 AM

Post #6 of 9 (896 views)
Re: [terrykhatri531] Need assistance in creating a chart [In reply to] Can't Post


Code
my $empname = []; 
my $sales = [];

while (my $row = $sth->fetchrow_array) {
push @$empname, $row[0];
push @$sales, $row[1];
}



(This post was edited by FishMonger on Jul 27, 2014, 10:40 AM)


terrykhatri531
User

Jul 27, 2014, 11:09 AM

Post #7 of 9 (893 views)
Re: [FishMonger] Need assistance in creating a chart [In reply to] Can't Post

Hi Ron, thanks very much, now I am getting the following error:


Code
  
Global symbol "@row" requires explicit package name at test1.pl line 27.
Global symbol "@row" requires explicit package name at test1.pl line 28.


Lines 27 and 28 are :


Code
  
while (my $row = $sth->fetchrow_array) {
push @$empname, $row[0];
push @$sales, $row[1];


And if I add my @row ; at the top the chart comes up empty !!

Here is the revised script:


Code
  
#!/usr/local/bin/perl

use strict;
use warnings;
use perlchartdir;
use DBI;

my $dbh = dbh();
my $empname = [];
my $sales = [];

$dbh->do("SET search_path to northwind") or die;

my $sql = qq! SELECT
a."FirstName"::TEXT || ' ' ||a."LastName" AS "Name",
ROUND(SUM((b."UnitPrice" - b."Discount") * b."Quantity")) AS "Sales"
FROM "Employees" a, "Order_Details" b, "Orders" c
WHERE a."EmployeeID" = c."EmployeeID" AND
b."OrderID" = c."OrderID"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10!;
my $sth=$dbh->prepare("$sql");

$sth->execute() || quit();

while (my $row = $sth->fetchrow_array) {
push @$empname, $row[0];
push @$sales, $row[1]; }


#The data for the bar chart
#my $data = [450, 560, 630, 800, 1100, 1350, 1600, 1950, 2300, 2700];
my $data = $sales;


#The labels for the bar chart
#my $labels = ["1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003", "2004", "2005"];
my $labels = $empname;

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

#Add a title to the chart using 18pts Times Bold Italic font
$c->addTitle("Top Ten Employees", "timesbi.ttf", 18);

# Set the plotarea at (60, 40) and of size 500 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, 500, 280, $c->linearGradientColor(60, 40, 60, 280, 0xD0D0D0,
0x909090), -1, 0xffffff, 0xffffff);

# Add a multi-color bar chart layer using the supplied data. Use soft lighting effect
# # with light direction from left.
$c->addBarLayer3($data)->setBorderColor($perlchartdir::Transparent,
perlchartdir::softLighting($perlchartdir::Left));

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

# Draw the ticks between label positions (instead of at label positions)
$c->xAxis()->setTickOffset(0.5);

# Add a title to the y axis with 10pts Arial Bold font
$c->yAxis()->setTitle("USD (millions)", "arialbd.ttf", 10);

# Set axis label style to 8pts Arial Bold

$c->xAxis()->setLabelStyle("arialbd.ttf", 8);
$c->yAxis()->setLabelStyle("arialbd.ttf", 8);

# Set axis line width to 2 pixels
$c->xAxis()->setWidth(2);
$c->yAxis()->setWidth(2);

# Output the chart
$c->makeChart("top10emps.png");

# connect to database
sub dbh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $dbh;







And when I print the Arrays to see what comes up, here's what comes up :


Code
  
Array for empname ARRAY(0x2121fc0)
Array for Sales ARRAY(0x259c578)


I checked the database, the query gets executed fine.


Many many thanks for your help.

Terry


(This post was edited by terrykhatri531 on Jul 27, 2014, 11:27 AM)


Zhris
Enthusiast

Jul 27, 2014, 12:24 PM

Post #8 of 9 (885 views)
Re: [terrykhatri531] Need assistance in creating a chart [In reply to] Can't Post


Code
while (my $row = $sth->fetchrow_array) { 
while (my @row = $sth->fetchrow_array) {



terrykhatri531
User

Jul 27, 2014, 12:48 PM

Post #9 of 9 (881 views)
Re: [Zhris] Need assistance in creating a chart [In reply to] Can't Post

Thank you very very much Chris and Ron, it works now.

Rgds.

Terry

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives