From 5b05a93abdd01805a2128e69ef79643bdaa01be3 Mon Sep 17 00:00:00 2001 From: Lyon3 Team Date: Tue, 21 May 2013 12:26:34 +0200 Subject: [PATCH] Bug 9593 Prices not imported correctly from a staged file Initial bug : When there's a round price with no decimals after it, or when the symbol is after the digits, the price is not captured by regular expression and the variable is not initialized. Enhancement : The MungeMarcPrice routine had been widely modified. It 's still possible to pick another price than the default one (first of the price zone string) but unlike the previous mechanism that worked only for prices preceded by the currency sign, it's now valid wherever the symbol is situated. As symbol you may enter a pure currency sign as well as a string including it like '$US'. Moreover, an 'isocode' column had been added in currency table (editable in the pro interface from Administration/Currencies and exchange rates). So you can also choose to pickup a currency from its iso code. If the active currency cannot be found in the string price, then others currencies likely to be present in this list are searched and the first found is used to provide a local price throughout conversion. If no active currency is defined, the first price will be systematically picked up --- C4/Biblio.pm | 100 ++++++++++++++------ admin/currency.pl | 7 +- installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 7 ++ .../prog/en/modules/admin/currency.tt | 16 +++- 5 files changed, 96 insertions(+), 35 deletions(-) diff --git a/C4/Biblio.pm b/C4/Biblio.pm index 7af8619..0e3e279 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -1465,8 +1465,18 @@ sub GetMarcPrice { for my $field ( $record->field(@listtags) ) { for my $subfield_value ($field->subfield($subfield)){ #check value - $subfield_value = MungeMarcPrice( $subfield_value ); - return $subfield_value if ($subfield_value); + my $rate; + ($subfield_value,$rate) = MungeMarcPrice( $subfield_value ); + if ($subfield_value) { + ( $subfield_value ) = $subfield_value =~ m/(\d[\d\,\. ]*\d{0,2})/; + # remove comma,dot or space when used as separators from hundreds + $subfield_value =~s/[\,\. ](\d{3})/$1/g; + # convert comma to dot to ensure correct display of decimals if existing + $subfield_value =~s/,/./; + # convert to active currency if necessary + $subfield_value/= $rate if $rate; + return $subfield_value; + } } } return 0; # no price found @@ -1479,39 +1489,67 @@ Return the best guess at what the actual price is from a price field. sub MungeMarcPrice { my ( $price ) = @_; - return unless ( $price =~ m/\d/ ); ## No digits means no price. - - ## Look for the currency symbol of the active currency, if it's there, - ## start the price string right after the symbol. This allows us to prefer - ## this native currency price over other currency prices, if possible. - my $active_currency = C4::Context->dbh->selectrow_hashref( 'SELECT * FROM currency WHERE active = 1', {} ); - my $symbol = quotemeta( $active_currency->{'symbol'} ); - if ( $price =~ m/$symbol/ ) { - my @parts = split(/$symbol/, $price ); - $price = $parts[1]; - } - - ## Grab the first number in the string ( can use commas or periods for thousands separator and/or decimal separator ) - ( $price ) = $price =~ m/([\d\,\.]+[[\,\.]\d\d]?)/; - - ## Split price into array on periods and commas - my @parts = split(/[\,\.]/, $price); - - ## If the last grouping of digits is more than 2 characters, assume there is no decimal value and put it back. - my $decimal = pop( @parts ); - if ( length( $decimal ) > 2 ) { - push( @parts, $decimal ); - $decimal = ''; - } - - $price = join('', @parts ); - - if ( $decimal ) { - $price .= ".$decimal"; + # Look for the currency symbol and the normalized code of the active currency, if it's there, + my @currencies= C4::Budgets->GetCurrencies(); + my $symbol; + my $isocode; + my $rate; + my $localprice; + my @altercurrencies; + foreach my $cur ( @currencies ) { + if ( $cur->{'active'} ) { + $symbol = $cur->{'symbol'}; + $isocode = $cur->{'isocode'}; + } else { + push @altercurrencies, {symbol=>$cur->{'symbol'},isocode=>$cur->{'isocode'},rate=>$cur->{'rate'},}; + } + } + + if ( $symbol ) { + my @matches =($price=~ / + \s? + ( # start of capturing parenthesis + (?: + (?:\p{Sc}|[a-zA-Z]){1,3} # currency sign (unicode propriety) or alphabetical character whitin 1 to 3 occurrences (block 1) + |(?:\d+[\p{P}\s]?){1,4} # or else at least one digit followed or not by a punctuation sign or whitespace, all theese within 1 to 3 occurrences (block 2) + ) + \s?\p{Sc}?\s? # followed or not by a whitespace + (?: + (?:\p{Sc}|[a-zA-Z]){1,3} # followed by same block as block 1 + |(?:\d+[\p{P}\s]?){1,4} # or by same block as block 2 + ) + # + #~ (?:\s[A-Z]{3})? + \s? # followed or not by 3 alphabetical uppercase character (= isocode for price like '3$ USD') + )? # end of capturing parenthesis + (?:\p{P}|\z) # followed by a punctuation sign, a whitespace or by the end of the string + /gx); + + + if ( @matches ) { + foreach ( @matches ) { + $localprice = $_ and last if index($_, $isocode)>=0; + } + if ( !$localprice ) { + foreach ( @matches ) { + $localprice = $_ and last if $_=~ /(^|[^A-Za-z\p{Sc}])\Q$symbol\E([^A-Za-z\p{Sc}]|\z)/; + } + } + if ( !$localprice and @altercurrencies ) { + foreach my $match ( @matches ) { + foreach my $alter ( @altercurrencies ) { + $localprice = $match and $rate=$alter->{rate} and last if index($match, $alter->{isocode})>=0; + my $altsymbol = $alter->{symbol}; + $localprice = $match and $rate=$alter->{rate} and last if $match=~ /(^|[^A-Za-z\p{Sc}])\Q$altsymbol\E([^A-Za-z\p{Sc}]|\z)/; + } + last if $localprice; + } + } + } + $price=$localprice; } - - return $price; + return ($price,$rate); } diff --git a/admin/currency.pl b/admin/currency.pl index ea22431..abbdca2 100755 --- a/admin/currency.pl +++ b/admin/currency.pl @@ -185,6 +185,7 @@ sub add_validate { my $rec = { rate => $input->param('rate'), symbol => $input->param('symbol') || q{}, + isocode => $input->param('isocode') || q{}, active => $input->param('active') || 0, currency => $input->param('currency'), }; @@ -198,20 +199,22 @@ sub add_validate { {}, $input->param('currency') ); if ($row_count) { $dbh->do( -q|UPDATE currency SET rate = ?, symbol = ?, active = ? WHERE currency = ? |, +q|UPDATE currency SET rate = ?, symbol = ?, isocode = ?, active = ? WHERE currency = ? |, {}, $rec->{rate}, $rec->{symbol}, + $rec->{isocode}, $rec->{active}, $rec->{currency} ); } else { $dbh->do( -q|INSERT INTO currency (currency, rate, symbol, active) VALUES (?,?,?,?) |, +q|INSERT INTO currency (currency, rate, symbol, isocode, active) VALUES (?,?,?,?,?) |, {}, $rec->{currency}, $rec->{rate}, $rec->{symbol}, + $rec->{isocode}, $rec->{active} ); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a6760d4..f4b53dd 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -588,6 +588,7 @@ DROP TABLE IF EXISTS `currency`; CREATE TABLE `currency` ( `currency` varchar(10) NOT NULL default '', `symbol` varchar(5) default NULL, + `isocode` varchar(5) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `rate` float(15,5) default NULL, `active` tinyint(1) default NULL, diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 7c97a51..461d519 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6771,6 +6771,13 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.11.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do("ALTER TABLE currency ADD isocode VARCHAR(5) default NULL AFTER symbol;"); + print "Upgrade to $DBversion done (Added isocode to the currency table)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/currency.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/currency.tt index 99f693b..b697f0b 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/currency.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/currency.tt @@ -24,6 +24,9 @@ if (f.symbol.value.length==0) { _alertString += _("- Symbol missing") + "\n"; } + if (f.isocode.value.length==0) { + _alertString += _("- Iso code missing") + "\n"; + } if (_alertString.length==0) { document.Aform.submit(); } else { @@ -72,7 +75,11 @@
- +

You can define here a currency that will be picked rather than the default one ( that says the first in the price zone ) during the importation process from a staged file +whenever the price data is provided under different currencies.
For this purpose, you must check the active box. +The symbol may be the pure currency sign or a string in which it's included ( like '$US' ).
+If the active currency cannot be found in the string price, then, others currencies likely to be present in this list are searched and the first found will be used to provide a local price throughout conversion.
+Beware that the active currency had to be enabled only if necessary.

[% IF ( else ) %]
New currency @@ -107,7 +114,10 @@ - +
  • + + +
  • Last updated: [% timestamp %]
    • @@ -187,6 +197,7 @@ Currency Rate Symbol + Iso code Last updated Active Actions  @@ -200,6 +211,7 @@ [% loo.currency %] [% loo.rate %] [% loo.symbol |html %] + [% loo.isocode |html %] [% loo.timestamp %] [% IF ( loo.active ) %]✓[% END %] Edit -- 1.7.2.5