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