Easy Global Market Products Change Proposal

This message was sent from: Suggestions and Proposals
http://forums.oscommerce.com/viewtopic.php?p=180715#180715
----------------------------------------------------------------

Hi Everyone;
  Here is a proposal for enabling the Administrator to handle Global Market products.  These are products that are manufactured and purchased from countries other than the country where they are being sold.

[b]WHY THIS CHANGE IS REQUIRED[/b]
	Products often have a 'Minimum Advertized Price' as well as a 'Suggested Retail Price'.  If the SRP for a product is $1000.00 USD and I need to enter the price of the product into the Products database as $1495.00 CAD.   Default currency is CAD so that when the product is sold it is handled correctly by the credit card gateway which only deals in Canadian dollars.  A while later I update the currency rates and of course the price is still listed as $1495.00 CAD, this now translates to $1105.34 USD which is substantially above the SRP.  This means that I would not be competitive with US based retailers and it doesn't accuratly reflect the price I should be charging which is still $1000.00 USD because I do purchase this item from a US manufacture and pay $1000.00 - 15% anyhow.  An opposite problem would happen in a falling Canadian currency in that the US price should would fall below the MAP and I would be in hot water with the US manufacturer for advertizing a price below th
 e MAP let alone losing money on selling products.  In reality the Canadian price should be the one fluctuating with the fluctuating currency rates and the US price should be anchored.

	However, I also sell a great deal of Canadian products purchased from Canadian sources where the price of these products needs to be anchored in Canadian dollars.  Same is true with product I import from Japan and other asian sources.  With JIT inventory, and fluctuating world currencies I don't maintain a lot of inventory but only stock what is needed for a very short term.
  

[b]A SIMPLE, EFFECTIVE, EASY SOLUTION[/b]
  By adding a field in the products database that would be the default currency code for that product and by adding/modifying two formulas that calculate the price displayed in the users choosen currency and the price that gets passed to the credit card gateway.  That's it!  :D

[b]DETAILS:[/b]

DisplayPrice = Int(ProductPrice * (DisplayCurrencyRate		 /	ProductDefaultCurrencyRate) * QuantityPurchased * 100 + .5) / 100
ChargePrice =	Int(ProductPrice * (GlobalDefaultCurrencyRate / ProductDefaultCurrencyRate) * QuantityPurchased * 100 + .5) / 100

Where:
	DisplayPrice		  =		  Price shown on web pages for the customer
	ChargePrice			  =		  Amount in local currency that is charged
	ProductPrice		  =		  Price entered into product database for that product.
	QuantityPurchased   =		  Number of items of this product in shopping cart.
	DisplayCurrencyRate =		  Currency conversion rate as entered into Currencies table for
										  the currency code that the customer has choosen to use for web display.
	GlobalDefaultCurrencyRate  = Currency conversion rate as entered into Currencies table for
										  the currency code that the Administrator has set as the default.
	ProductDefaultCurrencyRate = Currency conversion rate as entered into Currencies table for
										  the currency code that is associated with this product.
(The 'INT()' function is an integer function and the ' * 100 + .5) / 100 is to round the price to the nearest penny.)

The Product Table would contain a new entry; namely ProductCurrencyCode.  This should match one of the entries in the Curriencies Table.  One new global function would be defined, perhaps in the General.php program to calculate the prices.


Function Calc_Prices: () {
	Retrieve Product_Price from Products database, (or variable that is passed to this function)
	Retrieve Product_Currency_Code from Products database, (this is a new field)
	Retrieve QuantityPurchased from Order database, (or variable that is be passed to this function)
	
	ProductDefaultCurrencyRate =	(Retrieve Currency_Exchange_Rate for Product_Currency_Code)
	DisplayCurrencyRate =			(Retrieve Currency_Exchange_Rate for web display currency code)
	GlobalDefaultCurrencyRate =	(Retrieve Currency_Exchange_Rate for default currency code)
	
	DisplayPrice = (See formula above)
	ChargePrice = (See formula above)
	}
// End of Function - Calc_Prices	

	I have manually tested this and found it to work flawlessly no matter what the global default currency, product default currency or display currency.  The admin simply maintains the exchange values in the currency table based on any currency. (Doesn't even have to be the global default!)

	So it may seem like I have already done all the work but I don't know OSC code and how all the modules interleave.  Furthermore, although I can program in a dozen laguages, PHP is not one that I have experience with yet, (sure looks easy though).  Finally, I'm not familar with the database structures that would need to be modified nor am I aware of the ramifications of modifying the products database, both for current builds as well as the about to be release MS2.

	Some feedback please.