View | Details | Raw Unified | Return to bug 20144
Collapse All | Expand All

(-)a/C4/Members.pm (-16 / +20 lines)
Lines 907-931 sub GetBorrowersToExpunge { Link Here
907
907
908
    my $dbh   = C4::Context->dbh;
908
    my $dbh   = C4::Context->dbh;
909
    my $query = q|
909
    my $query = q|
910
        SELECT borrowers.borrowernumber,
910
        SELECT *
911
               MAX(old_issues.timestamp) AS latestissue,
911
        FROM (
912
               MAX(issues.timestamp) AS currentissue
912
            SELECT borrowers.borrowernumber,
913
        FROM   borrowers
913
                   MAX(old_issues.timestamp) AS latestissue,
914
        JOIN   categories USING (categorycode)
914
                   MAX(issues.timestamp) AS currentissue
915
        LEFT JOIN (
915
            FROM   borrowers
916
            SELECT guarantorid
916
            JOIN   categories USING (categorycode)
917
            FROM borrowers
917
            LEFT JOIN (
918
            WHERE guarantorid IS NOT NULL
918
                SELECT guarantorid
919
                AND guarantorid <> 0
919
                FROM borrowers
920
        ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
920
                WHERE guarantorid IS NOT NULL
921
        LEFT JOIN old_issues USING (borrowernumber)
921
                    AND guarantorid <> 0
922
        LEFT JOIN issues USING (borrowernumber)|;
922
            ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
923
            LEFT JOIN old_issues USING (borrowernumber)
924
            LEFT JOIN issues USING (borrowernumber)|;
923
    if ( $filterpatronlist  ){
925
    if ( $filterpatronlist  ){
924
        $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
926
        $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
925
    }
927
    }
926
    $query .= q| WHERE  category_type <> 'S'
928
    $query .= q| WHERE  category_type <> 'S'
927
        AND tmp.guarantorid IS NULL
929
        AND tmp.guarantorid IS NULL
928
   |;
930
    |;
929
    my @query_params;
931
    my @query_params;
930
    if ( $filterbranch && $filterbranch ne "" ) {
932
    if ( $filterbranch && $filterbranch ne "" ) {
931
        $query.= " AND borrowers.branchcode = ? ";
933
        $query.= " AND borrowers.branchcode = ? ";
Lines 947-957 sub GetBorrowersToExpunge { Link Here
947
        $query.=" AND patron_list_id = ? ";
949
        $query.=" AND patron_list_id = ? ";
948
        push( @query_params, $filterpatronlist );
950
        push( @query_params, $filterpatronlist );
949
    }
951
    }
950
    $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
952
    $query .= " GROUP BY borrowers.borrowernumber";
953
    $query .= q|
954
        ) xxx WHERE currentissue IS NULL|;
951
    if ( $filterdate ) {
955
    if ( $filterdate ) {
952
        $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
956
        $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
953
        push @query_params,$filterdate;
957
        push @query_params,$filterdate;
954
    }
958
    }
959
955
    warn $query if $debug;
960
    warn $query if $debug;
956
961
957
    my $sth = $dbh->prepare($query);
962
    my $sth = $dbh->prepare($query);
958
- 

Return to bug 20144