|
Lines 480-489
sub GetBudgetHierarchy {
Link Here
|
| 480 |
my @bind_params; |
480 |
my @bind_params; |
| 481 |
my $dbh = C4::Context->dbh; |
481 |
my $dbh = C4::Context->dbh; |
| 482 |
my $query = qq| |
482 |
my $query = qq| |
| 483 |
SELECT aqbudgets.*, aqbudgetperiods.budget_period_active, aqbudgetperiods.budget_period_description |
483 |
SELECT aqbudgets.*, aqbudgetperiods.budget_period_active, aqbudgetperiods.budget_period_description, |
|
|
484 |
b.firstname as budget_owner_firstname, b.surname as budget_owner_surname, b.borrowernumber as budget_owner_borrowernumber |
| 484 |
FROM aqbudgets |
485 |
FROM aqbudgets |
|
|
486 |
LEFT JOIN borrowers b on b.borrowernumber = aqbudgets.budget_owner_id |
| 485 |
JOIN aqbudgetperiods USING (budget_period_id)|; |
487 |
JOIN aqbudgetperiods USING (budget_period_id)|; |
| 486 |
|
488 |
|
| 487 |
my @where_strings; |
489 |
my @where_strings; |
| 488 |
# show only period X if requested |
490 |
# show only period X if requested |
| 489 |
if ($budget_period_id) { |
491 |
if ($budget_period_id) { |
|
Lines 541-552
sub GetBudgetHierarchy {
Link Here
|
| 541 |
_add_budget_children(\@sort, $first_parent, 0); |
543 |
_add_budget_children(\@sort, $first_parent, 0); |
| 542 |
} |
544 |
} |
| 543 |
|
545 |
|
|
|
546 |
# Get all the budgets totals in as few queries as possible |
| 547 |
my $hr_budget_spent = $dbh->selectall_hashref(qq| |
| 548 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
| 549 |
SUM( COALESCE(unitprice_tax_included, ecost_tax_included) * quantity ) AS budget_spent |
| 550 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
| 551 |
WHERE quantityreceived > 0 AND datecancellationprinted IS NULL |
| 552 |
GROUP BY budget_id |
| 553 |
|, 'budget_id'); |
| 554 |
my $hr_budget_ordered = $dbh->selectall_hashref(qq| |
| 555 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
| 556 |
SUM(ecost_tax_included * quantity) AS budget_ordered |
| 557 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
| 558 |
WHERE quantityreceived = 0 AND datecancellationprinted IS NULL |
| 559 |
GROUP BY budget_id |
| 560 |
|, 'budget_id'); |
| 561 |
my $hr_budget_spent_shipment = $dbh->selectall_hashref(qq| |
| 562 |
SELECT shipmentcost_budgetid as budget_id, |
| 563 |
SUM(shipmentcost) as shipmentcost |
| 564 |
FROM aqinvoices |
| 565 |
WHERE closedate IS NOT NULL |
| 566 |
GROUP BY shipmentcost_budgetid |
| 567 |
|, 'budget_id'); |
| 568 |
my $hr_budget_ordered_shipment = $dbh->selectall_hashref(qq| |
| 569 |
SELECT shipmentcost_budgetid as budget_id, |
| 570 |
SUM(shipmentcost) as shipmentcost |
| 571 |
FROM aqinvoices |
| 572 |
WHERE closedate IS NULL |
| 573 |
GROUP BY shipmentcost_budgetid |
| 574 |
|, 'budget_id'); |
| 575 |
|
| 576 |
my $recursiveAdd; |
| 577 |
$recursiveAdd = sub { |
| 578 |
my ($budget, $parent) = @_; |
| 579 |
|
| 580 |
foreach my $child (@{$budget->{children}}){ |
| 581 |
$recursiveAdd->($child, $budget); |
| 582 |
} |
| 583 |
|
| 584 |
$budget->{budget_spent} += $hr_budget_spent->{$budget->{budget_id}}->{budget_spent}; |
| 585 |
$budget->{budget_spent} += $hr_budget_spent_shipment->{$budget->{budget_id}}->{shipmentcost}; |
| 586 |
$budget->{budget_ordered} += $hr_budget_ordered->{$budget->{budget_id}}->{budget_ordered}; |
| 587 |
$budget->{budget_ordered} += $hr_budget_ordered_shipment->{$budget->{budget_id}}->{shipmentcost}; |
| 588 |
|
| 589 |
$budget->{total_spent} += $budget->{budget_spent}; |
| 590 |
$budget->{total_ordered} += $budget->{budget_ordered}; |
| 591 |
|
| 592 |
if ($parent) { |
| 593 |
$parent->{total_spent} += $budget->{total_spent}; |
| 594 |
$parent->{total_ordered} += $budget->{total_ordered}; |
| 595 |
} |
| 596 |
}; |
| 597 |
|
| 544 |
foreach my $budget (@sort) { |
598 |
foreach my $budget (@sort) { |
| 545 |
$budget->{budget_spent} = GetBudgetSpent( $budget->{budget_id} ); |
599 |
if ($budget->{budget_parent_id} == undef) { |
| 546 |
$budget->{budget_ordered} = GetBudgetOrdered( $budget->{budget_id} ); |
600 |
$recursiveAdd->($budget); |
| 547 |
$budget->{total_spent} = GetBudgetHierarchySpent( $budget->{budget_id} ); |
601 |
} |
| 548 |
$budget->{total_ordered} = GetBudgetHierarchyOrdered( $budget->{budget_id} ); |
|
|
| 549 |
} |
602 |
} |
|
|
603 |
|
| 550 |
return \@sort; |
604 |
return \@sort; |
| 551 |
} |
605 |
} |
| 552 |
|
606 |
|