Prices based on gold, which changes daily

Hi, this is quite an urgent question.

We have a store which sells jewelry and watches. Watches have a set retail price, but gold prices change daily depending on the gold commodity price (xe.com will give you that). We don't want to change the prices on ALL the gold products every day. We want to change the "exchange rate" once and have all prices changed automatically.

One huge bonus is we are not plugged into an online payment gateway like Authorize.net, rather we just use the plain old CC module and then we process payments manually with our point of sale merchant machine.

One idea is to set up watch prices in US$. Like eg. Rolex XYZ = $5000. And then set up a fake currency called GG (Grams of Gold) where we have Small Ring = 20 GG, Bracelet = 15.5 GG. Then we use the currency rate to edit the value of GG. So let's say we discover the price of gold is $10/gram today then our prices are:
Rolex XYZ = $5000
Small Ring = 2010 = $200
Bracelet = 15.510 = $155

and then tomorrow the price of gold is $20/gram we have:
Rolex XYZ = $5000
Small Ring = 2010 = $400
Bracelet = 15.510 = $310

Note that the customer should NEVER see the value quoted is GG, it should ONLY show US$. And when the payment gets mailed through to us it must come as $US not GG. This is important because the GG value changes every day so we need to know what the resulting price that the buyer saw on their screen.

So just to sum up:

1. the value of GG comes from us manually entering the currency value once per day.
2. we set up prices once in a made up currency called GG and from there actual $US prices are quoted on to the buyer
3. the web site should enforce that $US is the "only" currency and shouldn't give you a choice of paying in GG (as this makes little sense).

I know that with the flexibility of Zen Cart and the experience that you guys have there will be an elegant solution shortly.

Thanks,
Paul

This could be written, but you would really be best posting this in the Commercial Help Wanted to get an exchange rate added on a per product basis ...

You are not doing this across the board on your products, so you cannot apply it to all products, but rather need this customized based on some products ...

You should also consider if you want this a flat exchange rate on products, or, looking to the future, would you be better with a variable rate based on either a per product exchange rate on the weight or as a product grouping so that later you could have multiple exchange rates on multiple metals ...

