|
Lines 544-571
sub GetBudgetHierarchy {
Link Here
|
| 544 |
} |
544 |
} |
| 545 |
|
545 |
|
| 546 |
# Get all the budgets totals in as few queries as possible |
546 |
# Get all the budgets totals in as few queries as possible |
| 547 |
my $hr_budget_spent = $dbh->selectall_hashref(qq| |
547 |
my $hr_budget_spent = $dbh->selectall_hashref(q| |
| 548 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
548 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
| 549 |
SUM( COALESCE(unitprice_tax_included, ecost_tax_included) * quantity ) AS budget_spent |
549 |
SUM( COALESCE(unitprice_tax_included, ecost_tax_included) * quantity ) AS budget_spent |
| 550 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
550 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
| 551 |
WHERE quantityreceived > 0 AND datecancellationprinted IS NULL |
551 |
WHERE quantityreceived > 0 AND datecancellationprinted IS NULL |
| 552 |
GROUP BY budget_id |
552 |
GROUP BY budget_id |
| 553 |
|, 'budget_id'); |
553 |
|, 'budget_id'); |
| 554 |
my $hr_budget_ordered = $dbh->selectall_hashref(qq| |
554 |
my $hr_budget_ordered = $dbh->selectall_hashref(q| |
| 555 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
555 |
SELECT aqorders.budget_id, aqbudgets.budget_parent_id, |
| 556 |
SUM(ecost_tax_included * quantity) AS budget_ordered |
556 |
SUM(ecost_tax_included * quantity) AS budget_ordered |
| 557 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
557 |
FROM aqorders JOIN aqbudgets USING (budget_id) |
| 558 |
WHERE quantityreceived = 0 AND datecancellationprinted IS NULL |
558 |
WHERE quantityreceived = 0 AND datecancellationprinted IS NULL |
| 559 |
GROUP BY budget_id |
559 |
GROUP BY budget_id |
| 560 |
|, 'budget_id'); |
560 |
|, 'budget_id'); |
| 561 |
my $hr_budget_spent_shipment = $dbh->selectall_hashref(qq| |
561 |
my $hr_budget_spent_shipment = $dbh->selectall_hashref(q| |
| 562 |
SELECT shipmentcost_budgetid as budget_id, |
562 |
SELECT shipmentcost_budgetid as budget_id, |
| 563 |
SUM(shipmentcost) as shipmentcost |
563 |
SUM(shipmentcost) as shipmentcost |
| 564 |
FROM aqinvoices |
564 |
FROM aqinvoices |
| 565 |
WHERE closedate IS NOT NULL |
565 |
WHERE closedate IS NOT NULL |
| 566 |
GROUP BY shipmentcost_budgetid |
566 |
GROUP BY shipmentcost_budgetid |
| 567 |
|, 'budget_id'); |
567 |
|, 'budget_id'); |
| 568 |
my $hr_budget_ordered_shipment = $dbh->selectall_hashref(qq| |
568 |
my $hr_budget_ordered_shipment = $dbh->selectall_hashref(q| |
| 569 |
SELECT shipmentcost_budgetid as budget_id, |
569 |
SELECT shipmentcost_budgetid as budget_id, |
| 570 |
SUM(shipmentcost) as shipmentcost |
570 |
SUM(shipmentcost) as shipmentcost |
| 571 |
FROM aqinvoices |
571 |
FROM aqinvoices |
|
Lines 575-581
sub GetBudgetHierarchy {
Link Here
|
| 575 |
|
575 |
|
| 576 |
|
576 |
|
| 577 |
foreach my $budget (@sort) { |
577 |
foreach my $budget (@sort) { |
| 578 |
if ($budget->{budget_parent_id} == undef) { |
578 |
if ( not defined $budget->{budget_parent_id} ) { |
| 579 |
_recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment ); |
579 |
_recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment ); |
| 580 |
} |
580 |
} |
| 581 |
} |
581 |
} |
| 582 |
- |
|
|