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

Return to bug 7679