SQLCrosstab/Format.pm

filter: Statementstmt 79.2% 321 / 405 Branchbran 67.0% 118 / 176 Conditioncond 53.1% 25 / 47 MC/DCmcdc 31.0% 9 / 29 Subroutinesub 83.8% 26 / 31 Podpod 66.6% 6 / 9 totaltotal 72.4% 505 / 697 CC 131 SCAR 31.4
CC
131
Cov
74%
as_xls
110.0
as_csv
56.0
as_html
43.3
CRAP 23.2
« SQLCrosstab.pm ↑ Summary
1 package DBIx::SQLCrosstab::Format;
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 DBIx::SQLCrosstab;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 1 our $VERSION = '0.7';
8 # 08-Jan-2004
9  
10 1 require 5.006001;
11  
12 1 require Exporter;
13 1 our @ISA= qw(DBIx::SQLCrosstab);
14 1 our @EXPORT=qw();
15 1 our @EXPORT_OK=qw();
16  
17 1 my %_html_colors = (
18 text => "#009900", # green
19 number => "#FF0000", # red
20 header => "#0000FF", # blue
21 footer => "#000099", # darkblue
22 );
23  
24 1 my %_table_params = (
25 border => 1,
26 cellspacing => 0,
27 cellpadding => 2
28 );
29  
30 772 ▶sub _format {
Subroutine _format: called CC=11 SCAR=24.2
31 772 my $self = shift;
32 772 my ($str, $what) = @_;
33 772 ▶ return $str unless ($self->{add_colors} or $self->{commify});
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"add_colors"} or $self->{"commify"} 0 772 772
Condition66%2/3
$self->{"add_colors"} or $self->{"commify"}
l!l&&r!l&&!r
4303420
MC/DC0%0/2
$self->{"add_colors"} or $self->{"commify"}
$self->{"add_colors"} $self->{"commify"}
Truth table66%2/3
A or B
A= $self->{"add_colors"}B= $self->{"commify"}
ABresult
1X1
011
000
34 772 my $numeric = $str =~ /^[0-9.]+$/;
35  
36 772 ▶ if (($what eq "data") or ($what eq "footer")) {
Branch: 1 branch
BranchTrueFalseTotal
if ($what eq "data" or $what eq "footer") 710 62 772
Condition100%3/3
$what eq "data" or $what eq "footer"
l!l&&r!l&&!r
6684262
MC/DC100%2/2
$what eq "data" or $what eq "footer"
$what eq "data" $what eq "footer"
Truth table100%3/3
A or B
A= $what eq "data"B= $what eq "footer"
ABresult
1X1
011
000
37 710 ▶ if ( $self->{commify} && $numeric )
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"commify"} and $numeric) 684 26 710
Condition66%2/3
$self->{"commify"} and $numeric
!ll&&!rl&&r
026684
MC/DC50%1/2
$self->{"commify"} and $numeric
$self->{"commify"} $numeric
Truth table66%2/3
A and B
A= $self->{"commify"}B= $numeric
ABresult
0X0
100
111
38 {
39 684 ▶ if ($str =~ /\./) {
Branch: 1 branch
BranchTrueFalseTotal
if ($str =~ /\./) 0 684 684
40 0 $str = sprintf("%.2f", $str);
41 }
42 684 $str =~ s/(?<=\d)(?=(?:\d\d\d)+\b)/,/g;
43 }
44 }
45 772 ▶ if ($self->{add_colors} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"add_colors"}) 430 342 772
46 430 my $color_type;
47 430 ▶ $color_type = $what eq "data" ? $numeric ? "number": "text" : $what;
Branch: 2 branches
BranchTrueFalseTotal
$what eq 'data' ? : 326 104 430
$numeric ? : 304 22 326
48 430 $str = qq/<font color="$_html_colors{$color_type}">/ . $str . "</font>";
49 }
50 772 return $str;
51 }
52  
53 4 ▶sub _find_headers {
Subroutine _find_headers: called CC=15 SCAR=27.6
54 4 my $self = shift;
55 return seterr("can't create headers before fetching records ")
56 4 ▶ unless $self->{recs};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} 0 4 4
57 4 my $tree = Tree::DAG_Node->new;
58 4 $tree->name('xtab');
59  
60 # create headers tree
61 4 for my $name (@{$self->{NAME}}) {
62 84 my $top = $tree;
63 84 for my $lev ( split $self->{query_separator}, $name) {
64 176 my $node;
65 176 ($node) = grep {$_->name eq $lev} $top->daughters;
66 176 ▶ unless ($node) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($node) 84 92 176
67 84 $node = Tree::DAG_Node-> new;
68 84 $node->name($lev);
69 }
70 176 $top->add_daughter($node);
71 176 $top = $node;
72 }
73 }
74  
75 # add subtotal headers
76 $tree->walk_down({
77 128 ▶ callback => sub {
Subroutine __ANON__: called CC=4 SCAR=13.9
78 128 my $node = shift;
79 128 ▶ return 1 if $node->address eq "0";
Branch: 1 branch
BranchTrueFalseTotal
if $node->address eq 0 4 124 128
80 124 ▶ if ($node->descendants && $self->{col_sub_total}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($node->descendants and $self->{"col_sub_total"}) 40 84 124
Condition66%2/3
$node->descendants and $self->{"col_sub_total"}
!ll&&!rl&&r
84040
MC/DC50%1/2
$node->descendants and $self->{"col_sub_total"}
$node->descendants $self->{"col_sub_total"}
Truth table66%2/3
A and B
A= $node->descendantsB= $self->{"col_sub_total"}
ABresult
0X0
100
111
81 40 $node->new_daughter->name("total");
82 #$node->new_daughter->name("(". $node->name . ")");
83 }
84 }
85 4 });
86  
87 # find maximum depth
88 4 my $tree_depth = 0;
89 $tree->walk_down({
90 128 ▶ callback =>sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
91 128 my $node = shift;
92 128 ▶ my $depth = scalar $node->ancestors || 0;
Condition100%2/2
scalar $node->ancestors || 0
l!l
1244
MC/DC100%1/1
scalar $node->ancestors || 0
scalar $node->ancestors
Truth table100%2/2
A
A= scalar $node->ancestors
Aresult
11
00
93 128 ▶ $tree_depth = $depth if $depth > $tree_depth;
Branch: 1 branch
BranchTrueFalseTotal
if $depth > $tree_depth 12 116 128
94 128 1;
95 }
96 4 });
97  
98 # find initial columns without sublevels
99 4 my @header_columns =();
100 #@header_columns = map {$_->{alias}} @{$self->{rows}};
101  
102 4 ▶ if ($tree_depth> 1) {
Branch: 1 branch
BranchTrueFalseTotal
if ($tree_depth > 1) { } 4 0 4
103 $tree->walk_down({
104 128 ▶ callback => sub {
Subroutine __ANON__: called CC=4 SCAR=13.9
105 128 my $node=shift;
106 128 ▶ return 1 if $node->address eq "0";
Branch: 1 branch
BranchTrueFalseTotal
if $node->address eq 0 4 124 128
107 124 ▶ if ($node->descendants) {
Branch: 1 branch
BranchTrueFalseTotal
if ($node->descendants) 40 84 124
108 40 $_[0]->{_end_hc} = 1;
109 40 return 1
110 }
111 push @header_columns, $node->name
112 84 ▶ unless $_[0]->{_end_hc};
Branch: 1 branch
BranchTrueFalseTotal
unless $_[0]{"_end_hc"} 8 76 84
113 84 my $cur_depth = ($node->address =~ tr/://) -1;
114 84 $node->attributes->{rowspan} = $tree_depth - $cur_depth ;
115 #print STDERR $node->name," ",
116 # $node->attributes->{rowspan},
117 # "\n";
118 },
119 4 _end_hc => 0
120 });
121 }
122 else {
123 0 my $recs_rows = $#{$self->{recs}};
124 COL:
125 0 for my $col ( 0.. $#{$self->{recs}->[0]} ) {
126 0 my $all_numeric =1;
127 0 for my $row( 0.. $recs_rows) {
128 0 my $value = $self->{recs}[$row][$col];
129 0 ▶ $value = 0 unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 0 0 0
130 0 ▶ unless (($value =~ /^[0-9.]+$/))
Branch: 1 branch
BranchTrueFalseTotal
unless ($value =~ /^[0-9.]+$/) 0 0 0
131 {
132 0 push @header_columns,
133 ($tree->daughters)[$col]->name;
134 0 $all_numeric =0;
135 0 next COL;
136 }
137 }
138 0 ▶ last COL if $all_numeric;
Branch: 1 branch
BranchTrueFalseTotal
if $all_numeric 0 0 0
139 }
140 }
141  
142 # create the record tree for the initial columns
143 4 my $tree_rec = Tree::DAG_Node->new;
144 4 $tree_rec->name('recs');
145 4 for my $rec (@{$self->{recs}}) {
146 36 my @cols;
147 36 for (0..$#header_columns) {
148 72 push @cols, $rec->[$_];
149 }
150 36 my $top = $tree_rec;
151 36 for my $lev ( @cols) {
152 72 my $node;
153 72 ($node) = grep {$_->name eq $lev} $top->daughters;
154 72 ▶ unless ($node) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($node) 52 20 72
155 52 $node = Tree::DAG_Node-> new;
156 52 $node->name($lev);
157 }
158 72 $top->add_daughter($node);
159 72 $top = $node;
160 }
161 }
162  
163 4 my @header_formats =();
164  
165 # find column span values
166 $tree->walk_down( {
167 128 ▶ callbackback => sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
168 128 my $node = shift;
169 128 ▶ return 1 unless $node->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $node->mother 4 124 128
170 124 ▶ $node->attributes->{colspan} = 1
Branch: 1 branch
BranchTrueFalseTotal
unless $node->descendants 84 40 124
171 unless ($node->descendants);
172 $node->mother->attributes->{colspan}
173 124 += $node->attributes->{colspan};
174 }
175 4 });
176  
177 # insert values into header format array
178 $tree->walk_down({
179 128 ▶ callback => sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
180 128 my $node = shift;
181 128 ▶ return 1 if $node->address eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if $node->address eq 0 4 124 128
182 124 my $level = $node->address =~ tr/://;
183 124 $level--;
184 my %format = (
185 rowspan => 0,
186 colspan => $node->attributes->{colspan},
187 124 name => $node->name
188 );
189 124 ▶ if (defined $node->attributes->{rowspan}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $node->attributes->{"rowspan"}) 84 40 124
190 $format{rowspan} =
191 $node->attributes->{rowspan}
192 84 }
193 124 push @{$header_formats[$level]}, \%format;
194 }
195 4 });
196 4 $self->{header_formats} = \@header_formats;
197  
198 4 my %recs_formats =();
199  
200 # find row spans values for records
201 $tree_rec->walk_down( {
202 56 ▶ callbackback => sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
203 56 my $node = shift;
204 56 ▶ return 1 unless $node->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $node->mother 4 52 56
205 52 ▶ $node->attributes->{rowspan} = 1
Branch: 1 branch
BranchTrueFalseTotal
unless $node->descendants 36 16 52
206 unless ($node->descendants);
207 $node->mother->attributes->{rowspan}
208 52 += $node->attributes->{rowspan};
209 }
210 4 });
211  
212 # insert values into record format structure
213 $tree_rec->walk_down({
214 56 ▶ callback => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
215 56 my $node = shift;
216 56 ▶ return 1 if $node->address eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if $node->address eq 0 4 52 56
217 52 my $level = $node->address =~ tr/://;
218 52 push @{$recs_formats{$level-1}{$node->name}},
219 52 $node->attributes->{rowspan};
220 }
221 4 });
222 4 $self->{header_tree} = $tree;
223 4 $self->{recs_tree} = $tree_rec;
224 4 $self->{recs_formats} = \%recs_formats;
225 4 return $self;
226 }
227  
228 0 ▶sub html_header{
Subroutine html_header: not called CC=2 SCAR=17.9
Pod undocumented
229 0 my $self=shift;
230 0 my $html_title = "XTAB";
231 0 ▶ if ($self->{title}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"title"}) 0 0 0
232 $html_title = $self->op_list
233 0 .$self->{title};
234 }
235 return
236 0 "<html>\n<head>\n"
237 ."<title>$html_title</title>\n"
238 . "</head>\n"
239 ."<body>\n";
240 }
241  
242 21 ▶sub _strip_separator {
Subroutine _strip_separator: called CC=1 SCAR=0.0
243 21 my $self = shift;
244 21 my $str = shift;
245 21 $str =~ s/$self->{query_separator}/ /g;
246 21 return $str;
247 }
248  
249 1 ▶sub as_bare_html {
Subroutine as_bare_html: called CC=6 SCAR=18.0
Pod undocumented
250 1 my $self = shift;
251 return DBIx::SQLCrosstab::seterr("can't create table before record fetching")
252 1 ▶ unless $self->{recs};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} 0 1 1
253 1 my $html = qq(<table border="$_table_params{border}" cellspacing="$_table_params{cellspacing}" cellpadding="$_table_params{cellpadding}">\n);
254 #my $html = qq(<table border="1" cellspacing="0" cellpadding="3">\n);
255 1 $html .= "<tr>";
256 $html .= "<td>". $self->_strip_separator($_)."</th>"
257 1 for @{$self->{NAME}};
258 1 $html .= "</tr>\n";
259 1 for my $rec (@{$self->{recs}}) {
260 9 $html .= "<tr>";
261 9 ▶ $html .= "<td>". (defined $_ ? $_ : "-") . "</td>" for @$rec;
Branch: 1 branch
BranchTrueFalseTotal
defined $_ ? : 189 0 189
262 9 $html .= "</tr>\n";
263 }
264 1 $html .= "</table>\n";
265 1 return $html;
266 }
267  
268  
269 2 ▶sub as_html {
Subroutine as_html: called CC=29 SCAR=37.7
Pod documented
270 2 my $self=shift;
271 return DBIx::SQLCrosstab::seterr("can't create table before record fetching")
272 2 ▶ unless $self->{recs};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} 0 2 2
273 2 my $params =shift;
274 2 ▶ if ($params) {
Branch: 1 branch
BranchTrueFalseTotal
if ($params) 0 2 2
275 0 for (qw(complete_html_page only_html_header
276 add_colors text_color number_color header_color
277 footer_color table_cellpadding table_cellspacing table_border))
278 {
279 0 ▶ if (exists $params->{$_})
Branch: 1 branch
BranchTrueFalseTotal
if (exists $params->{$_}) 0 0 0
280 {
281 0 $self->{$_} = $params->{$_};
282 }
283 }
284 }
285 2 ▶ return undef unless $self->_find_headers;
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_find_headers 0 2 2
286 2 ▶ if ($self->{add_colors}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"add_colors"}) 2 0 2
287 2 for (qw(text number header footer)) {
288 8 ▶ if (exists $self->{$_."_color"}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $$self{$_ . '_color'}) 0 8 8
289 0 $_html_colors{$_} = $self->{$_."_color"};
290 }
291 }
292 }
293 2 for (qw(border cellpadding cellspacing)) {
294 6 ▶ if (exists $self->{"table_$_"}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $$self{"table_$_"}) 0 6 6
295 0 $_table_params{$_} = $self->{"table_$_"}
296 }
297 }
298 2 my $html ="";
299 2 ▶ if ($self->{complete_html_page})
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"complete_html_page"}) 0 2 2
300 {
301 0 $html = $self->html_header;
302 }
303 2 $html .= qq(<table border="$_table_params{border}" cellspacing="$_table_params{cellspacing}" cellpadding="$_table_params{cellpadding}">\n);
304 2 ▶ if ($self->{title_in_header}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"title_in_header"}) 2 0 2
305 2 my $colspan1 = scalar @{$self->{NAME}};
306 2 $html .= "<tr><th colspan=$colspan1>$self->{title}</th></tr>\n";
307 }
308 2 for my $h (@{$self->{header_formats}}) {
309 6 $html .= "<tr>\n";
310 6 for my $col (@$h) {
311 62 my $rowspan = "";
312 62 ▶ if (defined $col->{rowspan} )
Branch: 1 branch
BranchTrueFalseTotal
if (defined $col->{"rowspan"}) 62 0 62
313 {
314 62 $rowspan = "rowspan=$col->{rowspan}";
315 }
316 $html .=
317 "<th $rowspan colspan=$col->{colspan}>"
318 62 . $self->_format($col->{name}, "header")
319 . "</th>";
320 }
321 6 $html .= "\n</tr>\n";
322 }
323 2 ▶ if ($self->{only_html_header}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"only_html_header"}) 0 2 2
324 0 $html .= "</table>\n";
325 0 return $html;
326 }
327  
328 2 for my $rec (@{$self->{recs}}) {
329 18 $html .= "<tr>\n";
330 18 my $what = "data";
331 18 ▶ if ( $rec->[0]
Branch: 1 branch
BranchTrueFalseTotal
if ($rec->[0] and $rec->[0] =~ /\bzz+\b/ or $rec->[0] eq "total") 2 16 18
Condition66%4/6
$rec->[0] and $rec->[0] =~ /\bzz+\b/ or $rec->[0] eq "total"
l!l&&r!l&&!r
2016
$rec->[0] and $rec->[0] =~ /\bzz+\b/
!ll&&!rl&&r
0162
MC/DC33%1/3
$rec->[0] and $rec->[0] =~ /\bzz+\b/ or $rec->[0] eq "total"
$rec->[0] $rec->[0] =~ /\bzz+\b/ $rec->[0] eq "total"
Truth table40%2/5
A and B or C
A= $rec->[0]B= $rec->[0] =~ /\bzz+\b/C= $rec->[0] eq "total"
ABCresult
11X1
0X11
1011
0X00
1000
332 && ($rec->[0] =~ /\bzz+\b/)
333 || ($rec->[0] eq 'total') ) {
334 2 $what = "footer";
335 }
336 18 for (0 .. $#$rec) {
337 378 my %attr =();
338 378 ▶ if (defined ($self->{recs_formats}{$_})
Branch: 1 branch
BranchTrueFalseTotal
if (defined $self->{"recs_formats"}{$_} and defined $self->{"recs_formats"}{$_}{$rec->[$_]}) 36 342 378
Condition66%2/3
defined $self->{"recs_formats"}{$_} and defined $self->{"recs_formats"}{$_}{$rec->[$_]}
!ll&&!rl&&r
342036
MC/DC50%1/2
defined $self->{"recs_formats"}{$_} and defined $self->{"recs_formats"}{$_}{$rec->[$_]}
defined $self->{"recs_formats"}{$_} defined $self->{"recs_formats"}{$_}{$rec->[$_]}
Truth table66%2/3
A and B
A= defined $self->{"recs_formats"}{$_}B= defined $self->{"recs_formats"}{$_}{$rec->[$_]}
ABresult
0X0
100
111
339 && defined $self->{recs_formats}{$_}{$rec->[$_]})
340 {
341 36 ▶ if ( @{$self->{recs_formats}{ $_ }{ $rec->[$_] }} )
Branch: 1 branch
BranchTrueFalseTotal
if (@{$self->{'recs_formats'}{$_}{$rec->[$_]};}) { } 26 10 36
342 {
343 $attr{rowspan} = shift
344 26 @{$self->{recs_formats}{$_}{$rec->[$_]}};
345 }
346 else {
347 10 next;
348 }
349 }
350 #$rec->[$_] = "" unless defined $rec->[$_];
351 368 ▶ if ( defined($rec->[$_]) && ($rec->[$_] =~ /^[0-9.]+$/)) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $rec->[$_] and $rec->[$_] =~ /^[0-9.]+$/) 342 26 368
Condition66%2/3
defined $rec->[$_] and $rec->[$_] =~ /^[0-9.]+$/
!ll&&!rl&&r
026342
MC/DC50%1/2
defined $rec->[$_] and $rec->[$_] =~ /^[0-9.]+$/
defined $rec->[$_] $rec->[$_] =~ /^[0-9.]+$/
Truth table66%2/3
A and B
A= defined $rec->[$_]B= $rec->[$_] =~ /^[0-9.]+$/
ABresult
0X0
100
111
352 342 $attr{align} = "right";
353 }
354 my $td = "<td"
355 368 . (join(" ",map( { qq/ $_="$attr{$_}"/} keys %attr)))
356 . ">";
357 368 ▶ $html .= $td
Branch: 1 branch
BranchTrueFalseTotal
defined $rec->[$_] ? : 368 0 368
358 . $self->_format( defined $rec->[$_] ?
359 $rec->[$_] : "-", $what )
360 ."</td>";
361 }
362 18 $html .= "\n</tr>\n";
363 }
364 2 $html .= "</table>\n";
365 2 ▶ if ($self->{complete_html_page}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"complete_html_page"}) 0 2 2
366 0 $html .= $self->html_footer;
367 }
368 2 return $html;
369 }
370  
371 0 ▶sub html_footer {
Subroutine html_footer: not called CC=1 SCAR=6.9
Pod undocumented
372 0 my $self = shift;
373 0 return "</body>\n"
374 ."</html>\n";
375 }
376  
377 2 ▶sub as_xml {
Subroutine as_xml: called CC=3 SCAR=11.0
Pod documented
378 2 my $self = shift;
379 2 my $tab = " ";
380 2 ▶ return undef unless $self->_find_headers;
Branch: 1 branch
BranchTrueFalseTotal
unless $self->_find_headers 0 2 2
381 2 local $self->{add_colors} = 0;
382 2 my $xml = qq/<?xml version="1.0"?>\n/;
383 2 ▶ my $title = $self->{title} || "Crosstab";
Condition50%1/2
$self->{'title'} || 'Crosstab'
l!l
20
MC/DC0%0/1
$self->{'title'} || 'Crosstab'
$self->{'title'}
Truth table50%1/2
A
A= $self->{'title'}
Aresult
11
00
384 2 $title =~ s/&/&amp;/g;
385 2 $title =~ s/</&lt;/g;
386 2 $title =~ s/>/&gt;/g;
387 2 $xml .= qq/<xtab title="$title"\n/
388 . qq/generator="/ . ref($self) . qq/ version $VERSION">\n/;
389  
390 # attach database column labels to header descriptors
391 $self->{recs_tree}->walk_down ({
392 28 ▶ callback => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
393 28 my $n = shift;
394 28 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 2 26 28
395 $n->attributes->{label} =
396 26 $self->{NAME}->[$_[0]->{_depth}-1];
397 26 1;
398 },
399 2 _depth=>0,
400 });
401  
402 # attach database column labels to record descriptors
403 $self->{header_tree}->walk_down ({
404 64 ▶ callback => sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
405 64 my $n = shift;
406 64 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 2 62 64
407 my $label = $self->{cols}->[$_[0]->{_depth}-1]->{value}?
408 $self->{cols}->[$_[0]->{_depth}-1]->{value} :
409 62 ▶ $self->{cols}->[$_[0]->{_depth}-1]->{id};
Branch: 1 branch
BranchTrueFalseTotal
$self->{'cols'}[$_[0]{'_depth'} - 1]{'value'} ? : 32 30 62
410 62 $n->attributes->{label} = $label;
411 62 1;
412 },
413 2 _depth=>0,
414 });
415  
416 # start producing XML output
417 # processing records row by row
418  
419 2 my @records = map {[@$_]} @{$self->{recs}};
420 $self->{recs_tree}->walk_down ({
421 28 ▶ callback => sub {
Subroutine __ANON__: called CC=4 SCAR=13.9
422 28 my $n = shift;
423 28 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 2 26 28
424 26 ▶ if ($n->name eq 'zzzz') {
Branch: 1 branch
BranchTrueFalseTotal
if ($n->name eq "zzzz") 10 16 26
425 10 $n->name('total');
426 }
427 26 $xml .= $tab x $_[0]->{_depth};
428 $xml .= "<"
429 . $n->attributes->{label}
430 26 . " name="
431 . '"'
432 . $n->name
433 . '"'
434 . ">\n";
435 26 ▶ return 1 if $n->descendants;
Branch: 1 branch
BranchTrueFalseTotal
if $n->descendants 8 18 26
436 #
437 # for each row, a tree of its contents
438 # based on the header description
439 # is produced
440 $xml = $self->_make_xml_line($_[0]->{_depth},
441 18 $xml, shift @records, $tab);
442 18 1;
443 },
444 28 ▶ callbackback => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
445 28 my $n = shift;
446 28 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 2 26 28
447 26 $xml .= $tab x $_[0]->{_depth};
448 $xml .= "</"
449 . $n->attributes->{label}
450 26 . ">\n";
451 26 1;
452 },
453 2 _depth => 0,
454 });
455 2 $xml .= "</xtab>";
456 2 $xml =~ s{<(\w+)></\1>}{<$1/>}g;
457 2 $xml =~ s{<(\w+)(\s*\S*)>(?:\s*<\w+/>\s*)+</\1>}{<$1$2/>}sg;
458 2 return $xml;
459 }
460  
461 18 ▶sub _make_xml_line {
Subroutine _make_xml_line: called CC=2 SCAR=6.9
462 18 my $self = shift;
463 18 my $depth = shift;
464 18 my $xml = shift;
465 18 my $line = shift;
466 18 my $tab = shift;
467 18 my $skip = $depth; # columns to skip in callback
468 18 my $skipb = $depth; # columns to skip in callbackback
469 18 for (1..$skip) {
470 36 shift @$line;
471 }
472 $self->{header_tree}->walk_down({
473 576 ▶ callback => sub {
Subroutine __ANON__: called CC=6 SCAR=17.9
474 576 my $n = shift;
475 576 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 18 558 576
476 558 ▶ return 1 if $skip-- > 0;
Branch: 1 branch
BranchTrueFalseTotal
if $skip-- > 0 36 522 558
477 522 $xml .= $tab x $_[0]->{_depth};
478 522 ▶ if ($n->descendants) {
Branch: 1 branch
BranchTrueFalseTotal
if ($n->descendants) { } 180 342 522
479 $xml .= "<"
480 . $n->attributes->{label}
481 180 . ' name="'
482 . $n->name
483 . '">'
484 . "\n";
485 180 $_[0]->{_label} = 1;
486 180 return 1;
487 }
488 else {
489 342 $xml .= " <"
490 . $n->name
491 . ">";
492 342 $_[0]->{_label} = 0;
493 }
494 342 my $value = shift @$line;
495 342 ▶ if ($value) {
Branch: 1 branch
BranchTrueFalseTotal
if ($value) 156 186 342
496 156 $value =~ s/zzzz/total/;
497 }
498 342 ▶ $xml .= ""
Branch: 1 branch
BranchTrueFalseTotal
defined $value ? : 342 0 342
499 . (defined $value? $self->_format($value,"data") : "")
500 . "";
501 342 $_[0]->{_blanks} =0;
502 },
503 576 ▶ callbackback => sub {
Subroutine __ANON__: called CC=6 SCAR=17.9
504 576 my $n = shift;
505 576 ▶ return 1 unless $n->mother;
Branch: 1 branch
BranchTrueFalseTotal
unless $n->mother 18 558 576
506 558 ▶ return 1 if $skipb-- > 0;
Branch: 1 branch
BranchTrueFalseTotal
if $skipb-- > 0 36 522 558
507 522 ▶ if ($_[0]->{_blanks} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_[0]{'_blanks'}) { } 180 342 522
508 180 $xml .= $tab x $_[0]->{_depth};
509 }
510 else {
511 342 $_[0]->{_blanks} = 1;
512 }
513 522 ▶ if ($_[0]->{_label} or ($n->descendants)) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_[0]{'_label'} or $n->descendants) { } 180 342 522
Condition66%2/3
$_[0]{"_label"} or $n->descendants
l!l&&r!l&&!r
0180342
MC/DC50%1/2
$_[0]{"_label"} or $n->descendants
$_[0]{"_label"} $n->descendants
Truth table66%2/3
A or B
A= $_[0]{"_label"}B= $n->descendants
ABresult
1X1
011
000
514 180 $xml .= "</". $n->attributes->{label} . ">\n"
515 }
516 else {
517 342 $xml .= "</" . $n->name . ">\n";
518 }
519 522 1;
520 },
521 18 _depth => $depth,
522 _blanks =>1,
523 _label => 1,
524 });
525 18 return $xml;
526 }
527  
528 0 ▶sub as_xls {
Subroutine as_xls: not called CC=10 SCAR=47.0
Pod documented
529 0 my $self = shift;
530 0 ▶ my $fname = shift
Branch: 1 branch
BranchTrueFalseTotal
my $fname = shift() or return DBIx::SQLCrosstab::seterr("File name required to create spreadsheet") 0 0 0
531 or return DBIx::SQLCrosstab::seterr("File name required to create spreadsheet");
532 0 ▶ my $mode = shift || 'straight';
Condition0%0/2
shift() || 'straight'
l!l
00
MC/DC0%0/1
shift() || 'straight'
shift()
Truth table0%0/2
A
A= shift()
Aresult
11
00
533 0 $mode =~ s/\s*//g;
534 0 my %books = (
535 straight => $mode =~ /^(?:straight|both)$/i,
536 transpose => $mode =~ /^(?:transpose|both)$/i
537 );
538 0 eval {require Spreadsheet::WriteExcel};
539 0 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
540 0 return DBIx::SQLCrosstab::seterr("required module Spreadsheet::WriteExcel not found");
541 }
542 return DBIx::SQLCrosstab::seterr("Recordset not found. Execute query first")
543 0 ▶ unless $self->{recs};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} 0 0 0
544 0 ▶ my $workbook = Spreadsheet::WriteExcel->new($fname)
Branch: 1 branch
BranchTrueFalseTotal
my $workbook = "Spreadsheet::WriteExcel"->new($fname) or return DBIx::SQLCrosstab::seterr("Error creating spreadsheet") 0 0 0
545 or return DBIx::SQLCrosstab::seterr("Error creating spreadsheet");
546 0 my $format = $workbook->add_format(); # Add a format
547 0 $format->set_bold();
548 0 $format->set_text_wrap();
549 0 $format->set_color('blue');
550 0 $format->set_align('center');
551 0 ▶ if ($books{straight}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($books{"straight"}) 0 0 0
552 0 my $worksheet = $workbook->add_worksheet("Crosstab");
553 $worksheet->write('A1',[map {
554 0 join " ", split /$self->{query_separator}/, $_}
555 0 @{$self->{NAME}}], $format);
556 0 my $row = 2;
557 0 $worksheet->write('A'. ($row++), $_ ) for @{$self->{recs}};
558 }
559 0 ▶ if ($books{transpose}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($books{"transpose"}) 0 0 0
560 0 my $worksheet = $workbook->add_worksheet("Transposed");
561 0 my $row =1;
562 0 $format->set_text_wrap(0);
563 0 $format->set_align('left');
564 0 $worksheet->write('A' . ($row++), $_ , $format) for
565 0 map {join " ", split /$self->{query_separator}/, $_}
566 0 @{$self->{NAME}};
567 0 $worksheet->write('B1', $self->{recs});
568 }
569 0 $workbook->close();
570 0 return $workbook;
571 }
572  
573 6 ▶sub as_perl_struct {
Subroutine as_perl_struct: called CC=18 SCAR=29.1
Pod documented
574 6 my $self = shift;
575 6 ▶ my $struct = shift || 'lol';
Condition50%1/2
shift() || 'lol'
l!l
60
MC/DC0%0/1
shift() || 'lol'
shift()
Truth table50%1/2
A
A= shift()
Aresult
11
00
576 return DBIx::SQLCrosstab::seterr("no records to process")
577 6 ▶ unless $self->{recs} and $self->{NAME};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} and $self->{"NAME"} 0 6 6
Condition33%1/3
$self->{"recs"} and $self->{"NAME"}
!ll&&!rl&&r
006
MC/DC0%0/2
$self->{"recs"} and $self->{"NAME"}
$self->{"recs"} $self->{"NAME"}
Truth table33%1/3
A and B
A= $self->{"recs"}B= $self->{"NAME"}
ABresult
0X0
100
111
578 6 my %structs = (
579 lol => undef, # list of lists
580 loh => undef, # list of hashes (tree-like)
581 losh => undef, # list of simple hashes
582 hoh => undef # hash of hashes
583 );
584 return DBIx::SQLCrosstab::seterr ("unrecognized structure $struct")
585 6 ▶ unless exists $structs{$struct};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $structs{$struct} 0 6 6
586 6 ▶ if ($struct eq 'lol') {
Branch: 1 branch
BranchTrueFalseTotal
if ($struct eq "lol") 1 5 6
587 1 return $self->{recs};
588 }
589 5 my $depth = 1;
590 105 my @splitnames = map {[split /$self->{query_separator}/,$_]}
591 5 @{$self->{NAME}};
592  
593 5 for (@splitnames) {
594 105 ▶ $depth = @$_ if (@$_ > $depth);
Branch: 1 branch
BranchTrueFalseTotal
if @$_ > $depth 5 100 105
595 }
596 5 for (@splitnames) {
597 105 ▶ while (@$_ < $depth) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$_ < $depth) 95 105 200
598 95 push @$_, '-';
599 }
600 }
601 5 my $rowheaders = @{$self->{rows}};
602 5 my %hoh =();
603 5 for my $row (@{$self->{recs}}) {
604 45 ▶ if ($struct eq 'losh') {
Branch: 1 branch
BranchTrueFalseTotal
if ($struct eq 'losh') { } 9 36 45
605 9 my %rec=();
606 9 @rec{@{$self->{NAME}}} = @$row;
607 9 push @{$structs{losh}}, \%rec;
608 }
609 else {
610 36 my %rec;
611 36 my $count = 0;
612 36 my $rh ="";
613 36 for my $col (@$row) {
614 756 my $value = $col;
615 756 ▶ $value = "" unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 0 756 756
616 756 $value =~ s/zzzz/total/;
617 756 ▶ if ($count < $rowheaders) {
Branch: 1 branch
BranchTrueFalseTotal
if ($count < $rowheaders) { } 72 684 756
618 72 $rh .= "{$value}";
619 }
620 else {
621 2052 my $key = join "", map {"{$_}"}
622 684 @{$splitnames[$count]};
623 #print qq/\$rec$rh - $key = $col\n/;
624 684 ▶ if ($struct eq 'loh') {
Branch: 2 branches
BranchTrueFalseTotal
if ($struct eq 'loh') { } 171 513 684
elsif ($struct eq 'hoh') { } 513 0 513
625 171 eval qq/\$rec$rh$key = $value/;
626 }
627 elsif($struct eq 'hoh') {
628 513 eval qq/\$structs{hoh}{xtab}$rh$key = $value/;
629 }
630 }
631 756 $count++;
632 }
633 36 ▶ push @{$structs{loh}}, \%rec if $struct eq 'loh';
Branch: 1 branch
BranchTrueFalseTotal
if $struct eq "loh" 9 27 36
634 }
635 }
636 5 return $structs{$struct};
637 }
638  
639 0 ▶sub as_csv {
Subroutine as_csv: not called CC=7 SCAR=40.3
Pod documented
640 0 my $self = shift;
641 0 my $wantheader = shift;
642 return DBIx::SQLCrosstab::seterr("no records to process")
643 0 ▶ unless $self->{recs} and $self->{NAME};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} and $self->{"NAME"} 0 0 0
Condition0%0/3
$self->{"recs"} and $self->{"NAME"}
!ll&&!rl&&r
000
MC/DC0%0/2
$self->{"recs"} and $self->{"NAME"}
$self->{"recs"} $self->{"NAME"}
Truth table0%0/3
A and B
A= $self->{"recs"}B= $self->{"NAME"}
ABresult
0X0
100
111
644 0 my $csv ="";
645 0 ▶ if ($wantheader) {
Branch: 1 branch
BranchTrueFalseTotal
if ($wantheader) 0 0 0
646 0 $csv .= join ",", map {_quote($_)}
647 0 @{$self->{NAME}};
648 0 $csv .= "\n";
649 }
650 0 for my $row (@{$self->{recs}}) {
651 $csv .= join ",", map {
652 0 ▶ defined $_ and /^[0-9.]+$/ ? $_ : _quote($_)
Branch: 1 branch
BranchTrueFalseTotal
/^[0-9.]+$/ ? : 0 0 0
Condition0%0/3
defined $_ && (/^[0-9.]+$/ ? $_ : _quote($_))
!ll&&!rl&&r
000
MC/DC0%0/2
defined $_ && (/^[0-9.]+$/ ? $_ : _quote($_))
defined $_ (/^[0-9.]+$/ ? $_ : _quote($_))
Truth table0%0/3
A && B
A= defined $_B= (/^[0-9.]+$/ ? $_ : _quote($_))
ABresult
0X0
100
111
653 } @$row;
654 0 $csv .= "\n";
655 }
656 0 return $csv;
657 }
658  
659 1 ▶sub as_yaml {
Subroutine as_yaml: called CC=4 SCAR=15.5
Pod documented
660 1 my $self = shift;
661 return DBIx::SQLCrosstab::seterr("no records to process")
662 1 ▶ unless $self->{recs} and $self->{NAME};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"recs"} and $self->{"NAME"} 0 1 1
Condition33%1/3
$self->{"recs"} and $self->{"NAME"}
!ll&&!rl&&r
001
MC/DC0%0/2
$self->{"recs"} and $self->{"NAME"}
$self->{"recs"} $self->{"NAME"}
Truth table33%1/3
A and B
A= $self->{"recs"}B= $self->{"NAME"}
ABresult
0X0
100
111
663 1 eval {require YAML};
664 1 ▶ if ($@){
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 1 1
665 0 return DBIx::SQLCrosstab::seterr('required module YAML not found');
666 }
667 1 return YAML::Dump($self->as_perl_struct('hoh'));
668 }
669  
670 0 ▶sub _quote {
Subroutine _quote: not called CC=3 SCAR=24.8
671 0 my $str = shift;
672 0 ▶ $str =~ s/\"/\\\"/g if $str;
Branch: 1 branch
BranchTrueFalseTotal
if $str 0 0 0
673 0 ▶ return defined $str ? '"'.$str.'"' : '""' ;
Branch: 1 branch
BranchTrueFalseTotal
defined $str ? : 0 0 0
674 }
675  
676 1 1;
677 __END__
« SQLCrosstab.pm ↑ Summary