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

Return to bug 7683