1 |
=head1 NAME |
2 |
|
3 |
gedafe-pearls - Multilevel Reports for Gedafe |
4 |
|
5 |
=head1 DESCRIPTION |
6 |
|
7 |
With a more or less complex select statement you can get about any data you |
8 |
want from the database. Gedafe allows you to present the result of such |
9 |
queries through the F<*_rep> views which are presented on the entry screen. |
10 |
|
11 |
What if you want to show a report listing all your customers, with their |
12 |
orders, grouped by date. A query will quickly return the relevant data, but |
13 |
the customer will be repeated over and over again in many records, also you |
14 |
might not want to show orders from ages back, but only orders from the last |
15 |
month, so prior to makeing the report you would want to specify a begin and |
16 |
end date. This is where Gedafe Pearls come in. |
17 |
|
18 |
Pearls are object oriented perl modules sitting in sepcial directory. Each |
19 |
module has to offer a set of methodes which will be used from within |
20 |
Gedafe. First to list the Pearl in the entry screen, then to produce a custom |
21 |
form to let the user customize the report (eg select the date range). And |
22 |
finally a methode to create the actual report. |
23 |
|
24 |
------------------------------------ |
25 |
| Custome Report | |
26 |
| -------------- | |
27 |
| | |
28 |
| 2 - Peter David | |
29 |
| | |
30 |
| Orders from: 2002-12-01 | |
31 |
| 1 Red Flower | |
32 |
| 12 Bottles of Wather | |
33 |
| | |
34 |
| Orders from: 2003-08-14 | |
35 |
| 3 Green Software Packages | |
36 |
| | |
37 |
| 8 - John Miller | |
38 |
| | |
39 |
| Orders from: 2001-09-29 | |
40 |
| 18 Candles | |
41 |
| | |
42 |
------------------------------------ |
43 |
|
44 |
For creating the report you can use the included PearlReports module which |
45 |
makes the creation of multilevel reports very simple. Check the separate documentation. |
46 |
|
47 |
=head1 USAGE |
48 |
|
49 |
First you have to tell Gedafe that it should go looking for Pearls. This is |
50 |
done by placing the line |
51 |
|
52 |
pearl_dir => '/path/to/pearls', |
53 |
|
54 |
into the Start command of your Gedafe CGI. Then you have to write a Pearl |
55 |
and store it in F</path/to/pearls>. When you restart gedafe it will go |
56 |
looking there and integrate all Pearls it finds in its Entry screen. |
57 |
|
58 |
Pearls are object oriented Perl modles which inherit from F<Gedafe::Pearl>. |
59 |
A Pearl has to override the following methodes: |
60 |
|
61 |
=over |
62 |
|
63 |
=item info |
64 |
|
65 |
Returns two values. The name of the Pearl and a short description of the Pearl. |
66 |
|
67 |
=item template |
68 |
|
69 |
Returns a pointer to a list of lists. It is used to build a form which is |
70 |
presented to the user to setup the report. Each element of the list is a |
71 |
list with the following elements: B<name>, B<label>, B<widget>, B<default |
72 |
value>, B<test regexp>. The B<widgets> are normal gedafe widgets as you would |
73 |
use in a meta table. |
74 |
|
75 |
=item run |
76 |
|
77 |
Returns two values. The first value is the content type, the second is the |
78 |
data to be returned to the user. |
79 |
|
80 |
The run methodes has access to the open database handle as well as to the |
81 |
data entered in the form generated with the template method. |
82 |
|
83 |
=back |
84 |
|
85 |
See the example below for details. |
86 |
|
87 |
=head1 EXAMPLE |
88 |
|
89 |
Commented example Pearl. |
90 |
|
91 |
package demo; |
92 |
|
93 |
use strict; |
94 |
use Gedafe::Pearl qw(format_desc date_print); |
95 |
use vars qw(@ISA); |
96 |
@ISA = qw(Gedafe::Pearl); |
97 |
|
98 |
use DBIx::PearlReports; |
99 |
|
100 |
sub new($;@){ |
101 |
my $proto = shift; |
102 |
my $class = ref($proto) || $proto; |
103 |
my $self = $class->SUPER::new(@_); |
104 |
return $self; |
105 |
} |
106 |
|
107 |
sub info($){ |
108 |
#my $self = shift; |
109 |
return "Customers Orders","List all Orders of a particular customer"; |
110 |
} |
111 |
|
112 |
# what information do I need to go into action |
113 |
sub template ($){ |
114 |
#my $self = shift; |
115 |
# return a list of lists with the following elements |
116 |
# name desc widget |
117 |
return [['start', 'Start Date (YYYY-MM-DD)', 'text', |
118 |
date_print('month_first'),'\d+-\d+-\d+'], |
119 |
['end', 'End Date (YYYY-MM-DD)', 'text', |
120 |
date_print('month_last'),'\d+-\d+-\d+' ], |
121 |
['customer', 'Customer', 'idcombo(combo=customer_combo)','','\d+' ], |
122 |
]; |
123 |
} |
124 |
|
125 |
# check the PearlReports Documentation for details |
126 |
|
127 |
sub run ($$){ |
128 |
my $self = shift; |
129 |
my $s = shift; |
130 |
$self->SUPER::run($s); |
131 |
# run the parent ( this will set the params) |
132 |
my $p = $self->{param}; |
133 |
my $rep = DBIx::PearlReports::new |
134 |
( |
135 |
-handle => $s->{dbh}, |
136 |
-query => <<SQL, |
137 |
|
138 |
SELECT customer_id,customer_name, |
139 |
order_id,order_date,order_qty, |
140 |
product_hid,product_description |
141 |
FROM customer,order,product |
142 |
WHERE order_product=product_id |
143 |
AND customer_id = ? |
144 |
AND order_customer = customer_id |
145 |
AND order_date >= ? |
146 |
AND order_date <= ? |
147 |
ORDER BY customer_id,order_date,order_id |
148 |
|
149 |
SQL |
150 |
-param => [ $p->{customer},$p->{start},$p->{end}] |
151 |
|
152 |
); |
153 |
|
154 |
$rep->group |
155 |
( -trigger => sub { $field{customer_id} }, |
156 |
-head => sub { "Report for $field{customer_id} - $field{customer_name}\n". |
157 |
"Date: $p->{start} - $p->{end}\n". |
158 |
"-------------------------------------------------------------\n"}, |
159 |
-foot => sub { "Total Items Shipped :".rpcnt($field{product_id})."\n" } |
160 |
); |
161 |
|
162 |
$rep->group |
163 |
( -trigger => sub { $field{order_date} }, |
164 |
-head => sub { Orders for $field{order_date}\n"} |
165 |
); |
166 |
|
167 |
$rep->body |
168 |
( -contents => sub { |
169 |
sprintf " %10d %7d %8s %s\n", |
170 |
$field{order_id}, |
171 |
$field{order_qty}, |
172 |
$field{product_hid}, |
173 |
$field{product_desc} } ); |
174 |
|
175 |
return 'text/plain', |
176 |
join '', (map { defined $_ ? $_ : '' } $rep->makereport); |
177 |
} |
178 |
|
179 |
1; |
180 |
|
181 |
=head1 SEE ALSO |
182 |
|
183 |
F<gedafe-sql.pod>, F<gedafe-templates.txt>, F<Text::CPPTemplate>, F<gedafe-pearls.pod>, |
184 |
F<DBIx::PearlReports> |
185 |
|
186 |
=head1 COPYRIGHT |
187 |
|
188 |
Copyright (c) 2000-2003 ETH Zurich, All rights reserved. |
189 |
|
190 |
=head1 LICENSE |
191 |
|
192 |
This program is free software; you can redistribute it and/or modify |
193 |
it under the terms of the GNU General Public License as published by |
194 |
the Free Software Foundation; either version 2 of the License, or |
195 |
(at your option) any later version. |
196 |
|
197 |
This program is distributed in the hope that it will be useful, |
198 |
but WITHOUT ANY WARRANTY; without even the implied warranty of |
199 |
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
200 |
GNU General Public License for more details. |
201 |
|
202 |
You should have received a copy of the GNU General Public License |
203 |
along with this program; if not, write to the Free Software |
204 |
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. |
205 |
|
206 |
=head1 AUTHOR |
207 |
|
208 |
S<Tobias Oetiker E<lt>oetiker@ee.ethz.chE<gt>> |