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

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tt (-15 / +135 lines)
Lines 4-24 Link Here
4
[% INCLUDE 'calendar.inc' %]
4
[% INCLUDE 'calendar.inc' %]
5
<script type="text/javascript">
5
<script type="text/javascript">
6
//<![CDATA[
6
//<![CDATA[
7
        $(document).ready(function() {
7
    function changeRemovedDateTrStatus() {
8
        // http://jqueryui.com/demos/datepicker/#date-range
8
        var Cellvalue = $("input[name='Cellvalue']:checked").val();
9
        var dates = $( "#from, #to" ).datepicker({
9
        if(Cellvalue == "deleteditems") {
10
            changeMonth: true,
10
            $("#removeddatetr").show();
11
            numberOfMonths: 1,
11
            $("input[value='deleteditems.timestamp']").attr('disabled', false);
12
            onSelect: function( selectedDate ) {
12
            $("#deldateFrom").attr('disabled', false);
13
                var option = this.id == "from" ? "minDate" : "maxDate",
13
            $("#deldateTo").attr('disabled', false);
14
                    instance = $( this ).data( "datepicker" );
14
        } else {
15
                    date = $.datepicker.parseDate(
15
            $("#removeddatetr").hide();
16
                        instance.settings.dateFormat ||
16
            $("input[value='deleteditems.timestamp']").attr('disabled', true).attr('checked', false);
17
                        $.datepicker._defaults.dateFormat,
17
            $("#deldateFrom").attr('disabled', true).val('');
18
                        selectedDate, instance.settings );
18
            $("#deldateTo").attr('disabled', true).val('');
19
                dates.not( this ).datepicker( "option", option, date );
19
        }
20
            }
20
    }
21
        });
21
22
    $(document).ready(function() {
22
        var datesRO = $( "#fromRO, #toRO" ).datepicker({
23
        var datesRO = $( "#fromRO, #toRO" ).datepicker({
23
            changeMonth: true,
24
            changeMonth: true,
24
            numberOfMonths: 1,
25
            numberOfMonths: 1,
Lines 32-38 Link Here
32
                datesRO.not( this ).datepicker( "option", option, date );
33
                datesRO.not( this ).datepicker( "option", option, date );
33
            }
34
            }
34
        });
35
        });
36
        $("input[name='Cellvalue']").change(function() {
37
            changeRemovedDateTrStatus();
35
        });
38
        });
39
        changeRemovedDateTrStatus();
40
    });
36
//]]>
41
//]]>
37
</script>
42
</script>
38
</head>
43
</head>
Lines 220-225 Link Here
220
					</select>
225
					</select>
221
				</td>
226
				</td>
222
			</tr>
227
			</tr>
228
            [% IF (notforloan) %]
229
                <tr>
230
                    <td>[% notforloan_label %]</td>
231
                    <td><input type="radio" name="Line"   value="items.notforloan" /></td>
232
                    <td><input type="radio" name="Column" value="items.notforloan" /></td>
233
                    <td>
234
                        [% IF (notforloan_avlist) %]
235
                            <select name="Filter" id="notforloan">
236
                                <option value="">&nbsp;</option>
237
                                [% FOREACH av IN notforloan_avlist %]
238
                                    <option value="[% av.authorised_value %]">
239
                                        [% av.lib %]
240
                                    </option>
241
                                [% END %]
242
                            </select>
243
                        [% ELSE %]
244
                            <input type="text" name="Filter" id="notforloan" />
245
                        [% END %]
246
                    </td>
247
                </tr>
248
            [% ELSE %]
249
                <tr style="display:none">
250
                    <td colspan="4"><input type="hidden" name="Filter" /></td>
251
                </tr>
252
            [% END %]
253
            [% IF (materials) %]
254
                <tr>
255
                    <td>[% materials_label %]</td>
256
                    <td><input type="radio" name="Line"   value="items.materials" /></td>
257
                    <td><input type="radio" name="Column" value="items.materials" /></td>
258
                    <td>
259
                        [% IF (materials_avlist) %]
260
                            <select name="Filter" id="materials">
261
                                <option value="">&nbsp;</option>
262
                                [% FOREACH av IN materials_avlist %]
263
                                    <option value="[% av.authorised_value %]">
264
                                        [% av.lib %]
265
                                    </option>
266
                                [% END %]
267
                            </select>
268
                        [% ELSE %]
269
                            <input type="text" name="Filter" id="materials" />
270
                        [% END %]
271
                    </td>
272
                </tr>
273
            [% ELSE %]
274
                <tr style="display:none">
275
                    <td colspan="4"><input type="hidden" name="Filter" /></td>
276
                </tr>
277
            [% END %]
278
            [% IF (statisticvalue) %]
279
                <tr>
280
                    <td>[% statisticvalue_label %]</td>
281
                    <td><input type="radio" name="Line"   value="items.statisticvalue" /></td>
282
                    <td><input type="radio" name="Column" value="items.statisticvalue" /></td>
283
                    <td>
284
                        [% IF (statisticvalue_avlist) %]
285
                            <select name="Filter" id="statisticvalue">
286
                                <option value="">&nbsp;</option>
287
                                [% FOREACH av IN statisticvalue_avlist %]
288
                                    <option value="[% av.authorised_value %]">
289
                                        [% av.lib %]
290
                                    </option>
291
                                [% END %]
292
                            </select>
293
                        [% ELSE %]
294
                            <input type="text" name="Filter" id="statisticvalue" />
295
                        [% END %]
296
                    </td>
297
                </tr>
298
            [% ELSE %]
299
                <tr style="display:none">
300
                    <td colspan="4"><input type="hidden" name="Filter" /></td>
301
                </tr>
302
            [% END %]
223
            <tr>
303
            <tr>
224
                <td colspan="3">Filter barcode</td>
304
                <td colspan="3">Filter barcode</td>
225
                <td>
305
                <td>
Lines 231-238 Link Here
231
                    (use * to do a fuzzy search)
311
                    (use * to do a fuzzy search)
232
                </td>
312
                </td>
233
            </tr>
313
            </tr>
314
            <tr>
315
                <td>Acquired date</td>
316
                <td><input type="radio" name="Line" value="items.dateaccessioned" /></td>
317
                <td><input type="radio" name="Column" value="items.dateaccessioned" /></td>
318
                <td>
319
                    <label for="acqdateFrom">From:</label>
320
                    <input type="text" name="Filter" id="acqdateFrom" class="datepicker" />
321
                    <label for="acqdateTo">To:</label>
