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

Return to bug 7679