Attachment 25996 Details for Bug 11703 – Bug 11703 [QA Followup 8] - Speed up api/checkouts.pl as much as possible

[patch] Bug 11703 [QA Followup 8] - Speed up api/checkouts.pl as much as possible

Bug-11703-QA-Followup-8---Speed-up-apicheckoutspl-.patch (text/plain), 7.46 KB, created by Kyle M Hall (khall) on 2014-03-10 12:26:42 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2014-03-10 12:26:42 UTC

Size: 7.46 KB

patch

obsolete

>From e2a7dc3854ff99acd9567cb48647638414e3b280 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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

Actions: View | Diff | Splinter Review

Attachments on bug 11703: 25105 | 25110 | 25111 | 25213 | 25217 | 25218 | 25220 | 25221 | 25261 | 25262 | 25263 | 25264 | 25271 | 25272 | 25289 | 25290 | 25292 | 25390 | 25391 | 25986 | 25987 | 25988 | 25989 | 25990 | 25991 | 25992 | 25993 | 25994 | 25995 | 25996 | 25997 | 25998 | 25999 | 26081 | 26085 | 26115 | 26131 | 26158 | 26160 | 26241 | 26986 | 26990 | 26991 | 27102 | 27241 | 27338 | 27339 | 27340 | 27341 | 27342 | 27349 | 27350 | 27353 | 27354 | 27356 | 27548 | 27610 | 27717 | 27718 | 27745 | 27746 | 27750 | 27751 | 28025 | 28026 | 28027 | 28028 | 28029 | 29195 | 29196 | 29197 | 29198 | 29199 | 29279 | 29280 | 29281 | 29282 | 29283 | 29284 | 29419 | 29420 | 29432 | 29433 | 29434 | 29438 | 29439 | 29440 | 29444 | 29445 | 29446 | 29447 | 29448 | 29449 | 29450 | 29451 | 29452 | 29453 | 29454 | 29472 | 29473 | 29474 | 29475 | 29476 | 29477 | 29478 | 29479 | 29480 | 29493 | 30228