From 5226371ea3e438c2ecf43ef18cbac090fe17c373 Mon Sep 17 00:00:00 2001 From: Colin Campbell Date: Thu, 8 Apr 2010 17:58:20 +0100 Subject: [PATCH] Remove bottleneck making claims page unuseable While the idea of showing the number of late serials against the vendor name was nice it does not scale and on large sites selecting claims was just timing out. Improved the speed of the initial query but have removed the big query for each user just to get a count. Check for 0000-00-00 dates so that C4::Dates does not log error Removed a variable that was never set and the bit of template used if the impossible happened --- C4/Serials.pm | 22 ++++++------ .../prog/en/modules/serials/claims.tmpl | 4 --- serials/claims.pl | 36 ++++++++----------- 3 files changed, 24 insertions(+), 38 deletions(-) diff --git a/C4/Serials.pm b/C4/Serials.pm index 7b42465f38..a50d9038d1 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -91,15 +91,12 @@ the array is in name order sub GetSuppliersWithLateIssues { my $dbh = C4::Context->dbh; - my $query = qq| - SELECT DISTINCT id, name - FROM subscription - LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND (planneddate < now() OR serial.STATUS = 3 OR serial.STATUS = 4) - ORDER BY name - |; + my $query = q| + SELECT DISTINCT aqbooksellerid as id, aqbooksellers.name as name + FROM subscription + LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid + LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id + WHERE (planneddate < now() OR serial.STATUS = 3 OR serial.STATUS = 4) ORDER BY name|; return $dbh->selectall_arrayref($query, { Slice => {} }); } @@ -1708,7 +1705,7 @@ sub DelIssue { =head2 GetLateOrMissingIssues -@issuelist = &GetLateMissingIssues($supplierid,$serialid) +@issuelist = GetLateMissingIssues($supplierid,$serialid) this function selects missing issues on database - where serial.status = 4 or serial.status=3 or planneddateexecute; my @issuelist; while ( my $line = $sth->fetchrow_hashref ) { - if ($line->{planneddate}) { + + if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) { $line->{planneddate} = format_date( $line->{planneddate} ); } - if ($line->{claimdate}) { + if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) { $line->{claimdate} = format_date( $line->{claimdate} ); } $line->{"status".$line->{status}} = 1; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tmpl index 1e5c8b0fb9..d678022486 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tmpl @@ -292,10 +292,6 @@

- - " /> - &op=preview" onclick="popup(,''); return false" class="button">Vendor Group Claim Notice

--> -
diff --git a/serials/claims.pl b/serials/claims.pl index 5b73d8bd3a..e39e350e9d 100755 --- a/serials/claims.pl +++ b/serials/claims.pl @@ -10,7 +10,7 @@ use C4::Output; use C4::Bookseller; use C4::Context; use C4::Letters; -my $input = new CGI; +my $input = CGI->new; my $serialid = $input->param('serialid'); my $op = $input->param('op'); @@ -18,26 +18,26 @@ my $claimletter = $input->param('claimletter'); my $supplierid = $input->param('supplierid'); my $suppliername = $input->param('suppliername'); my $order = $input->param('order'); -my $supplierlist = GetSuppliersWithLateIssues; +my $supplierlist = GetSuppliersWithLateIssues(); +if ($supplierid) { + foreach my $s ( @{$supplierlist} ) { + if ($s->{id} == $supplierid ) { + $s->{selected} = 1; + last; + } + } +} # open template first (security & userenv set here) my ($template, $loggedinuser, $cookie) -= get_template_and_user({template_name => "serials/claims.tmpl", += get_template_and_user({template_name => 'serials/claims.tmpl', query => $input, - type => "intranet", + type => 'intranet', authnotrequired => 0, flagsrequired => {serials => 1}, debug => 1, }); -for my $supplier ( @{$supplierlist} ) { - my @dummy = GetLateOrMissingIssues($supplier->{id},q{},$order); - my $counting = scalar @dummy; - $supplier->{name} .= " ($counting)"; - if ($supplierid && $supplierid == $supplier->{id}) { - $supplier->{selected} = 1; - } -} my $letters = GetLetters('claimissues'); my @letters; @@ -47,17 +47,10 @@ foreach (keys %{$letters}){ my $letter=((scalar(@letters)>1) || ($letters[0]->{name}||$letters[0]->{code})); my @missingissues; +my @supplierinfo; if ($supplierid) { @missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order); -} - -my ($singlesupplier,@supplierinfo); -if($supplierid){ - (@supplierinfo)=GetBookSeller($supplierid); -} else { # set up supplierid for the claim links out of main table if all suppliers is chosen - for my $mi (@missingissues){ - $mi->{supplierid} = getsupplierbyserialid($mi->{serialid}); - } + @supplierinfo=GetBookSeller($supplierid); } my $preview=0; @@ -82,7 +75,6 @@ $template->param( missingissues => \@missingissues, supplierid => $supplierid, claimletter => $claimletter, - singlesupplier => $singlesupplier, supplierloop => \@supplierinfo, dateformat => C4::Context->preference("dateformat"), DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), -- 2.39.5