322
                    <input type="text" name="Filter" id="acqdateTo" class="datepicker" />
323
                </td>
324
            </tr>
325
            <tr id="removeddatetr">
326
                <td>Removed date</td>
327
                <td><input type="radio" name="Line" value="deleteditems.timestamp" /></td>
328
                <td><input type="radio" name="Column" value="deleteditems.timestamp" /></td>
329
                <td>
330
                    <label for="deldateFrom">From:</label>
331
                    <input type="text" name="Filter" id="deldateFrom" class="datepicker" />
332
                    <label for="deldateTo">To:</label>
333
                    <input type="text" name="Filter" id="deldateTo" class="datepicker"/>
334
                </td>
335
            </tr>
234
		</tbody>
336
		</tbody>
235
	</table><br /></fieldset>
337
	</table><br /></fieldset>
338
339
    <fieldset class="rows">
340
        <legend>Cell value</legend>
341
        <ol>
342
            <li>
343
                <label for="cellvalue_items">Count total items</label>
344
                <input type="radio" name="Cellvalue" value="items" id="cellvalue_items" checked="checked" />
345
            </li>
346
            <li>
347
                <label for="cellvalue_biblios">Count unique biblios</label>
348
                <input type="radio" name="Cellvalue" value="biblios" id="cellvalue_biblios" />
349
            </li>
350
            <li>
351
                <label for="cellvalue_deleteditems">Count deleted items</label>
352
                <input type="radio" name="Cellvalue" value="deleteditems" id="cellvalue_deleteditems" />
353
            </li>
354
        </ol>
355
    </fieldset>
236
	
356
	
237
	<fieldset class="rows">
357
	<fieldset class="rows">
238
	<legend>Output</legend>
358
	<legend>Output</legend>
(-)a/reports/catalogue_stats.pl (-398 / +498 lines)
Lines 28-33 use C4::Output; Link Here
28
use C4::Koha;
28
use C4::Koha;
29
use C4::Reports;
29
use C4::Reports;
30
use C4::Circulation;
30
use C4::Circulation;
31
use C4::Biblio qw/GetMarcSubfieldStructureFromKohaField/;
31
32
32
=head1 NAME
33
=head1 NAME
33
34
Lines 45-50 my $fullreportname = "reports/catalogue_stats.tmpl"; Link Here
45
my $do_it       = $input->param('do_it');
46
my $do_it       = $input->param('do_it');
46
my $line        = $input->param("Line");
47
my $line        = $input->param("Line");
47
my $column      = $input->param("Column");
48
my $column      = $input->param("Column");
49
my $cellvalue      = $input->param("Cellvalue"); # one of 'items', 'biblios', 'deleteditems'
48
my @filters     = $input->param("Filter");
50
my @filters     = $input->param("Filter");
49
my $deweydigits = $input->param("deweydigits");
51
my $deweydigits = $input->param("deweydigits");
50
my $lccndigits  = $input->param("lccndigits");
52
my $lccndigits  = $input->param("lccndigits");
Lines 70-109 my ($template, $borrowernumber, $cookie) Link Here
70
				});
72
				});
