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