3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
28 # use Date::Manip; # TODO: add not borrowed since date X criteria
33 Report that shows unborrowed items.
38 my $do_it = $input->param('do_it');
39 my $limit = $input->param("Limit") || 10;
40 my $column = $input->param("Criteria");
41 my @filters = $input->multi_param("Filter");
43 my ( $template, $borrowernumber, $cookie ) = get_template_and_user
(
45 template_name
=> "reports/catalogue_out.tt",
49 flagsrequired
=> { reports
=> 'execute_reports' },
54 $template->param( do_it
=> $do_it );
56 my $results = calculate
( $limit, $column, \
@filters );
57 $template->param( mainloop
=> $results );
58 output_html_with_http_headers
$input, $cookie, $template->output;
59 exit; # in either case, exit after do_it
62 my $itemtypes = Koha
::ItemTypes
->search_with_localization;
64 itemtypes
=> $itemtypes,
66 output_html_with_http_headers
$input, $cookie, $template->output;
69 my ( $limit, $column, $filters ) = @_;
74 my $dbh = C4
::Context
->dbh;
80 for ( my $i = 0 ; $i <= 6 ; $i++ ) {
81 if ( @
$filters[$i] ) {
82 my %cell = ( filter
=> @
$filters[$i] );
83 if ( ( $i == 1 ) and ( @
$filters[ $i - 1 ] ) ) {
84 $cell{err
} = 1 if ( @
$filters[$i] < @
$filters[ $i - 1 ] );
86 $cell{crit
} = "Branch" if ( $i == 0 );
87 $cell{crit
} = "Doc Type" if ( $i == 1 );
88 push @loopfilter, \
%cell;
91 push @loopfilter, { crit
=> 'limit', filter
=> $limit } if ($limit);
93 push @loopfilter, { crit
=> 'by', filter
=> $column };
94 my $tablename = ( $column =~ /branchcode/ ) ?
'branches' : 'items';
96 ( $column =~ /branchcode/ or $column =~ /itype/ )
97 ?
"$tablename.$column"
100 ( $tablename eq 'branches' )
101 ?
"SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column "
102 : "SELECT $column as coltitle, count(*) AS coltitle_count FROM $tablename ";
103 if ( $tablename eq 'branches' ) {
104 my $f = @
$filters[0];
106 $strsth2 .= " AND $column LIKE '$f' ";
108 $strsth2 .= " GROUP BY $column ORDER BY $column "; # needed for count
109 push @loopfilter, { crit
=> 'SQL', sql
=> 1, filter
=> $strsth2 };
110 $debug and warn "catalogue_out SQL: " . $strsth2;
111 my $sth2 = $dbh->prepare($strsth2);
114 while ( my ( $celvalue, $count ) = $sth2->fetchrow ) {
115 ($celvalue) or $celvalue = 'UNKNOWN';
116 $columns{$celvalue} = $count;
120 my %tables = ( map { $_ => [] } keys %columns );
122 # preparing calculation
125 SELECT items.barcode as barcode,
126 items.homebranch as branch,
127 items.itemcallnumber as itemcallnumber,
128 biblio.title as title,
129 biblio.biblionumber as biblionumber,
130 biblio.author as author";
131 ($column) and $query .= ",\n$column as col ";
134 LEFT JOIN biblio USING (biblionumber)
135 LEFT JOIN issues USING (itemnumber)
136 LEFT JOIN old_issues USING (itemnumber)
137 WHERE issues.itemnumber IS NULL
138 AND old_issues.itemnumber IS NULL
140 if ( $filters->[0] ) {
141 $filters->[0] =~ s/\*/%/g;
142 push @exe_args, $filters->[0];
143 $query .= " AND items.homebranch LIKE ?";
145 if ( $filters->[1] ) {
146 $filters->[1] =~ s/\*/%/g;
147 push @exe_args, $filters->[1];
148 $query .= " AND items.itype LIKE ?";
151 $query .= " AND $column = ? GROUP BY items.itemnumber, $column ";
152 # placeholder handled below
155 $query .= " GROUP BY items.itemnumber ";
157 $query .= " ORDER BY items.itemcallnumber DESC, barcode";
158 $query .= " LIMIT 0,$limit" if ($limit);
159 $debug and warn "SQL : $query";
161 # warn "SQL : $query";
162 push @loopfilter, { crit
=> 'SQL', sql
=> 1, filter
=> $query };
163 my $dbcalc = $dbh->prepare($query);
166 foreach ( sort keys %columns ) {
167 # execute(@exe_args,$_) would fail when the array is empty
168 # but @more_exe_args will work
169 my (@more_exe_args) = @exe_args;
170 push @more_exe_args, $_;
171 $dbcalc->execute(@more_exe_args)
172 or die "Query execute(@more_exe_args) failed: $query";
173 while ( my $data = $dbcalc->fetchrow_hashref ) {
174 my $col = $data->{col
} || 'NULL';
175 $tables{$col} or $tables{$col} = [];
176 push @
{ $tables{$col} }, $data;
181 ( scalar @exe_args ) ?
$dbcalc->execute(@exe_args) : $dbcalc->execute;
182 while ( my $data = $dbcalc->fetchrow_hashref ) {
183 my $col = $data->{col
} || 'NULL';
184 $tables{$col} or $tables{$col} = [];
185 push @
{ $tables{$col} }, $data;
189 foreach my $tablename ( sort keys %tables ) {
192 foreach my $cell ( @
{ $tables{$tablename} } ) {
193 if ( 0 == $i++ and $debug ) {
194 my $dump = Dumper
($cell);
197 print STDERR
"first cell for $tablename: $dump";
199 push @temptable, $cell;
201 my $count = scalar(@temptable);
202 my $allitems = $columns{$tablename};
203 $globalline{total_looptable_count
} += $count;
204 $globalline{total_coltitle_count
} += $allitems;
205 push @
{ $globalline{looptables
} },
207 looprow
=> \
@temptable,
208 coltitle
=> $tablename,
209 coltitle_count
=> $allitems,
210 looptable_count
=> $count,
211 looptable_first
=> ($count) ?
$temptable[0]->{itemcallnumber
} : '',
212 looptable_last
=> ($count) ?
$temptable[-1]->{itemcallnumber
} : '',
216 # the header of the table
217 $globalline{loopfilter
} = \
@loopfilter;
218 $globalline{limit
} = $limit;
219 $globalline{column
} = $column;
220 return [ ( \
%globalline ) ]; # reference to array of reference to hash