[Koha-patches] [PATCH] UpdateDataBase for smart-rules modification

[Henri-Damien LAURENT]

Members.pm :
Adding IsMemberBlocked
Circulation.pm :
Using IsMemberBlocked in order to implement finedays
---
 C4/Circulation.pm                                  |   14 +++-
 C4/Members.pm                                      |   67 ++++++++++++++++++++
 ...-permit_to_define_fine_days_in_issuing_rules.pl |   22 +++++++
 3 files changed, 99 insertions(+), 4 deletions(-)
 create mode 100755 installer/data/mysql/atomicupdate/0001-bug_2929-permit_to_define_fine_days_in_issuing_rules.pl

diff --git a/C4/Circulation.pm b/C4/Circulation.pm
index 7f2bfeb..98f324c 100644
--- a/C4/Circulation.pm
+++ b/C4/Circulation.pm
@@ -532,7 +532,6 @@ sub itemissues {
             $data->{'date_due'} = ($data->{'wthdrawn'} eq '1') ? 'Cancelled' : 'Available';
         }
 
-        $sth2->finish;
 
         # Find the last 3 people who borrowed this item.
         $sth2 = $dbh->prepare(
@@ -552,12 +551,10 @@ sub itemissues {
             }    # if
         }    # for
 
-        $sth2->finish;
         $results[$i] = $data;
         $i++;
     }
 
-    $sth->finish;
     return (@results);
 }
 
@@ -731,7 +728,16 @@ sub CanBookBeIssued {
         }
     }
 
-    #
+    my ($blocktype, $count) = C4::Members::IsMemberBlocked($borrower->{'borrowernumber'});
+    if($blocktype == -1){
+        ## remaining overdue documents
+        $issuingimpossible{USERBLOCKEDREMAINING} = $count;
+    }elsif($blocktype == 1){
+        ## blocked because of overdue return
+        $issuingimpossible{USERBLOCKEDOVERDUE} = $count;
+    }
+
+#
     # JB34 CHECKS IF BORROWERS DONT HAVE ISSUE TOO MANY BOOKS
     #
 	my $toomany = TooMany( $borrower, $item->{biblionumber}, $item );
diff --git a/C4/Members.pm b/C4/Members.pm
index d82e5e4..ea17d2b 100644
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -63,6 +63,7 @@ BEGIN {
     &PutPatronImage
     &RmPatronImage
 
+		&IsMemberBlocked
 		&GetMemberAccountRecords
 		&GetBorNotifyAcctRecord
 
@@ -506,6 +507,72 @@ LEFT JOIN categories on borrowers.categorycode=categories.categorycode
     return undef;        
 }
 
+=head2 IsMemberBlocked
+
+=over 4
+
+my $blocked = IsMemberBlocked( $borrowernumber );
+
+return the status, and the number of day or documents, depends his punishment
+
+return :
+-1 if the user have overdue returns
+1 if the user is punished X days
+0 if the user is authorised to loan
+
+=back
+
+=cut
+
+sub IsMemberBlocked {
+    my $borrowernumber = shift;
+    my $dbh            = C4::Context->dbh;
+    # if he have late issues
+    my $sth = $dbh->prepare(
+        "SELECT COUNT(*) as latedocs
+         FROM issues
+         WHERE borrowernumber = ?
+         AND date_due < now()"
+    );
+    $sth->execute($borrowernumber);
+    my $latedocs = $sth->fetchrow_hashref->{'latedocs'};
+
+    return (-1, $latedocs) if $latedocs > 0;
+
+	my $strsth=qq{
+            SELECT
+            ADDDATE(returndate, finedays * DATEDIFF(returndate,date_due) ) AS blockingdate,
+            DATEDIFF(ADDDATE(returndate, finedays * DATEDIFF(returndate,date_due)),NOW()) AS blockedcount
+            FROM old_issues
+	};
+    # or if he must wait to loan
+    if(C4::Context->preference("item-level_itypes")){
+        $strsth.=
+		qq{ LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber)
+            LEFT JOIN issuingrules ON (issuingrules.itemtype=items.itype)}
+    }else{
+        $strsth .= 
+		qq{ LEFT JOIN items ON (items.itemnumber=old_issues.itemnumber)
+            LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
+            LEFT JOIN issuingrules ON (issuingrules.itemtype=biblioitems.itemtype) };
+    }
+	$strsth.=
+        qq{ WHERE finedays IS NOT NULL
+            AND  date_due < returndate
+            AND borrowernumber = ?
+            ORDER BY blockingdate DESC, blockedcount DESC
+            LIMIT 1};
+	$sth=$dbh->prepare($strsth);
+    $sth->execute($borrowernumber);
+    my $row = $sth->fetchrow_hashref;
+    my $blockeddate  = $row->{'blockeddate'};
+    my $blockedcount = $row->{'blockedcount'};
+
+    return (1, $blockedcount) if $blockedcount > 0;
+
+    return 0
+}
+
 =head2 GetMemberIssuesAndFines
 
   ($overdue_count, $issue_count, $total_fines) = &GetMemberIssuesAndFines($borrowernumber);
diff --git a/installer/data/mysql/atomicupdate/0001-bug_2929-permit_to_define_fine_days_in_issuing_rules.pl b/installer/data/mysql/atomicupdate/0001-bug_2929-permit_to_define_fine_days_in_issuing_rules.pl
new file mode 100755
index 0000000..dfe3b6b
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/0001-bug_2929-permit_to_define_fine_days_in_issuing_rules.pl
@@ -0,0 +1,22 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use C4::Context;
+my $dbh=C4::Context->dbh;
+$dbh->do("ALTER TABLE issuingrules ADD 
+		COLUMN `finedays` int(11) default NULL AFTER `fine`,
+		COLUMN `renewalsallowed` smallint(6) default NULL, 
+		COLUMN `reservesallowed` smallint(6) default NULL,
+		");
+$sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
+$sth->execute();
+
+my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
+    
+while(my $row = $sth->fetchrow_hashref){
+      $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
+}
+    
+$dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
+    
+print "Upgrade done (Adding finedays renewalsallowed, and reservesallowed fields in issuingrules table)\n";
-- 
1.6.0.4