View | Details | Raw Unified | Return to bug 7683
Collapse All | Expand All

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/issues_stats.tt (-7 / +93 lines)
Lines 25-35 Link Here
25
            <p>Filtered on:</p>
25
            <p>Filtered on:</p>
26
            <ul>
26
            <ul>
27
            [% FOREACH loopfilte IN mainloo.loopfilter %]
27
            [% FOREACH loopfilte IN mainloo.loopfilter %]
28
                [% IF ( loopfilte.err ) %]<li class="error">Error:
28
                [% IF ( loopfilte.err ) %]
29
				[% ELSIF ( loopfilte.sql ) %]<li class="sql">
29
                    <li class="error">Error:
30
				[% ELSE %]<li>
30
                [% ELSIF ( loopfilte.sql ) %]
31
				[% END %]
31
                    <li class="sql">
32
					[% loopfilte.crit %] [% loopfilte.filter %]
32
                [% ELSE %]
33
                    <li>
34
                [% END %]
35
                [% SWITCH loopfilte.crit %]
36
                    [% CASE 0 %]Period From
37
                    [% CASE 1 %]Period To
38
                    [% CASE 2 %]Patron Category =
39
                    [% CASE 3 %]Item Type =
40
                    [% CASE 4 %]Library =
41
                    [% CASE 5 %]Collection =
42
                    [% CASE 6 %]Location =
43
                    [% CASE 7 %]Item callnumber &gt;=
44
                    [% CASE 8 %]Item callnumber &lt;
45
                    [% CASE 9 %]sort1 =
46
                    [% CASE 10 %]sort2 =
47
                    [% CASE 11 %]Home branch =
48
                    [% CASE 12 %]Holding branch =
49
                    [% CASE 13 %]Patron branch =
50
                    [% CASE 14 %]Issuing branch =
51
                    [% CASE %]A[% loopfilter.crit %]A
52
                [% END %]
53
                [% loopfilte.filter %]
33
				</li>
54
				</li>
34
            [% END %]
55
            [% END %]
35
            </ul>
56
            </ul>
Lines 97-102 Link Here
97
                    </select></td>
118
                    </select></td>
98
                <td colspan="2"><label for="DisplayBy">group by</label> <select name="DisplayBy" id="DisplayBy">
119
                <td colspan="2"><label for="DisplayBy">group by</label> <select name="DisplayBy" id="DisplayBy">
99
                    <option value="">None</option>
120
                    <option value="">None</option>
121
                    <option value ="4">Hour</option>
100
                    <option value ="1">Day of week</option>
122
                    <option value ="1">Day of week</option>
101
                    <option value ="2">Month</option>
123
                    <option value ="2">Month</option>
102
                    <option value ="3">Year</option>
124
                    <option value ="3">Year</option>
Lines 245-256 Link Here
245
                </select>
267
                </select>
246
            </td>
268
            </td>
247
        </tr>
269
        </tr>
270
        <tr>
271
            <td>Patron branch</td>
272
            <td><input type="radio" name="Line" value="borrowers.branchcode" /></td>
273
            <td><input type="radio" name="Column" value="borrowers.branchcode" /></td>
274
            <td>
275
                <select name="Filter" id="patronbranch">
276
                    <option value="">&nbsp;</option>
277
                    [% FOREACH branch IN branchloop %]
278
                        <option value="[% branch.value %]">
279
                            [% branch.branchname %]
280
                        </option>
281
                    [% END %]
282
                </select>
283
            </td>
284
        </tr>
285
        [% IF (attribute_types_by_class.keys.size) %]
286
            <tr>
287
                <th colspan="4">Patron attributes</th>
288
            </tr>
289
            [% FOREACH class IN attribute_types_by_class.keys.sort %]
290
                [% IF (class) %]
291
                <tr>
292
                    <th>[% class %]</th>
293
                    <th colspan="3"></th>
294
                </tr>
295
                [% END %]
296
                [% FOREACH attr_type IN attribute_types_by_class.$class %]
297
                    <tr>
298
                        <td>[% attr_type.description %]</td>
299
                        <td><input type="radio" name="Line" value="borrower_attributes.[% attr_type.code %]" /> </td>
300
                        <td><input type="radio" name="Column" value="borrower_attributes.[% attr_type.code %]" /> </td>
301
                        <td>
302
                            [% IF attr_type.authorised_value_category %]
303
                                <select name="Filter_borrower_attributes.[% attr_type.code %]">
304
                                    <option value="">&nbsp;</option>
305
                                    [% FOREACH av IN attr_type.authorised_values %]
306
                                        <option value="[% av.authorised_value %]">
307
                                            [% av.lib %]
308
                                        </option>
309
                                    [% END %]
310
                                </select>
311
                            [% ELSE %]
312
                                <input type="text" name="Filter_borrower_attributes.[% attr_type.code %]" />
313
                            [% END %]
314
                        </td>
315
                    </tr>
316
                [% END %]
317
            [% END %]
318
        [% END %]
248
319
249
        </tbody>
320
        </tbody>
250
    </table><br /></fieldset>
321
    </table><br /></fieldset>
251
322
252
<fieldset class="rows"><legend>Cell value </legend><ol><li><label for="cellvalue1">Count total items</label> <input type="radio" name="Cellvalue" value="1" id="cellvalue1" checked="checked" /> </li>
323
<fieldset class="rows">
253
            <li><label for="cellvalue3">Count unique items</label> <input type="radio" name="Cellvalue" id="cellvalue3" value="3" /> </li></ol></fieldset>
324
    <legend>Cell value</legend>
325
    <ol>
326
        <li>
327
            <label for="cellvalue1">Count total items</label>
328
            <input type="radio" name="Cellvalue" value="1" id="cellvalue1" checked="checked" />
329
        </li>
330
        <li>
331
            <label for="cellvalue3">Count unique items</label>
332
            <input type="radio" name="Cellvalue" id="cellvalue3" value="3" />
333
        </li>
334
        <li>
335
            <label for="cellvalue5">Count unique biblios</label>
336
            <input type="radio" name="Cellvalue" id="cellvalue5" value="5" />
337
        </li>
338
    </ol>
339
</fieldset>
254
			
340
			
255
	<fieldset class="rows">
341
	<fieldset class="rows">
256
	<legend>Output</legend>
342
	<legend>Output</legend>
