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

(-)a/Koha/Patrons.pm (+79 lines)
Lines 480-485 sub filter_by_attribute_value { Link Here
480
    return Koha::Patrons->_new_from_dbic($rs);
480
    return Koha::Patrons->_new_from_dbic($rs);
481
}
481
}
482
482
483
=head3 filter_by_amount_owed
484
485
    Koha::Patrons->filter_by_amount_owed(
486
        {
487
            less_than  => '2.00',
488
            more_than  => '0.50',
489
            debit_type => $debit_type_code,
490
            library    => $branchcode
491
        }
492
    );
493
494
Returns patrons filtered by how much money they owe, between passed limits.
495
496
Optionally limit to debts of a particular debit_type or/and owed to a particular library.
497
498
=head4 arguments hashref
499
500
=over 4
501
502
=item less_than (optional)  - filter out patrons who owe less than Amount
503
504
=item more_than (optional)  - filter out patrons who owe more than Amount
505
506
=item debit_type (optional) - filter the amount owed by debit type
507
508
=item library (optional)    - filter the amount owed to a particular branch
509
510
=back
511
512
=cut
513
514
sub filter_by_amount_owed {
515
    my ( $self, $options ) = @_;
516
517
    return $self
518
      unless (
519
        defined($options)
520
        && (   defined( $options->{less_than} )
521
            || defined( $options->{more_than} ) )
522
      );
523
524
    my $where = {};
525
    my $group_by =
526
      [ map { 'me.' . $_ } $self->_resultset->result_source->columns ];
527
528
    my $attrs = {
529
        join     => 'accountlines',
530
        group_by => $group_by,
531
        '+select' =>
532
          { sum => 'accountlines.amountoutstanding', '-as' => 'outstanding' },
533
        '+as' => 'outstanding'
534
    };
535
536
    $where->{'accountlines.debit_type_code'} = $options->{debit_type}
537
      if defined( $options->{debit_type} );
538
539
    $where->{'accountlines.branchcode'} = $options->{library}
540
      if defined( $options->{library} );
541
542
    $attrs->{'having'} = [
543
        { 'outstanding' => { '<' => $options->{less_than} } },
544
        { 'outstanding' => undef }
545
      ]
546
      if ( defined( $options->{less_than} )
547
        && !defined( $options->{more_than} ) );
548
549
    $attrs->{'having'} = { 'outstanding' => { '>' => $options->{more_than} } }
550
      if (!defined( $options->{less_than} )
551
        && defined( $options->{more_than} ) );
552
553
    $attrs->{'having'}->{'-and'} = [
554
        { 'outstanding' => { '>' => $options->{more_than} } },
555
        { 'outstanding' => { '<' => $options->{less_than} } }
556
      ]
557
      if ( defined( $options->{less_than} )
558
        && defined( $options->{more_than} ) );
559
560
    return $self->search( $where, $attrs );
561
}
483
562
484
=head3 _type
563
=head3 _type
485
564
(-)a/t/db_dependent/Koha/Patrons.t (-2 / +145 lines)
Lines 19-25 Link Here
19
19
20
use Modern::Perl;
20
use Modern::Perl;
21
21
22
use Test::More tests => 42;
22
use Test::More tests => 43;
23
use Test::Warn;
23
use Test::Warn;
24
use Test::Exception;
24
use Test::Exception;
25
use Test::MockModule;
25
use Test::MockModule;
Lines 2184-2187 subtest 'queue_notice' => sub { Link Here
2184
    is( Koha::Notice::Messages->search({borrowernumber => $patron->borrowernumber })->count, $counter,"Count of queued notices not increased in test mode");
2184
    is( Koha::Notice::Messages->search({borrowernumber => $patron->borrowernumber })->count, $counter,"Count of queued notices not increased in test mode");
2185
};
2185
};
2186
2186
2187
subtest 'filter_by_amount_owed' => sub {
2188
    plan tests => 6;
2189
2190
    my $library = $builder->build({source => 'Branch' });
2191
    my $category = $builder->build({source => 'Category' });
2192
2193
    my $new_patron_cf_1 = Koha::Patron->new(
2194
        {
2195
            cardnumber   => 'test_cn_cf_1',
2196
            branchcode   => $library->{branchcode},
2197
            categorycode => $category->{categorycode},
2198
            surname      => 'surname for patron1',
2199
            firstname    => 'firstname for patron1',
2200
            userid       => 'a_nonexistent_userid_cf_1',
2201
        }
2202
    )->store;
2203
    my $new_patron_cf_2 = Koha::Patron->new(
2204
        {
2205
            cardnumber   => 'test_cn_cf_2',
2206
            branchcode   => $library->{branchcode},
2207
            categorycode => $category->{categorycode},
2208
            surname      => 'surname for patron2',
2209
            firstname    => 'firstname for patron2',
2210
            userid       => 'a_nonexistent_userid_cf_2',
2211
        }
2212
    )->store;
2213
    my $new_patron_cf_3 = Koha::Patron->new(
2214
        {
2215
            cardnumber   => 'test_cn_cf_3',
2216
            branchcode   => $library->{branchcode},
2217
            categorycode => $category->{categorycode},
2218
            surname      => 'surname for patron3',
2219
            firstname    => 'firstname for patron3',
2220
            userid       => 'a_nonexistent_userid_cf_3',
2221
        }
2222
    )->store;
2223
2224
    my $results = Koha::Patrons->search(
2225
        {
2226
            'me.borrowernumber' => [
2227
                $new_patron_cf_1->borrowernumber,
2228
                $new_patron_cf_2->borrowernumber,
2229
                $new_patron_cf_3->borrowernumber
2230
            ]
2231
        }
2232
    );
2233
2234
    my $fine1 = $builder->build(
2235
        {
2236
            source => 'Accountline',
2237
            value  => {
2238
                borrowernumber    => $new_patron_cf_1->borrowernumber,
2239
                amountoutstanding => 12.00,
2240
                amount            => 12.00,
2241
                debit_type_code   => 'OVERDUE',
2242
                branchcode        => $library->{branchcode}
2243
            },
2244
        }
2245
    );
2246
    my $fine2 = $builder->build(
2247
        {
2248
            source => 'Accountline',
2249
            value  => {
2250
                borrowernumber    => $new_patron_cf_2->borrowernumber,
2251
                amountoutstanding => 8.00,
2252
                amount            => 8.00,
2253
                debit_type_code   => 'OVERDUE',
2254
                branchcode        => $library->{branchcode}
2255
2256
            },
2257
        }
2258
    );
2259
    my $fine3 = $builder->build(
2260
        {
2261
            source => 'Accountline',
2262
            value  => {
2263
                borrowernumber    => $new_patron_cf_2->borrowernumber,
2264
                amountoutstanding => 10.00,
2265
                amount            => 10.00,
2266
                debit_type_code   => 'OVERDUE',
2267
                branchcode        => $library->{branchcode}
2268
            },
2269
        }
2270
    );
2271
2272
    my $filtered = $results->filter_by_amount_owed();
2273
    is( ref($filtered), 'Koha::Patrons',
2274
'Koha::Patrons->filter_by_amount_owed should return a Koha::Patrons result set in a scalar context'
2275
    );
2276
2277
    my $lower_limit = 12.00;
2278
    my $upper_limit = 16.00;
2279
2280
    # Catch user with 1 x 12.00 fine and user with no fines.
2281
    $filtered =
2282
      $results->filter_by_amount_owed( { less_than => $upper_limit } );
2283
    is( $filtered->_resultset->as_subselect_rs->count, 2,
2284
"filter_by_amount_owed({ less_than => $upper_limit }) found two patrons"
2285
    );
2286
2287
    # Catch user with 1 x 8.00 and 1 x 10.00 fine
2288
    $filtered =
2289
      $results->filter_by_amount_owed( { more_than => $lower_limit } );
2290
    is( $filtered->_resultset->as_subselect_rs->count, 1,
2291
"filter_by_amount_owed({ more_than => $lower_limit }) found two patrons"
2292
    );
2293
2294
    # User with 2 fines falls above upper limit - Excluded,
2295
    # user with 1 fine falls below lower limit - Excluded
2296
    # and user with no fines falls below lower limit - Excluded.
2297
    $filtered = $results->filter_by_amount_owed(
2298
        { more_than => $lower_limit, less_than => $upper_limit } );
2299
    is( $filtered->_resultset->as_subselect_rs->count, 0,
2300
"filter_by_amount_owed({ more_than => $lower_limit, less_than => $upper_limit }) found zero patrons"
2301
    );
2302
2303
    my $library2 = $builder->build({source => 'Branch' });
2304
    my $fine4 = $builder->build(
2305
        {
2306
            source => 'Accountline',
2307
            value  => {
2308
                borrowernumber    => $new_patron_cf_2->borrowernumber,
2309
                amountoutstanding => 10.00,
2310
                amount            => 10.00,
2311
                debit_type_code   => 'HOLD',
2312
                branchcode        => $library2->{branchcode}
2313
            },
2314
        }
2315
    );
2316
2317
    # Catch only the user with a HOLD fee over 6.00
2318
    $filtered = $results->filter_by_amount_owed( { more_than => 6.00, debit_type => 'HOLD' } );
2319
    is( $filtered->_resultset->as_subselect_rs->count, 1,
2320
"filter_by_amount_owed({ more_than => 6.00, debit_type => 'HOLD' }) found one patron"
2321
    );
2322
2323
    # Catch only the user with a fee over 6.00 at the specified library
2324
    $filtered = $results->filter_by_amount_owed( { more_than => 6.00, library => $library2->{branchcode} } );
2325
    is( $filtered->_resultset->as_subselect_rs->count, 1,
2326
"filter_by_amount_owed({ more_than => 6.00, library => $library2->{branchcode} }) found one patron"
2327
    );
2328
2329
};
2330
2187
$schema->storage->txn_rollback;
2331
$schema->storage->txn_rollback;
2188
- 

Return to bug 15156