Reward Point Mod beta now available

[hem]

The way I would effect a sunrise program is using the log; the log (rather than the reward_customer_points table) would be the medium for determining the current customer balance. The default would be simply to get all log records for that customer older than (say) 30 days. I wouldn't write a log record until the order had moved to (say) processing state, which could obviously be delayed for some payment methods; alternately, the log could store a state for each row, which would be something other than "available" for points accumulated by a transaction which hasn't settled.

I've created a reward_points_audit table which tracks customer and order ID, reward points, date added and a status. The status can be 'pending', 'processed', 'cancelled' or 'redeemed'. A change to the status will update the customer reward points record which I am going to keep in order to keep the number of MySQL transactions/load down when just doing things like displaying the current total.

I've just got to write the Admin side of the audit trail and then I'll put it up for download.

[hem]

I can see the pro's and cons of the points maturity issue. One more thing to consider with the redemption is the fact that most retailers that use the rewards or point system only send the consumer the redemption once a quarter. This would deter some of the fraud activity with cod orders and such. My thought would be to let the point acrue for at least 30 days and then be released at least 2 weeks after that periods cut off date.

This is why I'm thinking that the redeem side should be further mods with the parent mod including some basic redeem functionality.

[yellow1912]

I have got permission from hem and numinix to merge 2 current reward point modules together:
The new project can be found at:
http://code.google.com/p/zencart-reward-point/

PM me if you want to join the developer team.

I will start merging code as soon as I can.

yellow1912

[yellow1912]

Okie, after looking at both modules, here is some food for thought:

1. hem did a good job using observer class. Will use that instead of hacking into the checkout page
2. I think it's better to put the point into the log table first, with status 0 (not approved), then do a check and update the total reward point in the reward point table.
Reason:
Depending on payment methods etc, we may not want to approve the point right away. Also, point should be associated with order number, so that we can later approve/remove points etc when we change order status

More to come later.

[hem]

Yellow- just a bit of an update on this:

With the feedback I've had here I've already started coding an audit trail. Points will now not automatically be added to a customers Reward Points pool. They will be added to a "Points Pending" until either the status of the order changes or a "sunrise period" passes- Both to be admin settable. Should have it finished by the end of the week.

[yellow1912]

Im trying to merge things together asap, so that we can work on the same code and dont have to worry about other things.

Yellow- just a bit of an update on this:

With the feedback I've had here I've already started coding an audit trail. Points will now not automatically be added to a customers Reward Points pool. They will be added to a "Points Pending" until either the status of the order changes or a "sunrise period" passes- Both to be admin settable. Should have it finished by the end of the week.

[yellow1912]

hem, more comments in your code would be appreciated, I'm having a hard time following the flow.

Anyhow, can you clarify the content of each field inside this table:

CREATE TABLE `reward_product_points` (
`rewards_products_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`scope` INT( 1 ) NOT NULL DEFAULT '0',
`scope_id` INT( 11 ) NOT NULL DEFAULT '0',
`point_ratio` DOUBLE( 15, 4 ) NOT NULL DEFAULT '1',
`bonus_points` DOUBLE( 15, 4 ) NULL,
PRIMARY KEY ( `rewards_products_id` ) ,
UNIQUE `unique_id` ( `scope` , `scope_id` ));

Im thinking of 1 or 2 tables:
1 would hold category specific point ratio
1 would hold product specific point ratio
(can merge the 2, but make more sense to use 2 instead of 1)
and we also have a global ratio

The point bonus will be calculated using product specific ratio first, if not available then category specific, if not then global.

The log table will then store
products_id
orders_id
ratio_applied
points_applied
status
created_on
modified_on

stuffs like that. It will be easy to re-calculate the point when users return a specific products etc,


Regards.

[yellow1912]

Proposed table structure:

# use this table name since we want to use the code from store credit module
CREATE TABLE sc_customer (
  `customer_id` int(11) NOT NULL,
  `amount` float NOT NULL default '0',
  `created_on` int(11) NOT NULL DEFAULT '0',
  `modified_on` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ( `customers_id` )
);

# holding category specific ratio
CREATE TABLE sc_categories_ratio (
  `categories_id` int(11) NOT NULL,
  `ratio` float NOT NULL DEFAULT '1',
   PRIMARY KEY ( `categories_id` )
);

# holding products specific ratio
CREATE TABLE sc_categories_ratio (
  `products_id` int(11) NOT NULL,
  `ratio` float NOT NULL DEFAULT '1',
   PRIMARY KEY ( `products_id` )
);

# log table
CREATE TABLE sc_logs (
  `products_id` int(11) NOT NULL,
  `orders_id` int(11) NOT NULL,
  `ratio` float NOT NULL,
  `amount` float NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0' ,
  `created_on` int(11) NOT NULL DEFAULT '0',
  `modified_on` int(11) NOT NULL DEFAULT '0',
)

[yellow1912]

hem, Im working my way thru yours and numinix's code, make progress bit by bit, for instances:
1. I think it's better to name the mod "store credit", since owners can just assign customers additional store credit for refund-etc.... (which is also a nice feature later, refund go to store credit instead of cash/cc/....)

2. Make changes to observer class, let it just simply do the call, it should not add credit or anything
http://code.google.com/p/zencart-rew...ore_credit.php

3. Moving functions into class, breaking them down into smaller functions.
The idea is that this class can be re-used on admin side to retrieve and edit credit as well.
http://code.google.com/p/zencart-rew...ore_credit.php

[yellow1912]

Also, a product can belong to multiple categories, which cat will be used to calculate ratio? Master cat?