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.
That doesn't quite add up
If it's retail divided by 5 - surely that's 1 point = 20p
If it's retail divided by 5 - surely that's 1 point = 20p
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 42 guests
