Attachment #141018 for bug #31631

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

Lines 79-84 BEGIN { Link Here

(-)a/C4/Budgets.pm (-6 / +40 lines)
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;

Lines 49-59 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here

(-)a/acqui/ordered.pl (-3 / +6 lines)
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;

Lines 51-56 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here

(-)a/acqui/spent.pl (-4 / +7 lines)
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	}

Lines 79-85 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt (-1 / +5 lines)
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 %]

Lines 80-86 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt (-2 / +5 lines)
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