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

(-)a/C4/Members.pm (-17 / +30 lines)
Lines 1903-1952 WHERE roadtypeid=?|; Link Here
1903
1903
1904
=head2 GetBorrowersWhoHaveNotBorrowedSince
1904
=head2 GetBorrowersWhoHaveNotBorrowedSince
1905
1905
1906
  &GetBorrowersWhoHaveNotBorrowedSince($date)
1906
  $borrowers = &GetBorrowersWhoHaveNotBorrowedSince( 
1907
      not_borrowered_since => $not_borrowered_since,
1908
      expired_before       => $expired_before,
1909
      category_code        => $category_code,
1910
      branchcode           => $branchcode
1911
  );
1907
1912
1908
this function get all borrowers who haven't borrowed since the date given on input arg.
1913
  This function get all borrowers based on the given criteria.
1909
1914
1910
=cut
1915
=cut
1911
1916
1912
sub GetBorrowersWhoHaveNotBorrowedSince {
1917
sub GetBorrowersWhoHaveNotBorrowedSince {
1913
    my $filterdate = shift||POSIX::strftime("%Y-%m-%d",localtime());
1918
    my $params = shift;
1914
    my $filterexpiry = shift;
1919
1915
    my $filterbranch = shift || 
1920
    my $filterdate     = $params->{'not_borrowered_since'};
1921
    my $filterexpiry   = $params->{'expired_before'};
1922
    my $filtercategory = $params->{'category_code'};
1923
    my $filterbranch   = $params->{'branchcode'} || 
1916
                        ((C4::Context->preference('IndependantBranches') 
1924
                        ((C4::Context->preference('IndependantBranches') 
1917
                             && C4::Context->userenv 
1925
                             && C4::Context->userenv 
1918
                             && C4::Context->userenv->{flags} % 2 !=1 
1926
                             && C4::Context->userenv->{flags} % 2 !=1 
1919
                             && C4::Context->userenv->{branch})
1927
                             && C4::Context->userenv->{branch})
1920
                         ? C4::Context->userenv->{branch}
1928
                         ? C4::Context->userenv->{branch}
1921
                         : "");  
1929
                         : "");  
1930
1922
    my $dbh   = C4::Context->dbh;
1931
    my $dbh   = C4::Context->dbh;
1923
    my $query = "
1932
    my $query = "
1924
        SELECT borrowers.borrowernumber,
1933
        SELECT borrowers.borrowernumber,
1925
               max(old_issues.timestamp) as latestissue,
1934
               MAX(old_issues.timestamp) AS latestissue,
1926
               max(issues.timestamp) as currentissue
1935
               MAX(issues.timestamp) AS currentissue
1927
        FROM   borrowers
1936
        FROM   borrowers
1928
        JOIN   categories USING (categorycode)
1937
        JOIN   categories USING (categorycode)
1929
        LEFT JOIN old_issues USING (borrowernumber)
1938
        LEFT JOIN old_issues USING (borrowernumber)
1930
        LEFT JOIN issues USING (borrowernumber) 
1939
        LEFT JOIN issues USING (borrowernumber) 
1931
        WHERE  category_type <> 'S'
1940
        WHERE  category_type <> 'S'
1932
        AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) 
1941
        AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0)
1933
   ";
1942
   ";
1934
    my @query_params;
1943
    my @query_params;
1935
    if ($filterbranch && $filterbranch ne ""){ 
1944
    if ( $filterbranch && $filterbranch ne "" ) { 
1936
        $query.=" AND borrowers.branchcode= ?";
1945
        $query.= " AND borrowers.branchcode = ? ";
1937
        push @query_params,$filterbranch;
1946
        push( @query_params, $filterbranch );
1938
    }
1947
    }
1939
    if($filterexpiry){
1948
    if ( $filterexpiry ) {
1940
        $query .= " AND dateexpiry < ? ";
1949
        $query .= " AND dateexpiry < ? ";
1941
        push @query_params,$filterdate;
1950
        push( @query_params, $filterexpiry );
1942
    }
1951
    }
1943
    $query.=" GROUP BY borrowers.borrowernumber";
1952
    if ( $filtercategory ) {
1944
    if ($filterdate){ 
1953
        $query .= " AND categorycode = ? ";
1945
        $query.=" HAVING (latestissue < ? OR latestissue IS NULL) 
1954
        push( @query_params, $filtercategory );
1946
                  AND currentissue IS NULL";
1955
    }    
1956
    $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
1957
    if ( $filterdate ) { 
1958
        $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
1947
        push @query_params,$filterdate;
1959
        push @query_params,$filterdate;
1948
    }
1960
    }
1949
    warn $query if $debug;
1961
    warn $query if $debug;
1962
1950
    my $sth = $dbh->prepare($query);
1963
    my $sth = $dbh->prepare($query);
1951
    if (scalar(@query_params)>0){  
1964
    if (scalar(@query_params)>0){  
1952
        $sth->execute(@query_params);
1965
        $sth->execute(@query_params);
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt (-7 / +22 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
                <option value="[% bc.categorycode %]">[% bc.description %]</option>
75
            [% END %]
76
        </select>
77
78
        <h3><input id="checkissue" type="checkbox" name="checkbox" value="issue" /> Anonymize history</h3>
66
        <label for="checkissue">Anonymize check-out history older than</label>
79
        <label for="checkissue">Anonymize check-out history older than</label>
67
        <input size="10" id="date2" name="filterdate2" value="[% filterdate2 %]" type="text" class="datepicker" />
80
        <input size="10" id="date2" name="filterdate2" type="text" class="datepicker" />
68
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
81
        <span class="hint">[% INCLUDE 'date-format.inc' %]</span></p>
69
82
70
            <!-- hidden here -->
83
            <!-- hidden here -->
Lines 108-113 Link Here
108
            <input type="hidden" name="step3" value="1" />
121
            <input type="hidden" name="step3" value="1" />
109
            <input type="hidden" name="filterdate1" value="[% filterdate1 %]" />
122
            <input type="hidden" name="filterdate1" value="[% filterdate1 %]" />
110
            <input type="hidden" name="filterdate2" value="[% filterdate2 %]" />
123
            <input type="hidden" name="filterdate2" value="[% filterdate2 %]" />
124
            <input type="hidden" name="borrower_dateexpiry" value="[% borrower_dateexpiry %]" />
125
            <input type="hidden" name="borrower_categorycode" value="[% borrower_categorycode %]" />
111
    </fieldset>
126
    </fieldset>
112
	<fieldset class="action"><input type="submit" value="Finish" /> <a class="cancel" href="/cgi-bin/koha/tools/cleanborrowers.pl">Cancel</a></fieldset>
127
	<fieldset class="action"><input type="submit" value="Finish" /> <a class="cancel" href="/cgi-bin/koha/tools/cleanborrowers.pl">Cancel</a></fieldset>
113
        </form>
128
        </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