From 8dd6357dce0bf6701fe9287a7765f0eb2277e4de Mon Sep 17 00:00:00 2001 From: Johanna Raisa Date: Fri, 25 Mar 2022 12:25:56 +0200 Subject: [PATCH] Bug 30359: GetBudgetHierarchy is slow on order receive page MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds skiptotals parameter to GetBudgetHierarchy so calculating totals can be skipped from some pages. Test plan: 1) Open browser's Inspect -> Network 2) Go to receive orders 3) Check the timings for page load 4) Apply the patch 5) Refresh the page 6) Check the timings again 7) prove t/db_dependent/Budgets.t Sponsored-by: Koha-Suomi Oy Signed-off-by: Nick Clemens Signed-off-by: Joonas Kylmälä --- C4/Budgets.pm | 89 ++++++++++++++++++++++--------------------- acqui/orderreceive.pl | 2 +- 2 files changed, 46 insertions(+), 45 deletions(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 9624650be1..0837edf88c 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -485,7 +485,7 @@ sub ModBudgetPeriod { # ------------------------------------------------------------------- sub GetBudgetHierarchy { - my ( $budget_period_id, $branchcode, $owner ) = @_; + my ( $budget_period_id, $branchcode, $owner, $skiptotals ) = @_; my @bind_params; my $dbh = C4::Context->dbh; my $query = qq| @@ -550,49 +550,50 @@ sub GetBudgetHierarchy { foreach my $first_parent (@first_parents) { _add_budget_children(\@sort, $first_parent, 0); } - - # Get all the budgets totals in as few queries as possible - my $hr_budget_spent = $dbh->selectall_hashref(q| - SELECT aqorders.budget_id, aqbudgets.budget_parent_id, - SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent - FROM aqorders JOIN aqbudgets USING (budget_id) - WHERE quantityreceived > 0 AND datecancellationprinted IS NULL - GROUP BY budget_id, budget_parent_id - |, 'budget_id'); - my $hr_budget_ordered = $dbh->selectall_hashref(q| - SELECT aqorders.budget_id, aqbudgets.budget_parent_id, - SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered - FROM aqorders JOIN aqbudgets USING (budget_id) - WHERE quantityreceived = 0 AND datecancellationprinted IS NULL - GROUP BY budget_id, budget_parent_id - |, 'budget_id'); - my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| - SELECT shipmentcost_budgetid as budget_id, - SUM(shipmentcost) as shipmentcost - FROM aqinvoices - GROUP BY shipmentcost_budgetid - |, 'budget_id'); - my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| - SELECT budget_id, - SUM(adjustment) as adjustments - FROM aqinvoice_adjustments - JOIN aqinvoices USING (invoiceid) - WHERE closedate IS NOT NULL - GROUP BY budget_id - |, 'budget_id'); - my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| - SELECT budget_id, - SUM(adjustment) as adjustments - FROM aqinvoice_adjustments - JOIN aqinvoices USING (invoiceid) - WHERE closedate IS NULL AND encumber_open = 1 - GROUP BY budget_id - |, 'budget_id'); - - - foreach my $budget (@sort) { - if ( not defined $budget->{budget_parent_id} ) { - _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); + if (!$skiptotals) { + # Get all the budgets totals in as few queries as possible + my $hr_budget_spent = $dbh->selectall_hashref(q| + SELECT aqorders.budget_id, aqbudgets.budget_parent_id, + SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent + FROM aqorders JOIN aqbudgets USING (budget_id) + WHERE quantityreceived > 0 AND datecancellationprinted IS NULL + GROUP BY budget_id, budget_parent_id + |, 'budget_id'); + my $hr_budget_ordered = $dbh->selectall_hashref(q| + SELECT aqorders.budget_id, aqbudgets.budget_parent_id, + SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered + FROM aqorders JOIN aqbudgets USING (budget_id) + WHERE quantityreceived = 0 AND datecancellationprinted IS NULL + GROUP BY budget_id, budget_parent_id + |, 'budget_id'); + my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| + SELECT shipmentcost_budgetid as budget_id, + SUM(shipmentcost) as shipmentcost + FROM aqinvoices + GROUP BY shipmentcost_budgetid + |, 'budget_id'); + my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| + SELECT budget_id, + SUM(adjustment) as adjustments + FROM aqinvoice_adjustments + JOIN aqinvoices USING (invoiceid) + WHERE closedate IS NOT NULL + GROUP BY budget_id + |, 'budget_id'); + my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| + SELECT budget_id, + SUM(adjustment) as adjustments + FROM aqinvoice_adjustments + JOIN aqinvoices USING (invoiceid) + WHERE closedate IS NULL AND encumber_open = 1 + GROUP BY budget_id + |, 'budget_id'); + + + foreach my $budget (@sort) { + if ( not defined $budget->{budget_parent_id} ) { + _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); + } } } return \@sort; diff --git a/acqui/orderreceive.pl b/acqui/orderreceive.pl index f380a675e5..c690dca55f 100755 --- a/acqui/orderreceive.pl +++ b/acqui/orderreceive.pl @@ -177,7 +177,7 @@ foreach my $period (@$periods) { $template->{'VARS'}->{'budget_period_description'} = $period->{'budget_period_description'}; } next if $period->{'budget_period_locked'} || !$period->{'budget_period_description'}; - my $budget_hierarchy = GetBudgetHierarchy( $period->{'budget_period_id'} ); + my $budget_hierarchy = GetBudgetHierarchy( $period->{'budget_period_id'}, undef, undef, 1 ); my @funds; foreach my $r ( @{$budget_hierarchy} ) { next unless ( CanUserUseBudget( $patron, $r, $userflags ) ); -- 2.30.2