GetAllIssues

=head2 GetAllIssues

  $issues = &GetAllIssues($borrowernumber, $sortkey, $limit);

Looks up what the patron with the given borrowernumber has borrowed,

and sorts the results.

C<$sortkey> is the name of a field on which to sort the results. This

should be the name of a field in the C<issues>, C<biblio>,

C<biblioitems>, or C<items> table in the Koha database.

C<$limit> is the maximum number of results to return.

C<&GetAllIssues> an arrayref, C<$issues>, of hashrefs, the keys of which

are the fields from the C<issues>, C<biblio>, C<biblioitems>, and

C<items> tables of the Koha database.

=cut

#'

sub GetAllIssues {

    my ( $borrowernumber, $order, $limit ) = @_;

    return unless $borrowernumber;

    $order = 'date_due desc' unless $order;

    my $dbh = C4::Context->dbh;

    my $query =

'SELECT issues.*, items.*, biblio.*, biblioitems.*, issues.timestamp as issuestimestamp, issues.renewals_count AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp,borrowers.firstname,borrowers.surname

  FROM issues

  LEFT JOIN items on items.itemnumber=issues.itemnumber

  LEFT JOIN borrowers on borrowers.borrowernumber=issues.issuer_id

  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber

  LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber

  WHERE issues.borrowernumber=?

  UNION ALL

  SELECT old_issues.*, items.*, biblio.*, biblioitems.*, old_issues.timestamp as issuestimestamp, old_issues.renewals_count AS renewals,items.renewals AS totalrenewals,items.timestamp AS itemstimestamp,borrowers.firstname,borrowers.surname

  FROM old_issues

  LEFT JOIN items on items.itemnumber=old_issues.itemnumber

  LEFT JOIN borrowers on borrowers.borrowernumber=old_issues.issuer_id

  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber

  LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber

  WHERE old_issues.borrowernumber=? AND old_issues.itemnumber IS NOT NULL

  order by ' . $order;

    if ($limit) {

        $query .= " limit $limit";

    }

    my $sth = $dbh->prepare($query);

    $sth->execute( $borrowernumber, $borrowernumber );

    return $sth->fetchall_arrayref( {} );

}

#!/usr/bin/perl

# 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 tests => 16;

use Test::MockModule;

use t::lib::TestBuilder;

use C4::Circulation qw( AddIssue );

use C4::Biblio qw( AddBiblio );

use C4::Items;

use C4::Members qw( GetAllIssues );

use Koha::Libraries;

use Koha::Patrons;

use MARC::Record;

my $schema = Koha::Database->schema;

my $dbh = C4::Context->dbh;

$schema->storage->txn_begin;

my $builder = t::lib::TestBuilder->new;

$dbh->do(q|DELETE FROM issues|);

$dbh->do(q|DELETE FROM borrowers|);

$dbh->do(q|DELETE FROM items|);

$dbh->do(q|DELETE FROM branches|);

$dbh->do(q|DELETE FROM biblio|);

$dbh->do(q|DELETE FROM categories|);

my $branchcode   = $builder->build( { source => 'Branch' } )->{branchcode};

my $categorycode = $builder->build( { source => 'Category' } )->{categorycode};

my $itemtype     = $builder->build( { source => 'Itemtype' } )->{itemtype};

my %item_infos = (

    homebranch    => $branchcode,

    holdingbranch => $branchcode,

    itype         => $itemtype

);

my ($biblionumber1) = AddBiblio( MARC::Record->new, '' );

# FIXME These tests will fail if the barcode exists in DB

my $itemnumber1 = $builder->build_sample_item({ biblionumber => $biblionumber1, barcode => '0101', %item_infos })->itemnumber;

my $itemnumber2 = $builder->build_sample_item({ biblionumber => $biblionumber1, barcode => '0102', %item_infos })->itemnumber;

my ($biblionumber2) = AddBiblio( MARC::Record->new, '' );

my $itemnumber3 = $builder->build_sample_item({ biblionumber => $biblionumber2, barcode => '0202', %item_infos })->itemnumber;

my $borrowernumber1 =

  Koha::Patron->new({ categorycode => $categorycode, branchcode => $branchcode })->store->borrowernumber;

my $borrowernumber2 =

  Koha::Patron->new({ categorycode => $categorycode, branchcode => $branchcode })->store->borrowernumber;

my $borrower1 = Koha::Patrons->find( $borrowernumber1 );

my $borrower2 = Koha::Patrons->find( $borrowernumber2 );

my $module = Test::MockModule->new('C4::Context');

$module->mock( 'userenv', sub { { branch => $branchcode } } );

my $issues = C4::Members::GetAllIssues();

is( $issues, undef, 'GetAllIssues without borrower number returns undef' );

$issues = C4::Members::GetAllIssues($borrowernumber1);

is( @$issues, 0, 'GetAllIssues returns the correct number of elements' );

$issues = C4::Members::GetAllIssues($borrowernumber2);

is( @$issues, 0, 'GetAllIssues returns the correct number of elements' );

AddIssue( $borrower1, '0101' );

$issues = C4::Members::GetAllIssues($borrowernumber1);

my $issues_with_order =

  C4::Members::GetAllIssues( $borrowernumber1, 'date_due desc' );

is_deeply( $issues, $issues_with_order,

'The value by default for the argument order in GellAllIssues is date_due_desc'

);

is( @$issues, 1, 'GetAllIssues returns the correct number of elements' );

is( $issues->[0]->{itemnumber}, $itemnumber1, '' );

$issues = C4::Members::GetAllIssues($borrowernumber2);

is( @$issues, 0, 'GetAllIssues returns the correct number of elements' );

AddIssue( $borrower1, '0102' );

$issues = C4::Members::GetAllIssues($borrowernumber1);

is( @$issues, 2, 'GetAllIssues returns the correct number of elements' );

is( $issues->[0]->{itemnumber}, $itemnumber1, '' );

is( $issues->[1]->{itemnumber}, $itemnumber2, '' );

$issues = C4::Members::GetAllIssues($borrowernumber2);

is( @$issues, 0, 'GetAllIssues returns the correct number of elements' );

AddIssue( $borrower2, '0202' );

$issues = C4::Members::GetAllIssues($borrowernumber1);

is( @$issues, 2, 'GetAllIssues returns the correct number of elements' );

is( $issues->[0]->{itemnumber}, $itemnumber1, '' );

is( $issues->[1]->{itemnumber}, $itemnumber2, '' );

$issues = C4::Members::GetAllIssues($borrowernumber2);

is( @$issues, 1, 'GetAllIssues returns the correct number of elements' );

is( $issues->[0]->{itemnumber}, $itemnumber3, '' );

$schema->storage->txn_begin;

-