Handling Prices

From Koha Wiki
Jump to navigation Jump to search

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

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.

Retrieved from "https://wiki.koha-community.org/w/index.php?title=Handling_Prices&oldid=11514"