View | Details | Raw Unified | Return to bug 19792
Collapse All | Expand All

(-)a/C4/Budgets.pm (-6 / +60 lines)
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
(-)a/acqui/acqui-home.pl (-4 lines)
Lines 78-86 foreach my $budget ( @{$budget_arr} ) { Link Here
78
    if ( !defined $budget->{budget_amount} ) {
78
    if ( !defined $budget->{budget_amount} ) {
79
        $budget->{budget_amount} = 0;
79
        $budget->{budget_amount} = 0;
80
    }
80
    }
81
82
    $budget->{'budget_ordered'} = GetBudgetOrdered( $budget->{'budget_id'} );
83
    $budget->{'budget_spent'}   = GetBudgetSpent( $budget->{'budget_id'} );
84
    if ( !defined $budget->{budget_spent} ) {
81
    if ( !defined $budget->{budget_spent} ) {
85
        $budget->{budget_spent} = 0;
82
        $budget->{budget_spent} = 0;
86
    }
83
    }
87
- 

Return to bug 19792