71
$template->param(do_it => $do_it);
73
$template->param(do_it => $do_it);
72
if ($do_it) {
74
if ($do_it) {
73
	my $results = calculate($line, $column, $deweydigits, $lccndigits, $cotedigits, \@filters);
75
    my $results = calculate( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, \@filters );
74
	if ($output eq "screen"){
76
    if ( $output eq "screen" ) {
75
		$template->param(mainloop => $results);
77
        $template->param( mainloop => $results );
76
		output_html_with_http_headers $input, $cookie, $template->output;
78
        output_html_with_http_headers $input, $cookie, $template->output;
77
		exit;
79
        exit(1);
78
	} else {
80
    } else {
79
		print $input->header(-type => 'application/vnd.sun.xml.calc',
81
        print $input->header(
80
                                     -encoding    => 'utf-8',
82
            -type       => 'application/vnd.sun.xml.calc',
81
							 -attachment=>"$basename.csv",
83
            -encoding   => 'utf-8',
82
							 -name=>"$basename.csv" );
84
            -attachment => "$basename.csv",
83
		my $cols  = @$results[0]->{loopcol};
85
            -name       => "$basename.csv"
84
		my $lines = @$results[0]->{looprow};
86
        );
85
		print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;
87
        my $cols  = @$results[0]->{loopcol};
86
		foreach my $col ( @$cols ) {
88
        my $lines = @$results[0]->{looprow};
87
			print $col->{coltitle}.$sep;
89
        print @$results[0]->{line} . "/" . @$results[0]->{column} . $sep;
88
		}
90
        foreach my $col (@$cols) {
89
		print "Total\n";
91
            print $col->{coltitle} . $sep;
90
		foreach my $line ( @$lines ) {
92
        }
91
			my $x = $line->{loopcell};
93
        print "Total\n";
92
			print $line->{rowtitle}.$sep;
94
        foreach my $line (@$lines) {
93
			foreach my $cell (@$x) {
95
            my $x = $line->{loopcell};
94
				print $cell->{value}.$sep;
96
            print $line->{rowtitle} . $sep;
95
			}
97
            foreach my $cell (@$x) {
96
			print $line->{totalrow};
98
                print $cell->{value} . $sep;
97
 			print "\n";
99
            }
98
	 	}
100
            print $line->{totalrow};
99
		print "TOTAL";
101
            print "\n";
100
		$cols = @$results[0]->{loopfooter};
102
        }
101
		foreach my $col ( @$cols ) {
103
        print "TOTAL";
102
			print $sep.$col->{totalcol};
104
        $cols = @$results[0]->{loopfooter};
103
		}
105
        foreach my $col (@$cols) {
104
		print $sep.@$results[0]->{total};
106
            print $sep. $col->{totalcol};
105
		exit;
107
        }
106
	}
108
        print $sep. @$results[0]->{total};
109
        exit(1);
110
    }
107
} else {
111
} else {
108
	my $dbh = C4::Context->dbh;
112
	my $dbh = C4::Context->dbh;
109
	my @values;
113
	my @values;
Lines 142-176 if ($do_it) { Link Here
142
    my $hascote = 1;
146
    my $hascote = 1;
143
    my $highcote = 5;
147
    my $highcote = 5;
144
148
145
	$req = $dbh->prepare("select itemtype, description from itemtypes order by description");
149
    $req = $dbh->prepare("select itemtype, description from itemtypes order by description");
146
	$req->execute;
150
    $req->execute;
147
	my $CGIitemtype = $req->fetchall_arrayref({});
151
    my $CGIitemtype = $req->fetchall_arrayref( {} );
148
152
149
	my $authvals = GetKohaAuthorisedValues("items.ccode");
153
    my $authvals = GetKohaAuthorisedValues("items.ccode");
150
	my @authvals;
154
    my @authvals;
151
	foreach (sort {$authvals->{$a} cmp $authvals->{$b} || $a cmp $b} keys %$authvals) {
155
    foreach ( sort { $authvals->{$a} cmp $authvals->{$b} || $a cmp $b } keys %$authvals ) {
152
		push @authvals, { code => $_, description => $authvals->{$_} };
156
        push @authvals, { code => $_, description => $authvals->{$_} };
153
	}
157
    }
154
	
158
155
	my $locations = GetKohaAuthorisedValues("items.location");
159
    my $locations = GetKohaAuthorisedValues("items.location");
156
	my @locations;
160
    my @locations;
157
	foreach (sort keys %$locations) {
161
    foreach ( sort keys %$locations ) {
158
		push @locations, { code => $_, description => "$_ - " . $locations->{$_} };
162
        push @locations, { code => $_, description => "$_ - " . $locations->{$_} };
159
	}
163
    }
160
	
164
161
	my @mime  = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
165
    foreach my $kohafield (qw(items.notforloan items.materials items.statisticvalue)) {
162
	
166
        my $subfield_structure = GetMarcSubfieldStructureFromKohaField($kohafield);
163
	$template->param(hasdewey=>$hasdewey,
167
        if($subfield_structure) {
164
					haslccn   => $haslccn,
168
            my $avlist;
165
					hascote   => $hascote,
169
            my $avcategory = $subfield_structure->{authorised_value};
166
					CGIItemType => $CGIitemtype,
170
            if($avcategory) {
167
					CGIBranch    => GetBranchesLoop(C4::Context->userenv->{'branch'}),
171
                $avlist = GetAuthorisedValues($avcategory);
168
					locationloop => \@locations,
172
            }
169
					authvals     => \@authvals,
173
            my $kf = $kohafield;
170
					CGIextChoice => \@mime,
174
            $kf =~ s/^items\.//;
171
					CGIsepChoice => GetDelimiterChoices,
175
            $template->param(
172
					item_itype => $item_itype
176
                $kf => 1,
173
					);
177
                $kf."_label" => $subfield_structure->{liblibrarian},
178
                $kf."_avlist" => $avlist
179
            );
180
        }
181
    }
182
183
    my @mime  = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
184
185
    $template->param(
186
        hasdewey     => $hasdewey,
187
        haslccn      => $haslccn,
188
        hascote      => $hascote,
189
        CGIItemType  => $CGIitemtype,
190
        CGIBranch    => GetBranchesLoop( C4::Context->userenv->{'branch'} ),
191
        locationloop => \@locations,
192
        authvals     => \@authvals,
193
        CGIextChoice => \@mime,
194
        CGIsepChoice => GetDelimiterChoices,
195
        item_itype   => $item_itype,
196
        DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
197
    );
174
198
175
}
199
}
176
output_html_with_http_headers $input, $cookie, $template->output;
200
output_html_with_http_headers $input, $cookie, $template->output;
Lines 179-523 output_html_with_http_headers $input, $cookie, $template->output; Link Here
179
203
180
204
181
sub calculate {
205
sub calculate {
182
	my ($line, $column, $deweydigits, $lccndigits, $cotedigits, $filters) = @_;
206
    my ( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, $filters ) = @_;
183
	my @mainloop;
207
    my @mainloop;
184
	my @loopfooter;
208
    my @loopfooter;
185
	my @loopcol;
209
    my @loopcol;
186
	my @loopline;
210
    my @loopline;
187
	my @looprow;
211
    my @looprow;
188
	my %globalline;
212
    my %globalline;
189
	my $grantotal =0;
213
    my $grantotal     = 0;
190
    my $barcodelike   = @$filters[13];
214
    my $barcodelike   = @$filters[16];
191
    my $barcodefilter = @$filters[14];
215
    my $barcodefilter = @$filters[17];
192
    my $not;
216
    my $not;
193
    
217
    my $itemstable = ($cellvalue eq 'deleteditems') ? 'deleteditems' : 'items';
194
# extract parameters
195
	my $dbh = C4::Context->dbh;
196
218
197
# if barcodefilter is empty set as %
219
    my $dbh = C4::Context->dbh;
198
if($barcodefilter){
220
199
    # Check if barcodefilter is "like" or "not like"
221
    # if barcodefilter is empty set as %
200
    if(!$barcodelike){
222
    if ($barcodefilter) {
201
        $not = "not";
223
224
        # Check if barcodefilter is "like" or "not like"
225
        if ( !$barcodelike ) {
226
            $not = "not";
227
        }
228
229
        # Change * to %
230
        $barcodefilter =~ s/\*/%/g;
202
    }
231
    }
203
    # Change * to %
204
    $barcodefilter =~ s/\*/%/g;
205
}
206
232
207
# Filters
233
    # Filters
208
# Checking filters
234
    # Checking filters
209
#
235
    #
210
	my @loopfilter;
236
    my @loopfilter;
211
	for (my $i=0;$i<=12;$i++) {
237
    for ( my $i = 0 ; $i <= @$filters ; $i++ ) {
212
		my %cell;
238
        my %cell;
213
		if ( @$filters[$i] ) {
239
        if ( defined @$filters[$i] and @$filters[$i] ne '' ) {
214
			if ((($i==1) or ($i==3) or ($i==5) or ($i==9)) and (@$filters[$i-1])) {
240
            if ( ( ( $i == 1 ) or ( $i == 3 ) or ( $i == 5 ) or ( $i == 9 ) ) and ( @$filters[ $i - 1 ] ) ) {
215
				$cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
241
                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
216
			}
242
            }
217
			$cell{filter} .= @$filters[$i];
243
            $cell{filter} .= @$filters[$i];
218
			$cell{crit} .=
244
            $cell{crit} .=
219
				($i== 0) ? "Dewey Classification From" :
245
                ( $i == 0 )  ? "Dewey Classification From"
220
				($i== 1) ? "Dewey Classification To"   :
246
              : ( $i == 1 )  ? "Dewey Classification To"
221
				($i== 2) ? "Lccn Classification From"  :
247
              : ( $i == 2 )  ? "Lccn Classification From"
222
				($i== 3) ? "Lccn Classification To"    :
248
              : ( $i == 3 )  ? "Lccn Classification To"
223
				($i== 4) ? "Item CallNumber From"  :
249
              : ( $i == 4 )  ? "Item CallNumber From"
224
				($i== 5) ? "Item CallNumber To"    :
250
              : ( $i == 5 )  ? "Item CallNumber To"
225
				($i== 6) ? "Item type"             :
251
              : ( $i == 6 )  ? "Item type"
226
				($i== 7) ? "Publisher"                 :
252
              : ( $i == 7 )  ? "Publisher"
227
				($i== 8) ? "Publication year From"     :
253
              : ( $i == 8 )  ? "Publication year From"
228
				($i== 9) ? "Publication year To"       :
254
              : ( $i == 9 )  ? "Publication year To"
229
				($i==10) ? "Library :"                  :
255
              : ( $i == 10 ) ? "Library"
230
				($i==11) ? "Shelving Location :"                :
256
              : ( $i == 11 ) ? "Shelving Location"
231
				($i==12) ? "Collection Code :"            : '';
257
              : ( $i == 12 ) ? "Collection Code"
232
			push @loopfilter, \%cell;
258
              : ( $i == 13 ) ? "Status"
233
		}
259
              : ( $i == 14 ) ? "Materials"
234
	}
260
              : ( $i == 15 ) ? "Statistic value"
235
	
261
              : ( $i == 18 ) ? "Acquired date from"
236
#	warn map {"filtres $_\n"} @filters[0..3];
262
              : ( $i == 19 ) ? "Acquired date to"
237
263
              : ( $i == 20 ) ? "Removed date from"
238
	my @linefilter;
264
              : ( $i == 21 ) ? "Removed date to"
239
 	$linefilter[0] = @$filters[0] if ($line =~ /dewey/ )  ;
265
              :                '';
240
 	$linefilter[1] = @$filters[1] if ($line =~ /dewey/ )  ;
266
            push @loopfilter, \%cell;
241
 	$linefilter[0] = @$filters[2] if ($line =~ /lccn/ )  ;
267
        }
242
 	$linefilter[1] = @$filters[3] if ($line =~ /lccn/ )  ;
268
    }
243
 	$linefilter[0] = @$filters[4] if ($line =~ /items\.itemcallnumber/ )  ;
269
244
 	$linefilter[1] = @$filters[5] if ($line =~ /items\.itemcallnumber/ )  ;
270
    @$filters[18] = C4::Dates->new(@$filters[18])->output('iso') if @$filters[18];
245
	if (C4::Context->preference('item-level_itypes')) {
271
    @$filters[19] = C4::Dates->new(@$filters[19])->output('iso') if @$filters[19];
246
 		$linefilter[0] = @$filters[6] if ($line =~ /items\.itype/ )  ;
272
    @$filters[20] = C4::Dates->new(@$filters[20])->output('iso') if @$filters[20];
247
	} else {
273
    @$filters[21] = C4::Dates->new(@$filters[21])->output('iso') if @$filters[21];
248
 		$linefilter[0] = @$filters[6] if ($line =~ /itemtype/ )  ;
274
249
	}
275
    my @linefilter;
250
 	$linefilter[0] = @$filters[7] if ($line =~ /publishercode/ ) ;
276
    $linefilter[0] = @$filters[0] if ( $line =~ /dewey/ );
251
 	$linefilter[0] = @$filters[8] if ($line =~ /publicationyear/ ) ;
277
    $linefilter[1] = @$filters[1] if ( $line =~ /dewey/ );
252
 	$linefilter[1] = @$filters[9] if ($line =~ /publicationyear/ ) ;
278
    $linefilter[0] = @$filters[2] if ( $line =~ /lccn/ );
253
 	$linefilter[0] = @$filters[10] if ($line =~ /items\.homebranch/ ) ;
279
    $linefilter[1] = @$filters[3] if ( $line =~ /lccn/ );
254
 	$linefilter[0] = @$filters[11] if ($line =~ /items\.location/ ) ;
280
    $linefilter[0] = @$filters[4] if ( $line =~ /items\.itemcallnumber/ );
255
 	$linefilter[0] = @$filters[12] if ($line =~ /items\.ccode/ ) ;
281
    $linefilter[1] = @$filters[5] if ( $line =~ /items\.itemcallnumber/ );
256
282
    if ( C4::Context->preference('item-level_itypes') ) {
257
 	my @colfilter ;
283
        $linefilter[0] = @$filters[6] if ( $line =~ /items\.itype/ );
258
 	$colfilter[0] = @$filters[0] if ($column =~ /dewey/ )  ;
284
    } else {
259
 	$colfilter[1] = @$filters[1] if ($column =~ /dewey/ )  ;
285
        $linefilter[0] = @$filters[6] if ( $line =~ /itemtype/ );
260
 	$colfilter[0] = @$filters[2] if ($column =~ /lccn/ )  ;
286
    }
261
 	$colfilter[1] = @$filters[3] if ($column =~ /lccn/ )  ;
287
    $linefilter[0] = @$filters[7] if ( $line =~ /publishercode/ );
262
 	$colfilter[0] = @$filters[4] if ($column =~ /items\.itemcallnumber/ )  ;
288
    $linefilter[0] = @$filters[8] if ( $line =~ /publicationyear/ );
263
 	$colfilter[1] = @$filters[5] if ($column =~ /items\.itemcallnumber/ )  ;
289
    $linefilter[1] = @$filters[9] if ( $line =~ /publicationyear/ );
264
	if (C4::Context->preference('item-level_itypes')) {
290
265
 		$colfilter[0] = @$filters[6] if ($column =~ /items\.itype/ )  ;
291
    $linefilter[0] = @$filters[10] if ( $line =~ /items\.homebranch/ );
266
	} else {
292
    $linefilter[0] = @$filters[11] if ( $line =~ /items\.location/ );
267
 		$colfilter[0] = @$filters[6] if ($column =~ /itemtype/ )  ;
293
    $linefilter[0] = @$filters[12] if ( $line =~ /items\.ccode/ );
268
	}
294
    $linefilter[0] = @$filters[13] if ( $line =~ /items\.notforloan/ );
269
 	$colfilter[0] = @$filters[7] if ($column =~ /publishercode/ ) ;
295
    $linefilter[0] = @$filters[14] if ( $line =~ /items\.materials/ );
270
 	$colfilter[0] = @$filters[8] if ($column =~ /publicationyear/ ) ;
296
    $linefilter[0] = @$filters[15] if ( $line =~ /items\.statisticvalue/ );
271
 	$colfilter[1] = @$filters[9] if ($column =~ /publicationyear/ ) ;
297
    $linefilter[0] = @$filters[18] if ( $line =~ /items\.dateaccessioned/ );
272
 	$colfilter[0] = @$filters[10] if ($column =~ /items\.homebranch/ ) ;
298
    $linefilter[1] = @$filters[19] if ( $line =~ /items\.dateaccessioned/ );
273
 	$colfilter[0] = @$filters[11] if ($column =~ /items\.location/ ) ;
299
    $linefilter[0] = @$filters[20] if ( $line =~ /deleteditems\.timestamp/ );
274
 	$colfilter[0] = @$filters[12] if ($column =~ /items\.ccode/ ) ;
300
    $linefilter[1] = @$filters[21] if ( $line =~ /deleteditems\.timestamp/ );
275
301
276
# 1st, loop rows.
302
    my @colfilter;
277
	my $linefield;
303
    $colfilter[0] = @$filters[0] if ( $column =~ /dewey/ );
278
	if (($line =~/dewey/)  and ($deweydigits)) {
304
    $colfilter[1] = @$filters[1] if ( $column =~ /dewey/ );
279
		$linefield .="left($line,$deweydigits)";
305
    $colfilter[0] = @$filters[2] if ( $column =~ /lccn/ );
280
	} elsif (($line=~/lccn/) and ($lccndigits)) {
306
    $colfilter[1] = @$filters[3] if ( $column =~ /lccn/ );
281
		$linefield .="left($line,$lccndigits)";
307
    $colfilter[0] = @$filters[4] if ( $column =~ /items\.itemcallnumber/ );
282
	} elsif (($line=~/items.itemcallnumber/) and ($cotedigits)) {
308
    $colfilter[1] = @$filters[5] if ( $column =~ /items\.itemcallnumber/ );
283
		$linefield .="left($line,$cotedigits)";
309
    if ( C4::Context->preference('item-level_itypes') ) {
284
	}else {
310
        $colfilter[0] = @$filters[6] if ( $column =~ /items\.itype/ );
285
		$linefield .= $line;
311
    } else {
286
	}
312
        $colfilter[0] = @$filters[6] if ( $column =~ /itemtype/ );
287
313
    }
288
	my $strsth = "SELECT DISTINCTROW $linefield FROM biblioitems 
314
    $colfilter[0] = @$filters[7]  if ( $column =~ /publishercode/ );
289
	                INNER JOIN items USING (biblioitemnumber)
315
    $colfilter[0] = @$filters[8]  if ( $column =~ /publicationyear/ );
290
	              WHERE $line IS NOT NULL ";
316
    $colfilter[1] = @$filters[9]  if ( $column =~ /publicationyear/ );
317
    $colfilter[0] = @$filters[10] if ( $column =~ /items\.homebranch/ );
318
    $colfilter[0] = @$filters[11] if ( $column =~ /items\.location/ );
319
    $colfilter[0] = @$filters[12] if ( $column =~ /items\.ccode/ );
320
    $colfilter[0] = @$filters[13] if ( $column =~ /items\.notforloan/ );
321
    $colfilter[0] = @$filters[14] if ( $column =~ /items\.materials/ );
322
    $colfilter[0] = @$filters[15] if ( $column =~ /items\.statisticvalue/ );
323
    $colfilter[0] = @$filters[18] if ( $column =~ /items.dateaccessioned/ );
324
    $colfilter[1] = @$filters[19] if ( $column =~ /items\.dateaccessioned/ );
325
    $colfilter[0] = @$filters[20] if ( $column =~ /deleteditems\.timestamp/ );
326
    $colfilter[1] = @$filters[21] if ( $column =~ /deleteditems\.timestamp/ );
327
328
    # 1st, loop rows.
329
    my $origline = $line;
330
    $line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
331
    my $linefield;
332
    if ( ( $line =~ /dewey/ ) and ($deweydigits) ) {
333
        $linefield = "left($line,$deweydigits)";
334
    } elsif ( ( $line =~ /lccn/ ) and ($lccndigits) ) {
335
        $linefield = "left($line,$lccndigits)";
336
    } elsif ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) {
337
        $linefield = "left($line,$cotedigits)";
338
    } elsif ( $line =~ /^deleteditems\.timestamp$/ ) {
339
        $linefield = "DATE($line)";
340
    } else {
341
        $linefield = $line;
342
    }
343
344
    my $strsth = "SELECT DISTINCTROW $linefield FROM $itemstable
345
                    LEFT JOIN biblioitems USING (biblioitemnumber)
346
                  WHERE 1 ";
291
    $strsth .= " AND barcode $not LIKE ? " if ($barcodefilter);
347
    $strsth .= " AND barcode $not LIKE ? " if ($barcodefilter);
292
	if ( @linefilter ) {
348
    if (@linefilter) {
293
		if ($linefilter[1]){
349
        if ( $linefilter[1] ) {
294
			$strsth .= " AND $line >= ? " ;
350
            $strsth .= " AND $line >= ? ";
295
			$strsth .= " AND $line <= ? " ;
351
            $strsth .= " AND $line <= ? ";
296
		} elsif ($linefilter[0]) {
352
        } elsif ( defined $linefilter[0] and $linefilter[0] ne '' ) {
297
			$linefilter[0] =~ s/\*/%/g;
353
            $linefilter[0] =~ s/\*/%/g;
298
			$strsth .= " AND $line LIKE ? " ;
354
            $strsth .= " AND $line LIKE ? ";
299
		}
355
        }
300
	}
356
    }
301
	$strsth .=" ORDER BY $linefield";
357
    $strsth .= " ORDER BY $linefield";
302
	$debug and print STDERR "catalogue_stats SQL: $strsth\n";
358
    $debug and print STDERR "catalogue_stats SQL: $strsth\n";
303
359
304
	my $sth = $dbh->prepare( $strsth );
360
    my $sth = $dbh->prepare($strsth);
305
	if (( @linefilter ) and ($linefilter[1])){
361
    if ( $barcodefilter and (@linefilter) and ( $linefilter[1] ) ) {
306
		$sth->execute($barcodefilter,$linefilter[0],$linefilter[1]);
362
        $sth->execute( $barcodefilter, $linefilter[0], $linefilter[1] );
307
	} elsif ($barcodefilter,$linefilter[0]) {
363
    } elsif ( (@linefilter) and ( $linefilter[1] ) ) {
308
		$sth->execute($barcodefilter,$linefilter[0]);
364
        $sth->execute( $linefilter[0], $linefilter[1] );
309
	} elsif ($barcodefilter) {
365
    } elsif ( $barcodefilter and $linefilter[0] ) {
310
		$sth->execute($barcodefilter);
366
        $sth->execute( $barcodefilter, $linefilter[0] );
311
	}else{
367
    } elsif ( $linefilter[0] ) {
312
	    $sth->execute();
368
        $sth->execute($linefilter[0]);
313
	}
369
    } elsif ($barcodefilter) {
314
 	while ( my ($celvalue) = $sth->fetchrow) {
370
        $sth->execute($barcodefilter);
315
 		my %cell;
371
    } else {
316
		if ($celvalue) {
372
        $sth->execute();
317
			$cell{rowtitle} = $celvalue;
373
    }
318
#		} else {
374
    my $rowauthvals = GetKohaAuthorisedValues($origline);
319
#			$cell{rowtitle} = "";
375
    while ( my ($celvalue) = $sth->fetchrow ) {
320
		}
376
        my %cell;
321
 		$cell{totalrow} = 0;
377
        if (defined $celvalue and $celvalue ne '') {
322
		push @loopline, \%cell;
378
            if($rowauthvals and $rowauthvals->{$celvalue}) {
323
 	}
379
                $cell{rowtitle} = $rowauthvals->{$celvalue};
324
380
            } else {
325
# 2nd, loop cols.
381
                $cell{rowtitle} = $celvalue;
326
	my $colfield;
382
            }
327
	if (($column =~/dewey/)  and ($deweydigits)) {
383
            $cell{value} = $celvalue;
328
		$colfield = "left($column,$deweydigits)";
384
        }
329
	}elsif (($column=~/lccn/) and ($lccndigits)) {
385
        else {
330
		$colfield = "left($column,$lccndigits)";
386
            $cell{rowtitle} = "NULL";
331
	}elsif (($column=~/itemcallnumber/) and ($cotedigits)) {
387
            $cell{value} = "zzEMPTY";
332
		$colfield = "left($column,$cotedigits)";
388
        }
333
	}else {
389
        $cell{totalrow} = 0;
334
		$colfield = $column;
390
        push @loopline, \%cell;
335
	}
391
    }
336
	
392
337
	my $strsth2 = "
393
    # 2nd, loop cols.
338
	SELECT distinctrow $colfield
394
    my $origcolumn = $column;
339
	FROM   biblioitems
395
    $column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
340
	INNER JOIN items
396
    my $colfield;
341
		USING (biblioitemnumber)
397
    if ( ( $column =~ /dewey/ ) and ($deweydigits) ) {
342
	WHERE $column IS NOT NULL ";
398
        $colfield = "left($column,$deweydigits)";
343
	$strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;
399
    } elsif ( ( $column =~ /lccn/ ) and ($lccndigits) ) {
344
	
400
        $colfield = "left($column,$lccndigits)";
345
	if (( @colfilter ) and ($colfilter[1])) {
401
    } elsif ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
346
		$strsth2 .= " AND $column> ? AND $column< ?";
402
        $colfield = "left($column,$cotedigits)";
347
	}elsif ($colfilter[0]){
403
    } elsif ( $column =~ /^deleteditems\.timestamp$/ ) {
348
		$colfilter[0] =~ s/\*/%/g;
404
        $colfield = "DATE($column)";
349
		$strsth2 .= " AND $column LIKE ? ";
405
    } else {
350
	} 
406
        $colfield = $column;
351
	$strsth2 .= " ORDER BY $colfield";
407
    }
352
	$debug and print STDERR "SQL: $strsth2";
408
353
	my $sth2 = $dbh->prepare( $strsth2 );
409
    my $strsth2 = "
354
	if ((@colfilter) and ($colfilter[1])) {
410
        SELECT distinctrow $colfield
355
		$sth2->execute($barcodefilter,$colfilter[0],$colfilter[1]);
411
        FROM   $itemstable
356
	} elsif ($colfilter[0]){
412
        LEFT JOIN biblioitems
357
		$sth2->execute($barcodefilter,$colfilter[0]);
413
            USING (biblioitemnumber)
358
	} elsif ($barcodefilter){
414
        WHERE 1 ";
359
	    $sth2->execute($barcodefilter);
415
    $strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;
360
	} else {
416
361
		$sth2->execute();
417
    if ( (@colfilter) and ( $colfilter[1] ) ) {
362
	}
418
        $strsth2 .= " AND $column >= ? AND $column <= ?";
363
 	while (my ($celvalue) = $sth2->fetchrow) {
419
    } elsif ( defined $colfilter[0] and $colfilter[0] ne '' ) {
364
 		my %cell;
420
        $colfilter[0] =~ s/\*/%/g;
365
		my %ft;
421
        $strsth2 .= " AND $column LIKE ? ";
366
		if ($celvalue) {
422
    }
367
			$cell{coltitle} = $celvalue;
423
    $strsth2 .= " ORDER BY $colfield";
368
#		} else {
424
    $debug and print STDERR "SQL: $strsth2";
369
#			$cell{coltitle} = "";
425
    my $sth2 = $dbh->prepare($strsth2);
370
		}
426
    if ( $barcodefilter and (@colfilter) and ( $colfilter[1] ) ) {
371
 		$ft{totalcol} = 0;
427
        $sth2->execute( $barcodefilter, $colfilter[0], $colfilter[1] );
372
		push @loopcol, \%cell;
428
    } elsif ( (@colfilter) and ( $colfilter[1] ) ) {
373
 	}
429
        $sth2->execute( $colfilter[0], $colfilter[1] );
374
	
430
    } elsif ( $barcodefilter && $colfilter[0] ) {
375
	my $i=0;
431
        $sth2->execute( $barcodefilter , $colfilter[0] );
376
	my @totalcol;
432
    } elsif ( $colfilter[0]) {
377
	my $hilighted=-1;
433
        $sth2->execute( $colfilter[0] );
378
	
434
    } elsif ($barcodefilter) {
379
	#Initialization of cell values.....
435
        $sth2->execute($barcodefilter);
380
	my %table;
436
    } else {
381
#	warn "init table";
437
        $sth2->execute();
382
	foreach my $row ( @loopline ) {
438
    }
383
		foreach my $col ( @loopcol ) {
439
    my $colauthvals = GetKohaAuthorisedValues($origcolumn);
384
#			warn " init table : $row->{rowtitle} / $col->{coltitle} ";
440
    while ( my ($celvalue) = $sth2->fetchrow ) {
385
			$table{$row->{rowtitle}}->{$col->{coltitle}}=0;
441
        my %cell;
386
		}
442
        if (defined $celvalue and $celvalue ne '') {
387
		$table{$row->{rowtitle}}->{totalrow}=0;
443
            if($colauthvals and $colauthvals->{$celvalue}) {
388
	}
444
                $cell{coltitle} = $colauthvals->{$celvalue};
389
445
            } else {
390
# preparing calculation
446
                $cell{coltitle} = $celvalue;
391
	my $strcalc = "SELECT $linefield, $colfield, count(*) FROM biblioitems INNER JOIN  items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1 ";
447
            }
392
	$strcalc .= "AND barcode $not like ? " if ($barcodefilter); 
448
            $cell{value} = $celvalue;
393
	
449
        }
394
	if (@$filters[0]){
450
        else {
395
		@$filters[0]=~ s/\*/%/g;
451
            $cell{coltitle} = "NULL";
396
		$strcalc .= " AND dewey >" . @$filters[0];
452
            $cell{value} = "zzEMPTY";
397
	}
453
        }
398
	if (@$filters[1]){
454
        $cell{totalcol} = 0;
399
		@$filters[1]=~ s/\*/%/g ;
455
        push @loopcol, \%cell;
400
		$strcalc .= " AND dewey <" . @$filters[1];
456
    }
401
	}
457
402
	if (@$filters[2]){
458
    my $i = 0;
403
		@$filters[2]=~ s/\*/%/g ;
459
    my @totalcol;
404
		$strcalc .= " AND lccn >" . @$filters[2];
460
    my $hilighted = -1;
405
	}
461
406
	if (@$filters[3]){
462
    #Initialization of cell values.....
407
		@$filters[3]=~ s/\*/%/g;
463
    my %table;
408
		$strcalc .= " AND lccn <" . @$filters[3];
464
409
	}
465
    foreach my $row (@loopline) {
410
	if (@$filters[4]){
466
        foreach my $col (@loopcol) {
411
		@$filters[4]=~ s/\*/%/g ;
467
            $table{ $row->{value} }->{ $col->{value} } = 0;
412
		$strcalc .= " AND items.itemcallnumber >=" . $dbh->quote(@$filters[4]);
468
        }
413
	}
469
        $table{ $row->{value} }->{totalrow} = 0;
414
	
470
    }
415
	if (@$filters[5]){
471
416
		@$filters[5]=~ s/\*/%/g;
472
    # preparing calculation
417
		$strcalc .= " AND items.itemcallnumber <=" . $dbh->quote(@$filters[5]);
473
    my $select_cellvalue = " COUNT(*) ";
418
	}
474
    $select_cellvalue = " COUNT(DISTINCT biblioitems.biblionumber) " if($cellvalue eq 'biblios');
419
	
475
    my $strcalc = "SELECT $linefield, $colfield, $select_cellvalue FROM $itemstable LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1 ";
420
	if (@$filters[6]){
476
    $strcalc .= "AND barcode $not like ? " if ($barcodefilter);
421
		@$filters[6]=~ s/\*/%/g;
477
422
		$strcalc .= " AND " . 
478
    if ( @$filters[0] ) {
423
			(C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype')
479
        @$filters[0] =~ s/\*/%/g;
424
			. " LIKE '" . @$filters[6] ."'";
480
        $strcalc .= " AND dewey >" . @$filters[0];
425
	}
481
    }
426
	
482
    if ( @$filters[1] ) {
427
	if (@$filters[7]){
483
        @$filters[1] =~ s/\*/%/g;
428
		@$filters[7]=~ s/\*/%/g;
484
        $strcalc .= " AND dewey <" . @$filters[1];
429
		@$filters[7].="%" unless @$filters[7]=~/%/;
485
    }
430
		$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] ."\"";
486
    if ( @$filters[2] ) {
431
	}
487
        @$filters[2] =~ s/\*/%/g;
432
	if (@$filters[8]){
488
        $strcalc .= " AND lccn >" . @$filters[2];
433
		@$filters[8]=~ s/\*/%/g;
489
    }
434
		$strcalc .= " AND publicationyear >" . @$filters[8];
490
    if ( @$filters[3] ) {
435
	}
491
        @$filters[3] =~ s/\*/%/g;
436
	if (@$filters[9]){
492
        $strcalc .= " AND lccn <" . @$filters[3];
437
		@$filters[9]=~ s/\*/%/g;
493
    }
438
		$strcalc .= " AND publicationyear <" . @$filters[9];
494
    if ( @$filters[4] ) {
439
	}
495
        @$filters[4] =~ s/\*/%/g;
440
	if (@$filters[10]){
496
        $strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[4] );
441
		@$filters[10]=~ s/\*/%/g;
497
    }
442
		$strcalc .= " AND items.homebranch LIKE '" . @$filters[10] ."'";
498
443
	}
