Lines 24-34
use CGI;
Link Here
|
24 |
use C4::Auth; |
24 |
use C4::Auth; |
25 |
use C4::Context; |
25 |
use C4::Context; |
26 |
use C4::Debug; |
26 |
use C4::Debug; |
27 |
use C4::Branch; # GetBranches |
27 |
use C4::Branch; # GetBranchesLoop |
28 |
use C4::Output; |
28 |
use C4::Output; |
29 |
use C4::Koha; # GetItemTypes |
29 |
use C4::Koha; # GetItemTypes |
30 |
use C4::Reports; # GetDelimiterChoices |
|
|
31 |
use C4::Circulation; |
32 |
# use Date::Manip; # TODO: add not borrowed since date X criteria |
30 |
# use Date::Manip; # TODO: add not borrowed since date X criteria |
33 |
use Data::Dumper; |
31 |
use Data::Dumper; |
34 |
|
32 |
|
Lines 38-188
Report that shows unborrowed items.
Link Here
|
38 |
|
36 |
|
39 |
=cut |
37 |
=cut |
40 |
|
38 |
|
41 |
my $input = new CGI; |
39 |
my $input = new CGI; |
42 |
my $do_it = $input->param('do_it'); |
40 |
my $do_it = $input->param('do_it'); |
43 |
my $limit = $input->param("Limit"); |
41 |
my $limit = $input->param("Limit") || 10; |
44 |
my $column = $input->param("Criteria"); |
42 |
my $column = $input->param("Criteria"); |
45 |
my @filters = $input->param("Filter"); |
43 |
my @filters = $input->param("Filter"); |
46 |
my $output = $input->param("output"); |
44 |
|
47 |
my $basename = $input->param("basename") || 'catalogue_out'; |
45 |
my ( $template, $borrowernumber, $cookie ) = get_template_and_user( |
48 |
my ($template, $borrowernumber, $cookie) = get_template_and_user({ |
46 |
{ |
49 |
template_name => "reports/catalogue_out.tmpl", |
47 |
template_name => "reports/catalogue_out.tmpl", |
50 |
query => $input, |
48 |
query => $input, |
51 |
type => "intranet", |
49 |
type => "intranet", |
52 |
authnotrequired => 0, |
50 |
authnotrequired => 0, |
53 |
flagsrequired => {reports => '*'}, |
51 |
flagsrequired => { reports => 'execute_reports' }, |
54 |
debug => 1, |
52 |
debug => 1, |
55 |
}); |
53 |
} |
56 |
|
54 |
); |
57 |
our $sep = $input->param("sep"); |
|
|
58 |
$sep = "\t" if ((! defined $sep) or $sep eq 'tabulation'); |
59 |
|
55 |
|
60 |
$template->param(do_it => $do_it); |
56 |
$template->param( do_it => $do_it ); |
61 |
if ($do_it) { |
57 |
if ($do_it) { |
62 |
my $results = calculate($limit, $column, \@filters); |
58 |
my $results = calculate( $limit, $column, \@filters ); |
63 |
if ($output eq "screen") { |
59 |
$template->param( mainloop => $results ); |
64 |
# Printing results to screen |
60 |
output_html_with_http_headers $input, $cookie, $template->output; |
65 |
$template->param(mainloop => $results); |
61 |
exit; # in either case, exit after do_it |
66 |
output_html_with_http_headers $input, $cookie, $template->output; |
|
|
67 |
} else { |
68 |
# Printing to a csv file FIXME: This is broken rather badly, if it ever worked at all here. |
69 |
print $input->header( |
70 |
-type => 'application/vnd.sun.xml.calc', |
71 |
-encoding => 'utf-8', |
72 |
-attachment=>"$basename.csv", |
73 |
-filename =>"$basename.csv" ); |
74 |
my $cols = @$results[0]->{loopcol}; |
75 |
my $lines = @$results[0]->{looprow}; |
76 |
# header |
77 |
print "num /". @$results[0]->{column} .$sep; |
78 |
# Other header |
79 |
foreach my $col ( @$cols ) { |
80 |
print $col->{coltitle}.$sep; |
81 |
} |
82 |
print "Total\n"; |
83 |
# Table |
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; |
89 |
} |
90 |
print $line->{totalrow}, "\n"; |
91 |
} |
92 |
# footer |
93 |
print "TOTAL"; |
94 |
foreach my $col ( @$cols ) { |
95 |
print $sep.$col->{totalcol}; |
96 |
} |
97 |
print $sep.@$results[0]->{total}; |
98 |
} |
99 |
exit; # in either case, exit after do_it |
100 |
} |
62 |
} |
101 |
|
63 |
|
102 |
# Displaying choices (i.e., not do_it) |
64 |
# Displaying choices (i.e., not do_it) |
103 |
my @values; |
65 |
my $itemtypes = GetItemTypes(); |
104 |
my %select; |
|
|
105 |
|
106 |
my @mime = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation |
107 |
my $itemtypes = GetItemTypes; |
108 |
my @itemtypeloop; |
66 |
my @itemtypeloop; |
109 |
foreach (sort {$itemtypes->{$a}->{description} cmp $itemtypes->{$b}->{description}} keys %$itemtypes) { |
67 |
foreach ( |
110 |
push @itemtypeloop, { |
68 |
sort { $itemtypes->{$a}->{'description'} cmp $itemtypes->{$b}->{'description'} } |
111 |
value => $_, |
69 |
keys %$itemtypes |
112 |
description => $itemtypes->{$_}->{'description'}, |
70 |
) |
113 |
}; |
71 |
{ |
|
|
72 |
push @itemtypeloop, |
73 |
{ |
74 |
value => $_, |
75 |
description => $itemtypes->{$_}->{'description'}, |
76 |
}; |
114 |
} |
77 |
} |
115 |
|
78 |
|
116 |
$template->param( |
79 |
$template->param( |
117 |
CGIextChoice => \@mime, |
80 |
itemtypeloop => \@itemtypeloop, |
118 |
CGIsepChoice => GetDelimiterChoices, |
81 |
branchloop => GetBranchesLoop(), |
119 |
itemtypeloop => \@itemtypeloop, |
|
|
120 |
branchloop => GetBranchesLoop($input->param("branch") || C4::Context->userenv->{branch}), |
121 |
); |
82 |
); |
122 |
output_html_with_http_headers $input, $cookie, $template->output; |
83 |
output_html_with_http_headers $input, $cookie, $template->output; |
123 |
|
84 |
|
124 |
|
|
|
125 |
sub calculate { |
85 |
sub calculate { |
126 |
my ($limit, $column, $filters) = @_; |
86 |
my ( $limit, $column, $filters ) = @_; |
127 |
my @loopline; |
87 |
my @loopline; |
128 |
my @looprow; |
88 |
my @looprow; |
129 |
my %globalline; |
89 |
my %globalline; |
130 |
my %columns = (); |
90 |
my %columns = (); |
131 |
my $dbh = C4::Context->dbh; |
91 |
my $dbh = C4::Context->dbh; |
132 |
|
92 |
|
133 |
# Filters |
93 |
# Filters |
134 |
# Checking filters |
94 |
# Checking filters |
135 |
# |
95 |
# |
136 |
my @loopfilter; |
96 |
my @loopfilter; |
137 |
for (my $i=0;$i<=6;$i++) { |
97 |
for ( my $i = 0 ; $i <= 6 ; $i++ ) { |
138 |
if ( @$filters[$i] ) { |
98 |
if ( @$filters[$i] ) { |
139 |
my %cell = (filter=>@$filters[$i]); |
99 |
my %cell = ( filter => @$filters[$i] ); |
140 |
if (($i==1) and (@$filters[$i-1])) { |
100 |
if ( ( $i == 1 ) and ( @$filters[ $i - 1 ] ) ) { |
141 |
$cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ; |
101 |
$cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] ); |
142 |
} |
102 |
} |
143 |
$cell{crit} = "Branch" if ($i==0); |
103 |
$cell{crit} = "Branch" if ( $i == 0 ); |
144 |
$cell{crit} = "Doc Type" if ($i==1); |
104 |
$cell{crit} = "Doc Type" if ( $i == 1 ); |
145 |
push @loopfilter, \%cell; |
105 |
push @loopfilter, \%cell; |
146 |
} |
106 |
} |
147 |
} |
107 |
} |
148 |
push @loopfilter, {crit=>'limit', filter=>$limit} if ($limit); |
108 |
push @loopfilter, { crit => 'limit', filter => $limit } if ($limit); |
149 |
if ($column){ |
109 |
if ($column) { |
150 |
push @loopfilter, {crit=>'by', filter=>$column}; |
110 |
push @loopfilter, { crit => 'by', filter => $column }; |
151 |
my $tablename = ($column =~ /branchcode/) ? 'branches' : 'items'; |
111 |
my $tablename = ( $column =~ /branchcode/ ) ? 'branches' : 'items'; |
152 |
$column = ($column =~ /branchcode/ or $column =~ /itype/) ? "$tablename.$column" : $column; |
112 |
$column = |
153 |
my $strsth2 = ($tablename eq 'branches') ? |
113 |
( $column =~ /branchcode/ or $column =~ /itype/ ) |
154 |
"SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column " : |
114 |
? "$tablename.$column" |
155 |
"SELECT $column as coltitle, count(*) AS coltitle_count FROM $tablename " ; |
115 |
: $column; |
156 |
if ($tablename eq 'branches') { |
116 |
my $strsth2 = |
157 |
my $f = @$filters[0]; |
117 |
( $tablename eq 'branches' ) |
|
|
118 |
? "SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column " |
119 |
: "SELECT $column as coltitle, count(*) AS coltitle_count FROM $tablename "; |
120 |
if ( $tablename eq 'branches' ) { |
121 |
my $f = @$filters[0]; |
158 |
$f =~ s/\*/%/g; |
122 |
$f =~ s/\*/%/g; |
159 |
$strsth2 .= " AND $column LIKE '$f' " ; |
123 |
$strsth2 .= " AND $column LIKE '$f' "; |
160 |
} |
124 |
} |
161 |
$strsth2 .=" GROUP BY $column ORDER BY $column "; # needed for count |
125 |
$strsth2 .= " GROUP BY $column ORDER BY $column "; # needed for count |
162 |
push @loopfilter, {crit=>'SQL', sql=>1, filter=>$strsth2}; |
126 |
push @loopfilter, { crit => 'SQL', sql => 1, filter => $strsth2 }; |
163 |
$debug and warn "catalogue_out SQL: ". $strsth2; |
127 |
$debug and warn "catalogue_out SQL: " . $strsth2; |
164 |
my $sth2 = $dbh->prepare($strsth2); |
128 |
my $sth2 = $dbh->prepare($strsth2); |
165 |
$sth2->execute; |
129 |
$sth2->execute; |
166 |
|
130 |
|
167 |
while (my ($celvalue, $count) = $sth2->fetchrow) { |
131 |
while ( my ( $celvalue, $count ) = $sth2->fetchrow ) { |
168 |
($celvalue) or $celvalue = 'UNKNOWN'; |
132 |
($celvalue) or $celvalue = 'UNKNOWN'; |
169 |
$columns{$celvalue} = $count; |
133 |
$columns{$celvalue} = $count; |
170 |
} |
134 |
} |
171 |
} |
135 |
} |
172 |
|
|
|
173 |
my %tables = (map {$_=>[]} keys %columns); |
174 |
|
136 |
|
175 |
# preparing calculation |
137 |
my %tables = ( map { $_ => [] } keys %columns ); |
176 |
my @exe_args = (); |
138 |
|
177 |
my $query = " |
139 |
# preparing calculation |
|
|
140 |
my @exe_args = (); |
141 |
my $query = " |
178 |
SELECT items.barcode as barcode, |
142 |
SELECT items.barcode as barcode, |
179 |
items.homebranch as branch, |
143 |
items.homebranch as branch, |
180 |
items.itemcallnumber as itemcallnumber, |
144 |
items.itemcallnumber as itemcallnumber, |
181 |
biblio.title as title, |
145 |
biblio.title as title, |
182 |
biblio.biblionumber as biblionumber, |
146 |
biblio.biblionumber as biblionumber, |
183 |
biblio.author as author"; |
147 |
biblio.author as author"; |
184 |
($column) and $query .= ",\n$column as col "; |
148 |
($column) and $query .= ",\n$column as col "; |
185 |
$query .= " |
149 |
$query .= " |
186 |
FROM items |
150 |
FROM items |
187 |
LEFT JOIN biblio USING (biblionumber) |
151 |
LEFT JOIN biblio USING (biblionumber) |
188 |
LEFT JOIN issues USING (itemnumber) |
152 |
LEFT JOIN issues USING (itemnumber) |
Lines 190-268
sub calculate {
Link Here
|
190 |
WHERE issues.itemnumber IS NULL |
154 |
WHERE issues.itemnumber IS NULL |
191 |
AND old_issues.itemnumber IS NULL |
155 |
AND old_issues.itemnumber IS NULL |
192 |
"; |
156 |
"; |
193 |
if ($filters->[0]) { |
157 |
if ( $filters->[0] ) { |
194 |
$filters->[0]=~ s/\*/%/g; |
158 |
$filters->[0] =~ s/\*/%/g; |
195 |
push @exe_args, $filters->[0]; |
159 |
push @exe_args, $filters->[0]; |
196 |
$query .= " AND items.homebranch LIKE ?"; |
160 |
$query .= " AND items.homebranch LIKE ?"; |
197 |
} |
161 |
} |
198 |
if ($filters->[1]) { |
162 |
if ( $filters->[1] ) { |
199 |
$filters->[1]=~ s/\*/%/g; |
163 |
$filters->[1] =~ s/\*/%/g; |
200 |
push @exe_args, $filters->[1]; |
164 |
push @exe_args, $filters->[1]; |
201 |
$query .= " AND items.itype LIKE ?"; |
165 |
$query .= " AND items.itype LIKE ?"; |
202 |
} |
166 |
} |
203 |
if ($column) { |
167 |
if ($column) { |
204 |
$query .= " AND $column = ? GROUP BY items.itemnumber, $column "; # placeholder handled below |
168 |
$query .= " AND $column = ? GROUP BY items.itemnumber, $column "; |
205 |
} else { |
169 |
# placeholder handled below |
206 |
$query .= " GROUP BY items.itemnumber "; |
170 |
} |
207 |
} |
171 |
else { |
208 |
$query .= " ORDER BY items.itemcallnumber DESC, barcode"; |
172 |
$query .= " GROUP BY items.itemnumber "; |
|
|
173 |
} |
174 |
$query .= " ORDER BY items.itemcallnumber DESC, barcode"; |
209 |
$query .= " LIMIT 0,$limit" if ($limit); |
175 |
$query .= " LIMIT 0,$limit" if ($limit); |
210 |
$debug and warn "SQL : $query"; |
176 |
$debug and warn "SQL : $query"; |
|
|
177 |
|
211 |
# warn "SQL : $query"; |
178 |
# warn "SQL : $query"; |
212 |
push @loopfilter, {crit=>'SQL', sql=>1, filter=>$query}; |
179 |
push @loopfilter, { crit => 'SQL', sql => 1, filter => $query }; |
213 |
my $dbcalc = $dbh->prepare($query); |
180 |
my $dbcalc = $dbh->prepare($query); |
214 |
|
181 |
|
215 |
if ($column) { |
182 |
if ($column) { |
216 |
foreach (sort keys %columns) { |
183 |
foreach ( sort keys %columns ) { |
217 |
my (@more_exe_args) = @exe_args; # execute(@exe_args,$_) would fail when the array is empty. |
184 |
# execute(@exe_args,$_) would fail when the array is empty |
218 |
push @more_exe_args, $_; # but @more_exe_args will work |
185 |
# but @more_exe_args will work |
219 |
$dbcalc->execute(@more_exe_args) or die "Query execute(@more_exe_args) failed: $query"; |
186 |
my (@more_exe_args) = @exe_args; |
220 |
while (my $data = $dbcalc->fetchrow_hashref) { |
187 |
push @more_exe_args, $_; |
221 |
my $col = $data->{col} || 'NULL'; |
188 |
$dbcalc->execute(@more_exe_args) |
222 |
$tables{$col} or $tables{$col} = []; |
189 |
or die "Query execute(@more_exe_args) failed: $query"; |
223 |
push @{$tables{$col}}, $data; |
190 |
while ( my $data = $dbcalc->fetchrow_hashref ) { |
224 |
} |
191 |
my $col = $data->{col} || 'NULL'; |
225 |
} |
192 |
$tables{$col} or $tables{$col} = []; |
226 |
} else { |
193 |
push @{ $tables{$col} }, $data; |
227 |
(scalar @exe_args) ? $dbcalc->execute(@exe_args) : $dbcalc->execute; |
194 |
} |
228 |
while (my $data = $dbcalc->fetchrow_hashref) { |
195 |
} |
229 |
my $col = $data->{col} || 'NULL'; |
196 |
} |
230 |
$tables{$col} or $tables{$col} = []; |
197 |
else { |
231 |
push @{$tables{$col}}, $data; |
198 |
( scalar @exe_args ) ? $dbcalc->execute(@exe_args) : $dbcalc->execute; |
232 |
} |
199 |
while ( my $data = $dbcalc->fetchrow_hashref ) { |
233 |
} |
200 |
my $col = $data->{col} || 'NULL'; |
234 |
|
201 |
$tables{$col} or $tables{$col} = []; |
235 |
foreach my $tablename (sort keys %tables) { |
202 |
push @{ $tables{$col} }, $data; |
236 |
my (@temptable); |
203 |
} |
237 |
my $i=0; |
204 |
} |
238 |
foreach my $cell (@{$tables{$tablename}}) { |
205 |
|
239 |
if (0 == $i++ and $debug) { |
206 |
foreach my $tablename ( sort keys %tables ) { |
240 |
my $dump = Dumper($cell); |
207 |
my (@temptable); |
241 |
$dump =~ s/\n/ /gs; |
208 |
my $i = 0; |
242 |
$dump =~ s/\s+/ /gs; |
209 |
foreach my $cell ( @{ $tables{$tablename} } ) { |
243 |
print STDERR "first cell for $tablename: $dump"; |
210 |
if ( 0 == $i++ and $debug ) { |
244 |
} |
211 |
my $dump = Dumper($cell); |
245 |
push @temptable, $cell; |
212 |
$dump =~ s/\n/ /gs; |
246 |
} |
213 |
$dump =~ s/\s+/ /gs; |
247 |
my $count = scalar(@temptable); |
214 |
print STDERR "first cell for $tablename: $dump"; |
248 |
my $allitems = $columns{$tablename}; |
215 |
} |
249 |
$globalline{total_looptable_count} += $count; |
216 |
push @temptable, $cell; |
250 |
$globalline{total_coltitle_count} += $allitems; |
217 |
} |
251 |
push @{$globalline{looptables}}, { |
218 |
my $count = scalar(@temptable); |
252 |
looprow => \@temptable, |
219 |
my $allitems = $columns{$tablename}; |
253 |
coltitle => $tablename, |
220 |
$globalline{total_looptable_count} += $count; |
254 |
coltitle_count => $allitems, |
221 |
$globalline{total_coltitle_count} += $allitems; |
255 |
looptable_count => $count, |
222 |
push @{ $globalline{looptables} }, |
256 |
looptable_first => ($count) ? $temptable[ 0]->{itemcallnumber} : '', |
223 |
{ |
257 |
looptable_last => ($count) ? $temptable[-1]->{itemcallnumber} : '', |
224 |
looprow => \@temptable, |
258 |
}; |
225 |
coltitle => $tablename, |
259 |
} |
226 |
coltitle_count => $allitems, |
|
|
227 |
looptable_count => $count, |
228 |
looptable_first => ($count) ? $temptable[0]->{itemcallnumber} : '', |
229 |
looptable_last => ($count) ? $temptable[-1]->{itemcallnumber} : '', |
230 |
}; |
231 |
} |
260 |
|
232 |
|
261 |
# the header of the table |
233 |
# the header of the table |
262 |
$globalline{loopfilter}=\@loopfilter; |
234 |
$globalline{loopfilter} = \@loopfilter; |
263 |
$globalline{limit} = $limit; |
235 |
$globalline{limit} = $limit; |
264 |
$globalline{column} = $column; |
236 |
$globalline{column} = $column; |
265 |
return [(\%globalline)]; # reference to array of reference to hash |
237 |
return [ ( \%globalline ) ]; # reference to array of reference to hash |
266 |
} |
238 |
} |
267 |
|
239 |
|
268 |
1; |
240 |
1; |
269 |
- |
|
|