Hi
I was wondering, what is the best way to add reward points to over 1000 products? Doing each product manually will be a pain.
Your suggestions are much appreciated.
is it all your products ?
Does each product have the same reward points or is there some formula that you use based on the selling price ?
This could be done with a bit of script to mass update the database.
Does each product have the same reward points or is there some formula that you use based on the selling price ?
This could be done with a bit of script to mass update the database.
Hi uksitebuilder
All the reward points are based on the retail. So the reward points given are for example 1 points per £1, and rewards needed to buy the product is retail price devided by 0.05. So 1 reward point is worth 5p.
Hope this helps.
All the reward points are based on the retail. So the reward points given are for example 1 points per £1, and rewards needed to buy the product is retail price devided by 0.05. So 1 reward point is worth 5p.
Hope this helps.
Last edited by cdamianou on Fri Sep 23, 2011 12:36 pm, edited 1 time in total.
If you're only using the Default customer group included with the OpenCart installation (e.g. you're not using the Wholesale group) you can run the following MySQL query in phpMyAdmin:
Be sure to BACK UP YOUR DATABASE before running this.
Code: Select all
TRUNCATE TABLE `product_reward`;
INSERT INTO `product_reward` (product_id) SELECT product.product_id AS product_id FROM `product`;
UPDATE `product_reward` pr LEFT JOIN `product` p ON (pr.product_id = p.product_id) SET pr.customer_group_id = '8', pr.points = ROUND(p.price * 5);-Ryan
Hi rph, I used the query and realised the p.price is for the rewards rewarded so I changed it to (p.price*1) as they get 1 reward for every £1
What is missing now is the rewards needed to by the product. That filled is still blank.
I would like each reward point to be worth 5p. So for every £1 they need 20 reward points.
Hope this makes sense.
What is missing now is the rewards needed to by the product. That filled is still blank.
I would like each reward point to be worth 5p. So for every £1 they need 20 reward points.
Hope this makes sense.
Here you go mate
Code: Select all
TRUNCATE TABLE `product_reward`;
INSERT INTO `product_reward` (product_id) SELECT product.product_id AS product_id FROM `product`;
UPDATE `product_reward` pr LEFT JOIN `product` p ON (pr.product_id = p.product_id) SET pr.customer_group_id = '8', pr.points = ROUND(p.price * 1);
UPDATE `product` SET points = ROUND(price*20);
note, the above assumes you are not vat registered as it works on the ex-vat price
If you want to it to work on prices including vat
do the following instead:
If you want to it to work on prices including vat
do the following instead:
Code: Select all
TRUNCATE TABLE `product_reward`;
INSERT INTO `product_reward` (product_id) SELECT product.product_id AS product_id FROM `product`;
UPDATE `product_reward` pr LEFT JOIN `product` p ON (pr.product_id = p.product_id) SET pr.customer_group_id = '8', pr.points = ROUND(p.price * 1.2);
UPDATE `product` SET points = ROUND(price*24);THANK YOU uksitebuilder and rph. This worked. Your help has been much appreciated.
Great support from a great community.
Great support from a great community.
Same here, could be potential for a mod for the inexperienced. The only things I had some difficulty with was having it run with a second customer group. It seems to work with either one or the other (not both). Also, it didn't reflect special prices. I wish I had this a few months ago.cdamianou wrote:THANK YOU uksitebuilder and rph. This worked. Your help has been much appreciated.
Great support from a great community.
Opencart Version 1.5.1.3
https://www.opencart.homebrewinn.com.au/shop/
By default they normally would be blank. Otherwise maybe set the values above to '0' instead of 1.2 * 24?
Opencart Version 1.5.1.3
https://www.opencart.homebrewinn.com.au/shop/
Unless there is a module, I don't think there is.cartgirl wrote:Is there any way to edit the # of rewards points (p.price * 5) without using phpMyAdmin?
Otherwise, there maybe a way of exporting it into an XLS file. I remember creating a formula. then copying the values into the reward section. However, the PHP method was quite easy, and quick.
Opencart Version 1.5.1.3
https://www.opencart.homebrewinn.com.au/shop/
Who is online
Users browsing this forum: No registered users and 60 guests