499
    if ( @$filters[5] ) {
444
	if (@$filters[11]){
500
        @$filters[5] =~ s/\*/%/g;
445
		@$filters[11]=~ s/\*/%/g;
501
        $strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[5] );
446
		$strcalc .= " AND items.location LIKE '" . @$filters[11] ."'";
502
    }
447
	}
503
448
	if (@$filters[12]){
504
    if ( @$filters[6] ) {
449
		@$filters[12]=~ s/\*/%/g;
505
        @$filters[6] =~ s/\*/%/g;
450
		$strcalc .= " AND items.ccode  LIKE '" . @$filters[12] ."'";
506
        $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[6] . "'";
451
	}
507
    }
452
	
508
453
	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
509
    if ( @$filters[7] ) {
454
	$debug and warn "SQL: $strcalc";
510
        @$filters[7] =~ s/\*/%/g;
455
	my $dbcalc = $dbh->prepare($strcalc);
511
        @$filters[7] .= "%" unless @$filters[7] =~ /%/;
456
	if($barcodefilter){
512
        $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] . "\"";
457
	   $dbcalc->execute($barcodefilter);
513
    }
458
	}else{
514
    if ( @$filters[8] ) {
459
	   $dbcalc->execute();
515
        @$filters[8] =~ s/\*/%/g;
460
	}
516
        $strcalc .= " AND publicationyear >" . @$filters[8];
461
#	warn "filling table";
517
    }
