4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it
9 # under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # Koha is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
25 use Date::Calc qw/Today Add_Delta_YM/;
31 use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/;
32 use C4::Acquisition qw/GetOrdersByBiblionumber/;
36 my $startdate = $input->param('from');
37 my $enddate = $input->param('to');
38 my $ratio = $input->param('ratio');
39 my $include_ordered = $input->param('include_ordered');
41 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
43 template_name => "circ/reserveratios.tt",
47 flagsrequired => { circulate => "circulate_remaining_permissions" },
52 my $booksellerid = $input->param('booksellerid') // '';
53 my $basketno = $input->param('basketno') // '';
54 if ($booksellerid && $basketno) {
55 $template->param( booksellerid => $booksellerid, basketno => $basketno );
58 $startdate = eval { output_pref( { dt => dt_from_string( $startdate ), dateonly => 1, dateformat => 'iso' } ); };
59 $enddate = eval { output_pref( { dt => dt_from_string( $enddate ), dateonly => 1, dateformat => 'iso' } ); };
61 my $todaysdate_dt = dt_from_string;
62 my $todaysdate = output_pref( { dt => $todaysdate_dt, dateonly => 1, dateformat => 'iso' } );
64 # A default of the prior years's holds is a reasonable way to pull holds
65 my $datelastyear_dt = $todaysdate_dt->subtract( days => 1);
66 my $datelastyear = output_pref( { dt => $datelastyear_dt, dateonly => 1, dateformat => 'iso' } );
68 $startdate = $datelastyear unless $startdate;
69 $enddate = $todaysdate unless $enddate;
71 if (!defined($ratio)) {
74 # Force to be a number
77 $ratio = 1; # prevent division by zero
80 my $dbh = C4::Context->dbh;
81 my $sqldatewhere = "";
82 $debug and warn output_pref({ dt => dt_from_string( $startdate ), dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => dt_from_string( $enddate ), dateformat => 'iso', dateonly => 1 });
83 my @query_params = ();
85 $sqldatewhere .= " AND reservedate >= ?";
86 push @query_params, $startdate ;
89 $sqldatewhere .= " AND reservedate <= ?";
90 push @query_params, $enddate;
93 my $nfl_comparison = $include_ordered ? '<=' : '=';
96 reserves.borrowernumber as borrowernumber,
97 reserves.biblionumber,
98 reserves.branchcode as branch,
100 items.itemcallnumber,
102 GROUP_CONCAT(DISTINCT items.itemcallnumber
103 ORDER BY items.itemnumber SEPARATOR '<br/>') as listcall,
104 GROUP_CONCAT(DISTINCT homebranch
105 ORDER BY items.itemnumber SEPARATOR '<br/>') as homebranch_list,
106 GROUP_CONCAT(DISTINCT holdingbranch
107 ORDER BY items.itemnumber SEPARATOR '<br/>') as holdingbranch_list,
108 GROUP_CONCAT(DISTINCT items.location
109 ORDER BY items.itemnumber SEPARATOR '<br/>') as l_location,
110 GROUP_CONCAT(DISTINCT items.itype
111 ORDER BY items.itemnumber SEPARATOR '<br/>') as l_itype,
116 count(DISTINCT reserves.borrowernumber) as reservecount,
117 count(DISTINCT items.itemnumber) as itemcount
119 LEFT JOIN items ON items.biblionumber=reserves.biblionumber
120 LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
122 notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0
126 if (C4::Context->preference('IndependentBranches')){
127 $strsth .= " AND items.holdingbranch=? ";
128 push @query_params, C4::Context->userenv->{'branch'};
131 $strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC";
133 $template->param(sql => $strsth);
134 my $sth = $dbh->prepare($strsth);
135 $sth->execute(@query_params);
137 my $ratio_atleast1 = ($ratio >= 1) ? 1 : 0;
139 while ( my $data = $sth->fetchrow_hashref ) {
140 my $thisratio = $data->{reservecount} / $data->{itemcount};
141 my $ratiocalc = ($thisratio / $ratio);
142 ($thisratio / $ratio) >= 1 or next; # TODO: tighter targeting -- get ratio limit into SQL using HAVING clause
143 my $record = GetMarcBiblio($data->{biblionumber});
144 $data->{subtitle} = GetRecordValue('subtitle', $record, GetFrameworkCode($data->{biblionumber}));
148 reservedate => $data->{reservedate},
149 priority => $data->{priority},
150 name => $data->{borrower},
151 title => $data->{title},
152 subtitle => $data->{subtitle},
153 author => $data->{author},
154 itemnum => $data->{itemnumber},
155 biblionumber => $data->{biblionumber},
156 holdingbranch => $data->{holdingbranch},
157 homebranch_list => $data->{homebranch_list},
158 holdingbranch_list => $data->{holdingbranch_list},
159 branch => $data->{branch},
160 itemcallnumber => $data->{itemcallnumber},
161 location => $data->{l_location},
162 itype => $data->{l_itype},
163 reservecount => $data->{reservecount},
164 itemcount => $data->{itemcount},
165 ratiocalc => sprintf( "%.0d", $ratio_atleast1 ? ( $thisratio / $ratio ) : $thisratio ),
166 thisratio => sprintf( "%.2f", $thisratio ),
167 thisratio_atleast1 => ( $thisratio >= 1 ) ? 1 : 0,
168 listcall => $data->{listcall}
173 for my $rd ( @reservedata ) {
174 next unless $rd->{biblionumber};
175 $rd->{pendingorders} = CountPendingOrdersByBiblionumber( $rd->{biblionumber} );
179 ratio_atleast1 => $ratio_atleast1,
180 todaysdate => $todaysdate,
184 include_ordered => $include_ordered,
185 reserveloop => \@reservedata,
188 output_html_with_http_headers $input, $cookie, $template->output;
190 sub CountPendingOrdersByBiblionumber {
191 my $biblionumber = shift;
192 my @orders = GetOrdersByBiblionumber( $biblionumber );
194 if (scalar(@orders)) {
195 for my $order ( @orders ) {
196 next if $order->{datecancellationprinted};
197 my $onum = $order->{quantity} // 0;
198 my $rnum = $order->{quantityreceived} // 0;
199 next if $rnum >= $onum;
200 $cnt += ($onum - $rnum);