User Guide | Reward Point and Voucher Configuration

flexi cart contains many features to aid the custom development of an e-commerce site.
In some instances, some of the features may be considered overkill, or may not require a database table to handle a feature.
In these cases, specific database tables can be disabled, or with some tables, specific columns can be disabled if not required.

In addition to this, the database tables and columns can be renamed to match the custom naming conventions.

Reward Point and Voucher Configuration

Reward Function Index | Get Reward Session Data | Get Reward Helper Data | Reward Admin Data
Config Setup Information

Table Schema Diagram | Setup Notes

Table and Config File Settings

Reward Points Pseudo Table | Reward Points Converted Table

Help with the Table Configuration

Show / Hide Help

Internal Name: The name that flexi cart internally references the table or column by.

Default Name: The default table or column name used in the actual table.

Data Type: The data type that is expected by the table column.

  • bool : Requires a boolean value set as either '0' (FALSE) or '1' (TRUE).
  • string : Requires a textual value.
  • int : Requires a numeric value. It does not matter whether the value is an integer, float, decimal etc.


Config File Location

The config file is located in CodeIgniters 'config' folder and is named 'flexi_cart.php'.

Reward Points Table Schema Diagram

A database table schema diagram, showing how the reward points tables are related to each other and the discount tables.
Note: The reward points table does not actually exist, it is a SQL SELECT query joining data from the order summary and order details tables. Table and columns names are defined using their internal names.

General Reward Point and Voucher Setup Notes

Below are some general notes to consider when setting up and managing the reward point and voucher tables.

  • The reward points table does not actually exist as a real table in the database, it is simply an SQL query that uses columns joined from the order tables to track how many reward points a user has earnt.
  • As the reward points are directly related to the order tables, if any records are deleted from these tables, the associated reward points will also be deleted.
  • If the order tables are setup to track the quantity of 'shipped' and 'cancelled' items from customer orders, then the allocation of reward points are directly related to these columns.

    If an item is marked as shipped, the associated reward points are activated for use after a defined number of days, which is recommended as being the number of days stated on the items return policy.

    The purpose of only activating the items outside of the return policy date, is to prevent customers from purchasing items soley to earn reward points that they can then spend on other items. Once they have bought the other items, they can then return the initially purchased item for a refund.

    If an item is cancelled after a customer has converted the related reward points to a reward voucher, the cart cannot deduct the associated reward points as they have been converted. Instead, the cart deducts the outstanding points from any other points the customer has earnt, if they do not have enough, their reward point total goes into a negative value so that any reward points earnt in future will repay the outstanding amount.

  • If the above mentioned 'shipped' and 'cancelled' columns are not used, the reward points are allocated the same number of defined days after the date the order was placed. To cancel the associated reward points from the order, the whole order would need to be cancelled using the order status column.

Reward Points Pseudo Table

Contains data on reward points that have been earnt by registered users when completing an order.

The reward points table does not actually exist, the data returned from reward point functions are a combination of joined data from the order summary and order details tables, with then a set of pseudo columns that are created within the SQL queries and returned as alias columns.

Itemised Pseudo Column Names
Help
Internal Name Default Name Data Type Description
row_points_total row_points_total int The total reward points earnt from one row of the order detail table.
row_points_pending row_points_pending int The total reward points from one row of the order detail table that are pending activation.
row_points_active row_points_active int The total reward points from one row of the order detail table that are currently active.
row_points_expired row_points_expired int The total reward points from one row of the order detail table that have expired.
row_points_converted row_points_converted int The total reward points from one row of the order detail table that have been converted to a reward voucher.
row_points_cancelled row_points_cancelled int The total reward points from one row of the order detail table that have been cancelled (or returned).
activate_date activate_date string | int The date and time that the reward points were activated on.
The data type of this column can either be mysql datetime or a unix timestamp.
Read the internal config documentation regarding setting the carts date and time settings.
expire_date expire_date string | int The date and time that the reward points expire.
The data type of this column can either be mysql datetime or a unix timestamp.
Read the internal config documentation regarding setting the carts date and time settings.
Summary Pseudo Column Names
Help
Internal Name Default Name Data Type Description
total_points total_points int The total reward points earnt from all of a users orders.
total_points_pending total_points_pending int The total reward points from all of a users orders that are pending activation.
total_points_active total_points_active int The total reward points from all of a users orders that are currently active.
total_points_expired total_points_expired int The total reward points from all of a users orders that have expired.
total_points_converted total_points_converted int The total reward points from all of a users orders that have been converted to reward vouchers.
total_points_cancelled total_points_cancelled int The total reward points from all of a users orders that have been cancelled (or returned).
Requirements

If the table is enabled, then all columns are required and the 'Reward Points Converted' table and all of the 'Order' and 'Discount' tables must be enabled too.


Note: The reward points table does not actually exist. The reward point data is generated from an SQL SELECT query that joins data from the order summary and order detail tables, and then creates the 'Pseudo' columns within the query.

The config file contains three sets of variables for the 'reward_points' table, unless disabling the table, do not edit ANY of the details from the first set as these are used to join data from the order summary and order detail tables.

Example
// Example #1 : Defining the pseudo column names.

$config['database']['reward_points']['columns']['row_points_total'] = 'row_points_total';
// Example #2 : Disabling the table.

$config['database']['reward_points']['table'] = FALSE;

Converted Reward Points Table

Contains data on reward points that have been converted to discount codes.


Table and Column Names
Help
Internal Name Default Name Data Type Description
table reward_points_converted - The tables name.
id rew_convert_id int The tables primary key.
reward rew_convert_ord_detail_fk int Relates the reward points converted to the order detail row.
The value acts as a foreign key relating the table to the primary key of the order details table.
discount rew_convert_discount_fk int Relates the converted reward points to the created reward voucher.
The value acts as a foreign key relating the table to the primary key of the discount table.
points rew_convert_points int The number of reward points that were converted.
date rew_convert_date string | int The date and time that the reward points were converted on.
The data type of this column can either be mysql datetime or a unix timestamp.
Read the internal config documentation regarding setting the carts date and time settings.
Requirements

If the table is enabled, then all columns are required and the 'Reward Points' table and all of the 'Order' and 'Discount' tables must be enabled too.

Example
// Example #1 : Defining the table and column names.

$config['database']['reward_points_converted']['table'] = 'reward_points_converted';
$config['database']['reward_points_converted']['columns']['id'] = 'rew_convert_id';
// Example #2 : Disabling the table.

$config['database']['reward_points_converted']['table'] = FALSE;