462
	
518
    if ( @$filters[9] ) {
463
	my $emptycol; 
519
        @$filters[9] =~ s/\*/%/g;
464
	while (my ($row, $col, $value) = $dbcalc->fetchrow) {
520
        $strcalc .= " AND publicationyear <" . @$filters[9];
465
#		warn "filling table $row / $col / $value ";
521
    }
466
		$emptycol = 1    if (!defined($col));
522
    if ( @$filters[10] ) {
467
		$col = "zzEMPTY" if (!defined($col));
523
        @$filters[10] =~ s/\*/%/g;
468
		$row = "zzEMPTY" if (!defined($row));
524
        $strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[10] . "'";
469
		
525
    }
470
		$table{$row}->{$col}+=$value;
526
    if ( @$filters[11] ) {
471
		$table{$row}->{totalrow}+=$value;
527
        @$filters[11] =~ s/\*/%/g;
472
		$grantotal += $value;
528
        $strcalc .= " AND $itemstable.location LIKE '" . @$filters[11] . "'";
473
	}
529
    }
474
530
    if ( @$filters[12] ) {
475
# 	my %cell = {rowtitle => 'zzROWEMPTY'};
531
        @$filters[12] =~ s/\*/%/g;
476
# 	push @loopline,\%cell;
532
        $strcalc .= " AND $itemstable.ccode  LIKE '" . @$filters[12] . "'";
477
# 	undef %cell;
533
    }
