@@ -, +, @@ 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 collections fee of 10.00 applied --- misc/cronjobs/account_collections.pl | 330 ++++++++++++++++++++++++++++++++++ 1 files changed, 330 insertions(+), 0 deletions(-) create mode 100755 misc/cronjobs/account_collections.pl --- a/misc/cronjobs/account_collections.pl +++ a/misc/cronjobs/account_collections.pl @@ -0,0 +1,330 @@ +#!/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 .= join( ' AND ', @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', + 'database_id', 'barcode', + '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->{database_id}, $r->{barcode}, + $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 ) ); + + } +} + +close $fh or die "$file: $!"; --