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

Return to bug 7679