1 package C4::Reserves2; #asummes C4/Reserves2
3 #requires DBI.pm to be installed
12 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
14 # set the version for version checking
18 @EXPORT = qw(&FindReserves &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
19 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
21 # your exported package globals go here,
22 # as well as any optionally exported functions
24 @EXPORT_OK = qw($Var1 %Hashit);
27 # non-exported package globals go here
28 use vars qw(@more $stuff);
30 # initalize package globals, first exported ones
35 # then the others (which are still accessible as $Some::Module::stuff)
39 # all file-scoped lexicals must be created before
40 # the functions below that use them.
42 # file-private lexicals go here
46 # here's a file-private function as a closure,
47 # callable as &$priv_func; it cannot be prototyped.
52 # make all your functions, whether exported or not;
57 my $query="Select *,reserves.branchcode,biblio.title as btitle
58 from reserves,borrowers,biblio ";
61 $query=$query." where reserves.biblionumber=$bib and
62 reserves.borrowernumber=borrowers.borrowernumber and
63 biblio.biblionumber=$bib and cancellationdate is NULL and
64 (found <> 'F' or found is NULL)";
66 $query=$query." where reserves.borrowernumber=borrowers.borrowernumber
67 and biblio.biblionumber=$bib and reserves.biblionumber=$bib
68 and cancellationdate is NULL and
69 (found <> 'F' or found is NULL)";
72 $query=$query." where borrowers.borrowernumber=$bor and
73 reserves.borrowernumber=borrowers.borrowernumber and reserves.biblionumber
74 =biblio.biblionumber and cancellationdate is NULL and
75 (found <> 'F' or found is NULL)";
77 $query.=" order by priority";
78 my $sth=$dbh->prepare($query);
82 while (my $data=$sth->fetchrow_hashref){
92 sub Findgroupreserve {
93 my ($bibitem,$biblio)=@_;
95 $bibitem=$dbh->quote($bibitem);
96 my $query="Select * from reserves
97 left join reserveconstraints on
98 reserves.biblionumber=reserveconstraints.biblionumber
100 reserves.biblionumber=$biblio and
101 ((reserveconstraints.biblioitemnumber=$bibitem
102 and reserves.borrowernumber=reserveconstraints.borrowernumber
103 and reserves.reservedate=reserveconstraints.reservedate)
104 or reserves.constrainttype='a')
105 and reserves.cancellationdate is NULL
106 and (reserves.found <> 'F' or reserves.found is NULL)";
108 my $sth=$dbh->prepare($query);
112 while (my $data=$sth->fetchrow_hashref){
123 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
124 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
125 my $dbh = &C4Connect;
126 my $const = lc substr($constraint,0,1);
127 my @datearr = localtime(time);
128 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
130 # updates take place here
133 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
134 my $updquery = "insert into accountlines
135 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
137 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
138 my $usth = $dbh->prepare($updquery);
143 my $query="insert into reserves
144 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
146 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
147 my $sth = $dbh->prepare($query);
151 if (($const eq "o") || ($const eq "e")) {
152 my $numitems = @$bibitems;
154 while ($i < $numitems) {
155 my $biblioitem = @$bibitems[$i];
156 my $query = "insert into
158 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
160 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
161 my $sth = $dbh->prepare($query);
173 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
175 my $dbh = &C4Connect;
176 my $const = lc substr($constraint,0,1);
177 my $query = "select * from borrowers,categories
178 where (borrowernumber = '$borrnum')
179 and (borrowers.categorycode = categories.categorycode)";
180 my $sth = $dbh->prepare($query);
182 my $data = $sth->fetchrow_hashref;
184 my $fee = $data->{'reservefee'};
185 my $cntitems = @->$bibitems;
187 # check for items on issue
188 # first find biblioitem records
190 my $query1 = "select * from biblio,biblioitems
191 where (biblio.biblionumber = '$biblionumber')
192 and (biblio.biblionumber = biblioitems.biblionumber)";
193 my $sth1 = $dbh->prepare($query1);
195 while (my $data1=$sth1->fetchrow_hashref) {
197 push @biblioitems,$data1;
201 while ($x < $cntitems) {
202 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
209 push @biblioitems,$data1;
213 push @biblioitems,$data1;
219 my $cntitemsfound = @biblioitems;
223 while ($x < $cntitemsfound) {
224 my $bitdata = @biblioitems[$x];
225 my $query2 = "select * from items
226 where biblioitemnumber = '$bitdata->{'biblioitemnumber'}'";
227 my $sth2 = $dbh->prepare($query2);
229 while (my $itdata=$sth2->fetchrow_hashref) {
230 my $query3 = "select * from issues
231 where itemnumber = '$itdata->{'itemnumber'}' and
234 my $sth3 = $dbh->prepare($query3);
236 if (my $isdata=$sth3->fetchrow_hashref) {
243 if ($allissued == 0) {
244 my $rquery = "select * from reserves
245 where biblionumber = '$biblionumber'";
246 my $rsth = $dbh->prepare($rquery);
248 if (my $rdata = $rsth->fetchrow_hashref) {
260 my ($env,$bornumber,$dbh)=@_;
262 my $query = "select * from accountlines
263 where (borrowernumber = '$bornumber')
264 order by accountno desc";
265 my $sth = $dbh->prepare($query);
267 if (my $accdata=$sth->fetchrow_hashref){
268 $nextaccntno = $accdata->{'accountno'} + 1;
271 return($nextaccntno);
275 #subroutine to update a reserve
276 my ($rank,$biblio,$borrower,$del,$branch)=@_;
278 my $query="Update reserves ";
280 $query.="set priority='$rank',branchcode='$branch' where
281 biblionumber=$biblio and borrowernumber=$borrower";
283 $query="Select * from reserves where biblionumber=$biblio and
284 borrowernumber=$borrower";
285 my $sth=$dbh->prepare($query);
287 my $data=$sth->fetchrow_hashref;
289 $query="Select * from reserves where biblionumber=$biblio and
290 priority > '$data->{'priority'}' and cancellationdate is NULL
292 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
293 $sth2->execute || die $sth2->errstr;
294 while (my $data=$sth2->fetchrow_hashref){
295 $data->{'priority'}--;
296 $query="Update reserves set priority=$data->{'priority'} where
297 biblionumber=$data->{'biblionumber'} and
298 borrowernumber=$data->{'borrowernumber'}";
299 my $sth3=$dbh->prepare($query);
300 $sth3->execute || die $sth3->errstr;
304 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
305 and borrowernumber=$borrower";
307 my $sth=$dbh->prepare($query);
313 sub getreservetitle {
314 my ($biblio,$bor,$date,$timestamp)=@_;
316 my $query="Select * from reserveconstraints,biblioitems where
317 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
318 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
319 = $bor and reserveconstraints.reservedate='$date' and
320 reserveconstraints.timestamp=$timestamp";
321 my $sth=$dbh->prepare($query);
323 my $data=$sth->fetchrow_hashref;
335 END { } # module clean-up code here (global destructor)