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 under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
25 use C4::Branch; # GetBranches
35 Report that shows unborrowed items.
40 my $fullreportname = "reports/catalogue_out.tmpl";
41 my $do_it = $input->param('do_it');
42 my $limit = $input->param("Limit");
43 my $column = $input->param("Criteria");
44 my @filters = $input->param("Filter");
45 my $output = $input->param("output");
46 my $basename = $input->param("basename") || 'catalogue_out';
47 my $mime = $input->param("MIME");
48 my ($template, $borrowernumber, $cookie)
49 = get_template_and_user({template_name => $fullreportname,
53 flagsrequired => {reports => 1},
57 our $sep = $input->param("sep");
58 $sep = "\t" if ($sep eq 'tabulation');
60 $template->param(do_it => $do_it);
62 my $results = calculate($limit, $column, \@filters);
63 if ($output eq "screen") {
64 # Printing results to screen
65 $template->param(mainloop => $results);
66 output_html_with_http_headers $input, $cookie, $template->output;
68 # Printing to a csv file FIXME: This is broken rather badly, if it ever worked at all here.
70 -type => 'application/vnd.sun.xml.calc',
72 -attachment=>"$basename.csv",
73 -filename =>"$basename.csv" );
74 my $cols = @$results[0]->{loopcol};
75 my $lines = @$results[0]->{looprow};
77 print "num /". @$results[0]->{column} .$sep;
79 foreach my $col ( @$cols ) {
80 print $col->{coltitle}.$sep;
84 foreach my $line ( @$lines ) {
85 my $x = $line->{loopcell}; # FIXME: No Such thing.
86 print $line->{rowtitle}.$sep;
87 foreach my $cell (@$x) {
88 print $cell->{value}.$sep;
90 print $line->{totalrow}, "\n";
94 foreach my $col ( @$cols ) {
95 print $sep.$col->{totalcol};
97 print $sep.@$results[0]->{total};
99 exit(1); # in either case, exit after do_it
102 # Displaying choices (i.e., not do_it)
103 my $dbh = C4::Context->dbh;
107 my @mime = (C4::Context->preference("MIME"));
108 my $CGIextChoice = CGI::scrolling_list(
115 my $CGIsepChoice = GetDelimiterChoices;
117 my $itemtypes = GetItemTypes;
119 foreach (keys %$itemtypes) {
120 push @itemtypeloop, {
122 # selected => ($_ eq $itemtype) ? 1 : 0,
123 description => $itemtypes->{$_}->{'description'},
126 my $branches = GetBranches;
128 foreach (keys %$branches) {
131 # selected => ($_ eq $branch) ? 1 : 0,
132 branchname => $branches->{$_}->{'branchname'},
137 CGIextChoice => $CGIextChoice,
138 CGIsepChoice => $CGIsepChoice,
139 itemtypeloop =>\@itemtypeloop,
140 branchloop =>\@branchloop,
142 output_html_with_http_headers $input, $cookie, $template->output;
146 my ($limit, $column, $filters) = @_;
151 my $dbh = C4::Context->dbh;
157 for (my $i=0;$i<=6;$i++) {
158 if ( @$filters[$i] ) {
159 my %cell = (filter=>@$filters[$i]);
160 if (($i==1) and (@$filters[$i-1])) {
161 $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
163 $cell{crit} = "Branch" if ($i==0);
164 $cell{crit} = "Doc Type" if ($i==1);
165 push @loopfilter, \%cell;
168 push @loopfilter, {crit=>'limit', filter=>$limit} if ($limit);
170 push @loopfilter, {crit=>'by', filter=>$column};
171 my $tablename = ($column =~ /branchcode/) ? 'branches' : 'items';
172 $column = ($column =~ /branchcode/ or $column =~ /itype/) ? "$tablename.$column" : $column;
173 my $strsth2 = ($tablename eq 'branches') ?
174 "SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column " :
175 "SELECT $column as coltitle, count(*) AS coltitle_count FROM $tablename " ;
176 if ($tablename eq 'branches') {
177 my $f = @$filters[0];
179 $strsth2 .= " AND $column LIKE '$f' " ;
181 $strsth2 .=" GROUP BY $column ORDER BY $column "; # needed for count
182 push @loopfilter, {crit=>'SQL', sql=>1, filter=>$strsth2};
183 $debug and warn "catalogue_out SQL: ". $strsth2;
184 my $sth2 = $dbh->prepare($strsth2);
187 while (my ($celvalue, $count) = $sth2->fetchrow) {
188 ($celvalue) or $celvalue = 'UNKNOWN';
189 $columns{$celvalue} = $count;
193 my %tables = (map {$_=>[]} keys %columns);
195 # preparing calculation
198 SELECT items.barcode as barcode,
199 items.homebranch as branch,
200 items.itemcallnumber as itemcallnumber,
201 biblio.title as title,
202 biblio.biblionumber as biblionumber,
203 biblio.author as author";
204 ($column) and $query .= ",\n$column as col ";
207 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
208 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
209 LEFT JOIN old_issues ON old_issues.itemnumber = items.itemnumber
210 WHERE old_issues.itemnumber IS NULL
213 $filters->[0]=~ s/\*/%/g;
214 push @exe_args, $filters->[0];
215 $query .= " AND items.homebranch LIKE ?";
218 $filters->[1]=~ s/\*/%/g;
219 push @exe_args, $filters->[1];
220 $query .= " AND biblioitems.itemtype LIKE ?";
223 $query .= " AND $column = ? GROUP BY items.itemnumber, $column "; # placeholder handled below
225 $query .= " GROUP BY items.itemnumber ";
227 $query .= " ORDER BY items.itemcallnumber DESC, barcode";
228 $query .= " LIMIT 0,$limit" if ($limit);
229 $debug and warn "SQL : $query";
230 # warn "SQL : $query";
231 push @loopfilter, {crit=>'SQL', sql=>1, filter=>$query};
232 my $dbcalc = $dbh->prepare($query);
235 foreach (sort keys %columns) {
236 my (@more_exe_args) = @exe_args; # execute(@exe_args,$_) would fail when the array is empty.
237 push @more_exe_args, $_; # but @more_exe_args will work
238 $dbcalc->execute(@more_exe_args) or die "Query execute(@more_exe_args) failed: $query";
239 while (my $data = $dbcalc->fetchrow_hashref) {
240 my $col = $data->{col} || 'NULL';
241 $tables{$col} or $tables{$col} = [];
242 push @{$tables{$col}}, $data;
246 (scalar @exe_args) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
247 while (my $data = $dbcalc->fetchrow_hashref) {
248 my $col = $data->{col} || 'NULL';
249 $tables{$col} or $tables{$col} = [];
250 push @{$tables{$col}}, $data;
254 foreach my $tablename (sort keys %tables) {
257 foreach my $cell (@{$tables{$tablename}}) {
258 if (0 == $i++ and $debug) {
259 my $dump = Dumper($cell);
262 print STDERR "first cell for $tablename: $dump";
264 push @temptable, $cell;
266 my $count = scalar(@temptable);
267 my $allitems = $columns{$tablename};
268 $globalline{total_looptable_count} += $count;
269 $globalline{total_coltitle_count} += $allitems;
270 push @{$globalline{looptables}}, {
271 looprow => \@temptable,
272 coltitle => $tablename,
273 coltitle_count => $allitems,
274 looptable_count => $count,
275 looptable_first => ($count) ? $temptable[ 0]->{itemcallnumber} : '',
276 looptable_last => ($count) ? $temptable[-1]->{itemcallnumber} : '',
280 # the header of the table
281 $globalline{loopfilter}=\@loopfilter;
282 $globalline{limit} = $limit;
283 $globalline{column} = $column;
284 return [(\%globalline)]; # reference to array of reference to hash