- my $results = $sth->fetchall_arrayref({});
- my @res = @$results;
- my $i = 0;
- while (1) {
- my $depth_cnt = 0;
- foreach my $r (@res) {
- my @child;
- # look for children
- $r->{depth} = '0' if !defined $r->{budget_parent_id};
- foreach my $r2 (@res) {
- if (defined $r2->{budget_parent_id}
- && $r2->{budget_parent_id} == $r->{budget_id}) {
- push @child, $r2->{budget_id};
- $r2->{depth} = ($r->{depth} + 1) if defined $r->{depth};
- }
- }
- $r->{child} = \@child if scalar @child > 0; # add the child
- $depth_cnt++ if !defined $r->{'depth'};
- }
- last if ($depth_cnt == 0 || $i == 100);
- $i++;
- }
-
- # look for top parents 1st
- my (@sort, $depth_count);
- ($i, $depth_count) = 0;
- while (1) {
- my $children = 0;
- foreach my $r (@res) {
- if ($r->{depth} == $depth_count) {
- $children++ if (ref $r->{child} eq 'ARRAY');
-
- # find the parent id element_id and insert it after
- my $i2 = 0;
- my $parent;
- if ($depth_count > 0) {
-
- # add indent
- my $depth = $r->{depth} * 2;
- $r->{budget_code_indent} = $r->{budget_code};
- $r->{budget_name_indent} = $r->{budget_name};
- foreach my $r3 (@sort) {
- if ($r3->{budget_id} == $r->{budget_parent_id}) {
- $parent = $i2;
- last;
- }
- $i2++;
- }
- } else {
- $r->{budget_code_indent} = $r->{budget_code};
- $r->{budget_name_indent} = $r->{budget_name};
- }
-
- if (defined $parent) {
- splice @sort, ($parent + 1), 0, $r;
- } else {
- push @sort, $r;
- }
- }
-
- $i++;
- } # --------------foreach
- $depth_count++;
- last if $children == 0;
- }
-
-
- foreach my $budget (@sort) {
- $budget->{budget_spent} = GetBudgetSpent( $budget->{budget_id} );
- $budget->{budget_ordered} = GetBudgetOrdered( $budget->{budget_id} );
- $budget->{total_spent} = GetBudgetHierarchySpent( $budget->{budget_id} );
- $budget->{total_ordered} = GetBudgetHierarchyOrdered( $budget->{budget_id} );
+
+ my %links;
+ # create hash with budget_id has key
+ while ( my $data = $sth->fetchrow_hashref ) {
+ $links{ $data->{'budget_id'} } = $data;
+ }
+
+ # link child to parent
+ my @first_parents;
+ foreach my $budget ( sort { $a->{budget_code} cmp $b->{budget_code} } values %links ) {
+ my $child = $links{$budget->{budget_id}};
+ if ( $child->{'budget_parent_id'} ) {
+ my $parent = $links{ $child->{'budget_parent_id'} };
+ if ($parent) {
+ unless ( $parent->{'children'} ) {
+ # init child arrayref
+ $parent->{'children'} = [];
+ }
+ # add as child
+ push @{ $parent->{'children'} }, $child;
+ }
+ } else {
+ push @first_parents, $child;
+ }
+ }
+
+ my @sort = ();
+ foreach my $first_parent (@first_parents) {
+ _add_budget_children(\@sort, $first_parent, 0);
+ }
+ 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 );
+ }
+ }