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/;
30 use C4
::Dates qw
/format_date format_date_in_iso/;
32 use C4
::Biblio qw
/GetMarcBiblio GetRecordValue GetFrameworkCode/;
33 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 my ( $year, $month, $day ) = Today
();
59 my $todaysdate = sprintf("%-04.4d-%-02.2d-%02.2d", $year, $month, $day);
60 # Find yesterday for the default shelf pull start and end dates
61 # A default of the prior years's holds is a reasonable way to pull holds
62 my $datelastyear = sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM
($year, $month, $day, -1, 0));
64 # Predefine the start and end dates if they are not already defined
65 # Check if null, should string match, if so set start and end date to yesterday
66 if (!defined($startdate) or $startdate !~ s/^\s*(\S+)\s*$/$1/) { # strip spaces, remove Taint
67 $startdate = format_date
($datelastyear);
69 if (!defined($enddate) or $enddate !~ s/^\s*(\S+)\s*$/$1/) { # strip spaces, remove Taint
70 $enddate = format_date
($todaysdate);
72 if (!defined($ratio)) {
75 # Force to be a number
78 $ratio = 1; # prevent division by zero
81 my $dbh = C4
::Context
->dbh;
82 my $sqldatewhere = "";
83 $debug and warn format_date_in_iso
($startdate) . "\n" . format_date_in_iso
($enddate);
84 my @query_params = ();
86 $sqldatewhere .= " AND reservedate >= ?";
87 push @query_params, format_date_in_iso
($startdate);
90 $sqldatewhere .= " AND reservedate <= ?";
91 push @query_params, format_date_in_iso
($enddate);
94 my $nfl_comparison = $include_ordered ?
'<=' : '=';
97 reserves.borrowernumber as borrowernumber,
98 reserves.biblionumber,
99 reserves.branchcode as branch,
101 items.itemcallnumber,
103 GROUP_CONCAT(DISTINCT items.itemcallnumber
104 ORDER BY items.itemnumber SEPARATOR '<br/>') as listcall,
105 GROUP_CONCAT(DISTINCT holdingbranch
106 ORDER BY items.itemnumber SEPARATOR '<br/>') as listbranch,
107 GROUP_CONCAT(DISTINCT items.location
108 ORDER BY items.itemnumber SEPARATOR '<br/>') as l_location,
109 GROUP_CONCAT(DISTINCT items.itype
110 ORDER BY items.itemnumber SEPARATOR '<br/>') as l_itype,
115 count(DISTINCT reserves.borrowernumber) as reservecount,
116 count(DISTINCT items.itemnumber) as itemcount
118 LEFT JOIN items ON items.biblionumber=reserves.biblionumber
119 LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
121 notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0
125 if (C4
::Context
->preference('IndependentBranches')){
126 $strsth .= " AND items.holdingbranch=? ";
127 push @query_params, C4
::Context
->userenv->{'branch'};
130 $strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC";
132 $template->param(sql
=> $strsth);
133 my $sth = $dbh->prepare($strsth);
134 $sth->execute(@query_params);
136 my $ratio_atleast1 = ($ratio >= 1) ?
1 : 0;
138 while ( my $data = $sth->fetchrow_hashref ) {
139 my $thisratio = $data->{reservecount
} / $data->{itemcount
};
140 my $ratiocalc = ($thisratio / $ratio);
141 ($thisratio / $ratio) >= 1 or next; # TODO: tighter targeting -- get ratio limit into SQL using HAVING clause
142 my $record = GetMarcBiblio
($data->{biblionumber
});
143 $data->{subtitle
} = GetRecordValue
('subtitle', $record, GetFrameworkCode
($data->{biblionumber
}));
147 reservedate
=> format_date
( $data->{reservedate
} ),
148 priority
=> $data->{priority
},
149 name
=> $data->{borrower
},
150 title
=> $data->{title
},
151 subtitle
=> $data->{subtitle
},
152 author
=> $data->{author
},
153 itemnum
=> $data->{itemnumber
},
154 biblionumber
=> $data->{biblionumber
},
155 holdingbranch
=> $data->{holdingbranch
},
156 listbranch
=> $data->{listbranch
},
157 branch
=> $data->{branch
},
158 itemcallnumber
=> $data->{itemcallnumber
},
159 location
=> $data->{l_location
},
160 itype
=> $data->{l_itype
},
161 reservecount
=> $data->{reservecount
},
162 itemcount
=> $data->{itemcount
},
163 ratiocalc
=> sprintf("%.0d", $ratio_atleast1 ?
($thisratio / $ratio) : $thisratio),
164 thisratio
=> sprintf("%.2f", $thisratio),
165 thisratio_atleast1
=> ($thisratio >= 1) ?
1 : 0,
166 listcall
=> $data->{listcall
}
171 for my $rd ( @reservedata ) {
172 next unless $rd->{biblionumber
};
173 $rd->{pendingorders
} = CountPendingOrdersByBiblionumber
( $rd->{biblionumber
} );
177 ratio_atleast1
=> $ratio_atleast1,
178 todaysdate
=> format_date
($todaysdate),
182 include_ordered
=> $include_ordered,
183 reserveloop
=> \
@reservedata,
186 output_html_with_http_headers
$input, $cookie, $template->output;
188 sub CountPendingOrdersByBiblionumber
{
189 my $biblionumber = shift;
190 my @orders = GetOrdersByBiblionumber
( $biblionumber );
192 if (scalar(@orders)) {
193 for my $order ( @orders ) {
194 next if $order->{datecancellationprinted
};
195 my $onum = $order->{quantity
} // 0;
196 my $rnum = $order->{quantityreceived
} // 0;
197 next if $rnum >= $onum;
198 $cnt += ($onum - $rnum);