Sharni:
I have tried searching this rather large thread for answers, but none of my key words get a result. So here goes.
I just installed this module on my cart. My business has been in operation for 2 years now, and I have a lot of loyal customers. I would like to give my existing customers reward points to match the orders that they have made. Is there a simple way to do this?
Thanks
Sharni
I couldn't find an answer, so I wrote one. Here is the code. You should copy this code and create a .php file with it. Place that file on your web server and replace the top line for connections with your own DB connection info. Execute the script by accessing it through your browser.
I have our sunrises period in the admin set to 31 so I wrote this to check whether the order is older than 30 days. You may adjust this to meet your needs.
This comes with NO warranty or guarantee. It did exactly what I needed it to though. The rewards points module creates 3 tables, one of which is only used to track the reward ratio of different categories, products, groups etc. . .
The additional two tables are what's important here. These tables are properly addressed in my lil' script.
Enjoy! John
BACKUP YOUR DB!!!
<?php
// hour minute second month day year
require 'connections.php';
$sunrises = (30 * 24 * 60 * 60); //days x hours x minutes x seconds
$now = time();
$issue_rewards = array();
$query = mysql_query("SELECT * FROM orders_total WHERE class ='ot_subtotal'");
$cID = '';
$date = '';
while($row = mysql_fetch_assoc($query)){
$updates = array();
$query2 = mysql_query("SELECT * FROM orders_status_history WHERE orders_id ='$row[orders_id]'");
while($row2 = mysql_fetch_assoc($query2)){
$updates[] = $row2['orders_status_id'];
}
if(in_array('3', $updates) && !in_array('5', $updates) && !in_array('6', $updates) && !in_array('111', $updates) && !in_array('107', $updates) && !in_array('109', $updates)){
$round_val = round($row['value'], PHP_ROUND_HALF_DOWN);
//2008-01-18 18:16:51
$o_get_date_time = mysql_query("SELECT * FROM orders WHERE orders_id ='$row[orders_id]'");
while($this_date_time = mysql_fetch_assoc($o_get_date_time)){
$dt_parts = explode(' ', $this_date_time['date_purchased']);
$dt_date = $dt_parts[0];
$dt_time = $dt_parts[1];
$cID = $this_date_time['customers_id'];
$date = $this_date_time['date_purchased'];
}
$d_parts = explode('-', $dt_date);
$mo = $d_parts[1]; $da = $d_parts[2]; $yr = $d_parts[0];
$t_parts = explode(':', $dt_time);
$hr = $t_parts[0]; $mn = $t_parts[1]; $se = $t_parts[2];
$odate_secs = mktime($hr, $mn, $se, $mo, $da, $yr);
//$recompile = $yr.'-'.$mo.'-'.$da.' '. $hr.':'.$mn.':'.$se;//testing here
$date_math = $now - $odate_secs;
if($date_math < $sunrises){
//insert these points as pending
echo $count . ": OK! Order ID: " . $row['orders_id'] . ' Will Get: ' . $round_val . ' ' . $recompile . ' PENDING<br />';
mysql_query("INSERT INTO reward_status_track (rewards_id,customers_id,orders_id,date,reward_points,status) VALUES ('',$cID,$row[orders_id],'$date',$round_val,'0')") or die(mysql_error());
$exisitng_check = mysql_query("SELECT * FROM reward_customer_points WHERE customers_id ='$cID'") or die(mysql_error());
$exis_chk_nums = mysql_num_rows($exisitng_check);
if($exis_chk_nums < 1){
mysql_query("INSERT INTO reward_customer_points (customers_id,reward_points,pending_points) VALUES ($cID,'0',$round_val)") or die(mysql_error());
}else{
while($existing_record = mysql_fetch_assoc($exisitng_check)){
//$existing_rp = $existing_record['reward_points'];
$existing_pp = $existing_record['pending_points'];
}
//$new_rp = $existing_rp + $round_val;
$new_pp = $existing_pp + $round_val;
mysql_query("UPDATE reward_customer_points SET pending_points=$new_pp WHERE customers_id=$cID") or die(mysql_error());
}
}else{
//insert these points as earned
echo $count . ": OK! Order ID: " . $row['orders_id'] . ' Will Get: ' . $round_val . ' ' . $recompile . ' EARNED!<br />';
mysql_query("INSERT INTO reward_status_track (rewards_id,customers_id,orders_id,date,reward_points,status) VALUES ('',$cID,$row[orders_id],'$date',$round_val,'1')") or die(mysql_error());
$exisitng_check = mysql_query("SELECT * FROM reward_customer_points WHERE customers_id ='$cID'") or die(mysql_error());
$exis_chk_nums = mysql_num_rows($exisitng_check);
if($exis_chk_nums < 1){
mysql_query("INSERT INTO reward_customer_points (customers_id,reward_points,pending_points) VALUES ($cID,$round_val,'0')") or die(mysql_error());
}else{
while($existing_record = mysql_fetch_assoc($exisitng_check)){
$existing_rp = $existing_record['reward_points'];
//$existing_pp = $existing_record['pending_points'];
}
$new_rp = $existing_rp + $round_val;
//$new_pp = $existing_pp + $round_val;
mysql_query("UPDATE reward_customer_points SET reward_points=$new_rp WHERE customers_id=$cID") or die(mysql_error());
}
}
}
}
?>
