Reward Point Mod beta now available

[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?

[hem]

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

I have it set to work in the customers favour a select the ratio which returns the best ratio at the moment. This may be subject to change.

[hem]

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/....)

This is why I think my mod and Numinix's are different. I'm writing a Reward Point mod and Numinix is writing a Store Credit mod. True there is some overlap but I think at the end of the day there is enough differences to make them two separate entities.

[yellow1912]

At the end of the day, we want to use a single table to store the credit, which can be applied using a single field. That's why I think it's appropriate to merge the 2

[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.