From ca31e4326eca42ce01982f18722048076d6e156d Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 4 Mar 2014 08:23:56 -0500 Subject: [PATCH] Bug 11887 - Add script for collections agency reports Some libraries use collections agencies such as Unique Management Services to collection large unpaid balances owed to the library. It would be helpful if Koha had a standard script to generate these reports, rather than forcing each library to create their own solution. This script is written following the guidelines for Unique Management Systems. However, the reports it outputs should be useable by any collections agency. Test Plan: 1) Create the four required patron attributes 2) Create a patron who owes more then 5.00 in fees 3) Run the submission report account_collections.pl -r submission \ -mb 3 -bd $yesterday_iso -ed $tomorrow_iso -f 10 \ -ft $fee_type -fd $fee_desc -c $can-collect-attribute-code \ -i $in-collections-attribute-code -l $last-updated-attribute-code \ -p previous-balance-attribute-code 4) Note the patron is now marked as in collections and has had the collections fee of 10.00 applied 5) Add a new fee to the patron's account 6) Run the update report with the same additional parameters 7) Verify the csv file generated contains that patron 8) Run the sync report with the same additional parameters 9) Note the csv file generated contains that patron --- misc/cronjobs/account_collections.pl | 337 ++++++++++++++++++++++++++++++++++ 1 files changed, 337 insertions(+), 0 deletions(-) create mode 100755 misc/cronjobs/account_collections.pl diff --git a/misc/cronjobs/account_collections.pl b/misc/cronjobs/account_collections.pl new file mode 100755 index 0000000..75a8fef --- /dev/null +++ b/misc/cronjobs/account_collections.pl @@ -0,0 +1,337 @@ +#!/usr/bin/perl + +# Copyright 2014 ByWater Solutions +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Getopt::Long; +use File::Spec; +use Text::CSV; + +use C4::Context; +use Koha::Accounts qw(AddDebit); + +my $help; +my $verbose; +my $directory; + +my $minimum_balance = "0.01"; +my $begin_date; +my $end_date; +my @exclude_fee_types; +my @branchcodes; +my @exclude_patron_types; +my $processing_fee; +my $processing_fee_type; +my $processing_fee_description; +my $can_collect_attribute_code; +my $in_collections_attribute_code; +my $last_updated_attribute_code; +my $previous_balance_attribute_code; +my $report_type; +my @where; +my $separator = ","; + +GetOptions( + 'h|help' => \$help, + 'v|verbose' => \$verbose, + 'd|dir:s' => \$directory, + + 'f|fee:s' => \$processing_fee, + 'ft|fee-type:s' => \$processing_fee_type, + 'fd|fee-description:s' => \$processing_fee_description, + + 'mb|min-balance:s' => \$minimum_balance, + 'bd|begin-date:s' => \$begin_date, + 'ed|end-date:s' => \$end_date, + 'et|exclude-fee-type:s' => \@exclude_fee_types, + 'b|branchcode:s' => \@branchcodes, + 'ep|exclude-patron-type:s' => \@exclude_patron_types, + + 'c|can-collect-attribute-code:s' => \$can_collect_attribute_code, + 'i|in-collections-attribute-code:s' => \$in_collections_attribute_code, + 'l|last-updated-attribute-code:s' => \$last_updated_attribute_code, + 'p|previous-balance-attribute-code:s' => \$previous_balance_attribute_code, + + 'r|report-type:s' => \$report_type, + + 'w|where:s' => \@where, + + 's|separator:s' => \$separator, +); +my $usage = << 'ENDUSAGE'; + +This script has the following parameters : + -h --help: this message + -d --dir: ouput directory (defaults to /tmp if !exist) + -v --verbose + + The following parameters are required for the submission report + -mb --min-balance: Minimum monetary value associated with a particular set of defined library branches + -bd --begin-date: Date before which that unpaid fines DO NOT apply to the Minimum Balance criterion + -ed --end-date: Date after which unpaid fines DO NOT apply to the Minimum Balance criterion + -et --exclude-fee-type: Fine codes/types which should NOT apply to the Minimum Balance criterion, repeatable + -b --branchcode: Only unpaid fines/fees of patrons associated with specified branches should be considered, repeatable + -ep --exclude-patron-type: Patron or borrower types which should NOT be referred to the collections agency, repeatable + -f --fee: Fee to charge patrons who enter into collections + -ft --fee-type: Fee type to charge + -fd --fee-description: Description to use for fee + + -c --can-collect-attribute-code: The patron attribute code that defines if a patron can be collected from ( YES_NO authorized value ) + -i --in-collections-attribute-code: The patron attribute code that defines if a patron is currently in collecions ( YES_NO authorised value ) + -l --last-updated-attribute-code: The patron attribute code that defines the date the patron was last updated for collections purposes + -p --previous-balance-attribute-code: The patron balance of the last time the update report was run + + -r --report-type: The report type to execute: + submission Output the submission report, new patrons that meet library defined criteria for referral to collection agency. + update Output the update report, previously referred accounts that have had a change in balance (positive or negative) + since the last time the update report was generated. + sync Output the sync report, a list of all accounts currently referred to the collections agency + + -w --where: Additional clauses you want added to the WHERE statment, repeatable + + -s --separator: The character used for separating fields, default is a comma (,) + +ENDUSAGE + +if ( + $help + || !( + $report_type + && $can_collect_attribute_code + && $in_collections_attribute_code + && $last_updated_attribute_code + && $previous_balance_attribute_code + ) + || ( + $report_type eq 'submission' + && !( + $processing_fee + && $processing_fee_type + && $processing_fee_description + && $minimum_balance + ) + ) + ) +{ + print $usage; + exit; +} + +my $ymd = DateTime->now( time_zone => C4::Context->tz() )->ymd(); + +my $csv = Text::CSV->new( { sep_char => $separator } ) + or die "Cannot use CSV: " . Text::CSV->error_diag(); +$csv->eol("\r\n"); + +my $fh; +$directory ||= File::Spec->tmpdir(); +my $name = "$report_type-$ymd.csv"; +my $file = File::Spec->catfile( $directory, $name ); +say "Opening CSV file $file for writing..." if $verbose; +open $fh, ">:encoding(utf8)", $file or die "$file: $!"; + +my $dbh = C4::Context->dbh(); + +my @parameters; +my $insert_attribute_sql = q{ + INSERT INTO borrower_attributes ( borrowernumber, code, attribute ) VALUES ( ?, ?, ? ) +}; +my $delete_attribute_sql = q{ + DELETE FROM borrower_attributes WHERE borrowernumber = ? AND code = ? +}; +my $sql = q{ + SELECT + borrowers.*, + guarantor.firstname AS guarantor_firstname, + guarantor.surname AS guarantor_surname, + DATE(account_debits.created_on) AS most_recent_unpaid_fine_date, + SUM(account_debits.amount_outstanding) AS computed_account_balance, + COALESCE( ba_c.attribute, 1 ) AS can_collect, + COALESCE( ba_i.attribute, 0 ) AS in_collections, + COALESCE( ba_l.attribute, 0 ) AS last_updated, + COALESCE( ba_p.attribute, 0 ) AS previous_balance + FROM borrowers + LEFT JOIN account_debits USING ( borrowernumber ) + LEFT JOIN borrower_attributes ba_c ON borrowers.borrowernumber = ba_c.borrowernumber AND ( ba_c.code = ? OR ba_c.code IS NULL ) + LEFT JOIN borrower_attributes ba_i ON borrowers.borrowernumber = ba_i.borrowernumber AND ( ba_i.code = ? OR ba_i.code IS NULL ) + LEFT JOIN borrower_attributes ba_l ON borrowers.borrowernumber = ba_l.borrowernumber AND ( ba_l.code = ? OR ba_l.code IS NULL ) + LEFT JOIN borrower_attributes ba_p ON borrowers.borrowernumber = ba_p.borrowernumber AND ( ba_p.code = ? OR ba_p.code IS NULL ) + LEFT JOIN borrowers guarantor ON ( borrowers.guarantorid = guarantor.borrowernumber ) + WHERE + COALESCE( ba_c.attribute, 1 ) != '0' +}; + +push( @parameters, $can_collect_attribute_code ); +push( @parameters, $in_collections_attribute_code ); +push( @parameters, $last_updated_attribute_code ); +push( @parameters, $previous_balance_attribute_code ); + +$sql .= 'AND ' . join( ' AND ', @where ) if @where; + +if ( $report_type eq 'submission' ) +{ # Don't select patrons who have already been sent to collections for submissions report + $sql .= q{ AND COALESCE( ba_i.attribute, 0 ) != '1' }; +} +elsif ( $report_type eq 'update' ) +{ # Select only patrons who have already been sent to collections and have had a change in balance for update report + $sql .= q{ AND COALESCE( ba_i.attribute, 0 ) = '1' }; +} +elsif ( $report_type eq 'sync' ) +{ # Select only patrons who have already been sent to collections and have owe a balance for sync report + $sql .= q{ AND ba_i.attribute = '1' }; +} + +if (@exclude_patron_types) { + $sql .= ' AND borrowers.categorycode NOT IN ( ' + . join( ',', ('?') x @exclude_patron_types ) . ' ) '; + + push( @parameters, @exclude_patron_types ); +} + +if (@branchcodes) { + $sql .= ' AND borrowers.branchcode IN ( ' + . join( ',', ('?') x @branchcodes ) . ' ) '; + + push( @parameters, @branchcodes ); +} + +if (@exclude_fee_types) { + $sql .= ' AND account_debits.type NOT IN ( ' + . join( ',', ('?') x @exclude_fee_types ) . ' ) '; + + push( @parameters, @exclude_fee_types ); +} + +if ($begin_date) { + $sql .= ' AND DATE(account_debits.created_on) >= DATE(?) '; + push( @parameters, $begin_date ); +} + +if ($end_date) { + $sql .= ' AND DATE(account_debits.created_on) <= DATE(?) '; + push( @parameters, $end_date ); +} + +$sql .= q{ GROUP BY borrowernumber }; + +if ( $report_type eq 'submission' ) +{ # Don't select patrons who have already been sent to collections for submissions report + $sql .= ' HAVING SUM(account_debits.amount_outstanding) >= ? '; + push( @parameters, $minimum_balance ); +} +elsif ( $report_type eq 'sync' ) +{ # Select only patrons who have already been sent to collections and have owe a balance for sync report + $sql .= q{ HAVING SUM(account_debits.amount_outstanding) > 0 }; +} +elsif ( $report_type eq 'update' ) { + $sql .= + q{ HAVING computed_account_balance != previous_balance }; +} + +$sql .= q{ ORDER BY account_debits.created_on DESC }; + +my $sth = $dbh->prepare($sql); +$sth->execute(@parameters); + +$csv->print( + $fh, + [ + 'firstname', 'surname', + 'address1', 'address2', + 'city', 'state', + 'zipcode', 'phone', + 'borrowernumber', 'cardnumber', + 'date_of_birth', 'category', + 'account_balance', 'library', + 'most_recent_unpaid_fine_date', 'guarantor_firstname', + 'guarantor_surname', + ] +); + +while ( my $r = $sth->fetchrow_hashref() ) { + + $csv->print( + $fh, + [ + $r->{firstname}, $r->{surname}, + $r->{address1}, $r->{address2}, + $r->{city}, $r->{state}, + $r->{zipcode}, $r->{phone}, + $r->{borrowernumber}, $r->{cardnumber}, + $r->{dateofbirth}, $r->{categorycode}, + $r->{computed_account_balance}, $r->{branchcode}, + $r->{most_recent_unpaid_fine_date}, $r->{guarantor_firstname}, + $r->{guarantor_surname}, + ] + ); + + if ( $report_type eq 'submission' ) { + + # Set patron as being in collections + $dbh->do( $delete_attribute_sql, undef, + ( $r->{borrowernumber}, $in_collections_attribute_code ) ); + $dbh->do( $insert_attribute_sql, undef, + ( $r->{borrowernumber}, $in_collections_attribute_code, '1' ) ); + + if ($processing_fee) { + AddDebit( + { + borrower => + Koha::Database->new()->schema->resultset('Borrower') + ->find( $r->{borrowernumber} ), + amount => $processing_fee, + type => $processing_fee_type, + description => $processing_fee_description, + } + ); + } + + } + + if ( $report_type eq 'submission' || $report_type eq 'update' ) { + + # Store patron's current account balance + $dbh->do( $delete_attribute_sql, undef, + ( $r->{borrowernumber}, $previous_balance_attribute_code ) ); + $dbh->do( + $insert_attribute_sql, + undef, + ( + $r->{borrowernumber}, $previous_balance_attribute_code, + $r->{computed_account_balance} + ) + ); + + # Store today's date as the date last updated for collections + $dbh->do( $delete_attribute_sql, undef, + ( $r->{borrowernumber}, $last_updated_attribute_code ) ); + $dbh->do( $insert_attribute_sql, undef, + ( $r->{borrowernumber}, $last_updated_attribute_code, $ymd ) ); + } + + if ( $report_type eq 'update' ) { + + # If the patron is in collections, but now has a 0 balance + # set the patron to no longer being in collections + $dbh->do( $delete_attribute_sql, undef, + ( $r->{borrowernumber}, $in_collections_attribute_code ) ); + } +} + +close $fh or die "$file: $!"; -- 1.7.2.5