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 (-12 lines)
Lines 71-90 my @budget_loop; Link Here
71
foreach my $budget ( @{$budget_arr} ) {
71
foreach my $budget ( @{$budget_arr} ) {
72
    next unless (CanUserUseBudget($loggedinuser, $budget, $userflags));
72
    next unless (CanUserUseBudget($loggedinuser, $budget, $userflags));
73
73
74
    my $patron = Koha::Patrons->find( $budget->{budget_owner_id} );
75
    if ( $patron ) {
76
        # FIXME should pass the entire object into budget_owner
77
        $budget->{budget_owner_firstname} = $patron->firstname;
78
        $budget->{budget_owner_surname} = $patron->surname;
79
        $budget->{budget_owner_borrowernumber} = $patron->borrowernumber;
80
    }
81
82
    if ( !defined $budget->{budget_amount} ) {
74
    if ( !defined $budget->{budget_amount} ) {
83
        $budget->{budget_amount} = 0;
75
        $budget->{budget_amount} = 0;
84
    }
76
    }
85
86
    $budget->{'budget_ordered'} = GetBudgetOrdered( $budget->{'budget_id'} );
87
    $budget->{'budget_spent'}   = GetBudgetSpent( $budget->{'budget_id'} );
88
    if ( !defined $budget->{budget_spent} ) {
77
    if ( !defined $budget->{budget_spent} ) {
89
        $budget->{budget_spent} = 0;
78
        $budget->{budget_spent} = 0;
90
    }
79
    }
91
- 

Return to bug 19792