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

Return to bug 7679