From e2a7dc3854ff99acd9567cb48647638414e3b280 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 14 Feb 2014 14:30:02 -0500 Subject: [PATCH] Bug 11703 [QA Followup 8] - Speed up api/checkouts.pl as much as possible --- api/checkouts.pl | 140 +++++++++++++++++++++++++++++++++--------------------- 1 files changed, 86 insertions(+), 54 deletions(-) diff --git a/api/checkouts.pl b/api/checkouts.pl index 4aebab1..6438953 100755 --- a/api/checkouts.pl +++ b/api/checkouts.pl @@ -19,18 +19,17 @@ # 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 strict; +use warnings; use CGI; use JSON qw(to_json); use C4::Auth qw(check_cookie_auth); use C4::Biblio qw(GetMarcBiblio GetFrameworkCode GetRecordValue ); -use C4::Charset; use C4::Circulation qw(GetIssuingCharges CanBookBeRenewed GetRenewCount); use C4::Context; -use Koha::Database; use Koha::DateUtils; my $input = new CGI; @@ -43,86 +42,119 @@ if ( $auth_status ne "ok" ) { exit 0; } -my $schema = Koha::Database->new()->schema(); - my @sort_columns = qw/date_due title itype issuedate branchcode itemcallnumber/; -my @borrowernumber = $input->param('borrowernumber'); -my $offset = $input->param('iDisplayStart'); -my $results_per_page = $input->param('iDisplayLength'); -my $sorting_column = $sort_columns[ $input->param('iSortCol_0') ] || 'issuedate'; -my $sorting_direction = $input->param('sSortDir_0') || 'desc'; +my @borrowernumber = $input->param('borrowernumber'); +my $offset = $input->param('iDisplayStart'); +my $results_per_page = $input->param('iDisplayLength') || -1; +my $sorting_column = $sort_columns[ $input->param('iSortCol_0') ] + || 'issuedate'; +my $sorting_direction = $input->param('sSortDir_0') eq 'asc' ? 'asc' : 'desc'; $results_per_page = undef if ( $results_per_page == -1 ); binmode STDOUT, ":encoding(UTF-8)"; print $input->header( -type => 'text/plain', -charset => 'UTF-8' ); -my $checkouts_rs = $schema->resultset('Issue')->search( - { borrowernumber => \@borrowernumber }, - { - prefetch => { 'item' => 'biblio' }, - order_by => { "-$sorting_direction" => $sorting_column } - } -); +my @parameters; +my $sql = ' + SELECT + issuedate, + date_due, + + biblionumber, + biblio.title, + author, + + itemnumber, + barcode, + itemnotes, + itemcallnumber, + replacementprice, + + issues.branchcode, + branchname, + + itype, + itemtype, + + borrowernumber, + surname, + firstname, + cardnumber + FROM issues + LEFT JOIN items USING ( itemnumber ) + LEFT JOIN biblio USING ( biblionumber ) + LEFT JOIN biblioitems USING ( biblionumber ) + LEFT JOIN borrowers USING ( borrowernumber ) + LEFT JOIN branches ON ( issues.branchcode = branches.branchcode ) + WHERE borrowernumber +'; + +if ( @borrowernumber == 1 ) { + $sql .= '= ?'; +} +else { + $sql = ' IN (' . join( ',', ('?') x @borrowernumber ) . ') '; +} +push( @parameters, @borrowernumber ); -my $borrower; -my @checkouts; -while ( my $c = $checkouts_rs->next() ) { +$sql .= " ORDER BY ? $sorting_direction "; +push( @parameters, $sorting_column ); - # No point in fetching this every time if only one borrower - $borrower = $c->borrower() - if ( !$borrower || @borrowernumber > 1 ); +my $dbh = C4::Context->dbh(); +my $sth = $dbh->prepare($sql); +$sth->execute( @parameters ); - my $borrowernumber = $borrower->borrowernumber(); - my $itemnumber = $c->item()->itemnumber(); - my $biblionumber = $c->item()->biblionumber(); +my $item_level_itypes = C4::Context->preference('item-level_itypes'); - my ($charge) = - GetIssuingCharges( $c->itemnumber()->itemnumber(), $borrowernumber ); +my @checkouts; +while ( my $c = $sth->fetchrow_hashref() ) { + my ($charge) = GetIssuingCharges( $c->{itemnumber}, $c->{borrowernumber} ); my ( $can_renew, $can_renew_error ) = - CanBookBeRenewed( $borrowernumber, $itemnumber ); + CanBookBeRenewed( $c->{borrowernumber}, $c->{itemnumber} ); my ( $renewals_count, $renewals_allowed, $renewals_remaining ) = - GetRenewCount( $borrowernumber, $itemnumber ); + GetRenewCount( $c->{borrowernumber}, $c->{itemnumber} ); push( @checkouts, { - DT_RowId => "$itemnumber-$borrowernumber", - title => $c->item()->biblio()->title(), - author => $c->item()->biblio()->author(), - barcode => $c->item()->barcode(), - itemtype => $c->item()->effective_itemtype(), - itemnotes => $c->item()->itemnotes(), - branchcode => $c->branchcode(), - branchname => $c->branch->branchname(), - itemcallnumber => $c->item()->itemcallnumber() || q{}, - charge => $charge, - price => $c->item->replacementprice() || q{}, - can_renew => $can_renew, + DT_RowId => $c->{itemnumber} . '-' . $c->{borrowernumber}, + title => $c->{title}, + author => $c->{author}, + barcode => $c->{barcode}, + itemtype => $item_level_itypes ? $c->{itype} : $c->{itemtype}, + itemnotes => $c->{itemnotes}, + branchcode => $c->{branchcode}, + branchname => $c->{branchname}, + itemcallnumber => $c->{itemcallnumber} || q{}, + charge => $charge, + price => $c->{replacementprice} || q{}, + can_renew => $can_renew, can_renew_error => $can_renew_error, - itemnumber => $itemnumber, - borrowernumber => $borrowernumber, - biblionumber => $biblionumber, - issuedate => $c->issuedate(), - date_due => $c->date_due(), + itemnumber => $c->{itemnumber}, + borrowernumber => $c->{borrowernumber}, + biblionumber => $c->{biblionumber}, + issuedate => $c->{issuedate}, + date_due => $c->{date_due}, renewals_count => $renewals_count, renewals_allowed => $renewals_allowed, renewals_remaining => $renewals_remaining, issuedate_formatted => - output_pref( dt_from_string( $c->issuedate() ) ), + output_pref( dt_from_string( $c->{issuedate} ) ), date_due_formatted => - output_pref_due( dt_from_string( $c->date_due() ) ), + output_pref_due( dt_from_string( $c->{date_due} ) ), subtitle => GetRecordValue( - 'subtitle', GetMarcBiblio($biblionumber), - GetFrameworkCode($biblionumber) + 'subtitle', + GetMarcBiblio( $c->{biblionumber} ), + GetFrameworkCode( $c->{biblionumber} ) ), borrower => { - surname => $borrower->surname(), - firstname => $borrower->firstname(), - cardnumber => $borrower->cardnumber(), + surname => $c->{surname}, + firstname => $c->{firstname}, + cardnumber => $c->{cardnumber}, } } ); -- 1.7.2.5