|
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; |