..Gedafe Pearls
Mailing List
Page Maintainer:
Tobias Oetiker

Last Update:
Gedafe Pearl Reports


gedafe-pearls - Multilevel Reports for Gedafe


With a more or less complex select statement you can get about any data you want from the database. Gedafe allows you to present the result of such queries through the *_rep views which are presented on the entry screen.

What if you want to show a report listing all your customers, with their orders, grouped by date. A query will quickly return the relevant data, but the customer will be repeated over and over again in many records, also you might not want to show orders from ages back, but only orders from the last month, so prior to makeing the report you would want to specify a begin and end date. This is where Gedafe Pearls come in.

Pearls are object oriented perl modules sitting in sepcial directory. Each module has to offer a set of methodes which will be used from within Gedafe. First to list the Pearl in the entry screen, then to produce a custom form to let the user customize the report (eg select the date range). And finally a methode to create the actual report.

 | Custome Report                     |
 | --------------                     |
 |                                    |
 | 2 - Peter David                    |
 |                                    |
 |     Orders from: 2002-12-01        |
 |         1 Red Flower               |
 |        12 Bottles of Wather        |
 |                                    |
 |     Orders from: 2003-08-14        |
 |         3 Green Software Packages  |
 |                                    |
 | 8 - John Miller                    |
 |                                    |
 |     Orders from: 2001-09-29        |
 |        18 Candles                  |
 |                                    |
For creating the report you can use the included PearlReports module which
makes the creation of multilevel reports very simple. Check the separate documentation.


First you have to tell Gedafe that it should go looking for Pearls. This is done by placing the line

 pearl_dir      => '/path/to/pearls',

into the Start command of your Gedafe CGI. Then you have to write a Pearl and store it in /path/to/pearls. When you restart gedafe it will go looking there and integrate all Pearls it finds in its Entry screen.

Pearls are object oriented Perl modles which inherit from Gedafe::Pearl. A Pearl has to override the following methodes:

Returns two values. The name of the Pearl and a short description of the Pearl.
Returns a pointer to a list of lists. It is used to build a form which is presented to the user to setup the report. Each element of the list is a list with the following elements: name, label, widget, default value, test regexp. The widgets are normal gedafe widgets as you would use in a meta table.
Returns two values. The first value is the content type, the second is the data to be returned to the user.

The run methodes has access to the open database handle as well as to the data entered in the form generated with the template method.

See the example below for details.


Commented example Pearl.

 package demo;
 use strict;
 use Gedafe::Pearl qw(format_desc date_print);
 use vars qw(@ISA);
 @ISA = qw(Gedafe::Pearl);
 use DBIx::PearlReports;
 sub new($;@){
     my $proto = shift;
     my $class = ref($proto) || $proto;
     my $self = $class->SUPER::new(@_);
     return $self;
 sub info($){
    return "Customers Orders","List all Orders of a particular customer";
 sub template ($){
    return [['start', 'Start Date (YYYY-MM-DD)', 'text',
            ['end', 'End Date (YYYY-MM-DD)', 'text',
              date_print('month_last'),'\d+-\d+-\d+' ],
            ['customer', 'Customer', 'idcombo(combo=customer_combo)','','\d+' ],
 sub run ($$){
    my $self = shift;
    my $s = shift;
    my $p = $self->{param};
    my $rep = DBIx::PearlReports::new
        -handle => $s->{dbh},
        -query => <<SQL,
 SELECT customer_id,customer_name,
 FROM customer,order,product
 WHERE order_product=product_id
      AND customer_id = ?
      AND order_customer = customer_id
      AND order_date >= ?
      AND order_date <= ?
 ORDER BY customer_id,order_date,order_id
        -param => [ $p->{customer},$p->{start},$p->{end}]
  ( -trigger => sub { $field{customer_id} },
    -head => sub { "Report for $field{customer_id} - $field{customer_name}\n".
                   "Date: $p->{start} - $p->{end}\n".
    -foot => sub { "Total Items Shipped :".rpcnt($field{product_id})."\n" }
  ( -trigger => sub { $field{order_date} },
     -head => sub { Orders for $field{order_date}\n"}
    ( -contents => sub {
         sprintf "  %10d %7d %8s  %s\n",
            $field{product_desc} } );
   return 'text/plain',
       join '', (map { defined $_ ? $_ : '' } $rep->makereport);


gedafe-sql.pod, gedafe-templates.txt, Text::CPPTemplate, gedafe-pearls.pod, DBIx::PearlReports


Copyright (c) 2000-2003 ETH Zurich, All rights reserved.


This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.


Tobias Oetiker <tobi@oetiker.ch>