478
# 	my %cell;
534
    if ( defined @$filters[13] and @$filters[13] ne '' ) {
479
# 	%cell = {coltitle => "zzEMPTY"};
535
        @$filters[13] =~ s/\*/%/g;
480
 	push @loopcol,{coltitle => "NULL"} if ($emptycol);
536
        $strcalc .= " AND $itemstable.notforloan  LIKE '" . @$filters[13] . "'";
481
	
537
    }
482
	foreach my $row ( sort keys %table ) {
538
    if ( defined @$filters[14] and @$filters[14] ne '' ) {
483
		my @loopcell;
539
        @$filters[14] =~ s/\*/%/g;
484
		#@loopcol ensures the order for columns is common with column titles
540
        $strcalc .= " AND $itemstable.materials  LIKE '" . @$filters[14] . "'";
485
		# and the number matches the number of columns
541
    }
486
		foreach my $col ( @loopcol ) {
542
    if ( defined @$filters[15] and @$filters[15] ne '' ) {
487
			my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
543
        @$filters[15] =~ s/\*/%/g;
488
			push @loopcell, {value => $value  } ;
544
        $strcalc .= " AND $itemstable.statisticvalue  LIKE '" . @$filters[15] . "'";
489
		}
545
    }
490
		push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
546
    if ( @$filters[18] ) {
491
						'loopcell' => \@loopcell,
547
        @$filters[18] =~ s/\*/%/g;
492
						'hilighted' => ($hilighted *= -1 > 0),
548
        $strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[18]' ";
493
						'totalrow' => $table{$row}->{totalrow}
549
    }
494
					};
