Real multi currency supported?

[Johannes(swe)]

Hello people, first timer poster but longtime browser.

 

My company is currently expanding into a second country and I was a bit baffled about the procedure with using one default currency and converting all prices from that price. I want to be able to set individual prices for each product in each specific country. This seems to me like the normal case of what you would do but it's not how oscommerce works and it seems to me that it will take me weeks to modify the behavior to suit my needs.

 

Have anyone around here implemented a working solution similar to what I need or/and seen any contrib's that would be helpful.

 

Creating separate products (duplicates with separate prices) for each country is not really an option for me.

 

Sincerely

 

/Johannes

[Johannes(swe)]

To clarify, currently you set one price per product. This price use the default currency. If you switch currency to say one that is worth 1/2 as much as the default currency all prices will be displayed as twice the default price. In my case I might want product X to cost 3a and 6b and product Y to cost 2a and 5b which I can't figure out a way to do.

[klarsen]

To clarify, currently you set one price per product. This price use the default currency. If you switch currency to say one that is worth 1/2 as much as the default currency all prices will be displayed as twice the default price. In my case I might want product X to cost 3a and 6b and product Y to cost 2a and 5b which I can't figure out a way to do.

 

You are right and also wrong. :D

 

Traditionally, international business is done as you assume -- a company sets up a subsidiary in the new country (or has a representative there), and prices are set in the local currency to what the market will bear.

 

BEFORE a company does this, however, customers sometimes find the product and simply purchase it, making a transaction in the company's home currency and calculating themselves the exchange rate and costs. The company has already set the prices to what the market will bear in the home currency. This is what the default osCommerce assumes.

 

BOTH of these have problems! If you set the prices to what the market will bear in the home currency, then the prices aren't optimized for the markets in other currencies. A good example is DVDs. The widely-accepted prices in the US are often lower than those in Europe, but higher than those in Latin America. This is what you are trying to solve -- you want to set a seperate price for Europe, another for the US and a different price for Latin America.

 

BUT if you do that, then someone could purchase your products at the Latin America price and sell them at your price or less in Europe, making a profit on your products! [Note: I am aware of DVD region coding, which makes this difficult or impossible. But it is very possible with other products.]

 

All this depends on the industry and products you are talking about. I explain all this simply to help everyone understand that BOTH approaches are needed, but BOTH also have problems.

 

Ideally, osCommerce should allow either approach. Currently, it only allows setting a home currency price and using exchange rates to give prices in other currencies (as you describe).

 

