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

(-)a/C4/Members.pm (-17 / +30 lines)
Lines 1940-1989 WHERE roadtypeid=?|; Link Here
1940
1940
1941
=head2 GetBorrowersWhoHaveNotBorrowedSince
1941
=head2 GetBorrowersWhoHaveNotBorrowedSince
1942
1942
1943
  &GetBorrowersWhoHaveNotBorrowedSince($date)
1943
  $borrowers = &GetBorrowersWhoHaveNotBorrowedSince(
1944
      not_borrowered_since => $not_borrowered_since,
1945
      expired_before       => $expired_before,
1946
      category_code        => $category_code,
1947
      branchcode           => $branchcode
1948
  );
1944
1949
1945
this function get all borrowers who haven't borrowed since the date given on input arg.
1950
  This function get all borrowers based on the given criteria.
1946
1951
1947
=cut
1952
=cut
1948
1953
1949
sub GetBorrowersWhoHaveNotBorrowedSince {
1954
sub GetBorrowersWhoHaveNotBorrowedSince {
1950
    my $filterdate = shift||POSIX::strftime("%Y-%m-%d",localtime());
1955
    my $params = shift;
1951
    my $filterexpiry = shift;
1956
1952
    my $filterbranch = shift || 
1957
    my $filterdate     = $params->{'not_borrowered_since'};
1958
    my $filterexpiry   = $params->{'expired_before'};
1959
    my $filtercategory = $params->{'category_code'};
1960
    my $filterbranch   = $params->{'branchcode'} ||
1953
                        ((C4::Context->preference('IndependantBranches') 
1961
                        ((C4::Context->preference('IndependantBranches') 
1954
                             && C4::Context->userenv 
1962
                             && C4::Context->userenv 
1955
                             && C4::Context->userenv->{flags} % 2 !=1 
1963
                             && C4::Context->userenv->{flags} % 2 !=1 
1956
                             && C4::Context->userenv->{branch})
1964
                             && C4::Context->userenv->{branch})
1957
                         ? C4::Context->userenv->{branch}
1965
                         ? C4::Context->userenv->{branch}
1958
                         : "");  
1966
                         : "");  
1967
1959
    my $dbh   = C4::Context->dbh;
1968
    my $dbh   = C4::Context->dbh;
1960
    my $query = "
1969
    my $query = "
1961
        SELECT borrowers.borrowernumber,
1970
        SELECT borrowers.borrowernumber,
1962
               max(old_issues.timestamp) as latestissue,
1971
               MAX(old_issues.timestamp) AS latestissue,
1963
               max(issues.timestamp) as currentissue
1972
               MAX(issues.timestamp) AS currentissue
1964
        FROM   borrowers
1973
        FROM   borrowers
1965
        JOIN   categories USING (categorycode)
1974
        JOIN   categories USING (categorycode)
1966
        LEFT JOIN old_issues USING (borrowernumber)
1975
        LEFT JOIN old_issues USING (borrowernumber)
1967
        LEFT JOIN issues USING (borrowernumber) 
1976
        LEFT JOIN issues USING (borrowernumber) 
1968
        WHERE  category_type <> 'S'
1977
        WHERE  category_type <> 'S'
1969
        AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) 
1978
        AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0)
1970
   ";
1979
   ";
1971
    my @query_params;
1980
    my @query_params;
1972
    if ($filterbranch && $filterbranch ne ""){ 
1981
    if ( $filterbranch && $filterbranch ne "" ) {
1973
        $query.=" AND borrowers.branchcode= ?";
1982
        $query.= " AND borrowers.branchcode = ? ";
1974
        push @query_params,$filterbranch;
1983
        push( @query_params, $filterbranch );
1975
    }
1984
    }
1976
    if($filterexpiry){
1985
    if ( $filterexpiry ) {
1977
        $query .= " AND dateexpiry < ? ";
1986
        $query .= " AND dateexpiry < ? ";
1978
        push @query_params,$filterdate;
1987
        push( @query_params, $filterexpiry );
1979
    }
1988
    }
1980
    $query.=" GROUP BY borrowers.borrowernumber";
1989
    if ( $filtercategory ) {
1981
    if ($filterdate){ 
1990
        $query .= " AND categorycode = ? ";
1982
        $query.=" HAVING (latestissue < ? OR latestissue IS NULL) 
1991
        push( @query_params, $filtercategory );
1983
                  AND currentissue IS NULL";
1992
    }
1993
    $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
1994
    if ( $filterdate ) {
1995
        $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
1984
        push @query_params,$filterdate;
1996
        push @query_params,$filterdate;
1985
    }
1997
    }
1986
    warn $query if $debug;
1998
    warn $query if $debug;
1999
1987
    my $sth = $dbh->prepare($query);
2000
    my $sth = $dbh->prepare($query);
