Reward Points Module for ZC v13x

hem:

Nevermind, found it. Okay, all the Reward Point tables have been restored to the last backup.

I am now getting an error message at the bottom of the customers>reward points screen.

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND status=0 ORDER BY date DESC LIMIT 12' at line 1
in:
[SELECT rewards_id, orders_id, date, reward_points, status FROM reward_status_track WHERE customers_id= AND status=0 ORDER BY date DESC LIMIT 12;]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Do you know what this is about?

Actually, I fixed it by running the update SQL again. I hope that didn't screw anything up! I am also having an issue with reward points not going straight to earned. I have the sunrise period set to zero, so not sure what else I need to fix. Also, still rewarding points for free gifts even though I have it set to earn points depending on order total. Any ideas?

hem:

Check this line in the RPD:

Try reinstalling upgrade.sql
Believe me i have done that a few time

Rasyr:

Is there any way to make it so that the maximum amount of Reward Points redeemed cannot exceed the subtotal?

This is built in if I recal correctly. Otherwise, there is a bit of code you could just add in to make this right.

lauriesscraps:

Actually, I fixed it by running the update SQL again. I hope that didn't screw anything up! I am also having an issue with reward points not going straight to earned. I have the sunrise period set to zero, so not sure what else I need to fix. Also, still rewarding points for free gifts even though I have it set to earn points depending on order total. Any ideas?

Set Reward Point Mode to 1 .

Also either set your payments initial order status to something other than Pending or go to Admin->Reward Points and set Reward Point Status Track to Advanced and change all the statuses to Earned

nigelt74:

Believe me i have done that a few time

Okay, try going to Admin->Tools->Install SQL Patch and copy the following into the query box:

REPLACE INTO `configuration` 
(`configuration_id` ,`configuration_title` ,`configuration_key` ,`configuration_value` ,`configuration_description` ,`configuration_group_id` ,`sort_order` ,`last_modified` ,`date_added` ,`use_function` ,`set_function`)
VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.<br />0= Against the subtotal.<br />1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');

Let me know if you get any error

hem:

Okay, try going to Admin->Tools->Install SQL Patch and copy the following into the query box:

REPLACE INTO configuration
(configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function)
VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.<br />0= Against the subtotal.<br />1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');

> 
> Let me know if you get any error

yep get an error

1048 Column 'configuration_group_id' cannot be null
in:
[REPLACE INTO zen_configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.
0= Against the subtotal.
1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

nigelt74:

yep get an error

1048 Column 'configuration_group_id' cannot be null
in:
[REPLACE INTO zen_configuration (configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function) VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.
0= Against the subtotal.
1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Ah sorry, forgot to retrieve the @group_id. Try this:

SET @group_id = '';
SELECT (@group_id := `configuration_group_id`) FROM `configuration_group` WHERE `configuration_group_title` LIKE 'Reward Points';

REPLACE INTO `configuration` 
(`configuration_id` ,`configuration_title` ,`configuration_key` ,`configuration_value` ,`configuration_description` ,`configuration_group_id` ,`sort_order` ,`last_modified` ,`date_added` ,`use_function` ,`set_function`)
VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.<br />0= Against the subtotal.<br />1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');

First of all, awesome module, really loving it!

Second, I have a question to limit the issuance of points..

I love being able to issue rewards points for goods that are according to Standard Retail Price (SRP), however when the product is discounted, I would not want to offer rewards points..

Basically, the way we have it in mind is that if a product is charged according to SRP, we'd give free shipping, rewards points and the ability to use certain payment options.. However, if we're providing a discounted rate (which actually means, we're charging them at whole-sale price), we'd not want to offer any benefits, in order for us not to go negative on our profits..

Is there any way right now to implement this?

mask2011:

First of all, awesome module, really loving it!

Second, I have a question to limit the issuance of points..

I love being able to issue rewards points for goods that are according to Standard Retail Price (SRP), however when the product is discounted, I would not want to offer rewards points..

Basically, the way we have it in mind is that if a product is charged according to SRP, we'd give free shipping, rewards points and the ability to use certain payment options.. However, if we're providing a discounted rate (which actually means, we're charging them at whole-sale price), we'd not want to offer any benefits, in order for us not to go negative on our profits..

Is there any way right now to implement this?

The easiest way would be to put all your discounted items into a Category called Discounts and then go to Admin->Categories->Reward Points and set the Redeem value of that category to 0.

Actually, in my store it works a bit different.. Discounted products would be normal products (showing as SRP products on my site) but my members will have discount coupons to enter during check-out to enjoy the lower price..

So, in this case, is there a way to limit rewards accrual (and maybe even redemption) only on sales that do not have a discount coupon applied to it?

hem:

Ah sorry, forgot to retrieve the @group_id. Try this:

SET @group_id = '';
SELECT (@group_id := configuration_group_id) FROM configuration_group WHERE configuration_group_title LIKE 'Reward Points';

REPLACE INTO configuration
(configuration_id ,configuration_title ,configuration_key ,configuration_value ,configuration_description ,configuration_group_id ,sort_order ,last_modified ,date_added ,use_function ,set_function)
VALUES (NULL , 'Allow Redeem of Reward Points on Order Total or Subtotal', 'REWARD_POINTS_ALLOW_TOTAL', '0', 'Allow points to be redeemed against the full order (including shipping) or only against the subtotal.<br />0= Against the subtotal.<br />1= Against the full order.', @group_id, '9', NULL, now(), NULL , 'zen_cfg_select_option(array(''0'', ''1''), ');

Nope still get the same issue, however it seems to work fine.

and on a completely different note and probably not overly important

Is there a way to

a) reset all rewards point to the global value OR
b) a way to find only the categories products that have rewards points set on them

I have just realised that on a particular store there are around 3-400 categories, subcategories, and I can guarantee I'll be the one expected to track down the errant rewards points when the store owner forgets where she has applied them

mask2011:

Actually, in my store it works a bit different.. Discounted products would be normal products (showing as SRP products on my site) but my members will have discount coupons to enter during check-out to enjoy the lower price..

So, in this case, is there a way to limit rewards accrual (and maybe even redemption) only on sales that do not have a discount coupon applied to it?

If you are using an Order Total mod to handle the discount coupons go to Admin->Config->Reward Points and select Advanced Reward Point Calculation Rules. Add a rule to subtract on that mod.

nigelt74:

and on a completely different note and probably not overly important

Is there a way to

a) reset all rewards point to the global value OR
b) a way to find only the categories products that have rewards points set on them

