Bug 25650: Add location and itype descriptions in ILS-DI GetRecords
[koha.git] / reports / catalogue_out.pl
blobbea7fe9bf2f4f4b0ac1c2a3fc929ae5bb1d1a434
1 #!/usr/bin/perl
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>.
20 use Modern::Perl;
21 use CGI qw ( -utf8 );
23 use C4::Auth;
24 use C4::Context;
25 use C4::Debug;
26 use C4::Output;
27 # use Date::Manip; # TODO: add not borrowed since date X criteria
28 use Data::Dumper;
30 =head1 catalogue_out
32 Report that shows unborrowed items.
34 =cut
36 my $input = CGI->new;
37 my $do_it = $input->param('do_it');
38 my $limit = $input->param("Limit") || 10;
39 my $column = $input->param("Criteria");
40 my @filters = $input->multi_param("Filter");
42 my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
44 template_name => "reports/catalogue_out.tt",
45 query => $input,
46 type => "intranet",
47 flagsrequired => { reports => 'execute_reports' },
48 debug => 1,
52 $template->param( do_it => $do_it );
53 if ($do_it) {
54 my $results = calculate( $limit, $column, \@filters );
55 $template->param( mainloop => $results );
56 output_html_with_http_headers $input, $cookie, $template->output;
57 exit; # in either case, exit after do_it
60 my $itemtypes = Koha::ItemTypes->search_with_localization;
61 $template->param(
62 itemtypes => $itemtypes,
64 output_html_with_http_headers $input, $cookie, $template->output;
66 sub calculate {
67 my ( $limit, $column, $filters ) = @_;
68 my %globalline;
69 my %columns = ();
70 my $dbh = C4::Context->dbh;
72 # Filters
73 # Checking filters
75 my @loopfilter;
76 for ( my $i = 0 ; $i <= 6 ; $i++ ) {
77 if ( @$filters[$i] ) {
78 my %cell = ( filter => @$filters[$i] );
79 if ( ( $i == 1 ) and ( @$filters[ $i - 1 ] ) ) {
80 $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
82 $cell{crit} = "Branch" if ( $i == 0 );
83 $cell{crit} = "Doc Type" if ( $i == 1 );
84 push @loopfilter, \%cell;
87 push @loopfilter, { crit => 'limit', filter => $limit } if ($limit);
88 if ($column) {
89 push @loopfilter, { crit => 'by', filter => $column };
90 my $tablename = ( $column =~ /branchcode/ ) ? 'branches' : 'items';
91 $column =
92 ( $column =~ /branchcode/ or $column =~ /itype/ )
93 ? "$tablename.$column"
94 : $column;
95 my $strsth2 =
96 ( $tablename eq 'branches' )
97 ? "SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column "
98 : "SELECT $column as coltitle, count(*) AS coltitle_count FROM $tablename ";
99 if ( $tablename eq 'branches' ) {
100 my $f = @$filters[0];
101 $f =~ s/\*/%/g;
102 $strsth2 .= " AND $column LIKE '$f' ";
104 $strsth2 .= " GROUP BY $column ORDER BY $column "; # needed for count
105 push @loopfilter, { crit => 'SQL', sql => 1, filter => $strsth2 };
106 $debug and warn "catalogue_out SQL: " . $strsth2;
107 my $sth2 = $dbh->prepare($strsth2);
108 $sth2->execute;
110 while ( my ( $celvalue, $count ) = $sth2->fetchrow ) {
111 ($celvalue) or $celvalue = 'UNKNOWN';
112 $columns{$celvalue} = $count;
116 my %tables = ( map { $_ => [] } keys %columns );
118 # preparing calculation
119 my @exe_args = ();
120 my $query = "
121 SELECT items.barcode as barcode,
122 items.homebranch as branch,
123 items.itemcallnumber as itemcallnumber,
124 biblio.title as title,
125 biblio.biblionumber as biblionumber,
126 biblio.author as author";
127 ($column) and $query .= ",\n$column as col ";
128 $query .= "
129 FROM items
130 LEFT JOIN biblio USING (biblionumber)
131 LEFT JOIN issues USING (itemnumber)
132 LEFT JOIN old_issues USING (itemnumber)
133 WHERE issues.itemnumber IS NULL
134 AND old_issues.itemnumber IS NULL
136 if ( $filters->[0] ) {
137 $filters->[0] =~ s/\*/%/g;
138 push @exe_args, $filters->[0];
139 $query .= " AND items.homebranch LIKE ?";
141 if ( $filters->[1] ) {
142 $filters->[1] =~ s/\*/%/g;
143 push @exe_args, $filters->[1];
144 $query .= " AND items.itype LIKE ?";
146 if ($column) {
147 $query .= " AND $column = ? GROUP BY items.itemnumber, $column ";
148 # placeholder handled below
150 else {
151 $query .= " GROUP BY items.itemnumber ";
153 $query .= " ORDER BY items.itemcallnumber DESC, barcode";
154 $query .= " LIMIT 0,$limit" if ($limit);
155 $debug and warn "SQL : $query";
157 # warn "SQL : $query";
158 push @loopfilter, { crit => 'SQL', sql => 1, filter => $query };
159 my $dbcalc = $dbh->prepare($query);
161 if ($column) {
162 foreach ( sort keys %columns ) {
163 # execute(@exe_args,$_) would fail when the array is empty
164 # but @more_exe_args will work
165 my (@more_exe_args) = @exe_args;
166 push @more_exe_args, $_;
167 $dbcalc->execute(@more_exe_args)
168 or die "Query execute(@more_exe_args) failed: $query";
169 while ( my $data = $dbcalc->fetchrow_hashref ) {
170 my $col = $data->{col} || 'NULL';
171 $tables{$col} or $tables{$col} = [];
172 push @{ $tables{$col} }, $data;
176 else {
177 ( scalar @exe_args ) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
178 while ( my $data = $dbcalc->fetchrow_hashref ) {
179 my $col = $data->{col} || 'NULL';
180 $tables{$col} or $tables{$col} = [];
181 push @{ $tables{$col} }, $data;
185 foreach my $tablename ( sort keys %tables ) {
186 my (@temptable);
187 my $i = 0;
188 foreach my $cell ( @{ $tables{$tablename} } ) {
189 if ( 0 == $i++ and $debug ) {
190 my $dump = Dumper($cell);
191 $dump =~ s/\n/ /gs;
192 $dump =~ s/\s+/ /gs;
193 print STDERR "first cell for $tablename: $dump";
195 push @temptable, $cell;
197 my $count = scalar(@temptable);
198 my $allitems = $columns{$tablename};
199 $globalline{total_looptable_count} += $count;
200 $globalline{total_coltitle_count} += $allitems;
201 push @{ $globalline{looptables} },
203 looprow => \@temptable,
204 coltitle => $tablename,
205 coltitle_count => $allitems,
206 looptable_count => $count,
207 looptable_first => ($count) ? $temptable[0]->{itemcallnumber} : '',
208 looptable_last => ($count) ? $temptable[-1]->{itemcallnumber} : '',
212 # the header of the table
213 $globalline{loopfilter} = \@loopfilter;
214 $globalline{limit} = $limit;
215 $globalline{column} = $column;
216 return [ ( \%globalline ) ]; # reference to array of reference to hash
220 __END__