Reward Points Module for ZC v13x

[abcisme]

I don't think you will get an answer to your question because I asked the same question perhaps a month or two ago, I think the module is not setup for that so I got one of my family members write me a code which I run daily, it's not the perfect way but itworks, you could set it up as a cron job if you like to run it daily, here is the code below:

<?php

try {
$dbh = new PDO("mysql:host=localhost;dbname=DATABASE_NAME", 'DATABASE_NAME_Points', '1qwer13');

$Totalquery = "Update reward_customer_points
SET reward_points = 0.00
Where customers_id not in (select customers_id from reward_status_track)";


$Totalstmt = $dbh->prepare($Totalquery);
$Totalstmt ->execute();

echo 'Total Task Completed';

}
catch(PDOException $e)
{
echo $e->getMessage();
}

?>

Basically when you set up your reward points module pending points to expire in 30 days, the reward_status_track table keeps only 30 days of records so what the code does if it's not in the track table then it sets the points to Zero.

You have to run this daily though, if not then the deleted records will be lost and won't be able to zero the reward points that are older than 30 days.

I am sure some people here could make it much better and full proof and run it as a cron job.

I am not an expert in code writing but was able to figure things out to solve my problem.

Hope this helps,

I need points to expire 90 days after they are earned.

This code is a good start, but I can see a problem...

Say we want rewards to expire at 90 days.
Day 1: Customer places order. Earns 100 points.
Day 30: Customer places another order. Earns 100 points.
Day 90: Only 100 points should be removed, not all of their points, which is what this code would do.

Has anyone determined how to properly remove earned points after a certain time period?

[abcisme]

I need points to expire 90 days after they are earned.

This code is a good start, but I can see a problem...

Say we want rewards to expire at 90 days.
Day 1: Customer places order. Earns 100 points.
Day 30: Customer places another order. Earns 100 points.
Day 90: Only 100 points should be removed, not all of their points, which is what this code would do.

Has anyone determined how to properly remove earned points after a certain time period?

Ok, I am not a programmer, so I am taking a stab in the dark at this.. but looking at the housekeeping function, if I create a subset for housekeeping which specifies earned points instead of pending points.. then switched the housekeeping function to reflect rewards_points instead of pending_points, it should work to remove any earned points instead of pending points. Right?

So pending points will switch to earned after the sunrise period, then be removed after the housekeeping period... Does this look correct?

function CleanupRewardPointHistory()
{
    global $db,$messageStack;
    
    $subset="IFNULL((SELECT SUM(rp.reward_points) FROM ".TABLE_REWARD_STATUS_TRACK." rp WHERE cp.customers_id=rp.customers_id AND rp.status='".STATUS_PENDING."' AND rp.date<NOW()-INTERVAL %s DAY),0)";
$subset2="IFNULL((SELECT SUM(rp.reward_points) FROM ".TABLE_REWARD_STATUS_TRACK." rp WHERE cp.customers_id=rp.customers_id AND rp.status='".STATUS_PROCESSED."' AND rp.date<NOW()-INTERVAL %s DAY),0)";
    $sunrise_subset=sprintf($subset,REWARD_POINTS_SUNRISE_PERIOD);
    $housekeeping_subset=sprintf($subset2,REWARD_POINTS_HOUSEKEEPING);
    
    if(REWARD_POINTS_SUNRISE_PERIOD>0)
     if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." cp, ".TABLE_REWARD_STATUS_TRACK." rp SET cp.reward_points=cp.reward_points+".$sunrise_subset.",cp.pending_points=cp.pending_points-".$sunrise_subset." WHERE cp.customers_id=rp.customers_id;"))
      $db->Execute("UPDATE ".TABLE_REWARD_STATUS_TRACK." SET status=".STATUS_PROCESSED." WHERE status=".STATUS_PENDING." AND date<NOW()-INTERVAL ".REWARD_POINTS_SUNRISE_PERIOD." DAY;");
     
    if(REWARD_POINTS_HOUSEKEEPING>0)
     if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." cp, ".TABLE_REWARD_STATUS_TRACK." rp SET cp.reward_points=cp.reward_points-".$housekeeping_subset." WHERE cp.customers_id=rp.customers_id;"))

      $db->Execute("DELETE FROM ".TABLE_REWARD_STATUS_TRACK." WHERE date<NOW()-INTERVAL ".(int)REWARD_POINTS_HOUSEKEEPING." DAY;");
}

