Reward Point Mod beta now available

[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',
)

[hem]

Fields

rewards_products_id: General unique record identifier.

scope: Can be '0' for Global (all products), '1' for 'Category' or '2' for 'Product'.

scope_id: If scope is set to Category or Product then this contains the categories_id or products_id of the record it relates to. It is set to 0 for Global.

point_ratio: The ratio of reward points to base price. If you had a product costing €15 and the ratio was set to 1 then you will recieve 15 reward points. If the ratio is set to 2 then you would get 30 reward points. If it was 0.5 then you would get 8 reward points (7.5 rounded up). The calculation is: reward_points=products_price * reward_point_ratio.

bonus_points: For future development.

Indexes:

PRIMARY KEY ( `rewards_products_id` ) : The primary index.

UNIQUE `unique_id` ( `scope` , `scope_id` ): Only one record of a specific scope/scope_id is allowed to exist.

The system works on a fallback basis. If a product has a corresponding reward point record (that is the scope is '2' and the scope_id==product_id) then that records ratio is used. Otherwise if the category has a corresponding record (scope='1' and scope_id=category_id) then that ratio gets used. Otherwise the Global (default) ratio is used.