|
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 |
- |
|
|