GEDAFE-PEARLS(1)                    gedafe                    GEDAFE-PEARLS(1)



NNAAMMEE
       gedafe-pearls - Multilevel Reports for Gedafe

DDEESSCCRRIIPPTTIIOONN
       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 _*___r_e_p 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.

UUSSAAGGEE
       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 _/_p_a_t_h_/_t_o_/_p_e_a_r_l_s. 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
       _G_e_d_a_f_e_:_:_P_e_a_r_l.  A Pearl has to override the following methodes:

       info
           Returns two values. The name of the Pearl and a short description
           of the Pearl.

       template
           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: nnaammee, llaabbeell, wwiidd--
           ggeett, ddeeffaauulltt vvaalluuee, tteesstt rreeggeexxpp. The wwiiddggeettss are normal gedafe wid-
           gets as you would use in a meta table.

       run 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.

EEXXAAMMPPLLEE
       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($){
           #my $self = shift;
           return "Customers Orders","List all Orders of a particular customer";
        }

        # what information do I need to go into action
        sub template ($){
           #my $self = shift;
           # return a list of lists with the following elements
           #    name        desc        widget
           return [['start', 'Start Date (YYYY-MM-DD)', 'text',
                     date_print('month_first'),'\d+-\d+-\d+'],
                   ['end', 'End Date (YYYY-MM-DD)', 'text',
                     date_print('month_last'),'\d+-\d+-\d+' ],
                   ['customer', 'Customer', 'idcombo(combo=customer_combo)','','\d+' ],
                  ];
        }

        # check the PearlReports Documentation for details

        sub run ($$){
           my $self = shift;
           my $s = shift;
           $self->SUPER::run($s);
           # run the parent ( this will set the params)
           my $p = $self->{param};
           my $rep = DBIx::PearlReports::new
              (
               -handle => $s->{dbh},
               -query => <<SQL,

        SELECT customer_id,customer_name,
               order_id,order_date,order_qty,
               product_hid,product_description
        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

        SQL
               -param => [ $p->{customer},$p->{start},$p->{end}]

              );

        $rep->group
         ( -trigger => sub { $field{customer_id} },
           -head => sub { "Report for $field{customer_id} - $field{customer_name}\n".
                          "Date: $p->{start} - $p->{end}\n".
                          "-------------------------------------------------------------\n"},
           -foot => sub { "Total Items Shipped :".rpcnt($field{product_id})."\n" }
         );

        $rep->group
         ( -trigger => sub { $field{order_date} },
            -head => sub { Orders for $field{order_date}\n"}
         );

        $rep->body
           ( -contents => sub {
                sprintf "  %10d %7d %8s  %s\n",
                   $field{order_id},
                   $field{order_qty},
                   $field{product_hid},
                   $field{product_desc} } );

          return 'text/plain',
              join '', (map { defined $_ ? $_ : '' } $rep->makereport);
        }

        1;

SSEEEE AALLSSOO
       _g_e_d_a_f_e_-_s_q_l_._p_o_d, _g_e_d_a_f_e_-_t_e_m_p_l_a_t_e_s_._t_x_t, _T_e_x_t_:_:_C_P_P_T_e_m_p_l_a_t_e,
       _g_e_d_a_f_e_-_p_e_a_r_l_s_._p_o_d, _D_B_I_x_:_:_P_e_a_r_l_R_e_p_o_r_t_s

CCOOPPYYRRIIGGHHTT
       Copyright (c) 2000-2003 ETH Zurich, All rights reserved.

LLIICCEENNSSEE
       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 MER-
       CHANTABILITY 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.

AAUUTTHHOORR
       Tobias Oetiker <oetiker@ee.ethz.ch>



1.4.0                             2007-03-16                  GEDAFE-PEARLS(1)