[speedyrider]

Hi,

in my site, when i change attributes the points dont change, please take a look: 66.147.242.164/~kiteaddi

[dmagic]

can share products ( facebook, twiiter ) and get points ?

[countrycharm]

can share products ( facebook, twiiter ) and get points ?

Could you be more clear in what you are asking. If your asking if someone shares your products on facebook, twiiter and receive reward points for doing that, the answer is no. You will have to hire someone to custom coded reward points to do that.

[speedyrider]

Hi,

I have a problem where if i enter in a product´s page it shows the current amount of points i should get for that product.

However when i actually add it to the cart it never adds the correct points. It doesnt matter if i set the ratio to 0.0, 0.5, to 2.0, it always adds the global ratio points (which is now set to 1).

My zen_reward_master table for a given category looks like this: point ratio (for category) = 0.5 (For example). Bonus points = null. redeem points = null. redeem ratio = null

Any ideas?

[Defkon1]

I have the following problem: in the product page the points are calculated on base price (wrong) and when I add it to the cart they are calculated on the subtotal (base price+taxes - right).

how to fix the points amount shown in product page?

thanks in advance

[abcisme]

Ok, I am not a programmer, so I am taking a stab in the dark at this.. but looking at the housekeeping function, if I create a subset for housekeeping which specifies earned points instead of pending points.. then switched the housekeeping function to reflect rewards_points instead of pending_points, it should work to remove any earned points instead of pending points. Right?

So pending points will switch to earned after the sunrise period, then be removed after the housekeeping period... Does this look correct?

function CleanupRewardPointHistory()
{
    global $db,$messageStack;
    
    $subset="IFNULL((SELECT SUM(rp.reward_points) FROM ".TABLE_REWARD_STATUS_TRACK." rp WHERE cp.customers_id=rp.customers_id AND rp.status='".STATUS_PENDING."' AND rp.date<NOW()-INTERVAL %s DAY),0)";
$subset2="IFNULL((SELECT SUM(rp.reward_points) FROM ".TABLE_REWARD_STATUS_TRACK." rp WHERE cp.customers_id=rp.customers_id AND rp.status='".STATUS_PROCESSED."' AND rp.date<NOW()-INTERVAL %s DAY),0)";
    $sunrise_subset=sprintf($subset,REWARD_POINTS_SUNRISE_PERIOD);
    $housekeeping_subset=sprintf($subset2,REWARD_POINTS_HOUSEKEEPING);
    
    if(REWARD_POINTS_SUNRISE_PERIOD>0)
     if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." cp, ".TABLE_REWARD_STATUS_TRACK." rp SET cp.reward_points=cp.reward_points+".$sunrise_subset.",cp.pending_points=cp.pending_points-".$sunrise_subset." WHERE cp.customers_id=rp.customers_id;"))
      $db->Execute("UPDATE ".TABLE_REWARD_STATUS_TRACK." SET status=".STATUS_PROCESSED." WHERE status=".STATUS_PENDING." AND date<NOW()-INTERVAL ".REWARD_POINTS_SUNRISE_PERIOD." DAY;");
     
    if(REWARD_POINTS_HOUSEKEEPING>0)
     if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." cp, ".TABLE_REWARD_STATUS_TRACK." rp SET cp.reward_points=cp.reward_points-".$housekeeping_subset." WHERE cp.customers_id=rp.customers_id;"))

      $db->Execute("DELETE FROM ".TABLE_REWARD_STATUS_TRACK." WHERE date<NOW()-INTERVAL ".(int)REWARD_POINTS_HOUSEKEEPING." DAY;");
}

So I guess I've messed up somewhere here. I'm hoping someone can help me figure this out.

I want to:
zen_reward_customer_points.reward_points - zen_reward_status_track.reward_points
WHEN
zen_reward_status_track date > 104 days ago
AND
zen_reward_status_track.customer_id = zen_reward_customer_points.customer_id

