From 98276af85832050490f6b63e32445585008552db Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Wed, 14 Nov 2012 11:40:21 -0500 Subject: [PATCH] Bug 9076 - Add ability to delete borrowers by expiration date and category code to cleanborrowers.pl Content-Type: text/plain; charset="utf-8" --- C4/Members.pm | 47 ++++++++++++------- .../prog/en/modules/tools/cleanborrowers.tt | 31 ++++++++++--- tools/cleanborrowers.pl | 25 ++++++---- 3 files changed, 69 insertions(+), 34 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index b2f45b9..fcccc6e 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1903,50 +1903,63 @@ WHERE roadtypeid=?|; =head2 GetBorrowersWhoHaveNotBorrowedSince - &GetBorrowersWhoHaveNotBorrowedSince($date) + $borrowers = &GetBorrowersWhoHaveNotBorrowedSince( + not_borrowered_since => $not_borrowered_since, + expired_before => $expired_before, + category_code => $category_code, + branchcode => $branchcode + ); -this function get all borrowers who haven't borrowed since the date given on input arg. + This function get all borrowers based on the given criteria. =cut sub GetBorrowersWhoHaveNotBorrowedSince { - my $filterdate = shift||POSIX::strftime("%Y-%m-%d",localtime()); - my $filterexpiry = shift; - my $filterbranch = shift || + my $params = shift; + + my $filterdate = $params->{'not_borrowered_since'}; + my $filterexpiry = $params->{'expired_before'}; + my $filtercategory = $params->{'category_code'}; + my $filterbranch = $params->{'branchcode'} || ((C4::Context->preference('IndependantBranches') && C4::Context->userenv && C4::Context->userenv->{flags} % 2 !=1 && C4::Context->userenv->{branch}) ? C4::Context->userenv->{branch} : ""); + my $dbh = C4::Context->dbh; my $query = " SELECT borrowers.borrowernumber, - max(old_issues.timestamp) as latestissue, - max(issues.timestamp) as currentissue + MAX(old_issues.timestamp) AS latestissue, + MAX(issues.timestamp) AS currentissue FROM borrowers JOIN categories USING (categorycode) 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) + AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) "; my @query_params; - if ($filterbranch && $filterbranch ne ""){ - $query.=" AND borrowers.branchcode= ?"; - push @query_params,$filterbranch; + if ( $filterbranch && $filterbranch ne "" ) { + $query.= " AND borrowers.branchcode = ? "; + push( @query_params, $filterbranch ); } - if($filterexpiry){ + if ( $filterexpiry ) { $query .= " AND dateexpiry < ? "; - push @query_params,$filterdate; + push( @query_params, $filterexpiry ); } - $query.=" GROUP BY borrowers.borrowernumber"; - if ($filterdate){ - $query.=" HAVING (latestissue < ? OR latestissue IS NULL) - AND currentissue IS NULL"; + if ( $filtercategory ) { + $query .= " AND categorycode = ? "; + push( @query_params, $filtercategory ); + } + $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL "; + if ( $filterdate ) { + $query.=" AND ( latestissue < ? OR latestissue IS NULL ) "; push @query_params,$filterdate; } warn $query if $debug; + my $sth = $dbh->prepare($query); if (scalar(@query_params)>0){ $sth->execute(@query_params); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt index f7193e3..0b745ce 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/cleanborrowers.tt @@ -10,8 +10,8 @@ */ function checkForm(form) { if((form.checkbox[0].checked)){ - if(!(form.date1.value)){ - alert(_("please enter a date !")); + if ( (!form.date1.value) && (!form.borrower_dateexpiry.value) && (!form.borrower_categorycode.value) ){ + alert(_("Please enter at least one criterion for deletion!")); document.form.date1.focus(); return false; } @@ -57,14 +57,29 @@
What do you want to do ? -

- - +

Delete borrowers

+ + + + [% INCLUDE 'date-format.inc' %]

+ + + [% INCLUDE 'date-format.inc' %]

-

+ + + +

Anonymize history

- + [% INCLUDE 'date-format.inc' %]

@@ -108,6 +123,8 @@ + +
Cancel
diff --git a/tools/cleanborrowers.pl b/tools/cleanborrowers.pl index 0b37c20..6a9729c 100755 --- a/tools/cleanborrowers.pl +++ b/tools/cleanborrowers.pl @@ -53,6 +53,8 @@ my $params = $cgi->Vars; my $filterdate1; # the date which filter on issue history. my $filterdate2; # the date which filter on borrowers last issue. +my $borrower_dateexpiry; +my $borrower_categorycode; # getting the template my ( $template, $loggedinuser, $cookie ) = get_template_and_user( @@ -68,12 +70,15 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( if ( $params->{'step2'} ) { $filterdate1 = format_date_in_iso($params->{'filterdate1'}); $filterdate2 = format_date_in_iso($params->{'filterdate2'}); + $borrower_dateexpiry = format_date_in_iso($params->{'borrower_dateexpiry'}); + $borrower_categorycode = $params->{'borrower_categorycode'}; + my %checkboxes = map { $_ => 1 } split /\0/, $params->{'checkbox'}; my $totalDel; my $membersToDelete; if ($checkboxes{borrower}) { - $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince($filterdate1, 1); + $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince({ not_borrowered_since => $filterdate1, expired_before => $borrower_dateexpiry, category_code => $borrower_categorycode }); $totalDel = scalar @$membersToDelete; } @@ -93,10 +98,11 @@ if ( $params->{'step2'} ) { memberstoanonymize_list => $membersToAnonymize, filterdate1 => format_date($filterdate1), filterdate2 => format_date($filterdate2), + borrower_dateexpiry => $borrower_dateexpiry, + borrower_categorycode => $borrower_categorycode, ); -### TODO : Use GetBorrowersNamesAndLatestIssue function in order to get the borrowers to delete or anonymize. -### Now, we are only using total, which is not enough imlo - #writing the template + + ### TODO : Use GetBorrowersNamesAndLatestIssue function in order to get the borrowers to delete or anonymize. output_html_with_http_headers $cgi, $cookie, $template->output; exit; } @@ -104,6 +110,9 @@ if ( $params->{'step2'} ) { if ( $params->{'step3'} ) { $filterdate1 = format_date_in_iso($params->{'filterdate1'}); $filterdate2 = format_date_in_iso($params->{'filterdate2'}); + $borrower_dateexpiry = format_date_in_iso($params->{'borrower_dateexpiry'}); + $borrower_categorycode = $params->{'borrower_categorycode'}; + my $do_delete = $params->{'do_delete'}; my $do_anonym = $params->{'do_anonym'}; @@ -111,7 +120,7 @@ if ( $params->{'step3'} ) { # delete members if ($do_delete) { - my $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince($filterdate1, 1); + my $membersToDelete = GetBorrowersWhoHaveNotBorrowedSince({ not_borrowered_since => $filterdate1, expired_before => $borrower_dateexpiry, category_code => $borrower_categorycode }); $totalDel = scalar(@$membersToDelete); $radio = $params->{'radio'}; if ( $radio eq 'trash' ) { @@ -154,16 +163,12 @@ if ( $params->{'step3'} ) { exit; } -#default value set to the template are the 'CNIL' value. -my ( $year, $month, $day ) = &Today(); -$filterdate1 = format_date(sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, -1, 0))); -$filterdate2 = format_date(sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, 0, -3))); - $template->param( step1 => '1', filterdate1 => $filterdate1, filterdate2 => $filterdate2, DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), + borrower_categorycodes => GetBorrowercategoryList(), ); #writing the template -- 1.7.2.5