Page 1 of 1
Best way to add reward points to over 1000 products - SOLVED
Posted: Fri Sep 23, 2011 4:41 am
by cdamianou
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.
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 4:43 am
by uksitebuilder
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.
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 4:53 am
by cdamianou
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.
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 5:04 am
by uksitebuilder
That doesn't quite add up
If it's retail divided by 5 - surely that's 1 point = 20p
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 12:37 pm
by cdamianou
Sorry retyped. I missed a the decimal points. It should read 0.05
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 1:35 pm
by rph
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:
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);
Be sure to
BACK UP YOUR DATABASE before running this.
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 3:09 pm
by rph
If the redemption rate is 20 points per 1 unit currency just set (p.price * 5) to (p.price * 20).
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 3:37 pm
by cdamianou
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.
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 4:59 pm
by uksitebuilder
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);
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 5:02 pm
by uksitebuilder
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:
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);
Re: Best way to add reward points to over 1000 products
Posted: Fri Sep 23, 2011 5:25 pm
by cdamianou
THANK YOU uksitebuilder and rph. This worked. Your help has been much appreciated.
Great support from a great community.
Re: Best way to add reward points to over 1000 products
Posted: Sun Nov 13, 2011 9:23 am
by hbg
cdamianou wrote:THANK YOU uksitebuilder and rph. This worked. Your help has been much appreciated.
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.
Re: Best way to add reward points to over 1000 products - SO
Posted: Wed Dec 28, 2011 7:32 pm
by speedingorange
Can someone advise how I would set all products to have 0 reward points?
Re: Best way to add reward points to over 1000 products - SO
Posted: Tue Jan 10, 2012 6:05 am
by hbg
By default they normally would be blank. Otherwise maybe set the values above to '0' instead of 1.2 * 24?
Re: Best way to add reward points to over 1000 products - SO
Posted: Wed Oct 03, 2012 4:41 am
by cartgirl
Is there any way to edit the # of rewards points (p.price * 5) without using phpMyAdmin?
Re: Best way to add reward points to over 1000 products - SO
Posted: Wed Oct 03, 2012 10:14 am
by hbg
cartgirl wrote:Is there any way to edit the # of rewards points (p.price * 5) without using phpMyAdmin?
Unless there is a module, I don't think there is.
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.
Re: Best way to add reward points to over 1000 products - SO
Posted: Wed May 15, 2013 7:09 pm
by Impaler
this topic saved me a lot of time and effort.
thank you very much for those who participated and posted solutions.
