SQLCrosstab.pm

filter: Statementstmt 23.5% 142 / 602 Branchbran 17.3% 48 / 276 Conditioncond 21.5% 14 / 65 MC/DCmcdc 4.7% 2 / 42 Subroutinesub 41.9% 13 / 31 Podpod 57.1% 8 / 14 totaltotal 22.0% 227 / 1030 CC 211 SCAR 63.8
CC
211
Cov
22%
get_query
1640.0
get_recs
1114.2
_get_xvalues
812.0
CRAP 587.5
↑ Summary SQLCrosstab/Format.pm »
1 package DBIx::SQLCrosstab;
2 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use Data::Dumper;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 1 ▶use Tree::DAG_Node;
Subroutine BEGIN: called CC=1 SCAR=0.0
7  
8 1 our $VERSION = '1.17';
9 # 07-Jan-2004
10  
11 1 require 5.006;
12  
13 1 require Exporter;
14 1 our @ISA= qw(Exporter);
15 1 our @EXPORT=qw();
16 1 our @EXPORT_OK=qw();
17  
18 1 our $errstr = "";
19 1 my $_RaiseError = 0;
20 1 my $_PrintError = 0;
21  
22 1 my %_xkeywords = (
23 dbh => 1,
24 rows => 1,
25 cols => 1,
26 op => 1,
27 op_col => 0, # DEPRECATED - KEPT for backward compatibility
28 from => 1,
29  
30 add_op => 0, # DEPRECATED - KEPT for backward compatibility
31 records => 0,
32 col_names => 0,
33  
34 where => 0,
35 having => 0,
36 title => 0,
37 remove_if_null => 0,
38 remove_if_zero => 0,
39 row_total => 0,
40 row_sub_total => 0,
41 col_total => 0,
42 col_sub_total => 0,
43 col_exclude => 0,
44  
45 complete_html_page => 0,
46 only_html_header => 0,
47  
48 add_colors => 0,
49 text_color => 0,
50 number_color => 0,
51 header_color => 0,
52 footer_color => 0,
53 table_border => 0,
54 table_cellspacing => 0,
55 table_cellpadding => 0,
56 commify => 0,
57 title_in_header => 0,
58  
59 add_real_names => 0,
60 use_real_names => 0,
61 RaiseError => 0,
62 PrintError => 0,
63 );
64  
65 1 my %_rowkeywords = (
66 col => 1,
67 alias => 0
68 );
69  
70 1 my %_colkeywords = (
71 id => 1,
72 from => 1,
73 value => 0,
74 group => 0,
75 exclude_value => 0,
76 where => 0,
77 orderby => 0,
78 col_list => 0,
79 );
80  
81 1 my $_stub = {
82 dbh => {dsn=>"dbi:ExampleP:test"},
83 op => [ [ 'COUNT' => 'dummy' ] ],
84 from => 'dummy',
85 cols => [ {id => 'dummy', from => 'dummy'}],
86 rows => [ {col => 'dummy'}],
87 };
88  
89 1 my @_operations = map {qr/^\s*$_\s*$/i}
90 (qw(count sum avg std var max min));
91  
92 2 ▶sub new {
Subroutine new: called CC=8 SCAR=22.0
Pod documented
93 2 my $class = shift;
94 2 my $opt = shift;
95 2 my $self = bless {
96 }, $class;
97 2 ▶ return seterr("Parameters required in $class constructor")
Branch: 1 branch
BranchTrueFalseTotal
unless $opt 0 2 2
98 unless $opt;
99 2 ▶ if (ref $opt eq 'HASH') {
Branch: 2 branches
BranchTrueFalseTotal
if (ref $opt eq 'HASH') { } 1 1 2
elsif ($opt =~ /^stub$/i) { } 1 0 1
100 1 for (keys %$opt) {
101 13 $self->{ $_} = $opt->{$_};
102 }
103 }
104 elsif ($opt =~/^stub$/i) {
105 1 for (keys %$_stub) {
106 5 $self->{$_} = $_stub->{$_}
107 }
108 }
109 2 ▶ if ($self->_check_allowed) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->_check_allowed) 2 0 2
110 2 ▶ if ($self->{RaiseError}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"RaiseError"}) 0 2 2
111 0 $_RaiseError = 1;
112 }
113 2 return $self->_check_required
114 }
115 0 return undef;
116 }
117  
118 #
119 # set_param( cols => [{id => 'mycol', from => 'mytable'}] )
120 #
121  
122 1 ▶sub set_param {
Subroutine set_param: called CC=4 SCAR=14.2
Pod documented
123 1 my $self = shift;
124 1 ▶ while (@_) {
Branch: 1 branch
BranchTrueFalseTotal
if (@_) 1 1 2
125 1 ▶ return seterr("odd number of parameters in set_param")
Branch: 1 branch
BranchTrueFalseTotal
unless 2 <= scalar @_ 0 1 1
126 unless 2 <= scalar(@_) ;
127 1 my $param = shift;
128 1 my $value = shift;
129 1 ▶ if (exists $_xkeywords{$param}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $_xkeywords{$param}) { } 1 0 1
130 1 $self->{$param} = $value;
131 }
132 else {
133 0 return seterr("unrecognized parameter $param ");
134 }
135 }
136 1 return $self->_check_required;
137 }
138  
139 0 ▶sub op_list{
Subroutine op_list: not called CC=1 SCAR=6.9
Pod undocumented
140 0 my $self = shift;
141 0 return join ",", map { uc( $_->[0]) ."($_->[1])" } @{$self->{op}};
142 }
143  
144 0 ▶sub op {
Subroutine op: not called CC=1 SCAR=6.9
Pod undocumented
145 0 my $self = shift;
146 0 return join ",", map { uc $_->[0] } @{$self->{op}};
147 }
148  
149 0 ▶sub op_col {
Subroutine op_col: not called CC=1 SCAR=6.9
Pod undocumented
150 0 my $self = shift;
151 0 return join ",", map { $_->[1] } @{$self->{op}};
152 }
153  
154 1 ▶sub get_params {
Subroutine get_params: called CC=5 SCAR=16.1
Pod documented
155 1 my $self = shift;
156 1 my $params_name = "params";
157 1 my %params =();
158 1 for (keys %_xkeywords) {
159 35 ▶ next if /^dbh$/;
Branch: 1 branch
BranchTrueFalseTotal
if /^dbh$/ 1 34 35
160 34 ▶ if (exists $self->{$_} and defined($self->{$_})) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $self->{$_} and defined $self->{$_}) 12 22 34
Condition66%2/3
exists $self->{$_} and defined $self->{$_}
!ll&&!rl&&r
22012
MC/DC50%1/2
exists $self->{$_} and defined $self->{$_}
exists $self->{$_} defined $self->{$_}
Truth table66%2/3
A and B
A= exists $self->{$_}B= defined $self->{$_}
ABresult
0X0
100
111
161 12 $params{$_} = $self->{$_};
162 }
163 }
164 1 local $Data::Dumper::Indent = 1;
165 1 return Data::Dumper->Dump([\%params],[$params_name]);
166 }
167  
168 1 ▶sub save_params {
Subroutine save_params: called CC=4 SCAR=14.5
Pod documented
169 1 my $self = shift;
170 1 ▶ my $param_file_name = shift || "xtab_params.pl";
Condition50%1/2
shift() || 'xtab_params.pl'
l!l
10
MC/DC0%0/1
shift() || 'xtab_params.pl'
shift()
Truth table50%1/2
A
A= shift()
Aresult
11
00
171 1 ▶ open PARAMS, "> $param_file_name"
Branch: 1 branch
BranchTrueFalseTotal
open PARAMS, "> $param_file_name" or return seterr("can't open $param_file_name") 1 0 1
172 or return seterr("can't open $param_file_name");
173 1 print PARAMS $self->get_params();
174 1 ▶ close(PARAMS) or return seterr("can't close $param_file_name");
Branch: 1 branch
BranchTrueFalseTotal
close PARAMS or return seterr("can't close $param_file_name") 1 0 1
175 1 return 1;
176 }
177  
178 1 ▶sub load_params {
Subroutine load_params: called CC=11 SCAR=26.0
Pod documented
179 1 my $self = shift;
180 1 my $params_file_name = shift;
181 1 my $params = undef;
182 1 ▶ return seterr("filename required to load_params()")
Branch: 1 branch
BranchTrueFalseTotal
unless $params_file_name 0 1 1
183 unless $params_file_name;
184 1 ▶ open PARAMS, "< $params_file_name"
Branch: 1 branch
BranchTrueFalseTotal
open PARAMS, "< $params_file_name" or return seterr("can't open $params_file_name") 1 0 1
185 or return seterr("can't open $params_file_name");
186 1 $errstr = undef;
187 {
188 1 local $/;
189 1 my $value = <PARAMS>;
190 1 ▶ if ($value) {
Branch: 1 branch
BranchTrueFalseTotal
if ($value) { } 1 0 1
191 1 eval $value;
192 1 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 1 1
193 0 seterr("error retrieving parameters from $params_file_name");
194 }
195 1 ▶ seterr("no params found in $params_file_name")
Branch: 1 branch
BranchTrueFalseTotal
unless $params 0 1 1
196 unless $params;
197 }
198 else {
199 0 seterr("no params found in $params_file_name")
200 }
201 }
202 1 ▶ close(PARAMS) or return seterr("can't close $params_file_name");
Branch: 1 branch
BranchTrueFalseTotal
close PARAMS or return seterr("can't close $params_file_name") 1 0 1
203 1 ▶ return undef if $errstr;
Branch: 1 branch
BranchTrueFalseTotal
if $DBIx::SQLCrosstab::errstr 0 1 1
204 1 ▶ return seterr("invalid parameters in $params_file_name")
Branch: 1 branch
BranchTrueFalseTotal
unless ref $params eq "HASH" 0 1 1
205 unless ref($params) eq 'HASH';
206 1 for (keys %$params) {
207 return seterr("unrecognized option ($_) in file $params_file_name")
208 12 ▶ unless exists $_xkeywords{$_};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $_xkeywords{$_} 0 12 12
209 12 $self->{$_} = $params->{$_};
210 }
211 1 return $self->_check_required;
212 }
213  
214 0 ▶sub seterr {
Subroutine seterr: not called CC=4 SCAR=30.0
Pod undocumented
215 0 ▶ my $msg = shift || "-- no msg --";
Condition0%0/2
shift() || '-- no msg --'
l!l
00
MC/DC0%0/1
shift() || '-- no msg --'
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
216 0 $errstr = $msg ;
217 0 ▶ if ($_RaiseError) {
Branch: 2 branches
BranchTrueFalseTotal
if ($_RaiseError) { } 0 0 0
elsif ($_PrintError) { } 0 0 0
218 0 die "$msg\n ";
219 }
220 elsif ($_PrintError) {
221 0 warn "$msg\n";
222 }
223 0 return undef;
224 }
225  
226 2 ▶sub _check_allowed{
Subroutine _check_allowed: called CC=7 SCAR=24.4
227 2 my $self = shift;
228 2 ▶ $_RaiseError = (exists($self->{RaiseError}) && $self->{RaiseError} ) ;
Condition33%1/3
exists $self->{'RaiseError'} && $self->{'RaiseError'}
!ll&&!rl&&r
200
MC/DC0%0/2
exists $self->{'RaiseError'} && $self->{'RaiseError'}
exists $self->{'RaiseError'} $self->{'RaiseError'}
Truth table33%1/3
A && B
A= exists $self->{'RaiseError'}B= $self->{'RaiseError'}
ABresult
0X0
100
111
229 2 ▶ $_PrintError = (exists($self->{PrintError}) && $self->{PrintError});
Condition33%1/3
exists $self->{'PrintError'} && $self->{'PrintError'}
!ll&&!rl&&r
200
MC/DC0%0/2
exists $self->{'PrintError'} && $self->{'PrintError'}
exists $self->{'PrintError'} $self->{'PrintError'}
Truth table33%1/3
A && B
A= exists $self->{'PrintError'}B= $self->{'PrintError'}
ABresult
0X0
100
111
230 2 for (keys %$self) {
231 return seterr("unrecognized option '$_'")
232 18 ▶ unless defined $_xkeywords{$_} ;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $_xkeywords{$_} 0 18 18
233 }
234 2 ▶ if ($self->{col_exclude}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"col_exclude"}) 0 2 2
235 return seterr ("list required with parameter 'col_exclude'")
236 0 ▶ unless ref $self->{col_exclude} eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $self->{"col_exclude"} eq "ARRAY" 0 0 0
237 }
238 2 return $self;
239 }
240  
241 0 ▶sub _check_required_kw {
Subroutine _check_required_kw: not called CC=3 SCAR=24.8
242 0 my ($set, $kw, $opt) = @_;
243 0 for (grep {$kw->{$_}} keys %$kw)
244 {
245 return seterr("required $opt '$_' not defined")
246 0 ▶ unless defined $set->{$_}
Branch: 1 branch
BranchTrueFalseTotal
unless defined $set->{$_} 0 0 0
247 }
248 0 return $set;
249 }
250  
251 4 ▶sub _check_required {
Subroutine _check_required: called CC=38 SCAR=51.3
252 4 my $self = shift;
253 4 for (grep {$_xkeywords{$_}} keys %_xkeywords) {
254 return seterr("required option '$_' not defined")
255 20 ▶ unless defined $self->{$_};
Branch: 1 branch
BranchTrueFalseTotal
unless defined $self->{$_} 0 20 20
256 }
257 4 ▶ if (defined $self->{dbh}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $self->{'dbh'}) { } 4 0 4
258 4 ▶ if ( ref($self->{dbh})
Branch: 2 branches
BranchTrueFalseTotal
if (ref $self->{'dbh'} and ref($self->{'dbh'}) =~ /DBI::db/) { } 1 3 4
elsif (ref $self->{'dbh'} and ref $self->{'dbh'} eq 'HASH') { } 3 0 3
Condition66%2/3
ref $self->{"dbh"} and ref($self->{"dbh"}) =~ /DBI::db/
!ll&&!rl&&r
031
MC/DC50%1/2
ref $self->{"dbh"} and ref($self->{"dbh"}) =~ /DBI::db/
ref $self->{"dbh"} ref($self->{"dbh"}) =~ /DBI::db/
Truth table66%2/3
A and B
A= ref $self->{"dbh"}B= ref($self->{"dbh"}) =~ /DBI::db/
ABresult
0X0
100
111
259 && ( (ref $self->{dbh}) =~ 'DBI::db' ))
260 {
261 # OK
262 }
263 ▶ elsif (ref($self->{dbh})
Condition33%1/3
ref $self->{"dbh"} and ref $self->{"dbh"} eq "HASH"
!ll&&!rl&&r
003
MC/DC0%0/2
ref $self->{"dbh"} and ref $self->{"dbh"} eq "HASH"
ref $self->{"dbh"} ref $self->{"dbh"} eq "HASH"
Truth table33%1/3
A and B
A= ref $self->{"dbh"}B= ref $self->{"dbh"} eq "HASH"
ABresult
0X0
100
111
264 && ( ref( $self->{dbh}) eq "HASH"))
265 {
266 3 my $par = $self->{dbh};
267 3 my $dbh;
268 3 eval {$dbh = DBI->connect($par->{dsn},
269 $par->{user}, $par->{password},
270 3 ▶ $par->{params}) or die "$DBI::errstr"
Branch: 1 branch
BranchTrueFalseTotal
$dbh = "DBI"->connect($par->{"dsn"}, $par->{"user"}, $par->{"password"}, $par->{"params"}) or die "$DBI::errstr" 3 0 3
271 };
272 3 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) { } 0 3 3
273 0 return seterr("error in connection $@")
274 }
275 else {
276 3 $self->{dbh} = $dbh;
277 }
278 }
279 else
280 {
281 0 return seterr("invalid \$dbh parameter")
282 }
283 }
284 else {
285 0 return seterr("\$dbh parameter required")
286 }
287 4 for my $row (@{$self->{rows}}) {
288 6 for (grep {$_rowkeywords{$_}} keys %_rowkeywords) {
289 return seterr(
290 "missing required parameter ($_) in row definition")
291 unless exists $row->{$_}
292 6 ▶ && defined $row->{$_}
Branch: 1 branch
BranchTrueFalseTotal
unless exists $row->{$_} and defined $row->{$_} 0 6 6
Condition33%1/3
exists $row->{$_} and defined $row->{$_}
!ll&&!rl&&r
006
MC/DC0%0/2
exists $row->{$_} and defined $row->{$_}
exists $row->{$_} defined $row->{$_}
Truth table33%1/3
A and B
A= exists $row->{$_}B= defined $row->{$_}
ABresult
0X0
100
111
293 }
294 6 for (keys %$row) {
295 return seterr("unrecognized row parameter ($_)")
296 8 ▶ unless exists $_rowkeywords{$_};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $_rowkeywords{$_} 0 8 8
297 }
298 }
299 4 for my $col (@{$self->{cols}}) {
300 8 for (grep {$_colkeywords{$_}} keys %_colkeywords) {
301 return seterr(
302 "missing required parameter ($_) in column definition")
303 unless exists $col->{$_}
304 16 ▶ && defined $col->{$_}
Branch: 1 branch
BranchTrueFalseTotal
unless exists $col->{$_} and defined $col->{$_} 0 16 16
Condition33%1/3
exists $col->{$_} and defined $col->{$_}
!ll&&!rl&&r
0016
MC/DC0%0/2
exists $col->{$_} and defined $col->{$_}
exists $col->{$_} defined $col->{$_}
Truth table33%1/3
A and B
A= exists $col->{$_}B= defined $col->{$_}
ABresult
0X0
100
111
305 }
306 8 for (keys %$col) {
307 return seterr("unrecognized row parameter ($_)")
308 22 ▶ unless exists $_colkeywords{$_};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $_colkeywords{$_} 0 22 22
309 }
310 }
311 4 my $op_allowed = 0;
312
313 4 ▶ unless ( ref($self->{op}) )
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $self->{"op"}) 0 4 4
314 # compatibility code for {op}
315 {
316 0 my $tmpop;
317 return seterr("Parameter 'op_col' undefined")
318 0 ▶ unless defined $self->{op_col};
Branch: 1 branch
BranchTrueFalseTotal
unless defined $self->{"op_col"} 0 0 0
319 0 push @$tmpop, [ $self->{op}, $self->{op_col}];
320 0 ▶ if ($self->{add_op}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"add_op"}) 0 0 0
321 return seterr("Parameter 'add_op' must be an array reference")
322 unless (ref($self->{add_op})
323 0 ▶ && (ref($self->{add_op}) eq 'ARRAY'));
Branch: 1 branch
BranchTrueFalseTotal
unless ref $self->{"add_op"} and ref $self->{"add_op"} eq "ARRAY" 0 0 0
Condition0%0/3
ref $self->{"add_op"} and ref $self->{"add_op"} eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
ref $self->{"add_op"} and ref $self->{"add_op"} eq "ARRAY"
ref $self->{"add_op"} ref $self->{"add_op"} eq "ARRAY"
Truth table0%0/3
A and B
A= ref $self->{"add_op"}B= ref $self->{"add_op"} eq "ARRAY"
ABresult
0X0
100
111
324 0 for my $aop(@{$self->{add_op}}) {
325 0 push @$tmpop, [$aop, $self->{op_col}];
326 }
327 0 delete $self->{add_op};
328 }
329 0 delete $self->{op_col};
330 0 $self->{op} = $tmpop;
331 }
332  
333 return seterr("Parameter 'op' must be an array reference")
334 4 ▶ unless ref($self->{op}) eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $self->{"op"} eq "ARRAY" 0 4 4
335 4 for my $op (@{$self->{op}}) {
336 return
337 4 ▶ seterr("All items in parameter {op} must be array references")
Branch: 1 branch
BranchTrueFalseTotal
unless ref $op and ref $op eq "ARRAY" 0 4 4
Condition33%1/3
ref $op and ref $op eq "ARRAY"
!ll&&!rl&&r
004
MC/DC0%0/2
ref $op and ref $op eq "ARRAY"
ref $op ref $op eq "ARRAY"
Truth table33%1/3
A and B
A= ref $opB= ref $op eq "ARRAY"
ABresult
0X0
100
111
338 unless (ref($op) && (ref($op) eq 'ARRAY'));
339 4 for my $item (@_operations) {
340 return seterr("unrecognized operator $op->[0]")
341 28 ▶ unless grep { $op->[0] =~ $_ } @_operations;
Branch: 1 branch
BranchTrueFalseTotal
unless grep {$op->[0] =~ /$_/i;} @_operations 0 28 28
342 0 ▶ return seterr("Invalid opertator definition (@{$op})")
Branch: 1 branch
BranchTrueFalseTotal
unless @$op eq 2 0 28 28
343 unless @$op eq 2;
344 }
345 4 ▶ if (scalar @{$self->{op}} > 1) {
Branch: 1 branch
BranchTrueFalseTotal
if (scalar @{$self->{"op"};} > 1) 0 4 4
346 0 $self->{col_total} = 0;
347 }
348 }
349
350 #for my $op (@_operations) {
351 # if ($self->{op} =~ $op ) {
352 # $op_allowed =1;
353 # last;
354 # }
355 #}
356 #return seterr("operation not allowed (" . $self->{op} . ")")
357 # unless $op_allowed;
358 #if ($self->{add_op}) {
359 # if (ref $self->{add_op} eq 'ARRAY') {
360 # my %seen =();
361 # my @ops = grep {
362 # ($_ ne $self->{op})
363 # and (not $seen{$_}++) } @{$self->{add_op}};
364 # if (@ops) {
365 # $self->{col_total} = 0;
366 # $self->{add_op} = \@ops;
367 # }
368 # else {
369 # $self->{add_op} = undef;
370 # }
371 # }
372 # elsif (lc($self->{add_op}) eq lc($self->{op}))
373 # {
374 # $self->{add_op} = undef;
375 # }
376 # else {
377 # $self->{col_total} = 0;
378 # }
379 #}
380 4 ▶ if ($self->{add_real_names} and $self->{use_real_names}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"add_real_names"} and $self->{"use_real_names"}) 0 4 4
Condition33%1/3
$self->{"add_real_names"} and $self->{"use_real_names"}
!ll&&!rl&&r
400
MC/DC0%0/2
$self->{"add_real_names"} and $self->{"use_real_names"}
$self->{"add_real_names"} $self->{"use_real_names"}
Truth table33%1/3
A and B
A= $self->{"add_real_names"}B= $self->{"use_real_names"}
ABresult
0X0
100
111
381 0 $self->{add_real_names} = 0;
382 }
383 4 return $self;
384 }
385  
386 # _permute function written by Randal L. Schwartz,
387 # aka merlyn
388 # http://www.perlmonks.org/index.pl?node_id=24270
389 #
390 0 ▶sub _permute {
Subroutine _permute: not called CC=2 SCAR=17.9
391 0 my $last = pop @_;
392 0 ▶ unless (@_) {
Branch: 1 branch
BranchTrueFalseTotal
unless (@_) 0 0 0
393 0 return map [$_], @$last;
394 }
395 0 return map { my $left = $_; map [@$left, $_], @$last } _permute(@_);
396 }
397  
398 # _permute_group is not permuting anything, actually,
399 # since the data coming from the distinct query
400 # is already a permutation. The only task performed here
401 # is returning the appropriate structure.
402 0 ▶sub _permute_group {
Subroutine _permute_group: not called CC=2 SCAR=17.9
403 0 my $array = shift;
404 0 my @permutations;
405 0 for my $row (@$array) {
406 0 push @permutations, [map { {xcol_id => $_, xcol_alias=> $_} } @$row ];
407 }
408 0 return \@permutations;
409 }
410  
411 # _add_values fills the tree to create
412 # the appropriate permutations
413 #
414 0 ▶sub _add_values {
Subroutine _add_values: not called CC=4 SCAR=30.0
415 0 my ($top, $array, $level) = @_;
416 0 ▶ return if $level > $#$array;
Branch: 1 branch
BranchTrueFalseTotal
if $level > $array->$#* 0 0 0
417 0 my $values = $array->[$level];
418 $top->new_daughter
419 0 ->attributes( {contents => $_} ) for @$values;
420 0 _add_values($_, $array, $level+1) for $top->daughters;
421 }
422  
423 # _add_group_values fills the tree without
424 # permutations, which were already found
425 # in the DISTINCT query
426 #
427 0 ▶sub _add_group_values {
Subroutine _add_group_values: not called CC=5 SCAR=34.0
428 0 my ($tr, $array) = @_;
429 0 for my $row (@$array) {
430 0 my $top = $tr;
431 0 for my $col(@$row) {
432 0 my $node = undef;
433 0 ▶ if ($top->daughters) {
Branch: 1 branch
BranchTrueFalseTotal
if ($top->daughters) 0 0 0
434 0 ($node) = grep {$_->name eq $col} $top->daughters;
435 }
436 0 ▶ unless ($node) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($node) 0 0 0
437 0 $node = Tree::DAG_Node->new;
438 0 $node->name($col);
439 0 $node->attributes( {contents => {
440 xcol_id => $col, xcol_alias=>$col
441 }} );
442 0 $top->add_daughter($node)
443 }
444 0 $top = $node;
445 }
446 }
447 }
448  
449 #
450 # _xpermute creates a permutation tree
451 #
452 0 ▶sub _xpermute {
Subroutine _xpermute: not called CC=4 SCAR=30.0
453 0 my $array = shift;
454 0 ▶ my $mode = shift || "normal";
Condition0%0/2
shift() || 'normal'
l!l
00
MC/DC0%0/1
shift() || 'normal'
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
455 0 my $tree = Tree::DAG_Node->new;
456 0 $tree->name('xtab');
457 0 ▶ if ($mode eq "normal") {
Branch: 2 branches
BranchTrueFalseTotal
if ($mode eq 'normal') { } 0 0 0
elsif ($mode eq 'group') { } 0 0 0
458 0 _add_values($tree, $array, 0);
459 }
460 elsif ($mode eq "group") {
461 0 _add_group_values ($tree,$array)
462 }
463 else {
464 0 return seterr("unrecognized tree-filling mode ($mode)");
465 }
466 #print map {"$_\n"} @{$tree->draw_ascii_tree}; exit;
467 0 my @permuted;
468 $tree->walk_down (
469 {
470 0 ▶ callbackback => sub {
Subroutine __ANON__: not called CC=2 SCAR=17.9
471 0 my $node = shift;
472 0 ▶ return 1 unless $node->ancestors;
Branch: 1 branch
BranchTrueFalseTotal
unless $node->ancestors 0 0 0
473 push @permuted,
474 [
475 reverse
476 0 map {$_->attributes->{contents}}
477 0 grep {$_->address ne '0'}
478 $node, $node->ancestors
479 ];
480 0 1;
481 }
482 }
483 0 );
484 0 $tree->delete_tree;
485 0 return \@permuted;
486 }
487  
488 0 ▶sub from {
Subroutine from: not called CC=2 SCAR=17.9
Pod documented
489 0 my $self = shift;
490 0 my $val = shift;
491 0 ▶ if ($val) {
Branch: 1 branch
BranchTrueFalseTotal
if ($val) 0 0 0
492 0 $self->{from} = $val;
493 0 $self->rows($self->{rows});
494 }
495 0 return $self->{from};
496 }
497  
498 0 ▶sub rows {
Subroutine rows: not called CC=2 SCAR=17.9
Pod undocumented
499 0 my $self = shift;
500 0 my $val = shift;
501 0 ▶ if ($val) {
Branch: 1 branch
BranchTrueFalseTotal
if ($val) 0 0 0
502 0 $self->{rows} = $val;
503 0 $self->{query} = "";
504 0 $self->{recs} = undef;
505 0 $self->columns($self->{cols});
506 }
507 0 return $self->{rows};
508 }
509  
510 0 ▶sub columns {
Subroutine columns: not called CC=2 SCAR=17.9
Pod undocumented
511 0 my $self = shift;
512 0 my $val = shift;
513 0 ▶ if ($val) {
Branch: 1 branch
BranchTrueFalseTotal
if ($val) 0 0 0
514 0 $self->{cols} = $val;
515 0 $self->{xvalues} = undef;
516 }
517 0 return $self->{cols};
518 }
519  
520 # _check_query_separator_applicability checks
521 # if the query separator is present in any of the
522 # column values, changing the separator if
523 # necessary
524 #
525 0 ▶sub _check_query_separator_applicability {
Subroutine _check_query_separator_applicability: not called CC=15 SCAR=54.8
526 0 my $self = shift;
527 0 my $permutations =shift;
528 0 my $separator = shift;
529 0 my %words =();
530 0 for my $row (@{$self->{rows}}) {
531 0 for (qw(id alias value)) {
532 0 ▶ if (exists $row->{$_}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $row->{$_}) 0 0 0
533 0 $words{$row->{$_}}++;
534 }
535 }
536 }
537 0 for my $p (@$permutations) {
538 0 $words{$_}++ for @$p;
539 }
540 0 ▶ if ($separator =~ /[a-z?*+]/i) {
Branch: 1 branch
BranchTrueFalseTotal
if ($separator =~ /[a-z?*+]/i) 0 0 0
541 0 $separator ='#';
542 }
543 0 my @separators = ( '#', '/', '-', '=', ',');
544 0 ▶ unless (grep {$_ eq $separator} @separators) {
Branch: 1 branch
BranchTrueFalseTotal
unless (grep {$_ eq $separator;} @separators) 0 0 0
545 0 unshift @separators, $separator;
546 }
547 0 my $ok = 0;
548 0 my $count =0;
549 0 ▶ while (! $ok) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 0 0 0
550 SEPARATOR:
551 0 for my $sep (@separators) {
552 0 $ok =1;
553 0 for my $k (keys %words) {
554 0 ▶ if ($k =~ /\Q$sep\E/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($k =~ /\Q$sep\E/) 0 0 0
555 0 $ok =0;
556 0 next SEPARATOR;
557 }
558 }
559 0 ▶ if ($ok) {
Branch: 1 branch
BranchTrueFalseTotal
if ($ok) 0 0 0
560 0 $separator = $sep;
561 0 last;
562 }
563 }
564 0 ▶ if (! $ok) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 0 0 0
565 0 @separators = map {$_ . substr($_,0,1) } @separators;
566 0 ▶ if ($count++ > 3 ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($count++ > 3) 0 0 0
567 0 return seterr("unable to find a suitable column separator char");
568 }
569 }
570 }
571 0 return $separator;
572 }
573  
574 #
575 # gets the values for the column headers
576 #
577 0 ▶sub _get_xvalues {
Subroutine _get_xvalues: not called CC=28 SCAR=67.0
578 0 my $self = shift;
579 0 my $dbh = $self->{dbh};
580  
581 0 my @xvalues;
582 0 $self->{xvalues} = undef;
583 #
584 # group values required
585 # columns are evaluated in a unique query
586 # rather than separately
587 #
588 0 ▶ if (grep {exists $_->{group}} @{$self->{cols}})
Branch: 1 branch
BranchTrueFalseTotal
if (grep {exists $_->{'group'};} @{$$self{'cols'};}) { } 0 0 0
589 {
590 0 my $colquery = qq{SELECT DISTINCT };
591 0 my $fieldlist = "";
592  
593 $fieldlist = join ", ", map {
594 0 ▶ $_->{id} . (exists $_->{alias}? " AS $_->{alias}" : "") }
Branch: 1 branch
BranchTrueFalseTotal
exists $_->{'alias'} ? : 0 0 0
595 0 @{$self->{cols}};
596 0 $colquery .= "$fieldlist\n";
597 0 my ($from) = map {$_->{from}}
598 0 ▶ grep {$_->{from} and ($_->{from} ne "1") }
Condition0%0/3
$_->{'from'} && $_->{'from'} ne '1'
!ll&&!rl&&r
000
MC/DC0%0/2
$_->{'from'} && $_->{'from'} ne '1'
$_->{'from'} $_->{'from'} ne '1'
Truth table0%0/3
A && B
A= $_->{'from'}B= $_->{'from'} ne '1'
ABresult
0X0
100
111
599 0 @{$self->{cols}};
600 0 $from =~ s/^\s*from//i;
601 0 $colquery .= qq{ FROM $from\n};
602 0 my ($orderby) = map {$_->{orderby}}
603 0 grep {$_->{orderby} }
604 0 @{$self->{cols}};
605 0 ▶ if ($orderby) {
Branch: 1 branch
BranchTrueFalseTotal
if ($orderby) 0 0 0
606 0 $orderby =~ s/^\s*order by//i;
607 0 $colquery .= qq{ ORDER BY $orderby\n}
608 }
609 0 my $sth;
610 my $colrecs;
611 #print $colquery,$/;
612 0 eval {
613 0 $sth = $dbh->prepare($colquery);
614 0 $sth->execute;
615 };
616 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
617 0 return seterr("Error building group column query ($@)");
618 }
619 0 eval {
620 0 $colrecs = $sth->fetchall_arrayref;
621 };
622 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
623 0 return seterr("Error retrieving group column records ($@)");
624 }
625 0 $self->{colrecs} = $colrecs;
626 0 for my $r (@$colrecs) {
627 0 my $count =0;
628 0 for my $c (@$r) {
629 0 push @{$xvalues[$count++]}, { xcol_id => $_ , xcol_alias => $_};
630 }
631 }
632 0 $self->{use_group} = 1;
633 }
634 else
635 # column values
636 # are retrieved separately
637 # and stored in a bi-dimensional array
638 #
639 {
640 0 for (@{$self->{cols}}) {
641 0 my $xvals;
642 0 ▶ my $xcol_alias = exists $_->{alias} ? $_->{alias} : "xcol_alias";
Branch: 1 branch
BranchTrueFalseTotal
exists $_->{'alias'} ? : 0 0 0
643 #
644 # if a list of values is provided
645 # then no query is issued, but the values are
646 # simply stored in the array
647 #
648 0 ▶ if ($_->{col_list}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_->{'col_list'}) { } 0 0 0
649 0 my $list = $_->{col_list};
650 0 ▶ unless (ref $list eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $list eq "ARRAY") 0 0 0
651 0 return seterr("list of values expected in parameter 'col_list'");
652 }
653 0 for my $val (@$list) {
654 0 ▶ return seterr("elements in {col_list} must be hash references")
Branch: 1 branch
BranchTrueFalseTotal
unless ref $val eq "HASH" 0 0 0
655 unless (ref $val eq 'HASH');
656 return seterr("elements in {col_list} must have an {id} key")
657 0 ▶ unless (defined $val->{id} );
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val->{"id"} 0 0 0
658 0 ▶ unless (defined $val->{value}) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $val->{"value"}) 0 0 0
659 0 $val->{value} = $val->{id};
660 }
661 0 push @$xvals, {xcol_id=> $val->{id}, $xcol_alias => $val->{value} };
662 }
663 }
664 else
665 # normal operation
666 # The values are retrieved from the database
667 # with a query
668 {
669 0 my $fields = qq[$_->{id} AS xcol_id];
670 0 ▶ if (exists $_->{value}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $_->{"value"}) 0 0 0
671 0 $fields .= qq[, $_->{value} AS $xcol_alias] ;
672 }
673 0 my $colquery = qq[SELECT DISTINCT $fields FROM $_->{from}];
674 0 ▶ if (exists $_->{where} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $_->{"where"}) 0 0 0
675 0 $_->{where} =~ s/^\s*where\b//i;
676 0 $colquery .= " WHERE ". $_->{where} ." ";
677 }
678 0 ▶ if (exists $_->{orderby} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $_->{"orderby"}) 0 0 0
679 0 $_->{orderby} =~ s/^\s*order\s+by\b//i;
680 0 $colquery .= " ORDER BY ". $_->{orderby} ." ";
681 }
682 0 my $sth;
683 0 eval {
684 0 $sth = $dbh->prepare($colquery);
685 0 $sth->execute;
686 };
687 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
688 0 return seterr
689 "error while retrieving column values for $_->{id}\n"
690 . qq(query: "$colquery"\n)
691 . "error: $DBI::errstr\n";
692 }
693 0 eval { $xvals = $sth->fetchall_arrayref({}) };
694 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
695 0 return seterr("Error while fetching column values "
696 ."($DBI::errstr)");
697 }
698 0 ▶ unless (exists $_->{value}) {
Branch: 1 branch
BranchTrueFalseTotal
unless (exists $_->{"value"}) 0 0 0
699 0 for my $row (@$xvals) {
700 0 $row->{$xcol_alias} = $row->{xcol_id};
701 }
702 }
703 }
704 #
705 # remove values if required
706 #
707 0 ▶ if ($_->{exclude_value}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_->{"exclude_value"}) 0 0 0
708 0 ▶ unless (ref $_->{exclude_value} eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $_->{"exclude_value"} eq "ARRAY") 0 0 0
709 0 return seterr(
710 "list of value expected in parameter"
711 . " '\$cols->{exclude_value}'");
712 }
713 0 my @copy_xvals;
714 0 my %exclude_value = map {$_, 1} @{$_->{exclude_value}};
715 @copy_xvals = grep {
716 0 not (exists ($exclude_value{$_->{xcol_id}})
717 0 ▶ or exists ($exclude_value{ $_->{$xcol_alias} }) )
Condition0%0/3
exists $exclude_value{$_->{'xcol_id'}} || exists $exclude_value{$_->{$xcol_alias}}
l!l&&r!l&&!r
000
MC/DC0%0/2
exists $exclude_value{$_->{'xcol_id'}} || exists $exclude_value{$_->{$xcol_alias}}
exists $exclude_value{$_->{'xcol_id'}} exists $exclude_value{$_->{$xcol_alias}}
Truth table0%0/3
A || B
A= exists $exclude_value{$_->{'xcol_id'}}B= exists $exclude_value{$_->{$xcol_alias}}
ABresult
1X1
011
000
718 } @$xvals;
719 0 $xvals = \@copy_xvals;
720 }
721 0 push @xvalues, $xvals;
722 0 ▶ my $label = exists $_->{value} ? $_->{value} : $_->{id};
Branch: 1 branch
BranchTrueFalseTotal
exists $_->{'value'} ? : 0 0 0
723 0 push @{$self->{xvalues}}, { label => $label, value => $xvals};
724 }
725 }
726 0 return \@xvalues;
727 }
728  
729 0 ▶sub get_query {
Subroutine get_query: not called CC=40 SCAR=74.0
Pod documented
730 0 my $self = shift;
731 0 ▶ my $separator = shift || '#';
Condition0%0/2
shift() || '#'
l!l
00
MC/DC0%0/1
shift() || '#'
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
732 0 ▶ return undef unless $self->_check_required;
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_check_required 0 0 0
733 0 my $dbh = $self->{dbh};
734  
735 0 ▶ my $xvalues = $self->_get_xvalues
Branch: 1 branch
BranchTrueFalseTotal
my $xvalues = $self->_get_xvalues or return undef 0 0 0
736 or return undef; # seterr is already called with the real reason
737  
738 0 for my $row ( @{$self->{rows}} ) {
739 $row->{alias} = $row->{col} unless $row->{alias}
740 0 ▶ }
Branch: 1 branch
BranchTrueFalseTotal
unless $row->{"alias"} 0 0 0
741 my $xrows = join ", ",
742 0 map { "$_->{col} AS $_->{alias}" }
743 0 @{$self->{rows}};
744  
745 0 my $query = qq{SELECT $xrows \n};
746  
747 0 my @xcols;
748  
749 my @permutations;
750 0 ▶ if ($self->{use_group}) {
Branch: 2 branches
BranchTrueFalseTotal
if ($self->{'use_group'}) { } 0 0 0
elsif ($self->{'col_sub_total'}) { } 0 0 0
751 0 ▶ if ( $self->{col_sub_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{'col_sub_total'}) { } 0 0 0
752 0 @permutations = @{_xpermute($self->{colrecs}, "group")};
753 }
754 else {
755 0 @permutations = @{_permute_group( $self->{colrecs} ) };
756 }
757 }
758 elsif ($self->{col_sub_total}) {
759 0 @permutations = @{_xpermute($xvalues)};
760 }
761 else {
762 0 @permutations = _permute(@$xvalues);
763 }
764 #for (@permutations) { print "@$_\n"; } exit;
765 $self->{query_separator} =
766 0 ▶ $self->_check_query_separator_applicability(\@permutations, $separator)
Branch: 1 branch
BranchTrueFalseTotal
$self->{"query_separator"} = $self->_check_query_separator_applicability(\@permutations, $separator) or return undef 0 0 0
767 or return undef;
768  
769 0 my %realnames =();
770 0 my $col_count ="xfld001";
771  
772 0 for my $op_pair (@{$self->{op}})
773 {
774 0 my ($operator, $opcolumn) = @$op_pair;
775 0 for my $val (@permutations)
776 {
777 0 my @cn = @{$self->{cols}};
778 my $condition = join " AND ",
779 map {
780 0 "$cn[$_]->{id} = "
781 0 . ($dbh->quote($val->[$_]->{xcol_id}))
782 ." "
783 }
784 (0 .. $#$val);
785 my $name = join $self->{query_separator},
786 0 map {$val->[$_]->{xcol_alias}}
787 (0..$#$val);
788 next if ($self->{col_exclude} and
789 0 ▶ ( grep {$name eq $_} @{$self->{col_exclude}} ));
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"col_exclude"} and grep {$name eq $_;} @{$$self{"col_exclude"};} 0 0 0
Condition0%0/3
$self->{"col_exclude"} and grep {$name eq $_;} @{$$self{"col_exclude"};}
!ll&&!rl&&r
000
MC/DC0%0/2
$self->{"col_exclude"} and grep {$name eq $_;} @{$$self{"col_exclude"};}
$self->{"col_exclude"} grep {$name eq $_;} @{$$self{"col_exclude"};}
Truth table0%0/3
A and B
A= $self->{"col_exclude"}B= grep {$name eq $_;} @{$$self{"col_exclude"};}
ABresult
0X0
100
111
790 0 ▶ if ($self->{check_group} and $self->{keepcols}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"check_group"} and $self->{"keepcols"}) 0 0 0
Condition0%0/3
$self->{"check_group"} and $self->{"keepcols"}
!ll&&!rl&&r
000
MC/DC0%0/2
$self->{"check_group"} and $self->{"keepcols"}
$self->{"check_group"} $self->{"keepcols"}
Truth table0%0/3
A and B
A= $self->{"check_group"}B= $self->{"keepcols"}
ABresult
0X0
100
111
791 0 ▶ next unless grep { $name =~ /^$_/ } @{$self->{keepcols}};
Branch: 1 branch
BranchTrueFalseTotal
unless grep {$name =~ /^$_/;} @{$$self{"keepcols"};} 0 0 0
792 }
793 #
794 # name manipulation
795 #
796 0 ▶ if (@{$self->{op}} > 1 ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$self->{"op"};} > 1) 0 0 0
797 $name = "x" . lc($operator)
798 0 . $self->{query_separator} . $name ;
799 }
800 0 ▶ if ($self->{use_real_names}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{'use_real_names'}) { } 0 0 0
801 0 $name = $dbh->quote($name);
802 }
803 else {
804 0 $realnames{$col_count} = $name;
805 0 $name = $col_count;
806 0 $col_count++;
807 }
808 #
809 #
810 #
811 0 my $line =
812 qq[,$operator(CASE WHEN $condition THEN ]
813 .qq[ $opcolumn ELSE NULL END) AS $name ] ;
814 0 ▶ if ($self->{add_real_names} )
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{'add_real_names'}) { } 0 0 0
815 {
816 0 $line .= qq[ -- ($realnames{$name}) \n];
817 }
818 else {
819 0 $line .= "\n";
820 }
821  
822 0 push @xcols, $line;
823 }
824 0 ▶ if ((@{$self->{op}} > 1) and $self->{col_sub_total})
Branch: 1 branch
BranchTrueFalseTotal
if (@{$self->{"op"};} > 1 and $self->{"col_sub_total"}) 0 0 0
Condition0%0/3
@{$self->{"op"};} > 1 and $self->{"col_sub_total"}
!ll&&!rl&&r
000
MC/DC0%0/2
@{$self->{"op"};} > 1 and $self->{"col_sub_total"}
@{$self->{"op"};} > 1 $self->{"col_sub_total"}
Truth table0%0/3
A and B
A= @{$self->{"op"};} > 1B= $self->{"col_sub_total"}
ABresult
0X0
100
111
825 {
826 0 my $opname = "x".lc($operator);
827 0 my $line = qq[,$operator($opcolumn) AS $opname\n];
828 0 push @xcols, $line;
829 }
830 }
831 0 ▶ if (@{$self->{op}} > 1) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$self->{"op"};} > 1) 0 0 0
832 0 unshift @{$self->{cols}}, { id => 'op', value=>'op',
833 0 col_list => [map {"x". lc($_->[0])} @{$self->{op}} ] }
834 }
835 0 $self->{realnames} = \%realnames;
836  
837 0 $self->{from} =~ s/^\s*from\b//i;
838 0 ▶ if ($self->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"where"}) 0 0 0
839 0 $self->{where} =~ s/^\s*where\b//i;
840 }
841 0 ▶ if ($self->{having}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"having"}) 0 0 0
842 0 $self->{having} =~ s/^\s*having\b//i;
843 }
844  
845 0 $query .= $_ for @xcols;
846 0 my $total1 = 'total';
847 0 ▶ if ($self->{col_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"col_total"}) 0 0 0
848 0 my ($operator, $opcolumn) = @{$self->{op}->[0]};
849 0 $query .= qq[,$operator($opcolumn) AS $total1\n];
850 }
851 0 $query .= qq[ FROM $self->{from}\n ];
852 0 ▶ if ($self->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"where"}) 0 0 0
853 0 $query.= " WHERE ". $self->{where} . " \n";
854 }
855 $query .= qq[GROUP BY ]
856 0 . join(", ", map {$_->{alias}} @{$self->{rows}})
857 . "\n";
858  
859 0 ▶ if ($self->{having}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"having"}) 0 0 0
860 0 $query.= " HAVING ". $self->{having} . " \n";
861 }
862  
863 0 my $numrows = @{$self->{rows}} -1;
864 0 my $nr = $numrows;
865 0 ▶ if ($self->{row_sub_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"row_sub_total"}) 0 0 0
866 0 for my $row (0..$numrows -1) {
867 $xrows = join ", ",
868 map {
869 0 my $val = $self->{rows}->[$_]->{col};
870 0 ▶ $val ="'zzzz'" if $_ >= $nr;
Branch: 1 branch
BranchTrueFalseTotal
if $_ >= $nr 0 0 0
871 0 "$val AS $self->{rows}->[$_]->{alias}"
872 }
873 (0..$numrows);
874 0 $nr--;
875 0 $query .= qq{UNION\n SELECT $xrows \n};
876 0 $query .= $_ for @xcols;
877 0 ▶ if ($self->{col_total} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"col_total"}) 0 0 0
878 0 my ($operator, $opcolumn) = @{$self->{op}->[0]};
879 0 $query .= qq[,$operator($opcolumn) AS $total1\n];
880 }
881 0 $query .= qq[ FROM $self->{from}\n ];
882 $xrows = join ", ",
883 map {
884 0 $self->{rows}->[$_]->{alias}
885 0 }
886 (0 .. $nr);
887  
888 0 ▶ if ($self->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"where"}) 0 0 0
889 0 $query.= " WHERE ". $self->{where} . " \n";
890 }
891 0 $query .= qq{GROUP BY $xrows\n};
892 0 ▶ if ($self->{having}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"having"}) 0 0 0
893 0 $query.= " HAVING ". $self->{having} . " \n";
894 }
895 }
896 }
897  
898 0 ▶ if ($self->{row_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"row_total"}) 0 0 0
899 $xrows = join ", ",
900 0 map {"'zzzz' AS $self->{rows}->[$_]->{alias}" }
901 (0..$numrows);
902 0 $query .= qq{UNION\n SELECT $xrows\n};
903 0 $query .= $_ for @xcols;
904 0 ▶ if ($self->{col_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"col_total"}) 0 0 0
905 0 my ($operator, $opcolumn) = @{$self->{op}->[0]};
906 0 $query .= qq[,$operator($opcolumn) AS $total1\n];
907 }
908 0 $query .= qq[ FROM $self->{from}\n ];
909  
910 0 ▶ if ($self->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"where"}) 0 0 0
911 0 $query.= " WHERE ". $self->{where} . " \n";
912 }
913 }
914  
915 $xrows = join ", ",
916 0 map { $self->{rows}->[$_]->{alias} } (0..$numrows);
917 0 $query .= qq[ORDER BY $xrows\n];
918 0 $query =~ s/ +/ /g;
919 0 $query =~ s/\n\s*\n/\n/sg;
920 0 $query =~ s/^ +//g;
921 0 $self->{query} = $query;
922 0 return $query;
923 }
924  
925 0 ▶sub _max {
Subroutine _max: not called CC=3 SCAR=24.8
926 0 my $max = 0;
927 0 for (@_) {
928 0 ▶ $max = $_ if $_ > $max;
Branch: 1 branch
BranchTrueFalseTotal
if $_ > $max 0 0 0
929 }
930 0 return $max;
931 }
932  
933 2 ▶sub get_recs {
Subroutine get_recs: called CC=38 SCAR=70.2
Pod documented
934 2 my $self = shift;
935 2 ▶ if ($self->{records} && $self->{col_names}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"records"} and $self->{"col_names"}) 2 0 2
Condition33%1/3
$self->{"records"} and $self->{"col_names"}
!ll&&!rl&&r
002
MC/DC0%0/2
$self->{"records"} and $self->{"col_names"}
$self->{"records"} $self->{"col_names"}
Truth table33%1/3
A and B
A= $self->{"records"}B= $self->{"col_names"}
ABresult
0X0
100
111
936 return seterr("Parameter 'col_names' must be an array reference")
937 2 ▶ unless (ref($self->{col_names}) eq 'ARRAY');
Branch: 1 branch
BranchTrueFalseTotal
unless ref $self->{"col_names"} eq "ARRAY" 0 2 2
938 return seterr("Parameter 'records' must be an array reference")
939 unless (ref($self->{records}) eq 'ARRAY')
940 2 ▶ and (ref($self->{records}->[0]) eq 'ARRAY');
Branch: 1 branch
BranchTrueFalseTotal
unless ref $self->{"records"} eq "ARRAY" and ref $self->{"records"}[0] eq "ARRAY" 0 2 2
Condition33%1/3
ref $self->{"records"} eq "ARRAY" and ref $self->{"records"}[0] eq "ARRAY"
!ll&&!rl&&r
002
MC/DC0%0/2
ref $self->{"records"} eq "ARRAY" and ref $self->{"records"}[0] eq "ARRAY"
ref $self->{"records"} eq "ARRAY" ref $self->{"records"}[0] eq "ARRAY"
Truth table33%1/3
A and B
A= ref $self->{"records"} eq "ARRAY"B= ref $self->{"records"}[0] eq "ARRAY"
ABresult
0X0
100
111
941 2 $self->{query} = qq{SELECT 'DUMMY'};
942 2 $self->{recs} = $self->{records};
943 2 $self->{NAME} = $self->{col_names};
944 2 $self->{query_separator} = '#';
945 2 return $self->{recs};
946 }
947 return seterr("call to get_recs() without get_query()")
948 0 ▶ unless $self->{query};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"query"} 0 0 0
949 0 my $sth;
950 0 local $self->{dbh}->{RaiseError} = 1;
951 0 local $self->{dbh}->{PrintError} = 0;
952 0 eval {
953 0 $sth = $self->{dbh}->prepare($self->{query});
954 };
955 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
956 return
957 0 seterr "error preparing Crosstab query ($DBI::errstr)\n";
958 }
959 0 eval {
960 0 $sth->execute;
961 };
962 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
963 return
964 0 seterr "error executing Crosstab query ($DBI::errstr)\n";
965 }
966  
967 my @fnames = map {exists $self->{realnames}{$_} ?
968 0 ▶ $self->{realnames}{$_} : $_ } @{$sth->{NAME}};
Branch: 1 branch
BranchTrueFalseTotal
exists $self->{'realnames'}{$_} ? : 0 0 0
969 my @lengths = map {
970 0 my @L = map {length $_ } split $self->{query_separator}, $_;
971 0 _max( @L)
972 } @fnames;
973  
974 0 my $numfields = $sth->{NUM_OF_FIELDS};
975  
976 0 my $recs;
977 0 eval {$recs = $sth->fetchall_arrayref()};
978 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
979 0 return seterr ("error fetching records ($DBI::errstr)")
980 }
981 0 ▶ if($self->{remove_if_zero}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"remove_if_zero"}) 0 0 0
982 0 ▶ my @zeroes = map {defined $_? 0 : 1 } @{$recs->[0]} ;
Branch: 1 branch
BranchTrueFalseTotal
defined $_ ? : 0 0 0
983 0 for my $r (@$recs) {
984 0 my $count =0;
985 0 for my $c (@$r) {
986 0 ▶ if (defined( $c) && ($c ne "0")) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $c and $c ne 0) 0 0 0
Condition0%0/3
defined $c and $c ne 0
!ll&&!rl&&r
000
MC/DC0%0/2
defined $c and $c ne 0
defined $c $c ne 0
Truth table0%0/3
A and B
A= defined $cB= $c ne 0
ABresult
0X0
100
111
987 0 $zeroes[$count] = 1;
988 }
989 0 $count++;
990 }
991 }
992 0 my @voids;
993 my @filled;
994 0 for (0..$#zeroes){
995 0 ▶ if ( $zeroes[$_] )
Branch: 1 branch
BranchTrueFalseTotal
if ($zeroes[$_]) { } 0 0 0
996 {
997 0 push @filled, $_
998 }
999 else {
1000 0 push @voids, $_
1001 }
1002 }
1003 0 ▶ if (@voids) {
Branch: 1 branch
BranchTrueFalseTotal
if (@voids) 0 0 0
1004 0 @fnames = @fnames[@filled];
1005 0 $numfields -= @voids;
1006 0 for my $rec (@$recs) {
1007 0 $rec = [@$rec[@filled]];
1008 }
1009 0 @lengths = @lengths[@filled];
1010 }
1011 }
1012  
1013 0 ▶ if($self->{remove_if_null}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"remove_if_null"}) 0 0 0
1014 0 my @nulls = map {0} @{$recs->[0]} ;
1015 0 for my $r (@$recs) {
1016 0 my $count =0;
1017 0 for (@$r) {
1018 0 ▶ if (defined $_) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $_) 0 0 0
1019 0 $nulls[$count] = 1;
1020 }
1021 0 $count++;
1022 }
1023 }
1024 0 my @voids;
1025 my @filled;
1026 0 for (0..$#nulls){
1027 0 ▶ if ( $nulls[$_] )
Branch: 1 branch
BranchTrueFalseTotal
if ($nulls[$_]) { } 0 0 0
1028 {
1029 0 push @filled, $_
1030 }
1031 else {
1032 0 push @voids, $_
1033 }
1034 }
1035 0 ▶ if (@voids) {
Branch: 1 branch
BranchTrueFalseTotal
if (@voids) 0 0 0
1036 0 @fnames = @fnames[@filled];
1037 0 $numfields -= @voids;
1038 0 for my $rec (@$recs) {
1039 0 $rec = [@$rec[@filled]];
1040 }
1041 0 @lengths = @lengths[@filled];
1042 }
1043 }
1044  
1045 0 ▶ for (my $i = 0 ; $i < $numfields; $i++) {
Branch: 1 branch
BranchTrueFalseTotal
if ($i < $numfields) 0 0 0
1046 0 for (@$recs) {
1047 0 ▶ my $len = $_->[$i] ? (length($_->[$i])) : 0;
Branch: 1 branch
BranchTrueFalseTotal
$_->[$i] ? : 0 0 0
1048 0 ▶ if (defined($_->[$i]) && $_->[$i] =~ /^\d+\.(\d+)/)
Branch: 1 branch
BranchTrueFalseTotal
if (defined $_->[$i] and $_->[$i] =~ /^\d+\.(\d+)/) 0 0 0
Condition0%0/3
defined $_->[$i] and $_->[$i] =~ /^\d+\.(\d+)/
!ll&&!rl&&r
000
MC/DC0%0/2
defined $_->[$i] and $_->[$i] =~ /^\d+\.(\d+)/
defined $_->[$i] $_->[$i] =~ /^\d+\.(\d+)/
Truth table0%0/3
A and B
A= defined $_->[$i]B= $_->[$i] =~ /^\d+\.(\d+)/
ABresult
0X0
100
111
1049 {
1050 0 ▶ if (length($1) > 2) {
Branch: 1 branch
BranchTrueFalseTotal
if (length $1 > 2) 0 0 0
1051 0 $len -= (length($1) - 2);
1052 }
1053 }
1054 0 ▶ $lengths[$i] = 0 unless defined $lengths[$i];
Branch: 1 branch
BranchTrueFalseTotal
unless defined $lengths[$i] 0 0 0
1055 0 ▶ $len = 0 unless defined $len;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $len 0 0 0
1056 0 ▶ $lengths[$i] = $len
Branch: 1 branch
BranchTrueFalseTotal
if $lengths[$i] < $len 0 0 0
1057 if $lengths[$i] < $len;
1058 }
1059 }
1060 0 $self->{recs} = $recs;
1061 0 $self->{NAME} = \@fnames;
1062 0 $self->{LENGTH} = \@lengths;
1063 0 $self->{NUM_OF_FIELDS} = $numfields;
1064 0 return $recs;
1065 }
1066 1 1;
1067  
1068 __END__
↑ Summary SQLCrosstab/Format.pm »