1988
    if (scalar(@query_params)>0){  
2001
    if (scalar(@query_params)>0){  
1989
        $sth->execute(@query_params);
2002
        $sth->execute(@query_params);
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt (-7 / +24 lines)
Lines 10-17 Link Here
10
         */
10
         */
11
          function checkForm(form) {
11
          function checkForm(form) {
12
              if((form.checkbox[0].checked)){
12
              if((form.checkbox[0].checked)){
13
                  if(!(form.date1.value)){
13
                  if ( (!form.date1.value) && (!form.borrower_dateexpiry.value) && (!form.borrower_categorycode.value) ){
14
                    alert(_("please enter a date !"));
14
                    alert(_("Please enter at least one criterion for deletion!"));
15
                    document.form.date1.focus();
15
                    document.form.date1.focus();
16
                    return false;
16
                    return false;
17
                  }
17
                  }
Lines 57-70 Link Here
57
    <form name="f1" onsubmit="return checkForm(this);" action="/cgi-bin/koha/tools/cleanborrowers.pl" method="post">
57
    <form name="f1" onsubmit="return checkForm(this);" action="/cgi-bin/koha/tools/cleanborrowers.pl" method="post">
58
    <fieldset>
58
    <fieldset>
59
    <legend>What do you want to do ?</legend>
59
    <legend>What do you want to do ?</legend>
60
        <p><input id="checkborrower" type="checkbox" name="checkbox" value="borrower" checked="checked" />
60
        <h3><input id="checkborrower" type="checkbox" name="checkbox" value="borrower" /> Delete borrowers</h3>
61
        <label for="checkborrower">Delete borrower who has not borrowed since:</label>
61
62
        <input size="10" id="date1" name="filterdate1" value="[% filterdate1 %]" type="text" class="datepicker" />
62
        <label for="checkborrower">Who have not borrowed since:</label>
63
        <input size="10" id="date1" name="filterdate1" type="text" class="datepicker" />
64
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
65
66
        <label for="borrower_dateexpiry">Whose expiration date is before:</label>
67
        <input size="10" id=borrower_dateexpiry" name="borrower_dateexpiry" type="text" class="datepicker" />
63
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
68
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
64
69
65
        <p><input id="checkissue" type="checkbox" name="checkbox" value="issue" checked="checked" />
70
        <label for="borrower_categorycode">Whose patron category is:</label>
71
        <select id="borrower_categorycode" name="borrower_categorycode">
72
            <option value="" selected="selected">Any</option>
73
            [% FOREACH bc IN borrower_categorycodes %]
74
                [% UNLESS bc.categorycode == 'S' %]
75
                    <option value="[% bc.categorycode %]">[% bc.description %]</option>
76
                [% END %]
77
            [% END %]
78
        </select>
79
80
        <h3><input id="checkissue" type="checkbox" name="checkbox" value="issue" /> Anonymize history</h3>
66
        <label for="checkissue">Anonymize check-out history older than</label>
81
        <label for="checkissue">Anonymize check-out history older than</label>
67
        <input size="10" id="date2" name="filterdate2" value="[% filterdate2 %]" type="text" class="datepicker" />
82
        <input size="10" id="date2" name="filterdate2" type="text" class="datepicker" />
68
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
83
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
69
84
70
            <!-- hidden here -->
85
            <!-- hidden here -->
Lines 108-113 Link Here
108
            <input type="hidden" name="step3" value="1" />
123
            <input type="hidden" name="step3" value="1" />
109
            <input type="hidden" name="filterdate1" value="[% filterdate1 %]" />
124
            <input type="hidden" name="filterdate1" value="[% filterdate1 %]" />
110
            <input type="hidden" name="filterdate2" value="[% filterdate2 %]" />
125
            <input type="hidden" name="filterdate2" value="[% filterdate2 %]" />
126
            <input type="hidden" name="borrower_dateexpiry" value="[% borrower_dateexpiry %]" />
127
            <input type="hidden" name="borrower_categorycode" value="[% borrower_categorycode %]" />
111
    </fieldset>
128
    </fieldset>
112
	<fieldset class="action"><input type="submit" value="Finish" /> <a class="cancel" href="/cgi-bin/koha/tools/cleanborrowers.pl">Cancel</a></fieldset>
129
	<fieldset class="action"><input type="submit" value="Finish" /> <a class="cancel" href="/cgi-bin/koha/tools/cleanborrowers.pl">Cancel</a></fieldset>
113
        </form>
130
        </form>
(-)a/tools/cleanborrowers.pl (-11 / +15 lines)
Lines 53-58 my $params = $cgi->Vars; Link Here
53
53
54
my $filterdate1;               # the date which filter on issue history.
54
my $filterdate1;               # the date which filter on issue history.
55
my $filterdate2;               # the date which filter on borrowers last issue.
55
my $filterdate2;               # the date which filter on borrowers last issue.
56
my $borrower_dateexpiry;
57
my $borrower_categorycode;
56
58
57
# getting the template
59
# getting the template
58
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
60
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
Lines 68-79 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
68
if ( $params->{'step2'} ) {
70
if ( $params->{'step2'} ) {
69
    $filterdate1 = format_date_in_iso($params->{'filterdate1'});
71
    $filterdate1 = format_date_in_iso($params->{'filterdate1'});
70
    $filterdate2 = format_date_in_iso($params->{'filterdate2'});
72
    $filterdate2 = format_date_in_iso($params->{'filterdate2'});
73
    $borrower_dateexpiry = format_date_in_iso($params->{'borrower_dateexpiry'});
74
    $borrower_categorycode = $params->{'borrower_categorycode'};
75
71
    my %checkboxes = map { $_ => 1 } split /\0/, $params->{'checkbox'};
76
    my %checkboxes = map { $_ => 1 } split /\0/, $params->{'checkbox'};
72
77
73
    my $totalDel;
78
    my $totalDel;
74
    my $membersToDelete;
79
    my $membersToDelete;
75
    if ($checkboxes{borrower}) {
80
    if ($checkboxes{borrower}) {
76
        $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince($filterdate1, 1);
81
        $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince({ not_borrowered_since => $filterdate1, expired_before => $borrower_dateexpiry, category_code => $borrower_categorycode });
77
        $totalDel = scalar @$membersToDelete;
82
        $totalDel = scalar @$membersToDelete;
78
            
83
            
79
    }
84
    }
Lines 93-102 if ( $params->{'step2'} ) { Link Here
93
        memberstoanonymize_list => $membersToAnonymize,    
98
        memberstoanonymize_list => $membersToAnonymize,    
94
        filterdate1      => format_date($filterdate1),
99
        filterdate1      => format_date($filterdate1),
95
        filterdate2      => format_date($filterdate2),
100
        filterdate2      => format_date($filterdate2),
101
        borrower_dateexpiry => $borrower_dateexpiry,
102
        borrower_categorycode => $borrower_categorycode,
96
    );
103
    );
97
### TODO : Use GetBorrowersNamesAndLatestIssue function in order to get the borrowers to delete or anonymize.
104
98
### Now, we are only using total, which is not enough imlo
105
    ### TODO : Use GetBorrowersNamesAndLatestIssue function in order to get the borrowers to delete or anonymize.
99
    #writing the template
100
    output_html_with_http_headers $cgi, $cookie, $template->output;
106
    output_html_with_http_headers $cgi, $cookie, $template->output;
101
    exit;
107
    exit;
102
}
108
}
Lines 104-109 if ( $params->{'step2'} ) { Link Here
104
if ( $params->{'step3'} ) {
110
if ( $params->{'step3'} ) {
105
    $filterdate1 = format_date_in_iso($params->{'filterdate1'});
111
    $filterdate1 = format_date_in_iso($params->{'filterdate1'});
106
    $filterdate2 = format_date_in_iso($params->{'filterdate2'});
112
    $filterdate2 = format_date_in_iso($params->{'filterdate2'});
113
    $borrower_dateexpiry = format_date_in_iso($params->{'borrower_dateexpiry'});
114
    $borrower_categorycode = $params->{'borrower_categorycode'};
115
107
    my $do_delete = $params->{'do_delete'};
116
    my $do_delete = $params->{'do_delete'};
108
    my $do_anonym = $params->{'do_anonym'};
117
    my $do_anonym = $params->{'do_anonym'};
109
118
Lines 111-117 if ( $params->{'step3'} ) { Link Here
111
    
120
    
112
    # delete members
121
    # delete members
113
    if ($do_delete) {
122
    if ($do_delete) {
114
        my $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince($filterdate1, 1);
123
        my $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince({ not_borrowered_since => $filterdate1, expired_before => $borrower_dateexpiry, category_code => $borrower_categorycode });
115
        $totalDel = scalar(@$membersToDelete);
124
        $totalDel = scalar(@$membersToDelete);
116
        $radio    = $params->{'radio'};
125
        $radio    = $params->{'radio'};
117
        if ( $radio eq 'trash' ) {
126
        if ( $radio eq 'trash' ) {
Lines 154-169 if ( $params->{'step3'} ) { Link Here
154
    exit;
163
    exit;
155
}
164
}
156
165
157
#default value set to the template are the 'CNIL' value.
158
my ( $year, $month, $day ) = &Today();
159
$filterdate1 = format_date(sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, -1, 0)));
160
$filterdate2 = format_date(sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, 0, -3)));
161
162
$template->param(
166
$template->param(
163
    step1       => '1',
167
    step1       => '1',
164
    filterdate1 => $filterdate1,
168
    filterdate1 => $filterdate1,
165
    filterdate2 => $filterdate2,
169
    filterdate2 => $filterdate2,
166
    DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
170
    DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
171
    borrower_categorycodes => GetBorrowercategoryList(),
167
);
172
);
168
173
169
#writing the template
174
#writing the template
170
- 

Return to bug 9076