Lines 485-491
sub ModBudgetPeriod {
Link Here
|
485 |
|
485 |
|
486 |
# ------------------------------------------------------------------- |
486 |
# ------------------------------------------------------------------- |
487 |
sub GetBudgetHierarchy { |
487 |
sub GetBudgetHierarchy { |
488 |
my ( $budget_period_id, $branchcode, $owner ) = @_; |
488 |
my ( $budget_period_id, $branchcode, $owner, $skiptotals ) = @_; |
489 |
my @bind_params; |
489 |
my @bind_params; |
490 |
my $dbh = C4::Context->dbh; |
490 |
my $dbh = C4::Context->dbh; |
491 |
my $query = qq| |
491 |
my $query = qq| |
Lines 550-598
sub GetBudgetHierarchy {
Link Here
|
550 |
foreach my $first_parent (@first_parents) { |
550 |
foreach my $first_parent (@first_parents) { |
551 |
_add_budget_children(\@sort, $first_parent, 0); |
551 |
_add_budget_children(\@sort, $first_parent, 0); |
552 |
} |
552 |
} |
553 |
|
553 |
if (!$skiptotals) { |
554 |
# Get all the budgets totals in as few queries as possible |
554 |
# Get all the budgets totals in as few queries as possible |
555 |
my $hr_budget_spent = $dbh->selectall_hashref(q| |
555 |
my $hr_budget_spent = $dbh->selectall_hashref(q| |
556 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
556 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
557 |
SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent |
557 |
SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent |
558 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
558 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
559 |
WHERE quantityreceived > 0 AND datecancellationprinted IS NULL |
559 |
WHERE quantityreceived > 0 AND datecancellationprinted IS NULL |
560 |
GROUP BY budget_id, budget_parent_id |
560 |
GROUP BY budget_id, budget_parent_id |
561 |
|, 'budget_id'); |
561 |
|, 'budget_id'); |
562 |
my $hr_budget_ordered = $dbh->selectall_hashref(q| |
562 |
my $hr_budget_ordered = $dbh->selectall_hashref(q| |
563 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
563 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
564 |
SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered |
564 |
SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered |
565 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
565 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
566 |
WHERE quantityreceived = 0 AND datecancellationprinted IS NULL |
566 |
WHERE quantityreceived = 0 AND datecancellationprinted IS NULL |
567 |
GROUP BY budget_id, budget_parent_id |
567 |
GROUP BY budget_id, budget_parent_id |
568 |
|, 'budget_id'); |
568 |
|, 'budget_id'); |
569 |
my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| |
569 |
my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| |
570 |
SELECT shipmentcost_budgetid as budget_id, |
570 |
SELECT shipmentcost_budgetid as budget_id, |
571 |
SUM(shipmentcost) as shipmentcost |
571 |
SUM(shipmentcost) as shipmentcost |
572 |
FROM aqinvoices |
572 |
FROM aqinvoices |
573 |
GROUP BY shipmentcost_budgetid |
573 |
GROUP BY shipmentcost_budgetid |
574 |
|, 'budget_id'); |
574 |
|, 'budget_id'); |
575 |
my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| |
575 |
my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| |
576 |
SELECT budget_id, |
576 |
SELECT budget_id, |
577 |
SUM(adjustment) as adjustments |
577 |
SUM(adjustment) as adjustments |
578 |
FROM aqinvoice_adjustments |
578 |
FROM aqinvoice_adjustments |
579 |
JOIN aqinvoices USING (invoiceid) |
579 |
JOIN aqinvoices USING (invoiceid) |
580 |
WHERE closedate IS NOT NULL |
580 |
WHERE closedate IS NOT NULL |
581 |
GROUP BY budget_id |
581 |
GROUP BY budget_id |
582 |
|, 'budget_id'); |
582 |
|, 'budget_id'); |
583 |
my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| |
583 |
my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| |
584 |
SELECT budget_id, |
584 |
SELECT budget_id, |
585 |
SUM(adjustment) as adjustments |
585 |
SUM(adjustment) as adjustments |
586 |
FROM aqinvoice_adjustments |
586 |
FROM aqinvoice_adjustments |
587 |
JOIN aqinvoices USING (invoiceid) |
587 |
JOIN aqinvoices USING (invoiceid) |
588 |
WHERE closedate IS NULL AND encumber_open = 1 |
588 |
WHERE closedate IS NULL AND encumber_open = 1 |
589 |
GROUP BY budget_id |
589 |
GROUP BY budget_id |
590 |
|, 'budget_id'); |
590 |
|, 'budget_id'); |
591 |
|
591 |
|
592 |
|
592 |
|
593 |
foreach my $budget (@sort) { |
593 |
foreach my $budget (@sort) { |
594 |
if ( not defined $budget->{budget_parent_id} ) { |
594 |
if ( not defined $budget->{budget_parent_id} ) { |
595 |
_recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); |
595 |
_recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); |
|
|
596 |
} |
596 |
} |
597 |
} |
597 |
} |
598 |
} |
598 |
return \@sort; |
599 |
return \@sort; |