1 |
dpavlin |
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>> |