(-)a/reports/issues_stats.pl (-351 / +466 lines)
Lines 19-25 Link Here
19
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
20
20
21
use strict;
21
use strict;
22
#use warnings; FIXME - Bug 2505
22
use warnings;
23
23
24
use CGI;
24
use CGI;
25
use Date::Manip;
25
use Date::Manip;
Lines 34-39 use C4::Circulation; Link Here
34
use C4::Reports;
34
use C4::Reports;
35
use C4::Dates qw/format_date format_date_in_iso/;
35
use C4::Dates qw/format_date format_date_in_iso/;
36
use C4::Members;
36
use C4::Members;
37
use C4::Members::AttributeTypes;
37
38
38
=head1 NAME
39
=head1 NAME
39
40
Lines 61-66 my $monthsel = $input->param("PeriodMonthSel"); Link Here
61
my $calc     = $input->param("Cellvalue");
62
my $calc     = $input->param("Cellvalue");
62
my $output   = $input->param("output");
63
my $output   = $input->param("output");
63
my $basename = $input->param("basename");
64
my $basename = $input->param("basename");
65
66
my $attribute_filters;
67
my $vars = $input->Vars;
68
foreach(keys %$vars) {
69
    if(/^Filter_borrower_attributes\.(.*)/) {
70
        $attribute_filters->{$1} = $vars->{$_};
71
    }
72
}
73
74
64
my ($template, $borrowernumber, $cookie) = get_template_and_user({
75
my ($template, $borrowernumber, $cookie) = get_template_and_user({
65
	template_name => $fullreportname,
76
	template_name => $fullreportname,
66
	query => $input,
77
	query => $input,
Lines 69-75 my ($template, $borrowernumber, $cookie) = get_template_and_user({ Link Here
69
	flagsrequired => {reports => '*'},
80
	flagsrequired => {reports => '*'},
70
	debug => 0,
81
	debug => 0,
71
});
82
});
72
our $sep     = $input->param("sep");
83
our $sep     = $input->param("sep") || ';';
73
$sep = "\t" if ($sep eq 'tabulation');
84
$sep = "\t" if ($sep eq 'tabulation');
74
$template->param(do_it => $do_it,
85
$template->param(do_it => $do_it,
75
);
86
);
Lines 86-130 my ($hassort1,$hassort2); Link Here
86
$hassort1=1 if $Bsort1;
97
$hassort1=1 if $Bsort1;
87
$hassort2=1 if $Bsort2;
98
$hassort2=1 if $Bsort2;
88
99
89
90
if ($do_it) {
100
if ($do_it) {
91
# Displaying results
101
    # Displaying results
92
	my $results = calculate($line, $column, $podsp, $type, $daysel, $monthsel, $calc, \@filters);
102
    my $results = calculate( $line, $column, $podsp, $type, $daysel, $monthsel, $calc, \@filters, $attribute_filters);
93
	if ($output eq "screen"){
103
    if ( $output eq "screen" ) {
94
# Printing results to screen
104
95
		$template->param(mainloop => $results);
105
        # Printing results to screen
96
		output_html_with_http_headers $input, $cookie, $template->output;
106
        $template->param( mainloop => $results );
97
	} else {
107
        output_html_with_http_headers $input, $cookie, $template->output;
98
# Printing to a csv file
108
    } else {
99
        print $input->header(-type => 'application/vnd.sun.xml.calc',
109
100
                            -encoding    => 'utf-8',
110
        # Printing to a csv file
101
                            -attachment=>"$basename.csv",
111
        print $input->header(
102
                            -filename=>"$basename.csv" );
112
            -type       => 'application/vnd.sun.xml.calc',
103
		my $cols  = @$results[0]->{loopcol};
113
            -encoding   => 'utf-8',
104
		my $lines = @$results[0]->{looprow};
114
            -attachment => "$basename.csv",
105
# header top-right
115
            -filename   => "$basename.csv"
106
		print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;
116
        );
107
# Other header
117
        my $cols  = @$results[0]->{loopcol};
108
		foreach my $col ( @$cols ) {
118
        my $lines = @$results[0]->{looprow};
109
			print $col->{coltitle}.$sep;
119
110
		}
120
        # header top-right
111
		print "Total\n";
121
        print @$results[0]->{line} . "/" . @$results[0]->{column} . $sep;
112
# Table
122
113
		foreach my $line ( @$lines ) {
123
        # Other header
114
			my $x = $line->{loopcell};
124
        foreach my $col (@$cols) {
115
			print $line->{rowtitle}.$sep;
125
            print $col->{coltitle} . $sep;
116
			print map {$_->{value}.$sep} @$x;
126
        }
117
			print $line->{totalrow}, "\n";
127
        print "Total\n";
118
		}
128
119
# footer
129
        # Table
120
        print "TOTAL";
130
        foreach my $line (@$lines) {
121
        $cols = @$results[0]->{loopfooter};
131
            my $x = $line->{loopcell};
122
		print map {$sep.$_->{totalcol}} @$cols;
132
            print $line->{rowtitle} . $sep;
123
        print $sep.@$results[0]->{total};
133
            print map { $_->{value} . $sep } @$x;
124
	}
134
            print $line->{totalrow}, "\n";
125
	exit; # exit either way after $do_it
135
        }
136
    }
137
    exit;
126
}
138
}
127
139
140
128
my $dbh = C4::Context->dbh;
141
my $dbh = C4::Context->dbh;
129
my @values;
142
my @values;
130
my %labels;
143
my %labels;
Lines 153-243 my $CGIextChoice=CGI::scrolling_list( Link Here
153
	-values   => ['CSV'], # FIXME translation
166
	-values   => ['CSV'], # FIXME translation
154
	-size     => 1,
167
	-size     => 1,
155
	-multiple => 0 );
168
	-multiple => 0 );
156
    
169
157
my $CGIsepChoice=GetDelimiterChoices;
170
my $CGIsepChoice=GetDelimiterChoices;
158
 
171
172
my @attribute_types = C4::Members::AttributeTypes::GetAttributeTypes(1);
173
my %attribute_types_by_class;
174
foreach my $attribute_type (@attribute_types) {
175
    if ($attribute_type->{authorised_value_category}) {
176
        my $authorised_values = C4::Koha::GetAuthorisedValues(
177
            $attribute_type->{authorised_value_category});
178
179
        foreach my $authorised_value (@$authorised_values) {
180
            push @{ $attribute_type->{authorised_values} }, $authorised_value;
181
        }
182
    }
183
    push @{ $attribute_types_by_class{$attribute_type->{class}} }, $attribute_type;
184
}
185
159
$template->param(
186
$template->param(
160
	categoryloop => $categoryloop,
187
    categoryloop => $categoryloop,
161
	itemtypeloop => \@itemtypeloop,
188
    itemtypeloop => \@itemtypeloop,
162
	locationloop => \@locations,
189
    locationloop => \@locations,
163
	   ccodeloop => \@ccodes,
190
    ccodeloop    => \@ccodes,
164
	  branchloop => GetBranchesLoop(C4::Context->userenv->{'branch'}),
191
    branchloop   => GetBranchesLoop( C4::Context->userenv->{'branch'} ),
165
	hassort1=> $hassort1,
192
    hassort1     => $hassort1,
166
	hassort2=> $hassort2,
193
    hassort2     => $hassort2,
167
	Bsort1 => $Bsort1,
194
    Bsort1       => $Bsort1,
168
	Bsort2 => $Bsort2,
195
    Bsort2       => $Bsort2,
169
	CGIextChoice => $CGIextChoice,
196
    CGIextChoice => $CGIextChoice,
170
	CGIsepChoice => $CGIsepChoice,
197
    CGIsepChoice => $CGIsepChoice,
198
    attribute_types_by_class => \%attribute_types_by_class,
171
);
199
);
172
output_html_with_http_headers $input, $cookie, $template->output;
200
output_html_with_http_headers $input, $cookie, $template->output;
173
201
174
sub calculate {
202
sub calculate {
175
	my ($line, $column, $dsp, $type,$daysel,$monthsel ,$process, $filters) = @_;
203
    my ( $line, $column, $dsp, $type, $daysel, $monthsel, $process, $filters, $attribute_filters ) = @_;
176
	my @loopfooter;
204
    my @loopfooter;
177
	my @loopcol;
205
    my @loopcol;
178
	my @loopline;
206
    my @loopline;
179
	my @looprow;
207
    my @looprow;
180
	my %globalline;
208
    my %globalline;
181
	my $grantotal =0;
209
    my $grantotal = 0;
182
# extract parameters
210
183
	my $dbh = C4::Context->dbh;
211
    # extract parameters
184
212
    my $dbh = C4::Context->dbh;
185
# Filters
213
186
# Checking filters
214
    my ($line_attribute_type, $column_attribute_type);
187
#
215
    if($line =~ /^borrower_attributes\.(.*)/) {
188
	my @loopfilter;
216
        $line_attribute_type = $1;
189
	for (my $i=0;$i<=12;$i++) {
217
        $line = "borrower_attributes.attribute";
190
		my %cell;
218
    }
191
		(@$filters[$i]) or next;
219
    if($column =~ /^borrower_attributes\.(.*)/) {
192
        if (($i==1) and (@$filters[$i-1])) {
220
        $column_attribute_type = $1;
193
            $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
221
        $column = "borrower_attributes.attribute";
222
    }
223
224
    # Filters
225
    # Checking filters
226
    #
227
    my @loopfilter;
228
    for ( my $i = 0 ; $i <= @$filters ; $i++ ) {
229
        my %cell;
230
        ( @$filters[$i] ) or next;
231
        if ( ( $i == 1 ) and ( @$filters[ $i - 1 ] ) ) {
232
            $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
194
        }
233
        }
195
            # format the dates filters, otherwise just fill as is
234
            # format the dates filters, otherwise just fill as is
196
        if ($i>=2) {
235
        if ($i>=2) {
197
          	$cell{filter} = @$filters[$i];
236
            $cell{filter} = @$filters[$i];
198
        } else {
237
        } else {
199
          	$cell{filter} = format_date(@$filters[$i]);
238
            $cell{filter} = format_date(@$filters[$i]);
200
		}
239
        }
201
		$cell{crit} = 
240
        $cell{crit} = $i;
202
            ( $i == 0 )  ? "Period From"
241
203
          : ( $i == 1 )  ? "Period To"
242
        push @loopfilter, \%cell;
204
          : ( $i == 2 )  ? "Patron Category ="
243
    }
205
          : ( $i == 3 )  ? "Item Type ="
244
    foreach (keys %$attribute_filters) {
206
          : ( $i == 4 )  ? "Library ="
245
        next unless $attribute_filters->{$_};
207
          : ( $i == 5 )  ? "Collection ="
246
        push @loopfilter, { crit => "$_ =", filter => $attribute_filters->{$_} };
208
          : ( $i == 6 )  ? "Location ="
209
          : ( $i == 7 )  ? "Item callnumber >="
210
          : ( $i == 8 )  ? "Item callnumber <"
211
          : ( $i == 9 )  ? "sort1 ="
212
          : ( $i == 10 ) ? "sort2 ="
213
          : ( $i == 11 ) ? "Home library ="
214
          : ( $i == 12 )? "Holding library ="
215
          :                "UNKNOWN FILTER ($i)";
216
217
		# FIXME - no translation mechanism !
218
		push @loopfilter, \%cell;
219
    }
247
    }
220
	push @loopfilter,{crit=>"Event",       filter=>$type    };
248
    push @loopfilter, { crit => "Event",        filter => $type };
221
	push @loopfilter,{crit=>"Display by",  filter=>$dsp     } if ($dsp);
249
    push @loopfilter, { crit => "Display by",   filter => $dsp } if ($dsp);
222
	push @loopfilter,{crit=>"Select Day",  filter=>$daysel  } if ($daysel);
250
    push @loopfilter, { crit => "Select Day",   filter => $daysel } if ($daysel);
223
	push @loopfilter,{crit=>"Select Month",filter=>$monthsel} if ($monthsel);
251
    push @loopfilter, { crit => "Select Month", filter => $monthsel } if ($monthsel);
224
252
225
	my @linefilter;
253
    my @linefilter;
226
	$debug and warn "filtres ". join "|", @filters;
254
    $debug and warn "filtres " . join "|", @filters;
227
	my ($colsource, $linesource);
255
    my ( $colsource, $linesource ) = ('', '');
228
	$linefilter[1] = @$filters[1] if ($line =~ /datetime/);
256
    $linefilter[1] = @$filters[1] if ( $line =~ /datetime/ );
229
	$linefilter[0] = 
257
    $linefilter[0] =
230
	    ( $line =~ /datetime/ ) ? @$filters[0]
258
        ( $line =~ /datetime/ )     ? @$filters[0]
231
      : ( $line =~ /category/ ) ? @$filters[2]
259
      : ( $line =~ /category/ )     ? @$filters[2]
232
      : ( $line =~ /itemtype/ ) ? @$filters[3]
260
      : ( $line =~ /itemtype/ )     ? @$filters[3]
233
      : ( $line =~ /^branch/ )   ? @$filters[4]
261
      : ( $line =~ /branch/ )       ? @$filters[4]
234
      : ( $line =~ /ccode/ )    ? @$filters[5]
262
      : ( $line =~ /ccode/ )        ? @$filters[5]
235
      : ( $line =~ /location/ ) ? @$filters[6]
263
      : ( $line =~ /location/ )     ? @$filters[6]
236
      : ( $line =~ /sort1/ )    ? @$filters[9]
264
      : ( $line =~ /sort1/ )        ? @$filters[9]
237
      : ( $line =~ /sort2/ )    ? @$filters[10]
265
      : ( $line =~ /sort2/ )        ? @$filters[10]
238
      : ( $line =~ /homebranch/)    ? @$filters[11]
266
      : ( $line =~ /homebranch/)    ? @$filters[11]
239
      : ( $line =~ /holdingbranch/) ? @$filters[12]
267
      : ( $line =~ /holdingbranch/) ? @$filters[12]
240
      :                           undef;
268
      : ( $line =~ /borrowers.branchcode/ ) ? @$filters[13]
269
      : ( $line_attribute_type )    ? $attribute_filters->{$line_attribute_type}
270
      :                               undef;
241
271
242
    if ( $line =~ /ccode/ or $line =~ /location/ or $line =~ /homebranch/ or $line =~ /holdingbranch/ ) {
272
    if ( $line =~ /ccode/ or $line =~ /location/ or $line =~ /homebranch/ or $line =~ /holdingbranch/ ) {
243
		$linesource = 'items';
273
		$linesource = 'items';
Lines 256-368 sub calculate { Link Here
256
      : ( $column =~ /sort1/ )    ? @$filters[10]
286
      : ( $column =~ /sort1/ )    ? @$filters[10]
257
      : ( $column =~ /homebranch/)    ? @$filters[11]
287
      : ( $column =~ /homebranch/)    ? @$filters[11]
258
      : ( $column =~ /holdingbranch/) ? @$filters[12]
288
      : ( $column =~ /holdingbranch/) ? @$filters[12]
259
      :                             undef;
289
      : ( $column =~ /borrowers.branchcode/ ) ? @$filters[13]
290
      : ( $column_attribute_type )    ? $attribute_filters->{$column_attribute_type}
291
      :                                 undef;
260
292
261
    if ( $column =~ /ccode/ or $column =~ /location/ or $column =~ /homebranch/ or $column =~ /holdingbranch/ ) {
293
    if ( $column =~ /ccode/ or $column =~ /location/ or $column =~ /homebranch/ or $column =~ /holdingbranch/ ) {
262
		$colsource = 'items';
294
        $colsource = 'items';
263
	}
295
    }
264
# 1st, loop rows.
265
	my $linefield;
266
	if ($line =~ /datetime/) {
267
		# by Day, Month or Year (1,2,3 respectively)
268
		$linefield = ($dsp == 1) ? "  dayname($line)" :
269
					 ($dsp == 2) ? "monthname($line)" :
270
					 ($dsp == 3) ? "     Year($line)" :
271
					'date_format(`datetime`,"%Y-%m-%d")'; # Probably should be left alone or passed through C4::Dates
272
	} else {
273
		$linefield = $line;
274
	}
275
	my $lineorder = ($linefield =~ /dayname/) ? "weekday($line)" :
276
					($linefield =~ /^month/ ) ? "  month($line)" : $linefield;
277
278
	my $strsth = "SELECT distinctrow $linefield FROM statistics, ";
279
		# get stats on items if ccode or location, otherwise borrowers.
280
	$strsth .= ($linesource eq 'items' ) ?
281
			" items     WHERE (statistics.itemnumber=items.itemnumber) " :
282
			" borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
283
	$strsth .= " AND $line is not null ";
284
285
	if ($line =~ /datetime/) {
286
		if ($linefilter[1] and ($linefilter[0])) {
287
			$strsth .= " AND $line between ? AND ? ";
288
		} elsif ($linefilter[1]) {
289
			$strsth .= " AND $line < ? ";
290
		} elsif ($linefilter[0]) {
291
		$strsth .= " AND $line > ? ";
292
		}
293
		$strsth .= " AND type ='".$type."' " if $type;
294
		$strsth .= " AND   dayname(datetime) ='".   $daysel ."' " if $daysel;
295
		$strsth .= " AND monthname(datetime) ='". $monthsel ."' " if $monthsel;
296
	} elsif ($linefilter[0]) {
297
		$linefilter[0] =~ s/\*/%/g;
298
		$strsth .= " AND $line LIKE ? ";
299
	}
300
	$strsth .=" group by $linefield order by $lineorder ";
301
	$debug and warn $strsth;
302
	push @loopfilter, {crit=>'SQL =', sql=>1, filter=>$strsth};
303
	my $sth = $dbh->prepare( $strsth );
304
	if ((@linefilter) and ($linefilter[1])){
305
		$sth->execute($linefilter[0],$linefilter[1]);
306
	} elsif ($linefilter[0]) {
307
		$sth->execute($linefilter[0]);
308
	} else {
309
		$sth->execute;
310
	}
311
296
312
	while (my ($celvalue) = $sth->fetchrow) {
297
    # 1st, loop rows.
313
		my %cell = (rowtitle => $celvalue, totalrow => 0); # we leave 'rowtitle' as hash key (used when filling the table), and add coltitle_display
298
    my $linefield;
314
		$cell{rowtitle_display} =
299
    if ( $line =~ /datetime/ ) {
315
			($line =~ /ccode/   ) ? $ccodes->{$celvalue}    :
300
316
			($line =~ /location/) ? $locations->{$celvalue} :
301
        # by Day, Month, Year or Hour (1,2,3,4 respectively)
317
			($line =~ /itemtype/) ? $itemtypes->{$celvalue}->{description} :
302
        $linefield =
318
			$celvalue; # default fallback
303
            ( $dsp == 1 ) ? "  dayname($line)"
319
		if ($line =~ /sort1/) {
304
          : ( $dsp == 2 ) ? "monthname($line)"
320
			foreach (@$Bsort1) {
305
          : ( $dsp == 3 ) ? "     Year($line)"
321
				($celvalue eq $_->{authorised_value}) or next;
306
          : ( $dsp == 4 ) ? "extract(hour from $line)"
322
				$cell{rowtitle_display} = $_->{lib} and last;
307
          :                 'date_format(`datetime`,"%Y-%m-%d")';    # Probably should be left alone or passed through C4::Dates
323
			}
308
    } else {
324
		} elsif ($line =~ /sort2/) {
309
        $linefield = $line;
325
			foreach (@$Bsort2) {
310
    }
326
				($celvalue eq $_->{authorised_value}) or next;
311
    my $lineorder =
327
				$cell{rowtitle_display} = $_->{lib} and last;
312
        ( $linefield =~ /dayname/ ) ? "weekday($line)"
328
			}
313
      : ( $linefield =~ /^month/ )  ? "  month($line)"
329
		} elsif ($line =~ /category/) {
314
      :                               $linefield;
330
			foreach (@$categoryloop) {
315
331
				($celvalue eq $_->{categorycode}) or next;
316
    my $strsth;
332
				$cell{rowtitle_display} = $_->{description} and last;
317
    if($line_attribute_type) {
333
			}
318
        $strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' ";
334
		}
319
    } else {
335
		push @loopline, \%cell;
320
        $strsth = "SELECT distinctrow $linefield FROM statistics, ";
336
	}
321
322
        # get stats on items if ccode or location, otherwise borrowers.
323
        $strsth .=
324
          ( $linesource eq 'items' )
325
          ? " items     WHERE (statistics.itemnumber=items.itemnumber) "
326
          : " borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
327
    }
328
    $strsth .= " AND $line is not null ";
329
330
    if ( $line =~ /datetime/ ) {
331
        if ( $linefilter[1] and ( $linefilter[0] ) ) {
332
            $strsth .= " AND $line between ? AND ? ";
333
        } elsif ( $linefilter[1] ) {
334
            $strsth .= " AND $line < ? ";
335
        } elsif ( $linefilter[0] ) {
336
            $strsth .= " AND $line > ? ";
337
        }
338
        $strsth .= " AND type ='" . $type . "' "                    if $type;
339
        $strsth .= " AND   dayname(datetime) ='" . $daysel . "' "   if $daysel;
340
        $strsth .= " AND monthname(datetime) ='" . $monthsel . "' " if $monthsel;
341
    } elsif ( $linefilter[0] ) {
342
        $linefilter[0] =~ s/\*/%/g;
343
        $strsth .= " AND $line LIKE ? ";
344
    }
345
    $strsth .= " group by $linefield order by $lineorder ";
346
    $debug and warn $strsth;
347
    push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth };
348
    my $sth = $dbh->prepare($strsth);
349
    if ( (@linefilter) and ( $linefilter[1] ) ) {
350
        $sth->execute( $linefilter[0], $linefilter[1] );
351
    } elsif ( $linefilter[0] ) {
352
        $sth->execute( $linefilter[0] );
353
    } else {
354
        $sth->execute;
355
    }
337
356
338
# 2nd, loop cols.
357
    while ( my ($celvalue) = $sth->fetchrow ) {
339
	my $colfield;
358
        my %cell = ( rowtitle => $celvalue, totalrow => 0 );    # we leave 'rowtitle' as hash key (used when filling the table), and add coltitle_display
340
	my $colorder;
359
        $cell{rowtitle_display} =
341
	if ($column =~ /datetime/) {
360
            ( $line =~ /ccode/ )    ? $ccodes->{$celvalue}
342
		#Display by Day, Month or Year (1,2,3 respectively)
361
          : ( $line =~ /location/ ) ? $locations->{$celvalue}
343
		$colfield = ($dsp == 1) ? "  dayname($column)" :
362
          : ( $line =~ /itemtype/ ) ? $itemtypes->{$celvalue}->{description}
344
					($dsp == 2) ? "monthname($column)" :
363
          :                           $celvalue;                               # default fallback
345
					($dsp == 3) ? "     Year($column)" :
364
        if ( $line =~ /sort1/ ) {
346
					'date_format(`datetime`,"%Y-%m-%d")'; # Probably should be left alone or passed through C4::Dates
365
            foreach (@$Bsort1) {
347
	} else {
366
                ( $celvalue eq $_->{authorised_value} ) or next;
348
		$colfield = $column;
367
                $cell{rowtitle_display} = $_->{lib} and last;
349
	}
368
            }
350
	$colorder = ($colfield =~ /dayname/) ? "weekday($column)" :
369
        } elsif ( $line =~ /sort2/ ) {
351
				($colfield =~ /^month/ ) ? "  month($column)" : $colfield;
370
            foreach (@$Bsort2) {
352
	my $strsth2 = "SELECT distinctrow $colfield FROM statistics, ";
371
                ( $celvalue eq $_->{authorised_value} ) or next;
353
	# get stats on items if ccode or location, otherwise borrowers.
372
                $cell{rowtitle_display} = $_->{lib} and last;
354
	$strsth2 .= ($colsource eq 'items' ) ?
373
            }
355
				"items     WHERE (statistics.itemnumber=items.itemnumber) " :
374
        } elsif ( $line =~ /category/ ) {
356
				"borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
375
            foreach (@$categoryloop) {
357
	$strsth2 .= " AND $column IS NOT NULL ";
376
                ( $celvalue eq $_->{categorycode} ) or next;
358
377
                $cell{rowtitle_display} = $_->{description} and last;
359
	if ($column =~ /datetime/){
378
            }
360
        if (($colfilter[1]) and ($colfilter[0])){
379
        }
361
			$strsth2 .= " AND $column BETWEEN ? AND ? " ;
380
        push @loopline, \%cell;
362
        } elsif ($colfilter[1]) {
381
    }
363
			$strsth2 .= " AND $column < ? " ;
382
364
        } elsif ($colfilter[0]) {
383
    # 2nd, loop cols.
365
			$strsth2 .= " AND $column > ? " ;
384
    my $colfield;
385
    my $colorder;
386
    if ( $column =~ /datetime/ ) {
387
388
        #Display by Day, Month or Year (1,2,3 respectively)
389
        $colfield =
390
            ( $dsp == 1 ) ? "  dayname($column)"
391
          : ( $dsp == 2 ) ? "monthname($column)"
392
          : ( $dsp == 3 ) ? "     Year($column)"
393
          : ( $dsp == 4 ) ? "extract(hour from $column)"
394
          :                 'date_format(`datetime`,"%Y-%m-%d")';    # Probably should be left alone or passed through C4::Dates
395
    } else {
396
        $colfield = $column;
397
    }
398
    $colorder =
399
        ( $colfield =~ /dayname/ ) ? "weekday($column)"
400
      : ( $colfield =~ /^month/ )  ? "  month($column)"
401
      :                              $colfield;
402
    my $strsth2;
403
    if($column_attribute_type) {
404
        $strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' ";
405
    } else {
406
        $strsth2 = "SELECT distinctrow $colfield FROM statistics, ";
407
408
        # get stats on items if ccode or location, otherwise borrowers.
409
        $strsth2 .=
410
          ( $colsource eq 'items' )
411
          ? "items     WHERE (statistics.itemnumber=items.itemnumber) "
412
          : "borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) ";
413
    }
414
    $strsth2 .= " AND $column IS NOT NULL ";
415
416
    if ( $column =~ /datetime/ ) {
417
        if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) {
418
            $strsth2 .= " AND $column BETWEEN ? AND ? ";
419
        } elsif ( $colfilter[1] ) {
420
            $strsth2 .= " AND $column < ? ";
421
        } elsif ( $colfilter[0] ) {
422
            $strsth2 .= " AND $column > ? ";
366
        }
423
        }
367
        $strsth2 .= " AND                type ='". $type     ."' " if $type;
424
        $strsth2 .= " AND                type ='". $type     ."' " if $type;
368
        $strsth2 .= " AND   dayname(datetime) ='". $daysel   ."' " if $daysel;
425
        $strsth2 .= " AND   dayname(datetime) ='". $daysel   ."' " if $daysel;
Lines 371-519 sub calculate { Link Here
371
        $colfilter[0] =~ s/\*/%/g;
428
        $colfilter[0] =~ s/\*/%/g;
372
        $strsth2 .= " AND $column LIKE ? " ;
429
        $strsth2 .= " AND $column LIKE ? " ;
373
    }
430
    }
374
	$strsth2 .=" GROUP BY $colfield ORDER BY $colorder ";
375
376
	my $sth2 = $dbh->prepare($strsth2);
377
	push @loopfilter, {crit=>'SQL =', sql=>1, filter=>$strsth2};
378
	if ((@colfilter) and ($colfilter[1])){
379
		$sth2->execute($colfilter[0], $colfilter[1]);
380
	} elsif ($colfilter[0]) {
381
		$sth2->execute($colfilter[0]);
382
	} else {
383
		$sth2->execute;
384
	}
385
431
386
	while (my ($celvalue) = $sth2->fetchrow) {
432
    $strsth2 .= " group by $colfield order by $colorder ";
387
		my %cell = (coltitle => $celvalue); # we leave 'coltitle' as hash key (used when filling the table), and add coltitle_display
433
    $debug and warn $strsth2;
388
		$cell{coltitle_display} =
434
    push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 };
389
			($column =~ /ccode/   ) ?    $ccodes->{$celvalue} :
435
    my $sth2 = $dbh->prepare($strsth2);
390
			($column =~ /location/) ? $locations->{$celvalue} :
436
    if ( (@colfilter) and ( $colfilter[1] ) ) {
391
			($column =~ /itemtype/) ? $itemtypes->{$celvalue}->{description} :
437
        $sth2->execute( $colfilter[0], $colfilter[1] );
392
			$celvalue; # default fallback
438
    } elsif ( $colfilter[0] ) {
393
		if ($column =~ /sort1/) {
439
        $sth2->execute( $colfilter[0] );
394
			foreach (@$Bsort1) {
440
    } else {
395
				($celvalue eq $_->{authorised_value}) or next;
441
        $sth2->execute;
396
				$cell{coltitle_display} = $_->{lib} and last;
442
    }
397
			}
398
		} elsif ($column =~ /sort2/) {
399
			foreach (@$Bsort2) {
400
				($celvalue eq $_->{authorised_value}) or next;
401
				$cell{coltitle_display} = $_->{lib} and last;
402
			}
403
		} elsif ($column =~ /category/) {
404
			foreach (@$categoryloop) {
405
				($celvalue eq $_->{categorycode}) or next;
406
				$cell{coltitle_display} = $_->{description} and last;
407
			}
408
		}
409
		push @loopcol, \%cell;
410
	}
411
443
412
	#Initialization of cell values.....
444
    while ( my ($celvalue) = $sth2->fetchrow ) {
413
	my %table;
445
        my %cell = ( coltitle => $celvalue );    # we leave 'coltitle' as hash key (used when filling the table), and add coltitle_display
414
	foreach my $row (@loopline) {
446
        $cell{coltitle_display} =
415
		foreach my $col (@loopcol) {
447
            ( $column =~ /ccode/ )    ? $ccodes->{$celvalue}
416
			$debug and warn " init table : $row->{rowtitle} ( $row->{rowtitle_display} ) / $col->{coltitle} ( $col->{coltitle_display} )  ";
448
          : ( $column =~ /location/ ) ? $locations->{$celvalue}
417
			$table{$row->{rowtitle}}->{$col->{coltitle}} = 0;
449
          : ( $column =~ /itemtype/ ) ? $itemtypes->{$celvalue}->{description}
418
		}
450
          :                             $celvalue;                               # default fallback
419
		$table{$row->{rowtitle}}->{totalrow} = 0;
451
        if ( $column =~ /sort1/ ) {
420
	}
452
            foreach (@$Bsort1) {
453
                ( $celvalue eq $_->{authorised_value} ) or next;
454
                $cell{coltitle_display} = $_->{lib} and last;
455
            }
456
        } elsif ( $column =~ /sort2/ ) {
457
            foreach (@$Bsort2) {
458
                ( $celvalue eq $_->{authorised_value} ) or next;
459
                $cell{coltitle_display} = $_->{lib} and last;
460
            }
461
        } elsif ( $column =~ /category/ ) {
462
            foreach (@$categoryloop) {
463
                ( $celvalue eq $_->{categorycode} ) or next;
464
                $cell{coltitle_display} = $_->{description} and last;
465
            }
466
        }
467
        push @loopcol, \%cell;
468
    }
421
469
422
# preparing calculation
470
    #Initialization of cell values.....
423
    my $strcalc = "SELECT $linefield, $colfield, ";
471
    my %table;
424
        $strcalc .= ($process == 1) ? " COUNT(*) "                                 :
472
    foreach my $row (@loopline) {
425
					($process == 2) ? "(COUNT(DISTINCT borrowers.borrowernumber))" :
473
        foreach my $col (@loopcol) {
426
        			($process == 3) ? "(COUNT(DISTINCT statistics.itemnumber))"        : '';
474
            $debug and warn " init table : $row->{rowtitle} ( $row->{rowtitle_display} ) / $col->{coltitle} ( $col->{coltitle_display} )  ";
427
	if ($process == 4) {
475
            $table{ $row->{rowtitle} }->{ $col->{coltitle} } = 0;
428
		my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items");
476
        }
429
		$rqbookcount->execute;
477
        $table{ $row->{rowtitle} }->{totalrow} = 0;
430
		my ($bookcount) = $rqbookcount->fetchrow;
478
    }
431
		$strcalc .= "100*(COUNT(DISTINCT statistics.itemnumber))/ $bookcount " ;
479
432
	}
480
    # preparing calculation
433
	$strcalc .= "
481
    my $strcalc = "SELECT ";
482
    if($line_attribute_type) {
483
        $strcalc .= "attribute_$line_attribute_type.attribute AS line_attribute, ";
484
    } else {
485
        $strcalc .= "$linefield, ";
486
    }
487
    if($column_attribute_type) {
488
        $strcalc .= "attribute_$column_attribute_type.attribute AS column_attribute, ";
489
    } else {
490
        $strcalc .= "$colfield, ";
491
    }
492
    $strcalc .=
493
        ( $process == 1 ) ? " COUNT(*) "
494
      : ( $process == 2 ) ? "(COUNT(DISTINCT borrowers.borrowernumber))"
495
      : ( $process == 3 ) ? "(COUNT(DISTINCT statistics.itemnumber))"
496
      : ( $process == 5 ) ? "(COUNT(DISTINCT items.biblionumber))"
497
      :                     '';
498
    if ( $process == 4 ) {
499
        my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items");
500
        $rqbookcount->execute;
501
        my ($bookcount) = $rqbookcount->fetchrow;
502
        $strcalc .= "100*(COUNT(DISTINCT statistics.itemnumber))/ $bookcount ";
503
    }
504
    $strcalc .= "
434
        FROM statistics
505
        FROM statistics
435
        LEFT JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
506
        LEFT JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber
436
	";
507
    ";
437
	$strcalc .= "LEFT JOIN items ON statistics.itemnumber=items.itemnumber "
508
    foreach (keys %$attribute_filters) {
438
        if ($linefield =~ /^items\./ or $colfield =~ /^items\./ or ($colsource eq 'items')
509
        if(
439
            ||@$filters[5]||@$filters[6]||@$filters[7]||@$filters[8]);
510
            ($line_attribute_type and $line_attribute_type eq $_)
440
        
511
            or $column_attribute_type and $column_attribute_type eq $_
441
	$strcalc .= "WHERE 1=1 ";
512
            or $attribute_filters->{$_}
442
	@$filters = map {defined($_) and s/\*/%/g; $_} @$filters;
513
        ) {
443
	$strcalc .= " AND statistics.datetime > '"       . @$filters[0] ."'" if (@$filters[0] );
514
            $strcalc .= " LEFT JOIN borrower_attributes AS attribute_$_ ON (statistics.borrowernumber = attribute_$_.borrowernumber AND attribute_$_.code = '$_') ";
444
	$strcalc .= " AND statistics.datetime < '"       . @$filters[1] ."'" if (@$filters[1] );
515
        }
445
	$strcalc .= " AND borrowers.categorycode LIKE '" . @$filters[2] ."'" if (@$filters[2] );
516
    }
446
	$strcalc .= " AND statistics.itemtype LIKE '"    . @$filters[3] ."'" if (@$filters[3] );
517
    $strcalc .= "LEFT JOIN items ON statistics.itemnumber=items.itemnumber "
447
	$strcalc .= " AND statistics.branch LIKE '"      . @$filters[4] ."'" if (@$filters[4] );
518
      if ( $linefield =~ /^items\./
448
	$strcalc .= " AND items.ccode LIKE '"            . @$filters[5] ."'" if (@$filters[5] );
519
        or $colfield =~ /^items\./
449
	$strcalc .= " AND items.location LIKE '"         . @$filters[6] ."'" if (@$filters[6] );
520
        or $process == 5
450
	$strcalc .= " AND items.itemcallnumber >='"      . @$filters[7] ."'" if (@$filters[7] );
521
        or ( $colsource eq 'items' ) || @$filters[5] || @$filters[6] || @$filters[7] || @$filters[8] );
451
	$strcalc .= " AND items.itemcallnumber <'"       . @$filters[8] ."'" if (@$filters[8] );
522
452
	$strcalc .= " AND borrowers.sort1 LIKE '"        . @$filters[9] ."'" if (@$filters[9] );
523
    $strcalc .= "WHERE 1=1 ";
453
	$strcalc .= " AND borrowers.sort2 LIKE '"        . @$filters[10]."'" if (@$filters[10]);
524
    @$filters = map { defined($_) and s/\*/%/g; $_ } @$filters;
454
	$strcalc .= " AND dayname(datetime) LIKE '"      . $daysel      ."'" if ($daysel  );
525
    $strcalc .= " AND statistics.datetime >= '" . @$filters[0] . "'"       if ( @$filters[0] );
455
	$strcalc .= " AND monthname(datetime) LIKE '"    . $monthsel    ."'" if ($monthsel);
526
    $strcalc .= " AND statistics.datetime <= '" . @$filters[1] . " 23:59:59'"       if ( @$filters[1] );
456
	$strcalc .= " AND statistics.type LIKE '"        . $type        ."'" if ($type    );
527
    $strcalc .= " AND borrowers.categorycode LIKE '" . @$filters[2] . "'" if ( @$filters[2] );
457
528
    $strcalc .= " AND statistics.itemtype LIKE '" . @$filters[3] . "'"    if ( @$filters[3] );
458
	$strcalc .= " GROUP BY $linefield, $colfield order by $lineorder,$colorder";
529
    $strcalc .= " AND statistics.branch LIKE '" . @$filters[4] . "'"      if ( @$filters[4] );
459
	($debug) and warn $strcalc;
530
    $strcalc .= " AND items.ccode LIKE '" . @$filters[5] . "'"            if ( @$filters[5] );
460
	my $dbcalc = $dbh->prepare($strcalc);
531
    $strcalc .= " AND items.location LIKE '" . @$filters[6] . "'"         if ( @$filters[6] );
461
	push @loopfilter, {crit=>'SQL =', sql=>1, filter=>$strcalc};
532
    $strcalc .= " AND items.itemcallnumber >='" . @$filters[7] . "'"      if ( @$filters[7] );
462
	$dbcalc->execute;
533
    $strcalc .= " AND items.itemcallnumber <'" . @$filters[8] . "'"       if ( @$filters[8] );
463
	my ($emptycol,$emptyrow); 
534
    $strcalc .= " AND borrowers.sort1 LIKE '" . @$filters[9] . "'"        if ( @$filters[9] );
464
	while (my ($row, $col, $value) = $dbcalc->fetchrow) {
535
    $strcalc .= " AND borrowers.sort2 LIKE '" . @$filters[10] . "'"       if ( @$filters[10] );
465
		($debug) and warn "filling table $row / $col / $value ";
536
    $strcalc .= " AND items.homebranch LIKE '" . @$filters[11] . "'"      if ( @$filters[11] );
466
		unless (defined $col) {
537
    $strcalc .= " AND items.holdingbranch LIKE '" . @$filters[12] . "'"   if ( @$filters[12] );
467
			$emptycol = 1; 
538
    $strcalc .= " AND borrowers.branchcode LIKE '" . @$filters[13] . "'"  if ( @$filters[13] );
468
			$col = "zzEMPTY" ;
539
    $strcalc .= " AND dayname(datetime) LIKE '" . $daysel . "'"           if ($daysel);
469
		}
540
    $strcalc .= " AND monthname(datetime) LIKE '" . $monthsel . "'"       if ($monthsel);
470
		unless (defined $row) {
541
    $strcalc .= " AND statistics.type LIKE '" . $type . "'"               if ($type);
471
			$emptyrow = 1;
542
    foreach (keys %$attribute_filters) {
472
			$row = "zzEMPTY"; 
543
        if($attribute_filters->{$_}) {
473
		}
544
            $strcalc .= " AND attribute_$_.attribute LIKE '" . $attribute_filters->{$_} . "'";
474
		$table{$row}->{$col}     += $value;
545
        }
475
		$table{$row}->{totalrow} += $value;
546
    }
476
		$grantotal += $value;
547
477
	}
548
    $strcalc .= " GROUP BY ";
478
	push @loopcol, {coltitle => "NULL", coltitle_display => 'NULL'} if ($emptycol);
549
    if($line_attribute_type) {
479
	push @loopline,{rowtitle => "NULL", rowtitle_display => 'NULL'} if ($emptyrow);
550
        $strcalc .= " line_attribute, ";
480
551
    } else {
481
	foreach my $row (@loopline) {
552
        $strcalc .= " $linefield, ";
482
		my @loopcell;
553
    }
483
		#@loopcol ensures the order for columns is common with column titles
554
    if($column_attribute_type) {
484
		# and the number matches the number of columns
555
        $strcalc .= " column_attribute ";
485
		foreach my $col (@loopcol) {
556
    } else {
486
			my $value = $table{null_to_zzempty($row->{rowtitle})}->{null_to_zzempty($col->{coltitle})};
557
        $strcalc .= " $colfield ";
487
			push @loopcell, {value => $value};
558
    }
488
		}
559
489
		my $rowtitle = ($row->{rowtitle} eq "NULL") ? "zzEMPTY" : $row->{rowtitle};
560
    $strcalc .= " ORDER BY ";
490
		push @looprow, {
561
    if($line_attribute_type) {
491
			'rowtitle_display' => $row->{rowtitle_display},
562
        $strcalc .= " line_attribute, ";
492
			'rowtitle' => $rowtitle,
563
    } else {
493
			'loopcell' => \@loopcell,
564
        $strcalc .= " $lineorder, ";
494
			'totalrow' => $table{$rowtitle}->{totalrow}
565
    }
495
		};
566
    if($column_attribute_type) {
496
	}
567
        $strcalc .= " column_attribute ";
497
	for my $col ( @loopcol ) {
568
    } else {
498
		my $total = 0;
569
        $strcalc .= " $colorder ";
499
		foreach my $row (@looprow) {
570
    }
500
			$total += $table{null_to_zzempty($row->{rowtitle})}->{null_to_zzempty($col->{coltitle})};
571
501
			$debug and warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
572
    ($debug) and warn $strcalc;
502
		}
573
    my $dbcalc = $dbh->prepare($strcalc);
503
		push @loopfooter, {'totalcol' => $total};
574
    push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strcalc };
504
	}
575
    $dbcalc->execute;
576
    my ( $emptycol, $emptyrow );
577
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
578
        ($debug) and warn "filling table $row / $col / $value ";
579
        unless ( defined $col ) {
580
            $emptycol = 1;
581
            $col      = "zzEMPTY";
582
        }
583
        unless ( defined $row ) {
584
            $emptyrow = 1;
585
            $row      = "zzEMPTY";
586
        }
587
        $table{$row}->{$col}     += $value;
588
        $table{$row}->{totalrow} += $value;
589
        $grantotal               += $value;
590
    }
591
    push @loopcol,  { coltitle => "NULL", coltitle_display => 'NULL' } if ($emptycol);
592
    push @loopline, { rowtitle => "NULL", rowtitle_display => 'NULL' } if ($emptyrow);
593
594
    foreach my $row (@loopline) {
595
        my @loopcell;
596
597
        #@loopcol ensures the order for columns is common with column titles
598
        # and the number matches the number of columns
599
        foreach my $col (@loopcol) {
600
            my $value = $table{ null_to_zzempty( $row->{rowtitle} ) }->{ null_to_zzempty( $col->{coltitle} ) };
601
            push @loopcell, { value => $value };
602
        }
603
        my $rowtitle = ( $row->{rowtitle} eq "NULL" ) ? "zzEMPTY" : $row->{rowtitle};
604
        push @looprow,
605
          { 'rowtitle_display' => $row->{rowtitle_display},
606
            'rowtitle'         => $rowtitle,
607
            'loopcell'         => \@loopcell,
608
            'totalrow'         => $table{$rowtitle}->{totalrow}
609
          };
610
    }
611
    for my $col (@loopcol) {
612
        my $total = 0;
613
        foreach my $row (@looprow) {
614
            $total += $table{ null_to_zzempty( $row->{rowtitle} ) }->{ null_to_zzempty( $col->{coltitle} ) };
615
            $debug and warn "value added " . $table{ $row->{rowtitle} }->{ $col->{coltitle} } . "for line " . $row->{rowtitle};
616
        }
617
        push @loopfooter, { 'totalcol' => $total };
618
    }
619
620
    # the header of the table
621
    $globalline{loopfilter} = \@loopfilter;
622
623
    # the core of the table
624
    $globalline{looprow} = \@looprow;
625
    $globalline{loopcol} = \@loopcol;
505
626
506
	# the header of the table
627
    # 	# the foot (totals by borrower type)
507
	$globalline{loopfilter}=\@loopfilter;
628
    $globalline{loopfooter} = \@loopfooter;
508
	# the core of the table
629
    $globalline{total}      = $grantotal;
509
	$globalline{looprow} = \@looprow;
630
    $globalline{line}       = $line_attribute_type ? $line_attribute_type : $line;
510
	$globalline{loopcol} = \@loopcol;
631
    $globalline{column}     = $column_attribute_type ? $column_attribute_type : $column;
511
	# 	# the foot (totals by borrower type)
632
    return [ ( \%globalline ) ];
512
	$globalline{loopfooter} = \@loopfooter;
513
	$globalline{total}  = $grantotal;
514
	$globalline{line}   = $line;
515
	$globalline{column} = $column;
516
	return [(\%globalline)];
517
}
633
}
518
634
519
sub null_to_zzempty ($) {
635
sub null_to_zzempty ($) {
520
- 

Return to bug 7683