I have just realised that on a particular store there are around 3-400 categories, subcategories, and I can guarantee I'll be the one expected to track down the errant rewards points when the store owner forgets where she has applied them

Do you have PhpMyAdmin installed on your/her site? If so use the following SQL:

SELECT `cd`.`categories_name` , r.`point_ratio`
FROM `reward_master` AS r, `categories_description` AS cd
WHERE `r`.`scope` =1
AND `r`.`scope_id` = `cd`.`categories_id`;

Note: you may have to change the table names if you use a prepend like 'zen_'

Hey Andrew,

First of all, thanks so much for supporting people here to get the mod to do what we want it to. You're really a wonderful help!

As for the suggestion of adding an Advanced Rule to subtract the ot_discount module, that worked, but only partially.. It does deduct some points, but not all. Let me try and illustrate:

• Customer A orders Product B at SRP $79.90
• Customer A checks out without a discount coupon and gets 80 Rewards Points

The above works excellent..

However, what I would like is:

• Customer A orders Product B at SRP $79.90
• Customer A checks out with discount coupon and pays only $60
• Customer A will not get any Rewards Points for this order

At this moment, Customer A gets 60 Rewards Points because thats the value of his order. But I would want it to not give any points.. Is there any way I could update the configuration to do that? Or is there some place in the code where I could apply the change to make this work?

Appreciate your suggestions!

mask2011:

Hey Andrew,

First of all, thanks so much for supporting people here to get the mod to do what we want it to. You're really a wonderful help!

As for the suggestion of adding an Advanced Rule to subtract the ot_discount module, that worked, but only partially.. It does deduct some points, but not all. Let me try and illustrate:

• Customer A orders Product B at SRP $79.90
• Customer A checks out without a discount coupon and gets 80 Rewards Points

The above works excellent..

However, what I would like is:

• Customer A orders Product B at SRP $79.90
• Customer A checks out with discount coupon and pays only $60
• Customer A will not get any Rewards Points for this order

At this moment, Customer A gets 60 Rewards Points because thats the value of his order. But I would want it to not give any points.. Is there any way I could update the configuration to do that? Or is there some place in the code where I could apply the change to make this work?

Appreciate your suggestions!

What happens if the customer "mixes and matches"- has an order with some discounted items and some regular? Do they receive any points in this situation?

hehe good point :) Logically, they should receive points for those items that are SRP, however that might be very difficult to track.

As most of the coupons are general coupons, with some of them having a "product restriction" on them, I think it would be extremely difficult (technically) to ensure that points are allocated for those products that are without a discount-coupon applied to them and give no points for discounted products.. Zen Cart doesnt allow you to apply multiple coupons to a cart, so if I were to add 2 products that have a discount option, and I would apply 2 coupons, it would only take the second coupon and apply it to my cart.

I would recommend my customers to create multiple orders in this case. For those cases where the customer does create a single order with a mixed (discount and non-discount) products set, I would assume the easiest way is to either split the order or perhaps give no points on check-out and do a manual allocation of points to that customer for the non-discounted products.

So, short answer: if the code is modular enough and written in such a way that it checks by product, it'd be wonderful.. If it is not, I am totally fine to say: apply a coupon and lose any points for products in your cart.. :)

I've installed this mod and it's awesome... exactly what I'm looking for.

I would like to know if there's any way to add "advanced rules" to the points displaying in the sidebox. I have a number of order total modules (better together, group discount, etc.) and I have enabled advanced rules in Configuration > Rewards points so I get the correct total of reward points after discounts are applied. But they show the original number of reward points in the sidebox.

Do you guys know how to fix this?

@hem: did u happen to have any additional suggestions that might make it possible to provide no rewards points for discounted products? perhaps I could hard-code it somewhere in the code? Like if ot_coupon is applied, rewards = 0 or something as rough as that?