I can think of one work around. You could use the "Separate Pricing per Customer" contribution (http://www.oscommerce.com/community/contributions,716) and set up customer groups by country. You would only need coding then to force customers into the proper groups when they register (i.e., when they enter their address). Of course, you would still need to work on presentation of prices (all groups would show the same currency, just have different prices). This is NOT a perfect solution, but with a little modification it could work.

 

The ideal solution is simply not available in osCommerce. I look forward to someone providing it as a modification.

[Johannes(swe)]

Thanks for the extensive response, much appreciated. It might be that my perspective is a bit narrow and I understand what you are saying about both approaches being needed. However there's an important distinction between the two different approaches while using osCommerce. In one of them the problem is a business problem and one that is easily solved by pricing your wares correctly. In the other one the problem is a technical limitation.

 

If I had to choose one and only one of those problems it would definitely be number one, maybe with the addition of a tool to automatically set the product prices for the new country/region based on an exchange rate much like it's done today for presentation purposes. This would not be a factor that would go against the rapid deployment idea and would give the users a flexibility I do believe they want.

 

While looking for a work around yesturday I came across the contrib "Separate Pricing per Customer" and today I'll definitly explore that with your blessing. It doesen't sound like it's made for my purposes but like you say with a bit of work the success rate should be good enough to try.

 

I'll update this thread with the progress when I've done some work.

[klarsen]

Thanks for the extensive response, much appreciated. It might be that my perspective is a bit narrow and I understand what you are saying about both approaches being needed. However there's an important distinction between the two different approaches while using osCommerce. In one of them the problem is a business problem and one that is easily solved by pricing your wares correctly. In the other one the problem is a technical limitation.

 

You are exactly right.

 

Half my reason for writing up my explanation is simply to point out to other readers that both systems are not only possible but needed. I hope that in the next iteration of the base osCommerce system, both choices are available.

 

It is also something that a descent php programmer could handle, although it does seem like a pretty extensive modification would be required.

 

Bottom line is, you are right, both are needed. The Separate Pricing per Customer might be a very rough work-around, but a better modification would require some php programming. Ideally, osCommerce should offer both in the future.

[Johannes(swe)]

I've researched the extension and came to the conclusion that due to the fact that our solution is heavily modified it will be easier to add a flag in the product table to indicate which country it is for, copy all the products that we are gonna sell in the new country with a different price and use that flag to decide which products I present to the customers in different zones. To avoid having to edit and enter twice the amount of product data I'll connect products so you actualy edit them at the same time. The idea is that to the super user the only difference from today is that there will be a language specific price field. Also activating/deactivating products will have to be done in a different way from today.

[klarsen]

I've researched the extension and came to the conclusion that due to the fact that our solution is heavily modified it will be easier to add a flag in the product table to indicate which country it is for, copy all the products that we are gonna sell in the new country with a different price and use that flag to decide which products I present to the customers in different zones. To avoid having to edit and enter twice the amount of product data I'll connect products so you actualy edit them at the same time. The idea is that to the super user the only difference from today is that there will be a language specific price field. Also activating/deactivating products will have to be done in a different way from today.

 

Please post this as a contribution. While I agree that its not the ideal solution to the problem, its much better to have the work-around available for others to use also.

[Arven]

Well, I do have a related problem the suggested "fix" doesn't solve.

 

I do have products that originate in US$ as well as those who come from the EU.

 

The products from the US I need to enter in US$ as well, which needs to be converted into EUR (the currency) displayed "on the fly" (in order to have competitive pricing without having to recheck every product each week just because the exchange rate fluctuates) while the original EU products can be displayed normally. Does anybody have a suggestion how to solve this?

[Sun19]

Hi I also have the same problem, that we want to set seperate price in ? and ? for the same commodity.

I was thinking aout adding a seperate table that just keep the products_id and the price_in_pound(later if another currency need to be added, then a field for the same could be added) and instea of calculating the price to be dispalyed,based on the conversion factor, just get the actual price form this table with products_id and display

I donno how feasible this solution is.

Any suggestions would be much appreciated.

Thanks

[celiawessen]

Extreme, but works - make two stores with the same design. Like the AppleStore.

 

Probably the least amount of coding. Forces customers to think globally too.

 

One of the osC stores I administer has seven brick-and-mortar shops around the world and web prescence in three different countries. They don't share inventory and they don't take each other's returns - so they needed three separate databases. Having three separate online shops was the best solution for them.

[flying_kites]

I am assuming you are talking about a separate pricing per country feature where you want to present the same product with different prices based on country rather than on exhange rate.

 

The issue is two-fold:

 

1. which country are you using to identify the price

 

When guests come to the store - what default price do you show

Do you allow then to change currency themselves and if so is that using exchange rates or your country table?

 

When they log in, which address are you using. Default address? Which means what? That coule be any one of residential address, delviry address or billling address.

 

Most sites, as indicated by the last post use a subdomain structure to direct people to the stores by country. E.g. figleaves.com. They used to be more general as in go to the US store, go to the UK store. but now you are sent to the US store and asked if you are shipping outside the US and if you click, you go to the UK store. Reason? Where the order is being fulfilled. Product on costs vary (usually higher) so proces change and shipping costs are ususaly cheaper (closer to where customer is)

 

If you are fullfilling the order from the same place then why different prices? Shipping is covered. Oncosts don't differ. You are just trying to change prices based on what the market can tolerate. Tricky thing to do and retain customers in the online environment.

 

Having said that exchange rates are tricky and prices can look stupid when converted so it is nice to be able to set prices yourself for each country.

 

The way to do that is to add a table to the product structure. With the default country price as part of the current table to min. database queries and an extra table to hold other countries.

 

But again, how do you decide what price to show? Not logged in default, logged in then what? Or you going to try and work out country by IP address?

[mindloop]

While working on a osC site for a client i stumbled upon the same problem as Arven's.

 

That is the store manager has multiple suppliers across the world and recieves price-lists in the supplier's currency.

Products are sold with prices updated to day's coversion rate. (daily update!)

The products are all displayed to the customer using only one currency of choice (i.e. default currency).

 

So the only sollution i see here, wich doesn'y involve but a small part of php recoding is to alter the database structure, and to compute at database level the price we want to display.

I found 2 ways of achieving this but both of them require mySql 5.x. ...

There might be other sollutions for < 5.x so if anyone finds one, it would be highly appreciated. Due the lack of time for searching for a mySql 4.x sollution i just urged my client to use a mysql 5.x hosting environment)

 

The two methods i mentioned above both require that you add a column named 'product_currency' either in the 'products' table or in a new table linked through 'products_id' to the 'products' table.

Presuming you have done this

Method 1. Conssists of creating a stored function to compute the real price by taking currencies values out of the currencies table and applying the value multiplier to the price from products.price.

like this:

CREATE FUNCTION get_price (id int(11),p_price decimal(15,4)) RETURNS decimal(15,4)
RETURN (select c.value from products_additional_fields af left join currencies c on c.code=af.product_currency where  af.products_id=id)*p_price;


ALTER TABLE products CHANGE price price_raw INTEGER;
ALTER TABLE products ADD COLUMN price AS get_price(products_id,raw_price) DECIMAL (15,4)

Care must be taken when inserting/updating products, as the actual 'price' column used by osC for updating/inserting should be 'raw_price' (PHP code modifications are needed, but theese are needed only in the products adding/updating parts of the script => few modifications).

For reading values osC will use the 'price' column wich is our computed column.

 

Method 2. Is to create a view of the table products and supply it to osC as the real products table. (either by renaming original 'products' table to something else and naming the view as 'products' or by altering the products table value in table_names.php to match the view's name)

You then a) build the necessareley triggers for update/insert/delete OR b) chage php code and use the real table for editing/inserting/deleting instead of the view.

 

Both methods involve code modifications to the script and structure modifications to the database.

 

An alternative method would be complete recoding of osC mode of computing price by adding a function called compute_price() which should be used cross-site everytime a price is needed for a product. This would open the door for so many useful contribs related to price manipulation (fees, tax calculation, so on). This change would be a nice addition to their next milestone release IMO.

[mindloop]

Oh, sorry about that,

ALTER TABLE products CHANGE price price_raw INTEGER;

should actually read

ALTER TABLE products CHANGE price price_raw DECIMAL(15,4);

(script wont let me edit previous post for some strange reason).