(bug #4358) fix sql query in C4::Members::GetAllIssues

[koha.git] / C4 / Members.pm

diff --git a/C4/Members.pm b/C4/Members.pm
index 4ac9917..3318a1c 100644 (file)
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -148,7 +148,7 @@ name.
 
 C<$filter> is assumed to be a list of elements to filter results on
 
-C<$showallbranches> is used in IndependantBranches Context to display all branches results.
+C<$showallbranches> is used in IndependentBranchPatron Context to display all branches results.
 
 C<&SearchMember> returns a two-element list. C<$borrowers> is a
 reference-to-array; each element is a reference-to-hash, whose keys
@@ -173,8 +173,20 @@ sub SearchMember {
     $query = "SELECT * FROM borrowers
         LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
         ";
-    my $sth = $dbh->prepare("$query WHERE cardnumber = ?");
-    $sth->execute($searchstring);
+    my (@where_string, @bind_params);
+    push @where_string, "cardnumber=?";
+    push @bind_params, $searchstring;
+    if (C4::Context->preference("IndependantBranchPatron") && !$showallbranches){
+          if (C4::Context->userenv && (C4::Context->userenv->{flags} % 2) !=1 && C4::Context->userenv->{'branch'}){
+            unless (C4::Context->userenv->{'branch'} eq "insecure"){
+               push @where_string,"borrowers.branchcode =?";
+               push @bind_params,C4::Context->userenv->{'branch'};
+           }
+          }
+    }
+    my $sth = $dbh->prepare("$query WHERE ".join(" AND ", @where_string));
+   
+    $sth->execute(@bind_params);
     my $data = $sth->fetchall_arrayref({});
     if (@$data){
         return ( scalar(@$data), $data );
@@ -185,8 +197,8 @@ sub SearchMember {
     {
         $query .= ($category_type ? " AND category_type = ".$dbh->quote($category_type) : ""); 
         $query .= " WHERE (surname LIKE ? OR cardnumber like ?) ";
-        if (C4::Context->preference("IndependantBranches") && !$showallbranches){
-          if (C4::Context->userenv && C4::Context->userenv->{flags}!=1 && C4::Context->userenv->{'branch'}){
+        if (C4::Context->preference("IndependentBranchPatron") && !$showallbranches){
+          if (C4::Context->userenv && (C4::Context->userenv->{flags} % 2) !=1 && C4::Context->userenv->{'branch'}){
             $query.=" AND borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'}) unless (C4::Context->userenv->{'branch'} eq "insecure");
           }
         }
@@ -198,8 +210,8 @@ sub SearchMember {
         @data  = split( ' ', $searchstring );
         $count = @data;
         $query .= " WHERE ";
-        if (C4::Context->preference("IndependantBranches") && !$showallbranches){
-          if (C4::Context->userenv && C4::Context->userenv->{flags}!=1 && C4::Context->userenv->{'branch'}){
+        if (C4::Context->preference("IndependentBranchPatron") && !$showallbranches){
+          if (C4::Context->userenv && (C4::Context->userenv->{flags} %2 )!=1 && C4::Context->userenv->{'branch'}){
             $query.=" borrowers.branchcode =".$dbh->quote(C4::Context->userenv->{'branch'})." AND " unless (C4::Context->userenv->{'branch'} eq "insecure");
           }      
         }     
@@ -222,7 +234,7 @@ sub SearchMember {
 
             # FIXME - .= <<EOT;
         }
-        $query = $query . ") OR cardnumber LIKE ? ";
+        $query = $query . " OR cardnumber LIKE ? ) ";
         push( @bind, $searchstring );
         if (C4::Context->preference('ExtendedPatronAttributes')) {
             $query .= "OR borrowernumber IN (
@@ -655,8 +667,9 @@ Returns the borrowernumber
 sub AddMember {
     my (%data) = @_;
     my $dbh = C4::Context->dbh;
-    $data{'userid'} = '' unless $data{'password'};
+
     $data{'password'} = md5_base64( $data{'password'} ) if $data{'password'};
+    $data{'password'} = '!' if (not $data{'password'} and $data{'userid'});
     
     # WE SHOULD NEVER PASS THIS SUBROUTINE ANYTHING OTHER THAN ISO DATES
     # IF YOU UNCOMMENT THESE LINES YOU BETTER HAVE A DARN COMPELLING REASON
@@ -1066,8 +1079,8 @@ sub GetAllIssues {
         # apparently specific to HLT.  Not sure if the ReadingHistory
         # syspref is still required, as old_issues by design
         # is no longer checked with each loan.
-        my $query2 = "SELECT * FROM oldissues
-                      LEFT JOIN items ON items.itemnumber=oldissues.itemnumber
+        my $query2 = "SELECT * FROM old_issues
+                      LEFT JOIN items ON items.itemnumber=old_issues.itemnumber
                       LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
                       LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
                       WHERE borrowernumber=? 
@@ -1755,7 +1768,7 @@ Looks up the different title . Returns array  with all borrowers title
 =cut
 
 sub GetTitles {
-    my @borrowerTitle = split /,|\|/,C4::Context->preference('BorrowersTitles');
+    my @borrowerTitle = split (/,|\|/,C4::Context->preference('BorrowersTitles'));
     unshift( @borrowerTitle, "" );
     my $count=@borrowerTitle;
     if ($count == 1){
@@ -1869,11 +1882,15 @@ sub GetBorrowersWhoHaveNotBorrowedSince {
                          : "");  
     my $dbh   = C4::Context->dbh;
     my $query = "
-        SELECT borrowers.borrowernumber,max(issues.timestamp) as latestissue
+        SELECT borrowers.borrowernumber,
+               max(old_issues.timestamp) as latestissue,
+               max(issues.timestamp) as currentissue
         FROM   borrowers
         JOIN   categories USING (categorycode)
-        LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber
+        LEFT JOIN old_issues USING (borrowernumber)
+        LEFT JOIN issues USING (borrowernumber) 
         WHERE  category_type <> 'S'
+        AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) 
    ";
     my @query_params;
     if ($filterbranch && $filterbranch ne ""){ 
@@ -1881,11 +1898,13 @@ sub GetBorrowersWhoHaveNotBorrowedSince {
         push @query_params,$filterbranch;
     }
     if($filterexpiry){
-        $query .= " AND dateexpiry < NOW() ";
+        $query .= " AND dateexpiry < ? ";
+        push @query_params,$filterdate;
     }
     $query.=" GROUP BY borrowers.borrowernumber";
     if ($filterdate){ 
-        $query.=" HAVING latestissue <? OR latestissue IS NULL";
+        $query.=" HAVING (latestissue < ? OR latestissue IS NULL) 
+                  AND currentissue IS NULL";
         push @query_params,$filterdate;
     }
     warn $query if $debug;