From 0062b4f08e932abb795bea2efe12ec7b10e9b4ef Mon Sep 17 00:00:00 2001
From: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi>
Date: Mon, 9 Nov 2015 12:57:52 +0200
Subject: [PATCH] Bug 15156 - Get all Borrowers with pending/unpaid
 fines/accountlines

A new API call.
Useful when doing group operations for Borrowers with fines.
Includes some test-coverage for C4::Accountlines since it was missing any.
---
 C4/Accounts.pm         | 23 ++++++++++++
 t/db_dependent/Fines.t | 99 ++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 122 insertions(+)
 create mode 100644 t/db_dependent/Fines.t

diff --git a/C4/Accounts.pm b/C4/Accounts.pm
index 7fc4ff8..29e07f9 100644
--- a/C4/Accounts.pm
+++ b/C4/Accounts.pm
@@ -669,6 +669,29 @@ sub recordpayment_selectaccts {
     return;
 }
 
+=head GetAllBorrowersWithUnpaidFines
+
+    my $badBorrowers = C4::Accounts::GetAllBorrowersWithUnpaidFines();
+
+@RETURNS ARRAYRef of HASHRefs of koha.borrowers + amountoutstanding-key
+            containing the total amount outstanding.
+            Ordered by borrowernumber ASCending.
+=cut
+
+sub GetAllBorrowersWithUnpaidFines {
+    my $sth = C4::Context->dbh->prepare("
+        SELECT b.*, SUM(a.amountoutstanding) AS amountoutstanding
+        FROM borrowers b
+            LEFT JOIN accountlines a ON b.borrowernumber = a.borrowernumber
+        WHERE a.amountoutstanding > 0
+        GROUP BY b.borrowernumber
+        ORDER BY b.borrowernumber ASC
+        "
+    );
+    $sth->execute();
+    return $sth->fetchall_arrayref({});
+}
+
 # makepayment needs to be fixed to handle partials till then this separate subroutine
 # fills in
 sub makepartialpayment {
diff --git a/t/db_dependent/Fines.t b/t/db_dependent/Fines.t
new file mode 100644
index 0000000..19ee7be
--- /dev/null
+++ b/t/db_dependent/Fines.t
@@ -0,0 +1,99 @@
+#!/usr/bin/perl
+
+# Copyright 2015 Vaara-kirjastot
+#
+# 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 3 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, see <http://www.gnu.org/licenses>.
+
+use Modern::Perl;
+use Test::More;
+use Try::Tiny;
+use Scalar::Util qw(blessed);
+
+use C4::Accounts;
+
+use t::lib::TestObjects::ObjectFactory;
+use t::lib::TestObjects::BorrowerFactory;
+
+##Setting up the test context
+my $testContext = {};
+
+my $password = '1234';
+my $borrowerFactory = t::lib::TestObjects::BorrowerFactory->new();
+my $borrowers = $borrowerFactory->createTestGroup([
+            {firstname  => 'Olli-Antti',
+             surname    => 'Kivi',
+             cardnumber => '1A01',
+             branchcode => 'CPL',
+             password   => $password,
+            },
+            {firstname  => 'Alli-Ontti',
+             surname    => 'Ivik',
+             cardnumber => '1A02',
+             branchcode => 'CPL',
+             password   => $password,
+            },
+            {firstname  => 'NoFines',
+             surname    => 'Angel',
+             cardnumber => '1A03',
+             branchcode => 'CPL',
+             password   => $password,
+            },
+        ], undef, $testContext);
+
+##Test context set, starting testing:
+subtest "Get all Borrowers with Fines" => \&getAllBorrowersWithFines;
+sub getAllBorrowersWithFines {
+    eval { #run in a eval-block so we don't die without tearing down the test context
+    my $borrowerKivi = $borrowers->{'1A01'};
+    my $borrowerIvik = $borrowers->{'1A02'};
+
+    C4::Accounts::manualinvoice($borrowerKivi->borrowernumber, undef, 'TESTIS1', 'F', 1.123456, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerKivi->borrowernumber, undef, 'TESTIS2', 'F', 2, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerKivi->borrowernumber, undef, 'TESTIS3', 'F', 3, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerIvik->borrowernumber, undef, 'TESTIS1', 'F', 1.123456, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerIvik->borrowernumber, undef, 'TESTIS2', 'F', 2, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerIvik->borrowernumber, undef, 'TESTIS3', 'F', 3, 'NOTED');
+    C4::Accounts::manualinvoice($borrowerIvik->borrowernumber, undef, 'TESTIS4', 'F', 4, 'NOTED');
+
+    my $badBorrowers = C4::Accounts::GetAllBorrowersWithUnpaidFines();
+    is($borrowerKivi->cardnumber,
+       $badBorrowers->[0]->{cardnumber},
+       "Got the correct bad Borrower '".$borrowerKivi->cardnumber."'");
+    is($badBorrowers->[0]->{amountoutstanding},
+       6.123456,
+       "Got the correct unpaid fines '6'");
+    is($borrowerIvik->cardnumber,
+       $badBorrowers->[1]->{cardnumber},
+       "Got the correct bad Borrower '".$borrowerIvik->cardnumber."'");
+    is($badBorrowers->[1]->{amountoutstanding},
+       10.123456,
+       "Got the correct unpaid fines '10'");
+    ok(not(defined($badBorrowers->[2])),
+       "Good Borrower not on the bad Borrowers list");
+
+    };
+    if ($@) { #Catch all leaking errors and gracefully terminate.
+        ok(0, $@);
+    }
+}
+
+##All tests done, tear down test context
+tearDown();
+done_testing;
+
+sub tearDown {
+    t::lib::TestObjects::ObjectFactory->tearDownTestContext($testContext);
+}
\ No newline at end of file
-- 
1.9.1