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

(-)a/C4/Budgets.pm (-6 / +40 lines)
Lines 79-84 BEGIN { Link Here
79
      CloneBudgetPeriod
79
      CloneBudgetPeriod
80
      CloneBudgetHierarchy
80
      CloneBudgetHierarchy
81
      MoveOrders
81
      MoveOrders
82
      FieldsForCalculatingFundValues
82
    );
83
    );
83
}
84
}
84
85
Lines 341-350 sub ModBudgetPlan { Link Here
341
sub GetBudgetSpent {
342
sub GetBudgetSpent {
342
    my ($budget_id) = @_;
343
    my ($budget_id) = @_;
343
    my $dbh = C4::Context->dbh;
344
    my $dbh = C4::Context->dbh;
344
    # unitprice_tax_included should always been set here
345
345
    # we should not need to retrieve ecost_tax_included
346
    # Choose correct unitprice and ecost fields
347
    my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues();
348
346
    my $sth = $dbh->prepare(qq|
349
    my $sth = $dbh->prepare(qq|
347
        SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE(unitprice_tax_included, ecost_tax_included)") . qq| * quantity ) AS sum FROM aqorders
350
        SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE($unitprice_field, $ecost_field)") . qq| * quantity ) AS sum FROM aqorders
348
            WHERE budget_id = ? AND
351
            WHERE budget_id = ? AND
349
            quantityreceived > 0 AND
352
            quantityreceived > 0 AND
350
            datecancellationprinted IS NULL
353
            datecancellationprinted IS NULL
Lines 374-381 sub GetBudgetSpent { Link Here
374
sub GetBudgetOrdered {
377
sub GetBudgetOrdered {
375
	my ($budget_id) = @_;
378
	my ($budget_id) = @_;
376
	my $dbh = C4::Context->dbh;
379
	my $dbh = C4::Context->dbh;
380
381
    # Get correct unitprice and ecost prices as possible
382
    my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues();
383
377
	my $sth = $dbh->prepare(qq|
384
	my $sth = $dbh->prepare(qq|
378
        SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . qq| *  quantity) AS sum FROM aqorders
385
        SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . qq| *  quantity) AS sum FROM aqorders
379
            WHERE budget_id = ? AND
386
            WHERE budget_id = ? AND
380
            quantityreceived = 0 AND
387
            quantityreceived = 0 AND
381
            datecancellationprinted IS NULL
388
            datecancellationprinted IS NULL
Lines 550-567 sub GetBudgetHierarchy { Link Here
550
    foreach my $first_parent (@first_parents) {
557
    foreach my $first_parent (@first_parents) {
551
        _add_budget_children(\@sort, $first_parent, 0);
558
        _add_budget_children(\@sort, $first_parent, 0);
552
    }
559
    }
560
561
    # Get correct unitprice and ecost prices as possible
562
    my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues();
563
553
    if (!$skiptotals) {
564
    if (!$skiptotals) {
554
        # Get all the budgets totals in as few queries as possible
565
        # Get all the budgets totals in as few queries as possible
555
        my $hr_budget_spent = $dbh->selectall_hashref(q|
566
        my $hr_budget_spent = $dbh->selectall_hashref(q|
556
            SELECT aqorders.budget_id, aqbudgets.budget_parent_id,
567
            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
568
                SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE($unitprice_field, $ecost_field)|) . q| * quantity ) AS budget_spent
558
            FROM aqorders JOIN aqbudgets USING (budget_id)
569
            FROM aqorders JOIN aqbudgets USING (budget_id)
559
            WHERE quantityreceived > 0 AND datecancellationprinted IS NULL
570
            WHERE quantityreceived > 0 AND datecancellationprinted IS NULL
560
            GROUP BY budget_id, budget_parent_id
571
            GROUP BY budget_id, budget_parent_id
561
            |, 'budget_id');
572
            |, 'budget_id');
562
        my $hr_budget_ordered = $dbh->selectall_hashref(q|
573
        my $hr_budget_ordered = $dbh->selectall_hashref(q|
563
            SELECT aqorders.budget_id, aqbudgets.budget_parent_id,
574
            SELECT aqorders.budget_id, aqbudgets.budget_parent_id,
564
                SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| *  quantity) AS budget_ordered
575
                SUM( | . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . q| *  quantity) AS budget_ordered
565
            FROM aqorders JOIN aqbudgets USING (budget_id)
576
            FROM aqorders JOIN aqbudgets USING (budget_id)
566
            WHERE quantityreceived = 0 AND datecancellationprinted IS NULL
577
            WHERE quantityreceived = 0 AND datecancellationprinted IS NULL
567
            GROUP BY budget_id, budget_parent_id
578
            GROUP BY budget_id, budget_parent_id
Lines 1399-1404 sub MoveOrders { Link Here
1399
    return \@report;
1410
    return \@report;
1400
}
1411
}
1401
1412
1413
=head2 FieldsForCalculatingFundValues
1414
1415
    my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues();
1416
1417
Fetch the tax inclusive or tax exclusive database fields for caclulating fund values based
1418
on the value of the CalculateFundValuesIncludingTax system preference.
1419
1420
=cut
1421
1422
sub FieldsForCalculatingFundValues {
1423
1424
    # Choose correct unitprice and ecost fields
1425
    my $unitprice_field = 'unitprice_tax_included';
1426
    my $ecost_field     = 'ecost_tax_included';
1427
1428
    if ( !C4::Context->preference('CalculateFundValuesIncludingTax') ) {
1429
       $unitprice_field = 'unitprice_tax_excluded';
1430
       $ecost_field     = 'ecost_tax_excluded';
1431
    }
1432
1433
    return ( $unitprice_field, $ecost_field);
1434
}
1435
1402
END { }    # module clean-up code here (global destructor)
1436
END { }    # module clean-up code here (global destructor)
1403
1437
1404
1;
1438
1;
(-)a/acqui/ordered.pl (-3 / +6 lines)
Lines 49-59 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
49
    }
49
    }
50
);
50
);
51
51
52
# Choose correct ecost field
53
my ( $unitprice_field, $ecost_field ) = C4::Budgets->FieldsForCalculatingFundValues();
54
52
my $query = <<EOQ;
55
my $query = <<EOQ;
53
SELECT
56
SELECT
54
    aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
57
    aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
55
    quantity-quantityreceived AS tleft,
58
    quantity-quantityreceived AS tleft,
56
    ecost_tax_included, budgetdate, entrydate,
59
    $ecost_field, budgetdate, entrydate,
57
    aqbasket.booksellerid,
60
    aqbasket.booksellerid,
58
    aqbooksellers.name as vendorname,
61
    aqbooksellers.name as vendorname,
59
    GROUP_CONCAT(DISTINCT itype SEPARATOR '|') AS itypes,
62
    GROUP_CONCAT(DISTINCT itype SEPARATOR '|') AS itypes,
Lines 74-80 WHERE Link Here
74
    (quantity > quantityreceived OR quantityreceived IS NULL)
77
    (quantity > quantityreceived OR quantityreceived IS NULL)
75
    GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
78
    GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
76
             tleft,
79
             tleft,
77
             ecost_tax_included, budgetdate, entrydate,
80
             $ecost_field, budgetdate, entrydate,
78
             aqbasket.booksellerid,
81
             aqbasket.booksellerid,
79
             aqbooksellers.name,
82
             aqbooksellers.name,
80
             title
83
             title
Lines 96-102 while ( my $data = $sth->fetchrow_hashref ) { Link Here
96
        $left = $data->{'quantity'};
99
        $left = $data->{'quantity'};
97
    }
100
    }
98
    if ( $left && $left > 0 ) {
101
    if ( $left && $left > 0 ) {
99
        my $subtotal = $left * get_rounded_price( $data->{'ecost_tax_included'} );
102
        my $subtotal = $left * get_rounded_price( $data->{$ecost_field} );
100
        $data->{subtotal} = sprintf( "%.2f", $subtotal );
103
        $data->{subtotal} = sprintf( "%.2f", $subtotal );
101
        $data->{'left'} = $left;
104
        $data->{'left'} = $left;
102
        push @ordered, $data;
105
        push @ordered, $data;
(-)a/acqui/spent.pl (-4 / +7 lines)
Lines 51-56 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
51
    }
51
    }
52
);
52
);
53
53
54
# Get correct unitprice field
55
my ( $unitprice_field ) = C4::Budgets->FieldsForCalculatingFundValues();
56
54
my $query = <<EOQ;
57
my $query = <<EOQ;
55
SELECT
58
SELECT
56
    aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
59
    aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
Lines 62-68 SELECT Link Here
62
    aqorders.invoiceid,
65
    aqorders.invoiceid,
63
    aqinvoices.invoicenumber,
66
    aqinvoices.invoicenumber,
64
    quantityreceived,
67
    quantityreceived,
65
    unitprice_tax_included,
68
    $unitprice_field,
66
    datereceived,
69
    datereceived,
67
    aqbooksellers.name as vendorname
70
    aqbooksellers.name as vendorname
68
FROM (aqorders, aqbasket)
71
FROM (aqorders, aqbasket)
Lines 89-95 WHERE Link Here
89
             aqorders.invoiceid,
92
             aqorders.invoiceid,
90
             aqinvoices.invoicenumber,
93
             aqinvoices.invoicenumber,
91
             quantityreceived,
94
             quantityreceived,
92
             unitprice_tax_included,
95
             $unitprice_field,
93
             datereceived,
96
             datereceived,
94
             aqbooksellers.name
97
             aqbooksellers.name
95
98
Lines 105-113 while ( my $data = $sth->fetchrow_hashref ) { Link Here
105
    my $recv = $data->{'quantityreceived'};
108
    my $recv = $data->{'quantityreceived'};
106
    $data->{'itemtypes'} = [split('\|', $data->{itypes})];
109
    $data->{'itemtypes'} = [split('\|', $data->{itypes})];
107
    if ( $recv > 0 ) {
110
    if ( $recv > 0 ) {
108
        my $rowtotal = $recv * get_rounded_price($data->{'unitprice_tax_included'});
111
        my $rowtotal = $recv * get_rounded_price($data->{$unitprice_field});
109
        $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
112
        $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
110
        $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
113
        $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{$unitprice_field} );
111
        $subtotal += $rowtotal;
114
        $subtotal += $rowtotal;
112
        push @spent, $data;
115
        push @spent, $data;
113
    }
116
    }
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt (-1 / +5 lines)
Lines 79-85 Link Here
79
        [% order.left | html %]
79
        [% order.left | html %]
80
    </td>
80
    </td>
81
    <td class="data cell">
81
    <td class="data cell">
82
        [% order.ecost_tax_included | $Price %]
82
        [% IF Koha.Preference('CalculateFundValuesIncludingTax') %]
83
            [% order.ecost_tax_included | $Price %]
84
        [% ELSE %]
85
            [% order.ecost_tax_excluded | $Price %]
86
        [% END %]
83
    </td>
87
    </td>
84
    <td class="cell" data-order="[% order.entrydate | html %]">
88
    <td class="cell" data-order="[% order.entrydate | html %]">
85
        [% order.entrydate | $KohaDates %]
89
        [% order.entrydate | $KohaDates %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt (-2 / +5 lines)
Lines 80-86 Link Here
80
                [% order.quantityreceived | html %]
80
                [% order.quantityreceived | html %]
81
            </td>
81
            </td>
82
            <td class="cell">
82
            <td class="cell">
83
                [% order.unitprice_tax_included | $Price %]
83
                [% IF Koha.Preference('CalculateFundValuesIncludingTax') %]
84
                    [% order.unitprice_tax_included | $Price %]
85
                [% ELSE %]
86
                    [% order.unitprice_tax_excluded | $Price %]
87
                [% END %]
84
            </td>
88
            </td>
85
            <td class="cell" data-order="[% order.entrydate | html %]">
89
            <td class="cell" data-order="[% order.entrydate | html %]">
86
                [% order.entrydate | $KohaDates %]
90
                [% order.entrydate | $KohaDates %]
87
- 

Return to bug 31631