550
    if ( @$filters[19] ) {
495
	}
551
        @$filters[19] =~ s/\*/%/g;
496
	
552
        $strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[19]' ";
497
#	warn "footer processing";
553
    }
498
	foreach my $col ( @loopcol ) {
554
    if ( $cellvalue eq 'deleteditems' and @$filters[20] ) {
499
		my $total=0;
555
        @$filters[20] =~ s/\*/%/g;
500
		foreach my $row ( @looprow ) {
556
        $strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[20]' ";
501
			$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
557
    }
502
#			warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
558
    if ( $cellvalue eq 'deleteditems' and @$filters[21] ) {
503
		}
559
        @$filters[21] =~ s/\*/%/g;
504
#		warn "summ for column ".$col->{coltitle}."  = ".$total;
560
        $strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[21]' ";
505
		push @loopfooter, {'totalcol' => $total};
561
    }
506
	}
562
    $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
507
			
563
    $debug and warn "SQL: $strcalc";
508
564
    my $dbcalc = $dbh->prepare($strcalc);
509
	# the header of the table
565
    if ($barcodefilter) {
510
	$globalline{loopfilter}=\@loopfilter;
566
        $dbcalc->execute($barcodefilter);
511
	# the core of the table
567
    } else {
512
	$globalline{looprow} = \@looprow;
568
        $dbcalc->execute();
513
 	$globalline{loopcol} = \@loopcol;
569
    }
