From 3c741d2376e939dea0554a05eddd4f9e9b2d9449 Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Mon, 24 Aug 2009 22:10:21 +0200 Subject: [PATCH] Smart Rules enhancements Adding finedays and reservesallowed and renewalsallowed management in smart rules Adding Clone Rules Adding CanBookBeReserved and CanItemBeReserved in C4::Reserves Manage Reservesallowed in opac and staff interface Manage renewalsallowed in C4/Circulation.pm Update Database follow Signed-off-by: Galen Charlton --- C4/Circulation.pm | 70 ++++--- C4/Reserves.pm | 173 ++++++++++++++++++ admin/clone-rules.pl | 107 +++++++++++ admin/smart-rules.pl | 14 +- .../prog/en/modules/admin/clone-rules.tmpl | 72 ++++++++ .../prog/en/modules/admin/smart-rules.tmpl | 77 ++++---- opac/opac-reserve.pl | 11 +- reserve/request.pl | 11 +- 8 files changed, 463 insertions(+), 72 deletions(-) create mode 100755 admin/clone-rules.pl create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/clone-rules.tmpl diff --git a/C4/Circulation.pm b/C4/Circulation.pm index 1778a1c912..7f2bfeb18d 100644 --- a/C4/Circulation.pm +++ b/C4/Circulation.pm @@ -2034,39 +2034,52 @@ sub CanBookBeRenewed { # Look in the issues table for this item, lent to this borrower, # and not yet returned. - # FIXME - I think this function could be redone to use only one SQL call. - my $sth1 = $dbh->prepare( - "SELECT * FROM issues - WHERE borrowernumber = ? - AND itemnumber = ?" - ); - $sth1->execute( $borrowernumber, $itemnumber ); - if ( my $data1 = $sth1->fetchrow_hashref ) { - - # Found a matching item - - # See if this item may be renewed. This query is convoluted - # because it's a bit messy: given the item number, we need to find - # the biblioitem, which gives us the itemtype, which tells us - # whether it may be renewed. - my $query = "SELECT renewalsallowed FROM items "; - $query .= (C4::Context->preference('item-level_itypes')) - ? "LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype " - : "LEFT JOIN biblioitems on items.biblioitemnumber = biblioitems.biblioitemnumber - LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype "; - $query .= "WHERE items.itemnumber = ?"; - my $sth2 = $dbh->prepare($query); - $sth2->execute($itemnumber); - if ( my $data2 = $sth2->fetchrow_hashref ) { - $renews = $data2->{'renewalsallowed'}; - } - if ( ( $renews && $renews > $data1->{'renewals'} ) || $override_limit ) { + # Look in the issues table for this item, lent to this borrower, + # and not yet returned. + my %branch = ( + 'ItemHomeLibrary' => 'items.homebranch', + 'PickupLibrary' => 'items.holdingbranch', + 'PatronLibrary' => 'borrowers.branchcode' + ); + my $controlbranch = $branch{C4::Context->preference('CircControl')}; + my $itype = C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype'; + + my $sthcount = $dbh->prepare(" + SELECT + borrowers.categorycode, biblioitems.itemtype, issues.renewals, renewalsallowed, $controlbranch + FROM issuingrules, + issues + LEFT JOIN items USING (itemnumber) + LEFT JOIN borrowers USING (borrowernumber) + LEFT JOIN biblioitems USING (biblioitemnumber) + + WHERE + issuingrules.categorycode = borrowers.categorycode + AND + issuingrules.itemtype = $itype + AND + (issuingrules.branchcode = $controlbranch OR issuingrules.branchcode = '*') + AND + borrowernumber = ? + AND + itemnumber = ? + ORDER BY + issuingrules.categorycode desc, + issuingrules.itemtype desc, + issuingrules.branchcode desc + LIMIT 1; + "); + + $sthcount->execute( $borrowernumber, $itemnumber ); + if ( my $data1 = $sthcount->fetchrow_hashref ) { + + if ( ( $data1->{renewalsallowed} && $data1->{renewalsallowed} > $data1->{renewals} ) || $override_limit ) { $renewokay = 1; } else { $error="too_many"; } - $sth2->finish; + my ( $resfound, $resrec ) = C4::Reserves::CheckReserves($itemnumber); if ($resfound) { $renewokay = 0; @@ -2074,7 +2087,6 @@ sub CanBookBeRenewed { } } - $sth1->finish; return ($renewokay,$error); } diff --git a/C4/Reserves.pm b/C4/Reserves.pm index 861003633b..2348c904df 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -329,7 +329,180 @@ sub GetReservesFromBorrowernumber { return @$data; } #------------------------------------------------------------------------------------- +=item CanBookBeReserved +$error = &CanBookBeReserved($borrowernumber, $biblionumber) + +=cut + +sub CanBookBeReserved{ + my ($borrowernumber, $biblionumber) = @_; + + my $dbh = C4::Context->dbh; + my $biblio = GetBiblioData($biblionumber); + my $borrower = C4::Members::GetMember($borrowernumber); + my $controlbranch = C4::Context->preference('ReservesControlBranch'); + my $itype = C4::Context->preference('item-level_itypes'); + my $reservesrights= 0; + my $reservescount = 0; + + # we retrieve the user rights + my @args; + my $rightsquery = "SELECT categorycode, itemtype, branchcode, reservesallowed + FROM issuingrules + WHERE categorycode = ?"; + push @args,$borrower->{categorycode}; + + if($controlbranch eq "ItemHomeLibrary"){ + $rightsquery .= " AND branchcode = '*'"; + }elsif($controlbranch eq "PatronLibrary"){ + $rightsquery .= " AND branchcode IN (?,'*')"; + push @args, $borrower->{branchcode}; + } + + if(not $itype){ + $rightsquery .= " AND itemtype IN (?,'*')"; + push @args, $biblio->{itemtype}; + }else{ + $rightsquery .= " AND itemtype = '*'"; + } + + $rightsquery .= " ORDER BY categorycode DESC, itemtype DESC, branchcode DESC"; + + my $sthrights = $dbh->prepare($rightsquery); + $sthrights->execute(@args); + + if(my $row = $sthrights->fetchrow_hashref()){ + $reservesrights = $row->{reservesallowed}; + } + + @args = (); + # we count how many reserves the borrower have + my $countquery = "SELECT count(*) as count + FROM reserves + LEFT JOIN items USING (itemnumber) + LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber) + LEFT JOIN borrowers USING (borrowernumber) + WHERE borrowernumber = ? + "; + push @args, $borrowernumber; + + if(not $itype){ + $countquery .= "AND itemtype = ?"; + push @args, $biblio->{itemtype}; + } + + if($controlbranch eq "PatronLibrary"){ + $countquery .= " AND borrowers.branchcode = ? "; + push @args, $borrower->{branchcode}; + } + + my $sthcount = $dbh->prepare($countquery); + $sthcount->execute(@args); + + if(my $row = $sthcount->fetchrow_hashref()){ + $reservescount = $row->{count}; + } + + if($reservescount < $reservesrights){ + return 1; + }else{ + return 0; + } + +} + +=item CanItemBeReserved + +$error = &CanItemBeReserved($borrowernumber, $itemnumber) + +this function return 1 if an item can be issued by this borrower. + +=cut + +sub CanItemBeReserved{ + my ($borrowernumber, $itemnumber) = @_; + + my $dbh = C4::Context->dbh; + my $allowedreserves = 0; + + my $controlbranch = C4::Context->preference('ReservesControlBranch'); + my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype"; + + # we retrieve borrowers and items informations # + my $item = GetItem($itemnumber); + my $borrower = C4::Members::GetMember($borrowernumber); + + # we retrieve user rights on this itemtype and branchcode + my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed + FROM issuingrules + WHERE (categorycode in (?,'*') ) + AND (itemtype IN (?,'*')) + AND (branchcode IN (?,'*')) + ORDER BY + categorycode DESC, + itemtype DESC, + branchcode DESC;" + ); + + my $querycount ="SELECT + count(*) as count + FROM reserves + LEFT JOIN items USING (itemnumber) + LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber) + LEFT JOIN borrowers USING (borrowernumber) + WHERE borrowernumber = ? + "; + + + my $itemtype = $item->{$itype}; + my $categorycode = $borrower->{categorycode}; + my $branchcode = ""; + my $branchfield = "reserves.branchcode"; + + if( $controlbranch eq "ItemHomeLibrary" ){ + $branchfield = "items.homebranch"; + $branchcode = $item->{homebranch}; + }elsif( $controlbranch eq "PatronLibrary" ){ + $branchfield = "borrowers.branchcode"; + $branchcode = $borrower->{branchcode}; + } + + # we retrieve rights + $sth->execute($categorycode, $itemtype, $branchcode); + if(my $rights = $sth->fetchrow_hashref()){ + $itemtype = $rights->{itemtype}; + $allowedreserves = $rights->{reservesallowed}; + }else{ + $itemtype = '*'; + } + + # we retrieve count + + $querycount .= "AND $branchfield = ?"; + + $querycount .= " AND $itype = ?" if ($itemtype ne "*"); + my $sthcount = $dbh->prepare($querycount); + + if($itemtype eq "*"){ + $sthcount->execute($borrowernumber, $branchcode); + }else{ + $sthcount->execute($borrowernumber, $branchcode, $itemtype); + } + + my $reservecount = "0"; + if(my $rowcount = $sthcount->fetchrow_hashref()){ + $reservecount = $rowcount->{count}; + } + + # we check if it's ok or not + if( $reservecount < $allowedreserves ){ + return 1; + }else{ + return 0; + } +} +#-------------------------------------------------------------------------------- =item GetReserveCount $number = &GetReserveCount($borrowernumber); diff --git a/admin/clone-rules.pl b/admin/clone-rules.pl new file mode 100755 index 0000000000..7daa369056 --- /dev/null +++ b/admin/clone-rules.pl @@ -0,0 +1,107 @@ +#!/usr/bin/perl +# vim: et ts=4 sw=4 +# Copyright BibLibre +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along with +# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA + + +# This script clones issuing rules from a library to another +# parameters : +# - frombranch : the branch we want to clone issuing rules from +# - tobranch : the branch we want to clone issuing rules to +# +# The script can be called with one of the parameters, both or none + +use strict; +use CGI; +use C4::Context; +use C4::Output; +use C4::Auth; +use C4::Koha; +use C4::Debug; +use C4::Branch; # GetBranches + +my $input = new CGI; +my $dbh = C4::Context->dbh; + +my ($template, $loggedinuser, $cookie) + = get_template_and_user({template_name => "admin/clone-rules.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {parameters => 1}, + debug => 1, + }); + +my $frombranch = $input->param("frombranch"); +my $tobranch = $input->param("tobranch"); +my $branchloop = GetBranchesLoop; + +$template->param(frombranch => $frombranch) if ($frombranch); +$template->param(frombranchname => GetBranchName($frombranch)) if ($frombranch); +$template->param(tobranch => $tobranch) if ($tobranch); +$template->param(tobranchname => GetBranchName($tobranch)) if ($tobranch); + +$template->param(branchloop => $branchloop); + +if ($frombranch && $tobranch) { + + my $error; + + # First, we create a temporary table with the rules we want to clone + my $query = "CREATE TEMPORARY TABLE tmpissuingrules ENGINE=memory SELECT * FROM issuingrules WHERE branchcode=?"; + my $sth = $dbh->prepare($query); + my $res = $sth->execute($frombranch); + $error = 1 unless ($res); + + if (!$error) { + # We modify these rules according to the new branchcode + $query = "UPDATE tmpissuingrules SET branchcode=? WHERE branchcode=?"; + $sth = $dbh->prepare($query); + $res = $sth->execute($tobranch, $frombranch); + $error = 1 unless ($res); + } + + if (!$error) { + # We delete the rules for the existing branchode + $query = "DELETE FROM issuingrules WHERE branchcode=?"; + $sth = $dbh->prepare($query); + $res = $sth->execute($tobranch); + $error = 1 unless ($res); + } + + + if (!$error) { + # We insert the new rules from our temporary table + $query = "INSERT INTO issuingrules SELECT * FROM tmpissuingrules WHERE branchcode=?"; + $sth = $dbh->prepare($query); + $res = $sth->execute($tobranch); + $error = 1 unless ($res); + } + + # Finally, we delete our temporary table + $query = "DROP TABLE tmpissuingrules"; + $sth = $dbh->prepare($query); + $res = $sth->execute(); + + $template->param(result => "1"); + $template->param(error => $error); +} + + + +output_html_with_http_headers $input, $cookie, $template->output; + diff --git a/admin/smart-rules.pl b/admin/smart-rules.pl index 6086636859..c3300f369b 100755 --- a/admin/smart-rules.pl +++ b/admin/smart-rules.pl @@ -99,16 +99,19 @@ elsif ($op eq 'delete-branch-item') { # save the values entered elsif ($op eq 'add') { my $sth_search = $dbh->prepare("SELECT COUNT(*) AS total FROM issuingrules WHERE branchcode=? AND categorycode=? AND itemtype=?"); - my $sth_insert = $dbh->prepare("INSERT INTO issuingrules (branchcode, categorycode, itemtype, maxissueqty, issuelength, fine, firstremind, chargeperiod) VALUES(?,?,?,?,?,?,?,?)"); - my $sth_update=$dbh->prepare("UPDATE issuingrules SET fine=?, firstremind=?, chargeperiod=?, maxissueqty=?, issuelength=? WHERE branchcode=? AND categorycode=? AND itemtype=?"); + my $sth_insert = $dbh->prepare("INSERT INTO issuingrules (branchcode, categorycode, itemtype, maxissueqty, renewalsallowed, reservesallowed, issuelength, fine, finedays, firstremind, chargeperiod) VALUES(?,?,?,?,?,?,?,?,?,?,?)"); + my $sth_update=$dbh->prepare("UPDATE issuingrules SET fine=?, finedays=?, firstremind=?, chargeperiod=?, maxissueqty=?, renewalsallowed=?, reservesallowed=?, issuelength=? WHERE branchcode=? AND categorycode=? AND itemtype=?"); my $br = $branch; # branch my $bor = $input->param('categorycode'); # borrower category my $cat = $input->param('itemtype'); # item type my $fine = $input->param('fine'); + my $finedays = $input->param('finedays'); my $firstremind = $input->param('firstremind'); my $chargeperiod = $input->param('chargeperiod'); my $maxissueqty = $input->param('maxissueqty'); + my $renewalsallowed = $input->param('renewalsallowed'); + my $reservesallowed = $input->param('reservesallowed'); $maxissueqty =~ s/\s//g; $maxissueqty = undef if $maxissueqty !~ /^\d+/; my $issuelength = $input->param('issuelength'); @@ -117,9 +120,9 @@ elsif ($op eq 'add') { $sth_search->execute($br,$bor,$cat); my $res = $sth_search->fetchrow_hashref(); if ($res->{total}) { - $sth_update->execute($fine, $firstremind, $chargeperiod, $maxissueqty,$issuelength,$br,$bor,$cat); + $sth_update->execute($fine, $finedays,$firstremind, $chargeperiod, $maxissueqty, $renewalsallowed,$reservesallowed, $issuelength,$br,$bor,$cat); } else { - $sth_insert->execute($br,$bor,$cat,$maxissueqty,$issuelength,$fine,$firstremind,$chargeperiod); + $sth_insert->execute($br,$bor,$cat,$maxissueqty,$renewalsallowed,$reservesallowed,$issuelength,$fine,$finedays,$firstremind,$chargeperiod); } } elsif ($op eq "set-branch-defaults") { @@ -476,7 +479,8 @@ $template->param(categoryloop => \@category_loop, rules => \@sorted_row_loop, branchloop => \@branchloop, humanbranch => ($branch ne '*' ? $branches->{$branch}->{branchname} : ''), - branch => $branch + branch => $branch, + definedbranch => $branch eq '*' ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/clone-rules.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/clone-rules.tmpl new file mode 100644 index 0000000000..4945f2e104 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/clone-rules.tmpl @@ -0,0 +1,72 @@ + +Koha › Administration › Clone Issuing Rules + + + + + + + + + +
+ +
+
+
+

Cloning issuing rules + + from "" + to "" + +

+ + + +

Issuing rules cloning failed!

+ +

The rules have been cloned.

+ + + +

Use carefully ! If the destination library already has issuing rules, they will be deleted without warning !

+
+ +
+ Please choose a library to clone rules from: + + + " /> +
+ + + +
+ Please choose the library to clone the rules to: + + + " /> +
+ + +
+ + +
+ +
+
+ +
+
+ diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/smart-rules.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/smart-rules.tmpl index 96481aab89..27dfcb61ee 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/smart-rules.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/smart-rules.tmpl @@ -56,6 +56,7 @@ $(document).ready(function() { +
" />


@@ -65,44 +66,54 @@ $(document).ready(function() { Patron Category Item Type Fine Amount + Fine Days Fine Grace Period Fine Charging Interval Current Checkouts Allowed + Renewals Allowed + Reserves Allowed Loan Period  - - - - - - - - Default - - - - - - Default - - - - - - day(s) - day(s) - - Unlimited - - - - - day(s) - - &categorycode=&branch=">Delete - - - + + + + + + + + Default + + + + + + Default + + + + + + + + day(s) + + + day(s) + day(s) + + Unlimited + + + + + time(s) + time(s) + day(s) + + &categorycode=&branch=">Delete + + +