Test major Koha Wiki changes or bug fixes here without fear of breaking the production wiki.
For the current Koha Wiki, visit https://wiki.koha-community.org .Handling Prices
Handling prices in Koha
This page gives some information about the way prices are stored, calculated, displayed, and used in connection with currencies, budget and vendor data.
It describes the current master behavior (07/10/2013).
It's only a draft for the moment
Goal of this page : There are different prices and Koha, and their articulation with system preferencies, currencies, budget and vendor data are not obvious.
Some bugs related to prices have been reported and are uneasy to fix because the normal behavior of Koha is sometimes not clear.
Fixing those bug will be easier with more available documentation about price management.
This page deals with prices but also prefs, data closely linked to prices and used to calculate some of them (currencies, gst rate), or used with prices (funds amount, shipping cost)
Storage
Prices
Prices themselves are stored in 3 places :
- in aqorders table
- in items and deleteditems tables
- in MARC records (in specific item subfield)
(column description are taken from http://schema.koha-community.org/)
In aqorders table :
- listprice : decimal (28,6) : the vendor price for this line item
- unitprice : decimal (28,6) : the actual cost entered when receiving this line item
- rrp : decimal (13,2) : the replacement cost for this line item
- ecost : decimal (13,2) : the estimated cost for this line item
- uncertainprice : bit (0)
rrp and ecost can or cannot include gst, depending on the vendor prefs.
In items and deleteditems table :
- price : decimal (8.2) : purchase price (MARC21 952$g)
- replacementprice : decimal (8.2) : cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
Informations related to prices are stored in these tables:
In aqorders table :
- currency : varchar (3) : the currency used for the purchase
- gstrate : decimal (6,4) : the tax rate for this line item
- discount : float (6,4) : the discount for this line item
In aqbooksellers table :
- listprice : varchar (10) : currency code for list prices
- invoiceprice: varchar (10) : currency code for list prices
- gstreg : tinyint (3) : is your library charged tax (1 for yes, 0 for no)
- listincgst : tinyint (3) : is tax included in list prices (1 for yes, 0 for no)
- invoiceincgst : tinyint (3) : is tax included in invoice prices (1 for yes, 0 for no)
- gstrate : decimal (6,4) : the tax rate the library is charged
- discount : float (6,4) : discount offered on all items ordered from this vendor
In currency table :
- rate : float (15,5)
In aqbudgets :
- budget_amount : decimal (28,6) : total amount for this fund
- budget_encumb : decimal (28,6) : not used in the code
- budget_expend : decimal (28,6) : not used in the code
In aqinvoices
- shipmentcost : decimal (28,6)
Calculations and transformations in C4 subs
C4 subs related to prices :
- C4::Acquisition :
- subs returning an order or some orders : GetOrderFromItemnumber ; GetPendingOrders ; GetOrders ; GetOrder ; GetOrdersByBiblionumber ; GetLastOrderNotReceivedFromSubscriptionid ; GetCancelledOrders ; SearchOrder ; GetLateOrders ; GetHistory
- subs creating or editing an order or some orders : NewOrder ; ModOrder ; ModReceiveOrder
- subs returning a basket or basketgroup in CSV : GetBasketAsCSV ; GetBasketGroupAsCSV
- subs returning parcel(s) : GetParcel ; GetParcels
- subs returning invoice details(s) : GetInvoiceDetails
- C4::Budgets::GetBudgetSpent
- C4::Budgets::ConvertCurrency
- C4::Budgets::GetBudgetOrdered
- C4::Budgets::GetBudgetsPlanCell
- C4::Biblio::GetMarcPrice
- C4::Biblio::MungeMarcPrice
- C4::Bookseller::AddBookseller
- C4::Bookseller::ModBookseller
- C4::Items::ModItemFromMarc
- C4::Items::GetLostItems
- C4::Circulation::LostItem
- C4::Accounts::chargelostitem
Most subs are only returning some fields from database without modification nor computation. Only some subs make some computation with prices:
- C4::Acquisition::GetLateOrders
SELECT... (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
- C4::Acquisition::GetHistory
$total_price += $line->{'quantity'} * $line->{'ecost'};
- C4::Budgets::GetBudgetSpent
SELECT SUM( COALESCE(unitprice, ecost) * quantity ) AS sum FROM aqorders
- C4::Budgets::GetBudgetOrdered
SELECT SUM(ecost * quantity) AS sum FROM aqorders
- C4::Budgets::ConvertCurrency
- C4::Budgets::GetBudgetsPlanCell
Calculations and transformations outside of C4 subs
- acqui/acqui-home.pl : uses GetBudgetOrdered and GetBudgetSpent
$budget->{'budget_ordered'} = GetBudgetOrdered( $budget->{'budget_id'} );
$budget->{'budget_spent'} = GetBudgetSpent( $budget->{'budget_id'} );
- acqui/spent.pl : custom SQL query, then use orders unitprice and invoice shipmentcost
my $rowtotal = $recv * $data->{'unitprice'};
$data->{'rowtotal'} = sprintf( "%.2f", $rowtotal );
$data->{'unitprice'} = sprintf( "%.2f", $data->{'unitprice'} );
$subtotal += $rowtotal;
shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
- uncertainprice.pl : calculate listprice, ecost and rpp from price entered by the librarian. Does not take tax into account
$order->{'listprice'} = $input->param('price'.$ordernumber);
$order->{'ecost'}= $input->param('price'.$ordernumber) - (($input->param('price'.$ordernumber) /100) * $bookseller->{'discount'});
$order->{'rrp'} = $input->param('price'.$ordernumber);
listprice = price entered
ecost = price entered - discount calculated on price entered
rrp = price entered
- parcel.pl : results from GetInvoiceDetails and GetPendingOrders, then calculate unitprice, ecost, gst ; use 3 custom subs for calculation
- main code
$invoice = GetInvoiceDetails($invoiceid) if $invoiceid;
...
my @parcelitems = @{ $invoice->{orders} };
...
for my $item ( @parcelitems ) {
$item->{unitprice} = get_value_with_gst_params( $item->{unitprice}, $item->{gstrate}, $bookseller );
$total = ( $item->{'unitprice'} ) * $item->{'quantityreceived'};
$item->{'unitprice'} += 0;
my %line;
%line = %{ $item };
my $ecost = get_value_with_gst_params( $line{ecost}, $line{gstrate}, $bookseller );
$line{ecost} = sprintf( "%.2f", $ecost );
$line{invoice} = $invoice->{invoicenumber};
$line{total} = sprintf($cfstr, $total);
$line{booksellerid} = $invoice->{booksellerid};
$totalprice += $item->{'unitprice'};
$line{unitprice} = sprintf( $cfstr, $item->{'unitprice'} );
my $gste = get_gste( $line{total}, $line{gstrate}, $bookseller );
my $gst = get_gst( $line{total}, $line{gstrate}, $bookseller );
$foot{$line{gstrate}}{gstrate} = $line{gstrate};
$foot{$line{gstrate}}{value} += sprintf( "%.2f", $gst );
$total_quantity += $line{quantity};
$total_gste += $gste;
$total_gsti += $gste + $gst;
and
if(!defined $invoice->{closedate}) {
my $pendingorders;
...
if($input->param('op') eq "search"){
...
$pendingorders = GetPendingOrders($booksellerid,$grouped,$owner,$basketno,$orderno,$search,$ean);
}else{
$pendingorders = GetPendingOrders($booksellerid);
...
for (my $i = 0 ; $i < $countpendings ; $i++) {
my %line;
%line = %{$pendingorders->[$i]};
my $ecost = get_value_with_gst_params( $line{ecost}, $line{gstrate}, $bookseller );
$line{unitprice} = get_value_with_gst_params( $line{unitprice}, $line{gstrate}, $bookseller );
$line{quantity} += 0;
$line{quantityreceived} += 0;
$line{unitprice}+=0;
$line{ecost} = sprintf( "%.2f", $ecost );
$line{ordertotal} = sprintf( "%.2f", $ecost * $line{quantity} );
$line{unitprice} = sprintf("%.2f",$line{unitprice});
$line{invoice} = $invoice;
$line{booksellerid} = $booksellerid;
- sub get_value_with_gst_params ($value,$gstrate,$bookseller)
If catalog prices and invoiced prices include tax, or If catalog prices and invoiced prices does not include tax,
return $value
Ex : $value = 100 ; $gstrate = 0.55 ; return 100
If catalog prices include tax, but not invoiced prices,
return $value / ( 1 + $gstrate );
Ex : $value = 100 ; $gstrate = 0.55 ; return 100/1.055 = 94.79
If catalog prices does not include tax, but invoiced prices does,
return $value * ( 1 + $gstrate )
Ex : $value = 100 ; $gstrate = 0.55 ; return 100*1.055 = 105.5
sub get_value_with_gst_params {
my $value = shift;
my $gstrate = shift;
my $bookseller = shift;
if ( $bookseller->{listincgst} ) {
if ( $bookseller->{invoiceincgst} ) {
return $value;
} else {
return $value / ( 1 + $gstrate );
}
} else {
if ( $bookseller->{invoiceincgst} ) {
return $value * ( 1 + $gstrate );
} else {
return $value;
}
}
}
- sub get_gste ($value,$gstrate,$bookseller)
If invoiced prices from the bookseller include tax, return $value / ( 1 + $gstrate ). Else return $value
Ex : $value = 100 ; $gstrate = 0.55 ; return either 100/1.055 = 94.79 or 100
sub get_gste {
my $value = shift;
my $gstrate = shift;
my $bookseller = shift;
return $bookseller->{invoiceincgst}
? $value / ( 1 + $gstrate )
: $value;
}
- sub get_gst ($value,$gstrate,$bookseller)
If invoiced prices from the bookseller include tax, return $value / ( 1 + $gstrate ) * $gstrate. Else return $value * ( 1 + $gstrate ) - $value
Ex : $value = 100 ; $gstrate = 0.55 ; return either 100/1.055 * 0.55 = 5.21 or 100 * 1.055 - 100 = 5.50
sub get_gst {
my $value = shift;
my $gstrate = shift;
my $bookseller = shift;
return $bookseller->{invoiceincgst}
? $value / ( 1 + $gstrate ) * $gstrate
: $value * ( 1 + $gstrate ) - $value;
}
- neworderempty.pl
new order from Z3950 import:
$listprice = GetMarcPrice($marcrecord, $marcflavour);
new order from a subscription renewal or a suggestion
$data = ($biblionumber) ? GetBiblioData($biblionumber) : GetSuggestion($suggestionid);
else modify order
$data = GetOrder($ordernumber);
get currency (of order, or of vendor, or default)
$data is passed to the template without transformation of prices
- invoice.pl : get data from GetInvoiceDetails
my $details = GetInvoiceDetails($invoiceid);
my $orders = $details->{'orders'};
calculate total_rrp and total_est, get discount and tax rate
$total_rrp += $order->{quantity} * $order->{rrp};
$total_est += $order->{quantity} * $order->{'ecost'};
my $gist = $bookseller->{gstrate} // C4::Context->preference("gist") // 0;
my $discount = $bookseller->{'discount'} ? ( $bookseller->{discount} / 100 ) : 0;
if no gst rate in bookseller or in general syspref
$total_rrp_gste = $total_rrp_gsti = $total_rrp; $total_est_gste = $total_est_gsti = $total_est; $gist_rrp = $gist_est = 0;
else (some gst rate defined), if catalog prices already include GST ($bookseller->{'listincgst'} =1 )
Ex : total_rrp = 100 ; total_est = 90; gist = 0.55 ; returns $total_rrp_gsti = 100 ; $total_rrp_gste = 100 / 1.055 = 94.79 ; $total_est_gsti = 90 ; $total_est_gste = 90 - (94.79 * 0.1) = 80.52
# we know $total_rrp_gsti $total_rrp_gsti = $total_rrp; # and can reverse compute other values $total_rrp_gste = $total_rrp_gsti / ( $gist + 1 ); $gist_rrp = $total_rrp_gsti - $total_rrp_gste; $total_est_gste = $total_rrp_gste - ( $total_rrp_gste * $discount ); $total_est_gsti = $total_est; $gist_est = $gist_rrp - ( $gist_rrp * $discount );
else if catalog prices does not include GST
# then we use the common way to compute other values $total_rrp_gste = $total_rrp; $gist_rrp = $total_rrp_gste * $gist; $total_rrp_gsti = $total_rrp_gste + $gist_rrp; $total_est_gste = $total_est; $total_est_gsti = $total_rrp_gsti - ( $total_rrp_gsti * $discount ); $gist_est = $gist_rrp - ( $gist_rrp * $discount );
add shipment cost
my $total_gsti_shipment = $total_est_gsti + $details->{shipmentcost};
Wanted behavior
Remarks, bugs and possible enh
acqui/invoice.pl does not seem to take into account unitprice (it is get from the order, but not transformed depending on gst rate), nor the value of $bookseller->{invoiceincgst}
Some SQL queries in acqui/*.pl could be moved to C4/Acquisitions.pm (UT to write too)
Code would be cleaner if specialized subs were used by all .pl files for computing price (like the 3 subs used in parcel.pl). Maybe we could wrap them in a new C4/Prices.pm or in C4/Acquisitions.pm. UT to write too.