514
# 	# the foot (totals by borrower type)
570
515
 	$globalline{loopfooter} = \@loopfooter;
571
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
516
 	$globalline{total}= $grantotal;
572
517
	$globalline{line} = $line;
573
        $col      = "zzEMPTY" if ( !defined($col) );
518
	$globalline{column} = $column;
574
        $row      = "zzEMPTY" if ( !defined($row) );
519
	push @mainloop,\%globalline;
575
520
	return \@mainloop;
576
        $table{$row}->{$col}     += $value;
577
        $table{$row}->{totalrow} += $value;
578
        $grantotal               += $value;
579
    }
580
581
    foreach my $row ( @loopline ) {
582
        my @loopcell;
583
584
        #@loopcol ensures the order for columns is common with column titles
585
        # and the number matches the number of columns
586
        foreach my $col (@loopcol) {
587
            my $value = $table{$row->{value}}->{ $col->{value} };
588
            push @loopcell, { value => $value };
589
        }
590
        push @looprow,
591
          { 'rowtitle' => $row->{rowtitle},
592
            'value'    => $row->{value},
593
            'loopcell' => \@loopcell,
594
            'hilighted' => ( $hilighted *= -1 > 0 ),
595
            'totalrow' => $table{$row->{value}}->{totalrow}
596
          };
597
    }
598
599
    foreach my $col (@loopcol) {
600
        my $total = 0;
601
        foreach my $row (@looprow) {
602
            $total += $table{ $row->{value} }->{ $col->{value} };
603
        }
604
605
        push @loopfooter, { 'totalcol' => $total };
606
    }
607
608
    # the header of the table
609
    $globalline{loopfilter} = \@loopfilter;
610
611
    # the core of the table
612
    $globalline{looprow} = \@looprow;
613
    $globalline{loopcol} = \@loopcol;
614
615
    # the foot (totals by borrower type)
616
    $globalline{loopfooter} = \@loopfooter;
617
    $globalline{total}      = $grantotal;
618
    $globalline{line}       = $line;
619
    $globalline{column}     = $column;
620
    push @mainloop, \%globalline;
621
    return \@mainloop;
521
}
622
}
522
623
523
1;
624
1;
524
- 

Return to bug 7683