Post by cdamianou » Fri Sep 23, 2011 4:41 am

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.
Last edited by cdamianou on Fri Sep 23, 2011 5:25 pm, edited 1 time in total.

http://www.magictao.co.uk


Active Member

Posts

Joined
Tue Aug 09, 2011 2:13 pm

Post by uksitebuilder » Fri Sep 23, 2011 4:43 am

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.

ImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by cdamianou » Fri Sep 23, 2011 4:53 am

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.
Last edited by cdamianou on Fri Sep 23, 2011 12:36 pm, edited 1 time in total.

http://www.magictao.co.uk


Active Member

Posts

Joined
Tue Aug 09, 2011 2:13 pm

Post by uksitebuilder » Fri Sep 23, 2011 5:04 am

That doesn't quite add up

If it's retail divided by 5 - surely that's 1 point = 20p

ImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by cdamianou » Fri Sep 23, 2011 12:37 pm

Sorry retyped. I missed a the decimal points. It should read 0.05

http://www.magictao.co.uk


Active Member

Posts

Joined
Tue Aug 09, 2011 2:13 pm

Post by rph » Fri Sep 23, 2011 1:35 pm

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.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by rph » Fri Sep 23, 2011 3:09 pm

If the redemption rate is 20 points per 1 unit currency just set (p.price * 5) to (p.price * 20).

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by cdamianou » Fri Sep 23, 2011 3:37 pm

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.

http://www.magictao.co.uk


Active Member

Posts

Joined
Tue Aug 09, 2011 2:13 pm

Post by uksitebuilder » Fri Sep 23, 2011 4:59 pm

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);

ImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by uksitebuilder » Fri Sep 23, 2011 5:02 pm

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);

ImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by cdamianou » Fri Sep 23, 2011 5:25 pm

THANK YOU uksitebuilder and rph. This worked. Your help has been much appreciated.

Great support from a great community.

http://www.magictao.co.uk


Active Member

Posts

Joined
Tue Aug 09, 2011 2:13 pm

Post by hbg » Sun Nov 13, 2011 9:23 am

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.

Opencart Version 1.5.1.3
https://www.opencart.homebrewinn.com.au/shop/


User avatar
hbg
Active Member

Posts

Joined
Wed Apr 13, 2011 8:52 am
Location - Newcastle NSW Australia

Post by speedingorange » Wed Dec 28, 2011 7:32 pm

Can someone advise how I would set all products to have 0 reward points?

Active Member

Posts

Joined
Tue Feb 23, 2010 7:33 pm

Post by hbg » Tue Jan 10, 2012 6:05 am

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/


User avatar
hbg
Active Member

Posts

Joined
Wed Apr 13, 2011 8:52 am
Location - Newcastle NSW Australia

Post by cartgirl » Wed Oct 03, 2012 4:41 am

Is there any way to edit the # of rewards points (p.price * 5) without using phpMyAdmin?

Active Member

Posts

Joined
Fri Aug 03, 2012 10:12 pm

Post by hbg » Wed Oct 03, 2012 10:14 am

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.

Opencart Version 1.5.1.3
https://www.opencart.homebrewinn.com.au/shop/


User avatar
hbg
Active Member

Posts

Joined
Wed Apr 13, 2011 8:52 am
Location - Newcastle NSW Australia

Post by Impaler » Wed May 15, 2013 7:09 pm

this topic saved me a lot of time and effort.

thank you very much for those who participated and posted solutions. :)

New member

Posts

Joined
Fri Aug 24, 2012 3:11 am
Who is online

Users browsing this forum: No registered users and 42 guests