Then, I want to delete the record from zen_reward_status_track

Here's what I currently have:

$subset2="IFNULL((SELECT SUM(rp.reward_points) FROM ".TABLE_REWARD_STATUS_TRACK." rp WHERE cp.customers_id=rp.customers_id AND rp.status='1' AND rp.date<NOW()-INTERVAL 104 DAY),0)";

$housekeeping_subset=sprintf($subset2,REWARD_POINTS_HOUSEKEEPING);

if(REWARD_POINTS_HOUSEKEEPING>0)
    
if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." cp, ".TABLE_REWARD_STATUS_TRACK." rp SET cp.reward_points=cp.reward_points-".$housekeeping_subset." WHERE cp.customers_id=rp.customers_id;"))

$db->Execute("DELETE FROM ".TABLE_REWARD_STATUS_TRACK." WHERE date<NOW()-INTERVAL 104 DAY;");
}

This deletes the record from zen_reward_status_track, but doesn't delete the points from zen_reward_customer_points.reward_points

Can anyone help?

[giancalr]

Hello everyone, I installed the module works all regularly, but when I go to update the order to give the points to the customer, those performed with payment paypla not update, while those made with bank payment or payment types eltri updates all , is there any change to do for payments with paypal? Thank you

[giancalr]

I find solution in oscommerce forum, as would fit in zen cart?
Open catalog/includes/modules/payment/paypal_standard.php
----------------------------------------------------------
Find:
-----

global $cartID, $cart_PayPal_Standard_ID, $customer_id, $languages_id, $order, $order_total_modules;

Replace with this:
------------------

#### Points/Rewards Module V2.00 balance customer points BOF ####
// global $cartID, $cart_PayPal_Standard_ID, $customer_id, $languages_id, $order, $order_total_modules;
global $cartID, $cart_PayPal_Standard_ID, $customer_id, $languages_id, $order, $customer_shopping_points, $customer_shopping_points_spending, $order_total_modules;
#### Points/Rewards Module V2.00 balance customer points EOF ####*/

----------------------------------------------------------
Find:
-----

tep_db_perform(TABLE_ORDERS_TOTAL, $sql_data_array);
}

Add after:
----------

#### Points/Rewards Module V2.1rc2a balance customer points BOF ####
if ((USE_POINTS_SYSTEM == 'true') && (USE_REDEEM_SYSTEM == 'true')) {
// customer pending points added
if ($order->info['total'] > 0) {
$points_toadd = get_points_toadd($order);
$points_comment = 'TEXT_DEFAULT_COMMENT';
$points_type = 'SP';
if ((get_redemption_awards($customer_shopping_points_spending) == true) && ($points_toadd >0)) {
tep_add_pending_points($customer_id, $insert_id, $points_toadd, $points_comment, $points_type);
}
}
// customer referral points added
if ((tep_session_is_registered('customer_referral')) && (tep_not_null(USE_REFERRAL_SYSTEM))) {
$referral_twice_query = tep_db_query("select unique_id from " . TABLE_CUSTOMERS_POINTS_PENDING . " where orders_id = '". (int)$insert_id ."' and points_type = 'RF' limit 1");
if (!tep_db_num_rows($referral_twice_query)) {
$points_toadd = USE_REFERRAL_SYSTEM;
$points_comment = 'TEXT_DEFAULT_REFERRAL';
$points_type = 'RF';
tep_add_pending_points($customer_referral, $insert_id, $points_toadd, $points_comment, $points_type);
}
}
// customer shoppping points account balanced
if ($customer_shopping_points_spending) {
tep_redeemed_points($customer_id, $insert_id, $customer_shopping_points_spending);
}
}
#### Points/Rewards Module V2.1rc2a balance customer points EOF ####*/

----------------------------------------------------------
Find:
-----

tep_session_unregister('shipping');
tep_session_unregister('payment');
tep_session_unregister('comments');

Add after:
----------

tep_session_unregister('customer_shopping_points');// Points/Rewards Module v1.10
tep_session_unregister('customer_shopping_points_spending');// Points/Rewards Module v1.10
tep_session_unregister('customer_referral');// Points/Rewards Module V2.00

----------------------------------------------------------
That's all
----------