Lines 21-27
Link Here
|
21 |
use Modern::Perl; |
21 |
use Modern::Perl; |
22 |
|
22 |
|
23 |
use CGI qw ( -utf8 ); |
23 |
use CGI qw ( -utf8 ); |
24 |
use Date::Calc qw/Today Add_Delta_YM/; |
|
|
25 |
|
24 |
|
26 |
use C4::Context; |
25 |
use C4::Context; |
27 |
use C4::Output; |
26 |
use C4::Output; |
Lines 30-36
use C4::Debug;
Link Here
|
30 |
use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/; |
29 |
use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/; |
31 |
use C4::Acquisition qw/GetOrdersByBiblionumber/; |
30 |
use C4::Acquisition qw/GetOrdersByBiblionumber/; |
32 |
use Koha::DateUtils; |
31 |
use Koha::DateUtils; |
33 |
use Koha::Acquisition::Baskets; |
32 |
use Koha::Util::HoldsRatios; |
34 |
|
33 |
|
35 |
my $input = new CGI; |
34 |
my $input = new CGI; |
36 |
my $startdate = $input->param('from'); |
35 |
my $startdate = $input->param('from'); |
Lines 38-43
my $enddate = $input->param('to');
Link Here
|
38 |
my $ratio = $input->param('ratio'); |
37 |
my $ratio = $input->param('ratio'); |
39 |
my $include_ordered = $input->param('include_ordered'); |
38 |
my $include_ordered = $input->param('include_ordered'); |
40 |
|
39 |
|
|
|
40 |
# If coming from acq we may be getting ratios for titles in a single basket |
41 |
my $booksellerid = $input->param('booksellerid') // ''; |
42 |
my $basketno = $input->param('basketno') // ''; |
43 |
$debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); |
44 |
|
41 |
my ( $template, $loggedinuser, $cookie ) = get_template_and_user( |
45 |
my ( $template, $loggedinuser, $cookie ) = get_template_and_user( |
42 |
{ |
46 |
{ |
43 |
template_name => "circ/reserveratios.tt", |
47 |
template_name => "circ/reserveratios.tt", |
Lines 49-218
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
Link Here
|
49 |
} |
53 |
} |
50 |
); |
54 |
); |
51 |
|
55 |
|
52 |
my $booksellerid = $input->param('booksellerid') // ''; |
56 |
my ($reservedata,$sql) = Koha::Util::HoldsRatios::get_ratios({ |
53 |
my $basketno = $input->param('basketno') // ''; |
57 |
from => $startdate, |
54 |
if ($booksellerid && $basketno) { |
58 |
to => $enddate, |
55 |
$template->param( booksellerid => $booksellerid, basketno => $basketno ); |
59 |
ratio => $ratio, |
56 |
} |
60 |
include_ordered => $include_ordered, |
57 |
|
61 |
basketno => $basketno |
58 |
my $effective_create_items = q{}; |
62 |
}); |
59 |
if ( $basketno ){ |
|
|
60 |
my $basket = Koha::Acquisition::Baskets->find( $basketno ); |
61 |
if ($basket){ |
62 |
$effective_create_items = $basket->effective_create_items; |
63 |
} else { |
64 |
$effective_create_items = C4::Context->preference('AcqCreateItem'); |
65 |
} |
66 |
} |
67 |
|
68 |
$startdate = eval { dt_from_string( $startdate ) } if $startdate; |
69 |
$enddate = eval { dt_from_string( $enddate ) } if $enddate; |
70 |
|
71 |
my $todaysdate = dt_from_string; |
72 |
|
73 |
# A default of the prior years's holds is a reasonable way to pull holds |
74 |
$enddate = $todaysdate unless $enddate; |
75 |
$startdate = $todaysdate->clone->subtract( years => 1 ) unless $startdate; |
76 |
|
77 |
if (!defined($ratio)) { |
78 |
$ratio = 3; |
79 |
} |
80 |
# Force to be a number |
81 |
$ratio += 0; |
82 |
if ($ratio <= 0) { |
83 |
$ratio = 1; # prevent division by zero |
84 |
} |
85 |
|
86 |
my $dbh = C4::Context->dbh; |
87 |
my $sqldatewhere = ""; |
88 |
$debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); |
89 |
my @query_params = (); |
90 |
|
91 |
$sqldatewhere .= " AND reservedate >= ?"; |
92 |
push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ; |
93 |
$sqldatewhere .= " AND reservedate <= ?"; |
94 |
push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' }); |
95 |
|
96 |
my $include_aqorders_qty = |
97 |
$effective_create_items eq 'receiving' |
98 |
? '+ COALESCE(aqorders.quantity, 0) - COALESCE(aqorders.quantityreceived, 0)' |
99 |
: q{}; |
100 |
|
101 |
my $include_aqorders_qty_join = |
102 |
$effective_create_items eq 'receiving' |
103 |
? 'LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber' |
104 |
: q{}; |
105 |
|
106 |
my $nfl_comparison = $include_ordered ? '<=' : '='; |
107 |
my $strsth = |
108 |
"SELECT reservedate, |
109 |
reserves.borrowernumber as borrowernumber, |
110 |
reserves.biblionumber, |
111 |
reserves.branchcode as branch, |
112 |
items.holdingbranch, |
113 |
items.itemcallnumber, |
114 |
items.itemnumber, |
115 |
GROUP_CONCAT(DISTINCT items.itemcallnumber |
116 |
ORDER BY items.itemnumber SEPARATOR '|') as listcall, |
117 |
GROUP_CONCAT(DISTINCT homebranch |
118 |
ORDER BY items.itemnumber SEPARATOR '|') as homebranch_list, |
119 |
GROUP_CONCAT(DISTINCT holdingbranch |
120 |
ORDER BY items.itemnumber SEPARATOR '|') as holdingbranch_list, |
121 |
GROUP_CONCAT(DISTINCT items.location |
122 |
ORDER BY items.itemnumber SEPARATOR '|') as l_location, |
123 |
GROUP_CONCAT(DISTINCT items.itype |
124 |
ORDER BY items.itemnumber SEPARATOR '|') as l_itype, |
125 |
|
126 |
reserves.found, |
127 |
biblio.title, |
128 |
biblio.author, |
129 |
count(DISTINCT reserves.borrowernumber) as reservecount, |
130 |
count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount |
131 |
FROM reserves |
132 |
LEFT JOIN items ON items.biblionumber=reserves.biblionumber |
133 |
LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber |
134 |
$include_aqorders_qty_join |
135 |
WHERE |
136 |
notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0 |
137 |
$sqldatewhere |
138 |
"; |
139 |
|
140 |
if (C4::Context->preference('IndependentBranches')){ |
141 |
$strsth .= " AND items.holdingbranch=? "; |
142 |
push @query_params, C4::Context->userenv->{'branch'}; |
143 |
} |
144 |
|
145 |
$strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC"; |
146 |
|
147 |
$template->param(sql => $strsth); |
148 |
my $sth = $dbh->prepare($strsth); |
149 |
$sth->execute(@query_params); |
150 |
|
151 |
my $ratio_atleast1 = ($ratio >= 1) ? 1 : 0; |
152 |
my @reservedata; |
153 |
while ( my $data = $sth->fetchrow_hashref ) { |
154 |
my $thisratio = $data->{reservecount} / $data->{itemcount}; |
155 |
my $ratiocalc = ($thisratio / $ratio); |
156 |
($thisratio / $ratio) >= 1 or next; # TODO: tighter targeting -- get ratio limit into SQL using HAVING clause |
157 |
my $record = GetMarcBiblio({ biblionumber => $data->{biblionumber} }); |
158 |
$data->{subtitle} = GetRecordValue('subtitle', $record, GetFrameworkCode($data->{biblionumber})); |
159 |
push( |
160 |
@reservedata, |
161 |
{ |
162 |
reservedate => $data->{reservedate}, |
163 |
priority => $data->{priority}, |
164 |
name => $data->{borrower}, |
165 |
title => $data->{title}, |
166 |
subtitle => $data->{subtitle}, |
167 |
author => $data->{author}, |
168 |
itemnum => $data->{itemnumber}, |
169 |
biblionumber => $data->{biblionumber}, |
170 |
holdingbranch => $data->{holdingbranch}, |
171 |
homebranch_list => [split('\|', $data->{homebranch_list})], |
172 |
holdingbranch_list => [split('\|', $data->{holdingbranch_list})], |
173 |
branch => $data->{branch}, |
174 |
itemcallnumber => $data->{itemcallnumber}, |
175 |
location => [split('\|', $data->{l_location})], |
176 |
itype => [split('\|', $data->{l_itype})], |
177 |
reservecount => $data->{reservecount}, |
178 |
itemcount => $data->{itemcount}, |
179 |
ratiocalc => sprintf( "%.0d", $ratio_atleast1 ? ( $thisratio / $ratio ) : $thisratio ), |
180 |
thisratio => sprintf( "%.2f", $thisratio ), |
181 |
thisratio_atleast1 => ( $thisratio >= 1 ) ? 1 : 0, |
182 |
listcall => [split('\|', $data->{listcall})] |
183 |
} |
184 |
); |
185 |
} |
186 |
|
187 |
for my $rd ( @reservedata ) { |
188 |
next unless $rd->{biblionumber}; |
189 |
$rd->{pendingorders} = CountPendingOrdersByBiblionumber( $rd->{biblionumber} ); |
190 |
} |
191 |
|
63 |
|
192 |
$template->param( |
64 |
$template->param( |
193 |
ratio_atleast1 => $ratio_atleast1, |
65 |
todaysdate => dt_from_string, |
194 |
todaysdate => $todaysdate, |
|
|
195 |
from => $startdate, |
66 |
from => $startdate, |
196 |
to => $enddate, |
67 |
to => $enddate, |
197 |
ratio => $ratio, |
68 |
ratio => $ratio, |
198 |
include_ordered => $include_ordered, |
69 |
include_ordered => $include_ordered, |
199 |
reserveloop => \@reservedata, |
70 |
reserveloop => $reservedata, |
200 |
); |
71 |
); |
|
|
72 |
if ($booksellerid && $basketno) { |
73 |
$template->param( |
74 |
booksellerid => $booksellerid, |
75 |
basketno => $basketno |
76 |
); |
77 |
} |
201 |
|
78 |
|
202 |
output_html_with_http_headers $input, $cookie, $template->output; |
79 |
output_html_with_http_headers $input, $cookie, $template->output; |
203 |
|
80 |
|
204 |
sub CountPendingOrdersByBiblionumber { |
|
|
205 |
my $biblionumber = shift; |
206 |
my @orders = GetOrdersByBiblionumber( $biblionumber ); |
207 |
my $cnt = 0; |
208 |
if (scalar(@orders)) { |
209 |
for my $order ( @orders ) { |
210 |
next if $order->{datecancellationprinted}; |
211 |
my $onum = $order->{quantity} // 0; |
212 |
my $rnum = $order->{quantityreceived} // 0; |
213 |
next if $rnum >= $onum; |
214 |
$cnt += ($onum - $rnum); |
215 |
} |
216 |
} |
217 |
return $cnt; |
218 |
} |
219 |
- |