Thanks for your reply. I think my requirements are probably simpler than you imagine (or maybe I've read you incorrectly).

Let's put it in different terms, is this possible?:

1. set up two currencies, US$ and CA$.
2. on the admin side, set some product's prices in US$ and some product's prices in CA$.
   BUT
3. enforce that the public side of the site should only ever show prices in US$

so if a product's price has been set in US$ as say US$1000 then it shows the customer US$1000

but if a product's price has been set in CA$ as say CA$1000 then is shows the customer US$850 (or whatever the CA$ -> US$ rate is)

and importantly NEVER give the shopper the option to see prices quoted in CA$. The whole site must show only US$.

THAT'S my question/objective

Thanks again!

I don't get how the price in US and CAD relate to the Gold Value of the day ... :blink:

The Zen Cart Admin works in the default currency ... so if you are setup for USD then the Admin works in USD ... if you are setup for CAD then the Admin works in CAD ...

I think I am missing the point of why you want to work in 2 currencies in the Admin ...

Sorry, forget the gold thing, that was too complicated. But anyhow, you've answered my question. It boils down to zen cart stores prices in the database as a number. That number is the number of default currency "dollars".

What I was HOPING is that zen cart actual stored a number AND the currency that belongs to that number.

This would allow us to set fixed price items as $200, $300, etc. and gold-based products in a differenct "currency" so that the gold->$US conversion can be calculated based on the "currency's" exchange rate which we will manually key in once every day.

This will not work though because zen cart appears to allow many currencies to be displayed to the viewer, but the admin only gets to work in one currency.

Do you get what we're trying to do? I think we're asking too much simply because of the design of the underlying database.

Thanks,
Paul

Alright Linda, here we go. I'm going to try to create a new product type called GOLD.

Any product marked as gold should have it's default currency price multiplied by the exchange rate of gold.

So what I need to do is create a new product type, then dig into the code and intercept the lowest level database access to the product price and modify it so that all places (frontend/backend/invoicing) where a price is displayed will actually show priceValueInDB * daysRateForGold

Do you get what I mean? and do you see how I thought that setting up a currency like CA$ to represent "price of gold" was my first crack at doing this?

I will begin my exploration through the core code... now! so any suggestions on where I might look?

Thanks so much,
Paul

Would it work if you flagged these products as something like products_is_gold = 1 and then had a way to store the products_price_base to the product and then had a setting to say update today's TODAYS_GOLD_RATE and could enter the amount that when products_is_gold = 1, multiple the TODAYS_GOLD_RATE by the products_price_base and update the products_price field ...

Now, the flag can be the products_type where you would need to clone the products_general files and then add the additional fields make a simple configuration_key for this and a place to update it ...

I think we're now both thinking along the correct/most pragmatic lines.

Your plan is probably more robust than mine. My idea was leave all the tables as is. Create a new product type called "Product - Gold". So fixed price items are set as "Product - General" and gold is "Product - Gold". "Product - Gold" has handler set to "product" so nothing fancy there. Now I just happen to know that when I created "Product - Gold" in the db it's ID was 6. It's not the best plan but I my idea is to hack the admin/includes/functions/functions_prices.php file and everywhere where it asks the database for the price, I also ask for the products_type and when it equals 6 then do a query for the currency exchange rate of gold from the "value" column of the "currencies" table and do a multiply.

I will have to do that for all admin and front end queries that find out prices. I've just noticed it's quite complex because you have things like specials prices, etc! Also it's ugly because the store admin must remember that for Gold products the dollars aren't really dollars! they're units of gold or products_price_base as you call it.

Anyway... I prefer your idea because:

1. fewer core hacks, less risk of missing a place where price of item is queried.
2. you don't damage the functions that look after complex pricing like specials, attributes, etc.
3. you don't confuse the store manager by showing dollars that aren't really dollars.
   ... I'm sure there are many other good reasons for your approach.

Question now is what is my roadmap to get this done?

1. create new product type. already done.
2. change the handler to a brand new one, based closely on "product" called "product_gold"
3. create a new table in the database for "product_gold". (correct??)
4. create a new php file which is the actual handler code for "product_gold" (correct??) based closely on "product"
5. add a field for "products_price_base".
   At this point we're done as far as having a working system, right?
6. create a currency called GOLD (which will never actually be used), set a rate.
7. create a script which is run daily, AFTER the day's gold rate has been updated.
8. the script queries the products table and updates every product who's type is "Product - Gold" and set it's products_price with products_base_price * GOLD's value.

Is that all correct? I know I've left something out, something to do with how do you create/modify the admin view where you get to put prices in to be able to enter the base price?

Yes your solution is the right one. Now it's just how to implement it!

I really appreciate your input, thanks!

This is where having the products_price update helps ... you just clone the products_general files everywhere, add the new fields to the products table and to the cloned files for adding/editing products ... and now you don't have to touch functions etc. as the products_price will technically be right with each update to the gold value when it updates the products_price ...

NOTE: Specials do not store the percentage of the discount but rather the calculated total or your manual input for the special price

You really don't have to have a new table for this as the product types use the fields that you address ... and if only the new product type used them, they get ignored ...

NOTE: you can use a separate table, but it is not necessary ...

Compare the products product type and the product_free_shipping product type ... they are more or less identical accept for default values on Always Free Shipping on the:
admin/includes/modules/

files and you can see how those work ...

Great, we must be on the same wavelength as I have begun working at made a decision not to create a new table as you suggested. I will have to add the products_base_price column though.

Ok, so:

1. I have cloned the admin/product.php to admin/product_gold.php but made no code changes.
2. My product type has product_gold as the handler.
3. I have cloned the 4 files in admin/includes/module/product/* to admin/includes/module/product_gold/*

at this point if I preview a product with type gold I get field titles like:
TEXT_PRODUCTS_TAX_CLASS
TEXT_PRODUCTS_PRICE_NET
TEXT_PRODUCTS_PRICE_GROSS
... instead of nice words like:
Tax Class:
Products Price (Net):
Products Price (Gross):

I guess I'm not done cloning.
When that's all working then I need to add the new field to the database and add the field to the adding/editing functions.

Not to worry about Specials, I've also considered it and realised that it won't work on the fluxating gold price. Not a stress!

So now to get the strings showing up, and adding/editing. Thanks so much again!

haha string were too easy, just cloned admin\includes\languages\english/product.php to admin\includes\languages\english/product_gold.php

any other cloning you can think of?

Also I just looked at the product GUI info collector and there's:
Tax Class:
Products Price (Net):
Products Price (Gross):

So normally you type into Gross, and it calculates Net using javascript and when you hit save it's doing what? grabbing the Net value and placing that in products_price? :unsure:

Language files will do you wonders ... :smile:

Look at the ones for product and the one for product_free_shipping product types ...

There are constants built for these ...

Also in the downloads are product types that are bigger, but give you an overall scope of how these compare ...

WOW!!! I owe you :hug: thanks so much for helping me! It's turned out better than I expected!!

Take a look:
Attachment #4288

... and it really works!

It does:

1. go to admin > localization > currencies (where I have set up US$ and XAU which is the official gold index) and hit update. It goes to xe.com and gets today's gold rate!
2. create new product, set type to "Product - Gold"
3. it queries the currencies table and gets the value of XAU
4. set up product, and then put price in either Net or Gross, the javascript allows Net and Gross to fight with each other and when they're done calculating they then mulitply the result by goldValue.
5. the value of Net is put into a field called products_base_price
6. the calculated $US price is put into a field called products_price.
7. hit preview and it shows the $US price
8. hit insert/update and it puts the base price into the db as products_base_price (which is ignored by other product types :wink:) and the day's estimate $US price is put into the normal products_price field!
   The customer may now buy the item!
   Next step is to edit the code in admin > currencies > localization and put in an extra button called "Update prices of Gold products" which simply does a batch update of the entire products table, multiplying products_base_price by goldValue and putting the answer in each case into products_price

Remaining questions, how do I clean up the product_types table? can I just delete the extra product types (Document - General, Product - Music, etc)? I should also then delete the actual products in the products table. What else? I don't want to delete the rubbish and end up breaking zen cart! But also I don't want my store manager to accidentally set a $3000 gold ring as a downloadable document!

The other thing is, where will I begin to look for the code to edit to put an extra button into admin > localization > currencies? It will have a form post action I believe which when invoked will fire off my sql batch update statement.

Thanks so much

Found where to edit, and it was really easy to do.

admin/currencies.php
and just created a new form action.

My client's mind will be blown! :D

Sounds like you have done good ... :smile:

Might consider contributing the new Product Type ... ain't that a tricky idea ... :cool:

While you could delete product types, you never know when one might be handy for future use ...

You might consider setting the Product type on the Category to exclude things you do not use, instead ...

Edit the Category to see where you can set the allowed Product Types ...

How profoundly you talked! Make me sense!!!
keep us posted!!!:clap:

jewelry store

You're quite right, I should just "turn off" product types for certain categories, that's a very nice solution.

I would be more than happy to contribute my work if I knew how. The code is neat and works well and looks elegant on the screen. Two main worries are that I hard coded the text strings instead of using a language file (easy to fix) and I also hard coded the gold index (XAU) into my sql queries, eg. "select value, last_updated from currencies where code = 'XAU'" so that would need to be generalised to make it more useful. Also what if you have products that are based on gold and other products based on platinum?

Anyway, I'm happy to share

That is something that makes taking what you have done and tossing it into a play site of a test v1.3.8 and then when you get bored you fix a bad thang like "I hard coded" here and did this there ... and kind of polish it a bit, if you can ...

Or, put together what you have and any SQL to make this work, say ... this is where I got to and this is where it needs to be taken further ...

Add-ons and contributions have to grow from something ... :cool:

Ok, I'll do that. I can neaten it up and then submit it. Where do I submit it to? I'm an opensource virgin :blush:

In the Downloads is a submit button for add-ons ... thanks! :smile: